/// <summary>
/// 添加原材料信息
/// </summary>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddMarerialInfoTable(Model.MarerialInfoTable marerialinfotable, ref string error)
{
if (IsExit(marerialinfotable.MaterialNumber))
{
error = "已存在该编号！请重新填写编号。";
return(false);
}
if (string.IsNullOrEmpty(marerialinfotable.MaterialNumber) ||
string.IsNullOrEmpty(marerialinfotable.StockSafeQty.ToString()) ||
string.IsNullOrEmpty(marerialinfotable.ProcurementPrice.ToString()) || string.IsNullOrEmpty(marerialinfotable.MinPacking) ||
string.IsNullOrEmpty(marerialinfotable.MinOrderQty))
{
error = "原材料信息不完整！";
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@" insert into MarerialInfoTable (MaterialNumber,MaterialName,Description,Kind,Type,Brand,StockSafeQty,
ProcurementPrice,MaterialPosition,MinPacking,MinOrderQty,ScrapPosition,Remark,Cargo,CargoType,NumberProperties,Unit)
values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')",
marerialinfotable.MaterialNumber, marerialinfotable.MaterialName, marerialinfotable.Description, marerialinfotable.Kind,
marerialinfotable.Type, marerialinfotable.Brand, marerialinfotable.StockSafeQty, marerialinfotable.ProcurementPrice,
marerialinfotable.MaterialPosition, marerialinfotable.MinPacking, marerialinfotable.MinOrderQty,
marerialinfotable.ScrapPosition, marerialinfotable.Remark, marerialinfotable.Cargo, marerialinfotable.CargoType, marerialinfotable.NumberProperties, marerialinfotable.Unit);
sqls.Add(sql);
sql = string.Format(@"insert into MaterialStock (MaterialNumber,StockQty,UpdateTime,WarehouseName)
values('{0}',{1},'{2}','ycl')", marerialinfotable.MaterialNumber, 0, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 编辑原材料信息
/// </summary>
/// <param name="user"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool EditMarerialInfoTable(MarerialInfoTable maretialinfotable, ref string error)
{
if (!IsExit(maretialinfotable.MaterialNumber))
{
error = "不存在该原材料编号！请重新填写！";
return(false);
}
List <string> list = new List <string>();
sql = string.Format(@" update MarerialInfoTable set MaterialName='{0}',
Description='{1}',Kind='{2}',Type='{3}',Brand='{4}',StockSafeQty={5},ProcurementPrice='{6}'
,MaterialPosition='{7}',MinPacking='{8}',
MinOrderQty='{9}',ScrapPosition='{10}',Remark='{11}' ,CargoType='{13}',NumberProperties='{14}',Unit='{15}'
where MaterialNumber='{12}'", maretialinfotable.MaterialName,
maretialinfotable.Description, maretialinfotable.Kind, maretialinfotable.Type,
maretialinfotable.Brand, maretialinfotable.StockSafeQty, maretialinfotable.ProcurementPrice,
maretialinfotable.MaterialPosition, maretialinfotable.MinPacking, maretialinfotable.MinOrderQty,
maretialinfotable.ScrapPosition, maretialinfotable.Remark, maretialinfotable.MaterialNumber, maretialinfotable.CargoType, maretialinfotable.NumberProperties, maretialinfotable.Unit);
list.Add(sql);
sql = string.Format(@" update Product set CostPrice =(select sum(bom.SingleDose*mit.ProcurementPrice ) from BOMInfo bom
inner join MarerialInfoTable mit on bom.MaterialNumber =mit.MaterialNumber
where bom.ProductNumber =p.ProductNumber and bom.Version =p.Version
group by bom.ProductNumber , bom.Version ) from Product p
inner join (
select ProductNumber ,Version from BOMInfo where MaterialNumber ='{0}') t on
p.ProductNumber=t.ProductNumber and p.Version =t.Version ", maretialinfotable.MaterialNumber);
list.Add(sql);
return(SqlHelper.BatchExecuteSql(list, ref error));
}
/// <summary>
/// 删除原材料信息
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
List <string> list = new List <string>();
sql = string.Format(@" delete MaterialSupplierProperty where MaterialNumber in ({0}) ", ids);
list.Add(sql);
sql = string.Format(@" delete MaterialCustomerProperty where MaterialNumber in ({0}) ", ids);
list.Add(sql);
sql = string.Format(@" delete MaterialBlueprintProperty where MaterialNumber in ({0}) ", ids);
list.Add(sql);
sql = string.Format(@" delete MarerialInfoTable where MaterialNumber in ({0}) ", ids);
list.Add(sql);
sql = string.Format(@" delete ProductCuttingLineInfo where MaterialNumber in ({0}) ", ids);
list.Add(sql);
sql = string.Format(@" delete BOMInfo where MaterialNumber in ({0})", ids);
list.Add(sql);
sql = string.Format(@" delete MaterialStock where MaterialNumber in ({0})", ids);
list.Add(sql);
sql = string.Format(@" update Product set CostPrice =(select sum(bom.SingleDose*mit.ProcurementPrice ) from BOMInfo bom
inner join MarerialInfoTable mit on bom.MaterialNumber =mit.MaterialNumber
where bom.ProductNumber =p.ProductNumber and bom.Version =p.Version
group by bom.ProductNumber , bom.Version ) from Product p
inner join (
select ProductNumber ,Version from BOMInfo where MaterialNumber in ({0})) t on
p.ProductNumber=t.ProductNumber and p.Version =t.Version ", ids);
list.Add(sql);
return(SqlHelper.BatchExecuteSql(list, ref error) == true ? "1" : error);
}
/// <summary>
/// 原材料生产出库审核
/// </summary>
/// <param name="auditor"></param>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static string SCCKAuditor(string auditor, string warehouseNumber)
{
string error = string.Empty;
List <string> sqls = new List <string>();
// string sql = string.Format(@" update MaterialStock set StockQty =vmq.StockQty-mwld.Qty from MaterialWarehouseLogDetail mwld
//inner join MaterialStock vmq on mwld.MaterialNumber =vmq.MaterialNumber
//where mwld.WarehouseNumber='{0}' ", warehouseNumber);
// sqls.Add(sql);
//写入欠料明细
// string sql = string.Format(@"
//insert into T_LessMaterialBreakdown(WarehouseNumber ,DocumentNumber ,MaterialNumber ,RowNumber ,LeadTime ,CustomerMaterialNumber
//,LibraryQty,StockQty ,LessMaterialQty ,CreateTime ,IsLessMaterial )
//select mwld.WarehouseNumber,mwld.DocumentNumber ,mwld. MaterialNumber,mwld.RowNumber ,
//mwld.LeadTime ,mwld.CustomerMaterialNumber,mwld. Qty,vmq.StockQty,
//case when vmq.StockQty<0 then mwld.Qty else
//vmq.StockQty -mwld.Qty end,'{1}','未还料' from MaterialWarehouseLogDetail mwld
//inner join MaterialStock vmq on mwld.MaterialNumber =vmq.MaterialNumber
//where mwld.WarehouseNumber='{0}'
//and vmq.StockQty-mwld.Qty <0", warehouseNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
// sqls.Add(sql);
string sql = string.Format(@"insert into T_LessMaterialBreakdown(WarehouseNumber ,DocumentNumber ,MaterialNumber ,RowNumber ,LeadTime ,CustomerMaterialNumber
,LibraryQty,StockQty ,LessMaterialQty ,CreateTime ,IsLessMaterial )
select '',mwld.DocumentNumber ,mwld. MaterialNumber,'',
'' ,mwld.CustomerMaterialNumber,mwld. Qty,vmq.StockQty,
case when vmq.StockQty<0 then 0-mwld.Qty else
vmq.StockQty -mwld.Qty end,'{1}','未还料' from (
select DocumentNumber,MaterialNumber,CustomerMaterialNumber,SUM(Qty) Qty from MaterialWarehouseLogDetail
where WarehouseNumber ='{0}'
group by DocumentNumber,MaterialNumber,CustomerMaterialNumber) mwld
inner join MaterialStock vmq on mwld.MaterialNumber =vmq.MaterialNumber
where
vmq.StockQty-mwld.Qty <0
", warehouseNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
//更新库存
sql = string.Format(@"
update MaterialStock set StockQty =
case when a.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
( select mwld.MaterialNumber,mwl.ChangeDirection,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on
mwld.WarehouseNumber =mwl.WarehouseNumber
where mwl.WarehouseNumber='{0}' and mwld.Qty>0
group by mwld.MaterialNumber,mwl.ChangeDirection) a
inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
where ms.WarehouseName ='ycl'
", warehouseNumber);
sqls.Add(sql);
sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber ='{2}'", auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
//更新瞬时库存数量
sqls.Add(StoreroomToolManager.GetUpDateInventoryQtySql(warehouseNumber));
//写入流水账
sqls.Add(StoreroomToolManager.WriteMateriLSZ(warehouseNumber, auditor));
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 添加产成品基本信息
/// </summary>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddProduct(Model.Product product, ref string error)
{
List <string> sqls = new List <string>();
if (IsExit(product.ProductNumber, product.Version))
{
error = "已存在该编号、版本！请重新填写！";
return(false);
}
if (string.IsNullOrEmpty(product.ProductNumber) || string.IsNullOrEmpty(product.Version))
{
error = "产成品信息不完整！";
return(false);
}
string sql = string.Format(@" insert into Product (ProductNumber,Version,ProductName,Description,RatedManhour,QuoteManhour,
CostPrice,SalesQuotation,HalfProductPosition,FinishedGoodsPosition,Remark,IsOldVersion,type,Cargo,Unit,NumberProperties) values
('{0}','{1}','{2}','{3}',{4},{5},'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
product.ProductNumber, product.Version, product.ProductName, product.Description, product.RatedManhour,
product.QuoteManhour, product.CostPrice, product.SalesQuotation, product.HalfProductPosition,
product.FinishedGoodsPosition, product.Remark, product.IsOldVersion, product.Type, product.Cargo, product.Unit, product.NumberProperties);
sqls.Add(sql);
sql = string.Format(@"
insert into ProductStock (ProductNumber ,Version ,WarehouseName ,StockQty ,UpdateTime )
values('{0}','{1}','cpk',0,'{2}')", product.ProductNumber, product.Version, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
if (product.Type == "包")
{
sql = string.Format(@"insert into PackageInfo(PackageNumber,PackageName)values('{0}','{1}')", product.ProductNumber, product.ProductName);
sqls.Add(sql);
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 删除原材料种类
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string ids)
{
List <string> list = new List <string>();
string sql = string.Format(@" delete MarerialKind where Id in ('{0}') ", ids);
list.Add(sql);
return(SqlHelper.BatchExecuteSql(list, ref error) == true ? "1" : error);
}
//删除半成品入库详细信息
public static string DeleteDetail(string ids)
{
error = string.Empty;
List <string> sqls = new List <string>();
sql = string.Format(@" delete HalfProductWarehouseLogDetail where guid in ({0})", ids);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) == true ? "1" : error);
}
/// <summary>
/// 删除产成品信息
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static string DeleteData(string Version, string ProductNumber)
{
List <string> list = new List <string>();
//产品客户属性
string GuidOne = string.Format(@" select guid from ProductCustomerProperty where ProductNumber in ({0})
and Version in ({1})", ProductNumber, Version);
sql = string.Format(@" delete ProductCustomerProperty where guid in ({0})", GuidOne);
list.Add(sql);
//产品图纸属性
string GuidTwo = string.Format(@" select guid from ProductBlueprintProperty where ProductNumber in ({0})
and Version in ({1})", ProductNumber, Version);
sql = string.Format(@" delete ProductBlueprintProperty where guid in ({0})", GuidTwo);
list.Add(sql);
//BOM信息表--裁线信息维护表
string GuidThree = string.Format(@" select guid from BOMInfo where ProductNumber in ({0})
and Version in ({1})", ProductNumber, Version);
sql = string.Format(@" delete BOMInfo where guid in ({0})", GuidThree);
list.Add(sql);
string ProductCuttingLineInfoGuid = " select pc.Guid as guid from dbo.BOMInfo bom inner join ProductCuttingLineInfo pc on bom.ProductNumber=pc.ProductNumber and bom.Version=pc.Version and bom.MaterialNumber=pc.MaterialNumber";
sql = string.Format(@" delete ProductCuttingLineInfo where guid in ({0})", ProductCuttingLineInfoGuid);
list.Add(sql);
//产品工序信息---产品工序系数
string ProductWorkSnPropertyGuid = "select pc.guid as Guid from ProductWorkSnProperty pp inner join ProductWorkSnCoefficient pc on pp.ProductNumber=pc.ProductNumber and pp.Version=pc.Version and pp.WorkSnNumber=pc.WorkSnNumber";
sql = string.Format(@" delete ProductWorkSnCoefficient where guid in ({0})", ProductWorkSnPropertyGuid);
list.Add(sql);
string GuidFour = string.Format(@" select guid from ProductWorkSnProperty where ProductNumber in ({0})
and Version in ({1})", ProductNumber, Version);
sql = string.Format(@" delete ProductWorkSnProperty where guid in ({0})", GuidFour);
list.Add(sql);
//产品基本信息表
sql = string.Format(@" delete from Product where ProductNumber in ({0})
and Version in ({1})", ProductNumber, Version);
list.Add(sql);
sql = string.Format(@" delete PackageAndProductRelation where ProductNumber in ({0}) and Version in ({1})",
ProductNumber, Version);
list.Add(sql);
sql = string.Format(@" delete ProductStock where ProductNumber in ({0}) and Version in ({1})
and WarehouseName='cpk' ", ProductNumber, Version);
list.Add(sql);
return(SqlHelper.BatchExecuteSql(list, ref error) == true ? "1" : error);
}
// select ProductNumber ,Version from ProductCustomerProperty where CustomerProductNumber=''
public static bool AddWorkSn(DataRow dr, ref string error)
{
string columName = string.Empty;
string version = dr["版本"].ToString().Trim().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
string snNumber = string.Empty;
string sn = string.Empty;
if (version.Equals("OO"))
{
version = "00";
}
string sql = string.Format(@"
select ProductNumber from ProductCustomerProperty where CustomerProductNumber ='{0}' and Version='{1}' ", dr["图纸号"], version);
string productNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(productNumber))
{
error = string.Format("系统不存在该图纸号：{0},版本：{1}", dr["图纸号"], version);
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format("select WorkSnNumber,WorkSnName,Sn from WorkSn");
foreach (DataRow drTemp in SqlHelper.GetTable(sql).Rows)
{
columName = drTemp["WorkSnName"].ToString();
if (dr.Table.Columns.Contains(columName) && !dr[columName].ToString().Trim().Equals(""))//如果有工时
{
// sql = string.Format(@"select COUNT(*) from ProductWorkSnProperty
//where ProductNumber='{0}' and Version ='{1}' and WorkSnNumber='{2}'", productNumber, version, drTemp["WorkSnNumber"].ToString());
// if (SqlHelper.GetScalar(sql).Equals("0"))
// {
// sqls.Add(GetInsertSqlForWorkSn(productNumber, version, drTemp["WorkSnNumber"].ToString(), drTemp["Sn"].ToString(), dr[columName].ToString()));
// }
//将原有数据库删除
sql = string.Format(@"delete ProductWorkSnProperty
where ProductNumber='{0}' and Version ='{1}' and WorkSnNumber='{2}'", productNumber, version, drTemp["WorkSnNumber"].ToString());
sqls.Add(sql);
sqls.Add(GetInsertSqlForWorkSn(productNumber, version, drTemp["WorkSnNumber"].ToString(), drTemp["Sn"].ToString(), dr[columName].ToString()));
}
}
bool result = SqlHelper.BatchExecuteSql(sqls, ref error);
string temp = string.Empty;
if (!result)
{
foreach (string str in sqls)
{
temp += string.Format("<br/>{0}", str);
}
error += string.Format("具体执行语句：{0},图纸号：{1},版本：{2}", temp, dr["图纸号"], version);
}
return(result);
}
//删除废品出入库
public static string DeleteData(string ids)
{
error = string.Empty;
List <string> sqls = new List <string>();
string guid = string.Format(@" select guid from ScarpWarehouseLogDetail where WarehouseNumber in ({0})", ids);
string sqlOne = string.Format(@" delete ScarpWarehouseLogDetail where guid in ({0})", guid);
sql = string.Format(@" delete ScarpWarehouseLog where WarehouseNumber in ({0}) ", ids);
sqls.Add(sqlOne);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) == true ? "1" : error);
}
public static string Delete(string ids)
{
List <string> sqls = new List <string>();
string error = string.Empty;
string sql = string.Format(@" delete TradingQuoteDetail where QuoteNumber in({0})", ids);
sqls.Add(sql);
sql = string.Format(@" delete MachineQuoteDetail where QuoteNumber in({0})", ids);
sqls.Add(sql);
sql = string.Format(@" delete QuoteInfo where QuoteNumber in({0})", ids);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) == true ? "1" : error);
}
public static void WriteAverageForYear(string year, string type, string isPorduct, string sql, string userNameColmName)
{
string error = string.Empty;
List <string> sqls = new List <string>();
string updateSql = string.Format(@" delete T_TempScore where YEAR ='{0}'
and Type='{1}' and IsProduct='{2}' ", year, type, isPorduct);
sqls.Add(updateSql);
updateSql = string.Format(@" insert into T_TempScore(Year,UserName,Average,Type,IsProduct)
select t.年份,t.{1},t.平均分新,'{2}','{3}' from ({0}) t", sql, userNameColmName, type, isPorduct);
sqls.Add(updateSql);
SqlHelper.BatchExecuteSql(sqls, ref error);
}
/// <summary>
/// 添加贸易销售订单明细
/// </summary>
/// <param name="tod">实体类</param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddTradingOrderDetail(TradingOrderDetail tod, ref string error)
{
try
{
Convert.ToDateTime(tod.Delivery);
}
catch (Exception)
{
error = string.Format("交期{0}不是标准日期格式 yyyy-MM-dd ", tod.Delivery);
return(false);
}
string sql = string.Format(@"select top 1 MaterialNumber
from MaterialCustomerProperty where CustomerMaterialNumber='{0}' and CustomerId='{1}'", tod.CustomerMaterialNumber, tod.CustomerId);
string materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format("系统不存在该客户原材料编号{0}", tod.CustomerMaterialNumber);
return(false);
}
if (string.IsNullOrEmpty(tod.OrdersNumber) || string.IsNullOrEmpty(tod.CustomerMaterialNumber) ||
string.IsNullOrEmpty(tod.RowNumber) || string.IsNullOrEmpty(tod.Delivery) ||
string.IsNullOrEmpty(tod.SN) || string.IsNullOrEmpty(tod.Quantity))
{
error = string.Format("信息填写不完整！");
return(false);
}
sql = string.Format(@" select COUNT(*) from TradingOrderDetail where OdersNumber ='{0}' and ProductNumber ='{1}' and RowNumber ='{2}'"
, tod.OrdersNumber, tod.ProductNumber, tod.RowNumber);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("订单记录已存在");
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@"
select 单价 from V_FindPriceForNewForTrading where 原材料编号='{0}' and 客户编号='{1}' ", materialNumber, tod.CustomerId);
string unitPrice = SqlHelper.GetScalar(sql);
unitPrice = string.IsNullOrEmpty(unitPrice) ? "0" : unitPrice;
sql = string.Format(@" insert into TradingOrderDetail(OdersNumber ,ProductNumber,RowNumber ,Delivery ,SN,CustomerMaterialNumber,
MaterialName ,Brand ,Quantity ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,TotalPrice ,CreateTime ,Remark )
select '{0}',MaterialNumber,'{1}','{2}',{3},'{4}',Description ,Brand ,{5},{5},0,{9},{9}*{5},'{6}','{8}'
from MarerialInfoTable where MaterialNumber ='{7}'", tod.OrdersNumber, tod.RowNumber, tod.Delivery, tod.SN, tod.CustomerMaterialNumber,
tod.Quantity, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), materialNumber, tod.Remark, unitPrice);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 添加产成品基本信息
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddProduct(DataRow dr, ref string error)
{
string version = dr["版本"].ToString().Trim().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
if (version.Equals("OO"))
{
version = "00";
}
List <string> sqls = new List <string>();
//客户产成品编号（图纸号） 客户编号 产成品编号(自己的号) 版本 型号 成品类别 描述
string sql = string.Format(@"
select count(*) from ProductCustomerProperty where CustomerProductNumber ='{0}' and CustomerId='{1}' and Version='{2}' ", dr["客户产成品编号（图纸号）"], dr["客户编号"], version);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统已存在该图纸号：{0},版本：{1}", dr["客户产成品编号（图纸号）"], version);
return(false);
}
sql = string.Format(@"
select count(0) from ProductCustomerProperty where ProductNumber ='{0}' and Version ='{1}' and CustomerProductNumber='{2}'
", dr["产成品编号(自己的号)"], version, dr["客户产成品编号（图纸号）"]);
if (SqlHelper.GetScalar(sql) != "0")
{
error = string.Format("系统已存在该图纸号：{0} ", dr["客户产成品编号（图纸号）"]);
return(false);
}
sql = string.Format(" select COUNT(*) from Product where ProductNumber ='{0}' and Version ='{1}' ", dr["产成品编号(自己的号)"], version);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into Product (ProductNumber,Version,ProductName ,Type,Description )
values('{0}','{1}','{2}','{3}','{4}')", dr["产成品编号(自己的号)"], version, dr["型号"], dr["成品类别"], dr["描述"]);
sqls.Add(sql);
}
sql = string.Format(@"
insert into ProductCustomerProperty(ProductNumber ,Version ,CustomerProductNumber,CustomerId )
values('{0}','{1}','{2}','{3}')", dr["产成品编号(自己的号)"], version, dr["客户产成品编号（图纸号）"], dr["客户编号"]);
sqls.Add(sql);
sql = string.Format(" select COUNT(*) from ProductStock where ProductNumber ='{0}' and Version ='{1}' "
, dr["产成品编号(自己的号)"], version);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into ProductStock (ProductNumber ,Version ,WarehouseName ,StockQty ,UpdateTime )
values('{0}','{1}','cpk',0,'{2}')", dr["产成品编号(自己的号)"], version, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 临时订单转正式订单
/// </summary>
/// <param name="oldNumber"></param>
/// <param name="newNumber"></param>
/// <returns></returns>
public static string ConvertNumber(string oldNumber, string newNumber)
{
List <string> sqls = new List <string>();
string error = string.Empty;
string sql = string.Format(" update SaleOder set OdersNumber ='{0}',Auditor ='' , CheckTime ='',OdersType='正常订单' where OdersNumber ='{1}' ", newNumber, oldNumber);
sqls.Add(sql);
sql = string.Format("update ProductPlanDetail set OrdersNumber ='{0}' where OrdersNumber ='{1}'", newNumber, oldNumber);
sqls.Add(sql);
sql = string.Format("update ProductPlanSubDetail set OrdersNumber ='{0}' where OrdersNumber ='{1}'", newNumber, oldNumber);
sqls.Add(sql);
sql = string.Format("update ProductWarehouseLogDetail set OrdersNumber ='{0}' where OrdersNumber ='{1}'", newNumber, oldNumber);
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 保存报价单
/// </summary>
/// <param name="quteNumber"></param>
/// <param name="quoteType"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool SaveQuteInfo(string quteNumber, string quoteType, ref string error)
{
string quteNumberNew = string.Empty;
if (quteNumber.Contains("_"))
{
string[] temp = quteNumber.Split('_');
quteNumberNew = temp[0] + "_" + (Convert.ToInt32(temp[1]) + 1).ToString();
}
else
{
quteNumberNew = quteNumber + "_1";
}
string sql = string.Format(" select COUNT (*) from QuoteInfo where QuoteNumber='{0}' ", quteNumberNew);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
error = "该版本报价单已被保存过，请找到最新版的报价单进行修改保存操作！";
return(false);
}
List <string> sqls = new List <string>();
sql = string.Format(@"insert into QuoteInfo
select '{0}',QuoteTime ,QuoteType ,CustomerId,ContactId ,'{1}',
'' from QuoteInfo where QuoteNumber='{2}'", quteNumberNew, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), quteNumber);
sqls.Add(sql);
if (quoteType.Equals("加工报价单"))
{
sql = string.Format(@" insert into MachineQuoteDetail (QuoteNumber,PackageNumber ,ProductNumber ,Version ,CustomerProductNumber
,MaterialNumber,CustomerMaterialNumber,FixedLeadTime ,Hierarchy ,BOMAmount
,MaterialPrcie ,TimeCharge ,Profit ,ManagementPrcie,LossPrcie ,UnitPrice ,Remark
,Description ,SS,BACNumber ,ProductType ,IsOne)
select '{0}',PackageNumber ,ProductNumber ,Version ,CustomerProductNumber
,MaterialNumber,CustomerMaterialNumber,FixedLeadTime ,Hierarchy ,BOMAmount
,MaterialPrcie ,TimeCharge ,Profit ,ManagementPrcie,LossPrcie ,UnitPrice ,Remark
,Description ,SS,BACNumber ,ProductType ,IsOne from MachineQuoteDetail where QuoteNumber='{1}'", quteNumberNew, quteNumber);
sqls.Add(sql);
}
else
{
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 审核采购订单
/// </summary>
/// <param name="OrdersNumbers">订单编号集合</param>
/// <param name="userNumber">当前登录用户k</param>
/// <returns></returns>
public static string AuditorPurchase(string OrdersNumbers, string userNumber)
{
string error = string.Empty;
List <string> sqls = new List <string>();
string sql = string.Format(@" update CertificateOrders set CheckTime ='{0}' ,Auditor ='{1}'
where OrdersNumber in({2}) "
, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userNumber, OrdersNumbers);
sqls.Add(sql);
string[] numbers = OrdersNumbers.Split(',');
foreach (string number in numbers)
{
sql = string.Format(" select PaymentMode from CertificateOrders where OrdersNumber ={0} ", number);
sql = GeneratingCopeSql(SqlHelper.GetScalar(sql), number); //获取产生应付的sql语句
if (!string.IsNullOrEmpty(sql))
{
sqls.Add(sql);
}
}
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 批量添加原材料新
/// </summary>
/// <param name="marerialinfotable"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddMarielToBatch(MarerialInfoTableNew marerialinfotable, ref string error)
{
List <string> sqls = new List <string>();
string sql = string.Format(" select COUNT (*) from MarerialInfoTable where MaterialNumber='{0}' ", marerialinfotable.MaterialNumber);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into MarerialInfoTable (MaterialNumber,MaterialName,Description,Kind,Type,Brand,StockSafeQty,
ProcurementPrice,MaterialPosition,MinPacking,MinOrderQty,ScrapPosition,Remark,Cargo)
values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}','{12}','{13}')",
marerialinfotable.MaterialNumber, marerialinfotable.MaterialName, marerialinfotable.Description, marerialinfotable.Kind,
marerialinfotable.Type, marerialinfotable.Brand, marerialinfotable.StockSafeQty, marerialinfotable.ProcurementPrice,
marerialinfotable.MaterialPosition, marerialinfotable.MinPacking, marerialinfotable.MinOrderQty,
marerialinfotable.ScrapPosition, marerialinfotable.Remark, marerialinfotable.Cargo);
sqls.Add(sql);
sqls.AddRange(GetAddMaterialSupplieAndCustomerSql(marerialinfotable));
}
else
{
sqls.AddRange(GetAddMaterialSupplieAndCustomerSql(marerialinfotable));
}
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 批量导入历史贸易报价单
/// </summary>
/// <param name="tods"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool BacthAddQuoteInfoTradingForListory(FileUpload FU_Excel, HttpServerUtility server, string userId, ref string error)
{
string sqlTemp = string.Format(" delete T_TradingQuoteDetail_Temp where userId='{0}' ", userId);
SqlHelper.ExecuteSql(sqlTemp, ref error);
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
DataTable dt = ds.Tables[0];
int i = 0;
string tempError = string.Empty;
if (dt.Rows.Count <= 0)
{
error = "没有要添加的数据";
return(false);
}
foreach (DataRow dr in dt.Rows)
{
tempError = "";
if (!AddQuoteInfoTradingForListory(dr, ref tempError, userId))
{
i++;
error += string.Format("添加失败：原因--{0}<br/>", tempError);
}
}
bool result = i > 0 ? false : true;
if (!result)
{
error = string.Format("添加成功{0}条，添加失败{1}条。<br/>{2}", (dt.Rows.Count - i).ToString(), i.ToString(), error);
}
//=================================以下将临时表中的数据导入报价单======================================
List <string> sqls = new List <string>();
string sql = string.Format(@" select userId,CustomerId,ContactId,QuoteTime from T_TradingQuoteDetail_Temp
where userId='{0}'
group by userId,CustomerId,ContactId,QuoteTime ", userId);
DataTable dtNew = SqlHelper.GetTable(sql);
string bjNumber = "";
int h = 1;
foreach (DataRow drNew in dtNew.Rows)
{
bjNumber = "MYBJ" + DateTime.Now.AddSeconds(h).ToString("yyyyMMddHHmmss");
//开一个报价单
sql = string.Format(@"insert into QuoteInfo(QuoteNumber,QuoteTime,QuoteType,CustomerId ,ContactId,CreateDateTime ,QuoteUser )
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", bjNumber, drNew["QuoteTime"], "贸易报价单",
drNew["CustomerId"], drNew["ContactId"], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userId);
sqls.Add(sql);
sql = string.Format(@" insert into TradingQuoteDetail (QuoteNumber,ProductNumber,CustomerMaterialNumber,FixedLeadTime
,UnitPrice,MinPackage,MinMOQ,MaterialDescription,DW,Remark,SupplierMaterialNumber)
select '{4}',MaterialNumber,CustomerMaterialNumber,FixedLeadTime
,UnitPrice,MinPackage,MinMOQ,MaterialDescription, DW,Remark ,SupplierMaterialNumber from T_TradingQuoteDetail_Temp
where userId='{0}' and CustomerId='{1}' and ContactId='{2}'
and QuoteTime='{3}' ", userId, drNew["CustomerId"], drNew["ContactId"], drNew["QuoteTime"], bjNumber);
sqls.Add(sql);
h++;
}
string tempErrorNew = string.Empty;
SqlHelper.BatchExecuteSql(sqls, ref tempErrorNew);
return(result);
}
/// <summary>
/// 导入采购订单
/// </summary>
/// <param name="FU_Excel"></param>
/// <param name="server"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool ImpCertificateOrdersList_New(FileUpload FU_Excel, string userId, HttpServerUtility server, ref string error)
{
string sql = string.Format(" delete T_CertificateOrders where userId='{0}'", userId);
SqlHelper.ExecuteSql(sql);
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
DataTable dt = ds.Tables[0];
int i = 0;
string tempError = string.Empty;
if (dt.Rows.Count <= 0)
{
error = "没有要添加的数据";
return(false);
}
foreach (DataRow dr in dt.Rows)
{
tempError = "";
if (!AddCertificateOrdersDetail_New(dr, userId, ref tempError))
{
i++;
error += string.Format("添加失败：原因--{0}<br/>", tempError);
}
}
bool result = i > 0 ? false : true;
if (!result)
{
error = string.Format("添加成功{0}条，添加失败{1}条。<br/>{2}", (dt.Rows.Count - i).ToString(), i.ToString(), error);
}
int a = 1;
string cgOrderNumber = string.Empty;
List <string> sqls = new List <string>();
sql = string.Format(@"select HTNumber ,OrdersDate,PaymentMode,SupplierId ,ContactId from T_CertificateOrders group by HTNumber ,OrdersDate,PaymentMode,SupplierId ,ContactId ");
DataTable dtNew = SqlHelper.GetTable(sql);
foreach (DataRow drNew in dtNew.Rows)
{
cgOrderNumber = "CG" + DateTime.Now.AddSeconds(a).ToString("yyyyMMddHHmmss");
a++;
sql = string.Format(@"insert into CertificateOrders (OrdersNumber ,HTNumber ,OrdersDate,
PaymentMode ,SupplierId ,ContactId ,OrderStatus,CreateTime )
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", cgOrderNumber, drNew["HTNumber"]
, drNew["OrdersDate"], drNew["PaymentMode"], drNew["SupplierId"], drNew["ContactId"], "未完成", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
sql = string.Format(@" insert into CertificateOrdersDetail
(OrdersNumber,MaterialNumber ,LeadTime,SupplierMaterialNumber ,OrderQty ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,
SumPrice,UnitPrice_C,SumPrice_C
,MinOrderQty ,PayOne ,PayTwo )
select '{0}',tco.MaterialNumber ,tco.LeadTime ,tco.SupplierMaterialNumber,tco.OrderQty ,tco.OrderQty ,0,msp.Prcie
,tco.OrderQty *msp.Prcie ,msp.Prcie *1.17,tco.OrderQty *msp.Prcie *1.17,tco.MinOrderQty ,tco.PayOne ,tco.PayTwo
from T_CertificateOrders tco inner join MaterialSupplierProperty msp on tco.SupplierMaterialNumber=msp.SupplierMaterialNumber
and tco.SupplierId =msp.SupplierId
where tco.HTNumber ='{1}' and tco.OrdersDate ='{2}' and tco.PaymentMode='{3}' and tco.SupplierId ='{4}' and tco.ContactId ='{5}' and tco.userId='{6}'"
, cgOrderNumber, drNew["HTNumber"]
, drNew["OrdersDate"], drNew["PaymentMode"], drNew["SupplierId"], drNew["ContactId"], userId);
sqls.Add(sql);
}
sql = string.Format(@"delete CertificateOrders where OrdersNumber not in (
select distinct OrdersNumber from CertificateOrdersDetail)");
sqls.Add(sql);
string tmeptempErrpr = string.Empty;
bool resulTwo = SqlHelper.BatchExecuteSql(sqls, ref tmeptempErrpr);
if (result && resulTwo)
{
return(true);
}
else
{
error = error + "<br/>" + tmeptempErrpr;
return(false);
}
}
/// <summary>
/// 数据自动恢复错误。
/// </summary>
public static bool ZDJC()
{
List <string> sqls = new List <string>();
string sql = @"
update
MachineOderDetail
set
DeliveryQty=t.ConformenceQty,
NonDeliveryQty=Qty-t.ConformenceQty,
Status=
(
case when (Qty-t.ConformenceQty)=0
then '已完成'
else '未完成'
end
)
from
MachineOderDetail m
inner join
(
select
OrdersNumber,
ProductNumber ,
Version ,
RowNumber,
SUM(ConformenceQty) ConformenceQty
from
DeliveryNoteDetailed dnd inner join DeliveryBill db on dnd.DeliveryNumber=db.DeliveryNumber
where db.IsConfirm='已确认'
group by
OrdersNumber,ProductNumber ,Version ,RowNumber
) t
on
t.OrdersNumber=m.OdersNumber
and t.ProductNumber=m.ProductNumber
and t.Version=m.Version
and t.RowNumber=m.RowNumber
where m.DeliveryQty!=t.ConformenceQty
";
sqls.Add(sql);
sql = @"
update
TradingOrderDetail
set
DeliveryQty=t.ConformenceQty,
NonDeliveryQty=Quantity-t.ConformenceQty,
Status =
(
case
when
(Quantity-t.ConformenceQty)=0
then
'已完成'
else
'未完成'
end
)
from
TradingOrderDetail m
inner join
(
select
OrdersNumber,
ProductNumber ,
RowNumber,
SUM(ConformenceQty) ConformenceQty
from
DeliveryNoteDetailed dnd inner join DeliveryBill db on dnd.DeliveryNumber=db.DeliveryNumber
where db.IsConfirm='已确认'
group by
OrdersNumber,ProductNumber ,RowNumber
) t
on
t.OrdersNumber=m.OdersNumber
and t.ProductNumber=m.ProductNumber
and t.RowNumber=m.RowNumber
where
m.DeliveryQty!=t.ConformenceQty
";
sqls.Add(sql);
sql = @"update SaleOder set OrderStatus ='已完成' where OdersNumber in (
select OdersNumber from TradingOrderDetail group by OdersNumber having(SUM (NonDeliveryQty ))=0
union
select OdersNumber from MachineOderDetail group by OdersNumber having(SUM (NonDeliveryQty ))=0)";
sqls.Add(sql);
sql = string.Format(@"
update SaleOder set OrderStatus ='未完成' where OdersNumber not in (
select OdersNumber from TradingOrderDetail group by OdersNumber having(SUM (NonDeliveryQty ))=0
union
select OdersNumber from MachineOderDetail group by OdersNumber having(SUM (NonDeliveryQty ))=0)");
sqls.Add(sql);
sql = string.Format(@"update ProductPlanDetail set StorageQty=a.总入库数量 from
(
select DocumentNumber,ProductNumber ,Version ,RowNumber,OrdersNumber,SUM(Qty) 总入库数量 from ProductWarehouseLogDetail
where DocumentNumber like'KG%'
group by DocumentNumber,ProductNumber ,Version ,RowNumber,OrdersNumber )
a
left join ProductPlanDetail b
on a.DocumentNumber=b.PlanNumber
and a.OrdersNumber=b.OrdersNumber
and a.ProductNumber=b.ProductNumber
and a.Version=b.Version
and a.RowNumber=b.RowNumber
where a.总入库数量!=b.StorageQty");
sqls.Add(sql);
string error = string.Empty;
return(SqlHelper.BatchExecuteSql(sqls, ref error));
}
/// <summary>
/// 审核销售订单
/// </summary>
/// <param name="autor">审核人ID</param>
/// <param name="orderNumbers">销售订单集合</param>
/// <returns></returns>
public static string CheckSaleOrder(string autor, string orderNumbers)
{
string createtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string error = string.Empty;
List <string> sqls = new List <string>();
string sql = string.Format(@" update SaleOder set CheckTime ='{0}' ,Auditor ='{1}'
where OdersNumber in({2}) "
, createtime, autor, orderNumbers);
sqls.Add(sql);
//产生预收第一层
sql = string.Format(@"insert into AccountsReceivable (OrdersNumber,CustomerOrdersNumber,SumPrice,CustomerId,PaymentTypes,SKFS,YSOne,YSTwo,CreateTime,IsAdvance) (select t.* from (
select so.OdersNumber as 销售订单号,
so.CustomerOrderNumber as 客户采购订单号,
t.订单总价,
c.CustomerId as 客户编号,
c.ReceiveType as 收款类型 ,
c.MakeCollectionsModeId 收款方式,
t.预收一,
t.预收二,
'{1}' as 创建时间,
'{2}' as 是否为预收
from
SaleOder so
inner join (select SUM(TotalPrice) as 订单总价,
isnull(SUM(cast(ReceiveOne as decimal(18,2))),0) as 预收一,
isnull(sum(cast(ReceiveTwo as decimal(18,2))),0) as 预收二,OdersNumber from TradingOrderDetail where OdersNumber in ({0}) group by OdersNumber
) t on t.OdersNumber =so.OdersNumber
inner join Customer c on so.CustomerId=c.CustomerId where ISNULL(so.CheckTime,'')!='' and (c.MakeCollectionsModeId='YSBF' or c.MakeCollectionsModeId='YSQK')
union all
select so.OdersNumber as 销售订单号,
so.CustomerOrderNumber as 客户采购订单号 ,
t.订单总价,
c.CustomerId as 客户编号,
c.ReceiveType as 收款类型 ,
c.MakeCollectionsModeId 收款方式,
t.预收一,
t.预收二,
'{1}' as 创建时间,
'{2}' as 是否为预收
from SaleOder so
inner join (select SUM(SumPrice)as 订单总价,
isnull(SUM(cast(ReceiveOne as decimal(18,2))),0) as 预收一,
isnull(sum(cast(ReceiveTwo as decimal(18,2))),0) as 预收二,
OdersNumber from MachineOderDetail where OdersNumber in ({0}) group by OdersNumber )t
on so.OdersNumber=t.OdersNumber
inner join Customer c on so.CustomerId=c.CustomerId where isnull(so.CheckTime,'')!='' and (c.MakeCollectionsModeId='YSBF' or c.MakeCollectionsModeId='YSQK'))t)", orderNumbers, createtime, '是');
sqls.Add(sql);
// sql = string.Format(@"select count(*) from SaleOder so inner join TradingOrderDetail tod
//on so.OdersNumber =tod.OdersNumber
//where tod.OdersNumber in({0})", orderNumbers);
// if (!SqlHelper.GetScalar(sql).Equals("0"))//有贸易销售订单 就自动产生出库单
// {
// string createTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// string warhouseNumber = "YCLCK" + DateTime.Now.ToString("yyyyMMddHHmmss");
// sql = string.Format(@"
//insert into MarerialWarehouseLog( WarehouseNumber,WarehouseName ,ChangeDirection,Type ,
//Creator,CreateTime ,Remark ,IsConfirm )
//values('{0}','ycl','出库','销售出库（贸易）','{1}','{2}','由贸易销售订单审核产生','是' )
//", warhouseNumber, autor, createTime);
// sqls.Add(sql);
// sql = string.Format(@"
//insert into MaterialWarehouseLogDetail (WarehouseNumber ,DocumentNumber ,MaterialNumber ,RowNumber ,LeadTime ,CreateTime ,
//CustomerMaterialNumber ,Qty ,UnitPrice)
//select '{0}',so.OdersNumber ,tod.ProductNumber,tod.RowNumber ,tod.Delivery ,'{1}',tod.CustomerMaterialNumber ,
//tod.Quantity ,tod.UnitPrice from SaleOder so inner join TradingOrderDetail tod
//on so.OdersNumber =tod.OdersNumber
//where tod.OdersNumber in({2}) ", warhouseNumber, createTime, orderNumbers);
// sqls.Add(sql);
// }
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 批量导入销售订单
/// </summary>
/// <param name="so"></param>
/// <param name="FU_Excel"></param>
/// <param name="server"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool BacthAddSaleOrderNew(SaleOder so, FileUpload FU_Excel, HttpServerUtility server, string userId, ref string error)
{
DataSet ds = ToolManager.ImpExcel(FU_Excel, server);
if (ds == null)
{
error = "选择的文件为空或不是标准的Excel文件！";
return(false);
}
string sql = string.Format(@" delete T_ImpSaleOder_Temp where userId='{0}'
", userId);
SqlHelper.ExecuteSql(sql);
DataTable dt = ds.Tables[0];
int i = 0;
string tempError = string.Empty;
if (dt.Rows.Count <= 0)
{
error = "没有要添加的数据";
return(false);
}
foreach (DataRow dr in dt.Rows)
{
tempError = "";
if (!AddSaleOrder(dr, so, userId, ref tempError))
{
i++;
error += string.Format("添加失败：原因--{0}<br/>", tempError);
}
}
bool result = i > 0 ? false : true;
if (!result)
{
error = string.Format("添加成功{0}条，添加失败{1}条。<br/>{2}", (dt.Rows.Count - i).ToString(), i.ToString(), error);
}
List <string> sqls = new List <string>();
string xsNumber = "";
sql = string.Format(@"select COUNT (*) from T_ImpSaleOder_Temp where ismateriNumber ='是' and userid='{0}'", userId);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
xsNumber = "XS" + DateTime.Now.AddSeconds(1).ToString("yyyyMMddHHmmss");
sql = string.Format(@"insert into SaleOder(OdersNumber,OrdersDate,OdersType,ProductType ,
MakeCollectionsMode,CustomerId ,ContactId ,OrderStatus ,CreateTime ,CustomerOrderNumber,KhddH )
values('{0}','{1}','{2}','贸易','{3}','{4}','{5}','未完成','{6}','{7}','{8}')", xsNumber, so.OrdersDate,
so.OdersType.Equals("包装生产订单") ? "正常订单" : so.OdersType, so.MakeCollectionsMode, so.CustomerId, so.ContactId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
so.CustomerOrderNumber, so.KhddH);
sqls.Add(sql);
sql = string.Format(@"insert into TradingOrderDetail (OdersNumber ,ProductNumber,RowNumber ,Delivery ,SN,CustomerMaterialNumber,MaterialName,Brand
,Quantity ,NonDeliveryQty ,DeliveryQty ,UnitPrice ,TotalPrice ,CreateTime ,Status )
select '{0}',tit.ProductNumber,tit.RowNumber,CONVERT(varchar(100), CAST(tit.LeadTime as date ), 23),1,tit.CustomerProductNumber,vtdn .物料描述
,vtdn.品牌,tit.Qty,tit.Qty,0,vtdn .单价,vtdn .单价*tit.Qty,'{1}','未完成'
from T_ImpSaleOder_Temp tit inner join V_FindLastNewPriceForTradingQuoteDetail vtdn on tit.ProductNumber=vtdn.原材料编号
where tit.userId='{2}' and vtdn.客户名称='{3}' and tit.IsMateriNumber='是'", xsNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), userId, so.CustomerName);
sqls.Add(sql);
}
sql = string.Format(@"select COUNT (*) from T_ImpSaleOder_Temp where ismateriNumber ='否' and userid='{0}'", userId);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
xsNumber = "XS" + DateTime.Now.AddSeconds(2).ToString("yyyyMMddHHmmss");
sql = string.Format(@"insert into SaleOder(OdersNumber,OrdersDate,OdersType,ProductType ,
MakeCollectionsMode,CustomerId ,ContactId ,OrderStatus ,CreateTime ,CustomerOrderNumber,KhddH )
values('{0}','{1}','{2}','加工','{3}','{4}','{5}','未完成','{6}','{7}','{8}')", xsNumber, so.OrdersDate,
so.OdersType, so.MakeCollectionsMode, so.CustomerId, so.ContactId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
so.CustomerOrderNumber, so.KhddH);
sqls.Add(sql);
sql = string.Format(@"
insert into MachineOderDetail (OdersNumber ,ProductNumber ,Version ,LeadTime ,RowNumber ,SN ,CustomerProductNumber ,Qty ,NonDeliveryQty
,DeliveryQty ,UnitPrice ,SumPrice ,CreateTime ,Status )
select '{0}',tit.ProductNumber ,tit.Version ,CONVERT(varchar(100), CAST(tit.LeadTime as date ), 23) ,tit.RowNumber ,1,tit.CustomerProductNumber ,tit.Qty
,tit.Qty ,0,vmr.单价未税,tit.Qty *vmr.单价未税,'{1}','未完成' from T_ImpSaleOder_Temp tit
inner join V_FindLastNewPriceForMachineQuoteDeatil vmr on tit.ProductNumber
=vmr.产成品编号 and tit.Version =vmr.版本 where tit.UserId ='{2}' and vmr.客户名称='{3}' and tit.IsMateriNumber='否' ", xsNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
, userId, so.CustomerName);
sqls.Add(sql);
}
sql = string.Format(@"delete SaleOder where OdersNumber not in (
select distinct OdersNumber from MachineOderDetail
union all
select distinct OdersNumber from TradingOrderDetail)");
sqls.Add(sql);
string errorTwo = string.Empty;
bool resultTwo = SqlHelper.BatchExecuteSql(sqls, ref errorTwo);
if (result && resultTwo)
{
return(true);
}
else
{
error += "<br/>" + errorTwo;
return(false);
}
}
/// <summary>
/// 销售出库（贸易）审核
/// </summary>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static string CheckMYXS(string auditor, string warehouseNumber)
{
string sql = string.Empty;
DataTable dtTemp = new DataTable();
string result = string.Empty;
List <string> sqls = new List <string>();
string error = string.Empty;
sql = string.Format(@"select mwld.DocumentNumber ,mwld.MaterialNumber ,mwld.LeadTime
from MaterialWarehouseLogDetail mwld inner join MarerialWarehouseLog mwl on mwl.WarehouseNumber =mwld.WarehouseNumber
left join TradingOrderDetail cod on mwld.DocumentNumber=cod.OdersNumber
and mwld.MaterialNumber =cod.ProductNumber and mwld.LeadTime =cod.Delivery and mwld.RowNumber =cod.RowNumber
where mwld.Qty -cod.NonDeliveryQty >0
and mwl.WarehouseNumber ='{0}'", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录的数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//纠错 仅限于销售出库（贸易）
string tempsql1 = string.Format(@"
DELETE MaterialWarehouseLogDetail
WHERE WarehouseNumber = '{0}'
AND Qty < 0;
", warehouseNumber);
SqlHelper.ExecuteSql(tempsql1);
//更新订单完成数量
//sqls.Add(GetUpdateQtySql(warehouseNumber));
//生成送货单
List <string> sqlsTemp = GetSqlForShSHD(warehouseNumber);
if (sqlsTemp.Count > 0)
{
sqls.AddRange(sqlsTemp);
}
sql = string.Format(@"
INSERT INTO T_LessMaterialBreakdown(WarehouseNumber ,DocumentNumber ,MaterialNumber ,RowNumber ,LeadTime ,CustomerMaterialNumber ,LibraryQty,StockQty ,LessMaterialQty ,CreateTime ,IsLessMaterial)
SELECT '',
mwld.DocumentNumber ,
mwld. MaterialNumber,
'',
'' ,
mwld.CustomerMaterialNumber,
mwld. Qty,
vmq.StockQty,
CASE
WHEN vmq.StockQty<0 THEN 0-mwld.Qty
ELSE vmq.StockQty -mwld.Qty
END,
'{1}',
'未还料'
FROM
(SELECT DocumentNumber,
MaterialNumber,
CustomerMaterialNumber,
SUM(Qty) Qty
FROM MaterialWarehouseLogDetail
WHERE WarehouseNumber ='{0}'
GROUP BY DocumentNumber,
MaterialNumber,
CustomerMaterialNumber) mwld
INNER JOIN MaterialStock vmq ON mwld.MaterialNumber =vmq.MaterialNumber
WHERE vmq.StockQty-mwld.Qty <0
", warehouseNumber, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
//更新库存
sql = string.Format(@"update MaterialStock set StockQty =
case when a.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
( select mwld.MaterialNumber,mwl.ChangeDirection,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on
mwld.WarehouseNumber =mwl.WarehouseNumber
where mwl.WarehouseNumber='{0}'
group by mwld.MaterialNumber,mwl.ChangeDirection) a
inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
where ms.WarehouseName ='ycl'
", warehouseNumber);
sqls.Add(sql);
sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber ='{2}'", auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
//更新瞬时库存数量
sqls.Add(StoreroomToolManager.GetUpDateInventoryQtySql(warehouseNumber));
//写入流水账
sqls.Add(StoreroomToolManager.WriteMateriLSZ(warehouseNumber, auditor));
sql = string.Format(" delete DeliveryNoteDetailed where DeliveryQty=0 ");//清除发货数量为0的送货单
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 原材料出入库审核【单条审核】
/// </summary>
/// <param name="auditor"></param>
/// <param name="warehouseNumber"></param>
/// <returns></returns>
public static string AuditorMarerialWarehouseLogForWarehouseNumber(string auditor, string warehouseNumber)
{
string checksqls = string.Format(@" select CheckTime from MarerialWarehouseLog where WarehouseNumber='{0}'", warehouseNumber);
if (!string.IsNullOrEmpty(SqlHelper.GetScalar(checksqls)))
{
return("该单已审核，请勿重复审核！");
}
List <string> sqls = new List <string>();
DataTable dtTemp = null;
string result = string.Empty;
string sql = string.Format(@"
select COUNT(*) from MaterialWarehouseLogDetail where WarehouseNumber='{0}'", warehouseNumber);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
return("没有需要审核的记录！");
}
sql = string.Format("select Type from MarerialWarehouseLog where WarehouseNumber='{0}'", warehouseNumber);
string type = SqlHelper.GetScalar(sql);
if (type.Equals("生产出库") || type.Equals("包装出库"))
{
return(SCCKAuditor(auditor, warehouseNumber));
}
if (type.Equals("销售出库（贸易）"))
{
return(CheckMYXS(auditor, warehouseNumber));
}
//步骤一：检测采购入库和销售出库（贸易）的数量-订单未交数量是否大于0（大于0为异常数据）
if (type.Equals("采购入库"))
{
sql = string.Format(@"
select t.DocumentNumber ,t.MaterialNumber ,t.LeadTime from (
select WarehouseNumber,DocumentNumber,MaterialNumber,LeadTime,sum(isnull(Qty,0)) qty from MaterialWarehouseLogDetail
where WarehouseNumber ='{0}'
group by
WarehouseNumber,DocumentNumber,MaterialNumber,LeadTime
)t left join CertificateOrdersDetail cod on t.DocumentNumber=cod.OrdersNumber
and t.MaterialNumber =cod.MaterialNumber and t.LeadTime =cod.LeadTime where t.Qty -cod.NonDeliveryQty >0", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += "<br/>" + string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//sqls.AddRange(FinancialManager.GetCSYF(warehouseNumber));
}
else if (type.Equals("销售出库（贸易）"))
{
sql = string.Format(@"select mwld.DocumentNumber ,mwld.MaterialNumber ,mwld.LeadTime
from MaterialWarehouseLogDetail mwld inner join MarerialWarehouseLog mwl on mwl.WarehouseNumber =mwld.WarehouseNumber
left join TradingOrderDetail cod on mwld.DocumentNumber=cod.OdersNumber
and mwld.MaterialNumber =cod.ProductNumber and mwld.LeadTime =cod.Delivery and mwld.RowNumber =cod.RowNumber
where mwld.Qty -cod.NonDeliveryQty >0
and mwl.WarehouseNumber ='{0}'", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
if (dtTemp.Rows.Count > 0)
{
foreach (DataRow dr in dtTemp.Rows)
{
result += string.Format(" 订单编号为：{0} 原材料编号为：{1} 交期为{2}的记录的数量大于订单未交数量！ "
, dr["DocumentNumber"], dr["MaterialNumber"], dr["LeadTime"]);
}
return(result);
}
//纠错 仅限于销售出库（贸易）
string tempsql1 = string.Format(@"
DELETE MaterialWarehouseLogDetail
WHERE WarehouseNumber = '{0}'
AND Qty < 0;
", warehouseNumber);
SqlHelper.ExecuteSql(tempsql1);
//更新订单完成数量
sqls.Add(GetUpdateQtySql(warehouseNumber));
//生成送货单
List <string> sqlsTemp = GetSqlForShSHD(warehouseNumber);
if (sqlsTemp.Count > 0)
{
sqls.AddRange(sqlsTemp);
}
}
//步骤二：检测库存数量是否满足出库
if (type.Equals("销售出库（贸易）"))
{
string tempchecksql = string.Format(@" select COUNT (*) from (
select t.MaterialNumber,t.WarehouseName, (ISNULL ( ms.StockQty,0)-t.Qty) as 差 from (
select mwld.MaterialNumber ,sum( mwld.Qty ) as Qty,mwl.WarehouseName
from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on mwld.WarehouseNumber=mwl.WarehouseNumber
where mwl.ChangeDirection ='出库' and mwl.WarehouseNumber in ('{0}') and mwld.Qty>0
group by mwld.MaterialNumber,mwl.WarehouseName ) t left join MaterialStock ms on t.MaterialNumber=ms.MaterialNumber ) t where 差<0", warehouseNumber);
if (!SqlHelper.GetScalar(tempchecksql).Equals("0"))
{
return("当前库存数量低，无法满足当前出库操作。");
}
//return SqlHelper.GetScalar(sql).Equals("0") ? true : false;
}
else
{
//if (!CheckInventoryQty("'" + warehouseNumber + "'"))
//{
// return "当前库存数量低，无法满足当前出库操作。";
//}
}
//步骤三：同步更新库存数量 并产生应付 同步更新采购入库的已交货数量
string paymentdays = string.Empty;
string mode = string.Empty;
string CGOrderNumbers = string.Empty;
string error = string.Empty;
sql = string.Format(@"select * from V_MaterialWarehouseLogDetail_Detail where 出入库编号='{0}' ", warehouseNumber);
dtTemp = SqlHelper.GetTable(sql);
foreach (DataRow dr in dtTemp.Rows)
{
if (type.Equals("采购入库")) //产生应付
{
sql = string.Format(@"update CertificateOrdersDetail set DeliveryQty +={0}
where OrdersNumber='{1}' and MaterialNumber='{2}' and LeadTime ='{3}' and Status ='未完成'
", dr["数量"], dr["订单编号"], dr["原材料编号"], dr["交期"]); //同步更新采购入库的已交货数量
sqls.Add(sql);
CGOrderNumbers += string.Format("'{0}',", dr["订单编号"]);
}
//同步更新库存数量
//sqls.Add(GetUpdateInventoryQtySql(dr["原材料编号"].ToString(), dr["仓库ID"].ToString(), dr["数量"].ToString(), dr["方向"].ToString()));
}
string timeNow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//类型为采购入库=======生成预付第二层信息
sql = string.Format(@"insert into T_AccountsPayable_Detail(ReceiptNo,PurchaseOrderNumber ,PurchaseContractNumber,MaterialNumber ,SupplierMaterialNumber
,Description,PurchaseCount,NumberOfArrival,UnitPrice ,SumPrice ,TransportNo,LeadTime,CreateTime )
select vmld.WarehouseNumber ,vmld.DocumentNumber ,co.HTNumber,co.MaterialNumber,co.SupplierMaterialNumber,mit.Description ,co.OrderQty
,vmld.Qty,co.UnitPrice ,co.UnitPrice *vmld.Qty ,vmld.RoadTransport,vmld.LeadTime,'{1}' from ( select mwl.Type,mwld.* from MarerialWarehouseLog mwl inner join MaterialWarehouseLogDetail mwld on mwl.WarehouseNumber =mwld.WarehouseNumber
) vmld inner join ( select co.HTNumber ,co.PaymentMode ,cod.* from CertificateOrders co inner join CertificateOrdersDetail cod on co.OrdersNumber=cod.OrdersNumber
) co on vmld.DocumentNumber =
co.OrdersNumber and vmld.MaterialNumber =co.MaterialNumber and vmld.LeadTime =co.LeadTime
inner join MarerialInfoTable mit on vmld .MaterialNumber =mit.MaterialNumber
where vmld.WarehouseNumber ='{0}' and vmld.Type ='采购入库' ", warehouseNumber, timeNow);
sqls.Add(sql);
//类型为采购入库的生成应付第一层
sql = string.Format(@"insert into T_AccountsPayable_Main(OrdersNumber,CreateTime ,CGHTNumber ,ArrivalNumber,SumPrice ,ArrivalPrice
,SupplierId,PaymentTypes,PaymentMode,DeliveryDate)
select a.OrdersNumber ,'{0}',co.HTNumber ,a.到货数量,a.订单总价,a.到货总价,si.SupplierId ,si.PayType,si.PaymentMode,CONVERT(varchar(100), GETDATE(), 23) from (
select co.OrdersNumber,SUM(vmld.Qty ) as 到货数量,SUM (vmld .Qty *co.UnitPrice ) as 到货总价,SUM( co.SumPrice) as 订单总价 from ( select mwl.Type,mwld.* from MarerialWarehouseLog mwl inner join MaterialWarehouseLogDetail mwld on mwl.WarehouseNumber =mwld.WarehouseNumber
) vmld inner join ( select co.HTNumber ,co.PaymentMode,co.SupplierId ,cod.* from CertificateOrders co inner join CertificateOrdersDetail cod on co.OrdersNumber=cod.OrdersNumber
) co on vmld.DocumentNumber =
co.OrdersNumber and vmld.MaterialNumber =co.MaterialNumber and vmld.LeadTime =co.LeadTime
inner join MarerialInfoTable mit on vmld .MaterialNumber =mit.MaterialNumber
inner join SupplierInfo si on co.SupplierId=si.SupplierId
where vmld.WarehouseNumber ='{1}' and vmld.Type ='采购入库' and co.PaymentMode!='YFBF' and co.PaymentMode!='YFQK'
group by co.OrdersNumber) a inner join CertificateOrders co on a.OrdersNumber =co.OrdersNumber
inner join SupplierInfo si on co.SupplierId =si.SupplierId ", timeNow, warehouseNumber);
sqls.Add(sql);
//统一更新库存数量
// sql = string.Format(@"update MaterialStock set StockQty =
//case when mwl.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
//,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
//( select mwld.MaterialNumber,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
// inner join MarerialWarehouseLog mwl on
// mwld.WarehouseNumber =mwl.WarehouseNumber
// where mwl.WarehouseNumber='{0}'
// group by mwld.MaterialNumber) a
// inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
// where ms.WarehouseName ='ycl'
// ", warehouseNumber);
sql = string.Format(@"update MaterialStock set StockQty =
case when a.ChangeDirection='入库' then StockQty +a.Qty else StockQty-a.Qty end
,UpdateTime =CONVERT (varchar(100),GETDATE(),120) from
( select mwld.MaterialNumber,mwl.ChangeDirection,sum(mwld.Qty) as Qty from MaterialWarehouseLogDetail mwld
inner join MarerialWarehouseLog mwl on
mwld.WarehouseNumber =mwl.WarehouseNumber
where mwl.WarehouseNumber='{0}'
group by mwld.MaterialNumber,mwl.ChangeDirection) a
inner join MaterialStock ms on ms.MaterialNumber=a.MaterialNumber
where ms.WarehouseName ='ycl'
", warehouseNumber);
sqls.Add(sql);
//如果是采购退料出库则反写出库单号至采购订单上
if (type.Equals("采购退料出库"))
{
string tempSql = string.Format(@"
update CertificateOrders set CCTCOrdersNumber='{0}'
where OrdersNumber in (
select distinct DocumentNumber from MaterialWarehouseLogDetail
where WarehouseNumber='{0}'
)", warehouseNumber);
sqls.Add(tempSql);
//warehouseNumber
}
//步骤四：同步更新采购入库订单明细的已交货、未交货、状态以及整条订单状态
sql = string.Format("update CertificateOrdersDetail set NonDeliveryQty =OrderQty-DeliveryQty");
sqls.Add(sql);
sql = string.Format("update CertificateOrdersDetail set Status ='已完成' where NonDeliveryQty=0");
sqls.Add(sql);
if (!string.IsNullOrEmpty(CGOrderNumbers))
{
sql = string.Format(@"update CertificateOrders set OrderStatus ='已完成'
where OrdersNumber in (
select OrdersNumber from CertificateOrdersDetail where OrdersNumber in({0})
group by OrdersNumber having SUM(NonDeliveryQty )=0
) and OrderStatus ='未完成'", CGOrderNumbers.TrimEnd(',')); //更新采购订单主表的状态
sqls.Add(sql);
}
sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber ='{2}'", auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
//更新瞬时库存数量
sqls.Add(StoreroomToolManager.GetUpDateInventoryQtySql(warehouseNumber));
//写入流水账
sqls.Add(StoreroomToolManager.WriteMateriLSZ(warehouseNumber, auditor));
sql = string.Format(" delete DeliveryNoteDetailed where DeliveryQty=0 ");//清除发货数量为0的送货单
sqls.Add(sql);
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 审核原材料出入库(产生应付、更新库存数量、更新采购订单已交货数量 )【批量审核】(销售出库、维修出库、样品出库产生送货单)
/// </summary>
/// <param name="auditor">审核人</param>
/// <param name="warehouseNumber">出入库编号集合</param>
/// <returns></returns>
public static string AuditorMarerialWarehouseLog(string auditor, string warehouseNumber)
{
string error = string.Empty;
string results = CheckQty(warehouseNumber);
if (!string.IsNullOrEmpty(results))
{
return(results);
}
//if (!CheckInventoryQty(warehouseNumber))
//{
// return "当前库存数量低，无法满足当前出库操作。";
//}
List <string> sqls = new List <string>();
string type = string.Empty; //出入库类型
string mode = string.Empty; //付款方式
string paymentdays = string.Empty; //账期
string CGOrderNumbers = string.Empty; //采购订单编号集合
string shNumber = string.Empty;
string checkTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string sql = string.Format(@" update MarerialWarehouseLog set Auditor='{0}' , CheckTime='{1}' where WarehouseNumber in ({2}) and (CheckTime ='' or CheckTime = null)",
auditor, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), warehouseNumber);
sqls.Add(sql);
string[] numbers = warehouseNumber.Split(',');
foreach (string number in numbers) //遍历出入库单
{
sql = string.Format(@"select * from V_MaterialWarehouseLogDetail_Detail where 出入库编号={0} ", number);
DataTable dt = SqlHelper.GetTable(sql);
foreach (DataRow dr in dt.Rows)
{
type = dr["出入库类型"] == null ? "" : dr["出入库类型"].ToString();
if (type.Equals("采购入库")) //产生应付
{
paymentdays = dr["采购账期"] == null ? "" : dr["采购账期"].ToString() + "天";
mode = dr["付款方式"] == null ? "" : dr["付款方式"].ToString();
//货到付款和预付部分货款的采购订单 在此产生应付，其它从数据库计划产生
//货到付款【账期为0天的产生，账期为其它天数的数据库计划产生】
if (mode.Equals("HDFK") && paymentdays.Equals("0天"))
{
sql = string.Format(@" insert into AccountsPayable
(OrdersNumber ,MaterialNumber,CreateTime
,SupplierMaterialNumber,SupplierId,Qty ,UnitPrice,SumPrice ,AccountPeriod)
values('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},'{8}')", dr["订单编号"], dr["原材料编号"],
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), dr["供应商物料编号"], dr["供应商编号"],
dr["数量"], dr["单价"], dr["总价"], paymentdays);
sqls.Add(sql);
}
else if (mode.Equals("YFBF"))//预付部分货款【产生预付剩余货款】
{
sql = string.Format(@" insert into AccountsPayable (OrdersNumber ,MaterialNumber ,CreateTime ,SumPrice,SupplierId )
select co.OrdersNumber,'','{0}',SUM ( coe .SumPrice)*CAST ((1-si.PercentageInAdvance) as decimal(18,2)) , co.SupplierId from CertificateOrders co
inner join CertificateOrdersDetail coe on co.OrdersNumber=coe.OrdersNumber
left join SupplierInfo si on si.SupplierId =co.SupplierId
where co.OrdersNumber ='{1}' group by co.OrdersNumber,co.SupplierId,si.PercentageInAdvance", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), dr["订单编号"]);
sqls.Add(sql);
}
sql = string.Format(@"update CertificateOrdersDetail set DeliveryQty +={0}
where OrdersNumber='{1}' and MaterialNumber='{2}' and LeadTime ='{3}' and Status ='未完成'
", dr["数量"], dr["订单编号"], dr["原材料编号"], dr["交期"]);
sqls.Add(sql);
CGOrderNumbers += string.Format("'{0}',", dr["订单编号"]);
}
else if (type.Equals("销售出库（贸易）"))
{
sql = string.Format(@"update TradingOrderDetail set DeliveryQty ={0}
where OdersNumber ='{1}' and ProductNumber ='{2}' and RowNumber ='{3}'
", dr["数量"], dr["订单编号"], dr["原材料编号"], dr["销售订单行号"]);
sqls.Add(sql);
}
}
}
//更新库存数量
sql = string.Format(@"select t.方向,t.仓库ID,t.原材料编号,SUM (t.数量) as 数量 from V_MaterialWarehouseLogDetail_Detail t
where t.出入库编号 in ({0})
group by t.方向,t.仓库ID,t.原材料编号", numbers);
foreach (DataRow drtemps in SqlHelper.GetTable(sql).Rows)
{
sqls.Add(GetUpdateInventoryQtySql(drtemps["原材料编号"].ToString(), drtemps["仓库ID"].ToString(), drtemps["数量"].ToString(), drtemps["方向"].ToString()));
}
//更新采购订单交货状态
sql = string.Format("update CertificateOrdersDetail set NonDeliveryQty =OrderQty-DeliveryQty");
sqls.Add(sql);
sql = string.Format("update CertificateOrdersDetail set Status ='已完成' where NonDeliveryQty<=0");
sqls.Add(sql);
if (!string.IsNullOrEmpty(CGOrderNumbers))
{
sql = string.Format(@"update CertificateOrders set OrderStatus ='已完成'
where OrdersNumber in (
select OrdersNumber from CertificateOrdersDetail where OrdersNumber in({0})
group by OrdersNumber having SUM(NonDeliveryQty )=0
) and OrderStatus ='未完成'", CGOrderNumbers.TrimEnd(',')); //更新采购订单主表的状态
sqls.Add(sql);
}
//更新销售订单（贸易）的交货状态
sql = string.Format("update TradingOrderDetail set NonDeliveryQty =Quantity-DeliveryQty");
sqls.Add(sql);
sql = string.Format("update TradingOrderDetail set Status ='已完成' where NonDeliveryQty<=0");
sqls.Add(sql);
if (!string.IsNullOrEmpty(CGOrderNumbers))
{
sql = string.Format(@"update SaleOder set OrderStatus ='已完成'
where OdersNumber in (
select OdersNumber from TradingOrderDetail where OdersNumber in({0})
group by OdersNumber having SUM(NonDeliveryQty )=0
) and OrderStatus ='未完成'", CGOrderNumbers.TrimEnd(',')); //更新采购订单主表的状态
sqls.Add(sql);
}
//产生送货单(销售出库（贸易）、样品出库、维修出库)
string tempSql = string.Format(@"select * from V_MaterialWarehouseLogDetail_SaleOder where WarehouseNumber in ({0})", numbers); //临时变量
sql = string.Format(" select distinct t.CustomerId from ( {0} )t ", tempSql); //找出客户
DataTable dtCustomer = SqlHelper.GetTable(sql);
foreach (DataRow dr in dtCustomer.Rows) //按客户进行遍历
{
//销售出库
sql = string.Format(" select count(*) from ({0})t where t.CustomerId='{1}' and t.Type='销售出库（贸易）' ", tempSql, dr["CustomerId"]);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
shNumber = "SH" + DateTime.Now.AddSeconds(1).ToString("yyyyMMddHHmmss");
sql = string.Format(@"insert into DeliveryBill (DeliveryNumber,IsConfirm,CreateTime ,Remark,CustomerId )
values('{0}','未确认','{1}','由原材料销售出库产生','{2}')", shNumber, checkTime, dr["CustomerId"]);
sqls.Add(sql);
sql = string.Format(@"insert into DeliveryNoteDetailed(DeliveryNumber ,OrdersNumber,ProductNumber ,Version ,CustomerProductNumber
,LeadTime ,RowNumber,SN ,MaterialDescription,DeliveryQty)
select '{2}',vps.DocumentNumber,vps.MaterialNumber,'0',vps.CusTomerMaterialNumber,vps.LeadTime,vps.RowNumber
,1,vps.Description,vps.Qty from ({0}) vps where vps.CustomerId='{1}' and
vps.Type='销售出库（贸易）'", tempSql, dr["CustomerId"], shNumber);
sqls.Add(sql);
}
//维修出库
sql = string.Format(" select count(*) from ({0})t where t.CustomerId='{1}' and t.Type='维修出库' ", tempSql, dr["CustomerId"]);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
shNumber = "SH" + DateTime.Now.AddSeconds(2).ToString("yyyyMMddHHmmss");
sql = string.Format(@"insert into DeliveryBill (DeliveryNumber,IsConfirm,CreateTime ,Remark,CustomerId )
values('{0}','未确认','{1}','由原材料维修出库产生','{2}')", shNumber, checkTime, dr["CustomerId"]);
sqls.Add(sql);
sql = string.Format(@"insert into DeliveryNoteDetailed(DeliveryNumber ,OrdersNumber,ProductNumber ,Version ,CustomerProductNumber
,LeadTime ,RowNumber,SN ,MaterialDescription,DeliveryQty)
select '{2}',vps.DocumentNumber,vps.MaterialNumber,'0',vps.CustomerMaterialNumber,vps.LeadTime,vps.RowNumber
,1,vps.Description,vps.Qty from ({0}) vps where vps.CustomerId='{1}' and
vps.Type='维修出库'", tempSql, dr["CustomerId"], shNumber);
sqls.Add(sql);
}
//样品出库
sql = string.Format(" select count(*) from ({0})t where t.CustomerId='{1}' and t.Type='样品出库' ", tempSql, dr["CustomerId"]);
if (!SqlHelper.GetScalar(sql).Equals("0"))
{
shNumber = "SH" + DateTime.Now.AddSeconds(3).ToString("yyyyMMddHHmmss");
sql = string.Format(@"insert into DeliveryBill (DeliveryNumber,IsConfirm,CreateTime ,Remark,CustomerId )
values('{0}','未确认','{1}','由原材料样品出库产生','{2}')", shNumber, checkTime, dr["CustomerId"]);
sqls.Add(sql);
sql = string.Format(@"insert into DeliveryNoteDetailed(DeliveryNumber ,OrdersNumber,ProductNumber ,Version ,CustomerProductNumber
,LeadTime ,RowNumber,SN ,MaterialDescription,DeliveryQty)
select '{2}',vps.DocumentNumber,vps.MaterialNumber,'0',vps.CusTomerMaterialNumber,vps.LeadTime,vps.RowNumber
,1,vps.Description,vps.Qty from ({0}) vps where vps.CustomerId='{1}' and
vps.Type='样品出库'", tempSql, dr["CustomerId"], shNumber);
sqls.Add(sql);
}
}
return(SqlHelper.BatchExecuteSql(sqls, ref error) ? "1" : error);
}
/// <summary>
/// 批量添加原材料之单条添加【仅仅适用于批量添加】
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool AddAddMaterialInfo(DataRow dr, ref string error)
{
Regex r = new Regex(@"[\u4e00-\u9fa5]+");
Match mc = r.Match(dr["原材料编号(瑞普迪编号)"].ToString().Trim());
if (mc.Length != 0)
{
error = "原材料编号禁止输入中文";
return(false);
}
List <string> sqls = new List <string>();
string sql = string.Format(" select COUNT(*) from MarerialInfoTable where MaterialNumber='{0}'", dr["原材料编号(瑞普迪编号)"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into MarerialInfoTable
(MaterialNumber,MaterialName,Description ,Kind ,Type ,Brand,ProcurementPrice ,MinPacking
,MinOrderQty ,Cargo ,Remark,CargoType,NumberProperties ,Unit )
values('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}','{9}','{10}','{11}'
,'{12}','{13}') ", dr["原材料编号(瑞普迪编号)"]
, dr["型号"], dr["描述"], dr["种类"], dr["类别"], dr["品牌"], "0"
, dr["最小包装"],
dr["最小起订量"], dr["货位"], dr["备注"], dr["货物类型"], dr["编号属性"], dr["单位"]);
sqls.Add(sql);
}
if (!dr["客户编号"].ToString().Trim().Equals(""))
{
sql = string.Format(@"
select COUNT(*) from Customer where CustomerId ='{0}' ", dr["客户编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该客户编号:{0},原材料编号：{1} ", dr["客户编号"], dr["原材料编号(瑞普迪编号)"]);
return(false);
}
sql = string.Format(@" select COUNT(*) from MaterialCustomerProperty where MaterialNumber='{0}' and CustomerId='{1}'", dr["原材料编号(瑞普迪编号)"], dr["客户编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@"insert into MaterialCustomerProperty (MaterialNumber ,CustomerMaterialNumber,CustomerId)
values('{0}','{1}','{2}')", dr["原材料编号(瑞普迪编号)"], dr["客户物料编号"].ToString().Trim(), dr["客户编号"]);
sqls.Add(sql);
}
}
if (!dr["供应商编号"].ToString().Equals(""))
{
sql = string.Format(@"
select COUNT (*) from SupplierInfo where SupplierId ='{0}' ", dr["供应商编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
error = string.Format("系统不存在该供应商编号:{0},原材料编号：{1} ", dr["供应商编号"], dr["原材料编号(瑞普迪编号)"]);
return(false);
}
sql = string.Format(@" select count(*) from MaterialSupplierProperty where MaterialNumber='{0}' and SupplierId ='{1}'", dr["原材料编号(瑞普迪编号)"], dr["供应商编号"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into MaterialSupplierProperty(MaterialNumber ,SupplierId ,SupplierMaterialNumber,MinOrderQty ,Prcie ,DeliveryCycle)
values('{0}','{1}','{2}','{3}',{4},'{5}')",
dr["原材料编号(瑞普迪编号)"], dr["供应商编号"], dr["供应商物料编号"].ToString().Trim()
, dr["供应商最小起订量"].ToString().Trim().Equals("") ? "0" : dr["供应商最小起订量"].ToString(),
dr["供应商单价"].ToString().Trim().Equals("") ? "0" : dr["供应商单价"].ToString(), dr["供应商交货周期"]);
sqls.Add(sql);
}
}
sql = string.Format(@"
select COUNT(*) from MaterialStock where MaterialNumber='{0}'", dr["原材料编号(瑞普迪编号)"]);
if (SqlHelper.GetScalar(sql).Equals("0"))
{
sql = string.Format(@" insert into MaterialStock values('{0}',0,'{1}','ycl')", dr["原材料编号(瑞普迪编号)"], DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqls.Add(sql);
}
if (SqlHelper.BatchExecuteSql(sqls, ref error))
{
return(true);
}
else
{
error += dr["原材料编号(瑞普迪编号)"].ToString();
return(false);
}
}
public static bool AddTwoBom(DataRow dr, ref string error)
{
string version = dr["版本"].ToString().Trim().Equals("") ? "WU" : dr["版本"].ToString().ToUpper();
if (version.Equals("OO"))
{
version = "00";
}
//客户产成品编号（图纸号） 客户物料号 单机用量 单位
string sql = string.Format(@"
select ProductNumber from ProductCustomerProperty where CustomerProductNumber ='{0}' and Version='{1}' ", dr["客户产成品编号（图纸号）"], version);
DataTable dt = SqlHelper.GetTable(sql);
if (dt.Rows.Count == 0)
{
error = string.Format("系统不存在该图纸号：{0},版本：{1}", dr["客户产成品编号（图纸号）"], version);
return(false);
}
string productNumber = dt.Rows[0]["ProductNumber"].ToString();
sql = string.Format(@" select MaterialNumber from MaterialCustomerProperty
where CustomerMaterialNumber ='{0}' ", dr["客户物料号"]);
string materialNumber = SqlHelper.GetScalar(sql);
if (string.IsNullOrEmpty(materialNumber))
{
error = string.Format("系统不存在该客户物料号：{0}。图纸号：{1}", dr["客户物料号"], dr["客户产成品编号（图纸号）"]);
return(false);
}
// sql = string.Format(@"select Kind from MarerialInfoTable where MaterialNumber='{0}'", materialNumber);
string js = "1";
if (dr["单位"].Equals("mm"))
{
js = "CAST( 1000 as decimal(18,5))";
}
List <string> sqls = new List <string>();
sql = string.Format(@"insert BOMInfo(ProductNumber ,Version ,MaterialNumber ,SingleDose ,CustomerMaterialNumber,
Unit,CustomnerProductNumber )
values('{0}','{1}','{2}',cast( {3}/{7} as decimal(18,5)),'{4}','{5}','{6}') ",
productNumber, version, materialNumber,
dr["单机用量"].ToString().Equals("") ? "1" : dr["单机用量"],
dr["客户物料号"], dr["单位"], dr["客户产成品编号（图纸号）"], js);
sqls.Add(sql);
////改变成本价
//sql = new BLL.ToolChangeProduct().changeProductCostPrice(productNumber, version);
//sqls.Add(sql);
// sql = string.Format(@"
//select kind from MarerialInfoTable where materialNumber='{0}' ", materialNumber);
// if (SqlHelper.GetScalar(sql).Equals("线材")) //增加裁线信息
// {
// sql = string.Format(@"
//insert into ProductCuttingLineInfo (ProductNumber ,Version ,MaterialNumber ,Length ,Qty )
//values('{0}','{1}','{2}','{3}',1)", productNumber, version, materialNumber, dr["单机用量"].ToString().Equals("") ? "1" : dr["单机用量"]);
// sqls.Add(sql);
// }
bool result = SqlHelper.BatchExecuteSql(sqls, ref error);
string temp = string.Empty;
if (!result)
{
foreach (string str in sqls)
{
temp += string.Format("<br/>{0}", str);
}
error = string.Format("具体执行命令:{2},图纸号：{0},版本：{1}", dr["客户产成品编号（图纸号）"], version, temp);
}
return(result);
}