public HttpResponseMessage Get(string Company = null, string PartNum = null, string LotNum = null)
{
try
{
EpicorDbContext epicorDbContext = new EpicorDbContext();
//string Qry = $"Select a.Company,a.PartNum,a.LotNum,a.WarehouseCode as Warehse,a.BinNum, 'Empty' as SuppId,a.OnhandQty as Qty,GETDATE() as ExpriyDate from erp.PartBin a Where a.Company='{Company}' and a.PartNum='{PartNum}' and a.LotNum='{LotNum}' ";
string Qry = $"Select a.Company,a.PartNum,a.LotNum,a.WarehouseCode as Warehse, a.BinNum, 'Empty' as SuppId,a.OnhandQty as Qty,GETDATE() as ExpriyDate from erp.PartBin a INNER JOIN Warehse b ON a.Company = b.Company AND a.WarehouseCode = b.WarehouseCode Where a.Company = '{Company}' and a.PartNum = '{PartNum}' and a.LotNum = '{LotNum}' and b.U_KS076_WHForOutgoing_c <> 1 and b.FS_ProdArea_c <> 1 and b.FS_ProdLine_c <> 1 and b.FS_LooseArea_c <> 1 and b.FS_PalletArea_c <> 1"; //Guide by Tsen
//List<PalletTypeVw> objPTLst = epicorDbContext.PalletTypes.Where(w => w.Company == Company).ToList();
//List<PickRawMtrlVw> objPRMLst = epicorDbContext.PickRawMtrls.Where(w => w.Company == Company).ToList();
// List<WarehseModel> WarehseModels = epicorDbContext.Database.SqlQuery<WarehseModel>(Qry, new SqlParameter("@Company", Company),new SqlParameter("@PartNum", PartNum), new SqlParameter("@LotNum", LotNum)).ToList<List<WarehseModel>>();
List <WarehseModel> WarehseModels = epicorDbContext.Database.SqlQuery <WarehseModel>(Qry).ToList();
//List<WarehseModel> WarehseModels = new List<WarehseModel>
//{
//new WarehseModel { Company = "CSTP", PartNum = "Part1", LotNum = "", Warehse="WH1",BinNum="BN1" },
//new WarehseModel { Company = "CSTP", PartNum = "Part1", LotNum = "" , Warehse="WH2",BinNum="BN2"},
//new WarehseModel { Company = "CSTP", PartNum = "Part1", LotNum = "" , Warehse="WH3",BinNum="BN3"}
//};
return(Request.CreateResponse(HttpStatusCode.OK, WarehseModels));
}
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()));
}
}
// GET: api/PickRawMtrl
public HttpResponseMessage Get(string Company, string UserId = null)
{
try
{
EpicorDbContext epicorDbContext = new EpicorDbContext();
List <UomDecPoint> uomDecPointLst = new List <UomDecPoint>();
List <PickUomDecPoint> pickUomDecPointLst = new List <PickUomDecPoint>();
List <PalletTypeVw> objPTLst = epicorDbContext.PalletTypes.Where(w => w.Company == Company).ToList();
List <PickRawMtrlVw> objPRMLst = epicorDbContext.PickRawMtrls.Where(w => w.Company == Company && w.UserId == UserId).ToList();
foreach (PickRawMtrlVw row in objPRMLst)
{
//string stdQry = "select isnull(PU.ConvFactor,1) as ConvFactor, u.UOMCode as UOM from part p " +
// "left join UOMConv u on p.Company = u.Company and p.UOMClassID = u.UOMClassID and u.FS_MTIssueUOM_c = 1 " +
// "left join erp.PartUOM PU on pu.Company = u.Company and pu.UOMCode = u.UOMCode " +
// "and pu.PartNum = p.PartNum " +
// $"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}'";
//***7/5/19
//string stdQry = "select top 1 isnull(PU.ConvFactor,1) as ConvFactor, PU.UOMCode as UOM from part p left join " +
// "PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.FS_PartMtlPick_c = 1 " +
// $"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}'";
//var stdConv = epicorDbContext.Database.SqlQuery<stdConvPRMModel>(stdQry).SingleOrDefault();
//***7/5/19
//string pickQry =
// "Select isnull(ConvFactor, 0) as ConvFactor, isnull(UOM, '') as UOM from ( " +
// "Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, PU.UOMCode as UOM from part p " +
// "left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.FS_PartMtlPick_c = 1 " +
// "left join UOMConv u on u.Company = p.Company and u.UOMCode = pu.UOMCode and u.UOMClassID = p.UOMClassID " +
// $"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' ) A";
//string incomingQry = "Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, u.UOMCode as UOM from part p " +
// $"left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.UOMCode = '{row.UOM}' " +
// "left join UOMConv u on u.Company = p.Company and u.UOMClassID = p.UOMClassID " +
// $"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' and u.UOMCode = '{row.UOM}'";
string pickQry =
"IF NOT EXISTS (Select isnull(ConvFactor, 0) as ConvFactor, isnull(UOM, '') as UOM from ( " +
"Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, PU.UOMCode as UOM from part p " +
"left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.FS_PartMtlPick_c = 1 " +
"left join UOMConv u on u.Company = p.Company and u.UOMCode = pu.UOMCode and u.UOMClassID = p.UOMClassID " +
$"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' ) A ) " +
$"BEGIN Select 0.0 as ConvFactor, '' as UOM END " +
$"ELSE Select isnull(ConvFactor, 0) as ConvFactor, isnull(UOM, '') as UOM from ( " +
"Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, PU.UOMCode as UOM from part p " +
"left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.FS_PartMtlPick_c = 1 " +
"left join UOMConv u on u.Company = p.Company and u.UOMCode = pu.UOMCode and u.UOMClassID = p.UOMClassID " +
$"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' ) A";
string incomingQry = "IF NOT EXISTS (Select isnull(ConvFactor, 0) as ConvFactor, isnull(UOM, '') as UOM from (Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, u.UOMCode as UOM from part p " +
$"left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.UOMCode = '{row.UOM}' " +
"left join UOMConv u on u.Company = p.Company and u.UOMClassID = p.UOMClassID " +
$"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' and u.UOMCode = '{row.UOM}' ) A ) " +
$"BEGIN Select 0.0 as ConvFactor, '' as UOM END " +
$"ELSE Select isnull(ConvFactor, 0) as ConvFactor, isnull(UOM, '') as UOM from (Select isnull(pu.ConvFactor,u.ConvFactor) as ConvFactor, u.UOMCode as UOM from part p " +
$"left join PartUOM PU on pu.Company = p.Company and pu.PartNum = p.PartNum and pu.UOMCode = '{row.UOM}' " +
"left join UOMConv u on u.Company = p.Company and u.UOMClassID = p.UOMClassID " +
$"where p.PartNum = '{row.PartNum}' and p.Company = '{row.Company}' and u.UOMCode = '{row.UOM}' ) A";
//convfactor = pick/incoming
var pickConv = epicorDbContext.Database.SqlQuery <stdConvPRMModel>(pickQry).SingleOrDefault();
if (pickConv.ConvFactor == 0 && pickConv.UOM == "")
{
throw new Exception("Error - No Pick Material UOM! (Part: " + row.PartNum + ")");
}
var incomingConv = epicorDbContext.Database.SqlQuery <stdConvPRMModel>(incomingQry).SingleOrDefault();
if (incomingConv.ConvFactor == 0 && incomingConv.UOM == "")
{
throw new Exception("Error - Can't find the UOM for " + row.UOM + " Part: " + row.PartNum + ")");
}
if (pickConv.ConvFactor == 0)
{
throw new Exception("Error - Pick ConvFactor cannot be 0 ! (Part: " + row.PartNum + ")");
}
if (incomingConv.ConvFactor == 0)
{
throw new Exception("Error - Incoming ConvFactor cannot be 0 ! (Part: " + row.PartNum + ")");
}
var stdConv = new stdConvPRMModel();
stdConv.UOM = pickConv.UOM;
stdConv.ConvFactor = pickConv.ConvFactor / incomingConv.ConvFactor;
string strQryNumOfDecimal = "select u.UOMCode as UOM,u.NumOfDec as DecPoint from UOM u " +
$"where u.UOMCode = '{row.UOM}' and u.Company = '{row.Company}'";
UomDecPoint numOfDecimals = epicorDbContext.Database.SqlQuery <UomDecPoint>(strQryNumOfDecimal).SingleOrDefault();
uomDecPointLst.Add(numOfDecimals);
////string tuQry = "select top 1 pd.OrdQty/pd.XOrdQty as ConvFactor, pd.PUM as UOM " +
//string tuQry = "select top 1 iif(pd.OrdQty > pd.XOrdQty, pd.OrdQty / pd.XOrdQty, iif(pd.XOrdQty > OrdQty, pd.XOrdQty / OrdQty, 1)) as ConvFactor, pd.PUM as UOM " +
//"from " + ConnectionLibrary.Conn.HandHeldDB + ".dbo.PODetailTbl pd left join Part p on p.Company = pd.Company " +
//"and p.PartNum = pd.PartNum left join PartClass pc on pc.Company = p.Company " +
//"and pc.ClassID = p.ClassID left join partlot pl on pl.Company = p.Company and pl.PartNum = p.PartNum " +
//$"where p.Company = '{row.Company}' and p.partnum = '{row.PartNum}' " +
//"order by iif(pc.U_KS147_InventoryIssuance_c = 'FIFO', pl.FirstRefDate, pl.ExpirationDate), pd.CreatedDate";
//var tuConv = epicorDbContext.Database.SqlQuery<tuConvPRMModel>(tuQry).SingleOrDefault();
//if (stdConv != null)
//{
// if (tuConv != null)
// {
// if (stdConv.UOM == tuConv.UOM)
// {
// row.ConvFactor = tuConv.ConvFactor;
// }
// else
// {
// row.ConvFactor = stdConv.ConvFactor;
// }
// }
// else
// {
// row.ConvFactor = stdConv.ConvFactor;
// }
//}
row.ConvFactor = stdConv.ConvFactor;
row.PickUOM = stdConv.UOM;
//Tsen: Is it possible to check the conversion =0 in PartUOM then check the conversation in UOMConv
//if (row.ConvFactor == 0)
//{
// //select Top (1) UOMCode as UOM, ConvFactor from UOMConv where Company = 'PMSB' and UOMCode = 'BAG'
// string UOMConv = $"select Top (1) ConvFactor, UOMCode as UOM from UOMConv where Company = '{row.Company}' and UOMCode = {row.PickUOM}";
// var UOMConvFactor = epicorDbContext.Database.SqlQuery<stdConvPRMModel>(UOMConv).SingleOrDefault();
// row.ConvFactor = UOMConvFactor.ConvFactor;
//}
//get PickUOM Dec Point
if (!string.IsNullOrEmpty(row.PickUOM))
{
string strQryPickUOMDecPoint = "select u.UOMCode as PickUOM,u.NumOfDec as PickDecPoint from UOM u " +
$"where u.UOMCode = '{row.PickUOM}' and u.Company = '{row.Company}'";
PickUomDecPoint pickNumOfDecimals = epicorDbContext.Database.SqlQuery <PickUomDecPoint>(strQryPickUOMDecPoint).SingleOrDefault();
pickUomDecPointLst.Add(pickNumOfDecimals);
}
}
List <PalletTypeModel> objPTModelLst = objPTLst.Select(s => new PalletTypeModel
{
Company = s.Company,
PartNum = s.PartNum,
UOM = s.UOM
}
).ToList();
List <PickRawMtrlModel> objPRMModelLst = objPRMLst.Select(m => new PickRawMtrlModel
{
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.Qty,
DynamicPick = m.DynamicPick,
ConvFactor = m.ConvFactor,
UOM = m.UOM,
// UOMDecPoint= uomDecPointLst.Where(w=>w.UOM==m.UOM).FirstOrDefault().Select(s=>s.DecPoint),
PickQty = m.PickQty,
PickUOM = m.PickUOM,
FromWarehse = m.FromWarehse,
FromBinNum = m.FromBinNum,
ToWarehse = m.ToWarehse,
ToBinNum = m.ToBinNum,
PalletType = m.PalletType,
PalletQty = m.PalletQty,
PalletTypeInfo = objPTModelLst,
UserId = m.UserId,
SysRevID = m.SysRevID,
SysRowId = m.SysRowId
}
).ToList();
foreach (var decP in uomDecPointLst)
{
objPRMModelLst.Where(w => w.UOM == decP.UOM).ToList().ForEach(f => f.UOMDecPoint = decP.DecPoint);
}
foreach (var decP in pickUomDecPointLst)
{
objPRMModelLst.Where(w => w.PickUOM == decP.PickUOM).ToList().ForEach(f => f.PickUOMDecPoint = decP.PickDecPoint);
}
return(Request.CreateResponse(HttpStatusCode.OK, objPRMModelLst));
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex.Message.ToString()));
}
}
