public static VolunteerApplication GetVolunteerApplicationByID(int volunteerApplicationID)
{
//declare object
VolunteerApplication volunteerApplication = new VolunteerApplication();
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_get_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("VolunteerApplicationID", volunteerApplicationID);
con.Open();
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
//reads and set values to object
volunteerApplication.VolunteerApplicationID = Convert.ToInt32(rd["VolunteerApplicationID"]);
volunteerApplication.ApplicationDate = Convert.ToDateTime(rd["ApplicationDate"].ToString());
volunteerApplication.VolunteerName = rd["VolunteerName"].ToString();
volunteerApplication.ContactNumber = rd["ContactNumber"].ToString();
volunteerApplication.EmailAddress = rd["EmailAddress"].ToString();
volunteerApplication.Availability = Convert.ToInt32(rd["Availability"].ToString());
volunteerApplication.PreferredUnit = Convert.ToInt32(rd["PreferredUnit"].ToString());
volunteerApplication.Status = Convert.ToInt32(rd["Status"].ToString());
}
rd.Close();
con.Close();
return(volunteerApplication);
}
public static DataTable GetSearchResult(Animal animalFilter, int pageIndex, int pageSize, out int rowCount)
{
DataTable dt = new DataTable("dt");
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_AnimalSearch", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AnimalType", animalFilter.AnimalType);
cmd.Parameters.AddWithValue("@AnimalAge", animalFilter.AnimalAge);
cmd.Parameters.AddWithValue("@Children", animalFilter.Children);
cmd.Parameters.AddWithValue("@SecureGarden ", animalFilter.SecureGarden);
cmd.Parameters.AddWithValue("@OtherPets", animalFilter.OtherPets);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
rowCount = recordCount;
}
return(dt);
}
public static int[] GetWeeklyVolunteerApplicants()
{
Dictionary <string, int> dict = new Dictionary <string, int>
{
{ "Monday", 0 },
{ "Tuesday", 0 },
{ "Wednesday", 0 },
{ "Thursday", 0 },
{ "Friday", 0 },
{ "Saturday", 0 },
{ "Sunday ", 0 }
};
SqlDataReader rd;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_WeeklyVolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
if (dict.ContainsKey(rd["Day"].ToString()))
{
dict[rd["Day"].ToString()] = Convert.ToInt32(rd["Count"]);
}
}
rd.Close();
}
return(dict.Values.ToArray());
}
public static Applicant GetApplicantByApplicationId(int applicationId)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlDataReader rd;
Applicant applicant = null;
using (con)
{
// Getting the database connectivity using stored procedure
SqlCommand cmd = new SqlCommand("sp_get_ApplicantByApplicationId", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("ApplicationID", applicationId);
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
applicant = new Applicant();
applicant.ApplicantID = Convert.ToInt32(rd["ApplicantID"]);
applicant.ApplicantName = rd["ApplicantName"].ToString();
applicant.ContactNumber = rd["ContactNumber"].ToString();
applicant.EmailAddress = rd["EmailAddress"].ToString();
}
rd.Close();
}
con.Close();
return(applicant);
}
public static AdminModel Login(string Username, string Password)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlDataReader rd;
AdminModel adminModel = null;
using (con)
{
// Getting the database connectivity using stored procedure
SqlCommand cmd = new SqlCommand("sp_get_Admin", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("Username", Username);
cmd.Parameters.AddWithValue("Password", Encryption.Encrypt(Password));
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
adminModel = new AdminModel();
adminModel.AdminID = Convert.ToInt32(rd["AdminID"]);
adminModel.Username = rd["Username"].ToString();
adminModel.Password = rd["Password"].ToString();
}
rd.Close();
}
con.Close();
return(adminModel);
}
public static void SaveAnimalType(string animalType)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_save_AnimalType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Type", animalType);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void DeleteAnimalType(int animalTypeID)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_delete_AnimalType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("AnimalTypeID", animalTypeID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void SaveAnimalApplication(AnimalApplication application)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_save_AnimalApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("AnimalID", application.AnimalID);
cmd.Parameters.AddWithValue("ApplicantID", application.ApplicantID);
cmd.Parameters.AddWithValue("Status", application.Status);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void SaveSuccessStory(SuccessStory successStory)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_save_SuccessStory", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Name", successStory.Name);
cmd.Parameters.AddWithValue("StroryDescription", successStory.StroryDescription);
cmd.Parameters.AddWithValue("Image", successStory.Image);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void MoveToAvailable(int animalID)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_MoveToActive", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("AnimalID", animalID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void UpdateFailedAttempt(string emailAddress)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_update_FailedAttempt", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("email", emailAddress);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static DataSet GetAnimalType()
{
DataSet dataSet = new DataSet("dt");
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_AnimalTypes", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataSet);
}
return(dataSet);
}
public static int GetVolunteerCount()
{
int count = 0;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_VolunteerCount", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
return(count);
}
public static void UpdateSuccessStoryStatus(int storyID, bool active)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_update_SuccessStoryStatus", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("StoryID", storyID);
cmd.Parameters.AddWithValue("Active", active);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static void SaveAdmin(string username, string password)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_save_Admin", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", Encryption.Encrypt(password));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static DataSet GetAnimal(bool rehomed)
{
DataSet dataSet = new DataSet("dt");
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_Animals", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("rehomed", rehomed);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataSet);
}
return(dataSet);
}
public static void UpdateVolunteerApplicationStatus(int volunteerApplicationID, int status, int adminId)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_update_VolunteerApplicationStatus", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("VolunteerApplicationID", volunteerApplicationID);
cmd.Parameters.AddWithValue("Status", status);
cmd.Parameters.AddWithValue("AdminId", adminId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static int GetAnimalCounts(bool rehomed, bool all)
{
int count = 0;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_AnimalCount", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("rehomed", rehomed);
cmd.Parameters.AddWithValue("all", all);
con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
return(count);
}
public static DataSet GetVolunteerApplication(DateTime from, DateTime to)
{
DataSet dataSet = new DataSet("dt");
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_rpt_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("from", from);
cmd.Parameters.AddWithValue("to", to);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataSet);
}
return(dataSet);
}
public static void SaveAnimal(Animal animal)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_save_Animal", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("AnimalType", animal.AnimalType);
cmd.Parameters.AddWithValue("AnimalAge", animal.AnimalAge);
cmd.Parameters.AddWithValue("AnimalName", animal.AnimalName);
cmd.Parameters.AddWithValue("Children", animal.Children);
cmd.Parameters.AddWithValue("OtherPets", animal.OtherPets);
cmd.Parameters.AddWithValue("SecureGarden", animal.SecureGarden);
cmd.Parameters.AddWithValue("ImageUrl", animal.ImageUrl);
cmd.Parameters.AddWithValue("AnimalID", animal.AnimalID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static int SaveApplicant(Applicant applicant)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_save_Applicant", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ApplicantName", applicant.ApplicantName);
cmd.Parameters.AddWithValue("ContactNumber", applicant.ContactNumber);
cmd.Parameters.AddWithValue("EmailAddress", applicant.EmailAddress);
cmd.Parameters.Add("@ApplicantID", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open();
//insert and return inserted applicant id
cmd.ExecuteNonQuery();
int applicantId = Convert.ToInt32(cmd.Parameters["@ApplicantID"].Value.ToString());
con.Close();
return(applicantId);
}
public static bool ValidateAnimalType(string animalType)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlCommand cmd = new SqlCommand("sp_validate_AnimalType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Type", animalType);
con.Open();
int rowCount = (int)cmd.ExecuteScalar();
con.Close();
if (rowCount > 0)
{
return(false);
}
return(true);
}
public static DataSet GetVolunteerApplication(bool isActive)
{
DataSet dataSet = new DataSet("dt");
// Getting the database connectivity as stored procedure
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_getAll_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("isActive", isActive);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
// Getting the data and filing to data set variable
da.Fill(dataSet);
}
return(dataSet);
}
public static void SaveVolunteerApplication(VolunteerApplication volunteerApplication)
{
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_save_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
//setting parameters
cmd.Parameters.AddWithValue("VolunteerName", volunteerApplication.VolunteerName);
cmd.Parameters.AddWithValue("Availability", volunteerApplication.Availability);
cmd.Parameters.AddWithValue("PreferredUnit", volunteerApplication.PreferredUnit);
cmd.Parameters.AddWithValue("Status", volunteerApplication.Status);
cmd.Parameters.AddWithValue("ContactNumber", volunteerApplication.ContactNumber);
cmd.Parameters.AddWithValue("EmailAddress", volunteerApplication.EmailAddress);
cmd.Parameters.AddWithValue("ApplicationDate", volunteerApplication.ApplicationDate);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public static List <TypeObject> GetAnimalsByType(bool isRehomed)
{
List <TypeObject> typeList = new List <TypeObject>();;
SqlDataReader rd;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
SqlCommand cmd = new SqlCommand("sp_get_AnimalsbyType", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@rehomed", isRehomed);
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
TypeObject type = new TypeObject();
type.Type = rd["Type"].ToString();
type.Count = Convert.ToInt32(rd["Count"]);
typeList.Add(type);
}
rd.Close();
}
return(typeList);
}
public static bool Validate(string emailAddress)
{
SqlDataReader rd;
//getting the database connectivity
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
//set command type as stored procedure
SqlCommand cmd = new SqlCommand("sp_validate_VolunteerApplication", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("EmailAddress", emailAddress);
con.Open();
//get row count (if no records count is 0)
int rowCount = (int)cmd.ExecuteScalar();
con.Close();
if (rowCount > 0)
{
return(false);
}
return(true);
}
public static int[] GetWeeklyRehomedAnimals()
{
Dictionary <string, int> dict = new Dictionary <string, int>
{
{ "Monday", 0 },
{ "Tuesday", 0 },
{ "Wednesday", 0 },
{ "Thursday", 0 },
{ "Friday", 0 },
{ "Saturday", 0 },
{ "Sunday ", 0 }
};
List <int> list = new List <int>();;
SqlDataReader rd;
using (SqlConnection con = new SqlConnection(DBCon.GetDBCon()))
{
//https://stackoverflow.com/questions/1110998/get-day-of-week-in-sql-server-2005-2008
//https://stackoverflow.com/questions/23051197/how-to-get-data-of-current-week-only-in-sql-server
//check the sp
SqlCommand cmd = new SqlCommand("sp_get_WeeklyRehomedAnimals", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
if (dict.ContainsKey(rd["Day"].ToString()))
{
dict[rd["Day"].ToString()] = Convert.ToInt32(rd["Count"]);
}
}
rd.Close();
}
//https://stackoverflow.com/questions/197059/convert-dictionary-values-into-array
return(dict.Values.ToArray());
}
public static Animal GetAnimalByAnimalID(string animalId)
{
SqlConnection con = new SqlConnection(DBCon.GetDBCon());
SqlDataReader rd;
Animal animal = null;
using (con)
{
// Getting the database connectivity using stored procedure
SqlCommand cmd = new SqlCommand("sp_get_AnimalByAnimalID", con);
cmd.CommandType = CommandType.StoredProcedure;
// Passing parameters
cmd.Parameters.AddWithValue("AnimalID", Convert.ToInt32(animalId));
con.Open();
rd = cmd.ExecuteReader();
while (rd.Read())
{
animal = new Animal();
animal.AnimalID = Convert.ToInt32(rd["AnimalID"]);
animal.AnimalName = rd["AnimalName"].ToString();
animal.AnimalAge = Convert.ToInt32(rd["AnimalAge"].ToString());
animal.AnimalType = Convert.ToInt32(rd["AnimalType"].ToString());
animal.Children = Convert.ToBoolean(rd["Children"].ToString());
animal.SecureGarden = Convert.ToBoolean(rd["SecureGarden"].ToString());
animal.OtherPets = Convert.ToBoolean(rd["OtherPets"].ToString());
animal.ImageUrl = rd["ImageUrl"].ToString();
}
rd.Close();
}
con.Close();
return(animal);
}
