public string updateAdminPass(string old)
{
try
{
string query = "update userLogin set Password='******' where Username='******' and Type=" + 2 + " and Password='******'";
dbConnect obj = new dbConnect();
string result = obj.executeNonQuery(query);
if (result.Contains("Occured") || result.Contains("Exception") || result.Contains("Error"))
{
return(result);
}
else if (Convert.ToInt32(result) == 1)
{
return("Password Changed");
}
else
{
return("Incorect Password");
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "user-updateAdminPass");
return("Exception Occured");
}
}
public static int inboxCountAdmin()
{
int count = 0;
try
{
string query = "select count(id) from Files where Files.[To]='Admin' and Seen='False'";
dbConnect obj = new dbConnect();
DataSet result = obj.executeSelectStatemant(query);
if (!result.HasErrors && result.Tables[0].Rows.Count > 0)
{
count += Convert.ToInt32(result.Tables[0].Rows[0][0]);
}
query = "select count(id) from Messages where Messages.[To]='Admin' and Seen='False'";
result.Dispose();
result = obj.executeSelectStatemant(query);
if (!result.HasErrors && result.Tables[0].Rows.Count > 0)
{
count += Convert.ToInt32(result.Tables[0].Rows[0][0]);
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "AjaxRequests-inboxCount");
}
return(count);
}
protected void save_encoded_Click(object sender, EventArgs e)
{
string map = HiddenMap.Value;
string mapCenter = HiddenCenter.Value;
string mapZoom = HiddenZoom.Value;
try
{
if (map != "")
{
string query = "update Applicant set GoogleMap='" + map + "', MapCenter='" + mapCenter + "' , MapZoom='" + mapZoom + "' where Applicant_ID=" + applicantID;
dbConnect obj = new dbConnect();
string result = obj.executeNonQuery(query);
if (!result.Contains("Occured"))
{
Response.Write("<div class='notification green'>Success</div>");
}
else
{
Response.Write("<div class='notification red'>" + result + "</div>");
throw new Exception("Could not save maps");
}
}
else
{
Response.Write("<div class='notification red'>No Data</div>");
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant.aspx.cs-save_encoded_Click");
}
}
public bool validate(string query)
{
try
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
con.Close();
return(true);
}
else
{
con.Close();
return(false);
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "dbConnect-validate");
con.Close();
return(false);
}
}
public DataSet retrieveAppData(int deptid)
{
try
{
//string query = "select u.Datetime,u.Name,a.Address,a.Applicant_ID,a.Oil_Company from applicant a inner join userLogin u on a.Applicant_ID=u.Id where Applicant_ID not in (select Applicant_ID from noc where deptid=" + DeptID + ") and Applicant_ID !=0";
string query = "select v.[" + deptid + "],u.Datetime,u.Name,a.LOC_Petrol_Pump,a.Applicant_ID,a.Oil_Company from applicant a inner join userLogin u on a.Applicant_ID=u.Id inner join noc_view v on v.applicant_ID=a.Applicant_ID inner join applicantDepts ad on a.applicant_ID=ad.Applicant_ID where a.Applicant_ID !=0 and a.authorized='True' and (ad.[1]=" + deptid + " or ad.[2]=" + deptid + " or ad.[3]=" + deptid + " or ad.[4]=" + deptid + " or ad.[5]=" + deptid + " or ad.[6]=" + deptid + " or ad.[7]=" + deptid + " or ad.[8]=" + deptid + " or ad.[9]=" + deptid + " or ad.[10]=" + deptid + " or ad.[11]=" + deptid + " ) order by datetime desc";
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
if (dset.Tables[0].Rows.Count > 0)
{
return(dset);
}
else
{
// string query2 = "select u.Name,a.Address,a.Applicant_ID,a.Oil_Company from applicant a inner join userLogin u on a.Applicant_ID=u.Id";
// DataSet dset2 = obj.executeSelectStatemant(query2);
// if (dset2.Tables[0].Rows.Count > 0)
// {
// return dset2;
// }
// else
// {
return(dset);
//}
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-retrieveAppData");
return(new DataSet());
}
}
public DataSet retrieveData()
{
try
{
string query = "select Id from userLogin where Username='******'";
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
if (dset.Tables[0].Rows.Count > 0)
{
ApplicantID = Convert.ToInt32(dset.Tables[0].Rows[0]["Id"]);
string query2 = "select * from Applicant where Applicant_ID=" + Convert.ToInt32(dset.Tables[0].Rows[0]["Id"]);
return(obj.executeSelectStatemant(query2));
}
else
{
throw new Exception();
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-retrieveData");
return(new DataSet());
}
}
protected void LinkButtonMessageReminder_Click(object sender, EventArgs e)
{
try
{
Dept obj = new Dept();
string message = TextBoxMessage.Text.Trim();
TextBoxMessage.Text = "";
string to = DropDownListDeptList1.SelectedValue;
obj.sendMessage("Admin", message, "Dept-" + to);
//Email mail = new Email();
//string query = "select email from departments where DeptId=" + to;
//dbConnect db = new dbConnect();
//System.Data.DataSet dset = db.executeSelectStatemant(query);
//string mailId = dset.Tables[0].Rows[0][0].ToString();
//mail.SendEmail(mailId, "Reminder Message", message);
string a = "alert('Message Sent!');";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", a, true);
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "admin-LinkButtonMessageReminder_Click");
string a = "alert('Exception Occured!');";
ScriptManager.RegisterClientScriptBlock((sender as Control), this.GetType(), "alert", a, true);
}
}
public string executeNonQuery(string query)
{
try
{
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
return(rowsAffected.ToString());
}
catch (Exception ex)
{
if (ex.Message.Contains("UNIQUE KEY") || ex.Message.Contains("unique key"))
{
return("Unique Key");
}
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "dbConnect-executeNonQuery");
con.Close();
return("Exception Occured");
}
}
public string deleteDeptFromNOC_View()
{
try
{
//string query = "alter table NOC_View alter column NOC_View[" + DeptID + "] drop default";
string query = "select name from sys.default_constraints where parent_object_id = object_id('NOC_VIEW') AND type = 'D' AND parent_column_id = (select column_id from sys.columns where object_id = object_id('NOC_VIEW') and name = '" + DeptID + "')";
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
if (dset.Tables[0].Rows.Count > 0)
{
query = "alter table NOC_View drop constraint " + dset.Tables[0].Rows[0][0].ToString();
string result = obj.executeNonQuery(query);
if (!result.Contains("Occured"))
{
query = "alter table NOC_View drop column [" + DeptID + "]";
return(obj.executeNonQuery(query));
}
else
{
return("Error Occured");
}
}
else
{
return("Error Occured");
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-deleteDeptFromNOC_View");
return("Exception Occured");
}
}
public string deleteDeptFromUserLogin()
{
try
{
string query = "select Username from Departments where DeptId=" + DeptID;
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
if (dset.Tables[0].Rows.Count > 0)
{
string usn = dset.Tables[0].Rows[0][0].ToString();
query = "delete from userLogin where Username='******' and Type=3";
return(obj.executeNonQuery(query));
}
else
{
return("Error Occured");
}
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-deleteDeptFromUserLogin");
return("Exception Occured");
}
}
public string saveSitePlan(string site)
{
try
{
string query = "update Applicant set Site_Plan='" + site + "' where Applicant_ID=" + ApplicantID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveSitePlan");
return("Exception Occured");
}
}
public string insertApplicantId()
{
try
{
string query = "insert into Applicant(Applicant_ID) Values(" + ApplicantID + ")";
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-insertApplicantId");
return("Exception Occured");
}
}
public string saveData(string oilCompany, string applicantName, string swd, string address, string subdivision, string district, string loc, int app)
{
try
{
string query = "update Applicant set Oil_Company ='" + oilCompany + "',Applicant_Name='" + applicantName + "',SWD='" + swd + "',Address='" + address + "',Sub_Division='" + subdivision + "',District='" + district + "',LOC_Petrol_Pump='" + loc + "' where Applicant_ID=" + app;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveData");
return("Exception Occured");
}
}
public string addAppIDtoNOC_View(int appid)
{
try
{
string query = "insert into NOC_View(Applicant_ID) Values(" + appid + ")";
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveFard");
return("Exception Occured");
}
}
public string registerApplicant()
{
try
{
string query = "insert into userLogin (Username,Password,Type,Name,Contact,Datetime) Values('" + Username + "','" + encryptedPass + "'," + 1 + ",'" + Name + "'," + Contact + ",'" + DateTime.Now + "')";
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "user-registerApplicant");
return("Exception Occured");
}
}
public string saveLOE(string LOE)
{
try
{
string query = "update Applicant set Letter_of_Entitlement='" + LOE + "' where Applicant_ID=" + ApplicantID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveLOE");
return("Exception Occured");
}
}
public DataSet retrieveData(int id)
{
try
{
string query2 = "select * from Applicant where Applicant_ID=" + id;
dbConnect obj = new dbConnect();
return(obj.executeSelectStatemant(query2));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-retrieveData");
return(new DataSet());
}
}
public string updateNocView()
{
try
{
string query = "update NOC_View set [" + DeptID + "]='" + true + "' where Applicant_ID=" + AppID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-updateNocView");
return("Exception Occured");
}
}
public string uploadNOC(string NOC)
{
try
{
string query = "insert into NOC Values(" + DeptID + ",'" + NOC + "'," + AppID + ",'" + DateTime.Now + "')";
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-uploadNOC");
return("Exception Occured");
}
}
public string deleteDeptFromDepartments()
{
try
{
string query = "delete from Departments where DeptId=" + DeptID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-deleteDeptFromDepartments");
return("Exception Occured");
}
}
public string uploadFile(string from, string filePath, string to)
{
try
{
string query = "insert into Files values('" + from + "','" + filePath + "','" + to + "','" + DateTime.Now + "')";
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-uploadFile");
return("Exception Occured");
}
}
public string saveAffidavit(string affi)
{
try
{
string query = "update Applicant set Affidavit='" + affi + "' where Applicant_ID=" + ApplicantID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveAffidavit");
return("Exception Occured");
}
}
public DataSet viewNOC_Status(int id)
{
try
{
string query = "SELECT n.Id, n.Applicant_ID, n.[1], n.[2], n.[3], n.[4], n.[5], n.[6], n.[7], n.[8], n.[9], n.[10], n.[11], userLogin.Username, userLogin.Name,userLogin.Datetime, userLogin.Contact, userLogin.Id AS Expr1 FROM NOC_View AS n INNER JOIN userLogin ON userLogin.Id = n.Applicant_ID AND userLogin.Id =" + id;
dbConnect obj = new dbConnect();
return(obj.executeSelectStatemant(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveFard");
return(new DataSet());
}
}
public string saveFard(string fard)
{
try
{
string query = "update Applicant set Fard='" + fard + "' where Applicant_ID=" + ApplicantID;
dbConnect obj = new dbConnect();
return(obj.executeNonQuery(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Applicant-saveFard");
return("Exception Occured");
}
}
public bool autenticateUser()
{
try
{
string query = "select * from userLogin where Username='******'and Password='******'";
dbConnect obj = new dbConnect();
return(obj.validate(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "user-authenticateUser");
return(false);
}
}
public DataSet NoOfNOCnotGiven(int DeptId, string from, string to)
{
try
{
string query = "select userLogin.Name,Applicant.LOC_Petrol_Pump,userLogin.Datetime,Applicant.Applicant_ID,Applicant.Oil_Company from userLogin inner join Applicant on userLogin.Id=Applicant.Applicant_ID where ( userLogin.Datetime>='" + from + "' and userLogin.Datetime<='" + to + "' and Applicant.Applicant_ID in (select NOC_View.Applicant_ID from NOC_View where NOC_View.[" + DeptId + "]=0))";
dbConnect obj = new dbConnect();
return(obj.executeSelectStatemant(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "home-NoOfNOCnotGiven");
return(new DataSet());
}
}
public DataSet listOFApp(string from, string to)
{
try
{
string query = "SELECT n.[1], n.[2], n.[3], n.[4], n.[5], n.[6], n.[7], n.[8], n.[9], n.[10], n.[11], Applicant.Applicant_ID,userLogin.Name,userLogin.Datetime, Applicant.LOC_Petrol_Pump, Applicant.Oil_Company FROM userLogin INNER JOIN Applicant ON userLogin.Id = Applicant.Applicant_ID inner join NOC_View n on n.Applicant_ID=Applicant.Applicant_ID WHERE (Applicant.Complete = 1) AND (userLogin.Datetime >= '" + from + "') AND (userLogin.Datetime <='" + to + "')";
dbConnect obj = new dbConnect();
return(obj.executeSelectStatemant(query));
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "home-listOFApp");
return(new DataSet());
}
}
public string NoOfNOCgiven(int deptId, string from, string to)
{
try
{
string query = "select NOC.Id from NOC INNER JOIN userLogin on userLogin.Id=NOC.Applicant_ID where (deptId=" + deptId + ") and (userLogin.Datetime>='" + from + "' and userLogin.Datetime<='" + to + "')";
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
return(dset.Tables[0].Rows.Count.ToString());
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "home-NoOfNOCgiven");
return("Exception Occured");
}
}
public string NOCApplications(string from, string to)
{
try
{
string query = "select Applicant_ID from NOC_View INNER JOIN userLogin ON userLogin.Id=NOC_View.Applicant_ID where (userLogin.Datetime>='" + from + "' and userLogin.Datetime<='" + to + "') and ( [1]=" + 1 + " and [2]=" + 1 + " and [3]=" + 1 + " and [4]=" + 1 + " and [5]=" + 1 + " and [6]=" + 1 + " and [7]=" + 1 + " and [8]=" + 1 + " and [9]=" + 1 + " and [10]=" + 1 + " and [11]=" + 1 + ")";
dbConnect obj = new dbConnect();
DataSet dset = obj.executeSelectStatemant(query);
return(dset.Tables[0].Rows.Count.ToString());
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "home-NOCApplications");
return("Exception Occured");
}
}
public string sendMessage(string from, string message, string to)
{
try
{
string query = "insert into Messages values('" + from + "','" + message + "','" + to + "','" + DateTime.Now + "',0)";
dbConnect obj = new dbConnect();
string result = obj.executeNonQuery(query);
return(result);
}
catch (Exception ex)
{
writeException obj = new writeException();
obj.WriteExceptionToFile(ex, "Dept-sendMessage");
return("Exception Occured");
}
}
