/// <summary>
/// 已經出貨的數據不再更新
/// </summary>
/// <param name="dn"></param>
/// <returns></returns>
public bool isShipOut(string dn)
{
using (var db = OleExec.GetSqlSugarClient(this.DbStr, true))
{
var entity = db.Queryable <MESDataObject.Module.R_DN_STATUS>().With(SqlSugar.SqlWith.NoLock).Any(x => x.DN_FLAG == "1" || x.DN_NO == dn);
return(entity);
}
}
/// <summary>
/// By customerCode get GtRoute => OrderBySEQ.Asc
/// </summary>
/// <param name="customerCode"></param>
/// <returns>List C_SHIPPING_ROUTE_DETAIL</returns>
public List <C_SHIPPING_ROUTE_DETAIL> GetGtRoute(string customerCode)
{
List <C_SHIPPING_ROUTE_DETAIL> cShipRouteDetailList = new List <C_SHIPPING_ROUTE_DETAIL>();
using (var db = OleExec.GetSqlSugarClient(DbStr, true)) {
cShipRouteDetailList = db.Queryable <C_SHIPPING_ROUTE_DETAIL, C_SHIP_CUSTOMER>((csrd, cs) =>
cs.ROUTENAME == csrd.ROUTENAME && cs.CUSTOMERNAME == customerCode).OrderBy((csrd) => csrd.SEQ, SqlSugar.OrderByType.Asc).Select((csrd) => csrd).ToList();
}
return(cShipRouteDetailList);
}
void SaveDn(string dn)
{
try
{
var sdDnDetail = dnBase.sdDnDetailList.Where(x => x.VBELN == dn).Distinct().ToList();
var sdToDetail = dnBase.sdToDetailList.Where(x => x.VBELN == dn).Distinct().LastOrDefault();
var sdToHead = dnBase.sdToHeadList.Where(x => x.TKNUM == sdToDetail.TKNUM).LastOrDefault();
var GtRoute = dnBase.GetGtRoute(sdToDetail.KUNNR.Trim());
#region CustmerCode未配置GtRoute的Dn忽略
if (GtRoute.Count == 0)
{
WriteLog.WriteIntoMESLog(dbstr, bu, "Interface", "MESInterface.VERTIV.VertivDownloadDn", "VertivDownloadDn", $@"Dn:{dn};To:{sdToDetail.TKNUM};CustmerCode:{sdToDetail.KUNNR}未配置GtRoute", "", sdToDetail.TKNUM, dn, sdToDetail.KUNNR, "system", "N");
return;
}
#endregion
using (var db = OleExec.GetSqlSugarClient(dbstr, true))
{
var result = db.Ado.UseTran(() =>
{
db.Deleteable <R_DN_STATUS>().Where(x => x.DN_NO == dn).ExecuteCommand();
foreach (var item in sdDnDetail)
{
db.Insertable <R_DN_STATUS>(new R_DN_STATUS()
{
ID = MesDbBase.GetNewID(db, bu, "R_DN_STATUS"),
DN_NO = dn,
DN_LINE = item.POSNR,
PO_NO = dnBase.sdCustmerSoList.Where(x => x.VBELN == dn && x.POSNR == item.POSNR).Single().BSTKD,
PO_LINE = dnBase.sdCustmerSoList.Where(x => x.VBELN == dn && x.POSNR == item.POSNR).Single().POSEX,
SO_NO = item.VGBEL,
SKUNO = DnBase.SkunoNameHandle(item.MATNR),
QTY = double.Parse(item.LFIMG),
GTTYPE = GtRoute.LastOrDefault().ROUTENAME,
GT_FLAG = "0",
DN_FLAG = "0",
DN_PLANT = sdToHead.WERKS,
GTEVENT = GtRoute.FirstOrDefault().SEQ,
CREATETIME = System.DateTime.Now,
EDITTIME = System.DateTime.Now
}).ExecuteCommand();
}
});
if (!result.IsSuccess)
{
WriteLog.WriteIntoMESLog(dbstr, bu, "Interface", "MESInterface.VERTIV.VertivDownloadDn", "VertivDownloadDn", $@"Dn:{dn};Err:{result.ErrorMessage}", "", "SaveDn", dn, "", "system", "N");
}
}
}
catch (Exception e)
{
WriteLog.WriteIntoMESLog(dbstr, bu, "Interface", "MESInterface.VERTIV.VertivDownloadDn", "VertivDownloadDn", $@"Dn:{dn};Err:{e.Message}", "", "SaveDn", dn, "", "system", "N");
}
}
/// <summary>
/// Get Table seqId
/// </summary>
/// <param name="DbStr"></param>
/// <param name="Bu"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public static string GetNewID(string DbStr, string Bu, string TableName)
{
using (var db = OleExec.GetSqlSugarClient(DbStr, true)) {
var IN_BU = new SugarParameter("@IN_BU", Bu);
var IN_TYPE = new SugarParameter("@IN_TYPE", TableName);
var OUT_RES = new SugarParameter("@OUT_RES", null, true);//isOutput=true
var excDt = db.Ado.UseStoredProcedure().GetDataTable("SFC.GET_ID", IN_BU, IN_TYPE, OUT_RES);
string newID = OUT_RES.Value.ToString();
if (newID.StartsWith("ERR"))
{
throw new Exception("獲取表'" + TableName + "' ID 異常! " + newID);
}
return(newID);
}
}
void SaveTo(string dn)
{
var sdToDetail = dnBase.sdToDetailList.Where(x => x.VBELN == dn).Distinct().ToList();
var sdToHead = dnBase.sdToHeadList.Where(x => x.TKNUM == sdToDetail.FirstOrDefault().TKNUM).Distinct().ToList();
using (var db = OleExec.GetSqlSugarClient(dbstr, true))
{
var result = db.Ado.UseTran(() =>
{
#region delete old data
db.Deleteable <R_TO_HEAD>().Where(x => x.TO_NO == sdToDetail[0].TKNUM).ExecuteCommand();
db.Deleteable <R_TO_DETAIL>().Where(x => x.DN_NO == dn).ExecuteCommand();
#endregion
#region new data
db.Insertable <R_TO_HEAD>(
new R_TO_HEAD()
{
ID = MesDbBase.GetNewID(db, bu, "R_TO_HEAD"),
TO_NO = sdToHead.FirstOrDefault().TKNUM,
PLAN_STARTIME = DateTime.Parse($@"{RFCDate(sdToHead.FirstOrDefault().DPREG)} {sdToHead.FirstOrDefault().UPREG}"),
PLAN_ENDTIME = DateTime.Parse($@"{RFCDate(sdToHead.FirstOrDefault().DPABF)} {sdToHead.FirstOrDefault().UPABF}"),
TO_CREATETIME = $@"{RFCDate(sdToHead.FirstOrDefault().ERDAT)} {sdToHead.FirstOrDefault().ERZET}",
CONTAINER_NO = sdToHead.FirstOrDefault().SIGNI,
VEHICLE_NO = sdToHead.FirstOrDefault().EXTI2,
EXTERNAL_NO = sdToHead.FirstOrDefault().EXTI1,
DROP_FLAG = "0"
}
).ExecuteCommand();
db.Insertable <R_TO_DETAIL>(
new R_TO_DETAIL()
{
ID = MesDbBase.GetNewID(db, bu, "R_TO_DETAIL"),
TO_NO = sdToDetail.FirstOrDefault().TKNUM,
TO_ITEM_NO = sdToDetail.FirstOrDefault().TPNUM,
DN_NO = dn,
DN_CUSTOMER = sdToDetail.FirstOrDefault().KUNNR,
CREATETIME = System.DateTime.Now
}).ExecuteCommand();
#endregion
});
if (!result.IsSuccess)
{
WriteLog.WriteIntoMESLog(dbstr, bu, "Interface", "MESInterface.VERTIV.VertivDownloadDn", "VertivDownloadDn", $@"Dn:{dn};Err:{result.ErrorMessage}", "", "SaveTo", dn, "", "system", "N");
}
}
}
public bool DownDnDataToMes(string dn, string to)
{
List <string> soList = new List <string>();
foreach (DataRow dataRow in ZRFC_SFC_NSG_0003.GetTableValue("SD_DN_DETAIL").Select($@"VBELN={dn}"))
{
soList.Add(dataRow["VGBEL"].ToString());
}
using (var db = OleExec.GetSqlSugarClient(this.DbStr, true))
{
var result = db.Ado.UseTran(() =>
{
#region delete old data
db.Deleteable <MESDataObject.Module.SD_CUSTMER_PO>().Where(x => soList.Contains(x.VBELN)).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_CUSTOMER_PO_ITEM>().Where(x => soList.Contains(x.VBELN)).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_CUSTOMER_SO>().Where(x => x.VBELN == dn).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_DN_DETAIL>().Where(x => x.VBELN == dn).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_REPORT_DETAIL>().Where(x => x.VBELN == dn).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_TO_DETAIL>().Where(x => x.VBELN == dn).ExecuteCommand();
db.Deleteable <MESDataObject.Module.SD_TO_HEAD>().Where(x => x.TKNUM == to).ExecuteCommand();
#endregion
#region writer new data
db.Insertable <SD_CUSTMER_PO>(sdCustmerPoList.Where(x => soList.Contains(x.VBELN)).ToArray()).ExecuteCommand();
db.Insertable <SD_CUSTOMER_PO_ITEM>(sdCustmerPoItemList.Where(x => soList.Contains(x.VBELN)).ToArray()).ExecuteCommand();
db.Insertable <SD_CUSTOMER_SO>(sdCustmerSoList.Where(x => x.VBELN == dn).ToArray()).ExecuteCommand();
db.Insertable <SD_DN_DETAIL>(sdDnDetailList.Where(x => x.VBELN == dn).ToArray()).ExecuteCommand();
db.Insertable <SD_REPORT_DETAIL>(sdReportDetailList.Where(x => x.VBELN == dn).ToArray()).ExecuteCommand();
db.Insertable <SD_TO_DETAIL>(sdToDetailList.Where(x => x.VBELN == dn).ToArray()).ExecuteCommand();
db.Insertable <SD_TO_HEAD>(sdToHeadList.Where(x => x.TKNUM == to).ToArray()).ExecuteCommand();
#endregion
currentDnList.Add(dn);
});
if (result.IsSuccess)
{
return(true);
}
else
{
return(false);
}
}
}
public static void WriteIntoMESLog(string Dbstr, string bu, string programName, string className, string functionName, string logMessage, string logSql, string data1, string data2, string data3, string editEmp, string mailflag)
{
using (var db = OleExec.GetSqlSugarClient(Dbstr, true))
{
db.Insertable <R_MES_LOG>(new R_MES_LOG {
ID = MesDbBase.GetNewID(Dbstr, bu, "R_MES_LOG"),
PROGRAM_NAME = programName,
CLASS_NAME = className,
FUNCTION_NAME = functionName,
LOG_MESSAGE = logMessage,
LOG_SQL = logSql,
DATA1 = data1,
DATA2 = data2,
DATA3 = data3,
EDIT_EMP = editEmp,
EDIT_TIME = System.DateTime.Now,
MAILFLAG = mailflag
}).ExecuteCommand();
}
}