protected void btnAllOO_PC_Click(object sender, EventArgs e)
{
string empid = txtempid.Text;
string sql = "select m.sno,tpp.oonum as oonum, " +
"to_char(tpp.oodate,'dd-mm-yyyy') as oodate, " +
"'12' as fsize, pshr.get_fullname(m.empid),to_char(m.empid) as empid,pshr.get_dob(m.empid) as dob," +
"pshr.get_post(m.oldloccode) as old_work_loc,m.oldloccode as old_work_loccode,pshr.get_desg(m.olddesgcode) as old_work_desg, m.olddesgcode as old_work_desgcode," +
"DECODE(m.rowno,0,pshr.get_post(m.oldloccode), pshr.get_post(cadre.get_lcode_rno(m.rowno))) AS old_pc_loc," +
"DECODE(m.rowno,0,m.oldloccode, cadre.get_lcode_rno(m.rowno)) AS old_pc_loccode," +
"DECODE(m.rowno,0,pshr.get_desg(m.olddesgcode), pshr.get_desg(cadre.get_dcode_rno(m.rowno))) AS old_pc_desg, " +
"DECODE(m.rowno,0,m.olddesgcode, cadre.get_dcode_rno(m.rowno)) AS old_pc_desgcode, " +
"DECODE(m.rowno,0,'0', cadre.get_indx_rno(m.rowno)) AS old_pc_indx, cadre.get_org_plants(m.cloccode) as new_work_loc," +
"m.cloccode as new_work_loccode,pshr.get_desg(m.cdesgcode) as new_work_desg,m.cdesgcode as new_work_desgcode," +
"decode(length(m.proposed_rowno),9,pshr.get_post(m.proposed_rowno), cadre.get_org_plants(cadre.get_lcode_rno(m.proposed_rowno))) AS new_pc_loc," +
"decode(length(m.proposed_rowno),9,m.proposed_rowno, cadre.get_lcode_rno(m.proposed_rowno)) AS new_pc_loccode," +
"DECODE(m.rowno,0,pshr.get_desg(m.olddesgcode), pshr.get_desg(cadre.get_dcode_rno(m.proposed_rowno))) AS new_pc_desg, " +
"DECODE(m.rowno,0,m.olddesgcode, cadre.get_dcode_rno(m.proposed_rowno)) AS new_pc_desgcode, " +
"cadre.get_indx_rno(m.proposed_rowno) as new_pc_indx, m.sysremarks || m.remarks as remarks,'G' as grp,m.propno, to_char(m.newempid) as newempid, m.status, " +
"m.disp_left, m.disp_right, m.prvcomment, pshr.get_soccat(m.empid) as categ " +
"from cadre.propcadrmap m, cadre.tp_proposals tpp " +
"where m.propno = tpp.pno and m.status is not null AND M.STATUS NOT IN ('S','V') " +
"and m.propno in (select propno from cadre.propcadrmap where empid = " + empid + ") and tpp.status='S' " +
"AND m.cloccode is not null order by oodate,sno";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
string pdfPath;
pdfPath = Server.MapPath("office_orders\\all_orders_" + empid + ".pdf");
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\all_rptposttrans_pc.rpt");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, pdfPath);
Utils.DownloadFile(pdfPath);
}
protected void Page_Load(object sender, EventArgs e)
{
string sql = "";
string file_name = "";
string empid = Session.Contents["empid"].ToString();
string desg = Session.Contents["desg"].ToString();
if ((Session.Contents["desg"].ToString() == "9048") || (Session.Contents["desg"].ToString() == "9047"))
{
desg = "9047,9048";
}
else if ((Session.Contents["desg"].ToString() == "9050") || (Session.Contents["desg"].ToString() == "9365"))
{
desg = "9050,9365";
}
else if ((Session.Contents["desg"].ToString() == "9052") || (Session.Contents["desg"].ToString() == "9366"))
{
desg = "9052,9366";
}
string org = Session.Contents["org"].ToString();
string branch = Session.Contents["branch"].ToString();
string categ = Session.Contents["categ"].ToString();
string loctype = Session.Contents["loctype"].ToString();
string sloc = Session.Contents["sloc"].ToString();
string fname = "";
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
string g;
if (loctype == "Zone")
{
g = "pshr.get_zone(loccode)";
}
else if (loctype == "Circle")
{
g = "pshr.get_circle(loccode)";
}
else
{
g = "pshr.get_org(loccode)";
}
if ((desg != "0") && (org != "0"))
{
if (sloc == "0")
{
sql = " select " + g + " as office,pshr.get_desg(desgcode) as Designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode ,a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select desgcode,loccode,rowno, count(*) as sanc from cadre.cadr where loccode = " + org + "and desgcode in (" + desg + ")" +
((branch != "0") ? " and branch = " + branch : "") +
" group by loccode,desgcode,rowno) a, " +
"(select desgcode,loccode, rowno, count(*) as post from cadre.cadr where loccode = " + org + "and desgcode in (" + desg + ") and rowno in (select rowno from cadre.cadrmap)" +
((branch != "0") ? " and branch = " + branch : "") +
" group by loccode,desgcode,rowno) b where a.rowno = b.rowno(+) ) d" +
" group by " + g + " ,pshr.get_desg(desgcode)";
}
else
{
sql = " select " + g + " as office ,pshr.get_desg(desgcode) as Designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode ,a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select desgcode,loccode,rowno, count(*) as sanc from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep) and desgcode in (" + desg + ") " +
((branch != "0") ? " and branch = " + branch : "") +
" group by desgcode,loccode,rowno)a, " +
"(select desgcode,loccode, rowno, count(*) as post from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep) and desgcode in (" + desg + ") and rowno in (select rowno from cadre.cadrmap)" +
((branch != "0") ? " and branch = " + branch : "") +
" group by desgcode,loccode,rowno) b where a.rowno = b.rowno(+)) d" +
" group by " + g + " ,pshr.get_desg(desgcode)";
}
file_name = "Reports\\rptdesgvacancy.rpt";
fname = "vdesgdet";
}
else if ((desg != "0") && (org == "0"))
{
sql = "select " + g + " as office ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select loccode,rowno, count(*) as sanc from cadre.cadr where desgcode in (" + desg + ")" +
((branch != "0") ? " and branch = " + branch : "") +
" group by loccode,rowno)a, " +
"(select loccode, rowno, count(*) as post from cadre.cadr where desgcode in (" + desg + ") and rowno in (select rowno from cadre.cadrmap)" +
((branch != "0") ? " and branch = " + branch : "") +
"group by loccode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
" group by " + g;
file_name = "Reports\\rptdesgvacancy.rpt";
fname = "vdesgdet";
}
else if ((desg == "0") && (org != "0"))
{
if (sloc == "0")
{
sql = " select pshr.get_org(" + org + ") as office,desgcode,pshr.get_desg(desgcode) as designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select loccode, desgcode,rowno, count(*) as sanc from cadre.cadr where loccode = " + org +
((branch != "0") ? " and branch = " + branch : "") +
" group by loccode, desgcode,rowno)a, " +
"(select loccode, desgcode,rowno, count(*) as post from cadre.cadr where loccode = " + org + " and rowno in (select rowno from cadre.cadrmap)" +
((branch != "0") ? " and branch = " + branch : "") +
"group by loccode, desgcode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
" group by desgcode order by cadre.get_hecode(desgcode)";
}
else
{
sql = " select pshr.get_org(" + org + ") as office,desgcode,pshr.get_desg(desgcode) as designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select loccode, desgcode,rowno, count(*) as sanc from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep)" +
((branch != "0") ? " and branch = " + branch : "") +
"group by loccode, desgcode,rowno)a, " +
"(select loccode, desgcode,rowno, count(*) as post from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = " + org + "connect by prior loccode = locrep) and rowno in (select rowno from cadre.cadrmap)" +
((branch != "0") ? " and branch = " + branch : "") +
"group by loccode, desgcode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
" group by desgcode order by cadre.get_hecode(desgcode)";
}
file_name = "Reports\\rptlocvacancy.rpt";
fname = "vlocdet";
}
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
string pdfPath = Server.MapPath("office_orders\\" + fname + sysdate + "-" + org + ".pdf");
//save Report at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath(file_name);
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "123");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
DownloadFile(pdfPath);
}
protected void Page_Load(object sender, EventArgs e)
{
string empid = Session.Contents["empid"].ToString();
string desg = Session.Contents["desg"].ToString();
if ((Session.Contents["desg"].ToString() == "9048") || (Session.Contents["desg"].ToString() == "9047"))
{
desg = "9047,9048";
}
else if ((Session.Contents["desg"].ToString() == "9050") || (Session.Contents["desg"].ToString() == "9365"))
{
desg = "9050,9365";
}
else if ((Session.Contents["desg"].ToString() == "9052") || (Session.Contents["desg"].ToString() == "9366"))
{
//ADDL SE, SRXEN AND SSM
desg = "9052,9366,9477";
}
string org = Session.Contents["org"].ToString();
string branch = Session.Contents["branch"].ToString();
string categ = Session.Contents["categ"].ToString();
string sql = "SELECT h.empid," +
"p.firstname || ' ' || p.middlename || ' ' || p.lastname as FullName, " +
"mc.categtext as cat_text, " +
"p.dob, " +
"h.todate," +
"md.desgabb, " +
"p.cloccode," +
"p.cdesgcode, " +
//"(SELECT locabb FROM MAST_LOC WHERE loccode = concat(substr(ml.loccode,1,3),'000000')) as pzone, "+
"pshr.get_org(pshr.get_chief(ml.loccode)) as pzone, " +
"ml.post AS posting, " +
"h.pcloccode, " +
"pshr.get_post(h.pcloccode) AS paychrg, " +
"pshr.get_desg(h.sancdesg) AS sancdesg," +
"h.sancindx, " +
"h.oonum, " +
"h.odate, " +
"md.hecode AS hecode, " +
"p.branchcode, " +
"p.seniorityno as sen, " +
"mb.branchtext " +
"FROM pshr.empperso p, emphistory h, mast_categ mc, mast_desg md, mast_loc ml, mast_branch mb " +
"where h.empid = p.empid " +
"AND mc.categcode = p.soccategory " +
"and md.desgcode = p.cdesgcode " +
"and ml.loccode = p.cloccode " +
"and mb.branchcode = p.branchcode " +
"AND h.rowno = (SELECT MAX(rowno) FROM pshr.emphistory WHERE empid = p.empid AND oonum IS NOT NULL) " +
"AND p.recstatus = 10 " +
((empid != "") ? " and p.empid in (" + empid + ")" : "") +
((desg != "0") ? " and p.cdesgcode in (" + desg + ")" : "") +
((org != "0") ? " and p.cloccode in (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep) " : "") +
((branch != "0") ? " and p.branchcode = " + branch : "") +
((categ != "0") ? " and p.soccategory = " + categ : "") +
"ORDER BY hecode, p.empid ";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//foreach (System.Data.DataRow drow in ds.Tables[0].Rows)
//{
// sql = "select nvl(pshr.get_dojp("+drow["e1"]+"),'') from dual";
// System.Data.DataSet ds1 = OraDBConnection.GetData(sql);
//}
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
string pdfPath = Server.MapPath("office_orders\\" + "pplist-" + sysdate + ".pdf");
//save office order at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rptpplist.rpt");
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "pshr");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
Utils.DownloadFile(pdfPath);
}
protected void Page_Load(object sender, EventArgs e)
{
string empid = Session.Contents["empid"].ToString();
string desg = Session.Contents["desg"].ToString();
if ((Session.Contents["desg"].ToString() == "9048") || (Session.Contents["desg"].ToString() == "9047"))
{
desg = "9047,9048";
}
else if ((Session.Contents["desg"].ToString() == "9050") || (Session.Contents["desg"].ToString() == "9365"))
{
desg = "9050,9365";
}
else if ((Session.Contents["desg"].ToString() == "9052") || (Session.Contents["desg"].ToString() == "9366"))
{
desg = "9052,9366";
}
string org = Session.Contents["org"].ToString();
string branch = Session.Contents["branch"].ToString();
string categ = Session.Contents["categ"].ToString();
string sql = "select e.empid, pshr.get_fullname(e.empid), e.fathername, e.dob,e.doj, d.desgtext, pshr.get_org(e.cloccode), e.gpfno,e.panno " +
"from pshr.empperso e,pshr.mast_desg d where e.recstatus = 10 and e.cdesgcode = d.desgcode " +
((empid != "") ? " and e.empid in( " + empid + ")" : "") +
((desg != "0") ? " and e.cdesgcode in( " + desg + ")" : "") +
((org != "0") ? " and e.cloccode = " + org : "") +
((branch != "0") ? " and e.branchcode = " + branch : "") +
((categ != "0") ? " and e.soccategory = " + categ : "") +
" order by d.hecode,e.empid ";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
string pdfPath = Server.MapPath("office_orders\\" + "pdetail-" + empid + ".pdf");
//save office order at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rptpdetails.rpt");
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "123");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
string sql2 = "select h.empid,e.event,h.todate, h.fromdate,pshr.get_desg(h.desgcode), pshr.get_org(h.loccode)" +
" from pshr.empperso p , pshr.emphistory h, pshr.mast_event e" +
" where p.recstatus = 10 and h.eventcode = e.eventcode and p.empid = h.empid ";
if (empid != "")
{
sql2 = sql2 + " and p.empid in( " + empid + ")";
}
if (desg != "0")
{
sql2 = sql2 + " and p.cdesgcode in ( " + desg + ")";
}
if (org != "0")
{
sql2 = sql2 + " and p.cloccode = " + org;
}
if (branch != "0")
{
sql2 = sql2 + " and p.branchcode = " + branch;
}
if (categ != "0")
{
sql2 = sql2 + " and p.soccategory = " + categ;
}
sql2 = sql2 + " order by p.empid,h.rowno ";
System.Data.DataSet ds3 = OraDBConnection.GetData(sql2);
CrystalReportSource1.ReportDocument.Subreports["emphist"].SetDataSource(ds3.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
DownloadFile(pdfPath);
}
protected void Page_Load(object sender, EventArgs e)
{
string empid = Session.Contents["empid"].ToString();
//string empid = 102972;
string desg = Session.Contents["desg"].ToString();
if ((Session.Contents["desg"].ToString() == "9048") || (Session.Contents["desg"].ToString() == "9047"))
{
desg = "9047,9048";
}
else if ((Session.Contents["desg"].ToString() == "9050") || (Session.Contents["desg"].ToString() == "9365"))
{
desg = "9050,9365";
}
else if ((Session.Contents["desg"].ToString() == "9052") || (Session.Contents["desg"].ToString() == "9366"))
{
desg = "9052,9366";
}
string org = Session.Contents["org"].ToString();
string branch = Session.Contents["branch"].ToString();
string categ = Session.Contents["categ"].ToString();
string sql = "select p.photo2, a.* from ( " +
"select e.empid, pshr.get_fullname(e.empid), e.fathername, e.dob,e.doj, d.desgtext, pshr.get_org(e.cloccode), e.gpfno,e.panno " +
"from pshr.empperso e,pshr.mast_desg d where e.cdesgcode = d.desgcode " +
((empid != "") ? " and e.empid in( " + empid + ")" : "") +
((desg != "0") ? " and e.cdesgcode in( " + desg + ")" : "") +
((org != "0") ? " and e.cloccode = " + org : "") +
((branch != "0") ? " and e.branchcode = " + branch : "") +
((categ != "0") ? " and e.soccategory = " + categ : "") +
" order by d.hecode,e.empid) a " +
" left outer join IMG_PSHR.img p on p.empid = a.empid ";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
string pdfPath = Server.MapPath("office_orders\\" + "pdetail-" + empid + ".pdf");
//save office order at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\panelreport.rpt");
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "123");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
//CrystalReportSource1.DataBind();
//CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
//DownloadFile(pdfPath);
string sql2 = "select h.empid,e.event,h.todate, h.fromdate,pshr.get_desg(h.desgcode), pshr.get_org(h.loccode)" +
" from pshr.empperso p , pshr.emphistory h, pshr.mast_event e" +
" where h.eventcode = e.eventcode and p.empid = h.empid ";
if (empid != "")
{
sql2 = sql2 + " and p.empid in( " + empid + ")";
}
if (desg != "0")
{
sql2 = sql2 + " and p.cdesgcode in ( " + desg + ")";
}
if (org != "0")
{
sql2 = sql2 + " and p.cloccode = " + org;
}
if (branch != "0")
{
sql2 = sql2 + " and p.branchcode = " + branch;
}
if (categ != "0")
{
sql2 = sql2 + " and p.soccategory = " + categ;
}
sql2 = sql2 + " order by p.empid,h.rowno ";
System.Data.DataSet ds3 = OraDBConnection.GetData(sql2);
CrystalReportSource1.ReportDocument.Subreports["emphist"].SetDataSource(ds3.Tables[0]);
//CrystalReportSource1.DataBind();
string sql4 = "select d.empid, d.fileno ||' / '|| dcp.get_section(d0.section) sec, " +
"dcp.get_dfttype(d0.DFTTYPECODE) dft, dcp.get_chgmemo(d.dcpno) chg, " +
"d0.charges, decode (s.STATCODE, 1, dcp.get_casestatus_c(d.dcpno), " +
" 2, dcp.get_decsub(d.dcpno,7) ||' , Case Decided', " +
" 3, dcp.get_casestatus_c(d.dcpno), " +
" 4, dcp.get_decsub(d.dcpno,8)||', Case Decided'," +
" 5, dcp.get_casestatus_c(d.dcpno)," +
" 6, dcp.get_decsub(d.dcpno,9) ||', Case Decided'," +
" 7, dcp.get_casestatus_c(d.dcpno)," +
" 8, dcp.get_casestatus_c(d.dcpno)," +
" 9, dcp.get_casestatus_c(d.dcpno)," +
" 10,dcp.get_decsub(d.dcpno,10)||' , Case Decided') dec " +
" from dcp.dcpno d,dcp.dcp0 d0, " +
" dcp.dcpstatus s " +
//", dcp.prom_id p " +
" where d.dcpno = d0.dcpno and d.dcpno = s.dcpno " +
//" and d.empid = p.empid "+
" and d.empid in( " + empid + ")" +
"order by s.statcode, d.fileno";
System.Data.DataSet ds4 = OraDBConnection.GetData(sql4);
CrystalReportSource1.ReportDocument.Subreports["dcases"].SetDataSource(ds4.Tables[0]);
//CrystalReportSource1.DataBind();
string sql5 = "select acr.empid, acr.fromdate, acr.todate, acr.apprscore, int.inttext, stat.statustext " +
"from ACR.acr_history_12 acr, PSHR.mast_integrity int, PSHR.mast_acrstatus stat " +
"where acr.apprintcode = int.intcode and acr.apprstatcode = stat.statuscode " +
"and acr.empid = " + empid +
//+ " and acr.fromdate >= add_months(sysdate,-(5*12))"
" order by acr.todate";
System.Data.DataSet ds5 = OraDBConnection.GetData(sql5);
CrystalReportSource1.ReportDocument.Subreports["acr"].SetDataSource(ds5.Tables[0]);
CrystalReportSource1.DataBind();
string sql6 = "select e.empid, e.passdate, p.testname, e.oonum from empdtest e, " +
"mast_depttest p where e.testcode = p.testcode and e.empid = " + empid +
" order by e.rowno";
System.Data.DataSet ds6 = OraDBConnection.GetData(sql6);
CrystalReportSource1.ReportDocument.Subreports["dae"].SetDataSource(ds6.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
DownloadFile(pdfPath);
}
protected void btnShow_Click(object sender, EventArgs e)
{
string desg = drpDesg.SelectedValue;
if (desg == "9048" || desg == "9047")
{
desg = "9047,9048";
}
else if (desg == "9050" || desg == "9365")
{
desg = "9050,9365";
}
else if (desg == "9052" || desg == "9366" || desg == "9477")
{
//ADDL SE, SRXEN AND SSM
desg = "9052,9366,9477";
}
string sql = "SELECT h.empid," +
"p.firstname || ' ' || p.middlename || ' ' || p.lastname as FullName, " +
"mc.categtext as cat_text, " +
"p.dob, " +
"h.todate," +
"md.desgabb, " +
"p.cloccode," +
"p.cdesgcode, " +
"pshr.get_org(pshr.get_chief(ml.loccode)) as pzone, " +
"ml.post AS posting, " +
"h.pcloccode, " +
"pshr.get_post(h.pcloccode) AS paychrg, " +
"pshr.get_desg(h.sancdesg) AS sancdesg," +
"h.sancindx, " +
"h.oonum, " +
"h.odate, " +
"md.hecode AS hecode, " +
"p.branchcode, " +
"p.seniorityno as sen, " +
"mb.branchtext " +
"FROM pshr.empperso p, emphistory h, mast_categ mc, mast_desg md, mast_loc ml, mast_branch mb " +
"where h.empid = p.empid " +
"AND mc.categcode = p.soccategory " +
"and md.desgcode = p.cdesgcode " +
"and ml.loccode = p.cloccode " +
"and mb.branchcode = p.branchcode " +
"AND h.rowno = (SELECT MAX(rowno) FROM pshr.emphistory WHERE empid = p.empid AND oonum IS NOT NULL) " +
"AND p.recstatus = 10 " +
"AND p.cdesgcode in (" + desg + ")" +
"ORDER BY hecode, " + (chkBySen.Checked ? "seniorityno" : "empid");
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
string pdfPath = Server.MapPath("office_orders\\" + "pplist-" + sysdate + ".pdf");
//save office order at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rptpplist.rpt");
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "pshr");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
Utils.DownloadFile(pdfPath);
}
protected void btnshow_Click(object sender, EventArgs e)
{
string sql = "";
string file_name = "";
string desg = drpdesg.SelectedValue.ToString();
string org = drporg.SelectedValue.ToString();
string fname = "";
if (desg == "9050" || desg == "9365")
{
desg = "9050,9365";
}
//get sysdate
string sysdate = OraDBConnection.GetScalar("select to_char(sysdate,'dd-mm-yyyy') from dual");
if (org != "0")
{
//sql = " select loccode,pshr.get_org(loccode) as Office,desgcode,pshr.get_desg(desgcode) as designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
// " (select loccode, desgcode,rowno, count(*) as sanc from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep) group by loccode, desgcode,rowno)a, " +
// "(select loccode, desgcode,rowno, count(*) as post from cadre.cadr where loccode in (select loccode from pshr.mast_loc start with loccode = "+ org + "connect by prior loccode = locrep) and rowno in (select rowno from cadre.cadrmap)group by loccode, desgcode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
// " group by loccode,desgcode order by loccode";
sql = " select pshr.get_org(" + org + ") as office,desgcode,pshr.get_desg(desgcode) as designation ," +
"sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, a.desgcode as desgcode, " +
"nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select loccode, desgcode,rowno, count(*) as sanc from cadre.cadr where loccode in" +
" (select loccode from pshr.mast_loc start with loccode = " + org + " connect by prior loccode = locrep) " +
" group by loccode, desgcode,rowno)a, " +
"(select loccode, desgcode,rowno, count(*) as post from cadre.cadr where loccode in " +
"(select loccode from pshr.mast_loc start with loccode = " + org + "connect by prior loccode = locrep) and " +
"rowno in (select rowno from cadre.cadrmap)group by loccode, desgcode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
" group by desgcode order by cadre.get_hecode(desgcode)";
file_name = "Reports\\rptlocvacancy.rpt";
fname = "vlocdet";
}
else if (desg != "0")
{
sql = " select loccode,pshr.get_org(loccode) as Office,desgcode,pshr.get_desg(desgcode) as designation ,sum(sanc) as sanctioned,sum(post) as posted from (select a.loccode as loccode, a.desgcode as desgcode, nvl(a.sanc,0) as sanc,nvl(b.post,0) as post from" +
" (select loccode, desgcode,rowno, count(*) as sanc from cadre.cadr where desgcode in (" + desg + ") group by loccode, desgcode,rowno)a, " +
"(select loccode, desgcode,rowno, count(*) as post from cadre.cadr where desgcode in (" + desg + ") and rowno in (select rowno from cadre.cadrmap)group by loccode, desgcode,rowno) b where a.rowno = b.rowno(+) and a.loccode not like '608%') d" +
" group by loccode,desgcode order by loccode";
file_name = "Reports\\rptdesgvacancy.rpt";
fname = "vdesgdet";
}
else
{
return;
}
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
string pdfPath = Server.MapPath("office_orders\\" + fname + sysdate + "-" + org + ".pdf");
//save Report at server
CrystalDecisions.Web.CrystalReportSource CrystalReportSource1 = new CrystalDecisions.Web.CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath(file_name);
CrystalReportSource1.ReportDocument.SetDatabaseLogon("pshr", "123");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
DownloadFile(pdfPath);
}
