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