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);
}
