protected void btnShowCount_Click(object sender, EventArgs e) { string userID = panActivity_drpUsers.SelectedValue; string billType = panActivity_drpBillType.SelectedValue; string sql = string.Empty; string sqlUser = string.Empty; string sDate2 = string.Empty; string eDate2 = string.Empty; string strCount = string.Empty; string strSQLSynced = string.Empty; bool searchByUser = panActivity_drpSearchBy.SelectedValue == "user"; bool isSap; string strLoc = panActivity_txtLoc.Value; if (!common.getDates(sDate.Value, eDate.Value, panActivity_drpDuration.SelectedValue, out sDate2, out eDate2)) { //date error, return panActivity_lblMsg.Text = "Invalid Date. Use format DD-Mon-YYYY (e.g. 01-Jan-2016)."; return; } if (searchByUser) { if (userID != "ALL") { sqlUser = string.Format("and userid = '{0}'", userID); } if (billType == "ALL") { panActivity_lblMsg.Text = "Please select a Bill Type"; return; } else if (billType == "SAP_SBM_GSC") { strSQLSynced = " and nvl(SYNCED,'NULL')<>'NCODE' "; } } else { if (billType == "ALL") { panActivity_lblMsg.Text = "Bill Type ALL is not supported in Location search"; return; } //check sap/non-sap isSap = (panActivity_drpBillType.SelectedItem.Text.StartsWith("SAP")); if (isSap) { if (!Regex.IsMatch(strLoc, "^[1-9][1-9_]{3}$")) { panActivity_lblMsg.Text = "Invalid SAP Location"; return; } sqlUser = string.Format(" and sub_division_code = '{0}' ", strLoc); } else { if (!Regex.IsMatch(strLoc, "^[A-Z][1-9_]{2}$")) { panActivity_lblMsg.Text = "Invalid Non-SAP Location"; return; } Dictionary <string, string> fieldAccNo = new Dictionary <string, string>(5); fieldAccNo.Add("LS", "accountno"); fieldAccNo.Add("MS", "prt_ac_no"); fieldAccNo.Add("SP", "acno"); fieldAccNo.Add("DSBELOW10KW", "accountno"); fieldAccNo.Add("DSABOVE10KW", "accountno"); sqlUser = string.Format(" and {0} like '{1}%' ", fieldAccNo[billType], strLoc); } } sql = string.Format("select count(*) from onlinebill.{0} " + "where 1=1 {1} and trunc(dtupload) between '{2}' and '{3}' {4}", billType, sqlUser, sDate2, eDate2, strSQLSynced); strCount = OraDBConnection.GetScalar(sql); panActivity_lblMsg.Text = strCount; }
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); }