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); } }