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>");
}
}
