public PartialViewResult CanceledClients(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select com_ref as 'Ref' , com_name as 'Customer', com_acc_cancel_date as 'Cancel Start',com_acc_cancel_enddate as 'Cancel End', com_acc_cancel_note as 'Comment' " + $"FROM threepeaks_tpms.tblcompany where com_acc_cancel = 2 and com_acc_cancel_enddate between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}'"; var model = new List <ManagementDashboard.Models.CanceledClients>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var depMov = new Models.CanceledClients(); depMov.Ref = dRow.Field <string>("Ref"); depMov.Customer = dRow.Field <string>("Customer"); depMov.CancelStart = (DateTime)dRow.Field <DateTime>("Cancel Start"); depMov.CancelEnd = (DateTime)dRow.Field <DateTime>("Cancel End"); depMov.Comment = dRow.Field <string>("Comment"); model.Add(depMov); } return(PartialView(model)); }
public PartialViewResult NoRunClients(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(-1).AddDays(-1); var db = new DBConnect(); string query = "select com_ref as Ref ,com_startdate as StartDate, com_name as Customer from tblcompany" + " where com_acc_cancel in (0,1) and (select count(*) from tblrbr where rbr_comref = com_ref and " + $"rbr_status not in (99) and rbr_date <= '{endDate.ToString("yyyy-MM-dd")}' limit 1) = 0 and com_startdate > '{startDate.ToString("yyyy-MM-dd")}'"; var model = new List <ManagementDashboard.Models.NoRunClients>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var depMov = new Models.NoRunClients(); depMov.Ref = dRow.Field <string>("Ref"); depMov.StartDate = (DateTime)dRow.Field <DateTime>("StartDate"); depMov.Customer = dRow.Field <string>("Customer"); model.Add(depMov); } return(PartialView(model)); }
public PartialViewResult InCancelation(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select com_ref as 'Customer Ref', com_name as 'Company Name', case com_ac_pending " + "when 0 then '' when 1 then 'Pending' when 2 then 'Pending' end as 'Pending Status', case com_acc_cancel " + "when 0 then 'Active' when 1 then 'Cancelling' when 2 then 'Cancelled' else '' end as 'Cancel Status', " + "com_acc_cancel_note as 'Comment' from `tblcompany` where com_acc_cancel = 1"; var model = new List <ManagementDashboard.Models.InCancelation>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var inCancel = new Models.InCancelation(); inCancel.Ref = dRow.Field <string>("Customer Ref"); inCancel.Customer = dRow.Field <string>("Company Name"); inCancel.CancelStatus = dRow.Field <string>("Cancel Status"); inCancel.PendingStatus = dRow.Field <string>("Pending Status"); inCancel.Comment = dRow.Field <string>("Comment"); model.Add(inCancel); } return(PartialView(model)); }
// GET: Top // Top 10 Value of Debit // Customer Reference / Debit Reference / Action Date / RBR /Amount public PartialViewResult GetTopValueDebits(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select dbt_comref,dbt_ref,dbt_date,dbt_rbr,dbt_amount from tbldebits left join tblrbr on rbr_id = dbt_rbr where rbr_status not in (99) and " + $"dbt_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' order by dbt_amount desc limit 20"; var model = new List <ManagementDashboard.Models.GetTopDebitValue>(); // do you have a model yet ??? (); var result = db.Query(query); foreach (DataRow dr in result.Tables[0].Rows) { var topItem = new Models.GetTopDebitValue(); topItem.Amount = (decimal)dr.Field <decimal>("dbt_amount"); topItem.CustomerRef = dr.Field <string>("dbt_comref"); topItem.DebitRef = dr.Field <string>("dbt_ref"); topItem.Rbr = (int)dr.Field <Int32>("dbt_rbr"); topItem.ActionDate = (DateTime)dr.Field <DateTime>("dbt_date"); model.Add(topItem); } return(PartialView(model)); }
public PartialViewResult uManageCustomers(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select rbr_comref as 'Customer Refernece', com_name as 'Company', count(*) as 'No Of Runs', " + "sum(rbr_total_records) as 'Total Records' from tblrbr left join tblcompany on com_ref = rbr_comref where " + $"rbr_subtype = 'File' and rbr_status not in (99) and rbr_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by rbr_comref "; var model = new List <ManagementDashboard.Models.uManageCustomers>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var uMan = new Models.uManageCustomers(); uMan.Ref = dRow.Field <string>("Customer Refernece"); uMan.Customer = dRow.Field <string>("Company"); uMan.NoRuns = (int)dRow.Field <Int64>("No Of Runs"); uMan.TotalRecords = (int)dRow.Field <decimal>("Total Records"); model.Add(uMan); } return(PartialView(model)); }
public PartialViewResult DepositMovement(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select cref, deposit_date, amount from tbl_accounting_depost_tracking where deposit_date" + $" between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' order by cref,deposit_date"; var model = new List <ManagementDashboard.Models.DepositMovement>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var depMov = new Models.DepositMovement(); depMov.Customer = dRow.Field <string>("cref"); depMov.Date = (DateTime)dRow.Field <DateTime>("deposit_date"); depMov.Value = (decimal)dRow.Field <decimal>("amount"); model.Add(depMov); } return(PartialView(model)); }
public PartialViewResult GetTopUnpaids(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select hec_description as Description , hec_code as Code, count(*) as Count ,sum(dbt_amount) as amount from tbldebits left join tblhyphen_errcodes on dbt_accrejcode = hec_code left join tblrbr on rbr_id = dbt_rbr where dbt_pass_unpaid in (2,3) and rbr_status not in (99) " + $" and rbr_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by hec_code"; var model = new List <ManagementDashboard.Models.GetTopUnpaids>(); var result = db.Query(query); //got an issue with this query... OK will help just now foreach (DataRow dRow in result.Tables[0].Rows) { var depMov = new Models.GetTopUnpaids(); depMov.Description = dRow.Field <string>("Description"); depMov.Code = dRow.Field <string>("Code"); //Type t = dRow["Count"].GetType(); depMov.Count = (int)dRow.Field <Int64>("Count"); depMov.Amount = (int)dRow.Field <decimal>("amount"); model.Add(depMov); } return(PartialView(model)); }
public PartialViewResult PendingClients(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select com_Ref as 'Ref', com_name as 'Customer',com_ac_pending_date as 'Pending Date' from tblcompany " + $"where com_ac_pending = 1 and com_acc_cancel != 2 and com_ac_pending_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}'"; var model = new List <ManagementDashboard.Models.PendingClients>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var PenCus = new Models.PendingClients(); PenCus.Ref = dRow.Field <string>("Ref"); PenCus.Customer = dRow.Field <string>("Customer"); PenCus.PendingDate = (DateTime)dRow.Field <DateTime>("Pending Date"); model.Add(PenCus); } return(PartialView(model)); }
public PartialViewResult GetTopValueCustomers(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select dbt_comref, sum(dbt_amount) as initial, count(*) as c from tbldebits left join tblrbr on rbr_id = dbt_rbr where dbt_date between" + $"'{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' and rbr_status not in (99) group by dbt_comref order by initial desc limit 10"; var model = new List <ManagementDashboard.Models.GetTopValueCustomer>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var topCus = new Models.GetTopValueCustomer(); topCus.Customer = dRow.Field <string>("dbt_comref"); topCus.Collections = (decimal)dRow.Field <decimal>("initial"); topCus.NumOfRecords = (int)dRow.Field <Int64>("c"); model.Add(topCus); } return(PartialView(model)); }
public PartialViewResult NewClients(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "SELECT com_ref as 'Customer Refernece', com_name as 'Company', com_startdate as 'Start Date' FROM " + $"threepeaks_tpms.tblcompany where com_startdate between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}'"; var model = new List <ManagementDashboard.Models.NewClients>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var nClient = new Models.NewClients(); nClient.Ref = dRow.Field <string>("Customer Refernece"); nClient.Customer = dRow.Field <string>("Company"); nClient.StartDate = (DateTime)dRow.Field <DateTime>("Start Date"); model.Add(nClient); } return(PartialView(model)); }
public PartialViewResult HighestCustomerValue(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select dbt_comref as 'Ref', count(*) as 'Count', sum(dbt_amount) as 'Value' from tbldebits " + $"left join tblrbr on rbr_id = dbt_rbr where dbt_date between '{startDate.ToString("yyyy-MM-dd")}' and" + $"'{endDate.ToString("yyyy-MM-dd")}' " + $"and rbr_status<> 99 and dbt_cdv = 3 and dbt_accrej = 3 group by dbt_comref order by count(*) desc limit 20"; var model = new List <ManagementDashboard.Models.HighestCustomerValue>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var highCus = new Models.HighestCustomerValue(); highCus.Ref = dRow.Field <string>("Ref"); highCus.Count = (int)dRow.Field <Int64>("Count"); highCus.Value = (int)dRow.Field <decimal>("Value"); model.Add(highCus); } return(PartialView(model)); }
public PartialViewResult GetTop20Unpaids(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); string query = "select dbt_comref as 'Company Ref', count(*) as 'Unpaids' from tbldebits " + "where dbt_pass_unpaid in (2,3) and dbt_pass_unpaid_datetime between " + $"'{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by " + "dbt_comref order by `Unpaids` desc limit 20"; var model = new List <ManagementDashboard.Models.GetTop20Unpaids>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var t20unpaid = new Models.GetTop20Unpaids(); t20unpaid.Customer = dRow.Field <string>("Company Ref"); t20unpaid.Unpaids = (int)dRow.Field <Int64>("Unpaids"); model.Add(t20unpaid); } return(PartialView(model)); }
public PartialViewResult NoRetentionDeposit(int id) { var db = new DBConnect(); string query = $"select com_ref as 'Ref', com_name as 'Customer', case com_retterms when 1 then 'Retention' when 3 then 'Deposit' else '' end 'Collateral', " + $"case com_retterms when 1 then ifnull((select sum(rbr_total_retention) from tblrbr where rbr_status = 3 and rbr_comref = com_ref " + $"and rbr_ret_released = 0),0) when 2 then - 1 when 3 then ifnull((select sum(amount) from tbl_accounting_depost_tracking where " + $"cref = com_ref),0) end as 'Value', if ((select count(*) from tblrbr where rbr_comref = com_ref limit 1) >= 1, 'Yes','No') as " + $"'Have Runs' from tblcompany where com_acc_cancel in (0, 1) "; var model = new List <ManagementDashboard.Models.NoRetentionDeposit>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var RetDep = new Models.NoRetentionDeposit(); RetDep.Ref = dRow.Field <string>("Ref"); RetDep.Customer = dRow.Field <string>("Customer"); RetDep.Collateral = dRow.Field <string>("Collateral"); RetDep.Value = (int)dRow.Field <decimal>("Value"); RetDep.HaveRuns = dRow.Field <string>("Have Runs"); model.Add(RetDep); } List <ManagementDashboard.Models.NoRetentionDeposit> filteredModel = model.Where(x => x.Value == 0).ToList(); return(PartialView(filteredModel)); }
public PartialViewResult ListOfPendingClient() { var db = new DBConnect(); string query = "select com_ref as 'Customer Ref', com_name as 'Company Name', com_ac_pending_date as 'Start Date', " + " com_acc_pending_flagdate as 'End Date', case com_ac_pending when 0 then '' when 1 then 'Pending' when 2 then 'Pending' end as 'Pending Status', " + "case com_acc_cancel when 0 then 'Active' when 1 then 'Cancelling' when 2 then 'Cancelled' else '' end as 'CancelCancel Status'," + " srv_name as 'Service', rep_name as 'Sales Agent' from tblcompany left join tblservice on srv_id = com_serviceid left join " + "tblrep on rep_id = com_repid where com_acc_cancel = 0 and com_ac_pending<> 0"; var model = new List <ManagementDashboard.Models.ListOfPendingClient>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var LisPenCus = new Models.ListOfPendingClient(); LisPenCus.Ref = dRow.Field <string>("Customer Ref"); LisPenCus.Customer = dRow.Field <string>("Company Name"); LisPenCus.StartDate = (DateTime)dRow.Field <DateTime>("Start Date"); LisPenCus.EndDate = (DateTime)dRow.Field <DateTime>("End Date"); LisPenCus.PendingStatus = dRow.Field <string>("Pending Status"); LisPenCus.Service = dRow.Field <string>("Service"); LisPenCus.SalesAgent = dRow.Field <string>("Sales Agent"); model.Add(LisPenCus); } return(PartialView(model)); }
public PartialViewResult SubmissionsTrendChart(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); //Get data from db - query string query = $"select rbr_date,count(*) as 'count' from tblrbr where rbr_date " + $"between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' " + $"and rbr_status not in (4,99) group by rbr_date"; DataSet result = db.Query(query); //create model to pass to view, var listLabels = new List <string>(); foreach (DataRow dr in result.Tables[0].Rows) { listLabels.Add(Convert.ToDateTime(dr["rbr_date"]).ToString(LABEL_DATE_FORMAT)); } string[] labels = listLabels.ToArray(); var data = new List <ComplexDataset>(); var submissionData = new List <double>(); foreach (DataRow dr in result.Tables[0].Rows) { submissionData.Add((double)dr.Field <Int64>("count")); } data.Add(new ComplexDataset { Data = submissionData, Label = "Submissions", FillColor = "rgba(151,187,205,0.2)", StrokeColor = "rgba(151,187,205,1)", PointColor = "rgba(151,187,205,1)", PointStrokeColor = "#fff", PointHighlightFill = "#fff", PointHighlightStroke = "rgba(151,187,205,1)", }); ManagementDashboard.Models.ChartModel model = new ChartModel(); model.Labels = labels; model.ComplesDatasets = data; model.Title = $"Date Range {startDate.ToString("dd/MM/yyyy")} to {endDate.ToString("dd/MM/yyyy")}"; model.ChartID = $"chart{id}"; return(PartialView("LineChartPartial", model)); }
public PartialViewResult NewGrowth(int id) { int monthSelected = id; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime prevStartDate = currentDate.AddMonths(-2); DateTime prevEndDateTime = currentDate.AddMonths(-1).AddDays(-1); DateTime nextStartDate = currentDate.AddMonths(-1); DateTime nextEndDateTime = currentDate.AddDays(-1); var db = new DBConnect(); string query = $"select dbt_comref as 'Ref', sum(if (dbt_date between '{prevStartDate.ToString("yyyy-MM-dd")}' and " + $"'{prevEndDateTime.ToString("yyyy-MM-dd")}' ,dbt_amount,0)) as 'Prev', " + $"sum(if (dbt_date between '{nextStartDate.ToString("yyyy-MM-dd")}' and " + $"'{nextEndDateTime.ToString("yyyy-MM-dd")}' ,dbt_amount,0)) as 'Next' from tbldebits dd left join tblrbr on rbr_id = dbt_rbr where " + $"dbt_date between '{prevStartDate.ToString("yyyy-MM-dd")}' and '{nextEndDateTime.ToString("yyyy-MM-dd")}' and rbr_status not in (99) group by dbt_comref"; var model = new List <ManagementDashboard.Models.NewGrowth>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var newGro = new Models.NewGrowth { CustomerReference = dRow.Field <string>("Ref"), //Customer Reference PrevValue = (int)dRow.Field <decimal>("Prev"), //Prev. Month Collection NextValue = (int)dRow.Field <decimal>("Next"), // PrevDateTime = prevEndDateTime, NextDatetime = nextEndDateTime, RiskMulti = 1 }; //Don't Include customers with 0 value and no change if (newGro.PrevValue == 0 && newGro.NextValue == 0) { continue; } newGro.Calculate(); if (newGro.IsOverRiskValue) { model.Add(newGro); } } model = model.OrderByDescending(x => x.Percent) .ThenBy(x => x.CustomerReference) //.Take(400) .ToList(); return(PartialView(model)); }
public PartialViewResult NewClientByIndustry(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); //Get data from db - query string query = $"SELECT count(*) as 'Count', srv_name as 'Industry' FROM threepeaks_tpms.tblcompany left join tblservice on com_serviceid = srv_id " + $"where com_startdate between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by srv_name"; DataSet result = db.Query(query); var listLabels = new List <string>(); foreach (DataRow dr in result.Tables[0].Rows) { listLabels.Add(dr.Field <string>("Industry")); } string[] labels = listLabels.ToArray(); var data = new List <ComplexDataset>(); var submissionData = new List <double>(); foreach (DataRow dr in result.Tables[0].Rows) { //Type t = dr["Count"].GetType(); submissionData.Add((int)dr.Field <Int64>("Count")); } data.Add(new ComplexDataset { Data = submissionData, Label = "Submissions", FillColor = "rgba(151,187,205,0.2)", StrokeColor = "rgba(151,187,205,1)", PointColor = "rgba(151,187,205,1)", PointStrokeColor = "#fff", PointHighlightFill = "#fff", PointHighlightStroke = "rgba(151,187,205,1)", }); ManagementDashboard.Models.ChartModel model = new ChartModel(); model.Labels = labels; model.ComplesDatasets = data; model.Title = $"Date Range {startDate.ToString("dd/MM/yyyy")} to {endDate.ToString("dd/MM/yyyy")}"; model.ChartID = $"ChartNewClientByIndustry{id}"; model.ChartHeight = 350; return(PartialView("BarChartPartial", model)); }
private List <string> GetFulcrumCustomerRefernces() { var db = new DBConnect(); //Get data from db - query string query = $"select customer from tbl_gateway_fulcrum_client"; DataSet result = db.Query(query); var customers = new List <string>(); foreach (DataRow dr in result.Tables[0].Rows) { customers.Add(dr.Field <string>("customer")); } return(customers); }
public PartialViewResult DormantClients(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); DateTime today = DateTime.Now.Date; int span = 45; var db = new DBConnect(); string query = $"select com_ref as 'Ref', case com_acc_cancel when 0 then if (com_ac_pending=1, if(com_acc_pending_flagdate < '{startDate.ToString("yyyy - MM - dd")}','Pending Ended', " + $"if (com_ac_pending_date > '{endDate.ToString("yyyy-MM-dd")}','Active', 'Pending')), 'Active') when 1 then 'In Cancellation' when 2 then " + $"if (com_acc_cancel_enddate < '{startDate.ToString("yyyy-MM-dd")}','Cancelled Before', if (com_acc_cancel_date < '{startDate.ToString("yyyy-MM-dd")}', " + $"'In Cancellation', if (com_acc_cancel_date < '{endDate.ToString("yyyy-MM-dd")}' ,'In Cancellation','Active'))) else -1 end as 'State', " + $"(select max(rbr_date) from tblrbr where rbr_status not in (99) and rbr_comref = com_ref and rbr_date< '{startDate.ToString("yyyy-MM-dd")}') as 'Prev Run', " + $"ifnull((select min(rbr_date) from tblrbr where rbr_status not in (99) and rbr_comref = com_ref and rbr_date between '{startDate.ToString("yyyy-MM-dd")}' " + $"and '{endDate.ToString("yyyy-MM-dd")}'),'na') as 'Next', datediff((select max(rbr_date) from tblrbr where rbr_status not in (99) and rbr_comref = com_ref " + $"and rbr_date < '{startDate.ToString("yyyy-MM-dd")}'), '{today.ToString("yyyy-MM-dd")}') as 'span' from tblcompany where (select min(rbr_date) from tblrbr " + $"where rbr_status not in (99) and rbr_comref = com_ref) < '{startDate.ToString("yyyy-MM-dd")}' and(select count(rbr_id) from tblrbr where rbr_status not in " + $"(99) and rbr_comref = com_ref and rbr_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}') = 0 and if (com_acc_cancel = 2 , " + $"if (com_acc_cancel_enddate < '{startDate.ToString("yyyy-MM-dd")}', 0,if (com_acc_cancel_date < '{startDate.ToString("yyyy-MM-dd")}', 1, " + $"if (com_acc_cancel_date < '{endDate.ToString("yyyy-MM-dd")}' ,1,1))),1) = 1 and com_acc_cancel = 0 and com_ac_pending = 0 and datediff((select max(rbr_date) " + $" from tblrbr where rbr_status not in (99) and rbr_comref = com_ref and rbr_date< '{startDate.ToString("yyyy-MM-dd")}'), " + $"'{endDate.ToString("yyyy-MM-dd")}') <= (-1 * '{span}') order by span, ifnull((select min(rbr_date) from tblrbr where rbr_status not in (99) " + $"and rbr_comref = com_ref and rbr_date > '{endDate.ToString("yyyy-MM-dd")}'),'0000-00-00'), (select max(rbr_date) from tblrbr where rbr_status not in " + $"(99) and rbr_comref = com_ref and rbr_date< '{startDate.ToString("yyyy-MM-dd")}'), com_ref"; var model = new List <ManagementDashboard.Models.DormantClients>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var domCus = new Models.DormantClients(); domCus.Ref = dRow.Field <string>("Ref"); domCus.State = dRow.Field <string>("State"); domCus.PrevRun = (DateTime)dRow.Field <DateTime>("Prev Run"); domCus.Next = dRow.Field <string>("Next"); Type t = dRow["span"].GetType(); domCus.Span = (int)dRow.Field <Int32>("span"); model.Add(domCus); } return(PartialView(model)); }
public PartialViewResult RetNotReleased(int id, bool IsBetween) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(1); var db = new DBConnect(); string query = "SELECT rbr_comref as 'Ref' , rbr_id as 'RBR',rbr_date as 'Action Date',rbr_ret_release_date as 'Release Date',rbr_total_retention as 'Retention Amount' , " + "datediff(rbr_ret_release_date, now()) as 'Age' FROM threepeaks_tpms.tblrbr left join tblcompany on com_ref = rbr_comref " + $"where rbr_status = 3 and rbr_ret_released = 0 and com_retterms in (1, 2) "; if (IsBetween) { query += $" and rbr_ret_release_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' and datediff(rbr_ret_release_date, now()) < - 35"; } else { query += $" and rbr_ret_release_date <= '{endDate.ToString("yyyy-MM-dd")}' and datediff(rbr_ret_release_date, now()) < - 35"; } var model = new List <ManagementDashboard.Models.RetNotReleased>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var depMov = new Models.RetNotReleased(); depMov.Ref = dRow.Field <string>("Ref"); depMov.Rbr = (int)dRow.Field <Int32>("RBR"); depMov.ActionDate = (DateTime)dRow.Field <DateTime>("Action Date"); depMov.ReleaseDate = (DateTime)dRow.Field <DateTime>("Release Date"); depMov.RetAmount = (int)dRow.Field <decimal>("Retention Amount"); depMov.Age = (int)dRow.Field <Int64>("Age"); //model.Add(depMov); if (depMov.Age <= 20) { model.Add(depMov); } } return(PartialView(model)); }
public PartialViewResult TransactionCodes() { var db = new DBConnect(); string query = "SELECT count(*) as 'Count', case cpr_loaded_at_hyphen " + "when 0 then 'Not Sent for Loading' when 1 then 'Waiting on Confirmation' " + "when 2 then 'Confirmed' else cpr_loaded_at_hyphen end as 'State' FROM `tbl_customer_profile` " + "group by cpr_loaded_at_hyphen"; var model = new List <ManagementDashboard.Models.TransactionCodes>(); var result = db.Query(query); foreach (DataRow dRow in result.Tables[0].Rows) { var tranCod = new Models.TransactionCodes(); tranCod.Count = (int)dRow.Field <Int64>("Count"); tranCod.State = dRow.Field <string>("State"); model.Add(tranCod); } return(PartialView(model)); }
public ActionResult ManagementPrediction() { var vm = new Models.SQLReportTableViewModel(); var db = new DBConnect(); string file = Server.MapPath("~") + "SQLQueries\\ManagementPrediction.sql"; if (System.IO.File.Exists(file)) { StreamReader streamReader = new StreamReader(file); var fileContent = streamReader.ReadToEnd(); var query = fileContent; var result = db.Query(query); string htmlTable = ConvertDataTableToHTML(result.Tables[0]); vm.HtmlTable = htmlTable; } return(View(vm)); }
public PartialViewResult SubmissionReceivedTrend(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); //Get data from db - query string query = $"select date_format(rbr_datetime_sub,'%Y-%m-%d') as 'Received Date', count(*) as 'Count', " + $"sum(rbr_total_records) as 'Total Records' from tblrbr where rbr_datetime_sub " + $"between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by date_format(rbr_datetime_sub, '%Y-%m-%d')"; DataSet result = db.Query(query); var listLabels = new List <string>(); foreach (DataRow dr in result.Tables[0].Rows) { listLabels.Add(Convert.ToDateTime(dr["Received Date"]).ToString(LABEL_DATE_FORMAT)); } string[] labels = listLabels.ToArray(); var data = new List <ComplexDataset>(); var ReconData = new List <double>(); foreach (DataRow dr in result.Tables[0].Rows) { //Type t = dr["Count"].GetType(); ReconData.Add((int)dr.Field <Int64>("Count")); } data.Add(new ComplexDataset { Data = ReconData, Label = "Recon Debit Trend", FillColor = "rgba(151,187,205,0.2)", StrokeColor = "rgba(151,187,205,1)", PointColor = "rgba(151,187,205,1)", PointStrokeColor = "#fff", PointHighlightFill = "#fff", PointHighlightStroke = "rgba(151,187,205,1)", }); ManagementDashboard.Models.ChartModel model = new ChartModel(); model.Labels = labels; model.ComplesDatasets = data; model.Title = $"Date Range {startDate.ToString("dd-MM-yyyy")} to {endDate.ToString("dd-MM-yyyy")}"; model.ChartID = $"ChartSubmissionReceivedTrend{id}"; return(PartialView("LineChartPartial", model)); }
public PartialViewResult ReconDebitTrendChart(int id) { int monthSelected = 0; if (id > 0) { monthSelected = -1 * id; } DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(monthSelected); DateTime startDate = currentDate; DateTime endDate = currentDate.AddMonths(1).AddDays(-1); var db = new DBConnect(); //Get data from db - query string query = $"SELECT date_format(rcd_date,'%Y-%m-%d') as groupDate, sum(rcd_paid) as paid FROM threepeaks_tpms.tblrecon_data where rcd_date between '{startDate.ToString("yyyy-MM-dd")}' and '{endDate.ToString("yyyy-MM-dd")}' group by date_format(rcd_date,'%Y-%m-%d')"; DataSet result = db.Query(query); var listLabels = new List <string>(); foreach (DataRow dr in result.Tables[0].Rows) { listLabels.Add(Convert.ToDateTime(dr["groupDate"]).ToString(LABEL_DATE_FORMAT)); } string[] labels = listLabels.ToArray(); var data = new List <ComplexDataset>(); var ReconData = new List <double>(); foreach (DataRow dr in result.Tables[0].Rows) { //Type t = dr["paid"].GetType(); ReconData.Add((double)dr.Field <decimal>("paid")); } data.Add(new ComplexDataset { Data = ReconData, Label = "Recon Debit Trend", FillColor = "rgba(151,187,205,0.2)", StrokeColor = "rgba(151,187,205,1)", PointColor = "rgba(151,187,205,1)", PointStrokeColor = "#fff", PointHighlightFill = "#fff", PointHighlightStroke = "rgba(151,187,205,1)", }); ManagementDashboard.Models.ChartModel model = new ChartModel(); model.Labels = labels; model.ComplesDatasets = data; model.Title = $"Date Range {startDate.ToString("dd-MM-yyyy")} to {endDate.ToString("dd-MM-yyyy")}"; model.ChartID = $"TrendPaymentValue{id}"; model.ShowTable = false; return(PartialView("LineChartPartial", model)); }