public IList<ContractPricingModel> getContractPricingData(IList<string> ItemList)
{
IList<ContractPricingModel> objResult = new List<ContractPricingModel>();
int? priVal = 1;
var abPriorityVal = objABPriority.GetSingle(ab => ab.Priority == priVal);
foreach (string item in ItemList)
{
var existingQuery = from r in objResult
where r.ItemNo2nd.Contains(item)
select r;
if (existingQuery.Count() > 0)
{
objResult.Add(existingQuery.FirstOrDefault());
}
else
{
ContractPricingModel cpm = new ContractPricingModel();
F41010Model itemModel = _F41010Manager.GetDataByItemNo2nd(item);
F58ZX003 ABInfo = objABPrice.GetSingle(x => x.APLITM.Trim().Equals(item) && x.APAN8.Equals(abPriorityVal.AddressNo));
var DuplicateItem = from d in ItemList.ToList()
where d.Equals(item)
select d;
if (DuplicateItem.Count() > 1)
cpm.dupicateStatus = "Duplicate " + item;
if (ABInfo != null)
{
cpm.HQABCur = ABInfo.APCRCD;
cpm.ABPrice = (decimal)ABInfo.APUPRC;
}
if (itemModel != null)
{
cpm.ItemNo2nd = itemModel.ItemNo2nd;
cpm.Desc1 = itemModel.Desc1;
cpm.PricingList = generatePricing(itemModel.ItemNo2nd).ToList();
cpm.WHQtyList = generateWHQty(itemModel.ItemNo2nd).ToList();
cpm.SRQty = _F41021Manager.getSRQty_SumByItemShortItemNo(itemModel.ShortItemNo);
cpm.OpenPOQty = _purchaseManager.getOpenPOQty_SumByShortItemNo(itemModel.ShortItemNo);
cpm.OpenPOATP = cpm.OpenPOQty -
(
_purchaseManager.getPurIndentQty_SumByItemShortItemNo(itemModel.ShortItemNo) +
_F41021Manager.getInspQty_SumByItemShortItemNo(itemModel.ShortItemNo) +
_F41021Manager.getInspATPQty_SumByItemShortItemNo(itemModel.ShortItemNo) +
_F41021Manager.getGitQty_SumByItemShortItemNo(itemModel.ShortItemNo)
);
cpm.CustomerCount = _salesManager.getCustomerCountByShortItemNo(itemModel.ShortItemNo);
cpm.CurYearQty = _salesManager.getQty_sumByYearAndShortItemNo(DateTime.Today.Year, itemModel.ShortItemNo);
cpm.Year1Qty = _salesManager.getQty_sumByYearAndShortItemNo(DateTime.Today.Year - 1, itemModel.ShortItemNo);
cpm.Year2Qty = _salesManager.getQty_sumByYearAndShortItemNo(DateTime.Today.Year - 2, itemModel.ShortItemNo);
cpm.Year3Qty = _salesManager.getQty_sumByYearAndShortItemNo(DateTime.Today.Year - 3, itemModel.ShortItemNo);
LastPurchaseReceivedModel LR = new LastPurchaseReceivedModel();
LR =_purchaseManager.getLastReceiptInfoByShortItemNo(itemModel.ShortItemNo);
if (LR != null)
{
cpm.LastReceiptCurr = LR.LastReceiptCurr;
cpm.LastReceiptDate = LR.LastDocDate.Date;
cpm.LastReceiptPrice = LR.LastReceiptPrice;
}
else
{
cpm.LastReceiptCurr = "";
cpm.LastReceiptPrice = 0;
cpm.LastReceiptDate = null;
}
}
else
{
cpm.ItemNo2nd = item;
cpm.dupicateStatus = "Item not exist, Please verify";
cpm.Desc1 = "N/A in Item Master";
cpm.PricingList = null;
cpm.WHQtyList = null;
cpm.SRQty = 0;
cpm.OpenPOQty = 0;
cpm.OpenPOATP = 0;
cpm.CustomerCount = 0;
cpm.CurYearQty = 0;
cpm.Year1Qty = 0;
cpm.Year2Qty = 0;
cpm.Year3Qty = 0;
cpm.LocalPlanCode = "";
cpm.UnitPerCarton = 0;
cpm.LastReceiptCurr = "";
cpm.LastReceiptPrice = 0;
cpm.LastReceiptDate = null;
}
objResult.Add(cpm);
} //if condition
}//foreach
return objResult;
}
public IList<ContractPricingModel> getContractPricingDataByJoin(IList<string> ItemList)
{
IList<ContractPricingModel> objResult = new List<ContractPricingModel>();
int reserveQty = 0;
int releaseQty = 0;
#region "objItem Collection"
//Item collection
var objItem = _F41010Manager.GetData(ItemList);
IList<int> ShortItemNoList = new List<int>();
foreach (var itemRow in objItem)
{
ShortItemNoList.Add(itemRow.ShortItemNo);
}
#endregion
#region" objABPrice Collection"
int? priVal = 1;
var abPriorityVal = objABPriority.GetSingle(ab => ab.Priority==priVal);
var objAB = (from r in objABPrice.GetQuery()
where ShortItemNoList.Contains((int)r.APITM) &&
r.APAN8.Equals(abPriorityVal.AddressNo)
select new {
ShortItemNo=(int)r.APITM,
CurrCode = r.APCRCD,
Price=r.APUPRC
}).AsQueryable();
#endregion
#region "objSR Collection"
//SR Collection
var objSR = (from r in SR.GetQuery()
where ShortItemNoList.Contains(r.ShortItemNo)
group r by r.ShortItemNo into gr
select new
{
shortItemNo = gr.Key,
SRQty_sum = gr.Sum(x => x.QtyResv1),
IndentQty_sum = gr.Sum(x => x.QtyIndentResv),
InspQty_sum = gr.Sum(x => x.QtyPOInsp),
gitQty_sum = gr.Sum(x => x.QtyGIT)
}).AsQueryable();
#endregion
#region "Cust Count"
var resultSales = (from h in SH.GetQuery()
join d in SD.GetQuery()
on new { DocType = h.DocType, DocNo = h.DocNo } equals new { DocType = d.DocType, DocNo = d.DocNo }
where ShortItemNoList.Contains(d.ShortItemNo)
select new
{
shortItemNo=d.ShortItemNo,
CustomerID = h.CustomerID
}).ToList().Distinct();
var objCustCount = (from r in resultSales
group r by r.shortItemNo into gr
select new
{
shortItemNo=gr.Key,
CustCount= gr.Count()
}).AsQueryable();
#endregion
#region "objPOQTY Collection"
var res = from a in Pur.GetQuery()
join b in PuIndent.GetQuery()
on new {doctype=a.DocType,docno=a.DocNo,linenum=a.LineNum} equals new {doctype=b.DocType,docno=b.DocNo,linenum=b.LineNum}
where ShortItemNoList.Contains(a.ShortItemNo)
select new
{
shortItemNo=a.ShortItemNo,
OrderQty = a.OrderQty,
CancelledQty = a.CancelledQty,
ReceivedQty =a.ReceivedQty,
GITQty =a.GITQty,
InspQty =a.InspQty,
ReserveQty=b.ReservedQty,
ReleaseQty=b.ReleasedQty
};
//Open PO Qty
var objPOQTY = (from r in res
group r by r.shortItemNo into gr
select new
{
shortItemNo = gr.Key,
orderQty_sum = gr.Sum(x => x.OrderQty),
cancelledQty_sum = gr.Sum(x => x.CancelledQty),
receivedQty_sum = gr.Sum(x => x.ReceivedQty),
gitQty_sum = gr.Sum(x => x.GITQty),
inspQty_sum = gr.Sum(x => x.InspQty),
reserveQty_sum = gr.Sum(x => x.ReserveQty),
releaseQty_sum = gr.Sum(x => x.ReleaseQty),
}).AsQueryable();
#endregion
#region "objCurYearQty,1,2,3 Collection"
//Sales History for 4 years
int CurYear = DateTime.Today.Year;
var objCurYearQty = (from s in salesHist.GetQuery()
where ShortItemNoList.Contains(s.ShortItemNo) && s.PeriodYear.Equals(CurYear)
group s by s.ShortItemNo into groupsales
select new
{
shortItemNo = groupsales.Key,
Qty_sum01 = groupsales.Sum(x => x.TotQty01),
Qty_sum02 = groupsales.Sum(x => x.TotQty02),
Qty_sum03 = groupsales.Sum(x => x.TotQty03),
Qty_sum04 = groupsales.Sum(x => x.TotQty04),
Qty_sum05 = groupsales.Sum(x => x.TotQty05),
Qty_sum06 = groupsales.Sum(x => x.TotQty06),
Qty_sum07 = groupsales.Sum(x => x.TotQty07),
Qty_sum08 = groupsales.Sum(x => x.TotQty08),
Qty_sum09 = groupsales.Sum(x => x.TotQty09),
Qty_sum10 = groupsales.Sum(x => x.TotQty10),
Qty_sum11 = groupsales.Sum(x => x.TotQty11),
Qty_sum12 = groupsales.Sum(x => x.TotQty12),
}).AsQueryable();
var objYear1Qty = (from s in salesHist.GetQuery()
where ShortItemNoList.Contains(s.ShortItemNo) && s.PeriodYear.Equals(CurYear-1)
group s by s.ShortItemNo into groupsales
select new
{
shortItemNo = groupsales.Key,
Qty_sum01 = groupsales.Sum(x => x.TotQty01),
Qty_sum02 = groupsales.Sum(x => x.TotQty02),
Qty_sum03 = groupsales.Sum(x => x.TotQty03),
Qty_sum04 = groupsales.Sum(x => x.TotQty04),
Qty_sum05 = groupsales.Sum(x => x.TotQty05),
Qty_sum06 = groupsales.Sum(x => x.TotQty06),
Qty_sum07 = groupsales.Sum(x => x.TotQty07),
Qty_sum08 = groupsales.Sum(x => x.TotQty08),
Qty_sum09 = groupsales.Sum(x => x.TotQty09),
Qty_sum10 = groupsales.Sum(x => x.TotQty10),
Qty_sum11 = groupsales.Sum(x => x.TotQty11),
Qty_sum12 = groupsales.Sum(x => x.TotQty12),
}).AsQueryable();
var objYear2Qty = (from s in salesHist.GetQuery()
where ShortItemNoList.Contains(s.ShortItemNo) && s.PeriodYear.Equals(CurYear - 2)
group s by s.ShortItemNo into groupsales
select new
{
shortItemNo = groupsales.Key,
Qty_sum01 = groupsales.Sum(x => x.TotQty01),
Qty_sum02 = groupsales.Sum(x => x.TotQty02),
Qty_sum03 = groupsales.Sum(x => x.TotQty03),
Qty_sum04 = groupsales.Sum(x => x.TotQty04),
Qty_sum05 = groupsales.Sum(x => x.TotQty05),
Qty_sum06 = groupsales.Sum(x => x.TotQty06),
Qty_sum07 = groupsales.Sum(x => x.TotQty07),
Qty_sum08 = groupsales.Sum(x => x.TotQty08),
Qty_sum09 = groupsales.Sum(x => x.TotQty09),
Qty_sum10 = groupsales.Sum(x => x.TotQty10),
Qty_sum11 = groupsales.Sum(x => x.TotQty11),
Qty_sum12 = groupsales.Sum(x => x.TotQty12),
}).AsQueryable();
var objYear3Qty = (from s in salesHist.GetQuery()
where ShortItemNoList.Contains(s.ShortItemNo) && s.PeriodYear.Equals(CurYear - 3)
group s by s.ShortItemNo into groupsales
select new
{
shortItemNo = groupsales.Key,
Qty_sum01 = groupsales.Sum(x => x.TotQty01),
Qty_sum02 = groupsales.Sum(x => x.TotQty02),
Qty_sum03 = groupsales.Sum(x => x.TotQty03),
Qty_sum04 = groupsales.Sum(x => x.TotQty04),
Qty_sum05 = groupsales.Sum(x => x.TotQty05),
Qty_sum06 = groupsales.Sum(x => x.TotQty06),
Qty_sum07 = groupsales.Sum(x => x.TotQty07),
Qty_sum08 = groupsales.Sum(x => x.TotQty08),
Qty_sum09 = groupsales.Sum(x => x.TotQty09),
Qty_sum10 = groupsales.Sum(x => x.TotQty10),
Qty_sum11 = groupsales.Sum(x => x.TotQty11),
Qty_sum12 = groupsales.Sum(x => x.TotQty12),
}).AsQueryable();
#endregion
#region "objLastPORec Collection"
//Last PO rec
var objLastPORec = (from r in LastPORec.GetQuery()
where ShortItemNoList.Contains(r.ShortItemNo)
select new {
ShortItemNo = r.ShortItemNo,
LastDocDate=r.LastDocDate,
LastCurrCode=r.LastReceiptCurr,
LastPrice=r.LastReceiptPrice
}).OrderByDescending(x => x.LastDocDate).AsQueryable();
#endregion
#region "Combine"
var objAllCombine = from itemList in objItem
join ABList in objAB
on itemList.ShortItemNo equals ABList.ShortItemNo into AB_Result
join SRList in objSR
on itemList.ShortItemNo equals SRList.shortItemNo into SR_Result
join CustCountList in objCustCount
on itemList.ShortItemNo equals CustCountList.shortItemNo into CustCnt_Result
join POList in objPOQTY
on itemList.ShortItemNo equals POList.shortItemNo into PO_result
join CurYearList in objCurYearQty
on itemList.ShortItemNo equals CurYearList.shortItemNo into CurYear_result
join Year1List in objYear1Qty
on itemList.ShortItemNo equals Year1List.shortItemNo into Year1_result
join Year2List in objYear2Qty
on itemList.ShortItemNo equals Year2List.shortItemNo into Year2_result
join Year3List in objYear3Qty
on itemList.ShortItemNo equals Year3List.shortItemNo into Year3_result
join PORecList in objLastPORec
on itemList.ShortItemNo equals PORecList.ShortItemNo into PORec_result
from ab_r in AB_Result.DefaultIfEmpty()
from sr_r in SR_Result.DefaultIfEmpty()
from custcnt_r in CustCnt_Result.DefaultIfEmpty()
from po_r in PO_result.DefaultIfEmpty()
from curYear_r in CurYear_result.DefaultIfEmpty()
from year1_r in Year1_result.DefaultIfEmpty()
from year2_r in Year2_result.DefaultIfEmpty()
from year3_r in Year3_result.DefaultIfEmpty()
from porec_r in PORec_result.DefaultIfEmpty()
select new
{
itemList,
ab_r,
sr_r,
custcnt_r,
po_r,
curYear_r,
year1_r,
year2_r,
year3_r,
porec_r
};
#endregion
#region "Assign Data"
// Assign to final model
//foreach (var row in objAllCombine.ToList())
foreach (string item in ItemList)
{
ContractPricingModel cpm = new ContractPricingModel();
var row = (from r in objAllCombine where r.itemList.ItemNo2nd.ToUpper().Equals(item.ToUpper()) select r).FirstOrDefault();
var DuplicateItem = from d in ItemList
where d.Equals(item)
select d;
if (DuplicateItem.Count() > 1)
cpm.dupicateStatus = "Duplicate " + item;
if (row != null)
{
cpm.ItemNo2nd = row.itemList.ItemNo2nd;
cpm.Desc1 = row.itemList.Desc1;
cpm.UnitPerCarton = row.itemList.UnitPerCarton;
cpm.LocalPlanCode = row.itemList.ICatCode14;
cpm.PricingList = generatePricing(row.itemList.ItemNo2nd).ToList();
cpm.WHQtyList = generateWHQty(row.itemList.ItemNo2nd).ToList();
//cpm.WHQtyList = null;
if (row.ab_r != null)
{
cpm.HQABCur = row.ab_r.CurrCode;
cpm.ABPrice = (decimal)row.ab_r.Price;
}
if (row.po_r != null)
{
cpm.OpenPOQty = row.po_r.orderQty_sum -
(row.po_r.cancelledQty_sum + row.po_r.receivedQty_sum + row.po_r.gitQty_sum + row.po_r.inspQty_sum);
reserveQty = row.po_r.reserveQty_sum;
releaseQty = row.po_r.releaseQty_sum;
}
else
{
cpm.OpenPOQty = 0;
reserveQty = 0;
releaseQty = 0;
}
if (row.sr_r != null)
{
cpm.SRQty = row.sr_r.SRQty_sum;
cpm.OpenPOATP = cpm.OpenPOQty -
(
(reserveQty - releaseQty) +
row.sr_r.InspQty_sum +
(row.sr_r.InspQty_sum - row.sr_r.IndentQty_sum) +
row.sr_r.gitQty_sum
);
}
else
{
cpm.SRQty = 0;
cpm.OpenPOATP = 0;
}
if (row.custcnt_r != null)
{
cpm.CustomerCount = row.custcnt_r.CustCount;
}
else
{
cpm.CustomerCount = 0;
}
if (row.curYear_r != null)
{
cpm.CurYearQty = row.curYear_r.Qty_sum01 + row.curYear_r.Qty_sum02 + row.curYear_r.Qty_sum03 + row.curYear_r.Qty_sum04 +
row.curYear_r.Qty_sum05 + row.curYear_r.Qty_sum06 + row.curYear_r.Qty_sum07 + row.curYear_r.Qty_sum08 +
row.curYear_r.Qty_sum09 + row.curYear_r.Qty_sum10 + row.curYear_r.Qty_sum11 + row.curYear_r.Qty_sum12;
}
else
{
cpm.CurYearQty = 0;
}
if (row.year1_r != null)
{
cpm.Year1Qty = row.year1_r.Qty_sum01 + row.year1_r.Qty_sum02 + row.year1_r.Qty_sum03 + row.year1_r.Qty_sum04 +
row.year1_r.Qty_sum05 + row.year1_r.Qty_sum06 + row.year1_r.Qty_sum07 + row.year1_r.Qty_sum08 +
row.year1_r.Qty_sum09 + row.year1_r.Qty_sum10 + row.year1_r.Qty_sum11 + row.year1_r.Qty_sum12;
}
else
{
cpm.Year1Qty = 0;
}
if (row.year2_r != null)
{
cpm.Year2Qty = row.year2_r.Qty_sum01 + row.year2_r.Qty_sum02 + row.year2_r.Qty_sum03 + row.year2_r.Qty_sum04 +
row.year2_r.Qty_sum05 + row.year2_r.Qty_sum06 + row.year2_r.Qty_sum07 + row.year2_r.Qty_sum08 +
row.year2_r.Qty_sum09 + row.year2_r.Qty_sum10 + row.year2_r.Qty_sum11 + row.year2_r.Qty_sum12;
}
else
{
cpm.Year2Qty = 0;
}
if (row.year3_r != null)
{
cpm.Year3Qty = row.year3_r.Qty_sum01 + row.year3_r.Qty_sum02 + row.year3_r.Qty_sum03 + row.year3_r.Qty_sum04 +
row.year3_r.Qty_sum05 + row.year3_r.Qty_sum06 + row.year3_r.Qty_sum07 + row.year3_r.Qty_sum08 +
row.year3_r.Qty_sum09 + row.year3_r.Qty_sum10 + row.year3_r.Qty_sum11 + row.year3_r.Qty_sum12;
}
else
{
cpm.Year3Qty = 0;
}
if (row.porec_r != null)
{
cpm.LastReceiptCurr = row.porec_r.LastCurrCode;
cpm.LastReceiptDate = row.porec_r.LastDocDate;
cpm.LastReceiptPrice = row.porec_r.LastPrice;
}
else
{
cpm.LastReceiptCurr = "";
cpm.LastReceiptDate = null;
cpm.LastReceiptPrice = 0;
}
}
else {
cpm.ItemNo2nd = item;
cpm.dupicateStatus = "Item not exist, Please verify";
cpm.Desc1 = "N/A in Item Master";
cpm.PricingList = null;
cpm.WHQtyList = null;
cpm.HQABCur = "";
cpm.ABPrice = 0;
cpm.PricingList =null;
cpm.WHQtyList =null;
cpm.SRQty = 0;
cpm.OpenPOQty = 0;
cpm.OpenPOATP = 0;
cpm.CustomerCount = 0;
cpm.CurYearQty = 0;
cpm.Year1Qty = 0;
cpm.Year2Qty = 0;
cpm.Year3Qty = 0;
cpm.UnitPerCarton=0;
cpm.LocalPlanCode="";
cpm.LastReceiptCurr="";
cpm.LastReceiptPrice=0;
cpm.LastReceiptDate = null;
}
objResult.Add(cpm);
}
#endregion
return objResult;
}