public static void InsertTempMAWB(string connectionDB, int dbIndex, NotifyMAWB MAWBItems, string State, string ErrorDescr, string BatchId)
{
string SerialNumber = (MAWBItems.SerialNumber == null ? "" : MAWBItems.SerialNumber);
string MAWB = (MAWBItems.MAWB == null ? "" : MAWBItems.MAWB);
string Plant = (MAWBItems.Plant == null ? "" : MAWBItems.Plant);
string DN = (MAWBItems.DN == null ? "" : MAWBItems.DN);
string Items = (MAWBItems.Items == null ? "" : MAWBItems.Items);
string Remark1 = (MAWBItems.Remark1 == null ? "" : MAWBItems.Remark1);
string DeclarationId = (MAWBItems.Declaration == null ? "" : MAWBItems.Declaration);
string OceanContainer = (MAWBItems.Containerid == null ? "" : MAWBItems.Containerid);
string strSQL = @"if not Exists (select * from MAWB_Master where SerialNumber=@SerialNumber and MAWB=@MAWB)
begin
insert into MAWB_Master(SerialNumber, MAWB, State, ErrorDescr, BatchId, Cdt, Udt)
values (@SerialNumber, @MAWB, @State, @ErrorDescr, @BatchId, @Now, @Now)
end
insert into MAWB_Detail(SerialNumber, Plant, DN, Items, DeclarationId, OceanContainer, Remark1, Cdt)
values (@SerialNumber, @Plant, @DN, @Items, @DeclarationId, @OceanContainer, @Remark1, @Now)";
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@SerialNumber", 40, SerialNumber),
SQLHelper.CreateSqlParameter("@MAWB", 32, MAWB),
SQLHelper.CreateSqlParameter("@Plant", 4, Plant),
SQLHelper.CreateSqlParameter("@DN", 10, DN),
SQLHelper.CreateSqlParameter("@Items", 6, Items),
SQLHelper.CreateSqlParameter("@Remark1", 25, Remark1),
SQLHelper.CreateSqlParameter("@State", 16, State),
SQLHelper.CreateSqlParameter("@ErrorDescr", 255, ErrorDescr),
SQLHelper.CreateSqlParameter("@Now", DateTime.Now),
SQLHelper.CreateSqlParameter("@BatchId", 32, BatchId),
SQLHelper.CreateSqlParameter("@DeclarationId", 64, DeclarationId),
SQLHelper.CreateSqlParameter("@OceanContainer", 64, OceanContainer));
}
public List<NotifyMAWBResponse> NotifyMAWB(NotifyMAWB[] MAWBItems)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
//切換 MAWB temp data
string SwitchMAWBTempDataToCQ = string.IsNullOrEmpty(WebConfigurationManager.AppSettings["SwitchMAWBTempDataToCQ"]) ? "" : WebConfigurationManager.AppSettings["SwitchMAWBTempDataToCQ"];
string connectionDB_BK = "";
if (SwitchMAWBTempDataToCQ == "Y")
{
connectionDB_BK = "SD_DBServer_BK_CQ";
}
else {
connectionDB_BK = "SD_DBServer_BK";
}
string BatchId = string.Format("{0:yyyyMMddHHmmssfff}", DateTime.Now);
//1.檢查必要的input parameter
Execute.ValidateParameter(connectionDB_BK, MAWBItems, BatchId);
//3.執行DB insert
Execute.Process(connectionDB_BK, MAWBItems, BatchId);
//4.Build Response Message
List<NotifyMAWBResponse> pgiresponse = Execute.BuildResponseMsg(connectionDB_BK, BatchId);
return pgiresponse;
}
catch (Exception e)
{
logger.Error(MethodBase.GetCurrentMethod(), e);
// UTL.SendMail("test", e.Message);
//4.Build Response Error Message
List<NotifyMAWBResponse> ResponseList = new List<NotifyMAWBResponse>();
foreach (NotifyMAWB item in MAWBItems)
{
string connectionDB = "SD_DBServer";
int dbIndex = 0;
NotifyMAWBResponse response = new NotifyMAWBResponse();
string SerialNumber = item.SerialNumber;
string MAWB = item.MAWB;
string State = "F";
response.SerialNumber = SerialNumber;
response.MAWB = MAWB;
response.Result = State;
response.ErrorText = e.ToString().Substring(0,255);
ResponseList.Add(response);
SQL.InsertTxnDataLog_DB(connectionDB, dbIndex,
EnumMsgCategory.Response,
"NotifyMAWBResponse",
string.IsNullOrEmpty(item.MAWB) ? "" : item.MAWB,
"",
string.IsNullOrEmpty(item.SerialNumber) ? "" : item.SerialNumber,
"",
"",
EnumMsgState.Fail,
e.ToString().Substring(0, 255));
}
return ResponseList;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//3.執行DB insert
public static void Process(string connectionDB_BK, NotifyMAWB[] MAWBItems, string BatchId)
{
string connectionDB = "SD_DBServer";
//string connectionDB_BK = "SD_DBServer_BK";
//string connectionDB_BK = "SD_DBServer_BK_CQ";
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
//Log success message to PGILog.
string dataType = "MAWB";
string State = "Success";
string ErrorDescr = "";
//Get DB Connection string
List<SAPMAWBDef> DefList = SQL.GetSAPMawbDef(connectionDB, dbIndex, dataType);
//Get MAWB items
List<MAWBMaster> MAWBMaster = SQL.GetMawbMaster(connectionDB_BK, dbIndex, BatchId);
foreach (MAWBMaster itemM in MAWBMaster)
{
if (itemM.State != "F") {
//Get MAWB detail items
List<MAWBDetail> MAWBDetail = SQL.GetMawbDetail(connectionDB_BK, dbIndex, itemM.SerialNumber);
foreach (SAPMAWBDef DefItem in DefList)
{
int itemcnt = 0;
int itemcnt_udDN = 0;
int itemcnt_udAll = 0;
foreach (MAWBDetail itemD in MAWBDetail)
{
if (itemD.Plant == DefItem.PlantCode)
{
//MAWB Case 1 : (TSB, FJ)
//刪除時，依據@MAWB & DN & Items為條件，將上次insert的先刪除
//新增時，取SAP.MAWB下滑線前的值=@MAWB，insert @MAWB 至MAWB表
if (DefItem.VolumnUnit == "DbyDN")
{
BaseLog.LoggingInfo(logger, "MAWB Type:[DbyDN] for TSB,FJ");
BaseLog.LoggingInfo(logger, "SAPWeightDef: \r\n{0}", DefItem.DBName+", Descr:"+DefItem.Descr);
//if (itemcnt_udDN == 0)
//{
// string LastMAWBSerialNumber = SQL.GetLastMAWB(connectionDB_BK, dbIndex, itemM.MAWB, itemM.Cdt);
// //取得此MAWB最後更新的時間, 以及DN+Item
// //若有資料，則先刪掉MAWB中，這些DN的資料
// if (LastMAWBSerialNumber != "")
// {
// List<MAWBDetail> LastMAWBDetail = SQL.GetMawbDetail(connectionDB_BK, dbIndex, LastMAWBSerialNumber);
// foreach (MAWBDetail itemL in LastMAWBDetail)
// {
// SQL.DeleteMAWBbyDelivery(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB, itemL.DN, itemL.Items, itemcnt_udDN);
// }
// }
//}
//刪除:若@MAWB+DN不存在於MAWB表中，則刪除MAWB中，DN對應的所有MAWB數據
SQL.DeleteMAWBbyDelivery(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB, itemD.DN, itemD.Items, itemcnt_udDN);
//新增:若@MAWB+DN不存在於MAWB表中，新增@MAWB & DN數據
SQL.UpdateMAWB_UnderLineByDN(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB,
itemD.DN, itemD.Items, itemcnt_udDN, itemM.Cdt);
itemcnt_udDN++;
}
else {
//MAWB Case 2: (DELL)
//刪除時，同時刪掉MAWB表中 MAWB=@MAWB or MAWB=SAP.MAWB之資料
//新增時，取SAP.MAWB下滑線前的值=@MAWB，insert @MAWB 至MAWB表
if (DefItem.VolumnUnit == "DbyAll")
{
BaseLog.LoggingInfo(logger, "MAWB Type:[DbyAll] for DELL");
BaseLog.LoggingInfo(logger, "SAPWeightDef: \r\n{0}", DefItem.DBName + ", Descr:" + DefItem.Descr);
SQL.DeleteMAWB(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB, itemcnt_udAll);
SQL.UpdateMAWB_UnderLine(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB,
itemD.DN, itemD.Items, itemcnt_udAll, itemM.Cdt);
itemcnt_udAll++;
}
else if (DefItem.VolumnUnit == "Declaration") {
//MAWB Case 4: (HP, ASUS)
//新增時，取SAP.MAWB=@MAWB，insert @MAWB 至MAWB表
//刪除時，同時刪掉MAWB表中 MAWB=@MAWB or MAWB=SAP.MAWB之資料
//新增報關單號、海運櫃號
BaseLog.LoggingInfo(logger, "MAWB Type:[DbyDN] for HP, ASUS");
BaseLog.LoggingInfo(logger, "SAPWeightDef: \r\n{0}", DefItem.DBName + ", Descr:" + DefItem.Descr);
//新增MAWB至各Plant的MAWB Table
SQL.UpdateMAWB_Declaration(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB,
itemD.DN, itemD.Items, itemcnt, itemM.Cdt, itemD.DeclarationId, itemD.OceanContainer, itemD.HAWB);
itemcnt++;
}
//MAWB Case 3: (HP_FRU, WYSE)
//新增時，取SAP.MAWB=@MAWB，insert @MAWB 至MAWB表
//刪除時，同時刪掉MAWB表中 MAWB=@MAWB or MAWB=SAP.MAWB之資料
else
{
BaseLog.LoggingInfo(logger, "MAWB Type:[DbyDN] for HP, HP_FRU, WYSE");
BaseLog.LoggingInfo(logger, "SAPWeightDef: \r\n{0}", DefItem.DBName + ", Descr:" + DefItem.Descr);
//新增MAWB至各Plant的MAWB Table
SQL.UpdateMAWB(DefItem.ConnectionStr, DefItem.DBName, itemM.MAWB,
itemD.DN, itemD.Items, itemcnt, itemM.Cdt);
itemcnt++;
}
}
}
}
}
//更新MAWB狀態
SQL.UpdateTempMAWB(connectionDB_BK, dbIndex, itemM.SerialNumber, itemM.MAWB, State, ErrorDescr);
}
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//1.檢查必要的input parameter
public static void ValidateParameter(string connectionDB_BK, NotifyMAWB[] MAWBItems, string BatchId)
{
string connectionDB = "SD_DBServer";
//string connectionDB_BK = "SD_DBServer_BK";
//string connectionDB_BK = "SD_DBServer_BK_CQ";
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
List<string> NotNullItemList = new List<string> {"SerialNumber",
"MAWB",
"Plant",
"DN",
"Items",
"Declaration"};
foreach (NotifyMAWB item in MAWBItems)
{
logger.DebugFormat("NotifyMAWB: \r\n{0}", ObjectTool.ObjectTostring(item));
SQL.InsertTxnDataLog_DB(connectionDB, dbIndex,
EnumMsgCategory.Receive,
"NotifyMAWB",
string.IsNullOrEmpty(item.MAWB) ? "" : item.MAWB,
"",
string.IsNullOrEmpty(item.SerialNumber) ? "" : item.SerialNumber,
"",
"",
EnumMsgState.Received,
item.Declaration+","+item.Containerid);
SQL.InsertTempMAWB(connectionDB_BK, dbIndex, item, "Receive", "", BatchId);
}
foreach (NotifyMAWB item in MAWBItems)
{
string SerialNumber = item.SerialNumber;
string MAWB = item.MAWB;
//Check null data
string className = item.GetType().BaseType.Name;
if (className == "Object")
{ className = item.GetType().Name; }
string title = "These columns of " + className + " are null or no data : ";
string error = "";
foreach (string itemcolumn in NotNullItemList)
{
if (string.IsNullOrEmpty(GetValueByType(itemcolumn, item).Trim()))
{ error = error + itemcolumn + ","; }
}
if (error != "")
{
error = title + error;
//Update MAWB Status.
string State = "Fail";
SQL.UpdateTempMAWB(connectionDB_BK, dbIndex, SerialNumber, MAWB, State, error);
}
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}