protected void btn_search_Click(object sender, EventArgs e) { //For retrieving selected values of state string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All" || users == null) { users = "%"; } #region Code to fetch string sql = ""; if (txtuser.Text == "") { #region Code to fetch data from DR_CTP sql = @"SELECT '' as [Excel],'' as [Photos],d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID], a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, convert(varchar(10),convert(date,vdate),103) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT] from dr_ctp d inner join atms a on d.atmid=a.atmid where d.userid like '" + users + "' and RM like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' order by convert(date,vdate) desc,convert(time,vtime) desc"; } else { sql = @"SELECT '' as [Excel],'' as [Photos],d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID], a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, convert(varchar(10),convert(date,vdate),103) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT] from dr_ctp d inner join atms a on d.atmid=a.atmid where d.userid like '" + users + "' and RM like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + ") order by convert(date,vdate) desc,convert(time,vtime) desc"; } #endregion bucket.BindGrid(GridView1, sql); // Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void timer_Tick(object sender, EventArgs e) { //System.Threading.Thread.Sleep(10000); //restart:; string bind = ""; bind = @"Select p.siteid as [SITE ID],sitename as [SITE NAME],COUNT(p.siteid) as [NO. OF VISITS],SUM(totamt) as [TOTAL AMOUNT] from Parking_Master p inner join Site s on p.siteid=s.siteid where CONVERT(date,vdate)=CONVERT(date,getdate() - 1) group by p.siteid,s.sitename order by p.siteid asc,s.sitename asc"; //Response.Write(bind); bucket.BindGrid(bind, "X", GridView1, this.Page); GridView1.DataBind(); if (GridView1.Rows.Count > 0) { Label3.ForeColor = Color.Green; Label3.Text = bucket.CountRows(GridView1, Label3); } else { Label3.ForeColor = Color.Red; Label3.Text = "No records found"; //goto restart; } //timer.Enabled = false; }
protected void Button1_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } if (string.IsNullOrEmpty(txt_frmDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } string q = @"Select c.vid, a.Location, a. Bankid as [Bank], a.Client as [Client], replace(substring(vid,len(userid)+2,len(vid)),'_',' ') as [Audit Date TIme] from DR_CTP c, ATMs a where c.atmid=a.atmid AND c.ATMID like '" + dd_atm.SelectedValue + "' and Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; bucket.BindGrid(GridView1, q); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ }
protected void btn_search_Click(object sender, EventArgs e) { #region Code to fetch data from DR_CTP string sql = @"Select i.atmid as [ATMID],a.SiteID as [SITE ID],addressline1 as [ADDRESS],bankid as [BANK],DDocketNumber as [TICKET NUMBER],CONVERT(varchar(10),OpenDate,103) + ' ' + CONVERT(varchar(10),OpenDate,108) as [CALL OPEN DATE] " + " from IncidentsNew1 i inner join atms a on a.atmid=i.ATMID where FaultID like '" + DropDownList2.SelectedValue + "' " + " and a.rcm like '" + Session["Sess_username"] + "' and (i.atmid like '%" + txtAtm.Text + "' or a.Siteid like '%" + txtAtm.Text + "') " + " and convert(date,opendate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' " + " and i.atmid in (Select atmid from IncidentsNew1 where FaultID like '" + DropDownList2.SelectedValue + "' group by atmid having COUNT(atmid) >= 2) "; bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void timer_Tick(object sender, EventArgs e) { //System.Threading.Thread.Sleep(10000); //restart:; //bucket.ExecuteQuery("Delete from DR_Branch where atmid='null' and vdate='null'"); string bind = ""; if (Session["sess_role"].ToString().Contains("AO")) { //bind = "select * from currentview where vid like '%" + Session["sess_userid"] + "%' order by [AUDIT DATE] desc, [AUDIT TIME] desc"; } else { bind = @"Select userid as [USERID],COUNT(userid) as [COUNT],vdate as [VISIT DATE] from DR_CTP where DATEDIFF(MI,chkdate,CONVERT(datetime,(vdate + ' ' + vtime))) > 30 and CONVERT(date,vdate) between '" + txtfromdate.Text + "' and '" + txttodate.Text + "' group by userid,vdate"; } bucket.BindGrid(GridView1, bind); GridView1.DataBind(); if (GridView1.Rows.Count > 0) { Label3.Visible = true; Label3.Text = bucket.CountRows(GridView1, Label3); timer.Enabled = false; } else { Label3.Visible = true; Label3.Text = "No Records Found"; Label3.ForeColor = System.Drawing.Color.Red; timer.Enabled = false; } }
protected void btn_search_Click(object sender, EventArgs e) { #region Code to fetch data from DR_CTP string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } string sql = ""; if (txtuser.Text == "") { sql = @"SELECT distinct(case when d.Siteid='NA' then Vid else d.Siteid end) as [SITE ID], d.USERID AS [USER ID], case when d.Siteid='NA' then d.address else a.addressline1 end AS LOCATION,a.city as CITY,case when d.Siteid='NA' then '-' else a.bankid end AS [BANK NAME], convert(varchar(10),convert(date,ldate),103) AS [DATE OF VISIT], d.ltime AS [TIME OF VISIT],DistanceTraveled as [DISTANCE IN KM] from Distance d left outer join atms a on d.siteid=a.siteid where d.userid like '" + users + "' and RCM like '" + Session["sess_username"] + "' and convert(date,ldate) = '" + txt_frmDate.Text + "' order by d.ltime desc"; } else { sql = @"SELECT distinct(case when d.Siteid='NA' then Vid else d.Siteid end) as [SITE ID], d.USERID AS [USER ID], case when d.Siteid='NA' then d.address else a.addressline1 end AS LOCATION,a.city as CITY,case when d.Siteid='NA' then '-' else a.bankid end AS [BANK NAME], convert(varchar(10),convert(date,ldate),103) AS [DATE OF VISIT], d.ltime AS [TIME OF VISIT],DistanceTraveled as [DISTANCE IN KM] from Distance d left outer join atms a on d.siteid=a.siteid where d.userid like '" + users + "' and RCM like '" + Session["sess_username"] + "' and a.state in (" + txtuser.Text + @") and convert(date,ldate) = '" + txt_frmDate.Text + "' order by d.ltime desc"; } bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; chk123.Visible = false; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion // getmap(); drawmap(users); }
protected void btn_Update_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } if (string.IsNullOrEmpty(txt_frmDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); string sql = ""; if (txtuser.Text == "") { sql = @"Select c.vid,c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(12),Convert(date,vdate),103)+' '+vtime as [Audit Date TIme] from DR_CTP c, atms a where c.atmid=a.atmid AND a.client like '" + dd_cust.SelectedItem.Value.ToString() + @"' and a.bankid like '" + dd_bank.SelectedItem.Value.ToString() + "' and a.CH like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "'"; } else { sql = @"Select c.vid,c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(12),Convert(date,vdate),103)+' '+vtime as [Audit Date TIme] from DR_CTP c, atms a where c.atmid=a.atmid AND a.client like '" + dd_cust.SelectedItem.Value.ToString() + @"' and a.bankid like '" + dd_bank.SelectedItem.Value.ToString() + "' and a.state in (" + txtuser.Text + ") and a.CH like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "'"; } // Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); /*------------------------------------------------------------------------------------------------*/ /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); // GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); if (string.IsNullOrEmpty(txt_frmDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } string q = ""; if (txtuser.Text == "") { q = @"select distinct a.atmid as [Atmid] ,a.location as Location,a.bankid as [Bank],region as [Region],u.username,a.siteid,a.onoffsite as [Site Type],a.state from atms a inner join Users u on u.ch=a.ch where atmstatus <> 'Inactive' and u.CH like '" + Session["sess_username"] + "'and a.atmid not in (select distinct atmid from dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } else { q = @"select distinct a.atmid as [Atmid] ,a.location as Location,a.bankid as [Bank],region as [Region],u.username,a.siteid,a.onoffsite as [Site Type],a.state from atms a inner join Users u on u.ch=a.ch where atmstatus <> 'Inactive' and u.CH like '" + Session["sess_username"] + "'and a.atmid not in (select distinct atmid from dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') and a.state in (" + txtuser.Text + ")"; } // q = @"select distinct a.atmid as [Atmid] ,a.location as Location,a.bankid as [Bank],region as [Region],u.username,a.siteid,a.onoffsite as [Site Type],a.state // from atms a,Users u,Usermap um // where a.atmid=um.atmid and um.userid=u.userid and atmstatus <> 'DEL' and u.CH like '" + Session["sess_username"] + // "'and a.atmid not in (select distinct atmid from dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + // "' and '" + txt_toDate.Text + "') and a.state in (" + txtuser.Text + ")"; bucket.BindGrid(grid_unaudit, q); //Response.Write(q); if (grid_unaudit.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(grid_unaudit, Label3); } }
protected void btn_search_Click(object sender, EventArgs e) { ImageButton1.Visible = true; string sql = GetQuery(); double version = Convert.ToDouble(ddlVersion.SelectedValue.ToString()); try { if (version > 3.9) { Label3.Text = bucket.CountRows(GridView2, Label3); Label1.Visible = false; create_parent_headers1(); } else { Label3.Text = bucket.CountRows(GridView1, Label3); Label1.Visible = false; } } catch { Label1.Visible = true; } //if (GridView1.Rows.Count.Equals(0)) //{ // Label1.Visible = true; // Label3.Visible = false; // div1.Visible = true; //} //else //{ // div1.Visible = true; // Label3.Visible = true; // Label1.Visible = false; // Label3.Text = bucket.CountRows(GridView1, Label3); //} }
protected void Page_Load(object sender, EventArgs e) { GridView1.DataBind(); if (GridView1.Rows.Count > 0) { Label1.ForeColor = Color.Green; Label1.Text = bucket.CountRows(GridView1, Label1); } else { Label1.ForeColor = Color.Red; Label1.Text = "No records found pertaining to your search. Please select other search criteria."; } }
protected void Button1_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } if (string.IsNullOrEmpty(txt_frmDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } string q = ""; #region Code to fetch data from DR_CTP if (txtuser.Text == "") { q = @"Select c.vid,a.siteid,a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103) +' '+vtime as [Audit Date TIme] from DR_CTP c, ATMs a where c.atmid=a.atmid AND (c.ATMID like '" + dd_atm.Text + "' or a.siteid like '" + dd_atm.Text + "') and a.RM like '" + Session["sess_username"] + "' and Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; } else { q = @"Select c.vid,a.siteid,a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103) +' '+vtime as [Audit Date TIme] from DR_CTP c, ATMs a where c.atmid=a.atmid AND (c.ATMID like '" + dd_atm.Text + "' or a.siteid like '" + dd_atm.Text + "') and a.state in (" + txtuser.Text + ") and a.RM like '" + Session["sess_username"] + "' and Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; } bucket.BindGrid(GridView1, q); #endregion /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_frmDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.Date.AddDays((-(Convert.ToDouble((DateTime.Now.Date.Day)))) + 1).ToString("MM'/'dd'/'yyyy"); } if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } string q = ""; #region Code to fetch data from DR_CTP if (txtuser.Text == "") { q = @"select distinct a.atmid as [Atmid] ,a.location as Location,a.bankid as [Bank],region as [Region],a.siteid,a.onoffsite as [Site Type],a.state from atms a inner join Users u on u.rm = a.rm where atmstatus <> 'Inactive' and u.RM like '" + Session["sess_username"] + "' and a.atmid not in (select distinct atmid from dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } else { q = @"select distinct a.atmid as [Atmid] ,a.location as Location,a.bankid as [Bank],region as [Region],a.siteid,a.onoffsite as [Site Type],a.state from atms a inner join Users u on u.rm = a.rm where atmstatus <> 'Inactive' and u.RM like '" + Session["sess_username"] + "' and a.state in (" + txtuser.Text + ") and a.atmid not in (select distinct atmid from dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } #endregion bucket.BindGrid(grid_unaudit, q); //Response.Write(q); if (grid_unaudit.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(grid_unaudit, Label3); } }
protected void btn_search_Click(object sender, EventArgs e) { ImageButton1.Visible = true; string sql = GetQuery(); if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } }
protected void btn_search_Click(object sender, EventArgs e) { //Response.Write(q); ImageButton2.Visible = true; string q = GetQuery(); if (grid_unaudit.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(grid_unaudit, Label3); } }
protected void btn_search_Click(object sender, EventArgs e) { ImageButton1.Visible = true; string sql = GetQry(); if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; div1.Visible = true; } else { div1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } }
protected void btn_Update_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); string sql = @"Select c.vid,c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], replace(substring(vid,len(userid)+2,len(vid)),'_',' ') as [Audit Date TIme] from DR_CTP c, atms a where c.atmid=a.atmid AND a.client='" + dd_cust.SelectedItem.Text.ToString() + @"' and a.bankid='" + dd_bank.SelectedItem.Text.ToString() + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "'"; /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); /*------------------------------------------------------------------------------------------------*/ /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); // GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); string q = ""; if (Request.QueryString["type"] == "ctp") { q = "select distinct Replace(atmid,'*BR*','') as [Atmid] ,location,bankid as [Bank] from atms where status <> 'Inactive' and bankid like '%ICICI%' and region='" + Session["sess_userid"].ToString().Trim() + "' and atmid not like '%*BR*%' and atmid not in (select distinct atmid from current_dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } else if (Request.QueryString["type"] == "hdfc") { q = "select distinct Replace(atmid,'HDFC','') as [Atmid] ,location,bankid as [Bank] from atms where status <> 'Inactive' and bankid like 'HDFC' and region='" + Session["sess_userid"].ToString().Trim() + "' and atmid not in (select distinct atmid from current_dr_ctp where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } else if (Request.QueryString["type"] == "bom") { q = "select distinct Replace(atmid,'*BR*','') as [Atmid] ,location,bankid as [Bank] from atms where status <> 'Inactive' and atmid like '%*BR*%' and atmid not like '%HDFC%' and region='" + Session["sess_userid"].ToString().Trim() + "' and bankid like 'BANK OF MAHARASHTRA' and atmid not in (select distinct atmid from dr_branchcurmonth where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "') "; } bucket.BindGrid(grid_unaudit, q); //Response.Write(q); if (grid_unaudit.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(grid_unaudit, Label3); } }
protected void btn_search_Click(object sender, EventArgs e) { #region Code to fetch data from DR_CTP string sql = @"SELECT d.Vid AS [VISIT ID], d.ATMID AS [ATM ID], d.USERID AS [USER ID], a.addressline1 AS LOCATION, a.bankid AS [BANK NAME], a.client AS MSP, convert(varchar(10),convert(date,vdate),103) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT], Q1 as [ATM MACHINE WORKING FINE?], Q2 as [CARETAKER AVAILABLE?],Q3 as [CARETAKER NAME],Q4 as [CARETAKER NUMBER],Q5 as [CLEANING DONE REGULARLY?], Q6 as [How Severe is the upkeep issue], Q7 as [FLOORING PROPER?], Q8 as [DUST BIN OK?], Q9 as [BACKROOM OK?], Q10 as [WRITING LEDGE AND VMS PROPER?], Q11 as [FIRE EXTINGUISHER OK?], Q12 as [IS RNM OK?], Q13 as [How Severe is the RnM issue], Q14 as [LIGHTS OK?],Q15 as [No. Of CFL Working],Q16 as [GLOW SIGN PROPER?], Q17 as [DOOR WORKING PROPERLY?], Q18 as [WALLS PROPER?], Q19 as [CEILING PROPER?], Q20 as [DOOR MAT AVAILABLE?], Q21 as [IS AC Installed at Site], Q22 as [AC WORKING PROPERLY?], Q23 as [AC Connected with timer?], Q24 as [AC connected with meter?], Q25 as [UPS AND BATTERIES WORKING?], Q26 as [CAMERAS AVAILABLE AT SITE?], Q27 as [Signage & Lollipop is working?], Q28 as [ANY ISSUE AFFECTNG THE TRANSACTIONS?], Q29 as [Feedback from Neighboring Shops/ LL], Q30 as [VSAT Ballasting], Q31 as [Mandatory Notices], Q32 as [Electricity Bill Payment], Q33 as [Any New Bills at Site], Q34 as [Submeter Reading], Q35 as [Any Power Theft Noticed], Q36 as [Multimeter Reading of Earthing], Q37 as [Is the Visit along with PM Engineer and CRA], Q38 as [If Yes: PM Docket No:], Q39 as [If Yes: Is PM Done properly], Q40 as [Cash Tallied with Admin Balance, Machine Counter and Physical Couting] from dr_ctp d inner join atms a on d.atmid=a.atmid where d.userid like '" + DropDownList1.SelectedValue.ToString() + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void search_Click(object sender, EventArgs e) { string sql = @"Select USERID,MEMOREMARK,Convert(varchar(10),SENTON,103) as [Sent Date],Convert(varchar(8),SENTON,108) as [Sent Time], case when SEENDATE is null then 'NA' else Convert(varchar(10),SEENDATE,103)+' '+Convert(varchar(8),SEENDATE,108) end as [Seen Date],SENTBY as [Sent by],STATUS as [Status] from Memo where CONVERT(DATE,convert(varchar(11),SENTON),103)='" + txtdate.Text + "' and Status='" + ddstatus.SelectedValue + "'"; // GridView1.DataBind(); bucket.BindGrid(GridView1, sql); if (GridView1.Rows.Count.Equals(0)) { lblNoRecord.Visible = true; lblSuccess.Visible = false; ImageButton1.Visible = false; } else { ImageButton1.Visible = true; lblSuccess.Visible = true; lblNoRecord.Visible = false; lblSuccess.Text = bucket.CountRows(GridView1, lblSuccess); } }
protected void btn_search_Click(object sender, EventArgs e) { //if (ddrole.SelectedValue == "%") //{ // role = "role in ('AO','DE','CM')"; //} //else //{ // role = "role like '" + ddrole.SelectedValue + "'"; //} string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } #region Code to fetch data from DR_CTP string sql = ""; // if (txtuser.Text == "") // { // sql = @"SELECT d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID],a.onoffsite as [SITE TYPE],a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, // convert(varchar(10),convert(date,vdate),101) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT],visittype as [VISIT TYPE],visitremark as [VISIT REMARK] ,Q1 as [ATM MACHINE WORKING FINE?], // Q2 as [CARETAKER AVAILABLE?],Q3 as [CARETAKER NAME],Q4 as [CARETAKER NUMBER],Q5 as [CLEANING DONE REGULARLY?], Q6 as [How Severe is the upkeep issue], Q7 as [FLOORING PROPER?], Q8 as [DUST BIN OK?], // Q9 as [BACKROOM OK?], Q10 as [WRITING LEDGE AND VMS PROPER?],Q41 as [Signage & Lollipop Cleaned or not?], Q11 as [FIRE EXTINGUISHER OK?], Q12 as [IS RNM OK?], Q13 as [How Severe is the RnM issue], // Q14 as [LIGHTS OK?],Q15 as [No. Of CFL Working],Q16 as [GLOW SIGN PROPER?], Q17 as [DOOR WORKING PROPERLY?], Q18 as [WALLS PROPER?], Q19 as [CEILING PROPER?], // Q20 as [DOOR MAT AVAILABLE?], Q21 as [IS AC Installed at Site], Q22 as [AC WORKING PROPERLY?], Q23 as [AC Connected with timer?], Q24 as [AC connected with meter?], // Q25 as [UPS AND BATTERIES WORKING?], Q26 as [CAMERAS AVAILABLE AT SITE?], Q27 as [Signage & Lollipop is working?], Q28 as [ANY ISSUE AFFECTNG THE TRANSACTIONS?], // Q29 as [Feedback from Neighboring Shops/ LL], Q30 as [VSAT Ballasting], Q31 as [Mandatory Notices], Q32 as [Electricity Bill Payment], Q33 as [Any New Bills at Site], // Q34 as [Submeter Reading], Q35 as [Any Power Theft Noticed], Q36 as [Multimeter Reading of Earthing], Q37 as [Is the Visit along with PM Engineer and CRA], // Q38 as [If Yes: PM Docket No:], Q39 as [If Yes: Is PM Done properly], Q40 as [Cash Tallied with Admin Balance, Machine Counter and Physical Couting],Q42 as [Raw Power Status],Q43 as [Multimeter P N Reading],Q44 as [Multimeter P E Reading],Q45 as [Multimeter N E Reading],Q46 as [UPS P N Reading],Q47 as [UPS P E Reading],Q48 as [UPS N E Reading],Q49 as [Power availability in a day (no of Hrs)],Q50 as [Frequency of power failure in a day],Q51 as [Is the ODU-IDU Connection done as per requirement], Q52 as [Other ATMs nearby (range within 500 meters)],Q53 as [Stabilizer available],Q54 as [Isolation available],Q55 as [Monkey cage available], d.lat as[LATITUDE], d.lon as[LONGITUDE] // from dr_ctp d inner join atms a on d.atmid=a.atmid join users u on u.userid=d.userid where d.userid like '" + users + // "' and " + role + " and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; // } // else // { // sql = @"SELECT d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID],a.onoffsite as [SITE TYPE],a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, // convert(varchar(10),convert(date,vdate),101) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT],visittype as [VISIT TYPE],visitremark as [VISIT REMARK], Q1 as [ATM MACHINE WORKING FINE?], // Q2 as [CARETAKER AVAILABLE?],Q3 as [CARETAKER NAME],Q4 as [CARETAKER NUMBER],Q5 as [CLEANING DONE REGULARLY?], Q6 as [How Severe is the upkeep issue], Q7 as [FLOORING PROPER?], Q8 as [DUST BIN OK?], // Q9 as [BACKROOM OK?], Q10 as [WRITING LEDGE AND VMS PROPER?],Q41 as [Signage & Lollipop Cleaned or not?], Q11 as [FIRE EXTINGUISHER OK?], Q12 as [IS RNM OK?], Q13 as [How Severe is the RnM issue], // Q14 as [LIGHTS OK?],Q15 as [No. Of CFL Working],Q16 as [GLOW SIGN PROPER?], Q17 as [DOOR WORKING PROPERLY?], Q18 as [WALLS PROPER?], Q19 as [CEILING PROPER?], // Q20 as [DOOR MAT AVAILABLE?], Q21 as [IS AC Installed at Site], Q22 as [AC WORKING PROPERLY?], Q23 as [AC Connected with timer?], Q24 as [AC connected with meter?], // Q25 as [UPS AND BATTERIES WORKING?], Q26 as [CAMERAS AVAILABLE AT SITE?], Q27 as [Signage & Lollipop is working?], Q28 as [ANY ISSUE AFFECTNG THE TRANSACTIONS?], // Q29 as [Feedback from Neighboring Shops/ LL], Q30 as [VSAT Ballasting], Q31 as [Mandatory Notices], Q32 as [Electricity Bill Payment], Q33 as [Any New Bills at Site], // Q34 as [Submeter Reading], Q35 as [Any Power Theft Noticed], Q36 as [Multimeter Reading of Earthing], Q37 as [Is the Visit along with PM Engineer and CRA], // Q38 as [If Yes: PM Docket No:], Q39 as [If Yes: Is PM Done properly], Q40 as [Cash Tallied with Admin Balance, Machine Counter and Physical Couting],Q42 as [Raw Power Status],Q43 as [P N Reading],Q44 as [P E Reading],Q45 as [N E Reading],Q46 as [P N Reading],Q47 as [P E Reading],Q48 as [N E Reading],Q49 as [Power availability in a day (no of Hrs)],Q50 as [Frequency of power failure in a day],Q51 as [Is the ODU-IDU Connection done as per requirement], Q52 as [Other ATMs nearby (range within 500 meters)],Q53 as [Stabilizer available],Q54 as [Isolation available],Q55 as [Monkey cage available], d.lat as[LATITUDE], d.lon as[LONGITUDE] // from dr_ctp d inner join atms a on d.atmid=a.atmid join users u on u.userid=d.userid where d.userid like '" + users + // "' and " + role + " and a.state in (" + txtuser.Text + ") and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; // } if (txtuser.Text == "") { sql = @"SELECT b.Vid AS [VISIT ID],b.reason as [REASON OF VISIT],b.remarks as [REMARKS], convert(varchar(10),convert(date,b.fromdate),103)+' '+convert(varchar(10),convert(time,b.fromdate),108) as [START TIME], convert(varchar(10),convert(date,b.todate),103)+' '+convert(varchar(10),convert(time,b.todate),108) as [END TIME], b.enteredby as [USER],convert(varchar(10),convert(date,b.enteredon),103)+' '+convert(varchar(10),convert(time,b.enteredon),108) as [ENTERED ON] from activity b join users a on b.enteredby = a.userid where b.enteredby like '" + users + "' and convert(date,fromdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' "; } else { sql = @"SELECT b.Vid AS [VISIT ID],b.reason as [REASON OF VISIT],b.remarks as [REMARKS], convert(varchar(10),convert(date,b.fromdate),103)+' '+convert(varchar(10),convert(time,b.fromdate),108) as [START TIME], convert(varchar(10),convert(date,b.todate),103)+' '+convert(varchar(10),convert(time,b.todate),108) as [END TIME], b.enteredby as [USER], convert(varchar(10),convert(date,b.enteredon),103)+' '+convert(varchar(10),convert(time,b.enteredon),108) as [ENTERED ON] from activity b join users a on b.enteredby = a.userid where b.enteredby like '" + users + "' and a.state in (" + txtuser.Text + ") and convert(date,fromdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' "; } bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void btn_search_Click(object sender, EventArgs e) { string sql = ""; #region Code to fetch data from DR_CTP string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } if (txtuser.Text == "") { sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],convert(varchar(10),convert(date,ldate),103) as [Date] from (SELECT userid, DistanceTraveled,ldate FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN FROM Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where CH like '" + Session["sess_username"] + @"' and userid like '" + users + @"') ) AS t WHERE RN = 1 ) x left outer join (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' group by userid,vdate) y on x.userid=y.userid and x.ldate=y.vdate order by convert(date,ldate) asc,userid asc"; } else { sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],convert(varchar(10),convert(date,ldate),103) as [Date] from (SELECT userid, DistanceTraveled,ldate FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN FROM Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where state in (" + txtuser.Text + @") and CH like '" + Session["sess_username"] + @"' and userid like '" + users + @"') ) AS t WHERE RN = 1 ) x left outer join (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + @") group by userid,vdate) y on x.userid=y.userid and x.ldate=y.vdate order by convert(date,ldate) asc,userid asc"; } // string sql = @"SELECT distinct(case when d.Siteid='NA' then Vid else d.Siteid end) as [SITE ID], d.USERID AS [USER ID], // case when d.Siteid='NA' then d.address else a.addressline1 end AS LOCATION,a.city as CITY,case when d.Siteid='NA' then '-' else a.bankid end AS [BANK NAME], // convert(varchar(10),convert(date,ldate),103) AS [DATE OF VISIT], d.ltime AS [TIME OF VISIT],DistanceTraveled as [DISTANCE IN KM] // from Distance d left outer join atms a on d.siteid=a.atmid join users u on d.userid=u.userid where d.userid like '" + DropDownList1.SelectedValue + "' and " + role + " and convert(date,ldate) = '" + // txt_frmDate.Text + "' order by d.ltime desc"; bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; //chk123.Visible = false; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); try { string sql = ""; if (txtuser.Text != "") { sql = @"select u.userid as [area officer],username as [User Name],COUNT(d.vid) as [visit] ,convert(varchar(10),convert(date,vdate),103) as [Audit Date] from DR_CTP d, users u where d.USERID=u.userid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + @"' and state in (" + txtuser.Text + @") and CH = '" + Session["sess_username"].ToString() + @"' group by u.userid ,vdate,username order by CONVERT(date, vdate) desc"; } else { sql = @"select u.userid as [area officer],username as [User Name],COUNT(d.vid) as [visit] ,convert(varchar(10),convert(date,vdate),103) as [Audit Date] from DR_CTP d, users u where d.USERID=u.userid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + @"' and state is not null and CH = '" + Session["sess_username"].ToString() + @"' group by u.userid ,vdate,username order by CONVERT(date, vdate) desc"; } //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); /*------------------------------------------------------------------------------------------------*/ /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ } catch { Response.Write("<script>alert('Fields can not be left blank')</script>"); } }
protected void timer_Tick(object sender, EventArgs e) { timer.Enabled = false; //System.Threading.Thread.Sleep(10000); //restart:; //bucket.ExecuteQuery("Delete from DR_Branch where atmid='null' and vdate='null'"); string bind = ""; try { if (Request.QueryString["userid"].ToString() != "") { try { if (Request.QueryString["dist"].ToString() == "yes") { bind = @"select VID,ATMID,substring(vdate,4,3) + substring(vdate,1,2)+ substring(vdate,6,5) as [AUDIT DATE], vtime as [AUDIT TIME] from DR_CTP where isdate(vdate)=1 and vdate like '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]' and userid like '" + Request.QueryString["userid"].ToString() + "' and vdate='" + Request.QueryString["vdate"].ToString() + "' and distance like '%.%' " + " and Convert(int,LEFT(distance, CHARINDEX('.', distance) - 1)) > 1000 order by srno desc"; // "select * from currentview order by [AUDIT DATE] desc, [AUDIT TIME] desc"; } else if (Request.QueryString["dist"].ToString() == "no") { bind = @"select VID,ATMID,substring(vdate,4,3) + substring(vdate,1,2)+ substring(vdate,6,5) as [AUDIT DATE], vtime as [AUDIT TIME] from DR_CTP where isdate(vdate)=1 and vdate like '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]' and userid like '" + Request.QueryString["userid"].ToString() + "' and vdate='" + Request.QueryString["vdate"].ToString() + "' and DATEDIFF(MI,chkdate,CONVERT(datetime,(vdate + ' ' + vtime))) > 30 order by srno desc"; // "select * from currentview order by [AUDIT DATE] desc, [AUDIT TIME] desc"; } } catch (Exception ee) { bind = @"select VID,ATMID,substring(vdate,4,3) + substring(vdate,1,2)+ substring(vdate,6,5) as [AUDIT DATE], vtime as [AUDIT TIME] from DR_CTP where isdate(vdate)=1 and vdate like '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]' and userid like '%" + Session["sess_userid"] + "%' and vdate='" + Request.QueryString["vdate"].ToString() + "' and lat='0' and lon='0' order by srno desc"; } } } catch (Exception ex) { if (Session["role"].ToString() == "AO") { bind = "select * from currentview where vid like '%" + Session["sess_userid"] + "%' order by [AUDIT DATE] desc, [AUDIT TIME] desc"; } else { bind = "select VID,ATMID,substring(vdate,4,3) + substring(vdate,1,2)+ substring(vdate,6,5) as [AUDIT DATE], vtime as [AUDIT TIME] from DR_CTP where isdate(vdate)=1 and vdate like '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]' and vdate=convert(date,GETDATE(),103) order by srno desc";// "select * from currentview order by [AUDIT DATE] desc, [AUDIT TIME] desc"; } } bucket.BindGrid(GridView1, bind); GridView1.DataBind(); if (GridView1.Rows.Count > 0) { Label3.Visible = true; Label3.Text = bucket.CountRows(GridView1, Label3); timer.Enabled = false; } else { timer.Enabled = false; } }
protected void btn_search_Click(object sender, EventArgs e) { string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } #region Code to fetch data from DR_CTP string sql = ""; if (txtuser.Text == "") { sql = @"SELECT d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID],a.onoffsite as [SITE TYPE],a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, convert(varchar(10),convert(date,vdate),103) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT],visittype as [VISIT TYPE],visitremark as [VISIT REMARK] ,Q1 as [ATM MACHINE WORKING FINE?], Q2 as [CARETAKER AVAILABLE?],Q3 as [CARETAKER NAME],Q4 as [CARETAKER NUMBER],Q5 as [CLEANING DONE REGULARLY?], Q6 as [How Severe is the upkeep issue], Q7 as [FLOORING PROPER?], Q8 as [DUST BIN OK?], Q9 as [BACKROOM OK?], Q10 as [WRITING LEDGE AND VMS PROPER?],Q41 as [Signage & Lollipop Cleaned or not?], Q56 as [FIRE EXTINGUISHER AVAILABLE?],Q11 as [IS THE FIRE EXTINGUISHER EXPIRED], Q12 as [IS RNM OK?], Q13 as [How Severe is the RnM issue], Q14 as [LIGHTS OK?],Q15 as [No. Of CFL Working],Q16 as [GLOW SIGN PROPER?], Q17 as [DOOR WORKING PROPERLY?], Q18 as [WALLS PROPER?], Q19 as [CEILING PROPER?], Q20 as [DOOR MAT AVAILABLE?], Q21 as [IS AC Installed at Site], Q22 as [AC WORKING PROPERLY?], Q23 as [AC Connected with timer?], Q24 as [AC connected with meter?], Q25 as [UPS AND BATTERIES WORKING?], Q26 as [CAMERAS AVAILABLE AT SITE?], Q27 as [Signage & Lollipop is working?], Q28 as [ANY ISSUE AFFECTNG THE TRANSACTIONS?], Q29 as [Feedback from Neighboring Shops/ LL], Q30 as [VSAT Ballasting], Q31 as [Mandatory Notices], Q32 as [Electricity Bill Payment], Q33 as [Any New Bills at Site], Q34 as [Submeter Reading], Q35 as [Any Power Theft Noticed], Q36 as [Multimeter Reading of Earthing], Q37 as [Is the Visit along with PM Engineer and CRA], Q38 as [If Yes: PM Docket No:], Q39 as [If Yes: Is PM Done properly], Q40 as [Cash Tallied with Admin Balance, Machine Counter and Physical Couting],q57 as [Housekeeping done on site?],Q58 as [Deep cleaning done on site?],Q42 as [Raw Power Status], Q43 as [Multimeter P N Reading],Q44 as [Multimeter P E Reading],Q45 as [Multimeter N E Reading], Q46 as [UPS P N Reading],Q47 as [UPS P E Reading],Q48 as [UPS N E Reading],Q49 as [Power availability in a day (no of Hrs)],Q50 as [Frequency of power failure in a day],Q51 as [Is the ODU-IDU Connection done as per requirement], Q52 as [Other ATMs nearby (range within 500 meters)],Q53 as [Stabilizer available],Q54 as [Isolation available],Q55 as [Monkey cage available],ISNULL(Q59,'NA') as [Is CAM1 Working?],ISNULL(Q60,'NA') as [Is CAM2 Working?],ISNULL(Q61,'NA') as [Is the Image getting stored?],ISNULL(Q62,'NA') as [Is EJ getting pulled?], isnull(Q63,'NA') as [IS ATM POWER SWITCH AVAILABLE INSIDE BACK ROOM ?],isnull(Q64,'NA')as [HEIGHT OF THE ATM ROOM ?],isnull(Q65,'NA') as [IS RAMP AVAILABLE ? ], isnull(Q66,'NA') as [IS LAN ROUTING PROPER ? ],isnull(Q67,'NA') as [Space availability in ATM backroom for 2 units with dimensions 2X2 each ? ], isnull(Q68,'NA') as [Space availability in ATM lobby for 2 units with size dimensions 2X2 each ? ],isnull(Q69,'NA') as [Network feasibility Voice ? ],isnull(Q70,'NA') as [Network feasibility Data ? ],isnull(Q71,'NA') as [Is shutter open close activity happening ? ],isnull(Q72,'NA') as [Shutter open/Close activity happening daily ? ],remark as [OTHER REMARK], [AC SRNO],[AC MAKE],[ATM SRNO],[ATM MAKE],[BAT1 SRNO],[BAT1 MAKE], [BAT2 SRNO],[BAT2 MAKE],[BAT3 SRNO],[BAT3 MAKE],[MODEM SRNO],[MODEM MAKE],[UPS SRNO],[UPS MAKE], d.lat as[LATITUDE], d.lon as[LONGITUDE] from dr_ctp d inner join atms a on d.atmid=a.atmid join users u on u.userid=d.userid left outer join (select vid,[AC_SRNO] as [AC SRNO],[AC_MAKE] as [AC MAKE],[ATM_SRNO] as [ATM SRNO],[ATM_MAKE] as [ATM MAKE], [BAT1_SRNO] as [BAT1 SRNO],[BAT1_MAKE] as [BAT1 MAKE],[BAT2_SRNO] as [BAT2 SRNO],[BAT2_MAKE] as [BAT2 MAKE], [BAT3_SRNO] as [BAT3 SRNO],[BAT3_MAKE] as [BAT3 MAKE],[MODEM_SRNO] as [MODEM SRNO],[MODEM_MAKE] as [MODEM MAKE], [UPS_SRNO] as [UPS SRNO],[UPS_MAKE] as [UPS MAKE] from (SELECT vid,type+'_'+col AS col,value FROM ( SELECT vid,srno as [Srno],make AS [Make], type FROM scan GROUP BY vid,srno,make,type) rt unpivot ( value FOR col in (srno,make))unpiv )tp pivot ( MAX(value) FOR col in ([AC_SRNO],[AC_MAKE],[ATM_SRNO],[ATM_MAKE],[BAT1_SRNO],[BAT1_MAKE], [BAT2_SRNO],[BAT2_MAKE],[BAT3_SRNO],[BAT3_MAKE],[MODEM_SRNO],[MODEM_MAKE],[UPS_SRNO],[UPS_MAKE])) piv ) s on d.vid=s.vid where d.userid like '" + users + "' and a.RCM like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' order by convert(date,vdate) desc,convert(time,vtime) desc"; } else { sql = @"SELECT d.Vid AS [VISIT ID],d.USERID AS [USER ID],d.ATMID AS [ATM ID],a.SiteID as [Site ID],a.onoffsite as [SITE TYPE],a.addressline1 AS LOCATION,a.state AS [STATE],a.bankid AS [BANK NAME], a.client AS MSP, convert(varchar(10),convert(date,vdate),103) AS [DATE OF VISIT], d.vtime AS [TIME OF VISIT],visittype as [VISIT TYPE],visitremark as [VISIT REMARK] ,Q1 as [ATM MACHINE WORKING FINE?], Q2 as [CARETAKER AVAILABLE?],Q3 as [CARETAKER NAME],Q4 as [CARETAKER NUMBER],Q5 as [CLEANING DONE REGULARLY?], Q6 as [How Severe is the upkeep issue], Q7 as [FLOORING PROPER?], Q8 as [DUST BIN OK?], Q9 as [BACKROOM OK?], Q10 as [WRITING LEDGE AND VMS PROPER?],Q41 as [Signage & Lollipop Cleaned or not?], Q56 as [FIRE EXTINGUISHER AVAILABLE?],Q11 as [IS THE FIRE EXTINGUISHER EXPIRED], Q12 as [IS RNM OK?], Q13 as [How Severe is the RnM issue], Q14 as [LIGHTS OK?],Q15 as [No. Of CFL Working],Q16 as [GLOW SIGN PROPER?], Q17 as [DOOR WORKING PROPERLY?], Q18 as [WALLS PROPER?], Q19 as [CEILING PROPER?], Q20 as [DOOR MAT AVAILABLE?], Q21 as [IS AC Installed at Site], Q22 as [AC WORKING PROPERLY?], Q23 as [AC Connected with timer?], Q24 as [AC connected with meter?], Q25 as [UPS AND BATTERIES WORKING?], Q26 as [CAMERAS AVAILABLE AT SITE?], Q27 as [Signage & Lollipop is working?], Q28 as [ANY ISSUE AFFECTNG THE TRANSACTIONS?], Q29 as [Feedback from Neighboring Shops/ LL], Q30 as [VSAT Ballasting], Q31 as [Mandatory Notices], Q32 as [Electricity Bill Payment], Q33 as [Any New Bills at Site], Q34 as [Submeter Reading], Q35 as [Any Power Theft Noticed], Q36 as [Multimeter Reading of Earthing], Q37 as [Is the Visit along with PM Engineer and CRA], Q38 as [If Yes: PM Docket No:], Q39 as [If Yes: Is PM Done properly], Q40 as [Cash Tallied with Admin Balance, Machine Counter and Physical Couting],q57 as [Housekeeping done on site?],Q58 as [Deep cleaning done on site?],Q42 as [Raw Power Status], Q43 as [Multimeter P N Reading],Q44 as [Multimeter P E Reading],Q45 as [Multimeter N E Reading], Q46 as [UPS P N Reading],Q47 as [UPS P E Reading],Q48 as [UPS N E Reading],Q49 as [Power availability in a day (no of Hrs)],Q50 as [Frequency of power failure in a day],Q51 as [Is the ODU-IDU Connection done as per requirement], Q52 as [Other ATMs nearby (range within 500 meters)],Q53 as [Stabilizer available],Q54 as [Isolation available],Q55 as [Monkey cage available],ISNULL(Q59,'NA') as [Is CAM1 Working?],ISNULL(Q60,'NA') as [Is CAM2 Working?],ISNULL(Q61,'NA') as [Is the Image getting stored?],ISNULL(Q62,'NA') as [Is EJ getting pulled?], isnull(Q63,'NA') as [IS ATM POWER SWITCH AVAILABLE INSIDE BACK ROOM ?],isnull(Q64,'NA')as [HEIGHT OF THE ATM ROOM ?],isnull(Q65,'NA') as [IS RAMP AVAILABLE ? ], isnull(Q66,'NA') as [IS LAN ROUTING PROPER ? ],isnull(Q67,'NA') as [Space availability in ATM backroom for 2 units with dimensions 2X2 each ? ], isnull(Q68,'NA') as [Space availability in ATM lobby for 2 units with size dimensions 2X2 each ? ],isnull(Q69,'NA') as [Network feasibility Voice ? ],isnull(Q70,'NA') as [Network feasibility Data ? ],isnull(Q71,'NA') as [Is shutter open close activity happening ? ],isnull(Q72,'NA') as [Shutter open/Close activity happening daily ? ],remark as [OTHER REMARK], [AC SRNO],[AC MAKE],[ATM SRNO],[ATM MAKE],[BAT1 SRNO],[BAT1 MAKE], [BAT2 SRNO],[BAT2 MAKE],[BAT3 SRNO],[BAT3 MAKE],[MODEM SRNO],[MODEM MAKE],[UPS SRNO],[UPS MAKE], d.lat as[LATITUDE], d.lon as[LONGITUDE] from dr_ctp d inner join atms a on d.atmid=a.atmid join users u on u.userid=d.userid left outer join (select vid,[AC_SRNO] as [AC SRNO],[AC_MAKE] as [AC MAKE],[ATM_SRNO] as [ATM SRNO],[ATM_MAKE] as [ATM MAKE], [BAT1_SRNO] as [BAT1 SRNO],[BAT1_MAKE] as [BAT1 MAKE],[BAT2_SRNO] as [BAT2 SRNO],[BAT2_MAKE] as [BAT2 MAKE], [BAT3_SRNO] as [BAT3 SRNO],[BAT3_MAKE] as [BAT3 MAKE],[MODEM_SRNO] as [MODEM SRNO],[MODEM_MAKE] as [MODEM MAKE], [UPS_SRNO] as [UPS SRNO],[UPS_MAKE] as [UPS MAKE] from (SELECT vid,type+'_'+col AS col,value FROM ( SELECT vid,srno as [Srno],make AS [Make], type FROM scan GROUP BY vid,srno,make,type) rt unpivot ( value FOR col in (srno,make))unpiv )tp pivot ( MAX(value) FOR col in ([AC_SRNO],[AC_MAKE],[ATM_SRNO],[ATM_MAKE],[BAT1_SRNO],[BAT1_MAKE], [BAT2_SRNO],[BAT2_MAKE],[BAT3_SRNO],[BAT3_MAKE],[MODEM_SRNO],[MODEM_MAKE],[UPS_SRNO],[UPS_MAKE])) piv ) s on d.vid=s.vid where d.userid like '" + users + "' and a.RCM like '" + Session["sess_username"] + "' and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + ") order by convert(date,vdate) desc,convert(time,vtime) desc"; } bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void btn_search_Click(object sender, EventArgs e) { string sql = ""; #region Code to fetch data from DR_CTP string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } if (txtuser.Text == "") { // sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],Ldate as [Date] from // (SELECT userid, DistanceTraveled,ldate // FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN // FROM Distance // where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + // @"' and userid in (select userid from users where RM like '" + Session["sess_username"] + @"' and userid like '"+users+@"') // ) AS t // WHERE RN = 1 ) x // left outer join // (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID // where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' // group by userid,vdate) y // on x.userid=y.userid and x.ldate=y.vdate // order by convert(date,ldate) asc,userid asc"; sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],Ldate as [Date] from (Select userid,sum(convert(float,distancetraveled)) [DistanceTraveled],ldate from ( Select userid,sum(convert(float,distancetraveled)) [DistanceTraveled],ldate from Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where RM like '" + Session["sess_username"] + @"' and userid like '" + users + @"') and vid='punchout' group by userid,ldate union all SELECT userid, case when vid='PUNCHOUT' then '0' else DistanceTraveled end [DistanceTraveled],ldate FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN FROM Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where RM like '" + Session["sess_username"] + @"' and userid like '" + users + @"')) tbl1 where rn=1 ) tbl group by userid ,ldate ) x left outer join (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' group by userid,vdate) y on x.userid=y.userid and x.ldate=y.vdate order by convert(date,ldate) asc,userid asc"; } else { // sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],Ldate as [Date] from // (SELECT userid, DistanceTraveled,ldate // FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN // FROM Distance // where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + // @"' and userid in (select userid from users where state in (" + txtuser.Text + @") and RM like '" + Session["sess_username"] + @"' and userid like '" + users + @"') // ) AS t // WHERE RN = 1 ) x // left outer join // (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID // where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + @") // group by userid,vdate) y // on x.userid=y.userid and x.ldate=y.vdate // order by convert(date,ldate) asc,userid asc"; sql = @"Select x.Userid,[DistanceTraveled],case when [Sites Audited] is null then '0' else [Sites Audited] end as [Sites Audited],Ldate as [Date] from (Select userid,sum(convert(float,distancetraveled)) [DistanceTraveled],ldate from ( Select userid,sum(convert(float,distancetraveled)) [DistanceTraveled],ldate from Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where state in (" + txtuser.Text + @") and RM like '" + Session["sess_username"] + @"' and userid like '" + users + @"') and vid='punchout' group by userid,ldate union all SELECT userid, case when vid='PUNCHOUT' then '0' else DistanceTraveled end [DistanceTraveled],ldate FROM(SELECT Distance.*, ROW_NUMBER() OVER (PARTITION BY ldate, userid ORDER BY CONVERT(date, ldate) desc, ltime desc) AS RN FROM Distance where convert(date,Ldate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"' and userid in (select userid from users where state in (" + txtuser.Text + @") and RM like '" + Session["sess_username"] + @"' and userid like '" + users + @"')) tbl1 where rn=1 ) tbl group by userid ,ldate ) x left outer join (Select count(vid) as 'Sites Audited',userid,vdate from dr_ctp dc inner join atms a on a.ATMID = dc.ATMID where convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + @") group by userid,vdate) y on x.userid=y.userid and x.ldate=y.vdate order by convert(date,ldate) asc,userid asc"; } bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; //chk123.Visible = false; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void btn_search_Click(object sender, EventArgs e) { if (ddrole.SelectedValue == "%") { role = "u.role in ('AO','DE','CM','CH','RM')"; } else { role = "u.role like '" + ddrole.SelectedValue + "'"; } string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All" || users == null) { users = "%"; } #region Code to fetch data from DR_CTP string sql = ""; if (txtuser.Text == "") { sql = @"SELECT distinct u.Userid as [USER ID],role, a.ce, a.cm, u.username as [Username], a.atmid as [ATM ID], a.bankid as [BANK], a.addressline1 as [ADDRESS], a.city as [CITY], a.state as [STATE] from usermap um join users u on u.userid=um.userid join atms a on a.atmid=um.atmid where um.status <> 'DEL' and u.userid like '" + users + "' and " + role + " order by u.username asc"; } else { sql = @"SELECT distinct u.Userid as [USER ID],role, a.ce, a.cm, u.username as [Username], a.atmid as [ATM ID], a.bankid as [BANK], a.addressline1 as [ADDRESS], a.city as [CITY], a.state as [STATE] from usermap um join users u on u.userid=um.userid join atms a on a.atmid=um.atmid where um.status <> 'DEL' and u.userid like '" + users + "' and " + role + " and a.state in (" + txtuser.Text + ") order by u.username asc"; } if (Session["sess_role"].ToString() == "CH") { sql = sql.Replace("and " + role, "and " + role + " and a.ch ='" + Session["sess_username"] + "'"); } if (Session["sess_role"].ToString() == "RM") { sql = sql.Replace("and " + role, "and " + role + " and a.rm ='" + Session["sess_username"] + "'"); } bucket.BindGrid(GridView1, sql); //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; ImageButton1.Visible = false; div1.Visible = true; } else { div1.Visible = true; ImageButton1.Visible = true; Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3); } /*------------------------------------------------------------------------------------------------*/ #endregion }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_frmDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); try { string q = ""; if (txtuser.Text == "") { q = @"select x.userid as[USERID],username as [USERNAME],role as [ROLE],rcm as [RCM],state as [STATE],convert(varchar(10),convert(date,vdate),103) as [LAST VISIT DATE],vtime as [LAST VISIT TIME] from (SELECT userid,atmid,vdate,vtime FROM (SELECT DR_CTP.*, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY CONVERT(date, vdate) desc, vtime DESC) AS RN FROM DR_CTP ) AS t WHERE RN = 1) x inner join (Select userid,role,username,rcm,state from users where CH like '" + Session["sess_username"].ToString() + @"' and userid not in (Select userid from dr_ctp where Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"') and role <> 'admin' ) y on x.userid=y.userid"; } else { q = @"select x.userid as[USERID],username as [USERNAME],role as [ROLE],rcm as [RCM],state as [STATE],convert(varchar(10),convert(date,vdate),103) as [LAST VISIT DATE],vtime as [LAST VISIT TIME] from (SELECT userid,atmid,vdate,vtime FROM (SELECT DR_CTP.*, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY CONVERT(date, vdate) desc, vtime DESC) AS RN FROM DR_CTP ) AS t WHERE RN = 1) x inner join (Select userid,role,username,rcm,state from users where state in (" + txtuser.Text + @") and CH like '" + Session["sess_username"].ToString() + @"' and userid not in (Select userid from dr_ctp where Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + @"') and role <> 'admin' ) y on x.userid=y.userid"; } //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, q); /*------------------------------------------------------------------------------------------------*/ /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ } catch { Response.Write("<script>alert('Fields can not be left blank')</script>"); } }
protected void btn_search_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } try { string sql = ""; string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } #region Code to fetch data from DR_CTP if (txtuser.Text == "") { sql = @"Select c.vid,c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103) as [Audit Date],vtime as [Audit Time] from DR_CTP c, ATMs a where c.atmid=a.atmid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "' and RCM like '" + Session["sess_username"] + "' and userid like '" + users + "' order by Convert(date,vdate) desc,vtime desc"; } else { sql = @"Select c.vid,c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103) as [Audit Date],vtime as [Audit Time] from DR_CTP c, ATMs a where c.atmid=a.atmid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "' and RCM like '" + Session["sess_username"] + "' and userid like '" + users + "' and a.state in (" + txtuser.Text + ") order by Convert(date,vdate) desc,vtime desc"; } #endregion /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); /*------------------------------------------------------------------------------------------------*/ /*------------------------------------------------------------------------------------------------*/ /* If no rows returned display null error or fetch count * /*------------------------------------------------------------------------------------------------*/ if (GridView1.Rows.Count.Equals(0)) { Label1.Visible = true; Label3.Visible = false; } else { Label3.Visible = true; Label1.Visible = false; Label3.Text = bucket.CountRows(GridView1, Label3);// GridView1.Rows.Count.ToString() + " records matching your criteria."; } /*------------------------------------------------------------------------------------------------*/ } catch { Response.Write("<script>alert('Fields can not be left blank')</script>"); } }