/// <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="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);
}