public static DataTable GetItemManufacturerBalanceForDropDown(int id)
{
try
{
HealthFacility hf = HealthFacility.GetHealthFacilityById(id);
if (hf != null)
{
string query = @"Select '-----' as ""GTIN"", '------' as ""VALUE"", '------' as ""CODE"" UNION SELECT DISTINCT (""HEALTH_FACILITY_BALANCE"".""GTIN"" || ' - ' || ""ITEM"".""CODE"") as ""GTIN"", ""HEALTH_FACILITY_BALANCE"".""GTIN"" AS ""VALUE"", ""ITEM"".""CODE"" as ""CODE"" FROM ""ITEM_MANUFACTURER"" join ""HEALTH_FACILITY_BALANCE"" using (""GTIN"") join ""ITEM"" on ""ITEM_MANUFACTURER"".""ITEM_ID"" = ""ITEM"".""ID"" WHERE ""ITEM_MANUFACTURER"".""IS_ACTIVE"" = true AND ""HEALTH_FACILITY_CODE"" = @ParamValue and (""GTIN_PARENT"" = '' or ""GTIN_PARENT"" is null) ORDER BY ""CODE"" ;";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@ParamValue", DbType.String)
{
Value = hf.Code
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(dt);
}
return(null);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityEntity", "GetItemManufacturerBalanceForDropDown", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static int GetCountUserList(string username, int hfid)
{
try
{
string s = string.Empty;
HealthFacility hf = HealthFacility.GetHealthFacilityById(hfid);
string query = @"SELECT COUNT(*) FROM ""USER"" WHERE 1 = 1 "
+ @" AND ( UPPER(""USERNAME"") like '" + username + "%')";
if (!hf.TopLevel)
{
s = HealthFacility.GetAllChildsForOneHealthFacility(hfid);
//query += @" AND (( ""HEALTH_FACILITY_ID"" = ANY( CAST( string_to_array(" + hfid.ToString() + ", ',' ) AS INTEGER[] ))) )";
query += @" AND (( ""HEALTH_FACILITY_ID"" in (" + s + ")))";
}
//List<NpgsqlParameter> parameters = new List<NpgsqlParameter>()
//{
// new NpgsqlParameter("@Username", DbType.String) { Value = ("%" + username + "%") },
// new NpgsqlParameter("@HealthFacilityId", DbType.String) { Value = hfid }
//};
object count = DBManager.ExecuteScalarCommand(query, CommandType.Text, null);
return(int.Parse(count.ToString()));
}
catch (Exception ex)
{
Log.InsertEntity("User", "GetCountUserList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static HealthFacility GetHealthFacilityAsObject(DataTable dt)
{
foreach (DataRow row in dt.Rows)
{
try
{
HealthFacility o = new HealthFacility();
o.Id = Helper.ConvertToInt(row["ID"]);
o.Name = row["NAME"].ToString();
o.Code = row["CODE"].ToString();
o.ParentId = Helper.ConvertToInt(row["PARENT_ID"]);
// o.TopLevel = Helper.ConvertToBoolean(row["TOP_LEVEL"]);
o.Leaf = Helper.ConvertToBoolean(row["LEAF"]);
o.Notes = row["NOTES"].ToString();
o.IsActive = Helper.ConvertToBoolean(row["IS_ACTIVE"]);
o.ModifiedOn = Helper.ConvertToDate(row["MODIFIED_ON"]);
o.ModifiedBy = Helper.ConvertToInt(row["MODIFIED_BY"]);
o.VaccinationPoint = Helper.ConvertToBoolean(row["VACCINATION_POINT"]);
o.Address = row["ADDRESS"].ToString();
o.VaccineStore = Helper.ConvertToBoolean(row["VACCINE_STORE"]);
o.Contact = row["CONTACT"].ToString();
o.ColdStorageCapacity = Helper.ConvertToDecimal(row["COLD_STORAGE_CAPACITY"]);
o.TypeId = Helper.ConvertToInt(row["TYPE_ID"]);
o.Ownership = Helper.ConvertToInt(row["OWNERSHIP"]);
o.Lowcode = Helper.ConvertToInt(row["LOWCODE"]);
return(o);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacility", "GetHealthFacilityAsObject", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
return(null);
}
public static List <HealthFacilityBalance> GetHealthFacilityBalanceByHealthFacility(int id)
{
try
{
HealthFacility hf = HealthFacility.GetHealthFacilityById(id);
if (hf != null)
{
string query = @"SELECT * FROM ""HEALTH_FACILITY_BALANCE"" join ""ITEM_MANUFACTURER"" using (""GTIN"") join ""ITEM"" on ""ITEM_ID"" = ""ITEM"".""ID"" join ""ITEM_LOT"" using (""GTIN"", ""LOT_NUMBER"") WHERE ""HEALTH_FACILITY_CODE"" = @ParamValue and ""ITEM_MANUFACTURER"".""IS_ACTIVE"" = true and ""ITEM_LOT"".""IS_ACTIVE"" = true ORDER BY ""ITEM"".""CODE"" ";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@ParamValue", DbType.String)
{
Value = hf.Code
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetHealthFacilityBalanceAsList(dt));
}
return(null);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityBalance", "GetHealthFacilityBalanceByHealthFacility", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static int GetCountHealthFacilityBalanceByHealthFacility(int id)
{
try
{
HealthFacility hf = HealthFacility.GetHealthFacilityById(id);
if (hf != null)
{
string query = @"SELECT count(*) FROM ""HEALTH_FACILITY_BALANCE"" WHERE ""HEALTH_FACILITY_CODE"" = @ParamValue ";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@ParamValue", DbType.String)
{
Value = hf.Code
}
};
object count = DBManager.ExecuteScalarCommand(query, CommandType.Text, parameters);
return(int.Parse(count.ToString()));
}
return(0);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityBalance", "GetHealthFacilityBalanceByHealthFacility", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public DataTable GetChartData(int id)
{
try
{
HealthFacility hf = HealthFacility.GetHealthFacilityById(id);
if (hf != null)
{
string query = @"SELECT VHFB.""NAME"", sum ""BALANCE"", ""SAFETY_STOCK"" FROM ""V_HEALTH_FACILITY_BALANCE_HELPER"" VHFB
LEFT JOIN ""V_GTIN_HF_STOCK_POLICY_HELPER"" VSP ON VHFB.""NAME"" = VSP.""NAME""
WHERE VHFB.""HEALTH_FACILITY_CODE"" = @hfCode AND
VSP.""HEALTH_FACILITY_CODE"" = @hfCode
order by 1 asc";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@hfCode", DbType.String)
{
Value = hf.Code
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(dt);
}
return(null);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityBalance", "GetHealthFacilityBalanceForList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static DataTable GetHealthFacilityBalanceForList(int id)
{
try
{
HealthFacility hf = HealthFacility.GetHealthFacilityById(id);
if (hf != null)
{
string query = @"Select '-----' as ""GTIN"" UNION SELECT DISTINCT ""GTIN"" FROM ""HEALTH_FACILITY_BALANCE"" join ""ITEM_MANUFACTURER"" using (""GTIN"")
WHERE ""HEALTH_FACILITY_CODE"" = @ParamValue and ""ITEM_MANUFACTURER"".""IS_ACTIVE"" = true ";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@ParamValue", DbType.String)
{
Value = hf.Code
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(dt);
}
return(null);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityBalance", "GetHealthFacilityBalanceForList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static DataTable GetRegister(int healthFacilityId, string firstname, string lastname, int year, int languageId)
{
try
{
string funcQueryHelper = string.Format("SELECT create_v_register();");
DataTable dtfHelper = DBManager.ExecuteReaderCommand(funcQueryHelper, CommandType.Text, null);
string funcQuery = string.Format("select get_register_query({0});", languageId);
DataTable dtf = DBManager.ExecuteReaderCommand(funcQuery, CommandType.Text, null);
string s = HealthFacility.GetAllChildsForOneHealthFacility(healthFacilityId);
string query = "";
if (dtf.Rows.Count != 0)
{
query = dtf.Rows[0][0].ToString();
query = @"{0} Where 1=1 "
+ @" AND (( ""ADMINISTRATION_ID"" = ANY( CAST( string_to_array(@AdministrationId, ',' ) AS INTEGER[] ))) or @AdministrationId = '')"
+ @" AND ( UPPER(""FIRSTNAME"") LIKE @Firstname or @Firstname is null or @Firstname = '%%' )"
+ @" AND ( UPPER(""LASTNAME"") LIKE @Lastname or @Lastname is null or @Lastname = '%%' )"
+ @" AND EXTRACT(YEAR FROM to_date(""BIRTHDATE"", get_date_format())) = @Year OR @Year is null or @Year = 0 "
+ ";";
}
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@AdministrationId", DbType.String)
{
Value = s
},
new NpgsqlParameter("@Firstname", DbType.String)
{
Value = firstname.Replace("'", @"''").ToUpper()
},
new NpgsqlParameter("@Lastname", DbType.String)
{
Value = lastname.Replace("'", @"''").ToUpper()
},
new NpgsqlParameter("@Year", DbType.Int32)
{
Value = year
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(dt);
}
catch (Exception ex)
{
Log.InsertEntity("Register", "GetRegister", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
return(null);
}
}
public static List <Child> GetDuplications(bool birthdateFlag, bool firstnameFlag, bool genderFlag, int healthFacilityId)
{
try
{
if (birthdateFlag || firstnameFlag || genderFlag)
{
string query = String.Format(@"SELECT DISTINCT C1.* from ""CHILD"" C1, ""CHILD"" C2 where C1.""ID"" <> C2.""ID"" AND
C1.""STATUS_ID"" = 1 AND C2.""STATUS_ID"" = 1 ");
query += String.Format(@" AND C1.""LASTNAME1"" = C2.""LASTNAME1"" ");
if (firstnameFlag)
{
query += String.Format(@" AND C1.""FIRSTNAME1"" = C2.""FIRSTNAME1"" ");
}
if (birthdateFlag)
{
query += String.Format(@" AND C1.""BIRTHDATE"" = C2.""BIRTHDATE"" ");
}
if (genderFlag)
{
query += String.Format(@" AND C1.""GENDER"" = C2.""GENDER"" ");
}
HealthFacility hf = HealthFacility.GetHealthFacilityById(healthFacilityId);
string s = "";
if (!hf.TopLevel)
{
s = HealthFacility.GetAllChildsForOneHealthFacility(hf.Id);
query += string.Format(@" AND (C1.""HEALTHCENTER_ID"" in (" + s + @") OR C2.""HEALTHCENTER_ID"" in (" + s + ")) ");
}
query += String.Format(@" ORDER BY C1.""LASTNAME1"" ");
// List<NpgsqlParameter> parameters = new List<NpgsqlParameter>()
//{
// new NpgsqlParameter("@HealthCenterId", DbType.String) { Value = s }
//};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
return(GetChildAsList(dt));
}
}
catch (Exception ex)
{
Log.InsertEntity("Child", "GetDuplications", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
return(null);
}
public static DataTable GetPlaceListbyHealthFacility(Int32 hfId)
{
try
{
string str = HealthFacility.GetAllChildsForOneHealthFacility(hfId);
string query = @"SELECT -1 as ""ID"", '-----' as ""NAME"" UNION SELECT ""ID"", ""NAME"" FROM ""PLACE"" WHERE ""HEALTH_FACILITY_ID"" in ( " + str + @") ORDER BY ""NAME"" ";
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
return(dt); // GetPlaceAsList(dt);
}
catch (Exception ex)
{
Log.InsertEntity("Place", "GetPlaceListbyHealthFacility", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
private static void recursiveHealthFacility(int id)
{
// s += id + ", ";
if (!HealthFacility.GetHealthFacilityById(id).Leaf)
{
List <HealthFacility> list = HealthFacility.GetHealthFacilityByParentId(id);
foreach (HealthFacility hf in list)
{
s += hf.Id + ", ";
if (!hf.Leaf)
{
recursiveHealthFacility(hf.Id);
}
}
}
}
public static List <TransactionLines> GetLotTracking(ref int maximumRows, ref int startRowIndex, int hfId, int itemlotid)
{
string s = HealthFacility.GetAllChildsForOneHealthFacility(hfId);
try
{
string query = String.Format(@"SELECT DISTINCT ""TRANSACTION_LINES"".""ID"", ""TRANSACTION_LINES"".""TRANSACTION_ID"", ""TRANSACTION_LINES"".""ITEM_LOT_ID"", ""TRANSACTION_LINES"".""QUANTITY"", ""TRANSACTION_LINES"".""IS_ACTIVE"", ""TRANSACTION_LINES"".""ADJUSTMENT_ID"", ""TRANSACTION_LINES"".""NOTES"", ""TRANSACTION_DATE"" AS ""MODIFIED_ON"", ""TRANSACTION_LINES"".""MODIFIED_BY"", ""SENDER_ID""
FROM ""TRANSACTION"" inner join ""TRANSACTION_LINES"" on ""TRANSACTION"".""ID"" = ""TRANSACTION_LINES"".""TRANSACTION_ID"" inner join ""HEALTH_FACILITY"" ON (""TRANSACTION"".""SENDER_ID"" = ""HEALTH_FACILITY"".""ID"" OR ""TRANSACTION"".""RECEIVER_ID"" = ""HEALTH_FACILITY"".""ID"")
WHERE ""TRANSACTION_LINES"".""IS_ACTIVE"" = true AND ""TRANSACTION_TYPE_ID"" in (1,2) AND ""ITEM_LOT_ID"" = {1} AND (""SENDER_ID"" in ({0}) OR ""RECEIVER_ID"" in ({0})) ORDER BY ""TRANSACTION_DATE"", ""SENDER_ID"" OFFSET {2} LIMIT {3} ;", s, itemlotid, startRowIndex, maximumRows);
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
return(GetTransactionLinesAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("TransactionLines", "GetPagedTransactionLinesList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static List <User> GetPagedUserList(string username, int hfid, ref int maximumRows, ref int startRowIndex)
{
try
{
//string wsearch = txtUsername.Text.Replace("'", @"''");
//string where;
//int hcId = CurrentEnvironment.LoggedUser.HealthFacilityId;
//string s = HealthFacility.GetAllChildsForOneHealthFacility(hcId);
//where = @" UPPER(""USERNAME"") like '%" + wsearch.ToUpper() + "%'";
//where += string.Format(@" AND ""HEALTH_FACILITY_ID"" in ({0}) ", s);
//string query = @"SELECT * FROM ""USER"" WHERE " + where + @" ORDER BY ""USERNAME"" OFFSET " + startRowIndex + " LIMIT " + maximumRows + ";";
string s = string.Empty;
HealthFacility hf = HealthFacility.GetHealthFacilityById(hfid);
string query = @"SELECT * FROM ""USER"" WHERE 1 = 1 "
+ @" AND ( UPPER(""USERNAME"") like '" + username + "%')";
if (!hf.TopLevel)
{
s = HealthFacility.GetAllChildsForOneHealthFacility(hfid);
//query += @" AND (( ""HEALTH_FACILITY_ID"" = ANY( CAST( string_to_array(" + s + ", ',' ) AS INTEGER[] ))) )";
query += @" AND (( ""HEALTH_FACILITY_ID"" in (" + s + ")))";
}
query += @" ORDER BY ""USERNAME"" OFFSET " + startRowIndex + " LIMIT " + maximumRows;
//List<NpgsqlParameter> parameters = new List<NpgsqlParameter>()
//{
// new NpgsqlParameter("@Username", DbType.String) { Value = "%" + username + "%" },
// new NpgsqlParameter("@HealthFacilityId", DbType.String) { Value = hfid.ToString() },
// new NpgsqlParameter("@MaximumRows", DbType.Int32) { Value = maximumRows },
// new NpgsqlParameter("@StartRowIndex", DbType.Int32) { Value = startRowIndex }
//};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
return(GetUserAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("User", "GetPagedUserList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static int Update(HealthFacility o)
{
try
{
string query = @"UPDATE ""HEALTH_FACILITY"" SET ""ID"" = @Id, ""NAME"" = @Name, ""CODE"" = @Code, ""PARENT_ID"" = @ParentId, ""TOP_LEVEL"" = @TopLevel, ""LEAF"" = @Leaf, ""NOTES"" = @Notes, ""IS_ACTIVE"" = @IsActive, ""MODIFIED_ON"" = @ModifiedOn, ""MODIFIED_BY"" = @ModifiedBy, ""VACCINATION_POINT"" = @VaccinationPoint, ""ADDRESS"" = @Address, ""VACCINE_STORE"" = @VaccineStore, ""CONTACT"" = @Contact, ""COLD_STORAGE_CAPACITY"" = @ColdStorageCapacity, ""TYPE_ID"" = @TypeId, ""OWNERSHIP"" = @Ownership, ""LOWCODE"" = @Lowcode WHERE ""ID"" = @Id ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Name", DbType.String)
{
Value = o.Name
},
new NpgsqlParameter("@Code", DbType.String)
{
Value = o.Code
},
new NpgsqlParameter("@ParentId", DbType.Int32)
{
Value = o.ParentId
},
new NpgsqlParameter("@TopLevel", DbType.Boolean)
{
Value = o.TopLevel
},
new NpgsqlParameter("@Leaf", DbType.Boolean)
{
Value = o.Leaf
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? 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("@VaccinationPoint", DbType.Boolean)
{
Value = o.VaccinationPoint
},
new NpgsqlParameter("@Address", DbType.String)
{
Value = (object)o.Address ?? DBNull.Value
},
new NpgsqlParameter("@VaccineStore", DbType.Boolean)
{
Value = o.VaccineStore
},
new NpgsqlParameter("@Contact", DbType.String)
{
Value = (object)o.Contact ?? DBNull.Value
},
new NpgsqlParameter("@ColdStorageCapacity", DbType.Double)
{
Value = (object)o.ColdStorageCapacity ?? DBNull.Value
},
new NpgsqlParameter("@TypeId", DbType.Int32)
{
Value = (object)o.TypeId ?? DBNull.Value
},
new NpgsqlParameter("@Ownership", DbType.Int32)
{
Value = (object)o.Ownership ?? DBNull.Value
},
new NpgsqlParameter("@Lowcode", DbType.Int32)
{
Value = (object)o.Lowcode ?? DBNull.Value
},
new NpgsqlParameter("@Id", DbType.Int32)
{
Value = o.Id
}
};
int rowAffected = DBManager.ExecuteNonQueryCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("HealthFacility", o.Id.ToString(), 2, DateTime.Now, o.ModifiedBy);
return(rowAffected);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacility", "Update", 2, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}
public static int Insert(HealthFacility o)
{
try
{
string query = @"INSERT INTO ""HEALTH_FACILITY"" (""NAME"", ""CODE"", ""PARENT_ID"", ""TOP_LEVEL"", ""LEAF"", ""NOTES"", ""IS_ACTIVE"", ""MODIFIED_ON"", ""MODIFIED_BY"", ""VACCINATION_POINT"", ""ADDRESS"", ""VACCINE_STORE"", ""CONTACT"", ""COLD_STORAGE_CAPACITY"", ""TYPE_ID"", ""OWNERSHIP"", ""LOWCODE"") VALUES (@Name, @Code, @ParentId, @TopLevel, @Leaf, @Notes, @IsActive, @ModifiedOn, @ModifiedBy, @VaccinationPoint, @Address, @VaccineStore, @Contact, @ColdStorageCapacity, @TypeId, @Ownership, @Lowcode) returning ""ID"" ";
List <Npgsql.NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@Name", DbType.String)
{
Value = o.Name
},
new NpgsqlParameter("@Code", DbType.String)
{
Value = o.Code
},
new NpgsqlParameter("@ParentId", DbType.Int32)
{
Value = o.ParentId
},
new NpgsqlParameter("@TopLevel", DbType.Boolean)
{
Value = o.TopLevel
},
new NpgsqlParameter("@Leaf", DbType.Boolean)
{
Value = o.Leaf
},
new NpgsqlParameter("@Notes", DbType.String)
{
Value = (object)o.Notes ?? 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("@VaccinationPoint", DbType.Boolean)
{
Value = o.VaccinationPoint
},
new NpgsqlParameter("@Address", DbType.String)
{
Value = (object)o.Address ?? DBNull.Value
},
new NpgsqlParameter("@VaccineStore", DbType.Boolean)
{
Value = o.VaccineStore
},
new NpgsqlParameter("@Contact", DbType.String)
{
Value = (object)o.Contact ?? DBNull.Value
},
new NpgsqlParameter("@ColdStorageCapacity", DbType.Double)
{
Value = (object)o.ColdStorageCapacity ?? DBNull.Value
},
new NpgsqlParameter("@TypeId", DbType.Int32)
{
Value = (object)o.TypeId ?? DBNull.Value
},
new NpgsqlParameter("@Ownership", DbType.Int32)
{
Value = (object)o.Ownership ?? DBNull.Value
},
new NpgsqlParameter("@Lowcode", DbType.Int32)
{
Value = (object)o.Lowcode ?? DBNull.Value
}
};
object id = DBManager.ExecuteScalarCommand(query, CommandType.Text, parameters);
AuditTable.InsertEntity("HealthFacility", id.ToString(), 1, DateTime.Now, o.ModifiedBy);
return(int.Parse(id.ToString()));
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacility", "Insert", 1, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
}
return(-1);
}
public DataTable GetCoverageChart(int hfId)
{
try
{
string s = HealthFacility.GetAllChildsForOneHealthFacility(hfId);
if (!string.IsNullOrEmpty(s))
{
string query = @"with tmp as
(
SELECT SV.""NAME"" as ""Name"", date_part('month', ""SCHEDULED_DATE"") as ""Month"", date_part('year', ""SCHEDULED_DATE"") ""Year"", COUNT(*) as ""Count"", 'Scheduled' as ""Type""
FROM ""VACCINATION_EVENT"" VE
INNER JOIN ""DOSE"" D ON VE.""DOSE_ID"" = D.""ID""
INNER JOIN ""SCHEDULED_VACCINATION"" SV ON SV.""ID"" = D.""SCHEDULED_VACCINATION_ID""
WHERE ""SCHEDULED_DATE"" BETWEEN (date_trunc('month', now()) - interval '3 month') AND (date_trunc('month', now())::date - 1)
AND ""HEALTH_FACILITY_ID"" IN (" + s + @")
GROUP BY SV.""NAME"", date_part('month', ""SCHEDULED_DATE""), date_part('year', ""SCHEDULED_DATE"")
union all
SELECT SV.""NAME"", date_part('month', ""VACCINATION_DATE""), date_part('year', ""VACCINATION_DATE""), COUNT(*), 'Done'
FROM ""VACCINATION_EVENT"" VE
INNER JOIN ""DOSE"" D ON VE.""DOSE_ID"" = D.""ID""
INNER JOIN ""SCHEDULED_VACCINATION"" SV ON SV.""ID"" = D.""SCHEDULED_VACCINATION_ID""
WHERE ""VACCINATION_DATE"" BETWEEN (date_trunc('month', now()) - interval '3 month') AND (date_trunc('month', now())::date - 1)
AND ""VACCINATION_STATUS"" = true
AND ""HEALTH_FACILITY_ID"" IN (" + s + @")
GROUP BY SV.""NAME"", date_part('month', ""VACCINATION_DATE""), date_part('year', ""VACCINATION_DATE"")
)
select t1.""Name"", t1.""Month"", t1.""Year"", trunc((t2.""Count"" / t1.""Count""::float * 100)::numeric, 2) as ""Percentage""
from tmp t1 left outer join tmp t2
on t1.""Name"" = t2.""Name"" and t1.""Month"" = t2.""Month"" and t1.""Year"" = t2.""Year"" and t1.""Type"" <> t2.""Type""
where t1.""Type"" = 'Scheduled'
order by t1.""Name"", t1.""Year"", t1.""Month""; ";
//List<NpgsqlParameter> parameters = new List<NpgsqlParameter>()
//{
// new NpgsqlParameter("@hfid_1", DbType.String) { Value = s },
// new NpgsqlParameter("@hfid_2", DbType.String) { Value = s }
//};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, null);
return(dt);
}
return(null);
}
catch (Exception ex)
{
Log.InsertEntity("HealthFacilityBalance", "GetHealthFacilityBalanceForList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
