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