コード例 #1
0
        public string InsertPOHeader(POHeader objPOHeader)
        {
            string msg = string.Empty;

            try
            {
                DAL objDAL = new DAL();
                msg = objDAL.InsertPOData(objPOHeader);

                if (msg == "")
                {
                    InitiateSchedular();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message.ToString();
            }

            //if msg = empty, run a scheduler project

            return(msg);
        }
コード例 #2
0
        public POHeader GetPOHeaderByPONum(int PONum, string Company)
        {
            POHeader objPOHeader = new POHeader();
            DAL      objDAL      = new DAL();

            objPOHeader = objDAL.GetDataByPONum(PONum, Company);
            //objPOHeader.PONum = 1001;
            //objPOHeader.DONum = "Test1001";
            //objPOHeader.SuppNum = 1;
            //objPOHeader.SuppId = "Cust1";
            //objPOHeader.SuppName = "Test Supplier";
            //objPOHeader.PODetails = new List<PODetail>
            //{
            //    new PODetail { PONum = objPOHeader.PONum,POLine=1, PartNum="TestPart_1", PUM="KG",
            //        LotAttribute= new LotAttribute { PONum=objPOHeader.PONum,POLine=1,Batch="Tracked",MBatch="NotTracked"},
            //        PrintTag=new PrintTag{PONum=objPOHeader.PONum,POLine=1}
            //    },
            //    new PODetail { PONum = objPOHeader.PONum,POLine=2, PartNum="TestPart_2", PUM="KG",
            //        LotAttribute= new LotAttribute { PONum=objPOHeader.PONum,POLine=2,Batch="Tracked",MBatch="NotTracked"},
            //        PrintTag=new PrintTag{PONum=objPOHeader.PONum,POLine=2}
            //    }
            //};
            return(objPOHeader);
        }
コード例 #3
0
        public FGHeader GetDataByPONum(int PONum, string Company)
        {
            FGHeader objPOHeader = null;
            bool     isApprove   = true;
            //PO Header info
            string cmdQry = string.Format("select Top 1 ICPOLocked,a.ApprovalStatus,a.Approve,a.Company,a.PONum,b.VendorNum,b.VendorID,b.[Name],FS_CartonLabel_c as CartonLabel " +
                                          "from erp.POHeader a inner join Vendor b " +
                                          "On a.Company =b.Company and a.VendorNum=b.VendorNum " +
                                          "INNER JOIN PODetail c " +
                                          "ON a.Company=c.Company and a.PONum=c.PONUM " +
                                          "Where a.Company=\'{0}\' and a.PONum={1} and a.OpenOrder=1 and a.Approve=1 and a.ApprovalStatus='A' and c.HHIsRcvFull_c =0 and a.Linked=0 and a.ICPOLocked=0 "
                                          , Company, PONum);

            using (SqlConnection connection = new SqlConnection(EpicorConStr))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(cmdQry, connection);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    objPOHeader = new POHeader();
                    while (reader.Read())
                    {
                        objPOHeader.Company     = reader["Company"].ToString();
                        objPOHeader.PONum       = Convert.ToInt32(reader["PONum"]);
                        objPOHeader.SuppNum     = Convert.ToInt32(reader["VendorNum"]);
                        objPOHeader.SuppId      = reader["VendorID"].ToString();
                        objPOHeader.SuppName    = reader["Name"].ToString();
                        objPOHeader.CartonLabel = Convert.ToBoolean(reader["CartonLabel"]);
                        isApprove = Convert.ToBoolean(reader["Approve"]);
                    }
                }
            }
            //if(!isApprove || string.IsNullOrEmpty(objPOHeader.Company))
            //{
            //    throw new Exception($"PO: {PONum} is not Approved!");
            //}

            if (!isApprove || string.IsNullOrEmpty(objPOHeader.Company))
            {
                string msg = string.Empty;

                //throw new Exception($"PO: {PONum} is not Approved!");
                StringBuilder sbQry = new StringBuilder();
                sbQry.Append($" select case When (select PONum from POHeader where Company='{Company}' and PONum={PONum}) is null then 'Not found Record'  ");
                sbQry.Append($" When (select ApprovalStatus from POHeader where Company='{Company}' and PONum={PONum} ) != 'A' then 'Not Approved'  ");
                sbQry.Append($" When (select OpenOrder from POHeader where Company='{Company}' and PONum={PONum} ) = 0 then 'Closed'   ");
                sbQry.Append($" When (select Linked from POHeader where Company='{Company}' and PONum={PONum} ) = 1 then 'InterCompany PO'   ");
                sbQry.Append($" When (select ICPOLocked from POHeader where Company='{Company}' and PONum={PONum} ) = 1 then 'InterCompany PO'   ");
                sbQry.Append($" When (select Top 1 HHIsRcvFull_c from PODetail where Company='{Company}' and PONum={PONum} and HHIsRcvFull_c=0 ) is null then '" +
                             $"Received Full'   ");
                sbQry.Append($" else '0' end  ");

                using (SqlConnection connection = new SqlConnection(EpicorConStr))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
                    msg = Convert.ToString(command.ExecuteScalar());
                }
                if (!string.IsNullOrEmpty(msg))
                {
                    if (msg != "0")
                    {
                        throw new Exception($"PO: {PONum} is {msg}!");
                    }
                }
                else
                {
                    throw new Exception($"Something Went Wrong!");
                }
            }



            if (objPOHeader != null)
            {
                objPOHeader.PODetails = GetPODtlByPONum(PONum, Company);
            }


            return(objPOHeader);
        }
