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.
4205 lines
204 KiB
C#
4205 lines
204 KiB
C#
using System;
|
|
using System.Collections;
|
|
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 CG_ChaseData
|
|
{
|
|
#region 分页查询
|
|
public List<HW_CGGoods> GetListCGGoodsInfo(string DayBuy,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"GoodsId=cast(a.GoodsId as int),a.GoodsCode,a.SortId,a.SupplierId,a.SortName,a.GoodsName,a.Position,b.AvgTime,a.State,a.CompanyId,b.DetailId,b.TypeCode,b.TypeDesc,b.NoGoods,b.MinBuyNum,b.GoodsNum,b.GoodsLockNum,b.GoodsPlanNum,GoodsOutNum=isnull(b.GoodsOutNum,0),b.SafeNum,b.GoodsInNum,b.Price,b.GoodsImageIds,b.FirstImgUrl,LeftNum=isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0),b.SKU1,a.SupplierName";
|
|
if (DayBuy!="")
|
|
ser.Tables = @"(select *,SupplierName=dbo.getGoodsSupplier(GoodsId) from HW_GoodsInfo)a inner join (select * from HW_GoodsDetail where GoodsNum<" + DayBuy + @")b on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsSort c on a.SortId=c.SortId";
|
|
else
|
|
ser.Tables = @"(select *,SupplierName=dbo.getGoodsSupplier(GoodsId) from HW_GoodsInfo)a inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsSort c on a.SortId=c.SortId";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<HW_CGGoods> 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<HW_CGGoods>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询
|
|
public List<HW_CGGoods> GetListCGGoodsInfoForTM(string DayBuy,string BuyName, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"GoodsId=cast(a.GoodsId as int),a.GoodsCode,a.GoodsOldCode,a.SortId,SupplierName=a.SupplierId,a.SortName,a.GoodsName,a.Position,b.AvgTime,a.State,a.CompanyId,b.DetailId,b.TypeCode,b.TypeDesc,b.NoGoods,b.MinBuyNum,b.GoodsNum,b.GoodsLockNum,b.GoodsPlanNum,GoodsOutNum=isnull(b.GoodsOutNum,0),b.SafeNum,b.GoodsInNum,b.Price,b.GoodsImageIds,b.FirstImgUrl,LeftNum=isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0),b.SKU1,a.Supplier,d.BuyName,BuyUserId=d.BuyerUserId";
|
|
// if (DayBuy != "")
|
|
// ser.Tables = @"HW_GoodsInfo a inner join (select * from HW_GoodsDetail where GoodsNum<" + DayBuy + @")b on a.GoodsId=b.GoodsId
|
|
//inner join HW_GoodsSort c on a.SortId=c.SortId
|
|
//";
|
|
// else
|
|
ser.Tables = @"HW_GoodsInfo a inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsSort c on a.SortId=c.SortId";
|
|
if (BuyName== "0") //没有分配采购人
|
|
{
|
|
ser.Tables = @"(select * from HW_GoodsInfo where GoodsId not in (select distinct GoodsId from [CG_GoodsMate] where BuyerUserId>0)) a inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsSort c on a.SortId=c.SortId
|
|
left join (select distinct a.GoodsId,BuyName=b.Name,a.BuyerUserId from CG_GoodsMate a
|
|
inner join JC_UserInfo b on a.BuyerUserId=b.UserId) d on a.GoodsId=d.GoodsId
|
|
";
|
|
}
|
|
else
|
|
if (BuyName != "")
|
|
{
|
|
ser.Tables += @" inner join (select distinct a.GoodsId,BuyName=b.Name,a.BuyerUserId from CG_GoodsMate a
|
|
inner join JC_UserInfo b on a.BuyerUserId=b.UserId
|
|
where b.Name='" +BuyName+"') d on a.GoodsId=d.GoodsId";
|
|
}
|
|
else
|
|
ser.Tables += @" left join (select distinct a.GoodsId,BuyName=b.Name,a.BuyerUserId from CG_GoodsMate a
|
|
inner join JC_UserInfo b on a.BuyerUserId=b.UserId) d on a.GoodsId=d.GoodsId";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<HW_CGGoods> 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<HW_CGGoods>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 分页查询
|
|
public List<HW_CGGoods> GetListCGGoodsInfoForTM2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"GoodsId=cast(a.GoodsId as int),a.GoodsCode,a.GoodsOldCode,a.SortId,SupplierName=a.SupplierId,a.SortName,a.GoodsName,a.Position,b.AvgTime,a.State,a.CompanyId,b.DetailId,b.TypeCode,b.TypeDesc,b.NoGoods,b.MinBuyNum,b.GoodsNum,b.GoodsLockNum,b.GoodsPlanNum,GoodsOutNum=isnull(b.GoodsOutNum,0),b.SafeNum,b.GoodsInNum,b.Price,b.GoodsImageIds,b.FirstImgUrl,LeftNum=isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0),b.SKU1,a.Supplier,d.BuyName,BuyUserId=d.BuyerUserId";
|
|
|
|
ser.Tables = @"HW_GoodsInfo a inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
inner join HW_GoodsSort c on a.SortId=c.SortId";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<HW_CGGoods> 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<HW_CGGoods>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询入库异常
|
|
public List<HW_RKErrorGoods> GetListCGError(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"a.Id,a.ChaseId,c.DetailId,d.GoodsId,b.ChaseCode,b.BuyCode,d.GoodsCode,d.GoodsOldCode,c.TypeDesc,a.ErrorDesc,a.InDate,a.GoodsNum,BuyName=b.InName,a.DoDate,a.IsCG,IsCGState=case when a.IsCG=1 then '是' else '否' end,a.DoResult,a.PostCode,b.InUserId,PostionDesc=b.PostionCode,SupplierName=d.SupplierId";
|
|
ser.Tables = @"CG_ChaseInStore a
|
|
inner join CG_Purchase b on a.ChaseId=b.ChaseId
|
|
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<HW_RKErrorGoods> 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<HW_RKErrorGoods>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询入库异常
|
|
public DataTable GetListCGError2(RefParameterCollection where, string Sort)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryOrdService ser = new QueryOrdService();
|
|
ser.Fields = @"a.Id,a.ChaseId,c.DetailId,d.GoodsId,b.ChaseCode,b.BuyCode,d.GoodsCode,d.GoodsOldCode,c.TypeDesc,a.ErrorDesc,a.InDate,a.GoodsNum,BuyName=b.InName,a.DoDate,a.IsCG,IsCGState=case when a.IsCG=1 then '是' else '否' end,a.DoResult,a.PostCode,b.InUserId,PostionDesc=b.PostionCode,SupplierName=d.SupplierId";
|
|
ser.Tables = @"CG_ChaseInStore a
|
|
inner join CG_Purchase b on a.ChaseId=b.ChaseId
|
|
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, where);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
return tb;
|
|
}
|
|
#endregion
|
|
#region 返回原库位
|
|
public string GetPostionDesc(int DetailId)
|
|
{
|
|
|
|
string tsql = @"select top 1 b.PostionCode from HW_GoodsInDetail a
|
|
inner join CK_StorePostion b on a.PostionId=b.PostionId
|
|
where a.DetailId=@DetailId and b.IsLS=0 order by a.InId desc";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
string a= Convert.ToString(db.ExecuteScalar(cmd));
|
|
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 异常标记给采购人
|
|
public void UpdateErrorToCG(string Ids)
|
|
{
|
|
|
|
string tsql = @"update CG_ChaseInStore set IsCG=1 where Id in ("+Ids+")";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 处理异常全部
|
|
public void DoneError(int Id, string DoResult,int DetailId,int ChaseId,int GoodsNum, string InName,string PostCode)
|
|
{
|
|
|
|
string tsql = @"
|
|
if @DoResult='处理完入库'
|
|
begin
|
|
update CG_PurchaseGoods set InGoodsNum=isnull(InGoodsNum,0)+@GoodsNum where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
update CG_ChaseInStore set DoResult=@DoResult,DoDate=getdate(),DoName=@InName,GoodsInNum=GoodsNum,PostCode=@PostCode where Id=@Id
|
|
if(select count(0) from CG_PurchaseGoods where InGoodsNum<GoodsNum)=0
|
|
begin
|
|
update CG_Purchase set GoodsState=2,state=3 where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set GoodsState=1 where ChaseId=@ChaseId
|
|
end
|
|
end
|
|
else if @DoResult='退货' or @DoResult='报废'
|
|
begin
|
|
update CG_PurchaseGoods set BackNum=isnull(BackNum,0)+@GoodsNum where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
end
|
|
if @DoResult<>'处理完入库'
|
|
begin
|
|
update CG_ChaseInStore set DoResult=@DoResult,DoDate=getdate(),DoName=@InName,GoodsInNum=0,PostCode=@PostCode where Id=@Id
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@DoResult", DbType.String, DoResult);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, PostCode);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, InName);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 处理异常拆分
|
|
public void DoneErrorPart(int Id,int DetailId,int ChaseId,int GoodsNum, string DoResult, string InName, string PostCode)
|
|
{
|
|
|
|
string tsql = @"
|
|
if @DoResult='处理完入库'
|
|
begin
|
|
update CG_PurchaseGoods set InGoodsNum=isnull(InGoodsNum,0)+@GoodsNum where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
end
|
|
else if @DoResult='退货' or @DoResult='报废'
|
|
begin
|
|
update CG_PurchaseGoods set BackNum=isnull(BackNum,0)+@GoodsNum where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
end
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],[InUserId],[IsCG],[DoResult],[DoDate],[DoName],[PostCode],[GoodsInNum])
|
|
select [ChaseId],[GoodsDetailId],GoodsNum=@GoodsNum,[ErrorDesc],[InDate],[Remark],[State],[StoreId],[InUserId],[IsCG],@DoResult,getdate(),@InName,@PostCode,GoodsInNum=case when @DoResult='处理完入库' then @GoodsNum else 0 end from CG_ChaseInStore where Id=@Id
|
|
|
|
update CG_ChaseInStore set GoodsNum=GoodsNum-@GoodsNum where Id=@Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DoResult", DbType.String, DoResult);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, PostCode);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, InName);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 返回采购人
|
|
public CG_GoodsMate GetBuyName(int GoodsId)
|
|
{
|
|
CG_GoodsMate model = null;
|
|
string tsql = @"select top 1 BuyerName=b.Name,a.BuyerUserId from CG_GoodsMate a
|
|
inner join JC_UserInfo b on a.BuyerUserId=b.UserId
|
|
where GoodsId=@GoodsId order by a.Id desc";
|
|
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<CG_GoodsMate>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
#region 返回订单日期
|
|
public List<DT_OrderDate> GetOrderdate(string DetailIds)
|
|
{
|
|
List<DT_OrderDate> list = null;
|
|
string tsql = @"select a.InDate,a.BuyDate,b.DetailId,a.FPDate,c.ShopId,c.DeptId,c.PlatType from DT_OrderInfo a
|
|
inner join DT_OrderGoods b on a.OrderId=b.OrderId
|
|
INNER JOIN dbo.JC_Shop c ON a.ShopId=c.ShopId
|
|
where b.DetailId in (" + DetailIds + ") and a.State=1 order by a.InDate ";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<DT_OrderDate>();
|
|
return list;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询当天采购单号
|
|
public int Get_CGOrderCode(int CompanyId)
|
|
{
|
|
|
|
string tsql = @"select count(0) from CG_Purchase where datediff(day,InDate,getdate())=0 and CompanyId=@CompanyId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyID", DbType.Int32, CompanyId);
|
|
int code = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return code;
|
|
}
|
|
#endregion
|
|
#region 保存采购
|
|
/// <summary>
|
|
/// 保存采购
|
|
/// </summary>
|
|
public int SavePurchase(CG_Purchase Model)
|
|
{
|
|
string tsql = @"
|
|
if @ChaseId>0
|
|
begin
|
|
Update [CG_Purchase] set [ChaseCode]=@ChaseCode,[BuyCode]=@BuyCode,[GoodsState]=@GoodsState,[PostFee]=@PostFee,[Remark]=@Remark,[InDate]=@InDate,[BuyDate]=@BuyDate,[DoneDate]=@DoneDate,[Supplier]=@Supplier,[SupplierName]=@SupplierName,[SupplierPhone]=@SupplierPhone,[InName]=@InName,[InUserId]=@InUserId,[Account]=@Account,[State]=@State,[CompanyId]=@CompanyId,[CheckUid]=@CheckUid,CheckContent=null,CheckDate=null,[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_Purchase]([ChaseCode],[BuyCode],[GoodsState],[PostFee],[Remark],[ErrorInfo],[InDate],[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],[InName],[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum],[GoodsMoney])values(@ChaseCode,@BuyCode,@GoodsState,@PostFee,@Remark,@ErrorInfo,@InDate,@BuyDate,@DoneDate,@Supplier,@SupplierName,@SupplierPhone,@InName,@InUserId,@Account,@IsDelete,@State,@CompanyId,@CheckUid,@CheckContent,@CheckDate,@GoodsNum,@GoodsMoney)
|
|
set @ChaseId=@@IDENTITY
|
|
end
|
|
select @ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@ChaseCode", DbType.String, Model.ChaseCode);
|
|
db.AddInParameter(cmd, "@BuyCode", DbType.String, Model.BuyCode);
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, Model.ErrorInfo);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
db.AddInParameter(cmd, "@DoneDate", DbType.DateTime, Model.DoneDate);
|
|
db.AddInParameter(cmd, "@Supplier", DbType.String, Model.Supplier);
|
|
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
|
|
db.AddInParameter(cmd, "@SupplierPhone", DbType.String, Model.SupplierPhone);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@Account", DbType.String, Model.Account);
|
|
db.AddInParameter(cmd, "@IsDelete", DbType.Int32, Model.IsDelete);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@CheckUid", DbType.Int32, Model.CheckUid);
|
|
db.AddInParameter(cmd, "@CheckContent", DbType.String, Model.CheckContent);
|
|
db.AddInParameter(cmd, "@CheckDate", DbType.DateTime, Model.CheckDate);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购
|
|
/// <summary>
|
|
/// 保存采购
|
|
/// </summary>
|
|
public int SavePurchaseForTM(CG_Purchase Model)
|
|
{
|
|
string tsql = @"
|
|
if @ChaseId>0
|
|
begin
|
|
if @GoodsState>0
|
|
begin
|
|
declare @OldGoodsMoney money,@BackType int,@BackMoney money
|
|
select @OldGoodsMoney=GoodsMoney from CG_Purchase where ChaseId=@ChaseId
|
|
if @OldGoodsMoney>@GoodsMoney
|
|
begin
|
|
set @BackType=1
|
|
set @BackMoney=@OldGoodsMoney-@GoodsMoney
|
|
end
|
|
else if @OldGoodsMoney<@GoodsMoney
|
|
begin
|
|
set @BackType=2
|
|
set @BackMoney=@GoodsMoney-@OldGoodsMoney
|
|
end
|
|
Update [CG_Purchase] set [ChaseCode]=@ChaseCode,[BuyCode]=@BuyCode,[ErrorInfo]=@ErrorInfo,[PostFee]=@PostFee,[Remark]=@Remark,[Supplier]=@Supplier,[SupplierName]=@SupplierName,[SupplierPhone]=@SupplierPhone,[InName]=@InName,[Account]=@Account,[CompanyId]=@CompanyId,[CheckUid]=@CheckUid,CheckContent=null,CheckDate=null,[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney,[IsFactory]=@IsFactory,[IsBH]=@IsBH,BackType=@BackType,BackMoney=@BackMoney,BackUserId=@InUserId,IsGJ=@IsGJ,BackDate=@BackDate,TCFee1=@TCFee1,TCFee2=@TCFee2,TCFee3=@TCFee3,TCFee4=@TCFee4,TCFee5=@TCFee5,TCFee6=@TCFee6 where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
Update [CG_Purchase] set [ChaseCode]=@ChaseCode,[BuyCode]=@BuyCode,[ErrorInfo]=@ErrorInfo,[PostFee]=@PostFee,[Remark]=@Remark,[Supplier]=@Supplier,[SupplierName]=@SupplierName,[SupplierPhone]=@SupplierPhone,[InName]=@InName,[Account]=@Account,[CompanyId]=@CompanyId,[CheckUid]=@CheckUid,CheckContent=null,CheckDate=null,[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney,[IsFactory]=@IsFactory,[IsBH]=@IsBH,IsGJ=@IsGJ,BackDate=@BackDate,TCFee1=@TCFee1,TCFee2=@TCFee2,TCFee3=@TCFee3,TCFee4=@TCFee4,TCFee5=@TCFee5,TCFee6=@TCFee6 where ChaseId=@ChaseId
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_Purchase]([ChaseCode],[BuyCode],[GoodsState],[PostFee],[Remark],[ErrorInfo],[InDate],[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],[InName],[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum],[GoodsMoney],[IsFactory],[IsBH],BackDate,TCFee1,TCFee2,TCFee3,TCFee4,TCFee5,TCFee6)values(@ChaseCode,@BuyCode,@GoodsState,@PostFee,@Remark,@ErrorInfo,@InDate,@BuyDate,@DoneDate,@Supplier,@SupplierName,@SupplierPhone,@InName,@InUserId,@Account,@IsDelete,@State,@CompanyId,@CheckUid,@CheckContent,@CheckDate,@GoodsNum,@GoodsMoney,@IsFactory,@IsBH,@BackDate,@TCFee1,@TCFee2,@TCFee3,@TCFee4,@TCFee5,@TCFee6)
|
|
set @ChaseId=@@IDENTITY
|
|
end
|
|
select @ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@ChaseCode", DbType.String, Model.ChaseCode);
|
|
db.AddInParameter(cmd, "@BuyCode", DbType.String, Model.BuyCode);
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, Model.ErrorInfo);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
db.AddInParameter(cmd, "@DoneDate", DbType.DateTime, Model.DoneDate);
|
|
db.AddInParameter(cmd, "@Supplier", DbType.String, Model.Supplier);
|
|
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
|
|
db.AddInParameter(cmd, "@SupplierPhone", DbType.String, Model.SupplierPhone);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@Account", DbType.String, Model.Account);
|
|
db.AddInParameter(cmd, "@IsDelete", DbType.Int32, Model.IsDelete);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@CheckUid", DbType.Int32, Model.CheckUid);
|
|
db.AddInParameter(cmd, "@CheckContent", DbType.String, Model.CheckContent);
|
|
db.AddInParameter(cmd, "@CheckDate", DbType.DateTime, Model.CheckDate);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
db.AddInParameter(cmd, "@IsFactory", DbType.Int32, Model.IsFactory);
|
|
db.AddInParameter(cmd, "@IsBH", DbType.Int32, Model.IsBH);
|
|
db.AddInParameter(cmd, "@IsGJ", DbType.Int32, Model.IsGJ);
|
|
db.AddInParameter(cmd, "@BackDate", DbType.DateTime, Model.BackDate);
|
|
db.AddInParameter(cmd, "@TCFee1", DbType.Decimal, Model.TCFee1);
|
|
db.AddInParameter(cmd, "@TCFee2", DbType.Decimal, Model.TCFee2);
|
|
db.AddInParameter(cmd, "@TCFee3", DbType.Decimal, Model.TCFee3);
|
|
db.AddInParameter(cmd, "@TCFee4", DbType.Decimal, Model.TCFee4);
|
|
db.AddInParameter(cmd, "@TCFee5", DbType.Decimal, Model.TCFee5);
|
|
db.AddInParameter(cmd, "@TCFee6", DbType.Decimal, Model.TCFee6);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 保存采购
|
|
/// <summary>
|
|
/// 保存采购
|
|
/// </summary>
|
|
public int SavePurchaseForTM2(CG_Purchase Model)
|
|
{
|
|
string tsql = @"
|
|
if @ChaseId>0
|
|
begin
|
|
Update [CG_Purchase] set [BuyCode]=@BuyCode,[PostFee]=@PostFee,[Remark]=@Remark,[ErrorInfo]=@ErrorInfo,[BuyDate]=@BuyDate,[DoneDate]=@DoneDate,[Supplier]=@Supplier,[SupplierName]=@SupplierName,[SupplierPhone]=@SupplierPhone,[InName]=@InName,[InUserId]=@InUserId,[Account]=@Account,[IsDelete]=@IsDelete,[State]=@State,[CompanyId]=@CompanyId,[CheckUid]=@CheckUid,[CheckContent]=@CheckContent,[CheckDate]=@CheckDate,[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney,[IsFactory]=@IsFactory,[IsBH]=@IsBH,[PostState]=@PostState,[PostDate]=@PostDate,[BackDate]=@BackDate,[PostionCode]=@PostionCode,[IsError]=@IsError,[IsGJ]=@IsGJ,[TCFee1]=@TCFee1,[TCFee2]=@TCFee2,[TCFee3]=@TCFee3,[TCFee4]=@TCFee4,[TCFee5]=@TCFee5,[TCFee6]=@TCFee6,[TSoild]=@TSoild where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_Purchase]([ChaseCode],[BuyCode],[GoodsState],[PostFee],[Remark],[ErrorInfo],[InDate],[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],[InName],[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum],[GoodsMoney],[IsFactory],[IsBH],IsGJ,BackDate,TCFee1,TCFee2,TCFee3,TCFee4,TCFee5,TCFee6,TSoild)values(@ChaseCode,@BuyCode,@GoodsState,@PostFee,@Remark,@ErrorInfo,@InDate,@BuyDate,@DoneDate,@Supplier,@SupplierName,@SupplierPhone,@InName,@InUserId,@Account,@IsDelete,@State,@CompanyId,@CheckUid,@CheckContent,@CheckDate,@GoodsNum,@GoodsMoney,@IsFactory,@IsBH,@IsGJ,@BackDate,@TCFee1,@TCFee2,@TCFee3,@TCFee4,@TCFee5,@TCFee6,@TSoild)
|
|
set @ChaseId=@@IDENTITY
|
|
end
|
|
select @ChaseId
|
|
";
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@ChaseCode", DbType.String, Model.ChaseCode);
|
|
db.AddInParameter(cmd, "@BuyCode", DbType.String, Model.BuyCode);
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, Model.ErrorInfo);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
db.AddInParameter(cmd, "@DoneDate", DbType.DateTime, Model.DoneDate);
|
|
db.AddInParameter(cmd, "@Supplier", DbType.String, Model.Supplier);
|
|
db.AddInParameter(cmd, "@SupplierName", DbType.String, Model.SupplierName);
|
|
db.AddInParameter(cmd, "@SupplierPhone", DbType.String, Model.SupplierPhone);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, Model.InName);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@Account", DbType.String, Model.Account);
|
|
db.AddInParameter(cmd, "@IsDelete", DbType.Int32, Model.IsDelete);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@CheckUid", DbType.Int32, Model.CheckUid);
|
|
db.AddInParameter(cmd, "@CheckContent", DbType.String, Model.CheckContent);
|
|
db.AddInParameter(cmd, "@CheckDate", DbType.DateTime, Model.CheckDate);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
db.AddInParameter(cmd, "@IsFactory", DbType.Int32, Model.IsFactory);
|
|
db.AddInParameter(cmd, "@IsBH", DbType.Int32, Model.IsBH);
|
|
db.AddInParameter(cmd, "@PostState", DbType.Int32, Model.PostState);
|
|
db.AddInParameter(cmd, "@PostDate", DbType.DateTime, Model.PostDate);
|
|
|
|
db.AddInParameter(cmd, "@IsCheck", DbType.Int32, Model.IsCheck);
|
|
|
|
db.AddInParameter(cmd, "@BackDate", DbType.DateTime, Model.BackDate);
|
|
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
db.AddInParameter(cmd, "@IsError", DbType.Int32, Model.IsError);
|
|
|
|
db.AddInParameter(cmd, "@IsGJ", DbType.Int32, Model.IsGJ);
|
|
db.AddInParameter(cmd, "@TCFee1", DbType.Decimal, Model.TCFee1);
|
|
db.AddInParameter(cmd, "@TCFee2", DbType.Decimal, Model.TCFee2);
|
|
db.AddInParameter(cmd, "@TCFee3", DbType.Decimal, Model.TCFee3);
|
|
db.AddInParameter(cmd, "@TCFee4", DbType.Decimal, Model.TCFee4);
|
|
db.AddInParameter(cmd, "@TCFee5", DbType.Decimal, Model.TCFee5);
|
|
db.AddInParameter(cmd, "@TCFee6", DbType.Decimal, Model.TCFee6);
|
|
db.AddInParameter(cmd, "@TSoild", DbType.Decimal, Model.TSoild);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购
|
|
/// <summary>
|
|
/// 保存采购
|
|
/// </summary>
|
|
public int UpdatePurchaseForTM2(CG_Purchase Model)
|
|
{
|
|
string tsql = @"
|
|
update [CG_Purchase] set BuyCode=@BuyCode,PostFee=@PostFee,GoodsMoney=@GoodsMoney,GoodsNum=@GoodsNum,Account=@Account
|
|
where ChaseId=@ChaseId
|
|
select @ChaseId
|
|
";
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
|
|
db.AddInParameter(cmd, "@BuyCode", DbType.String, Model.BuyCode);
|
|
db.AddInParameter(cmd, "@Account", DbType.String, Model.Account);
|
|
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购快递
|
|
/// <summary>
|
|
/// 保存采购快递
|
|
/// </summary>
|
|
public void UpdatePurchasePost2(CG_PurchasePost Model)
|
|
{
|
|
string tsql = @"
|
|
|
|
Update [CG_PurchasePost] set [PostCode]=@PostCode where ChaseId=@ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 拆分采购批次
|
|
/// <summary>
|
|
/// 拆分采购批次
|
|
/// </summary>
|
|
public int UpdatePurchaseForTM(CG_Purchase Model)
|
|
{
|
|
string tsql = @"
|
|
if @ChaseId>0
|
|
begin
|
|
select top 1 @ChaseCode=ChaseCode from CG_Purchase where ChaseCode like @ChaseCode+'%' order by ChaseId desc
|
|
Update [CG_Purchase] set [PostFee]=@PostFee,GoodsNum=GoodsNum-@GoodsNum,GoodsMoney=GoodsMoney-@GoodsMoney where ChaseId=@ChaseId
|
|
INSERT INTO [CG_Purchase]([ChaseCode],[BuyCode],[GoodsState],[PostFee],[Remark],[ErrorInfo],[InDate],[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],[InName],[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum],[GoodsMoney],[IsFactory],[IsBH])
|
|
select ChaseCode=@ChaseCode+'-拆',[BuyCode],[GoodsState],[PostFee]=0,[Remark],[ErrorInfo],[InDate]=GETDATE(),[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],InName,[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney,[IsFactory],[IsBH] from [CG_Purchase]
|
|
where ChaseId=@ChaseId
|
|
set @ChaseId=@@IDENTITY
|
|
end
|
|
select @ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@ChaseCode", DbType.String, Model.ChaseCode);
|
|
db.AddInParameter(cmd, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, Model.ErrorInfo);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public int UpdatePurchaseGoods(int Id, int GoodsNum, int CFGoodsNum, decimal CFPrice)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
if @GoodsNum=@CFGoodsNum
|
|
begin
|
|
delete from CG_PurchaseGoods where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
update a set a.GoodsMoney=a.GoodsMoney-@CFPrice from CG_Purchase a inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId where b.Id=@Id
|
|
Update [CG_PurchaseGoods] set [GoodsNum]=GoodsNum-@CFGoodsNum where Id=@Id
|
|
end
|
|
end
|
|
select @Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@CFGoodsNum", DbType.Int32, CFGoodsNum);
|
|
db.AddInParameter(cmd, "@CFPrice", DbType.Decimal, CFPrice);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public int SavePurchaseGoods(CG_PurchaseGoods Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
if @GoodsNum=0
|
|
begin
|
|
delete from CG_PurchaseGoods where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
Update [CG_PurchaseGoods] set [ChaseId]=@ChaseId,[GoodsCode]=@GoodsCode,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[GoodsPrice]=@GoodsPrice,[LockNum]=@LockNum,[IsOrder]=@IsOrder,[CompanyId]=@CompanyId where Id=@Id
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_PurchaseGoods]([ChaseId],[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum])values(@ChaseId,@GoodsCode,@GoodsDetailId,@GoodsNum,@GoodsPrice,@LockNum,@IsOrder,@CompanyId,@InGoodsNum)
|
|
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, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsPrice", DbType.Decimal, Model.GoodsPrice);
|
|
db.AddInParameter(cmd, "@LockNum", DbType.Int32, Model.LockNum);
|
|
db.AddInParameter(cmd, "@IsOrder", DbType.Int32, Model.IsOrder);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, Model.InGoodsNum);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public int SavePurchaseGoods2(CG_PurchaseGoods Model)
|
|
{
|
|
string tsql = @"
|
|
INSERT INTO [CG_PurchaseGoods]([ChaseId],[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum])values(@ChaseId,@GoodsCode,@GoodsDetailId,@GoodsNum,@GoodsPrice,@LockNum,@IsOrder,@CompanyId,@InGoodsNum)
|
|
set @Id=SCOPE_IDENTITY()
|
|
if @AddNum>0
|
|
begin
|
|
update HW_GoodsDetail set GoodsInNum=isnull(GoodsInNum,0)+@AddNum where DetailId=@GoodsDetailId
|
|
end
|
|
select @Id
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsPrice", DbType.Decimal, Model.GoodsPrice);
|
|
db.AddInParameter(cmd, "@LockNum", DbType.Int32, Model.LockNum);
|
|
db.AddInParameter(cmd, "@IsOrder", DbType.Int32, Model.IsOrder);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, Model.InGoodsNum);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.BuyNum);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public int SavePurchaseGoods3(CG_PurchaseGoods Model)
|
|
{
|
|
string tsql = @"
|
|
INSERT INTO [CG_PurchaseGoods]([ChaseId],[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum],OrderId)values(@ChaseId,@GoodsCode,@GoodsDetailId,@GoodsNum,@GoodsPrice,@LockNum,@IsOrder,@CompanyId,@InGoodsNum,@OrderId)
|
|
set @Id=SCOPE_IDENTITY()
|
|
if @AddNum>0
|
|
begin
|
|
update HW_GoodsDetail set GoodsInNum=isnull(GoodsInNum,0)+@AddNum where DetailId=@GoodsDetailId
|
|
end
|
|
select @Id
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsPrice", DbType.Decimal, Model.GoodsPrice);
|
|
db.AddInParameter(cmd, "@LockNum", DbType.Int32, Model.LockNum);
|
|
db.AddInParameter(cmd, "@IsOrder", DbType.Int32, Model.IsOrder);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, Model.InGoodsNum);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.BuyNum);
|
|
db.AddInParameter(cmd, "@OrderId", DbType.Int32, Model.OrderId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public void UpdatePurchaseGoods3(CG_PurchaseGoods Model)
|
|
{
|
|
string tsql = @"
|
|
update CG_PurchaseGoods set GoodsNum=@GoodsNum,GoodsPrice=@GoodsPrice where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
|
|
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsPrice", DbType.Decimal, Model.GoodsPrice);
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 保存采购货物
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public void UpdateOrderState(int OrderId)
|
|
{
|
|
string tsql = @"
|
|
update DT_OrderInfoNew set IsCG=1,BuyDate=getdate() where OrderId=@OrderId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
|
|
|
|
db.ExecuteScalar(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 获取货物备注
|
|
/// <summary>
|
|
/// 保存采购货物
|
|
/// </summary>
|
|
public string GetGoodsRemark(int GoodsId)
|
|
{
|
|
string tsql = @"
|
|
declare @Remark nvarchar(1000),@DeptRemark nvarchar(1000),@NoticeDays int,@GoodsOldCode nvarchar(50)
|
|
select @Remark=isnull(GoodsRemark,''),@DeptRemark=isnull(DeptRemark,''),@NoticeDays=ISNULL(NoticeDays,0),@GoodsOldCode=GoodsOldCode from HW_GoodsInfo where GoodsId=1
|
|
if(select COUNT(0) from CG_PurchaseGoods a
|
|
inner join CG_Purchase b on a.ChaseId=b.ChaseId
|
|
where a.GoodsCode=@GoodsOldCode and b.BuyDate=DATEADD(day,@NoticeDays*-1,getdate()))=0
|
|
begin
|
|
select Remark=@Remark
|
|
end
|
|
else
|
|
begin
|
|
select Remark=@Remark+@DeptRemark
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
|
|
string a = Convert.ToString(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 删除采购单
|
|
public void DeletePurchase(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
|
|
update b set b.GoodsInNum=b.GoodsInNum-a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
update c set c.GoodsInNum=(select SUM(isnull(GoodsInNum,0)) from HW_GoodsDetail where GoodsId=c.GoodsId) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId
|
|
update CG_Purchase set IsDelete=1 where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
}
|
|
#endregion
|
|
|
|
#region 删除采购单
|
|
public void DeletePurchaseForTM(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set IsDelete=1 where ChaseId=@ChaseId
|
|
update b set b.GoodsInNum=b.GoodsInNum-a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
}
|
|
#endregion
|
|
#region 查询采购单货物编号
|
|
public List<CG_PurchaseGoods> GetGoodcode(int ChaseId)
|
|
{
|
|
List<CG_PurchaseGoods> list = null;
|
|
string tsql = @"
|
|
select DISTINCT GoodsCode from CG_PurchaseGoods
|
|
where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_PurchaseGoods>();
|
|
return list;
|
|
}
|
|
#endregion
|
|
#region 判断该货物编号是否有其他批次
|
|
public int CheckGoodcode(int ChaseId,string goodcode)
|
|
{
|
|
string tsql = @"
|
|
SELECT COUNT(*) FROM dbo.CG_PurchaseGoods WHERE GoodsCode='"+goodcode+@"' AND
|
|
ChaseId!=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 更新价格
|
|
public void UpdateGoodPrice(Decimal price, string goodcode)
|
|
{
|
|
string tsql = @"
|
|
UPDATE dbo.HW_GoodsInfo SET NowPrice=@price WHERE GoodsCode='" + goodcode + @"'
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@price", DbType.Decimal, price);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
}
|
|
#endregion
|
|
#region 更新价格
|
|
public void UpdateGoodPrice(Decimal price, int GoodsId)
|
|
{
|
|
string tsql = @"
|
|
UPDATE dbo.HW_GoodsInfo SET NowPrice=@price WHERE GoodsId='" + GoodsId + @"'
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@price", DbType.Decimal, price);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
}
|
|
#endregion
|
|
#region 审批采购单
|
|
public void CheckPurchase(int ChaseId, string Remark,string CheckContent, int checkstate)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set CheckContent=@CheckContent,Remark=@Remark,CheckDate=getdate(),state=@checkstate where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@CheckContent", DbType.String, CheckContent);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Remark);
|
|
db.AddInParameter(cmd, "@checkstate", DbType.Int32, checkstate);
|
|
db.ExecuteNonQuery(cmd);
|
|
}
|
|
#endregion
|
|
#region 修改采购单备注
|
|
public void UpdateChaseRemark(int ChaseId,string ErrorInfo, string Remark)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set Remark=@Remark,ErrorInfo=@ErrorInfo where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Remark);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, ErrorInfo);
|
|
db.ExecuteNonQuery(cmd);
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 返回采购Model
|
|
public CG_Purchase GetPurchaseModel(int ChaseId)
|
|
{
|
|
CG_Purchase model = null;
|
|
string tsql = @"select a.*,CheckName=b.Name from CG_Purchase a
|
|
left join JC_UserInfo b on a.CheckUid=b.UserId
|
|
where ChaseId=@ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CG_Purchase>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<CG_PurchaseGoods> GetPurchaseGoodsDeatil(int ChaseId)
|
|
{
|
|
|
|
List<CG_PurchaseGoods> list = null;
|
|
string tsql = @"
|
|
select * from CG_PurchaseGoods
|
|
where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_PurchaseGoods>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsSPDetail> GetPurchaseGoodsList(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsSPDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,a.ChaseId,a.GoodsCode,c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,a.Error,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,b.SafeNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsSPDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsSPDetail> GetPurchaseGoodsListNew(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsSPDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,a.ChaseId,a.GoodsCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,a.Error,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,PostionId=(select top 1 PostionId from HW_GoodsInDetail where DetailId=a.GoodsDetailId order by InId desc) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsSPDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsSPDetail> GetPurchaseTableGoods(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsSPDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsId,a.ChaseId,a.GoodsCode,c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,a.Error,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,b.SafeNum,a.ErrorNum,c.GoodsSupplyCode,c.FirstImgUrl,BackNum=isnull(a.BackNum,0) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,c.GoodsSupplyCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsSPDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsSPDetail> GetPurchaseTableGoods2(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsSPDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsId,a.ChaseId,a.GoodsCode,c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,a.Error,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,b.SafeNum,a.ErrorNum,c.GoodsSupplyCode,c.FirstImgUrl,BackNum=isnull(a.BackNum,0) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsSPDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询发往海外仓货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseOutGoods(string Ids)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsCode,d.ChaseCode,d.InDate,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=case when a.SJOutNum is null then a.OutNum else a.SJOutNum end, a.OutNum,a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum from CG_PurchaseGoods a
|
|
inner join CG_Purchase d on a.ChaseId=d.ChaseId
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.OutNum>0 and isnull(SJOutNum,0)=0 and a.Id in (" + Ids+") order by c.GoodsId,b.TypeCode";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询发往海外仓货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseOutGoods2(string Ids)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsCode,d.ChaseCode,d.InDate,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=case when a.SJOutNum is null then a.OutNum else a.SJOutNum end, a.OutNum,a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum from CG_PurchaseGoods a
|
|
inner join CG_Purchase d on a.ChaseId=d.ChaseId
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.OutNum>0 and a.Id in (" + Ids + ") order by c.GoodsId,b.TypeCode";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询发往海外仓货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseOutGoods22(string Ids)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsCode,d.ChaseCode,d.InDate,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=a.OutNum-isnull(a.TJOutNum,0),a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum from CG_PurchaseGoods a
|
|
inner join CG_Purchase d on a.ChaseId=d.ChaseId
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.Id in (" + Ids + ") order by c.GoodsId,b.TypeCode";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询发往海外仓货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseOutGoods222(string Ids)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsCode,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=a.OutNum-isnull(a.TJOutNum,0),a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.Id in (" + Ids + ") order by c.GoodsId,b.TypeCode";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 分页查询发往海外仓货物
|
|
public List<HW_GoodsHWCOutDetail> GetListOutGoodsInfo(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),d.ChaseCode,d.InDate,c.GoodsCode,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=a.OutNum-isnull(a.TJOutNum,0), OutNum=a.OutNum-isnull(a.TJOutNum,0),a.OutInNum,c.Solid,a.GoodsPrice,a.GoodsNum,IsAdd=1";
|
|
|
|
ser.Tables = @"CG_PurchaseGoods a
|
|
inner join CG_Purchase d on a.ChaseId=d.ChaseId
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<HW_GoodsHWCOutDetail> 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<HW_GoodsHWCOutDetail>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseGoodsOut(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,d.ChaseCode,d.InDate,c.GoodsCode,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=isnull(SJOutNum,0), a.OutNum,a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum from CG_PurchaseGoods a
|
|
inner join CG_Purchase d on a.ChaseId=d.ChaseId
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.GJChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseGoodsOut2(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsHWCOutDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,c.GoodsCode,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=isnull(SJOutNum,0), a.OutNum,a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum,IsAdd=0 from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.GJChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询采购货物
|
|
public List<HW_GoodsSPDetail> GetPurchaseGoods(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsSPDetail> list = null;
|
|
string tsql = @"
|
|
select GoodsCode=isnull(a.GoodsCode,''),c.GoodsOldCode,a.GoodsNum,a.GoodsPrice,c.GoodsName,b.TypeCode,b.TypeDesc,d.SupplierName from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
left join JC_Supplier d on c.Supplier=d.SupplierId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsSPDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购货物
|
|
public List<HW_GoodsCGDetail> GetPurchaseGoodsForTM(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsCGDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,C.GoodsId,a.ChaseId,PostionCode=isnull(a.PostionCode,''),GoodsCode=isnull(c.GoodsCode,''),c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,ErrorNum=0,Error='',c.FirstImgUrl from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsCGDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_GoodsCGDetail> GetPurchaseGoodsForTM3(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsCGDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,C.GoodsId,a.ChaseId,PostionCode=isnull(a.PostionCode,''),GoodsCode=isnull(c.GoodsCode,''),c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,ErrorNum=0,Error='',c.FirstImgUrl,SJOutNum=isnull(a.SJOutNum,0),OutInNum=isnull(a.OutInNum,0) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.GJChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsCGDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购货物
|
|
public List<HW_GoodsCGDetail> GetPurchaseGoodsForTM2(int ChaseId)
|
|
{
|
|
|
|
List<HW_GoodsCGDetail> list = null;
|
|
string tsql = @"
|
|
select a.Id,C.GoodsId,a.ChaseId,PostionCode=isnull(a.PostionCode,''),GoodsCode=isnull(c.GoodsCode,''),c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,ErrorNum=0,Error='',c.FirstImgUrl,OutNum=isnull(a.OutNum,0),SJOutNum=isnull(a.SJOutNum,0) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsCGDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购货物
|
|
public List<HW_ChaseGoods> GetPurchaseGoodsTable(int ChaseId)
|
|
{
|
|
|
|
List<HW_ChaseGoods> list = null;
|
|
string tsql = @"
|
|
select c.GoodsName,b.SKU1,b.TypeDesc,a.GoodsNum,a.GoodsPrice,d.PostionDesc from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
left join (
|
|
select distinct b.PostionCode,b.PostionDesc,a.DetailId from HW_GoodsInDetail a
|
|
inner join CK_StorePostion b on a.PostionId=b.PostionId
|
|
where a.PostionId>0 and a.DetailId in (select GoodsDetailId from CG_PurchaseGoods where ChaseId=@ChaseId))d
|
|
on a.GoodsDetailId=d.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_ChaseGoods>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询采购快递
|
|
public List<CG_PurchasePost> GetchasePostList(int ChaseId)
|
|
{
|
|
|
|
List<CG_PurchasePost> list = null;
|
|
string tsql = @"
|
|
select a.*,PostName=b.Name from CG_PurchasePost a
|
|
left join JC_BaseCodeDetail b on a.PostId=b.Code and b.Keyname='gnkd'
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_PurchasePost>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购快递
|
|
public List<CG_PurchasePost> GetchasePostListForTM(int ChaseId)
|
|
{
|
|
|
|
List<CG_PurchasePost> list = null;
|
|
string tsql = @"
|
|
select * from CG_PurchasePost
|
|
where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_PurchasePost>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 保存采购快递
|
|
/// <summary>
|
|
/// 保存采购快递
|
|
/// </summary>
|
|
public int SavePurchasePost(CG_PurchasePost Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_PurchasePost] set [ChaseId]=@ChaseId,[PostId]=@PostId,[PostCode]=@PostCode where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_PurchasePost]([ChaseId],[PostId],[PostCode])values(@ChaseId,@PostId,@PostCode)
|
|
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, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@PostId", DbType.Int32, Model.PostId);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, Model.PostCode);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 分页查询
|
|
public List<CG_Purchase> GetListCG_Purchase(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark,SimpErrorInfo=case when len(a.ErrorInfo)>20 then substring(a.ErrorInfo,0,20)+'...' else a.ErrorInfo end, a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.GoodsState=0 then '未到货' when a.GoodsState=1 then '部分到货' when a.GoodsState=2 then '全部到货' when a.GoodsState=3 then '到货异常' end";
|
|
ser.Tables = @"CG_Purchase a";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<CG_Purchase> 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<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询
|
|
public List<CG_Purchase> GetListCG_Purchase(int GoodsType,string Name,RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
string Name1 = "";
|
|
string Name2 = "";
|
|
if (Name != "" && Name.Contains(' '))
|
|
{
|
|
Name1 = Name.Split(' ')[0];
|
|
Name2 = Name.Split(' ')[1];
|
|
}
|
|
string sql1 = "";
|
|
|
|
if (Name1 != "" && Name2 != "")
|
|
{
|
|
sql1 = @"select a.ChaseId into #chasetemp1 from (select a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=1 and (c.GoodsCode like '%" + Name1 + "%' or c.GoodsOldCode like '%" + Name1 + "%') and b.TypeCode='" + Name2 + @"'
|
|
union
|
|
select a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=1 and (c.GoodsCode like '%" + Name1 + "%' or c.GoodsOldCode like '%" + Name1 + "%') and (c.GoodsName like '%" + Name2 + "%' or b.TypeDesc like '%" + Name2 + @"%'))a
|
|
";
|
|
}
|
|
else
|
|
if (GoodsType==3&&Name != "")
|
|
sql1+= @" select distinct a.ChaseId into #chasetemp1 from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.GoodsName like '%" + Name + "%' or c.GoodsOldCode like '%" + Name + "%' or c.GoodsCode like '%" + Name + "%' or b.TypeDesc like '%" + Name + "%'";
|
|
else if (GoodsType == 5 && Name != "")
|
|
sql1 += @" select distinct a.ChaseId into #chasetemp1 from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.GoodsSupplyCode like '%" + Name + "%' ";
|
|
else if (GoodsType == 6 && Name != "")
|
|
sql1 += @" select distinct ChaseId into #chasetemp1 from CG_PurchasePost
|
|
where PostCode like '%" + Name + "%' ";
|
|
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark,a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.BackDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.GoodsState=0 then '未到货' when a.GoodsState=1 then '部分到货' when a.GoodsState=2 then '全部到货' when a.GoodsState=3 then '到货异常' end,PostStateName=case when a.PostState=1 then '在途' when a.PostState=2 then '签收' when a.PostState=3 then '停滞' else '无物流' end,b.InGoodsNum";
|
|
if (sql1 != "")
|
|
ser.Tables = @"(select * from CG_Purchase where ChaseId in (select ChaseId from #chasetemp1))a
|
|
inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId
|
|
";
|
|
else
|
|
ser.Tables = @"CG_Purchase a inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
if (sql1 != "")
|
|
tsql = sql1 + " " + tsql;
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<CG_Purchase> ListModel = null;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
// db.AddInParameter(cmd, "@Name", DbType.String, Name);
|
|
db.AddInParameter(cmd, where);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
|
|
ListModel = tb.ToList<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
#region 分页查询
|
|
public List<CG_Purchase> GetListCG_Purchase(int GoodsType, string Name,string People, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
|
|
string sql1 = "";
|
|
|
|
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark,a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.GoodsState=0 then '未到货' when a.GoodsState=1 then '部分到货' when a.GoodsState=2 then '全部到货' when a.GoodsState=3 then '到货异常' end,PostStateName=case when a.PostState=1 then '在途' when a.PostState=2 then '签收' when a.PostState=3 then '停滞' else '无物流' end,b.InGoodsNum";
|
|
if (GoodsType == 16)
|
|
ser.Tables = @"CG_Purchase a inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId and a.InName like @Name+';%'";
|
|
if (GoodsType == 17)
|
|
ser.Tables = @"CG_Purchase a inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId and a.InName like '%'+@Name+'%'";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
if (sql1 != "")
|
|
tsql = sql1 + " " + tsql;
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<CG_Purchase> ListModel = null;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Name", DbType.String, People);
|
|
db.AddInParameter(cmd, where);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
|
|
ListModel = tb.ToList<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询采购单
|
|
public List<CG_Purchase> GetListCG_PurchaseNew(int CompanyId,string PostCode, string Name, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
string Name1 = "";
|
|
string Name2 = "";
|
|
if (Name != "" && Name.Contains(' '))
|
|
{
|
|
Name1 = Name.Split(' ')[0];
|
|
Name2 = Name.Split(' ')[1];
|
|
}
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark,SimpErrorInfo=case when len(a.ErrorInfo)>20 then substring(a.ErrorInfo,0,20)+'...' else a.ErrorInfo end, a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.GoodsState=0 then '未到货' when a.GoodsState=1 then '部分到货' when a.GoodsState=2 then '全部到货' when a.GoodsState=3 then '到货异常' end";
|
|
if (PostCode != "")
|
|
ser.Tables = @"(select * from CG_Purchase
|
|
where chaseid in (select distinct ChaseId from CG_PurchasePost
|
|
where PostCode=@PostCode))a";
|
|
else
|
|
if (Name1 != "" && Name2 != "")
|
|
{
|
|
ser.Tables = @"(select * from CG_Purchase where chaseid in (select distinct a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=@CompanyId and c.GoodsCode='" + Name1 + "' and b.TypeCode='" + Name2 + @"'))a";
|
|
|
|
}
|
|
// else if (Name != "")
|
|
// ser.Tables = @"CG_Purchase a
|
|
//inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId) b on a.ChaseId=b.ChaseId
|
|
//inner join
|
|
//(select ChaseId from (
|
|
// select distinct ChaseId from CG_PurchasePost where PostCode like '%'+@Name+'%'
|
|
// union
|
|
// select distinct a.ChaseId from CG_PurchaseGoods a
|
|
//inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
//inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
//where c.CompanyId=1 and (c.GoodsCode=@Name or c.GoodsOldCode=@Name or b.TypeDesc like '%'+@Name+'%' or c.GoodsName like '%'+@Name+'%' or b.SKU1 like '%'+@Name+'%')
|
|
//union
|
|
//select ChaseId from CG_Purchase where ChaseCode like '%'+@Name+'%' or BuyCode like '%'+@Name+'%')c on a.ChaseId=c.ChaseId
|
|
//
|
|
//";
|
|
else
|
|
if (PostCode == "" && Name != "")
|
|
ser.Tables = @"(select * from CG_Purchase where chaseid in (select distinct a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=@CompanyId and (c.GoodsCode like '%'+@Name+'%' or b.TypeDesc like '%'+@Name+'%' or c.GoodsName like '%'+@Name+'%' or b.SKU1 like '%'+@Name+'%')))a
|
|
";
|
|
else ser.Tables = @"CG_Purchase a
|
|
";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32); ;
|
|
List<CG_Purchase> ListModel = null;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, PostCode);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@Name", DbType.String, Name);
|
|
db.AddInParameter(cmd, where);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
|
|
ListModel = tb.ToList<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 分页查询采购单
|
|
public List<CG_Purchase> GetListCG_PurchaseForTM(int CompanyId, string PostCode, string Name, RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
string Name1 = "";
|
|
string Name2 = "";
|
|
if (Name != "" && Name.Contains(' '))
|
|
{
|
|
|
|
Name1 = Name.Split(' ')[0].Trim();
|
|
int len = Name.Split(' ').Length;
|
|
Name2 = Name.Split(' ')[len-1].Trim();
|
|
}
|
|
string sql1 = "";
|
|
if (PostCode != "")
|
|
{
|
|
sql1 = @"select ChaseId into #chasetemp from CG_PurchasePost
|
|
where PostCode=@PostCode";
|
|
}
|
|
if (Name1 != "" && Name2 != "")
|
|
{
|
|
sql1 = @"select a.ChaseId into #chasetemp from (select a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=@CompanyId and (c.GoodsCode like '%" + Name1 + "%' or c.GoodsOldCode like '%" + Name1 + "%') and b.TypeCode='" + Name2 + @"'
|
|
union
|
|
select a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=@CompanyId and (c.GoodsCode like '%" + Name1 + "%' or c.GoodsOldCode like '%" + Name1 + "%' or b.TypeDesc like '%" + Name1 + "%') and (c.GoodsName like '%" + Name2 + "%' or b.TypeDesc like '%" + Name2 + @"%'))a
|
|
";
|
|
}
|
|
else if (Name != "")
|
|
{
|
|
sql1 = @"select ChaseId into #chasetemp from (
|
|
select distinct ChaseId from CG_PurchasePost where PostCode like '%'+@Name+'%'
|
|
union
|
|
select distinct a.ChaseId from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where c.CompanyId=@CompanyId and (c.GoodsCode like '%'+@Name+'%' or c.GoodsOldCode like '%'+@Name+'%' or b.TypeDesc like '%'+@Name+'%' or c.GoodsName like '%'+@Name+'%' or b.SKU1 like '%'+@Name+'%')
|
|
union
|
|
select ChaseId from CG_Purchase where ChaseCode like '%'+@Name+'%' or BuyCode like '%'+@Name+'%')d";
|
|
|
|
}
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark, a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.GoodsState=0 then '未到货' when a.GoodsState=1 then '部分到货' when a.GoodsState=2 then '全部到货' when a.GoodsState=3 then '到货异常' end,b.InGoodsNum";
|
|
// if (PostCode != "")
|
|
// ser.Tables = @"(select * from CG_Purchase
|
|
//where chaseid in (select distinct ChaseId from CG_PurchasePost
|
|
//where PostCode=@PostCode))a
|
|
//inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId
|
|
//";
|
|
// else
|
|
// if (Name1 != "" && Name2 != "")
|
|
// {
|
|
// ser.Tables = @"(select * from CG_Purchase where chaseid in (select distinct a.ChaseId from CG_PurchaseGoods a
|
|
//inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
//inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
//where c.CompanyId=@CompanyId and c.GoodsCode='" + Name1 + "' and b.TypeCode='" + Name2 + @"'))a
|
|
//inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId) b on a.ChaseId=b.ChaseId
|
|
//";
|
|
|
|
// }
|
|
// else if (Name != "")
|
|
// ser.Tables = @"CG_Purchase a
|
|
//inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId) b on a.ChaseId=b.ChaseId
|
|
//inner join
|
|
//(select ChaseId from (
|
|
// select distinct ChaseId from CG_PurchasePost where PostCode like '%'+@Name+'%'
|
|
// union
|
|
// select distinct a.ChaseId from CG_PurchaseGoods a
|
|
//inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
//inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
//where c.CompanyId=@CompanyId and (c.GoodsCode=@Name or c.GoodsOldCode=@Name or b.TypeDesc like '%'+@Name+'%' or c.GoodsName like '%'+@Name+'%' or b.SKU1 like '%'+@Name+'%')
|
|
//union
|
|
//select ChaseId from CG_Purchase where ChaseCode like '%'+@Name+'%' or BuyCode like '%'+@Name+'%')d)c on a.ChaseId=c.ChaseId
|
|
//";
|
|
// else
|
|
// if (PostCode != "" && Name != "")
|
|
// ser.Tables = @"(select * from CG_Purchase where chaseid in (select distinct ChaseId from CG_PurchasePost
|
|
//where PostCode=@PostCode) and chaseid in (select distinct a.ChaseId from CG_PurchaseGoods a
|
|
//inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
//inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
//where c.CompanyId=@CompanyId and (c.GoodsCode like '%'+@Name+'%' or b.TypeDesc like '%'+@Name+'%' or c.GoodsName like '%'+@Name+'%' or b.SKU1 like '%'+@Name+'%')))a
|
|
//inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId) b on a.ChaseId=b.ChaseId
|
|
//";
|
|
if(sql1!="")
|
|
ser.Tables = @"(select * from CG_Purchase where ChaseId in (select ChaseId from #chasetemp))a
|
|
inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId
|
|
";
|
|
else
|
|
ser.Tables = @"CG_Purchase a
|
|
inner join (select ChaseId,InGoodsNum=SUM(isnull(InGoodsNum,0)) from CG_PurchaseGoods group by ChaseId)b on a.ChaseId=b.ChaseId
|
|
";
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
if (sql1 != "")
|
|
tsql = sql1 + " " + tsql;
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<CG_Purchase> ListModel = null;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, PostCode);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@Name", DbType.String, Name);
|
|
db.AddInParameter(cmd, where);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
RowCount = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
|
|
ListModel = tb.ToList<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 查询采购明细
|
|
public List<CG_PurchaseGoods> GetchaseGoodsList(string Ids)
|
|
{
|
|
|
|
List<CG_PurchaseGoods> list = null;
|
|
string tsql = @"
|
|
select a.Id,FirstImgUrl=case when b.FirstImgUrl is not null then b.FirstImgUrl else c.FirstImgUrl end, a.ChaseId,c.GoodsId,c.GoodsOldCode,a.GoodsCode,a.GoodsDetailId,a.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,InGoodsNum=isnull(a.InGoodsNum,0),a.Error,c.GoodsName,b.SKU1,b.TypeDesc,b.TypeCode,OutNum=isnull(a.OutNum,0) from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId in (" + Ids + ") order by c.GoodsId,b.TypeCode";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Ids", DbType.String, Ids);
|
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_PurchaseGoods>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 删除采购货物明细
|
|
public void DeletePurchaseGoods(int Id)
|
|
{
|
|
string tsql = @"
|
|
declare @DetailId int,@GoodsNum int
|
|
select top 1 @DetailId=GoodsDetailId,@GoodsNum=GoodsNum from CG_PurchaseGoods where Id=@Id
|
|
update HW_GoodsDetail set GoodsInNum=isnull(GoodsInNum,0)-@GoodsNum where DetailId=@DetailId
|
|
delete from CG_PurchaseGoods 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 DeletechasePost(int Id)
|
|
{
|
|
string tsql = @"
|
|
delete from CG_PurchasePost 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 DeletechaseError(int Id)
|
|
{
|
|
string tsql = @"
|
|
delete from CG_ChaseInStore 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 SaveChaseInStore(CG_ChaseInStore Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_ChaseInStore] set [ChaseId]=@ChaseId,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[ErrorDesc]=@ErrorDesc,[InDate]=getdate(),[Remark]=@Remark,[State]=@State,[StoreId]=@StoreId where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],InUserId)values(@ChaseId,@GoodsDetailId,@GoodsNum,@ErrorDesc,getdate(),@Remark,@State,@StoreId,@InUserId)
|
|
set @Id=SCOPE_IDENTITY()
|
|
end
|
|
if @State=1 and @AddNum>0--采购入库
|
|
begin
|
|
--declare @PostionId int
|
|
--set @PostionId=0
|
|
--select top 1 @PostionId=PostionId from HW_GoodsInList where DetailId=@GoodsDetailId and StoreId=@StoreId and PostionId>0 order by InId desc
|
|
INSERT INTO [HW_GoodsInList]([DetailId],[ChaseId],[Price],[GoodsNum],GoodsLockNum,[PostPrice],[StoreId],[PostionId],[InDate],[InUserId],GoodsOldNum)
|
|
values(@GoodsDetailId,@ChaseId,@Price,@AddNum,0,@PostPrice,@StoreId,@PostionId,getdate(),@InUserId,@AddNum)
|
|
end
|
|
select @Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.AddNum);
|
|
db.AddInParameter(cmd, "@ErrorDesc", DbType.String, Model.ErrorDesc);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, Model.PostPrice);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购入库明细
|
|
public int SaveChaseInStoreForTM(CG_ChaseInStore Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_ChaseInStore] set [ChaseId]=@ChaseId,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[ErrorDesc]=@ErrorDesc,[InDate]=@InDate,[Remark]=@Remark,[State]=@State,[StoreId]=@StoreId,[InUserId]=@InUserId,[IsCG]=@IsCG,[DoResult]=@DoResult,[DoDate]=@DoDate,[DoName]=@DoName where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
update CG_PurchaseGoods set ErrorNum=isnull(ErrorNum,0)+@GoodsNum where ChaseId=@ChaseId and GoodsDetailId=@GoodsDetailId
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],[InUserId],[IsCG],[DoResult],[DoDate],[DoName])values(@ChaseId,@GoodsDetailId,@GoodsNum,@ErrorDesc,@InDate,@Remark,@State,@StoreId,@InUserId,@IsCG,@DoResult,@DoDate,@DoName)
|
|
set @Id=SCOPE_IDENTITY()
|
|
update CG_Purchase set DHError=1,GoodsState=1,PostionCode=@PostionCode where ChaseId=@ChaseId and GoodsState=0
|
|
end
|
|
select @Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@ErrorDesc", DbType.String, Model.ErrorDesc);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@IsCG", DbType.Int32, Model.IsCG);
|
|
db.AddInParameter(cmd, "@DoResult", DbType.Int32, Model.DoResult);
|
|
db.AddInParameter(cmd, "@DoDate", DbType.DateTime, Model.DoDate);
|
|
db.AddInParameter(cmd, "@DoName", DbType.String, Model.DoName);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 保存采购入库
|
|
public int SaveChaseInStoreNewForTM(CG_ChaseInStore Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_ChaseInStore] set [ChaseId]=@ChaseId,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[ErrorDesc]=@ErrorDesc,[InDate]=getdate(),[Remark]=@Remark,[State]=@State,[StoreId]=@StoreId where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],InUserId)values(@ChaseId,@GoodsDetailId,@GoodsNum,@ErrorDesc,getdate(),@Remark,@State,@StoreId,@InUserId)
|
|
set @Id=SCOPE_IDENTITY()
|
|
end
|
|
|
|
if @State=1 and @AddNum>0--采购入库
|
|
begin
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
end
|
|
--while @AddNum>0
|
|
if @AddNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum)values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,@InDate,@InUserId,0,null,@RKDetailId,@AddNum)
|
|
--set @AddNum=@AddNum-1
|
|
end
|
|
end
|
|
select @Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.AddNum);
|
|
db.AddInParameter(cmd, "@ErrorDesc", DbType.String, Model.ErrorDesc);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, Model.PostPrice);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@RKDetailId", DbType.Int32, Model.RKDetailId);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 保存预先库位
|
|
public void SaveChaseYXKW(int ChaseId, string PostionCode, string DPostionCode, int DetailId)
|
|
{
|
|
string tsql = @"
|
|
|
|
if @PostionCode<>''
|
|
begin
|
|
update CG_Purchase set PostionCode=@PostionCode where ChaseId=@ChaseId
|
|
end
|
|
if @DPostionCode<>''
|
|
begin
|
|
update CG_PurchaseGoods set PostionCode=@DPostionCode where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
end
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@DPostionCode", DbType.String, DPostionCode);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 保存采购异常处理入库
|
|
public void SaveChaseErrorInStoreForTM(CG_ChaseInStore Model)
|
|
{
|
|
string tsql = @"
|
|
declare @Price Decimal,@PostPrice Decimal
|
|
if @ChaseId>0
|
|
begin
|
|
select top 1 @Price=GoodsPrice from CG_PurchaseGoods where ChaseId=@ChaseId and GoodsDetailId=@GoodsDetailId
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId
|
|
|
|
end
|
|
if @AddNum>0--采购入库
|
|
begin
|
|
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionId=@PostionId
|
|
|
|
--while @AddNum>0
|
|
if @AddNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum)values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,0,null,@RKDetailId,@AddNum)
|
|
--set @AddNum=@AddNum-1
|
|
end
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.AddNum);
|
|
db.AddInParameter(cmd, "@ErrorDesc", DbType.String, Model.ErrorDesc);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, Model.PostionCode);
|
|
//db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
//db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, Model.PostPrice);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@RKDetailId", DbType.Int32, Model.RKDetailId);
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 保存采购异常备注
|
|
public void SaveChaseErrorInfo(int ChaseId, string ErrorInfo, int IsError)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set ErrorInfo=@ErrorInfo,IsError=@IsError where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32,ChaseId);
|
|
db.AddInParameter(cmd, "@IsError", DbType.Int32, IsError);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, ErrorInfo);
|
|
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 删除采购异常
|
|
public void DeleteChaseErrorTM(int ChaseId,int DetailId)
|
|
{
|
|
string tsql = @"
|
|
--update CG_PurchaseGoods set ErrorNum=0 where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
delete from CG_ChaseInStore where ChaseId=@ChaseId and GoodsDetailId=@DetailId and State=2 and DoResult is null
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.ExecuteNonQuery(cmd);
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 保存采购入库
|
|
public int SaveChaseInStoreNew(CG_ChaseInStore Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_ChaseInStore] set [ChaseId]=@ChaseId,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[ErrorDesc]=@ErrorDesc,[InDate]=getdate(),[Remark]=@Remark,[State]=@State,[StoreId]=@StoreId where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],InUserId)values(@ChaseId,@GoodsDetailId,@GoodsNum,@ErrorDesc,getdate(),@Remark,@State,@StoreId,@InUserId)
|
|
set @Id=SCOPE_IDENTITY()
|
|
end
|
|
if @State=1 and @AddNum>0--采购入库
|
|
begin
|
|
while @AddNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime])values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,@InDate,@InUserId,0,null)
|
|
set @AddNum=@AddNum-1
|
|
end
|
|
end
|
|
select @Id";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@AddNum", DbType.Int32, Model.AddNum);
|
|
db.AddInParameter(cmd, "@ErrorDesc", DbType.String, Model.ErrorDesc);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@Remark", DbType.String, Model.Remark);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, Model.PostPrice);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 查询采购入库明细
|
|
public List<CG_ChaseInStore> GetChaseIds(int CompanyId, DateTime? SDate, DateTime? EDate)
|
|
{
|
|
|
|
List<CG_ChaseInStore> list = null;
|
|
string tsql = @"
|
|
select distinct a.ChaseId from CG_ChaseInStore a
|
|
inner join CG_Purchase b on a.ChaseId=b.ChaseId
|
|
where (@SDate is null or a.InDate>=@SDate) and (@EDate is null or a.InDate<=@EDate) and b.CompanyId=@CompanyId";
|
|
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);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_ChaseInStore>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购入库明细
|
|
public List<CG_ChaseInStoreDetail> GetChaseInStoreList(int ChaseId)
|
|
{
|
|
|
|
List<CG_ChaseInStoreDetail> list = null;
|
|
string tsql = @"
|
|
select c.GoodsCode,c.GoodsOldCode,c.GoodsName,b.SKU1,b.TypeCode,b.TypeDesc,ErrorDesc=case when a.State=1 then '入库'+cast(a.GoodsNum as nvarchar(10)) +'个' else ErrorDesc+cast(a.GoodsNum as nvarchar(10)) +'个' end,d.Name,a.InDate from CG_ChaseInStore a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
left join JC_UserInfo d on a.InUserId=d.UserId
|
|
where ChaseId=@ChaseId order by c.GoodsId,b.TypeCode
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_ChaseInStoreDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购入库明细
|
|
public List<CG_ChaseInStore> GetChaseInStoreList(int ChaseId,int DetailId,int State)
|
|
{
|
|
|
|
List<CG_ChaseInStore> list = null;
|
|
string tsql = @"
|
|
select *,PostionId=1 from CG_ChaseInStore
|
|
where ChaseId=@ChaseId and (@DetailId=0 or GoodsDetailId=@DetailId) and (@State=0 or State=@State)";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, State);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_ChaseInStore>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购入库明细
|
|
public List<CG_ChaseInStore> GetChaseInStoreListForTM(int ChaseId, int DetailId, int State)
|
|
{
|
|
|
|
List<CG_ChaseInStore> list = null;
|
|
string tsql = @"
|
|
select *,PostionId=1 from CG_ChaseInStore
|
|
where ChaseId=@ChaseId and State=@State";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, State);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_ChaseInStore>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 删除采购入库明细
|
|
public void DeleteChaseInStore(int Id)
|
|
{
|
|
string tsql = @"
|
|
delete from CG_ChaseInStore 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 UpdateChaseInStore(int Id)
|
|
{
|
|
string tsql = @"
|
|
delete from CG_ChaseInStore 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 SaveInchaseGoods(HW_GoodsSPDetail Model)
|
|
{
|
|
string tsql = @"
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=@InGoodsNum,[Error]=@Error where Id=@Id
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, Model.DetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, Model.InGoodsNum);
|
|
db.AddInParameter(cmd, "@Error", DbType.String, Model.Error);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 采购货物明细修改
|
|
public void SaveInchaseGoodsForTM(int ChaseId,int UserId,decimal? Price, decimal? PostPrice, int GoodsDetailId, int InGoodsNum,int RKId, string Error, string PostionCode)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@StoreId int,@PostionId int
|
|
select top 1 @Id=Id from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
end
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=isnull(InGoodsNum,0)+@InGoodsNum,[Error]=@Error where Id=@Id
|
|
if @InGoodsNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum)
|
|
values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,0,null,@RKId,@InGoodsNum,@InGoodsNum)
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@RKId", DbType.Int32, RKId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32,GoodsDetailId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32,InGoodsNum);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@Error", DbType.String,Error);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, PostPrice);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 采购货物明细修改
|
|
public void SaveInchaseGoodsForTM2(int ChaseId, int UserId, decimal? Price, decimal? PostPrice, int GoodsDetailId, int InGoodsNum, int RKId, string Error, string PostionCode, int PostState)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@StoreId int,@PostionId int,@SJOutNum int,@OutInNum int
|
|
select top 1 @Id=Id,@SJOutNum=SJOutNum,@OutInNum=isnull(OutInNum,0) from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and GJChaseId=@ChaseId
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
update CG_Purchase set PostionCode=@PostionCode where ChaseId=@ChaseId
|
|
end
|
|
if @PState<7 and @SJOutNum=@OutInNum+@InGoodsNum
|
|
begin
|
|
set @PState=7
|
|
end
|
|
if @PState<6 and @SJOutNum>@OutInNum+@InGoodsNum
|
|
begin
|
|
set @PState=6
|
|
end
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=isnull(InGoodsNum,0)+@InGoodsNum,[OutInNum]=isnull(OutInNum,0)+@InGoodsNum,[Error]=@Error,PState=@PState where Id=@Id
|
|
if @InGoodsNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum)
|
|
values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,0,null,@RKId,@InGoodsNum,@InGoodsNum)
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@RKId", DbType.Int32, RKId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, InGoodsNum);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@Error", DbType.String, Error);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, PostPrice);
|
|
db.AddInParameter(cmd, "@PState", DbType.Int32, PostState);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 采购货物明细修改
|
|
public void SaveInchaseGoodsForTM2(int ChaseId,int GoodsDetailId, int OutNum)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@StoreId int,@PostionId int
|
|
select top 1 @Id=Id from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId
|
|
|
|
Update [CG_PurchaseGoods] set OutNum=@OutNum where Id=@Id
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
db.AddInParameter(cmd, "@OutNum", DbType.Int32, OutNum);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 采购货物明细修改
|
|
public void SaveInchaseGoodsForTM2(int ChaseId, int UserId, decimal? Price, decimal? PostPrice, int GoodsDetailId, int InGoodsNum, int RKId, string Error, string PostionCode, int OrderGoodsId, int OrderId)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@StoreId int,@PostionId int
|
|
select top 1 @Id=Id from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
end
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=isnull(InGoodsNum,0)+@InGoodsNum where Id=@Id
|
|
|
|
|
|
|
|
if @InGoodsNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum)
|
|
values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,@OrderGoodsId,null,@RKId,@InGoodsNum,@InGoodsNum)
|
|
|
|
update a set a.CKDetailId=d.Id from HW_GoodsInDetail a
|
|
inner join DT_OrderGoods b on a.OrderGoodsId=b.Id
|
|
inner join HW_GoodsOutList c on b.OrderId=c.OrderId
|
|
inner join HW_GoodsOutDetail d on c.OutId=d.OutId and b.DetailId=d.DetailId
|
|
where b.OrderId=@OrderId
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@RKId", DbType.Int32, RKId);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
db.AddInParameter(cmd, "@InGoodsNum", DbType.Int32, InGoodsNum);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@Error", DbType.String, Error);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Price);
|
|
db.AddInParameter(cmd, "@PostPrice", DbType.Decimal, PostPrice);
|
|
db.AddInParameter(cmd, "@OrderGoodsId", DbType.Int32, OrderGoodsId);
|
|
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 扫描货物入库
|
|
public void SaveGoodsIn(int ChaseId, int UserId, decimal? Price, decimal? PostPrice, int OrderGoodsId, int GoodsDetailId, string PostionCode)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int,@StoreId int,@PostionId int,@PostPrice Decimal(8,2),@Price Decimal(8,2)
|
|
set @PostPrice=0.0
|
|
set @Price=0.0
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId and PostFee>0 and GoodsNum>0
|
|
select top 1 @Id=Id,@Price=GoodsPrice from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
end
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=isnull(InGoodsNum,0)+1 where Id=@Id
|
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum)
|
|
values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,@OrderGoodsId,null,0,1,1)
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
db.AddInParameter(cmd, "@OrderGoodsId", DbType.Int32, OrderGoodsId);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 扫描货物入库
|
|
public int SaveGoodsIn2(int ChaseId, int UserId, decimal? Price, decimal? PostPrice, int OrderGoodsId, int GoodsDetailId, string PostionCode)
|
|
{
|
|
string tsql = @"
|
|
declare @InId int,@Id int,@StoreId int,@PostionId int,@PostPrice Decimal(8,2),@Price Decimal(8,2)
|
|
set @PostPrice=0.0
|
|
set @Price=0.0
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId and PostFee>0 and GoodsNum>0
|
|
select top 1 @Id=Id,@Price=GoodsPrice from CG_PurchaseGoods where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId
|
|
if @PostionCode<>''
|
|
begin
|
|
select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode
|
|
end
|
|
Update [CG_PurchaseGoods] set [InGoodsNum]=isnull(InGoodsNum,0)+1 where Id=@Id and InGoodsNum<GoodsNum
|
|
if(select count(0) from CG_PurchaseGoods where ChaseId=@ChaseId and InGoodsNum+isnull(BackNum,0)<GoodsNum)=0 --全部到货
|
|
begin
|
|
update CG_Purchase set GoodsState=2,State=3,DoneDate=getdate() where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set GoodsState=1 where ChaseId=@ChaseId and GoodsState=0
|
|
end
|
|
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum)
|
|
values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,@OrderGoodsId,null,0,1,1)
|
|
set @InId=SCOPE_IDENTITY()
|
|
select @InId
|
|
|
|
update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsInNum=dbo.GetBuyGoodsNum(b.DetailId) from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
db.AddInParameter(cmd, "@OrderGoodsId", DbType.Int32, OrderGoodsId);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostionCode", DbType.String, PostionCode);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, UserId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 扫描货物入库
|
|
public void SaveGoodsIn3(int InId, int OrderGoodsId)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsInDetail set OrderGoodsId=@OrderGoodsId where InId=@InId
|
|
update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsPlanNum=dbo.GetOrderGoodsNum(b.DetailId) from [HW_GoodsInDetail] a
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
where a.InId=@InId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@OrderGoodsId", DbType.Int32, OrderGoodsId);
|
|
db.AddInParameter(cmd, "@InId", DbType.Int32, InId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 修改采购单
|
|
public void UpdateChase(int ChaseId, string ErrorInfo, int State, int GoodsState)
|
|
{
|
|
string tsql = @"
|
|
if @State=3
|
|
begin
|
|
update CG_Purchase set GoodsState=@GoodsState,ErrorInfo=@ErrorInfo,state=@State where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set GoodsState=@GoodsState,ErrorInfo=@ErrorInfo where ChaseId=@ChaseId
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@ErrorInfo", DbType.String, ErrorInfo);
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, GoodsState);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, State);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改采购单
|
|
public void UpdateChaseForTM(int ChaseId, int State,int PostionId, int GoodsState, int IsCheck)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set GoodsState=@GoodsState,PostionId=@PostionId,IsCheck=@IsCheck,state=@State where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
db.AddInParameter(cmd, "@GoodsState", DbType.Int32, GoodsState);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, State);
|
|
db.AddInParameter(cmd, "@IsCheck", DbType.Int32, IsCheck);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 修改采购单
|
|
public void UpdateChaseStateForTM(int ChaseId,int IsDH,int IsCheck)
|
|
{
|
|
string tsql = @"
|
|
if @IsDH=0 and (select count(0) from CG_PurchaseGoods where ChaseId=@ChaseId and InGoodsNum<GoodsNum)>0
|
|
begin
|
|
update CG_Purchase set GoodsState=1,IsCheck=1 where ChaseId in (select top 1 ChaseId from CG_PurchaseGoods
|
|
where ChaseId=@ChaseId and InGoodsNum>0)
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set GoodsState=2,State=3,IsCheck=@IsCheck,DoneDate=getdate() where ChaseId=@ChaseId --全部到货结束采购单
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@IsCheck", DbType.Int32, IsCheck);
|
|
db.AddInParameter(cmd, "@IsDH", DbType.Int32, IsDH);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改采购单
|
|
public void UpdateChaseStateForTM2(int ChaseId, int PostState,int IsDH, int IsCheck)
|
|
{
|
|
string tsql = @"
|
|
if @PostState<7
|
|
begin
|
|
if @IsDH=0 and (select count(0) from CG_PurchaseGoods where GJChaseId=@ChaseId and OutInNum<SJOutNum)>0
|
|
begin
|
|
update CG_Purchase set PostState=6 where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set PostState=7,DoneDate=getdate() where ChaseId=@ChaseId --全部到货结束采购单
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set PostState=@PostState where ChaseId=@ChaseId
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostState", DbType.Int32, PostState);
|
|
db.AddInParameter(cmd, "@IsCheck", DbType.Int32, IsCheck);
|
|
db.AddInParameter(cmd, "@IsDH", DbType.Int32, IsDH);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改采购单
|
|
public void UpdateChaseStateForTM3(int ChaseId, int PostState)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set PostState=@PostState where ChaseId=@ChaseId
|
|
update CG_PurchaseGoods set PState=@PostState where GJChaseId=@ChaseId and PState<6
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostState", DbType.Int32, PostState);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 修改采购单
|
|
public void UpdateChaseStateForHWC(int ChaseId, int PostState)
|
|
{
|
|
string tsql = @"
|
|
update CG_PurchaseHWC set PostState=@PostState where ChaseId=@ChaseId
|
|
update CG_PurchaseGoodsHWC set PState=@PostState where ChaseId=@ChaseId and PState<6
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostState", DbType.Int32, PostState);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改采购单
|
|
public void UpdateChaseCheck(int ChaseId, int IsCheck)
|
|
{
|
|
string tsql = @"
|
|
update CG_Purchase set IsCheck=@IsCheck where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@IsCheck", DbType.Int32, IsCheck);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改在途数量
|
|
public void UpdateGoodsInNum(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update b set b.GoodsInNum=b.GoodsInNum-a.GoodsNum+a.InGoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId and a.GoodsNum-a.InGoodsNum>0 where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改库存数量
|
|
public void UpdateGoodsNum(int DetailId, int StoreId, int GoodsNum,decimal Price)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsNum=GoodsNum+@GoodsNum,GoodsInNum=isnull(GoodsInNum,0)-@GoodsNum,Price=@Price where DetailId=@DetailId
|
|
|
|
update a set a.GoodsNum=(select SUM(isnull(GoodsNum,0)) from HW_GoodsDetail where GoodsId=b.GoodsId),a.GoodsInNum=(select SUM(isnull(GoodsInNum,0)) from HW_GoodsDetail where GoodsId=b.GoodsId) from HW_GoodsInfo a
|
|
inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
where b.DetailId=@DetailId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, StoreId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Price);
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 修改库存数量
|
|
public void UpdateGoodsNumForTM(int DetailId, int GoodsNum,int GoodsInNum, decimal Price)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsNum=GoodsNum+@GoodsNum,GoodsInNum=isnull(GoodsInNum,0)-@GoodsInNum where DetailId=@DetailId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@GoodsInNum", DbType.Int32, GoodsInNum);
|
|
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Price);
|
|
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改购买途中数量
|
|
public void UpdateBuyInGoodsNum(int DetailId, int GoodsNum)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsInNum=isnull(GoodsInNum,0)+@GoodsNum where DetailId=@DetailId
|
|
-- update a set a.GoodsInNum=isnull(a.GoodsInNum,0)+@GoodsNum from HW_GoodsInfo a
|
|
-- inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
-- where b.DetailId=@DetailId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改在途数量
|
|
public void UpdateGoodsInNumForTM(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update b set b.GoodsInNum=b.GoodsInNum+a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改购买途中数量
|
|
public void UpdateBuyInGoodsNumForTM(int DetailId, int GoodsNum,decimal? Price)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsInNum=isnull(GoodsInNum,0)+@GoodsNum where DetailId=@DetailId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, GoodsNum);
|
|
db.AddInParameter(cmd, "@Price", DbType.Int32, Price);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 修改购买途中数量
|
|
public void UpdateBuyInGoodsNum(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update b set b.GoodsInNum=isnull(b.GoodsInNum,0)-a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
|
|
update c set c.GoodsInNum=isnull(c.GoodsInNum,0)-a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 结束采购单修改购买途中数量
|
|
public void UpdateErrorInGoodsNum(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update b set b.GoodsInNum=isnull(b.GoodsInNum,0)-a.GoodsNum from CG_ChaseInStore a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId and a.State=2 and a.ErrorDesc<>'多发货'
|
|
|
|
update c set c.GoodsInNum=(select SUM(isnull(GoodsInNum,0)) from HW_GoodsDetail where GoodsId=c.GoodsId) from CG_ChaseInStore a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId and a.State=2 and a.ErrorDesc<>'多发货'
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 验证直接入库
|
|
public int IsChaseInStore(int ChaseId)
|
|
{
|
|
string tsql = @"select count(0) from CG_ChaseInStore where ChaseId=@ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
|
|
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
|
|
}
|
|
#endregion
|
|
#region 验证直接入库
|
|
public int IsChaseInStore2(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
declare @Id int
|
|
set @Id=0
|
|
select top 1 @Id=Id from CG_PurchaseGoods
|
|
where (InGoodsNum>0 or BackNum>0) and ChaseId=@ChaseId
|
|
select Id=@Id
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 采购直接入库
|
|
public void UpdateChaseInStore(int ChaseId, int UserId,int CompanyId)
|
|
{
|
|
string tsql = @"
|
|
declare @StoreId int,@PostPrice money
|
|
set @PostPrice=0
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId and GoodsNum>0
|
|
select top 1 @StoreId=StoreId from CK_StoreHouse where IsDefault=1 and CompanyId=@CompanyId
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[InDate],[State],[StoreId],[InUserId])
|
|
select [ChaseId],[GoodsDetailId],[GoodsNum],getdate(),1,@StoreId,@UserId from CG_PurchaseGoods where ChaseId=@ChaseId
|
|
INSERT INTO [HW_GoodsInList]([DetailId],[ChaseId],[Price],[GoodsNum],[PostPrice],[StoreId],[PostionId],[InDate],[InUserId],[GoodsLockNum],GoodsOldNum)
|
|
select [GoodsDetailId],[ChaseId],GoodsPrice,[GoodsNum],PostPrice=@PostPrice,@StoreId,PostionId=isnull((select top 1 PostionId from HW_GoodsInList where StoreId=@StoreId and DetailId=a.GoodsDetailId order by InId desc),0),getdate(),@UserId,0,GoodsNum from CG_PurchaseGoods a
|
|
where ChaseId=@ChaseId
|
|
|
|
Update [CG_Purchase] set GoodsState=2,State=3 where ChaseId=@ChaseId
|
|
|
|
Update [CG_PurchaseGoods] set InGoodsNum=GoodsNum where ChaseId=@ChaseId
|
|
|
|
update b set b.GoodsNum=b.GoodsNum+a.GoodsNum,b.GoodsInNum=isnull(b.GoodsInNum,0)-a.GoodsNum,b.Price=a.GoodsPrice from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
|
|
update c set c.GoodsNum=(select SUM(isnull(GoodsNum,0)) from HW_GoodsDetail where GoodsId=c.GoodsId),c.GoodsInNum=(select SUM(isnull(GoodsInNum,0)) from HW_GoodsDetail where GoodsId=c.GoodsId) from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
}
|
|
#endregion
|
|
#region 采购直接入库
|
|
public void UpdateChaseInStoreNew(int ChaseId, int UserId, int CompanyId)
|
|
{
|
|
string tsql = @"
|
|
declare @StoreId int
|
|
select top 1 @StoreId=StoreId from CK_StoreHouse where IsDefault=1 and CompanyId=@CompanyId
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[InDate],[State],[StoreId],[InUserId])
|
|
select [ChaseId],[GoodsDetailId],[GoodsNum],getdate(),1,@StoreId,@UserId from CG_PurchaseGoods where ChaseId=@ChaseId
|
|
|
|
Update [CG_Purchase] set GoodsState=2,State=3 where ChaseId=@ChaseId
|
|
|
|
Update [CG_PurchaseGoods] set InGoodsNum=GoodsNum where ChaseId=@ChaseId
|
|
|
|
update b set b.GoodsNum=b.GoodsNum+a.GoodsNum,b.GoodsInNum=isnull(b.GoodsInNum,0)-a.GoodsNum,b.Price=a.GoodsPrice from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 采购直接入库
|
|
public void UpdateGoodsInDetailNew(HW_GoodsInList Model, int CompanyId)
|
|
{
|
|
string tsql = @"
|
|
declare @StoreId int,@PostPrice money
|
|
set @PostPrice=0
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId and GoodsNum>0
|
|
select top 1 @StoreId=StoreId from CK_StoreHouse where IsDefault=1 and CompanyId=@CompanyId
|
|
|
|
while @GoodsNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime])values(@DetailId,@ChaseId,@StoreId,0,@Price,@PostPrice,@InDate,@InUserId,0,null)
|
|
set @GoodsNum=@GoodsNum-1
|
|
end
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, Model.DetailId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 采购直接入库
|
|
public void UpdateGoodsInDetailForTM(HW_GoodsInList Model, int CompanyId)
|
|
{
|
|
string tsql = @"
|
|
declare @PostPrice money
|
|
set @PostPrice=0
|
|
select @PostPrice=PostFee/GoodsNum from CG_Purchase where ChaseId=@ChaseId and GoodsNum>0
|
|
select top 1 @StoreId=StoreId from CK_StorePostion where PostionId=@PostionId
|
|
|
|
--while @GoodsNum>0
|
|
if @GoodsNum>0
|
|
begin
|
|
INSERT INTO [HW_GoodsInDetail]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum)values(@DetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,@InDate,@InUserId,0,null,@RKDetailId,@GoodsNum)
|
|
--set @GoodsNum=@GoodsNum-1
|
|
end
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, Model.DetailId);
|
|
db.AddInParameter(cmd, "@Price", DbType.Decimal, Model.Price);
|
|
db.AddInParameter(cmd, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@InUserId", DbType.Int32, Model.InUserId);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@StoreId", DbType.Int32, Model.StoreId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, Model.PostionId);
|
|
db.AddInParameter(cmd, "@RKDetailId", DbType.Int32, Model.RKDetailId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 采购直接入库
|
|
public void UpdateChaseInStoreForTM(int ChaseId,int PostionId, int UserId, int CompanyId)
|
|
{
|
|
string tsql = @"
|
|
--INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[InDate],[State],[StoreId],[InUserId])
|
|
--select [ChaseId],[GoodsDetailId],[GoodsNum],getdate(),1,@PostionId,@UserId from CG_PurchaseGoods where ChaseId=@ChaseId
|
|
|
|
Update [CG_Purchase] set GoodsState=2,State=3,DoneDate=getdate() where ChaseId=@ChaseId
|
|
|
|
Update [CG_PurchaseGoods] set InGoodsNum=GoodsNum where ChaseId=@ChaseId
|
|
|
|
update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsInNum=dbo.GetBuyGoodsNum(b.DetailId) from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@PostionId", DbType.Int32, PostionId);
|
|
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 修改库存，在途
|
|
public void UpdateChaseGoodsNum(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsInNum=dbo.GetBuyGoodsNum(b.DetailId) from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 修改库存，在途
|
|
public void UpdateChaseGoodsNum2(int DetailId)
|
|
{
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsNum=dbo.GetStoreGoodsNum(DetailId),GoodsInNum=dbo.GetBuyGoodsNum(DetailId) where DetailId=@DetailId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 查询需要采购货物
|
|
public DataTable GetNeedBuyGoodsList(int CompanyId)
|
|
{
|
|
|
|
DataTable dt = null;
|
|
string tsql = @"
|
|
select a.GoodsCode,a.GoodsName,b.SKU1,b.TypeDesc,b.GoodsNum,b.GoodsPlanNum,b.GoodsInNum,LeftNum=isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0),a.SupplierId from HW_GoodsInfo a
|
|
inner join HW_GoodsDetail b on a.GoodsId=b.GoodsId
|
|
where a.CompanyId=@CompanyId and (isnull(b.GoodsNum,0)+isnull(b.GoodsInNum,0)-isnull(b.GoodsPlanNum,0))<0
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
dt = db.ExecuteDataTable(cmd);
|
|
|
|
return dt;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 采购物流导入
|
|
public int SaveChasePost(string ChaseOrderCode, string PostCode, string PostInfo)
|
|
{
|
|
string tsql = @"
|
|
declare @ChaseId int,@Id int
|
|
select top 1 @ChaseId=ChaseId from [CG_Purchase] where BuyCode=@ChaseOrderCode
|
|
select top 1 @Id=Id from [CG_PurchasePost] where PostCode=@PostCode and ChaseId=@ChaseId
|
|
if @Id>0
|
|
begin
|
|
if(select count(0) from CG_PurchasePost where Id=@Id and CAST(PostInfo as nvarchar(4000))=@PostInfo)=0
|
|
begin
|
|
Update [CG_PurchasePost] set PostInfo=@PostInfo,PostDate=getdate() where Id=@Id
|
|
if CHARINDEX('签收',@PostInfo)>0
|
|
begin
|
|
update CG_Purchase set PostState=2,PostDoneDate=getdate() where ChaseId=@ChaseId
|
|
end
|
|
end
|
|
|
|
end
|
|
else if @ChaseId>0
|
|
begin
|
|
INSERT INTO [CG_PurchasePost]([ChaseId],[PostId],[PostCode],PostInfo,PostDate)
|
|
values(@ChaseId,0,@PostCode,@PostInfo,getdate())
|
|
if @PostCode<>''
|
|
begin
|
|
update CG_Purchase set PostState=1,PostDate=getdate() where ChaseId=@ChaseId and GoodsState=0
|
|
end
|
|
if CHARINDEX('签收',@PostInfo)>0
|
|
begin
|
|
update CG_Purchase set PostState=2,PostDoneDate=getdate() where ChaseId=@ChaseId
|
|
end
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseOrderCode", DbType.String, ChaseOrderCode);
|
|
db.AddInParameter(cmd, "@PostCode", DbType.String, PostCode);
|
|
db.AddInParameter(cmd, "@PostInfo", DbType.String, PostInfo);
|
|
return db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 保存采购分配
|
|
public int SaveGoodsMate(CG_GoodsMate Model)
|
|
{
|
|
string tsql = @"
|
|
if @Id>0
|
|
begin
|
|
Update [CG_GoodsMate] set [GoodsId]=@GoodsId,[BuyerUserId]=@BuyerUserId,[InDate]=@InDate,[BuyDate]=@BuyDate,[State]=@State,[CompanyId]=@CompanyId where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_GoodsMate]([GoodsId],[BuyerUserId],[InDate],[BuyDate],[State],[CompanyId])values(@GoodsId,@BuyerUserId,@InDate,@BuyDate,@State,@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, "@GoodsId", DbType.Int32, Model.GoodsId);
|
|
db.AddInParameter(cmd, "@BuyerUserId", DbType.Int32, Model.BuyerUserId);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 分页查询采购分配
|
|
public List<CG_GoodsMate> GetListCG_GoodsMate(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.GoodsId,a.BuyerUserId,a.InDate,a.BuyDate,a.State,a.CompanyId,b.GoodsCode,b.GoodsName,BuyerName=c.Name,d.SupplierName,b.GoodsOldCode";
|
|
ser.Tables = @"CG_GoodsMate a inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
|
|
inner join JC_UserInfo c on a.BuyerUserId=c.UserId
|
|
left join JC_Supplier d on b.Supplier=d.SupplierId";
|
|
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<CG_GoodsMate> 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<CG_GoodsMate>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
#region 保存货物分配
|
|
public int UpdateGoodsMate(CG_GoodsMate Model)
|
|
{
|
|
string tsql = @"
|
|
declare @BuyerUserId int,@GoodsId int
|
|
set @GoodsId=0
|
|
set @BuyerUserId=0
|
|
select top 1 @GoodsId=GoodsId from HW_GoodsInfo where CompanyId=@CompanyId and (GoodsCode=@GoodsCode or GoodsOldCode=@GoodsCode)
|
|
if @GoodsId=0
|
|
begin
|
|
select -1
|
|
end
|
|
select top 1 @BuyerUserId=UserId from JC_UserInfo where CompanyId=@CompanyId and Name=@BuyerName and State=1
|
|
if @BuyerUserId=0
|
|
begin
|
|
select -2
|
|
end
|
|
if @GoodsId>0 and @BuyerUserId>0
|
|
begin
|
|
if @Id>0
|
|
begin
|
|
Update [CG_GoodsMate] set [GoodsId]=@GoodsId,[BuyerUserId]=@BuyerUserId,[InDate]=@InDate,[BuyDate]=@BuyDate,[State]=@State,[CompanyId]=@CompanyId where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_GoodsMate]([GoodsId],[BuyerUserId],[InDate],[BuyDate],[State],[CompanyId])values(@GoodsId,@BuyerUserId,@InDate,@BuyDate,@State,@CompanyId)
|
|
set @Id=SCOPE_IDENTITY()
|
|
end
|
|
select @Id
|
|
end
|
|
else
|
|
begin
|
|
select 0
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
db.AddInParameter(cmd, "@BuyerName", DbType.String, Model.BuyerName);
|
|
db.AddInParameter(cmd, "@InDate", DbType.DateTime, Model.InDate);
|
|
db.AddInParameter(cmd, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, Model.State);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询未分配采购的货物
|
|
public List<CG_GoodsNoMate> GetNoMateList(int CompanyId)
|
|
{
|
|
|
|
List<CG_GoodsNoMate> list = null;
|
|
string tsql = @"
|
|
select distinct d.GoodsId,Supplier=isnull(d.Supplier,0) from HW_GoodsDetail a
|
|
inner join HW_GoodsInfo d on a.GoodsId=d.GoodsId
|
|
where d.CompanyId=@CompanyId and (a.GoodsNum+isnull(a.GoodsInNum,0)-isnull(a.GoodsPlanNum,0))<0";
|
|
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<CG_GoodsNoMate>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 删除分配采购的货物
|
|
public void DeleteBuyGoodsMate(int Id)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
delete from CG_GoodsMate 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 DeleteAllBuyGoodsMate()
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
delete from CG_GoodsMate
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 调整库存
|
|
public void KCChnage()
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
update HW_GoodsDetail set GoodsNum=0,GoodsInNum=0,GoodsPlanNum=0
|
|
update a set a.GoodsPlanNum=b.GoodsNum from HW_GoodsDetail a
|
|
inner join (
|
|
select a.DetailId,GoodsNum=SUM(a.GoodsNum) from DT_OrderGoods a
|
|
inner join DT_OrderInfo b on a.OrderId=b.OrderId
|
|
where a.DetailId>0 and b.State=1 and b.PrintState=0 group by a.DetailId)b on a.DetailId=b.DetailId
|
|
where a.GoodsPlanNum<>b.GoodsNum
|
|
|
|
update a set a.GoodsNum=b.GoodsNum from HW_GoodsDetail a
|
|
inner join (
|
|
select a.DetailId,GoodsNum=SUM(a.KCNum) from HW_GoodsInDetail a
|
|
where isnull(CKDetailId,0)=0 group by a.DetailId)b on a.DetailId=b.DetailId
|
|
where a.GoodsNum<>b.GoodsNum
|
|
|
|
update a set a.GoodsInNum=b.GoodsNum from HW_GoodsDetail a
|
|
inner join (
|
|
select b.GoodsDetailId,GoodsNum=SUM(b.GoodsNum-isnull(b.InGoodsNum,0)-isnull(b.BackNum,0)) from CG_Purchase a
|
|
inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
|
|
where a.IsDelete=0 and isnull(a.IsFactory,0)=0 and isnull(a.IsBH,0)=0 and a.State<3
|
|
group by b.GoodsDetailId)b on a.DetailId=b.GoodsDetailId
|
|
where a.GoodsInNum<>b.GoodsNum
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 分配采购的货物
|
|
public void BuyGoodsMate(int CompanyId, int GoodsId, int SupplierId)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
|
|
declare @BuyUserId int
|
|
set @BuyUserId=0
|
|
declare @ShopId int
|
|
select @BuyUserId=BuyerUserId from CG_GoodsMate where GoodsId=@GoodsId
|
|
if @BuyUserId=0
|
|
begin
|
|
if @SupplierId>0 --货物有供应商
|
|
begin
|
|
select @BuyUserId=isnull(BuyUserId,0) from HW_GoodsInfo a
|
|
inner join JC_Supplier b on a.Supplier=b.SupplierId
|
|
where a.GoodsId=@GoodsId
|
|
if @BuyUserId=0 --供应商没有分配采购人
|
|
begin
|
|
|
|
select top 1 @ShopId=ShopId from (
|
|
select b.shopid,Num=SUM(a.GoodsNum) from DT_OrderGoods a
|
|
inner join DT_OrderInfo b on a.OrderId=b.OrderId
|
|
inner join HW_GoodsDetail c on a.DetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
|
|
inner join JC_Shop e on b.ShopId=e.ShopId
|
|
where b.State=1 and b.CompanyId=@CompanyId and d.Supplier=@SupplierId
|
|
group by b.shopid)a order by Num desc
|
|
if @ShopId>0
|
|
begin
|
|
select top 1 @BuyUserId=UserId from JC_ShopUser where ShopId=@ShopId and SType='B'
|
|
end
|
|
end
|
|
end
|
|
|
|
|
|
if @BuyUserId=0
|
|
begin
|
|
select top 1 @ShopId=ShopId from (
|
|
select b.shopid,Num=SUM(a.GoodsNum) from DT_OrderGoods a
|
|
inner join DT_OrderInfo b on a.OrderId=b.OrderId
|
|
inner join HW_GoodsDetail c on a.DetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
|
|
inner join JC_Shop e on b.ShopId=e.ShopId
|
|
where b.State=1 and b.CompanyId=1 and d.GoodsId=@GoodsId
|
|
group by b.shopid)a order by Num desc
|
|
if @ShopId>0
|
|
begin
|
|
select top 1 @BuyUserId=UserId from JC_ShopUser where ShopId=@ShopId and SType='B'
|
|
end
|
|
end
|
|
|
|
if @BuyUserId>0 and @SupplierId>0
|
|
begin
|
|
INSERT INTO [CG_GoodsMate]([GoodsId],[BuyerUserId],[InDate],[BuyDate],[State],[CompanyId])
|
|
select [GoodsId],@BuyUserId,GETDATE(),null,0,@CompanyId from HW_GoodsInfo where Supplier=@SupplierId and State=1 and GoodsId not in (select GoodsId from CG_GoodsMate)
|
|
end
|
|
else
|
|
if @BuyUserId>0
|
|
begin
|
|
INSERT INTO [CG_GoodsMate]([GoodsId],[BuyerUserId],[InDate],[BuyDate],[State],[CompanyId])
|
|
values(@GoodsId,@BuyUserId,GETDATE(),null,0,@CompanyId)
|
|
end
|
|
else
|
|
if @BuyUserId=0
|
|
begin
|
|
INSERT INTO [CG_GoodsMate]([GoodsId],[BuyerUserId],[InDate],[BuyDate],[State],[CompanyId])
|
|
values(@GoodsId,0,GETDATE(),null,0,@CompanyId)
|
|
end
|
|
end
|
|
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@CompanyId", DbType.Int32, CompanyId);
|
|
db.AddInParameter(cmd, "@GoodsId", DbType.Int32, GoodsId);
|
|
db.AddInParameter(cmd, "@SupplierId", DbType.Int32, SupplierId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 查询未分配采购时间的订单
|
|
public List<CG_GoodsNoBuyDate> GetChaseGoodsNoBuyDate(int CompanyId)
|
|
{
|
|
|
|
List<CG_GoodsNoBuyDate> list = null;
|
|
string tsql = @"
|
|
select a.OrderId,b.DetailId,b.GoodsNum,IsBuy=1 from DT_OrderInfo a
|
|
inner join DT_OrderGoods b on a.OrderId=b.OrderId
|
|
where a.State=1 and b.DetailId>0 and a.CompanyId=@CompanyId and a.BuyDate is null order by a.OrderLevel";
|
|
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<CG_GoodsNoBuyDate>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询未分配采购时间的货物数量
|
|
public List<CG_GoodsNoBuyDateNum> GetChaseGoodsNoBuyDateNum(int CompanyId)
|
|
{
|
|
|
|
List<CG_GoodsNoBuyDateNum> list = null;
|
|
string tsql = @"
|
|
select a.DetailId,LeftNum=(a.GoodsNum+isnull(a.GoodsInNum,0)-isnull(a.GoodsPlanNum,0))*-1 from HW_GoodsDetail a
|
|
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
|
|
where b.CompanyId=@CompanyId and a.GoodsNum+isnull(a.GoodsInNum,0)-isnull(a.GoodsPlanNum,0)<0";
|
|
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<CG_GoodsNoBuyDateNum>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 查询取消分配采购时间的订单
|
|
public List<CG_GoodsNoBuyDate> GetChaseGoodsCancelBuyDate(int DetailId)
|
|
{
|
|
|
|
List<CG_GoodsNoBuyDate> list = null;
|
|
string tsql = @"
|
|
select a.OrderId,b.DetailId,b.GoodsNum,IsBuy=1 from DT_OrderInfo a
|
|
inner join DT_OrderGoods b on a.OrderId=b.OrderId
|
|
where a.State=1 and b.DetailId=@DetailId and a.BuyDate is not null order by a.OrderLevel";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_GoodsNoBuyDate>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
#region 修改订单购买时间
|
|
public void UpdateOrderBuyDate(int OrderId)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
update DT_OrderInfo set BuyDate=getdate() where OrderId=@OrderId
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 修改订单购买时间
|
|
public List<CG_GoodsNoBuyDateNum> UpdateOrderBuyDateForTM(int ChaseId)
|
|
{
|
|
List<CG_GoodsNoBuyDateNum> list = null;
|
|
string tsql = @"
|
|
update d set d.BuyDate=GETDATE() from DT_OrderGoods a
|
|
inner join DT_OrderInfo d on a.OrderId=d.OrderId
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
inner join CG_PurchaseGoods c on a.DetailId=c.GoodsDetailId
|
|
where c.ChaseId=@ChaseId and d.BuyDate is null and a.OrderId not in
|
|
(select a.OrderId from DT_OrderGoods a
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
inner join CG_PurchaseGoods c on a.DetailId=c.GoodsDetailId
|
|
where c.ChaseId=@ChaseId and b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum<0)
|
|
|
|
select a.DetailId,LeftNum=b.GoodsPlanNum-b.GoodsNum-b.GoodsInNum from DT_OrderGoods a
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
where a.OrderId in (select distinct b.OrderId from CG_PurchaseGoods a
|
|
inner join DT_OrderGoods b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId) and (b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum)<0
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_GoodsNoBuyDateNum>();
|
|
return list;
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 修改订单购买时间
|
|
public List<CG_GoodsNoBuyDateNum> GetOrderBuyDateForTM(int ChaseId)
|
|
{
|
|
List<CG_GoodsNoBuyDateNum> list = null;
|
|
string tsql = @"
|
|
select a.DetailId,LeftNum=b.GoodsPlanNum-b.GoodsNum-b.GoodsInNum from DT_OrderGoods a
|
|
inner join HW_GoodsDetail b on a.DetailId=b.DetailId
|
|
where a.OrderId in (select distinct b.OrderId from CG_PurchaseGoods a
|
|
inner join DT_OrderGoods b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId) and (b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum)<0
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_GoodsNoBuyDateNum>();
|
|
return list;
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 查询订单
|
|
public List<CG_GoodsNoBuyOrder> SelectOrderBuyForTM(int DetailId)
|
|
{
|
|
List<CG_GoodsNoBuyOrder> list = null;
|
|
string tsql = @"
|
|
select a.OrderId,a.BuyDate,b.GoodsNum,b.DetailId 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 b.DetailId=@DetailId and a.State=1 and a.MateState=0 and (c.GoodsNum+c.GoodsInNum-c.GoodsPlanNum)<0 order by a.OrderLevel,a.OrderDate desc
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_GoodsNoBuyOrder>();
|
|
return list;
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 修改订单购买时间
|
|
public void GetOrderBuyDate(string OrderIds)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
update DT_OrderInfo set BuyDate=getdate() where OrderId in ("+OrderIds+@")
|
|
update DT_OrderInfoNew set BuyDate=getdate() where OrderId in ("+OrderIds+@")
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 取消订单购买时间
|
|
public void CancelBuyDate(string OrderIds)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
update DT_OrderInfo set BuyDate=null where OrderId in ("+OrderIds+@")
|
|
update DT_OrderInfoNew set BuyDate=null where OrderId in ("+OrderIds+")";
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 取消订单购买时间
|
|
public void CancelOrderBuyDate(int OrderId)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
update DT_OrderInfo set BuyDate=null where OrderId=@OrderId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@OrderId", DbType.Int32, OrderId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 验证批次是否匹配
|
|
public int IsChaseMate(int ChaseId)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
select count(0) from HW_GoodsInDetail where ChaseId=@ChaseId and OrderGoodsId<>0
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
int a=Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 取消批次入库
|
|
public void CancelChaseIn(int ChaseId,int State)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
Update [CG_Purchase] set GoodsState=0,State=2 where ChaseId=@ChaseId
|
|
Update CG_PurchaseGoods set InGoodsNum=0,Error='' where ChaseId=@ChaseId
|
|
delete from CG_ChaseInStore where ChaseId=@ChaseId
|
|
|
|
if @State=3
|
|
begin
|
|
update b set b.GoodsNum=b.GoodsNum-a.GoodsNum,b.GoodsInNum=isnull(b.GoodsInNum,0)+a.GoodsNum from CG_PurchaseGoods a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
end
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@State", DbType.Int32, State);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 采购退款
|
|
public void ChaseBackMoney(CG_PurchaseBackMoney Model)
|
|
{
|
|
|
|
|
|
string tsql = @"
|
|
Update [CG_Purchase] set [BackType]=@BackType,[BackReason]=@BackReason,[BackDate]=@BackDate,[BackMoney]=@BackMoney,[BackRemark]=@BackRemark,[BackUserId]=@BackUserId where ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
db.AddInParameter(cmd, "@BackType", DbType.Int32, Model.BackType);
|
|
db.AddInParameter(cmd, "@BackReason", DbType.String, Model.BackReason);
|
|
db.AddInParameter(cmd, "@BackDate", DbType.DateTime, Model.BackDate);
|
|
db.AddInParameter(cmd, "@BackMoney", DbType.Decimal, Model.BackMoney);
|
|
db.AddInParameter(cmd, "@BackRemark", DbType.String, Model.BackRemark);
|
|
db.AddInParameter(cmd, "@BackUserId", DbType.Int32, Model.BackUserId);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 返回采购退款Model
|
|
public CG_PurchaseBackMoney GetBackMoneyModel(int ChaseId)
|
|
{
|
|
CG_PurchaseBackMoney model = null;
|
|
string tsql = "select a.BackType,a.BackReason,a.BackDate,a.BackMoney,a.BackRemark,a.BackUserId from CG_Purchase a where a.ChaseId=@ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CG_PurchaseBackMoney>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
#region 返回采购物流
|
|
public CG_PurchasePost GetWuLiu(int ChaseId)
|
|
{
|
|
CG_PurchasePost model = null;
|
|
string tsql = "select a.PostCode from CG_PurchasePost a where a.ChaseId=@ChaseId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CG_PurchasePost>();
|
|
return model;
|
|
}
|
|
#endregion
|
|
|
|
#region 查询采购剩余
|
|
public List<CG_GoodsLeft> GetBuyLeft()
|
|
{
|
|
List<CG_GoodsLeft> list = null;
|
|
string tsql = @"
|
|
select Name=isnull(Name,'无人'),Num=COUNT(0) from (
|
|
select distinct d.Name,b.GoodsId from HW_GoodsDetail a
|
|
inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId
|
|
left join CG_GoodsMate c on a.GoodsId=c.GoodsId
|
|
left join JC_UserInfo d on c.BuyerUserId=d.UserId
|
|
where a.GoodsNum+a.GoodsInNum-a.GoodsPlanNum<0 and isnull(b.NoGoods,0)=0 and isnull(a.NoGoods,0)=0)a group by Name";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<CG_GoodsLeft>();
|
|
return list;
|
|
|
|
|
|
}
|
|
#endregion
|
|
#region 查询采购剩余
|
|
public CG_ChaseModel GetChaseModel(int ChaseId, int DetailId)
|
|
{
|
|
CG_ChaseModel model = null;
|
|
string tsql = @"
|
|
select a.ChaseId,b.Id,a.ChaseCode,a.InName,a.InDate,d.GoodsName,d.GoodsCode,c.TypeCode,c.TypeDesc 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.ChaseId=@ChaseId and b.GoodsDetailId=@DetailId";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
DataTable tb = db.ExecuteDataTable(cmd);
|
|
if (tb.Rows.Count > 0) model = tb.Rows[0].ToModel<CG_ChaseModel>();
|
|
return model;
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 扫描退货
|
|
public void ScanChaseErrorIn(int ChaseId, int DetailId, int UserId, string DoResult, string InName)
|
|
{
|
|
|
|
string tsql = @"
|
|
update CG_PurchaseGoods set BackNum=isnull(BackNum,0)+1 where ChaseId=@ChaseId and GoodsDetailId=@DetailId
|
|
if(select count(0) from CG_PurchaseGoods where ChaseId=@ChaseId and InGoodsNum+isnull(BackNum,0)<GoodsNum)=0 --全部到货
|
|
begin
|
|
update CG_Purchase set GoodsState=2,State=3,DoneDate=getdate() where ChaseId=@ChaseId
|
|
end
|
|
else
|
|
begin
|
|
update CG_Purchase set GoodsState=1 where ChaseId=@ChaseId and GoodsState=0
|
|
end
|
|
declare @Id int
|
|
select top 1 @Id=Id from CG_ChaseInStore where ChaseId=@ChaseId and GoodsDetailId=@DetailId and DoResult=@DoResult
|
|
if @Id>0
|
|
begin
|
|
update CG_ChaseInStore set GoodsNum=isnull(GoodsNum,0)+1,GoodsInNum=isnull(GoodsInNum,0)+1 where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
INSERT INTO [CG_ChaseInStore]([ChaseId],[GoodsDetailId],[GoodsNum],[ErrorDesc],[InDate],[Remark],[State],[StoreId],[InUserId],[IsCG],[DoResult],[DoDate],[DoName],[PostCode],[GoodsInNum])
|
|
values(@ChaseId,@DetailId,1,'处理不了',GETDATE(),null,2,0,@UserId,1,@DoResult,GETDATE(),@InName,null,1)
|
|
end
|
|
update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsInNum=dbo.GetBuyGoodsNum(b.DetailId) from [CG_PurchaseGoods] a
|
|
inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId
|
|
where a.ChaseId=@ChaseId
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
db.AddInParameter(cmd, "@DetailId", DbType.Int32, DetailId);
|
|
db.AddInParameter(cmd, "@UserId", DbType.Int32, UserId);
|
|
db.AddInParameter(cmd, "@DoResult", DbType.String, DoResult);
|
|
db.AddInParameter(cmd, "@InName", DbType.String, InName);
|
|
db.ExecuteNonQuery(cmd);
|
|
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
#region 分页查询
|
|
public List<CG_PurchaseOrder> GetListCGGoodsInfoForOrder(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"OrderId=cast(a.OrderId as int),d.GoodsCode,d.GoodsName,c.TypeCode,c.TypeDesc,a.PlatOrderCode,a.JoinOrderCode,b.GoodsNum,FirstImgUrl=case when c.FirstImgUrl is not null and c.FirstImgUrl<>'' then c.FirstImgUrl else d.FirstImgUrl end,Price=case when c.Price is not null then c.Price else d.NowPrice end,c.DetailId,d.GoodsId,e.ShopName,a.InDate";
|
|
|
|
ser.Tables = @"DT_OrderInfoNew a
|
|
inner Join DT_OrderGoods b on a.OrderId=b.OrderId
|
|
inner join HW_GoodsDetail c on b.DetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
|
|
inner join jc_shop e on a.ShopId=e.ShopId
|
|
";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<CG_PurchaseOrder> 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<CG_PurchaseOrder>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 分页查询
|
|
public List<CG_PurchaseOrder> GetListCGGoodsInfoForOrder2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"OrderId=cast(c.OrderId as int),a.Account,c.PlatOrderCode,c.JoinOrderCode,a.ChaseId,a.ChaseCode,f.PostCode,a.BuyCode,a.BuyDate,BuyNum=b.GoodsNum,b.GoodsNum,Price=b.GoodsPrice,PostPrice=a.PostFee,e.GoodsCode,d.TypeCode,d.TypeDesc,d.SKU1";
|
|
ser.Tables = @"CG_Purchase a
|
|
inner join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
|
|
inner join DT_OrderInfoNew c on b.OrderId=c.OrderId
|
|
inner join HW_GoodsDetail d on b.GoodsDetailId=d.DetailId
|
|
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
|
|
left join CG_PurchasePost f on a.ChaseId=f.ChaseId
|
|
";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "GoodsId";
|
|
string tsql = ser.GetText();
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<CG_PurchaseOrder> 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<CG_PurchaseOrder>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
#region 删除海外仓
|
|
/// <summary>
|
|
/// 删除海外仓
|
|
/// </summary>
|
|
public int DeleteHWCGoods(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
delete from CG_Purchase where ChaseId=@ChaseId
|
|
update CG_PurchaseGoods set SJOutNum=0,PState=0,GJChaseId=0 where GJChaseId=@ChaseId
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
#region 删除海外仓
|
|
/// <summary>
|
|
/// 删除海外仓
|
|
/// </summary>
|
|
public int DeleteHWCGoods2(int Id)
|
|
{
|
|
string tsql = @"
|
|
declare @OldId int,@SJOutNum int
|
|
select @OldId=OldId,@SJOutNum=isnull(SJOutNum,0) from CG_PurchaseGoods where Id=@Id
|
|
if @OldId>0
|
|
begin
|
|
update CG_PurchaseGoods set TJOutNum=isnull(TJOutNum,0)-@SJOutNum where Id=@OldId
|
|
delete from CG_PurchaseGoods where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
update CG_PurchaseGoods set SJOutNum=0,TJOutNum=TJOutNum-SJOutNum,PState=0,GJChaseId=0 where Id=@Id
|
|
end
|
|
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Id);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存海外仓
|
|
/// <summary>
|
|
/// 保存海外仓
|
|
/// </summary>
|
|
public int UpdateHWCGoods2(HW_GoodsHWCOutDetail Model)
|
|
{
|
|
string tsql = @"
|
|
if(select count(0) from CG_PurchaseGoods where Id=@Id and GJChaseId>0)>0
|
|
begin
|
|
update CG_PurchaseGoods set TJOutNum=isnull(TJOutNum,0)+@SJOutNum where Id=@Id
|
|
INSERT INTO [CG_PurchaseGoods]([ChaseId],[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum],[ErrorNum],[Error],[BackNum],[PostionCode],[BoxNum],[BarCode],[OrderId],[OutNum],[SJOutNum],[OutInNum],[GJChaseId],[PState],[TJOutNum],OldId)
|
|
select [ChaseId]=0,[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum],[ErrorNum],[Error],[BackNum],[PostionCode],[BoxNum],[BarCode],[OrderId],OutNum=@SJOutNum,SJOutNum=@SJOutNum,0,GJChaseId=@GJChaseId,PState=@PState,TJOutNum=@SJOutNum,Id=@Id from [CG_PurchaseGoods] where Id=@Id
|
|
end
|
|
else
|
|
begin
|
|
update CG_PurchaseGoods set SJOutNum=@SJOutNum,TJOutNum=@SJOutNum,PState=@PState,GJChaseId=@GJChaseId where Id=@Id
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@PState", DbType.Int32, Model.PState);
|
|
db.AddInParameter(cmd, "@GJChaseId", DbType.Int32, Model.GJChaseId);
|
|
db.AddInParameter(cmd, "@SJOutNum", DbType.Int32, Model.SJOutNum);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 保存海外仓
|
|
/// <summary>
|
|
/// 保存海外仓
|
|
/// </summary>
|
|
public int UpdateHWCGoods(HW_GoodsHWCOutDetail Model)
|
|
{
|
|
string tsql = @"
|
|
declare @OldId int
|
|
select @OldId=OldId from CG_PurchaseGoods where Id=@Id
|
|
update CG_PurchaseGoods set SJOutNum=@SJOutNum,PState=@PState,GJChaseId=@GJChaseId,TJOutNum=@SJOutNum where Id=@Id
|
|
if @OldId>0
|
|
begin
|
|
declare @Num int
|
|
select @Num=sum(isnull(SJOutNum,0)) from CG_PurchaseGoods where OldId=@OldId
|
|
update CG_PurchaseGoods set TJOutNum=isnull(SJOutNum,0)+@Num where Id=@OldId
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@Id", DbType.Int32, Model.Id);
|
|
db.AddInParameter(cmd, "@PState", DbType.Int32, Model.PState);
|
|
db.AddInParameter(cmd, "@GJChaseId", DbType.Int32, Model.GJChaseId);
|
|
db.AddInParameter(cmd, "@SJOutNum", DbType.Int32, Model.SJOutNum);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
#region 分页查询海外批次
|
|
public List<CG_Purchase> GetListCG_PurchaseForHWC(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
|
|
|
|
if (where == null) where = new RefParameterCollection();
|
|
QueryService ser = new QueryService();
|
|
ser.Fields = @"ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark, a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.PostState=0 then '未出运'when a.PostState=3 then '准备出运' when a.PostState=4 then '出运海上' when a.PostState=5 then '出运海外' when a.PostState=6 then '部分入库' when a.PostState=7 then '全部入库' else '未出运' end,TSoild=isnull(a.TSoild,0),a.TCFee5,a.TCFee6";
|
|
ser.Tables = @"CG_Purchase a";
|
|
|
|
ser.Filter = where.GetWhere(System.Data.CommandType.Text);
|
|
ser.PageIndex = PageIndex;
|
|
ser.PageSize = PageSize;
|
|
ser.Sort = Sort;
|
|
ser.KeyName = "ChaseId";
|
|
string tsql = ser.GetText();
|
|
|
|
where.AddOutParameter("RowCount", System.Data.DbType.Int32);
|
|
List<CG_Purchase> 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<CG_Purchase>();
|
|
return ListModel;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 保存海外仓头程费计算
|
|
/// <summary>
|
|
/// 保存海外仓头程费计算
|
|
/// </summary>
|
|
public int UpdateHWCGoodsFee(int ChaseId)
|
|
{
|
|
string tsql = @"
|
|
--头程费
|
|
update c set HWCFee=a.TCFee6* b.SJOutNum*1.00/a.TSoild from CG_Purchase a
|
|
inner join CG_PurchaseGoods b on a.ChaseId=b.GJChaseId
|
|
inner join HW_GoodsDetail d on b.GoodsDetailId=d.DetailId
|
|
inner join HW_GoodsInfo e on d.GoodsId=e.GoodsId
|
|
inner join HW_GoodsInDetail c on a.ChaseId=c.ChaseId and b.GoodsDetailId=c.DetailId
|
|
where a.ChaseId=@ChaseId and a.TCFee6>0 and a.TSoild>0
|
|
|
|
update b set b.TCFee=a.hwcfee*b.GoodsNum from HW_GoodsInDetail a
|
|
inner join DT_OrderGoods b on a.OrderGoodsId=b.Id
|
|
inner join CG_PurchaseGoods c on a.ChaseId=c.ChaseId and a.DetailId=c.GoodsDetailId
|
|
where c.GJChaseId=@ChaseId and a.KCNum>0 and a.hwcfee>0 and c.ChaseId=1
|
|
|
|
|
|
update a set a.Fee3=TCFee from DT_OrderInfo a
|
|
inner join(select a.OrderId,TCFee=SUM(isnull(TCFee,0)) from DT_OrderGoods a
|
|
inner join HW_GoodsInDetail b on a.Id=b.OrderGoodsId
|
|
inner join CG_PurchaseGoods c on b.ChaseId=c.ChaseId and b.DetailId=c.GoodsDetailId
|
|
where c.GJChaseId=@ChaseId group by a.OrderId)b on a.OrderId=b.OrderId
|
|
where b.TCFee>0
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@ChaseId", DbType.Int32, ChaseId);
|
|
int a = Convert.ToInt32(db.ExecuteScalar(cmd));
|
|
return a;
|
|
}
|
|
#endregion
|
|
|
|
|
|
#region 查询海外货物
|
|
public List<HW_GoodsCGDetail> GetchaseGoodsDetail(int PostState,string GoodsCode)
|
|
{
|
|
|
|
List<HW_GoodsCGDetail> list = null;
|
|
string tsql = @"
|
|
if @PostState=2
|
|
begin
|
|
select b.ChaseCode,d.GoodsCode,c.TypeCode,c.TypeDesc,d.FirstImgUrl,GoodsNum=a.GoodsNum-isnull(a.InGoodsNum,0)-isnull(a.OutInNum,0)-isnull(a.ErrorNum,0) from CG_PurchaseGoods a
|
|
inner join CG_Purchase b on a.ChaseId=b.ChaseId
|
|
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
|
|
where b.IsDelete=0 and b.GoodsState<>3 and a.GoodsNum-isnull(a.InGoodsNum,0)-isnull(a.OutInNum,0)-isnull(a.ErrorNum,0)>0 and (@GoodsCode='' or d.GoodsCode like '%'+@GoodsCode+'%')
|
|
end
|
|
else
|
|
begin
|
|
select b.ChaseCode,d.GoodsCode,c.TypeCode,c.TypeDesc,d.FirstImgUrl,GoodsNum=SJOutNum-isnull(OutInNum,0) from CG_PurchaseGoods a
|
|
inner join CG_Purchase b on a.GJChaseId=b.ChaseId
|
|
inner join HW_GoodsDetail c on a.GoodsDetailId=c.DetailId
|
|
inner join HW_GoodsInfo d on c.GoodsId=d.GoodsId
|
|
where b.IsGJ=1 and a.PState=@PostState and SJOutNum-isnull(OutInNum,0)>0 and (@GoodsCode='' or d.GoodsCode like '%'+@GoodsCode+'%')
|
|
end
|
|
";
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand cmd = db.GetSqlStringCommand(tsql);
|
|
db.AddInParameter(cmd, "@PostState", DbType.Int32, PostState);
|
|
db.AddInParameter(cmd, "@GoodsCode", DbType.String, GoodsCode);
|
|
|
|
DataSet ds = db.ExecuteDataSet(cmd);
|
|
list = ds.Tables[0].ToList<HW_GoodsCGDetail>();
|
|
return list;
|
|
|
|
}
|
|
#endregion
|
|
|
|
|
|
public List<HW_GoodsHWCOutDetail> GetListOutGoodsInfo2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null)
|
|
{
|
|
where = new RefParameterCollection();
|
|
}
|
|
string text = new QueryService
|
|
{
|
|
Fields = "Id=0,a.detailId,b.GoodsCode,b.GoodsName,a.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else a.FirstImgUrl end,SKU=a.SKU1,Solid=isnull(b.Solid,0),GoodsPrice=isnull(b.InPrice,0)",
|
|
Tables = "HW_GoodsDetail a inner join HW_GoodsInfo b on a.GoodsId=b.GoodsId",
|
|
Filter = where.GetWhere(CommandType.Text),
|
|
PageIndex = PageIndex,
|
|
PageSize = PageSize,
|
|
Sort = Sort,
|
|
KeyName = "GoodsId"
|
|
}.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<HW_GoodsHWCOutDetail>();
|
|
}
|
|
public int SavePurchaseForHWC(CG_PurchaseHWC Model)
|
|
{
|
|
string query = " if @ChaseId>0 begin Update [CG_PurchaseHWC] set [BuyCode]=@BuyCode,[PostFee]=@PostFee,[Remark]=@Remark,[ErrorInfo]=@ErrorInfo,[BuyDate]=@BuyDate,[DoneDate]=@DoneDate,[Supplier]=@Supplier,[SupplierName]=@SupplierName,[SupplierPhone]=@SupplierPhone,[InName]=@InName,[InUserId]=@InUserId,[Account]=@Account,[IsDelete]=@IsDelete,[State]=@State,[CompanyId]=@CompanyId,[CheckUid]=@CheckUid,[CheckContent]=@CheckContent,[CheckDate]=@CheckDate,[GoodsNum]=@GoodsNum,[GoodsMoney]=@GoodsMoney,[IsFactory]=@IsFactory,[IsBH]=@IsBH,[PostState]=@PostState,[PostDate]=@PostDate,[BackDate]=@BackDate,[PostionCode]=@PostionCode,[IsError]=@IsError,[IsGJ]=@IsGJ,[TCFee1]=@TCFee1,[TCFee2]=@TCFee2,[TCFee3]=@TCFee3,[TCFee4]=@TCFee4,[TCFee5]=@TCFee5,[TCFee6]=@TCFee6,[TSoild]=@TSoild where ChaseId=@ChaseId end else begin INSERT INTO [CG_PurchaseHWC]([ChaseCode],[BuyCode],[GoodsState],[PostFee],[Remark],[ErrorInfo],[InDate],[BuyDate],[DoneDate],[Supplier],[SupplierName],[SupplierPhone],[InName],[InUserId],[Account],[IsDelete],[State],[CompanyId],[CheckUid],[CheckContent],[CheckDate],[GoodsNum],[GoodsMoney],[IsFactory],[IsBH],IsGJ,BackDate,TCFee1,TCFee2,TCFee3,TCFee4,TCFee5,TCFee6,TSoild)values(@ChaseCode,@BuyCode,@GoodsState,@PostFee,@Remark,@ErrorInfo,@InDate,@BuyDate,@DoneDate,@Supplier,@SupplierName,@SupplierPhone,@InName,@InUserId,@Account,@IsDelete,@State,@CompanyId,@CheckUid,@CheckContent,@CheckDate,@GoodsNum,@GoodsMoney,@IsFactory,@IsBH,@IsGJ,@BackDate,@TCFee1,@TCFee2,@TCFee3,@TCFee4,@TCFee5,@TCFee6,@TSoild) set @ChaseId=@@IDENTITY end select @ChaseId ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseCode", DbType.String, Model.ChaseCode);
|
|
database.AddInParameter(sqlStringCommand, "@BuyCode", DbType.String, Model.BuyCode);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsState", DbType.Int32, Model.GoodsState);
|
|
database.AddInParameter(sqlStringCommand, "@PostFee", DbType.Decimal, Model.PostFee);
|
|
database.AddInParameter(sqlStringCommand, "@Remark", DbType.String, Model.Remark);
|
|
database.AddInParameter(sqlStringCommand, "@ErrorInfo", DbType.String, Model.ErrorInfo);
|
|
database.AddInParameter(sqlStringCommand, "@InDate", DbType.DateTime, Model.InDate);
|
|
database.AddInParameter(sqlStringCommand, "@BuyDate", DbType.DateTime, Model.BuyDate);
|
|
database.AddInParameter(sqlStringCommand, "@DoneDate", DbType.DateTime, Model.DoneDate);
|
|
database.AddInParameter(sqlStringCommand, "@Supplier", DbType.String, Model.Supplier);
|
|
database.AddInParameter(sqlStringCommand, "@SupplierName", DbType.String, Model.SupplierName);
|
|
database.AddInParameter(sqlStringCommand, "@SupplierPhone", DbType.String, Model.SupplierPhone);
|
|
database.AddInParameter(sqlStringCommand, "@InName", DbType.String, Model.InName);
|
|
database.AddInParameter(sqlStringCommand, "@InUserId", DbType.Int32, Model.InUserId);
|
|
database.AddInParameter(sqlStringCommand, "@Account", DbType.String, Model.Account);
|
|
database.AddInParameter(sqlStringCommand, "@IsDelete", DbType.Int32, Model.IsDelete);
|
|
database.AddInParameter(sqlStringCommand, "@State", DbType.Int32, Model.State);
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
database.AddInParameter(sqlStringCommand, "@CheckUid", DbType.Int32, Model.CheckUid);
|
|
database.AddInParameter(sqlStringCommand, "@CheckContent", DbType.String, Model.CheckContent);
|
|
database.AddInParameter(sqlStringCommand, "@CheckDate", DbType.DateTime, Model.CheckDate);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsMoney", DbType.Decimal, Model.GoodsMoney);
|
|
database.AddInParameter(sqlStringCommand, "@IsFactory", DbType.Int32, Model.IsFactory);
|
|
database.AddInParameter(sqlStringCommand, "@IsBH", DbType.Int32, Model.IsBH);
|
|
database.AddInParameter(sqlStringCommand, "@PostState", DbType.Int32, Model.PostState);
|
|
database.AddInParameter(sqlStringCommand, "@PostDate", DbType.DateTime, Model.PostDate);
|
|
database.AddInParameter(sqlStringCommand, "@IsCheck", DbType.Int32, Model.IsCheck);
|
|
database.AddInParameter(sqlStringCommand, "@BackDate", DbType.DateTime, Model.BackDate);
|
|
database.AddInParameter(sqlStringCommand, "@PostionCode", DbType.String, Model.PostionCode);
|
|
database.AddInParameter(sqlStringCommand, "@IsError", DbType.Int32, Model.IsError);
|
|
database.AddInParameter(sqlStringCommand, "@IsGJ", DbType.Int32, Model.IsGJ);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee1", DbType.Decimal, Model.TCFee1);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee2", DbType.Decimal, Model.TCFee2);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee3", DbType.Decimal, Model.TCFee3);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee4", DbType.Decimal, Model.TCFee4);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee5", DbType.Decimal, Model.TCFee5);
|
|
database.AddInParameter(sqlStringCommand, "@TCFee6", DbType.Decimal, Model.TCFee6);
|
|
database.AddInParameter(sqlStringCommand, "@TSoild", DbType.Decimal, Model.TSoild);
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
}
|
|
public int UpdateHWCGoods(int ChaseId)
|
|
{
|
|
string query = " update CG_PurchaseGoodsHWC set GoodsNum=0 where ChaseId=@ChaseId ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
}
|
|
|
|
|
|
public int Save_PurchaseGoodsHWC(CG_PurchaseGoodsHWC Model)
|
|
{
|
|
string query = " if @Id>0 begin Update [CG_PurchaseGoodsHWC] set [ChaseId]=@ChaseId,[GoodsCode]=@GoodsCode,[GoodsDetailId]=@GoodsDetailId,[GoodsNum]=@GoodsNum,[GoodsPrice]=@GoodsPrice,[LockNum]=@LockNum,[IsOrder]=@IsOrder,[CompanyId]=@CompanyId,[InGoodsNum]=@InGoodsNum,[ErrorNum]=@ErrorNum,[Error]=@Error,[BackNum]=@BackNum,[PostionCode]=@PostionCode,[BoxNum]=@BoxNum,[BarCode]=@BarCode,[OrderId]=@OrderId,[OutInNum]=@OutInNum,[OutNum]=@OutNum,[SJOutNum]=@SJOutNum,[GJChaseId]=@GJChaseId,[PState]=@PState,[TJOutNum]=@TJOutNum,[OldId]=@OldId where Id=@Id end else begin INSERT INTO [CG_PurchaseGoodsHWC]([ChaseId],[GoodsCode],[GoodsDetailId],[GoodsNum],[GoodsPrice],[LockNum],[IsOrder],[CompanyId],[InGoodsNum],[ErrorNum],[Error],[BackNum],[PostionCode],[BoxNum],[BarCode],[OrderId],[OutInNum],[OutNum],[SJOutNum],[GJChaseId],[PState],[TJOutNum],[OldId])values(@ChaseId,@GoodsCode,@GoodsDetailId,@GoodsNum,@GoodsPrice,@LockNum,@IsOrder,@CompanyId,@InGoodsNum,@ErrorNum,@Error,@BackNum,@PostionCode,@BoxNum,@BarCode,@OrderId,@OutInNum,@OutNum,@SJOutNum,@GJChaseId,@PState,@TJOutNum,@OldId) 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, "@ChaseId", DbType.Int32, Model.ChaseId);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsCode", DbType.String, Model.GoodsCode);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsDetailId", DbType.Int32, Model.GoodsDetailId);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsNum", DbType.Int32, Model.GoodsNum);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsPrice", DbType.Decimal, Model.GoodsPrice);
|
|
database.AddInParameter(sqlStringCommand, "@LockNum", DbType.Int32, Model.LockNum);
|
|
database.AddInParameter(sqlStringCommand, "@IsOrder", DbType.Int32, Model.IsOrder);
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, Model.CompanyId);
|
|
database.AddInParameter(sqlStringCommand, "@InGoodsNum", DbType.Int32, Model.InGoodsNum);
|
|
database.AddInParameter(sqlStringCommand, "@ErrorNum", DbType.Int32, Model.ErrorNum);
|
|
database.AddInParameter(sqlStringCommand, "@Error", DbType.String, Model.Error);
|
|
database.AddInParameter(sqlStringCommand, "@BackNum", DbType.Int32, Model.BackNum);
|
|
database.AddInParameter(sqlStringCommand, "@PostionCode", DbType.String, Model.PostionCode);
|
|
database.AddInParameter(sqlStringCommand, "@BoxNum", DbType.Int32, Model.BoxNum);
|
|
database.AddInParameter(sqlStringCommand, "@BarCode", DbType.String, Model.BarCode);
|
|
database.AddInParameter(sqlStringCommand, "@OrderId", DbType.Int32, Model.OrderId);
|
|
database.AddInParameter(sqlStringCommand, "@OutInNum", DbType.Int32, Model.OutInNum);
|
|
database.AddInParameter(sqlStringCommand, "@OutNum", DbType.Int32, Model.OutNum);
|
|
database.AddInParameter(sqlStringCommand, "@SJOutNum", DbType.Int32, Model.SJOutNum);
|
|
database.AddInParameter(sqlStringCommand, "@GJChaseId", DbType.Int32, Model.GJChaseId);
|
|
database.AddInParameter(sqlStringCommand, "@PState", DbType.Int32, Model.PState);
|
|
database.AddInParameter(sqlStringCommand, "@TJOutNum", DbType.Int32, Model.TJOutNum);
|
|
database.AddInParameter(sqlStringCommand, "@OldId", DbType.Int32, Model.OldId);
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
}
|
|
|
|
public int UpdateHWCGoods2(int ChaseId)
|
|
{
|
|
string query = " delete from CG_PurchaseGoodsHWC where ChaseId=@ChaseId and GoodsNum=0 ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
return Convert.ToInt32(database.ExecuteScalar(sqlStringCommand));
|
|
}
|
|
|
|
public CG_PurchaseHWC GetPurchaseModelHWC(int ChaseId)
|
|
{
|
|
CG_PurchaseHWC ehwc = null;
|
|
string query = "select a.*,CompanyName=b.CompanyName from CG_PurchaseHWC a inner join JC_Company b on a.CompanyId=b.CompanyId where ChaseId=@ChaseId";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
DataTable table = database.ExecuteDataTable(sqlStringCommand);
|
|
if (table.Rows.Count > 0)
|
|
{
|
|
ehwc = table.Rows[0].ToModel<CG_PurchaseHWC>();
|
|
}
|
|
return ehwc;
|
|
}
|
|
|
|
public List<HW_GoodsCGDetail> GetPurchaseGoodsForHWC(int ChaseId)
|
|
{
|
|
string query = " select a.Id,C.GoodsId,a.ChaseId,PostionCode=isnull(a.PostionCode,''),GoodsCode=isnull(c.GoodsCode,''),c.GoodsOldCode,a.GoodsDetailId,GoodsNum=b.GoodsNum,a.GoodsPrice,a.LockNum,a.IsOrder,a.CompanyId,a.InGoodsNum,DetailId=a.GoodsDetailId,Price=b.Price,OldBuyNum=a.GoodsNum,BuyNum=a.GoodsNum,BuyMoney=a.GoodsPrice,c.GoodsName,b.TypeCode,b.SKU1,b.TypeDesc,b.GoodsPlanNum,b.GoodsInNum,GoodsLeftNum=b.GoodsNum+b.GoodsInNum-b.GoodsPlanNum,ErrorNum=0,Error='',c.FirstImgUrl,SJOutNum=isnull(a.SJOutNum,0),OutInNum=isnull(a.OutInNum,0) from CG_PurchaseGoodsHWC a inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
return database.ExecuteDataSet(sqlStringCommand).Tables[0].ToList<HW_GoodsCGDetail>();
|
|
}
|
|
public List<HW_GoodsHWCOutDetail> GetPurchaseGoodsOutHWC(int ChaseId)
|
|
{
|
|
string query = " select a.Id,b.DetailId,c.GoodsCode,c.GoodsName,b.TypeDesc,FirstImgUrl=case when b.FirstImgUrl is not null or b.FirstImgUrl<>'' then b.FirstImgUrl else c.FirstImgUrl end,SJOutNum=isnull(SJOutNum,0), a.OutNum,a.OutInNum,Solid=isnull(c.Solid,0),a.GoodsPrice,a.GoodsNum,IsAdd=0 from CG_PurchaseGoodsHWC a inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId inner join HW_GoodsInfo c on b.GoodsId=c.GoodsId where a.ChaseId=@ChaseId order by c.GoodsId,b.TypeCode ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
return database.ExecuteDataSet(sqlStringCommand).Tables[0].ToList<HW_GoodsHWCOutDetail>();
|
|
}
|
|
|
|
|
|
public List<CG_Purchase> GetListCG_PurchaseForHWC2(RefParameterCollection where, int PageIndex, int PageSize, string Sort, out int RowCount)
|
|
{
|
|
if (where == null)
|
|
{
|
|
where = new RefParameterCollection();
|
|
}
|
|
string text = new QueryService
|
|
{
|
|
Fields = "ChaseId=cast(a.ChaseId as int),a.ChaseCode,a.BuyCode,a.GoodsState,a.PostFee,a.Remark, a.ErrorInfo,a.InDate,a.BuyDate,a.DoneDate,a.Supplier,a.SupplierName,a.SupplierPhone,a.InName,a.InUserId,a.Account,a.IsDelete,a.State,a.CompanyId,a.CheckUid,a.CheckContent,a.CheckDate,a.GoodsNum,a.GoodsMoney,checkstate=case when a.state=0 then '未审批' when a.state=1 then '审批通过' when a.state=2 then '审批不通过' when a.state=3 then '采购单结束' end,ReveState=case when a.PostState=0 then '未出运'when a.PostState=3 then '准备出运' when a.PostState=4 then '出运海上' when a.PostState=5 then '出运海外' when a.PostState=6 then '部分入库' when a.PostState=7 then '全部入库' else '未出运' end,TSoild=isnull(a.TSoild,0),a.TCFee5,a.TCFee6",
|
|
Tables = "CG_PurchaseHWC a",
|
|
Filter = where.GetWhere(CommandType.Text),
|
|
PageIndex = PageIndex,
|
|
PageSize = PageSize,
|
|
Sort = Sort,
|
|
KeyName = "ChaseId"
|
|
}.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<CG_Purchase>();
|
|
}
|
|
|
|
|
|
public void SaveInchaseGoodsForHWC(int ChaseId, int UserId, decimal? Price, decimal? PostPrice, int GoodsDetailId, int InGoodsNum, int RKId, string Error, string PostionCode, int PostState, int CompanyId)
|
|
{
|
|
string query = " declare @Id int,@StoreId int,@PostionId int,@GoodsNum int,@InGoodsNum2 int select top 1 @Id=Id,@GoodsNum=GoodsNum,@InGoodsNum2=isnull(InGoodsNum,0) from CG_PurchaseGoodsHWC where GoodsDetailId=@GoodsDetailId and ChaseId=@ChaseId if @PostionCode<>'' begin select top 1 @StoreId=StoreId,@PostionId=PostionId from CK_StorePostion where PostionCode=@PostionCode update CG_PurchaseHWC set PostionCode=@PostionCode where ChaseId=@ChaseId end if @PState<7 and @GoodsNum=@InGoodsNum2+@InGoodsNum begin set @PState=7 end if @PState<6 and @GoodsNum>@InGoodsNum2+@InGoodsNum begin set @PState=6 end Update [CG_PurchaseGoodsHWC] set [InGoodsNum]=isnull(InGoodsNum,0)+@InGoodsNum,[Error]=@Error,PState=@PState where Id=@Id if @InGoodsNum>0 begin INSERT INTO [HW_GoodsInDetailHWC]([DetailId],[ChaseId],[StoreId],[PostionId],[Price],[PostPrice],[InDate],[InUserId],[OrderGoodsId],[UpDateTime],RKDetailId,KCNum,OldNum,CompanyId) values(@GoodsDetailId,@ChaseId,@StoreId,@PostionId,@Price,@PostPrice,getdate(),@InUserId,0,null,@RKId,@InGoodsNum,@InGoodsNum,@CompanyId) end ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@RKId", DbType.Int32, RKId);
|
|
database.AddInParameter(sqlStringCommand, "@CompanyId", DbType.Int32, CompanyId);
|
|
database.AddInParameter(sqlStringCommand, "@GoodsDetailId", DbType.Int32, GoodsDetailId);
|
|
database.AddInParameter(sqlStringCommand, "@InGoodsNum", DbType.Int32, InGoodsNum);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
database.AddInParameter(sqlStringCommand, "@Error", DbType.String, Error);
|
|
database.AddInParameter(sqlStringCommand, "@PostionCode", DbType.String, PostionCode);
|
|
database.AddInParameter(sqlStringCommand, "@InUserId", DbType.Int32, UserId);
|
|
database.AddInParameter(sqlStringCommand, "@Price", DbType.Decimal, Price);
|
|
database.AddInParameter(sqlStringCommand, "@PostPrice", DbType.Decimal, PostPrice);
|
|
database.AddInParameter(sqlStringCommand, "@PState", DbType.Int32, PostState);
|
|
database.ExecuteNonQuery(sqlStringCommand);
|
|
}
|
|
public void UpdateChaseStateForHWC(int ChaseId, int PostState, int IsDH, int IsCheck)
|
|
{
|
|
string query = @" if @PostState<7
|
|
begin if @IsDH=0 and (select count(0) from CG_PurchaseGoodsHWC where ChaseId=@ChaseId and InGoodsNum<GoodsNum)>0
|
|
begin
|
|
update CG_PurchaseHWC set PostState=6 where ChaseId=@ChaseId end else begin update CG_PurchaseHWC set PostState=7,DoneDate=getdate() where ChaseId=@ChaseId --全部到货结束采购单
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
update CG_PurchaseHWC set PostState=@PostState where ChaseId=@ChaseId
|
|
end ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
database.AddInParameter(sqlStringCommand, "@PostState", DbType.Int32, PostState);
|
|
database.AddInParameter(sqlStringCommand, "@IsCheck", DbType.Int32, IsCheck);
|
|
database.AddInParameter(sqlStringCommand, "@IsDH", DbType.Int32, IsDH);
|
|
database.ExecuteNonQuery(sqlStringCommand);
|
|
}
|
|
public void UpdateChaseGoodsNumHWC(int ChaseId)
|
|
{
|
|
string query = " update b set b.GoodsNum=dbo.GetStoreGoodsNum(b.DetailId),b.GoodsInNum=dbo.GetBuyGoodsNum(b.DetailId) from [CG_PurchaseGoodsHWC] a inner join HW_GoodsDetail b on a.GoodsDetailId=b.DetailId where a.ChaseId=@ChaseId ";
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(query);
|
|
database.AddInParameter(sqlStringCommand, "@ChaseId", DbType.Int32, ChaseId);
|
|
database.ExecuteNonQuery(sqlStringCommand);
|
|
}
|
|
|
|
public List<CG_PurchaseGoodsForCGPC> GetCG_PurchaseGoodsCGPCList(int strType, string strValue, int state, int goodsState, string dateType, DateTime? startDate, DateTime? endDate, int PageIndex, int PageSize,out int rowCount)
|
|
{
|
|
var listModel=new List<CG_PurchaseGoodsForCGPC>();
|
|
var sqlFil = " where 1=1 ";
|
|
if (!string.IsNullOrEmpty(strValue))
|
|
{
|
|
switch (strType)
|
|
{
|
|
case 0:
|
|
sqlFil += " and a.ChaseCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 1:
|
|
sqlFil += " and a.BuyCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 2:
|
|
sqlFil+= " and b.GoodsCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 3:
|
|
sqlFil += " and c.GoodsName like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
}
|
|
}
|
|
if (state != -1)
|
|
sqlFil += " and a.State=" + state;
|
|
if (goodsState != -1)
|
|
sqlFil += " and a.GoodsState=" + goodsState;
|
|
|
|
if (startDate.HasValue)
|
|
sqlFil += " and convert(date, " + dateType + ") >='" + startDate.Value.ToString("yyyy-MM-dd") + "'";
|
|
if (endDate.HasValue)
|
|
sqlFil += " and convert(date," + dateType + ") <='" + endDate.Value.ToString("yyyy-MM-dd") + "'";
|
|
|
|
|
|
var sqlCount = @"select COUNT(1) from CG_Purchase a left join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
|
|
left join HW_GoodsInfo c on b.GoodsCode=c.GoodsCode " + sqlFil;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand1 = db.GetSqlStringCommand(sqlCount);
|
|
var countObj = db.ExecuteScalar(sqlStringCommand1);
|
|
rowCount = countObj != null ? (int)countObj : 0;
|
|
|
|
var sql = @"select ChaseId,ChaseCode,BuyCode,GoodsCode,GoodsName,BuyDate,DoneDate,InGoodsNum,GoodsNum,GoodsPrice,TotalPrice,SingleCube,TotalCube,GoodsState,State
|
|
,case when GoodsState=0 then '未到货' when GoodsState=1 then '部分到货' when GoodsState=2 then '全部到货' when GoodsState=3 then '到货异常' end GoodsStateName,
|
|
case when state=0 then '未审批' when state=1 then '审批通过' when state=2 then '审批不通过' when state=3 then '采购单结束' end StateName from (
|
|
select ROW_NUMBER() Over(order by a.BuyDate desc) row ,a.ChaseId,a.ChaseCode,a.BuyCode ,b.GoodsCode ,c.GoodsName , a.BuyDate ,a.DoneDate ,b.InGoodsNum , b.GoodsNum ,b.GoodsPrice
|
|
,b.GoodsNum*b.GoodsPrice TotalPrice,convert(decimal(12,2), c.Long*c.Width*c.Height/1000000) SingleCube,CONVERT(decimal(12,2), c.Long*c.Width*c.Height*b.GoodsNum/1000000) TotalCube,a.TSoild, a.GoodsState,a.State
|
|
from CG_Purchase a left join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
|
|
left join HW_GoodsInfo c on b.GoodsCode=c.GoodsCode " + sqlFil + " ) t where t.row between " + ((PageIndex - 1) * PageSize + 1) + " and " + PageIndex * PageSize;
|
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(sql);
|
|
return database.ExecuteDataSet(sqlStringCommand).Tables[0].ToList<CG_PurchaseGoodsForCGPC>();
|
|
}
|
|
|
|
public void GetCG_GoodsCGPCPriceAndCube(int strType, string strValue, int state, int goodsState, string dateType, DateTime? startDate, DateTime? endDate,out decimal totalPrice,out decimal totalCube)
|
|
{
|
|
var sqlFil = " where 1=1 ";
|
|
if (!string.IsNullOrEmpty(strValue))
|
|
{
|
|
switch (strType)
|
|
{
|
|
case 0:
|
|
sqlFil += " and a.ChaseCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 1:
|
|
sqlFil += " and a.BuyCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 2:
|
|
sqlFil += " and b.GoodsCode like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
case 3:
|
|
sqlFil += " and c.GoodsName like '%" + strValue.Trim() + "%'";
|
|
break;
|
|
}
|
|
}
|
|
if (state != -1)
|
|
sqlFil += " and a.State=" + state;
|
|
if (goodsState != -1)
|
|
sqlFil += " and a.GoodsState=" + goodsState;
|
|
|
|
if (startDate.HasValue)
|
|
sqlFil += " and convert(date, " + dateType + ") >='" + startDate.Value.ToString("yyyy-MM-dd") + "'";
|
|
if (endDate.HasValue)
|
|
sqlFil += " and convert(date," + dateType + ") <='" + endDate.Value.ToString("yyyy-MM-dd") + "'";
|
|
|
|
var sql = @"select Convert(decimal(12,2),Sum(ISNULL(TotalPrice,0))) totalPrice, Convert(decimal(12,2),Sum(ISNULL(TotalCube,0))) totalCube from (
|
|
select b.GoodsNum*b.GoodsPrice TotalPrice,CONVERT(decimal(12,2), c.Long*c.Width*c.Height*b.GoodsNum/1000000) TotalCube from CG_Purchase a left join CG_PurchaseGoods b on a.ChaseId=b.ChaseId
|
|
left join HW_GoodsInfo c on b.GoodsCode=c.GoodsCode " + sqlFil + " ) t";
|
|
|
|
Database database = DatabaseFactory.CreateDatabase();
|
|
DbCommand sqlStringCommand = database.GetSqlStringCommand(sql);
|
|
var dt=database.ExecuteDataTable(sqlStringCommand);
|
|
if(dt!=null && dt.Rows.Count > 0)
|
|
{
|
|
totalPrice = !string.IsNullOrEmpty(dt.Rows[0]["totalPrice"].ToString()) ? Convert.ToDecimal(dt.Rows[0]["totalPrice"]) : 0;
|
|
totalCube = !string.IsNullOrEmpty(dt.Rows[0]["totalCube"].ToString()) ? Convert.ToDecimal(dt.Rows[0]["totalCube"]) : 0;
|
|
}
|
|
else
|
|
{
|
|
totalPrice = 0;
|
|
totalCube = 0;
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|