//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);
//string h = ObjectTool.ObjectTostring(obj);
//ObjectTool.CheckNullData(header.NotNullItemList, header);
int i = 1;
foreach (DBMoItemDetail item in itemList)
{
logger.DebugFormat("Item{0}: \r\n{1}", i.ToString(), ObjectTool.ObjectTostring(item));
if (!string.IsNullOrEmpty(item.Delete))
logger.DebugFormat("Component {0} has delete flag", item.Component);
ObjectTool.CheckNullData(item.NotNullItemList, item);
i++;
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//2.檢查資料內容
public static void CheckData(DBMoHeader moheader,DBMoItemDetail[] lstItemdetail)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
//檢查 MO 的 Model是否存在於 FAMOMaterialPreFix 設定中。
string FAMoMtlPreFixList = WebConfigurationManager.AppSettings["FAMOMaterialPreFix"];
string[] FAMOMaterialPreFix = FAMoMtlPreFixList.Split(new char[] { ',', ';' });
int stringCnt = 0;
for (int i = 0; i < FAMOMaterialPreFix.Length; i++)
{
if (FAMOMaterialPreFix[i].Trim() != "")
{
bool b = moheader.BuildOutMtl.StartsWith(FAMOMaterialPreFix[i]);
if (!b)
{
stringCnt++;
}
}
}
if (stringCnt == FAMOMaterialPreFix.Length)
{
string errMsg = "This Model :[" + moheader.BuildOutMtl + "] is not include in FA MO material list";
throw new Exception(errMsg);
}
string isproduct = SQL.CheckMOBOMPart(moheader.BuildOutMtl);
if (isproduct == "N") // semi-product need create Model
{
if (moheader.MaterialType.Trim() == WSConstant.SAPFinishedGood)
SQL.CreateModelFamily(moheader.MaterialGroup, moheader.BuildOutMtl, 1, moheader.Plant);
else
SQL.CreateModelFamily(moheader.MaterialGroup, moheader.BuildOutMtl, 2,moheader.Plant);
}
else if (isproduct == "F")
{
if (moheader.MaterialType.Trim() == WSConstant.SAPSemiProduct)
{
SQL.CreateModelFamily(moheader.MaterialGroup, moheader.BuildOutMtl,2);
moheader.IsProduct = "N";
}
else
{
SQL.CreateModelFamily("UNKNOW", "UNKNOW",3);
string model = moheader.BuildOutMtl;
string errMsg = "This Model :[" + moheader.BuildOutMtl + "] is no data in IMES Model table";
moheader.BuildOutMtl = "UNKNOW";
moheader.IsProduct = "N";
if (moheader.MaterialType.Trim() == WSConstant.SAPFinishedGood)
moheader.IsProduct = "Y";
SQL.InsertMO(moheader, "Y",
SysHoldCode.MOCheckModelFail,
errMsg);
moheader.BuildOutMtl = model;
throw new Exception(errMsg);
}
}
moheader.IsProduct = "N";
if (moheader.MaterialType.Trim() == WSConstant.SAPFinishedGood)
moheader.IsProduct = "Y";
string ispart="";
List<String> missParts = new List<string>();
foreach (DBMoItemDetail item in lstItemdetail)
{
ispart = SQL.CheckPart(item.Component);
if (ispart == "F")
{
missParts.Add(item.Component);
}
}
if (missParts.Count > 0)
{
string errMsg = "This PartNO :[" + string.Join(",", missParts.ToArray()) + "] is no data in IMES Part table";
SQL.InsertMO(moheader, "Y",
SysHoldCode.MOCheckMaterialFail,
errMsg);
throw new Exception(errMsg);
}
}
catch (Exception e)
{
BaseLog.LoggingError(logger, MethodBase.GetCurrentMethod(), e);
throw e;
}
finally
{
BaseLog.LoggingEnd(logger, methodName);
}
}
//3.執行DB insert
public static void Process(DBMoHeader dbheader, DBMoItemDetail[] moitems)
{
string methodName = MethodBase.GetCurrentMethod().Name;
BaseLog.LoggingBegin(logger, methodName);
try
{
using (TransactionScope txn = UTL.CreateDbTxn())
{
//1. insert MO & ModelBOM for SKU level
SQL.InsertMO(dbheader,"N","","");
//2 insert MOBOM
if (moitems.Length > 0 && dbheader.Status != "TECO")
{
SQL.InsertMOBOM(moitems);
}
//if (dbheader.Status != "TECO") {
// // if dbheader is finish good then create ModelBOM
// if (dbheader.MaterialType == "ZFRT" || dbheader.MaterialType == "HALB")
// {
// SQL.InsertModelBOM(dbheader.BuildOutMtl, dbheader.Plant, moitems);
// }
//}
//SQL.InsertMOBOM(moitems);
//SQL.InsertTxnDataLog("Receive", "MoRelease", dbheader.MoNumber, dbheader.BuildOutMtl, dbheader.TxnId, "", "", "Received","");
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);
}
}
public MoReleaseResponse MoRelease(MoHeader moheader, MoItemDetail[] moitems)
{
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
{
WS.Common.SQL.InsertTxnDataLog(EnumMsgCategory.Receive,
msgName,
string.IsNullOrEmpty(moheader.MoNumber) ? "" : moheader.MoNumber,
string.IsNullOrEmpty(moheader.Status) ? "" : moheader.Status,
string.IsNullOrEmpty(moheader.SerialNumber) ? "" : moheader.SerialNumber,
"",
"",
EnumMsgState.Received,
"");
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);
//4.Build Response Message
MoReleaseResponse mrealease = Execute.BuildResponseMsg(moheader, true, "");
WS.Common.SQL.InsertTxnDataLog(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(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);
}
}
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));
//Change to default issued value =0
salparams.Add(SQLHelper.CreateSqlParameter("@Qty", 0));
salparams.Add(SQLHelper.CreateSqlParameter("@TotalQty", (int)totalqty));
salparams.Add(SQLHelper.CreateSqlParameter("@Print_Qty",0));
salparams.Add(SQLHelper.CreateSqlParameter("@Transfer_Qty",0));
//R -> H -> C Status
string moStatus = (isHold == "Y" ? "X" : ((dbheader.Status == "TECO" || dbheader.Status == "CNF") ? "C" : "R"));
salparams.Add(SQLHelper.CreateSqlParameter("@Status", 1, moStatus ));
salparams.Add(SQLHelper.CreateSqlParameter("@Cdt",DateTime.Now));
salparams.Add(SQLHelper.CreateSqlParameter("@Udt",DateTime.Now));
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();
}
public static void InsertMO(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])
VALUES
(@MO,@Plant,@Model,@CreateDate,@StartDate,@Qty,@SAPStatus,@SAPQty,@Print_Qty,@Transfer_Qty
,@Status,@Cdt,@Udt)
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=@SAPStatus,
SAPQty=@SAPQty,
Status = ( case when Print_Qty>0 and @Status='R' then
'H'
when Status='H' and @Status='R' then
'H'
else
@Status
end),
Udt=@Udt,
Model=@Model
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(),
System.Data.CommandType.Text,
strSQL, sqlparamsArray);
}