public static bool DecideUPSCDSI(UPSDatabase db, ProductInfo productInfo,CDSIPO poInfo, out UPSCombinePO combinePo)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
combinePo = null;
if (db.UPSModelEntity.Any(x => x.Model== productInfo.Model &&
x.Status==EnumUPSModelStatus.Enable.ToString()))
{
combinePo = db.UPSCombinePOEntity.Where(x => x.Model == productInfo.Model &&
x.IECPO == poInfo.PO &&
(x.Status == EnumUPSCombinePOStatus.Free.ToString() ||
x.Status == EnumUPSCombinePOStatus.Release.ToString()))
.OrderBy(x=>x.ID).FirstOrDefault();
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public static void TestDB(AppConfig config,
UPSDatabase db)
{
//UPSDatabase db = new UPSDatabase(config.DBConnectStr);
DateTime now = DateTime.Now;
var upsModelList = db.UPSModelEntity.Where(x => x.Remark == "NEW").ToList();
db.Connection.Close();
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
db.Transaction = db.Connection.BeginTransaction();
foreach (var item in upsModelList)
{
item.Udt = now;
}
var upsModel = db.UPSModelEntity.Where(x => x.Model == "testModel").FirstOrDefault();
if (upsModel == null)
{
upsModel = new UPSModel
{
Model = "testModel",
FirstReceiveDate = now,
LastReceiveDate = now,
Remark = "NEW",
Status = EnumUPSModelStatus.Enable.ToString(),
Editor = string.Empty,
Cdt = now,
Udt = now
};
db.UPSModelEntity.InsertOnSubmit(upsModel);
db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
}
db.Transaction.Commit();
if (db.Transaction == null)
{
Console.WriteLine("null transaction!!");
}
db.Transaction = db.Connection.BeginTransaction();
var upsModel1 = db.UPSModelEntity.Where(x => x.Model == "testModel").FirstOrDefault();
if (upsModel1 == null)
{
upsModel1 = new UPSModel
{
Model = "testModel",
FirstReceiveDate = now,
LastReceiveDate = now,
Remark = "NEW",
Status = EnumUPSModelStatus.Enable.ToString(),
Editor = string.Empty,
Cdt = now,
Udt = now
};
db.UPSModelEntity.InsertOnSubmit(upsModel1);
db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
}
db.Transaction.Commit();
//db.Connection.Close();
}
public static void UpdateUPSModel(AppConfig config,
UPSDatabase db,
HPPO dbPo,
UPSPOBOM poBom,
string editor)
{
DateTime now = DateTime.Now;
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
db.Transaction = db.Connection.BeginTransaction();
dbPo.IECPOList.ForEach(n =>
{
n.Udt = now;
var upsModel = db.UPSModelEntity.Where(x => x.Model == n.Model).FirstOrDefault();
if (upsModel == null)
{
upsModel = new UPSModel
{
Model = n.Model,
FirstReceiveDate = dbPo.PO.ReceiveDate,
LastReceiveDate = dbPo.PO.ReceiveDate,
Remark = "NEW",
Status = EnumUPSModelStatus.Enable.ToString(),
Editor = dbPo.PO.Editor,
Cdt = now,
Udt = now
};
db.UPSModelEntity.InsertOnSubmit(upsModel);
db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
}
//else
//{
// upsModel.LastReceiveDate = dbPo.PO.ReceiveDate;
// upsModel.Udt = now;
//}
});
db.Transaction.Commit();
//db.Connection.Close();
}
catch (Exception e)
{
logger.Error(e.Message, e);
if (db.Transaction != null && db.Transaction.Connection != null)
{
db.Transaction.Rollback();
}
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public static void UpdateUPSHPPO(AppConfig config,
UPSDatabase db,
HPPO dbPo,
UPSPOBOM poBom,
string editor)
{
DateTime now = DateTime.Now;
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
//UPSDatabase db = new UPSDatabase(config.DBConnectStr);
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
db.Transaction = db.Connection.BeginTransaction();
if (dbPo.PO.Status != poBom.State.ToString())
{
dbPo.PO.Status = poBom.State.ToString();
dbPo.PO.ErrorText = poBom.ErrorText;
dbPo.PO.Udt = now;
//db.UPSHPPOEntity.Attach(dbPo.PO, true);
}
if (poBom.State == SendBOMState.VerifyOK)
{
//產生UPSCombinePO
List<UPSCombinePO> UPSCombinePOList = new List<UPSCombinePO>();
List<UPSIECPO> IECPOList = null;
if (dbPo.isWithdraw)
{
IECPOList = dbPo.WithdrawIECPOList;
foreach (UPSIECPO iecpo in dbPo.IECPOList)
{
if (iecpo.Editor == "Withdraw")
{
iecpo.Editor = "UPS-" + iecpo.Editor;
iecpo.Udt = now;
}
}
foreach (UPSPOAVPart avpart in dbPo.PartNoList)
{
if (avpart.Editor == "Withdraw")
{
avpart.Editor = "UPS-" + avpart.Editor;
avpart.Udt = now;
}
}
}
else
{
IECPOList = dbPo.IECPOList;
}
IECPOList.ForEach(n =>
{
//n.Status = SendBOMState.VerifyOK.ToString();
n.Udt = now;
var upsModel = db.UPSModelEntity.Where(x => x.Model == n.Model).FirstOrDefault();
if (upsModel == null)
{
upsModel = new UPSModel
{
Model = n.Model,
FirstReceiveDate = dbPo.PO.ReceiveDate,
LastReceiveDate = dbPo.PO.ReceiveDate,
Remark = string.Empty,
Status = EnumUPSModelStatus.Enable.ToString(),
Editor = dbPo.PO.Editor,
Cdt = now,
Udt = now
};
db.UPSModelEntity.InsertOnSubmit(upsModel);
}
else
{
upsModel.LastReceiveDate = dbPo.PO.ReceiveDate;
upsModel.Udt = now;
}
int qty = n.Qty;
for (int i = 0; i < qty; i++)
{
UPSCombinePO combinePO = new UPSCombinePO
{
HPPO = dbPo.PO.HPPO,
IECPO = n.IECPO,
IECPOItem = n.IECPOItem,
Model = n.Model,
ReceiveDate = dbPo.PO.ReceiveDate,
ProductID = string.Empty,
CUSTSN = string.Empty,
Station = string.Empty,
IsShipPO = "N",
Status = EnumCombinePoState.Free.ToString(),
Remark = string.Empty,
Editor = editor,
Cdt = now,
Udt = now
};
UPSCombinePOList.Add(combinePO);
}
});
DataTableHelper.BulkCopyToDatabase(UPSCombinePOList,
"UPSCombinePO",
(SqlConnection)db.Connection,
(SqlTransaction)db.Transaction);
}
db.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
db.Transaction.Commit();
// db.Connection.Close();
}
catch (Exception e)
{
logger.Error(e.Message, e);
if (db.Transaction != null && db.Transaction.Connection != null)
{
db.Transaction.Rollback();
}
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public static List<HPPO> GetUPSHPPO(AppConfig config,
UPSDatabase db,
DateTime receiveDate)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
List<HPPO> upspoList = new List<HPPO>();
//UPSDatabase db = new UPSDatabase(config.DBConnectStr);
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
var mesPoList = (from x in db.UPSHPPOEntity
where x.ReceiveDate >= receiveDate
select x).ToList();
var poNumList = mesPoList.Select(x => x.HPPO).ToList();
var mesIecPoList = (from x in db.UPSIECPOEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
var mesPoPartList = (from x in db.UPSPOAVPartEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
foreach (var po in mesPoList)
{
HPPO hppo = new HPPO();
hppo.PO = po;
hppo.IECPOList = mesIecPoList.Where(x => x.HPPO == po.HPPO).ToList();
hppo.PartNoList = mesPoPartList.Where(x => x.HPPO == po.HPPO).ToList();
hppo.isWithdraw = mesIecPoList.Any(x => x.HPPO == po.HPPO && x.Editor == "Withdraw");
if (hppo.isWithdraw)
{
hppo.WithdrawIECPOList = mesIecPoList.Where(x => x.HPPO == po.HPPO && x.Editor == "Withdraw").ToList();
hppo.WithdrawPartNoList = mesPoPartList.Where(x => x.HPPO == po.HPPO && x.Editor == "Withdraw").ToList();
hppo.PO.WithdrawQty = hppo.WithdrawIECPOList.Sum(x => x.Qty);
}
hppo.Insert = false;
upspoList.Add(hppo);
}
return upspoList;
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public static List<HPPO> AddUPSHPPO(AppConfig config,
UPSDatabase db,
IList<SAPPO> sapPoList,
List<ModelBom> modelBomList,
string editor)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
List<HPPO> upspoList = new List<HPPO>();
DateTime now = DateTime.Now;
List<String> poNumList = sapPoList.Select(x => x.HPPO).Distinct().ToList();
//UPSDatabase db = new UPSDatabase(config.DBConnectStr);
var mesPoList = (from x in db.UPSHPPOEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
var mesIecPoList = (from x in db.UPSIECPOEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
var mesPoPartList = (from x in db.UPSPOAVPartEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
foreach (var poId in poNumList)
{
var poList = sapPoList.Where(x => x.HPPO == poId).ToList();
var first = poList[0];
var po = mesPoList.Where(x => x.HPPO == poId).FirstOrDefault();
if (po == null) //新增
{
#region 新增
HPPO hppo = new HPPO();
hppo.PO = new UPSHPPO
{
HPPO = poId,
Plant = first.Plant,
POType = first.POType,
HPSKU = first.HPSku,
EndCustomerPO = first.CustPO,
Status = SendBOMState.CreatedPoBOM.ToString(),
Qty = poList.Sum(x => x.Qty),
Editor = editor,
ErrorText = string.Empty,
ReceiveDate = new DateTime(now.Year, now.Month, now.Day),
Cdt = now,
Udt = now
};
hppo.IECPOList = new List<UPSIECPO>();
hppo.PartNoList = new List<UPSPOAVPart>();
foreach (var sappo in poList)
{
UPSIECPO iecpo = new UPSIECPO
{
HPPO = poId,
IECPO = sappo.IECPO,
IECPOItem = sappo.IECPOItem,
Model = sappo.IECSku,
Qty = sappo.Qty,
//Status = SendBOMState.Waiting.ToString(),
Cdt = now,
Udt = now,
Editor = editor,
Status ="New"
};
hppo.IECPOList.Add(iecpo);
//有2笔HPPO,且机型不同，AV 也不同，会导致漏赛UPSPOAVPart。
// foreach (var partNo in first.UPSMatchAVPart)
foreach (var partNo in sappo.UPSMatchAVPart)
{
var modelBom = modelBomList.Where(x => x.AVPartNo == partNo).FirstOrDefault();
UPSPOAVPart part = new UPSPOAVPart
{
HPPO = poId,
AVPartNo = partNo,
IECPartNo = modelBom == null ? string.Empty : modelBom.IECPartNo,
IECPartType = modelBom == null ? string.Empty : modelBom.IECPartType,
Editor = editor,
Remark = string.Empty,
Udt = now,
Cdt = now
};
hppo.PartNoList.Add(part);
}
}
hppo.Insert = true;
upspoList.Add(hppo);
#endregion
}
else //查詢 需考慮Cancel PO,再產生新的IECPO case
{
HPPO hppo = new HPPO();
bool isWithdraw = false;
#region 檢查及計算是否有拉單Case
var hpIECPOList = mesIecPoList.Where(x => x.HPPO == poId).ToList();
var hpAvPOList = mesPoPartList.Where(x => x.HPPO == poId).ToList();
var withdrawPOList =poList.Where(x => !hpIECPOList.Any(y => x.IECPO == y.IECPO && x.IECPOItem == y.IECPOItem)).ToList();
if (withdrawPOList.Count > 0)
{
hppo.WithdrawIECPOList = new List<UPSIECPO>();
foreach (var sappo in withdrawPOList)
{
UPSIECPO iecpo = new UPSIECPO
{
HPPO = poId,
IECPO = sappo.IECPO,
IECPOItem = sappo.IECPOItem,
Model = sappo.IECSku,
Qty = sappo.Qty,
//Status = SendBOMState.Waiting.ToString(),
Cdt = now,
Udt = now,
Status ="Withdraw",
Editor = "Withdraw"
};
hppo.WithdrawIECPOList.Add(iecpo);
}
hppo.WithdrawPartNoList = new List<UPSPOAVPart>();
foreach (var partNo in withdrawPOList[0].UPSMatchAVPart)
{
var modelBom = modelBomList.Where(x => x.AVPartNo == partNo).FirstOrDefault();
UPSPOAVPart part = new UPSPOAVPart
{
HPPO = poId,
AVPartNo = partNo,
IECPartNo = modelBom == null ? string.Empty : modelBom.IECPartNo,
IECPartType = modelBom == null ? string.Empty : modelBom.IECPartType,
Editor = "Withdraw",
Remark ="Withdraw",
Udt = now,
Cdt = now
};
hppo.WithdrawPartNoList.Add(part);
}
isWithdraw = true;
po.Status = SendBOMState.CreatedWithdrawPoBOM.ToString();
po.WithdrawQty = withdrawPOList.Sum(x => x.Qty);
po.Qty = po.Qty + po.WithdrawQty;
}
#endregion
hppo.PO = po;
hppo.IECPOList = mesIecPoList.Where(x => x.HPPO == poId).ToList();
hppo.PartNoList = mesPoPartList.Where(x => x.HPPO == poId).ToList();
hppo.isWithdraw = isWithdraw;
hppo.Insert = false;
upspoList.Add(hppo);
}
}
var insertDataList = upspoList.Where(x => x.Insert || x.isWithdraw ).ToList();
if (insertDataList.Count > 0)
{
#region insert MES db data case
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
db.Transaction = db.Connection.BeginTransaction();
var hppoList = insertDataList.Where(x=>x.Insert).Select(x => x.PO).ToList();
DataTableHelper.BulkCopyToDatabase(hppoList, "UPSHPPO", (SqlConnection)db.Connection, (SqlTransaction)db.Transaction);
//db.UPSHPPOEntity.InsertAllOnSubmit(hppoList);
var iecpoList = new List<UPSIECPO>();
insertDataList.ForEach(x => {
if (x.Insert)
{
iecpoList = iecpoList.Union(x.IECPOList).ToList();
}
else
{
iecpoList = iecpoList.Union(x.WithdrawIECPOList).ToList();
}
});
DataTableHelper.BulkCopyToDatabase(iecpoList, "UPSIECPO", (SqlConnection)db.Connection, (SqlTransaction)db.Transaction);
//db.UPSIECPOEntity.InsertAllOnSubmit(iecpoList);
var avPartList = new List<UPSPOAVPart>();
insertDataList.ForEach(x =>
{
if (x.Insert)
{
avPartList = avPartList.Concat(x.PartNoList).ToList();
}
else
{
avPartList = avPartList.Concat(x.WithdrawPartNoList).ToList();
}
});
//db.UPSPOAVPartEntity.InsertAllOnSubmit(avPartList);
DataTableHelper.BulkCopyToDatabase(avPartList, "UPSPOAVPart", (SqlConnection)db.Connection, (SqlTransaction)db.Transaction);
//db.SubmitChanges();
if (insertDataList.Any(x => x.isWithdraw))
{
db.SubmitChanges();
}
db.Transaction.Commit();
db.Connection.Close();
//重新get data
var mesPoList1 = (from x in db.UPSHPPOEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
var mesIecPoList1 = (from x in db.UPSIECPOEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
var mesPoPartList1 = (from x in db.UPSPOAVPartEntity
where poNumList.Contains(x.HPPO)
select x).ToList();
upspoList = new List<HPPO>();
foreach (var poId in poNumList)
{
HPPO hppo = new HPPO();
hppo.PO = mesPoList1.Where(x => x.HPPO == poId).First();
hppo.IECPOList = mesIecPoList1.Where(x => x.HPPO == poId).ToList();
hppo.PartNoList = mesPoPartList1.Where(x => x.HPPO == poId).ToList();
var withdrawDataList= insertDataList.Where(x => x.PO.HPPO == poId && x.isWithdraw).FirstOrDefault();
if (withdrawDataList!=null)
{
hppo.WithdrawIECPOList = withdrawDataList.WithdrawIECPOList;
hppo.isWithdraw = true;
hppo.WithdrawPartNoList = withdrawDataList.WithdrawPartNoList;
hppo.PO.WithdrawQty = withdrawDataList.PO.WithdrawQty;
}
hppo.Insert = false;
upspoList.Add(hppo);
}
#endregion
}
return upspoList;
}
catch (Exception e)
{
logger.Error(e.Message, e);
if (db.Transaction != null && db.Transaction.Connection != null)
{
db.Transaction.Rollback();
}
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
public static bool AssignCNRSPO(AppConfig config,
UPSDatabase db,
ProductInfo productInfo,
CDSIPO poInfo,
UPSCombinePO combinePo,
out string errorText)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
errorText = string.Empty;
//Get AVPart Number
string hpPo = combinePo.HPPO;
IList<string> avPartNoList = db.UPSPOAVPartEntity.Where(x => x.HPPO == hpPo && x.IECPartNo!=null && x.IECPartNo!="" )
.Select(x=>x.AVPartNo).Distinct().ToList();
if (avPartNoList == null || avPartNoList.Count == 0)
{
errorText = string.Format("HPPO: {0} Not Find AV PartNo in UPS System", hpPo);
return false;
}
productInfo.ATSNAV = avPartNoList[0];
logger.InfoFormat("HPPO: {0} AVPartNo:{1}", hpPo, avPartNoList[0]);
ATMStruct ret = GetATM(productInfo.CUSTSN, productInfo.ATSNAV, combinePo.HPPO);
if (ret.retcode < 0 ||
string.IsNullOrEmpty(ret.assetTagNum))
{
errorText = string.Format("UPSATM Return:{0} AssetTag:{1} Message:{2}", ret.retcode.ToString(),ret.assetTagNum??"", ret.message);
logger.ErrorFormat(errorText);
return false;
}
else
{
logger.InfoFormat(" UPSATM Return:{0} Message:{1} Asset Tag:{2}", ret.retcode.ToString(), ret.message, ret.assetTagNum);
}
string astNum = ret.assetTagNum;
#region write db data
DateTime now = DateTime.Now;
SqlConnection dbconnect = (SqlConnection)db.Connection;
if (dbconnect.State == System.Data.ConnectionState.Closed)
{
dbconnect.Open();
}
SqlTransaction dbTxn = dbconnect.BeginTransaction();
db.Transaction = dbTxn;
//PoMO
SQLStatement.InsertCNRSPoMo(dbconnect, dbTxn, poInfo);
//int assignQty = poInfo.DeliveryQty - poInfo.RemainQty;
//int specialRemainQty = demianOrder.Qty - assignQty;
//if (specialRemainQty == 1)
//{
// SQLStatement.UpdateSpecialOrderStatus(dbconnect, poInfo.PO, "Closed");
//}
//else if (demianOrder.Status.Equals("Created"))
//{
// SQLStatement.UpdateSpecialOrderStatus(dbconnect, poInfo.PO, "Active");
//}
//CSIAST table
SQLStatement.DeleteCDSIAST(dbconnect, dbTxn, productInfo.ProductID);
SQLStatement.WriteCDSIAST(dbconnect, dbTxn, productInfo.ProductID, "DID", "");
SQLStatement.WriteCDSIAST(dbconnect, dbTxn, productInfo.ProductID, "ASSET_TAG", astNum);
SQLStatement.WriteCDSIAST(dbconnect, dbTxn, productInfo.ProductID, "HPOrder", combinePo.HPPO);
SQLStatement.WriteCDSIAST(dbconnect, dbTxn, productInfo.ProductID, "PurchaseOrder", "");
SQLStatement.WriteCDSIAST(dbconnect, dbTxn, productInfo.ProductID, "FactoryPO", combinePo.IECPO);
SQLStatement.WriteProductAttr(dbconnect, dbTxn, productInfo.ProductID, "CNRSState", "OK", "CNRS", now);
//Update UPSCombinePO
combinePo.ProductID = productInfo.ProductID;
combinePo.CUSTSN = productInfo.CUSTSN;
combinePo.Status = EnumUPSCombinePOStatus.Used.ToString();
combinePo.IsShipPO = "CNRS";
combinePo.Udt = now;
combinePo.Editor = "UPS.CNRS";
db.SubmitChanges();
dbTxn.Commit();
dbconnect.Close();
#endregion
return true;
}
catch (Exception e)
{
errorText = e.Message;
logger.Error(e.Message, e);
return false;
}
finally
{
if (db.Connection.State == System.Data.ConnectionState.Open)
{
db.Connection.Close();
}
logger.DebugFormat("END: {0}()", methodName);
}
}
public static bool AssignUPSPO(AppConfig config,
UPSDatabase db,
ProductInfo productInfo,
CDSIPO poInfo,
UPSCombinePO combinePo,
out string errorText)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
errorText = string.Empty;
//ATMStruct ret = GetATM(productInfo.CUSTSN, productInfo.ATSNAV, combinePo.HPPO);
//if (ret.retcode < 0 || string.IsNullOrEmpty(ret.assetTagNum))
//{
// errorText = string.Format(" UPSATM Return:{0} Asset Tag:{1} Message:{2}", ret.retcode.ToString(),ret.assetTagNum??"", ret.message);
// logger.ErrorFormat(errorText);
// return false;
//}
//else
//{
// logger.InfoFormat(" UPSATM Return:{0} Message:{1} Asset Tag:{2}", ret.retcode.ToString(), ret.message, ret.assetTagNum);
//}
var hppoData=db.UPSHPPOEntity.Where(x=>x.HPPO== combinePo.HPPO).FirstOrDefault();
string hpSku =hppoData==null?null: hppoData.HPSKU;
USIStruct returnUSI= GetUSI(productInfo.CUSTSN, productInfo.ATSNAV, combinePo.HPPO,hpSku);
if (returnUSI.retcode < 0 || string.IsNullOrEmpty(returnUSI.unattend))
{
errorText = string.Format(" UPSUSI Return:{0} unattend:{1} Message:{2}", returnUSI.retcode.ToString(),returnUSI.unattend ??"", returnUSI.message);
logger.ErrorFormat(errorText);
return false;
}
else
{
logger.InfoFormat(" UPSUSI Return:{0} Message:{1} unattend:{2}", returnUSI.retcode.ToString(), returnUSI.message, returnUSI.unattend);
}
string unattend = returnUSI.unattend;
string astNum = null;
XmlDocument doc = new XmlDocument();
doc.LoadXml(unattend);
XmlNodeList assetTagNodList = doc.GetElementsByTagName("ComputerName");
XmlNodeList userNodeList = doc.GetElementsByTagName("Credentials");
XmlNodeList admNodeList = doc.GetElementsByTagName("AdministratorPassword");
foreach (XmlNode node in assetTagNodList)
{
if (!string.IsNullOrEmpty(node.InnerText))
{
astNum = node.InnerText;
break;
}
}
if (string.IsNullOrEmpty(astNum))
{
errorText = string.Format("Asset Tag is empty!");
logger.ErrorFormat(errorText);
return false;
}
copyUnattend2ImgSrv(config, unattend, productInfo);
#region write db data
DateTime now = DateTime.Now;
if (db.Connection.State == System.Data.ConnectionState.Closed)
{
db.Connection.Open();
}
db.Transaction = db.Connection.BeginTransaction();
//SqlTransaction dbTxn= dbconnect.BeginTransaction();
//db.Transaction = dbTxn;
SqlConnection dbconnect = (SqlConnection)db.Connection;
SqlTransaction transaction = (SqlTransaction)db.Transaction;
//PoMO
SQLStatement.InsertCDSIPoMo(dbconnect, transaction, poInfo);
//CSIAST table
SQLStatement.DeleteCDSIAST(dbconnect, transaction, productInfo.ProductID);
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "DID", "");
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "ASSET_TAG", astNum);
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "HPOrder", combinePo.HPPO);
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "PurchaseOrder", hppoData.EndCustomerPO ?? "");
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "FactoryPO", combinePo.IECPO);
SQLStatement.WriteProductAttr(dbconnect, transaction, productInfo.ProductID, "CDSIState", "OK", "CDSI", now);
foreach (XmlNode node in userNodeList)
{
foreach (XmlNode cnode in node.ChildNodes)
{
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, cnode.Name, cnode.InnerText);
}
}
foreach (XmlNode node in admNodeList)
{
foreach (XmlNode cnode in node.ChildNodes)
{
if (cnode.Name.ToLower() == "value")
{
SQLStatement.WriteCDSIAST(dbconnect, transaction, productInfo.ProductID, "AdministratorPassword", cnode.InnerText);
}
}
}
//Update UPSCombinePO
combinePo.ProductID = productInfo.ProductID;
combinePo.CUSTSN = productInfo.CUSTSN;
combinePo.Status = EnumUPSCombinePOStatus.Used.ToString();
combinePo.IsShipPO = "CDSI";
combinePo.Udt = now;
combinePo.Editor = "UPS.CDSI";
db.SubmitChanges();
db.Transaction.Commit();
db.Connection.Close();
#endregion
return true;
}
catch (Exception e)
{
errorText = e.Message;
logger.Error(e.Message, e);
return false;
}
finally
{
if (db.Connection.State == System.Data.ConnectionState.Open)
{
db.Connection.Close();
}
logger.DebugFormat("END: {0}()", methodName);
}
}
static void Main(string[] args)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
//if (File.Exists(@"\\10.99.183.50\fsm_com\Mailbox\CNU23090R7-28924AD3C1D7.ACK"))
//{
// Console.WriteLine(@"CheckCDSIResult find \\10.99.183.50\fsm_com\Mailbox\CNU23090R7-28924AD3C1D7.ACK Ack file!!");
//}
if (!ExecShell.CheckOneProcessRun())
{
return;
}
AppConfig config = new AppConfig();
string msg = "";
string localmsg = "";
//Log log = new Log(config.logPath, config.logPrefixName);
//log.write(LogType.Info, 0, "Main" , "", "Start Program...");
try
{
SqlConnection dbconnect = new SqlConnection(config.DBConnectStr);
dbconnect.Open();
logger.DebugFormat("connected with HPIMES DB", "OK");
SqlConnection CdsiDbconnect = new SqlConnection(config.CDSIDBConnectStr);
CdsiDbconnect.Open();
logger.DebugFormat("connected with CDSI DB", "OK");
List<string> SnoIdList = SQLStatement.GetCDSISNList(dbconnect, config.OffsetDay);
logger.DebugFormat("Send Link CDSI PO.........................");
List<ProductInfo> AssignedPOList = new List<ProductInfo>();
List<ProductInfo> UPSAssignedPOList = new List<ProductInfo>();
#region send PO Link to CDSI Server
foreach (string snoId in SnoIdList)
{
#region //0.Check ProductID info
ProductInfo productInfo = SQLStatement.GetProductInfo(dbconnect, snoId);
if (string.IsNullOrEmpty(productInfo.CUSTSN) ||
string.IsNullOrEmpty(productInfo.MAC) ||
string.IsNullOrEmpty(productInfo.ATSNAV))
{
#region send email not found PO need mantain PO data
string emptyDataMsg = "";
string DataMsg = "";
if (string.IsNullOrEmpty(productInfo.CUSTSN))
emptyDataMsg = emptyDataMsg + " CustomSN: ";
else
DataMsg = DataMsg + " CustomSN:" + productInfo.CUSTSN;
if (string.IsNullOrEmpty(productInfo.MAC))
emptyDataMsg = emptyDataMsg + " MAC: ";
else
DataMsg = DataMsg + " MAC:" + productInfo.MAC;
if (string.IsNullOrEmpty(productInfo.ATSNAV))
emptyDataMsg = emptyDataMsg + " ATSNAV: ";
else
DataMsg = DataMsg + " ATSNAV:" + productInfo.ATSNAV;
//localmsg = "<br/> CDSI ProductID:" + snoId + " CustomSN:" + (string.IsNullOrEmpty(productInfo.CUSTSN) ? "" : productInfo.CUSTSN) + " " +
// " MAC:" + (string.IsNullOrEmpty(productInfo.MAC) ? "" : productInfo.MAC) + " " +
// " ATSNAV:" + (string.IsNullOrEmpty(productInfo.ATSNAV) ? "" : productInfo.ATSNAV) + " " + " 為空!!! 請檢查設定!!" + "<br/>";
localmsg = "<br/> CDSI ProductID:" + snoId + DataMsg + ";" + emptyDataMsg + "為空!!! 請檢查設定或等過站分配" + emptyDataMsg + "值!!" + "<br/>";
msg = msg + localmsg;
logger.DebugFormat("Data is null or empty {0}", localmsg);
#endregion
continue;
}
#endregion
#region //1.Get PO
string po = SQLStatement.GetSnoPoMo(dbconnect, snoId);
if (string.IsNullOrEmpty(po))
{
#region assign PO
if (config.AutoAssignPO == 1)
{
CDSIPO poInfo = new CDSIPO();
poInfo.ProductID = snoId;
poInfo.MOId = productInfo.MOId;
poInfo.PO = "";
poInfo.DeliveryNo = "";
#region assign PO
if (SQLStatement.AssignPO(dbconnect, config, productInfo, poInfo))
{
logger.DebugFormat( "AssignPO " + poInfo.ProductID + " PO:" + poInfo.PO + " DeliveryNo:" + poInfo.DeliveryNo);
//檢查UPS Device
UPSDatabase db= new UPSDatabase(config.DBConnectStr);
UPSCombinePO combinePO =null;
if (UPSData.DecideUPSCDSI(db, productInfo, poInfo, out combinePO))
{
#region UPS Device
logger.DebugFormat("ProductID:{0} Model:{1} PO:{2} is UPS device!!", productInfo.ProductID, productInfo.Model, productInfo.PO);
string errorText=null;
if (combinePO == null)
{
logger.ErrorFormat("ProductID:{0} Model:{1} PO:{2} is UPS device, no upload UPS PO!!", productInfo.ProductID, productInfo.Model, productInfo.PO);
localmsg = "<br/> CDSI ProductID:" + productInfo.ProductID +
" CustomSN:" + productInfo.CUSTSN +
" MAC:" + productInfo.MAC +
" ATSNAV:" + productInfo.ATSNAV +
" Model:" + productInfo.Model +
" PO:" + productInfo.PO + " 未上傳 至UPS Server ,請上傳此PO至UPS Server <br/>";
msg = msg + localmsg;
}
else
{
if (!UPSData.AssignUPSPO(config, db, productInfo, poInfo, combinePO, out errorText))
{
localmsg = "<br/> CDSI ProductID:" + productInfo.ProductID +
" CustomSN:" + productInfo.CUSTSN +
" MAC:" + productInfo.MAC +
" ATSNAV:" + productInfo.ATSNAV +
" Model:" + productInfo.Model +
" PO:" + productInfo.PO + " 為 UPS Server 報錯 " + errorText + "<br/>";
msg = msg + localmsg;
}
}
#endregion
continue;
}
else
{
logger.DebugFormat("ProductID:{0} Model:{1} PO:{2} is not UPS device!!", productInfo.ProductID, productInfo.Model, productInfo.PO);
#region CDSI SRV
SQLStatement.InsertCDSIPoMo(dbconnect, poInfo);
po = SQLStatement.GetSnoPoMo(dbconnect, snoId);
//Thread.Sleep(config.CDSICmdInterval);
//SendCDSIPOLinkCmd(dbconnect,
// log,
// config,
// productInfo,
// po,
// ref msg);
string msgData = "";
CDSIFile.SendCDSIUpdateCmd(dbconnect,
config,
productInfo,
po,
true,
ref msgData);
productInfo.PO = po;
AssignedPOList.Add(productInfo);
#endregion
}
}
else
{
#region send email not found PO need upload PO data
localmsg = "<br/>" + "CDSI ProductID:" + snoId +
" CustomSN:" + productInfo.CUSTSN +
" MAC:" + productInfo.MAC +
" ATSNAV:" + productInfo.ATSNAV +
" Model:" + productInfo.Model +
" 找不到船務資料，請上傳船務資料!!" + "<br/>";
msg = msg + "<br/>" + localmsg;
logger.DebugFormat( "Not Found DN {0}" ,
localmsg);
#endregion
continue;
}
#endregion
}
else
{
#region send email not found PO need upload PO data
localmsg = "<br/>" + "CDSI ProductID:" + snoId +
" MAC:" + productInfo.MAC +
" ATSNAV:" + productInfo.ATSNAV +
" CustomSN:" + productInfo.CUSTSN +
" Model:" + productInfo.Model +
" 未設定PO, 請至iMES維護頁面CDSI PO" + "<br/>";
msg = msg + "<br/>" + localmsg;
logger.DebugFormat( "Not Found PO {0}",
localmsg);
#endregion
continue;
}
#endregion
}
else
{
CDSIFile.SendCDSIPOLinkCmd(dbconnect,
config,
productInfo,
po,
ref msg);
productInfo.PO = po;
AssignedPOList.Add(productInfo);
}
#endregion
}
#endregion
Thread.Sleep(config.CDSICmdInterval);
logger.DebugFormat("Send CDSI Update & Retrive Asset Tag.........................");
#region Send OMSUpdate & Retrive ; used assigned PO for each send comand to CDSI Server
//foreach (string snoId in SnoIdList)
foreach (ProductInfo productInfo in AssignedPOList)
{
#region //0. Check CDSI Server FactoryPO
int cdsiCategory = SQLStatement.CheckCDSIFactoryPO(CdsiDbconnect,
productInfo.PO);
if (cdsiCategory == 0) // not found CDSI FactoryPO
{
#region not found CDSI FactoryPO
localmsg = "<br/> CDSI ProductID:" + productInfo.ProductID +
" CustomSN:" + productInfo.CUSTSN +
" MAC:" + productInfo.MAC +
" ATSNAV:" + productInfo.ATSNAV +
" Model:" + productInfo.Model +
" PO:" + productInfo.PO + " 在CDSI DB 找不到FactoryPO!!!" + "<br/>";
msg = msg + localmsg;
logger.DebugFormat( "not found CDSI FactoryPO {0}",
localmsg);
#endregion
continue;
}
else
{
localmsg = "CDSI FactoryPO Category: " + (cdsiCategory == 1 ? "Shell PO" : " Nornal PO");
logger.DebugFormat( "CheckCDSIFactoryPO {0}",
localmsg);
}
#endregion
CDSIFile.SendCDSICmd(dbconnect,
config,
productInfo,
productInfo.PO,
false,
ref msg);
}
#endregion
}
catch (Exception e)
{
#region exception handle
msg = e.StackTrace + " " + e.Message;
logger.Error(msg, e);
//log.write(LogType.error, AppDomain.CurrentDomain.Id, "Main", "StackTrace", e.StackTrace);
//log.write(LogType.error, AppDomain.CurrentDomain.Id, "Main", "Message", e.Message);
SendMail.Send(config.FromAddress,
config.ToAddress,
config.CcAddress,
config.MailSubject + "program error",
msg,
config.EmailServer);
#endregion
}
finally
{
#region error send email
if (!string.IsNullOrEmpty(msg))
{
SendMail.Send(config.FromAddress,
config.ToAddress,
config.CcAddress,
config.MailSubject + " 資料有誤!!",
msg,
config.EmailServer);
}
#endregion
}
}
catch (Exception e)
{
logger.Error(e.Message, e);
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
static void Main(string[] args)
{
string methodName = MethodBase.GetCurrentMethod().Name;
logger.InfoFormat("*************************************************************************************************************");
logger.InfoFormat("BEGIN: {0}()", methodName);
try
{
if (!ExecShell.CheckOneProcessRun())
{
return;
}
IList<SendBOMState> ReadySendBOMStateList = new List<SendBOMState> {
SendBOMState.CreatedPoBOM,
SendBOMState.CreatedWithdrawPoBOM,
SendBOMState.ReadySendBOM,
SendBOMState.SendBOMUnSupportedAV,
SendBOMState.SendBOMFail
};
IList<SendBOMState> ReadySendOSIStateList = new List<SendBOMState> {
SendBOMState.GetOSIFail,
SendBOMState.SendOSIFail
};
IList<SendBOMState> ReadyVerifyStateList = new List<SendBOMState> {
SendBOMState.SendOSIOK,
SendBOMState.SendBOMOK,
SendBOMState.SendBOMHolding,
SendBOMState.VerifyFail,
SendBOMState.AlreadySendBOM
};
IList<SendBOMState> DelayVerifyStateList= new List<SendBOMState> {
SendBOMState.SendBOMHolding,
SendBOMState.AlreadySendBOM
};
List<HPPO> hpPoList = new List<HPPO>();
UPSDatabase db = new UPSDatabase(config.DBConnectStr);
//MESData.TestDB(config, db);
//正常case readfile-> CheckData -> SendBOM -> SendOSI -> VerifyPo
List<SAPPO> SAPPOList = UPSPoData.GetSAPPO(config);
if (SAPPOList.Count > 0)
{
#region 處理 SAP UPSPO.txt 與 UPSHPPO
//1.Get UPS Support AV
List<string> avPartList = UPSWS.GetAvailableAV(config);
if (string.IsNullOrEmpty(config.AVPartNo))
{
SQLStatement.InsertUPSSupportAV(config.DBConnectStr, avPartList);
}
//2.Check AV PartNo
List<ModelBom> modelBomList = null;
var needSendSapPoList = UPSPoData.GetAstSapPo(config, SAPPOList, avPartList, out modelBomList);
var warnMsgs = needSendSapPoList.Where(x => !string.IsNullOrEmpty(x.ErrorText)).Select(x => x.ErrorText).ToArray();
if (warnMsgs.Length > 0)
{
string warmStr = string.Join("<br/>", warnMsgs);
sendMail("UPS 料號有誤,請SIE確認!!", warmStr);
}
//3.Get UPSHPPO by HPPO
hpPoList =MESData.AddUPSHPPO(config, db,needSendSapPoList, modelBomList,"UPS");
#endregion
}
else
{
#region 處理UPSHPPO by ReceiveData
//讀取UPSHPPO 資料檢查Fail Case,重送資料
DateTime now = DateTime.Now;
DateTime receiveDate = new DateTime(now.Year, now.Month, now.Day);
receiveDate = receiveDate.AddDays(config.ReceiveDateOffSetDay);
logger.InfoFormat("Check UPSHPPO Receive Data:{0}", receiveDate.ToString("yyyyMMdd"));
hpPoList = MESData.GetUPSHPPO(config, db, receiveDate);
#endregion
}
List<HPPO> sendBomHoldingList = new List<HPPO>();
if (hpPoList != null && hpPoList.Count > 0)
{
var needSendPoList = hpPoList.Where(x => x.PO.Status != SendBOMState.VerifyOK.ToString() || x.isWithdraw).ToList();
foreach (var hpPo in needSendPoList)
{
UPSPOBOM poBom = UPSPoData.GenUPSPoBom(hpPo);
MESData.UpdateUPSModel(config, db, hpPo, poBom, "UPS");//无论sendbom 是否ok,都需要记录UPSModel,否则UPS 机型产线已经过完50站。
#region Check Status and requset UPS Web service
if (ReadySendBOMStateList.Contains(poBom.State))
{
UPSPoData.SendCreatePOStatus(config, poBom);
}
else if (ReadySendOSIStateList.Contains(poBom.State))
{
UPSPoData.SendOSIStatus(config, poBom);
}
else if (ReadyVerifyStateList.Contains(poBom.State))
{
UPSPoData.SendVerifyPOStatus(config, poBom);
}
else
{
logger.InfoFormat("No Handle UPSHPPO HPPO:{0} CustPO:{1} Status:{2}", poBom.HPPO, poBom.CustPO, poBom.State.ToString());
continue;
}
#endregion
MESData.UpdateUPSHPPO(config, db, hpPo, poBom,"UPS");
if (DelayVerifyStateList.Contains(poBom.State))
{
sendBomHoldingList.Add(hpPo);
}
}
#region Check SendBomHolding status, verify again
//var sendBomHoldingList = needSendPoList.Where(x => DelayVerifyStateList.Contains( x.PO.Status)));
if (sendBomHoldingList.Count() > 0)
{
//waiting UPS Generate AST Number
logger.InfoFormat("Waiting {0} minutes then verify po again", config.DelayVerifyPo.ToString());
Thread.Sleep(config.DelayVerifyPo*60 *1000);
foreach (var hpPo in sendBomHoldingList)
{
UPSPOBOM poBom = UPSPoData.GenUPSPoBom(hpPo);
UPSPoData.SendVerifyPOStatus(config, poBom);
MESData.UpdateUPSHPPO(config, db, hpPo, poBom, "UPS");
}
}
#endregion
//Send Warning message
string errorFormat="POType:{0} HPPO:{1} CustPO:{2} Qty:{3} Status:{4} ErrorText:{5}";
var wanMsgList = needSendPoList.Where(x => !string.IsNullOrEmpty(x.PO.ErrorText))
.Select(x => string.Format(errorFormat,
x.PO.POType,
x.PO.HPPO,
x.PO.EndCustomerPO,
x.PO.Qty,
x.PO.Status, x.PO.ErrorText)).ToArray();
if (wanMsgList.Length > 0)
{
string warmStr = string.Join("<br/>", wanMsgList);
sendMail("UPS AV Part 資料有誤,請確認!!", warmStr);
}
}
else
{
logger.InfoFormat("No SAP PO Data");
sendMail("無SAP 收單資料", "無SAP 收單資料");
return;
}
}
catch (Exception e)
{
logger.Error(methodName, e);
sendMail("程式報錯", e.Message + Environment.NewLine+e.StackTrace);
}
finally
{
logger.InfoFormat("END: {0}()", methodName);
}
}
public static IList<UPSHPPO> GetUPSHPPO(AppConfig config,List<string> poList)
{
string methodName = MethodBase.GetCurrentMethod().Name;
logger.InfoFormat("BEGIN: {0}()", methodName);
try
{
UPSDatabase db = new UPSDatabase(config.DBConnectStr);
return (from po in db.UPSHPPOEntity
join d in poList on po.HPPO equals d
select po).ToList();
}
catch (Exception e)
{
logger.Error(methodName, e);
throw e;
}
finally
{
logger.InfoFormat("END: {0}()", methodName);
}
}
