public string DeletePallet(int Id)
{
string msg = string.Empty;
DAL objDAL = new DAL();
PalletSelection objPallet = new PalletSelection();
objPallet = objDAL.GetPalletTblDataById(Id);
objPallet.PalletQty = objPallet.PalletQty * -1;
objDAL.UpdateDataInUD108Epicor(objPallet);
if (msg == "")
{
string temp = "";
objPallet.PalletQty = objPallet.PalletQty * -1;
temp = objPallet.FromWareHouse.ToString();
objPallet.FromWareHouse = objPallet.ToWareHouse;
objPallet.ToWareHouse = temp;
temp = "";
temp = objPallet.FromBinNum.ToString();
objPallet.FromBinNum = objPallet.ToBinNum;
objPallet.ToBinNum = temp;
msg = InventoryTransfer(objPallet);
}
msg = objDAL.DeletePalletById(Id);
return(msg);
}
public string InventoryTransfer(PalletSelection objPallet)
{
string msg = "";
try
{
InventoryTransferServices it = new InventoryTransferServices();
InvTransfer invTransfer = new InvTransfer();
invTransfer.company = objPallet.Company;
invTransfer.plant = objPallet.Plant;
//invTransfer.id = objPallet.Id;
//invTransfer.id = objPallet.Id.ToString();
invTransfer.id = "Loose Picking : Pallet " + objPallet.Id.ToString();
invTransfer.partNum = objPallet.PartNum;
invTransfer.qty = objPallet.PalletQty;
invTransfer.uom = objPallet.UomCode;
invTransfer.frmWarehouse = objPallet.FromWareHouse;
invTransfer.frmBinNum = objPallet.FromBinNum;
invTransfer.frmLotNum = objPallet.LotNum;
invTransfer.toWarehouse = objPallet.ToWareHouse;
invTransfer.toBinNum = objPallet.ToBinNum;
invTransfer.toLotNum = objPallet.LotNum;
it.doInvTransfer(invTransfer);
}
catch (Exception ex)
{
msg = ex.Message.ToString();
}
return(msg);
}
public PalletSelection GetPalletDataById(int Id)
{
PalletSelection objPallet = new PalletSelection();
DAL objDAL = new DAL();
objPallet = objDAL.GetPalletTblDataById(Id);
return(objPallet);
}
public static string GenerateCSV(List <PalletSelection> objVerifiedPallets, string lblNumStr)
{
string msg = string.Empty;
try
{
PalletSelection verifiedPallet = objVerifiedPallets.FirstOrDefault();
string fileName = $@"\\{Conn.LoosePalletLocation}\{verifiedPallet.Company}\LoosePallet\{verifiedPallet.SONum}_{verifiedPallet.PalletNum}_{lblNumStr}.csv";
using (StreamWriter sw = System.IO.File.CreateText(fileName))
{
string shipDate = verifiedPallet.ShipByDate.Date.ToString("dd/MM/yyyy"); //Steven asked on 25th Feb 2019
sw.WriteLine("SoNum,ShippingDate,PalletID");
sw.WriteLine(string.Format("{0},{1},{2}",
verifiedPallet.SONum,
shipDate,
lblNumStr));
}
/*
* PODetail objPODtl = objPOHeader.PODetails.FirstOrDefault();
* PrintTag objPrintTag = objPODtl.PrintTag;
* string fileName = $@"\\{Conn.POLabelLocation}\{objPOHeader.Company}\{objPOHeader.PONum}_{objPOHeader.DONum}_{objPODtl.POLine}_{objPODtl.Id}.csv";
*
* using (StreamWriter sw = System.IO.File.CreateText(fileName))
* {
* sw.WriteLine("Part Num,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}",
* objPODtl.PartNum,
* objPODtl.LotNum,
* objPrintTag.QtyPerContainer,
* objPODtl.UomCode,
* objPrintTag.NumberOfTags));
* msg = $"\nLabel:{fileName} Generated Sucessfully!";
*
* }
*/
}
catch (Exception ex)
{
msg = $"CSV Generate Failed. \nError : {ex.Message.ToString()}";
}
return(msg);
}
public string InsertLPPalletSelection(PalletSelection objPallet)
{
string msg = string.Empty;
try
{
if (string.IsNullOrEmpty(objPallet.Company))
{
throw new Exception("Company is Require!");
}
if (objPallet.SONum < 1)
{
throw new Exception("SONum is Require!");
}
if (string.IsNullOrEmpty(objPallet.LotNum))
{
throw new Exception("LotNum is Require!");
}
if (objPallet.PalletNum < 0)
{
throw new Exception("PalletNum is Require!");
}
if (objPallet.PalletQty <= 0)
{
throw new Exception("PalletQty is Require!");
}
if (objPallet.ActQty < objPallet.PrevQty + objPallet.PalletQty)
{
throw new Exception("Not enough Qty!");
}
msg = InventoryTransfer(objPallet);
if (msg == "")
{
DAL objDAL = new DAL();
msg = objDAL.InsertLPPalletData(objPallet);
}
}
catch (Exception ex)
{
//msg = ex.Message.ToString();
throw new Exception(ex.Message.ToString());
}
return(msg);
}
public PalletSelection GetPalletTblDataById(int Id)
{
PalletSelection objPallet = null;
StringBuilder sbQry = new StringBuilder();
sbQry.Append("select a.Id,a.Company,a.Plant,a.SONum,a.ShipByDate,a.PartNum,a.PartDesc,a.ActQty, UD108Key1, DPNum, ");
sbQry.Append("SysRowId, SONum, ShipByDate, PartNum, PartDesc, ActQty, a.PalletNum,a.LotNum,a.PalletQty,a.UOMCode,a.VerifyStatus, ");
sbQry.Append($"a.FromBinNum, a.FromWareHouse, a.ToWareHouse, a.ToBinNum from WOLPPalletSelectionTbl a Where a.Id={Id}");
using (SqlConnection connection = new SqlConnection(StagingConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objPallet = new PalletSelection();
objPallet.Id = Convert.ToInt32(reader["Id"]);
objPallet.Company = reader["Company"].ToString();
objPallet.Plant = reader["Plant"].ToString();
objPallet.UD108Key1 = reader["UD108Key1"].ToString();
objPallet.SysRowId = reader["SysRowId"].ToString();
objPallet.SONum = Convert.ToInt32(reader["SONum"]);
objPallet.ShipByDate = Convert.ToDateTime(reader["ShipByDate"]);
objPallet.PartNum = reader["PartNum"].ToString();
objPallet.PartDesc = reader["PartDesc"].ToString();
objPallet.ActQty = Convert.ToDecimal(reader["ActQty"]);
objPallet.UomCode = reader["UOMCode"].ToString();
objPallet.PalletNum = Convert.ToInt32(reader["PalletNum"]);
objPallet.LotNum = reader["LotNum"].ToString();
objPallet.PalletQty = Convert.ToDecimal(reader["PalletQty"]);
objPallet.VerifyStatus = Convert.ToBoolean(reader["VerifyStatus"]);
objPallet.DPNum = reader["DPNum"].ToString();
objPallet.FromWareHouse = reader["FromWareHouse"].ToString();
objPallet.FromBinNum = reader["FromBinNum"].ToString();
objPallet.ToWareHouse = reader["ToWareHouse"].ToString();
objPallet.ToBinNum = reader["ToBinNum"].ToString();
}
}
}
return(objPallet);
}
public PalletSelection GetLotFromLoose(string Company, string PartNum, string LotNum, ref string errMsg)
{
PalletSelection objPallet = new PalletSelection();
List <LotSuggestions> lotSuggestions = new List <LotSuggestions>();
DAL objDAL = new DAL();
objPallet = objDAL.GetLotFromLoose(Company, PartNum, LotNum, ref errMsg);
objDAL.GetLPLotSuggestions(Company, PartNum, ref lotSuggestions);
if (objPallet != null && lotSuggestions != null)
{
if (objPallet.LotNum != lotSuggestions.First().LotNum)
{
objPallet.LotCheck = $"Lot No {objPallet.LotNum} selected was not the earliest product";
}
}
return(objPallet);
}
public List <PalletSelection> GetPalletDataByPKeys(string Company, int SONum, DateTime ShipByDate)
{
List <PalletSelection> objLstPalletSelection = new List <PalletSelection>();
StringBuilder sbQry = new StringBuilder();
sbQry.Append("select a.Id,a.Company,a.Plant,a.UD108Key1, a.DPNum, a.SysRowId,a.SONum,a.ShipByDate,a.PartNum,a.PartDesc,a.ActQty ");
sbQry.Append(",a.PalletNum,a.LotNum,a.PalletQty,a.UOMCode,a.VerifyStatus from WOLPPalletSelectionTbl a ");
sbQry.Append($"Where a.SONum={SONum} and a.Company='{Company}' and a.ShipByDate='{ShipByDate.ToString("yyyy-MM-dd")}' ");
using (SqlConnection connection = new SqlConnection(StagingConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
PalletSelection objPallet = new PalletSelection();
objPallet.Id = Convert.ToInt32(reader["Id"]);
objPallet.Company = reader["Company"].ToString();
objPallet.Plant = reader["Plant"].ToString();
objPallet.UD108Key1 = reader["UD108Key1"].ToString();
objPallet.SysRowId = reader["SysRowId"].ToString();
objPallet.SONum = Convert.ToInt32(reader["SONum"]);
objPallet.ShipByDate = Convert.ToDateTime(reader["ShipByDate"]);
objPallet.PartNum = reader["PartNum"].ToString();
objPallet.PartDesc = reader["PartDesc"].ToString();
objPallet.ActQty = Convert.ToDecimal(reader["ActQty"]);
objPallet.UomCode = reader["UOMCode"].ToString();
objPallet.PalletNum = Convert.ToInt32(reader["PalletNum"]);
objPallet.LotNum = reader["LotNum"].ToString();
objPallet.PalletQty = Convert.ToDecimal(reader["PalletQty"]);
objPallet.VerifyStatus = Convert.ToBoolean(reader["VerifyStatus"]);
objPallet.DPNum = reader["DPNum"].ToString();
objLstPalletSelection.Add(objPallet);
}
}
}
return(objLstPalletSelection);
}
public string UpdateVerifiedStatusUD108(PalletSelection objPallet)
{
SqlTransaction Trans = null;
SqlCommand cmd = null;
string msg = "";
try
{
using (SqlConnection Conn = new SqlConnection(EpicorConStr))
{
Conn.Open();
Trans = Conn.BeginTransaction();
StringBuilder strQry = null;
//Header SQL
if (objPallet != null)
{
strQry = new StringBuilder();
strQry.Append($"Update UD108 set FS_Status_c = 'Loose Picking Verified' ");
strQry.Append($" Where Company=\'{objPallet.Company}\' And Key1='{objPallet.UD108Key1}' And SysRowId='{objPallet.SysRowId}' ");
//Execute insert Query
cmd = new SqlCommand(strQry.ToString(), Conn, Trans);
cmd.ExecuteNonQuery();
}
Trans.Commit();
}
}
catch (Exception ex)
{
msg = ex.Message.ToString();
//if (Trans != null)
// Trans.Rollback();
//throw new Exception(ex.Message.ToString());
}
finally
{
}
return("");
//return msg;
}
public void UpdateDataInUD108Epicor(PalletSelection objPallet)
{
SqlTransaction Trans = null;
SqlCommand cmd = null;
try
{
using (SqlConnection Conn = new SqlConnection(EpicorConStr))
{
Conn.Open();
Trans = Conn.BeginTransaction();
StringBuilder strQry = null;
//Header SQL
if (objPallet != null)
{
strQry = new StringBuilder();
strQry.Append($"Update UD108 set HHLPQty_c=HHLPQty_c + {objPallet.PalletQty} , HHLPStatus_c='{(objPallet.LoosePickStatus=="Full" ? "Full" : "Partial")}' ");
strQry.Append($", HHIsLPFull_c={(objPallet.LoosePickStatus == "Full" ? 1 : 0)} ");
strQry.Append($" Where Company=\'{objPallet.Company}\' And Key1='{objPallet.UD108Key1}' And SysRowId='{objPallet.SysRowId}' ");
//Execute insert Query
cmd = new SqlCommand(strQry.ToString(), Conn, Trans);
cmd.ExecuteNonQuery();
}
Trans.Commit();
}
}
catch (Exception ex)
{
if (Trans != null)
{
Trans.Rollback();
}
throw new Exception(ex.Message.ToString());
}
finally
{
}
}
public string InsertLPPalletData(PalletSelection objPallet)
{
string msg = string.Empty;
bool isSqlSucess = true;
SqlTransaction Trans = null;
SqlCommand cmd = null;
try
{
using (SqlConnection Conn = new SqlConnection(StagingConStr))
{
Conn.Open();
Trans = Conn.BeginTransaction();
StringBuilder strQry = null;
//Header SQL
if (objPallet != null)
{
strQry = new StringBuilder();
strQry.Append(" INSERT INTO [WOLPPalletSelectionTbl] ");
strQry.Append("([Company],[Plant],[UD108Key1], [DPNum], [SysRowId],[SONum],[ShipByDate],[PartNum],[PartDesc],[ActQty] ");
strQry.Append(",[PrevQty],[UOMCode],[PalletNum],[LotNum],[PalletQty],[FromWareHouse],[FromBinNum],[ToWareHouse] ");
strQry.Append(",[ToBinNum],[VerifyStatus],[Remark],[LoosePickStatus],[UserId]) ");
strQry.Append("Values ");
strQry.Append($" ( '{objPallet.Company}' , '{objPallet.Plant}' , '{objPallet.UD108Key1}' , '{objPallet.DPNum}' , '{objPallet.SysRowId}' , ");
strQry.Append($" {objPallet.SONum} , '{objPallet.ShipByDate.ToString("yyyy-MM-dd")}' , '{objPallet.PartNum}' , '{objPallet.PartDesc}' , ");
strQry.Append($" {objPallet.ActQty} , {objPallet.PrevQty} , '{objPallet.UomCode}' , {objPallet.PalletNum} , ");
strQry.Append($" '{objPallet.LotNum}' , {objPallet.PalletQty} , '{objPallet.FromWareHouse}' , '{objPallet.FromBinNum}' , ");
strQry.Append($" '{objPallet.ToWareHouse}','{objPallet.ToBinNum}' , {(objPallet.VerifyStatus==true?1:0)} , '{objPallet.Remark}' , '{objPallet.LoosePickStatus}' , ");
strQry.Append($" '{objPallet.UserId}' ) ");
//Execute insert Query
cmd = new SqlCommand(strQry.ToString(), Conn, Trans);
cmd.ExecuteNonQuery();
//Pending...
//Epicor POHeader and PODtl ud columns updations
UpdateDataInUD108Epicor(objPallet);
}
Trans.Commit();
msg = "";
// msg += Label.Generate(objPOHeader);
}
}
catch (Exception ex)
{
msg += $"\n{ ex.Message.ToString()}";
isSqlSucess = false;
throw new Exception(msg);
}
finally
{
try
{
if (Trans != null && !isSqlSucess)
{
Trans.Rollback();
}
}
catch (Exception ex)
{
string exmsg = ex.Message.ToString();
}
}
return(msg);
}
public PalletSelection GetLotFromLoose(string Company, string PartNum, string LotNum, ref string sErrMsg)
{
sErrMsg = string.Empty; string sLotNum = string.Empty; string TranType = string.Empty;
PalletSelection objPallet = null;
StringBuilder sbQry = new StringBuilder();
sbQry.Append("select TOP(1) TranType from erp.PartTran ");
sbQry.Append($"where Company = '{Company}' and PartNum = '{PartNum}' and LotNum = '{LotNum}' order by TranNum desc");
using (SqlConnection connection = new SqlConnection(EpicorConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TranType = reader["TranType"].ToString();
}
}
connection.Close();
}
if (TranType == "STK-INS" || TranType == "INS-DMR")
{
sErrMsg = "Pending QA Release";
}
//Added by AMIN Start
//Issue #155_2
sbQry = new StringBuilder();
sbQry.Append("Select case");
sbQry.Append($" when (select top 1 PartNum from erp.partlot where Company = '{Company}' and PartNum = '{PartNum}' and LotNum='{LotNum}') is null then 'Lot No {LotNum} does not belong to the selected product!'");
sbQry.Append($" when (select top 1 PartNum from erp.PartBin pb inner join WhseBin w on pb.Company = '{Company}'");
sbQry.Append($" and pb.WarehouseCode = w.WarehouseCode and pb.BinNum = w.BinNum where pb.Company = w.Company");
sbQry.Append($" and pb.PartNum='{PartNum}' and w.FS_DefaultLoose_c = 1 and pb.LotNum='{LotNum}') is null then 'Lot No {LotNum} does not belong to Loose Warehouse'");
sbQry.Append(" else '0' end as ErrMsg");
string ErrMsg = string.Empty;
using (SqlConnection connection = new SqlConnection(EpicorConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ErrMsg = reader["ErrMsg"].ToString();
}
}
}
if (ErrMsg != "0")
{
//sErrMsg = ErrMsg;
throw new Exception(ErrMsg);
}
//Added by AMIN End
else
{
sbQry = new StringBuilder();
sbQry.Append("Select x.Company,x.PartNum,x.LotNum,x.OnhandQty,x.WarehouseCode as FromWareHouse,x.BinNum as FromBinNum ");
sbQry.Append(", isnull((Select top 1 a.WarehouseCode from WhseBin a where a.Company=x.Company and a.FS_DefaultReserve_c=1),'') as ToWareHouse ");
sbQry.Append(", isnull((Select top 1 a.BinNum from WhseBin a where a.Company=x.Company and a.FS_DefaultReserve_c=1),'') as ToBinNum ");
sbQry.Append("from erp.PartBin x inner join WhseBin z on x.Company=z.Company and x.WarehouseCode=z.WarehouseCode and x.BinNum=z.BinNum ");
sbQry.Append($"where z.FS_DefaultLoose_c=1 and z.FS_DefaultReserve_c=0 and x.Company='{Company}' and x.PartNum='{PartNum}' and x.LotNum='{LotNum}' ");
using (SqlConnection connection = new SqlConnection(EpicorConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sbQry.ToString(), connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
objPallet = new PalletSelection();
objPallet.Company = reader["Company"].ToString();
objPallet.PartNum = reader["PartNum"].ToString();
objPallet.PartDesc = string.Empty;
objPallet.LotNum = reader["LotNum"].ToString();
sLotNum = reader["LotNum"].ToString();
objPallet.PalletQty = Convert.ToDecimal(reader["OnhandQty"]);
objPallet.FromWareHouse = reader["FromWareHouse"].ToString();
objPallet.FromBinNum = reader["FromBinNum"].ToString();
objPallet.ToWareHouse = reader["ToWareHouse"].ToString();
objPallet.ToBinNum = reader["ToBinNum"].ToString();
}
}
}
return(objPallet);
}
}