public HttpResponseMessage Get(string Company, string UserId = null)
{
try
{
WIPMCReceipientVerification.EpicorDbContext epicorDbContext = new WIPMCReceipientVerification.EpicorDbContext();
List <ReasonTypeVw> objRTLst = epicorDbContext.ReasonTypes.Where(w => w.Company == Company).ToList();
StagingDbContext stagingDbContext = new StagingDbContext();
List <PMCReceipientVerificationVw> objPMCRVLst = stagingDbContext.PMCReceipientVerificationVws.Where(w => w.Company == Company).ToList();
//var test=epicorDbContext.Database.SqlQuery<string>("Select JobNum from erp.JobMtl Where Company=@Company", new SqlParameter("@Company", Company)).FirstOrDefault();
List <ReasonModel> objRTModelLst = objRTLst.Select(s => new ReasonModel
{
Company = s.Company,
ReasonCode = s.ReasonCode,
ReasonDesc = s.ReasonDesc
}
).ToList();
List <PMCReceipientVerificationModel> objPMCRVModelLst = objPMCRVLst.Select(m => new PMCReceipientVerificationModel
{
PickRawMtrlFKId = m.PickRawMtrlFKId,
Company = m.Company,
Plant = m.Plant,
JobNum = m.JobNum,
RequestDate = m.RequestDate,
PartNum = m.PartNum,
PartDesc = m.PartDesc,
LotNo = m.LotNo,
TuId = m.TuId,
ActQty = m.ActQty,
Qty = m.VerifyQty,
UOM = m.UOM,
PickQty = m.PickQty,
PickUOM = m.PickUOM,
FromWarehse = m.FromWarehse,
FromBinNum = m.FromBinNum,
ToWarehse = m.ToWarehse,
ToBinNum = m.ToBinNum,
PalletType = m.PalletType,
PalletQty = m.PalletQty,
ReasonTypes = objRTModelLst,
UserId = m.UserId,
IsEpiRow = m.IsEpiRow,
ts = m.ts
}
).ToList();
return(Request.CreateResponse(HttpStatusCode.OK, objPMCRVModelLst));
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message.ToString()));
}
}
public HttpResponseMessage Get(string Company, string PartNum, string LotNum, string TuId)
{
try
{
StagingDbContext stagingDbContext = new StagingDbContext();
string Qry = $"Select TuConv,Qty,UOMCode from [" + ConnectionLibrary.Conn.HandHeldDB + $"].[dbo].[PODetailTbl] Where Company='{Company}' and PartNum='{PartNum}' and LotNum='{LotNum}' and TuId='{TuId}' ";
var tuConv = stagingDbContext.Database.SqlQuery <tuScanConvPRMModel>(Qry).FirstOrDefault();
if (tuConv != null)
{
return(Request.CreateResponse(HttpStatusCode.OK, tuConv));
}
else
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "Tuid Not Found!"));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex));
}
}
// GET: api/ProdPMCReceipientVerify/5
//public string Get(int id)
//{
// return "value";
//}
// POST: api/ProdPMCReceipientVerify
public HttpResponseMessage Post([FromBody] PMCReceipientVerificationModel obj)
{
try
{
if (obj != null)
{
PMCReceipientVerification objPMCRV = new PMCReceipientVerification
{
PickRawMtrlFKId = obj.PickRawMtrlFKId,
Company = obj.Company,
Plant = obj.Plant,
JobNum = obj.JobNum,
RequestDate = obj.RequestDate,
PartNum = obj.PartNum,
PartDesc = obj.PartDesc,
LotNo = obj.LotNo,
TuId = obj.TuId,
ActQty = obj.ActQty,
Qty = obj.Qty,
UOM = obj.UOM,
PickQty = obj.PickQty,
PickUOM = obj.PickUOM,
FromWarehse = obj.FromWarehse,
FromBinNum = obj.FromBinNum,
ToWarehse = obj.ToWarehse,
ToBinNum = obj.ToBinNum,
//PalletType = obj.PalletType,
//PalletQty = obj.PalletQty,
ReasonCode = obj.ReasonCode,
ReasonDesc = obj.ReasonDesc,
RejQty = obj.RejQty,
//Status = "Accepted",
UserId = obj.UserId,
CreatedUtc = DateTime.Now
};
StagingDbContext stagingDbContext = new StagingDbContext();
List <PMCReceipientVerification> objPMCRVs = new List <PMCReceipientVerification>();
if (objPMCRV.RejQty <= 0)
{
objPMCRV.Status = "Accepted";
//TODO
if (objPMCRV.Status == "Accepted")
{
if (string.IsNullOrEmpty(objPMCRV.ToBinNum))
{
throw new Exception("No To Bin Num!");
}
//Normal InvTrans BO - If Accept only
InvTransfer objInvTran = new InvTransfer();
objInvTran.company = objPMCRV.Company;
objInvTran.frmBinNum = objPMCRV.FromBinNum;
objInvTran.frmLotNum = objPMCRV.LotNo;
objInvTran.frmWarehouse = objPMCRV.FromWarehse;
objInvTran.toBinNum = objPMCRV.ToBinNum;
objInvTran.toLotNum = objPMCRV.LotNo;
objInvTran.toWarehouse = objPMCRV.ToWarehse;
objInvTran.partNum = objPMCRV.PartNum;
objInvTran.plant = objPMCRV.Plant;
objInvTran.qty = objPMCRV.Qty;
objInvTran.uom = objPMCRV.UOM;
objInvTran.id = "PMCReceipientVer";
InventoryTransferServices objInvTranService = new InventoryTransferServices();
objInvTranService.doInvTransfer(objInvTran);
}
//Savedata to staging db
PMCReceipientVerification objNewPMCRV = stagingDbContext.PMCReceipientVerifications.Add(objPMCRV);
stagingDbContext.SaveChanges();
}
else
{
PMCReceipientVerification tempDataReject = new PMCReceipientVerification();
PMCReceipientVerification tempDataAccept = new PMCReceipientVerification();
//Create accepted one
tempDataAccept = objPMCRV;
decimal normalQty = objPMCRV.Qty;
decimal rejQty = objPMCRV.RejQty;
decimal Qty = normalQty - rejQty;
if (Qty > 0)
{
tempDataAccept.Qty = Qty;
tempDataAccept.Status = "Accepted";
tempDataAccept.RejQty = 0;
tempDataAccept.ReasonCode = "";
tempDataAccept.ReasonDesc = "";
//TODO
if (tempDataAccept.Status == "Accepted")
{
if (string.IsNullOrEmpty(tempDataAccept.ToBinNum))
{
throw new Exception("No To Bin Num!");
}
//Normal InvTrans BO - If Accept only
InvTransfer objInvTran = new InvTransfer();
objInvTran.company = tempDataAccept.Company;
objInvTran.frmBinNum = tempDataAccept.FromBinNum;
objInvTran.frmLotNum = tempDataAccept.LotNo;
objInvTran.frmWarehouse = tempDataAccept.FromWarehse;
objInvTran.toBinNum = tempDataAccept.ToBinNum;
objInvTran.toLotNum = tempDataAccept.LotNo;
objInvTran.toWarehouse = tempDataAccept.ToWarehse;
objInvTran.partNum = tempDataAccept.PartNum;
objInvTran.plant = tempDataAccept.Plant;
objInvTran.qty = tempDataAccept.Qty;
objInvTran.uom = tempDataAccept.UOM;
objInvTran.id = "PMCReceipientVer";
InventoryTransferServices objInvTranService = new InventoryTransferServices();
objInvTranService.doInvTransfer(objInvTran);
}
PMCReceipientVerification objNewPMCRVAcc = stagingDbContext.PMCReceipientVerifications.Add(tempDataAccept);
stagingDbContext.SaveChanges();
}
//Create rejected one
tempDataReject = objPMCRV;
tempDataReject.RejQty = obj.RejQty;
tempDataReject.ReasonCode = obj.ReasonCode;
tempDataReject.Status = "Rejected";
DAL dal = new DAL();
string rejBin = dal.GetRejBin(obj.Company);
tempDataReject.ToBinNum = rejBin;
tempDataReject.ToWarehse = obj.ToWarehse;
if (string.IsNullOrEmpty(tempDataReject.ToBinNum))
{
throw new Exception("No Reject Bin!");
}
PMCReceipientVerification objNewPMCRV = stagingDbContext.PMCReceipientVerifications.Add(tempDataReject);
stagingDbContext.SaveChanges();
}
BusinessLayer BL = new BusinessLayer();
string PalletType = "";
decimal PalletQty = 0;
BL.GetPalletDetails(objPMCRV.PickRawMtrlFKId, out PalletType, out PalletQty);
string PalletWareHouse = "";
string PalletBinNum = "";
BL.GetPalletWarehouse(objPMCRV.Company, out PalletWareHouse, out PalletBinNum);
//TODO
// PalletType InvTrans BO - If Rej Or Accept have to do
if (!obj.IsEpiRow && !string.IsNullOrEmpty(PalletType))
{
InvTransfer objInvTranPalletType = new InvTransfer();
objInvTranPalletType.company = objPMCRV.Company;
objInvTranPalletType.frmBinNum = PalletBinNum;
//objInvTranPalletType.frmLotNum = objPMCRV.LotNo;
objInvTranPalletType.frmWarehouse = PalletWareHouse;
objInvTranPalletType.toBinNum = objPMCRV.ToBinNum;
//objInvTranPalletType.toLotNum = objPMCRV.LotNo;
objInvTranPalletType.toWarehouse = objPMCRV.ToWarehse;
objInvTranPalletType.partNum = PalletType;
objInvTranPalletType.plant = objPMCRV.Plant;
objInvTranPalletType.qty = PalletQty;
//objInvTranPalletType.uom = objPMCRV.UOM;
objInvTranPalletType.id = "PMCReceipientVer";
InventoryTransferServices objInvTranService = new InventoryTransferServices();
objInvTranService.doInvTransfer(objInvTranPalletType);
}
//Validate obj.ts with db to know any one changed data or not
// 1) PalletType InvTrans BO - If Rej Or Accept have to do
// 2) Normal InvTrans BO - If Accept only
// 3) Insert Recrod in to Staging DB
// 4) Update Status (isPMCVerify=true or false And isPalletTypeInvTranBO = true or not) in PickRawMtrl table of Staging DB
string UpdQry = $"Update [" + ConnectionLibrary.Conn.HandHeldDB + $"].[dbo].[WIPickRawMtrlTbl] Set [IsPMCVerify]=1 , IsPalletTypeInvTransBO={(!obj.IsEpiRow?1:0)} Where Id={obj.PickRawMtrlFKId} ";
int i = stagingDbContext.Database.ExecuteSqlCommand(UpdQry);
return(Request.CreateResponse(HttpStatusCode.OK, "Inserted Successfully!"));
}
else
{
throw new Exception("PMCReceipientVerification Model is Null!");
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message.ToString()));
}
}
// POST: api/PickRawMtrl
public HttpResponseMessage Post([FromBody] PickRawMtrlModel objPickRawMtrlModel)
{
try
{
if (objPickRawMtrlModel != null)
{
if (string.IsNullOrEmpty(objPickRawMtrlModel.ToBinNum))
{
throw new Exception("To Bin is required!"); //No Transit Bin
}
PickRawMtrl objPRM = new PickRawMtrl
{
Company = objPickRawMtrlModel.Company,
Plant = objPickRawMtrlModel.Plant,
JobNum = objPickRawMtrlModel.JobNum,
RequestDate = objPickRawMtrlModel.RequestDate,
PartNum = objPickRawMtrlModel.PartNum,
PartDesc = objPickRawMtrlModel.PartDesc,
LotNo = objPickRawMtrlModel.LotNo,
TuId = objPickRawMtrlModel.TuId,
ActQty = objPickRawMtrlModel.ActQty,
Qty = objPickRawMtrlModel.Qty,
UOM = objPickRawMtrlModel.UOM,
PickQty = objPickRawMtrlModel.PickQty,
PickUOM = objPickRawMtrlModel.PickUOM,
FromWarehse = objPickRawMtrlModel.FromWarehse, //Must from Transit Bin. To here is refer to UD21. ShortChar08
FromBinNum = objPickRawMtrlModel.FromBinNum, //Must from Transit Bin. To here is refer to UD21. ShortChar08
ToWarehse = objPickRawMtrlModel.ToWarehse, //To PMC Wh . Refer to UD21. ShortChar03
ToBinNum = objPickRawMtrlModel.ToBinNum, //TO PMC Bin . Refer to UD21. ShortChar04
//FromWarehse = objPickRawMtrlModel.FromWarehse,
//FromBinNum = objPickRawMtrlModel.FromBinNum,
//ToWarehse = objPickRawMtrlModel.ToWarehse,
//ToBinNum = objPickRawMtrlModel.ToBinNum,
PalletType = objPickRawMtrlModel.PalletType,
PalletQty = objPickRawMtrlModel.PalletQty,
UserId = objPickRawMtrlModel.UserId,
SysRowId = objPickRawMtrlModel.SysRowId,
CreatedUtc = DateTime.Now,
ConvFactor = objPickRawMtrlModel.ConvFactor,
DynamicPick = objPickRawMtrlModel.DynamicPick
};
//Validation
if (string.IsNullOrEmpty(objPRM.Company))
{
throw new Exception("Company is Require!");
}
if (string.IsNullOrEmpty(objPRM.PartNum))
{
throw new Exception("PartNum is Require!");
}
if (string.IsNullOrEmpty(objPRM.LotNo))
{
throw new Exception("LotNum is Require!");
}
if (string.IsNullOrEmpty(objPRM.FromWarehse))
{
throw new Exception("FromWarehse is Require!");
}
if (string.IsNullOrEmpty(objPRM.FromBinNum))
{
throw new Exception("FromBinNum is Require!");
}
if (objPRM.Qty <= 0)
{
throw new Exception("Qty is Require!");
}
if (string.IsNullOrEmpty(objPRM.PalletType))
{
//throw new Exception("PalletType is Require!");
}
if (!string.IsNullOrEmpty(objPRM.PalletType) && objPRM.PalletQty <= 0)
{
throw new Exception("PalletQty is Require!");
}
EpicorDbContext epicorDbContext = new EpicorDbContext();
//DBValidation for (Lot,FromWH and FromBin,OnHandQty,ToWH ,ToBin) And Also for (PalletType and Qty)
StringBuilder sbVQry = new StringBuilder();
sbVQry.Append(" select case ");
sbVQry.Append($" When (select top 1 LotNum from PartLot where Company='{objPRM.Company}' and PartNum='{objPRM.PartNum}' and LotNum='{objPRM.LotNo}' ) is null then 'LotNum {objPRM.LotNo} was not found!' ");
sbVQry.Append($" When (select top 1 WarehouseCode as FromWH from erp.PartWhse where Company='{objPRM.Company}' and PartNum='{objPRM.PartNum}' and WarehouseCode='{objPRM.FromWarehse}' ) is null then 'FromWarehouse {objPRM.FromWarehse} not found!' ");
sbVQry.Append($" When (select top 1 b.BinNum as FromBin from erp.PartWhse a inner join WhseBin b on a.Company=b.Company and a.WarehouseCode=b.WarehouseCode where a.Company='{objPRM.Company}' and a.PartNum='{objPRM.PartNum}' and a.WarehouseCode='{objPRM.FromWarehse}' and b.BinNum='{objPRM.FromBinNum}' ) is null then 'FromBinNum {objPRM.FromBinNum} not found!' ");
sbVQry.Append($" When (select Top 1 (OnhandQty-AllocatedQty) as Qty from erp.PartBin where Company='{objPRM.Company}' and PartNum='{objPRM.PartNum}' and LotNum='{objPRM.LotNo}' and OnhandQty-AllocatedQty >= {objPRM.Qty} ) is null then 'OnHandQty not Enough!' ");
sbVQry.Append($" When (select top 1 WarehouseCode as ToWH from erp.PartWhse where Company='{objPRM.Company}' and PartNum='{objPRM.PartNum}' and WarehouseCode='{objPRM.ToWarehse}' ) is null then 'ToWarehouse {objPRM.ToWarehse} not found!' ");
sbVQry.Append($" When (select top 1 b.BinNum as ToBin from erp.PartWhse a inner join WhseBin b on a.Company=b.Company and a.WarehouseCode=b.WarehouseCode where a.Company='{objPRM.Company}' and a.PartNum='{objPRM.PartNum}' and a.WarehouseCode='{objPRM.ToWarehse}' and b.BinNum='{objPRM.ToBinNum}' ) is null then 'ToBinNum {objPRM.ToBinNum} not found!' ");
sbVQry.Append(" else '0' end ");
string errMsg = epicorDbContext.Database.SqlQuery <string>(sbVQry.ToString()).SingleOrDefault();
if (errMsg != "0")
{
throw new Exception(errMsg);
}
//Validation of SysRevId
//string revQry = "Select SysRevId from erp.JobMtl Where SysRowId=@SysRowId";
//EpicorDbContext epicorDbContext = new EpicorDbContext();
//Type obj=null;
//epicorDbContext.Database.SqlQuery(obj, revQry, new SqlParameter("@SysRowId", objPickRawMtrlModel.SysRowId.ToString()));
//string epiSysRevId = obj.ToString();
//Do Inventory Transfer to PMC Warehouse Transit Bin (Table WhseBin, Column FS_PMCTransit_c)
InvTransfer objInvTran = new InvTransfer();
objInvTran.company = objPickRawMtrlModel.Company;
objInvTran.frmBinNum = objPickRawMtrlModel.FromBinNum;
objInvTran.frmLotNum = objPickRawMtrlModel.LotNo;
objInvTran.frmWarehouse = objPickRawMtrlModel.FromWarehse;
objInvTran.toBinNum = objPickRawMtrlModel.ToBinNum; //move to transit bin
objInvTran.toLotNum = objPickRawMtrlModel.LotNo;
objInvTran.toWarehouse = objPickRawMtrlModel.ToWarehse;
objInvTran.partNum = objPickRawMtrlModel.PartNum;
objInvTran.plant = objPickRawMtrlModel.Plant;
objInvTran.qty = objPickRawMtrlModel.Qty;
objInvTran.uom = objPickRawMtrlModel.UOM;
objInvTran.id = "PickRawMtl";
InventoryTransferServices objInvTranService = new InventoryTransferServices();
objInvTranService.doInvTransfer(objInvTran);
//Inventory Transfer End
StagingDbContext stagingDbContext = new StagingDbContext();
PickRawMtrl objNewPRM = stagingDbContext.PickRawMtrls.Add(objPRM);
stagingDbContext.SaveChanges();
string QryCheckQty = $"IF NOT EXISTS (Select Top 1 Qty from dbo.vwHH_WIPickRawMtrl Where SysRowID='{objPickRawMtrlModel.SysRowId}' ) BEGIN Select 0.0 as Qty END ELSE (Select Top 1 Qty from dbo.vwHH_WIPickRawMtrl Where SysRowID='{objPickRawMtrlModel.SysRowId}')";
decimal pickedQty = epicorDbContext.Database.SqlQuery <decimal>(QryCheckQty.ToString()).SingleOrDefault();
if (pickedQty - objPRM.Qty <= 0)
{
string Qry = $"Update [Ice].[UD21] Set [CheckBox01]=1 Where SysRowID='{objPickRawMtrlModel.SysRowId}' ";
int i = epicorDbContext.Database.ExecuteSqlCommand(Qry);
}
return(Request.CreateResponse(HttpStatusCode.OK, pickedQty - objPRM.Qty));
}
else
{
throw new Exception("PickRawMtrl is Null!");
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message.ToString()));
}
}
