public static void UpdatePGIStatus(string connectionDB, int dbIndex, PGIStatus result)
{
string SerialNumber = result.SerialNumber;
string Plant = result.Plant;
string EventType = result.EventType;
string ID = result.ID;
string DNType = result.Type;
string PGI_Date = result.PGI_Date;
string strSQL = @"if not exists(select * from PGIStatus where DN_Shipment=@ID and Plant=@Plant)
begin
insert into PGIStatus(Plant, DN_Shipment, [Type], [Status], PGI_Date, Cdt, Udt)
values (@Plant, @ID, @Type, @Status, @PGI_Date, @Now, @Now)
end
else
begin
update PGIStatus
set [Type]=@Type, [Status]=@Status, PGI_Date=@PGI_Date, Udt=@Now
where DN_Shipment=@ID and Plant=@Plant
end";
//SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_HISTORY(1),
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@Plant", 4, Plant),
SQLHelper.CreateSqlParameter("@ID", 10, ID),
SQLHelper.CreateSqlParameter("@Type", 1, DNType),
SQLHelper.CreateSqlParameter("@Status", 6, EventType),
SQLHelper.CreateSqlParameter("@PGI_Date", 25, PGI_Date),
SQLHelper.CreateSqlParameter("@Now", DateTime.Now));
}
//1.檢查必要的input parameter
public static void ValidateParameter(string connectionDB, PGIStatus[] results)
{
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
List<string> NotNullItemList = new List<string> {"SerialNumber",
"Plant",
"EventType",
"ID",
"Type",
"PGI_Date"};
foreach (PGIStatus item in results)
{
logger.DebugFormat("PGIStatus: \r\n{0}", ObjectTool.ObjectTostring(item));
}
foreach (PGIStatus 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;
//Log error message to PGILog.
string State = "Fail";
SQL.InsertPGILog(connectionDB, dbIndex, item, State, error);
}
//ObjectTool.CheckNullData(NotNullItemList, item);
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
public static void UpdatePGIDeliveryStatus(string connectionDB, int dbIndex, PGIStatus result, string PGIDnStatus)
{
string ID = result.ID+"%";
string strSQL = @"update Delivery
set Status=@PGIDnStatus, Editor='SAPPGI', Udt=@Now
where DeliveryNo like @ID and Status in ('88', '98', '87')";
//SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_HISTORY(1),
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@ID", 16, ID),
SQLHelper.CreateSqlParameter("@PGIDnStatus", 6, PGIDnStatus),
SQLHelper.CreateSqlParameter("@Now", DateTime.Now));
}
public List<PGIStatusResponse> PGIStatus(PGIStatus[] PGIItems)
{
string connectionDB_BK = "SD_DBServer_BK";
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
//1.檢查必要的input parameter
Execute.ValidateParameter(connectionDB_BK, PGIItems);
//3.執行DB insert
Execute.Process(connectionDB_BK, PGIItems);
//4.Build Response Message
List<PGIStatusResponse> pgiresponse = Execute.BuildResponseMsg(connectionDB_BK, PGIItems);
return pgiresponse;
}
catch (Exception e)
{
logger.Error(MethodBase.GetCurrentMethod(), e);
// UTL.SendMail("test", e.Message);
//4.Build Response Error Message
List<PGIStatusResponse> ResponseList = new List<PGIStatusResponse>();
foreach (PGIStatus item in PGIItems)
{
PGIStatusResponse response = new PGIStatusResponse();
string SerialNumber = "";
string State = "F";
response.SerialNumber = SerialNumber;
response.Result = State;
ResponseList.Add(response);
}
return ResponseList;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
public static void UpdatePGIDeliveryStatusIncludeShipDate(string connectionDB, int dbIndex, PGIStatus result, string PGIDnStatus)
{
string ID = result.ID+"%";
string PGI_Date = result.PGI_Date;
string strSQL = @"update Delivery
set ShipDate = Convert(date, @PGI_Date),
Status= case when (Status in ('88', '98', '87'))
then @PGIDnStatus
else Status
end,
Editor='SAPPGI', Udt=@Now
where DeliveryNo like @ID";
//SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_HISTORY(1),
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@ID", 16, ID),
SQLHelper.CreateSqlParameter("@PGI_Date", 32, PGI_Date),
SQLHelper.CreateSqlParameter("@PGIDnStatus", 6, PGIDnStatus),
SQLHelper.CreateSqlParameter("@Now", DateTime.Now));
}
//3.執行DB insert
public static void Process(string connectionDB_BK, PGIStatus[] results)
{
string connectionDB = "SD_DBServer";
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
//Log success message to PGILog.
string State = "Success";
string ErrorDescr = "";
string PGIDnStatus = string.IsNullOrEmpty(WebConfigurationManager.AppSettings["PGIDeliveryStatus"]) ? "" : WebConfigurationManager.AppSettings["PGIDeliveryStatus"];
string UpdateDeliveryByPlant = string.IsNullOrEmpty(WebConfigurationManager.AppSettings["UpdateDeliveryByPlant"]) ? "" : WebConfigurationManager.AppSettings["UpdateDeliveryByPlant"];
string UpdateDnShipDateByPlant = string.IsNullOrEmpty(WebConfigurationManager.AppSettings["UpdateDnShipDateByPlant"]) ? "" : WebConfigurationManager.AppSettings["UpdateDnShipDateByPlant"];
//string[] PlantList = UpdateDeliveryByPlant.Split(new char[] { ',', ';' });
BaseLog.LoggingInfo(logger, "The Setting of Plant need to update Delivery.Status! Plant: \r\n{0}", UpdateDeliveryByPlant);
foreach (PGIStatus item in results)
{
string SerialNumber = item.SerialNumber;
string LogState = SQL.GetPGIState(connectionDB_BK, dbIndex, SerialNumber);
if (LogState != "F")
{
SQL.UpdatePGIStatus(connectionDB_BK, dbIndex, item);
SQL.InsertPGILog(connectionDB_BK, dbIndex, item, State, ErrorDescr);
if (UpdateDeliveryByPlant.Contains(item.Plant)) {
// if plant setting is exists, update Delivery.Status by Plant setting
if (item.EventType == "PGI")
{
// if plant setting is exists, update Delivery.ShipDate = PGI_Date
if (UpdateDnShipDateByPlant.Contains(item.Plant))
{
SQL.UpdatePGIDeliveryStatusIncludeShipDate(connectionDB, 1, item, PGIDnStatus);
}
else {
SQL.UpdatePGIDeliveryStatus(connectionDB, 1, item, PGIDnStatus);
}
}
else
BaseLog.LoggingInfo(logger, "The PGI Status no need to update Delivery.Status ! Plant: \r\n{0}", item.Plant + ", EventType :" + item.EventType);
}
else
BaseLog.LoggingInfo(logger, "The Plant no need to update Delivery.Status ! Plant: \r\n{0}", item.Plant + ", EventType :" + item.EventType);
}
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//4.Build Response message structure
public static List<PGIStatusResponse> BuildResponseMsg(string connectionDB, PGIStatus[] results)
{
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
List<PGIStatusResponse> ResponseList = new List<PGIStatusResponse>();
try
{
foreach (PGIStatus item in results)
{
BaseLog.LoggingInfo(logger, "SerialNumber: \r\n{0}", ObjectTool.ObjectTostring(item.SerialNumber));
}
foreach (PGIStatus item in results)
{
PGIStatusResponse response = new PGIStatusResponse();
string SerialNumber = item.SerialNumber;
string State = SQL.GetPGIState(connectionDB, dbIndex, SerialNumber);
if (State == "N") State = "F";
response.SerialNumber = item.SerialNumber;
response.Result = State;
ResponseList.Add(response);
}
return ResponseList;
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
public static void InsertPGILog(string connectionDB, int dbIndex, PGIStatus result, string State, string ErrorDescr)
{
string SerialNumber = result.SerialNumber;
string Plant = result.Plant;
string EventType = result.EventType;
string ID = result.ID;
string DNType = result.Type;
string PGI_Date = result.PGI_Date;
string Remark1 = (result.Remark1 == null ? "" : result.Remark1);
string strSQL = @"insert into PGILog(SerialNumber, Plant, DN_Shipment, [Type], [Status], PGI_Date, Remark1, State, ErrorDescr, Cdt)
values (@SerialNumber, @Plant, @ID, @Type, @Status, @PGI_Date, @Remark1, @State, @ErrorDescr, @Now) ";
//SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_HISTORY(1),
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@SerialNumber", 25, SerialNumber),
SQLHelper.CreateSqlParameter("@Plant", 4, Plant),
SQLHelper.CreateSqlParameter("@ID", 10, ID),
SQLHelper.CreateSqlParameter("@Type", 1, DNType),
SQLHelper.CreateSqlParameter("@Status", 6, EventType),
SQLHelper.CreateSqlParameter("@PGI_Date", 19, PGI_Date),
SQLHelper.CreateSqlParameter("@Remark1", 25, Remark1),
SQLHelper.CreateSqlParameter("@State", 16, State),
SQLHelper.CreateSqlParameter("@ErrorDescr", 255, ErrorDescr),
SQLHelper.CreateSqlParameter("@Now", DateTime.Now));
}