public static string CheckECOModelExists(string connectionStr, string DBName, NotifyECOModel result, string PlantCode) { string ECRNo = (result.ECRNo == null ? "" : result.ECRNo); string Model = (result.Model == null ? "" : result.Model); string connectStr = string.Format(connectionStr, DBName); string strSQL = @"if exists(select top 1 ECRNo from ECOModel where ECRNo=@ECRNo and Model=@Model and Plant=@Plant) begin set @ret = 'Y' end else begin select @ret= 'N' end"; SqlParameter ParaRet = SQLHelper.CreateSqlParameter("@ret", 8, "", ParameterDirection.InputOutput); SQLHelper.ExecuteDataFill(connectStr, System.Data.CommandType.Text, strSQL, SQLHelper.CreateSqlParameter("@ECRNo", 20, ECRNo), SQLHelper.CreateSqlParameter("@Model", 20, Model), SQLHelper.CreateSqlParameter("@Plant", 20, PlantCode), ParaRet); return ParaRet.Value.ToString().Trim(); }
//1.檢查必要的input parameter public static void ValidateParameter(string connectionDB, NotifyECOModel[] results) { int dbIndex = 0; string methodName = MethodBase.GetCurrentMethod().Name; BaseLog.LoggingBegin(logger, methodName); try { List<string> NotNullItemList = new List<string> {"TxnId", "Plant", "ECRNo", "Model"}; foreach (NotifyECOModel item in results) { logger.DebugFormat("NotifyECOModel: \r\n{0}", ObjectTool.ObjectTostring(item)); //log receive record to TxnDataLog SQL.InsertTxnDataLog_DB(connectionDB, dbIndex, EnumMsgCategory.Receive, "NotifyECOModel", string.IsNullOrEmpty(item.ECRNo) ? "" : item.ECRNo, string.IsNullOrEmpty(item.Model) ? "" : item.Model, string.IsNullOrEmpty(item.TxnId) ? "" : item.TxnId, "", "", EnumMsgState.Received, item.Plant + ";" + item.ECONo); } foreach (NotifyECOModel item in results) { //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; throw new Exception(error); } } } catch (Exception e) { BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e); throw e; } finally { BaseLog.LoggingEnd(logger, methodName); } }
public static string CheckECOModelInWIP(string connectionStr, string DBName, NotifyECOModel result) { string Model = (result.Model == null ? "" : result.Model); string connectStr = string.Format(connectionStr, DBName); // string strSQL = @"if exists(select top 1 a.ProductID // from Product a // inner join ProductStatus b on a.ProductID = b.ProductID and // b.Station not in (select Value from ConstValueType where Type='NoneWIPStationForECOModel') // where a.Model = @Model) // begin // if not exists (select * from ConstValueType where Type='TravelCardHoldModel' and Value like '%'+@Model+'%') // begin // insert into ConstValueType(Type, Value, Description, Editor, Cdt, Udt) // values ('TravelCardHoldModel', '^'+@Model+'$', 'ECOModel', 'SAP', @Now, @Now) // // set @ret = 'Hold' // end // else // begin // set @ret = 'IsHold' // end // end // else // begin // set @ret = 'NoneWIP' // end"; string strSQL = @"if not exists (select * from ConstValueType where Type='TravelCardHoldModel' and Value like '%'+@Model+'%') begin insert into ConstValueType(Type, Value, Description, Editor, Cdt, Udt) values ('TravelCardHoldModel', '^'+@Model+'$', 'ECOModel', 'SAP', @Now, @Now) set @ret = 'Hold' end else begin set @ret = 'IsHold' end"; SqlParameter ParaRet = SQLHelper.CreateSqlParameter("@ret", 8, "", ParameterDirection.InputOutput); SQLHelper.ExecuteDataFill(connectStr, System.Data.CommandType.Text, strSQL, SQLHelper.CreateSqlParameter("@Model", 20, Model), SQLHelper.CreateSqlParameter("@Now", DateTime.Now), ParaRet); return ParaRet.Value.ToString().Trim(); }
public List<NotifyECOModelResponse> NotifyECOModel(NotifyECOModel[] ECOModelItems) { string connectionDB = "PP_DBServer"; string methodName = MethodBase.GetCurrentMethod().Name; BaseLog.LoggingBegin(logger, methodName); try { //1.檢查必要的input parameter Execute.ValidateParameter(connectionDB, ECOModelItems); //3.執行DB insert Execute.Process(connectionDB, ECOModelItems); //4.Build Response Message List<NotifyECOModelResponse> ecoresponse = Execute.BuildResponseMsg(connectionDB, ECOModelItems, true, ""); return ecoresponse; } catch (Exception e) { logger.Error(MethodBase.GetCurrentMethod(), e); // UTL.SendMail("test", e.Message); //4.Build Response Error Message List<NotifyECOModelResponse> ecoresponse = Execute.BuildResponseMsg(connectionDB, ECOModelItems, false , e.Message); /* List<NotifyECOModelResponse> ResponseList = new List<NotifyECOModelResponse>(); foreach (NotifyECOModel item in ECOModelItems) { NotifyECOModelResponse response = new NotifyECOModelResponse(); response.TxnId = (item.TxnId == null ? "" : item.TxnId); response.ECONo = (item.ECONo == null ? "" : item.ECONo); response.Model = (item.Model == null ? "" : item.Model); response.Result = "F"; response.Message = e.Message; ResponseList.Add(response); } */ return ecoresponse; } finally { BaseLog.LoggingEnd(logger, methodName); } }
public static void InsertECOModel(string connectionStr, string DBName, NotifyECOModel result, string PlantCode, string ECOStatus) { string ECRNo = (result.ECRNo == null ? "" : result.ECRNo); string ECONo = (result.ECONo == null ? "" : result.ECONo); string Model = (result.Model == null ? "" : result.Model); string ValidateFromDate = (result.ValidateFromDate == null ? "" : result.ValidateFromDate); string connectStr = string.Format(connectionStr, DBName); string strSQL = @"if not exists(select top 1 ECRNo from ECOModel where ECRNo=@ECRNo and Model=@Model and Plant=@Plant) begin insert into ECOModel(Plant, ECRNo, ECONo, Model, ValidateFromDate, PreStatus, Status, Editor, Cdt, Udt) values (@Plant, @ECRNo, @ECONo, @Model, @ValidateFromDate,'', @Status, 'SAP', @Now, @Now) end"; SQLHelper.ExecuteDataFill(connectStr, System.Data.CommandType.Text, strSQL, SQLHelper.CreateSqlParameter("@ECRNo", 32, ECRNo), SQLHelper.CreateSqlParameter("@ECONo", 32, ECONo), SQLHelper.CreateSqlParameter("@Model", 32, Model), SQLHelper.CreateSqlParameter("@Plant", 20, PlantCode), SQLHelper.CreateSqlParameter("@ValidateFromDate", 32, ValidateFromDate), SQLHelper.CreateSqlParameter("@Status", 20, ECOStatus), SQLHelper.CreateSqlParameter("@Now", DateTime.Now)); }
//3.執行DB insert public static void Process(string connectionDB, NotifyECOModel[] results) { int dbIndex = 0; string methodName = MethodBase.GetCurrentMethod().Name; BaseLog.LoggingBegin(logger, methodName); try { foreach (NotifyECOModel item in results) { string ECRNo = item.ECRNo; string Model = item.Model; string[] PlantCode = item.Plant.Split(new char[] { ',', ';' }); string dataType = "ECOModel"; for (int i = 0; i < PlantCode.Length; i++) { string Plant = PlantCode[i].ToUpper(); if (PlantCode[i].Trim() != "") { //根據PlantCode獲取連線定義 List<SAPMAWBDef> DefList = SQL.GetSAPMawbDefByPlantCode(connectionDB, dbIndex, dataType, Plant); //若有獲取連線定義,進行資料處理;否則By Pass Data if (DefList.Count != 0) { foreach (SAPMAWBDef DefItem in DefList) { string IsExists = SQL.CheckECOModelExists(DefItem.ConnectionStr, DefItem.DBName, item, DefItem.PlantCode); if (IsExists == "Y") { BaseLog.LoggingInfo(logger, "The Data is exists, no need process ! ECRNo: \r\n{0}", item.ECRNo + ", Model:" + item.Model + ", Plant:" + DefItem.PlantCode); } else { //Check WIP string HoldState = SQL.CheckECOModelInWIP(DefItem.ConnectionStr, DefItem.DBName, item); if (HoldState == "Hold") BaseLog.LoggingInfo(logger, "The Model is Hold ! ECRNo: \r\n{0}", item.ECRNo + ", Model:" + item.Model + ", Plant:" + Plant); else if (HoldState == "IsHold") BaseLog.LoggingInfo(logger, "The Model is already Hold ! ECRNo: \r\n{0}", item.ECRNo + ", Model:" + item.Model + ", Plant:" + Plant); else BaseLog.LoggingInfo(logger, "The Model is not in WIP ! ECRNo: \r\n{0}", item.ECRNo + ", Model:" + item.Model + ", Plant:" + Plant); //檢查ECRNo + Model + Plant 是否已存在ECOModel表,不存在則新增,存在則不處理 SQL.InsertECOModel(DefItem.ConnectionStr, DefItem.DBName, item, DefItem.PlantCode, "HoldTravelCard"); } } } else BaseLog.LoggingInfo(logger, "The data of this PlantCode no need process ! ECRNo: \r\n{0}", item.ECRNo + ", Model:" + item.Model + ", Plant:" + Plant); } } } } catch (Exception e) { BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e); throw e; } finally { BaseLog.LoggingEnd(logger, methodName); } }
//4.Build Response message structure public static List<NotifyECOModelResponse> BuildResponseMsg(string connectionDB, NotifyECOModel[] results, bool IsOK, string Message) { int dbIndex = 0; string methodName = MethodBase.GetCurrentMethod().Name; BaseLog.LoggingBegin(logger, methodName); List<NotifyECOModelResponse> ResponseList = new List<NotifyECOModelResponse>(); try { if (IsOK) { foreach (NotifyECOModel item in results) { //BaseLog.LoggingInfo(logger, "TxnId: \r\n{0}", ObjectTool.ObjectTostring(item.TxnId)); NotifyECOModelResponse response = new NotifyECOModelResponse(); response.TxnId = item.TxnId; response.ECRNo = item.ECRNo; response.Model = item.Model; response.Result = "T"; response.Message = Message; ResponseList.Add(response); logger.DebugFormat("NotifyECOModelResponse: \r\n{0}", ObjectTool.ObjectTostring(response)); //log success response record to TxnDataLog SQL.InsertTxnDataLog_DB(connectionDB, dbIndex, EnumMsgCategory.Response, "NotifyECOModelResponse", string.IsNullOrEmpty(item.ECRNo) ? "" : item.ECRNo, string.IsNullOrEmpty(item.Model) ? "" : item.Model, string.IsNullOrEmpty(item.TxnId) ? "" : item.TxnId, "", "", EnumMsgState.Success, item.Plant +";" + item.ECONo); } } else { foreach (NotifyECOModel item in results) { //BaseLog.LoggingInfo(logger, "TxnId: \r\n{0}", ObjectTool.ObjectTostring(item.TxnId)); NotifyECOModelResponse response = new NotifyECOModelResponse(); response.TxnId = item.TxnId; response.ECRNo = item.ECRNo; response.Model = item.Model; response.Result = "F"; response.Message = Message; ResponseList.Add(response); logger.DebugFormat("NotifyECOModelResponse: \r\n{0}", ObjectTool.ObjectTostring(response)); //log fail response record to TxnDataLog SQL.InsertTxnDataLog_DB(connectionDB, dbIndex, EnumMsgCategory.Response, "NotifyECOModelResponse", string.IsNullOrEmpty(item.ECRNo) ? "" : item.ECRNo, string.IsNullOrEmpty(item.Model) ? "" : item.Model, string.IsNullOrEmpty(item.TxnId) ? "" : item.TxnId, "F", Message, EnumMsgState.Fail, item.Plant + ";" + item.ECONo); } } return ResponseList; } catch (Exception e) { BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e); throw e; } finally { BaseLog.LoggingEnd(logger, methodName); } }