//2.檢查資料內容
public static void CheckData(DBMoHeader moheader,DBMoItemDetail[] lstItemdetail)
{
string HPPlantCode = WebConfigurationManager.AppSettings["HPPlantCode"];
if (moheader.Plant != HPPlantCode)
{
string errMsg = "This Plant :[" + moheader.Plant + "] is not HP Plant Code";
throw new Exception(errMsg);
}
}
//3.執行DB insert
public static void Process(DBMoHeader dbheader, DBMoItemDetail[] moitems, string connectionDB)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
using (TransactionScope txn = UTL.CreateDbTxn())
{
dbheader.IsProduct = "Y";
//檢查 MO 的 Model是否存在於 DockMOMaterialPreFix 設定中。
string DockMoMtlPreFixList = WebConfigurationManager.AppSettings["DockMOMaterialPreFix"];
string[] DockMOMaterialPreFix = DockMoMtlPreFixList.Split(new char[] { ',', ';' });
int stringCnt = 0;
for (int i = 0; i < DockMOMaterialPreFix.Length; i++)
{
if (DockMOMaterialPreFix[i].Trim() != "")
{
bool b = dbheader.BuildOutMtl.StartsWith(DockMOMaterialPreFix[i]);
//not match
if (!b)
{
stringCnt++;
}
}
}
if (stringCnt == DockMOMaterialPreFix.Length)
{
//Model不符合 Docking的Model，則將MO insert 到HP DB
//insert MO to HP
int dbIndex = 1;
SQL.InsertMO(connectionDB, dbIndex, dbheader, "N", "", "");
}
else
{
//Model有符合 Dock的Model，則將MO insert 到Docking DB
//insert MO to Docking
int dbIndex = 0;
SQL.InsertMO(connectionDB, dbIndex, dbheader, "N", "", "");
}
txn.Complete();
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
//SQL.InsertTxnDataLog("Receive", "MoRelease", dbheader.MoNumber, dbheader.BuildOutMtl, dbheader.TxnId,"",e.Message, "Fail","");
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//1.檢查必要的input parameter
public static void ValidateParameter(DBMoHeader header, DBMoItemDetail[] itemList)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
logger.DebugFormat("Header: \r\n{0}", ObjectTool.ObjectTostring(header));
ObjectTool.CheckNullData(header.NotNullItemList, header);
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
public MoReleaseResponse MoRelease(MoHeader moheader, MoItemDetail[] moitems)
{
string connectionDB = "PP_DBServer";
int dbIndex = 0;
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
string msgName = "MORelease";
string responseMsg = "MOReleaseResponse";
if (string.IsNullOrEmpty(moheader.TCode) ||
moheader.TCode == "CO02" ||
moheader.TCode == "BADI")
{
msgName = "MOChange";
responseMsg = "MOChangeResponse";
}
DBMoHeader dbheader = new DBMoHeader(moheader);
try
{
string BuildOutMtl = "";
string MaterialGroup = "";
string TotalQty = "";
string Status = "";
BuildOutMtl = string.IsNullOrEmpty(moheader.BuildOutMtl) ? "" : moheader.BuildOutMtl;
MaterialGroup = string.IsNullOrEmpty(moheader.MaterialGroup) ? "" : moheader.MaterialGroup;
TotalQty = string.IsNullOrEmpty(moheader.TotalQty) ? "" : moheader.TotalQty;
Status = string.IsNullOrEmpty(moheader.Status) ? "" : moheader.Status;
WS.Common.SQL.InsertTxnDataLog_DB(connectionDB, dbIndex,
EnumMsgCategory.Receive,
msgName,
string.IsNullOrEmpty(moheader.MoNumber) ? "" : moheader.MoNumber,
string.IsNullOrEmpty(moheader.Status) ? "" : moheader.Status,
string.IsNullOrEmpty(moheader.SerialNumber) ? "" : moheader.SerialNumber,
"",
"",
EnumMsgState.Received,
BuildOutMtl + ";" + MaterialGroup + ";" + TotalQty + ";" + Status);
List<DBMoItemDetail> lstdbItemdetail = new List<DBMoItemDetail>();
foreach (MoItemDetail item in moitems)
{
lstdbItemdetail.Add(new DBMoItemDetail(item));
}
//1.檢查必要的input parameter
DBMoItemDetail[] dbitemsArr = lstdbItemdetail.ToArray();
Execute.ValidateParameter(dbheader, lstdbItemdetail.ToArray());
//2.檢查資料內容
Execute.CheckData(dbheader, dbitemsArr);
//3.執行DB insert
Execute.Process(dbheader, dbitemsArr, connectionDB);
//4.Build Response Message
MoReleaseResponse mrealease = Execute.BuildResponseMsg(moheader, true, "");
WS.Common.SQL.InsertTxnDataLog_DB(connectionDB, dbIndex,
EnumMsgCategory.Send,
responseMsg,
string.IsNullOrEmpty(moheader.MoNumber) ? "" : moheader.MoNumber,
string.IsNullOrEmpty(moheader.Status) ? "" : moheader.Status,
string.IsNullOrEmpty(moheader.SerialNumber) ? "" : moheader.SerialNumber,
"",
"",
EnumMsgState.Success,
"");
return mrealease;
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
MoReleaseResponse mrealease = Execute.BuildResponseMsg(moheader, false, e.Message);
WS.Common.SQL.InsertTxnDataLog_DB(connectionDB, dbIndex,
EnumMsgCategory.Receive,
responseMsg,
string.IsNullOrEmpty(moheader.MoNumber) ? "" : moheader.MoNumber,
string.IsNullOrEmpty(moheader.Status) ? "" : moheader.Status,
string.IsNullOrEmpty(moheader.SerialNumber) ? "" : moheader.SerialNumber,
"",
e.Message,
EnumMsgState.Fail,
"");
return mrealease;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
public static void InsertMO(string connectionDB, int dbIndex,
DBMoHeader dbheader,
string isHold,
string holdCode,
string holdComment)
{
string strSQL = @"IF NOT EXISTS (SELECT MO FROM MO WHERE MO=@MO)
BEGIN
INSERT INTO MO
([MO],[Plant],[Model],[CreateDate],[StartDate],[Qty],[SAPStatus],[SAPQty],[Print_Qty],[Transfer_Qty]
,[Status],[Cdt],[Udt], [CustomerSN_Qty])
VALUES
(@MO,@Plant,@Model,@CreateDate,@StartDate,@Qty,
case when (@SAPStatus = 'REL' or @SAPStatus='PCNF') then ' ' else @SAPStatus end,
@SAPQty,@Print_Qty,@Transfer_Qty
,@Status,@Cdt,@Udt,@CustomerSN_Qty)
INSERT INTO MOData
([MO],[TxnId],[MOType],[Unit] ,[FinishDate] ,[ProductVer],[Priority],[BOMCategory],[BOMExpDate] ,[SalesOrder]
,[SOItem],[IsProduct] ,[DataSource],[TotalQty],[Editor],[Udt])
VALUES
(@MO,@TxnId,@MOType,@Unit ,@FinishDate ,@ProductVer,@Priority,@BOMCategory,@BOMExpDate ,@SalesOrder
,@SOItem,@IsProduct ,@DataSource,@TotalQty,@Editor,@Udt)
INSERT INTO MOStatus(MO, Status, IsHold, HoldCode,
Comment, Editor, LastTxnId, Udt)
VALUES(@MO, 'Release', @IsHold, @HoldCode,
@HoldComment, @Editor, @TxnId, @Udt)
INSERT INTO MOStatusLog(MO, [Function], [Action], Station, PreStatus,
Status, IsHold, HoldCode, TxnId, Comment,
Editor, Cdt)
VALUES(@MO, 'WSMORelease','CreateMO', '','',
'Release',@IsHold, @HoldCode,@TxnId ,@HoldComment,
@Editor, @Udt)
END
ELSE
BEGIN
UPDATE MO
SET StartDate=@StartDate,
Qty=(case when Qty>@TotalQty then
@TotalQty
else
Qty
end),
SAPStatus=(case when (@SAPStatus = 'REL' or @SAPStatus='PCNF') then ' ' else @SAPStatus end),
SAPQty=@SAPQty,
Udt=@Udt,
Model=@Model,
Status=@Status
WHERE MO=@MO
UPDATE MOData
SET TxnId=@TxnId,MOType=@MOType,Unit=@Unit,FinishDate=@FinishDate,ProductVer=@ProductVer,Priority=@Priority,
BOMCategory=@BOMCategory,BOMExpDate=@BOMExpDate,SalesOrder=@SalesOrder,SOItem=@SOItem,
IsProduct=@IsProduct,TotalQty=@TotalQty, Udt=@Udt
WHERE MO=@MO
IF @IsHold='N'
BEGIN
update MOStatus
set IsHold=@IsHold,
HoldCode=@HoldCode,
Comment=@HoldComment
where MO=@MO and
( HoldCode='SYS100' OR
HoldCode='SYS101' )
END
ELSE
BEGIN
update MOStatus
set IsHold=@IsHold,
HoldCode=@HoldCode,
Comment=@HoldComment
where MO=@MO and
IsHold='N'
END
INSERT INTO MOStatusLog(MO, [Function], [Action], Station, PreStatus,
Status, IsHold, HoldCode, TxnId, Comment,
Editor, Cdt)
SELECT @MO, 'WSMORelease','UpdateMO', '',Status, Status,
IsHold, HoldCode,@TxnId ,Comment, @Editor, @Udt
FROM MOStatus
WHERE MO =@MO
END";
SqlParameter[] sqlparamsArray = BuildHeaderSqlParams(dbheader,
isHold,
holdCode,
holdComment );
//SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_CFG(),
SQLHelper.ExecuteNonQuery(SQLHelper.GetDBConnectionString(connectionDB, dbIndex),
System.Data.CommandType.Text,
strSQL, sqlparamsArray);
}
private static SqlParameter[] BuildHeaderSqlParams(DBMoHeader dbheader,
string isHold,
string holdCode,
string holdComment)
{
List<SqlParameter> salparams = new List<SqlParameter>();
salparams.Add(SQLHelper.CreateSqlParameter("@MO", 20, dbheader.MoNumber.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@Plant", 20, dbheader.Plant.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@Model", 20, dbheader.BuildOutMtl.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@CreateDate", ObjectTool.String2Date(dbheader.CreateDate)));
salparams.Add(SQLHelper.CreateSqlParameter("@StartDate", ObjectTool.String2Date(dbheader.BasicStartDate)));
salparams.Add(SQLHelper.CreateSqlParameter("@SAPStatus",10, dbheader.Status.Trim()));
double qty = Math.Ceiling(float.Parse( dbheader.DeliveredQty));
salparams.Add(SQLHelper.CreateSqlParameter("@SAPQty",(int)qty));
double totalqty = Math.Ceiling(float.Parse(dbheader.TotalQty));
salparams.Add(SQLHelper.CreateSqlParameter("@Qty", (int)totalqty));
salparams.Add(SQLHelper.CreateSqlParameter("@TotalQty", (int)totalqty));
salparams.Add(SQLHelper.CreateSqlParameter("@Print_Qty",0));
salparams.Add(SQLHelper.CreateSqlParameter("@Transfer_Qty",0));
if (dbheader.Status.Trim() == "TECO" || dbheader.Status.Trim() == "CNF" || dbheader.Status.Trim() == "CLOSE")
salparams.Add(SQLHelper.CreateSqlParameter("@Status",1,"C"));// 表示工單關閉
else
salparams.Add(SQLHelper.CreateSqlParameter("@Status", 1, "H"));
salparams.Add(SQLHelper.CreateSqlParameter("@Cdt", DateTime.Now));
salparams.Add(SQLHelper.CreateSqlParameter("@Udt",DateTime.Now));
salparams.Add(SQLHelper.CreateSqlParameter("@CustomerSN_Qty",0));
salparams.Add(SQLHelper.CreateSqlParameter("@FinishDate", ObjectTool.String2Date(dbheader.BasicFinishDate)));
salparams.Add(SQLHelper.CreateSqlParameter("@MOType", 4, dbheader.MoType.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@Unit", 8, dbheader.Unit.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@ProductVer", 8, dbheader.ProductionVer.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@Priority", 8, dbheader.Priority==null ? "": dbheader.Priority.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@BOMCategory", 8, dbheader.BOMStatus.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@BOMExpDate", ObjectTool.String2Date(dbheader.BOMExplDate)));
salparams.Add(SQLHelper.CreateSqlParameter("@SalesOrder", 16, string.IsNullOrEmpty( dbheader.SalesOrder)? "" :dbheader.SalesOrder.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@SOItem",8, string.IsNullOrEmpty(dbheader.SOItem)?"":dbheader.SOItem.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@TxnId",8, dbheader.SerialNumber.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@IsProduct", 1, dbheader.IsProduct.Trim()));
salparams.Add(SQLHelper.CreateSqlParameter("@DataSource",8, "SAP"));
salparams.Add(SQLHelper.CreateSqlParameter("@Editor",8, "SAP"));
salparams.Add(SQLHelper.CreateSqlParameter("@IsHold", 1, isHold));
salparams.Add(SQLHelper.CreateSqlParameter("@HoldCode", 32, holdCode));
salparams.Add(SQLHelper.CreateSqlParameter("@HoldComment", 255, holdComment));
return salparams.ToArray();
}