示例#1
0
 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;
            }
        }
示例#3
0
        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 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;
     }
 }
示例#5
0
        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";
                }
            }
        }