Пример #1
0
        //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
0
        //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
0
        //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);
            } 
        }
Пример #4
0
//         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++;
            }
        }                 
Пример #5
0
        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));

        }
Пример #6
0
        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();
        }
Пример #7
0
        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);
        }