protected void Page_Load(object sender, EventArgs e) { //Label1.Visible = false; string updatedwntimeonload = @"Update IncidentsNew1 set downtime=cast((cast(cast(getdate() as float) - cast(opendate as float) as int) * 24) + datepart(hh, getdate() - opendate) as varchar(10)) + ':' + right('0' + cast(datepart(mi, getdate() - opendate) as varchar(2)), 2) where (callstatus='OPEN' or callstatus='DISPATCHED' or callstatus='RE-OPEN')"; obj.NonExecuteQuery(updatedwntimeonload); string upddwntime = @"Update IncidentsNew1 set Downtime=(Select CONVERT(varchar(5),datediff(s,opendate,resolveddate)/3600) + ':' + CONVERT(varchar(5),datediff(s,opendate,resolveddate)/3600%60)),DTStatus='SYN' where (CallStatus='RESOLVED' or CallStatus='CLOSE') and DTStatus='CRE'"; obj.NonExecuteQuery(upddwntime); //update query to replace '-' in downtime when it is calculated in negative //downtime is calculated in negative when opendate from application is greater than the current date string upddwntimenew = @"Update IncidentsNew1 set Downtime=REPLACE(downtime,'-','') where downtime like '%-%'"; obj.NonExecuteQuery(upddwntimenew); if (!Page.IsPostBack) { //status = ""; chkAll.Checked = true; for (int i = 0; i < ddcalltype.Items.Count; i++) { ddcalltype.Items[i].Selected = true; } ////ddcalltype1.Text = status.Remove("'"); ////Response.Write(ddcalltype1.Text); lblTotalSelectedEmailCount.Text = ddcalltype.Items.Count.ToString() + " item(s) selected"; //StartDate_CalendarExtender.EndDate = Convert.ToDateTime(DateTime.Now.Date.ToString("MM/dd/yyyy")); //EndDate_CalendarExtender.EndDate = Convert.ToDateTime(DateTime.Now.Date.ToString("MM/dd/yyyy")); string sessrole = ""; try { sessrole = Session["sess_role"].ToString(); } catch (Exception ee) { Response.Redirect("~/Login.aspx"); } dd_faultC.Items.Add("ALL"); dd_faultC.Items.FindByText("ALL").Value = "%"; string query1x = "Select distinct [faultcode] FROM [faultcode]"; obj.BindDropDown(dd_faultC, query1x); try { if (Request.QueryString["fault"].ToString() != "") { dd_faultC.Items.FindByText(Request.QueryString["fault"].ToString()).Selected = true; if (Request.QueryString["TAT"].ToString() == "true") { SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("where", "where CONVERT(int,REPLACE(Downtime,':',''))<CONVERT(int,REPLACE(f.TAT,':','')) and "); SqlDataSource1.DataBind(); } else if (Request.QueryString["TAT"].ToString() == "false") { SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("where", "where CONVERT(int,REPLACE(Downtime,':',''))>CONVERT(int,REPLACE(f.TAT,':','')) and "); SqlDataSource1.DataBind(); } else if (Request.QueryString["TAT"].ToString() == "both") { } } } catch { dd_faultC.Items.FindByValue("%").Selected = true; } //ddbank.DataBind(); ddbank.Items.Add("ALL"); ddbank.Items.FindByText("ALL").Value = "%"; ddbank.Items.FindByValue("%").Selected = true; string query1 = "Select distinct ltrim(rtrim(bankid)) FROM [atms]"; obj.BindDropDown(ddbank, query1); ddagency.Items.Add("ALL"); ddagency.Items.FindByText("ALL").Value = "%"; ddagency.Items.FindByValue("%").Selected = true; string query9 = "Select distinct client FROM [atms]"; obj.BindDropDown(ddagency, query9); //ddcity.DataBind(); //ddcity.Items.Add("ALL"); //ddcity.Items.FindByText("ALL").Value = "%"; //ddcity.Items.FindByValue("%").Selected = true; string query3 = "Select distinct [username] as [RCM] FROM [Users] where username like '" + Session["sess_username"] + "'"; obj.BindDropDown(ddcity, query3); ddzone.DataBind(); ddzone.Items.Add("ALL"); ddzone.Items.FindByText("ALL").Value = "%"; ddzone.Items.FindByValue("%").Selected = true; ddproject.Items.Add("ALL"); ddproject.Items.FindByText("ALL").Value = "%"; ddproject.Items.FindByValue("%").Selected = true; string queryx = "Select distinct project FROM [ATMs]"; obj.BindDropDown(ddproject, queryx); //ddcalltype.DataBind(); //ddcalltype.Items.Add("ALL"); //ddcalltype.Items.FindByText("ALL").Value = "%"; //ddcalltype.Items.FindByValue("%").Selected = true; ////ddcalltype.Items.Add("NO ISSUES"); ////ddcalltype.Items.FindByText("NO ISSUES").Value = "NO ISSUES"; //string query4 = "Select distinct ltrim(rtrim(callstatus)) as [callstatus] FROM [IncidentsNew1] order by callstatus asc"; //obj.BindDropDown(ddcalltype, query4); //dddowntime.DataBind(); dddowntime.Items.Add("ALL"); dddowntime.Items.FindByText("ALL").Value = "120000"; dddowntime.Items.Add("0-5 Days"); dddowntime.Items.FindByText("0-5 Days").Value = "11999"; dddowntime.Items.Add("6-10 Days"); dddowntime.Items.FindByText("6-10 Days").Value = "23999"; dddowntime.Items.Add("11-15 Days"); dddowntime.Items.FindByText("11-15 Days").Value = "35999"; dddowntime.Items.Add("16-20 Days"); dddowntime.Items.FindByText("16-20 Days").Value = "47999"; dddowntime.Items.Add("21-30 Days"); dddowntime.Items.FindByText("21-30 Days").Value = "71999"; dddowntime.Items.Add(">30 Days"); dddowntime.Items.FindByText(">30 Days").Value = "119999"; dddowntime.Items.FindByValue("120000").Selected = true; Label2.Text = "000"; //Label5.Text = "CLOSE"; //Label6.Text = "RESOLVED"; if (txtEndDate.Text == "") { Label4.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } if (txtStartDate.Text == "") { //Label3.Text = "01/09/2014"; Label3.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } string[] words = Label3.Text.Split('/'); Label5.Text = words[1] + '/' + words[0] + '/' + words[2]; string[] words1 = Label4.Text.Split('/'); Label6.Text = words1[1] + '/' + words1[0] + '/' + words1[2]; Hid1.Value = "%"; Hid2.Value = "%"; SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("callstatus in (@callstatus)", "Callstatus like '%'"); SqlDataSource1.DataBind(); GridView1.AllowPaging = true; GridView1.AllowSorting = false; GridView1.DataSourceID = "SqlDataSource1"; GridView1.DataBind(); } else { for (int i = 0; i < ddcalltype.Items.Count; i++) { if (ddcalltype.Items[i].Selected == true) { status += ddcalltype.Items[i].Text + "','"; cnt++; } } if (cnt == 0) { status = ""; } lblTotalSelectedEmailCount.Text = cnt.ToString() + " item(s) selected"; if (status.Length > 3) { status = status.Remove(status.Length - 3); } if (txtEndDate.Text == "") { Label4.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } else { Label4.Text = txtEndDate.Text; } if (txtStartDate.Text == "") { //Label3.Text = "01/09/2014"; Label3.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } else { Label3.Text = txtStartDate.Text; } // ddcalltype1.Text = status; SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("callstatus in (@callstatus)", "callstatus in ('" + status + "')"); SqlDataSource1.DataBind(); GridView1.DataSourceID = "SqlDataSource1"; GridView1.DataBind(); } }
protected void Page_Load(object sender, EventArgs e) { //Label1.Visible = false; //CreateACvpr(); //CreateSRMvpr(); string updatedwntimeonload = @"Update IncidentsNew1 set Downtime=(Select CONVERT(varchar(5),datediff(s,DispatchDate,resolveddate)/3600) + ':' + CONVERT(varchar(5),datediff(s,DispatchDate,resolveddate)/3600%60)),DTStatus='SYN' where (CallStatus='RESOLVED' or CallStatus='CLOSE') and DTStatus<>'SYN';Update IncidentsNew1 set Downtime=(Select CONVERT(varchar(5),datediff(s,Opendate,getdate())/3600) + ':' + CONVERT(varchar(5),datediff(s,Opendate,getdate())/3600%60)) where (CallStatus='DISPATCHED' or CallStatus='OPEN')"; obj.NonExecuteQuery(updatedwntimeonload); if (!Page.IsPostBack) { string sessrole = ""; try { sessrole = Session["sess_role"].ToString(); } catch (Exception ee) { Response.Redirect("~/Login.aspx"); } //dd_faultC.Items.Add("ALL"); //dd_faultC.Items.FindByText("ALL").Value = "%"; //string query1x = "Select distinct [faultcode] FROM [faultcode]"; //obj.BindDropDown(dd_faultC, query1x); try { if (Request.QueryString["fault"].ToString() != "") { //dd_faultC.Items.FindByText(Request.QueryString["fault"].ToString()).Selected = true; if (Request.QueryString["TAT"].ToString() == "true") { SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("where", "where CONVERT(int,REPLACE(Downtime,':',''))<CONVERT(int,REPLACE(f.TAT,':','')) and "); SqlDataSource1.DataBind(); } else if (Request.QueryString["TAT"].ToString() == "false") { SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("where", "where CONVERT(int,REPLACE(Downtime,':',''))>CONVERT(int,REPLACE(f.TAT,':','')) and "); SqlDataSource1.DataBind(); } else if (Request.QueryString["TAT"].ToString() == "both") { } } } catch { //dd_faultC.Items.FindByValue("%").Selected = true; } //ddbank.DataBind(); string query1 = ""; query1 = "Select distinct ltrim(rtrim(BankName)) FROM [BankMap]"; obj.BindDropDown(ddbank, query1); if (ddbank.Items.Count > 1) { ddbank.Items.Add("ALL"); ddbank.Items.FindByText("ALL").Value = "%"; ddbank.Items.FindByValue("%").Selected = true; } //ddcity.DataBind(); string query3 = ""; ddcity.Items.Add("ALL"); ddcity.Items.FindByText("ALL").Value = "%"; ddcity.Items.FindByValue("%").Selected = true; query3 = "Select distinct [username] as [RCM] FROM [Users] where role='RM'"; obj.BindDropDown(ddcity, query3); ddzone.DataBind(); ddzone.Items.Add("ALL"); ddzone.Items.FindByText("ALL").Value = "%"; ddzone.Items.FindByValue("%").Selected = true; ddproject.Items.Add("ALL"); ddproject.Items.FindByText("ALL").Value = "%"; ddproject.Items.FindByValue("%").Selected = true; string queryx = "Select distinct branchid FROM [ATMs]"; obj.BindDropDown(ddproject, queryx); //ddcalltype.DataBind(); ddcalltype.Items.Add("ALL"); ddcalltype.Items.FindByText("ALL").Value = "%"; ddcalltype.Items.FindByValue("%").Selected = true; //ddcalltype.Items.Add("NO ISSUES"); //ddcalltype.Items.FindByText("NO ISSUES").Value = "NO ISSUES"; string query4 = "Select distinct ltrim(rtrim(callstatus)) as [callstatus] FROM [IncidentsNew1] order by callstatus asc"; obj.BindDropDown(ddcalltype, query4); //dddowntime.DataBind(); dddowntime.Items.Add("ALL"); dddowntime.Items.FindByText("ALL").Value = "120000"; dddowntime.Items.Add("0-5 Days"); dddowntime.Items.FindByText("0-5 Days").Value = "11999"; dddowntime.Items.Add("6-10 Days"); dddowntime.Items.FindByText("6-10 Days").Value = "23999"; dddowntime.Items.Add("11-15 Days"); dddowntime.Items.FindByText("11-15 Days").Value = "35999"; dddowntime.Items.Add("16-20 Days"); dddowntime.Items.FindByText("16-20 Days").Value = "47999"; dddowntime.Items.Add("21-30 Days"); dddowntime.Items.FindByText("21-30 Days").Value = "71999"; dddowntime.Items.Add(">30 Days"); dddowntime.Items.FindByText(">30 Days").Value = "119999"; dddowntime.Items.FindByValue("120000").Selected = true; Label2.Text = "000"; //Label5.Text = "CLOSE"; //Label6.Text = "RESOLVED"; if (txtEndDate.Text == "") { Label4.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } if (txtStartDate.Text == "") { Label3.Text = "01/11/2014"; //Label3.Text = System.DateTime.Today.ToString("dd'/'MM'/'yyyy"); } string[] words = Label3.Text.Split('/'); Label5.Text = words[1] + '/' + words[0] + '/' + words[2]; string[] words1 = Label4.Text.Split('/'); Label6.Text = words1[1] + '/' + words1[0] + '/' + words1[2]; Hid1.Value = "%"; Hid2.Value = "%"; GridView1.AllowPaging = true; GridView1.AllowSorting = false; SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand.ToString().Replace("faultcode in (@fault)", "faultcode like '%'").Replace("and a.bankname in (Select bankname from bankmap where userid = @user)", ""); SqlDataSource1.DataBind(); // Response.Write(SqlDataSource1.SelectCommand); GridView1.DataSourceID = "SqlDataSource1"; GridView1.DataBind(); GridView1.Columns[16].Visible = false; GridView1.Columns[17].Visible = false; } if (IsPostBack) { StringBuilder orderedItems = new StringBuilder(); StringBuilder order = new StringBuilder(); order.Append("'"); foreach (ComboBoxItem item in ComboBox1.Items) { CheckBox checkbox = item.FindControl("CheckBox1") as CheckBox; if (checkbox.Checked) { if (orderedItems.Length > 0) { orderedItems.Append(","); } orderedItems.Append(item.Value); } } faulthf.Value = "'" + orderedItems.ToString().Replace(",", "','") + "'"; //Response.Write(faulthf.Value); // Response.Write(SqlDataSource1.SelectCommand); } }