public ChurchService()
{
_churchTransformer = new ChurchTransformer();
_massDetailTransformer = new MassDetailTransformer();
_userTransformer = new UserTransformer();
_churchReviewTransformer = new ChurchReviewTransformer();
_announcementTransformer = new AnnouncementTransformer();
}
public List <ChurchInfo> GetFavoriteChurches(int userId)
{
var churchTransformer = new ChurchTransformer();
var churches = new List <ChurchInfo>();
using (var dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (var cmd = new SqlCommand("spGetFavoriteChurch", dbconn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userID", userId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var church = churchTransformer.Transform(reader);
char[] separator = { ',' };
var photos = reader["ChurchPhotos"].ToString().Split(separator, StringSplitOptions.None);
church.ChurchPhotos = new List <ChurchPhotosModel>();
foreach (var strPix in photos)
{
church.ChurchPhotos.Add(new ChurchPhotosModel
{
ChurchPhotos = strPix == string.Empty ? "" : @"Images\Photos\" + strPix
});
}
churches.Add(church);
}
}
catch (Exception)
{
// ignored
}
}
}
return(churches);
}
public static int InsertAdorations(int SimbahanID, int IsOpen24By7, string DisplayText, bool HasAircon, bool HasCeilingFan, bool HasOrdinaryFan)
{
ChurchTransformer churchTransformer = new ChurchTransformer();
ChurchInfo adoration = new ChurchInfo();
using (SqlConnection dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (SqlCommand cmd = new SqlCommand("spInsertAdorations", dbconn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SimbahanID", SimbahanID);
cmd.Parameters.AddWithValue("@IsOpen24By7", IsOpen24By7);
cmd.Parameters.AddWithValue("@DisplayText", DisplayText);
cmd.Parameters.Add("@AdorationAutoID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
var reader = cmd.ExecuteReader();
HttpContext.Current.Session["adoID"] = Convert.ToInt32(cmd.Parameters["@AdorationAutoID"].Value.ToString());
adoration.AdorationID = Convert.ToInt32(HttpContext.Current.Session["adoID"]);
while (reader.Read())
{
adoration = churchTransformer.Transform(reader);
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
}
StartInsertAdorationVentilation(adoration.AdorationID, HasAircon, HasCeilingFan, HasOrdinaryFan);
return(adoration.AdorationID);
}
public List <Tuple <ChurchInfo, User> > GetChurchFollowers(int churchId)
{
var churchTransformer = new ChurchTransformer();
var userTransformer = new UserTransformer();
var followers = new List <Tuple <ChurchInfo, User> >();
using (var dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (var cmd = new SqlCommand("spGetChurchFollowers", dbconn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@churchID", churchId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var church = churchTransformer.Transform(reader);
var user = userTransformer.Transform(reader);
var follower = new Tuple <ChurchInfo, User>(church, user);
followers.Add(follower);
}
}
catch (Exception)
{
// ignored
}
}
}
return(followers);
}
public List <Tuple <Church, User> > GetChurchFollowers(int churchId)
{
var churchTransformer = new ChurchTransformer();
var userTransformer = new UserTransformer();
var followers = new List <Tuple <Church, User> >();
using (var sp = new StoredProcedure(""))
{
sp.SqlCommand.Parameters.AddWithValue("@churchID", churchId);
var reader = sp.SqlCommand.ExecuteReader();
while (reader.Read())
{
var church = churchTransformer.Transform(reader);
var user = userTransformer.Transform(reader);
var follower = new Tuple <Church, User>(church, user);
followers.Add(follower);
}
}
return(followers);
}
public static ChurchInfo CreateChurch(string name, string address, string city, string province, string country, string vicariate, string feastday, string parishPriest, string administeredBy, string diocese, string dateEstablished, string phoneNumber, string email, string website, int activated, int LocationID, int HasAdorationChapel, int IsOpen24By7, string DisplayText, bool HasAircon, bool HasCeilingFan, bool HasOrdinaryFan, string OfficeHours, string DevotionSchedule, string textBapt, string DateCreatedBapt, string textWedd, string DateCreatedWedd, float Long, float Lat)
{
ChurchTransformer churchTransformer = new ChurchTransformer();
ChurchInfo church = new ChurchInfo();
using (SqlConnection dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (SqlCommand cmd = new SqlCommand("InsertSimbahanInfo", dbconn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ChurchName", name);
cmd.Parameters.AddWithValue("@Address", address);
cmd.Parameters.AddWithValue("@City", city);
cmd.Parameters.AddWithValue("@Province", province);
cmd.Parameters.AddWithValue("@Vicariate", vicariate);
cmd.Parameters.AddWithValue("@FeastDay", feastday);
cmd.Parameters.AddWithValue("@ParishPriest", parishPriest);
//cmd.Parameters.AddWithValue("@AdministeredBy", administeredBy);
cmd.Parameters.AddWithValue("@Diocese", diocese);
cmd.Parameters.AddWithValue("@DateEstablished", dateEstablished);
cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
cmd.Parameters.AddWithValue("@Email", email);
cmd.Parameters.AddWithValue("@Website", website);
cmd.Parameters.AddWithValue("@Activated", activated);
cmd.Parameters.AddWithValue("@LocationID", LocationID);
cmd.Parameters.AddWithValue("@HasAdorationChapel", HasAdorationChapel);
cmd.Parameters.AddWithValue("@OfficeHours", OfficeHours);
cmd.Parameters.AddWithValue("@DevotionSchedule", DevotionSchedule);
cmd.Parameters.AddWithValue("@StreetNo", 0);
cmd.Parameters.AddWithValue("@StreetName", "");
cmd.Parameters.AddWithValue("@Barangay", "");
cmd.Parameters.AddWithValue("@DateCreated", DateTime.UtcNow);
cmd.Parameters.AddWithValue("@LastUpdate", DateTime.UtcNow);
cmd.Parameters.AddWithValue("@churchType", 1);
cmd.Parameters.AddWithValue("@Long", Long);
cmd.Parameters.AddWithValue("@Lat", Lat);
cmd.Parameters.AddWithValue("@churchCode", "");
cmd.Parameters.AddWithValue("@churchHistory", "");
cmd.Parameters.Add("@SimbahanAutoID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
var reader = cmd.ExecuteReader();
HttpContext.Current.Session["sid"] = Convert.ToInt32(cmd.Parameters["@SimbahanAutoID"].Value.ToString());
church.SimbahanID = Convert.ToInt32(HttpContext.Current.Session["sid"]);
//this.ClearFields();
while (reader.Read())
{
church = churchTransformer.Transform(reader);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
var adorationID = InsertAdorations(church.SimbahanID, IsOpen24By7, DisplayText, HasAircon, HasCeilingFan, HasOrdinaryFan);
church.AdorationID = adorationID;
InsertBaptismDetails(textBapt, church.SimbahanID, DateCreatedBapt);
InsertWeddingDetails(textWedd, church.SimbahanID, DateCreatedWedd);
return(church);
}
public ChurchReviewService()
{
_churchReviewTransformer = new ChurchReviewTransformer();
_churchTransformer = new ChurchTransformer();
_userTransformer = new UserTransformer();
}
// TODO: CREATE A TRANSFORMER FOR STATUS.
public VisitaIglesiaService()
{
_visitaIglesiaTransformer = new VisitaIglesiaTransformer();
_churchTransformer = new ChurchTransformer();
_userTransformer = new UserTransformer();
}
public List <Adoration> Search(string keyword, string location, string day, string time, string chapelLocation,
string ventilation, string parkings)
{
var adorations = new List <Adoration>();
var churchTransformer = new ChurchTransformer();
using (var sp = new StoredProcedure("spSearchAdorations"))
{
sp.SqlCommand.Parameters.AddWithValue("@keyword", keyword);
sp.SqlCommand.Parameters.AddWithValue("@location", location);
sp.SqlCommand.Parameters.AddWithValue("@scheduleTime", time);
sp.SqlCommand.Parameters.AddWithValue("@scheduleDay", day);
sp.SqlCommand.Parameters.AddWithValue("@chapelLocation", chapelLocation);
sp.SqlCommand.Parameters.AddWithValue("@ventilation", ventilation);
sp.SqlCommand.Parameters.AddWithValue("@parkings", parkings);
var reader = sp.SqlCommand.ExecuteReader();
while (reader.Read())
{
var adoration = new Adoration
{
AdorationId = Convert.ToInt32(reader["AdorationID"]),
ChurchId = Convert.ToInt32(reader["SimbahanId"]),
Is247 = Convert.ToBoolean(reader["IsOpen24By7"]),
DisplayText = reader["DisplayText"].ToString()
};
char[] separator = { ',' };
char[] pipeSeparator = { '|' };
var ventilations = reader["Ventilations"].ToString().Split(separator, StringSplitOptions.None);
foreach (var ventilationItem in ventilations)
{
adoration.Ventilations.Add(ventilationItem);
}
var photos = reader["AdorationPhotos"].ToString().Split(separator, StringSplitOptions.None);
foreach (var photo in photos)
{
adoration.Images.Add(photo == string.Empty ? "" : @"Images\Photos\" + photo);
}
var masses = reader["Masses"].ToString().Split(separator, StringSplitOptions.None);
foreach (var massDb in masses)
{
var massDetail = massDb.Split(pipeSeparator, StringSplitOptions.None);
var mass = new MassDetailsModel
{
ScheduleId = Convert.ToInt32(massDetail[2]),
Time = massDetail[3],
TimeStandardId = Convert.ToInt32(massDetail[4])
};
switch (Convert.ToInt32(massDetail[2]))
{
case 1:
mass.Days = "Sunday";
adoration.SundaySchedule.Add(mass);
break;
case 2:
mass.Days = "Monday";
adoration.MondaySchedule.Add(mass);
break;
case 3:
mass.Days = "Tuesday";
adoration.TuesdaySchedule.Add(mass);
break;
case 4:
mass.Days = "Wednesday";
adoration.WednesdaySchedule.Add(mass);
break;
case 5:
mass.Days = "Thursday";
adoration.ThursdaySchedule.Add(mass);
break;
case 6:
mass.Days = "Friday";
adoration.FridaySchedule.Add(mass);
break;
case 7:
mass.Days = "Saturday";
adoration.SaturdaySchedule.Add(mass);
break;
}
}
adoration.Church = churchTransformer.Transform(reader);
adorations.Add(adoration);
}
}
return(adorations);
}
public Adoration Find(int id)
{
var adoration = new Adoration();
var churchTransformer = new ChurchTransformer();
using (var sp = new StoredProcedure("spFindAdoration"))
{
sp.SqlCommand.Parameters.AddWithValue("@adorationID", id);
var reader = sp.SqlCommand.ExecuteReader();
while (reader.Read())
{
adoration.Church = churchTransformer.Transform(reader);
adoration.Is247 = Convert.ToBoolean(reader["IsOpen24By7"]);
adoration.ChurchId = Convert.ToInt32(reader["SimbahanID"]);
adoration.AdorationId = Convert.ToInt32(reader["AdorationID"]);
adoration.DisplayText = reader["DisplayText"].ToString();
}
// Adoration Schedule
reader.NextResult();
while (reader.Read())
{
var mass = new MassDetailsModel()
{
Id = Convert.ToInt32(reader["AdorationID"]),
ScheduleId = Convert.ToInt32(reader["ScheduleID"]),
TimeStandardId = Convert.ToInt32(reader["TimeStandardID"]),
Time = reader["Time"].ToString()
};
switch (Convert.ToInt32(reader["ScheduleID"]))
{
case 1:
mass.Days = "Sunday";
adoration.SundaySchedule.Add(mass);
break;
case 2:
mass.Days = "Monday";
adoration.MondaySchedule.Add(mass);
break;
case 3:
mass.Days = "Tuesday";
adoration.TuesdaySchedule.Add(mass);
break;
case 4:
mass.Days = "Wednesday";
adoration.WednesdaySchedule.Add(mass);
break;
case 5:
mass.Days = "Thursday";
adoration.ThursdaySchedule.Add(mass);
break;
case 6:
mass.Days = "Friday";
adoration.FridaySchedule.Add(mass);
break;
case 7:
mass.Days = "Saturday";
adoration.SaturdaySchedule.Add(mass);
break;
}
adoration.Masses.Add(mass);
}
reader.NextResult();
while (reader.Read())
{
adoration.Ventilations.Add(reader["VentType"].ToString());
}
reader.NextResult();
while (reader.Read())
{
adoration.Images.Add("Images/Photos/" + reader["ImagePath"]);
}
}
return(adoration);
}
public List <Adoration> Search(string keyword, string location, string day, string time, string chapelLocation,
string ventilation)
{
var adorations = new List <Adoration>();
var churchTransformer = new ChurchTransformer();
using (var dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (var cmd = new SqlCommand("spSearchAdorations", dbconn))
{
try
{
if (day == "8")
{
day = (int)DateTime.Now.DayOfWeek + 1 + "";
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@keyword", keyword);
cmd.Parameters.AddWithValue("@location", location);
cmd.Parameters.AddWithValue("@scheduleTime", time);
cmd.Parameters.AddWithValue("@scheduleDay", day);
cmd.Parameters.AddWithValue("@chapelLocation", chapelLocation);
cmd.Parameters.AddWithValue("@ventilation", ventilation);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var adoration = new Adoration
{
AdorationID = Convert.ToInt32(dr["AdorationID"]),
SimbahanID = Convert.ToInt32(dr["SimbahanId"]),
Is247 = Convert.ToBoolean(dr["IsOpen24By7"]),
DisplayText = dr["DisplayText"].ToString()
};
char[] separator = { ',' };
char[] pipeSeparator = { '|' };
var ventilations = dr["Ventilations"].ToString().Split(separator, StringSplitOptions.None);
foreach (var ventilationItem in ventilations)
{
adoration.Ventilations.Add(ventilationItem);
}
var photos = dr["AdorationPhotos"].ToString().Split(separator, StringSplitOptions.None);
foreach (var photo in photos)
{
if (photo != string.Empty)
{
adoration.Images.Add(@"Images\Photos\" + photo);
}
}
if (photos.Length == 0)
{
adoration.Images.Add(@"Images\Photos\default.jpg");
}
var masses = dr["Masses"].ToString().Split(separator, StringSplitOptions.None);
foreach (var massDb in masses)
{
var massDetail = massDb.Split(pipeSeparator, StringSplitOptions.None);
var mass = new MassDetailsModel
{
ScheduleId = Convert.ToInt32(massDetail[2]),
Time = massDetail[3],
TimeStandardId = Convert.ToInt32(massDetail[4])
};
switch (Convert.ToInt32(massDetail[2]))
{
case 1:
mass.Days = "Sunday";
adoration.SundaySchedule.Add(mass);
break;
case 2:
mass.Days = "Monday";
adoration.MondaySchedule.Add(mass);
break;
case 3:
mass.Days = "Tuesday";
adoration.TuesdaySchedule.Add(mass);
break;
case 4:
mass.Days = "Wednesday";
adoration.WednesdaySchedule.Add(mass);
break;
case 5:
mass.Days = "Thursday";
adoration.ThursdaySchedule.Add(mass);
break;
case 6:
mass.Days = "Friday";
adoration.FridaySchedule.Add(mass);
break;
case 7:
mass.Days = "Saturday";
adoration.SaturdaySchedule.Add(mass);
break;
}
}
adoration.Church = churchTransformer.Transform(dr);
adorations.Add(adoration);
}
}
catch (Exception)
{
// ignored
}
}
}
return(adorations);
}
public static void InsertOrganization(string name, string address, string streetNo,
string streetName, string barangay, string city,
string province, string country, string dateEstablished, string parentorg,
string feastBuilder, string branch, string contact, string email,
string website, string retreat, string recollect, string talks,
string camp, string volunteer, float latitude, float longitude,
string about)
{
ChurchTransformer churchTransformer = new ChurchTransformer();
ChurchInfo org = new ChurchInfo();
using (SqlConnection dbconn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString))
{
if (dbconn.State == ConnectionState.Open)
{
dbconn.Close();
}
dbconn.Open();
using (SqlCommand cmd = new SqlCommand("[spInsertCatholicOrganization]", dbconn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@OrgName", name);
cmd.Parameters.AddWithValue("@LastUpdate", DateTime.UtcNow);
cmd.Parameters.AddWithValue("@CompleteAddress", address);
cmd.Parameters.AddWithValue("@StreetNo", streetNo);
cmd.Parameters.AddWithValue("@StreetName", streetName);
cmd.Parameters.AddWithValue("@Barangay", barangay);
cmd.Parameters.AddWithValue("@CityOrMunicipality", city);
cmd.Parameters.AddWithValue("@StateOrProvince", province);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Parameters.AddWithValue("@DateEstablished", dateEstablished);
cmd.Parameters.AddWithValue("@ParentOrganization", parentorg);
cmd.Parameters.AddWithValue("@FeastBuilderOrPreacher", feastBuilder);
cmd.Parameters.AddWithValue("@BranchOrLocation", branch);
cmd.Parameters.AddWithValue("@ContactNo", contact);
cmd.Parameters.AddWithValue("@EmailAddress", email);
cmd.Parameters.AddWithValue("@Website", website);
cmd.Parameters.AddWithValue("@RetreatSchedule", retreat);
cmd.Parameters.AddWithValue("@RecollectSchedule", recollect);
cmd.Parameters.AddWithValue("@TalkSchedule", talks);
cmd.Parameters.AddWithValue("@CampSchedule", camp);
cmd.Parameters.AddWithValue("@VolunteerSchedule", volunteer);
cmd.Parameters.AddWithValue("@Latitude", latitude);
cmd.Parameters.AddWithValue("@Longitude", longitude);
cmd.Parameters.AddWithValue("@About", about);
cmd.Parameters.Add("@CatholicOrgAutoId", SqlDbType.BigInt).Direction = ParameterDirection.Output;
var reader = cmd.ExecuteReader();
HttpContext.Current.Session["orgID"] = Convert.ToInt32(cmd.Parameters["@CatholicOrgAutoId"].Value.ToString());
org.SimbahanID = Convert.ToInt32(HttpContext.Current.Session["orgID"]);
while (reader.Read())
{
org = churchTransformer.Transform(reader);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
}