public static List <VaccinationAppointment> GetVaccinationAppointmentsByChild(DateTime modifiedOn, int userId)
{
try
{
User user = User.GetUserById(userId);
//string query = @"SELECT VA.* FROM ""VACCINATION_APPOINTMENT"" VA WHERE VA.""CHILD_ID"" = @ChildId AND VA.""MODIFIEDON"" >= @ModifiedOn AND VA.""MODIFIED_BY"" <> @UserId ;";
string query = @"SELECT VA.* FROM ""VACCINATION_APPOINTMENT"" VA WHERE VA.""CHILD_ID"" in (Select ""ID"" from ""CHILD"" where ""HEALTHCENTER_ID"" = @hfId ) AND VA.""MODIFIEDON"" >= @ModifiedOn AND VA.""MODIFIED_BY"" <> @UserId;";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
//new NpgsqlParameter("@ChildId", DbType.Int32) { Value = childId },
new NpgsqlParameter("@ModifiedOn", DbType.DateTime)
{
Value = modifiedOn
},
new NpgsqlParameter("@hfId", DbType.Int32)
{
Value = user.HealthFacilityId.ToString()
},
new NpgsqlParameter("@UserId", DbType.Int32)
{
Value = userId
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetVaccinationAppointmentAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("VaccinationAppointment", "GetVaccinationAppointmentsByChild", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static List <Place> GetPlacesByHealthFacilityIdSinceLastLogin(int userId)
{
try
{
User user = User.GetUserById(userId);
string query = string.Format(@"select P.* from ""PLACE"" P
WHERE P.""HEALTH_FACILITY_ID"" = @hfId
AND P.""MODIFIED_ON"" <= @lastlogin ");
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@hfId", DbType.Int32)
{
Value = user.HealthFacilityId.ToString()
},
new NpgsqlParameter("@lastlogin", DbType.DateTime)
{
Value = user.Lastlogin
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetPlaceAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("Child", "GetPlacesByHealthFacilityIdSinceLastLogin", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static List <Child> GetChildByHealthFacilityIdDayFirstLogin(int userId)
{
try
{
User user = User.GetUserById(userId);
// string query = string.Format(@"select distinct C.* from ""CHILD"" C join ""VACCINATION_EVENT"" V on V.""CHILD_ID"" = C.""ID""
// WHERE C.""HEALTHCENTER_ID"" = @hfId
// AND (C.""MODIFIED_ON""::date = @lastlogin::date OR ""MODIFIEDON""::date = @lastlogin::date) ");
string query = string.Format(@"select C.* from ""CHILD"" C
WHERE C.""HEALTHCENTER_ID"" = @hfId
AND C.""MODIFIED_ON""::date = @lastlogin::date ");
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@hfId", DbType.Int32)
{
Value = user.HealthFacilityId.ToString()
},
new NpgsqlParameter("@lastlogin", DbType.DateTime)
{
Value = user.Lastlogin.AddDays(-1)
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetChildAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("Child", "GetChildByHealthFacilityIdBeforeLastLogin", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
//GetVaccinationQueue(_date, _userId) - merr te dhenat nga tabela "Vaccination Queue"
//ku date = _date dhe healthfacilityId = hf_id te userit dhe userId <> _userId
public static List <VaccinationQueue> GetVaccinationQueueByDateAndUser(DateTime date, int userId)
{
try
{
User user = User.GetUserById(userId);
string query = @"select V.* from ""VACCINATION_QUEUE"" V
WHERE V.""HEALTH_FACILITY_ID"" = @hfId
AND CAST(V.""DATE"" as date) = @Date
AND V.""USER_ID"" <> @UserId";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@UserId", DbType.Int32)
{
Value = userId
},
new NpgsqlParameter("@hfId", DbType.Int32)
{
Value = user.HealthFacilityId
},
new NpgsqlParameter("@Date", DbType.Date)
{
Value = date
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetVaccinationQueueAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("VaccinationQueue", "GetVaccinationQueueByDateAndUser", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}
public static List <Child> GetChildByIdList(string childIdList, int userId)
{
//tring[] childList = childIdList.Split(',');
try
{
User user = User.GetUserById(userId);
// string query = string.Format(@"select distinct C.* from ""CHILD"" C join ""VACCINATION_EVENT"" V on V.""CHILD_ID"" = C.""ID""
// WHERE C.""ID"" = ANY( CAST( string_to_array(@ChildList, ',' ) AS INTEGER[] ))
// AND ((C.""MODIFIED_ON"" <= @lastlogin AND C.""MODIFIED_ON"" > @PrevLogin
// AND C.""MODIFIED_BY"" <> @UserId)
// OR (""MODIFIEDON"" < @lastlogin AND ""MODIFIEDON"" > @PrevLogin AND V.""MODIFIED_BY"" <> @UserId)) ");
string query = string.Format(@"select distinct C.* from ""CHILD"" C
WHERE C.""ID"" = ANY( CAST( string_to_array(@ChildList, ',' ) AS INTEGER[] ))
AND (C.""MODIFIED_ON"" <= @lastlogin AND C.""MODIFIED_ON"" > @PrevLogin
AND C.""MODIFIED_BY"" <> @UserId) ");
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter("@UserId", DbType.Int32)
{
Value = userId.ToString()
},
new NpgsqlParameter("@lastlogin", DbType.DateTime)
{
Value = user.Lastlogin
},
new NpgsqlParameter("@PrevLogin", DbType.DateTime)
{
Value = user.PrevLogin
},
new NpgsqlParameter("@ChildList", DbType.String)
{
Value = childIdList
}
};
DataTable dt = DBManager.ExecuteReaderCommand(query, CommandType.Text, parameters);
return(GetChildAsList(dt));
}
catch (Exception ex)
{
Log.InsertEntity("Child", "GetChildByIdList", 4, ex.StackTrace.Replace("'", ""), ex.Message.Replace("'", ""));
throw ex;
}
}