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 string GetQuery() { 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 = ""; if (txtuser.Text != "") { q = @"SELECT '' as [REPORT],'' as [DOWNLOAD],c.Vid AS [VISIT ID],a.siteid,a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103)+' '+vtime as [Audit Date TIme] ,dbo.udf_GetNumeric(version) as [VERSION] 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 Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "' and a.state in (" + txtuser.Text + ")"; } else { q = @"SELECT '' as [REPORT],'' as [DOWNLOAD],c.Vid AS [VISIT ID],a.siteid,a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103)+' '+vtime as [Audit Date TIme] ,dbo.udf_GetNumeric(version) as [VERSION] 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 Convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'"; } bucket.BindGrid(GridView1, q); return(q); }
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 string GetQuery() { 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 = @"SELECT '' as [REPORT],'' as [DOWNLOAD],c.Vid AS [VISIT ID],c.ATMID as [ATM], a.Location, a. Bankid as [Bank], a.Client as [Client], convert(varchar(10),convert(date,vdate),103)+' '+vtime as [Audit Date TIme],dbo.udf_GetNumeric(version) as [VERSION] 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 convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "'"; if (txtuser.Text != "") { sql += " and a.state in (" + txtuser.Text + ") "; } // Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); return(sql); }
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 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:; //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 string GetQuery() { 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"); } if (ddrole.SelectedValue == "%") { role = "u.role in ('AO','DE','CM','RM','CH')"; } else { role = "u.role like '" + ddrole.SelectedValue + "'"; } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } string sql = ""; if (txtuser.Text == "") { sql = @"SELECT '' as [Report],'' as [DOWNLOAD],c.Vid AS [VISIT ID],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], dbo.udf_GetNumeric(version) as [VERSION] from DR_CTP c, ATMs a, Users u where c.atmid=a.atmid and c.userid=u.userid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "' and " + role + " and c.userid like '" + users + "' order by Convert(date,vdate) desc,vtime desc"; } else { sql = @"SELECT '' as [Report],'' as [DOWNLOAD],c.Vid AS [VISIT ID],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],dbo.udf_GetNumeric(version) as [VERSION] from DR_CTP c, ATMs a, Users u where c.atmid=a.atmid and c.userid=u.userid and Convert(date,vdate) between '" + txt_frmDate.Text + "' AND '" + txt_toDate.Text + "' and " + role + " and c.userid like '" + users + "' and a.state in (" + txtuser.Text + ") order by Convert(date,vdate) desc,vtime desc"; } //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); return(sql); }
protected string GetQuery() { 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"); string sql = ""; if (txtuser.Text != "") { sql = @"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 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 { sql = @"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 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, sql); return sql; }
protected string GetQuery() { if (string.IsNullOrEmpty(txt_toDate.Text.Trim())) { txt_toDate.Text = DateTime.Now.ToString("MM'/'dd'/'yyyy"); } if (ddrole.SelectedValue == "%") { role = "u.role in ('AO','DE','CM','RM','CH')"; } else { role = "u.role like '" + ddrole.SelectedValue + "'"; } txt_frmDate.Attributes.Add("readonly", "readonly"); txt_toDate.Attributes.Add("readonly", "readonly"); string users = Request.Form[DropDownList1.UniqueID]; hdfUsers.Value = users; if (users == "All") { users = "%"; } string q = ""; 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,Users u where atmstatus <> 'Inactive' and u.userid like '" + users + "' and " + role + " 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,Users u where atmstatus <> 'Inactive' and u.userid like '" + users + "' and " + role + " 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); return(q); }
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 string GetQuery() { string sql = ""; if (ddrole.SelectedValue == "%") { role = "u.role in ('AO','DE','CM','RM','CH')"; } else { role = "u.role like '" + ddrole.SelectedValue + "'"; } #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 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,case when d.Siteid='NA' then '-' else a.city end 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 join users u on d.userid=u.userid where d.userid like '" + users + "' and " + role + " 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,case when d.Siteid='NA' then '-' else a.city end 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 and a.state in (" + txtuser.Text + @") join users u on d.userid=u.userid where d.userid like '" + users + "' and " + role + " and convert(date,ldate) = '" + txt_frmDate.Text + "' order by d.ltime desc"; } drawmap(users); bucket.BindGrid(GridView1, sql); // Response.Write(sql); return(sql); #endregion }
protected string GetQry() { if (ddrole.SelectedValue == "%") { role = "u.role in ('AO','DE','CM','RM','CH')"; } 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 '' 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],dbo.udf_GetNumeric(version) as [VERSION] 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 + "' 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],dbo.udf_GetNumeric(version) as [VERSION] 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 + "' order by convert(date,vdate) desc,convert(time,vtime) desc"; } bucket.BindGrid(GridView1, sql); return(sql); #endregion }
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 string GetQuery() { 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"); 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 vdate 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 + @") 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 vdate 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 group by u.userid ,vdate,username order by CONVERT(date, vdate) desc"; } //Response.Write(sql); /*------------------------------------------------------------------------------------------------*/ /* Bind query to grid view * /*------------------------------------------------------------------------------------------------*/ bucket.BindGrid(GridView1, sql); return(sql); }
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) { #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 Page_Load(object sender, EventArgs e) { string qOnline = "select count(l.userid) as 'online' from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery>5 and DATEDIFF(MINUTE,CONVERT(VARCHAR(19), GETDATE(),120),replace(B_date,'/','-') + ' ' + l_time) > -60 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; string[] aonline = bucket.verifyReader(qOnline, "online"); lbl_Online.Text = aonline[0]; string qOffline = "select count(*) as 'offline' from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery>5 and DATEDIFF(MINUTE,CONVERT(VARCHAR(19), GETDATE(),120),replace(B_date,'/','-') + ' ' + l_time) < -60 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; string[] aOffline = bucket.verifyReader(qOffline, "offline"); lbl_Offline.Text = aOffline[0]; string qBatteryLow = "select count(*) as 'BL' from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery<=5 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; string[] aBatteryLow = bucket.verifyReader(qBatteryLow, "BL"); lbl_Battery.Text = aBatteryLow[0]; if (Request.QueryString.ToString() != "") { string sqlOffline = null; if (Request.QueryString["Online"] == "True") { sqlOffline = "select l.userid as [User], case when rem_battery>0 then ltrim(rtrim(rem_battery)) + '%' when rem_battery<0 then '0%' end as [Battery Remaining],'LocatorOne_1.aspx?userid=' + l.userid as [View On Map], substring(B_date,4,3) + substring(B_date,1,2)+ substring(B_date,6,5) + ' ' + L_time as [Last Updated On] from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery>5 and DATEDIFF(MINUTE,CONVERT(VARCHAR(19), GETDATE(),120),replace(B_date,'/','-') + ' ' + l_time) > -60 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; } else if (Request.QueryString["Offline"] == "True") { sqlOffline = "select l.userid as [User], case when rem_battery>0 then ltrim(rtrim(rem_battery)) + '%' when rem_battery<0 then '0%' end as [Battery Remaining],'LocatorOne_1.aspx?userid=' + l.userid as [View On Map], substring(B_date,4,3) + substring(B_date,1,2)+ substring(B_date,6,5) + ' ' + L_time as [Last Updated On] from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery>5 and DATEDIFF(MINUTE,CONVERT(VARCHAR(19), GETDATE(),120),replace(B_date,'/','-') + ' ' + l_time) < -60 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; } else if (Request.QueryString["Battery"] == "True") { sqlOffline = "select l.userid as [User],case when rem_battery>0 then ltrim(rtrim(rem_battery)) + '%' when rem_battery<0 then '0%' end as [Battery Remaining],'LocatorOne_1.aspx?userid=' + l.userid as [View On Map], substring(B_date,4,3) + substring(B_date,1,2)+ substring(B_date,6,5) + ' ' + L_time as [Last Updated On] from location l inner join users u on l.userid=u.userid where B_date<>'' and Rem_Battery<=5 and u.status<>'DEL' and RM like '" + Session["Sess_username"] + "' and role in ('AO','DE')"; } bucket.BindGrid(GridView1, sqlOffline); GridView1.AllowPaging = false; GridView1.DataBind(); } }
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 Page_Load(object sender, EventArgs e) { string ver = @"select dbo.udf_GetNumeric(REPLACE(version,'_','')) as version from DR_CTP where vid='" + Request.QueryString["auditid"] + "'"; string[] v = { "version" }; string[] v1 = bucket.xread(ver, v); tr51.Visible = true; tr52.Visible = true; tr53.Visible = true; trATMShop.Visible = true; if (Convert.ToDecimal(v1[0]) < Convert.ToDecimal("2.6")) { lbl43.Visible = false; lbl44.Visible = false; lbl45.Visible = false; lbl46.Visible = false; lbl47.Visible = false; lbl48.Visible = false; lbl49.Visible = false; lbl50.Visible = false; lbl51.Visible = false; lbl52.Visible = false; lbl53.Visible = false; lbl54.Visible = false; lbl55.Visible = false; lbl56.Visible = false; lbl57.Visible = false; lbl58.Visible = false; //fire ext expired lbl59.Visible = true; lbl60.Visible = false; lbl11.Visible = false; lblHK.Visible = false; lblDClean.Visible = false; //Label43.Visible = false; //Label44.Visible = false; //Label45.Visible = false; //Label46.Visible = false; //Label47.Visible = false; //Label48.Visible = false; //Label49.Visible = false; //Label50.Visible = false; //Label51.Visible = false; //Label52.Visible = false; //Label53.Visible = false; //Label54.Visible = false; //Label55.Visible = false; //Label56.Visible = false; //Label57.Visible = false; //Label58.Visible = false; } else if ((Convert.ToDecimal(v1[0]) == Convert.ToDecimal("2.6")) || (Convert.ToDecimal(v1[0]) == Convert.ToDecimal("2.7"))) { lbl36.Visible = false; lblHK.Visible = false; lblDClean.Visible = false; // Label36.Visible = false; } else { lbl36.Visible = true; lbl44.Visible = false; lbl45.Visible = false; lbl46.Visible = false; lbl47.Visible = false; lblHK.Visible = true; lblDClean.Visible = true; } if (!Page.IsPostBack) { if (Request.QueryString["auditid"] != "") { string querystring = Request.QueryString["auditid"]; int qno = 0; string q1 = @"SELECT a.atmid,a.bankid,a.addressline1,city,a.region,a.state,a.pin,'' as [ZONE],vid,convert(varchar(10),convert(date,vdate),103) as vdate,vtime,d.userid,d.visittype,case when d.visitremark is null then 'NA' else d.visitremark end as visitremark,dbo.udf_GetNumeric(REPLACE(version,'_','')) as version FROM Atms a INNER JOIN DR_CTP d ON a.atmid = d.ATMID where vid='" + querystring + "'"; string[] c1 = { "ATMID", "bankid", "Addressline1", "City", "Region", "STATE", "PIN", "ZONE", "vid", "vdate", "userid", "vtime", "visittype", "visitremark", "version" }; string[] a1 = bucket.xread(q1, c1); lbl_atmID.Text = a1[0]; lbl_bankid.Text = a1[1]; lbl_add.Text = a1[2]; lbl_city.Text = a1[3]; lbl_reg.Text = a1[4]; lbl_state.Text = a1[5]; lbl_pin.Text = a1[6]; lbl_zone.Text = a1[7]; lbl_vid.Text = a1[8]; lbl_vdate.Text = a1[9]; lbl_visitedby.Text = a1[10]; lbl_vtime.Text = a1[11]; lbl_typ.Text = a1[12]; lbl_rmk.Text = a1[13]; #region add questions lbl_q1.Text = "1. ATM MACHINE WORKING FINE?"; lbl_q2.Text = "2. CARETAKER AVAILABLE?"; lbl_q3.Text = " I. CARETAKER NAME?"; lbl_q4.Text = " II. CARETAKER NUMBER?"; lbl_q5.Text = "3. CLEANING DONE REGULARLY?"; //lbl_q6.Text = " I. HOW SEVERE IS THE UPKEEP ISSUE"; lbl_q7.Text = "4. FLOORING PROPER?"; lbl_q8.Text = "5. DUST BIN OK?"; lbl_q9.Text = "6. BACKROOM OK?"; lbl_q10.Text = "7. WRITING LEDGE AND VMS PROPER?"; lbl_q42.Text = "8. SIGNAGE & LOLLIPOP CLEANED OR NOT?"; lbl_q11.Text = "9.FIRE EXTINGUISHER AVAILABLE ? "; if (Convert.ToDecimal(a1[14]) < Convert.ToDecimal("2.6")) { lbl_q59.Text = "9.IS THE FIRE EXTINGUISHER EXPIRED?"; } else { lbl_q59.Text = "I.IS THE FIRE EXTINGUISHER EXPIRED?"; } lbl_q12.Text = "10. IS RNM OK?"; //lbl_q13.Text = " I. HOW SEVERE IS THE RNM ISSUE"; //lbl_q14.Text = "5.2 FLOORING PROPER?"; lbl_q14.Text = "11. LIGHTS OK?"; lbl_q15.Text = "12. No. Of CFL Working"; lbl_q16.Text = "13. GLOW SIGN PROPER?"; lbl_q17.Text = "14. DOOR WORKING PROPERLY?"; lbl_q18.Text = "15. WALLS PROPER?"; lbl_q19.Text = "16. CEILING PROPER?"; lbl_q20.Text = "17. DOOR MAT AVAILABLE?"; lbl_q21.Text = "18. IS AC INSTALLED AT SITE"; lbl_q22.Text = " I. AC WORKING PROPERLY?"; lbl_q23.Text = " II. AC CONNECTED WITH TIMER?"; lbl_q24.Text = " III. AC CONNECTED WITH METER?"; lbl_q25.Text = "19. UPS AND BATTERIES WORKING?"; lbl_q26.Text = "20. CAMERAS AVAILABLE AT SITE?"; lbl_q27.Text = "21. SIGNAGE & LOLLIPOP IS WORKING?"; lbl_q28.Text = "22. ANY ISSUE AFFECTNG THE TRANSACTIONS?"; lbl_q29.Text = " I. FEEDBACK FROM NEIGHBORING SHOPS/ LL"; lbl_q30.Text = "23. VSAT BALLASTING"; lbl_q31.Text = "24. MANDATORY NOTICES"; lbl_q32.Text = "25. ELECTRICITY BILL PAYMENT"; lbl_q33.Text = "I. ANY NEW BILLS AT SITE"; lbl_q34.Text = "II. SUBMETER READING"; lbl_q35.Text = "26. ANY POWER THEFT NOTICED"; lbl_q36.Text = "27. MULTIMETER READING OF EARTHING"; if (Convert.ToDecimal(a1[14]) <= Convert.ToDecimal("2.7")) { lbl_q37.Text = "27. IS THE VISIT ALONG WITH PM ENGINEER AND CRA?"; } else { lbl_q37.Text = "28. IS THE VISIT ALONG WITH PM ENGINEER AND CRA?"; } lbl_q38.Text = " I. PM DOCKET NO:"; lbl_q39.Text = " II. IS PM DONE PROPERLY?"; lbl_q40.Text = " III. CASH TALLIED WITH ADMIN BALANCE, MACHINE COUNTER AND PHYSICAL COUTING"; //new 2 ques from ver2.8 onwards lbl_q61.Text = "29. HOUSEKEEPING DONE ON SITE?"; lbl_q62.Text = "30. DEEP CLEANING DONE ON SITE?"; if (Convert.ToDecimal(a1[14]) <= Convert.ToDecimal("2.7")) { lbl_q43.Text = " 28. RAW POWER STATUS: "; lbl_q44.Text = " 29.MULTIMETER READING OF EARTHING: "; lbl_q45.Text = " I.P N READING: "; lbl_q46.Text = " II.P E READING: "; lbl_q47.Text = " III.N E READING: "; lbl_q48.Text = " 30.UPS POWER (VOLT): "; lbl_q49.Text = " I.P N READING: "; lbl_q50.Text = " II.P E READING: "; lbl_q51.Text = " II.N E READING: "; lbl_q52.Text = "31.POWER AVAILABILITY IN A DAY (NO OF HRS)"; lbl_q53.Text = "32.FREQUENCY OF POWER FAILURE IN A DAY"; lbl_q54.Text = "33.IS THE ODU-IDU CONNECTION DONE AS PER REQUIREMENT?"; lbl_q55.Text = "34.OTHER ATMS NEARBY (RANGE WITHIN 500 METERS) ?"; lbl_q56.Text = "35.STABILIZER AVAILABLE ?"; lbl_q57.Text = "36.ISOLATION AVAILABLE ?"; lbl_q58.Text = "37.MONKEY CAGE AVAILABLE ?"; lbl_qu59.Text = "38.IS CAM1 WORKING?"; lbl_qu60.Text = "39.IS CAM2 WORKING?"; lbl_qu61.Text = "40.IS THE IMAGE GETTING STORED?"; lbl_qu62.Text = "41.IS EJ GETTING PULLED?"; lbl_q60.Text = "42.OTHER REMARK ?"; } else { lbl_q43.Text = " 31. RAW POWER STATUS: "; #region notiuseincurver lbl_q44.Text = " 32.MULTIMETER READING OF EARTHING: "; lbl_q45.Text = " I.P N READING: "; lbl_q46.Text = " II.P E READING: "; lbl_q47.Text = " III.N E READING: "; #endregion lbl_q48.Text = " 32.UPS POWER (VOLT): "; lbl_q49.Text = " I.P N READING: "; lbl_q50.Text = " II.P E READING: "; lbl_q51.Text = " II.N E READING: "; lbl_q52.Text = "33.POWER AVAILABILITY IN A DAY (NO OF HRS)"; lbl_q53.Text = "34.FREQUENCY OF POWER FAILURE IN A DAY"; lbl_q54.Text = "35.IS THE ODU-IDU CONNECTION DONE AS PER REQUIREMENT?"; lbl_q55.Text = "36.OTHER ATMS NEARBY (RANGE WITHIN 500 METERS) ?"; lbl_q56.Text = "37.STABILIZER AVAILABLE ?"; lbl_q57.Text = "38.ISOLATION AVAILABLE ?"; lbl_q58.Text = "39.MONKEY CAGE AVAILABLE ?"; lbl_qu59.Text = "40.IS CAM1 WORKING?"; lbl_qu60.Text = "41.IS CAM2 WORKING?"; lbl_qu61.Text = "42.IS THE IMAGE GETTING STORED?"; lbl_qu62.Text = "43.IS EJ GETTING PULLED?"; lbl_q60.Text = "44.OTHER REMARK ?"; } if (Convert.ToDecimal(a1[14]) >= Convert.ToDecimal(3.2)) { lbl63.Visible = true; lbl64.Visible = true; lbl65.Visible = true; lbl_q63.Text = "45.IS ATM POWER SWITCH AVAILABLE INSIDE BACK ROOM ?"; lbl_q64.Text = "46.HEIGHT OF THE ATM ROOM ?"; lbl_q65.Text = "47.IS RAMP AVAILABLE ?"; } if (Convert.ToDecimal(a1[14]) >= Convert.ToDecimal(3.3)) { lbl66.Visible = true; lbl_q66.Text = "48.IS LAN ROUTING PROPER ?"; } if (Convert.ToDecimal(a1[14]) >= Convert.ToDecimal(3.7)) { lbl67.Visible = true; lbl68.Visible = true; lbl69.Visible = true; lbl70.Visible = true; lbl71.Visible = true; lbl72.Visible = true; lbl_q67.Text = "49.SPACE AVAILABILITY IN ATM BACKROOM FOR 2 UNITS WITH DIMENSIONS 2X2 EACH ?"; lbl_q68.Text = "50.SPACE AVAILABILITY IN ATM LOBBY FOR 2 UNITS WITH SIZE DIMENSIONS 2X2 EACH ?"; lbl_ATMShop.Text = "51.ATM SHOP AREA"; lbl_51.Text = "I.TOTAL ATM AREA ALLOCATED SQ. FEET"; lbl_52.Text = "II.ATM LOBBY SIZE IN SQ. FEET"; lbl_53.Text = "III.ATM BACK ROOM SIZE IN SQ. FEET "; lbl_q69.Text = "52.NETWORK FEASIBILITY VOICE ?"; lbl_q70.Text = "53.NETWORK FEASIBILITY DATA ?"; lbl_q71.Text = "54.IS SHUTTER OPEN CLOSE ACTIVITY HAPPENING ?"; lbl_q72.Text = "55.SHUTTER OPEN/CLOSE ACTIVITY HAPPENING DAILY ?"; } //lbl_q42.Text = "20. ANY OTHER ISSUES NOTICED?"; #endregion string q = "select Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18,Q19,Q20,Q21,Q22,Q23,Q24,Q25,Q26,Q27,Q28,Q29,Q30,Q31,Q32,Q33,Q34,Q35,Q36,Q37,Q38,Q39,Q40,Q41,Q42,Q43,Q44,Q45,Q46,Q47,Q48,Q49,Q50,Q51,Q52,Q53,Q54,Q55,Q56,ltrim(rtrim(pix)) as 'pix',Q57,remark,Q58,isnull(Q59,'NA') as Q59,isnull(Q60,'NA') as Q60,isnull(Q61,'NA') as Q61,isnull(Q62,'NA') as Q62,isnull(Q63,'NA') as Q63,isnull(Q64,'NA') as Q64,isnull(Q65,'NA') as Q65,isnull(Q66,'NA') as Q66,isnull(Q67,'NA') as Q67,isnull(Q68,'NA') as Q68,isnull(Q69,'NA') as Q69,isnull(Q70,'NA') as Q70,isnull(Q71,'NA') as Q71,isnull(Q72,'NA') as Q72,isnull(Q73,'NA') as Q73,isnull(Q74,'NA') as Q74,isnull(Q75,'NA') as Q75 from dr_ctp where vid='" + querystring + "'"; string[] c2 = { "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q41", "Q12", "Q13", "Q14", "Q15", "Q16", "Q17", "Q18", "Q19", "Q20", "Q21", "Q22", "Q23", "Q24", "Q25", "Q26", "Q27", "Q28", "Q29", "Q30", "Q31", "Q32", "Q33", "Q34", "Q35", "Q36", "Q37", "Q38", "Q39", "Q40", "Q42", "Q43", "Q44", "Q45", "Q46", "Q47", "Q48", "Q49", "Q50", "Q51", "Q52", "Q53", "Q54", "Q55", "Q56", "pix", "Q57", "remark", "Q58", "Q59", "Q60", "Q61", "Q62", "Q63", "Q64", "Q65", "Q66", "Q67", "Q68", "Q69", "Q70", "Q71", "Q72", "Q73", "Q74", "Q75" }; string[] a = bucket.xread(q, c2); Label[] l = new Label[77]; l[0] = Label1; l[1] = Label2; l[2] = Label3; l[3] = Label4; l[4] = Label5; //l[5] = Label6; l[6] = Label7; l[7] = Label8; l[8] = Label9; l[9] = Label10; //fire ext expired l[10] = Label59; l[11] = Label42; //Signage & Lollypop Cleaned or not? l[12] = Label12; //l[13] = Label13; l[14] = Label14; l[15] = Label15; l[16] = Label16; l[17] = Label17; l[18] = Label18; l[19] = Label19; l[20] = Label20; l[21] = Label21; l[22] = Label22; l[23] = Label23; l[24] = Label24; l[25] = Label25; l[26] = Label26; l[27] = Label27; l[28] = Label28; l[29] = Label29; l[30] = Label30; l[31] = Label31; l[32] = Label32; l[33] = Label33; l[34] = Label34; l[35] = Label35; l[36] = Label36; l[37] = Label37; l[38] = Label38; l[39] = Label39; l[40] = Label40; l[41] = Label41; l[41] = Label43; l[42] = Label45; l[43] = Label46; l[44] = Label47; // l[46] = Label48; l[45] = Label49; l[46] = Label50; l[47] = Label51; l[48] = Label52; l[49] = Label53; l[50] = Label54; l[51] = Label55; l[52] = Label56; l[53] = Label57; l[54] = Label58; //fire ext available l[55] = Label11; l[58] = Label60; l[57] = Label61; l[59] = Label62; l[60] = lbl_an59; l[61] = lbl_an60; l[62] = lbl_an61; l[63] = lbl_an62; l[64] = Label63; l[65] = Label64; l[66] = Label65; l[67] = Label66; l[68] = Label67; l[69] = lbl_A51; l[70] = lbl_A52; l[71] = lbl_A53; l[72] = Label68; l[73] = Label69; l[74] = Label70; l[75] = Label71; l[76] = Label72; //l[11] = Label12; l[12] = Label13; l[13] = Label14; l[14] = Label15; l[15] = Label16; l[16] = Label17; l[17] = Label18; l[18] = Label19; //l[19] = Label20; l[20] = Label21; l[21] = Label22; l[22] = Label23; l[23] = Label24; l[24] = Label25; l[25] = Label26; l[26] = Label27; l[27] = Label28; //l[28] = Label29; l[29] = Label30; l[30] = Label31; l[31] = Label32; l[32] = Label33; l[33] = Label34; l[34] = Label35; l[35] = Label36; l[36] = Label37; //l[37] = Label38; l[38] = Label39; l[39] = Label40; l[40] = Label41; l[42] = Label43; l[43] = Label44; l[44] = Label45; l[45] = Label46; //l[46] = Label47; l[47] = Label48; l[48] = Label49; l[49] = Label50; int i = 0; while (i < 77) { try { if (i == 34) { beautify(a[i].Trim(), l[i], true); } else { beautify(a[i].Trim(), l[i], false); } } catch { } i++; } string bind = "Select siteid as [SITE ID],userid as [USER ID],type as [ASSET],srno as [SRNO],make as [MAKE],scanstatus as [STATUS] from Scan where vid='" + querystring + "'"; bucket.BindGrid(GridView1, bind); show_available_images(Convert.ToInt32(a[56]) - 1, lbl_vid.Text); show_CheckList_images(65, lbl_vid.Text); } } }
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 Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if (Request.QueryString["auditid"] != "") { string querystring = Request.QueryString["auditid"]; int qno = 0; string q1 = @"SELECT a.atmid,a.bankid,a.addressline1,city,a.region,a.state,a.pin,'' as [ZONE],vid,vdate,vtime,d.userid FROM Atms a INNER JOIN DR_CTP d ON a.atmid = d.ATMID where vid='" + querystring + "'"; string[] c1 = { "ATMID", "bankid", "Addressline1", "City", "Region", "STATE", "PIN", "ZONE", "vid", "vdate", "userid", "vtime" }; string[] a1 = bucket.xread(q1, c1); lbl_atmID.Text = a1[0]; lbl_bankid.Text = a1[1]; lbl_add.Text = a1[2]; lbl_city.Text = a1[3]; lbl_reg.Text = a1[4]; lbl_state.Text = a1[5]; lbl_pin.Text = a1[6]; lbl_zone.Text = a1[7]; lbl_vid.Text = a1[8]; lbl_vdate.Text = a1[9]; lbl_visitedby.Text = a1[10]; lbl_vtime.Text = a1[11]; #region add questions lbl_q1.Text = "1. ATM MACHINE WORKING FINE?"; lbl_q2.Text = "2. CARETAKER AVAILABLE?"; lbl_q3.Text = "I. CARETAKER NAME?"; lbl_q4.Text = "II. CARETAKER NUMBER?"; lbl_q5.Text = "3. CLEANING DONE REGULARLY?"; //lbl_q6.Text = "I. HOW SEVERE IS THE UPKEEP ISSUE"; lbl_q7.Text = "II. FLOORING PROPER?"; lbl_q8.Text = "III. DUST BIN OK?"; lbl_q9.Text = "IV. BACKROOM OK?"; lbl_q10.Text = "V WRITING LEDGE AND VMS PROPER?"; lbl_q11.Text = "4. FIRE EXTINGUISHER OK?"; lbl_q12.Text = "5. IS RNM OK?"; //lbl_q13.Text = "I HOW SEVERE IS THE RNM ISSUE"; //lbl_q14.Text = "5.2 FLOORING PROPER?"; lbl_q14.Text = "I. LIGHTS OK?"; lbl_q15.Text = "II. No. Of CFL Working"; lbl_q16.Text = "III. GLOW SIGN PROPER?"; lbl_q17.Text = "IV. DOOR WORKING PROPERLY?"; lbl_q18.Text = "V. WALLS PROPER?"; lbl_q19.Text = "VI. CEILING PROPER?"; lbl_q20.Text = "6. DOOR MAT AVAILABLE?"; lbl_q21.Text = "7. IS AC INSTALLED AT SITE"; lbl_q22.Text = "I. AC WORKING PROPERLY?"; lbl_q23.Text = "II. AC CONNECTED WITH TIMER?"; lbl_q24.Text = "III. AC CONNECTED WITH METER?"; lbl_q25.Text = "8. UPS AND BATTERIES WORKING?"; lbl_q26.Text = "9. CAMERAS AVAILABLE AT SITE?"; lbl_q27.Text = "10. SIGNAGE & LOLLIPOP IS WORKING?"; lbl_q28.Text = "11. ANY ISSUE AFFECTNG THE TRANSACTIONS?"; lbl_q29.Text = "12. FEEDBACK FROM NEIGHBORING SHOPS/ LL"; lbl_q30.Text = "13. VSAT BALLASTING"; lbl_q31.Text = "14. MANDATORY NOTICES"; lbl_q32.Text = "15. ELECTRICITY BILL PAYMENT"; lbl_q33.Text = "I. ANY NEW BILLS AT SITE"; lbl_q34.Text = "II. SUBMETER READING"; lbl_q35.Text = "16. ANY POWER THEFT NOTICED"; lbl_q36.Text = "17. MULTIMETER READING OF EARTHING"; lbl_q37.Text = "18. IS THE VISIT ALONG WITH PM ENGINEER AND CRA?"; lbl_q38.Text = "I. PM DOCKET NO:"; lbl_q39.Text = "II. IS PM DONE PROPERLY?"; lbl_q40.Text = "19. CASH TALLIED WITH ADMIN BALANCE, MACHINE COUNTER AND PHYSICAL COUTING"; //lbl_q42.Text = "20. ANY OTHER ISSUES NOTICED?"; #endregion string q = "select Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18,Q19,Q20,Q21,Q22,Q23,Q24,Q25,Q26,Q27,Q28,Q29,Q30,Q31,Q32,Q33,Q34,Q35,Q36,Q37,Q38,Q39,Q40,Q41,Q42,Q43,Q44,Q45,Q46,Q47,Q48,Q49,Q50,ltrim(rtrim(pix)) as 'pix' from dr_ctp where vid='" + querystring + "'"; string[] c2 = { "Q1", "Q2", "Q3", "Q4", "Q5", "Q6", "Q7", "Q8", "Q9", "Q10", "Q11", "Q12", "Q13", "Q14", "Q15", "Q16", "Q17", "Q18", "Q19", "Q20", "Q21", "Q22", "Q23", "Q24", "Q25", "Q26", "Q27", "Q28", "Q29", "Q30", "Q31", "Q32", "Q33", "Q34", "Q35", "Q36", "Q37", "Q38", "Q39", "Q40", "Q41", "Q42", "Q43", "Q44", "Q45", "Q46", "Q47", "Q48", "Q49", "Q50", "pix" }; string[] a = bucket.xread(q, c2); Label[] l = new Label[50]; l[0] = Label1; l[1] = Label2; l[2] = Label3; l[3] = Label4; l[4] = Label5; //l[5] = Label6; l[6] = Label7; l[7] = Label8; l[8] = Label9; l[9] = Label10; l[10] = Label11; l[11] = Label12;// l[12] = Label13; l[13] = Label14; l[14] = Label15; l[15] = Label16; l[16] = Label17; l[17] = Label18; l[18] = Label19; l[19] = Label20; l[20] = Label21; l[21] = Label22; l[22] = Label23; l[23] = Label24; l[24] = Label25; l[25] = Label26; l[26] = Label27; l[27] = Label28; l[28] = Label29; l[29] = Label30; l[30] = Label31; l[31] = Label32; l[32] = Label33; l[33] = Label34; l[34] = Label35; l[35] = Label36; l[36] = Label37; l[37] = Label38; l[38] = Label39; l[39] = Label40; l[40] = Label41; l[41] = Label42; l[42] = Label43; l[43] = Label44; l[44] = Label45; l[45] = Label46; l[46] = Label47; l[47] = Label48; l[48] = Label49; l[49] = Label50; int i = 0; while (i < 40) { try { beautify(a[i].Trim(), l[i]); } catch { } i++; } string bind = "Select vid as [VISIT ID],siteid as [SITE ID],userid as [USER ID],type as [ASSET],scanstatus as [STATUS] from Scan where vid='" + querystring + "'"; bucket.BindGrid(GridView1, bind); show_available_images(Convert.ToInt32(a[50]) - 1, lbl_vid.Text); } } }
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 (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>"); } }
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 }