Esempio n. 1
0
        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));
        }
Esempio n. 2
0
        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);
            }
        
        }
Esempio n. 3
0
        //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);
            }
        }
Esempio n. 4
0
        //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);
            }            
        }