|
|
using ICSSoft.Common;using ICSSoft.Entity;using Newtonsoft.Json;using Newtonsoft.Json.Linq;using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Net.Http;using System.Text;using System.Threading.Tasks;
namespace ICSSoft.DataProject{ /// <summary>
/// 使用中
/// 库内作业模块
/// </summary>
public class ICSWareHouseService { private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
#region 一步调拨
/// <summary>
/// 一步调拨
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void OneStepTransferDocIn(string TransCode, string TransSequence, string WorkPoint, string Quantity, string IsPN, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = string.Empty; if (IsPN == "1") { sql = @"UPDATE ICSTransfer SET TransferQuantity=ISNULL(TransferQuantity,0)+'{2}'
WHERE TransferNO='{0}' AND Sequence='{3}' AND WorkPoint='{1}' AND Type='3'
IF EXISTS(SELECT a.ID FROM ICSTransfer a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' AND a.Type='3' AND a.TransferQuantity<a.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo144") + @"',16,1); RETURN END";
} else { sql = @"UPDATE ICSTransfer SET TransferQuantity=ISNULL(TransferQuantity,0)+'{2}'
WHERE TransferNO='{0}' AND Sequence='{3}' AND WorkPoint='{1}' AND Type='1'
IF EXISTS(SELECT a.ID FROM ICSTransfer a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' AND a.Type='1' AND a.TransferQuantity<a.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo144") + @"',16,1); RETURN END";
}
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo145"));//"调拨单信息更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 晶华一步调拨
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void JHOneStepTransferDocIn(string TransCode, string TransSequence, string WorkPoint, string Quantity, string IsPN, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = string.Empty; sql = @"UPDATE ICSTransfer SET Quantity=TransferQuantity
WHERE TransferNO='{0}' AND WorkPoint='{1}' AND Type='1'";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo145"));//"调拨单信息更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 无源头调拨接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void OneStepNoTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode,string PalletCode) { try { #region 生成调拨单
string sql = @"SELECT a.FromWarehouseCode+a.ToWarehouseCode+a.MUSER AS Costre,'' AS FromDepCode,'' AS ToDepCode,a.FromWarehouseCode AS FromWHCode,a.ToWarehouseCode AS ToWHCode,'' AS TACode,CASE WHEN a.MUSER='2301' or a.MUSER='2305' THEN '039' ELSE a.MUSER END AS MUSER,ROW_NUMBER() OVER (ORDER BY a.FromWarehouseCode,a.ToWarehouseCode,a.InvCode) AS Sequence,
a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(lot.Amount) ELSE '0' END AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,'' AS TAetailID ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' and a.BusinessCode='70' GROUP BY lot.Amount,inv.AmountEnable,a.FromWarehouseCode,a.ToWarehouseCode,a.MUSER,a.InvCode,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,FromDepCode,ToDepCode,FromWHCode,ToWHCode,TACode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock,'TransIn002'as DocType,SYSDATETIME() as DocDate FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,TAetailID,'{1}' AS PalletCode,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,FromWHCode as OutWhCode,ToWHCode as INWhCode FROM #TempERP
DROP TABLE #TempERP";
sql = string.Format(sql, Identification, PalletCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = ""; if (DBHelper.IsU9()) { resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.U9OneStepNoTransferDocInURL, Inputstr); } else { resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepNoTransferDocInURL, Inputstr); }
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; string allcol = jo["FromWHCode"].ToString() + det["ProjectCode"].ToString() + det["BatchCode"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString() + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString(); ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, "", Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["TransCode"].ToString(), det["Sequence"].ToString(), allcol, cmd, language, BusinessCode); } }
//foreach (DataRow dr in ds.Tables)
//{
// ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, "", Identification, "", "", "", "", "", cmd, language);
//}
} catch (Exception ex) { log.Debug(sql); log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } }
/// <summary>
/// 晶华无源头调拨接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void JHOneStepNoTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode,string PalletCode) { try { #region 生成调拨单
string sql = @"SELECT a.MUSER AS Costre,'' AS FromDepCode,'' AS ToDepCode,a.FromWarehouseCode AS FromWHCode,a.ToWarehouseCode AS ToWHCode,'' AS TACode,CASE WHEN a.MUSER='2301' or a.MUSER='2305' THEN '039' ELSE a.MUSER END AS MUSER,
ROW_NUMBER() OVER (ORDER BY a.FromWarehouseCode,a.ToWarehouseCode,a.InvCode) AS Sequence, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(lot.Amount) ELSE '0' END AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,'' AS TAetailID ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint --INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' and a.BusinessCode='70-1' GROUP BY a.workpoint,a.MUSER,a.FromWarehouseCode,a.ToWarehouseCode,a.InvCode,con.Enable,conStock.Enable,inv.AmountEnable,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,FromDepCode,ToDepCode,TACode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock,'TransIn002'as DocType,SYSDATETIME() as DocDate FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,TAetailID,'' AS PalletCode,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,FromWHCode as OutWhCode,ToWHCode as INWhCode FROM #TempERP
DROP TABLE #TempERP";
sql = string.Format(sql, Identification, PalletCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = ""; if (DBHelper.IsU9()) { resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.U9OneStepNoTransferDocInURL, Inputstr); } else { resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.JHOneStepNoTransferDocInURL, Inputstr); }
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); string updatesql = @"UPDATE ICSWareHouseLotInfoLog set TransCode='{0}'
where Identification='{1}'";
updatesql = string.Format(updatesql, res[0]["TransCode"].ToString(), Identification); DBHelper.ExecuteNonQuery(updatesql, cmd); foreach (var item in res) { JObject jo = (JObject)item; //JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString());
//foreach (var detail in resdetail)
//{
// JObject det = (JObject)detail;
// string allcol = jo["FromWHCode"].ToString() + det["ProjectCode"].ToString() + det["BatchCode"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString()
// + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString();
// ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, "", Identification, jo["ID"].ToString(),
// det["DetailID"].ToString(), jo["TransCode"].ToString(), det["Sequence"].ToString(), allcol, cmd, language, BusinessCode);
//}
}
//foreach (DataRow dr in ds.Tables)
//{
// ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, "", Identification, "", "", "", "", "", cmd, language);
//}
} catch (Exception ex) { log.Debug(sql); log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } }
#region 一步调拨(调拨申请单)
/// <summary>
/// 一步调拨(调拨申请单)
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void OneStepTransferApplicationIn(string TransCode, string TransSequence, string WorkPoint, string Quantity, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = ""; if (DBHelper.IsPNU9()) { sql = @"UPDATE ICSTransferApplication SET TransferQuantity=ISNULL(TransferQuantity,0)+'{2}'
WHERE TransferNO='{0}' AND Sequence='{3}' AND WorkPoint='{1}' AND Type='1'";
} else { sql = @"UPDATE ICSTransferApplication SET TransferQuantity=ISNULL(TransferQuantity,0)+'{2}'
WHERE TransferNO='{0}' AND Sequence='{3}' AND WorkPoint='{1}' AND Type='1'
IF EXISTS(SELECT a.ID FROM ICSTransferApplication a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' AND a.Type='1' AND a.TransferQuantity<a.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo144") + @"',16,1); RETURN END";
}
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence);
log.Debug(sql); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo145"));//"调拨单信息更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 一步调拨接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void OneStepTransferDocInERP(string TransType, string Identification, string isPN, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode, string UserCode) { string Inputstr2 = ""; string resultStr = ""; Result result = new Result(); try { #region ERP开立状态单据审核
string sql = ""; //检验调拨单是否一次性发完
if (isPN == "1") {
sql = @"
SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='3' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.TransferQuantity) AS ISSQty,a.transCode from ICSTransfer b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.TransferNO --AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString();
if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT DISTINCT b.TransferID AS ID,'{1}' AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='3' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode, UserCode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); if (isPN == "1") {//派纳调拨
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.PNOneStepTransferDocInURL, Inputstr); //Result result = new Result();
result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dtt = DBHelper.SQlReturnData(sql, cmd); string Inputstrr = JsonConvert.SerializeObject(dtt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstrr); Result resultt = new Result(); resultt = JsonConvert.DeserializeObject<Result>(resultStr); if (resultt.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + resultt.Message); } } // resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
}
} else { if (DBHelper.IsPNU9()) { sql = @"
SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.TransferQuantity) AS ISSQty,a.transCode from ICSTransfer b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.TransferNO --AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString(); if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT DISTINCT b.TransferID AS ID,'{1}' AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='1' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode, UserCode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr); //Result result = new Result();
result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } } else { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); Inputstr2 = Inputstr;
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
// resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
//Result result = new Result();
result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
}
#endregion
} catch (Exception) { //DataTable WHtable = ICSControlModeService.GetJHWHCode();
//if (WHtable == null)
//{
throw; //}
//else
//{
// ICSControlModeService.InsertErrLog(ERPUrl.OneStepTransferDocInURL, Inputstr2, result.Message, JArray.Parse(Inputstr2)[0]["User"]?.ToString(), JArray.Parse(Inputstr2)[0]["WorkPoint"]?.ToString(), cmd);
// // ICSControlModeService.InsertErrLog(ERPUrl.OneStepTransferDocInURL, Inputstr2, result.Message, JObject.Parse(Inputstr2)["UserCode"]?.ToString(), JObject.Parse(Inputstr2)["OrgCode"]?.ToString(), cmd);
//}
} }
public static void JHOneStepTransferDocInERP(string TransType, string Identification, string isPN, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode, string UserCode) { try { #region ERP开立状态单据审核
string resultStr = ""; string sql = ""; //检验调拨单是否一次性发完
if (isPN == "1") {
sql = @"
SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='3' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.TransferQuantity) AS ISSQty,a.transCode from ICSTransfer b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.TransferNO --AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString();
if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT DISTINCT b.TransferID AS ID,'{1}' AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='3' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode, UserCode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); if (isPN == "1") {//派纳调拨
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.PNOneStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dtt = DBHelper.SQlReturnData(sql, cmd); string Inputstrr = JsonConvert.SerializeObject(dtt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstrr); Result resultt = new Result(); resultt = JsonConvert.DeserializeObject<Result>(resultStr); if (resultt.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + resultt.Message); } } // resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
}
} else { if (DBHelper.IsPNU9()) { sql = @"
SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.TransferQuantity) AS ISSQty,a.transCode from ICSTransfer b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.TransferNO --AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString(); if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT DISTINCT b.TransferID AS ID,'{1}' AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='1' and a.EATTRIBUTE1<>'1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode, UserCode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } } else { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt);
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
// resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
}
#endregion
} catch (Exception) { throw; } }
/// <summary>
/// 一步调拨接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void NewOneStepTransferDocInERP(string TransType, string Identification, string isPN, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string resultStr = ""; string sql = ""; //检验调拨单是否一次性发完
if (isPN == "1") { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='3' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt);
if (isPN == "1") {//派纳调拨
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.PNOneStepTransferDocInURL, Inputstr); } else { resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr); } // resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { if (DBHelper.IsPNU9()) { sql = @"
SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.TransferQuantity) AS ISSQty,a.transCode from ICSTransfer b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.TransferNO --AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString(); if (SUMQty - ISSQty == 0) { if (isPN == "2") { string pnsql = @"SELECT DISTINCT b.TransferDetailID AS DocLineID,SUM(a.Quantity) AS TransferQTY,ext.BatchCode AS TransferBatchCode,a.WorkPoint AS OrgCode
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo --AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID --AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferDetailID,ext.BatchCode,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.NewOneStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { string pnsql = @"SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint
FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
";
pnsql = string.Format(pnsql, trancode); DataTable dt = DBHelper.SQlReturnData(pnsql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
} } else { sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt);
resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
// resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OneStepTransferDocInURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
}
#endregion
} catch (Exception) { throw; } } #endregion
#region 调拨
/// <summary>
/// 调拨接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void StepTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
//检验调拨单是否一次性发完
string sql = @"SELECT a.ToWarehouseCode+a.MUSER+a.FromWarehouseCode AS Costre,a.ToWarehouseCode AS ToWHCode,a.FromWarehouseCode AS FromWHCode,a.MUSER,'' AS FromDepCode,'' AS ToDepCode,NULL AS TACode,ROW_NUMBER() OVER (ORDER BY a.ToWarehouseCode) AS Sequence,NULL AS TADetailID,
a.InvCode,SUM(a.Quantity) AS Quantity,0 AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' GROUP BY a.ToWarehouseCode,a.MUSER,a.InvCode,conStock.Enable,con.Enable,a.FromWarehouseCode ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,ToWHCode,FromWHCode,FromDepCode ,ToDepCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,WorkPoint,TACode,UpdateStock FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,TADetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
sql = string.Format(sql, Identification); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.StepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; string allcol = jo["WHCode"].ToString() + det["ProjectCode"].ToString() + det["cBatch"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString() + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString(); ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, det["DNDetailID"].ToString(), Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["RCVTCode"].ToString(), det["Sequence"].ToString(), allcol, cmd, language, BusinessCode); } } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 调拨申请单接口
/// <summary>
/// 调拨申请单接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void StepTransferApplicationERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = ""; //检验调拨单是否一次性发完
if (DBHelper.IsPNU9())//派纳
{ sql = @"SELECT c.TransferDetailID as TADetailID,a.TransCode+a.ToWarehouseCode+a.MUSER+a.FromWarehouseCode AS Costre,a.ToWarehouseCode AS ToWHCode,a.FromWarehouseCode AS FromWHCode,a.MUSER,'' AS FromDepCode,'' AS ToDepCode,a.TransCode AS TACode,ROW_NUMBER() OVER (ORDER BY a.ToWarehouseCode) AS Sequence,
a.InvCode,SUM(a.Quantity) AS Quantity,0 AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,c.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransferApplication c ON a.TransCode=c.TransferNO and a.TransSequence=c.Sequence and a.WorkPoint=c.EATTRIBUTE30 --INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' and a.businesscode ='47' GROUP BY a.ToWarehouseCode,a.MUSER,c.TransferDetailID,a.InvCode,conStock.Enable,con.Enable,a.FromWarehouseCode ,c.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, ''),a.TransCode
SELECT DISTINCT Costre,ToWHCode,FromWHCode,FromDepCode ,ToDepCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,WorkPoint,TACode,UpdateStock FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,ToWHCode AS INWhCode,FromWHCode AS OutWhCode,TADetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
} else { sql = @"SELECT c.TransferDetailID as TADetailID,a.TransCode+a.ToWarehouseCode+a.MUSER+a.FromWarehouseCode AS Costre,a.ToWarehouseCode AS ToWHCode,a.FromWarehouseCode AS FromWHCode,a.MUSER,'' AS FromDepCode,'' AS ToDepCode,a.TransCode AS TACode,ROW_NUMBER() OVER (ORDER BY a.ToWarehouseCode) AS Sequence,
a.InvCode,SUM(a.Quantity) AS Quantity,0 AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransferApplication c ON a.TransCode=c.TransferNO and a.TransSequence=c.Sequence and a.WorkPoint=c.WorkPoint --INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' and a.businesscode ='47' GROUP BY a.ToWarehouseCode,a.MUSER,c.TransferDetailID,a.InvCode,conStock.Enable,con.Enable,a.FromWarehouseCode ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, ''),a.TransCode
SELECT DISTINCT Costre,ToWHCode,FromWHCode,FromDepCode ,ToDepCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,WorkPoint,TACode,UpdateStock FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,TADetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
} sql = string.Format(sql, Identification); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd); log.Debug(sql); log.Debug(ds.ToString()); string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.StepTransferApplicationURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) {
JObject det = (JObject)detail; string allcol = "" + det["ProjectCode"].ToString() + det["cBatch"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString() + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString(); ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, det["DNDetailID"].ToString(), Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["RCVTCode"].ToString(), det["Sequence"].ToString(), allcol, cmd, language, BusinessCode); } } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 两步调出
/// <summary>
/// 两步调出
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void TwoStepTransferDocOut(string TransCode, string TransSequence, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language, string TransID) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSOtherOut a INNER JOIN ICSTransfer b ON a.TransferDetailID=b.TransferDetailID AND a.WorkPoint=b.WorkPoint WHERE a.OutCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}' and a.id='{4}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo146") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo147") + @"',16,1); RETURN END UPDATE a SET OutQuantity=ISNULL(OutQuantity,0)+'{2}' FROM ICSOtherOut a INNER JOIN ICSTransfer b ON a.TransferDetailID=b.TransferDetailID AND a.WorkPoint=b.WorkPoint WHERE a.OutCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}' and a.id='{4}'
IF EXISTS(SELECT a.ID FROM ICSOtherOut a INNER JOIN ICSTransfer b ON a.TransferDetailID=b.TransferDetailID AND a.WorkPoint=b.WorkPoint WHERE a.OutCode='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' and a.id='{4}' AND a.Quantity<a.OutQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence, TransID);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo148"));//"两步调出单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 两步调出接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void TwoStepTransferDocOutERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence,FromWarehouseCode
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode = '22'
IF EXISTS(SELECT b.ID FROM ICSOtherOut b INNER JOIN ICSTransfer c ON b.TransferDetailID=c.TransferDetailID AND b.WorkPoint=c.WorkPoint WHERE b.OutCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.OutQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo098") + @"',16,1); RETURN END SELECT b.OutID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSOtherOut b ON a.TransCode=b.OutCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSTransfer c ON b.TransferDetailID=c.TransferDetailID AND b.WorkPoint=c.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.OutID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.TwoStepTransferDocOutURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 晶华两步调出
/// <summary>
/// 晶华两步调出
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void JHTwoStepTransferDocOut(string TransCode, string TransSequence, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language, string TransID) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSTransfer a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}' and a.id='{4}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo146") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo147") + @"',16,1); RETURN END UPDATE a SET EATTRIBUTE1=ISNULL(EATTRIBUTE1,0)+{2} FROM ICSTransfer a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}' and a.id='{4}'
IF EXISTS(SELECT a.ID FROM ICSTransfer a WHERE a.TransferNO='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' and a.id='{4}' AND a.Quantity<ISNULL(a.EATTRIBUTE1,0)) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence, TransID);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo148"));//"两步调出单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 两步调出接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
/// <summary>
/// 两步调出接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void JHTwoStepTransferDocOutERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '3' AND b.Quantity!=ISNULL(b.EATTRIBUTE1,0)) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='3' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.JHTwoStepTransferDocOutURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 两步调入
/// <summary>
/// 两步调入
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void TwoStepTransferDocIn(string TransCode, string TransSequence, string LotNo, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { //需要关联其他入库单
try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=Otin.Status FROM ICSOtherIn Otin INNER JOIN ICSTransfer tra ON Otin.TransferDetailID=tra.TransferDetailID AND Otin.WorkPoint=tra.WorkPoint INNER JOIN ICSOtherOut out ON out.TransferDetailID=tra.TransferDetailID AND out.WorkPoint=tra.WorkPoint INNER JOIN ICSWareHouseLotInfoLog log ON out.OutCode=log.TransCode AND out.Sequence=log.TransSequence AND out.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND Otin.InCode='{2}' AND Otin.Sequence='{3}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo137") + @"',16,1); RETURN END UPDATE Otin SET InQuantity=ISNULL(InQuantity,0)+'{4}' FROM ICSOtherIn Otin INNER JOIN ICSTransfer tra ON Otin.TransferDetailID=tra.TransferDetailID AND Otin.WorkPoint=tra.WorkPoint INNER JOIN ICSOtherOut out ON out.TransferDetailID=tra.TransferDetailID AND out.WorkPoint=tra.WorkPoint INNER JOIN ICSWareHouseLotInfoLog log ON out.OutCode=log.TransCode AND out.Sequence=log.TransSequence AND out.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND Otin.InCode='{2}' AND Otin.Sequence='{3}' IF EXISTS(SELECT a.LotNo FROM ICSOtherIn Otin INNER JOIN ICSTransfer tra ON Otin.TransferDetailID=tra.TransferDetailID AND Otin.WorkPoint=tra.WorkPoint INNER JOIN ICSOtherOut out ON out.TransferDetailID=tra.TransferDetailID AND out.WorkPoint=tra.WorkPoint INNER JOIN ICSWareHouseLotInfoLog log ON out.OutCode=log.TransCode AND out.Sequence=log.TransSequence AND out.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND Otin.InCode='{2}' AND Otin.Sequence='{3}' AND Otin.Quantity<Otin.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo149") + @"',16,1); END";
sql = string.Format(sql, LotNo, WorkPoint, TransCode, TransSequence, Quantity); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo150"));//"两步调入单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 两步调入接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void TwoStepTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSOtherIn b INNER JOIN ICSTransfer c ON b.TransferDetailID=c.TransferDetailID AND b.WorkPoint=c.WorkPoint WHERE b.InCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo099") + @"',16,1); RETURN END SELECT b.InID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSTransfer c ON b.TransferDetailID=c.TransferDetailID AND b.WorkPoint=c.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.InID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.TwoStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 晶华两步调入
/// <summary>
/// 晶华两步调入
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void JHTwoStepTransferDocIn(string TransCode, string TransSequence, string LotNo, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = @"
UPDATE tra SET TransferQuantity=ISNULL(TransferQuantity,0)+{4} FROM ICSTransfer tra INNER JOIN ICSWareHouseLotInfoLog log ON tra.TransferNO=log.TransCode AND tra.Sequence=log.TransSequence AND tra.ID=log.transID AND tra.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' IF EXISTS(SELECT a.LotNo FROM ICSTransfer tra INNER JOIN ICSWareHouseLotInfoLog log ON tra.TransferNO=log.TransCode AND tra.Sequence=log.TransSequence AND tra.ID=log.transID AND tra.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND tra.Quantity<tra.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo149") + @"',16,1); END";
sql = string.Format(sql, LotNo, WorkPoint, TransCode, TransSequence, Quantity); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo150"));//"两步调入单更新失败！");
}
string checksql = @"UPDATE tra SET TransferQuantity=ISNULL(TransferQuantity,0)+{2}
FROM ICSTransfer tra WHERE tra.TransferNO='{0}' AND tra.Sequence='{1}' IF EXISTS(SELECT a.LotNo FROM ICSTransfer tra INNER JOIN ICSWareHouseLotInfoLog log ON tra.TransferNO=log.TransCode AND tra.Sequence=log.TransSequence AND tra.ID=log.transID AND tra.WorkPoint=log.WorkPoint INNER JOIN ICSWareHouseLotInfo a ON a.LotNo=log.LotNo AND a.WorkPoint=log.WorkPoint WHERE tra.TransferNO='{0}' AND tra.Sequence='{1}' AND tra.Quantity<tra.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo149") + @"',16,1); END";
checksql = string.Format(checksql, TransCode, TransSequence, Quantity); if (!DBHelper.ExecuteNonQuery(checksql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo150"));//"两步调入单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 晶华两步调入接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void JHTwoStepTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { log.Debug("晶华两步调入erp接口开发中;"); #region ERP开立状态单据审核
string sql = @"IF EXISTS(SELECT b.ID FROM ICSTransfer b
WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Type = '1' AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo097") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM ICSWareHouseLotInfoLog a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND b.Type='1' GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.JHTwoStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 锐腾两步调入
/// <summary>
/// 锐腾两步调入
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void RTTwoStepTransferDocIn(string TransCode, string TransSequence, string LotNo, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { //需要关联其他入库单
try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=tra.Status FROM ICSTransfer tra INNER JOIN ICSInventoryLotDetail lotd ON lotd.TransCode=tra.TransferNO AND lotd.TransSequence=tra.Sequence and lotd.WorkPoint=tra.WorkPoint INNER JOIN ICSInventoryLot lot ON lotd.LotNo=lot.LotNo and lotd.WorkPoint=lot.WorkPoint WHERE lot.LotNo='{0}' AND lot.WorkPoint='{1}' AND tra.TransferNO='{2}' and tra.Sequence='{3}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END UPDATE tra SET TransferQuantity=ISNULL(TransferQuantity,0)+'{4}' FROM ICSTransfer tra INNER JOIN ICSInventoryLotDetail lotd ON lotd.TransCode=tra.TransferNO AND lotd.TransSequence=tra.Sequence and lotd.WorkPoint=tra.WorkPoint INNER JOIN ICSInventoryLot lot ON lotd.LotNo=lot.LotNo and lotd.WorkPoint=lot.WorkPoint WHERE lot.LotNo='{0}' AND lot.WorkPoint='{1}' AND tra.TransferNO='{2}' and tra.Sequence='{3}' IF EXISTS(SELECT lot.LotNo FROM ICSTransfer tra INNER JOIN ICSInventoryLotDetail lotd ON lotd.TransCode=tra.TransferNO AND lotd.TransSequence=tra.Sequence and lotd.WorkPoint=tra.WorkPoint INNER JOIN ICSInventoryLot lot ON lotd.LotNo=lot.LotNo and lotd.WorkPoint=lot.WorkPoint WHERE lot.LotNo='{0}' AND lot.WorkPoint='{1}' AND tra.TransferNO='{2}' and tra.Sequence='{3}' AND tra.Quantity<tra.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo149") + @"',16,1); END";
sql = string.Format(sql, LotNo, WorkPoint, TransCode, TransSequence, Quantity); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo150"));//"两步调入单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 两步调入接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void RTTwoStepTransferDocInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSTransfer b WHERE b.TransferNO+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.TransferQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo099") + @"',16,1); RETURN END SELECT b.TransferID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSTransfer b ON a.TransCode=b.TransferNO AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.TransferID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.RTTwoStepTransferDocInURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 其他出库
/// <summary>
/// 其他出库
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void OtherOutDoc(string TransCode, string TransSequence, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language, string TransID) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSOtherOut a WHERE a.OutCode='{0}' AND a.Sequence='{3}' and a.id='{4}' AND a.WorkPoint='{1}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo151") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo152") + @"',16,1); RETURN END UPDATE a SET OutQuantity=ISNULL(OutQuantity,0)+'{2}' FROM ICSOtherOut a WHERE a.OutCode='{0}' AND a.Sequence='{3}' and a.id='{4}' AND a.WorkPoint='{1}'
IF EXISTS(SELECT a.ID FROM ICSOtherOut a WHERE a.OutCode='{0}' AND a.Sequence='{3}' and a.id='{4}' and a.WorkPoint='{1}' AND a.Quantity<a.OutQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence, TransID);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo153"));//"其他出库单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 其他出库接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void OtherOutDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { string sql = string.Empty;
if (DBHelper.IsPNU9()) { sql = @"SELECT a.ToWarehouseCode+b.OutCode+a.MUSER AS Costre
,a.TransCode+a.TransSequence+a.ToWarehouseCode+b.OutCode+a.MUSER AS Costre2 ,a.ToWarehouseCode AS WarehouseCode,b.OutCode,b.Sequence,a.MUSER, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(b.Amount/b.Quantity)) ELSE '0' END AS Amount, b.OutDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,conWhCode.Enable AS ErpWhCode ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherOut b ON a.TransCode=b.OutCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock005' AND a.WorkPoint=conStock.WorkPointINNER JOIN ICSConfiguration conWhCode ON conWhCode.Code='ERPWHCode' AND a.WorkPoint=conWhCode.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND BusinessCode = '24' GROUP BY a.TransCode,a.TransSequence,conWhCode.Enable,inv.AmountEnable,a.ToWarehouseCode,b.OutCode,b.Sequence,a.MUSER,a.InvCode,b.OutDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,OutCode AS DocNo,0 AS ID FROM #TempERP SELECT Costre,Costre2,Sequence,CASE WHEN isnull(ErpWhCode,0)='1' then WarehouseCode ELSE '' END AS WHCode,InvCode,Quantity,Quantity AS ShipQuantity,Amount,OutDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
sql = string.Format(sql, Identification); //DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
//string Inputstr = DataToJsonHelper.DataSetToJson2(ds, "details", "Costre");
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.OutQuantity) AS ISSQty,a.transCode from ICSOtherOut b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.OutCode AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString(); if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT b.OutCode AS Costre
,a.TransCode+a.TransSequence+a.FromWarehouseCode+b.OutCode+a.MUSER AS Costre2 ,a.FromWarehouseCode AS WarehouseCode,b.OutCode,b.Sequence,a.MUSER, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(b.Amount/b.Quantity)) ELSE '0' END AS Amount, b.OutDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,conWhCode.Enable AS ErpWhCode ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherOut b ON a.TransCode=b.OutCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock005' AND a.WorkPoint=conStock.WorkPointINNER JOIN ICSConfiguration conWhCode ON conWhCode.Code='ERPWHCode' AND a.WorkPoint=conWhCode.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND BusinessCode = '24' and a.EATTRIBUTE1<>'1' GROUP BY a.TransCode,a.TransSequence,conWhCode.Enable,inv.AmountEnable,a.FromWarehouseCode,b.OutCode,b.Sequence,a.MUSER,a.InvCode,b.OutDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,OutCode AS DocNo,0 AS ID FROM #TempERP SELECT Costre,Sequence,CASE WHEN isnull(ErpWhCode,0)='1' then WarehouseCode ELSE '' END AS WHCode,InvCode,Quantity,Quantity AS ShipQuantity,Amount,OutDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
pnsql = string.Format(pnsql, trancode); DataSet ds = DBHelper.SQlReturnDataSet(pnsql, cmd); string Inputstr = DataToJsonHelper.DataSetToJson2(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.MiscShipURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; // string ERPupdate = @"UPDATE a set ERPID='{2}',ERPDetailID='{3}',ERPCode='{4}',ERPSequence='{5}',ERPUpload='1',Quantity='{6}'
// FROM ICSWareHouseLotInfoLog a
// INNER JOIN ICSSSD c ON a.TransCode=c.SSDCOde AND a.TransSequence=c.Sequence AND a.WorkPoint=c.WorkPoint
//INNER JOIN ICSInventoryLot lot on a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint
//INNER JOIN ICSExtension d ON lot.ExtensionID=d.id and lot.WorkPoint=d.WorkPoint
// WHERE c.SSDCode='{0}' AND a.BusinessCode ='{7}' AND d.BatchCode='{8}' AND lot.InvCode='{1}'";
// ERPupdate = string.Format(ERPupdate);
// if (!DBHelper.ExecuteNonQuery(ERPupdate, cmd))
// {
// throw new Exception(TransType + language.GetNameByCode("WMSAPIInfo175"));//"回写日志失败！");
// }
} } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); }
}
else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } } else { #region ERP开立状态单据审核
sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence,FromWarehouseCode,transid
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode = '24'
IF EXISTS(SELECT b.ID FROM ICSOtherOut b WHERE b.OutCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.OutQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo100") + @"',16,1); RETURN END SELECT b.OutID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSOtherOut b ON a.TransCode=b.OutCode AND a.TransSequence=b.Sequence and a.transid=b.id AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.OutID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OtherOutDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
}
} catch (Exception) { throw; } }
/// <summary>
/// 创建U9杂发单接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void CreateOtherOutDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region U9创建单据并审核
string sql = @"select LotNo,WorkPoint,InvCode,FromWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode='24'
SELECT a.FromWarehouseCode+b.OutCode+a.MUSER AS Costre,a.FromWarehouseCode AS WarehouseCode,b.OutCode,a.MUSER,ROW_NUMBER() OVER (ORDER BY a.FromWarehouseCode,b.OutCode,a.InvCode) AS Sequence,b.EATTRIBUTE2 as DocType,b.EATTRIBUTE4 as ClientCode,b.EATTRIBUTE7 as Project,b.EATTRIBUTE1 as Dep, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(lot.Amount/lot.Quantity)) ELSE '0' END AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM #NewTempERP a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherOut b ON a.TransCode=b.OutCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock009' AND a.WorkPoint=conStock.WorkPoint GROUP BY inv.AmountEnable,a.FromWarehouseCode,b.OutCode,a.MUSER,a.InvCode,con.Enable,conStock.Enable,b.EATTRIBUTE4,b.EATTRIBUTE2,b.EATTRIBUTE7 ,b.EATTRIBUTE1 ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,'' AS DepCode,WarehouseCode AS WHCode,DocType,ClientCode,OutCode AS SourceCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock FROM #TempERP SELECT WarehouseCode AS WHCode,Costre,Sequence,InvCode,Quantity,Amount,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,Project,Dep FROM #TempERP
DROP TABLE #TempERP DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification, BusinessCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateOtherOutDocURL, Inputstr); //sql = string.Format(sql, Identification);
//DataTable dt = DBHelper.SQlReturnData(sql, cmd);
//string Inputstr = JsonConvert.SerializeObject(dt);
//string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateOtherOutDocURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { //foreach (DataRow dr in dt.Rows)
//{
// ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode);
//}
} catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } }
public static void CreateOtherOutDocNoSourceERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region U9创建无源头单据并审核
string sql = @"select LotNo,WorkPoint,InvCode,FromWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode='{1}'
SELECT a.FromWarehouseCode+a.MUSER AS Costre,a.FromWarehouseCode AS WarehouseCode,a.MUSER,ROW_NUMBER() OVER (ORDER BY a.FromWarehouseCode,a.InvCode) AS Sequence, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(lot.Amount/lot.Quantity)) ELSE '0' END AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM #NewTempERP a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock009' AND a.WorkPoint=conStock.WorkPoint GROUP BY inv.AmountEnable,a.FromWarehouseCode,a.MUSER,a.InvCode,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,'' AS DepCode,WarehouseCode AS WHCode,'MiscShip004' AS DocType,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock FROM #TempERP SELECT WarehouseCode AS WHCode,Costre,Sequence,InvCode,Quantity,Amount,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,'10403' AS Dep FROM #TempERP
DROP TABLE #TempERP DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification, BusinessCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateOtherOutDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { //foreach (DataRow dr in dt.Rows)
//{
// ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode);
//}
} catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } }
#endregion
#region 其他入库
/// <summary>
/// 其他入库
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void OtherInDoc(string LotNo, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=c.Status FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND isnull(c.Type,'1')='1'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo137") + @"',16,1); RETURN END UPDATE c SET InQuantity=ISNULL(InQuantity,0)+'{2}' FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND isnull(c.Type,'1')='1' IF EXISTS(SELECT a.LotNo FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND isnull(c.Type,'1')='1' AND dbo.GetExcessInQty(c.InvCode, c.WorkPoint, 'OverIn021', c.Quantity,1,4,'')<c.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo154") + @"',16,1); END";
if (DBHelper.IsU9()) { sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=c.Status FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo137") + @"',16,1); RETURN END UPDATE c SET InQuantity=ISNULL(InQuantity,0)+'{2}' FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}'-- AND c.EATTRIBUTE2='1' IF EXISTS(SELECT a.LotNo FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSOtherIn c ON b.TransCode=c.InCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND c.EATTRIBUTE2='1' AND dbo.GetExcessInQty(c.InvCode, c.WorkPoint, 'OverIn021', c.Quantity,1,4,'')<c.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo154") + @"',16,1); END"; }
sql = string.Format(sql, LotNo, WorkPoint, Quantity); log.Debug("回写单据数量前" + sql);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo155"));//"其他入库单更新失败！");
} log.Debug("回写单据数量后"); } catch (Exception) { throw; } }
public static void OtherInRedDoc(string TransCode, string TransSequence, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language, string TransID) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSOtherIn a WHERE a.InCode='{0}' AND a.Sequence='{3}' and a.id='{4}' AND a.WorkPoint='{1}' and a.type='2'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo151") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo152") + @"',16,1); RETURN END UPDATE a SET InQuantity=ISNULL(InQuantity,0)+'{2}' FROM ICSOtherIn a WHERE a.InCode='{0}' AND a.Sequence='{3}' and a.id='{4}' AND a.WorkPoint='{1}' and a.type='2'
IF EXISTS(SELECT a.ID FROM ICSOtherIn a WHERE a.InCode='{0}' AND a.Sequence='{3}' and a.id='{4}' and a.WorkPoint='{1}' and a.type='2' AND a.Quantity<a.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence, TransID);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("红字其他入库单更新失败！");//"红字其他入库单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 其他入库接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void OtherInDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { string sql = string.Empty; log.Debug("ERP开立状态单据审核"); if (DBHelper.IsPNU9()) { sql = @"SELECT a.ToWarehouseCode+b.InCode+a.MUSER AS Costre
,a.TransCode+a.TransSequence+a.ToWarehouseCode+b.InCode+a.MUSER AS Costre2 ,a.ToWarehouseCode AS WarehouseCode,b.InCode,b.Sequence,a.MUSER, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(b.Amount/b.Quantity)) ELSE '0' END AS Amount, b.InDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,conWhCode.Enable AS ErpWhCode ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock005' AND a.WorkPoint=conStock.WorkPointINNER JOIN ICSConfiguration conWhCode ON conWhCode.Code='ERPWHCode' AND a.WorkPoint=conWhCode.WorkPoint WHERE a.Identification='{0}' AND ERPUpload='0' AND BusinessCode = '25' GROUP BY a.TransCode,a.TransSequence,conWhCode.Enable,inv.AmountEnable,a.ToWarehouseCode,b.InCode,b.Sequence,a.MUSER,a.InvCode,b.InDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,InCode AS DocNo,0 AS ID FROM #TempERP SELECT Costre,Costre2,Sequence,CASE WHEN isnull(ErpWhCode,0)='1' then WarehouseCode ELSE '' END AS WHCode,InvCode,Quantity,Quantity AS RcvQuantity,Amount,InDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
sql = string.Format(sql, Identification); //DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
//string Inputstr = DataToJsonHelper.DataSetToJson2(ds, "details", "Costre");
string checksql = @"select SUM(b.Quantity) AS SUMQty,SUM(b.InQuantity) AS ISSQty,a.transCode from ICSOtherIn b inner join ICSWareHouseLotInfoLog a ON a.TransCode=b.InCode AND a.WorkPoint=b.WorkPoint
where a.Identification='{0}' GROUP BY a.transCode";
checksql = string.Format(checksql, Identification); DataTable chekdt = DBHelper.SQlReturnData(checksql, cmd); decimal SUMQty = Convert.ToDecimal(chekdt.Rows[0]["SUMQty"]); decimal ISSQty = Convert.ToDecimal(chekdt.Rows[0]["ISSQty"]); string trancode = chekdt.Rows[0]["transCode"].ToString(); if (SUMQty - ISSQty == 0) { string pnsql = @"SELECT a.ToWarehouseCode+b.InCode+a.MUSER AS Costre
,a.TransCode+a.TransSequence+a.ToWarehouseCode+b.InCode+a.MUSER AS Costre2 ,a.ToWarehouseCode AS WarehouseCode,b.InCode,b.Sequence,a.MUSER, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(b.Amount/b.Quantity)) ELSE '0' END AS Amount, b.InDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,conWhCode.Enable AS ErpWhCode ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM ICSWareHouseLotInfoLog a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.FromWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock005' AND a.WorkPoint=conStock.WorkPointINNER JOIN ICSConfiguration conWhCode ON conWhCode.Code='ERPWHCode' AND a.WorkPoint=conWhCode.WorkPoint WHERE a.TransCode='{0}' AND ERPUpload='0' AND BusinessCode = '25' and a.EATTRIBUTE1<>'1' GROUP BY a.TransCode,a.TransSequence,conWhCode.Enable,inv.AmountEnable,a.ToWarehouseCode,b.InCode,b.Sequence,a.MUSER,a.InvCode,b.InDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,InCode AS DocNo,0 AS ID FROM #TempERP SELECT Costre,Costre2,Sequence,CASE WHEN isnull(ErpWhCode,0)='1' then WarehouseCode ELSE '' END AS WHCode,InvCode,Quantity,Quantity AS RcvQuantity,Amount,InDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP";
pnsql = string.Format(pnsql, trancode); DataSet ds = DBHelper.SQlReturnDataSet(pnsql, cmd); string Inputstr = DataToJsonHelper.DataSetToJson2(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.MiscRcvURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; // string ERPupdate = @"UPDATE a set ERPID='{2}',ERPDetailID='{3}',ERPCode='{4}',ERPSequence='{5}',ERPUpload='1',Quantity='{6}'
// FROM ICSWareHouseLotInfoLog a
// INNER JOIN ICSSSD c ON a.TransCode=c.SSDCOde AND a.TransSequence=c.Sequence AND a.WorkPoint=c.WorkPoint
//INNER JOIN ICSInventoryLot lot on a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint
//INNER JOIN ICSExtension d ON lot.ExtensionID=d.id and lot.WorkPoint=d.WorkPoint
// WHERE c.SSDCode='{0}' AND a.BusinessCode ='{7}' AND d.BatchCode='{8}' AND lot.InvCode='{1}'";
// ERPupdate = string.Format(ERPupdate);
// if (!DBHelper.ExecuteNonQuery(ERPupdate, cmd))
// {
// throw new Exception(TransType + language.GetNameByCode("WMSAPIInfo175"));//"回写日志失败！");
// }
} } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); }
} else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
} else { #region ERP开立状态单据审核
sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSOtherIn b WHERE b.InCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=ISNULL(b.InQuantity,0)) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo101") + @"',16,1); RETURN END SELECT b.InID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint AND isnull(b.Type,'1')='1' INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.InID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd); string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OtherInDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
}
} catch (Exception) { throw; } }
public static void OtherInRedDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSOtherIn b WHERE b.InCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.InQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo101") + @"',16,1); RETURN END SELECT b.InID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint AND b.Type='2' INNER JOIN ICSConfiguration con ON con.Code='Stock001' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock003' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.InID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.OtherInDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } }
/// <summary>
/// 创建U9杂收单接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void CreateOtherInDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode='25'
SELECT a.ToWarehouseCode+b.InCode+a.MUSER AS Costre,a.ToWarehouseCode AS WarehouseCode,b.InCode,a.MUSER,ROW_NUMBER() OVER (ORDER BY a.ToWarehouseCode,b.InCode,a.InvCode) AS Sequence,b.EATTRIBUTE2 as DocType,b.EATTRIBUTE4 as ClientCode, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(lot.Amount/lot.Quantity)) ELSE '0' END AS Amount,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 ,b.EATTRIBUTE7 as Project,b.EATTRIBUTE1 as Dep INTO #TempERP FROM #NewTempERP a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSOtherIn b ON a.TransCode=b.InCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock009' AND a.WorkPoint=conStock.WorkPoint GROUP BY inv.AmountEnable,a.ToWarehouseCode,b.InCode,a.MUSER,a.InvCode,con.Enable,conStock.Enable,b.EATTRIBUTE4,b.EATTRIBUTE2 ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, ''),b.EATTRIBUTE7,b.EATTRIBUTE1
SELECT DISTINCT Costre,WorkPoint,'' AS DepCode,DocType,ClientCode,InCode AS SourceCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock FROM #TempERP SELECT WarehouseCode AS WHCode,Costre,Sequence,InvCode,Quantity,Amount,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,Project,Dep FROM #TempERP
DROP TABLE #TempERP DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification, BusinessCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateOtherInDocURL, Inputstr);
//sql = string.Format(sql, Identification);
//DataTable dt = DBHelper.SQlReturnData(sql, cmd);
//string Inputstr = JsonConvert.SerializeObject(dt);
//string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateOtherInDocURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { //foreach (DataRow dr in dt.Rows)
//{
//ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode);
//}
} catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 拆卸单
/// <summary>
/// 拆卸单
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void DisassemblyDoc(string LotNo, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { log.Debug("拆卸111"); string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=c.Status FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSDisassemblyDoc c ON b.TransCode=c.DABDOCCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND c.DABDOCType='2'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END ELSE IF (@Status!='1') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo137") + @"',16,1); RETURN END UPDATE c SET DABDOCQuantity=ISNULL(DABDOCQuantity,0)+'{2}' FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSDisassemblyDoc c ON b.TransCode=c.DABDOCCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' IF EXISTS(SELECT a.LotNo FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSDisassemblyDoc c ON b.TransCode=c.DABDOCCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND dbo.GetExcessInQty(c.InvCode, c.WorkPoint, 'OverIn024', c.Quantity,1,4,'')<c.DABDOCQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo156") + @"',16,1); END";
sql = string.Format(sql, LotNo, WorkPoint, Quantity);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo157"));//"拆卸单更新失败！");
} log.Debug("拆卸222"); } catch (Exception) { throw; } }
/// <summary>
/// 拆卸单更新套件锁定数量
/// </summary>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void DisassemblyDoc(string Identification, SqlCommand cmd, Dictionary<string, string> language) { try { log.Debug("拆卸单sql前"); string sql = @"select * into #table2 from ICSWareHouseLotInfoLog where transcode in(
select transcode from ICSWareHouseLotInfoLog where Identification='{0}')
UPDATE ICSDisassemblyDoc SET DABDOCQuantity=Quantity WHERE DABDOCCode+Sequence+WorkPoint IN(SELECT DISTINCT d.TransCode+d.TransSequence+d.WorkPoint FROM #table2 a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSDisassemblyDoc c ON b.TransCode=c.DABDOCCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint INNER JOIN #table2 d ON b.TransCode=d.TransCode AND d.TransSequence='1' AND b.WorkPoint=d.WorkPoint WHERE a.Identification='{0}' AND d.ERPUpload='0' AND d.Lock='1')
UPDATE ICSWareHouseLotInfo SET Quantity=Quantity-LockQuantity,LockQuantity=0 WHERE LotNo+WorkPoint IN(SELECT DISTINCT d.LotNo+d.WorkPoint FROM #table2 a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSDisassemblyDoc c ON b.TransCode=c.DABDOCCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint INNER JOIN #table2 d ON b.TransCode=d.TransCode AND d.TransSequence='1' AND b.WorkPoint=d.WorkPoint WHERE a.Identification='{0}' AND d.ERPUpload='0' AND d.Lock='1')
DROP TABLE #table2";
sql = string.Format(sql, Identification);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo158"));//"拆卸单套件更新失败！");
} log.Debug("拆卸单sql后"); } catch (Exception) { throw; } }
/// <summary>
/// 拆卸单接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void DisassemblyDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP开立状态单据审核
string sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSDisassemblyDoc b WHERE b.DABDOCCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=b.DABDOCQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo102") + @"',16,1); RETURN END SELECT b.DABDOCID AS ID,a.MUSER AS [User],SYSDATETIME() AS MTime,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSDisassemblyDoc b ON a.TransCode=b.DABDOCCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock003' AND a.WorkPoint=con.WorkPoint INNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock006' AND a.WorkPoint=conStock.WorkPoint GROUP BY b.DABDOCID,a.MUSER,con.Enable,conStock.Enable,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd);
string Inputstr = JsonConvert.SerializeObject(dt); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.DisassemblyDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode); } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } #endregion
#region 借用
/// <summary>
/// 借用
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void BrrowDoc(string TransCode, string TransSequence, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSBrrow a WHERE a.BrrowCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo159") + @"',16,1); RETURN END ELSE IF (@Status!='2') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo160") + @"',16,1); RETURN END UPDATE a SET BrrowQuantity=ISNULL(BrrowQuantity,0)+'{2}' FROM ICSBrrow a WHERE a.BrrowCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}'
IF EXISTS(SELECT a.ID FROM ICSBrrow a WHERE a.BrrowCode='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' AND a.Quantity<a.BrrowQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo161"));//"借用单更新失败！");
} } catch (Exception) { throw; } }
/// <summary>
/// 借用接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void BrrowDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP
string sql = string.Empty; if (!DBHelper.IsBBU9()) { sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence,FromWarehouseCode
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode = '26'
SELECT a.FromWarehouseCode+b.BrrowCode+a.MUSER AS Costre,a.FromWarehouseCode AS WarehouseCode,b.BrrowCode,a.MUSER,ROW_NUMBER() OVER (ORDER BY a.FromWarehouseCode,b.BrrowCode,b.BrrowDetailID,a.InvCode) AS Sequence, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(b.Amount/b.Quantity)) ELSE '0' END AS Amount,b.BrrowDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock,conWhCode.Enable AS ErpWhCode ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM #NewTempERP a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSBrrow b ON a.TransCode=b.BrrowCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock008' AND a.WorkPoint=conStock.WorkPointINNER JOIN ICSConfiguration conWhCode ON conWhCode.Code='ERPWHCode' AND a.WorkPoint=conWhCode.WorkPoint GROUP BY conWhCode.Enable,inv.AmountEnable,a.FromWarehouseCode,b.BrrowCode,a.MUSER,a.InvCode,b.BrrowDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,'' AS DepCode,WarehouseCode AS WHCode,'借用单' AS SourceType,BrrowCode AS SourceCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,CASE WHEN isnull(ErpWhCode,0)='1' then WarehouseCode ELSE '' END AS WHCode,BrrowDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.BrrowDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; string allcol = jo["WHCode"].ToString() + det["ProjectCode"].ToString() + det["cBatch"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString() + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString(); ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, det["SourceDetailID"].ToString(), Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["OtherOutCode"].ToString(), det["Sequence"].ToString(), allcol, cmd, language, BusinessCode); } } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSBrrow b WHERE b.BrrowCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=ISNULL(b.BrrowQuantity,0)) BEGIN RAISERROR('借出单还有条码未扫入，请确认！',16,1); RETURN END SELECT b.BrrowCode AS DocNO,a.MUSER AS [User],SYSDATETIME() AS MTime,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSBrrow b ON a.TransCode=b.BrrowCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint GROUP BY b.BrrowCode,a.MUSER,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd); string Inputstr = JsonConvert.SerializeObject(dt); Inputstr = Inputstr.Replace("[", ""); Inputstr = Inputstr.Replace("]", ""); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.BrrowDocApproveURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { //ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode);
} } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
#endregion
} catch (Exception) { throw; } } #endregion
#region 归还
/// <summary>
/// 归还
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void ReturnDoc(string TransCode, string TransSequence, string LotNo, string LogID, string Quantity, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = string.Empty; if (string.IsNullOrEmpty(LogID)) { sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=c.Status FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSReturn c ON b.TransCode=c.ReturnCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo081") + @"',16,1); RETURN END ELSE IF (@Status!='2') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo142") + @"',16,1); RETURN END UPDATE c SET ReturnQuantity=ISNULL(ReturnQuantity,0)+'{2}' FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSReturn c ON b.TransCode=c.ReturnCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' IF EXISTS(SELECT a.LotNo FROM ICSInventoryLot a INNER JOIN ICSInventoryLotDetail b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint INNER JOIN ICSReturn c ON b.TransCode=c.ReturnCode AND b.TransSequence=c.Sequence AND b.WorkPoint=c.WorkPoint WHERE a.LotNo='{0}' AND a.WorkPoint='{1}' AND dbo.GetExcessInQty(c.InvCode, c.WorkPoint, 'OverIn023', c.Quantity,1,4,'')<c.ReturnQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo162") + @"',16,1); END";
sql = string.Format(sql, LotNo, WorkPoint, Quantity); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo163"));//"归还单更新失败！");
} } else { sql = @"DECLARE @Status VARCHAR(10)
SELECT @Status=a.Status FROM ICSReturn a WHERE a.ReturnCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}'
IF (@Status IS NULL) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo151") + @"',16,1); RETURN END ELSE IF (@Status!='2') BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo152") + @"',16,1); RETURN END UPDATE a SET ReturnQuantity=ISNULL(ReturnQuantity,0)+'{2}' FROM ICSReturn a WHERE a.ReturnCode='{0}' AND a.Sequence='{3}' AND a.WorkPoint='{1}'
IF EXISTS(SELECT a.ID FROM ICSReturn a WHERE a.ReturnCode='{0}' AND a.Sequence='{3}' and a.WorkPoint='{1}' AND a.Quantity<a.ReturnQuantity) BEGIN RAISERROR('" + language.GetNameByCode("WMSAPIInfo091") + @"',16,1); RETURN END";
sql = string.Format(sql, TransCode, WorkPoint, Quantity, TransSequence); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo163"));//"归还单更新失败！");
} }
} catch (Exception) { throw; } }
/// <summary>
/// 归还接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void ReturnDocERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language, string BusinessCode) { try { #region ERP
string sql = string.Empty; if (!DBHelper.IsBBU9()) { sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLogWHERE Identification='{0}' AND ERPUpload='0' AND BusinessCode='{1}'
SELECT a.ToWarehouseCode+b.ReturnCode+a.MUSER AS Costre,a.ToWarehouseCode AS WarehouseCode,b.ReturnCode,a.MUSER,ROW_NUMBER() OVER (ORDER BY a.ToWarehouseCode,b.ReturnCode,b.ReturnDetailID,a.InvCode) AS Sequence, a.InvCode,SUM(a.Quantity) AS Quantity,CASE inv.AmountEnable WHEN '1' THEN SUM(a.Quantity*(lot.Amount/lot.Quantity)) ELSE '0' END AS Amount,b.ReturnDetailID,con.Enable AS UpdateTodoQuantity,conStock.Enable AS UpdateStock ,a.WorkPoint,ISNULL(ext.ProjectCode, '') AS ProjectCode,CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END AS BatchCode,ISNULL(ext.Version, '') AS Version,ISNULL(ext.Brand, '') AS Brand, ISNULL(ext.cFree1, '') AS cFree1,ISNULL(ext.cFree2, '') AS cFree2,ISNULL(ext.cFree3, '') AS cFree3,ISNULL(ext.cFree4, '') AS cFree4,ISNULL(ext.cFree5, '') AS cFree5,ISNULL(ext.cFree6, '') AS cFree6,ISNULL(ext.cFree7, '') AS cFree7,ISNULL(ext.cFree8, '') AS cFree8,ISNULL(ext.cFree9, '') AS cFree9,ISNULL(ext.cFree10, '') AS cFree10 INTO #TempERP FROM #NewTempERP a INNER JOIN ICSInventoryLot lot ON a.LotNo=lot.LotNo AND a.WorkPoint=lot.WorkPoint INNER JOIN ICSExtension ext ON lot.ExtensionID=ext.ID AND lot.WorkPoint=ext.WorkPoint INNER JOIN ICSInventory inv ON a.InvCode=inv.InvCode AND a.WorkPoint=inv.WorkPoint LEFT JOIN ICSInventoryBatchEnable invBat ON a.InvCode=invBat.InvCode AND a.ToWarehouseCode=invBat.WHCode AND a.WorkPoint=invBat.WorkPoint INNER JOIN ICSReturn b ON a.TransCode=b.ReturnCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint INNER JOIN ICSConfiguration con ON con.Code='Stock002' AND a.WorkPoint=con.WorkPointINNER JOIN ICSConfiguration conStock ON conStock.Code='UpdateStock009' AND a.WorkPoint=conStock.WorkPoint GROUP BY inv.AmountEnable,a.ToWarehouseCode,b.ReturnCode,a.MUSER,a.InvCode,b.ReturnDetailID,con.Enable,conStock.Enable ,a.WorkPoint,ISNULL(ext.ProjectCode, ''),CASE WHEN (invBat.BatchEnable IS NULL AND ISNULL(inv.BatchEnable, '0')='1') OR ISNULL(invBat.BatchEnable, '0')='1' THEN ISNULL(ext.BatchCode, '') ELSE '' END,ISNULL(ext.Version, ''),ISNULL(ext.Brand, ''), ISNULL(ext.cFree1, ''),ISNULL(ext.cFree2, ''),ISNULL(ext.cFree3, ''),ISNULL(ext.cFree4, ''),ISNULL(ext.cFree5, ''),ISNULL(ext.cFree6, ''),ISNULL(ext.cFree7, ''),ISNULL(ext.cFree8, ''),ISNULL(ext.cFree9, ''),ISNULL(ext.cFree10, '')
SELECT DISTINCT Costre,WorkPoint,'' AS DepCode,WarehouseCode AS WHCode,'归还单' AS SourceType,ReturnCode AS SourceCode,MUSER AS [User],SYSDATETIME() AS MTime,UpdateTodoQuantity,UpdateStock FROM #TempERP SELECT Costre,Sequence,InvCode,Quantity,Amount,ReturnDetailID AS SourceDetailID,ProjectCode,BatchCode,Version,Brand,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10 FROM #TempERP
DROP TABLE #TempERP DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification, BusinessCode); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre");
string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.ReturnDocURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { JArray res = (JArray)JsonConvert.DeserializeObject(result.Data.ToString()); foreach (var item in res) { JObject jo = (JObject)item; JArray resdetail = (JArray)JsonConvert.DeserializeObject(jo["details"].ToString()); foreach (var detail in resdetail) { JObject det = (JObject)detail; string allcol = jo["WHCode"].ToString() + det["ProjectCode"].ToString() + det["cBatch"].ToString() + det["version"].ToString() + det["brand"].ToString() + det["cFree1"].ToString() + det["cFree2"].ToString() + det["cFree3"].ToString() + det["cFree4"].ToString() + det["cFree5"].ToString() + det["cFree6"].ToString() + det["cFree7"].ToString() + det["cFree8"].ToString() + det["cFree9"].ToString() + det["cFree10"].ToString(); ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, det["SourceDetailID"].ToString(), Identification, jo["ID"].ToString(), det["DetailID"].ToString(), jo["OtherInCode"].ToString(), det["Sequence"].ToString(), "", cmd, language, BusinessCode); } } } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } } else { sql = @"select LotNo,WorkPoint,InvCode,ToWarehouseCode,MUSER,Quantity,TransCode,TransSequence
INTO #NewTempERPfrom ICSWareHouseLotInfoLog WHERE Identification='{0}' AND ERPUpload='0'
IF EXISTS(SELECT b.ID FROM ICSReturn b WHERE b.ReturnCode+b.WorkPoint IN (SELECT a.TransCode+a.WorkPoint FROM ICSWareHouseLotInfoLog a WHERE a.Identification='{0}') AND b.Quantity!=ISNULL(b.ReturnQuantity,0)) BEGIN RAISERROR('还入单还有条码未扫入，请确认！',16,1); RETURN END SELECT b.ReturnCode AS DocNO,a.MUSER AS [User],SYSDATETIME() AS MTime,a.WorkPoint FROM #NewTempERP a INNER JOIN ICSReturn b ON a.TransCode=b.ReturnCode AND a.TransSequence=b.Sequence AND a.WorkPoint=b.WorkPoint GROUP BY b.ReturnCode,a.MUSER,a.WorkPoint
DROP TABLE #NewTempERP";
sql = string.Format(sql, Identification); DataTable dt = DBHelper.SQlReturnData(sql, cmd); string Inputstr = JsonConvert.SerializeObject(dt); Inputstr = Inputstr.Replace("[", ""); Inputstr = Inputstr.Replace("]", ""); string resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.ReturnDocApproveURL, Inputstr); Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (result.Success) { try { foreach (DataRow dr in dt.Rows) { //ICSWareHouseLotInfoService.WareHouseLotInfoLogUpdate(TransType, dr["ID"].ToString(), Identification, "", "", "", "", "", cmd, language, BusinessCode);
} } catch (Exception ex) { log.Debug(ex.ToString()); log.Debug(resultStr); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } }
#endregion
} catch (Exception) { throw; } } #endregion
#endregion
#region 工单关联
/// <summary>
/// 工单关联
/// </summary>
/// <param name="TransCode"></param>
/// <param name="TransSequence"></param>
/// <param name="Quantity"></param>
/// <param name="WorkPoint"></param>
/// <param name="cmd"></param>
public static void ICSMOReceive(string LotNo, string Quantity, string TransCode, string TransSequence, string WorkPoint, string Identification, string User, string SRMLotGroup, string ProjectCode, string BatchCode, string Version, string Brand, string cFree1, string cFree2, string cFree3, string cFree4, string cFree5, string cFree6, string cFree7, string cFree8, string cFree9, string cFree10, string BusinessCode, SqlCommand cmd, Dictionary<string, string> language) { try { decimal LOTQTY = 0; decimal MOQTY = 0; String EffectiveEnable = ""; String Colspan = ""; String IDD = ""; int EffectiveDays = 0; String Time = ""; int EffectiveDayss = 0; DateTime dtt; DateTime now = DateTime.Now; string sql = @"select * from ICSInventoryLotDetail
where LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint); DataTable dttte = DBHelper.SQlReturnData(sql, cmd); if (dttte.Rows.Count > 0) { sql = @"IF NOT EXISTS(select * from ICSMO b
where b.MOCODE='{2}' and b.WorkPoint='{1}' and b.EATTRIBUTE1='1') BEGIN select * from ICSInventoryLotDetail a left join ICSMO b ON a.transcode=b.mocode and a.WorkPoint=b.WorkPoint where a.LotNo='{0}' and a.WorkPoint='{1}' and a.TransCode = '' END ELSE BEGIN select * from ICSInventoryLotDetail where LotNo='{0}' and WorkPoint='{1}' END";
sql = string.Format(sql, LotNo, WorkPoint, TransCode); DataTable tt = DBHelper.SQlReturnData(sql, cmd); if (tt.Rows.Count > 0) { ///修改条码关联表
sql = @"update ICSInventoryLotDetail set TransCode='{0}',TransSequence='{1}'
where lotno='{3}' and WorkPoint='{2}' ";
sql = string.Format(sql, TransCode, TransSequence, WorkPoint, LotNo);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo367")); } ///修改条码表
sql = @"update ICSInventoryLot set InvCode=(select a.InvCode from ICSMO a where a.MOCode='{0}' and a.Sequence='{1}')
where lotno='{3}' and WorkPoint='{2}' ";
sql = string.Format(sql, TransCode, TransSequence, WorkPoint, LotNo);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo367")); } } else { throw new Exception(language.GetNameByCode("WMSAPIInfo374")); }
} else { sql = @"select EffectiveEnable,EffectiveDays from ICSInventory a
Left Join ICSMO b ON a.InvCode=b.InvCode and a.WorkPoint = b.WorkPoint where b.MoCode='{0}' and b.WorkPoint='{1}'";
sql = string.Format(sql, TransCode, WorkPoint); DataTable dt = DBHelper.SQlReturnData(sql, cmd); if (dt.Rows.Count == 0) { throw new Exception(language.GetNameByCode("WMSAPIInfo366")); } else { EffectiveEnable = dt.Rows[0]["EffectiveEnable"].ToString(); EffectiveDays = Convert.ToInt32(dt.Rows[0]["EffectiveDays"]); } //EffectiveDayss = Convert.ToInt32(EffectiveDays);
if (!EffectiveEnable.Equals("False")) { Time = DBHelper.ReTime(now, EffectiveDays); } else { Time = "2999-12-31 00:00:00.000"; } dtt = Convert.ToDateTime(Time);
sql = @"DECLARE @aa VARCHAR(10)
DECLARE @bb VARCHAR(10) SELECT @aa=a.EATTRIBUTE1 FROM ICSMO a WHERE a.MOCode='{0}' and a.WorkPoint='{1}'
print @aa IF(@aa='1') BEGIN select Count(c.lotno) AS LOTQTY,a.Quantity+ISNULL((select SUM(b.Quantity) from ICSMO a left join ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint where a.MOCode='{0}' and a.EATTRIBUTE1='1' and b.EATTRIBUTE1='1' and a.WorkPoint='{1}'),0) AS MOQTY from ICSMO a left join ICSInventoryLotDetail bb on a.MOCode=bb.TransCode and a.WorkPoint=bb.WorkPoint left join ICSInventoryLot c on bb.LotNo=c.LotNo and c.WorkPoint=bb.WorkPoint where a.MOCode='{0}' and a.WorkPoint='{1}' group by a.Quantity END ELSE BEGIN select Count(bb.lotno) AS LOTQTY,a.Quantity AS MOQTY from ICSMO a left join ICSInventoryLotDetail bb on a.MOCode=bb.TransCode and a.Sequence=bb.transSequence and a.WorkPoint=bb.WorkPoint left join ICSInventoryLot c on bb.LotNo=c.LotNo and c.WorkPoint=bb.WorkPoint where a.MOCode='{0}' and a.WorkPoint='{1}' group by a.Quantity END
";
sql = string.Format(sql, TransCode, WorkPoint); DataTable dtttt = DBHelper.SQlReturnData(sql, cmd); LOTQTY = Convert.ToDecimal(dtttt.Rows[0]["LOTQTY"]); MOQTY = Convert.ToDecimal(dtttt.Rows[0]["MOQTY"]);
sql = @"select * from ICSInventoryLot
where LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint); DataTable dttaa = DBHelper.SQlReturnData(sql, cmd);
if (LOTQTY < MOQTY) {
//检验自由项
Colspan = ProjectCode + "~" + BatchCode + "~" + Version + "~" + Brand + "~" + cFree1 + "~" + cFree2 + "~" + cFree3 + "~" + cFree4 + "~" + cFree5 + "~" + cFree6 + "~" + cFree7 + "~" + cFree8 + "~" + cFree9 + "~" + cFree10; sql = @"select ID,Colspan from ICSExtension a
where Colspan='{0}' and WorkPoint='{1}'";
sql = string.Format(sql, Colspan, WorkPoint); DataTable dttt = DBHelper.SQlReturnData(sql, cmd); if (dttt.Rows.Count == 0) { IDD = Guid.NewGuid().ToString(); sql = @"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
select '{17}','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',GETDATE(),'{15}',f.F_RealName,'{16}'from Sys_SRM_User f where f.F_Account='{15}' and f.F_Location='{16}'";
sql = string.Format(sql, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, User, WorkPoint, IDD); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo366")); } } else { IDD = dttt.Rows[0]["ID"].ToString(); }
if (dttaa.Rows.Count == 0) {
//存入条码表
sql = @"IF NOT EXISTS(SELECT F_Account FROM Sys_SRM_User WHERE F_Account='{2}' AND F_Location='{4}')
BEGIN RAISERROR('" + string.Format(language.GetNameByCode("WMSAPIInfo060"), "{2}") + @"',16,1); RETURN END
INSERT INTO ICSInventoryLot(ID,LotNo,InvCode,ProductDate,ExpirationDate, Quantity,Amount,ExtensionID,Type,PrintTimes, LastPrintUser,LastPrintTime,MUSER,MUSERName,MTIME, WorkPoint,EATTRIBUTE1) SELECT TOP 1 NEWID(),'{0}',a.InvCode,GETDATE(),'{6}', 1,'0','{7}','90',null, null,null,'{2}' ,f.F_RealName ,GETDATE(), '{4}' ,'' FROM ICSMO a INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location INNER JOIN ICSExtension b ON a.ExtensionID=b.ID AND a.WorkPoint=b.WorkPoint INNER JOIN ICSInventory c ON a.InvCode=c.InvCode AND a.WorkPoint=c.WorkPoint where a.MOCode='{3}' and a.Sequence='{5}' and a.WorkPoint='{4}' ";
sql = string.Format(sql, LotNo, Quantity, User, TransCode, WorkPoint, TransSequence, dtt, IDD); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo366")); }
}
///存入条码关联表
sql = @"IF NOT EXISTS(SELECT F_Account FROM Sys_SRM_User WHERE F_Account='{2}' AND F_Location='{3}')
BEGIN RAISERROR('" + string.Format(language.GetNameByCode("WMSAPIInfo060"), "{2}") + @"',16,1); RETURN END
INSERT INTO ICSInventoryLotDetail(LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME, WorkPoint,EATTRIBUTE1) SELECT a.LotNo,'{0}','{1}',f.F_Account ,f.F_RealName,GETDATE(), a.WorkPoint,'' FROM ICSInventoryLot a INNER JOIN Sys_SRM_User f ON f.F_Account='{2}' AND a.WorkPoint=f.F_Location WHERE a.LotNo='{4}' AND a.WorkPoint='{3}' ";
sql = string.Format(sql, TransCode, TransSequence, User, WorkPoint, LotNo);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo367")); }
}
else { throw new Exception(language.GetNameByCode("WMSAPIInfo373"));//條碼綁定工單失敗!數量已滿!
} }
} catch (Exception) { throw; } }
public static void ICSMOReceiveUntie(string LotNo, string WorkPoint, string BusinessCode, SqlCommand cmd, Dictionary<string, string> language) { try { DateTime now = DateTime.Now; string sql = @"select * from ICSInventoryLotDetail
where LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint); DataTable dttte = DBHelper.SQlReturnData(sql, cmd); if (dttte.Rows.Count > 0) { ///删除条码关联表数据
sql = @"DELETE FROM [dbo].[ICSInventoryLotDetail] WHERE LotNo='{0}' AND WorkPoint='{1}'
";
sql = string.Format(sql, LotNo, WorkPoint);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo464")); }
sql = @"DELETE FROM [dbo].[ICSInventoryLot] WHERE LotNo = '{0}' AND WorkPoint = '{1}'
";
sql = string.Format(sql, LotNo, WorkPoint);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo464")); }
} else { throw new Exception(language.GetNameByCode("WMSAPIInfo465")); }
} catch (Exception) { throw; } } #endregion
#region 容器条码绑定
public static bool ICSContainerLot(string LotNo, string containerCode, string WorkPoint, string Identification, string User, string ProjectCode, string BatchCode, string Version, string Brand, string cFree1, string cFree2, string cFree3, string cFree4, string cFree5, string cFree6, string cFree7, string cFree8, string cFree9, string cFree10, string BusinessCode, SqlCommand cmd, Dictionary<string, string> language) { try { bool flag = false; string Colspan = ""; string IDD = ""; string Time = ""; DateTime dtt; DateTime now = DateTime.Now;
string sql = @"select * FROM ICSContainerLot WHERE LotNo='{0}' AND WorkPoint='{1}'
";
sql = string.Format(sql, LotNo, WorkPoint); DataTable dttte = DBHelper.SQlReturnData(sql, cmd); if (dttte.Rows.Count > 0) {
///修改容器关联表
sql = @"UPDATE ICSContainerLot SET ContainerID=(SELECT ID FROM ICSContainer WHERE ContainerCode='{0}' AND WorkPoint='{1}' )
where lotno='{2}' and WorkPoint='{1}' ";
sql = string.Format(sql, containerCode, WorkPoint, LotNo);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(string.Format(language.GetNameByCode("WMSAPIInfo525"), containerCode, LotNo)); } flag = true; } else { sql = @"select 1 from ICSInventoryLot
where LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint); DataTable dttaa = DBHelper.SQlReturnData(sql, cmd); if (dttaa.Rows.Count > 0) { //检验自由项
Colspan = ProjectCode + "~" + BatchCode + "~" + Version + "~" + Brand + "~" + cFree1 + "~" + cFree2 + "~" + cFree3 + "~" + cFree4 + "~" + cFree5 + "~" + cFree6 + "~" + cFree7 + "~" + cFree8 + "~" + cFree9 + "~" + cFree10; sql = @"select ID,Colspan from ICSExtension a
where Colspan='{0}' and WorkPoint='{1}'";
sql = string.Format(sql, Colspan, WorkPoint); DataTable dttt = DBHelper.SQlReturnData(sql, cmd); if (dttt.Rows.Count == 0) { IDD = Guid.NewGuid().ToString(); sql = @"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
select '{17}','{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',GETDATE(),'{15}',f.F_RealName,'{16}'from Sys_SRM_User f where f.F_Account='{15}' and f.F_Location='{16}'";
sql = string.Format(sql, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, User, WorkPoint, IDD); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(language.GetNameByCode("WMSAPIInfo366")); } } else { IDD = dttt.Rows[0]["ID"].ToString(); } Time = "2999-12-31 00:00:00.000"; dtt = Convert.ToDateTime(Time);
///存入容器关联表
sql = @"IF NOT EXISTS(SELECT F_Account FROM Sys_SRM_User WHERE F_Account='{1}' AND F_Location='{2}')
BEGIN RAISERROR('" + string.Format(language.GetNameByCode("WMSAPIInfo060"), "{1}") + @"',16,1); RETURN END
INSERT INTO ICSContainerLot(ID,ContainerID,LotNo,MUSER,MUSERName,WorkPoint,MTIME) SELECT NEWID(),a.ID,'{3}','{1}',f.F_RealName,'{2}',GETDATE() FROM ICSContainer a INNER JOIN Sys_SRM_User f ON f.F_Account='{1}' AND a.WorkPoint=f.F_Location WHERE a.ContainerCode='{0}' AND WorkPoint='{2}' ";
sql = string.Format(sql, containerCode, User, WorkPoint, LotNo);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(string.Format(language.GetNameByCode("WMSAPIInfo525"), containerCode, LotNo)); }
///记录日志
sql = @"INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME)
VALUES(NEWID(), (SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{0}'), '{1}', 2, '{2}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{2}' and F_Location ='{3}'), '{3}', GETDATE())";
sql = string.Format(sql, containerCode, LotNo, User, WorkPoint); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception(string.Format(language.GetNameByCode("WMSAPIInfo526"), containerCode, LotNo)); }
flag = true; }
} return flag;
} catch (Exception ex) { log.Error(ex.Message); //return false;
throw; }
}
/// <summary>
/// 容器条码解绑
/// </summary>
/// <param name="LotNo"></param>
/// <param name="WorkPoint"></param>
/// <param name="User"></param>
/// <param name="cmd"></param>
/// <param name="language"></param>
public static bool ICSContainerLotUntie(string ContainerCode, string LotNo, string WorkPoint, string User, string Type, SqlCommand cmd, Dictionary<string, string> language) { try { DateTime now = DateTime.Now; string sql = @"select 1 from ICSContainerLot
where ContainerID=(SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{2}') AND LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint, ContainerCode); log.Debug("查询容器条码关联表是否存在sql：" + sql); DataTable dttte = DBHelper.SQlReturnData(sql, cmd); if (dttte.Rows.Count > 0) { ///删除条码关联表数据
sql = @"DELETE FROM ICSContainerLot
where ContainerID=(SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{2}') AND LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint, ContainerCode);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("容器条码关联表数据删除失败"); }
///记录日志
sql = @"INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME)
VALUES(NEWID(), (SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{0}'), '{1}', '{4}', '{2}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{2}' and F_Location ='{3}' ), '{3}', GETDATE())";
sql = string.Format(sql, ContainerCode, LotNo, User, WorkPoint, Type); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("条码解绑容器记录日志表失败"); }
return true;
} else { throw new Exception(language.GetNameByCode("容器条码关联表不存在容器" + ContainerCode + "与条码" + LotNo + "的绑定关系")); }
} catch (Exception) { throw; } } /// <summary>
/// 容器绑定父容器
/// </summary>
/// <param name="superiorContainerCode"></param>
/// <param name="containerCode"></param>
/// <param name="WorkPoint"></param>
/// <param name="User"></param>
/// <param name="Type"></param>
/// <param name="cmd"></param>
/// <param name="language"></param>
/// <returns></returns>
public static bool ICSContainerCon(string superiorContainerCode, string containerCode, string WorkPoint, string User, string MTIME, string Type, SqlCommand cmd, Dictionary<string, string> language) { try { Boolean flag = false;
DateTime now = DateTime.Now; string superiorContainerID = "";
string sql = @"SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{0}' AND WorkPoint='{1}'"; sql = string.Format(sql, superiorContainerCode, WorkPoint);
DataTable dataTable = DBHelper.SQlReturnData(sql, cmd); if (dataTable.Rows.Count == 0) { throw new Exception("父容器不存在"); } else { //绑定父容器
superiorContainerID = dataTable.Rows[0]["ID"].ToString(); sql = @"UPDATE ICSContainer SET ContainerID='{0}',MUSER='{1}',MUSERName=(SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{1}' AND F_Location='{4}'),MTIME='{2}' WHERE ContainerCode='{3}' AND WorkPoint='{4}'"; sql = string.Format(sql, superiorContainerID, User, MTIME, containerCode, WorkPoint); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("绑定父容器失败"); } //添加日志
sql = @"INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME)
VALUES(NEWID(), (SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{0}'), '{1}', '{4}', '{2}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{2}' and F_Location ='{3}'), '{3}', GETDATE())";
sql = string.Format(sql, superiorContainerCode, containerCode, User, WorkPoint, Type); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("日志记录失败"); }
flag = true; }
return flag;
} catch (Exception ex) { log.Error(ex.Message); //return false;
throw; }
} /// <summary>
/// 容器解绑父容器
/// </summary>
/// <param name="ContainerCode"></param>
/// <param name="WorkPoint"></param>
/// <param name="User"></param>
/// <param name="Type"></param>
/// <param name="MTIME"></param>
/// <param name="cmd"></param>
/// <param name="language"></param>
/// <returns></returns>
public static bool ICSContainerConUntie(string ContainerCode, string WorkPoint, string User, string Type, SqlCommand cmd, Dictionary<string, string> language) { try { //添加日志
string sql = @"INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME)
VALUES(NEWID(), '', '{0}', '{3}', '{1}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{1}'), '{2}', GETDATE())";
sql = string.Format(sql, ContainerCode, User, WorkPoint, Type); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("日志记录失败"); }
//容器表父容器ID清空
sql = string.Format(@"UPDATE ICSContainer SET ContainerID='',MUSER='{2}',
MUSERName=(SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{2}' AND F_Location='{1}') ,MTIME=GETDATE() WHERE ContainerCode='{0}' AND WorkPoint='{1}'",
ContainerCode, WorkPoint, User); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("容器解绑父容器失败"); }
return true; } catch (Exception) { throw; } }
#endregion
#region 车次条码清单
public static bool ICSCartScheduleCreate(string LotNo, string Quantity, string CartNo, string User, string WorkPoint, SqlCommand cmd, Dictionary<string, string> language) { try { bool flag = false; string sql = @"INSERT INTO dbo.ICSCartLotNoCheckList (id,CartNo,LotNo,MUSER,MUSERName,MTIME,WorkPoint,Quantity)
VALUES(NEWID(),'{0}','{1}','{2}','{2}',CONVERT(VARCHAR,GETDATE(),120),'{3}','{4}')";
sql = string.Format(sql, CartNo, LotNo, User, WorkPoint, "" == Quantity ? null : Quantity); log.Debug("生成车次条码清单：" + sql); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("生成车次条码清单失败！"); } flag = true; return flag;
} catch (Exception ex) { log.Error(ex.Message); //return false;
throw; }
} #endregion
/// <summary>
/// 入库时解绑条码与容器
/// </summary>
/// <param name="ContainerCode"></param>
/// <param name="LotNo"></param>
/// <param name="WorkPoint"></param>
/// <param name="User"></param>
/// <param name="Type"></param>
/// <param name="cmd"></param>
/// <param name="language"></param>
/// <returns></returns>
public static bool ICSContainerLotUntieWhenIn(string ContainerCode, string LotNo, string WorkPoint, string User, string Type, SqlCommand cmd, Dictionary<string, string> language) { try { DateTime now = DateTime.Now; string sql = @"select 1 from ICSContainerLot
where ContainerID=(SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{2}') AND LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint, ContainerCode); log.Debug("查询容器条码关联表是否存在sql：" + sql); DataTable dttte = DBHelper.SQlReturnData(sql, cmd); if (dttte.Rows.Count > 0) { ///删除条码关联表数据
sql = @"DELETE FROM ICSContainerLot
where ContainerID=(SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{2}') AND LotNo='{0}' and WorkPoint='{1}' ";
sql = string.Format(sql, LotNo, WorkPoint, ContainerCode);
if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("容器条码关联表数据删除失败"); }
///记录日志
sql = @"INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME)
VALUES(NEWID(), (SELECT DISTINCT ID FROM ICSContainer WHERE ContainerCode='{0}'), '{1}', '{4}', '{2}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{2}' and F_Location ='{3}'), '{3}', GETDATE())";
sql = string.Format(sql, ContainerCode, LotNo, User, WorkPoint, Type); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { throw new Exception("条码解绑容器记录日志表失败"); }
log.Debug(language.GetNameByCode("容器" + ContainerCode + "与条码" + LotNo + "已解除绑定")); return true;
} else { return false; }
} catch (Exception) { throw; } }
/// <summary>
/// 入库时解绑父容器与容器
/// </summary>
/// <param name="ContainerCode"></param>
/// <param name="WorkPoint"></param>
/// <param name="User"></param>
/// <param name="Type"></param>
/// <param name="cmd"></param>
/// <param name="language"></param>
/// <returns></returns>
public static bool ICSContainerConUntieWhenIn(string ContainerCode, string WorkPoint, string User, string Type, SqlCommand cmd, Dictionary<string, string> language) { try { string sql = @"IF EXISTS(SELECT 1 FROM ICSContainer WHERE ContainerCode='{0}' AND WorkPoint='{2}' AND ContainerID IS NOT NULL AND ContainerID<>'')
BEGIN INSERT INTO ICSContainerLog(ID,ContainerID,ContainerOrLotNo,Type,MUSER,MUSERName,WorkPoint,MTIME) VALUES(NEWID(), '', '{0}', '{3}', '{1}', (SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{1}'), '{2}', GETDATE())
UPDATE ICSContainer SET ContainerID='',MUSER='{1}', MUSERName=(SELECT DISTINCT F_RealName FROM Sys_SRM_User WHERE F_Account='{1}' AND F_Location='{2}') ,MTIME=GETDATE() WHERE ContainerCode='{0}' AND WorkPoint='{2}' END";
sql = string.Format(sql, ContainerCode, User, WorkPoint, Type); if (!DBHelper.ExecuteNonQuery(sql, cmd)) { return false; }
log.Debug("容器 " + ContainerCode + " 与父容器已解绑"); return true; } catch (Exception) { throw; } }
/// <summary>
/// U9盘点差异单接口
/// </summary>
/// <param name="TransType"></param>
/// <param name="Identification"></param>
/// <param name="cmd"></param>
public static void CheckDiffInERP(string TransType, string Identification, SqlCommand cmd, Dictionary<string, string> language) { try { #region 查询盘点数据
string sql = @" select FromWarehouseCode AS WHCode,A.InvCode,C.BatchCode into #tempWh from ICSWareHouseLotInfoLog A
LEFT JOIN ICSInventoryLot B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint LEFT JOIN ICSExtension C ON C.ID=B.ExtensionID where Identification='{0}' GROUP BY FromWarehouseCode,A.InvCode,C.BatchCode
select A.WorkPoint AS Costre,A.WorkPoint AS WorkPoint,A.InvCode AS InvCode ,A.WarehouseCode AS WHCode,SUM(A.Quantity) AS Quantity,D.BatchCode AS BatchCode into #TempERP from ICSWareHouseLotInfo A INNER JOIN ICSInventoryLot B ON B.LotNo=A.LotNo AND B.WorkPoint=A.WorkPoint INNER JOIN ICSExtension D ON D.ID=B.ExtensionID INNER JOIN #tempWh C ON C.WHCode=A.WarehouseCode AND C.InvCode=A.InvCode AND C.BatchCode=D.BatchCode GROUP BY A.WorkPoint,A.InvCode,A.WarehouseCode,D.BatchCode
SELECT DISTINCT Costre,WorkPoint FROM #TempERP SELECT Costre,InvCode,WHCode,Quantity,0 AS Amount,BatchCode FROM #TempERP
DROP TABLE #tempWh DROP TABLE #TempERP";
sql = string.Format(sql, Identification); DataSet ds = DBHelper.SQlReturnDataSet(sql, cmd);
string Inputstr = DataToJsonHelper.DataSetToJson(ds, "details", "Costre"); string resultStr = ""; resultStr = HTTPHelper.HttpPost(TransType, ERPUrl.CreateCheckDiffJinHURL, Inputstr);
Result result = new Result(); result = JsonConvert.DeserializeObject<Result>(resultStr); if (!result.Success) { throw new Exception(language.GetNameByCode("WMSAPIInfo080") + result.Message); } #endregion
} catch (Exception) { throw; } } }}
|
