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