public List <Models.ExcelPriceList> ImportPrice(DataTable dt) { List <Models.ExcelPriceList> list = new List <Models.ExcelPriceList>(); // OfferActionType checkIsExist; OfferActionType actionTypes = OfferActionType.Non; DateTime StartDate = DateTime.Now; DateTime EndDate = DateTime.Now; //Getting the default Margin for reseller ViewModel.DefaultMargin.DefaultMarginResellers odjDMR = new DefaultMargin.DefaultMarginResellers(); Models.DefaultMargin defaultResellerMargin = odjDMR.GetDefaultMargin(); //Getting the default Margin for Customer ViewModel.DefaultMargin.DefaultMarginUsers odjDMU = new DefaultMargin.DefaultMarginUsers(); Models.DefaultMargin defaultUserMargin = odjDMU.GetDefaultMargin(); foreach (DataRow item in dt.Rows) { if (item[0].ToString() != "A/C/D/U") { if (item[0].ToString() != "") { actionTypes = OfferActionTypes.ParseEnum <OfferActionType>(item[0].ToString()); //item["A/C/D/U"] } if (item[1].ToString() != "") { StartDate = Convert.ToDateTime(item[1].ToString()); //item["Valid-From Date"] } if (item[2].ToString() != "") { EndDate = Convert.ToDateTime(item[2].ToString()); //item["Valid To Date"] } AgreementType agreementTypes = AgreementTypes.ParseEnum <AgreementType>(item[5].ToString()); //item["License Agreement Type"] CustomerType customerTypes = CustomerTypes.ParseEnum <CustomerType>(item[8].ToString()); //item["End Customer Type"] LicenseType licenseTypes = LicenseTypes.ParseEnum <LicenseType>(item[7].ToString()); //item["Secondary License Type"] string[] unit = item[6].ToString().Split(' ');//item["Purchase Unit"] PurchaseUnit purchaseUnit = PurchaseUnits.ParseEnum <PurchaseUnit>(unit[1]); Models.ExcelPriceList _m = new Models.ExcelPriceList(); _m.ActionType = Convert.ToInt16(actionTypes); _m.AgreementType = Convert.ToInt16(agreementTypes); _m.CustomerType = Convert.ToInt16(customerTypes); _m.LicenseType = Convert.ToInt16(licenseTypes); _m.PurchaseUnitNumber = Convert.ToInt16(unit[0]); _m.PurchaseUnit = Convert.ToInt16(purchaseUnit); _m.MicrosoftId = item[4].ToString();//item["Offer ID"] _m.StartDate = StartDate; _m.EndDate = EndDate; _m.Price = float.Parse(item[9].ToString()); //item["List Price"] _m.Name = item[3].ToString(); _m.CustomerPrice = item[10].ToString() != "" && item[10].ToString() != " " ? Convert.ToDouble(item[10].ToString()) : Convert.ToDouble((_m.Price * defaultUserMargin.DefaultPercentage / 100) + _m.Price); //item["ERP Price"] _m.ResellerPrice = 0; //item[11].ToString() != "" && item[11].ToString() != " " ? Convert.ToDouble(item[11].ToString()) : Convert.ToDouble(((_m.Price * defaultResellerMargin.DefaultPercentage / 100) + _m.Price));//item["Reseller Price"] list.Add(_m); } } return(list); }
//For Reseller public List <Models.GridPriceList> GetForReseller(int Id) { try { var db = new Context.ConnectionStringsContext(); var query = (from p in db.MicrosoftPriceList //join rp in db.ResellerPrice //on p.MicrosoftId equals rp.MicrosoftId //into rps //from rp in rps.DefaultIfEmpty() where p.Status == true //&& rp.ResellerId == Id group p by p.MicrosoftId into op select new { MicrosoftId = op.Key, Name = op.Max(x => x.Name), StartDate = op.Max(x => x.StartDate), EndDate = op.Max(x => x.EndDate), Price = op.Max(x => x.Price), ResellerPrice = db.ResellerPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id && m.Status == true) ? db.ResellerPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id && m.Status == true).OrderByDescending(m => m.Id).FirstOrDefault().Price : (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null)) ? db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).OrderByDescending(m => m.Id).FirstOrDefault().ResellerPrice : 0, CustomerPrice = (db.CustomerPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id)) ? (db.CustomerPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id)).OrderByDescending(m => m.Id).FirstOrDefault().Price : (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id && m.CustomerPrice != null)) ? db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == Id).OrderByDescending(m => m.Id).FirstOrDefault().CustomerPrice : op.Max(x => x.CustomerPrice), AgreementType = op.Max(x => x.AgreementType), CustomerType = op.Max(x => x.CustomerType), LicenseType = op.Max(x => x.LicenseType), PurchaseUnit = op.Max(x => x.PurchaseUnit), PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber), Id = op.Max(x => x.Id), DefaultMarginReseller = db.Resellers.Where(m => m.Id == Id).FirstOrDefault().Margin //get the margin from reseller table }).ToList(); List <Models.GridPriceList> mic = query.ToList().Select(r => new Models.GridPriceList { EndDate = r.EndDate, Id = r.Id, MicrosoftId = r.MicrosoftId, Name = r.Name, StartDate = r.StartDate, // Date = r.StartDate.Date + "<br/> "+r.EndDate.Date, Price = r.Price, AgreementType = AgreementTypes.ParseEnumToString <AgreementType>(r.AgreementType), CustomerType = CustomerTypes.ParseEnumToString <CustomerType>(r.CustomerType), LicenseType = LicenseTypes.ParseEnumToString <LicenseType>(r.LicenseType), ResellerPrice = (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100), ResellerPricePercentage = ((double)System.Math.Round(((double)((((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100)) - r.Price) * 100) / r.Price), 2)).ToString(), //((double)System.Math.Round(((double)((((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.CustomerPrice - (r.CustomerPrice * r.DefaultMarginReseller) / 100)) - r.Price) * 100) / r.Price), 2)).ToString(), CustomerPrice = r.CustomerPrice, CustomerPricePercentage = ((double)System.Math.Round((((r.CustomerPrice - ((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100))) * 100) / ((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100))), 2)).ToString(), PurchaseUnit = r.PurchaseUnitNumber + " " + PurchaseUnits.ParseEnumToString <PurchaseUnit>(r.PurchaseUnit), //PurchaseUnitNumber = r.PurchaseUnitNumber, DefaultMarginReseller = (double)r.ResellerPrice != 0 ? ((double)(( ((double)System.Math.Round((((r.CustomerPrice - r.Price) * 100) / r.Price), 2)) - ((double)System.Math.Round(((double)((((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100)) - r.Price) * 100) / r.Price), 2))) * 100 ) / ((double)(((double)System.Math.Round((((r.CustomerPrice - r.Price) * 100) / r.Price), 2)))) ).ToString() : r.DefaultMarginReseller.ToString() }).OrderBy(m => m.Name).ToList(); return(mic); } catch { return(new List <Models.GridPriceList>()); } }
//For Admin (Overall price list => ResellerPrice > MicrosoftPriceList) public List <Models.GridPriceList> GetGridPriceListForAdmin() { //IEnumerable<MicrosoftOffer> microsoftOffers = await ApplicationDomain.Instance.OffersRepository.RetrieveMicrosoftOffersAsync().ConfigureAwait(false); try { var db = new Context.ConnectionStringsContext(); var query = (from p in db.MicrosoftPriceList // join where p.Status == true group p by p.MicrosoftId into op select new { MicrosoftId = op.Key, Name = op.Max(x => x.Name), StartDate = op.Max(x => x.StartDate), EndDate = op.Max(x => x.EndDate), Price = op.Max(x => x.Price), ResellerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null)) ? db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).OrderByDescending(m => m.Id).FirstOrDefault().ResellerPrice : 0, //op.Max(x => x.CustomerPrice) - (op.Max(x => x.CustomerPrice) * (db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage)) / 100, //ResellerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.ResellerPrice != null)) ? // db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).FirstOrDefault().ResellerPrice // : op.Max(x => x.ResellerPrice), CustomerPrice = (db.ResellerCustomersPrice.Any(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0 && m.CustomerPrice != null)) ? db.ResellerCustomersPrice.Where(m => m.PriceId == op.Max(x => x.Id) && m.ResellerId == 0).OrderByDescending(m => m.Id).FirstOrDefault().CustomerPrice : op.Max(x => x.CustomerPrice), AgreementType = op.Max(x => x.AgreementType), CustomerType = op.Max(x => x.CustomerType), LicenseType = op.Max(x => x.LicenseType), PurchaseUnit = op.Max(x => x.PurchaseUnit), PurchaseUnitNumber = op.Max(x => x.PurchaseUnitNumber), Id = op.Max(x => x.Id), DefaultMarginReseller = db.DefaultMargin.Where(m => m.ResellerId == 0 && m.Role == (int)Roles.Resellers).FirstOrDefault().DefaultPercentage }).ToList(); List <Models.GridPriceList> mic = query.ToList().Select(r => new Models.GridPriceList { EndDate = r.EndDate, Id = r.Id, MicrosoftId = r.MicrosoftId, Name = r.Name, StartDate = r.StartDate, // Date = r.StartDate.Date + "<br/> "+r.EndDate.Date, Price = r.Price, AgreementType = AgreementTypes.ParseEnumToString <AgreementType>(r.AgreementType), CustomerType = CustomerTypes.ParseEnumToString <CustomerType>(r.CustomerType), LicenseType = LicenseTypes.ParseEnumToString <LicenseType>(r.LicenseType), ResellerPrice = (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100), ResellerPricePercentage = ((double)System.Math.Round(((double)((( (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100) ) - r.Price) * 100) / r.Price), 2)).ToString(), //ResellerPrice = (double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.CustomerPrice - (r.CustomerPrice * r.DefaultMarginReseller) / 100), //ResellerPricePercentage = ((double)System.Math.Round(((double)((((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.CustomerPrice - (r.CustomerPrice * r.DefaultMarginReseller) / 100)) - r.Price) * 100) / r.Price), 2)).ToString(), CustomerPrice = r.CustomerPrice, CustomerPricePercentage = ((double)System.Math.Round((((r.CustomerPrice - r.Price) * 100) / r.Price), 2)).ToString(), PurchaseUnit = r.PurchaseUnitNumber + " " + PurchaseUnits.ParseEnumToString <PurchaseUnit>(r.PurchaseUnit), //PurchaseUnitNumber = r.PurchaseUnitNumber, DefaultMarginReseller = (double)r.ResellerPrice != 0 ? ((double)(( ((double)System.Math.Round((((r.CustomerPrice - r.Price) * 100) / r.Price), 2)) - ((double)System.Math.Round(((double)((((double)r.ResellerPrice != 0 ? (double)r.ResellerPrice : (r.Price + ((r.CustomerPrice - r.Price) * r.DefaultMarginReseller) / 100)) - r.Price) * 100) / r.Price), 2))) * 100 ) / ((double)(((double)System.Math.Round((((r.CustomerPrice - r.Price) * 100) / r.Price), 2)))) ).ToString() : r.DefaultMarginReseller.ToString() }).OrderBy(m => m.Name).ToList(); return(mic); } catch (Exception ex) { return(new List <Models.GridPriceList>()); } }