protected void btn_search_Click(object sender, EventArgs e)
{
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 = "%";
}
#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(Q71,'NA') as [Space availability in ATM lobby for 2 units with size dimensions 2X2 each ?],
dbo.getvalue(Q68,'x',1) as[LENGTH1],dbo.getvalue(dbo.getvalue(Q68,'x',2),'=',1)as[WIDTH1],dbo.getvalue(dbo.getvalue(Q68,'x',2),'=',2) as [I.Total ATM area allocated Sq. Feet],
dbo.getvalue(Q69,'x',1) as[LENGTH2],dbo.getvalue(dbo.getvalue(Q69,'x',2),'=',1)as[WIDTH2],dbo.getvalue(dbo.getvalue(Q69,'x',2),'=',2) as [II.ATM lobby size in Sq. feet],
dbo.getvalue(Q70,'x',1) as[LENGTH3],dbo.getvalue(dbo.getvalue(Q70,'x',2),'=',1)as[WIDTH3],dbo.getvalue(dbo.getvalue(Q70,'x',2),'=',2) as [III.ATM Back room size in Sq. Feet],
isnull(Q72,'NA') as [Network feasibility Voice ?],
isnull(Q73,'NA') as [Network feasibility Data ?],
isnull(Q74,'NA') as [Is shutter open close activity happening ?],
isnull(Q75,'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 " + role + " and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'";
}
else
{
#region oldquery
// 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],Q59 as [Is CAM1 Working?],Q60 as [Is CAM2 Working?],Q61 as [Is the Image getting stored?],Q62 as [Is EJ getting pulled?],remark as [OTHER REMARK],
// [AC],[ATM],[BAT1],[BAT2],[BAT3],[MODEM],[UPS], 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],[ATM],[BAT1],[BAT2],[BAT3],[MODEM],[UPS] from
// (
// select vid,srno, type
// from scan
// ) x
// pivot
// (
// max(srno)
// for type in ([AC],[ATM],[BAT1],[BAT2],[BAT3],[MODEM],[UPS])
// ) p)
// s on d.vid=s.vid
// 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 + "'";
#endregion
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(Q71,'NA') as [Space availability in ATM lobby for 2 units with size dimensions 2X2 each ?],
dbo.getvalue(Q68,'x',1) as[LENGTH1],dbo.getvalue(dbo.getvalue(Q68,'x',2),'=',1)as[WIDTH1],dbo.getvalue(dbo.getvalue(Q68,'x',2),'=',2) as [I.Total ATM area allocated Sq. Feet],
dbo.getvalue(Q69,'x',1) as[LENGTH2],dbo.getvalue(dbo.getvalue(Q69,'x',2),'=',1)as[WIDTH2],dbo.getvalue(dbo.getvalue(Q69,'x',2),'=',2) as [II.ATM lobby size in Sq. feet],
dbo.getvalue(Q70,'x',1) as[LENGTH3],dbo.getvalue(dbo.getvalue(Q70,'x',2),'=',1)as[WIDTH3],dbo.getvalue(dbo.getvalue(Q70,'x',2),'=',2) as [III.ATM Back room size in Sq. Feet],
isnull(Q72,'NA') as [Network feasibility Voice ?],
isnull(Q73,'NA') as [Network feasibility Data ?],
isnull(Q74,'NA') as [Is shutter open close activity happening ?],
isnull(Q75,'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 " + role + " and a.state in (" + txtuser.Text + ") and convert(date,vdate) between '" + txt_frmDate.Text + "' and '" + txt_toDate.Text + "'";
}
bucket.BindGrid1(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
create_parent_headers1();
}