// GET: RegulatorController
public ActionResult Index(int?page)
{
if (!Utils.Utils.GetPermission("REGULATOR_SHOW"))
{
return(new RedirectResult("/abonent"));
}
DateTime dateFrom = DateTime.Now;
DateTime dateTo = DateTime.Now;
dateFrom = Utils.Utils.GetRequestDate(Request["dt_from"], true);
dateTo = Utils.Utils.GetRequestDate(Request["dt_to"], false);
ViewBag.dtFrom = dateFrom;
ViewBag.dtTo = dateTo;
DateTime dateFromDay = DateTime.Now;
dateFromDay = dateFrom.AddHours((double)1);
List <int> intss = new List <int>();
Double Dagger = 1.18;
Double Dagger_018 = 0.18;
PacksChargesRent packs_charges_rent = new PacksChargesRent();
using (DataContext _db = new DataContext())
{
ViewBag.ReCardAbonentPackgulatorActive = "active";
var CardAbonentPack = _db.Cards.Include("CardLogs").Include("CardCharges").Include("Customer").Include("Subscribtions.SubscriptionPackages.Package").Where(c => c.CardCharges.Any(cc => cc.Tdate >= dateFrom && cc.Tdate <= dateTo)).ToList();
decimal share15price = (decimal)_db.Packages.Where(s => s.Price == 15).FirstOrDefault().Price;
decimal share12price = (decimal)_db.Packages.Where(s => s.Name == "სტანდარტი").FirstOrDefault().Price;
decimal share8price = (decimal)_db.Packages.Where(p => p.Name == "აქცია 8").FirstOrDefault().Price;
decimal share6price = (decimal)_db.Packages.Where(p => p.Name == "თანამშრომელი").FirstOrDefault().Price;
int service_days = Convert.ToInt32(_db.Params.First(p => p.Name == "ServiceDays").Value);
decimal Caharges15 = Math.Round((decimal)(share15price / (decimal)service_days), 2);
decimal Caharges12 = Math.Round((decimal)(share12price / (decimal)service_days), 2);
decimal Caharges8 = Math.Round((decimal)(share8price / (decimal)service_days), 2);
decimal Caharges6 = Math.Round((decimal)(share6price / (decimal)service_days), 2);
var pack15BackActive = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges15 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Physical).ToList();
var pack12BackActive = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges12 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Physical).ToList();
var pack8BackActive = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges8 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Physical).ToList();
var pack6BackActive = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges6 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Physical).ToList();
int Repetition = 0;
var AbonentPack = CardAbonentPack.Where(c => c.CardLogs.Any(cc => cc.Date >= dateFrom && cc.Date <= dateTo && cc.Status == CardLogStatus.Open) && c.Customer.Type == CustomerType.Physical).ToList();
var newabonent = CardAbonentPack.Where(c => c.CardLogs.Any(cc => cc.Date >= dateFrom && cc.Date <= dateTo && cc.Status == CardLogStatus.Montage) && c.Customer.Type == CustomerType.Physical).ToList();
int count15 = 0, count12 = 0, count6 = 0, count8 = 0;
var packs8 = pack15BackActive.Where(c => c.Subscribtions.Any(sb => sb.Tdate >= dateFrom && sb.Tdate <= dateTo && sb.SubscriptionPackages.Any(s => s.Package.Id == 304085))).ToList();
count8 += packs8.Count;
int _count8 = packs8.Count;
List <Card> CardsAbonent = new List <Card>();
foreach (var item in AbonentPack)
{
var pack = item.CardLogs.Where(c => c.Date >= dateFrom && c.Date <= dateTo).OrderBy(l => l.Date).ToList();
if (pack != null && pack.Count() > 0)
{
if (pack[pack.Count() - 1].Status == CardLogStatus.Open)
{
if (pack15BackActive.Contains(item) || pack12BackActive.Contains(item) || pack8BackActive.Contains(item) || pack6BackActive.Contains(item))
{
}
else
{
CardsAbonent.Add(item);
DateTime chargetime = new DateTime(pack[pack.Count() - 1].Date.Year, pack[pack.Count() - 1].Date.Month, pack[pack.Count() - 1].Date.Day, 0, 1, 0);
chargetime = chargetime.AddDays(1);
CardCharge charge = item.CardCharges.Where(c => c.Tdate == chargetime).FirstOrDefault();
if (charge != null)
{
decimal chargeCoeff = Math.Round(charge.Amount, 2);
if (chargeCoeff == Caharges15)
{
if (item.Subscribtions.Any(sb => sb.Tdate >= dateFrom && sb.Tdate <= dateTo && sb.SubscriptionPackages.Any(s => s.Package.Id == 304085)))
{
count8++;
}
else
{
count15++;
}
}
if (chargeCoeff == Caharges12)
{
count12++;
}
if (chargeCoeff == Caharges6)
{
count6++;
}
if (chargeCoeff == Caharges8)
{
count8++;
}
}
else
{
decimal packprice = (decimal)item.Subscribtions.Where(s => s.Status == true).FirstOrDefault().SubscriptionPackages.Sum(s => s.Package.Price);
decimal coeff = Math.Round((decimal)(packprice / (decimal)service_days), 2);
if (coeff == Caharges15)
{
count15++;
}
if (coeff == Caharges12)
{
count12++;
}
if (coeff == Caharges6)
{
count6++;
}
if (coeff == Caharges8)
{
count8++;
}
}
}
}
}
}
ViewBag.StandardNewAbonent = newabonent.Count();// pack15newAbonent.Count() + pack12newAbonent.Count()+ pack6newAbonent.Count();
ViewBag.RegionNewAbonent = 0;
// იურდიული
var pack15BackActiveJuridical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges15 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Juridical).ToList();
var pack12BackActiveJuridical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges12 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Juridical).ToList();
var pack8BackActiveJuridical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges8 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Juridical).ToList();
// var pack6BackActiveJuridical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => Math.Round(cc.Amount, 2) == Caharges6 && cc.Tdate >= dateFrom && cc.Tdate <= dateFromDay && cc.Status == CardChargeStatus.Daily) && c.Customer.Type == CustomerType.Juridical).ToList();
var AbonentPackJuridical = CardAbonentPack.Where(c => c.CardLogs.Any(cc => cc.Date >= dateFrom && cc.Date <= dateTo && cc.Status == CardLogStatus.Open) && c.Customer.Type == CustomerType.Juridical).ToList();
var newabonentJuridical = CardAbonentPack.Where(c => c.CardLogs.Any(cc => cc.Date >= dateFrom && cc.Date <= dateTo && cc.Status == CardLogStatus.Montage) && c.Customer.Type == CustomerType.Juridical).ToList();
int count15Juridical = 0, count12Juridical = 0, count8Juridical = 0;
var packs8Juridical = pack15BackActiveJuridical.Where(c => c.Subscribtions.Any(sb => sb.Tdate >= dateFrom && sb.Tdate <= dateTo && sb.SubscriptionPackages.Any(s => s.Package.Id == 304085))).ToList();
count8Juridical += packs8Juridical.Count;
int _count8Juridical = packs8Juridical.Count;
List <Card> CardsAbonentJuridical = new List <Card>();
foreach (var item in AbonentPackJuridical)
{
var pack = item.CardLogs.Where(c => c.Date >= dateFrom && c.Date <= dateTo).OrderBy(l => l.Date).ToList();
if (pack != null && pack.Count() > 0)
{
if (pack[pack.Count() - 1].Status == CardLogStatus.Open)
{
if (pack15BackActiveJuridical.Contains(item) || pack12BackActiveJuridical.Contains(item) || pack8BackActiveJuridical.Contains(item))
{
}
else
{
//CardsAbonent.Add(item);
DateTime chargetime = new DateTime(pack[pack.Count() - 1].Date.Year, pack[pack.Count() - 1].Date.Month, pack[pack.Count() - 1].Date.Day, 0, 1, 0);
chargetime = chargetime.AddDays(1);
CardCharge charge = item.CardCharges.Where(c => c.Tdate == chargetime).FirstOrDefault();
if (charge != null)
{
decimal chargeCoeff = Math.Round(charge.Amount, 2);
if (chargeCoeff == Caharges15)
{
if (item.Subscribtions.Any(sb => sb.Tdate >= dateFrom && sb.Tdate <= dateTo && sb.SubscriptionPackages.Any(s => s.Package.Id == 304085)))
{
count8Juridical++;
}
else
{
count15Juridical++;
}
}
if (chargeCoeff == Caharges12)
{
count12Juridical++;
}
if (chargeCoeff == Caharges8)
{
count8Juridical++;
}
}
else
{
decimal packprice = (decimal)item.Subscribtions.Where(s => s.Status == true).FirstOrDefault().SubscriptionPackages.Sum(s => s.Package.Price);
decimal coeff = Math.Round((decimal)(packprice / (decimal)service_days), 2);
if (coeff == Caharges15)
{
count15Juridical++;
}
if (coeff == Caharges12)
{
count12Juridical++;
}
if (coeff == Caharges8)
{
count8Juridical++;
}
}
}
}
}
}
foreach (var item in AbonentPackJuridical)
{
var pack = item.CardLogs.Where(c => c.Date >= dateFrom && c.Date <= dateTo).OrderBy(l => l.Date).ToList();
if (pack != null && pack.Count() > 0)
{
if (pack[pack.Count() - 1].Status == CardLogStatus.Open)
{
if (pack15BackActiveJuridical.Contains(item) || pack12BackActiveJuridical.Contains(item) || pack8BackActiveJuridical.Contains(item))
{
}
else
{
CardsAbonentJuridical.Add(item);
}
}
}
}
var pack15Juridical = CardsAbonentJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Price == 15)).Select(c => c).ToList();
var pack12Juridical = CardsAbonentJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "სტანდარტი")).Select(c => c).ToList();
var pack8Juridical = CardsAbonentJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "აქცია 8")).Select(c => c).ToList();
//var pack6Juridical = CardsAbonentJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "თანამშრომელი")).Select(c => c).ToList();
ViewBag.Standard = pack15BackActive.Count() - _count8 + pack12BackActive.Count() + pack6BackActive.Count() + count15 + count12 + count6;
ViewBag.StandardNewAbonent = newabonent.Count();
ViewBag.Region = pack8BackActive.Count() + count8;
ViewBag.RegionJuridical = pack8BackActiveJuridical.Count() + count8Juridical;
ViewBag.StandardJuridical = pack15BackActiveJuridical.Count() - _count8Juridical + pack12BackActiveJuridical.Count() + count15Juridical + count12Juridical;
ViewBag.StandardNewAbonentJuridical = newabonentJuridical.Count();
List <Card> cardsPhysical = null;
List <Card> cardsJuridical = null;
PacksCharges packs_charges = new PacksCharges();
PacksChargesCoeff packs_charges_coeff = new PacksChargesCoeff();
PacksExcept packs_execept = new PacksExcept();
cardsPhysical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => cc.Tdate >= dateFrom && cc.Tdate <= dateTo) && c.Customer.Type == CustomerType.Physical).ToList();
var packemployee = cardsPhysical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "თანამშრომელი")).Select(c => c).ToList();
var pack12 = cardsPhysical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "სტანდარტი")).Select(c => c).ToList();
var pack8 = cardsPhysical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "აქცია 8")).Select(c => c).ToList();
var pack15 = cardsPhysical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Price == 15)).Select(c => c).ToList();
var packexpect = packs_execept.Packs_Except_Charges(pack8, pack15, dateTo);
pack8 = packexpect.packs8;
pack15 = packexpect.packs15;
var employee6 = packs_charges_coeff.PacksCardsChargesCoeff(packemployee, dateFrom, dateTo, share6price, service_days);//packs_charges.PacksCardsCharges(packemployee, dateFrom, dateTo);
var paketphysical_8 = packs_charges.PacksCardsCharges(pack8, dateFrom, dateTo);
var pack8charg = packs_charges_coeff.PacksCardsChargesCoeff(pack8, dateFrom, dateTo, share8price, service_days);
var pack8sum15 = paketphysical_8 - pack8charg;
var pack12charg = packs_charges.PacksCardsCharges(pack12, dateFrom, dateTo);
var pack15charg = packs_charges.PacksCardsCharges(pack15, dateFrom, dateTo);
var pack15coeffcharg = packs_charges_coeff.PacksCardsChargesCoeff(pack15, dateFrom, dateTo, share15price, service_days);
pack8charg = pack8charg + (pack15charg - pack15coeffcharg);
pack15charg = pack15coeffcharg + pack8sum15;
var PhysicalContraqt = pack12.Where(c => c.CardLogs.Any(cc => cc.Status == CardLogStatus.Montage && cc.Date >= dateFrom && cc.Date <= dateTo)).ToList();
// იურდიული პირები
cardsJuridical = CardAbonentPack.Where(c => c.CardCharges.Any(cc => cc.Tdate >= dateFrom && cc.Tdate <= dateTo) && c.Customer.Type == CustomerType.Juridical).ToList();
var pack8_Juridical = cardsJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "აქცია 8")).Select(c => c).ToList();
var pack15_Juridical = cardsJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Price == 15)).Select(c => c).ToList();
var packexpectJuridical = packs_execept.Packs_Except_Charges(pack8_Juridical, pack15_Juridical, dateTo);
pack8_Juridical = packexpectJuridical.packs8;
pack15_Juridical = packexpectJuridical.packs15;
var packsphysical_8Juridical = packs_charges.PacksCardsCharges(pack8_Juridical, dateFrom, dateTo);
var pack8chargJuridical = packs_charges_coeff.PacksCardsChargesCoeff(pack8_Juridical, dateFrom, dateTo, share8price, service_days);
var pack8sum15Juridical = packsphysical_8Juridical - pack8chargJuridical;
var pack12_Juridical = cardsJuridical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "სტანდარტი")).Select(c => c).ToList();
var pack12chargJuridical = packs_charges.PacksCardsCharges(pack12_Juridical, dateFrom, dateTo);
var pack15chargJuridical = packs_charges.PacksCardsCharges(pack15_Juridical, dateFrom, dateTo); //pack15statusJuridical.Select(c => c.Where(cc => cc.Tdate >= dateFrom && cc.Tdate <= dateTo).Select(s => s.Amount).Sum()).Sum();
var pack15coeffchargJuridical = packs_charges_coeff.PacksCardsChargesCoeff(pack15_Juridical, dateFrom, dateTo, share15price, service_days); //pack15coeffJuridical.Select(c => c.Where(cc => cc.Tdate >= dateFrom && cc.Tdate <= dateTo).Select(s => s.Amount).Sum()).Sum();
pack8chargJuridical = pack8chargJuridical + (pack15chargJuridical - pack15coeffchargJuridical);
pack15chargJuridical = pack15coeffchargJuridical + pack8sum15Juridical;
var JuridicalContraqt = pack12_Juridical.Where(c => c.CardLogs.Any(cc => cc.Status == CardLogStatus.Montage && cc.Date >= dateFrom && cc.Date <= dateTo)).ToList();
//ViewBag.StandardPacks_income = Math.Round((double)((pack12charg) + (pack15charg - (pack15charg * (20m / 100m))) + ((employee6 - (employee6 * (50m / 100m))) * 3)) / Dagger, 2);
//ViewBag.StandardPacks_incomeDagger = Math.Round((double)ViewBag.StandardPacks_income * Dagger_018, 2);
//ViewBag.RegionPacks = Math.Round((double)(pack8charg - (pack8charg * (37.5m / 100m))) / Dagger, 2);
//ViewBag.RegionPacksDangger = Math.Round((double)ViewBag.RegionPacks * Dagger_018, 2);
//ViewBag.StandardPacks_income_Jurdical = Math.Round((double)((pack12chargJuridical) + (pack15chargJuridical - (pack15chargJuridical * (20m / 100m)))) / Dagger, 2);
//ViewBag.StandardPacks_income_JurdicalDagger = Math.Round((double)ViewBag.StandardPacks_income_Jurdical * Dagger_018, 2);
//ViewBag.RegionPacksJuridical = Math.Round((double)(pack8chargJuridical - (pack8chargJuridical * (37.5m / 100m))) / Dagger, 2);
//ViewBag.RegionPacksJuridicalDangger = Math.Round((double)ViewBag.RegionPacksJuridical * Dagger_018, 2);
////////////////////////////////////////////////////////////////////////////////////////////////////////
//თანამშრომელი
//var packemployee = cardsPhysical.Where(c => c.Subscribtions.Where(s => s.Status == true).First().SubscriptionPackages.Any(s => s.Package.Name == "თანამშრომელი")).Select(c => c).ToList();
//var employee6 = packs_charges_coeff.PacksCardsChargesCoeff(packemployee, dateFrom, dateTo, share6price, service_days);
//var packsRent6Sum = packs_charges_rent.PacksCardsChargesRent(packemployee, dateFrom, dateTo);
//employee6 = (employee6 - packsRent6Sum);
var emploe6 = (employee6 - (employee6 * (50m / 100m))) + ((employee6 - (employee6 * (50m / 100m))) * 4);
ViewBag.StandardPacks_income = Math.Round((double)((pack12charg) + (pack15charg) + (emploe6) + (PhysicalContraqt.Count() * 87)) / Dagger, 2);
ViewBag.StandardPacks_incomeDagger = Math.Round((double)ViewBag.StandardPacks_income * Dagger_018, 2);
ViewBag.RegionPacks = Math.Round((double)(pack8charg) / Dagger, 2);
ViewBag.RegionPacksDangger = Math.Round((double)ViewBag.RegionPacks * Dagger_018, 2);
ViewBag.StandardPacks_income_Jurdical = Math.Round((double)((pack12chargJuridical) + (pack15chargJuridical) + (JuridicalContraqt.Count() * 87)) / Dagger, 2);
ViewBag.StandardPacks_income_JurdicalDagger = Math.Round((double)ViewBag.StandardPacks_income_Jurdical * Dagger_018, 2);
ViewBag.RegionPacksJuridical = Math.Round((double)(pack8chargJuridical) / Dagger, 2);
ViewBag.RegionPacksJuridicalDangger = Math.Round((double)ViewBag.RegionPacksJuridical * Dagger_018, 2);
}
return(View());
}
