public static string Generate(POHeader objPOHeader) { string msg = string.Empty; try { PODetail objPODtl = objPOHeader.PODetails.FirstOrDefault(); PrintTag objPrintTag = objPODtl.PrintTag; string fileName = $@"\\{Conn.POLabelLocation}\{objPOHeader.Company}\Carton\{objPOHeader.PONum}_{objPOHeader.DONum}_{objPODtl.POLine}_{objPODtl.Id}.csv"; using (StreamWriter sw = System.IO.File.CreateText(fileName)) { sw.WriteLine("Part Num,Part Desc,Lot Num,Qty,UOM,Copies"); //Response.ClearContent(); //Response.AddHeader("content-disposition", "attachment;filename=Carton-" + csvModel.PONum + "-" + csvModel.DONum + ".csv"); //Response.ContentType = "text/csv"; sw.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}", objPODtl.PartNum, objPODtl.PartDesc, objPODtl.LotNum, objPrintTag.QtyPerContainer, objPODtl.UomCode, objPrintTag.NumberOfTags)); msg = $"\nLabel:{fileName} Generated Sucessfully!"; } } catch (Exception ex) { msg = $"Label Generate Failed. \nError : {ex.Message.ToString()}"; } return(msg); }
public bool TuValidation(POHeader objPOHeader) { bool isValid = false; SqlCommand cmd = null; try { using (SqlConnection Conn = new SqlConnection(StagingConStr)) { Conn.Open(); StringBuilder strPODtlQry = null; //Header SQL if (objPOHeader.PODetails != null) { //PODetail SQL PODetail objPODtl = objPOHeader.PODetails.SingleOrDefault(); strPODtlQry = new StringBuilder(); strPODtlQry.Append(" Select Count(*) from [PODetailTbl] where " + $"Company=\'{objPOHeader.Company}\' and PONum={objPOHeader.PONum} and POLine={objPODtl.POLine}" + $" and DONum=\'{objPOHeader.DONum}\' and TuId=\'{objPODtl.TuId}\' and LotNum=\'{objPODtl.LotNum}\' "); //Check TuValidation cmd = new SqlCommand(strPODtlQry.ToString(), Conn); int cnt = Convert.ToInt32(cmd.ExecuteScalar()); if (cnt == 0) { isValid = true; } } } } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } finally { } return(isValid); }
public bool isOverReceiving(POHeader objPOHeader) { bool isValid = false; SqlCommand cmd = null; try { using (SqlConnection Conn = new SqlConnection(StagingConStr)) { Conn.Open(); StringBuilder strPODtlQry = null; //Header SQL if (objPOHeader.PODetails != null) { //PODetail SQL PODetail objPODtl = objPOHeader.PODetails.SingleOrDefault(); strPODtlQry = new StringBuilder(); strPODtlQry.Append(" Select isnull(Sum(Qty),0) from [PODetailTbl] where " + $"Company=\'{objPOHeader.Company}\' and PONum={objPOHeader.PONum} and POLine={objPODtl.POLine}"); //Check TuValidation cmd = new SqlCommand(strPODtlQry.ToString(), Conn); decimal rcvdQty = Convert.ToDecimal(cmd.ExecuteScalar()); if ((rcvdQty + objPODtl.Qty) > objPODtl.OrdQty) { isValid = true; } } } } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } finally { } return(isValid); }
public void UpdatePODataInEpicor(POHeader objPOHeader) { SqlTransaction Trans = null; SqlCommand cmd = null; try { using (SqlConnection Conn = new SqlConnection(EpicorConStr)) { Conn.Open(); Trans = Conn.BeginTransaction(); StringBuilder strPODtlQry = null; //Header SQL if (objPOHeader.PODetails != null) { //PODetail SQL PODetail objPODtl = objPOHeader.PODetails.SingleOrDefault(); strPODtlQry = new StringBuilder(); //Update PODetail set HHDONum_c='TestDO1', HHIsRcvFull_c=0, HHRcvStatus_c='P',HHRcvdQty_c=HHRcvdQty_c + 2 where Company='EPIC03' and PONUM=4207 and POLine=1 strPODtlQry.Append($"Update PODetail set HHDONum_c=\'{objPOHeader.DONum}\', HHIsRcvFull_c={(objPODtl.IsReceivedFull ? 1 : 0)}, HHRcvStatus_c=\'{(objPODtl.IsReceivedFull ? "Full" : "Partial")}\' " + $",HHRcvdQty_c=HHRcvdQty_c + {objPODtl.Qty} where Company=\'{objPOHeader.Company}\' and PONUM={objPOHeader.PONum} and POLine={objPODtl.POLine} "); //Execute PODtl insert Query cmd = new SqlCommand(strPODtlQry.ToString(), Conn, Trans); cmd.ExecuteNonQuery(); } Trans.Commit(); } } catch (Exception ex) { if (Trans != null) { Trans.Rollback(); } throw new Exception(ex.Message.ToString()); } finally { } }
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); }
public List <PODetail> GetPODtlByPONum(int PONum, string Company) { List <PODetail> objPODtls = null; //PO Header info string cmdQry = string.Format("Select a.Company,a.PONum,a.POLine,a.PartNum,a.LineDesc,a.XOrderQty,a.IUM,a.OrderQty,a.PUM, " + "a.HHRcvdQty_c as ReceivedQty,(a.OrderQty-a.HHRcvdQty_c) as Qty " + ",a.HHIsRcvFull_c,a.HHRcvStatus_c,b.TrackLots,c.PrimWhse,d.BinNum " + "from PODetail a inner join erp.Part b " + "on a.Company=b.Company and a.PartNum=b.PartNum " + "left join erp.PartPlant c on a.Company=c.Company and a.PartNum=c.PartNum " + "left join erp.PartBinInfo d on c.Company=d.Company and c.PartNum=d.PartNum and c.PrimWhse=d.WarehouseCode " + "Where a.Company=\'{0}\' and a.PONum={1} and a.HHIsRcvFull_c=0 " , Company, PONum); using (SqlConnection connection = new SqlConnection(EpicorConStr)) { connection.Open(); SqlCommand command = new SqlCommand(cmdQry, connection); using (SqlDataReader reader = command.ExecuteReader()) { objPODtls = new List <PODetail>(); while (reader.Read()) { PODetail objPODtl = new PODetail(); objPODtl.Company = reader["Company"].ToString(); objPODtl.PONum = Convert.ToInt32(reader["PONum"]); objPODtl.POLine = Convert.ToInt32(reader["POLine"]); objPODtl.PartNum = reader["PartNum"].ToString(); objPODtl.PartDesc = reader["LineDesc"].ToString(); objPODtl.TrackLot = Convert.ToBoolean(reader["TrackLots"]); objPODtl.XOrdQty = Convert.ToDecimal(reader["XOrderQty"]); objPODtl.IUM = reader["IUM"].ToString(); objPODtl.OrdQty = Convert.ToDecimal(reader["OrderQty"]); objPODtl.PUM = reader["PUM"].ToString(); objPODtl.PrevRcvQty = Convert.ToDecimal(reader["ReceivedQty"]); objPODtl.Qty = Convert.ToDecimal(reader["Qty"]); objPODtl.IsReceivedFull = Convert.ToBoolean(reader["HHIsRcvFull_c"]); objPODtl.ReceiveStatus = reader["HHRcvStatus_c"].ToString(); objPODtl.UomCode = reader["PUM"].ToString(); objPODtl.WarehouseCode = reader["PrimWhse"] == null?"": reader["PrimWhse"].ToString(); objPODtl.BinNum = reader["BinNum"] == null ? "" : reader["BinNum"].ToString(); objPODtls.Add(objPODtl); } } } if (objPODtls != null && objPODtls.Any()) { foreach (var objRow in objPODtls) { LotAttribute objLotAttribute = GetLotAttributeByPartNum(objRow.Company, objRow.PartNum); if (objLotAttribute != null) { objLotAttribute.Company = objRow.Company; objLotAttribute.PONum = objRow.PONum; objLotAttribute.POLine = objRow.POLine; objLotAttribute.PartNum = objRow.PartNum; objRow.LotAttribute = objLotAttribute; } } } return(objPODtls); }