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));
}
