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