//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 static void InsertTxnDataLog(string Category,string Action,string KeyValue1,string KeyValue2,string TxnId,string ErrorCode,string ErrorDescr,string State,string Comment)
// {
// string strSQL = @"INSERT INTO TxnDataLog ([Category],[Action],[KeyValue1],[KeyValue2],[TxnId],[ErrorCode],[ErrorDescr]
// ,[State],[Comment],[Cdt])
// VALUES (@Category,@Action,@KeyValue1,@KeyValue2,@TxnId,@ErrorCode,@ErrorDescr
// ,@State,@Comment,GETDATE()) ";
// SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_CFG(),
// System.Data.CommandType.Text,
// strSQL,
// SQLHelper.CreateSqlParameter("@Category", 16, Category),
// SQLHelper.CreateSqlParameter("@Action", 16, Action),
// SQLHelper.CreateSqlParameter("@KeyValue1", 32, KeyValue1),
// SQLHelper.CreateSqlParameter("@KeyValue2", 32, KeyValue2),
// SQLHelper.CreateSqlParameter("@TxnId", 32, TxnId),
// SQLHelper.CreateSqlParameter("@ErrorCode", 32, ErrorCode),
// SQLHelper.CreateSqlParameter("@ErrorDescr", 255, ErrorDescr),
// SQLHelper.CreateSqlParameter("@State", 16, State),
// SQLHelper.CreateSqlParameter("@Comment", 255, Comment));
// }
private static void SetBOMGroup(DBMoItemDetail[] moitems)
{
int groupNo = 1;
for (int i = 0; i < moitems.Length; i++)
{
if (moitems[i].Group == 0)
{
moitems[i].Group = groupNo;
}
else
{
continue;
}
for (int k = i + 1; k < moitems.Length; k++)
{
if (moitems[i].AltGroup == moitems[k].AltGroup &&
moitems[i].ParentMaterial == moitems[k].ParentMaterial &&
!string.IsNullOrEmpty(moitems[i].AltGroup ) &&
!string.IsNullOrEmpty(moitems[k].AltGroup))
{
moitems[i].Group = groupNo;
moitems[k].Group = groupNo;
moitems[i].HasAltGroup = true;
moitems[k].HasAltGroup = true;
}
}
groupNo++;
}
}
public static void InsertModelBOM(string Material, string Plant, DBMoItemDetail[] moitems)
{
SetBOMGroup(moitems);
List<TbMoItem> TbMoItem = new List<TbMoItem>();
foreach (DBMoItemDetail item in moitems)
{
TbMoItem temp = new TbMoItem();
temp.SerialNumber = item.SerialNumber == null ? "" : item.SerialNumber;
temp.MoNumber = item.MoNumber == null ? "" : item.MoNumber;
temp.MoItem = item.MoItem == null ? "" : item.MoItem;
temp.Component = item.Component == null ? "" : item.Component;
temp.UnitReqQty = item.UnitReqQty == null ? "0" : item.UnitReqQty;
temp.ParentMaterial = item.ParentMaterial == null ? "0" : item.ParentMaterial;
temp.Group = item.Group;
TbMoItem.Add(temp);
}
DataTable dt = TbMoItem.ToDataTable();
string strSQL = @"exec IMES_ModelBOM_Insert @Material, @Plant, @dt";
SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_CFG(),
System.Data.CommandType.Text,
strSQL,
SQLHelper.CreateSqlParameter("@Material", Material == null ? "" : Material),
SQLHelper.CreateSqlParameter("@Plant", Plant == null ? "" : Plant),
SQLHelper.CreateSqlParameter("@dt", "TbMoItem", dt));
}
private static SqlParameter[] BuildBOMItemsSqlParams(DBMoItemDetail[] moitems, ref string strSQL,string strOneSQL)
{
SetBOMGroup(moitems);
List<SqlParameter> sqlparams = new List<SqlParameter>();
int i = 0; //items count
sqlparams.Add(SQLHelper.CreateSqlParameter("@MO", 20, moitems[0].MoNumber.Trim()));
sqlparams.Add(SQLHelper.CreateSqlParameter("@ID", 0));
foreach (DBMoItemDetail item in moitems)
{
// SAP delete material flag don't insert MoBOM
if (string.IsNullOrEmpty( item.Delete))
{
string[] col = new string[16];
col[0] = "@MO" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[0], 20, item.MoNumber.Trim()));
col[1] = "@PartNo" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[1], 20, item.Component.Trim()));
col[2] = "@Qty" + i.ToString();
//double qty = Math.Ceiling(float.Parse(item.ReqQty));
double qty = Math.Ceiling(float.Parse(item.UnitReqQty));
sqlparams.Add(SQLHelper.CreateSqlParameter(col[2], (int)qty));
col[3] = "@Group" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[3], item.Group));//group
col[4] = "@TxnId" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[4], 32, item.SerialNumber.Trim()));
col[5] = "@MOItem" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[5], 8, item.MoItem.Trim()));
col[6] = "@Reservation" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[6], 16, item.Reservation.Trim()));
col[7] = "@ResvItem" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[7], 8, item.ResvItem.Trim()));
col[8] = "@WithdrawQty" + i.ToString();
double wqty = Math.Ceiling(float.Parse(item.WithdrawQty));
sqlparams.Add(SQLHelper.CreateSqlParameter(col[8], (int)wqty));
col[9] = "@Unit" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[9], 8, item.Unit));
col[10] = "@AltGroup" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[10], 8, item.AltGroup == null ? "" : item.AltGroup.Trim()));
col[11] = "@IsPhantom" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[11], 1, item.Pantom == null ? "" : item.Pantom.Trim()));
col[12] = "@IsBulk" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[12], 1, item.Bulk == null ? "" : item.Bulk.Trim()));
col[13] = "@SpecialStock" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[13], 1, item.SpecialStock == null ? "" : item.SpecialStock.Trim()));
col[14] = "@MN" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[14], 16, item.MN == null ? "" : item.MN.Trim()));
col[15] = "@ParentMaterial" + i.ToString();
sqlparams.Add(SQLHelper.CreateSqlParameter(col[15], 16, item.ParentMaterial == null ? "" : item.ParentMaterial.Trim()));
strSQL = strSQL + string.Format
(strOneSQL, col[0], col[1], col[2], col[3], col[4], col[5], col[6], col[7], col[8], col[9], col[10],
col[11], col[12], col[13], col[14], col[15]) + "\r\n";
i++;
}
}
return sqlparams.ToArray();
}
public static void InsertMOBOM(DBMoItemDetail[] moitems)
{
string strOneSQL = @"INSERT INTO MoBOM
([MO],[PartNo],[Qty],[Group],[Deviation],[Action],[Editor],[Cdt],[Udt])
VALUES ({0},{1},{2},{3},'1','ADD','SAP',GETDATE(),GETDATE());
set @ID = SCOPE_IDENTITY();
INSERT INTO MoBOMData
([MoBOM_ID],[TxnId],[MOItem],[Reservation],[ResvItem],[WithdrawQty],[Unit],[AltGroup]
,[IsPhantom],[IsBulk],[SpecialStock],[MN],[ParentMaterial],[DataSource],[Udt])
VALUES (@ID,{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},'SAP',GETDATE())";
string strSQL = "";
SqlParameter[] sqlparamsArray = BuildBOMItemsSqlParams(moitems, ref strSQL, strOneSQL);
//strSQL = "declare @ID int ;" + strSQL;
// strSQL = //@"IF EXISTS (SELECT MO FROM MO WHERE MO=@MO)
// @" Delete from MoBOMData
// where MoBOM_ID in (Select ID from MoBOM WHERE MO=@MO)
// Delete from MoBOM
// WHERE MO=@MO; " + strSQL;
// don't delete manual material
strSQL = @"Delete from MoBOM
from MoBOM a inner join MoBOMData b on a.ID= b.MoBOM_ID
where MO=@MO
Delete from MoBOMData
where MoBOM_ID in (Select ID from MoBOM WHERE MO=@MO); " + strSQL;
SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString_CFG(),
System.Data.CommandType.Text,
strSQL,
sqlparamsArray);
}