/// <summary>
/// Dashboards the stats.
/// </summary>
/// <param name="Email">The email.</param>
/// <returns></returns>
public DashboardStats DashboardStats(string Email)
{
DashboardStats model = new DashboardStats();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand("[DASHBOARD].[PROC_DASHBOARD_ATTACHMENT_ASSOCIATION]", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@LOGGEDINUSEREMAIL", Email);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.DOWNLOADS = (int)rdr["DOWNLOADS"];
model.EXPIRINGLINKS = (int)rdr["EXPIRINGLINKS"];
model.FILESPURGING = (int)rdr["FILESPURGING"];
model.FILESRECEIVED = (int)rdr["FILESRECEIVED"];
model.PURCHASEDFILES = (int)rdr["PURCHASEDFILES"];
model.UPLOADS = (int)rdr["UPLOADS"];
}
con.Close();
return(model);
}
}
public Dashboard GetDashboardSummary()
{
Dashboard ds = new Dashboard();
SqlServerConnection conn = new SqlServerConnection();
SqlDataReader dr = conn.SqlServerConnect("DECLARE @yr INT = YEAR(GETDATE()); SELECT COUNT(*)total, ISNULL(SUM(CASE WHEN ps_gender='male' THEN 1 ELSE 0 END),0)males, ISNULL(SUM(CASE WHEN ps_gender='female' THEN 1 ELSE 0 END),0)females, ISNULL(SUM(CASE WHEN age BETWEEN 0 AND 17 THEN 1 ELSE 0 END),0)children, ISNULL(SUM(CASE WHEN age BETWEEN 18 AND 34 THEN 1 ELSE 0 END),0)youths, ISNULL(SUM(CASE WHEN age BETWEEN 35 AND 64 THEN 1 ELSE 0 END),0)adults, ISNULL(SUM(CASE WHEN age>=65 THEN 1 ELSE 0 END),0)seniors, ISNULL(SUM(CASE WHEN pp_enrolled_on>=DATEADD(MONTH,-4,GETDATE()) THEN 1 ELSE 0 END),0)t_new, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=@yr THEN 1 ELSE 0 END),0)t_year, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=(@yr-1) THEN 1 ELSE 0 END),0)t_prev, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=@yr AND pp_category=10 THEN 1 ELSE 0 END),0)new, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=@yr AND pp_category=11 THEN 1 ELSE 0 END),0)relapse, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=@yr AND pp_category=12 THEN 1 ELSE 0 END),0)failr, ISNULL(SUM(CASE WHEN YEAR(pp_enrolled_on)=@yr AND pp_category=13 THEN 1 ELSE 0 END),0)transfr, COUNT(DISTINCT pp_facility)facility FROM (SELECT pp_facility, ps_gender, pp_category, pp_enrolled_on, DATEDIFF(hour,ps_dob,GETDATE())/8766 AS age FROM PatientProgram INNER JOIN Patient ON pt_idnt=pp_patient INNER JOIN Person ON pt_person=ps_idnt " + GetRolesCommand("pp_facility") + ") As Foo");
if (dr.Read())
{
ds = new Dashboard {
AllPatients = Convert.ToInt64(dr[0]),
Male = Convert.ToInt64(dr[1]),
Female = Convert.ToInt64(dr[2]),
Children = Convert.ToInt64(dr[3]),
Youths = Convert.ToInt64(dr[4]),
Adults = Convert.ToInt64(dr[5]),
Seniors = Convert.ToInt64(dr[6]),
CaseNew = Convert.ToInt64(dr[7]),
CaseCurrent = Convert.ToInt64(dr[8]),
CasePrevious = Convert.ToInt64(dr[9]),
NewCatg = Convert.ToInt64(dr[10]),
Relapse = Convert.ToInt64(dr[11]),
Failure = Convert.ToInt64(dr[12]),
Transfer = Convert.ToInt64(dr[13]),
Facility = Convert.ToInt64(dr[14])
};
}
if (ds.CaseCurrent == ds.CasePrevious || ds.CaseCurrent == 0)
{
ds.CasePercent = 0;
}
else if (ds.CasePrevious == 0)
{
ds.CasePercent = 100;
}
else if (ds.CaseCurrent > ds.CasePrevious)
{
ds.CasePercent = (ds.CasePrevious / ds.CaseCurrent) * 100;
}
else if (ds.CasePrevious > ds.CaseCurrent)
{
ds.CasePercent = 100 - ((ds.CaseCurrent / ds.CasePrevious) * 100);
ds.CaseStatus = "Decrease";
}
conn = new SqlServerConnection();
dr = conn.SqlServerConnect("SELECT mnth_prefix, COUNT(*) [count] FROM Months LEFT OUTER JOIN PatientProgram ON mnth_idnt=MONTH(pp_enrolled_on) AND YEAR(pp_enrolled_on)=YEAR(GETDATE()) " + GetRolesCommand("pp_facility", false) + " GROUP BY mnth_idnt, mnth_prefix, mnth_name ORDER BY mnth_idnt");
if (dr.HasRows)
{
while (dr.Read())
{
ds.MonthNames.Add(dr[0].ToString());
ds.MonthValue.Add(Convert.ToInt32(dr[1]));
}
}
conn = new SqlServerConnection();
dr = conn.SqlServerConnect("SELECT COUNT(*) FROM Facilities WHERE fc_void=0 " + GetRolesCommand("fc_idnt", false));
if (dr.Read())
{
ds.TBMUs = Convert.ToInt64(dr[0]);
}
DashboardStats stats = new DashboardStats();
conn = new SqlServerConnection();
dr = conn.SqlServerConnect("SELECT mnth_idnt, ISNULL(Pul,0)Pul, ISNULL(Ep,0)Ep, ISNULL(Bc,0)Bc, ISNULL(Cd,0)Cd, ISNULL(Hiv,0)Hiv FROM Months LEFT OUTER JOIN (SELECT DATEPART(QUARTER, pp_enrolled_on)QT, SUM(CASE WHEN pp_type=3 THEN 1 ELSE 0 END)Pul, SUM(CASE WHEN pp_type=4 THEN 1 ELSE 0 END)Ep, SUM(CASE WHEN pp_confirmation=6 THEN 1 ELSE 0 END)Bc, SUM(CASE WHEN pp_confirmation=7 THEN 1 ELSE 0 END)Cd, SUM(CASE WHEN pe_hiv_exam=36 THEN 1 ELSE 0 END)Hiv FROM PatientProgram LEFT OUTER JOIN PatientExamination ON pp_idnt=pe_program WHERE YEAR(pp_enrolled_on)=YEAR(GETDATE()) " + GetRolesCommand("pp_facility", false) + " GROUP BY DATEPART(QUARTER, pp_enrolled_on)) As Foo ON QT=mnth_idnt");
if (dr.HasRows)
{
while (dr.Read())
{
var stat = new DashboardStats
{
Month = Convert.ToInt64(dr[0]),
Pul = Convert.ToInt64(dr[1]),
Ep = Convert.ToInt64(dr[2]),
Bc = Convert.ToInt64(dr[3]),
Cd = Convert.ToInt64(dr[4]),
Hiv = Convert.ToInt64(dr[5])
};
stats.Pul += stat.Pul;
stats.Ep += stat.Ep;
stats.Bc += stat.Bc;
stats.Cd += stat.Cd;
stats.Hiv += stat.Hiv;
ds.Stats.Add(stat);
}
ds.Stats.Add(stats);
}
return(ds);
}
