|
|
using System;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Data;
|
|
|
using System.Data.Common;
|
|
|
using System.Linq;
|
|
|
using System.Text;
|
|
|
using NetLibrary;
|
|
|
using NetLibrary.Data;
|
|
|
using TradeModel;
|
|
|
|
|
|
namespace TradeData
|
|
|
{
|
|
|
public class CK_HouseData
|
|
|
{
|
|
|
#region 保存
|
|
|
/// <summary>
|
|
|
/// 保存
|
|
|
/// </summary>
|
|
|
public int SaveStoreHouse(CK_StoreHouse Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @StoreId>0
|
|
|
begin
|
|
|
Update [CK_StoreHouse] set [StoreName]=@StoreName,[Remark]=@Remark,[CompanyId]=@CompanyId,[StoreType]=@StoreType,[LevelGrade]=@LevelGrade,[IsUse]=@IsUse,[UseShop]=@UseShop,[UseCountry]=@UseCountry,[IsDefault]=@IsDefault,StoreCode=@StoreCode where StoreId=@StoreId
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_StoreHouse]([StoreName],[Remark],[CompanyId],[StoreType],[LevelGrade],[IsUse],[UseShop],[UseCountry],[IsDefault],StoreCode)values(@StoreName,@Remark,@CompanyId,@StoreType,@LevelGrade,@IsUse,@UseShop,@UseCountry,@IsDefault,@StoreCode)
|
|
|
set @StoreId=SCOPE_IDENTITY()
|
|
|
end
|
|
|
if @IsDefault=1
|
|
|
begin
|
|
|
Update [CK_StoreHouse] set IsDefault=0 where StoreId<>@StoreId and CompanyId=@CompanyId
|
|
|
end
|
|
|
else if @IsDefault=0
|
|
|
begin
|
|
|
if(select count(0) from CK_StoreHouse where CompanyId=@CompanyId and isuse=1 and IsDefault=1)=0
|
|
|
begin
|
|
|
Update [CK_StoreHouse] set IsDefault=1 where StoreId in (select top 1 StoreId from CK_StoreHouse where CompanyId=@CompanyId and isuse=1 order by LevelGrade desc)
|
|
|
end
|
|
|
end
|
|
|
select @StoreId";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
|
db.AddInParameter(cmd, "@StoreCode", DbType.String, Model.StoreCode);
|
|
|
db.AddInParameter(cmd, "@StoreName", DbType.String, Model.StoreName);
|
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
|
db.AddInParameter(cmd, "@StoreType", DbType.Int32, Model.StoreType);
|
|
|
db.AddInParameter(cmd, "@LevelGrade", DbType.Int32, Model.LevelGrade);
|
|
|
db.AddInParameter(cmd, "@IsUse", DbType.Int32, Model.IsUse);
|
|
|
db.AddInParameter(cmd, "@UseShop", DbType.String, Model.UseShop);
|
|
|
db.AddInParameter(cmd, "@UseCountry", DbType.String, Model.UseCountry);
|
|
|
db.AddInParameter(cmd, "@IsDefault", DbType.Int32, Model.IsDefault);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 删除
|
|
|
public void DeleteStoreHouse(int StoreId)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
delete from CK_StoreHouse where StoreId=@StoreId
|
|
|
delete from CK_StorePostion where StoreId=@StoreId
|
|
|
|
|
|
update b set b.GoodsNum=b.GoodsNum-c.GoodsNum from HW_GoodsDetail b
|
|
|
inner join HW_GoodsPostion c on b.detailid=c.detailid
|
|
|
where c.StoreId=@StoreId
|
|
|
|
|
|
update d set d.GoodsNum=d.GoodsNum-c.GoodsNum from HW_GoodsDetail b
|
|
|
inner join HW_GoodsPostion c on b.detailid=c.detailid
|
|
|
inner join HW_GoodsInfo d on b.GoodsId=d.GoodsId
|
|
|
where c.StoreId=@StoreId
|
|
|
delete from HW_GoodsPostion where StoreId=@StoreId
|
|
|
|
|
|
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
}
|
|
|
#endregion
|
|
|
#region 判断仓库有没有货物
|
|
|
public int IsStore(int StoreId)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
|
|
|
select count(0) from HW_GoodsDetail b
|
|
|
inner join HW_GoodsInList c on b.detailid=c.detailid
|
|
|
inner join HW_GoodsInfo d on b.GoodsId=d.GoodsId
|
|
|
where c.StoreId=@StoreId and (c.GoodsNum>0 or c.GoodsLockNum>0) and d.state=1
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 判断仓库编码
|
|
|
public int IsStoreCode(int StoreId,string StoreCode)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
select count(0) from CK_StoreHouse
|
|
|
where StoreId<>@StoreId and StoreCode=@StoreCode
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@StoreCode", DbType.String, StoreCode);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 删除库位
|
|
|
public void DeleteStorePostion(int PostionId)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
delete from CK_StorePostion where PostionId=@PostionId
|
|
|
update HW_GoodsPostion set PostionId=0 where PostionId=@PostionId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 判断库位编码
|
|
|
public int IsPostionCode(int PostionId, string PostionCode)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
select count(0) from CK_StorePostion
|
|
|
where PostionId<>@PostionId and PostionCode=@PostionCode
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 判断库位编码
|
|
|
public int IsStorePostionCode(int StoreId, string PostionCode)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
declare @PostionId int
|
|
|
set @PostionId=0
|
|
|
select @PostionId=PostionId from CK_StorePostion
|
|
|
where (@StoreId=0 or StoreId=@StoreId) and PostionCode=@PostionCode
|
|
|
select @PostionId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 保存库位
|
|
|
public int SaveGoodsPostion(CK_StorePostion Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @PostionId>0
|
|
|
begin
|
|
|
Update [CK_StorePostion] set [StoreId]=@StoreId,[PostionCode]=@PostionCode,[PostionDesc]=@PostionDesc,[Remark]=@Remark,IsLS=@IsLS where PostionId=@PostionId
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_StorePostion]([StoreId],[PostionCode],[PostionDesc],[Remark],IsLS)values(@StoreId,@PostionCode,@PostionDesc,@Remark,@IsLS)
|
|
|
set @PostionId=SCOPE_IDENTITY()
|
|
|
end
|
|
|
select @PostionId";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
|
db.AddInParameter(cmd, "@IsLS", DbType.Int32, Model.IsLS);
|
|
|
db.AddInParameter(cmd, "@PostionDesc", DbType.String, Model.PostionDesc);
|
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 保存库位
|
|
|
public int SaveGoodsPostion2(CK_StorePostion Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @PostionId>0
|
|
|
begin
|
|
|
Update [CK_StorePostion] set [StoreId]=@StoreId,[PostionCode]=@PostionCode,[PostionDesc]=@PostionDesc,[Remark]=@Remark,IsLS=@IsLS,SortNo=@SortNo,PostionBox=@PostionBox where PostionId=@PostionId
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_StorePostion]([StoreId],[PostionCode],[PostionDesc],[Remark],IsLS,SortNo,PostionBox)values(@StoreId,@PostionCode,@PostionDesc,@Remark,@IsLS,@SortNo,@PostionBox)
|
|
|
set @PostionId=SCOPE_IDENTITY()
|
|
|
end
|
|
|
select @PostionId";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
|
db.AddInParameter(cmd, "@IsLS", DbType.Int32, Model.IsLS);
|
|
|
db.AddInParameter(cmd, "@PostionDesc", DbType.String, Model.PostionDesc);
|
|
|
db.AddInParameter(cmd, "@PostionBox", DbType.String, Model.PostionBox);
|
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
|
db.AddInParameter(cmd, "@SortNo", DbType.String, Model.SortNo);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 分页查询库位
|
|
|
public List<CK_StorePostion> GetStorePostionList(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
if (where == null) where = new RefParameterCollection();
|
|
|
QueryService ser = new QueryService();
|
|
|
ser.Fields = @"PostionId=cast(a.PostionId as int),a.StoreId,a.PostionCode,a.PostionDesc,a.Remark,a.IsLS,IsLSState=case when a.IsLS=1 then '是' else '否' end,a.SortNo,a.PostionBox";
|
|
|
ser.Tables = @"CK_StorePostion a";
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
|
ser.PageIndex = PageIndex;
|
|
|
ser.PageSize = PageSize;
|
|
|
ser.Sort = Sort;
|
|
|
ser.KeyName = "PostionId";
|
|
|
string tsql = ser.GetText();
|
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
|
List<CK_StorePostion> 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<CK_StorePostion>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 开启关闭
|
|
|
public void UpdateStoreHouse(int StoreId, int IsUse)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
update CK_StoreHouse set IsUse=@IsUse where StoreId=@StoreId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@IsUse", DbType.Int32, IsUse);
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
}
|
|
|
#endregion
|
|
|
#region 返回Model
|
|
|
public CK_StoreHouse GetStoreHouseModel(int StoreId)
|
|
|
{
|
|
|
CK_StoreHouse model = null;
|
|
|
string tsql = "select * from CK_StoreHouse where StoreId=@StoreId";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CK_StoreHouse>();
|
|
|
return model;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 分页查询
|
|
|
public List<CK_StoreHouse> GetListCK_StoreHouse(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
if (where == null) where = new RefParameterCollection();
|
|
|
QueryService ser = new QueryService();
|
|
|
ser.Fields = @"StoreId=cast(a.StoreId as int),a.StoreName,a.Remark,a.CompanyId,a.StoreType,a.LevelGrade,a.IsUse,a.UsePlat";
|
|
|
ser.Tables = @"CK_StoreHouse a";
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
|
ser.PageIndex = PageIndex;
|
|
|
ser.PageSize = PageSize;
|
|
|
ser.Sort = Sort;
|
|
|
ser.KeyName = "StoreId";
|
|
|
string tsql = ser.GetText();
|
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
|
List<CK_StoreHouse> 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<CK_StoreHouse>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 查询
|
|
|
public List<CK_StoreHouse> GetStoreHouseList(int CompanyId)
|
|
|
{
|
|
|
|
|
|
List<CK_StoreHouse> list = null;
|
|
|
string tsql = @"
|
|
|
select *,StoreTypeName=case when StoreType=1 then '自有仓' when StoreType=2 then '自建海外仓' when StoreType=3 then '亚马逊FBA' when StoreType=4 then '第三方海外仓' end,UseState=case when IsUse=1 then '开启' else '关闭' end,BackState=case when IsUse=1 then '关闭' else '开启' end,CKDefault=case when IsDefault=1 then '是' else '' end from CK_StoreHouse where CompanyId=@CompanyId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StoreHouse>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
#region 查询
|
|
|
public List<CK_StoreHouse> GetUseStoreHouse(int CompanyId)
|
|
|
{
|
|
|
|
|
|
List<CK_StoreHouse> list = null;
|
|
|
string tsql = @"
|
|
|
select StoreId,StoreName,IsDefault,StoreType from CK_StoreHouse where CompanyId=@CompanyId and IsUse=1 order by IsDefault desc
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StoreHouse>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 查询
|
|
|
public List<CK_StorePostion> GetStorePostion(int StoreId)
|
|
|
{
|
|
|
|
|
|
List<CK_StorePostion> list = null;
|
|
|
string tsql = @"
|
|
|
select * from CK_StorePostion where StoreId=@StoreId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StorePostion>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 查询
|
|
|
public List<CK_StorePostion> GetStorePostionForTM(int StoreId, int IsLS)
|
|
|
{
|
|
|
|
|
|
List<CK_StorePostion> list = null;
|
|
|
string tsql = @"
|
|
|
select * from CK_StorePostion where (@StoreId=0 or StoreId=@StoreId) and (@IsLS=0 or IsLS=@IsLS) order by PostionCode
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@IsLS", DbType.Int32, IsLS);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StorePostion>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
#region 开启关闭
|
|
|
public int UpdatePostionCode(int StoreId, int IsMH,string OldCode,string NewCode)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @IsMH=0
|
|
|
begin
|
|
|
update CK_StorePostion set PostionCode=@NewCode where StoreId=@StoreId and PostionCode=@OldCode
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
update CK_StorePostion set PostionCode=@NewCode+substring(PostionCode,len(@OldCode)+1,len(PostionCode)-len(@OldCode)) where StoreId=@StoreId and PostionCode like @OldCode+'%'
|
|
|
end
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@IsMH", DbType.Int32, IsMH);
|
|
|
db.AddInParameter(cmd, "@OldCode", DbType.String, OldCode);
|
|
|
db.AddInParameter(cmd, "@NewCode", DbType.String, NewCode);
|
|
|
int a=db.ExecuteNonQuery(cmd);
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 移库
|
|
|
public int UpdateGoodsPostionCode(int StoreId,int DetailId, int PostionId, string PostionCode, int GoodsNum)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
declare @NewPostionId int,@NewStoreId int
|
|
|
select top 1 @NewPostionId=PostionId,@NewStoreId=StoreId from CK_StorePostion where PostionCode=@PostionCode
|
|
|
--while @GoodsNum>0 and @NewPostionId>0
|
|
|
--begin
|
|
|
--update HW_GoodsInDetail set PostionId=@NewPostionId,UpDateTime=getdate() where inid in (select top 1 InId from HW_GoodsInDetail where StoreId=@StoreId and PostionId=@PostionId and DetailId=@DetailId and OrderGoodsId=0 order by InId)
|
|
|
--set @GoodsNum=@GoodsNum-1
|
|
|
--end
|
|
|
|
|
|
|
|
|
|
|
|
declare @OldInId int,@InId int,@KCNum int
|
|
|
|
|
|
while @GoodsNum>0
|
|
|
begin
|
|
|
set @OldInId=0
|
|
|
select top 1 @OldInId=InId,@KCNum=KCNum from HW_GoodsInDetail where OrderGoodsId=0 and PostionId=@PostionId and DetailId=@DetailId
|
|
|
if @OldInId>0 and @GoodsNum>=@KCNum
|
|
|
begin
|
|
|
update HW_GoodsInDetail set StoreId=@NewStoreId,PostionId=@NewPostionId where InId=@OldInId
|
|
|
end
|
|
|
else if @OldInId>0 and @GoodsNum<@KCNum
|
|
|
begin
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[KCNum],[UpDateTime],[RKDetailId],[CKDetailId],CompanyId)
|
|
|
select [DetailId],[ChaseId],StoreId=@NewStoreId,PostionId=@NewPostionId,[Price],[PostPrice],[InDate],[InUserId],OrderGoodsId,KCNum=@GoodsNum,getdate(),[RKDetailId],CKDetailId,CompanyId from [HW_GoodsInDetail] where InId=@OldInId
|
|
|
update HW_GoodsInDetail set KCNum=KCNum-@GoodsNum where InId=@OldInId
|
|
|
end
|
|
|
|
|
|
if @OldInId=0
|
|
|
begin
|
|
|
set @GoodsNum=0
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
set @GoodsNum=@GoodsNum-@KCNum
|
|
|
end
|
|
|
end
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.String, GoodsNum);
|
|
|
int a = db.ExecuteNonQuery(cmd);
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 剩余的出库
|
|
|
public int UpdateGoodsOut(int CKDetailId,int StoreId, int DetailId, int PostionId, int GoodsNum)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
while @GoodsNum>0
|
|
|
begin
|
|
|
update HW_GoodsInDetail set OrderGoodsId=1,UpDateTime=getdate(),CKDetailId=@CKDetailId where inid in (select top 1 InId from HW_GoodsInDetail where StoreId=@StoreId and PostionId=@PostionId and DetailId=@DetailId and OrderGoodsId=0 order by InId)
|
|
|
set @GoodsNum=@GoodsNum-1
|
|
|
end
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CKDetailId", DbType.Int32, CKDetailId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.String, GoodsNum);
|
|
|
int a = db.ExecuteNonQuery(cmd);
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 查询库位
|
|
|
public CK_GoodsPostionMd GetStorePostionId(int CompanyId,string PostionCode, string GoodsCode,string TypeCode)
|
|
|
{
|
|
|
|
|
|
CK_GoodsPostionMd model = null;
|
|
|
string tsql = @"
|
|
|
declare @Id int,@SType int,@StoreId int,@TypeDesc nvarchar(200),@GoodsName nvarchar(200)
|
|
|
if @GoodsCode='' and @TypeCode=''
|
|
|
begin
|
|
|
select top 1 @Id=a.PostionId,@StoreId=a.StoreId,@SType=1 from CK_StorePostion a
|
|
|
inner join CK_StoreHouse b on a.StoreId=b.StoreId
|
|
|
where b.CompanyId=@CompanyId and a.PostionCode=@PostionCode
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
select top 1 @Id=DetailId,@StoreId=0,@SType=2,@TypeDesc=a.TypeDesc,@GoodsName=b.GoodsName from HW_GoodsDetail a
|
|
|
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
|
|
|
where b.CompanyId=@CompanyId and (b.GoodsCode=@GoodsCode or b.GoodsOldCode=@GoodsCode) and a.TypeCode=@TypeCode
|
|
|
end
|
|
|
select Id=@Id,SType=@SType,StoreId=@StoreId,GoodsName=@GoodsName,TypeDesc=@TypeDesc
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
|
|
|
db.AddInParameter(cmd, "@TypeCode", DbType.String, TypeCode);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CK_GoodsPostionMd>();
|
|
|
return model;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
#region 查询货物
|
|
|
public int GetStoreGoods(string GoodsCode, string TypeCode)
|
|
|
{
|
|
|
|
|
|
|
|
|
string tsql = @"
|
|
|
declare @Id int
|
|
|
set @Id =0
|
|
|
select top 1 @Id=a.DetailId from HW_GoodsDetail a
|
|
|
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
|
|
|
where (b.GoodsCode=@GoodsCode or b.GoodsOldCode=@GoodsCode) and a.TypeCode=@TypeCode
|
|
|
select @Id
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
|
|
|
db.AddInParameter(cmd, "@TypeCode", DbType.String, TypeCode);
|
|
|
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 查询库位库存
|
|
|
public PostionSku GetStoreGoodsNum(int PostionId, int DetailId,int GoodsNum)
|
|
|
{
|
|
|
|
|
|
PostionSku model = null;
|
|
|
string tsql = @"
|
|
|
declare @KCNum int,@LockNum int
|
|
|
set @LockNum=0
|
|
|
set @KCNum=0
|
|
|
select @KCNum=isnull(sum(kcnum),0) from HW_GoodsInDetail where DetailId=@DetailId and PostionId=@PostionId and OrderGoodsId=0
|
|
|
if @KCNum<>@GoodsNum
|
|
|
begin
|
|
|
select @LockNum=isnull(sum(kcnum),0) from HW_GoodsInDetail where DetailId=@DetailId and PostionId=@PostionId and OrderGoodsId>0 and isnull(CKDetailId,0)=0
|
|
|
select KCNum=@KCNum,LockNum=@LockNum
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
select KCNum=-1,LockNum=-1
|
|
|
end
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<PostionSku>();
|
|
|
return model;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 查询扫描库位库存
|
|
|
public List<HwPostionSku> GetStoreScanGoodsKCNum(int PostionId)
|
|
|
{
|
|
|
|
|
|
List<HwPostionSku> list = null;
|
|
|
string tsql = @"
|
|
|
select a.DetailId,c.GoodsName,c.GoodsCode,c.GoodsOldCode,b.TypeCode,b.TypeDesc,a.KCNum,IsBJ=0 from (
|
|
|
select DetailId,KCNum=SUM(kcnum) from HW_GoodsInDetail where OrderGoodsId=0 and PostionId=@PostionId group by DetailId)a
|
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<HwPostionSku>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
#region 查询扫描库位库存
|
|
|
public List<HwPostionSku> GetStoreScanGoodsLockNum(int PostionId)
|
|
|
{
|
|
|
|
|
|
List<HwPostionSku> list = null;
|
|
|
string tsql = @"
|
|
|
select a.DetailId,c.GoodsName,c.GoodsCode,c.GoodsOldCode,b.TypeCode,b.TypeDesc,a.LockNum,IsBJ=0 from (
|
|
|
select DetailId,LockNum=SUM(kcnum) from HW_GoodsInDetail where OrderGoodsId<>0 and isnull(CKDetailId,0)=0 and PostionId=@PostionId group by DetailId)a
|
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<HwPostionSku>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
#region sku入库位
|
|
|
public int UpdateSKUToPostion(int DetailId, int PostionId, int StoreId, int OldPostionId)
|
|
|
{
|
|
|
|
|
|
|
|
|
string tsql = @"
|
|
|
update HW_GoodsInDetail set StoreId=@StoreId,PostionId=@PostionId where InId in (select top 1 InId from HW_GoodsInDetail where DetailId=@DetailId and PostionId=@OldPostionId and OrderGoodsId=0)
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@OldPostionId", DbType.Int32, OldPostionId);
|
|
|
int a = Convert.ToInt32(db.ExecuteNonQuery(cmd));
|
|
|
return a;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region sku入库位
|
|
|
public int UpdateSKUToPostion2(int DetailId, int PostionId, int StoreId, int OldPostionId)
|
|
|
{
|
|
|
|
|
|
|
|
|
string tsql = @"
|
|
|
declare @OldInId int,@InId int,@KCNum int
|
|
|
set @InId=0
|
|
|
set @OldInId=0
|
|
|
select @OldInId=InId,@KCNum=KCNum from HW_GoodsInDetail where PostionId=@OldPostionId and DetailId=@DetailId and OrderGoodsId=0
|
|
|
select @InId=InId from HW_GoodsInDetail where PostionId=@PostionId and DetailId=@DetailId and OrderGoodsId=0
|
|
|
if @InId>0 and @OldInId>0
|
|
|
begin
|
|
|
update HW_GoodsInDetail set KCNum=KCNum+1 where InId=@InId
|
|
|
if @KCNum=1
|
|
|
begin
|
|
|
update HW_GoodsInDetail set StoreId=@StoreId,PostionId=@PostionId where InId=@OldInId
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
update HW_GoodsInDetail set KCNum=KCNum-1 where InId=@OldInId
|
|
|
end
|
|
|
select 1
|
|
|
end
|
|
|
else if @InId=0 and @OldInId>0
|
|
|
begin
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[KCNum],[UpDateTime],[RKDetailId],[CKDetailId])
|
|
|
select [DetailId],[ChaseId],StoreId=@StoreId,PostionId=@PostionId,[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[KCNum]=1,[UpDateTime],[RKDetailId],[CKDetailId] from [HW_GoodsInDetail] where InId=@OldInId
|
|
|
if @KCNum=1
|
|
|
begin
|
|
|
update HW_GoodsInDetail set StoreId=@StoreId,PostionId=@PostionId where InId=@OldInId
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
update HW_GoodsInDetail set KCNum=KCNum-1 where InId=@OldInId
|
|
|
end
|
|
|
select 1
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
select 0
|
|
|
end
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@OldPostionId", DbType.Int32, OldPostionId);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region sku入库位
|
|
|
public int UpdateSKUToPostion3(int DetailId, int PostionId, int StoreId, int OldPostionId,int GoodsNum)
|
|
|
{
|
|
|
|
|
|
|
|
|
string tsql = @"
|
|
|
declare @OldInId int,@InId int,@KCNum int,@Flag int
|
|
|
set @Flag=1
|
|
|
while @GoodsNum>0
|
|
|
begin
|
|
|
set @OldInId=0
|
|
|
select top 1 @OldInId=InId,@KCNum=KCNum from HW_GoodsInDetail where PostionId=@OldPostionId and OrderGoodsId=0 and DetailId=@DetailId
|
|
|
if @OldInId>0 and @GoodsNum=@KCNum
|
|
|
begin
|
|
|
update HW_GoodsInDetail set StoreId=@StoreId,PostionId=@PostionId where InId=@OldInId
|
|
|
set @GoodsNum=0
|
|
|
end
|
|
|
else if @OldInId>0 and @GoodsNum<@KCNum
|
|
|
begin
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[KCNum],[UpDateTime],[RKDetailId],[CKDetailId])
|
|
|
select [DetailId],[ChaseId],StoreId=@StoreId,PostionId=@PostionId,[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],KCNum=@GoodsNum,[UpDateTime],[RKDetailId],[CKDetailId] from [HW_GoodsInDetail] where InId=@OldInId
|
|
|
update HW_GoodsInDetail set KCNum=KCNum-@GoodsNum where InId=@OldInId
|
|
|
set @GoodsNum=0
|
|
|
end
|
|
|
else if @OldInId>0 and @GoodsNum>@KCNum
|
|
|
begin
|
|
|
update HW_GoodsInDetail set StoreId=@StoreId,PostionId=@PostionId where InId=@OldInId
|
|
|
set @GoodsNum=@GoodsNum-@KCNum
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
set @GoodsNum=0
|
|
|
set @Flag=0
|
|
|
end
|
|
|
end
|
|
|
select Flag=@Flag
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@OldPostionId", DbType.Int32, OldPostionId);
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region sku入库位
|
|
|
public int UpdateSKUToPostion4(int DetailId, int PostionId, int StoreId, int GoodsNum)
|
|
|
{
|
|
|
|
|
|
|
|
|
string tsql = @"
|
|
|
declare @Price money,@PostPrice money
|
|
|
set @Price=0
|
|
|
set @PostPrice=0
|
|
|
select top 1 @Price=Price,@PostPrice=PostPrice from HW_GoodsInDetail where Price>0 and DetailId=@DetailId order by InDate desc
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[KCNum],[UpDateTime],[RKDetailId],[CKDetailId],OldNum)
|
|
|
select @DetailId,0,StoreId=@StoreId,PostionId=@PostionId,@Price,@PostPrice,getdate(),0,0,KCNum=@GoodsNum,null,323,0,@GoodsNum
|
|
|
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 查询库存
|
|
|
public List<CK_StorePostionNew> GetStoreHouseNew(int CompanyId, int StoreId, string PostionBox)
|
|
|
{
|
|
|
|
|
|
List<CK_StorePostionNew> list = null;
|
|
|
string tsql = @"
|
|
|
select a.* from CK_StorePostion a
|
|
|
where (@CompanyId=0 or a.CompanyId=@CompanyId) and (@StoreId=0 or a.StoreId=@StoreId) and (@PostionBox='' or PostionBox like '%'+@PostionBox+'%')
|
|
|
order by a.PostionBox,a.SortNo";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionBox", DbType.String, PostionBox);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StorePostionNew>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 查询库存
|
|
|
public List<CK_StorePostionNew> GetStoreHouseGoodsNew(int CompanyId, int StoreId, string PostionBox)
|
|
|
{
|
|
|
|
|
|
List<CK_StorePostionNew> list = null;
|
|
|
string tsql = @"
|
|
|
select a.PostionId,e.GoodsCode,FirstImgUrl=case when e.FirstImgUrl is not null and e.FirstImgUrl<>'' then e.FirstImgUrl when d.FirstImgUrl is not null and d.FirstImgUrl<>'' then d.FirstImgUrl else '../image/noimage.gif' end,kcnum=isnull(a.kcnum,0) from HW_GoodsInDetail2 a
|
|
|
inner join HW_GoodsDetail d on a.detailid=d.DetailId
|
|
|
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
|
|
|
inner join CK_StorePostion f on a.PostionId=f.PostionId
|
|
|
where a.CompanyId=@CompanyId and (@StoreId=0 or f.StoreId=@StoreId) and (@PostionBox='' or f.PostionBox like '%'+@PostionBox+'%')
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
|
db.AddInParameter(cmd, "@PostionBox", DbType.String, PostionBox);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
list = ds.Tables[0].ToList<CK_StorePostionNew>();
|
|
|
return list;
|
|
|
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 保存
|
|
|
public static int Save_BackGoods(DT_BackGoods Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @Id>0
|
|
|
begin
|
|
|
Update [DT_BackGoods] set [InDate]=@InDate,[InUserId]=@InUserId,[ImgIds]=@ImgIds,[TrackCode]=@TrackCode,[OrderCode]=@OrderCode,[GoodsState]=@GoodsState,[State]=@State,[ImgUrl]=@ImgUrl where Id=@Id
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [DT_BackGoods]([InDate],[InUserId],[ImgIds],[TrackCode],[OrderCode],[GoodsState],[State],[ImgUrl])values(@InDate,@InUserId,@ImgIds,@TrackCode,@OrderCode,@GoodsState,@State,@ImgUrl)
|
|
|
set @Id=SCOPE_IDENTITY()
|
|
|
end
|
|
|
if @OrderCode<>''
|
|
|
begin
|
|
|
update DT_OrderBackMoneyApply set PostCode=@TrackCode,BackState=@GoodsState where OrderCode=@OrderCode
|
|
|
end
|
|
|
select @Id";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
|
db.AddInParameter(cmd, "@ImgIds", DbType.String, Model.ImgIds);
|
|
|
db.AddInParameter(cmd, "@TrackCode", DbType.String, Model.TrackCode);
|
|
|
db.AddInParameter(cmd, "@OrderCode", DbType.String, Model.OrderCode);
|
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
|
db.AddInParameter(cmd, "@ImgUrl", DbType.String, Model.ImgUrl);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 删除
|
|
|
public static void Delete_BackGoods(int Id)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
delete from DT_BackGoods 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 static DT_BackGoods GetModel_BackGoods(int Id)
|
|
|
{
|
|
|
DT_BackGoods model = null;
|
|
|
//string tsql = "select a.Id,a.InDate,a.InUserId,a.ImgIds,a.TrackCode,a.OrderCode,a.GoodsState,a.State,a.ImgUrl from DT_BackGoods";
|
|
|
string tsql = @"
|
|
|
select * from DT_BackGoods where Id=@Id";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
|
{
|
|
|
model = ds.Tables[0].Rows[0].ToModel<DT_BackGoods>();
|
|
|
}
|
|
|
return model;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 分页查询
|
|
|
public static List<DT_BackGoods> GetListBackGoods(int ShopId,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.InDate,a.InUserId,a.ImgIds,a.TrackCode,a.OrderCode,a.GoodsState,a.State,ImgUrl=case when a.ImgUrl is null or a.ImgUrl='' then '../images/notp.png' else '../'+a.ImgUrl end,a.GoodsCode,a.Num,a.PostionCode,a.OrderId,a.ImgUrl2,a.ImgUrl3,a.ImgUrl4,a.ImgUrl5,a.DetailId";
|
|
|
|
|
|
if (ShopId > 0)
|
|
|
{
|
|
|
ser.Tables = "DT_BackGoods a inner join DT_OrderInfo b on a.OrderCode=b.PlatOrderCode ";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
ser.Tables = "DT_BackGoods 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<DT_BackGoods> 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<DT_BackGoods>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 多条件普通查询
|
|
|
public static List<DT_BackGoods> GetListBackGoods(RefParameterCollection where, string Sort)
|
|
|
{
|
|
|
if (where == null) where = new RefParameterCollection();
|
|
|
QueryOrdService ser = new QueryOrdService();
|
|
|
//ser.Fields = "a.Id,a.InDate,a.InUserId,a.ImgIds,a.TrackCode,a.OrderCode,a.GoodsState,a.State,a.ImgUrl";
|
|
|
ser.Fields = "a.*";
|
|
|
ser.Tables = @"DT_BackGoods a";
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
|
ser.KeyName = "Id";
|
|
|
ser.Sort = Sort;
|
|
|
string tsql = ser.GetText();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, where);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
List<DT_BackGoods> ListModel = tb.ToList<DT_BackGoods>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 返回列表
|
|
|
public static List<DT_BackGoods> GetListBackGoods()
|
|
|
{
|
|
|
//string tsql = "select a.Id,a.InDate,a.InUserId,a.ImgIds,a.TrackCode,a.OrderCode,a.GoodsState,a.State,a.ImgUrl from DT_BackGoods";
|
|
|
string tsql = "select * from DT_BackGoods";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
List<DT_BackGoods> ListModel = tb.ToList<DT_BackGoods>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 分页动态条件查询
|
|
|
public static List<DT_BackGoods> GetListBackGoods(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
QueryService2 ser = new QueryService2();
|
|
|
ser.Tsql = "select Id=cast(a.Id as int),a.InDate,a.InUserId,a.ImgIds,a.TrackCode,a.OrderCode,a.GoodsState,a.State,a.ImgUrl from DT_BackGoods a";
|
|
|
ser.Tsql += " " + ser.Filter(where);
|
|
|
ser.PageIndex = PageIndex;
|
|
|
ser.PageSize = PageSize;
|
|
|
if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.Id desc"; }
|
|
|
else { ser.Sort = Sort; }
|
|
|
string tsql = ser.GetText();
|
|
|
List<DT_BackGoods> 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<DT_BackGoods>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 判断是否重复
|
|
|
public static bool CheckName_BackGoods(string OrderCode)
|
|
|
{
|
|
|
string tsql = @"select count(0) from DT_OrderBackMoneyApply where OrderCode=@OrderCode";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@OrderCode", DbType.String, OrderCode);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
if (a == 0) return true;
|
|
|
return false;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 保存
|
|
|
public static int Save_FeeList(CK_FeeList Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @Id>0
|
|
|
begin
|
|
|
Update [CK_FeeList] set [CompanyId]=@CompanyId,[FeeType]=@FeeType,[FeeDesc]=@FeeDesc,[OffNum]=@OffNum where Id=@Id
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_FeeList]([CompanyId],[FeeType],[FeeDesc],[OffNum])values(@CompanyId,@FeeType,@FeeDesc,@OffNum)
|
|
|
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, "@FeeType", DbType.Int32, Model.FeeType);
|
|
|
db.AddInParameter(cmd, "@FeeDesc", DbType.String, Model.FeeDesc);
|
|
|
db.AddInParameter(cmd, "@OffNum", DbType.Decimal, Model.OffNum);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 删除
|
|
|
public static void Delete_FeeList(int Id)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
delete from CK_FeeList 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 static CK_FeeList GetModel_FeeList(int Id)
|
|
|
{
|
|
|
CK_FeeList model = null;
|
|
|
//string tsql = "select a.Id,a.CompanyId,a.FeeType,a.FeeDesc,a.OffNum from CK_FeeList";
|
|
|
string tsql = @"
|
|
|
select * from CK_FeeList where Id=@Id";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
|
{
|
|
|
model = ds.Tables[0].Rows[0].ToModel<CK_FeeList>();
|
|
|
}
|
|
|
return model;
|
|
|
}
|
|
|
#endregion
|
|
|
#region 分页查询
|
|
|
public static List<CK_FeeList> GetListFeeList(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.CompanyId,a.FeeType,a.FeeDesc,a.OffNum";
|
|
|
ser.Tables = @"CK_FeeList 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<CK_FeeList> 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<CK_FeeList>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 多条件普通查询
|
|
|
public static List<CK_FeeList> GetListFeeList(RefParameterCollection where, string Sort)
|
|
|
{
|
|
|
if (where == null) where = new RefParameterCollection();
|
|
|
QueryOrdService ser = new QueryOrdService();
|
|
|
//ser.Fields = "a.Id,a.CompanyId,a.FeeType,a.FeeDesc,a.OffNum";
|
|
|
ser.Fields = "a.*";
|
|
|
ser.Tables = @"CK_FeeList a";
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
|
ser.KeyName = "Id";
|
|
|
ser.Sort = Sort;
|
|
|
string tsql = ser.GetText();
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, where);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
List<CK_FeeList> ListModel = tb.ToList<CK_FeeList>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 返回列表
|
|
|
public static List<CK_FeeList> GetListFeeList()
|
|
|
{
|
|
|
//string tsql = "select a.Id,a.CompanyId,a.FeeType,a.FeeDesc,a.OffNum from CK_FeeList";
|
|
|
string tsql = "select * from CK_FeeList";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
List<CK_FeeList> ListModel = tb.ToList<CK_FeeList>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 分页动态条件查询
|
|
|
public static List<CK_FeeList> GetListFeeList(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
QueryService2 ser = new QueryService2();
|
|
|
ser.Tsql = "select Id=cast(a.Id as int),a.CompanyId,a.FeeType,a.FeeDesc,a.OffNum from CK_FeeList a";
|
|
|
ser.Tsql += " " + ser.Filter(where);
|
|
|
ser.PageIndex = PageIndex;
|
|
|
ser.PageSize = PageSize;
|
|
|
if (string.IsNullOrEmpty(Sort) == true) { ser.Sort = "a.Id desc"; }
|
|
|
else { ser.Sort = Sort; }
|
|
|
string tsql = ser.GetText();
|
|
|
List<CK_FeeList> 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<CK_FeeList>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 判断是否重复
|
|
|
public static bool CheckName_FeeList(int Id, string Name)
|
|
|
{
|
|
|
string tsql = @"select count(0) from CK_FeeList where Id<>@Id and Name=@Name";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
|
db.AddInParameter(cmd, "@Name", DbType.String, Name);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
if (a == 0) return true;
|
|
|
return false;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
#region 返回列表
|
|
|
public static List<CK_FeeDetail> GetListFeeDetail(int FeeId)
|
|
|
{
|
|
|
//string tsql = "select a.Id,a.FeeId,a.FeeType,a.FeeRate,a.OffRate,a.MoneyCode,a.FeeUnit,a.SInt,a.EInt,a.SDec,a.EDec,a.Sort,a.YLInt1,a.YLInt2,a.YLDec1,a.YLDec2,a.YLChar1,a.YLChar2,a.YLChar3 from CK_FeeDetail";
|
|
|
string tsql = @"
|
|
|
if(select count(0) from CK_FeeDetail where FeeId=@FeeId)=0
|
|
|
begin
|
|
|
INSERT INTO [CK_FeeDetail]([FeeId],[FeeType],[FeeRate],[OffRate],[MoneyCode],[FeeUnit],[SInt],[EInt],[SDec],[EDec],[Sort],[YLInt1],[YLInt2],[YLDec1],[YLDec2],[YLChar1],[YLChar2],[YLChar3])
|
|
|
select FeeId=@FeeId,[FeeType],[FeeRate],0,[MoneyCode],[FeeUnit],[SInt],[EInt],[SDec],[EDec],[Sort],[YLInt1],[YLInt2],[YLDec1],[YLDec2],[YLChar1],[YLChar2],[YLChar3] from CK_FeeDetail where FeeId=1
|
|
|
end
|
|
|
select * from CK_FeeDetail where FeeId=@FeeId
|
|
|
";
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
db.AddInParameter(cmd, "@FeeId", DbType.Int32, FeeId);
|
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
|
List<CK_FeeDetail> ListModel = tb.ToList<CK_FeeDetail>();
|
|
|
return ListModel;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 保存
|
|
|
public static int Save_FeeDetail(CK_FeeDetail Model)
|
|
|
{
|
|
|
string tsql = @"
|
|
|
if @Id>0
|
|
|
begin
|
|
|
Update [CK_FeeDetail] set [FeeId]=@FeeId,[FeeType]=@FeeType,[FeeRate]=@FeeRate,[OffRate]=@OffRate,[MoneyCode]=@MoneyCode,[FeeUnit]=@FeeUnit,[SInt]=@SInt,[EInt]=@EInt,[SDec]=@SDec,[EDec]=@EDec,[Sort]=@Sort,[YLInt1]=@YLInt1,[YLInt2]=@YLInt2,[YLDec1]=@YLDec1,[YLDec2]=@YLDec2,[YLChar1]=@YLChar1,[YLChar2]=@YLChar2,[YLChar3]=@YLChar3 where Id=@Id
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_FeeDetail]([FeeId],[FeeType],[FeeRate],[OffRate],[MoneyCode],[FeeUnit],[SInt],[EInt],[SDec],[EDec],[Sort],[YLInt1],[YLInt2],[YLDec1],[YLDec2],[YLChar1],[YLChar2],[YLChar3])values(@FeeId,@FeeType,@FeeRate,@OffRate,@MoneyCode,@FeeUnit,@SInt,@EInt,@SDec,@EDec,@Sort,@YLInt1,@YLInt2,@YLDec1,@YLDec2,@YLChar1,@YLChar2,@YLChar3)
|
|
|
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, "@FeeId", DbType.Int32, Model.FeeId);
|
|
|
db.AddInParameter(cmd, "@FeeType", DbType.Int32, Model.FeeType);
|
|
|
db.AddInParameter(cmd, "@FeeRate", DbType.Decimal, Model.FeeRate);
|
|
|
db.AddInParameter(cmd, "@OffRate", DbType.Decimal, Model.OffRate);
|
|
|
db.AddInParameter(cmd, "@MoneyCode", DbType.String, Model.MoneyCode);
|
|
|
db.AddInParameter(cmd, "@FeeUnit", DbType.String, Model.FeeUnit);
|
|
|
db.AddInParameter(cmd, "@SInt", DbType.Int32, Model.SInt);
|
|
|
db.AddInParameter(cmd, "@EInt", DbType.Int32, Model.EInt);
|
|
|
db.AddInParameter(cmd, "@SDec", DbType.Decimal, Model.SDec);
|
|
|
db.AddInParameter(cmd, "@EDec", DbType.Decimal, Model.EDec);
|
|
|
db.AddInParameter(cmd, "@Sort", DbType.Int32, Model.Sort);
|
|
|
db.AddInParameter(cmd, "@YLInt1", DbType.Int32, Model.YLInt1);
|
|
|
db.AddInParameter(cmd, "@YLInt2", DbType.Int32, Model.YLInt2);
|
|
|
db.AddInParameter(cmd, "@YLDec1", DbType.Decimal, Model.YLDec1);
|
|
|
db.AddInParameter(cmd, "@YLDec2", DbType.Decimal, Model.YLDec2);
|
|
|
db.AddInParameter(cmd, "@YLChar1", DbType.String, Model.YLChar1);
|
|
|
db.AddInParameter(cmd, "@YLChar2", DbType.String, Model.YLChar2);
|
|
|
db.AddInParameter(cmd, "@YLChar3", DbType.String, Model.YLChar3);
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
|
return a;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
public static int Save_BackGoods2(DT_BackGoods Model)
|
|
|
{
|
|
|
string sql = @"
|
|
|
if @Id>0
|
|
|
begin
|
|
|
Update [DT_BackGoods] set [InDate]=@InDate,[InUserId]=@InUserId,[ImgIds]=@ImgIds,[TrackCode]=@TrackCode,[OrderCode]=@OrderCode,[GoodsState]=@GoodsState,[State]=@State,[ImgUrl]=@ImgUrl,[GoodsCode]=@GoodsCode,[Num]=@Num,[PostionCode]=@PostionCode,[OrderId]=@OrderId,[ImgUrl2]=@ImgUrl2,[ImgUrl3]=@ImgUrl3,[ImgUrl4]=@ImgUrl4,[ImgUrl5]=@ImgUrl5,[DetailId]=@DetailId where Id=@Id
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [DT_BackGoods]([InDate],[InUserId],[ImgIds],[TrackCode],[OrderCode],[GoodsState],[State],[ImgUrl],[GoodsCode],[Num],[PostionCode],[OrderId],[ImgUrl2],[ImgUrl3],[ImgUrl4],[ImgUrl5],[DetailId])
|
|
|
values(@InDate,@InUserId,@ImgIds,@TrackCode,@OrderCode,@GoodsState,@State,@ImgUrl,@GoodsCode,@Num,@PostionCode,@OrderId,@ImgUrl2,@ImgUrl3,@ImgUrl4,@ImgUrl5,@DetailId) set @Id=SCOPE_IDENTITY()
|
|
|
end
|
|
|
if @OrderCode<>''
|
|
|
begin
|
|
|
update DT_OrderBackMoneyApply set PostCode=@TrackCode,BackState=@GoodsState where OrderCode=@OrderCode
|
|
|
end
|
|
|
select @Id";
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(sql);
|
|
|
database.AddInParameter(sqlStringCommand, "@Id", DbType.Int32, Model.Id);
|
|
|
database.AddInParameter(sqlStringCommand, "@InDate", DbType.DateTime, Model.InDate);
|
|
|
database.AddInParameter(sqlStringCommand, "@InUserId", DbType.Int32, Model.InUserId);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgIds", DbType.String, Model.ImgIds);
|
|
|
database.AddInParameter(sqlStringCommand, "@TrackCode", DbType.String, Model.TrackCode);
|
|
|
database.AddInParameter(sqlStringCommand, "@OrderCode", DbType.String, Model.OrderCode);
|
|
|
database.AddInParameter(sqlStringCommand, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
|
database.AddInParameter(sqlStringCommand, "@State", DbType.Int32, Model.State);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgUrl", DbType.String, Model.ImgUrl);
|
|
|
database.AddInParameter(sqlStringCommand, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
|
database.AddInParameter(sqlStringCommand, "@Num", DbType.Int32, Model.Num);
|
|
|
database.AddInParameter(sqlStringCommand, "@PostionCode", DbType.String, Model.PostionCode);
|
|
|
database.AddInParameter(sqlStringCommand, "@OrderId", DbType.Int32, Model.OrderId);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgUrl2", DbType.String, Model.ImgUrl2);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgUrl3", DbType.String, Model.ImgUrl3);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgUrl4", DbType.String, Model.ImgUrl4);
|
|
|
database.AddInParameter(sqlStringCommand, "@ImgUrl5", DbType.String, Model.ImgUrl5);
|
|
|
database.AddInParameter(sqlStringCommand, "@DetailId", DbType.Int32, Model.DetailId);
|
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
|
}
|
|
|
|
|
|
public static List<CK_OtherFee> GetListOtherFee()
|
|
|
{
|
|
|
string query = "select * from CK_OtherFee";
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
|
return database.ExecuteDataTable(sqlStringCommand).ToList<CK_OtherFee>();
|
|
|
}
|
|
|
public static List<CK_OtherFee> GetListOtherFee(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
if (where == null)
|
|
|
{
|
|
|
where = new RefParameterCollection();
|
|
|
}
|
|
|
string text = new QueryService
|
|
|
{
|
|
|
Fields = "Id=cast(a.Id as int),a.FeeDetailId,a.Num,a.FeeRate,a.TotalFee,a.CompanyId,a.InDate,a.InUserId,a.FeeDate,a.OffRate,a.FeeName",
|
|
|
Tables = "CK_OtherFee a",
|
|
|
Filter = where.GetWhere(CommandType.Text),
|
|
|
PageIndex = PageIndex,
|
|
|
PageSize = PageSize,
|
|
|
Sort = Sort,
|
|
|
KeyName = "Id"
|
|
|
}.GetText();
|
|
|
where.AddOutParameter("RowCount", DbType.Int32);
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(text);
|
|
|
database.AddInParameter(sqlStringCommand, where);
|
|
|
DataTable tb = database.ExecuteDataTable(sqlStringCommand);
|
|
|
RowCount = Convert.ToInt32(sqlStringCommand.Parameters["@RowCount"].Value);
|
|
|
return tb.ToList<CK_OtherFee>();
|
|
|
}
|
|
|
public static List<CK_OtherFee> GetListOtherFee(string where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
|
{
|
|
|
QueryService2 service = new QueryService2
|
|
|
{
|
|
|
Tsql = "select Id=cast(a.Id as int),a.FeeDetailId,a.Num,a.FeeRate,a.TotalFee,a.CompanyId,a.InDate,a.InUserId,a.FeeDate,a.OffRate,b.CompanyName,c.Name,a.FeeName from CK_OtherFee a inner join JC_Company b on a.CompanyId=b.CompanyId inner join JC_UserInfo c on a.InUserId=c.UserId"
|
|
|
};
|
|
|
service.Tsql = service.Tsql + " " + service.Filter(where);
|
|
|
service.PageIndex = PageIndex;
|
|
|
service.PageSize = PageSize;
|
|
|
if (string.IsNullOrEmpty(Sort))
|
|
|
{
|
|
|
service.Sort = "a.Id desc";
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
service.Sort = Sort;
|
|
|
}
|
|
|
string text = service.GetText();
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(text);
|
|
|
database.AddOutParameter(sqlStringCommand, "@RowCount", DbType.Int32, 4);
|
|
|
DataTable tb = database.ExecuteDataTable(sqlStringCommand);
|
|
|
RowCount = Convert.ToInt32(sqlStringCommand.Parameters["@RowCount"].Value);
|
|
|
return tb.ToList<CK_OtherFee>();
|
|
|
}
|
|
|
|
|
|
public static List<CK_OtherFee> GetListFeeDetailOther(int CompanyId)
|
|
|
{
|
|
|
string query = " select FeeName=c.YLChar2,c.FeeRate,c.OffRate,FeeDetailId=c.Id from JC_Company a inner join CK_FeeList b on a.FeeId=b.Id inner join CK_FeeDetail c on b.Id=c.FeeId where c.FeeType=9 and a.CompanyId=@CompanyId ";
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
return database.ExecuteDataTable(sqlStringCommand).ToList<CK_OtherFee>();
|
|
|
}
|
|
|
public static int Save_OtherFee(CK_OtherFee Model)
|
|
|
{
|
|
|
string query = @" if @Id>0
|
|
|
begin Update [CK_OtherFee] set [FeeDetailId]=@FeeDetailId,[Num]=@Num,[FeeRate]=@FeeRate,[TotalFee]=@TotalFee,[CompanyId]=@CompanyId,[InDate]=@InDate,[InUserId]=@InUserId,[FeeDate]=@FeeDate,[OffRate]=@OffRate,[FeeName]=@FeeName where Id=@Id
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
INSERT INTO [CK_OtherFee]([FeeDetailId],[Num],[FeeRate],[TotalFee],[CompanyId],[InDate],[InUserId],[FeeDate],[OffRate],[FeeName])
|
|
|
values(@FeeDetailId,@Num,@FeeRate,@TotalFee,@CompanyId,@InDate,@InUserId,@FeeDate,@OffRate,@FeeName) set @Id=SCOPE_IDENTITY()
|
|
|
end
|
|
|
select @Id";
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
|
database.AddInParameter(sqlStringCommand, "@Id", DbType.Int32, Model.Id);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeDetailId", DbType.Int32, Model.FeeDetailId);
|
|
|
database.AddInParameter(sqlStringCommand, "@Num", DbType.Decimal, Model.Num);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeRate", DbType.Decimal, Model.FeeRate);
|
|
|
database.AddInParameter(sqlStringCommand, "@TotalFee", DbType.Decimal, Model.TotalFee);
|
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
|
database.AddInParameter(sqlStringCommand, "@InDate", DbType.DateTime, Model.InDate);
|
|
|
database.AddInParameter(sqlStringCommand, "@InUserId", DbType.Int32, Model.InUserId);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeDate", DbType.DateTime, Model.FeeDate);
|
|
|
database.AddInParameter(sqlStringCommand, "@OffRate", DbType.Decimal, Model.OffRate);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeName", DbType.String, Model.FeeName);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeName2", DbType.String, Model.FeeName);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeName", DbType.String, Model.FeeName);
|
|
|
database.AddInParameter(sqlStringCommand, "@FeeName", DbType.String, Model.FeeName);
|
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
|
}
|
|
|
public static void UpdateOtherFee(DateTime InDate, int CompanyId)
|
|
|
{
|
|
|
string query=@"declare @r nvarchar(3000),@totalfee money
|
|
|
set @r=''
|
|
|
select @r=@r+'；'+isnull(FeeName,0)+'金额：['+cast(totalfee as nvarchar(8))+']' from CK_OtherFee where CompanyId=@CompanyId and DATEDIFF(day,InDate,@InDate)=0
|
|
|
select @totalfee=SUM(totalfee) from CK_OtherFee where CompanyId=@CompanyId and DATEDIFF(day,InDate,@InDate)=0
|
|
|
if(select COUNT(0) from CK_FeeReport where CompanyId=@CompanyId and FeeType=9 and DATEDIFF(day,InDate,@InDate)=0)=0
|
|
|
begin
|
|
|
INSERT INTO [CK_FeeReport]([InDate],[FeeType],[TotalFee],[FeeDesc],[CompanyId],[Num],[Num2],[Dec],[Dec2])
|
|
|
select InDate=@InDate,9,totalfee=@totalfee,r=@r,@CompanyId,0,0,@totalfee,0
|
|
|
end
|
|
|
else
|
|
|
begin
|
|
|
update[CK_FeeReport] set FeeDesc=@r,TotalFee=@totalfee where CompanyId=@CompanyId and DATEDIFF(day,InDate,@InDate)=0
|
|
|
end";
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
|
database.AddInParameter(sqlStringCommand, "@InDate", DbType.DateTime, InDate);
|
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, CompanyId);
|
|
|
database.ExecuteNonQuery(sqlStringCommand);
|
|
|
}
|
|
|
}
|
|
|
}
|
