public PartialViewResult Edit(int DivisionIdEdit, string DivisionCodeEdit, string DivisionNameEdit) { IEnumerable <Division> divisions = new List <Division>(); BCS_Context db = new BCS_Context(); try { using (var dbtransaction = db.Database.BeginTransaction()) { Division division = new Division(); division = db.Division.FirstOrDefault(m => m.DivisionId == DivisionIdEdit); division.Code = DivisionCodeEdit; division.Name = DivisionNameEdit; db.Entry(division).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); dbtransaction.Commit(); divisions = db.Division.ToList().OrderBy(m => m.Name); //divisions.Add(division); SL.LogInfo(User.Identity.Name, Request.RawUrl, "Division - Division Edited - from Terminal: " + ipaddress); return(PartialView("_DivisionPartial", divisions)); } } catch (Exception) { SL.LogInfo(User.Identity.Name, Request.RawUrl, "Division - Edit division failed - from Terminal: " + ipaddress); divisions = db.Division.ToList().OrderBy(m => m.Name); return(PartialView("_DivisionPartial", divisions)); } }
public void ProcessRequest(HttpContext context) { if (context.Request.Files.Count > 0) { BCS_Context db = new BCS_Context(); ApplicationDbContext appdb = new ApplicationDbContext(); HttpFileCollection files = context.Request.Files; HttpPostedFile file = files["uploadData"]; var logUSer = HttpContext.Current.User.Identity.Name; string ZoneGroup = appdb.Users.FirstOrDefault(m => m.UserName == logUSer).ZoneGroup; var billingPeriod1 = db.BillingPeriod.Where(m => m.groupCode == ZoneGroup).ToList(); var billPeriod = billingPeriod1.Max(m => m.BillingPeriodId); if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) { string filename = file.FileName; string filecontenttype = file.ContentType; byte[] filebytes = new byte[file.ContentLength]; var data = file.InputStream.Read(filebytes, 0, Convert.ToInt32(file.ContentLength)); using (var transaction = db.Database.BeginTransaction()) { var excelHelper = new Helper.ExcelHelper(); List <AssessmentPayment> assessments = new List <AssessmentPayment>(); List <AssessmentPayment> tempAssessments = new List <AssessmentPayment>(); db.SaveChanges(); try { var prop = excelHelper.GetProperties(typeof(AssessmentPayment), new[] { "REF_NO", "COMP_CODE", "RATE_TYPE", "ZONE_CODE", "BILL_PERIOD_MONTH", "BILL_PERIOD_YEAR", "BILL_GENERATION_DATE", "OR_NUM", "OR_DATE", "CHECK_AMOUNT", "CASH_AMOUNT", "CHECK_NO", "CHECK_BRANCH", "CHECK_DATE", "TOTAL_PAYMENT", "PRINCIPAL_PAYMENT", "INTEREST_PAYMENT", "ASSESSMENT_TYPE", "BILL_COVERAGE_DATE_FROM", "BILL_COVERAGE_DATE_TO", "RATE_CODE", "ACCT_CODE", "CURRENT_BILLING_PERIOD" }); var data1 = excelHelper.ReadData <AssessmentPayment>(file.InputStream, file.FileName, prop, billPeriod, "Payment", ""); assessments.AddRange(data1); foreach (var item in assessments) { AssessmentPayment assessmentPayment = new AssessmentPayment(); assessmentPayment = item; var compId = db.Company.Where(m => m.CompanyCode == item.COMP_CODE).Select(x => (int?)x.CompanyID).FirstOrDefault() ?? 0; assessmentPayment.COMPANY_ID = compId.ToString(); tempAssessments.Add(assessmentPayment); } db.AssessmentPayment.AddRange(tempAssessments); db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); } } } } }
public PartialViewResult Create(string DivisionCode, string DivisionName) { IEnumerable <Division> divisions = new List <Division>(); BCS_Context db = new BCS_Context(); try { using (var dbtransaction = db.Database.BeginTransaction()) { Division division = new Division(); division.Code = DivisionCode; division.Name = DivisionName; db.Division.Add(division); db.SaveChanges(); dbtransaction.Commit(); divisions = db.Division.ToList().OrderBy(m => m.Name); SL.LogInfo(User.Identity.Name, Request.RawUrl, "Division - Division Created - from Terminal: " + ipaddress); return(PartialView("_DivisionPartial", divisions)); } } catch (Exception) { SL.LogInfo(User.Identity.Name, Request.RawUrl, "Division - Create Division failed - from Terminal: " + ipaddress); divisions = db.Division.ToList().OrderBy(m => m.Name); return(PartialView("_DivisionPartial", divisions)); } }
public ActionResult Register() { BCS_Context db = new BCS_Context(); ViewBag.ZoneGroupCode = new SelectList(db.ZoneGroup.Where(m => m.ZoneGroupCode != "99"), "ZoneGroupCode", "ZoneGroupName"); //Dynamic name of viewbag will be passed in view. ViewBag.Name = new SelectList(context.Roles.Where(m => m.Name != "System Administrator" && m.Name != "Super User"), "Name", "Name"); ViewBag.Division = new SelectList(db.Division.ToList(), "Code", "Name"); return(View()); }
// GET: Division public ActionResult Index() { BCS_Context db = new BCS_Context(); List <Division> division = new List <Division>(); division = db.Division.ToList(); ViewBag.Division = division; return(View()); }
public JsonResult SelectZone(string zonecode) { BCS_Context db = new BCS_Context(); int i = db.ZoneGroup.FirstOrDefault(m => m.ZoneGroupCode == zonecode).ZoneGroupId; List <Zone> zones = new List <Zone>(); zones = db.Zone.Where(m => m.ZoneGroup == i.ToString()).ToList(); return(Json(zones)); }
public decimal Balance(string BillingType, string BillingReference, int CompanyId, int MaxBillingPeriod) { BCS_Context db = new BCS_Context(); var balDr = db.Balances.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "INTEREST").Where(m => m.TransactionType == "BILLING").Where(m => m.BillingReference == BillingReference).Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.Amount) ?? 0; var balCr = db.Balances.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "BALANCE").Where(m => m.TransactionType == "CREDIT").Where(m => m.BillingReference == BillingReference).Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.Amount) ?? 0; var slDr = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "INTEREST").Where(m => m.Other == BillingReference).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Sum(m => (decimal?)m.DebitAmount) ?? 0; var slCr = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "INTEREST").Where(m => m.Other == BillingReference).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Sum(m => (decimal?)m.CreditAmount) ?? 0; decimal bal = (balDr - (balCr * -1)) + (slDr - slCr); return(bal); }
public async Task <ActionResult> Register(RegisterViewModel model, String Name) { BCS_Context db = new BCS_Context(); if (ModelState.IsValid) { Encrypt encrypt = new Encrypt(model.Password); var user = new ApplicationUser { UserName = model.UserName, Email = model.Email, LastName = model.LastName, MiddleName = model.MiddleName, GivenName = model.GivenName, ZoneGroup = model.ZoneGroupCode, Status = model.Status, Division = model.Division, LocalPass = encrypt.EncryptedValue, Zone = model.Zone }; var result = await UserManager.CreateAsync(user, model.Password); if (result.Succeeded) { //Assign Role to user Here await this.UserManager.AddToRoleAsync(user.Id, Name); RoleAssignmentMatrix roleAssignmentMatrix = new RoleAssignmentMatrix(); roleAssignmentMatrix.UserName = model.UserName; roleAssignmentMatrix.ZoneGroup = model.ZoneGroupCode; db.RoleAssignmentMatrix.Add(roleAssignmentMatrix); await db.SaveChangesAsync(); //Ends Here //await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false); // For more information on how to enable account confirmation and password reset please visit http://go.microsoft.com/fwlink/?LinkID=320771 // Send an email with this link // string code = await UserManager.GenerateEmailConfirmationTokenAsync(user.Id); // var callbackUrl = Url.Action("ConfirmEmail", "Account", new { userId = user.Id, code = code }, protocol: Request.Url.Scheme); // await UserManager.SendEmailAsync(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>"); //return RedirectToAction("Index", "Home"); TempData["TransactionSuccess"] = "Add"; SL.LogInfo(User.Identity.Name, Request.RawUrl, "Account Registered - from Terminal: " + ipaddress); return(RedirectToAction("ViewUser", "Account")); } AddErrors(result); } // If we got this far, something failed, redisplay form ViewBag.ZoneGroupCode = new SelectList(db.ZoneGroup.ToList(), "ZoneGroupCode", "ZoneGroupName"); ViewBag.Name = new SelectList(context.Roles.ToList(), "Name", "Name"); ViewBag.Division = new SelectList(db.Division.ToList(), "Code", "Name"); TempData["TransactionSuccess"] = "Failed"; return(View(model)); }
public PartialViewResult GetList(string divisionCode) { IEnumerable <Division> division; BCS_Context db = new BCS_Context(); if (!string.IsNullOrEmpty(divisionCode)) { division = db.Division.Where(m => m.Code == divisionCode).ToList().OrderBy(m => m.Name); } else { division = db.Division.ToList().OrderBy(m => m.Name); } return(PartialView("_DivisionPartial", division)); }
public PartialViewResult GetLogs(string searchstr, string selsrch) { List <systemlogs> syslogs = new List <systemlogs>(); BCS_Context db = new BCS_Context(); if (!string.IsNullOrEmpty(searchstr)) { //var datedate = Convert.ToDateTime(searchstr.ToString()); switch (selsrch) { case "default": syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.UserName.Contains(searchstr)).ToList(); break; case "username": syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.UserName.Contains(searchstr)).ToList(); break; case "level": syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.loglevel.Contains(searchstr)).ToList(); break; case "message": syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.remarks.Contains(searchstr)).ToList(); break; case "": syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.UserName.Contains(searchstr)).ToList(); break; case "date": DateTime dt1 = Convert.ToDateTime(searchstr); DateTime dtaam1 = dt1.AddHours(23); DateTime dtbam2 = dt1.AddMinutes(59); syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.timestamp.Year == dt1.Year && s.timestamp.Month == dt1.Month && s.timestamp.Day == dt1.Day).ToList(); break; } } else { //syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).ToList(); syslogs = db.systemlogs.OrderByDescending(s => s.timestamp).Where(s => s.UserName == s.UserName).ToList(); } return(PartialView("_GetLogsPartial", syslogs)); }
public decimal Interest(string BillingType, int CompanyId, string billingReference, DateTime CoverageFrom, DateTime BillDate, decimal amount, string LastId, decimal currentPrincipalAmount) { BCS_Context db = new BCS_Context(); decimal interest = 0; LatestBillingPeriodInfo latestBillingPeriodInfo = new LatestBillingPeriodInfo(); var a = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId && m.BillingType.ToUpper() == BillingType.ToUpper() && m.TransactionType.ToUpper() == "BILLING" && m.BillingPeriod == db.SubsidiaryLedger.Where(k => k.CompanyId == CompanyId && k.BillingType == BillingType && k.TransactionType == "BILLING").Max(j => j.BillingPeriod)) .Select(x => new { x.BillingPeriod, x.BillingReference, x.TransactionReference }).FirstOrDefault(); // will return the latest billing period and biling reference per company per billing reference var b = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId) .Where(m => m.TransactionType == "BILLING") .Where(m => m.BillingType == BillingType).ToList().Select(n => n.BillingPeriod).Distinct().ToList(); //array of billing period per selected company and billing reference //If balance detected => check if has previous billing //If previous billing has no balance => ( (Principal_From_Previous * 0.01) / n) * Days_Late) //If previous billing has balance => ( (Previous_Balance * 0.01) / n) * Days_Late) + ( (Principal_Amount * 0.01) / n) * Days_Late) if (b.Count() > 0) { //Has previous billing var index = b.IndexOf(a.BillingPeriod); var previousBillingPeriod = b[index]; //get previous billing id //var previousBillingPeriodForBalances = 0; //if (b.Count == 1) // previousBillingPeriodForBalances = 0; //else // previousBillingPeriodForBalances = previousBillingPeriod; var existingBalance = 0.0M; var previousInterest = 0.0M; try { existingBalance = db.Balances //get the Balance amount from previous balance .Where(l => l.CurrentBillingPeriod == previousBillingPeriod) .Where(n => n.BillingType.ToUpper() == BillingType.ToUpper()) .Where(v => v.CompanyId == CompanyId) .Where(k => k.TransactionType == "BILLING") .Where(o => o.BillingSubType == "BALANCE") //Remove if Type in Balances will not be used. .FirstOrDefault().Amount; previousInterest = db.SubsidiaryLedger.Where(m => m.BillingPeriod == previousBillingPeriod) .Where(q => q.CompanyId == CompanyId) .Where(e => e.TransactionType.ToUpper() == "BILLING") .Where(t => t.BillingType.ToUpper() == BillingType.ToUpper()) .Where(r => r.BillingSubType.ToUpper() == "INTEREST").FirstOrDefault().DebitAmount; } catch (Exception) { } var previousPrincipalAmount = 0.0M; previousPrincipalAmount = db.SubsidiaryLedger.Where(m => m.BillingPeriod == previousBillingPeriod) .Where(q => q.CompanyId == CompanyId) .Where(e => e.TransactionType.ToUpper() == "BILLING") .Where(t => t.BillingType.ToUpper() == BillingType.ToUpper()) .Where(r => r.BillingSubType.ToUpper() == "PRINCIPAL").FirstOrDefault().DebitAmount; previousPrincipalAmount = previousPrincipalAmount > 0 ? previousPrincipalAmount : currentPrincipalAmount; var previousBillingMonth = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).SingleOrDefault().DateTo; var previousDueDate = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).SingleOrDefault().DueDate; int daysInAMonth = System.DateTime.DaysInMonth(previousBillingMonth.Year, previousBillingMonth.Month); double totalDaysLate = (BillDate.Date - previousDueDate.Value).TotalDays; if (existingBalance > 0) { interest = ((previousPrincipalAmount * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()) + (((amount + previousInterest) * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()); } else { interest = (amount * 0.01M) / daysInAMonth; interest *= decimal.Parse(totalDaysLate.ToString()); } } else { int lastid = int.Parse(LastId); try { if (BillingType.ToUpper() == "GARBAGE") { var previousBillingMonth = db.BillingPeriod.Where(m => m.BillingPeriodId == lastid).SingleOrDefault().DateTo; var previousDueDate = db.BillingPeriod.Where(m => m.BillingPeriodId == lastid).SingleOrDefault().DueDate; int daysInAMonth = System.DateTime.DaysInMonth(previousBillingMonth.Year, previousBillingMonth.Month); double totalDaysLate = (BillDate.Date - previousDueDate.Value).TotalDays; interest = (amount * 0.01M) / daysInAMonth; interest *= decimal.Parse(totalDaysLate.ToString()); } } catch (Exception) { } //No previous billing = No interest } return(interest + (interest * 0.12M)); //12% is an add on by Sir Rufel 02-07-2017 }
public decimal Interest(string BillingType, int CompanyId, string BillingReference, string ZoneCode, DateTime CoverageFrom, DateTime BillDate, decimal amount) { bool hasValidPreviousBillingPeriod = true; ApplicationDbContext context = new ApplicationDbContext(); var UserName = HttpContext.Current.User.Identity.Name; var user = context.Users.Where(m => m.UserName == UserName).FirstOrDefault(); string ZoneGroup = context.Users.FirstOrDefault(m => m.Id == user.Id).ZoneGroup; BCS_Context db = new BCS_Context(); decimal interest = 0; var a = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId && m.TransactionType.ToUpper() == "BILLING" && m.BillingPeriod == db.SubsidiaryLedger.Where(k => k.CompanyId == CompanyId && k.BillingType == BillingType && k.TransactionType == "BILLING" && k.Other == ZoneCode).Max(j => j.BillingPeriod)) .Select(x => new { x.BillingPeriod, x.BillingReference, x.TransactionReference }).FirstOrDefault(); // will return the latest billing period and biling reference per company per billing reference var b = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId) .Where(m => m.BillingType == BillingType) .Where(m => m.TransactionType == "BILLING") .Where(m => m.Other == ZoneCode).ToList().Select(n => n.BillingPeriod).Distinct().ToList(); //array of billing period per selected company and billing reference var latestBillingPeriod = db.BillingPeriod.Where(m => m.BillingPeriodId == a.BillingPeriod).FirstOrDefault(); if (latestBillingPeriod.groupCode != ZoneGroup) { hasValidPreviousBillingPeriod = false; } //If balance detected => check if has previous billing //If previous billing has no balance => ( (Principal_From_Previous * 0.01) / n) * Days_Late) //If previous billing has balance => ( (Previous_Balance * 0.01) / n) * Days_Late) + ( (Principal_Amount * 0.01) / n) * Days_Late) if (b.Count() > 0 && hasValidPreviousBillingPeriod) { //Has previous billing var index = b.IndexOf(a.BillingPeriod); var previousBillingPeriod = b[index]; //get previous billing id //var previousBillingPeriodForBalances = 0; //if (b.Count == 1) // previousBillingPeriodForBalances = 0; //else // previousBillingPeriodForBalances = previousBillingPeriod; var previousBalance = 0.0M; try { previousBalance = db.Balances //get the Balance amount from previous balance .Where(l => l.CurrentBillingPeriod == previousBillingPeriod) .Where(n => n.BillingType.ToUpper() == BillingType.ToUpper()) .Where(v => v.CompanyId == CompanyId) .Where(k => k.TransactionType == "BILLING") .Where(o => o.BillingSubType == "BALANCE") //Remove if Type in Balances will not be used. .Where(p => p.BillingReference == ZoneCode) .Where(c => c.TransactionReference == a.TransactionReference).SingleOrDefault().Amount; } catch (Exception) { } var previousPrincipalAmount = db.SubsidiaryLedger.Where(m => m.BillingPeriod == previousBillingPeriod) .Where(q => q.CompanyId == CompanyId) .Where(l => l.Other == ZoneCode) .Where(e => e.TransactionType.ToUpper() == "BILLING") .Where(t => t.BillingType.ToUpper() == BillingType.ToUpper()) .Where(r => r.BillingSubType.ToUpper() == "PRINCIPAL").SingleOrDefault().DebitAmount; var previousBillingMonth = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).SingleOrDefault().DateTo; var previousDueDate = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).SingleOrDefault().DueDate; int daysInAMonth = System.DateTime.DaysInMonth(previousBillingMonth.Year, previousBillingMonth.Month); double totalDaysLate = (BillDate.Date - previousDueDate.Value).TotalDays; if (previousBalance > 0) { interest = ((previousPrincipalAmount * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()) + ((amount * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()); } else { interest = (previousPrincipalAmount * 0.01M) / daysInAMonth; interest *= decimal.Parse(totalDaysLate.ToString()); } } else { //No previous billing = No interest } return(interest + (interest * 0.12M)); //12% is an add on by Sir Rufel 02-07-2017 }
public decimal Interest(string BillingType, int CompanyId, string billingReference, DateTime CoverageFrom, DateTime BillDate, decimal amount, decimal prevbalance, string lastId, decimal currentPrincipalAmount) { BCS_Context db = new BCS_Context(); //AS OF NOW COMPUTATION OF BILLING INTEREST IS FIXED TO PER MONTH /////////////////////////////////////////////////////////////////////////////// decimal interest = 0; //var maxbillp = db.SubsidiaryLedger.Where(k => k.CompanyId == CompanyId && k.BillingType == BillingType && k.TransactionType == "BILLING").Max(j => j.BillingPeriod); //var ab = db.SubsidiaryLedger.Where(m => m.CompanyId == CompanyId).Where(j=>j.TransactionType.ToUpper() == "BILLING").Where(h=> h.BillingPeriod == maxbillp).Where(l => l.BillingReference == billingReference).FirstOrDefault(); var a = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId && m.TransactionType.ToUpper() == "BILLING" && m.BillingPeriod == db.SubsidiaryLedger.Where(k => k.CompanyId == CompanyId && k.BillingType == BillingType && k.TransactionType == "BILLING").Max(j => j.BillingPeriod)) .Where(l => l.BillingReference == billingReference) .Select(x => new { x.BillingPeriod, x.BillingReference, x.TransactionReference }).FirstOrDefault(); // will return the latest billing period and biling reference per company per billing reference //var countBalances = db.Balances.Where(m => m.CompanyId == CompanyId && m.BillingReference == billingReference && m.BillingType.ToUpper() == BillingType.ToUpper() && m.TransactionReference == a.TransactionReference).ToList(); //if countBalances == 0 then no interest.; The billing period of 1st billing generation is 0.; Changes made 12/27/2016 var b = db.SubsidiaryLedger .Where(m => m.CompanyId == CompanyId) .Where(m => m.BillingType == BillingType) .Where(m => m.TransactionType == "BILLING") .Where(l => l.BillingReference == billingReference).ToList().Select(n => n.BillingPeriod).Distinct().ToList(); //array of billing period per selected company and billing reference //If balance detected => check if has previous billing //If previous billing has no balance => ( (Principal_From_Previous * 0.01) / n) * Days_Late) //If previous billing has balance => ( (Previous_Balance * 0.01) / n) * Days_Late) + ( (Principal_Amount * 0.01) / n) * Days_Late) if (b.Count() > 0) { //Has previous billing var index = b.IndexOf(a.BillingPeriod); var previousBillingPeriod = b[index]; //get previous billing id //var billp = b[b.Count - 1]; //var previousBillingPeriodForBalances = 0; //if (b.Count == 1) // previousBillingPeriodForBalances = 0; //else // previousBillingPeriodForBalances = previousBillingPeriod; var existingBalance = 0.0M; var previousInterest = 0.0M; try { existingBalance = db.Balances.Where(m => m.BillingReference == billingReference) //get the Balance amount from previous balance .Where(l => l.CurrentBillingPeriod == previousBillingPeriod) .Where(n => n.BillingType.ToUpper() == BillingType.ToUpper()) .Where(v => v.CompanyId == CompanyId) .Where(k => k.TransactionType == "BILLING") .Where(o => o.BillingSubType == "BALANCE") //Remove if Type in Balances will not be used. .Where(c => c.TransactionReference == a.TransactionReference).FirstOrDefault().Amount; previousInterest = db.SubsidiaryLedger.Where(m => m.BillingPeriod == previousBillingPeriod) .Where(q => q.CompanyId == CompanyId) .Where(w => w.BillingReference == billingReference) .Where(e => e.TransactionType.ToUpper() == "BILLING") .Where(t => t.BillingType.ToUpper() == BillingType.ToUpper()) .Where(r => r.BillingSubType.ToUpper() == "INTEREST").FirstOrDefault().DebitAmount; } catch (Exception) { } //var previousBalance = prevbalance; var previousPrincipalAmount = db.SubsidiaryLedger.Where(m => m.BillingPeriod == previousBillingPeriod) .Where(q => q.CompanyId == CompanyId) .Where(w => w.BillingReference == billingReference) .Where(e => e.TransactionType.ToUpper() == "BILLING") .Where(t => t.BillingType.ToUpper() == BillingType.ToUpper()) .Where(r => r.BillingSubType.ToUpper() == "PRINCIPAL").FirstOrDefault().DebitAmount; previousPrincipalAmount = previousPrincipalAmount > 0 ? previousPrincipalAmount : currentPrincipalAmount; var previousBillingMonth = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).FirstOrDefault().DateTo; var previousDueDate = db.BillingPeriod.Where(m => m.BillingPeriodId == previousBillingPeriod).FirstOrDefault().DueDate; int daysInAMonth = System.DateTime.DaysInMonth(previousBillingMonth.Year, previousBillingMonth.Month); double totalDaysLate = (BillDate.Date - previousDueDate.Value).TotalDays; if (existingBalance > 0) { interest = ((prevbalance * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()) + (((amount + previousInterest) * 0.01M) / daysInAMonth) * decimal.Parse(totalDaysLate.ToString()); } else { interest = (amount * 0.01M); interest /= daysInAMonth; interest *= Math.Abs(decimal.Parse(totalDaysLate.ToString())); } } else { int LastId = int.Parse(lastId); try { var previousBillingMonth = db.BillingPeriod.Where(m => m.BillingPeriodId == LastId).FirstOrDefault().DateTo; var previousDueDate = db.BillingPeriod.Where(m => m.BillingPeriodId == LastId).FirstOrDefault().DueDate; int daysInAMonth = System.DateTime.DaysInMonth(previousBillingMonth.Year, previousBillingMonth.Month); double totalDaysLate = (BillDate.Date - previousDueDate.Value).TotalDays; interest = (amount * 0.01M); interest /= daysInAMonth; interest *= Math.Abs(decimal.Parse(totalDaysLate.ToString())); } catch (Exception) { } } return(interest + (interest * 0.12M)); //12% is an add on by Sir Rufel 02-07-2017 }
public void ProcessRequest(HttpContext context) { var a = context.Request.QueryString["Copy"].ToString(); var b = context.Request.QueryString["Type"].ToString(); var origin = context.Request.QueryString["Origin"].ToString(); var billing = context.Request.QueryString["Billing"].ToString(); DateTime originNew = Convert.ToDateTime(origin); DateTime billingNew = Convert.ToDateTime(billing); HttpFileCollection files = context.Request.Files; HttpPostedFile file = files["uploadData"]; int copy = int.Parse(a); BCS_Context db = new BCS_Context(); if ((file != null) && (file.ContentLength > 0) && (!string.IsNullOrEmpty(file.FileName))) { using (var dbtransaction = db.Database.BeginTransaction()) { try { ApplicationDbContext con = new ApplicationDbContext(); //db.PassedOnBillingInformation.RemoveRange(db.PassedOnBillingInformation) var userid = System.Web.HttpContext.Current.User.Identity.GetUserId(); var zonegroup = con.Users.FirstOrDefault(m => m.Id == userid).ZoneGroup; SearchCompanyPerGroup searchCompanyPerGroup = new SearchCompanyPerGroup(zonegroup); IEnumerable <Company> company = searchCompanyPerGroup.Companies; using (var package = new ExcelPackage(file.InputStream)) { var currentSheet = package.Workbook.Worksheets; var workSheet = currentSheet.First(); var noOfCol = workSheet.Dimension.End.Column; var noOfRow = workSheet.Dimension.End.Row; for (int rowIterator = 12; rowIterator <= noOfRow; rowIterator++) { var compid = workSheet.Cells[rowIterator, 3].Value; var newCompId = int.Parse(compid.ToString()); Company comp = company.Where(m => m.CompanyID == newCompId).FirstOrDefault(); var type = workSheet.Cells[rowIterator, 12].Value.ToString() != null ? workSheet.Cells[rowIterator, 12].Value.ToString() : ""; if (type == b) { PassedOnBillingInformation passedOnBillingInformation = new PassedOnBillingInformation(); passedOnBillingInformation.Type = type; var cid = workSheet.Cells[rowIterator, 3].Value != null ? workSheet.Cells[rowIterator, 3].Value : 0; passedOnBillingInformation.CompanyId = int.Parse(cid.ToString()); passedOnBillingInformation.BillingPeriod = int.Parse(a); var amt = workSheet.Cells[rowIterator, 13].Value != null ? workSheet.Cells[rowIterator, 13].Value : 0; passedOnBillingInformation.Amount = decimal.Parse(amt.ToString()); passedOnBillingInformation.CreatedBy = userid; passedOnBillingInformation.CreateDate = DateTime.Now; passedOnBillingInformation.OriginDate = originNew; passedOnBillingInformation.BillingDate = billingNew; passedOnBillingInformation.Remarks = workSheet.Cells[rowIterator, 14].ToString(); db.PassedOnBillingInformation.Add(passedOnBillingInformation); db.SaveChanges(); } } } dbtransaction.Commit(); } catch (Exception ex) { var error = ex.Message; dbtransaction.Rollback(); } } } }
public List <T> ReadData <T>(Stream stream, string fileName, List <PropertyInfo> columns, int billperiod, string uploadType, string uploadtype2) { var list = new List <T>(); if (fileName.EndsWith("xls")) { var workbook = new HSSFWorkbook(stream); var sheet = workbook.GetSheetAt(0); if (sheet.PhysicalNumberOfRows <= 1) { return(list); } if (uploadType == "Billing") { var test1 = sheet.GetRow(1).GetCell(3).ToString(); BCS_Context db = new BCS_Context(); var lst = db.AssessmentBilling.Where(m => m.TRANS_DATE == test1).ToList(); db.Database.ExecuteSqlCommand("Delete from AssessmentBillings where TRANS_DATE = '" + test1 + "'"); //db.AssessmentBilling.RemoveRange(lst); db.SaveChanges(); } else if (uploadType == "Payment") { var test1 = sheet.GetRow(1).GetCell(8).ToString(); BCS_Context db = new BCS_Context(); var lst = db.AssessmentPayment.Where(m => m.OR_DATE == test1).ToList(); db.Database.ExecuteSqlCommand("Delete from AssessmentPayments where OR_DATE = '" + test1 + "'"); //db.AssessmentPayment.RemoveRange(lst); db.SaveChanges(); } //else if (uploadType == "Admin") //{ // BCS_Context db = new BCS_Context(); // var lst = db.AdminFee.Where(m => m.BillingPeriodId == billperiod).ToList(); // db.Database.ExecuteSqlCommand("delete from AdminFees where BillingPeriodId = '" + billperiod + "'"); // //db.AdminFee.RemoveRange(lst); // db.SaveChanges(); //} for (int i = 1; i < sheet.PhysicalNumberOfRows; i++) { var item = Activator.CreateInstance <T>(); var row = sheet.GetRow(i); var j = 0; foreach (var column in columns) { if (j == row.Cells.Count) { Type t2 = Nullable.GetUnderlyingType(column.PropertyType) ?? column.PropertyType; column.SetValue(item, Convert.ChangeType(billperiod, t2), null); Type t3 = Nullable.GetUnderlyingType(columns[14].PropertyType) ?? columns[14].PropertyType; columns[14].SetValue(item, Convert.ChangeType(uploadtype2, t3), null); break; } var val = row.GetCell(j).ToString(); if (val == "AYALA LAND INC") { } if (val.Contains("\n")) { val = val.Substring(0, val.Length - 1); } j++; if (string.IsNullOrWhiteSpace(val)) { continue; } Type t = Nullable.GetUnderlyingType(column.PropertyType) ?? column.PropertyType; column.SetValue(item, Convert.ChangeType(val, t), null); } list.Add(item); } } else if (fileName.EndsWith("xlsx")) { var workbook = new XSSFWorkbook(stream); var sheet = workbook.GetSheetAt(0); for (int i = 0; i < sheet.PhysicalNumberOfRows; i++) { var item = Activator.CreateInstance <T>(); var row = sheet.GetRow(i); var j = 0; foreach (var column in columns) { var val = row.GetCell(j).StringCellValue; if (string.IsNullOrWhiteSpace(val)) { continue; } Type t = Nullable.GetUnderlyingType(column.PropertyType) ?? column.PropertyType; column.SetValue(item, Convert.ChangeType(val, t), null); j++; } list.Add(item); } } return(list); }
public decimal Balance(string BillingType, string BillingReference, int CompanyId, int MaxBillingPeriod) { decimal balance = 0; BCS_Context db = new BCS_Context(); var hasForwarded = db.Balances.Where(m => m.BillingType == BillingType) //.Where(m => m.TransactionType == "BILLING") .Where(m => m.BillingReference.ToUpper() == BillingReference.ToUpper()) .Where(m => m.CompanyId == CompanyId) .Where(m => m.BillingSubType == "BALANCE") .Where(m => m.BalanceType == "FORWARDED BALANCE").ToList(); if (hasForwarded.Count > 0) { var maxBalanceData = hasForwarded.Max(m => (int?)m.CurrentBillingPeriod) ?? 0; var balDr1 = db.Balances.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "BALANCE") .Where(m => m.TransactionType == "BILLING") .Where(m => m.BillingReference.ToUpper() == BillingReference.ToUpper()) .Where(m => m.CurrentBillingPeriod == maxBalanceData) .Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.Amount) ?? 0; var balCr1 = 0; //db.Balances.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "BALANCE") // .Where(m => m.TransactionType == "CREDIT") // .Where(m => m.CurrentBillingPeriod == maxBalanceData) // .Where(m => m.BillingReference.ToUpper() == BillingReference.ToUpper()) // .Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.Amount) ?? 0; var slDr1 = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "PRINCIPAL").Where(m => m.Other.ToUpper() == BillingReference.ToUpper()).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Sum(m => (decimal?)m.DebitAmount) ?? 0; var slCr1 = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "PRINCIPAL").Where(m => m.Other.ToUpper() == BillingReference.ToUpper()).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Sum(m => (decimal?)m.CreditAmount) ?? 0; var slCrWTAX = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.BillingSubType == "WTAX").Where(m => m.Other.ToUpper() == BillingReference.ToUpper()).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Sum(m => (decimal?)m.CreditAmount) ?? 0; slCr1 += slCrWTAX; bool isCancel = false; try { //If has Cancel OR Payment. Do not Subtract CR in SL //Add this .Where(m => m.TransactionType.ToUpper() == "PAYMENT") and .Max(x => x.SubsidiaryLedgerId) var maxId = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType).Where(m => m.TransactionType.ToUpper() == "PAYMENT").Where(m => m.BillingSubType == "PRINCIPAL").Where(m => m.Other.ToUpper() == BillingReference.ToUpper()).Where(m => m.CompanyId == CompanyId).Where(m => m.BillingPeriod == MaxBillingPeriod).Max(x => x.SubsidiaryLedgerId); var referenceNumber = db.SubsidiaryLedger.Where(m => m.SubsidiaryLedgerId == maxId).Select(x => x.TransactionReference); var cancelFlag = db.Database.SqlQuery <String>("select CancelFlag from GeneralCollection where ORNumber = '" + referenceNumber + "'").FirstOrDefault(); if (cancelFlag.ToUpper() == "YES") { isCancel = true; } } catch (Exception) { } if (!isCancel) { balance = (balDr1 - (balCr1 * -1)) + (slDr1 - slCr1); } else { balance = (balDr1 - (balCr1 * -1)) + (slDr1); } } else { var balDr = db.Balances.Where(m => m.BillingType == BillingType) .Where(m => m.BillingSubType == "BALANCE") .Where(m => m.TransactionType == "BILLING") .Where(m => m.BillingReference.ToUpper() == BillingReference.ToUpper()) .Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.Amount) ?? 0; var balCr = db.SubsidiaryLedger.Where(m => m.BillingType == BillingType) .Where(m => m.BillingSubType.ToUpper() == "PRINCIPAL") .Where(m => m.TransactionType == "Payment") .Where(m => m.Other.ToUpper() == BillingReference.ToUpper()) .Where(m => m.CompanyId == CompanyId).Sum(m => (decimal?)m.CreditAmount) ?? 0; //balance = Math.Abs(balDr - balCr); balance = balDr - balCr; } return(balance); }
public void ProcessRequest(HttpContext context) { if (context.Request.Files.Count > 0) { var billPeriodFromContext = context.Request.QueryString["billperiod"].ToString(); var zoneTypeFromContext = context.Request.QueryString["zonetype"].ToString(); int billPeriod = int.Parse(billPeriodFromContext); BCS_Context db = new BCS_Context(); HttpFileCollection files = context.Request.Files; HttpPostedFile file = files["uploadData"]; if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) { using (var transaction = db.Database.BeginTransaction()) { var excelHelper = new Helper.ExcelHelper(); List <AdminFee> adminfees = new List <AdminFee>(); db.AdminFee.RemoveRange(db.AdminFee.Where(m => m.BillingPeriodId == billPeriod && m.Upload_Type.ToUpper().Trim() == zoneTypeFromContext.ToUpper().Trim()).ToList()); db.SaveChanges(); try { var uname = HttpContext.Current.User.Identity.Name; ApplicationDbContext dbcontext = new ApplicationDbContext(); var user = dbcontext.Users.Where(m => m.UserName == uname).FirstOrDefault(); var zone = user.ZoneGroup; var zoneid = db.ZoneGroup.Where(m => m.ZoneGroupCode == zone).FirstOrDefault().ZoneGroupId; var prop = excelHelper.GetProperties(typeof(AdminFee), new[] { "Ecozone", "Zone_Type", "Company_Name", "Enterprise_Type", "Employment", "Zone_Code", "Month", "Year", "Comp_Code", "Developer", "Dev_Comp_Code", "Total_Locators", "Total_Employment", "BillingPeriodId", "Upload_Type" }); var data = excelHelper.ReadData <AdminFee>(file.InputStream, file.FileName, prop, billPeriod, "Admin", zoneTypeFromContext); foreach (var item in data) { var groupId = ""; try { string tempZone = ""; if (zoneTypeFromContext.ToUpper().Trim() != "ALL") { if (item.Zone_Type.ToUpper().Trim() == "IT CENTER" || item.Zone_Type.ToUpper().Trim() == "IT PARK") { tempZone = "IT"; } else if (item.Zone_Type.ToUpper().Trim() != "IT CENTER" && item.Zone_Type.ToUpper().Trim() != "IT PARK" && item.Zone_Type.ToUpper().Trim() != "MANUFACTURING CEZ") { tempZone = "OTHERS"; } else if (item.Zone_Type.ToUpper().Trim() == "MANUFACTURING SEZ") { tempZone = "MANUFACTURING"; } else { tempZone = item.Zone_Type.ToUpper().Trim(); } //tempZone = item.Zone_Type.ToUpper().Trim() == "IT CENTER" || item.Zone_Type.ToUpper().Trim() == "IT PARK" ? // "IT" : item.Zone_Type.ToUpper().Trim(); //tempZone = item.Zone_Type.ToUpper().Trim() != "IT CENTER" && item.Zone_Type.ToUpper().Trim() != "IT PARK" && item.Zone_Type.ToUpper().Trim() != "MANUFACTURING CEZ" ? // "OTHERS" : item.Zone_Type.ToUpper().Trim(); //tempZone = item.Zone_Type.ToUpper().Trim() == "MANUFACTURING CEZ" ? // "MANUFACTURING" : item.Zone_Type.ToUpper().Trim(); } if (tempZone == zoneTypeFromContext.ToUpper().Trim()) { try { var zoneCode = db.Company.Where(m => m.CompanyCode == item.Dev_Comp_Code).FirstOrDefault().ZoneCode ?? ""; if (zoneCode != null || zoneCode != "") { groupId = db.Zone.Where(m => m.ZoneCode == zoneCode).FirstOrDefault().ZoneGroup ?? ""; } } catch (Exception) { IOHelper.LogTxt(item.Dev_Comp_Code, billPeriodFromContext, zoneTypeFromContext); //throw new Exception("Zone code is has no match in current record " + item.Dev_Comp_Code); } } else { throw new Exception("Missmatch zone type " + tempZone); } } catch (Exception ex) { throw new Exception(ex.Message); } if (groupId == zoneid.ToString()) { adminfees.Add(item); } } db.AdminFee.AddRange(adminfees); db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); HttpContext.Current.Response.ContentType = "text/plain"; HttpContext.Current.Response.Write(ex.Message); throw new Exception(ex.Message); } } } //if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) // { // string filename = file.FileName; // string filecontenttype = file.ContentType; // byte[] filebytes = new byte[file.ContentLength]; // var data = file.InputStream.Read(filebytes, 0, Convert.ToInt32(file.ContentLength)); // //int billingPeriodId = Convert.ToInt32(frm["BillingPeriod"].ToString()); // List<AdminFee> adminFee = new List<AdminFee>(); // using (var transaction = db.Database.BeginTransaction()) // { // db.AdminFee.RemoveRange(db.AdminFee.Where(m => m.BillingPeriodId == billPeriod).ToList()); // db.SaveChanges(); // try // { // using (var package = new ExcelPackage(file.InputStream)) // { // List<String> notRegisteredCompany = new List<string>(); // var currentSheet = package.Workbook.Worksheets; // var workSheet = currentSheet.First(); // var noOfCol = workSheet.Dimension.End.Column; // var noOfRow = workSheet.Dimension.End.Row; // for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++) // { // AdminFee adminfee = new AdminFee(); // adminfee.Ecozone = workSheet.Cells[rowIterator, 1].Value != null ? workSheet.Cells[rowIterator, 1].Value.ToString() : ""; // adminfee.Zone_Type = workSheet.Cells[rowIterator, 2].Value != null ? workSheet.Cells[rowIterator, 2].Value.ToString() : ""; // adminfee.Company_Name = workSheet.Cells[rowIterator, 3].Value != null ? workSheet.Cells[rowIterator, 3].Value.ToString() : ""; // adminfee.Enterprise_Type = workSheet.Cells[rowIterator, 4].Value != null ? workSheet.Cells[rowIterator, 4].Value.ToString() : ""; // adminfee.Employment = workSheet.Cells[rowIterator, 5].Value != null ? workSheet.Cells[rowIterator, 5].Value.ToString() : ""; // adminfee.Zone_Code = workSheet.Cells[rowIterator, 6].Value != null ? workSheet.Cells[rowIterator, 6].Value.ToString() : ""; // adminfee.Month = workSheet.Cells[rowIterator, 7].Value != null ? workSheet.Cells[rowIterator, 7].Value.ToString() : ""; // adminfee.Year = workSheet.Cells[rowIterator, 8].Value != null ? workSheet.Cells[rowIterator, 8].Value.ToString() : ""; // adminfee.Comp_Code = workSheet.Cells[rowIterator, 9].Value != null ? workSheet.Cells[rowIterator, 9].Value.ToString() : ""; // adminfee.Developer = workSheet.Cells[rowIterator, 10].Value != null ? workSheet.Cells[rowIterator, 10].Value.ToString() : ""; // adminfee.Dev_Comp_Code = workSheet.Cells[rowIterator, 11].Value != null ? workSheet.Cells[rowIterator, 11].Value.ToString() : ""; // adminfee.Total_Locators = workSheet.Cells[rowIterator, 12].Value != null ? workSheet.Cells[rowIterator, 12].Value.ToString() : ""; // adminfee.Total_Employment = workSheet.Cells[rowIterator, 13].Value != null ? workSheet.Cells[rowIterator, 13].Value.ToString() : ""; // adminfee.BillingPeriodId = billPeriod; // if (!db.Company.Any(m => m.CompanyCode == adminfee.Comp_Code)) // notRegisteredCompany.Add(adminfee.Company_Name); // db.AdminFee.Add(adminfee); // db.SaveChanges(); // } // //TempData["notRegisteredCompany"] = notRegisteredCompany; // //TempData["TransactionSuccess"] = "Complete"; // transaction.Commit(); // } // } // catch (Exception) // { // transaction.Rollback(); // //TempData["TransactionSuccess"] = "Failed"; // } // } // } } }