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 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 public bool AssignPO(SqlConnection connect,
AppConfig config,
ProductInfo prodInfo,
CDSIPO poInfo)
{
bool selectedPo = false;
List<Delivery> poList = GetAvailableDelivery(connect,
prodInfo,
config.ShipDateOffSetDay);
if (poList.Count == 0) return selectedPo;
int availableQty = 0;
foreach (Delivery po in poList)
{
availableQty = po.Qty - GetAssignPOQty(connect, po.DeliveryNo, po.PO);
if (availableQty > 0)
{
poInfo.PO = po.PO;
poInfo.DeliveryNo = po.DeliveryNo;
prodInfo.PO = po.PO;
selectedPo = true;
break;
}
}
return selectedPo;
}
static public bool AssignMO(SqlConnection connect,
AppConfig config,
ProductInfo prodInfo,
CDSIPO poInfo)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
bool selectedMo = false;
List<MO> moList = GetAvailableMO(connect,
prodInfo,
config.MOStartDateOffSetDay,
config.MOUdtOffSetDay);
if (moList.Count == 0) return selectedMo;
int availableQty = 0;
foreach (MO mo in moList)
{
availableQty = mo.Qty - GetAssignMOQty(connect, mo.MOId);
if (availableQty > 0)
{
poInfo.MOId = mo.MOId;
selectedMo = true;
break;
}
}
return selectedMo;
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}
static public void InsertCDSIPoMo(SqlConnection connect,
SqlTransaction transaction,
CDSIPO poInfo)
{
string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
logger.DebugFormat("BEGIN: {0}()", methodName);
try
{
SqlCommand dbCmd = new SqlCommand();
//SqlCommand dbCmd = connect.CreateCommand();
dbCmd.Connection = connect;
dbCmd.Transaction = transaction;
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = @"insert dbo.SnoDet_PoMo(SnoId, Mo, PO, POItem, Delivery, PLT, BoxId, Remark, Editor, Cdt, Udt)
values(@SnoId, @Mo, @PO, '00001', @Delivery, '', '', '', 'CDSI', GETDATE(), GETDATE())";
SQLHelper.createInputSqlParameter(dbCmd, "@SnoId", 15, poInfo.ProductID);
SQLHelper.createInputSqlParameter(dbCmd, "@Mo", 32, poInfo.MOId);
SQLHelper.createInputSqlParameter(dbCmd, "@PO", 32, poInfo.PO);
SQLHelper.createInputSqlParameter(dbCmd, "@Delivery", 32, poInfo.DeliveryNo);
logger.DebugFormat("SQL {0}", dbCmd.CommandText);
logger.DebugFormat("SQL {0} {1}", "@SnoId", poInfo.ProductID);
logger.DebugFormat("SQL {0} {1}", "@Mo", poInfo.MOId);
logger.DebugFormat("SQL {0} {1}", "@PO", poInfo.PO);
logger.DebugFormat("SQL {0} {1}", "@Delivery", poInfo.DeliveryNo);
dbCmd.ExecuteNonQuery();
}
catch (Exception e)
{
logger.Error(e.Message, e);
throw;
}
finally
{
logger.DebugFormat("END: {0}()", methodName);
}
}