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