public bool updatePassword(String Email)
{
var CL = new commonLogic();
SqlConnection con = CL.connect();
try
{
Password = hassPassword(CnfPassword);
SqlCommand cmd;
string query = "UPDATE users SET password = @password WHERE email = @email";
cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@email", Email);
cmd.Parameters.AddWithValue("@password", Password);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();
SqlConnection cnn = CL.connect();
try
{
SqlCommand command;
string sql;
sql = "SELECT * FROM users WHERE email = @email";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@email", Email);
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
HttpContext.Current.Session["id"] = dataReader["id"].ToString();
HttpContext.Current.Session["email"] = dataReader["email"].ToString();
HttpContext.Current.Session["first_name"] = dataReader["first_name"].ToString();
HttpContext.Current.Session["last_name"] = dataReader["last_name"].ToString();
break;
}
cnn.Close();
return(true);
}
else
{
cnn.Close();
return(false);
}
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
cnn.Close();
return(false);
}
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
con.Close();
return(false);
}
}
public bool emergencyDrillClients(int newId)
{
var CL = new commonLogic();
try
{
SqlConnection cnn = CL.connect();
SqlCommand command;
string sql;
for (int loop = 0; loop < ClientName.Length; loop++)
{
sql = "INSERT INTO emergency_drill_clients (emergency_drill_log_id,client_name,evacuation_time,prompt_level,comments,updated_date,added_date) VALUES ";
sql = sql + " (@emergency_drill_log_id,@ClientName,@EvacuationTime,@PromptLevel,@Comments,@updatedDate,GETDATE())";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@emergency_drill_log_id", newId);
command.Parameters.AddWithValue("@ClientName", ClientName[loop]);
command.Parameters.AddWithValue("@EvacuationTime", EvacuationTime[loop]);
command.Parameters.AddWithValue("@PromptLevel", PromptLevel[loop]);
command.Parameters.AddWithValue("@Comments", Comments[loop]);
command.Parameters.AddWithValue("@updatedDate", DBNull.Value);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
cnn.Close();
}
catch (Exception e)
{
CL.getLog("error while adding data to emergency_drill_clients table" + e);
return(false);
}
return(true);
}
public bool updateUniqueId(int newId)
{
var CL = new commonLogic();
try
{
SqlConnection cnn = CL.connect();
SqlCommand command;
string sql;
string uniqueID = "RES" + DateTime.Now.ToString("hhmmss") + newId;
sql = "UPDATE residency_survey SET unique_id = @unique_id WHERE id = @id";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@id", newId);
command.Parameters.AddWithValue("@unique_id", uniqueID);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
cnn.Close();
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
return(false);
}
return(true);
}
public FileContentResult pdf()
{
string html = string.Empty;
string uniqueId = (string)Request.QueryString["a"];
string page = (string)Request.QueryString["page"];
var OS = new OfficeSurveyModel();
var CL = new commonLogic();
var htmlToPdf = new HtmlToPdfConverter();
var pdfContentType = "application/pdf";
try
{
OS.where = " WHERE unique_id = '" + uniqueId + "'";
CL.dataTable = OS.select();
CL.pageType = page;
html = CL.ViewHtml();
string htmlContent = "<!DOCTYPE html><html lang='en'><head><style><style>body{font-family: 'Poppins', sans-serif;}.h5, h5 {font-size: 1rem; }hr{width: 100%;}hr {margin-top: 1rem;margin-bottom: 1rem;border: 0; border-top: 1px solid rgba(0, 0, 0, 0.1);}.col-md-12{width:100%;}.col-sm-8{width:60%; float:left;}.col-sm-4{width:40%;float:right;}</style></style></head><body>" + html + "</body></html>";
//var htmlToPdf = new NReco.PdfGenerator.HtmlToPdfConverter();
htmlToPdf.PageFooterHtml = $@"page <span class=""page""></span> of <span class=""topage""></span>";
var pdfBytes = htmlToPdf.GeneratePdf(htmlContent);
string fileName = CL.getFileName();
return(File(pdfBytes, "application/pdf", fileName + ".pdf"));
}
catch (Exception e)
{
CL.getLog("Fail to generate PDF." + e);
return(File(htmlToPdf.GeneratePdf("<b>Something Went wrong!!!</b>", null), pdfContentType));
}
}
public bool emergencyDrillParticipants(int newId)
{
var CL = new commonLogic();
try
{
SqlConnection cnn = CL.connect();
SqlCommand command;
string sql;
for (int loop = 0; loop < ParticipantName.Length; loop++)
{
sql = "INSERT INTO emergency_drill_participants (emergency_drill_log_id,participant_name,participant_title,added_date) VALUES (@emergency_drill_log_id,@ParticipantName,@ParticipantTitle,GETDATE())";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@emergency_drill_log_id", newId);
command.Parameters.AddWithValue("@ParticipantName", ParticipantName[loop]);
command.Parameters.AddWithValue("@ParticipantTitle", ParticipantTitle[loop]);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
cnn.Close();
}
catch (Exception e)
{
CL.getLog("error while adding data to emergencyDrillParticipants table" + e);
return(false);
}
return(true);
}
public string fileUpload()
{
string fileDataString = "{";
var CL = new commonLogic();
try
{
var i = 0;
foreach (HttpPostedFileBase file in files)
{
//Checking file is available to save.
if (file != null)
{
i++;
if (CheckFileType(file.FileName.ToString(), CL))
{
string newFileName = Path.GetFileNameWithoutExtension(file.FileName) + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + Path.GetExtension(file.FileName);
var path = Path.Combine(HttpContext.Current.Server.MapPath("~/upload"), newFileName);
file.SaveAs(path);
fileDataString += "File" + i + ":'" + newFileName + "',";
}
}
}
}
catch (Exception e)
{
CL.getLog("error uploading file ");
fileDataString += "}";
return(fileDataString);
}
fileDataString += "}";
return(fileDataString);
}
public bool ResidentialDrillClients(int newId)
{
var CL = new commonLogic();
try
{
SqlConnection cnn = CL.connect();
SqlCommand command;
string sql;
for (int loop = 0; loop < ClientName.Length; loop++)
{
if (ClientName[loop] != "")
{
sql = "INSERT INTO resdential_drill_clients (residential_drill_id,client_name,present_for_drill,time_to_exit,client_classification,prompt_level,added_date) VALUES (@residential_drill_id,@client_name,@present_for_drill,@time_to_exit,@client_classification,@prompt_level,GETDATE())";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@residential_drill_id", newId);
command.Parameters.AddWithValue("@client_name", ClientName[loop]);
command.Parameters.AddWithValue("@present_for_drill", PresentForDrill[loop]);
command.Parameters.AddWithValue("@time_to_exit", TimeToExit[loop]);
command.Parameters.AddWithValue("@client_classification", ClientClassification[loop]);
command.Parameters.AddWithValue("@prompt_level", PromptLevel[loop]);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
cnn.Close();
}
catch (Exception e)
{
CL.getLog("error while adding data to resdential_drill_clients table" + e);
return(false);
}
return(true);
}
public string GetDrillStatus(string reportForQuanterEnding, string DateTimeofDrill, string TypeOfDrill, string Durationofdrill)
{
var CL = new commonLogic();
string status = "Fail";
try
{
if (Durationofdrill != null)
{
double seconds = TimeSpan.Parse(Durationofdrill).TotalMinutes; //convert to seconds
if (TypeOfDrill == "Fire" && seconds < 150)
{
status = "Pass";
}
if (TypeOfDrill != "Fire")
{
status = "Pass";
}
}
else
{
status = "Pass";
}
}
catch (Exception e)
{
CL.getLog("error getting status resdential_drill_log " + e);
}
return(status);
}
public Boolean insert()
{
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
SqlCommand command;
String sql = "";
string files = fileUpload();
string id = HttpContext.Current.Session["id"].ToString();
try
{
sql = "INSERT INTO residency_survey (user_id,form_data,files,added_date) OUTPUT INSERTED.ID VALUES";
sql = sql + "(@user_id,@form_data,@files,GETDATE())";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@user_id", id);
command.Parameters.AddWithValue("@form_data", completeFormData);
command.Parameters.AddWithValue("@files", files);
command.CommandType = CommandType.Text;
int newId = Convert.ToInt32(command.ExecuteScalar());
if (newId < 1)
{
CL.getLog("Fail to insert");
return(false);
}
bool uniqueIdUpdate = updateUniqueId(newId);
if (uniqueIdUpdate == false)
{
CL.getLog("Fail to update with unique id to Residency Survey table.");
return(false);
}
cnn.Close();
}
catch (Exception e)
{
CL.getLog("Fail to insert to Residency Survey " + e);
return(false);
}
return(true);
}
public bool checkUser()
{
bool status = false;
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
try
{
SqlCommand command;
string sql;
Password = hassPassword(Password);
sql = "SELECT * FROM users WHERE email = @email AND password = @password";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@email", Email);
command.Parameters.AddWithValue("@password", Password);
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
HttpContext.Current.Session["id"] = dataReader["id"].ToString();
HttpContext.Current.Session["email"] = dataReader["email"].ToString();
HttpContext.Current.Session["first_name"] = dataReader["first_name"].ToString();
HttpContext.Current.Session["last_name"] = dataReader["last_name"].ToString();
break;
}
status = true;
}
else
{
status = false;
}
return(status);
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
cnn.Close();
return(false);
}
cnn.Close();
return(status);
}
public String getViewData(FormCollection form)
{
string uniqueID = form["uniqueID"];
string html = "";
var CL = new commonLogic();
try
{
var RM = new ResidentialDrillModel();
DataTable residentialData = RM.GetResidentialData(uniqueID);
//DataTable residentialClientData = RM.GetResidentialClientData(residentialData.Rows[0]["id"].ToString());
html += CL.buildResidentialHTML(residentialData);
}
catch (Exception e)
{
CL.getLog("error while getting data for popip in resdential_drill_log " + e);
return(html);
}
return(html);
}
public bool checkVerificationCode(String verification_code)
{
bool status = false;
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
try
{
SqlCommand command;
string sql;
sql = "SELECT * FROM reset_password WHERE verification_code = @verification_code";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@verification_code", verification_code);
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
HttpContext.Current.Session["email"] = dataReader["email"].ToString();
break;
}
status = true;
}
else
{
status = false;
}
return(status);
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
cnn.Close();
return(false);
}
cnn.Close();
return(status);
}
public string uploadFile()
{
string newFileName = "";
var CL = new commonLogic();
try
{
if (uploadSurvey != null && uploadSurvey.ContentLength > 0)
{
// extract only the filename
newFileName = Path.GetFileNameWithoutExtension(uploadSurvey.FileName) + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + Path.GetExtension(uploadSurvey.FileName);
var path = Path.Combine(HttpContext.Current.Server.MapPath("~/upload"), newFileName);
uploadSurvey.SaveAs(path);
}
}
catch (Exception e)
{
CL.getLog("error uploading file ");
return(newFileName);
}
return(newFileName);
}
public String getViewData(FormCollection form)
{
string uniqueID = form["uniqueID"];
string html = "";
var CL = new commonLogic();
try
{
var EM = new EmergencyModels();
DataTable emergencyLogData = EM.viewData(uniqueID);
DataTable emergencyClientData = EM.GetEmergencyClientData(emergencyLogData.Rows[0]["id"].ToString());
DataTable emergencyParticipantsData = EM.GetEmergencyParticipantsData(emergencyLogData.Rows[0]["id"].ToString());
html += CL.buildEmergencyHTML(emergencyLogData, emergencyClientData, emergencyParticipantsData);
}
catch (Exception e)
{
CL.getLog("error while getting data for popip in resdential_drill_log " + e);
return(html);
}
return(html);
}
internal bool insert()
{
string sql = "";
SqlCommand command;
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
string id = HttpContext.Current.Session["id"].ToString();
try
{
string uploadFileName = uploadFile();
string DrillType = string.Join(",", TypeOfDrill);
Durationofdrill = CL.setDurationOfDrill(DurationofdrillMinutes, DurationofdrillSeconds);
string drillStatus = CL.GetDrillStatus(reportForQuanterEnding, DateTimeofDrill, DrillType, Durationofdrill);
string drillShift = CL.GetDrillShift(DateTimeofDrill);
sql = "INSERT INTO resdential_drill_log(user_id,program_address,program_location,drill_shift,report_for_quarter_ending,drill_status,type_of_drill,evacuation,name_of_program,date_time_of_drill,staff_ratio,duration_of_drill,drill_scenario,building_exit,egress_was_blocked,secondary_location,staff_signature,staff_signature_date,supervisor_signature,supervisor_signature_date,added_date,drill_was_during,name_of_staff,upload_survey) ";
sql += " OUTPUT INSERTED.ID VALUES(@user_id,@ProgramAddress,@ProgramLocation,@drillShift,@reportForQuanterEnding,@DrillStatus,@TypeOfDrill,@Evacuation,@NameofProgram,@DateTimeofDrill, @StaffRatio, @Durationofdrill,@DrillScenario, @BuildingExit,@EgressWasBlocked, @SecondaryLocation,@StaffSignature, @StaffSignatureDate,@SupervisorSignature,@SupervisorSignatureDate,GETDATE(),@DrillWasDuring,@NameofStaff,@upload_survey) ";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@user_id", id);
command.Parameters.AddWithValue("@ProgramAddress", ProgramAddress);
command.Parameters.AddWithValue("@ProgramLocation", Location);
command.Parameters.AddWithValue("@drillShift", drillShift);
command.Parameters.AddWithValue("@reportForQuanterEnding", DBNull.Value);
command.Parameters.AddWithValue("@DrillStatus", drillStatus);
command.Parameters.AddWithValue("@TypeOfDrill", DrillType);
command.Parameters.AddWithValue("@Evacuation", Evacuation);
command.Parameters.AddWithValue("@NameofProgram", NameofProgram);
command.Parameters.AddWithValue("@DateTimeofDrill", DateTimeofDrill);
command.Parameters.AddWithValue("@StaffRatio", StaffRatio);
if (Durationofdrill == null)
{
command.Parameters.AddWithValue("@Durationofdrill", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@Durationofdrill", Durationofdrill);
}
command.Parameters.AddWithValue("@DrillScenario", DrillScenario);
command.Parameters.AddWithValue("@BuildingExit", BuildingExit);
command.Parameters.AddWithValue("@EgressWasBlocked", EgressWasBlocked);
command.Parameters.AddWithValue("@SecondaryLocation", SecondaryLocation);
command.Parameters.AddWithValue("@StaffSignature", StaffSignature);
command.Parameters.AddWithValue("@StaffSignatureDate", StaffSignatureDate);
command.Parameters.AddWithValue("@SupervisorSignature", SupervisorSignature);
command.Parameters.AddWithValue("@SupervisorSignatureDate", SupervisorSignatureDate);
command.Parameters.AddWithValue("@DrillWasDuring", DrillWasDuring);
command.Parameters.AddWithValue("@NameofStaff", NameofStaff);
command.Parameters.AddWithValue("@upload_survey", uploadFileName);
command.CommandType = CommandType.Text;
int newId = Convert.ToInt32(command.ExecuteScalar());
if (newId < 1)
{
CL.getLog("Fail to insert");
return(false);
}
bool RDclient = true;//ResidentialDrillClients(newId);
bool RDUniqueID = updateUniqueId(newId);
if (RDclient == false || RDUniqueID == false)
{
CL.getLog("Fail to insert and update other tables");
return(false);
}
cnn.Close();
return(true);
}
catch (Exception e)
{
CL.getLog("error while inserting records in resdential_drill_log " + e);
cnn.Close();
return(false);
}
}
public bool checkEmail()
{
bool status = false;
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
try
{
SqlCommand command;
string sql;
sql = "SELECT * FROM users WHERE email = @email";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@email", Email);
SqlDataReader dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
var ID = dataReader["id"].ToString();
var Email = dataReader["email"].ToString();
var FristName = dataReader["first_name"].ToString();
var LastName = dataReader["last_name"].ToString();
String verification_code = CL.GetUniqueKey(49);
cnn.Close();
SqlConnection con = CL.connect();
try
{
SqlCommand cmd;
string query = @"IF EXISTS(SELECT * FROM reset_password WHERE email = @email)
UPDATE reset_password
SET verification_code = @verification_code
WHERE email = @email
ELSE
INSERT INTO reset_password(email, verification_code) VALUES(@email, @verification_code);";
cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@email", Email);
cmd.Parameters.AddWithValue("@verification_code", verification_code);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
var Body = "Hi " + FristName + ",<br/><br/>Please <a href='http://*****:*****@chd.com", Email, "CHD password reset", Body);
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
con.Close();
return(false);
}
con.Close();
break;
}
status = true;
}
else
{
status = false;
}
return(status);
}
catch (Exception e)
{
CL.getLog("error while updating unique id to table" + e);
cnn.Close();
return(false);
}
cnn.Close();
return(status);
}
public Boolean insert()
{
var CL = new commonLogic();
SqlConnection cnn = CL.connect();
SqlCommand command;
String sql = "";
string uploadFileName = uploadFile();
string id = HttpContext.Current.Session["id"].ToString();
try
{
Durationofdrill = CL.setDurationOfDrill(DurationofdrillMinutes, DurationofdrillSeconds);
string DrillType = string.Join(",", TypeOfDrill);
string drillStatus = CL.GetDrillStatus(reportForQuanterEnding, DrillDate, DrillType, Durationofdrill);
string drillShift = "1";//As it is non-residential, it always be shift 1.
sql = "INSERT INTO emergency_drill_log (user_id,evacuation,duration_of_drill,drill_status,report_for_quarter_ending,drill_shift,site_address,name_of_program,program_location,drill_date,time_started,time_completed,table_top_drill,person_conducted,type_of_drill,program_participants_involved,ec_evcuate,sc_accounted,people_left,emergency_disaster,pp_enhance_safety,report_completed_name,completed_date,improvement_date,upload_survey,added_date) OUTPUT INSERTED.ID VALUES";
sql = sql + "(@user_id,@evacuation,@Durationofdrill,@drillStatus,@reportForQuanterEnding,@drillShift,@site_address,@NameofProgram,@Location,@drill_date,@time_started,@time_completed,@table_top_drill,@person_conducted,@type_of_drill,@program_participants_involved,@ec_evcuate,@sc_accounted,@people_left,@emergency_disaster,@pp_enhance_safety,@report_completed_name,@completed_date,@improvement_date,@upload_survey,GETDATE())";
command = new SqlCommand(sql, cnn);
command.Parameters.AddWithValue("@user_id", id);
command.Parameters.AddWithValue("@evacuation", Evacuation);
if (Durationofdrill == null)
{
command.Parameters.AddWithValue("@Durationofdrill", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@Durationofdrill", Durationofdrill);
}
command.Parameters.AddWithValue("@drillStatus", drillStatus);
command.Parameters.AddWithValue("@reportForQuanterEnding", DBNull.Value);
command.Parameters.AddWithValue("@drillShift", drillShift);
command.Parameters.AddWithValue("@site_address", ProgramAddress);
command.Parameters.AddWithValue("@NameofProgram", NameofProgram);
command.Parameters.AddWithValue("@Location", Location);
command.Parameters.AddWithValue("@drill_date", DrillDate);
command.Parameters.AddWithValue("@time_started", DBNull.Value);
command.Parameters.AddWithValue("@time_completed", DBNull.Value);
command.Parameters.AddWithValue("@table_top_drill", DBNull.Value);
command.Parameters.AddWithValue("@person_conducted", PersonConducted);
command.Parameters.AddWithValue("@type_of_drill", DrillType);
command.Parameters.AddWithValue("@program_participants_involved", ProgramParticipantsInvolved);
command.Parameters.AddWithValue("@ec_evcuate", ECEvcuate);
command.Parameters.AddWithValue("@sc_accounted", SCAccounted);
command.Parameters.AddWithValue("@people_left", PeopleLeft);
command.Parameters.AddWithValue("@emergency_disaster", EmergencyDisaster);
command.Parameters.AddWithValue("@pp_enhance_safety", PPEnhanceSafety);
command.Parameters.AddWithValue("@report_completed_name", ReportCompletedName);
command.Parameters.AddWithValue("@completed_date", CompletedDate);
command.Parameters.AddWithValue("@improvement_date", ImprovementDate);
command.Parameters.AddWithValue("@upload_survey", uploadFileName);
command.CommandType = CommandType.Text;
int newId = Convert.ToInt32(command.ExecuteScalar());
if (newId < 1)
{
CL.getLog("Fail to insert");
return(false);
}
cnn.Close();
bool EDparticipant = emergencyDrillParticipants(newId);
bool EDClients = true;
if (ProgramParticipantsInvolved == "YES")
{
EDClients = emergencyDrillClients(newId);
}
bool uniqueIdUpdate = updateUniqueId(newId);
if (EDClients == false || EDparticipant == false || uniqueIdUpdate == false)
{
CL.getLog("Fail to insert to other tables.");
return(false);
}
}
catch (Exception e)
{
CL.getLog("Fail to insert to emergency_drill_log " + Location + "<++>" + NameofProgram + "<++>" + ProgramAddress + "<++>" + Durationofdrill + "<++>" + reportForQuanterEnding + "<++>" + e);
return(false);
}
return(true);
}