private void Page_Load(object sender, System.EventArgs e)
{
//checking whether user is logged in
if (Session["user_id"] == null)
{
Response.Redirect("errorPage.aspx");
}
//generating avalilable linemen number
if (!Page.IsPostBack)
{
cboAssignmentLinemanNum.Items.Clear();
strCommandString = "select lineman_num from complaints_linemen_tb where status='AVAILABLE'";
cboAssignmentLinemanNum.DataSource = (ComplaintsDB.FetchDataSet(strConnectionString, strCommandString, "complaints_linemen_tb")).Tables ["complaints_linemen_tb"];
cboAssignmentLinemanNum.DataMember = "complaints_linemen_tb";
cboAssignmentLinemanNum.DataTextField = "lineman_num";
cboAssignmentLinemanNum.DataBind();
if (cboAssignmentLinemanNum.Items.Count == 0)
{
//making required fields visible and invisible
cboAssignmentLinemanNum.Enabled = false;
lblAssignmentMsg.Text = "No line man is currently available. Enter appropriate delay reason";
lblAssignmentMsg.Visible = true;
lblAssignmentDelayReason.Visible = true;
txtAssignmentDelayReason.Visible = true;
txtAssignmentDelayReasonReqdValidator.Enabled = true;
}
else
{
cboAssignmentLinemanNum.Items.Insert(0, "select");
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
//checking whether user is logged in or not
if (Session["user_id"] == null)
{
Response.Redirect("errorPage.aspx");
}
//auto generating refernce number
strCommandString = "select count(ref_num)+1 from complaints_new_tb";
txtRefNum.Text = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
}
private void cmdCloseComplaintGenerate_Click(object sender, System.EventArgs e)
{
try
{
/*checking whether given reference number exists or not */
strCommandString = "select ref_num from complaints_new_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
bool blnResult = ComplaintsDB.CheckForExistance(strConnectionString, strCommandString);
if (!blnResult)
{
lblCloseComplaintRefNumMsg.Text = "Entered reference number doesn't exist";
return;
}
/* checking whether complaint with given reference number is tried for assignment or not */
strCommandString = "select ref_num from complaints_assignment_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
blnResult = ComplaintsDB.CheckForExistance(strConnectionString, strCommandString);
if (!blnResult)
{
lblCloseComplaintRefNumMsg.Text = "Complaint for given reference number is not yet assigned ";
return;
}
/*checking status of complaint with given reference number */
strCommandString = "select status from complaints_assignment_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
string strStatus = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
if (strStatus.Equals("PENDING"))
{
lblCloseComplaintRefNumMsg.Text = "Complaint for given reference number is not yet assigned bcz of non availability of linemen";
return;
}
else if (strStatus.Equals("CLOSED"))
{
lblCloseComplaintRefNumMsg.Text = "complaint already closed";
return;
}
/*retrieving assigned date and delay reason for entered reference number */
strCommandString = "select assign_date,delay_reason from complaints_assignment_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
DataSet dataSet = ComplaintsDB.FetchDataSet(strConnectionString, strCommandString, "complaints_assignment_tb");
txtCloseComplaintAssignedDate.Text = dataSet.Tables ["complaints_assignment_tb"].Rows [0]["assign_date"].ToString();
txtCloseComplaintDelayReason.Text = dataSet.Tables ["complaints_assignment_tb"].Rows [0]["delay_reason"].ToString();
cmdCloseComplaintClose.Enabled = true;
}
catch (Exception exception)
{
lblCloseComplaintClosedDateMsg.Text = "insertion failed:" + exception.Message.ToString();
return;
}
}
private void cmdNewFormSubmit_Click(object sender, System.EventArgs e)
{
bool blnResult = true;
int intFlag;
/*combining hour and minute together*/
txtGivenTime.Text = txtGivenTimeHour.Text + ":" + txtGivenTimeMinute.Text;
/*checking whether consumer details available or not */
strCommandString = "select count(meter_num) from complaints_consumers_tb where meter_num='" + txtConsumerNum.Text + "'";
intFlag = int.Parse(ComplaintsDB.FetchScalar(strConnectionString, strCommandString));
if (intFlag == 0) /*new consumer - inserting data into consumers table*/
{
strCommandString = "insert into complaints_consumers_tb values('" + txtConsumerNum.Text + "','" + txtConsumerName.Text + "','" + txtPhoneNum.Text + "','" + txtArea.Text + "','" + txtCity.Text + "','" + cboState.Items [cboState.SelectedIndex] + "')";
try
{
blnResult = ComplaintsDB.FetchBoolean(strConnectionString, strCommandString);
}
catch (Exception exception)
{
lblNewComplaintMsg.Text = "Error while insertion " + exception.Message.ToString();
return;
}
}
if (blnResult) //previous insertion is successful
{
//checking whether consumer complaint already exists
strCommandString = "select count(consumer_num) from complaints_new_tb,complaints_assignment_tb where consumer_num='" + txtConsumerNum.Text + "'";
//and status not in ('CLOSED',null) and complaints_new_tb.ref_num=complaints_assignment_tb.ref_num"
intFlag = int.Parse(ComplaintsDB.FetchScalar(strConnectionString, strCommandString));
if (intFlag == 1)
{
lblNewComplaintMsg.Text = "Complaint is under processing";
Response.Redirect("information.html");
}
/* inserting complaint details into new complaints table*/
strCommandString = "insert into complaints_new_tb values('" + txtRefNum.Text + "','" + txtGivenDate.Text + "','" + txtGivenTime.Text + "','" + cboNature.Items [cboNature.SelectedIndex] + "','" + cboFaultAt.Items [cboFaultAt.SelectedIndex] + "','" + txtConsumerNum.Text + "','" + Session["user_id"].ToString() + "')";
blnResult = ComplaintsDB.FetchBoolean(strConnectionString, strCommandString);
if (blnResult) /*insertion successfull*/
{
Response.Redirect("insertionSuccess.aspx");
}
}
else /* insertion failed*/
{
lblNewComplaintMsg.Text = "Insertion failed";
}
}
private void cmdJEViewReprt_Click(object sender, System.EventArgs e)
{
strCommandString = "select * from complaints_report_mgr_vw";
dataGridReportJE.Visible = true;
DataSet dataSet = ComplaintsDB.FetchDataSet(strConnectionString, strCommandString, "complaints_report_mgr_vw");
for (int intTraverser = 0; intTraverser < dataSet.Tables [0].Rows.Count; intTraverser++)
{
if (dataSet.Tables [0].Rows [intTraverser]["Complaint Status"].ToString().Equals("PENDING") | dataSet.Tables [0].Rows [intTraverser]["Complaint Status"].ToString().Equals("PENDING"))
{
dataSet.Tables [0].Rows [intTraverser]["Closed Date"] = null;
dataSet.Tables [0].Rows [intTraverser]["Closed Time"] = null;
}
}
dataGridReportJE.DataSource = dataSet;
dataGridReportJE.DataBind();
}
private void cmdViewRpt_Click(object sender, System.EventArgs e)
{
//checking whether dates entered
if (txtStartDate.Text.Equals("") | txtEndDate.Text.Equals(""))
{
lblReportStaffMsg.Text = "Select dates";
return;
}
try
{
int intResult;
//checking whether range selected is valid
intResult = DateTime.Compare(Convert.ToDateTime(txtStartDate.Text), Convert.ToDateTime(txtEndDate.Text));
if (intResult > 0)
{
lblReportStaffMsg.Text = "invalid date range";
return;
}
/* query to fetch results from view */
strCommandString = "select [Consumer Name],[Complaint Nature],[Given Date],[Given Time],[Closed Date],[Closed Time],[Lineman Number],[Complaint Status],[Reason for Delay] from complaints_report_staff_vw where staff_id='" + Session ["user_id"].ToString() + "' and (cast([Given Date] as datetime)>'" + txtStartDate.Text + "' and cast([Given Date] as datetime)<'" + txtEndDate.Text + "')";
DataSet dataSet = ComplaintsDB.FetchDataSet(strConnectionString, strCommandString, "complaints_report_staff_vw");
/* updating closed date and times to null for which complaints or not closed */
for (int intTraverser = 0; intTraverser < dataSet.Tables [0].Rows.Count; intTraverser++)
{
if (dataSet.Tables [0].Rows [intTraverser]["Complaint Status"].ToString().Equals("PENDING") | dataSet.Tables [0].Rows [intTraverser]["Complaint Status"].ToString().Equals("PENDING"))
{
dataSet.Tables [0].Rows [intTraverser]["Closed Date"] = null;
dataSet.Tables [0].Rows [intTraverser]["Closed Time"] = null;
}
}
/* binding data to datagrid */
dataGridReportStaff.DataSource = dataSet;
dataGridReportStaff.DataBind();
dataGridReportStaff.Visible = true;
lblReportStaffMsg.Text = "";
}
catch (Exception exception)
{
lblReportStaffMsg.Text = "Database error: " + exception.Message.ToString();
return;
}
}
private void cmdLogin_Click(object sender, System.EventArgs e)
{
/*checking whether user is valid or not based on credentials entered*/
strCommandString = "select user_id from complaints_users_tb where user_id='" + txtUserID.Text + "' and passwd='" + txtPasswd.Text + "'";
bool blnResult = ComplaintsDB.CheckForExistance(strConnectionString, strCommandString);
if (blnResult) /*user exists */
{
/*creating session for user*/
Session ["user_id"] = txtUserID.Text;
if (int.Parse(txtUserID.Text) > 100)
{
Response.Redirect("Welcome.html");
}
else
{
Response.Redirect("Welcome_JE.aspx");
}
}
else /*user doesn't exist*/
{
Response.Write("Invalid User");
}
}
private void cmdCloseComplaintClose_Click(object sender, System.EventArgs e)
{
/* checking whether closed date is null */
if (txtCloseComplaintClosedDate.Text.Equals(""))
{
lblCloseComplaintClosedDateMsg.Text = "Select closed date";
return;
}
try
{
/* comparing complaint assigned date and date to be closed */
int intFlag = ComplaintsDB.CompareDates(strConnectionString, strCommandString, txtCloseComplaintRefNum.Text, txtCloseComplaintAssignedDate.Text, txtCloseComplaintClosedDate.Text, "complaints_validateDates_proc");
if (intFlag < 0) /* invalid date of closing */
{
lblCloseComplaintClosedDateMsg.Text = "Date clsoed is before complaint assigned.";
return;
}
/* checking whether closed time is null */
if (txtCloseComplaintClosedTimeHour.Text.Trim().Equals("") || txtCloseComplaintClosedTimeMinute.Text.Trim().Equals(""))
{
lblCloseComplaintClosedDateMsg.Text = "Enter valid closed time";
return;
}
/* combining hour and minute together */
txtCloseComplaintClosedTime.Text = txtCloseComplaintClosedTimeHour.Text + ":" + txtCloseComplaintClosedTimeMinute.Text;
lblCloseComplaintClosedDateMsg.Text = "";
/* calculating time taken in hrs */
/*getting complaint given date and time */
strCommandString = "select given_date,given_time from complaints_new_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
DataSet dataSet = ComplaintsDB.FetchDataSet(strConnectionString, strCommandString, "complaints_new_tb");
string strGiven_date_string = dataSet.Tables ["complaints_new_tb"].Rows [0]["given_date"].ToString();
string strGiven_time_string = dataSet.Tables ["complaints_new_tb"].Rows [0]["given_time"].ToString();
/* combining complaint given date and time */
string strGiven_date = strGiven_date_string + " " + strGiven_time_string;
/* combining complaint closed date and time */
string strClosed_date = txtCloseComplaintClosedDate.Text + " " + txtCloseComplaintClosedTime.Text;
/* retrieving difference between complaint given date and closed date in hours */
intFlag = ComplaintsDB.CompareDates(strConnectionString, strCommandString, txtCloseComplaintRefNum.Text, strGiven_date, strClosed_date, "complaints_validateDatesWithTimes_proc");
txtCloseComplaintTimeTaken.Text = intFlag.ToString();
if (intFlag < 0)
{
lblCloseComplaintClosedDateMsg.Text = "Invalid times: closed time is before given time as on same day";
return;
}
// checcking fault-at
int intIncentive;
strCommandString = "select falut_at from complaints_new_tb where ref_num='" + txtCloseComplaintRefNum.Text + "'";
string strFaultAt = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
if (strFaultAt.Equals("POLE"))
{
/* retrieving previous incentive value */
strCommandString = "select incentive_earned from complaints_linemen_tb,complaints_assignment_tb where complaints_assignment_tb.ref_num='" + txtCloseComplaintRefNum.Text + "' and complaints_assignment_tb.lineman_num=complaints_linemen_tb.lineman_num ";
string strIncentive = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
intIncentive = int.Parse(strIncentive);
if (intFlag < 60)
{
intIncentive += 5;
}
}
else
{
intIncentive = 0;
}
string strIncentive_earned = intIncentive.ToString();
/* qry to update incentive and status of lineman to 'AVAILABLE' for given reference number */
strCommandString = "update complaints_linemen_tb set incentive_earned='" + strIncentive_earned + "',status='AVAILABLE' where lineman_num = (select lineman_num from complaints_assignment_tb where ref_num='" + txtCloseComplaintRefNum.Text + "')";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
/* query to update complaint status to 'CLOSED' */
strCommandString = "update complaints_assignment_tb set status='CLOSED',delay_reason='delayed as no lineman available' where ref_num='" + txtCloseComplaintRefNum.Text + "'";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
/* inserting complaints closed details into close_list table */
strCommandString = "insert into complaints_close_list_tb values('" + txtCloseComplaintRefNum.Text + "','" + txtCloseComplaintClosedDate.Text + "','" + txtCloseComplaintClosedTime.Text + "','" + txtCloseComplaintTimeTaken.Text + "')";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
lblCloseComplaintClosedDateMsg.Text = "";
}
catch (Exception exception)
{
lblCloseComplaintClosedDateMsg.Text = "Database error: insertion failed-" + exception.Message.ToString();
return;
}
}
private void cmdAssignmentAssign_Click(object sender, System.EventArgs e)
{
/* checking whether given reference number is existing or not*/
strCommandString = "select ref_num from complaints_new_tb where ref_num='" + txtAssignmentRefNum.Text + "'";
bool blnResult = ComplaintsDB.CheckForExistance(strConnectionString, strCommandString);
if (!blnResult)
{
lblAssignmentRefNumMsg.Text = "Entered complaint reference number doesn't exist";
return;
}
/* checking whether assign date has choosen or not */
if (cboAssignmentLinemanNum.Items.Count > 0)
{
if (txtAssignmentAssignDate.Text.ToString().Trim().Equals(""))
{
lblAssignmentAssignDateMsg.Text = "Select assign date";
return;
}
/* comparing complaint given date and date to be assigned */
/*fetching complaint given date for entered refernce number */
lblAssignmentRefNumMsg.Text = "";
strCommandString = "select given_date from complaints_new_tb where ref_num='" + txtAssignmentRefNum.Text + "'";
string strDate_first_string = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
int intFlag = ComplaintsDB.CompareDates(strConnectionString, strCommandString, txtAssignmentRefNum.Text, strDate_first_string, txtAssignmentAssignDate.Text, "complaints_validateDates_proc");
if (intFlag < 0) /* invalid assign date */
{
lblAssignmentAssignDateMsg.Text = "date to be assigned is before complaint given date";
return;
}
}
/* checking whether complaint with given reference number is considered for assignment */
strCommandString = "select count(ref_num) from complaints_assignment_tb where ref_num='" + txtAssignmentRefNum.Text + "'";
int intCount = ComplaintsDB.CheckForDuplicates(strConnectionString, strCommandString);
if (intCount == 1) /*reference number existing and now cheking for 'PENDING' status */
{
if (cboAssignmentLinemanNum.Items.Count != 0) /* linemen available for assignment */
{
try
{
/* if no lineman number is selected */
if (cboAssignmentLinemanNum.SelectedIndex <= 0)
{
Label1.Text = "select lineman number";
return;
}
/* checking whether complaint with given reference number is already assigned or not*/
/* query for getting status for given reference number */
strCommandString = "select status from complaints_assignment_tb where ref_num='" + txtAssignmentRefNum.Text + "'";
/* getting status for given reference number */
string strStatus = ComplaintsDB.FetchScalar(strConnectionString, strCommandString);
if (strStatus.Equals("ALLOTTED") | strStatus.Equals("CLOSED"))
{
lblAssignmentMsg.Text = "Already assigned";
return;
}
/* not yet assigned */
/* assigning work to selected lineman and updating required fileds in assignment table */
strCommandString = "update complaints_assignment_tb set lineman_num='" + cboAssignmentLinemanNum.Items [cboAssignmentLinemanNum.SelectedIndex] + "',status='ALLOTTED',assign_date='" + txtAssignmentAssignDate.Text + "',delay_reason='delayed-no lineman is available' where ref_num='" + txtAssignmentRefNum.Text + "'";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
/* updating the status of selected lineman to 'ALLOTTED' */
strCommandString = "update complaints_linemen_tb set status='ALLOTTED' where lineman_num='" + cboAssignmentLinemanNum.Items [cboAssignmentLinemanNum.SelectedIndex] + "'";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
Response.Redirect("assignmentSuccess.aspx");
}
catch (Exception exception)
{
lblAssignmentMsg.Text = "insertion failed" + exception.Message.ToString();
return;
}
}
else
{
lblAssignmentMsg.Text = "This complaint is already in pending status";
return;
}
}
else /* not already assigned */
{
if (cboAssignmentLinemanNum.Items.Count == 0) /* no lineman available to allot */
{
// checking whether delay reason is entered
if (txtAssignmentDelayReason.Text.Trim().Equals(""))
{
lblAssignmentMsg.Text = "enter delay reason";
return;
}
/* query to perform insertion when no lineman is available */
strCommandString = "insert into complaints_assignment_tb values('" + txtAssignmentRefNum.Text + "',null,null,'" + txtAssignmentDelayReason.Text + "','PENDING')";
}
else /* lineman is available to allot */
{
try
{
if (cboAssignmentLinemanNum.SelectedIndex < 1) /* if no lineman number is selected */
{
Label1.Text = "select lineman number";
return;
}
/*updating selected lineman status to 'ALLOTTED' */
strCommandString = "update complaints_linemen_tb set status='ALLOTTED' where lineman_num='" + cboAssignmentLinemanNum.Items [cboAssignmentLinemanNum.SelectedIndex] + "'";
ComplaintsDB.ChangeStatus(strConnectionString, strCommandString);
/*query to perform insertion when lineman is available */
strCommandString = "insert into complaints_assignment_tb values('" + txtAssignmentRefNum.Text + "','" + txtAssignmentAssignDate.Text + "','" + cboAssignmentLinemanNum.Items [cboAssignmentLinemanNum.SelectedIndex] + "',null,'ALLOTTED')";
}
catch (Exception exception)
{
lblAssignmentMsg.Text = "error while insertion:" + exception.Message.ToString();
return;
}
}
/* performing insertion */
blnResult = ComplaintsDB.FetchBoolean(strConnectionString, strCommandString);
if (blnResult)
{
Response.Redirect("assignmentSuccess.aspx");
}
else
{
Label1.Text = "failed";
}
}
}