public bool RegenerateBillingPerEnterprise() { bool isDeleted = false; try { BCS_Context db = new BCS_Context(); List <Company> Companies = new List <Company>(); Companies = db.Company.Where(m => m.EnterpriseType.ToUpper() == EnterpriseType.ToUpper()).ToList(); foreach (var item in Companies) { //db.SubsidiaryLedger.RemoveRange(db.SubsidiaryLedger.Where(m => m.BillingPeriod == id)); db.Database.ExecuteSqlCommand("Delete from SubsidiaryLedgers where BillingPeriod = '" + id + "' and CompanyId = '" + item.CompanyID + "' and TransactionType = 'BILLING'"); //db.Database.ExecuteSqlCommand("Delete from GeneralBillings where BillingPeriod = '" + id + "' and CompanyId = '" + companyId + "' and TransactionType = 'BILLING'"); db.Database.ExecuteSqlCommand("Delete from Balances where CurrentBillingPeriod = '" + id + "' and CompanyId = '" + item.CompanyID + "'");// and TransactionType = 'BILLING'"); } db.SaveChanges(); isDeleted = true; } catch (Exception) { isDeleted = false; } return(isDeleted); }
public bool RegenerateBilling() { bool isDeleted = false; try { //db.SubsidiaryLedger.RemoveRange(db.SubsidiaryLedger.Where(m => m.BillingPeriod == id)); db.Database.ExecuteSqlCommand("Delete from SubsidiaryLedgers where BillingPeriod = '" + id + "' and TransactionType = 'BILLING'"); //db.Database.ExecuteSqlCommand("Delete from GeneralBillings where BillingPeriod = '" + id + "' and TransactionType = 'BILLING'"); db.Database.ExecuteSqlCommand("Delete from Balances where CurrentBillingPeriod = '" + id + "'"); //and TransactionType = 'BILLING'"); db.SaveChanges(); isDeleted = true; } catch (Exception) { isDeleted = false; } return(isDeleted); }
public bool EOMTransaction() { bool isSuccess = false; ApplicationDbContext context = new ApplicationDbContext(); string ZoneGroup = context.Users.SingleOrDefault(m => m.Id == userid).ZoneGroup; SearchCompanyPerGroup searchCompanyPerGroup = new SearchCompanyPerGroup(ZoneGroup); List <Company> Companies = searchCompanyPerGroup.Companies; using (var transaction = db.Database.BeginTransaction()) { try { int MaxId = db.EOMProcessing.Max(m => (int?)m.EOMNumber) ?? 0; BillingPeriod billingPeriod = new BillingPeriod(); billingPeriod = db.BillingPeriod.FirstOrDefault(m => m.BillingPeriodId == maxBillingPeriodId); billingPeriod.EOMStatus = "DONE"; db.Entry(billingPeriod).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); foreach (var item in Companies) { decimal[] Balance = new decimal[7]; int CompanyId = item.CompanyID; DateTime? CompanyDateCreated = db.Company.SingleOrDefault(m => m.CompanyID == CompanyId).CreateDate; for (int i = 0; i < BillingType.Count(); i++) { string billingtype = BillingType[i].ToString(); var Credit = db.SubsidiaryLedger.Where(m => m.BillingType.ToUpper() == billingtype).Where(m => m.CompanyId == CompanyId && m.BillingSubType.ToUpper() != "INTEREST").Sum(m => (decimal?)m.CreditAmount) ?? 0; var Debit = db.SubsidiaryLedger.Where(m => m.BillingType.ToUpper() == billingtype).Where(m => m.CompanyId == CompanyId && m.BillingSubType.ToUpper() != "INTEREST").Sum(m => (decimal?)m.DebitAmount) ?? 0; Balance[i] = Debit - Credit; } //Save to database if (!isExist(item.CompanyID)) { for (int i = 0; i < BillingType.Count() - 1; i++) { EOMProcessing eOMProcessing = new EOMProcessing(); eOMProcessing.CompanyId = CompanyId; eOMProcessing.BillingType = BillingType[i].ToString(); eOMProcessing.EOMNumber = ++MaxId; db.EOMProcessing.Add(eOMProcessing); db.SaveChanges(); } } for (int i = 0; i < BillingType.Count() - 1; i++) { string billingtype = BillingType[i].ToString(); EOMProcessing updateEOM = db.EOMProcessing.Where(m => m.CompanyId == CompanyId).Where(m => m.BillingType == billingtype).SingleOrDefault(); switch (month) { case 12: { updateEOM.November = Balance[i]; db.SaveChanges(); break; } case 11: { updateEOM.October = Balance[i]; db.SaveChanges(); break; } case 10: { updateEOM.September = Balance[i]; db.SaveChanges(); break; } case 9: { updateEOM.August = Balance[i]; db.SaveChanges(); break; } case 8: { updateEOM.July = Balance[i]; db.SaveChanges(); break; } case 7: { updateEOM.June = Balance[i]; db.SaveChanges(); break; } case 6: { updateEOM.May = Balance[i]; db.SaveChanges(); break; } case 5: { updateEOM.April = Balance[i]; db.SaveChanges(); break; } case 4: { updateEOM.March = Balance[i]; db.SaveChanges(); break; } case 2: { updateEOM.February = Balance[i]; db.SaveChanges(); break; } case 1: { updateEOM.January = 0; updateEOM.February = 0; updateEOM.March = 0; updateEOM.April = 0; updateEOM.May = 0; updateEOM.June = 0; updateEOM.July = 0; updateEOM.August = 0; updateEOM.September = 0; updateEOM.October = 0; updateEOM.November = 0; updateEOM.December = 0; int CompanyYear = CompanyDateCreated.Value.Year - DateTime.Now.Year; if (CompanyYear == 0) { updateEOM.PrevYr1 = Balance[i]; } else if (CompanyYear == 1) { updateEOM.PrevYr2 = Balance[i]; } else if (CompanyYear == 2) { updateEOM.PrevYr3 = Balance[i]; } else if (CompanyYear == 3) { updateEOM.PrevYr4 = Balance[i]; } else if (CompanyYear >= 4) { updateEOM.OtherPrevYr = Balance[i]; } //int prevYrToUpdate = 0; //if (updateEOM.PrevYr1 != null) // updateEOM.PrevYr1 = Balance[i]; //else if (updateEOM.PrevYr2 != null) // updateEOM.PrevYr2 = Balance[i]; //else if (updateEOM.PrevYr3 != null) // updateEOM.PrevYr3 = Balance[i]; //else if (updateEOM.PrevYr4 != null) // updateEOM.PrevYr4 = Balance[i]; //else if (updateEOM.OtherPrevYr != null) // updateEOM.OtherPrevYr = Balance[i]; ////below code will only use if multiple EOM Process in month of January. ////Check the latest prev year data. //if (updateEOM.PrevYr1 != null) // prevYrToUpdate++; //else if (updateEOM.PrevYr2 != null) // prevYrToUpdate++; //else if (updateEOM.PrevYr3 != null) // prevYrToUpdate++; //else if (updateEOM.PrevYr4 != null) // prevYrToUpdate++; //else if(updateEOM.OtherPrevYr != null) // prevYrToUpdate++; ////update the latest prev year data //if (prevYrToUpdate ==1) // updateEOM.PrevYr1 = Balance[i]; //else if (prevYrToUpdate == 2) // updateEOM.PrevYr2 = Balance[i]; //else if (prevYrToUpdate == 3) // updateEOM.PrevYr3 = Balance[i]; //else if (prevYrToUpdate == 4) // updateEOM.PrevYr4 = Balance[i]; //else if (prevYrToUpdate == 5) // updateEOM.OtherPrevYr = Balance[i]; db.SaveChanges(); break; } default: break; } } }//end foreach company transaction.Commit(); isSuccess = true; } catch (Exception ex) { transaction.Rollback(); } }//end transaction return(isSuccess); }