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