コード例 #4
0
        public string InsertPOData(POHeader objPOHeader)
        {
            string msg         = string.Empty;
            bool   isSqlSucess = true;

            //Check TuValidation
            if (!TuValidation(objPOHeader))
            {
                throw new Exception("TuId Validation Failed!");
            }
            if (isOverReceiving(objPOHeader))
            {
                throw new Exception("Saving Failed due to OverReceiving!");
            }
            SqlTransaction Trans = null;
            SqlCommand     cmd   = null;

            try
            {
                using (SqlConnection Conn = new SqlConnection(StagingConStr))
                {
                    Conn.Open();
                    Trans = Conn.BeginTransaction();
                    StringBuilder strPODtlQry    = null;
                    StringBuilder strLotAttQry   = null;
                    StringBuilder strPrintTagQry = null;
                    //Header SQL
                    if (objPOHeader.PODetails != null)
                    {
                        //PODetail SQL
                        PODetail objPODtl = objPOHeader.PODetails.SingleOrDefault();

                        strPODtlQry = new StringBuilder();

                        strPODtlQry.Append(" INSERT INTO PODetailTbl (Company,Plant,UserId,PONum,POLine,PartNum,PartDesc" +
                                           ",OrdQty,PUM,XOrdQty,IUM,PrevRcvQty,Qty,UOMCode,LotNum,TuId,WareHouseCode,BinNum" +
                                           ",JobNum,[Assy],[Seq],IsReceivedFull,ReceiveStatus,DONum,SuppNum,SuppId,SuppName,TuConv) VALUES ");

                        strPODtlQry.Append($" ( '{objPOHeader.Company}' , '{objPOHeader.Plant}' , '{objPOHeader.UserId}' , {objPOHeader.PONum} , ");

                        strPODtlQry.Append($" {objPODtl.POLine} , \'{objPODtl.PartNum}\' , \'{objPODtl.PartDesc}\' ,{objPODtl.OrdQty}, '{objPODtl.PUM}' ,  ");

                        strPODtlQry.Append($" {objPODtl.XOrdQty},'{objPODtl.IUM}' , '{objPODtl.PrevRcvQty}' , {objPODtl.Qty} , '{objPODtl.UomCode}' , ");

                        strPODtlQry.Append($" '{objPODtl.LotNum}' , '{objPODtl.TuId}' , '{objPODtl.WarehouseCode}' , '{objPODtl.BinNum}' , ");

                        strPODtlQry.Append($" \'{objPODtl.JobNum}\' , \'{objPODtl.Assembly}\' , \'{objPODtl.Sequence}\' , {(objPODtl.IsReceivedFull?1:0)} , ");

                        strPODtlQry.Append($" \'{(objPODtl.IsReceivedFull ? "Full" : "Partial")}\' , \'{objPOHeader.DONum}\' , \'{objPOHeader.SuppNum}\' , ");

                        strPODtlQry.Append($" \'{objPOHeader.SuppId}\' , \'{objPOHeader.SuppName}\' , {objPODtl.TuConv} ) ");

                        //Execute PODtl insert Query
                        cmd = new SqlCommand(strPODtlQry.ToString(), Conn, Trans);
                        cmd.ExecuteNonQuery();

                        //Get ScopeIdentity
                        cmd = new SqlCommand("SELECT SCOPE_IDENTITY()", Conn, Trans);
                        int FKPODtlId = Convert.ToInt32(cmd.ExecuteScalar());
                        objPODtl.Id = FKPODtlId;

                        //LotAttributes SQL
                        if (objPODtl.LotAttribute != null)
                        {
                            strLotAttQry = new StringBuilder();

                            strLotAttQry.Append("INSERT INTO LotAttributeTbl (PODtIdFK,PONum,POLine,Batch,MBatch,MLot,Heat" +
                                                ",Firm,BestBefore,OrigMfg,Cure,Expire) VALUES ");


                            strLotAttQry.Append($" ( {FKPODtlId}, {objPOHeader.PONum}, {objPODtl.POLine} , ");

                            strLotAttQry.Append($" \'{objPODtl.LotAttribute.Batch}\', \'{objPODtl.LotAttribute.MBatch}\', \'{objPODtl.LotAttribute.MLot}\', ");

                            strLotAttQry.Append($" \'{objPODtl.LotAttribute.Heat}\', \'{objPODtl.LotAttribute.Firm}\',  ");



                            strLotAttQry.Append(" @BestBefore,@OrigMfg,@Cure,@Expire) ");

                            if (objPODtl.TrackLot)
                            {
                                //Execute LotAttribute insert Query
                                cmd = new SqlCommand(strLotAttQry.ToString(), Conn, Trans);
                                if (objPODtl.LotAttribute.BestBefore != null)
                                {
                                    cmd.Parameters.AddWithValue("@BestBefore", objPODtl.LotAttribute.BestBefore);
                                }
                                else
                                {
                                    //cmd.Parameters.AddWithValue("@BestBefore", DBNull.Value);
                                    cmd.Parameters.Add("@BestBefore", System.Data.SqlDbType.DateTime).Value = DBNull.Value;
                                }
                                //
                                if (objPODtl.LotAttribute.OrigMfg != null)
                                {
                                    cmd.Parameters.AddWithValue("@OrigMfg", objPODtl.LotAttribute.OrigMfg);
                                }
                                else
                                {
                                    //cmd.Parameters.AddWithValue("@OrigMfg", objPODtl.LotAttribute.OrigMfg);
                                    cmd.Parameters.Add("@OrigMfg", System.Data.SqlDbType.DateTime).Value = DBNull.Value;
                                }
                                //
                                if (objPODtl.LotAttribute.Cure != null)
                                {
                                    cmd.Parameters.AddWithValue("@Cure", objPODtl.LotAttribute.Cure);
                                }
                                else
                                {
                                    //cmd.Parameters.AddWithValue("@Cure", objPODtl.LotAttribute.Cure);
                                    cmd.Parameters.Add("@Cure", System.Data.SqlDbType.DateTime).Value = DBNull.Value;
                                }
                                //
                                if (objPODtl.LotAttribute.Expire != null)
                                {
                                    cmd.Parameters.AddWithValue("@Expire", objPODtl.LotAttribute.Expire);
                                }
                                else
                                {
                                    //cmd.Parameters.AddWithValue("@Expire", objPODtl.LotAttribute.Expire);
                                    cmd.Parameters.Add("@Expire", System.Data.SqlDbType.DateTime).Value = DBNull.Value;
                                }


                                cmd.ExecuteNonQuery();
                            }
                        }
                        //Print Tag

                        if (objPODtl.PrintTag != null)
                        {
                            strPrintTagQry = new StringBuilder();

                            strPrintTagQry.Append("INSERT INTO PrintTagTbl (PODtIdFK,PONum,POLine,QtyPerContainer,UOMCode,TransactionQty,TagQty" +
                                                  ",NumberOfTags) VALUES ");

                            strPrintTagQry.Append($" ( {FKPODtlId}, {objPOHeader.PONum}, {objPODtl.POLine} , ");

                            strPrintTagQry.Append($" \'{objPODtl.PrintTag.QtyPerContainer}\', \'{objPODtl.PrintTag.Uom}\', \'{objPODtl.PrintTag.TransactionQty}\', ");

                            strPrintTagQry.Append($" \'{objPODtl.PrintTag.TagQty}\', \'{objPODtl.PrintTag.NumberOfTags}\' ) ");

                            //Execute LotAttribute insert Query
                            cmd = new SqlCommand(strPrintTagQry.ToString(), Conn, Trans);
                            cmd.ExecuteNonQuery();
                        }

                        //Epicor POHeader and PODtl ud columns updations

                        UpdatePODataInEpicor(objPOHeader);
                    }
                    Trans.Commit();

                    msg = "Inserted record successfully!";

                    if (objPOHeader.CartonLabel)
                    {
                        msg += Label.Generate(objPOHeader);
                    }
                    else
                    {
                        msg += "Carton Label Genration is not Require!";
                    }
                }
            }
            catch (Exception ex)
            {
                msg        += $"\n{ ex.Message.ToString()}";
                isSqlSucess = false;
            }
            finally
            {
                try
                {
                    if (Trans != null && !isSqlSucess)
                    {
                        Trans.Rollback();
                    }
                }
                catch (Exception ex)
                { }
            }
            return(msg);
        }