wufan
/
ERP
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
ERP/TradeData/BaseService.cs

6393 lines
294 KiB
C#
Raw Permalink Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using TradeModel;
using NetLibrary.Data;
using NetLibrary;
using NetLibrary.Log;
using NetLibrary.OnlineTrade;
namespace TradeData
{
public class BaseService
{
#region 保存
public static int Save_Company(JC_Company Model)
{
string tsql = @"
if @CompanyId>0
begin
Update [JC_Company] set [CompanyCode]=@CompanyCode,[ParentCompanyId]=@ParentCompanyId,[CompanyName]=@CompanyName,[SimpleName]=@SimpleName,[CompanyType]=@CompanyType,[Address]=@Address,[Phone]=@Phone,[Fax]=@Fax,[Email]=@Email,[HomePage]=@HomePage,[ChargeName]=@ChargeName,[Shen]=@Shen,[Shi]=@Shi,[Qu]=@Qu,[CompanyDesc]=@CompanyDesc,[State]=@State,[PostCode]=@PostCode,[province]=@province,[city]=@city,[county]=@county,[street]=@street,[EnglishAddress]=@EnglishAddress,[CompanyEnglishName]=@CompanyEnglishName,[ChargeEnglishName]=@ChargeEnglishName,[WeightRate]=@WeightRate,[PersonFee]=@PersonFee,[ApiKey]=@ApiKey,[FeeId]=@FeeId where CompanyId=@CompanyId
end
else
begin
INSERT INTO [JC_Company]([CompanyCode],[ParentCompanyId],[CompanyName],[SimpleName],[CompanyType],[Address],[Phone],[Fax],[Email],[HomePage],[ChargeName],[Shen],[Shi],[Qu],[CompanyDesc],[State],[PostCode],[province],[city],[county],[street],[EnglishAddress],[CompanyEnglishName],[ChargeEnglishName],[WeightRate],[PersonFee],[ApiKey],FeeId)values(@CompanyCode,@ParentCompanyId,@CompanyName,@SimpleName,@CompanyType,@Address,@Phone,@Fax,@Email,@HomePage,@ChargeName,@Shen,@Shi,@Qu,@CompanyDesc,@State,@PostCode,@province,@city,@county,@street,@EnglishAddress,@CompanyEnglishName,@ChargeEnglishName,@WeightRate,@PersonFee,@ApiKey,@FeeId)
set @CompanyId=SCOPE_IDENTITY()
end
select @CompanyId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyID);
db.AddInParameter(cmd, "@CompanyCode", DbType.String, Model.CompanyCode);
db.AddInParameter(cmd, "@ParentCompanyId", DbType.Int32, Model.ParentCompanyId);
db.AddInParameter(cmd, "@CompanyName", DbType.String, Model.CompanyName);
db.AddInParameter(cmd, "@SimpleName", DbType.String, Model.SimpleName);
db.AddInParameter(cmd, "@CompanyType", DbType.Int32, Model.CompanyType);
db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
db.AddInParameter(cmd, "@Email", DbType.String, Model.Email);
db.AddInParameter(cmd, "@HomePage", DbType.String, Model.HomePage);
db.AddInParameter(cmd, "@ChargeName", DbType.String, Model.ChargeName);
db.AddInParameter(cmd, "@Shen", DbType.String, Model.Shen);
db.AddInParameter(cmd, "@Shi", DbType.String, Model.Shi);
db.AddInParameter(cmd, "@Qu", DbType.String, Model.Qu);
db.AddInParameter(cmd, "@CompanyDesc", DbType.String, Model.CompanyDesc);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
db.AddInParameter(cmd, "@province", DbType.String, Model.province);
db.AddInParameter(cmd, "@city", DbType.String, Model.city);
db.AddInParameter(cmd, "@county", DbType.String, Model.county);
db.AddInParameter(cmd, "@street", DbType.String, Model.street);
db.AddInParameter(cmd, "@EnglishAddress", DbType.String, Model.EnglishAddress);
db.AddInParameter(cmd, "@CompanyEnglishName", DbType.String, Model.CompanyEnglishName);
db.AddInParameter(cmd, "@ChargeEnglishName", DbType.String, Model.ChargeEnglishName);
db.AddInParameter(cmd, "@WeightRate", DbType.Int32, Model.WeightRate);
db.AddInParameter(cmd, "@PersonFee", DbType.Decimal, Model.PersonFee);
db.AddInParameter(cmd, "@ApiKey", DbType.String, Model.ApiKey);
db.AddInParameter(cmd, "@FeeId", DbType.Int32, Model.FeeId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 分页动态条件查询
public static List<JC_Company> GetListCompany(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
QueryService2 ser = new QueryService2();
ser.Tsql = @"select CompanyId=cast(a.CompanyId as int),a.CompanyCode,a.ParentCompanyId,a.CompanyName,a.SimpleName,a.CompanyType,a.Address,a.Phone,a.Fax,a.Email,a.HomePage,a.ChargeName,a.Shen,a.Shi,a.Qu,a.CompanyDesc,a.State,a.PostCode,a.province,a.city,a.county,a.street,a.EnglishAddress,a.CompanyEnglishName,a.ChargeEnglishName,a.WeightRate,a.PersonFee,a.ApiKey,b.UserName,b.Password,a.FeeId from JC_Company a
inner join jc_userinfo b on a.CompanyId=b.CompanyId
";
ser.Tsql += " " + ser.Filter(where);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.CompanyId desc"; }
else { ser.Sort = Sort; }
string tsql = ser.GetText();
List<JC_Company> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddOutParameter(cmd, "@RowCount", DbType.Int32, 4);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Company>();
return ListModel;
}
#endregion
#region 读取全部或单个机构
public static List<BaseModel> GetListCompany(int CompanyID)
{
string tsql = "select ID=CompanyID,Name=CompanyName from JC_Company where State=1";
if (CompanyID > 0)
{
tsql = "select ID=CompanyId,Name=CompanyName from JC_Company where CompanyID=" + CompanyID + " and State=1";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取全部或单个机构
public static List<BaseModel> GetListCompany3(int CompanyID)
{
string tsql = "select ID=CompanyID,Name=CompanyName from JC_Company where State=1";
if (CompanyID > 1)
{
tsql = "select ID=CompanyId,Name=CompanyName from JC_Company where CompanyID=" + CompanyID + " and State=1";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取全部或单个机构
public static List<BaseModel> GetListCompany2(int CompanyID)
{
string tsql = "select ID=CompanyID,Name=CompanyName from JC_Company where State=1 and CompanyId>1";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取所有部门
public static List<BaseModel> GetListDept(int CompanyID)
{
string tsql = @"select ID=DeptID,Name=DeptName from JC_DepartMent where CompanyID=@CompanyID order by SortNo desc,DeptID asc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取所有部门
public static List<JC_DepartMent> GetMyListDept(int CompanyID,int UserId)
{
string tsql = @"
declare @DeptId int
select top 1 @DeptId=DeptId from JC_UserInfo where UserId=@UserId
select DeptID,DeptName from JC_DepartMent where CompanyID=@CompanyID and DeptId=@DeptId order by SortNo desc,DeptID asc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_DepartMent>();
}
#endregion
#region 读取所属部门人员
public static List<BaseModel> GetListUser(int CompanyID, int DeptID)
{
string tsql = "select ID=UserId,Name=Name from JC_UserInfo where CompanyID=@CompanyID and State=1";
if (DeptID > 0) tsql += " and UserID in (select UserID from JC_DepartUser where DeptID=@DeptID)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
if (DeptID > 0) db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取所属部门人员
public static List<BaseModel> GetListUserForTM(int CompanyID, int DeptID)
{
string tsql = "select ID=UserId,Name=Name from JC_UserInfo where CompanyID=@CompanyID and State=1 and (@DeptID=0 or DeptID=@DeptID)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取所属部门人员
public static List<BaseModel> GetListUserInfo(int CompanyID, int DeptID, string Name, string Persons)
{
string tsql = "select ID=UserId,Name=Name from JC_UserInfo where CompanyID=@CompanyID and State=1";
if (DeptID > 0) tsql += " and UserID in (select UserID from JC_DepartUser where DeptID=@DeptID)";
if (string.IsNullOrEmpty(Name) == false) tsql += " and Name like '%'+@Name+'%'";
if (string.IsNullOrEmpty(Persons) == false) tsql += " and UserId not in (" + Persons + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
if (DeptID > 0) db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
if (string.IsNullOrEmpty(Name) == false) db.AddInParameter(cmd, "@Name", DbType.String, Name);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取所属部门人员
public static List<BaseModel> GetListUserInfoForTM(int CompanyID, int DeptID, string Name, string Persons)
{
string tsql = "select ID=UserId,Name=Name from JC_UserInfo where CompanyID=@CompanyID and State=1 and (@DeptID=0 or DeptID=@DeptID)";
if (string.IsNullOrEmpty(Name) == false) tsql += " and Name like '%'+@Name+'%'";
if (string.IsNullOrEmpty(Persons) == false) tsql += " and UserId not in (" + Persons + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
if (DeptID > 0) db.AddInParameter(cmd, "@DeptID", DbType.Int32, DeptID);
if (string.IsNullOrEmpty(Name) == false) db.AddInParameter(cmd, "@Name", DbType.String, Name);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<BaseModel>();
}
#endregion
#region 读取菜单
public static List<JC_MenuList> GetListMenu(int UserID, int CompanyId)
{
string tsql = @"
declare @UserType nvarchar(10)
select top 1 @UserType=UserType from JC_UserInfo where UserID=@UserID
if @UserType='S' or @UserType='M'
begin
select * from JC_MenuList where IsUse=1 and
id not in (select id from JC_MenuList where CompanyId is not null and CompanyId<>@CompanyId)
order by SortNo
end
else
begin
if(select COUNT(0) from JC_RoleInfo a
inner join JC_RoleUser b on a.RoleId=b.RoleId
inner join JC_RoleMenu c on a.RoleId=c.RoleID
where b.UserId=@UserID)>0
begin
select * from JC_MenuList where IsUse=1 and ModuleNo in (
select distinct c.ModuleNo from JC_RoleInfo a
inner join JC_RoleUser b on a.RoleId=b.RoleId
inner join JC_RoleMenu c on a.RoleId=c.RoleID
where b.UserId=@UserID and c.PopedomType>0) order by SortNo
end
else
begin
select *,css='ico_edit' from JC_MenuList where IsUse=1 and PopedomType>0 order by SortNo
end
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_MenuList>();
}
#endregion
#region 读取菜单
public static List<JC_MenuList2> GetListMenu2(int UserID, int CompanyId)
{
string tsql = @"
declare @UserType nvarchar(10)
select top 1 @UserType=UserType from JC_UserInfo where UserID=@UserID
if @UserType='S' or @UserType='M' or @CompanyId=1
begin
select * from JC_MenuList2 where IsUse=1 and
id not in (select id from JC_MenuList2 where CompanyId is not null and CompanyId<>@CompanyId)
order by SortNo
end
else
begin
if(select COUNT(0) from JC_RoleInfo a
inner join JC_RoleUser b on a.RoleId=b.RoleId
inner join JC_RoleMenu c on a.RoleId=c.RoleID
where b.UserId=@UserID)>0
begin
select * from JC_MenuList2 where IsUse=1 and ModuleNo in (
select distinct c.ModuleNo from JC_RoleInfo a
inner join JC_RoleUser b on a.RoleId=b.RoleId
inner join JC_RoleMenu c on a.RoleId=c.RoleID
where b.UserId=@UserID and c.PopedomType>0) order by SortNo
end
else
begin
select * from JC_MenuList2 where IsUse=1 and PopedomType>0 order by SortNo
end
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_MenuList2>();
}
#endregion
#region 登录
public static JC_UserInfo Login(string UserName, string PassWord, string LoginIp)
{
string tsql = @"
declare @UserID int,@Name nvarchar(20),@UserType nvarchar(5)
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and a.State=1 and b.State=1
--if @UserID>0
--begin
--insert JC_LoginLog(UserID,LoginTime,LoginIp,LoginOutTime)
--values(@UserID,getdate(),@LoginIp,null)
--update JC_UserInfo set LastLoginTime=getdate(),LoginCount=isnull(LoginCount,0)+1,LastLoginIp=@LoginIp where UserID=@UserID
--end
select a.UserID,a.Name,a.UserType,a.CompanyId,CompanyName=b.SimpleName,a.DeptId,a.Duty
from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@PassWord", DbType.String, PassWord);
db.AddInParameter(cmd, "@LoginIp", DbType.String, LoginIp);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0) return null;
var list = tb.Rows[0].ToModel<JC_UserInfo>();
return list;
}
#endregion
#region 登录
public static JC_UserInfo LoginNew(string UserName, string PassWord, string LoginIp, int IsNB)
{
string tsql = @"
declare @UserID int,@Name nvarchar(20),@UserType nvarchar(5)
if @IsNB=1 or (select count(0) from [JC_InnerIp] where InnerIp=@LoginIp and InnerIp Is not null)>0
begin
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and UserType<>'H' and a.State=1 and b.State=1
end
else
begin
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and a.State=1 and b.State=1 and a.IsLogin=1
end
--if @UserID>0
--begin
--insert JC_LoginLog(UserID,LoginTime,LoginIp,LoginOutTime)
--values(@UserID,getdate(),@LoginIp,null)
--update JC_UserInfo set LastLoginTime=getdate(),LoginCount=isnull(LoginCount,0)+1,LastLoginIp=@LoginIp where UserID=@UserID
--end
select a.UserID,a.Name,a.UserType,a.CompanyId,CompanyName=b.SimpleName,a.DeptId,a.Duty
from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@PassWord", DbType.String, PassWord);
db.AddInParameter(cmd, "@LoginIp", DbType.String, LoginIp);
db.AddInParameter(cmd, "@IsNB", DbType.String, IsNB);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0) return null;
var list = tb.Rows[0].ToModel<JC_UserInfo>();
return list;
}
#endregion
#region 登录
public static JC_UserInfo LoginNew2(string UserName, string PassWord)
{
string tsql = @"
declare @UserID int
select top 1 @UserID=UserID from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserName=@UserName and a.PassWord=@PassWord and UserType<>'P' and a.State=1 and b.State=1 and a.IsLogin=1
select a.UserID,a.Name,a.UserType,a.CompanyId,CompanyName=b.SimpleName,a.DeptId,a.Duty
from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@PassWord", DbType.String, PassWord);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0) return null;
var list = tb.Rows[0].ToModel<JC_UserInfo>();
return list;
}
#endregion
#region 登录
public static JC_UserInfo Login2(int UserId)
{
string tsql = @"
select a.UserID,a.Name,a.UserType,a.CompanyId,CompanyName=b.SimpleName,a.deptId
from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.UserID=@UserId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0) return null;
var list = tb.Rows[0].ToModel<JC_UserInfo>();
return list;
}
#endregion
#region 退出
public static void ExitLogin(int UserID)
{
//return;
string tsql = @"
declare @id int
select top 1 @id=id from JC_LoginLog
where UserID=@UserID
order by id desc
--update JC_LoginLog set LoginOutTime=getdate() where id=@id
update JC_UserInfo set LoginState=0 where UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 修改密码
public static int UpdatePassWord(string UserName, int UserID, string OldPassword, string NewPassWord)
{
string tsql = @"
declare @bk int
select @bk=count(0) from JC_UserInfo where UserID=@UserID and Password=@OldPassword
if @bk=1 and (select count(0) from JC_UserInfo where UserID<>@UserID and UserName=@UserName and State=1)>0
begin
select @bk=-1
end
if @bk=1
begin
update JC_UserInfo set Password=@NewPassWord,UserName=@UserName where UserID=@UserID
end
select @bk
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@OldPassword", DbType.String, OldPassword);
db.AddInParameter(cmd, "@NewPassWord", DbType.String, NewPassWord);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 新增管理员账号
public static int AddUser(string UserName, string Password,int CompanyId)
{
string tsql = @"
if(select count(0) from JC_UserInfo where UserName=@UserName and State=1)>0
begin
select 1
set
end
else
begin
INSERT INTO [JC_UserInfo]([Name],[Sex],[UserName],[Password],[UserType],[State],[CompanyId])
values('管理员','男',@UserName,@Password,'M',1,@CompanyId)
select 0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@Password", DbType.String, Password);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证账号
public int IsUserName(string UserName)
{
string tsql = @"
select count(0) from JC_UserInfo where UserName=@UserName and State=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证账号
public int IsUser(int UserId, string Password)
{
string tsql = @"
select count(0) from JC_UserInfo where UserId=@UserID and Password=@Password and State=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@Password", DbType.String, Password);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 新增管理员账号
public int RegisterAddUser(string UserName, string Password, int CompanyId)
{
string tsql = @"
declare @UserId int
INSERT INTO [JC_UserInfo]([Name],[Sex],[UserName],[Password],[UserType],[State],[CompanyId])
values('管理员','男',@UserName,@Password,'M',1,@CompanyId)
set @UserId=SCOPE_IDENTITY()
select @UserId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@Password", DbType.String, Password);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 读取基础类型
public static List<BaseModel> GetListBaseCode(int CompanyID, string KeyName)
{
string tsql = @"
select ID=Code,Name from JC_BaseCodeDetail
where CompanyID=@CompanyID and KeyName=@KeyName and IsUse=1
order by SortNo
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count == 0)
{
tsql = "select ID=Code,Name from JC_BaseCodeDetail where KeyName=@KeyName and IsUse=1 order by SortNo";
cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
tb = db.ExecuteDataTable(cmd);
}
return tb.ToList<BaseModel>();
}
#endregion
#region 读取平台
public static List<JC_BaseCodeDetail> GetPlatBaseCode(int CompanyID, string KeyName)
{
string tsql = @"
select * from JC_BaseCodeDetail where KeyName=@KeyName and (@CompanyID=0 or CompanyID=@CompanyID) and IsUse=1 order by SortNo
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@KeyName", DbType.String, KeyName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_BaseCodeDetail>();
}
#endregion
#region 读取审批配置
public SP_CheckConfig GetCheckConfig(int CompanyID, string CheckType, int StepNo)
{
SP_CheckConfig model = null;
string tsql = "select * from SP_CheckConfig where CompanyID=@CompanyID and CheckType=@CheckType and StepNo=@StepNo";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@CheckType", DbType.String, CheckType);
db.AddInParameter(cmd, "@StepNo", DbType.Int32, StepNo);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<SP_CheckConfig>();
return model;
}
#endregion
#region 提示消息
/// <summary>
/// 提示消息
/// </summary>
public DataSet UserOpenMess(string UserType, int UserId)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("GetOpenMess");
db.AddInParameter(cmd, "@UserType", DbType.String, UserType);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataSet ds = null;
ds = db.ExecuteDataSet(cmd);
return ds;
}
#endregion
#region 保存提示消息
public static void SendOpenMess(int UserId, string Content, string Url)
{
JC_OpenMess Model = new JC_OpenMess();
Model.UserId = UserId;
Model.UserType = "P";
Model.Content = Content;
Model.Type = 1;
Model.Url = Url;
Model.IsRead = 0;
SaveOpenMess(Model);
}
#endregion
#region 保存提示消息
public static int SaveOpenMess(JC_OpenMess Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_OpenMess] set [Content]=@Content,[IsRead]=@IsRead,[OpenDate]=@OpenDate,[Target]=@Target,[Type]=@Type,[UserType]=@UserType,[UserId]=@UserId,[Url]=@Url,[MenuName]=@MenuName where Id=@Id
end
else
begin
INSERT INTO [JC_OpenMess]([Content],[IsRead],[OpenDate],[Target],[Type],[UserType],[UserId],[Url],[MenuName],InUserId)values(@Content,@IsRead,@OpenDate,@Target,@Type,@UserType,@UserId,@Url,@MenuName,@InUserId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@Content", DbType.String, Model.Content);
db.AddInParameter(cmd, "@IsRead", DbType.Int32, Model.IsRead);
db.AddInParameter(cmd, "@OpenDate", DbType.DateTime, Model.OpenDate);
db.AddInParameter(cmd, "@Target", DbType.Int32, Model.Target);
db.AddInParameter(cmd, "@Type", DbType.Int32, Model.Type);
db.AddInParameter(cmd, "@UserType", DbType.String, Model.UserType);
db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserId);
db.AddInParameter(cmd, "@Url", DbType.String, Model.Url);
db.AddInParameter(cmd, "@MenuName", DbType.String, Model.MenuName);
db.AddInParameter(cmd, "@InUserId", DbType.String, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存附件资源
/// <summary>
/// 保存附件资源
/// </summary>
public int SaveResource(JC_Resource Model)
{
string tsql = @"
if @id>0
begin
Update [JC_Resource] set [Type]=@Type,[FileName]=@FileName,[FileUrl]=@FileUrl,[FileSize]=@FileSize,[ExtType]=@ExtType,[FileKeyWord]=@FileKeyWord,[CreateTime]=@CreateTime,[UpdateTime]=@UpdateTime,[State]=@State,[ItemName]=@ItemName,[InUserId]=@InUserId,[CompanyId]=@CompanyId where id=@id
end
else
begin
INSERT INTO [JC_Resource]([Type],[FileName],[FileUrl],[FileSize],[ExtType],[FileKeyWord],[CreateTime],[UpdateTime],[State],[ItemName],[InUserId],[CompanyId])values(@Type,@FileName,@FileUrl,@FileSize,@ExtType,@FileKeyWord,@CreateTime,@UpdateTime,@State,@ItemName,@InUserId,@CompanyId)
set @id=@@IDENTITY
end
select @id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@id", DbType.Int32, Model.id);
db.AddInParameter(cmd, "@Type", DbType.Int32, Model.Type);
db.AddInParameter(cmd, "@FileName", DbType.String, Model.FileName);
db.AddInParameter(cmd, "@FileUrl", DbType.String, Model.FileUrl);
db.AddInParameter(cmd, "@FileSize", DbType.String, Model.FileSize);
db.AddInParameter(cmd, "@ExtType", DbType.String, Model.ExtType);
db.AddInParameter(cmd, "@FileKeyWord", DbType.String, Model.FileKeyWord);
db.AddInParameter(cmd, "@CreateTime", DbType.DateTime, Model.CreateTime);
db.AddInParameter(cmd, "@UpdateTime", DbType.DateTime, Model.UpdateTime);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@ItemName", DbType.String, Model.ItemName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
/// <summary>
/// 保存附件资源返回资源ids
/// </summary>
public string SaveResourceList(List<JC_Resource> List)
{
string Ids = "";
if (List != null)
{
foreach (var md in List)
{
Ids += SaveResource(md) + ",";
}
}
return Ids.Trim(',');
}
#region 分页查询
public List<JC_Resource> GetListJC_Resource(string Ids)
{
string tsql = "select a.id,a.Type,a.FileName,a.FileUrl,a.FileSize,a.ExtType,a.FileKeyWord,a.CreateTime,a.UpdateTime,a.State,a.ItemName,a.InUserId,a.CompanyId from JC_Resource a where Id in (" + Ids + ")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Resource>();
}
#endregion
/// <summary>
/// 根据ids返回资源url
/// </summary>
public List<JC_Resource> GetResourceList(string Ids)
{
if (Ids != "")
{
var list = GetListJC_Resource(Ids.Trim(','));
return list;
}
return null;
}
#endregion
#region 读取店铺
public List<JC_Shop> GetShopList(int CompanyID, int PlatType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where CompanyId=@CompanyId and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取店铺
public List<JC_Shop> GetShopListForTM(int CompanyID, int UserId)
{
string tsql = @"
select a.ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade,IsSelf=case when b.shopid>0 then 1 else 0 end
from JC_Shop a
left join (
select distinct shopid from JC_ShopUser where UserId=@UserId and (SType='M' or SType='S'))b
on a.ShopId=b.ShopId
where CompanyId=@CompanyID order by PlatType,b.shopid desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取店铺
public List<JC_Shop> GetDeptShopListUser(int UserId, int PlatType, string UserType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
(DeptId IN (SELECT DeptId from JC_UserInfo WHERE UserId=@UserId)
or ShopId IN (SELECT ShopId from JC_ShopUser WHERE UserId=@UserId))
and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (UserType == "M" || UserType == "S") tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取主管店铺
public List<JC_Shop> GetShopListMaster(int UserId, int PlatType, string UserType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
ShopId IN (SELECT ShopId from JC_ShopUser WHERE SType='M' and UserId=@UserId)
and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (UserType == "M" || UserType == "S") tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取店铺
public List<JC_Shop> GetShopListUser(int UserId, int PlatType, string UserType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
ShopId IN (SELECT ShopId from JC_ShopUser WHERE UserId=@UserId)
and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (UserType == "M" || UserType == "S") tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取店铺
public List<JC_Shop> GetShopListUser2(int DeptId,int UserId, int PlatType, string UserType)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where
ShopId IN (SELECT ShopId from JC_ShopUser WHERE UserId=@UserId)
and (@DeptId=0 or DeptId=@DeptId) and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
if (UserType == "M" || UserType == "S") tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where (@DeptId=0 or DeptId=@DeptId) and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DeptId", DbType.Int32, DeptId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取部门店铺
public List<JC_Shop> GetDeptShopList(int CompanyID, int PlatType,int DeptId)
{
string tsql = @"
select ShopId,ShopName,PlatType,DeptId,InDate,CompanyId,LevelGrade from JC_Shop where CompanyId=@CompanyId and (@DeptId=0 or DeptId=@DeptId) and (@PlatType=0 or PlatType=@PlatType) order by PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@PlatType", DbType.Int32, PlatType);
db.AddInParameter(cmd, "@DeptId", DbType.Int32, DeptId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 读取店铺优先级
public List<JC_Shop> GetAllShopList(int CompanyID)
{
string tsql = @"
select ShopId,ShopName,PlatType,PlatName=b.Name,DeptId,InDate,a.CompanyId,LevelGrade=isnull(LevelGrade,0) from JC_Shop a
inner join JC_BaseCodeDetail b on a.PlatType=b.SortNo and b.Keyname='ptlx' where a.CompanyId=@CompanyId and b.CompanyId=@CompanyId order by LevelGrade desc,a.PlatType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Shop>();
}
#endregion
#region 删除审批配置
public void DeleteCheckConfig(string CheckType, int CompanyId)
{
string tsql = @"
delete from SP_CheckConfig where CheckType=@CheckType and CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CheckType", DbType.String, CheckType);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存审批配置
public int SaveCheckConfig(SP_CheckConfig Model)
{
string tsql = @"
if @Id>0
begin
Update [SP_CheckConfig] set [CheckType]=@CheckType,[StepNo]=@StepNo,[CheckUserId]=@CheckUserId,[CompanyId]=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [SP_CheckConfig]([CheckType],[StepNo],[CheckUserId],[CompanyId])values(@CheckType,@StepNo,@CheckUserId,@CompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@CheckType", DbType.String, Model.CheckType);
db.AddInParameter(cmd, "@StepNo", DbType.Int32, Model.StepNo);
db.AddInParameter(cmd, "@CheckUserId", DbType.String, Model.CheckUserId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 读取首页订单数
public DT_DefaultData GetDefaultData(int CompanyID, DateTime? InDate)
{
DT_DefaultData model = null;
string tsql = @"
declare @Num int,@TotalPrice Decimal(18,2),@SiglePrice Decimal(18,2),@BuyMoney Decimal(18,2)
select @Num=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,Indate,@InDate)=0
select @TotalPrice=SUM(RMBPrice) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,Indate,@InDate)=0
select @SiglePrice=@TotalPrice/@Num
--select @BuyMoney=sum(b.GoodsNum*c.Price) from DT_OrderInfo a
--inner join DT_OrderGoods b on a.OrderId=b.OrderId
--inner join HW_GoodsDetail c on b.DetailId=c.DetailId
--where a.CompanyId=@CompanyID and state>0 and datediff(day,a.InDate,@InDate)=0
select Num=isnull(@Num,0),TotalPrice=isnull(@TotalPrice,0),SiglePrice=isnull(@SiglePrice,0),AddPrice=isnull(@TotalPrice,0)-isnull(@BuyMoney,0)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_DefaultData>();
return model;
}
#endregion
#region 读取首页订单数
public DT_DefaultData GetDefaultDataForTM(int CompanyID, DateTime? InDate, DateTime? PreDate)
{
DT_DefaultData model = null;
string tsql = @"
declare @Num int,@TotalPrice Decimal(18,2),@SiglePrice Decimal(18,2),@BuyMoney Decimal(18,2),@Num2 int,@Num3 int,@Num4 money,@Num5 money,@Num6 money,@Num7 money,@Num9 int
select @Num=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,Indate,@InDate)=0
select @TotalPrice=SUM(RMBPrice) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,Indate,@InDate)=0
select @SiglePrice=@TotalPrice/@Num
select @Num2=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,Indate,@PreDate)=0
select @Num3=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state=2 and datediff(day,PostDate,@PreDate)=0
select @Num4=SUM(c.Price*c.KCNum) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsInDetail c on b.Id=c.OrderGoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,a.PostDate,@PreDate)=0
select @Num5=SUM(GoodsMoney)+sum(PostFee) from CG_Purchase where IsDelete=0 and CompanyId=@CompanyID and datediff(day,InDate,@PreDate)=0
select @Num6=SUM(Price*KCNum) from HW_GoodsInDetail where datediff(day,InDate,@PreDate)=0 and OrderGoodsId>-1
select @Num7=SUM(Price*KCNum) from HW_GoodsInDetail where CKDetailId=0
select @Num9=COUNT(0) from DT_OrderInfoNew where State=1 and FPDate is not null and MoneyState=1 and PostState=0 and BuyDate is not null
select Num=isnull(@Num,0),TotalPrice=isnull(@TotalPrice,0),SiglePrice=isnull(@SiglePrice,0),Num2=isnull(@Num2,0),Num3=isnull(@Num3,0),Num4=isnull(@Num4,0),Num5=isnull(@Num5,0),Num6=isnull(@Num6,0),Num7=isnull(@Num7,0),Num9=isnull(@Num9,0)
select top 3 a.RMBPrice,b.ShopName from (
select ShopId,RMBPrice=SUM(RMBPrice) from DT_OrderInfo where State>0 and State<3 and DATEDIFF(day,InDate,@PreDate)=0 group by ShopId)a
inner join JC_Shop b on a.ShopId=b.ShopId
order by a.RMBPrice desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
db.AddInParameter(cmd, "@PreDate", DbType.DateTime, PreDate);
DataSet tb = db.ExecuteDataSet(cmd);
if (tb.Tables[0].Rows.Count > 0) model = tb.Tables[0].Rows[0].ToModel<DT_DefaultData>();
string shop = "";
if (tb.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < tb.Tables[1].Rows.Count; i++)
{
//shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + "【" + tb.Tables[1].Rows[i]["RMBPrice"].ToString() + "】 ";
shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + " ";
}
}
model.Num8 = shop.Trim();
return model;
}
#endregion
#region 读取首页订单数
public DT_DefaultData GetDefaultDataForTM2(int CompanyID, DateTime? InDate, DateTime? PreDate,DateTime? SDate,DateTime? EDate)
{
DT_DefaultData model = null;
string tsql = @"
declare @Num int,@TotalPrice Decimal(18,2),@SiglePrice Decimal(18,2),@TotalPriceUSD Decimal(18,2),@SiglePriceUSD Decimal(18,2),@BuyMoney Decimal(18,2),@Num2 int,@Num3 int,@Num4 money,@Num5 money,@Num6 money,@Num7 money,@Num9 int,@Num10 money,@Num11 int,@BackPrice Decimal(18,2),@MRate Decimal(18,4)
select @Num=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and OrderDate>=@SDate and OrderDate<=@EDate
select @TotalPrice=SUM(RMBPrice) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and OrderDate>=@SDate and OrderDate<=@EDate
select @SiglePrice=@TotalPrice/@Num
set @MRate=6.7
select top 1 @MRate=MRate from JC_Money where MCode='USD'
set @TotalPriceUSD=@TotalPrice/@MRate
set @SiglePriceUSD=@SiglePrice/@MRate
select @Num2=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state>0 and datediff(day,InDate,@InDate)=0
select @Num3=COUNT(*) from DT_OrderInfo where CompanyId=@CompanyID and state=2 and datediff(day,PostDate,@PreDate)=0
select @Num10=sum((b.GoodsNum-b.InGoodsNum)*b.GoodsPrice) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
where a.IsDelete=0 and a.GoodsState<2 and a.State<3 and b.InGoodsNum<b.GoodsNum
select @Num4=SUM(c.Price*c.KCNum) from DT_OrderInfo a
inner join DT_OrderGoods b on a.OrderId=b.OrderId
inner join HW_GoodsInDetail c on b.Id=c.OrderGoodsId
where a.CompanyId=@CompanyID and a.state=2 and datediff(day,a.PostDate,@PreDate)=0
select @Num5=SUM(GoodsMoney)+sum(PostFee) from CG_Purchase where IsDelete=0 and CompanyId=@CompanyID and datediff(day,InDate,@PreDate)=0
select @Num6=SUM(Price*KCNum) from HW_GoodsInDetail where datediff(day,InDate,@PreDate)=0 and OrderGoodsId>-1
select @Num7=SUM(Price*KCNum) from HW_GoodsInDetail where CKDetailId=0
--select @Num9=COUNT(0) from DT_OrderInfoNew where State=1 and FPDate is not null and MoneyState=1 and PostState=0 and BuyDate is not null
select @Num9=COUNT(0) from DT_OrderInfoNew where State=1 and PostState=0
select @Num11=COUNT(0) from DT_OrderInfo a
inner join DT_OrderBagBack b on a.OrderId=b.OrderId
where a.CompanyId=@CompanyID and DATEDIFF(day,b.InDate,GETDATE())<=7
select @BackPrice=SUM(a.TotalPrice) from DT_OrderInfo a
inner join DT_OrderBagBack b on a.OrderId=b.OrderId
where a.CompanyId=@CompanyID and DATEDIFF(day,b.InDate,GETDATE())<=7
select Num=isnull(@Num,0),TotalPrice=isnull(@TotalPrice,0),SiglePrice=isnull(@SiglePrice,0),TotalPriceUSD=isnull(@TotalPriceUSD,0),SiglePriceUSD=isnull(@SiglePriceUSD,0),Num2=isnull(@Num2,0),Num3=isnull(@Num3,0),Num4=isnull(@Num4,0),Num5=isnull(@Num5,0),Num6=isnull(@Num6,0),Num7=isnull(@Num7,0),Num9=isnull(@Num9,0),Num10=isnull(@Num10,0),Num11=isnull(@Num11,0),BackPrice=isnull(@BackPrice,0)
select top 3 a.RMBPrice,b.ShopName from (
select ShopId,RMBPrice=SUM(RMBPrice) from DT_OrderInfo where State>0 and State<3 and DATEDIFF(day,InDate,@PreDate)=0 group by ShopId)a
inner join JC_Shop b on a.ShopId=b.ShopId
order by a.RMBPrice desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
db.AddInParameter(cmd, "@PreDate", DbType.DateTime, PreDate);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataSet tb = db.ExecuteDataSet(cmd);
if (tb.Tables[0].Rows.Count > 0) model = tb.Tables[0].Rows[0].ToModel<DT_DefaultData>();
string shop = "";
if (tb.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < tb.Tables[1].Rows.Count; i++)
{
//shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + "【" + tb.Tables[1].Rows[i]["RMBPrice"].ToString() + "】 ";
shop += tb.Tables[1].Rows[i]["ShopName"].ToString() + " ";
}
}
model.Num8 = shop.Trim();
return model;
}
#endregion
#region 读取首页订单数
public DT_DefaultData2 GetDefaultDataForTM3(int CompanyID)
{
DT_DefaultData2 model = null;
string tsql = @"
declare @Num1 int,@TSoild1 Decimal(18,2),@Price1 Decimal(18,2),@Num2 int,@TSoild2 Decimal(18,2),@Price2 Decimal(18,2),@Num3 int,@TSoild3 Decimal(18,2),@Price3 Decimal(18,2),@Num4 int,@TSoild4 Decimal(18,2),@Price4 Decimal(18,2),@Num5 int,@TSoild5 Decimal(18,2),@Price5 Decimal(18,2),@Num6 int,@TSoild6 Decimal(18,2),@Price6 Decimal(18,2)
select @Num1=sum(b.GoodsNum-ISNULL(b.InGoodsNum,0)-ISNULL(b.ErrorNum,0)),@TSoild1=sum(isnull(d.Solid,0)*(b.GoodsNum-ISNULL(b.InGoodsNum,0)-ISNULL(b.ErrorNum,0))),@Price1=sum(isnull(b.GoodsPrice,0)*(b.GoodsNum-ISNULL(b.InGoodsNum,0)-ISNULL(b.ErrorNum,0))) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.State<3 and a.GoodsState<2 and isnull(a.IsGJ,0)=0
select @Num6=sum(b.SJOutNum),@TSoild6=sum(isnull(d.Solid,0)*b.SJOutNum),@Price6=sum(isnull(b.GoodsPrice,0)*b.SJOutNum) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.IsGJ=1 and a.PostState=0
select @Num2=sum(b.SJOutNum),@TSoild2=sum(isnull(d.Solid,0)*b.SJOutNum),@Price2=sum(isnull(b.GoodsPrice,0)*b.SJOutNum) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.IsGJ=1 and a.PostState=3
select @Num3=sum(b.SJOutNum),@TSoild3=sum(isnull(d.Solid,0)*b.SJOutNum),@Price3=sum(isnull(b.GoodsPrice,0)*b.SJOutNum) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.IsGJ=1 and a.PostState=4
select @Num4=sum(b.SJOutNum),@TSoild4=sum(isnull(d.Solid,0)*b.SJOutNum),@Price4=sum(isnull(b.GoodsPrice,0)*b.SJOutNum) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.IsGJ=1 and a.PostState=5
select @Num5=sum(b.SJOutNum-ISNULL(b.OutInNum,0)),@TSoild5=sum(isnull(d.Solid,0)*(b.SJOutNum-ISNULL(b.OutInNum,0))),@Price5=sum(isnull(b.GoodsPrice,0)*(b.SJOutNum-ISNULL(b.OutInNum,0))) from CG_Purchase a
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
inner join HW_GoodsDetail c on b.GoodsDetailId=c.DetailId
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
where a.CompanyID=@CompanyID and a.IsDelete=0 and a.IsGJ=1 and a.PostState=6
select Num1=isnull(@Num1,0),TSoild1=isnull(@TSoild1,0),Price1=isnull(@Price1,0),Num2=isnull(@Num2,0),TSoild2=isnull(@TSoild2,0),Price2=isnull(@Price2,0),Num3=isnull(@Num3,0),TSoild3=isnull(@TSoild3,0),Price3=isnull(@Price3,0),Num4=isnull(@Num4,0),TSoild4=isnull(@TSoild4,0),Price4=isnull(@Price4,0),Num5=isnull(@Num5,0),TSoild5=isnull(@TSoild5,0),Price5=isnull(@Price5,0),Num6=isnull(@Num6,0),TSoild6=isnull(@TSoild6,0),Price6=isnull(@Price6,0)
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataSet tb = db.ExecuteDataSet(cmd);
if (tb.Tables[0].Rows.Count > 0) model = tb.Tables[0].Rows[0].ToModel<DT_DefaultData2>();
return model;
}
#endregion
#region 读取未读数量
public DefaultReadNum GetDefaultUnRead(int CompanyID, int UserId)
{
DefaultReadNum model = null;
string tsql = @"
declare @CGCheckNum int,@CGCheckNum2 int,@CGCheckNum3 int,@CGCheckNum4 int
select @CGCheckNum=COUNT(0) from CG_Purchase where CompanyId=@CompanyID and CheckUid=@UserId and State=0
select @CGCheckNum2=COUNT(0) from CG_Purchase where CompanyId=@CompanyID and InUserId=@UserId and State<3 and ErrorInfo is not null and ErrorInfo<>''
select @CGCheckNum3=COUNT(0) from DT_OrderInfo where CompanyId=@CompanyID and State=1 and MateState=0
select CGCheckNum=@CGCheckNum,CGCheckNum2=@CGCheckNum2,CGCheckNum3=@CGCheckNum3,CGCheckNum4=@CGCheckNum4
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DefaultReadNum>();
return model;
}
#endregion
#region 读取首页通知
public List<TZ_Notice> GetNoticeList(int CompanyID)
{
string tsql = @"
select top 10 a.* from TZ_Notice a
inner join TZ_NoticeRece b on a.Id=b.NoticeId
where b.ReceType=1 and (a.StartDate is null or a.StartDate<=GETDATE()) and (a.EndDate is null or a.EndDate>GETDATE()) and (b.ReceCompanyId=0 or b.ReceCompanyId=@CompanyId)
order by a.InDate desc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<TZ_Notice>();
}
#endregion
#region 读取海外仓国家
public List<JC_Country> GetFbaCountry(int CompanyID)
{
string tsql = @"
select * from JC_Country where IsFba=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Country>();
}
#endregion
#region 验证国家
public DT_Country GetCountryCode(string CountryName)
{
DT_Country model = null;
string tsql = @"
select top 1 Code,CountryName=Name,EnglishName from JC_Country where Name=@CountryName or EnglishName=@CountryName or Code=@CountryName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CountryName", DbType.String, CountryName);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_Country>();
return model;
}
#endregion
#region 读取国家
public List<JC_Country> GetCountryList(int CompanyID)
{
string tsql = @"
select * from JC_Country where Isuse=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Country>();
}
#endregion
#region 锁定,true锁定成功,false锁定失败(被人占用)
public static bool SyncLock(int CompanyId, int SyncType, int InUserId, DateTime? StartDate)
{
string tsql = @"
declare @Id int,@State int
select @id=id,@State=State from JC_SyncState where CompanyId=@CompanyId and SyncType=@SyncType
if @State=1
begin
select 1
end
else
begin
if @id>0
begin
update JC_SyncState set InUserId=@InUserId,StartDate=getdate(),State=1 where id=@id
end
else
begin
insert JC_SyncState(CompanyId,SyncType,InUserId,StartDate,State)
values(@CompanyId,@SyncType,@InUserId,getdate(),1)
end
select 0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SyncType", DbType.Int32, SyncType);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, InUserId);
db.AddInParameter(cmd, "@StartDate", DbType.DateTime, StartDate);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
if (a == 0) return true;
return false;
}
#endregion
#region 锁定,true锁定成功,false锁定失败(被人占用)
public static bool SyncLock3()
{
string tsql = @"
select State from JC_SyncState where CompanyId=1 and SyncType=2
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
if (a == 1) return false;
return true;
}
#endregion
#region 锁定,true锁定成功,false锁定失败(被人占用)
public static bool SyncLock2(string Ids,int UserId,string InName, DateTime? SDate, DateTime? EDate)
{
string tsql = @"
if(select count(0) from JC_SyncState where State=1 and CompanyId in (" + Ids + @") and SyncType=1)>0
begin
select 1
end
else
begin
update JC_SyncState set InName=@InName,InUserId=@UserId,state=1,StartDate=getdate(),EndDate=null,IsDD=1,DDResult=null,DDSDate=@DDSDate,DDEDate=@DDEDate where CompanyId in (" + Ids + @") and SyncType=1
select 0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DDSDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@DDEDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@InName", DbType.String, InName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
if (a == 1) return true;
return false;
}
#endregion
#region 锁定,true锁定成功,false锁定失败(被人占用)
public static void SyncLockAll(int UserId, string InName, DateTime? SDate, DateTime? EDate)
{
string tsql = @"
update a set InName=@InName,InUserId=@UserId,state=1,StartDate=getdate(),EndDate=null,IsDD=1,DDResult=null,DDSDate=@DDSDate,DDEDate=@DDEDate from JC_SyncState a
inner join JC_Shop b on a.CompanyId=b.ShopId
where SyncType=1 and a.State=0 and IsDD=0 and b.AccessToken is not null and b.AccessToken<>''
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DDSDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@DDEDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@InName", DbType.String, InName);
db.ExecuteNonQuery(cmd);
}
#endregion
#region
public static List<JC_SyncState> GetSyncState(string Ids)
{
string tsql = @"
select a.*,b.ShopName from JC_SyncState a
inner join Jc_shop b on a.CompanyId=b.ShopId
where a.CompanyId in (" + Ids + @") and SyncType=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_SyncState>();
}
#endregion
#region 解锁
public static void UnSyncLock(int CompanyId, int SyncType, DateTime? EndDate)
{
string tsql = @"
update JC_SyncState set State=0,EndDate=@EndDate where CompanyId=@CompanyId and SyncType=@SyncType
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SyncType", DbType.Int32, SyncType);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, EndDate);
db.ExecuteNonQuery(cmd);
}
#endregion
#region API订单转本地订单
public static void OrderAdd(int CompanyId, int PlatOrderId)
{
try
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("OrderAdd");
db.AddInParameter(cmd, "@PlatOrderId", DbType.Int32, PlatOrderId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
ErrorFollow.TraceWrite("API订单转本地订单", "OrderAdd", ex.Message + "PlatOrderId:" + PlatOrderId + ";CompanyId:" + CompanyId);
}
}
#endregion
#region API订单转本地订单
public static void OrderAddForTM(int CompanyId, int PlatOrderId,int UserId)
{
try
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("OrderAddForTM");
db.AddInParameter(cmd, "@PlatOrderId", DbType.Int32, PlatOrderId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
db.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
ErrorFollow.TraceWrite("API订单转本地订单", "OrderAdd", ex.Message + "PlatOrderId:" + PlatOrderId + ";CompanyId:" + CompanyId);
}
}
#endregion
#region 读取运费参考
public JC_PostFee GetPostFeeModel(int CompanyID, int ExpressId)
{
JC_PostFee model = null;
string tsql = @"
select * from JC_PostFee
where CompanyId=@CompanyID and Expressid=@ExpressId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_PostFee>();
return model;
}
#endregion
#region 读取运费计算规则
public List<JC_PostFeeDetail> GetPostFeeDetailList(int CompanyID, int ExpressId, string Country)
{
string tsql = @"
declare @QCCountry nvarchar(100)
set @QCCountry=''
select top 1 @QCCountry=EnglishName from JC_Country where Code=@Country and IsUse=1
if @QCCountry<>'' and (select COUNT(0) from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country=@QCCountry)>0
begin
select b.*,a.FeeType from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country=@QCCountry order by SWeight
end
else
begin
select b.*,a.FeeType from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country='0' order by SWeight
end ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PostFeeDetail>();
}
#endregion
#region 读取运费计算规则
public List<JC_PostFeeDetail> GetPostFeeDetailListForTM(int CompanyID, int ExpressId, string Country)
{
string tsql = @"
declare @QCCountry nvarchar(100),@CountryName nvarchar(150)
set @QCCountry=''
select top 1 @QCCountry=EnglishName,@CountryName=Name from JC_Country where Code=@Country and IsUse=1
if @QCCountry<>'' and (select COUNT(0) from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country=@QCCountry)>0
begin
select b.*,a.FeeType from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country=@QCCountry order by SWeight
end
else
if @CountryName<>'' and (select COUNT(0) from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.CountryName=@CountryName)>0
begin
select b.*,a.FeeType from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.CountryName=@CountryName order by SWeight
end
begin
select b.*,a.FeeType from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
where a.Expressid=@ExpressId and b.Country='0' order by SWeight
end ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PostFeeDetail>();
}
#endregion
#region 读取运费计算规则
public List<JC_PostFeeDetail> GetPostFeeDetail(int ExpressId)
{
string tsql = @"
select a.*,b.FeeType from JC_PostFeeDetail a
inner join JC_PostFee b on a.FeeId=b.Id
where b.ExpressID=@ExpressId order by a.CountryCode,a.SWeight";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PostFeeDetail>();
}
#endregion
#region 读取物流渠道
public List<JC_ExpressDetail> GetExpressPostList(int CompanyID, int LogisticsId)
{
string tsql = @"
select c.ExpressID,c.Name,c.PrintTemplateName from JC_ExpressPost a
--inner join JC_ExpressPostDetail b on a.ExpressPostID=b.ExpressPostID
inner join JC_Express c on a.ExpressID=c.ExpressID
where a.CompanyId=@CompanyID and a.IsUse=1 and (@LogisticsId=0 or LogisticsId=@LogisticsId) order by c.LogisticsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_ExpressDetail>();
}
#endregion
#region 读取物流对象
public JC_Express GetExpressModel(int ExpressId,int CompanyId)
{
JC_Express model = null;
string tsql = @"
select a.*,b.DefaultAddr,b.UserCode from JC_Express a
inner join JC_ExpressPost b on a.ExpressId=b.ExpressId
where a.Expressid=@ExpressId and b.CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Express>();
return model;
}
#endregion
#region 读取物流对象
public JC_Express GetExpressModelForTM(int ExpressId, int CompanyId)
{
JC_Express model = null;
string tsql = @"
select a.*,b.ExpressInfo from JC_Express a
inner join JC_ExpressType b on a.LogisticsId=b.LogisticsId
where a.Expressid=@ExpressId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Express>();
return model;
}
#endregion
#region 读取物流对象
public string GetExpressGroup(int ExpressId,string Country)
{
string tsql = @"
declare @Groups nvarchar(50)
set @Groups=''
select top 1 @Groups=Groups from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
inner join JC_Country c on b.Country=c.EnglishName
where a.ExpressID=@ExpressId and c.Code=@Country
select @Groups
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
string group =Convert.ToString(db.ExecuteScalar(cmd));
return group;
}
#endregion
#region 读取物流对象
public JC_PostFeeDetail GetExpressGroupNew(int ExpressId, string Country)
{
JC_PostFeeDetail model = null;
string tsql = @"
select top 1 b.Groups,b.Groups2 from JC_PostFee a
inner join JC_PostFeeDetail b on a.Id=b.FeeId
inner join JC_Country c on b.Country=c.EnglishName
where a.ExpressID=@ExpressId and c.Code=@Country
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressId", DbType.Int32, ExpressId);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_PostFeeDetail>();
return model;
}
#endregion
#region 保存日志
public static int SaveLog(JC_Log Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_Log] set [InUserId]=@InUserId,[InDate]=@InDate,[LogType]=@LogType,[LogContext]=@LogContext,[InnerId]=@InnerId where Id=@Id
end
else
begin
INSERT INTO [JC_Log]([InUserId],[InDate],[LogType],[LogContext],[InnerId])values(@InUserId,@InDate,@LogType,@LogContext,@InnerId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@LogType", DbType.String, Model.LogType);
db.AddInParameter(cmd, "@LogContext", DbType.String, Model.LogContext);
db.AddInParameter(cmd, "@InnerId", DbType.Int32, Model.InnerId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 新增机构的时候初始化数据
public void SaveStartData(int CompanyId)
{
string tsql = @"
INSERT INTO [CK_StoreHouse]([StoreName],[Remark],[CompanyId],[StoreType],[LevelGrade],[IsUse],[IsDefault])
values('仓库','',@CompanyId,1,1,1,1) --仓库
--INSERT INTO [JC_Country]([Code],[Name],[CompanyId])
--select [Code] ,[Name],[CompanyId]=@CompanyId from [JC_Country] where CompanyId=0 --国家
INSERT INTO [JC_Money]([MCode],[MName],[MLabel],[MRate],[CompanyId])
select [MCode],[MName],[MLabel],[MRate],[CompanyId]=@CompanyId from [JC_Money] where CompanyId=0 --钱币
insert JC_ExpressPost(CompanyId,Name,PostType,IsUse,ExpressID,DefaultAddr,IsSys)
select CompanyId=@CompanyId,Name,PostType,1,ExpressID,0,1
from JC_Express
--insert JC_ExpressPostDetail(ExpressPostID,ExpressID,IsUse)
--select b.ExpressPostID,a.ExpressID,1
--from JC_Express a
--inner join JC_ExpressPost b on a.ExpressID=b.ExpressID
--where b.CompanyId=@CompanyId --物流
INSERT INTO [JC_BaseCodeDetail]([KeyName],[Name],[IsUse],[Remark],[SortNo],[CompanyId])
select a.[KeyName],a.[Name],a.[IsUse],a.[Remark],a.[SortNo],[CompanyId]=@CompanyId from JC_BaseCodeDetail a
inner join JC_BaseCode b on a.KeyName=b.KeyName
where b.IsSys=0 and a.CompanyId=0
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存货币
public int SaveMoney(JC_Money Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_Money] set [MCode]=@MCode,[MName]=@MName,[MLabel]=@MLabel,[MRate]=@MRate,[CompanyId]=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [JC_Money]([MCode],[MName],[MLabel],[MRate],[CompanyId])values(@MCode,@MName,@MLabel,@MRate,@CompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@MCode", DbType.String, Model.MCode);
db.AddInParameter(cmd, "@MName", DbType.String, Model.MName);
db.AddInParameter(cmd, "@MLabel", DbType.String, Model.MLabel);
db.AddInParameter(cmd, "@MRate", DbType.Decimal, Model.MRate);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存货币
public int SaveMoneyForTM(JC_Money Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_Money] set [MCode]=@MCode,[MName]=@MName,[MLabel]=@MLabel,[MRate]=@MRate,[CompanyId]=@CompanyId,PlatId=@PlatId where Id=@Id
end
else
begin
INSERT INTO [JC_Money]([MCode],[MName],[MLabel],[MRate],[CompanyId],PlatId)values(@MCode,@MName,@MLabel,@MRate,@CompanyId,@PlatId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@MCode", DbType.String, Model.MCode);
db.AddInParameter(cmd, "@MName", DbType.String, Model.MName);
db.AddInParameter(cmd, "@MLabel", DbType.String, Model.MLabel);
db.AddInParameter(cmd, "@MRate", DbType.Decimal, Model.MRate);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@PlatId", DbType.Int32, Model.PlatId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 读取货币
public List<JC_Money> GetMoneyList(int CompanyID)
{
string tsql = @"
if @CompanyID=1
begin
select * from JC_Money
where CompanyId=@CompanyID and PlatId=1
end
begin
select * from JC_Money
where CompanyId=@CompanyID
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Money>();
}
#endregion
#region 读取货币
public List<JC_Money> GetMoneyList2()
{
string tsql = @"
select * from JC_Money
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Money>();
}
#endregion
#region 读取货币
public List<JC_Money> GetMoneyListForTM(int CompanyID,int PlatId)
{
string tsql = @"
select * from JC_Money
where CompanyId=@CompanyID and PlatId=@PlatId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@PlatId", DbType.Int32, PlatId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Money>();
}
#endregion
#region 返回Model
public JC_Money GetMoneyModel(int CompanyId, string MCode)
{
JC_Money model = null;
string tsql = "select top 1 * from JC_Money where CompanyId=@CompanyId and MCode=@MCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@MCode", DbType.String, MCode);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Money>();
return model;
}
#endregion
#region 删除货币
public void DeleteMoney(int Id)
{
string tsql = @"
delete from JC_Money where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存通知
public int SaveNotice(TZ_Notice Model)
{
string tsql = @"
if @Id>0
begin
Update [TZ_Notice] set NoticeType=@NoticeType,[Title]=@Title,[SendName]=@SendName,[InUserId]=@InUserId,[InDate]=@InDate,[EndDate]=@EndDate,[StartDate]=@StartDate,[NoticeContent]=@NoticeContent,[SimpContent]=@SimpContent,[CompayId]=@CompayId where Id=@Id
end
else
begin
INSERT INTO [TZ_Notice](NoticeType,[Title],[SendName],[InUserId],[InDate],[EndDate],[StartDate],[NoticeContent],[SimpContent],[CompayId])values(@NoticeType,@Title,@SendName,@InUserId,@InDate,@EndDate,@StartDate,@NoticeContent,@SimpContent,@CompayId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@Title", DbType.String, Model.Title);
db.AddInParameter(cmd, "@SendName", DbType.String, Model.SendName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, Model.EndDate);
db.AddInParameter(cmd, "@StartDate", DbType.DateTime, Model.StartDate);
db.AddInParameter(cmd, "@NoticeContent", DbType.String, Model.NoticeContent);
db.AddInParameter(cmd, "@SimpContent", DbType.String, Model.SimpContent);
db.AddInParameter(cmd, "@CompayId", DbType.Int32, Model.CompayId);
db.AddInParameter(cmd, "@NoticeType", DbType.Int32, Model.NoticeType);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存通知明细
public int SaveNoticeRece(TZ_NoticeRece Model)
{
string tsql = @"
if @Id>0
begin
Update [TZ_NoticeRece] set [NoticeId]=@NoticeId,[ReceType]=@ReceType,[ReceCompanyId]=@ReceCompanyId where Id=@Id
end
else
begin
INSERT INTO [TZ_NoticeRece]([NoticeId],[ReceType],[ReceCompanyId])values(@NoticeId,@ReceType,@ReceCompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@NoticeId", DbType.Int32, Model.NoticeId);
db.AddInParameter(cmd, "@ReceType", DbType.Int32, Model.ReceType);
db.AddInParameter(cmd, "@ReceCompanyId", DbType.Int32, Model.ReceCompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 分页查询通知
public List<TZ_Notice> GetListTZ_Notice(int CompanyId, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.Title,a.SendName,a.InUserId,a.InDate,a.EndDate,a.StartDate,a.NoticeContent,a.SimpContent,a.CompayId,a.NoticeType";
ser.Tables = @"(select * from TZ_Notice where Id in (select distinct NoticeId from TZ_NoticeRece where ReceCompanyId=0 or ReceCompanyId=@CompanyId)) a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<TZ_Notice> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<TZ_Notice>();
return ListModel;
}
#endregion
#region 返回通知Model
public TZ_Notice GetNoticeModel(int Id)
{
TZ_Notice model = null;
string tsql = "select * from TZ_Notice where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<TZ_Notice>();
return model;
}
#endregion
#region 读取通知接受人
public List<TZ_NoticeRece> GetNoticeReceList(int NoticeId)
{
string tsql = @"
select a.*,b.CompanyName from TZ_NoticeRece a
left join JC_Company b on a.ReceCompanyId=b.CompanyId
where NoticeId=@NoticeId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@NoticeId", DbType.Int32, NoticeId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<TZ_NoticeRece>();
}
#endregion
#region 删除通知
public void DeleteNotice(int Id)
{
string tsql = @"
delete from TZ_NoticeRece where NoticeId=@Id
delete from TZ_Notice where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除通知
public void DeleteNoticeRece(int Id)
{
string tsql = @"
delete from TZ_NoticeRece where NoticeId=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存公告通知
public int SaveGGNotice(GG_Notice Model)
{
string tsql = @"
if @Id>0
begin
Update [GG_Notice] set NoticeType=@NoticeType,[Title]=@Title,[SendName]=@SendName,[InUserId]=@InUserId,[InDate]=@InDate,[EndDate]=@EndDate,[StartDate]=@StartDate,[NoticeContent]=@NoticeContent,[SimpContent]=@SimpContent,[CompayId]=@CompayId where Id=@Id
end
else
begin
INSERT INTO [GG_Notice](NoticeType,[Title],[SendName],[InUserId],[InDate],[EndDate],[StartDate],[NoticeContent],[SimpContent],[CompayId])values(@NoticeType,@Title,@SendName,@InUserId,@InDate,@EndDate,@StartDate,@NoticeContent,@SimpContent,@CompayId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@Title", DbType.String, Model.Title);
db.AddInParameter(cmd, "@SendName", DbType.String, Model.SendName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, Model.EndDate);
db.AddInParameter(cmd, "@StartDate", DbType.DateTime, Model.StartDate);
db.AddInParameter(cmd, "@NoticeContent", DbType.String, Model.NoticeContent);
db.AddInParameter(cmd, "@SimpContent", DbType.String, Model.SimpContent);
db.AddInParameter(cmd, "@CompayId", DbType.Int32, Model.CompayId);
db.AddInParameter(cmd, "@NoticeType", DbType.Int32, Model.NoticeType);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存公告通知明细
public int SaveGGNoticeRece(GG_NoticeRece Model)
{
string tsql = @"
if @Id>0
begin
Update [GG_NoticeRece] set [NoticeId]=@NoticeId,[ReceType]=@ReceType,[ReceCompanyId]=@ReceCompanyId where Id=@Id
end
else
begin
INSERT INTO [GG_NoticeRece]([NoticeId],[ReceType],[ReceCompanyId])values(@NoticeId,@ReceType,@ReceCompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@NoticeId", DbType.Int32, Model.NoticeId);
db.AddInParameter(cmd, "@ReceType", DbType.Int32, Model.ReceType);
db.AddInParameter(cmd, "@ReceCompanyId", DbType.Int32, Model.ReceCompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 分页查询公告通知
public List<GG_Notice> GetListGG_Notice(int CompanyId, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.Title,a.SendName,a.InUserId,a.InDate,a.EndDate,a.StartDate,a.NoticeContent,a.SimpContent,a.CompayId,a.NoticeType";
ser.Tables = @"(select * from GG_Notice where Id in (select distinct NoticeId from GG_NoticeRece where ReceCompanyId=0 or ReceCompanyId=@CompanyId)) a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<GG_Notice> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<GG_Notice>();
return ListModel;
}
#endregion
#region 返回公告通知Model
public GG_Notice GetGGNoticeModel(int Id)
{
GG_Notice model = null;
string tsql = "select * from GG_Notice where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<GG_Notice>();
return model;
}
#endregion
#region 读取公告通知接受人
public List<GG_NoticeRece> GetGGNoticeReceList(int NoticeId)
{
string tsql = @"
select a.*,b.CompanyName from GG_NoticeRece a
left join JC_Company b on a.ReceCompanyId=b.CompanyId
where NoticeId=@NoticeId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@NoticeId", DbType.Int32, NoticeId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<GG_NoticeRece>();
}
#endregion
#region 删除公告通知
public void DeleteGGNotice(int Id)
{
string tsql = @"
delete from GG_NoticeRece where NoticeId=@Id
delete from GG_Notice where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除公告通知
public void DeleteGGNoticeRece(int Id)
{
string tsql = @"
delete from GG_NoticeRece where NoticeId=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回邮编规则
public List<JC_PostCodeRule> GetPostCodeRule(string Country)
{
string tsql = "select * from JC_PostCodeRule where Country=@Country";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PostCodeRule>();
}
#endregion
#region 返回顺丰邮编规则
public List<SF_USZIPCode> GetPostCodeRuleSF()
{
string tsql = "select * from SF_USZIPCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<SF_USZIPCode>();
}
#endregion
#region 返回邮编规则
public JC_PostCodeRule GetPostCodeRule2(string Country)
{
JC_PostCodeRule md = null;
string tsql = "select top 1 * from JC_PostCodeRule where Country=@Country and Id>50";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb != null && tb.Rows.Count>0)
md= tb.Rows[0].ToModel<JC_PostCodeRule>();
return md;
}
#endregion
#region 验证是否设置过店铺
public int IsShopAdd(int CompanyID)
{
string tsql = "select count(0) from JC_Shop where CompanyID=@CompanyID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证能否发送该物流
public int IsExpress(int CompanyID, int PostId, string Country, string CountryCode)
{
string tsql = @"
declare @bk int
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and Country=@Country
if @bk=0
begin
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and CountryCode=@CountryCode
end
if @bk=0
begin
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and CountryCode='0'
end
select bk=@bk
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
db.AddInParameter(cmd, "@Country", DbType.String, Country);
db.AddInParameter(cmd, "@CountryCode", DbType.String, CountryCode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证能否发送该物流
public int IsExpress2(int CompanyID, int PostId, string CountryCode)
{
string tsql = @"
declare @bk int,@Country nvarchar(150)
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and Country=@Country
if @bk=0
begin
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and CountryCode=@CountryCode
end
if @bk=0
begin
select @bk=count(0) from JC_ExpressCountry where ExpressId=@PostId and CountryCode='0'
end
select bk=@bk
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
db.AddInParameter(cmd, "@CountryCode", DbType.String, CountryCode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存发货地址
public int SavePostAddress(JC_PostAddress Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_PostAddress] set [CompanyId]=@CompanyId,[SendName]=@SendName,[SendEnglishName]=@SendEnglishName,[Address]=@Address,[EnglishAddress]=@EnglishAddress,[Phone]=@Phone,[PostCode]=@PostCode,[province]=@province,[city]=@city,[county]=@county,[street]=@street,[provincecode]=@provincecode,[citycode]=@citycode,[countycode]=@countycode,[streetcode]=@streetcode where Id=@Id
end
else
begin
INSERT INTO [JC_PostAddress]([CompanyId],[SendName],[SendEnglishName],[Address],[EnglishAddress],[Phone],[PostCode],[province],[city],[county],[street],[provincecode],[citycode],[countycode],[streetcode])values(@CompanyId,@SendName,@SendEnglishName,@Address,@EnglishAddress,@Phone,@PostCode,@province,@city,@county,@street,@provincecode,@citycode,@countycode,@streetcode)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@SendName", DbType.String, Model.SendName);
db.AddInParameter(cmd, "@SendEnglishName", DbType.String, Model.SendEnglishName);
db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
db.AddInParameter(cmd, "@EnglishAddress", DbType.String, Model.EnglishAddress);
db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
db.AddInParameter(cmd, "@province", DbType.String, Model.province);
db.AddInParameter(cmd, "@city", DbType.String, Model.city);
db.AddInParameter(cmd, "@county", DbType.String, Model.county);
db.AddInParameter(cmd, "@street", DbType.String, Model.street);
db.AddInParameter(cmd, "@provincecode", DbType.String, Model.provincecode);
db.AddInParameter(cmd, "@citycode", DbType.String, Model.citycode);
db.AddInParameter(cmd, "@countycode", DbType.String, Model.countycode);
db.AddInParameter(cmd, "@streetcode", DbType.String, Model.streetcode);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除发货地址
public void DeletePostAddress(int Id)
{
string tsql = @"
delete from JC_PostAddress where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 读取发货地址
public List<JC_PostAddress> GetPostAddressList(int CompanyId)
{
string tsql = @"
select * from JC_PostAddress
where CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PostAddress>();
}
#endregion
#region 返回Model
public JC_PostAddress GetAddressModel(int Id)
{
JC_PostAddress model = null;
string tsql = @"
select a.*,b.CompanyName,b.Email,b.CompanyEnglishName,b.Shen,b.Shi,b.ChargeName from JC_PostAddress a
inner join JC_Company b on a.companyid=b.companyid
where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_PostAddress>();
return model;
}
#endregion
public JC_PostAddress GetAddressModel2(int PostId)
{
JC_PostAddress address = null;
string query = "select a.*,CompanyName=SendEnglishName,Email='',CompanyEnglishName=SendEnglishName,Shen=provincecode,Shi=citycode,ChargeName=SendEnglishName from JC_PostAddress a inner join JC_ExpressPost b on a.Id=b.DefaultAddr where b.ExpressID=@PostId ";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "@PostId", DbType.Int32, PostId);
DataTable table = database.ExecuteDataTable(sqlStringCommand);
if (table.Rows.Count > 0)
{
address = table.Rows[0].ToModel<JC_PostAddress>();
}
return address;
}
public JC_PostAddress GetAddressModel222(int id)
{
JC_PostAddress address = null;
string query = "select a.*,CompanyName=SendEnglishName,Email='',CompanyEnglishName=SendEnglishName,Shen=provincecode,Shi=citycode,ChargeName=SendEnglishName from JC_PostAddress a where a.Id=@id ";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "@id", DbType.Int32, id);
DataTable table = database.ExecuteDataTable(sqlStringCommand);
if (table.Rows.Count > 0)
{
address = table.Rows[0].ToModel<JC_PostAddress>();
}
return address;
}
#region 保存物流
public int SaveExpress(JC_Express Model)
{
string tsql = @"
if @ExpressID>0
begin
Update [JC_Express] set [Name]=@Name,[EName]=@EName,[PrintTemplateName]=@PrintTemplateName,[PrintTemplateName2]=@PrintTemplateName2,[Remark]=@Remark,[PostType]=@PostType where ExpressID=@ExpressID
end
else
begin
INSERT INTO [JC_Express]([Name],[EName],[PrintTemplateName],[PrintTemplateName2],[Remark],[PostType])values(@Name,@EName,@PrintTemplateName,@PrintTemplateName2,@Remark,@PostType)
set @ExpressID=SCOPE_IDENTITY()
end
select @ExpressID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, Model.ExpressID);
db.AddInParameter(cmd, "@Name", DbType.String, Model.Name);
db.AddInParameter(cmd, "@EName", DbType.String, Model.EName);
db.AddInParameter(cmd, "@PrintTemplateName", DbType.String, Model.PrintTemplateName);
db.AddInParameter(cmd, "@PrintTemplateName2", DbType.String, Model.PrintTemplateName2);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@PostType", DbType.Int32, Model.PostType);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 返回Model
public WL_TrackList GetTrackListModel(string TrackCode)
{
WL_TrackList model = null;
string tsql = @"select top 1 a.*,b.Lan,b.Days from WL_TrackList a
inner join JC_Country b on a.Country=b.EnglishName
where TrackCode=@TrackCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@TrackCode", DbType.String, TrackCode);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<WL_TrackList>();
return model;
}
#endregion
#region 读取跟踪码最后id
public int GetMaxTrackId()
{
string tsql = @"
delete from [WL_TrackList] where DATEDIFF(month,InDate,GETDATE())>5
select TrackId=isnull(max(TrackId),0) from WL_TrackList
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存物流跟踪码
public void SaveTrackList(WL_TrackList Model)
{
string tsql = @"
if(select count(0) from WL_TrackList where TrackCode=@TrackCode)=0
begin
INSERT INTO [WL_TrackList]([Country],[TrackCode],[InDate],[City],[Num],TrackId)values(@Country,@TrackCode,@InDate,@City,@Num,@TrackId)
set @Id=SCOPE_IDENTITY()
end";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@City", DbType.String, Model.City);
db.AddInParameter(cmd, "@Num", DbType.Int32, Model.Num);
db.AddInParameter(cmd, "@TrackId", DbType.Int32, Model.TrackId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 验证friendcode
public int IsFriendCode(string Code)
{
string tsql = @"if(select count(0) from JC_FriendCode where FriendCode=@Code and UseState=0)=0
begin
select 0
end
else
begin
select 1
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Code", DbType.String, Code);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 修改friendcode使用状态
public void UpdateFriendCode(string Code,int UserId)
{
string tsql = @"
update JC_FriendCode set UseState=1,UseTime=getdate(),UseUserId=@UserId where FriendCode=@Code
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Code", DbType.String, Code);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 分页查询
public List<JC_FriendCode> GetListJC_FriendCode(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.FriendCode,a.CreatTime,UseStateName=case when a.UseState=1 then '已使用' else '未使用' end,a.UseState,a.UseTime,a.UseUserId,c.CompanyName,a.Remark";
ser.Tables = @"JC_FriendCode a left join JC_UserInfo b on a.UseUserId=b.UserId left join JC_Company c on b.CompanyId=c.CompanyId";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_FriendCode> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_FriendCode>();
return ListModel;
}
#endregion
#region 保存邀请码
public int SaveFriendCode(JC_FriendCode Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_FriendCode] set [FriendCode]=@FriendCode,[CreatTime]=@CreatTime,[UseState]=@UseState,[UseTime]=@UseTime,[UseUserId]=@UseUserId where Id=@Id
end
else
begin
if(select count(0) from JC_FriendCode where FriendCode=@FriendCode)=0
begin
INSERT INTO [JC_FriendCode]([FriendCode],[CreatTime],[UseState],[UseTime],[UseUserId])values(@FriendCode,@CreatTime,@UseState,@UseTime,@UseUserId)
set @Id=SCOPE_IDENTITY()
end
set @Id=0
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@FriendCode", DbType.String, Model.FriendCode);
db.AddInParameter(cmd, "@CreatTime", DbType.DateTime, Model.CreatTime);
db.AddInParameter(cmd, "@UseState", DbType.Int32, Model.UseState);
db.AddInParameter(cmd, "@UseTime", DbType.DateTime, Model.UseTime);
db.AddInParameter(cmd, "@UseUserId", DbType.Int32, Model.UseUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除
public void DeleteFriendCode(int Id)
{
string tsql = @"
delete from JC_FriendCode where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 读取sku属性
public List<JC_Property> GetPropertyList(int CompanyId)
{
string tsql = @"
if(select count(0) from JC_Property where CompanyId=@CompanyId)=0
begin
select * from JC_Property where CompanyId=0
end
else
begin
select * from JC_Property where CompanyId=@CompanyId
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Property>();
}
#endregion
#region 读取sku属性
public List<JC_PropertyDetail> GetPropertyDetailList(int PropertyId)
{
string tsql = @"
select * from JC_PropertyDetail where PropertyId=@PropertyId ORDER BY SortNo
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@PropertyId", DbType.Int32, PropertyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_PropertyDetail>();
}
#endregion
#region 删除sku属性
public void DeleteProperty(int CompanyId,int Id)
{
string tsql = @"
delete from JC_PropertyDetail where PropertyId in (select Id from JC_Property where CompanyId=@CompanyId and Id=@Id)
delete from JC_Property where CompanyId=@CompanyId and Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除sku属性
public void DeletePropertyDetail(int Id)
{
string tsql = @"
delete from JC_PropertyDetail where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存sku属性
public int SaveProperty(JC_Property Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_Property] set [PropertyName]=@PropertyName,[CompanyId]=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [JC_Property]([PropertyName],[CompanyId])values(@PropertyName,@CompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@PropertyName", DbType.String, Model.PropertyName);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
if (Model.PropertyDetailList != null)
{
foreach (var md in Model.PropertyDetailList)
{
md.PropertyId = a;
md.CompanyId = Model.CompanyId;
SavePropertyDetail(md);
}
}
return a;
}
#endregion
#region 保存sku属性
public int SavePropertyDetail(JC_PropertyDetail Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_PropertyDetail] set [PropertyId]=@PropertyId,[Name]=@Name,[EnglishName]=@EnglishName where Id=@Id
end
else
begin
INSERT INTO [JC_PropertyDetail]([PropertyId],[Name],[EnglishName])values(@PropertyId,@Name,@EnglishName)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@PropertyId", DbType.Int32, Model.PropertyId);
db.AddInParameter(cmd, "@Name", DbType.String, Model.Name);
db.AddInParameter(cmd, "@EnglishName", DbType.String, Model.EnglishName);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存供应商
public int SaveSupplier(JC_Supplier Model)
{
string tsql = @"
if @SupplierId>0
begin
Update [JC_Supplier] set [SupplierName]=@SupplierName,[Address]=@Address,[Person]=@Person,[Mobile]=@Mobile,[Phone]=@Phone,[BankName]=@BankName,[BankCode]=@BankCode,[Fax]=@Fax,[QQ]=@QQ,[Email]=@Email,[Credit]=@Credit,[Cooperation]=@Cooperation,[Grade]=@Grade,[ReDays]=@ReDays,[LinkUrl]=@LinkUrl,[Comment]=@Comment,[Remark]=@Remark,[CompanyId]=@CompanyId,[InDate]=@InDate,[InUserId]=@InUserId where SupplierId=@SupplierId
end
else
begin
INSERT INTO [JC_Supplier]([SupplierName],[Address],[Person],[Mobile],[Phone],[BankName],[BankCode],[Fax],[QQ],[Email],[Credit],[Cooperation],[Grade],[ReDays],[LinkUrl],[Comment],[Remark],[CompanyId],[InDate],[InUserId])values(@SupplierName,@Address,@Person,@Mobile,@Phone,@BankName,@BankCode,@Fax,@QQ,@Email,@Credit,@Cooperation,@Grade,@ReDays,@LinkUrl,@Comment,@Remark,@CompanyId,@InDate,@InUserId)
set @SupplierId=SCOPE_IDENTITY()
end
select @SupplierId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
db.AddInParameter(cmd, "@Person", DbType.String, Model.Person);
db.AddInParameter(cmd, "@Mobile", DbType.String, Model.Mobile);
db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
db.AddInParameter(cmd, "@BankName", DbType.String, Model.BankName);
db.AddInParameter(cmd, "@BankCode", DbType.String, Model.BankCode);
db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
db.AddInParameter(cmd, "@QQ", DbType.String, Model.QQ);
db.AddInParameter(cmd, "@Email", DbType.String, Model.Email);
db.AddInParameter(cmd, "@Credit", DbType.String, Model.Credit);
db.AddInParameter(cmd, "@Cooperation", DbType.String, Model.Cooperation);
db.AddInParameter(cmd, "@Grade", DbType.Int32, Model.Grade);
db.AddInParameter(cmd, "@ReDays", DbType.Int32, Model.ReDays);
db.AddInParameter(cmd, "@LinkUrl", DbType.String, Model.LinkUrl);
db.AddInParameter(cmd, "@Comment", DbType.String, Model.Comment);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public int SaveSupplierForTM(JC_Supplier Model)
{
string tsql = @"
if @SupplierId>0
begin
declare @SupplierName1 nvarchar(500)
select @SupplierName1=SupplierName from JC_Supplier where SupplierId=@SupplierId
Update [JC_Supplier] set [SupplierName]=@SupplierName,[Address]=@Address,[Person]=@Person,[Mobile]=@Mobile,[Phone]=@Phone,[BankName]=@BankName,[BankCode]=@BankCode,[Fax]=@Fax,[QQ]=@QQ,[Email]=@Email,[Credit]=@Credit,[Cooperation]=@Cooperation,[Grade]=@Grade,[ReDays]=@ReDays,[LinkUrl]=@LinkUrl,[Comment]=@Comment,[Remark]=@Remark,[CompanyId]=@CompanyId,[InDate]=@InDate,[InUserId]=@InUserId,[MoneyType]=@MoneyType,[BuyUserId]=@BuyUserId,[BuyerName]=@BuyerName where SupplierId=@SupplierId
if @SupplierName1<>@SupplierName
begin
update HW_GoodsInfo set SupplierId=@SupplierName where SupplierId=@SupplierName1
end
end
else
begin
INSERT INTO [JC_Supplier]([SupplierName],[Address],[Person],[Mobile],[Phone],[BankName],[BankCode],[Fax],[QQ],[Email],[Credit],[Cooperation],[Grade],[ReDays],[LinkUrl],[Comment],[Remark],[CompanyId],[InDate],[InUserId],[MoneyType],[BuyUserId],[BuyerName])values(@SupplierName,@Address,@Person,@Mobile,@Phone,@BankName,@BankCode,@Fax,@QQ,@Email,@Credit,@Cooperation,@Grade,@ReDays,@LinkUrl,@Comment,@Remark,@CompanyId,@InDate,@InUserId,@MoneyType,@BuyUserId,@BuyerName)
set @SupplierId=SCOPE_IDENTITY()
end
select @SupplierId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
db.AddInParameter(cmd, "@Person", DbType.String, Model.Person);
db.AddInParameter(cmd, "@Mobile", DbType.String, Model.Mobile);
db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
db.AddInParameter(cmd, "@BankName", DbType.String, Model.BankName);
db.AddInParameter(cmd, "@BankCode", DbType.String, Model.BankCode);
db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
db.AddInParameter(cmd, "@QQ", DbType.String, Model.QQ);
db.AddInParameter(cmd, "@Email", DbType.String, Model.Email);
db.AddInParameter(cmd, "@Credit", DbType.String, Model.Credit);
db.AddInParameter(cmd, "@Cooperation", DbType.String, Model.Cooperation);
db.AddInParameter(cmd, "@Grade", DbType.Int32, Model.Grade);
db.AddInParameter(cmd, "@ReDays", DbType.Int32, Model.ReDays);
db.AddInParameter(cmd, "@LinkUrl", DbType.String, Model.LinkUrl);
db.AddInParameter(cmd, "@Comment", DbType.String, Model.Comment);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@MoneyType", DbType.String, Model.MoneyType);
db.AddInParameter(cmd, "@BuyUserId", DbType.Int32, Model.BuyUserId);
db.AddInParameter(cmd, "@BuyerName", DbType.String, Model.BuyerName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
public int SaveSupplierForTM2(JC_Supplier Model)
{
string tsql = @"
if @SupplierId>0
begin
declare @SupplierName1 nvarchar(500)
select @SupplierName1=SupplierName from JC_Supplier where SupplierId=@SupplierId
Update [JC_Supplier] set [SupplierName]=@SupplierName,SupplierCode=@SupplierCode,[Address]=@Address,[Person]=@Person,[Mobile]=@Mobile,[Phone]=@Phone,[BankName]=@BankName,[BankCode]=@BankCode,[Fax]=@Fax,[QQ]=@QQ,[Email]=@Email,[Credit]=@Credit,[Cooperation]=@Cooperation,[Grade]=@Grade,[ReDays]=@ReDays,[LinkUrl]=@LinkUrl,[Comment]=@Comment,[Remark]=@Remark,[CompanyId]=@CompanyId,[InDate]=@InDate,[InUserId]=@InUserId,[MoneyType]=@MoneyType,[BuyUserId]=@BuyUserId,[BuyerName]=@BuyerName where SupplierId=@SupplierId
if @SupplierName1<>@SupplierName
begin
update HW_GoodsInfo set SupplierId=@SupplierName where SupplierId=@SupplierName1
end
end
else
begin
INSERT INTO [JC_Supplier]([SupplierName],SupplierCode,[Address],[Person],[Mobile],[Phone],[BankName],[BankCode],[Fax],[QQ],[Email],[Credit],[Cooperation],[Grade],[ReDays],[LinkUrl],[Comment],[Remark],[CompanyId],[InDate],[InUserId],[MoneyType],[BuyUserId],[BuyerName])values(@SupplierName,@SupplierCode,@Address,@Person,@Mobile,@Phone,@BankName,@BankCode,@Fax,@QQ,@Email,@Credit,@Cooperation,@Grade,@ReDays,@LinkUrl,@Comment,@Remark,@CompanyId,@InDate,@InUserId,@MoneyType,@BuyUserId,@BuyerName)
set @SupplierId=SCOPE_IDENTITY()
end
select @SupplierId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
db.AddInParameter(cmd, "@SupplierCode", DbType.String, Model.SupplierCode);
db.AddInParameter(cmd, "@Address", DbType.String, Model.Address);
db.AddInParameter(cmd, "@Person", DbType.String, Model.Person);
db.AddInParameter(cmd, "@Mobile", DbType.String, Model.Mobile);
db.AddInParameter(cmd, "@Phone", DbType.String, Model.Phone);
db.AddInParameter(cmd, "@BankName", DbType.String, Model.BankName);
db.AddInParameter(cmd, "@BankCode", DbType.String, Model.BankCode);
db.AddInParameter(cmd, "@Fax", DbType.String, Model.Fax);
db.AddInParameter(cmd, "@QQ", DbType.String, Model.QQ);
db.AddInParameter(cmd, "@Email", DbType.String, Model.Email);
db.AddInParameter(cmd, "@Credit", DbType.String, Model.Credit);
db.AddInParameter(cmd, "@Cooperation", DbType.String, Model.Cooperation);
db.AddInParameter(cmd, "@Grade", DbType.Int32, Model.Grade);
db.AddInParameter(cmd, "@ReDays", DbType.Int32, Model.ReDays);
db.AddInParameter(cmd, "@LinkUrl", DbType.String, Model.LinkUrl);
db.AddInParameter(cmd, "@Comment", DbType.String, Model.Comment);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@MoneyType", DbType.String, Model.MoneyType);
db.AddInParameter(cmd, "@BuyUserId", DbType.Int32, Model.BuyUserId);
db.AddInParameter(cmd, "@BuyerName", DbType.String, Model.BuyerName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存
public int UpdateSupplierForTM(JC_Supplier Model)
{
string tsql = @"
if @SupplierId>0
begin
Update [JC_Supplier] set [MoneyType]=@MoneyType,[BuyUserId]=@BuyUserId,[BuyerName]=@BuyerName where SupplierId=@SupplierId
end
select @SupplierId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@MoneyType", DbType.String, Model.MoneyType);
db.AddInParameter(cmd, "@BuyUserId", DbType.Int32, Model.BuyUserId);
db.AddInParameter(cmd, "@BuyerName", DbType.String, Model.BuyerName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 修改邀请码备注
public void SetFriendCode(string Remark,int Id)
{
string tsql = @"
update JC_FriendCode set Remark=@Remark where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.AddInParameter(cmd, "@Remark", DbType.String, Remark);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除供应商
public void DeleteSupplier(int SupplierId)
{
string tsql = @"
update HW_GoodsInfo set Supplier=null,SupplierId=null where Supplier=@SupplierId
delete from JC_Supplier where SupplierId=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除供应商
public void DeleteSupplierForTM(int SupplierId)
{
string tsql = @"
delete from JC_Supplier where SupplierId=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除供应商
public void DeleteSupplierFromGoods(int GoodsId)
{
string tsql = @"
delete from JC_SupplierGoods where GoodsId=@GoodsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回供应商Model
public JC_Supplier GetSupplierModel(int SupplierId)
{
JC_Supplier model = null;
string tsql = "select * from JC_Supplier where SupplierId=@SupplierId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Supplier>();
return model;
}
#endregion
#region 返回供应商Model
public JC_Supplier GetSupplierModelForTM(int GoodsId)
{
JC_Supplier model = null;
string tsql = @"select a.* from JC_Supplier a
inner join HW_GoodsInfo b on a.SupplierId=b.Supplier
where b.GoodsId=@GoodsId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Supplier>();
return model;
}
#endregion
#region 分页查询供应商
public List<JC_Supplier> GetListJC_Supplier(int CompanyId,string GoodsCode,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string sql1 = "";
if (GoodsCode != "")
sql1 = @"select distinct a.SupplierId from JC_SupplierGoods a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
where b.CompanyId=@CompanyId and (b.GoodsName like '%" + GoodsCode + "%' or b.GoodsCode like '%" + GoodsCode + "%')";
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"SupplierId=cast(a.SupplierId as int),a.SupplierName,a.Address,a.Person,a.Mobile,a.Phone,a.BankName,a.BankCode,a.Fax,a.QQ,a.Email,a.Credit,a.Cooperation,a.Grade,a.ReDays,a.LinkUrl,a.Comment,a.Remark,a.CompanyId";
if (sql1 != "")
ser.Tables = @"(select * from JC_Supplier where SupplierId in (" + sql1 + "))a";
else
ser.Tables = @"JC_Supplier a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "SupplierId";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Supplier> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Supplier>();
return ListModel;
}
#endregion
#region 分页查询供应商
public List<JC_Supplier> GetListJC_SupplierForTM(int CompanyId,string GoodsCode, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string sql1 = "";
if (GoodsCode != "")
sql1 = @"select distinct Supplier from HW_GoodsInfo
where CompanyId=@CompanyId and (GoodsName like '%" + GoodsCode + "%' or GoodsCode like '%" + GoodsCode + "%' or GoodsOldCode like '%" + GoodsCode + "%')";
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"SupplierId=cast(a.SupplierId as int),a.SupplierName,a.Address,a.Person,a.Mobile,a.Phone,a.BankName,a.BankCode,a.Fax,a.QQ,a.Email,a.Credit,a.Cooperation,a.Grade,a.ReDays,a.LinkUrl,a.Comment,a.Remark,a.CompanyId,a.MoneyType,a.BuyUserId,a.BuyerName";
if (sql1 != "")
ser.Tables = @"(select * from JC_Supplier where SupplierId in (" + sql1 + "))a";
else
ser.Tables = @"JC_Supplier a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "SupplierId";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<JC_Supplier> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Supplier>();
return ListModel;
}
#endregion
#region 分页查询供应商
public List<JC_Supplier> GetListJC_SupplierForTM2(int CompanyId, string GoodsCode, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string sql1 = "";
if (GoodsCode != "")
sql1 = @"select distinct Supplier from HW_GoodsInfo
where CompanyId=@CompanyId and (GoodsName like '%" + GoodsCode + "%' or GoodsCode like '%" + GoodsCode + "%' or GoodsOldCode like '%" + GoodsCode + "%')";
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"SupplierId=cast(a.SupplierId as int),a.SupplierCode,a.SupplierName,a.Address,a.Person,a.Mobile,a.Phone,a.BankName,a.BankCode,a.Fax,a.QQ,a.Email,a.Credit,a.Cooperation,a.Grade,a.ReDays,a.LinkUrl,a.Comment,a.Remark,a.CompanyId,a.MoneyType,a.BuyUserId,a.BuyerName";
if (sql1 != "")
ser.Tables = @"(select * from JC_Supplier where SupplierId in (" + sql1 + "))a";
else
ser.Tables = @"JC_Supplier a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "SupplierId";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<JC_Supplier> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Supplier>();
return ListModel;
}
#endregion
#region 分页查询供应商
public List<JC_Supplier> GetListJC_CGSupplier(int CompanyId,string GoodsCode,int State,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
string sql1 = "";
if (GoodsCode != "")
sql1 = @"select distinct a.SupplierId from JC_SupplierGoods a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
inner join HW_GoodsDetail c on b.GoodsId=c.GoodsId
where b.CompanyId=@CompanyId and (b.GoodsName like '%" + GoodsCode + "%' or b.GoodsCode like '%" + GoodsCode + "%' or c.SKU1 like '%" + GoodsCode + "%')";
if (State == 1 && sql1=="")
sql1 = @"select distinct a.SupplierId from JC_SupplierGoods a
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
inner join HW_GoodsDetail c on b.GoodsId=c.GoodsId
where b.CompanyId=@CompanyId and b.State=1 and (c.GoodsNum+c.GoodsInNum-c.GoodsPlanNum)<0";
else if (State == 1 && sql1!="")
sql1 = @"select distinct b.SupplierId from HW_GoodsInfo a
inner join JC_Supplier b on a.Supplier=b.SupplierId
where a.State=1 and a.CompanyId=@CompanyId and (a.GoodsNum+a.GoodsInNum-a.GoodsPlanNum)<0 and (b.GoodsName like '%" + GoodsCode + "%' or b.GoodsCode like '%" + GoodsCode + "%' or c.SKU1 like '%" + GoodsCode + "%')";
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"SupplierId=cast(a.SupplierId as int),a.SupplierName,a.Address,a.Person,a.Mobile,a.Phone,a.BankName,a.BankCode,a.Fax,a.QQ,a.Email,a.Credit,a.Cooperation,a.Grade,a.ReDays,a.LinkUrl,a.Comment,a.Remark,a.CompanyId,a.GoodsNum";
if (sql1 != "")
ser.Tables = @"(select a.*,GoodsNum=dbo.[GetSupplierGoodsNum](a.companyId,a.SupplierId) from JC_Supplier a inner join (" + sql1 + ")b on a.SupplierId=b.SupplierId)a";
else
ser.Tables = @"(select *,GoodsNum=dbo.[GetSupplierGoodsNum](companyId,SupplierId) from JC_Supplier)a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "SupplierId";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
List<JC_Supplier> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Supplier>();
return ListModel;
}
#endregion
#region 读取供应商
public List<JC_Supplier> GetSupplierList(int CompanyId)
{
string tsql = @"
select a.SupplierId,SupplierCode=isnull(a.SupplierCode,''),a.SupplierName,a.Address,a.Person,a.Mobile,a.Phone,a.BankName,a.BankCode,a.Fax,a.QQ,a.Email,a.Credit,a.Cooperation,a.Grade,a.ReDays,a.LinkUrl,a.Comment,a.Remark,a.CompanyId,a.InDate,a.InUserId,a.MoneyType,a.BuyUserId,a.BuyerName from JC_Supplier a where CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Supplier>();
}
#endregion
#region 读取供应商
public List<JC_Supplier> GetSupplierListFromName(int CompanyId,string Name,int GoodsId)
{
string tsql = @"
select a.*,Id=isnull(b.Id,0) from JC_Supplier a
left join JC_SupplierGoods b on a.SupplierId=b.SupplierId and b.GoodsId=@GoodsId
where CompanyId=@CompanyId and (@Name='' or SupplierName like '%'+@Name+'%' or Phone like '%'+@Name+'%' or Person like '%'+@Name+'%' or Mobile like '%'+@Name+'%')
order by Id desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@Name", DbType.String, Name);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Supplier>();
}
#endregion
#region 读取货物供应商
public List<JC_Supplier> GetSupplierListFromGoods(int CompanyId,int GoodsId)
{
string tsql = @"
select a.*,b.Id from JC_Supplier a
inner join JC_SupplierGoods b on a.SupplierId=b.SupplierId
where a.CompanyId=@CompanyId and b.GoodsId=@GoodsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_Supplier>();
}
#endregion
#region 读取供应商货物
public List<HW_Goods> GetSupplierGoods(int SupplierId,string GoodsName)
{
string tsql = @"
select a.GoodsId,b.GoodsName,b.GoodsCode from JC_SupplierGoods a
inner join HW_GoodsInfo b on a.goodsid=b.goodsid
where b.State=1 and a.supplierid=@SupplierId and (@GoodsName='' or b.GoodsName like '%+@GoodsName+%' or b.GoodsCode like '%+@GoodsName+%')
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.AddInParameter(cmd, "@GoodsName", DbType.String, GoodsName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<HW_Goods>();
}
#endregion
#region 读取供应商货物
public List<HW_Goods> GetSupplierGoodsForTM(int SupplierId, string GoodsName)
{
string tsql = @"
select GoodsId,GoodsName,GoodsCode from HW_GoodsInfo
where State=1 and supplier=@SupplierId and (@GoodsName='' or GoodsName like '%+@GoodsName+%' or GoodsCode like '%+@GoodsName+%')
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.AddInParameter(cmd, "@GoodsName", DbType.String, GoodsName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<HW_Goods>();
}
#endregion
#region 读取供应商货物
public List<JC_SupplierGoods> GetSupplierGoodsList(int SupplierId)
{
string tsql = @"
select GoodsCode,GoodsOldCode,GoodsName,FirstImgUrl from HW_GoodsInfo
where State=1 and supplier=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_SupplierGoods>();
}
#endregion
#region 读取供应商货物
public List<HW_Goods> GetSupplierGoodsAll(int CompanyId, string GoodsName)
{
string tsql = @"
select * from HW_GoodsInfo
where State=1 and CompanyId=@CompanyId and GoodsId not in (select distinct GoodsId from JC_SupplierGoods) and (@GoodsName='' or GoodsName like '%'+@GoodsName+'%' or GoodsCode like '%'+@GoodsName+'%')";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@GoodsName", DbType.String, GoodsName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<HW_Goods>();
}
#endregion
#region 读取供应商货物
public List<HW_Goods> GetSupplierGoodsAllForTM(int CompanyId, string GoodsName)
{
string tsql = @"
select * from HW_GoodsInfo
where State=1 and CompanyId=@CompanyId and Supplier is null and (@GoodsName='' or GoodsName like '%'+@GoodsName+'%' or GoodsCode like '%'+@GoodsName+'%' or GoodsOldCode like '%'+@GoodsName+'%')";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@GoodsName", DbType.String, GoodsName);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<HW_Goods>();
}
#endregion
#region 删除供应商货物
public void DeleteSupplierGoodsForTM(int SupplierId)
{
string tsql = @"
update HW_GoodsInfo set Supplier=null,SupplierId=null where Supplier=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除供应商货物
public void DeleteSupplierGoods(int SupplierId)
{
string tsql = @"
delete from JC_SupplierGoods where SupplierId=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存供应商货物
public int SaveSupplierGoods(JC_SupplierGoods Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_SupplierGoods] set [SupplierId]=@SupplierId,[GoodsId]=@GoodsId where Id=@Id
end
else
begin
INSERT INTO [JC_SupplierGoods]([SupplierId],[GoodsId])values(@SupplierId,@GoodsId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证供应商名称
public static int GetSupplierId(int CompanyId,string SupplierName)
{
string tsql = @"
declare @SupplierId int
set @SupplierId=0
select @SupplierId=SupplierId from JC_Supplier
where CompanyId=@CompanyId and SupplierName=@SupplierName
select SupplierId=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, SupplierName);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证供应商名称
public static int GetSupplierIdForTM(int CompanyId, string SupplierName, int IsAdd, int InUserId)
{
string tsql = @"
declare @SupplierId int
set @SupplierId=0
select @SupplierId=SupplierId from JC_Supplier
where CompanyId=@CompanyId and SupplierName=@SupplierName
if @IsAdd=1 and @SupplierId=0
begin
INSERT INTO [JC_Supplier]([SupplierName],[CompanyId],[InDate],[InUserId])
values(@SupplierName,@CompanyId,getdate(),@InUserId)
set @SupplierId=@@IDENTITY
end
select SupplierId=@SupplierId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, SupplierName);
db.AddInParameter(cmd, "@IsAdd", DbType.Int32, IsAdd);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存供应商货物
public void SaveSupplierGoodsForTM(JC_SupplierGoods Model)
{
string tsql = @"
Update [HW_GoodsInfo] set [Supplier]=@SupplierId,SupplierId=@SupplierName where GoodsId=@GoodsId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, Model.SupplierId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, Model.GoodsId);
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 导入测试数据
public void TestDataImport(int OldCompanyId, int CompanyId)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("CSDataImport");
db.AddInParameter(cmd, "@OldCompanyId", DbType.Int32, @OldCompanyId);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 判断管理员账号密码
public int IsManage(string UserName, string Password, int CompanyId)
{
string tsql = "select COUNT(0) from JC_UserInfo where State=1 and UserType='M' and CompanyId=@CompanyId and UserName=@UserName and Password=@Password ";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@Password", DbType.String, Password);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除测试数据
public void DeleteTestData(int CompanyId,int IsShop,int IsSort,int IsGoods,int IsChase,int IsOrder)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("CSDataDelete");
db.AddInParameter(cmd, "@IsShop", DbType.Int32, IsShop);
db.AddInParameter(cmd, "@IsSort", DbType.Int32, IsSort);
db.AddInParameter(cmd, "@IsGoods", DbType.Int32, IsGoods);
db.AddInParameter(cmd, "@IsChase", DbType.Int32, IsChase);
db.AddInParameter(cmd, "@IsOrder", DbType.Int32, IsOrder);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回
public JC_UserInfo GetUserPwd(string Email)
{
JC_UserInfo model = null;
string tsql = @"select top 1 a.* from JC_UserInfo a
inner join JC_Company b on a.CompanyId=b.CompanyId
where a.State=1 and b.State=1 and a.UserType='M' and b.Email=@Email order by UserId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Email", DbType.String, Email);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_UserInfo>();
return model;
}
#endregion
#region 返回工作
public List<JC_UserInfoNew> GetUserWork(int CompanyId, int DeptId)
{
string tsql = @"select a.UserId,Duty=isnull(a.Duty,''),a.DeptId,a.Name,WorkDesc=isnull(a.WorkDesc,''),b.DeptName,NowState=case when (select COUNT(0) from OA_Leave where SDate<=GETDATE() and EDate>=GETDATE() and InUserId=a.UserId)>0 then '请假' else '在岗' end,Mobile1=isnull(a.Mobile1,''),QQ=isnull(a.QQ,''),Email=isnull(a.Email,'') from JC_UserInfo a
inner join JC_DepartMent b on a.DeptId=b.DeptId
where a.state=1 and a.CompanyId=@CompanyId and (@DeptId=0 or a.DeptId=@DeptId) order by a.DeptId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@DeptId", DbType.Int32, DeptId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<JC_UserInfoNew>();
}
#endregion
#region 保存留言
public int SaveLY_Content(LY_Content Model)
{
string tsql = @"
if @Id>0
begin
Update [LY_Content] set [BackContent]=@BackContent,[OrderId]=@OrderId,[InDate]=@InDate,[InUserId]=@InUserId where Id=@Id
end
else
begin
INSERT INTO [LY_Content]([BackContent],[OrderId],[InDate],[InUserId])values(@BackContent,@OrderId,@InDate,@InUserId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@BackContent", DbType.String, Model.BackContent);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存留言模板
public int SaveLY_Templete(LY_Templete Model)
{
string tsql = @"
if @Id>0
begin
Update [LY_Templete] set [TempName]=@TempName,[TempContent]=@TempContent,[CompanyId]=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [LY_Templete]([TempName],[TempContent],[CompanyId])values(@TempName,@TempContent,@CompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@TempName", DbType.String, Model.TempName);
db.AddInParameter(cmd, "@TempContent", DbType.String, Model.TempContent);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 读取留言模板
public List<LY_Templete> GetLY_TempleteList(int CompanyId)
{
string tsql = @"
if(select count(0) from LY_Templete where CompanyId=@CompanyId)=0
begin
INSERT INTO [LY_Templete]([TempName],[TempContent],[CompanyId])
select [TempName],[TempContent],CompanyId=@CompanyId from LY_Templete where CompanyId=0
end
select * from LY_Templete where CompanyId=@CompanyId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<LY_Templete>();
}
#endregion
#region 删除留言模板
public void DeleteTemplete(int Id)
{
string tsql = @"
delete from LY_Templete where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回留言模板Model
public LY_Templete GetTempleteModel(int Id)
{
LY_Templete model = null;
string tsql = "select * from LY_Templete where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<LY_Templete>();
return model;
}
#endregion
#region 返回使用情况Model
public JC_CompanyUse GetCompanyUseModel(int CompanyId)
{
JC_CompanyUse model = null;
string tsql = @"declare @SDate datetime,@LogNum int,@DDNum int,@MinNum int,@MaxNum int,@LoginLog nvarchar(500)
select @SDate=MIN(indate) from JC_UserInfo where CompanyId=@CompanyId
select @LogNum=COUNT(0) from JC_Log a
inner join JC_UserInfo b on a.InUserId=b.UserId
where b.CompanyId=@CompanyId and LogType='登陆日志'
select top 1 @LoginLog=a.LogContext from JC_Log a
inner join JC_UserInfo b on a.InUserId=b.UserId
where b.CompanyId=@CompanyId and LogType='登陆日志' order by a.Id desc
select @DDNum=COUNT(0) from DT_OrderInfo where CompanyId=@CompanyId
select @MinNum=MIN(num),@MaxNum=Max(num) from (
select num=count(0) from DT_OrderInfo where CompanyId=@CompanyId group by convert(nvarchar(10),OrderDate,120))a
select SDate=@SDate,LogNum=@LogNum,DDNum=@DDNum,MinNum=@MinNum,MaxNum=@MaxNum,LoginLog=@LoginLog";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_CompanyUse>();
return model;
}
#endregion
#region 保存线上发货物流方案
public int SaveOnlineExpressService(JC_OnlineExpressService Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_OnlineExpressService] set [logisticsServiceName]=@logisticsServiceName,[trialResult]=@trialResult,[logisticsTimeliness]=@logisticsTimeliness,[logisticsServiceId]=@logisticsServiceId,[deliveryAddress]=@deliveryAddress where Id=@Id
end
else
begin
INSERT INTO [JC_OnlineExpressService]([logisticsServiceName],[trialResult],[logisticsTimeliness],[logisticsServiceId],[deliveryAddress])values(@logisticsServiceName,@trialResult,@logisticsTimeliness,@logisticsServiceId,@deliveryAddress)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@logisticsServiceName", DbType.String, Model.logisticsServiceName);
db.AddInParameter(cmd, "@trialResult", DbType.String, Model.trialResult);
db.AddInParameter(cmd, "@logisticsTimeliness", DbType.String, Model.logisticsTimeliness);
db.AddInParameter(cmd, "@logisticsServiceId", DbType.String, Model.logisticsServiceId);
db.AddInParameter(cmd, "@deliveryAddress", DbType.String, Model.deliveryAddress);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存线上发货内容模板
public int SaveOnlineTemp(JC_OnlineTemp Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_OnlineTemp] set [TempName]=@TempName,[IsBattery]=@IsBattery,[productWeight]=@productWeight,[hsCode]=@hsCode,[DeclareAmount]=@DeclareAmount,[city]=@city,[postcode]=@postcode,[province]=@province,[streetAddress]=@streetAddress,[name]=@name,[mobile]=@mobile,[CompanyId]=@CompanyId where Id=@Id
end
else
begin
INSERT INTO [JC_OnlineTemp]([TempName],[IsBattery],[productWeight],[hsCode],[DeclareAmount],[city],[postcode],[province],[streetAddress],[name],[mobile],[CompanyId])values(@TempName,@IsBattery,@productWeight,@hsCode,@DeclareAmount,@city,@postcode,@province,@streetAddress,@name,@mobile,@CompanyId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@TempName", DbType.String, Model.TempName);
db.AddInParameter(cmd, "@IsBattery", DbType.Int32, Model.IsBattery);
db.AddInParameter(cmd, "@productWeight", DbType.Decimal, Model.productWeight);
db.AddInParameter(cmd, "@hsCode", DbType.String, Model.hsCode);
db.AddInParameter(cmd, "@DeclareAmount", DbType.Decimal, Model.DeclareAmount);
db.AddInParameter(cmd, "@city", DbType.String, Model.city);
db.AddInParameter(cmd, "@postcode", DbType.String, Model.postcode);
db.AddInParameter(cmd, "@province", DbType.String, Model.province);
db.AddInParameter(cmd, "@streetAddress", DbType.String, Model.streetAddress);
db.AddInParameter(cmd, "@name", DbType.String, Model.name);
db.AddInParameter(cmd, "@mobile", DbType.String, Model.mobile);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存线上发货内容模板
public List<JC_OnlineTemp> GetOnlineTempList(int CompanyId)
{
List<JC_OnlineTemp> list = null;
string tsql = @"
select * from JC_OnlineTemp where CompanyId=@CompanyId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_OnlineTemp>();
return list;
}
#endregion
#region 删除线上发货内容模板
public void DeleteOnlineTemp(int Id)
{
string tsql = @"
delete from JC_OnlineTemp where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回Model线上发货内容模板
public JC_OnlineTemp GetOnlineTempModel(int Id)
{
JC_OnlineTemp model = null;
string tsql = "select * from JC_OnlineTemp where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_OnlineTemp>();
return model;
}
#endregion
#region 获取国内物流
public List<JC_InnerExpress> GetInnerPostList()
{
List<JC_InnerExpress> list = null;
string tsql = @"
select * from JC_InnerExpress";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_InnerExpress>();
return list;
}
#endregion
#region 获取线上物流
public List<logisticsService> GetOnlineLogisticsList()
{
List<logisticsService> list = null;
string tsql = @"
select * from JC_OnlineExpressService";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<logisticsService>();
return list;
}
#endregion
#region 返回线上发货
public string GetOnlineServiceName(int OrderId)
{
string tsql = @"
declare @OnlinePostId nvarchar(50),@logistics nvarchar(50)
select @OnlinePostId=OnlinePostId from DT_OrderXXInfo where OrderId=@OrderId
if @OnlinePostId is not null
begin
select top 1 @logistics=SUBSTRING(logisticsServiceId,0,CHARINDEX('_',logisticsServiceId,0)) from JC_OnlineExpressService
where logisticsServiceId=@OnlinePostId
if @logistics='YANWENJYT'
begin
select 'YANWEN_JYT'
end
else
begin
if(select count(0) from JC_Express where Remark=@logistics)>0
begin
select @logistics
end
else
begin
select 'CPAM'
end
end
end
else begin
select 'CPAM'
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
string a =Convert.ToString(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 获取阿里表情
public List<JC_AliFaceImg> GetAliFaceImgList()
{
List<JC_AliFaceImg> list = null;
string tsql = @"
select * from JC_AliFaceImg";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_AliFaceImg>();
return list;
}
#endregion
#region 保存请假
/// <summary>
/// 保存请假
/// </summary>
public int SaveLeave(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [InName]=@InName,[InUserId]=@InUserId,[InDate]=@InDate,[Days]=@Days,[SDate]=@SDate,[EDate]=@EDate,[State]=@State,[Reason]=@Reason,[LeaveType]=@LeaveType,[DeptUserId]=@DeptUserId,[DeptOption]=@DeptOption,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[DeptAgree]=@DeptAgree,[CompanyAgree]=@CompanyAgree,AMPM=@AMPM,DaysUint=@DaysUint,[PDays]=@PDays,[PSDate]=@PSDate,[PEDate]=@PEDate where Id=@Id
end
else
begin
INSERT INTO [OA_Leave]([InName],[InUserId],[InDate],[Days],[SDate],[EDate],[State],[Reason],[LeaveType],[DeptUserId],[DeptOption],[CompanyUserId],[CompanyOption],[DeptAgree],[CompanyAgree],AMPM,DaysUint,[PDays],[PSDate],[PEDate],ManageUserId)values(@InName,@InUserId,@InDate,@Days,@SDate,@EDate,@State,@Reason,@LeaveType,@DeptUserId,@DeptOption,@CompanyUserId,@CompanyOption,@DeptAgree,@CompanyAgree,@AMPM,@DaysUint,@PDays,@PSDate,@PEDate,@ManageUserId)
set @Id=@@IDENTITY
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@Days", DbType.Decimal, Model.Days);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, Model.SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, Model.EDate);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@Reason", DbType.String, Model.Reason);
db.AddInParameter(cmd, "@LeaveType", DbType.String, Model.LeaveType);
db.AddInParameter(cmd, "@DeptUserId", DbType.Int32, Model.DeptUserId);
db.AddInParameter(cmd, "@DeptOption", DbType.String, Model.DeptOption);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@DeptAgree", DbType.Int32, Model.DeptAgree);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
db.AddInParameter(cmd, "@AMPM", DbType.String, Model.AMPM);
db.AddInParameter(cmd, "@DaysUint", DbType.String, Model.DaysUint);
db.AddInParameter(cmd, "@PDays", DbType.Decimal, Model.PDays);
db.AddInParameter(cmd, "@PSDate", DbType.DateTime, Model.PSDate);
db.AddInParameter(cmd, "@PEDate", DbType.DateTime, Model.PEDate);
db.AddInParameter(cmd, "@ManageUserId", DbType.Int32, Model.ManageUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 核对请假
public int CheckLeave(int InUserId,DateTime? LeaveDate)
{
string tsql = "select Num=count(0) from [OA_Leave] where DATEDIFF(month,SDate,@LeaveDate)=0 and state=2 and DeptAgree=1 and (companyagree is null or companyagree=1) and LeaveType='运动(生理假)' and InUserId=@InUserId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, InUserId);
db.AddInParameter(cmd, "@LeaveDate", DbType.DateTime, LeaveDate);
int a =Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 核对请假
public int CheckLeaveN(OA_Leave Model)
{
int checkcount = 0;
string tsql = @"
declare @LDays decimal(18, 2),@YDays decimal(18, 2)
set @YDays=0
set @LDays=3.0
select top 1 @LDays=AnnualLeave from OA_TotalLeave where UserId=@InUserId
SELECT @YDays=SUM(CASE WHEN DaysUint='小时' THEN DAYS/8 ELSE DAYS END) FROM dbo.OA_Leave WHERE state=2 and InUserId=@InUserId and LeaveType='年假'
if @LDays<@YDays+@Days
begin
select 1
end
else
begin
select 0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@Days", DbType.Decimal, Model.Days);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) checkcount = tb.Rows.Count;
return checkcount;
}
#endregion
#region 核对请假
public int CheckLeaveM(OA_Leave Model)
{
string tsql = @"
declare @LDays decimal(18, 2),@YDays decimal(18, 2)
set @YDays=0
set @LDays=3.0
select top 1 @LDays=MoodLeave from OA_TotalLeave where UserId=@InUserId
SELECT @YDays=SUM(CASE WHEN DaysUint='小时' THEN DAYS/8 ELSE DAYS END) FROM dbo.OA_Leave WHERE state=2 and InUserId=@InUserId and LeaveType='心情假'
if @LDays<@YDays+@Days
begin
select 1
end
else
begin
select 0
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@Days", DbType.Decimal, Model.Days);
DataTable tb = db.ExecuteDataTable(cmd);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存年假
public static int SaveTotalLeave(OA_TotalLeave Model)
{
string tsql = @"
if @LeaveID>0
begin
Update [OA_TotalLeave] set [UserID]=@UserID,[AnnualLeave]=@AnnualLeave,[MoodLeave]=@MoodLeave,[AddLeave]=@AddLeave,[DelLeave]=@DelLeave,[EndDate]=@EndDate where LeaveID=@LeaveID
end
else
begin
INSERT INTO [OA_TotalLeave]([UserID],[AnnualLeave],[MoodLeave],[AddLeave],[DelLeave],[EndDate])values(@UserID,@AnnualLeave,@MoodLeave,@AddLeave,@DelLeave,@EndDate)
set @LeaveID=SCOPE_IDENTITY()
end
select @LeaveID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@LeaveID", DbType.Int32, Model.LeaveID);
db.AddInParameter(cmd, "@UserID", DbType.Int32, Model.UserID);
db.AddInParameter(cmd, "@AnnualLeave", DbType.Int32, Model.AnnualLeave);
db.AddInParameter(cmd, "@MoodLeave", DbType.Int32, Model.MoodLeave);
db.AddInParameter(cmd, "@AddLeave", DbType.Int32, Model.AddLeave);
db.AddInParameter(cmd, "@DelLeave", DbType.Int32, Model.DelLeave);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, Model.EndDate);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除年假
public static void DeleteTotalLeave(int LeaveID)
{
string tsql = @"
delete from OA_TotalLeave where LeaveID=@LeaveID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@LeaveID", DbType.Int32, LeaveID);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回年假Model
public static OA_TotalLeave GetModel(int LeaveID)
{
OA_TotalLeave model = null;
string tsql = "select * from OA_TotalLeave where LeaveID=@LeaveID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@LeaveID", DbType.Int32, LeaveID);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<OA_TotalLeave>();
return model;
}
#endregion
#region 分页查询
public static List<OA_TotalLeave> GetListOA_TotalLeave(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"LeaveID=cast(a.LeaveID as int),a.UserID,a.AnnualLeave,a.MoodLeave,a.AddLeave,a.DelLeave,a.EndDate";
ser.Tables = @"OA_TotalLeave a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "LeaveID";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<OA_TotalLeave> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<OA_TotalLeave>();
return ListModel;
}
#endregion
#region 保存加班
/// <summary>
/// 保存加班
/// </summary>
public int SaveWorkAdd(OA_WorkAdd Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_WorkAdd] set [InName]=@InName,[InUserId]=@InUserId,[InDate]=@InDate,[Days]=@Days,[SDate]=@SDate,[EDate]=@EDate,[State]=@State,[Reason]=@Reason,[LeaveType]=@LeaveType,[DeptUserId]=@DeptUserId,[DeptOption]=@DeptOption,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[DeptAgree]=@DeptAgree,[CompanyAgree]=@CompanyAgree,AMPM=@AMPM where Id=@Id
end
else
begin
INSERT INTO [OA_WorkAdd]([InName],[InUserId],[InDate],[Days],[SDate],[EDate],[State],[Reason],[LeaveType],[DeptUserId],[DeptOption],[CompanyUserId],[CompanyOption],[DeptAgree],[CompanyAgree],AMPM)values(@InName,@InUserId,@InDate,@Days,@SDate,@EDate,@State,@Reason,@LeaveType,@DeptUserId,@DeptOption,@CompanyUserId,@CompanyOption,@DeptAgree,@CompanyAgree,@AMPM)
set @Id=@@IDENTITY
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@Days", DbType.Decimal, Model.Days);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, Model.SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, Model.EDate);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@Reason", DbType.String, Model.Reason);
db.AddInParameter(cmd, "@LeaveType", DbType.String, Model.LeaveType);
db.AddInParameter(cmd, "@DeptUserId", DbType.Int32, Model.DeptUserId);
db.AddInParameter(cmd, "@DeptOption", DbType.String, Model.DeptOption);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@DeptAgree", DbType.Int32, Model.DeptAgree);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
db.AddInParameter(cmd, "@AMPM", DbType.String, Model.AMPM);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存加班部门审批
/// <summary>
/// 保存加班部门审批
/// </summary>
public int SaveWorkAddCheck(OA_WorkAdd Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_WorkAdd] set [State]=@State,[DeptUserId]=@DeptUserId,CompanyUserId=@CompanyUserId,[DeptOption]=@DeptOption,[DeptAgree]=@DeptAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@DeptUserId", DbType.Int32, Model.DeptUserId);
db.AddInParameter(cmd, "@DeptOption", DbType.String, Model.DeptOption);
db.AddInParameter(cmd, "@DeptAgree", DbType.Int32, Model.DeptAgree);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存加班公司审批
/// <summary>
/// 保存加班审批
/// </summary>
public int SaveWorkAddCompanyCheck(OA_WorkAdd Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_WorkAdd] set [State]=@State,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[CompanyAgree]=@CompanyAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假部门审批
/// <summary>
/// 保存请假审批
/// </summary>
public int SaveLeaveCheck(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [State]=@State,[DeptUserId]=@DeptUserId,CompanyUserId=@CompanyUserId,[DeptOption]=@DeptOption,[DeptAgree]=@DeptAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@DeptUserId", DbType.Int32, Model.DeptUserId);
db.AddInParameter(cmd, "@DeptOption", DbType.String, Model.DeptOption);
db.AddInParameter(cmd, "@DeptAgree", DbType.Int32, Model.DeptAgree);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假公司审批
/// <summary>
/// 保存请假审批
/// </summary>
public int SaveCompanyCheck(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [State]=@State,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[CompanyAgree]=@CompanyAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假副总审批
/// <summary>
/// 保存请假副总审批
/// </summary>
public int SaveCompanyCheck1(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [State]=@State,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[CompanyAgree]=@CompanyAgree,ManageUserId=@ManageUserId where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
db.AddInParameter(cmd, "@ManageUserId", DbType.Int32, Model.ManageUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假总经理审批
/// <summary>
/// 保存请假审批
/// </summary>
public int SaveCompanyCheck2(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [State]=@State,[ManageUserId]=@ManageUserId,[ManageOption]=@ManageOption,[ManageAgree]=@ManageAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@ManageUserId", DbType.Int32, Model.ManageUserId);
db.AddInParameter(cmd, "@ManageOption", DbType.String, Model.ManageOption);
db.AddInParameter(cmd, "@ManageAgree", DbType.Int32, Model.ManageAgree);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假公司审批
/// <summary>
/// 保存请假审批
/// </summary>
public int SaveCompanyCheckUser(OA_Leave Model)
{
string tsql = @"
if @InUserId>0
begin
Update [OA_Leave] set [State]=@State,[CompanyUserId]=@CompanyUserId,[CompanyOption]=@CompanyOption,[CompanyAgree]=@CompanyAgree where InUserId=@InUserId and CompanyUserId=@CompanyUserId and ISNULL(CompanyAgree,0)=0
end
select @InUserId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@CompanyUserId", DbType.Int32, Model.CompanyUserId);
db.AddInParameter(cmd, "@CompanyOption", DbType.String, Model.CompanyOption);
db.AddInParameter(cmd, "@CompanyAgree", DbType.Int32, Model.CompanyAgree);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存请假总经理审批
/// <summary>
/// 保存请假审批
/// </summary>
public int SaveMangeCheck(OA_Leave Model)
{
string tsql = @"
if @Id>0
begin
Update [OA_Leave] set [State]=@State,[ManageOption]=@ManageOption,[ManageAgree]=@ManageAgree where Id=@Id
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@ManageUserId", DbType.Int32, Model.ManageUserId);
db.AddInParameter(cmd, "@ManageOption", DbType.String, Model.ManageOption);
db.AddInParameter(cmd, "@ManageAgree", DbType.Int32, Model.ManageAgree);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 查询请假审批人
public List<OA_DeptCharge> GetLeaveCharge(int UserId)
{
DataSet ds = new DataSet();
List<OA_DeptCharge> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"declare @Duty nvarchar(50),@DeptId int
set @Duty=''
select top 1 @Duty=isnull(Duty,''),@DeptId=DeptId from JC_UserInfo where UserId=@UserId
if @Duty='副总经理'
begin
select UserId,DeptId,DeptName,Name,Duty from JC_UserInfo
where State=1 and duty='总经理'
end
else if(select count(0) from JC_DepartUser where UserId=@UserId and Duty='M')>0
begin
select UserId,DeptId,DeptName,Name,Duty from JC_UserInfo
where State=1 and duty='副总经理'
end
else
begin
select a.UserId,a.DeptId,a.DeptName,a.Name,Duty='经理' from JC_UserInfo a
inner join JC_DepartUser b on a.UserId=b.UserId
where a.State=1 and a.deptid=@DeptId and b.DeptId=@DeptId and b.duty='M'
end";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 查询请假审批人
public List<OA_DeptCharge> GetLeaveMasterCharge()
{
DataSet ds = new DataSet();
List<OA_DeptCharge> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select UserId,DeptId,DeptName,Name,Duty from JC_UserInfo
where State=1 and duty='副总经理'
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 分页查询
public List<OA_Leave> GetListOA_Leave(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InName,a.InUserId,a.InDate,a.Days,a.SDate,a.EDate,a.State,a.Reason,a.LeaveType,a.DeptUserId,a.DeptOption,a.CompanyUserId,a.CompanyOption,a.DeptAgree,a.CompanyAgree,StateName=case when a.State=2 then '审批完成' when a.State=1 then '审批中' when a.State=0 then '未审批' end,CheckState=case when a.State=2 and a.DeptAgree=0 then '【未准假】' when a.State=2 and a.CompanyAgree=0 then '【未准假】' when a.State=2 and a.ManageAgree=0 then '【未准假】' when a.State=2 and a.DeptAgree=1 and a.CompanyUserId is null then '【准假】' when a.State=2 and a.CompanyAgree=1 and a.ManageUserId is null then '【准假】' when a.State=2 and a.ManageAgree=1 then '【准假】' end,a.AMPM,a.DaysUint,a.ManageUserId,a.ManageAgree";
ser.Tables = @"OA_Leave a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<OA_Leave> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<OA_Leave>();
return ListModel;
}
#endregion
#region 分页查询加班
public List<OA_WorkAdd> GetListOA_WorkAdd(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InName,a.InUserId,a.InDate,a.Days,a.SDate,a.EDate,a.State,a.Reason,a.LeaveType,a.DeptUserId,a.DeptOption,a.CompanyUserId,a.CompanyOption,a.DeptAgree,a.CompanyAgree,StateName=case when a.State=2 then '审批完成' when a.State=1 then '审批中' when a.State=0 then '未审批' end,CheckState=case when a.State=2 and a.DeptAgree=0 then '【未批准】' when a.State=2 and a.CompanyAgree=0 then '【未批准】' when a.State=2 and a.DeptAgree=1 and a.CompanyAgree=1 then '【批准】' end,a.AMPM";
ser.Tables = @"OA_WorkAdd a";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<OA_WorkAdd> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<OA_WorkAdd>();
return ListModel;
}
#endregion
public List<OA_WorkAdd> GetWorkAddCount(DateTime? SDate, DateTime? EDate,int CheckState)
{
List<OA_WorkAdd> ListModel = new List<OA_WorkAdd>();
string tsql = @"
if @CheckState=2
begin
select InUserId,InName,Days=sum(days) from OA_WorkAdd where State=2 and deptagree=1 and (companyagree is null or companyagree=1) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) group by InUserId,InName
end
else if @CheckState=1
begin
select InUserId,InName,Days=sum(days) from OA_WorkAdd where State=2 and (deptagree=0 or companyagree=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) group by InUserId,InName
end
else if @CheckState=0
begin
select InUserId,InName,Days=sum(days) from OA_WorkAdd where State=0 and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) group by InUserId,InName
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_WorkAdd>();
return ListModel;
}
public List<OA_WorkAdd> GetWorkAddCountInfo(int UserId, DateTime? SDate, DateTime? EDate, int CheckState)
{
List<OA_WorkAdd> ListModel = new List<OA_WorkAdd>();
string tsql = @"
if @CheckState=2
begin
select * from OA_WorkAdd where InUserId=@UserId and State=2 and deptagree=1 and (companyagree is null or companyagree=1) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate)
end
else if @CheckState=1
begin
select * from OA_WorkAdd where InUserId=@UserId and State=2 and (deptagree=0 or companyagree=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate)
end
else if @CheckState=0
begin
select * from OA_WorkAdd where InUserId=@UserId and State=0 and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate)
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_WorkAdd>();
return ListModel;
}
public List<OA_Leave> GetLeaveCount(DateTime? SDate, DateTime? EDate, string LeaveType, int CheckState)
{
List<OA_Leave> ListModel = new List<OA_Leave>();
string tsql = @"
if @CheckState=2
begin
select InUserId,InName,Days=cast(sum(Days) as Decimal(8,2)),Hours=cast(sum(Hours) as Decimal(8,2)) from (
select InUserId,InName,Days=sum(days),Hours=0 from OA_Leave where DaysUint='天' and State=2 and deptagree=1 and (companyagree is null or companyagree=1) and (ManageAgree is null or ManageAgree=1) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName
union
select InUserId,InName,Days=0,Hours=sum(days) from OA_Leave where DaysUint='小时' and State=2 and deptagree=1 and (companyagree is null or companyagree=1) and (ManageAgree is null or ManageAgree=1) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName)a group by InUserId,InName
end
else if @CheckState=1
begin
select InUserId,InName,Days=cast(sum(Days) as Decimal(8,2)),Hours=cast(sum(Hours) as Decimal(8,2)) from (
select InUserId,InName,Days=sum(days),Hours=0 from OA_Leave where DaysUint='天' and State=2 and (deptagree=0 or ISNULL(companyagree,0)=0) and (ManageAgree is null or ManageAgree=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName
union
select InUserId,InName,Hours=sum(days),Days=0 from OA_Leave where DaysUint='小时' and State=2 and (deptagree=0 or ISNULL(companyagree,0)=0) and (ManageAgree is null or ManageAgree=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName)a group by InUserId,InName
end
else if @CheckState=0
begin
select InUserId,InName,Days=cast(sum(Days) as Decimal(8,2)),Hours=cast(sum(Hours) as Decimal(8,2)) from (
select InUserId,InName,Days=sum(days),Hours=0 from OA_Leave where DaysUint='天' and State=0 and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName
union
select InUserId,InName,Hours=sum(days),Days=0 from OA_Leave where DaysUint='小时' and State=0 and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) group by InUserId,InName)a group by InUserId,InName
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@LeaveType", DbType.String, LeaveType);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_Leave>();
return ListModel;
}
public List<OA_Leave> GetLeaveCountInfo(int UserId, DateTime? SDate, DateTime? EDate, string LeaveType, int CheckState)
{
List<OA_Leave> ListModel = new List<OA_Leave>();
string tsql = @"
if @CheckState=2
begin
select * from OA_Leave where InUserId=@UserId and State=2 and deptagree=1 and (companyagree is null or companyagree=1) and (ManageAgree is null or ManageAgree=1) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType)
end
else if @CheckState=1
begin
select * from OA_Leave where InUserId=@UserId and State=2 and (deptagree=0 or ISNULL(companyagree,0)=0) and (ManageAgree is null or ManageAgree=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType)
end
else if @CheckState=0
begin
select * from OA_Leave where InUserId=@UserId and State=0 and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType)
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@LeaveType", DbType.String, LeaveType);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_Leave>();
return ListModel;
}
public List<OA_Leave> GetNoLeaveCount(int UserId, DateTime? SDate, DateTime? EDate, string LeaveType, int CheckState)
{
List<OA_Leave> ListModel = new List<OA_Leave>();
string tsql = @"
select InUserId,InName,Days=cast(sum(Days) as Decimal(8,2)),Hours=cast(sum(Hours) as Decimal(8,2)) from (
select InUserId,InName,Days=sum(days),Hours=0 from OA_Leave where DaysUint='天' and (deptagree=1 and ISNULL(companyagree,0) =0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) and CompanyUserId=@UserId group by InUserId,InName
union
select InUserId,InName,Hours=sum(days),Days=0 from OA_Leave where DaysUint='小时' and (deptagree=1 and ISNULL(companyagree,0)=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType) and CompanyUserId=@UserId group by InUserId,InName)a group by InUserId,InName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@LeaveType", DbType.String, LeaveType);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_Leave>();
return ListModel;
}
public List<OA_Leave> GetNoReLeave(int UserId, DateTime? SDate, DateTime? EDate, string LeaveType, int CheckState)
{
List<OA_Leave> ListModel = new List<OA_Leave>();
string tsql = @"
select * from OA_Leave where InUserId=@UserId and (deptagree=1 and ISNULL(companyagree,0)=0) and (@SDate is null or Sdate>=@SDate) and (@EDate is null or Sdate<@EDate) and (@LeaveType='0' or LeaveType=@LeaveType)
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@LeaveType", DbType.String, LeaveType);
db.AddInParameter(cmd, "@CheckState", DbType.Int32, CheckState);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_Leave>();
return ListModel;
}
public OA_TotalLeave GetUserLeaveModel(int UserId)
{
OA_TotalLeave model = null;
string tsql = @"
SELECT UserID,AnnualLeave, DelLeave=ISNULL(
(SELECT SUM(CASE WHEN b.DaysUint='小时' THEN b.Days/8 ELSE b.Days END) FROM dbo.OA_Leave b WHERE a.userid=b.InUserId AND LeaveType='年假' AND b.SDate>DATEADD(yy,-1,a.EndDate)AND b.SDate<a.EndDate),0)
, MoodLeave,AddLeave=ISNULL(
(SELECT SUM(CASE WHEN b.DaysUint='小时' THEN b.Days/8 ELSE b.Days END) FROM dbo.OA_Leave b WHERE a.userid=b.InUserId AND LeaveType='心情假'AND b.SDate>DATEADD(yy,-1,a.EndDate)AND b.SDate<a.EndDate),0)
,EndDate FROM dbo.OA_TotalLeave a where a.UserID=@UserId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<OA_TotalLeave>();
return model;
}
public List<OA_TotalLeave> GetUserLeave(int UserId)
{
List<OA_TotalLeave> ListModel = new List<OA_TotalLeave>();
string tsql = @"
SELECT UserID,AnnualLeave, DelLeave=ISNULL(
(SELECT SUM(CASE WHEN b.DaysUint='小时' THEN b.Days/8 ELSE b.Days END) FROM dbo.OA_Leave b WHERE a.userid=b.InUserId AND LeaveType='年假' AND b.SDate>DATEADD(yy,-1,a.EndDate)AND b.SDate<a.EndDate),0)
, MoodLeave,AddLeave=ISNULL(
(SELECT SUM(CASE WHEN b.DaysUint='小时' THEN b.Days/8 ELSE b.Days END) FROM dbo.OA_Leave b WHERE a.userid=b.InUserId AND LeaveType='心情假'AND b.SDate>DATEADD(yy,-1,a.EndDate)AND b.SDate<a.EndDate),0)
,EndDate FROM dbo.OA_TotalLeave a where a.UserID=@UserId
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
ListModel = tb.ToList<OA_TotalLeave>();
return ListModel;
}
#region 保存
public int SaveUserLeaveModel(OA_TotalLeave Model)
{
string tsql = @"
Update [OA_TotalLeave] set [AnnualLeave]=@AnnualLeave,[MoodLeave]=@MoodLeave,[AddLeave]=@AddLeave,[DelLeave]=@DelLeave,[EndDate]=@EndDate where UserID=@UserID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserID);
db.AddInParameter(cmd, "@AnnualLeave", DbType.Decimal, Model.AnnualLeave);
db.AddInParameter(cmd, "@MoodLeave", DbType.Decimal, Model.MoodLeave);
db.AddInParameter(cmd, "@AddLeave", DbType.Decimal, Model.AddLeave);
db.AddInParameter(cmd, "@DelLeave", DbType.Decimal, Model.DelLeave);
db.AddInParameter(cmd, "@EndDate", DbType.DateTime, Model.EndDate);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除加班
public void DeleteWorkAdd(int Id)
{
string tsql = @"
delete from OA_WorkAdd where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除请假
public void DeleteLeave(int Id)
{
string tsql = @"
delete from OA_Leave where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回Model
public OA_Leave GetLeaveModel(int Id)
{
OA_Leave model = null;
string tsql = "select * from OA_Leave where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<OA_Leave>();
return model;
}
#endregion
#region 返回Model
public OA_WorkAdd GetWorkAddModel(int Id)
{
OA_WorkAdd model = null;
string tsql = "select * from OA_WorkAdd where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<OA_WorkAdd>();
return model;
}
#endregion
#region 保存店铺数据
public static int SaveShopData(JC_ShopData Model)
{
string tsql = @"
if @Id>0
begin
Update [JC_ShopData] set [GoodRate]=@GoodRate,[ODR]=@ODR,[DSRGoods]=@DSRGoods,[DSRService]=@DSRService,[DSRPost]=@DSRPost,[USDUseMoney]=@USDUseMoney,[USDNoMoney]=@USDNoMoney,[RMBUseMoney]=@RMBUseMoney,[RMBNoMoney]=@RMBNoMoney,[ShopId]=@ShopId,[InUserId]=@InUserId,[InName]=@InName,[InforViolate]=@InforViolate,[PropertyViolate]=@PropertyViolate,[OtherViolate]=@OtherViolate,Rate1=@Rate1,Rate2=@Rate2,Rate3=@Rate3,Rate4=@Rate4,Rate5=@Rate5,Rate6=@Rate6 where Id=@Id
end
else
begin
INSERT INTO [JC_ShopData]([GoodRate],[ODR],[DSRGoods],[DSRService],[DSRPost],[USDUseMoney],[USDNoMoney],[RMBUseMoney],[RMBNoMoney],[ShopId],[InDate],[InUserId],[InName],[InforViolate],[PropertyViolate],[OtherViolate],Rate1,Rate2,Rate3,Rate4,Rate5,Rate6)values(@GoodRate,@ODR,@DSRGoods,@DSRService,@DSRPost,@USDUseMoney,@USDNoMoney,@RMBUseMoney,@RMBNoMoney,@ShopId,@InDate,@InUserId,@InName,@InforViolate,@PropertyViolate,@OtherViolate,@Rate1,@Rate2,@Rate3,@Rate4,@Rate5,@Rate6)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@GoodRate", DbType.Decimal, Model.GoodRate);
db.AddInParameter(cmd, "@ODR", DbType.Decimal, Model.ODR);
db.AddInParameter(cmd, "@DSRGoods", DbType.Decimal, Model.DSRGoods);
db.AddInParameter(cmd, "@DSRService", DbType.Decimal, Model.DSRService);
db.AddInParameter(cmd, "@DSRPost", DbType.Decimal, Model.DSRPost);
db.AddInParameter(cmd, "@USDUseMoney", DbType.Decimal, Model.USDUseMoney);
db.AddInParameter(cmd, "@USDNoMoney", DbType.Decimal, Model.USDNoMoney);
db.AddInParameter(cmd, "@RMBUseMoney", DbType.Decimal, Model.RMBUseMoney);
db.AddInParameter(cmd, "@RMBNoMoney", DbType.Decimal, Model.RMBNoMoney);
db.AddInParameter(cmd, "@ShopId", DbType.Int32, Model.ShopId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@InforViolate", DbType.Decimal, Model.InforViolate);
db.AddInParameter(cmd, "@PropertyViolate", DbType.Decimal, Model.PropertyViolate);
db.AddInParameter(cmd, "@OtherViolate", DbType.Decimal, Model.OtherViolate);
db.AddInParameter(cmd, "@Rate1", DbType.Decimal, Model.Rate1);
db.AddInParameter(cmd, "@Rate2", DbType.Decimal, Model.Rate2);
db.AddInParameter(cmd, "@Rate3", DbType.Decimal, Model.Rate3);
db.AddInParameter(cmd, "@Rate4", DbType.Decimal, Model.Rate4);
db.AddInParameter(cmd, "@Rate5", DbType.Decimal, Model.Rate5);
db.AddInParameter(cmd, "@Rate6", DbType.Decimal, Model.Rate6);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除店铺数据
public static void DeleteShopData(int Id)
{
string tsql = @"
delete from JC_ShopData where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 分页查询店铺数据
public static List<JC_ShopData> GetListJC_ShopData2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.GoodRate,a.ODR,a.DSRGoods,a.DSRService,a.DSRPost,a.USDUseMoney,a.USDNoMoney,a.RMBUseMoney,a.RMBNoMoney,a.ShopId,a.InDate,a.InUserId,a.InName,b.ShopName,a.InforViolate,a.PropertyViolate,a.OtherViolate,a.Rate1,a.Rate2,a.Rate3,a.Rate4,a.Rate5,a.Rate6";
ser.Tables = @"JC_ShopData a inner join jc_shop b on a.shopid=b.shopid";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_ShopData> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_ShopData>();
return ListModel;
}
#endregion
#region 分页查询店铺数据
public static List<JC_ShopData> GetListJC_ShopData(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.GoodRate,a.ODR,a.DSRGoods,a.DSRService,a.DSRPost,a.USDUseMoney,a.USDNoMoney,a.RMBUseMoney,a.RMBNoMoney,a.ShopId,a.InDate,a.InUserId,a.InName,b.ShopName,a.InforViolate,a.PropertyViolate,a.OtherViolate";
ser.Tables = @"JC_ShopData a inner join jc_shop b on a.shopid=b.shopid";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_ShopData> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_ShopData>();
return ListModel;
}
#endregion
#region 获取店铺成员
public List<JC_ShopUser> GetShopUser(int ShopId)
{
List<JC_ShopUser> list = null;
string tsql = @"
select a.*,b.Name from JC_ShopUser a
inner join JC_UserInfo b on a.UserId=b.UserId
where a.ShopId=@ShopId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ShopId", DbType.Int32, ShopId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_ShopUser>();
return list;
}
#endregion
#region 获取报表数据
public DataTable GetReportData(string Sql)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(Sql);
DataTable tb = db.ExecuteDataTable(cmd);
return tb;
}
#endregion
#region 获取报表数据
public DataSet GetReportData2(string Sql)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(Sql);
DataSet tb = db.ExecuteDataSet(cmd);
return tb;
}
#endregion
#region 返回报表Model
public static JC_Report GetReportModel(int Id)
{
JC_Report model = null;
string tsql = "select * from JC_Report where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Report>();
return model;
}
#endregion
#region 获取报表字段
public List<JC_RepoartCols> GetRepoartColsList(int ReportId)
{
List<JC_RepoartCols> list = null;
string tsql = @"
select * from JC_RepoartCols where ReportId=@ReportId order by SortNo";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ReportId", DbType.Int32, ReportId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_RepoartCols>();
return list;
}
#endregion
#region 获取比较记录
public List<WL_TrackCompare> GetWL_TrackCompareList()
{
List<WL_TrackCompare> list = null;
string tsql = @"
select * from WL_TrackCompare order by Id desc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_TrackCompare>();
return list;
}
#endregion
#region 删除比较记录
public void DeleteTrackCompare(int Id)
{
string tsql = @"
delete from WL_TrackCompare where Id=@Id
delete from WL_TrackCompareDetail where MasterId=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 保存物流比较记录
public int SaveTrackCompareDetail(WL_TrackCompareDetail Model)
{
string tsql = @"
select top 1 @Id=Id from WL_TrackCompareDetail where [TrackCode]=@TrackCode and MasterId=@MasterId
if @Id>0
begin
Update [WL_TrackCompareDetail] set [TrackCode]=@TrackCode,[Weight]=@Weight,[Fee]=@Fee,[OrderCode]=@OrderCode,[Country]=@Country,[MasterId]=@MasterId where Id=@Id
end
else
begin
INSERT INTO [WL_TrackCompareDetail]([TrackCode],[Weight],[Fee],[OrderCode],[PostId],[InDate],[MyWeight],[MyFee],[LastWeight],[LastFee],[State],[Country],[MasterId])values(@TrackCode,@Weight,@Fee,@OrderCode,@PostId,@InDate,@MyWeight,@MyFee,@LastWeight,@LastFee,@State,@Country,@MasterId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
db.AddInParameter(cmd, "@Weight", DbType.Decimal, Model.Weight);
db.AddInParameter(cmd, "@Fee", DbType.Decimal, Model.Fee);
db.AddInParameter(cmd, "@OrderCode", DbType.String, Model.OrderCode);
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@MyWeight", DbType.Decimal, Model.MyWeight);
db.AddInParameter(cmd, "@MyFee", DbType.Decimal, Model.MyFee);
db.AddInParameter(cmd, "@LastWeight", DbType.Decimal, Model.LastWeight);
db.AddInParameter(cmd, "@LastFee", DbType.Decimal, Model.LastFee);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@Country", DbType.String, Model.Country);
db.AddInParameter(cmd, "@MasterId", DbType.Int32, Model.MasterId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存物流比较
public int SaveTrackCompare(WL_TrackCompare Model)
{
string tsql = @"
if @Id>0
begin
Update [WL_TrackCompare] set [Year]=@Year,[Month]=@Month,[Name]=@Name,[PostId]=@PostId,[InDate]=@InDate,[InUserId]=@InUserId where Id=@Id
end
else
begin
INSERT INTO [WL_TrackCompare]([Year],[Month],[Name],[PostId],[InDate],[InUserId])values(@Year,@Month,@Name,@PostId,@InDate,@InUserId)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@Year", DbType.Int32, Model.Year);
db.AddInParameter(cmd, "@Month", DbType.Int32, Model.Month);
db.AddInParameter(cmd, "@Name", DbType.String, Model.Name);
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 获取报表
public List<JC_Report> GetReportList()
{
List<JC_Report> list = null;
string tsql = @"
select * from JC_Report order by SortNo";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_Report>();
return list;
}
#endregion
#region 获取选品审批人
public List<OA_DeptCharge> GetXPCheck(int UserId)
{
List<OA_DeptCharge> list = null;
string tsql = @"
select a.UserId,b.Name from JC_DepartUser a
inner join JC_UserInfo b on a.UserId=b.UserId
where a.Duty='M' and a.DeptId in (select DeptId from JC_UserInfo where UserId=@UserId)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 删除选品
public void DeleteGoodsSelect(int Id)
{
string tsql = @"
delete from XP_GoodsSelect where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 获取选品审批人
public List<OA_DeptCharge> GetCompanyXPCheck(int UserId)
{
List<OA_DeptCharge> list = null;
string tsql = @"
select b.UserId,b.Name from XP_Check a
inner join JC_UserInfo b on a.CheckUserId=b.UserId
where a.DeptId in (select DeptId from JC_UserInfo where UserId=@UserId)";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 返回选品Model
public XP_GoodsSelectModel GetXPModel(int Id)
{
XP_GoodsSelectModel model = null;
string tsql = "select * from XP_GoodsSelect where Id=@Id and ISNULL(IsUse,0)=0";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<XP_GoodsSelectModel>();
return model;
}
#endregion
#region 查询选品人
public List<XP_GoodsSelectModel> GetGoodsSelectName(int UserId)
{
List<XP_GoodsSelectModel> list = null;
string tsql = @"
select distinct InName from XP_GoodsSelect where InUserId=@UserId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<XP_GoodsSelectModel>();
return list;
}
#endregion
#region 保存选品
public int SaveGoodsSelect(XP_GoodsSelectModel Model)
{
string tsql = @"
if @Id>0
begin
Update [XP_GoodsSelect] set [SortId]=@SortId,[GoodsName]=@GoodsName,[SortName]=@SortName,[LinkUrl]=@LinkUrl,[Remark]=@Remark,[InUserId]=@InUserId,[InName]=@InName,[InDate]=@InDate,[CheckUId1]=@CheckUId1,[CheckName1]=@CheckName1,[CheckContent1]=@CheckContent1,[CheckState1]=@CheckState1,[CheckDate1]=@CheckDate1,[CheckUId2]=@CheckUId2,[CheckContent2]=@CheckContent2,[CheckState2]=@CheckState2,[CheckDate2]=@CheckDate2,[IsAdd]=@IsAdd,[CompanyId]=@CompanyId,[CheckName2]=@CheckName2,ShopName=@ShopName,IsUse=@IsUse where Id=@Id
end
else
begin
INSERT INTO [XP_GoodsSelect]([SortId],[GoodsName],[SortName],[LinkUrl],[Remark],[InUserId],[InName],[InDate],[CheckUId1],[CheckName1],[CheckContent1],[CheckState1],[CheckDate1],[CheckUId2],[CheckContent2],[CheckState2],[CheckDate2],[IsAdd],[CompanyId],[CheckName2],ShopName,IsUse)values(@SortId,@GoodsName,@SortName,@LinkUrl,@Remark,@InUserId,@InName,@InDate,@CheckUId1,@CheckName1,@CheckContent1,@CheckState1,@CheckDate1,@CheckUId2,@CheckContent2,@CheckState2,@CheckDate2,@IsAdd,@CompanyId,@CheckName2,@ShopName,@IsUse)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@SortId", DbType.Int32, Model.SortId);
db.AddInParameter(cmd, "@GoodsName", DbType.String, Model.GoodsName);
db.AddInParameter(cmd, "@SortName", DbType.String, Model.SortName);
db.AddInParameter(cmd, "@LinkUrl", DbType.String, Model.LinkUrl);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@CheckUId1", DbType.Int32, Model.CheckUId1);
db.AddInParameter(cmd, "@CheckName1", DbType.String, Model.CheckName1);
db.AddInParameter(cmd, "@CheckContent1", DbType.String, Model.CheckContent1);
db.AddInParameter(cmd, "@CheckState1", DbType.Int32, Model.CheckState1);
db.AddInParameter(cmd, "@CheckDate1", DbType.DateTime, Model.CheckDate1);
db.AddInParameter(cmd, "@CheckUId2", DbType.Int32, Model.CheckUId2);
db.AddInParameter(cmd, "@CheckContent2", DbType.String, Model.CheckContent2);
db.AddInParameter(cmd, "@CheckState2", DbType.Int32, Model.CheckState2);
db.AddInParameter(cmd, "@CheckDate2", DbType.DateTime, Model.CheckDate2);
db.AddInParameter(cmd, "@IsAdd", DbType.Int32, Model.IsAdd);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
db.AddInParameter(cmd, "@CheckName2", DbType.String, Model.CheckName2);
db.AddInParameter(cmd, "@ShopName", DbType.String, Model.ShopName);
db.AddInParameter(cmd, "@IsUse", DbType.Int32, Model.IsUse);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 更新选品状态(取消)
public int UpdateXP(int id)
{
string tsql = @"
Update [XP_GoodsSelect] set IsUse=1 where Id=" + id + @"
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.ExecuteScalar(cmd);
return id;
}
#endregion
#region 分页查询选品
public List<XP_GoodsSelectModel> GetListXP_GoodsSelect(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.SortId,a.GoodsName,a.SortName,a.LinkUrl,a.Remark,a.InUserId,a.InName,a.InDate,a.CheckUId1,a.CheckName1,a.CheckContent1,a.CheckState1,a.CheckDate1,a.CheckUId2,a.CheckContent2,a.CheckState2,a.CheckDate2,a.IsAdd,a.CompanyId,a.CheckName2,CheckStateName1=((case when a.CheckState1=0 then '未审核 ' when a.CheckState1=1 then '审核通过 ' else '审核未通过 ' end)+isnull(a.CheckContent1,'')),CheckStateName2=((case when a.CheckState2=0 then '未审核 ' when a.CheckState2=1 then '审核通过 ' else '审核未通过 ' end)+isnull(a.CheckContent2,'')),IsAddState=case when a.IsAdd=1 then '已添加编号' else '未生成编号' end,GoodsCode=isnull(a.GoodsCode,''),a.ShopName,DoName=b.Name";
ser.Tables = @"XP_GoodsSelect a inner join JC_UserInfo b on a.InUserId=b.UserId";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<XP_GoodsSelectModel> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<XP_GoodsSelectModel>();
return ListModel;
}
#endregion
#region 保存选品审批
public int SaveDeptSelectCheck(XP_GoodsSelectModel Model)
{
string tsql = @"
Update [XP_GoodsSelect] set [CheckContent1]=@CheckContent1,[CheckState1]=@CheckState1,[CheckDate1]=@CheckDate1,CheckUId2=@CheckUId2,CheckName2=@CheckName2 where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@CheckContent1", DbType.String, Model.CheckContent1);
db.AddInParameter(cmd, "@CheckState1", DbType.Int32, Model.CheckState1);
db.AddInParameter(cmd, "@CheckDate1", DbType.DateTime, Model.CheckDate1);
db.AddInParameter(cmd, "@CheckUId2", DbType.Int32, Model.CheckUId2);
db.AddInParameter(cmd, "@CheckName2", DbType.String, Model.CheckName2);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存选品审批
public int SaveCompanySelectCheck(XP_GoodsSelectModel Model)
{
string tsql = @"
Update [XP_GoodsSelect] set [CheckContent2]=@CheckContent2,[CheckState2]=@CheckState2,[CheckDate2]=@CheckDate2,ShopName=@ShopName where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@CheckContent2", DbType.String, Model.CheckContent2);
db.AddInParameter(cmd, "@CheckState2", DbType.Int32, Model.CheckState2);
db.AddInParameter(cmd, "@CheckDate2", DbType.DateTime, Model.CheckDate2);
db.AddInParameter(cmd, "@ShopName", DbType.String, Model.ShopName);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 工资单
#region 导入工资
/// <summary>
/// 导入工资
/// </summary>
public int SaveSalaryInfo(GZ_SalaryInfo Model)
{
string tsql = @"
if(select count(0) from JC_UserInfo where name=@UserName and state=1)>0
begin
select top 1 @UserId=UserId from JC_UserInfo where name=@UserName and state=1
INSERT INTO [GZ_SalaryInfo]([SYear],[SMonth],[SDate],[InDate],[InUserId],[UserId],[UserIndex],[UserName],[SalaryName],[ItemName],[ItemIndex],[ItemValue])values(@SYear,@SMonth,@SDate,@InDate,@InUserId,@UserId,@UserIndex,@UserName,@SalaryName,@ItemName,@ItemIndex,@ItemValue)
set @SalaryId=@@IDENTITY
select @SalaryId
end
else
begin
select -1
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SalaryId", DbType.Int32, Model.SalaryId);
db.AddInParameter(cmd, "@SYear", DbType.Int32, Model.SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, Model.SMonth);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, Model.SDate);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserId);
db.AddInParameter(cmd, "@UserIndex", DbType.Int32, Model.UserIndex);
db.AddInParameter(cmd, "@UserName", DbType.String, Model.UserName);
db.AddInParameter(cmd, "@SalaryName", DbType.String, Model.SalaryName);
db.AddInParameter(cmd, "@ItemName", DbType.String, Model.ItemName);
db.AddInParameter(cmd, "@ItemIndex", DbType.Int32, Model.ItemIndex);
db.AddInParameter(cmd, "@ItemValue", DbType.Decimal, Model.ItemValue);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除已经导入的工资
/// <summary>
/// 删除已经导入的工资
/// </summary>
public void IsSalaryName(string UserName, string SalaryName)
{
string tsql = @"
delete from [GZ_SalaryInfo] where SalaryName=@SalaryName and UserName=@UserName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserName", DbType.String, UserName);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 删除已经导入的工资
/// <summary>
/// 删除已经导入的工资
/// </summary>
public void deleteSalaryName(string SalaryName)
{
string tsql = @"
delete from [GZ_SalaryInfo] where SalaryName=@SalaryName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 已经导入的工资
/// <summary>
/// 已经导入的工资
/// </summary>
public void updateSalaryName(DateTime indate, string SalaryName)
{
string tsql = @"
update [GZ_SalaryInfo] set indate=@indate where SalaryName=@SalaryName
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
db.AddInParameter(cmd, "@indate", DbType.DateTime, indate);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetMySalaryInfoList(int UserId, int SYear, int SMonth, int EYear, int EMonth)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from
(select username,salaryname,itemvalue=sum(itemvalue),InDate from GZ_SalaryInfo where (SYear>@SYear or (SYear=@SYear and SMonth>=@SMonth)) and (SYear<@EYear or (SYear=@EYear and SMonth<=@EMonth)) and userid=@UserId and userindex=1 group by username,salaryname,InDate
union
select username,salaryname,itemvalue=0,InDate from GZ_SalaryInfo where (SYear>@SYear or (SYear=@SYear and SMonth>=@SMonth)) and (SYear<@EYear or (SYear=@EYear and SMonth<=@EMonth)) and userid=@UserId and userindex=2 group by username,salaryname,InDate)a
order by InDate
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@EYear", DbType.Int32, EYear);
db.AddInParameter(cmd, "@EMonth", DbType.Int32, EMonth);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetAllSalaryInfoList(int SYear, int SMonth, int EYear, int EMonth)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select distinct salaryname,InDate from GZ_SalaryInfo where (SYear>@SYear or (SYear=@SYear and SMonth>=@SMonth)) and (SYear<@EYear or (SYear=@EYear and SMonth<=@EMonth)) order by InDate
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@EYear", DbType.Int32, EYear);
db.AddInParameter(cmd, "@EMonth", DbType.Int32, EMonth);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetAllSalaryYear(int UserId,int Year)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select distinct SYear,SMonth from GZ_SalaryInfo where UserId=@UserId and (@Year=0 or SYear=@Year) order by SYear Desc,SMonth desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@Year", DbType.Int32, Year);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetMySalaryInfoList(int Year, int Month,int UserId)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from GZ_SalaryInfo a where SYear=@Year and SMonth=@Month and userid=@UserId order by ItemIndex
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Year", DbType.Int32, Year);
db.AddInParameter(cmd, "@Month", DbType.Int32, Month);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetAllSalaryInfoList(int Year, int Month)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from GZ_SalaryInfo a where SYear=@Year and SMonth=@Month order by userId,ItemIndex
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Year", DbType.Int32, Year);
db.AddInParameter(cmd, "@Month", DbType.Int32, Month);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 导出个人工资单
public List<GZ_SalaryInfo> ExcelDataST(int UserId, int SYear, int SMonth, int EYear, int EMonth)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from GZ_SalaryInfo where (SYear>@SYear or (SYear=@SYear and SMonth>=@SMonth)) and (SYear<@EYear or (SYear=@EYear and SMonth<=@EMonth)) and UserId=@UserId order by InDate
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@EYear", DbType.Int32, EYear);
db.AddInParameter(cmd, "@EMonth", DbType.Int32, EMonth);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 导出一期工资单
public List<GZ_SalaryInfo> ExcelSalary(string salaryname)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from GZ_SalaryInfo where SalaryName=@salaryname order by InDate
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@salaryname", DbType.String, salaryname);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回工资列表
public List<GZ_SalaryInfo> GetUserSalaryInfoList(string salaryname)
{
List<GZ_SalaryInfo> list = null;
string tsql = @"
select * from (
select userid,username,salaryname,itemvalue=sum(itemvalue),InDate from GZ_SalaryInfo where salaryname=@salaryname and userindex=1 group by userid,username,salaryname,InDate
union
select userid,username,salaryname,itemvalue=0,InDate from GZ_SalaryInfo where salaryname=@salaryname and userindex=2 group by userid,username,salaryname,InDate)a
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@salaryname", DbType.String, salaryname);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 返回Model
public List<GZ_SalaryInfo> GetSalaryModel(int UserId, string SalaryName)
{
List<GZ_SalaryInfo> list = null;
string tsql = "select * from GZ_SalaryInfo where UserId=@UserId and SalaryName=@SalaryName";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
DataSet ds = db.ExecuteDataSet(cmd);
list = ds.Tables[0].ToList<GZ_SalaryInfo>();
return list;
}
#endregion
#region 导入
public static DataTable GZ_ImportSalaryList(int SYear, int SMonth, string SalaryName, List<GZ_SalaryInfo> list)
{
if (list != null && list.Count > 0)
{
string tsql = @"delete from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
db.ExecuteNonQuery(cmd);
tsql = @"insert into [GZ_SalaryInfo] ([SYear],[SMonth],[SDate],[InDate],[InUserId],[UserId],[UserName],[UserIndex],[SalaryName],[ItemName],[ItemIndex],[ItemValue])values(@SYear,@SMonth,@SDate,@InDate,@InUserId,@UserId,@UserName,@UserIndex,@SalaryName,@ItemName,@ItemIndex,@ItemValue);";
cmd = db.GetSqlStringCommand(tsql);
foreach (GZ_SalaryInfo item in list)
{
cmd.Parameters.Clear();
db.AddInParameter(cmd, "@SYear", DbType.Int32, item.SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, item.SMonth);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, item.SDate);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, item.InDate);
db.AddInParameter(cmd, "@InUserId", DbType.Int32, item.InUserId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, item.UserId);
db.AddInParameter(cmd, "@UserName", DbType.String, item.UserName);
db.AddInParameter(cmd, "@UserIndex", DbType.Int32, item.UserIndex);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
db.AddInParameter(cmd, "@ItemName", DbType.String, item.ItemName);
db.AddInParameter(cmd, "@ItemIndex", DbType.Int32, item.ItemIndex);
db.AddInParameter(cmd, "@ItemValue", DbType.Decimal, item.ItemValue);
db.ExecuteNonQuery(cmd);
}
tsql = "select [Mobile] from [JC_UserMobile] where [UserId] in (select [UserId] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName)";
cmd = db.GetSqlStringCommand(tsql);
cmd.Parameters.Clear();
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
return db.ExecuteDataTable(cmd);
}
else
{
return null;
}
}
#endregion
#region 读取一个用户的工资信息
public static DataSet GZ_GetOneUser(int CompanyId, int UserId, DateTime StartTime, DateTime EndTime)
{
string tsql = @"
select distinct [SalaryName],[SYear],[SMonth] from [GZ_SalaryInfo] where [UserId]=@UserId and [SDate] between @StartTime and @EndTime order by [SYear] asc,[SMonth] asc,[SalaryName] asc;
select s.[SYear],s.[SMonth],s.[SDate],s.[InDate],s.[InUserId],u.[Name] as [InUserName],s.[UserId],s.[UserName],s.[UserIndex],s.[SalaryName],s.[ItemName],s.[ItemIndex],s.[ItemValue] from [JC_UserInfo] as u,[GZ_SalaryInfo] as s where s.[InUserId]=u.[UserId] and s.[UserId]=@UserId and (s.[SDate] between @StartTime and @EndTime);";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@StartTime", DbType.DateTime, StartTime);
db.AddInParameter(cmd, "@EndTime", DbType.DateTime, EndTime);
return db.ExecuteDataSet(cmd);
}
#endregion
#region 分页读取多个用户的某个月
public static DataSet GZ_GetUsersMonth(int CompanyId, int SYear, int SMonth, string SalaryName, int PageIndex, int PageSize, out int RowsCount)
{
//sql0：记录数；sql1：所有工资项；sql2：该页用户；sql3：该页记录
string tsql = @"
select count(distinct([UserId])) as cnt from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName;
select distinct [ItemName],[ItemIndex] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName order by [ItemIndex] asc;
select distinct top " + PageSize.ToString() + @" [UserId],[UserName],[UserIndex] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName and [UserId] not in (select distinct top " + PageSize * (PageIndex - 1) + @" [UserId] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName);
select [SYear],[SMonth],[InDate],[InUserId],[UserId],[UserName],[UserIndex],[SalaryName],[ItemName],[ItemIndex],[ItemValue] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName and [UserId] in (select distinct top " + PageSize.ToString() + @" [UserId] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName and [UserId] not in (select distinct top " + PageSize * (PageIndex - 1) + @" [UserId] from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName))";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
DataSet ds = db.ExecuteDataSet(cmd);
RowsCount = Convert.ToInt32(ds.Tables[0].Rows[0]["cnt"]);
return ds;
}
#endregion
#region 读取某个用户的年份清单
public static DataTable GZ_GetUserSYearList(int UserId)
{
string tsql = @"select distinct([SYear]) from [GZ_SalaryInfo] where [UserId]=@UserId order by [SYear] desc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
return db.ExecuteDataTable(cmd);
}
#endregion
#region 读取某个用户的某年的月份清单
public static DataTable GZ_GetUserSMonthList(int UserId, int SYear)
{
string tsql = @"select distinct([SMonth]) from [GZ_SalaryInfo] where [UserId]=@UserId and [SYear]=@SYear order by [SMonth] asc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
return db.ExecuteDataTable(cmd);
}
#endregion
#region 读取用户的年份清单
public static DataTable GZ_GetSYearList()
{
string tsql = @"select distinct([SYear]) from [GZ_SalaryInfo] order by [SYear] desc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
return db.ExecuteDataTable(cmd);
}
#endregion
#region 读取用户的某年的月份清单
public static DataTable GZ_GetSMonthList(int SYear)
{
string tsql = @"select distinct([SMonth]) from [GZ_SalaryInfo] where [SYear]=@SYear order by [SMonth] asc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
return db.ExecuteDataTable(cmd);
}
#endregion
#region 读取用户的某年某月的项目清单
public static DataTable GZ_GetSalaryList(int SYear, int SMonth)
{
string tsql = @"select distinct([SalaryName]) from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth order by [SalaryName] asc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
return db.ExecuteDataTable(cmd);
}
#endregion
#region 删除某年某月某项目的信息
public static bool GZ_DeleteSalary(int SYear, int SMonth, string SalaryName)
{
string tsql = @"delete from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName;select count([SalaryId]) as cnt from [GZ_SalaryInfo] where [SYear]=@SYear and [SMonth]=@SMonth and [SalaryName]=@SalaryName;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SYear", DbType.Int32, SYear);
db.AddInParameter(cmd, "@SMonth", DbType.Int32, SMonth);
db.AddInParameter(cmd, "@SalaryName", DbType.String, SalaryName);
int cnt = Convert.ToInt32(db.ExecuteScalar(cmd));
if (cnt > 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 查询用户
public static DataTable GZ_GetUsers()
{
string tsql = @"select Name,UserName,UserId from [JC_UserInfo] where [State]=@State and [UserType]=@UserType order by [Name] asc;";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@State", DbType.Int32, 1);
db.AddInParameter(cmd, "@UserType", DbType.String, "T");
return db.ExecuteDataTable(cmd);
}
#endregion
#endregion 工资单
#region 保存weixin
public static int SaveWXUser(WX_User Model)
{
string tsql = @"
select top 1 @Id=Id from WX_User where openId=@openId
if @Id>0
begin
Update [WX_User] set [openId]=@openId,[GetMoney]=@GetMoney,[GetDate]=@GetDate where Id=@Id
end
else
begin
INSERT INTO [WX_User]([openId],[InDate],[GetMoney],[GetDate])values(@openId,@InDate,@GetMoney,@GetDate)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@openId", DbType.String, Model.openId);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@GetMoney", DbType.Decimal, Model.GetMoney);
db.AddInParameter(cmd, "@GetDate", DbType.DateTime, Model.GetDate);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证
public static int IsWeiXin(string OpenId)
{
string tsql = @"
if(select count(0) from WX_User where OpenId=@OpenId)>0
begin
select -1
end
else
begin
declare @Id int,@Hb int
set @Hb=0
select top 1 @Id=Id,@Hb=HbMoney from WX_Hb where IsUse=0
update WX_Hb set OpenId=@OpenId,InDate=getdate(),IsUse=1 where Id=@Id
select Hb=@Hb
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OpenId", DbType.String, OpenId);
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 验证
public static int UseWeiXin(string OpenId)
{
string tsql = @"
update WX_Hb set InDate=null,IsUse=0 where OpenId=@OpenId and IsUse=1
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OpenId", DbType.String, OpenId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 分页查询
public static List<JC_Log> GetListJC_Log(int LogType,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"JC_Log a
left join JC_UserInfo b on a.InUserId=b.UserId
";
if (LogType == 1)
{
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,LogContext=c.PlatOrderCode+a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"(select * from JC_Log where LogType like '%订单%')a
inner join DT_OrderInfo c on a.InnerId=c.OrderId
left join JC_UserInfo b on a.InUserId=b.UserId
";
}
else if (LogType == 2)
{
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,LogContext=c.ChaseCode+a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"(select * from JC_Log where LogType like '%采购%')a
inner join CG_Purchase c on a.InnerId=c.ChaseId
left join JC_UserInfo b on a.InUserId=b.UserId";
}
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Log> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Log>();
return ListModel;
}
#endregion
#region 分页查询
public static List<JC_Log> GetListJC_Log1(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,LogContext=c.PlatOrderCode+a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"JC_Log a
inner join DT_OrderInfo c on a.InnerId=c.OrderId
left join JC_UserInfo b on a.InUserId=b.UserId
";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Log> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Log>();
return ListModel;
}
#endregion
#region 分页查询
public static List<JC_Log> GetListJC_Log2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,LogContext=c.ChaseCode+a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"JC_Log a
inner join CG_Purchase c on a.InnerId=c.ChaseId
left join JC_UserInfo b on a.InUserId=b.UserId
";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Log> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Log>();
return ListModel;
}
#endregion
#region 分页查询
public static List<JC_Log> GetListJC_Log3(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),InUserId=ScanUserId,a.InDate,LogType='发货扫描',LogContext=a.OrderCode+'发货扫描',InnerId=a.OrderId,InName=b.Name";
ser.Tables = @"DT_TrackCodeScan a
left join DT_OrderInfo c on a.OrderId=c.OrderId
left join JC_UserInfo b on a.ScanUserId=b.UserId";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Log> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Log>();
return ListModel;
}
#endregion
#region 分页查询
public static List<JC_Log> GetListJC_Log4(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.InUserId,a.InDate,a.LogType,LogContext=isnull(c.GoodsCode,'')+'-'+isnull(c.GoodsOldCode,'')+a.LogContext,a.InnerId,InName=b.Name";
ser.Tables = @"JC_Log a
inner join HW_GoodsInfo c on a.InnerId=c.GoodsId
left join JC_UserInfo b on a.InUserId=b.UserId
";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_Log> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_Log>();
return ListModel;
}
#endregion
#region 分页查询消息提醒
public static List<JC_OpenMess> GetListJC_OpenMess(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
{
if (where == null) where = new RefParameterCollection();
QueryService ser = new QueryService();
ser.Fields = @"Id=cast(a.Id as int),a.Content,a.IsRead,a.OpenDate,a.Target,a.Type,a.UserType,a.UserId,a.Url,a.MenuName,a.InUserId,a.IsLook,ReadState=case when a.IsLook=1 then '已读' else '未读' end,a.ReadDate,RevName=b.Name,SendName=c.Name";
ser.Tables = @"JC_OpenMess a
inner join JC_UserInfo b on a.UserId=b.UserId
inner join JC_UserInfo c on a.InUserId=c.UserId
";
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
ser.PageIndex = PageIndex;
ser.PageSize = PageSize;
ser.Sort = Sort;
ser.KeyName = "Id";
string tsql = ser.GetText();
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
List<JC_OpenMess> ListModel = null;
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, where);
DataTable tb = db.ExecuteDataTable(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
ListModel = tb.ToList<JC_OpenMess>();
return ListModel;
}
#endregion
#region 已读
public static void MessRead(int Id)
{
string tsql = @"
update JC_OpenMess set IsLook=1,ReadDate=getdate() where Id=@Id
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 查询部门负责人
public List<OA_DeptCharge> GetDeptCharge(int UserId)
{
DataSet ds = new DataSet();
List<OA_DeptCharge> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"declare @DeptId int
select top 1 @DeptId=DeptId from JC_UserInfo where UserId=@UserId
select a.UserId,a.DeptId,a.DeptName,a.Name,Duty='经理' from JC_UserInfo a
inner join JC_DepartUser b on a.UserId=b.UserId
where a.State=1 and a.deptid=@DeptId and b.DeptId=@DeptId and b.duty='M'";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 查询赔款审批人
public List<OA_DeptCharge> GetDeptCharge2(int DeptId)
{
DataSet ds = new DataSet();
List<OA_DeptCharge> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 1 a.UserId,a.DeptId,a.DeptName,a.Name,Duty='经理' from JC_UserInfo a
inner join XP_Check b on a.UserId=b.CheckUserId
where b.deptid=@DeptId ";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@DeptId", DbType.Int32, DeptId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<OA_DeptCharge>();
return list;
}
#endregion
#region 返回订单总数
public List<TJ_YJOrderNum> GetSumGoodsNumList(DateTime? SDate, DateTime? EDate, DateTime? PSDate, DateTime? PEDate)
{
DataSet ds = new DataSet();
List<TJ_YJOrderNum> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select c.Price,c.DetailId,GoodsNum=isnull(SUM(c.KCNum),0) from DT_OrderGoods a
inner join DT_OrderInfo b on a.OrderId=b.OrderId
inner join HW_GoodsInDetail c on a.Id=c.OrderGoodsId
inner join HW_BuyPriceOff d on a.DetailId=d.DetailId
where b.State=2 and b.PostDate>=@PSDate and b.PostDate<@PEDate and d.InDate>=@SDate and d.InDate<@EDate group by c.Price,c.DetailId";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PSDate", DbType.DateTime, PSDate);
db.AddInParameter(cmd, "@PEDate", DbType.DateTime, PEDate);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<TJ_YJOrderNum>();
return list;
}
#endregion
#region 返回三个月最低压
public Decimal GetLowGoodsPrice(DateTime? InDate, DateTime? SDate, int DetailId,int GoodsId)
{
string tsql = @"
declare @Price decimal
set @Price=0
select @Price=MIN(a.GoodsPrice) from CG_PurchaseGoods a
inner join CG_Purchase b on a.ChaseId=b.ChaseId
where GoodsDetailId=@DetailId and b.IsDelete=0 and a.InGoodsNum>0 and InDate>=@SDate and InDate<@InDate
if @Price=0
begin
select @Price=MIN(a.GoodsPrice) from CG_PurchaseGoods a
inner join CG_Purchase b on a.ChaseId=b.ChaseId
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
where c.GoodsId=@GoodsId and b.IsDelete=0 and a.InGoodsNum>0 and InDate>=@SDate and InDate<@InDate
end
if @Price=0
begin
select top 1 @Price=a.GoodsPrice from CG_PurchaseGoods a
inner join CG_Purchase b on a.ChaseId=b.ChaseId
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
where GoodsDetailId=@DetailId and b.IsDelete=0 and a.InGoodsNum>0 and InDate<@InDate
end
select Price=isnull(@Price,0)
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, InDate);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
decimal a = Convert.ToDecimal(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 返回要压价记录
public DataTable GetLowGoodsPriceList(DateTime? SDate, DateTime? EDate)
{
string tsql = @"select c.GoodsCode,c.GoodsOldCode,c.GoodsName,b.TypeDesc,b.TypeCode,a.Price,a.GoodsId,a.DetailId,a.LowPrice,a.InDate,OffPrice=0.00,GoodsNum=0,TotalPrice=0.00 from HW_BuyPriceOff a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
where a.InDate>=@SDate and a.InDate<@EDate order by b.GoodsId,b.TypeCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable dt = db.ExecuteDataTable(cmd);
return dt;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetPostInfoList(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
// string tsql = @"
//select top 20 Id,TrackCode from WL_PostInfo where state=0 and postid not in (72,79,82,84,90,91,92,94,96,107,110,118,128) order by Id";
string tsql = @"
select top 10 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state=0 and DATEDIFF(day,ScanDate,getdate())>2 and postid not in (72,79,82,84,90,91,92,94,96,107,100,110,118,128,1,6,86,87,105,106,130,134,135) order by PostId,Id";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetPostInfoList2(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 10 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state>=3 and State<=5 and DATEDIFF(day,UpdateTime,getdate())>0 and and postid not in (72,79,82,84,90,91,92,94,96,107,100,110,118,128,1,6,86,87,105,106,130,134,135) order by PostId,Id";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetPostInfoList3(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 10 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state=1 and DATEDIFF(day,UpdateTime,getdate())>0 and and and postid not in (72,79,82,84,90,91,92,94,96,107,100,110,118,128,1,6,86,87,105,106,130,134,135) order by PostId,Id";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetPostInfoList4(DateTime? SDate, DateTime? EDate,int State,int LogisticsId, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 50 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo a
inner join JC_Express b on a.PostId=b.ExpressID
where state=@State and ScanDate>=@SDate and ScanDate<=@EDate and b.LogisticsId=@LogisticsId and (@PostId=0 or a.PostId=@PostId) order by PostId,Id";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@State", DbType.Int32, State);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
db.AddInParameter(cmd, "@LogisticsId", DbType.Int32, LogisticsId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetEUBInfoList(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 200 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state=0 and DATEDIFF(day,ScanDate,getdate())>0 and postid in (1,6,86,87,105,106,130,134,135) order by PostId,Id
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetEUBInfoList2(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 200 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state>=3 and State<=5 and DATEDIFF(day,UpdateTime,getdate())>0 and postid in (1,6,86,87,105,106,130,134,135) order by PostId,Id
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 返回物流跟踪信息
public List<WL_PostInfo> GetEUBInfoList3(DateTime? SDate, DateTime? EDate, int PostId)
{
DataSet ds = new DataSet();
List<WL_PostInfo> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select top 200 Id,TrackCode,PostDate1,PostDate2,PostDate3,PostDate4,DoneDate,State,NowState,PostId from WL_PostInfo where state=1 and DATEDIFF(day,UpdateTime,getdate())>0 and postid in (1,6,86,87,105,106,130,134,135) order by PostId,Id
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@PostId", DbType.Int32, PostId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<WL_PostInfo>();
return list;
}
#endregion
#region 保存物流跟踪信息
public int SavePostInfo(WL_PostInfo Model)
{
string tsql = @"
if @Id>0
begin
Update [WL_PostInfo] set [NowState]=@NowState,[PostInfo]=@PostInfo,[InDate]=@InDate,[UpdateTime]=@UpdateTime,[InName]=@InName,[PostDate1]=@PostDate1,[PostDate2]=@PostDate2,[PostDate3]=@PostDate3,[DoneDate]=@DoneDate,[Days]=@Days,State=@State where Id=@Id
end
else
begin
INSERT INTO [WL_PostInfo]([OrderId],[TrackCode],[PostId],[NowState],[PostInfo],[InDate],[UpdateTime],[InName],[ScanDate],[PostDate1],[PostDate2],[PostDate3],[DoneDate],[Days])values(@OrderId,@TrackCode,@PostId,@NowState,@PostInfo,@InDate,@UpdateTime,@InName,@ScanDate,@PostDate1,@PostDate2,@PostDate3,@DoneDate,@Days)
set @Id=SCOPE_IDENTITY()
end
select @Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
db.AddInParameter(cmd, "@NowState", DbType.String, Model.NowState);
db.AddInParameter(cmd, "@PostInfo", DbType.String, Model.PostInfo);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@UpdateTime", DbType.DateTime, Model.UpdateTime);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@ScanDate", DbType.DateTime, Model.ScanDate);
db.AddInParameter(cmd, "@PostDate1", DbType.DateTime, Model.PostDate1);
db.AddInParameter(cmd, "@PostDate2", DbType.DateTime, Model.PostDate2);
db.AddInParameter(cmd, "@PostDate3", DbType.DateTime, Model.PostDate3);
db.AddInParameter(cmd, "@DoneDate", DbType.DateTime, Model.DoneDate);
db.AddInParameter(cmd, "@Days", DbType.Int32, Model.Days);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存物流跟踪信息
public int SavePostInfo2(WL_PostInfo Model)
{
string tsql = @"
Update [WL_PostInfo] set [NowState]=@NowState,[PostInfo]=@PostInfo,[PostInfo1]=@PostInfo1,[PostInfo2]=@PostInfo2,[UpdateTime]=getdate(),[PostDate1]=@PostDate1,[PostDate2]=@PostDate2,[PostDate3]=@PostDate3,[DoneDate]=@DoneDate,[Days]=@Days,State=@State,getcount=isnull(getcount,0)+1,Remark=@Remark where Id=@Id
if @PostDate1 is not null
begin
update WL_PostInfo set Days= DATEDIFF (day,PostDate1,DoneDate) where State=2 and PostDate1 is not null and Id=@Id
end
else
begin
update WL_PostInfo set Days= DATEDIFF (day,ScanDate,DoneDate)-1 where State=2 and ScanDate is not null and Id=@Id
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
db.AddInParameter(cmd, "@NowState", DbType.String, Model.NowState);
db.AddInParameter(cmd, "@PostInfo", DbType.String, Model.PostInfo);
db.AddInParameter(cmd, "@PostInfo1", DbType.String, Model.PostInfo1);
db.AddInParameter(cmd, "@PostInfo2", DbType.String, Model.PostInfo2);
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
db.AddInParameter(cmd, "@UpdateTime", DbType.DateTime, Model.UpdateTime);
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
db.AddInParameter(cmd, "@ScanDate", DbType.DateTime, Model.ScanDate);
db.AddInParameter(cmd, "@PostDate1", DbType.DateTime, Model.PostDate1);
db.AddInParameter(cmd, "@PostDate2", DbType.DateTime, Model.PostDate2);
db.AddInParameter(cmd, "@PostDate3", DbType.DateTime, Model.PostDate3);
db.AddInParameter(cmd, "@DoneDate", DbType.DateTime, Model.DoneDate);
db.AddInParameter(cmd, "@Days", DbType.Int32, Model.Days);
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
return a;
}
#endregion
#region 读取首页通知
public List<GG_Notice> GetGGNoticeList(int CompanyID)
{
string tsql = @"
select top 10 a.* from GG_Notice a
inner join GG_NoticeRece b on a.Id=b.NoticeId
where b.ReceType=1 and (a.StartDate is null or a.StartDate<=GETDATE()) and (a.EndDate is null or a.EndDate>GETDATE()) and (b.ReceCompanyId=0 or b.ReceCompanyId=@CompanyId)
order by a.InDate desc";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyID);
DataTable tb = db.ExecuteDataTable(cmd);
return tb.ToList<GG_Notice>();
}
#endregion
#region 保存物流跟踪信息
public int SavePostInfo3(string TrackCode)
{
string tsql = @"
Update [WL_PostInfo] set State=1,UpdateTime=getdate() where TrackCode=@TrackCode
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@TrackCode", DbType.String, TrackCode);
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
return a;
}
#endregion
#region 保存物流跟踪信息
public int SavePostInfo4(string Ids)
{
string tsql = @"
Update [WL_PostInfo] set State=1,UpdateTime=getdate() where id in ("+Ids+")";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
return a;
}
#endregion
#region 保存物流跟踪信息
public int SavePostInfo5(int Id,int state, string error)
{
string tsql = @"
Update [WL_PostInfo] set getcount=isnull(getcount,0)+1,UpdateTime=getdate(),state=@state,ErrorInfo=@error where Id=@Id";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
db.AddInParameter(cmd, "@state", DbType.Int32, state);
db.AddInParameter(cmd, "@error", DbType.String, error);
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
return a;
}
#endregion
#region 返回物流标准
public List<JC_Express> GetExpressDays()
{
DataSet ds = new DataSet();
List<JC_Express> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select a.ExpressID,a.Name,a.DoneDays,b.Remark from JC_Express a
inner join JC_ExpressPost b on a.ExpressID=b.ExpressID
where b.IsUse=1
order by LogisticsId
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<JC_Express>();
return list;
}
#endregion
#region 返回物流Model
public WL_PostInfo GetPostInfoModel(int OrderId, string TrackCode)
{
WL_PostInfo model = null;
string tsql = "select top 1 * from WL_PostInfo where TrackCode=@TrackCode";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
db.AddInParameter(cmd, "@TrackCode", DbType.String, TrackCode);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<WL_PostInfo>();
return model;
}
#endregion
#region 保存物流标准时间
public int SaveDoneDays(int ExpressID, int DoneDays)
{
string tsql = @"
Update [JC_Express] set DoneDays=@DoneDays where ExpressID=@ExpressID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ExpressID", DbType.Int32, ExpressID);
db.AddInParameter(cmd, "@DoneDays", DbType.Int32, DoneDays);
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
return a;
}
#endregion
#region 保存会议室预约
public int SaveOrderRecord(FR_OrderRecord Model)
{
string tsql = @"
if @ID>0
begin
Update [FR_Order] set [StartTime]=@StartTime,[EndTime]=@EndTime,[InTime]=@InTime,[Description]=@Description,[IsDel]=@IsDel,[Type]=@Type,[RTId]=@RTId,[UserId]=@UserId where ID=@ID
end
else
begin
if(select count(0) from FR_Order where StartTime<=@StartTime and EndTime>=@StartTime and RTId=@RTId)>0
begin
select @ID=0
end
else
begin
INSERT INTO [FR_Order]([StartTime],[EndTime],[InTime],[Description],[IsDel],[Type],[RTId],[UserId])values(@StartTime,@EndTime,@InTime,@Description,@IsDel,@Type,@RTId,@UserId)
set @ID=SCOPE_IDENTITY()
end
end
select @ID";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ID", DbType.Int32, Model.ID);
db.AddInParameter(cmd, "@StartTime", DbType.DateTime, Model.StartTime);
db.AddInParameter(cmd, "@EndTime", DbType.DateTime, Model.EndTime);
db.AddInParameter(cmd, "@InTime", DbType.DateTime, Model.InTime);
db.AddInParameter(cmd, "@Description", DbType.String, Model.Description);
db.AddInParameter(cmd, "@IsDel", DbType.Int32, Model.IsDel);
db.AddInParameter(cmd, "@Type", DbType.Int32, Model.Type);
db.AddInParameter(cmd, "@RTId", DbType.Int32, Model.RTId);
db.AddInParameter(cmd, "@UserId", DbType.Int32, Model.UserId);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 删除会议室预约
public void DeleteOrder(int ID)
{
string tsql = @"
delete from FR_Order where ID=@ID
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@ID", DbType.Int32, ID);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回Model
public List<FR_OrderRecord> GetRoomList(int RTId)
{
List<FR_OrderRecord> list = null;
string tsql = "select a.*,b.Name from FR_Order a inner join jc_userinfo b on a.UserId=b.userId where EndTime>=getdate() and RTId=@RTId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@RTId", DbType.Int32, RTId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) list = tb.ToList<FR_OrderRecord>();
return list;
}
#endregion
#region 返回仓库剩余立方
public List<CK_StoreHouse> GetLeftSolid(int CompanyId)
{
DataSet ds = new DataSet();
List<CK_StoreHouse> list = null;
Database db = DatabaseFactory.CreateDatabase();
string tsql = @"
select d.StoreName,LeftSolid=SUM(isnull(c.Solid,0)*a.KCNum) from HW_GoodsInDetail a
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
inner join CK_StoreHouse d on a.StoreId=d.StoreId
where d.CompanyId=@CompanyId and a.CKDetailId=0 and a.KCNum>0 group by d.StoreName
";
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
list = tb.ToList<CK_StoreHouse>();
return list;
}
#endregion
#region 保存固定费用
public int SaveGDFee(int CompanyId,int Rate,decimal? GDFee)
{
string tsql = @"
Update [JC_Company] set [WeightRate]=@Rate,[PersonFee]=@GDFee where CompanyId=@CompanyId
select @CompanyId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@Rate", DbType.Int32, Rate);
db.AddInParameter(cmd, "@GDFee", DbType.Decimal, GDFee);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 保存固定费用
public int SaveFee(int Type, string OrderCode, decimal? Fee)
{
string tsql = @"
if @Type=1 --运费
begin
update a set a.BoxFee=@Fee,a.PostFee=@Fee from DT_OrderXXInfo a
inner join DT_OrderInfo b on a.OrderId=b.OrderId
where b.PlatOrderCode=@OrderCode
end
else if @Type=2 --头程
begin
update b set b.Fee3=@Fee from DT_OrderXXInfo a
inner join DT_OrderInfo b on a.OrderId=b.OrderId
where b.PlatOrderCode=@OrderCode
end
else if @Type=3 --货物成本
begin
update b set b.Fee4=@Fee from DT_OrderXXInfo a
inner join DT_OrderInfo b on a.OrderId=b.OrderId
where b.PlatOrderCode=@OrderCode
end
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@OrderCode", DbType.String, OrderCode);
db.AddInParameter(cmd, "@Type", DbType.Int32, Type);
db.AddInParameter(cmd, "@Fee", DbType.Decimal, Fee);
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
return a;
}
#endregion
#region 返回物流Model
public JC_Company GetCompanyModel(int CompanyId)
{
JC_Company model = null;
string tsql = "select top 1 * from JC_Company where CompanyId=@CompanyId";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<JC_Company>();
return model;
}
#endregion
#region 返回列表
public static List<DT_USMap> GetListUSMap()
{
//string tsql = "select a.Id,a.statename,a.Num,a.code,a.color,a.Price from DT_USMap";
string tsql = "select *,value=Num,name=code from DT_USMap";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_USMap> ListModel = tb.ToList<DT_USMap>();
return ListModel;
}
#endregion
#region 返回列表
public static void SetData()
{
//string tsql = "select a.Id,a.statename,a.Num,a.code,a.color,a.Price from DT_USMap";
string tsql = @"update a set a.RevProvinceCode=b.code from DT_OrderXXInfo a
inner join DT_USMap b on a.RevProvince=b.ename
where a.CountryCode='US' and a.RevProvinceCode is null
update a set a.RevProvinceCode=b.code from DT_OrderXXInfo a
inner join DT_USMap b on a.RevProvince=b.code
where a.CountryCode='US' and a.RevProvinceCode is null";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.ExecuteNonQuery(cmd);
}
#endregion
#region 返回地图订单合计
public static DT_USMap GetListUSMapModel(string GoodsCode, DateTime? SDate, DateTime? EDate)
{
DT_USMap model = null;
string tsql = @"
select Num=COUNT(0),Price=SUM(a.totalprice) from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
where b.CountryCode='US' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3";
if (GoodsCode != "")
{
tsql = @"select Num=COUNT(0),Price=SUM(a.totalprice) from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
inner join DT_OrderGoods c on a.OrderId=c.OrderId
inner join HW_GoodsDetail d on c.DetailId=d.DetailId
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
where b.CountryCode='US' and e.GoodsCode like '%'+@GoodsCode+'%' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_USMap>();
return model;
}
#endregion
#region 返回列表
public static List<DT_USMap> GetListUSOrder(string GoodsCode, DateTime? SDate, DateTime? EDate)
{
//string tsql = "select a.Id,a.statename,a.Num,a.code,a.color,a.Price from DT_USMap";
string tsql = @"
select a.Id,a.statename,a.ename,name=a.code,a.code,value=isnull(b.Num,1),num=isnull(b.Num,0),Price=isnull(b.Price,0) from DT_USMap a
left join (
select b.RevProvinceCode,Num=COUNT(0),Price=SUM(a.totalprice) from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
where b.CountryCode='US' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3 group by b.RevProvinceCode)b on a.code=b.RevProvinceCode
order by isnull(b.Num,0) desc,isnull(b.Price,0) desc";
if (GoodsCode != "")
{
tsql = @"select a.Id,a.statename,a.ename,name=a.code,a.code,value=isnull(b.Num,0),num=isnull(b.Num,0),Price=isnull(b.Price,0) from DT_USMap a
left join (
select b.RevProvinceCode,Num=COUNT(0),Price=SUM(a.totalprice) from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
inner join DT_OrderGoods c on a.OrderId=c.OrderId
inner join HW_GoodsDetail d on c.DetailId=d.DetailId
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
where b.CountryCode='US' and e.GoodsCode like '%'+@GoodsCode+'%' and a.OrderDate>=@SDate and a.OrderDate<@EDate and a.State>0 and a.State<3 group by b.RevProvinceCode)b on a.code=b.RevProvinceCode
order by isnull(b.Num,0) desc,isnull(b.Price,0) desc";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
DataTable tb = db.ExecuteDataTable(cmd);
List<DT_USMap> ListModel = tb.ToList<DT_USMap>();
return ListModel;
}
#endregion
#region 验证新订单
public static DT_Order IsNewOrder(DateTime Sdate)
{
DT_Order model = null;
string tsql = @"
select top 1 a.InDate from DT_OrderInfo a
inner join DT_OrderXXInfo b on a.OrderId=b.OrderId
where b.CountryCode='US' and a.InDate>@SDate and a.State>0 and a.State<3 order by a.InDate desc
";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@Sdate", DbType.String, Sdate);
DataTable tb = db.ExecuteDataTable(cmd);
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<DT_Order>();
return model;
}
#endregion
#region 返回费用
public static List<CK_FeeReport> GetListFeeReport(int CompanyId,int FeeType, DateTime? SDate, DateTime? EDate)
{
//string tsql = "select a.Id,a.statename,a.Num,a.code,a.color,a.Price from DT_USMap";
string tsql = @"
select a.*,FeeName=b.Name from CK_FeeReport a
inner join JC_BaseCodeDetail b on a.FeeType=b.SortNo and b.KeyName='FYLX'
where a.CompanyId=@CompanyId and (@FeeType=0 or FeeType=@FeeType) and Indate>=@SDate and Indate<@EDate order by Indate desc,FeeType";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(tsql);
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
db.AddInParameter(cmd, "@SDate", DbType.DateTime, SDate);
db.AddInParameter(cmd, "@EDate", DbType.DateTime, EDate);
db.AddInParameter(cmd, "@FeeType", DbType.Int32, FeeType);
DataTable tb = db.ExecuteDataTable(cmd);
List<CK_FeeReport> ListModel = tb.ToList<CK_FeeReport>();
return ListModel;
}
#endregion
public static List<CK_FeeReport> GetListFeeReport2(int CompanyId, int FeeType, DateTime? SDate, DateTime? EDate)
{
string query = "select CompanyId,FeeType,TotalFee=SUM(TotalFee) from CK_FeeReport where (@CompanyId=0 or CompanyId=@CompanyId) and Indate>=@SDate and Indate<@EDate group by CompanyId,FeeType order by CompanyId";
Database database = DatabaseFactory.CreateDatabase();
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, CompanyId);
database.AddInParameter(sqlStringCommand, "@SDate", DbType.DateTime, SDate);
database.AddInParameter(sqlStringCommand, "@EDate", DbType.DateTime, EDate);
database.AddInParameter(sqlStringCommand, "@FeeType", DbType.Int32, FeeType);
return database.ExecuteDataTable(sqlStringCommand).ToList<CK_FeeReport>();
}
}
}
Reference in New Issue View Git Blame Copy Permalink