public static List <NonvaccinationReason> GetNonvaccinationReasonAsList(DataTable dt)
{
List <NonvaccinationReason> oList = new List <NonvaccinationReason>();
foreach (DataRow row in dt.Rows)
{
try
{
NonvaccinationReason o = new NonvaccinationReason();
o.Id = Helper.ConvertToInt(row["ID"]);
o.Name = row["NAME"].ToString();
o.Code = row["CODE"].ToString();
o.IsActive = Helper.ConvertToBoolean(row["IS_ACTIVE"]);
o.ModifiedOn = Helper.ConvertToDate(row["MODIFIED_ON"]);
o.ModifiedBy = Helper.ConvertToInt(row["MODIFIED_BY"]);
o.Notes = row["NOTES"].ToString();
o.KeepChildDue = Helper.ConvertToBoolean(row["KEEP_CHILD_DUE"]);
oList.Add(o);
}
catch (Exception ex)
{
Log.InsertEntity("NonvaccinationReason", "GetNonvaccinationReasonAsList", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
return(oList);
}
public static int Update(NonvaccinationReason o)
{
try
{
string query = @"UPDATE ""NONVACCINATION_REASON"" SET ""ID"" = @Id, ""NAME"" = @Name, ""CODE"" = @Code, ""IS_ACTIVE"" = @IsActive, ""MODIFIED_ON"" = @ModifiedOn, ""MODIFIED_BY"" = @ModifiedBy, ""NOTES"" = @Notes, ""KEEP_CHILD_DUE"" = @KeepChildDue WHERE ""ID"" = @Id ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Name", DbType.String)
{
Value = o.Name
},
new NpgsqlParameter("@Code", DbType.String)
{
Value = (object)o.Code ?? DBNull.Value
},
new NpgsqlParameter("@IsActive", DbType.Boolean)
{
Value = o.IsActive
},
new NpgsqlParameter("@ModifiedOn", DbType.DateTime)
{
Value = o.ModifiedOn
},
new NpgsqlParameter("@ModifiedBy", DbType.Int32)
{
Value = o.ModifiedBy
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? DBNull.Value
},
new NpgsqlParameter("@KeepChildDue", DbType.Boolean)
{
Value = o.KeepChildDue
},
new NpgsqlParameter("@Id", DbType.Int32)
{
Value = o.Id
}
};
int rowAffected = DBManager.ExecuteNonQueryCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("NonvaccinationReason", o.Id.ToString(), 2, DateTime.Now, o.ModifiedBy);
return(rowAffected);
}
catch (Exception ex)
{
Log.InsertEntity("NonvaccinationReason", "Update", 2, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}
public static int Insert(NonvaccinationReason o)
{
try
{
string query = @"INSERT INTO ""NONVACCINATION_REASON"" (""NAME"", ""CODE"", ""IS_ACTIVE"", ""MODIFIED_ON"", ""MODIFIED_BY"", ""NOTES"", ""KEEP_CHILD_DUE"") VALUES (@Name, @Code, @IsActive, @ModifiedOn, @ModifiedBy, @Notes, @KeepChildDue) returning ""ID"" ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Name", DbType.String)
{
Value = o.Name
},
new NpgsqlParameter("@Code", DbType.String)
{
Value = (object)o.Code ?? DBNull.Value
},
new NpgsqlParameter("@IsActive", DbType.Boolean)
{
Value = o.IsActive
},
new NpgsqlParameter("@ModifiedOn", DbType.DateTime)
{
Value = o.ModifiedOn
},
new NpgsqlParameter("@ModifiedBy", DbType.Int32)
{
Value = o.ModifiedBy
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? DBNull.Value
},
new NpgsqlParameter("@KeepChildDue", DbType.Boolean)
{
Value = o.KeepChildDue
}
};
object id = DBManager.ExecuteScalarCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("NonvaccinationReason", id.ToString(), 1, DateTime.Now, o.ModifiedBy);
return(int.Parse(id.ToString()));
}
catch (Exception ex)
{
Log.InsertEntity("NonvaccinationReason", "Insert", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}
public static DataTable GetCohortCoverage(int languageId, string where, DateTime firstDate, DateTime today, DateTime enddDate, int vaccineId, int community, int domicile)
{
try
{
string helper = "";
List <NonvaccinationReason> nvrList = NonvaccinationReason.GetNonvaccinationReasonList();
foreach (NonvaccinationReason nvr in nvrList)
{
string s = string.Format(@",( -- non vaccinated for a reason
Select Count(*) from ""VACCINATION_EVENT"", ""CHILD""
where ""VACCINATION_EVENT"".""CHILD_ID"" = ""CHILD"".""ID"" AND ""DOSE_ID"" = v_parent_rec.""ID""
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date) AND ""VACCINATION_DATE"" between cast(firstdate as date) and cast(enddate as date)
AND ""VACCINATION_EVENT"".""VACCINATION_STATUS"" = 'False' AND ""VACCINATION_EVENT"".""NONVACCINATION_REASON_ID"" = {0} AND ""CHILD"".""STATUS_ID"" = 1
AND ""HEALTHCENTER_ID"" in ({1})
)" , nvr.Id, where);
if (where == "1")
{
s = string.Format(@",( -- non vaccinated for a reason
Select Count(*) from ""VACCINATION_EVENT"", ""CHILD""
where ""VACCINATION_EVENT"".""CHILD_ID"" = ""CHILD"".""ID"" AND ""DOSE_ID"" = v_parent_rec.""ID""
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date) AND ""VACCINATION_DATE"" between cast(firstdate as date) and cast(enddate as date)
AND ""VACCINATION_EVENT"".""VACCINATION_STATUS"" = 'False' AND ""VACCINATION_EVENT"".""NONVACCINATION_REASON_ID"" = {0} AND ""CHILD"".""STATUS_ID"" = 1
)" , nvr.Id);
}
helper += (s + Environment.NewLine);
}
string optwhere = String.Empty;
if (vaccineId != -1)
{
optwhere = String.Format(@" AND ""DOSE"".""ID"" = {0}", vaccineId);
}
string conwhere = string.Empty;
if (community != 0)
{
conwhere = String.Format(@" AND ""COMMUNITY_ID"" = {0}", community);
}
if (domicile != 0)
{
conwhere += String.Format(@" AND ""DOMICILE_ID"" = {0}", domicile);
}
StringBuilder sb = new StringBuilder();
sb.AppendLine(string.Format(@"CREATE OR REPLACE FUNCTION cohort_helper(optwhere text, firstdate text, todate text, enddate text) RETURNS void AS
$BODY$
DECLARE
v_parent_rec RECORD;
BEGIN
FOR v_parent_rec IN SELECT ""DOSE"".* FROM ""DOSE"" join ""SCHEDULED_VACCINATION"" on ""DOSE"".""SCHEDULED_VACCINATION_ID"" = ""SCHEDULED_VACCINATION"".""ID""
WHERE ""ENTRY_DATE"" <= cast(todate as date) AND (""EXIT_DATE"" >= cast(todate as date) OR ""EXIT_DATE"" is null OR ""EXIT_DATE"" = '0001-01-01')
{1} ORDER BY ""ID""
LOOP
INSERT INTO ""HELPER""
select
(
(
select ""ID"" FROM ""DOSE"" WHERE ""ID"" = v_parent_rec.""ID""
), (
select ""FULLNAME"" FROM ""DOSE"" WHERE ""ID"" = v_parent_rec.""ID""
), ( -- cohort
Select Count(*) from ""CHILD""
Where ""CHILD"".""STATUS_ID"" = 1
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date)
AND ""HEALTHCENTER_ID"" in ({2}) {3}
) ,( -- immunized
Select Count(*) from ""VACCINATION_EVENT"", ""CHILD""
where ""VACCINATION_EVENT"".""CHILD_ID"" = ""CHILD"".""ID"" AND ""DOSE_ID"" = v_parent_rec.""ID""
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date) AND ""VACCINATION_DATE"" between cast(firstdate as date) and cast(enddate as date)
AND ""VACCINATION_EVENT"".""VACCINATION_STATUS"" = 'True' AND ""VACCINATION_EVENT"".""NONVACCINATION_REASON_ID"" = 0 AND ""CHILD"".""STATUS_ID"" = 1
AND ""HEALTHCENTER_ID"" in ({2}) {3}
)
{0}
);
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
", helper, optwhere, where, conwhere));
if (where == "1")
{
sb.Clear();
sb.AppendLine(string.Format(@"CREATE OR REPLACE FUNCTION cohort_helper(optwhere text, firstdate text, todate text, enddate text) RETURNS void AS
$BODY$
DECLARE
v_parent_rec RECORD;
BEGIN
FOR v_parent_rec IN SELECT ""DOSE"".* FROM ""DOSE"" join ""SCHEDULED_VACCINATION"" on ""DOSE"".""SCHEDULED_VACCINATION_ID"" = ""SCHEDULED_VACCINATION"".""ID""
WHERE ""ENTRY_DATE"" <= cast(todate as date) AND (""EXIT_DATE"" >= cast(todate as date) OR ""EXIT_DATE"" is null OR ""EXIT_DATE"" = '0001-01-01')
{1} ORDER BY ""ID""
LOOP
INSERT INTO ""HELPER""
select
(
(
select ""ID"" FROM ""DOSE"" WHERE ""ID"" = v_parent_rec.""ID""
), (
select ""FULLNAME"" FROM ""DOSE"" WHERE ""ID"" = v_parent_rec.""ID""
), ( -- cohort
Select Count(*) from ""CHILD""
Where ""CHILD"".""STATUS_ID"" = 1
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date)
{2}
) ,( -- immunized
Select Count(*) from ""VACCINATION_EVENT"", ""CHILD""
where ""VACCINATION_EVENT"".""CHILD_ID"" = ""CHILD"".""ID"" AND ""DOSE_ID"" = v_parent_rec.""ID""
AND ""BIRTHDATE"" between cast(firstdate as date) and cast(todate as date) AND ""VACCINATION_DATE"" between cast(firstdate as date) and cast(enddate as date)
AND ""VACCINATION_EVENT"".""VACCINATION_STATUS"" = 'True' AND ""VACCINATION_EVENT"".""NONVACCINATION_REASON_ID"" = 0 AND ""CHILD"".""STATUS_ID"" = 1
{2}
)
{0}
);
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
", helper, optwhere, conwhere));
}
DBManager.ExecuteNonQueryCommand(sb.ToString(), CommandType.Text, null);
string truncateQuery = string.Format(@"TRUNCATE ""HELPER"";");
DBManager.ExecuteNonQueryCommand(truncateQuery, CommandType.Text, null);
string funcQueryHelper = string.Format("SELECT cohort_helper('{0}', '{1}', '{2}', '{3}');", " ", firstDate.ToString("yyyy-MM-dd"), today.ToString("yyyy-MM-dd"), enddDate.ToString("yyyy-MM-dd"));
DataTable dtfHelper = DBManager.ExecuteReaderCommand(funcQueryHelper, CommandType.Text, null);
string funcQuery = string.Format(@"select * from ""HELPER"";");
DataTable dtf = DBManager.ExecuteReaderCommand(funcQuery, CommandType.Text, null);
return(dtf);
//string query = "";
//if (dtf.Rows.Count != 0)
//{
// query = string.Format("{1} Where 1=1 {0};", where, dtf.Rows[0][0].ToString());
//}
//DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
//return dt;
}
catch (Exception ex)
{
Log.InsertEntity("CohortCoverage", "GetCohortCoverage", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
return(null);
}
}