//private void DownloadFile(String pdfPath)
//{
// System.IO.FileInfo objFi = new System.IO.FileInfo(pdfPath);
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + objFi.Name);
// HttpContext.Current.Response.Charset = "";
// HttpContext.Current.Response.AddHeader("Content-Length", objFi.Length.ToString());
// HttpContext.Current.Response.ContentType = "application/pdf";
// HttpContext.Current.Response.WriteFile(objFi.FullName);
// HttpContext.Current.Response.End();
//}
protected void btnShow_Click(object sender, EventArgs e)
{
const string STRSHOW = "Show";
const string STRHIDE = "Hide";
string empid = txtEmpID.Text;
string sql = String.Empty;
panEmpHist.Visible = (btnShow.Text == STRSHOW);
btnShow.Text = (btnShow.Text == STRSHOW) ? STRHIDE : STRSHOW;
sql = "select pshr.get_fullname(empid) || ' (' || pshr.get_desg(cdesgcode) || '), ' || pshr.get_org(cloccode) as Info" +
" from pshr.empperso where empid = '" + empid + "'";
lblEmpInfo.Text = OraDBConnection.GetScalar(sql);
if (lblEmpInfo.Text == "")
{
lblDesgMsg.Text = "Invalid Empid";
panEmpHist.Visible = false;
btnShow.Text = STRSHOW;
return;
}
//show last 5 rows of emphistory
sql = "select * from (select eref as Event,to_char(fromdate,'dd-Mon-YYYY') as FromDate,to_char(todate,'dd-Mon-YYYY') as ToDate," +
"pshr.get_desg(desgcode) as Desg,pshr.get_org(loccode) as Location from " +
"pshr.emphistory eh,pshr.mast_event me where eh.eventcode = me.eventcode and empid=" + empid + " order by rowno desc) a " +
"where rownum < 6";
OraDBConnection.FillGrid(ref gvEmpHist, sql);
lblDesgMsg.Text = "";
}
protected void btnShow_Click(object sender, EventArgs e)
{
string empid = txtEmpID.Text;
string sql = String.Empty;
sql = "select count(*) from pshr.empperso where empid= " + empid;
if (string.IsNullOrEmpty(empid) || OraDBConnection.GetScalar(sql) == "0")
{
return;
}
//panEmpHist.Visible = true;
sql = "select pshr.get_fullname(empid) || ' (' || pshr.get_desg(cdesgcode) || '), ' || pshr.get_org(cloccode) as Info" +
" from pshr.empperso where empid = " + empid;
lblEmpInfo.Text = OraDBConnection.GetScalar(sql);
//show last 5 rows of emphistory
sql = "select * from (select eref as Event,to_char(fromdate,'dd-Mon-YYYY') as FromDate,to_char(todate,'dd-Mon-YYYY') as ToDate," +
"pshr.get_desg(desgcode) as Desg,pshr.get_org(loccode) as Location from " +
"pshr.emphistory eh,pshr.mast_event me where eh.eventcode = me.eventcode and empid=" + empid + " order by rowno desc) a " +
"where rownum < 6";
OraDBConnection.FillGrid(ref gvEmpHist, sql);
lblDesgMsg.Text = "";
}
int FillGrid()
{
string sql = "select empid as EmpID, name as Name, to_char(dob,'dd-Mon-YYYY') as DOB, locabb as Office," +
"to_char(fdate,'dd-Mon-YYYY') as \"Promotion Date\" from cadre.prop_redesig where propno = " + propno + " order by fdate,empid";
OraDBConnection.FillGrid(ref gv_redesig, sql);
return(gv_redesig.Rows.Count);
}
private void FillEventGrid(string empid)
{
string sql = string.Format("SELECT rowno as \"Row #\",get_event(eventcode) as \"Event\", " +
"to_char(fromdate,'DD-MM-YYYY') \"From Date\", " +
"to_char(todate,'DD-MM-YYYY') \"To Date\", get_desg(desgcode) as \"Desg\", " +
"get_post(loccode) as \"Posting Loc\", pshr.get_post(pcloccode) as \"Pay Charge Loc\", " +
"pshr.get_desg(sancdesg)||'-'||sancindx as \"Sanctioned Post\"," +
"(select cm.empid from cadre.cadr c,cadre.cadrmap cm where c.loccode=pcloccode and " +
"c.desgcode=sancdesg and c.indx = sancindx and c.rowno=cm.rowno) as \"Mapped Empid\"," +
"oonum as \"O/o Num\"," +
"to_char(odate,'dd-Mon-yyyy') as \"O/o Date\" from pshr.emphistory " +
"where empid='{0}' order by rowno", empid);
OraDBConnection.FillGrid(ref GridView1, sql);
}
int FillGrid()
{
string sql = "select rownum as \"S.No.\", a.* from (SELECT e.empid, firstname||' '|| decode(middlename, NULL, '', middlename||' ') || lastname AS name, " +
"to_char(e.dob,'dd-Mon-YYYY') AS DOB, md.desgtext as DESG, ml.locname as LOC, " +
"to_char(rl.dor,'dd-Mon-YYYY') as DOR " +
"FROM cadre.ret_list rl, pshr.mast_loc ml, pshr.mast_desg md, pshr.empperso e " +
"WHERE rl.empid = e.empid " +
"AND e.cloccode = ml.loccode " +
"AND e.cdesgcode = md.desgcode " +
"AND rl.propno = " + propno +
" order by md.hecode,e.empid) a";
OraDBConnection.FillGrid(ref gv_redesig, sql);
return(gv_redesig.Rows.Count);
}
int FillGrid()
{
//string sql = "select empid as EmpID, name as Name, to_char(dob,'dd-Mon-YYYY') as DOB, locabb as Office,"+
// "to_char(fdate,'dd-Mon-YYYY') as \"Promotion Date\" from cadre.ret_list where propno = " + propno + " order by fdate,empid";
//string sql = "SELECT EMPID, PSHR.GET_FULLNAME(empid) as NAME, pshr.get_desg(pshr.get_desg_by_id(empid)) as DESG," +
// "pshr.get_org(pshr.get_loc_by_id(empid)) as LOC, pshr.get_dob_by_id(empid) as DOB " +
// "FROM cadre.ret_list WHERE propno = " + propno + " order by empid";
string sql = "select rownum as \"S.No.\", a.* from (SELECT e.empid, firstname||' '|| decode(middlename, NULL, '', middlename||' ') || lastname AS name, " +
"to_char(e.dob,'dd-Mon-YYYY') AS DOB, md.desgtext as DESG, ml.locname as LOC, " +
"to_char(rl.dor,'dd-Mon-YYYY') as DOR " +
"FROM cadre.ret_list rl, pshr.mast_loc ml, pshr.mast_desg md, pshr.empperso e " +
"WHERE rl.empid = e.empid " +
"AND e.cloccode = ml.loccode " +
"AND e.cdesgcode = md.desgcode " +
"AND rl.propno = " + propno +
" order by md.hecode,e.empid) a";
OraDBConnection.FillGrid(ref gv_redesig, sql);
return(gv_redesig.Rows.Count);
}
protected void btnShowData_Click(object sender, EventArgs e)
{
string sql = "";
if (drpotp.SelectedValue == "1")
{
sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name,pshr.get_desg(e.cdesgcode) as Desg, " +
"pshr.get_org(e.cloccode) as Loc FROM pshr.empperso e, pshr.emphistory h where e.recstatus = 10 and e.empid = h.empid " +
"and h.todate is null and h.oonum is not null and e.cloccode != 999999999 and h.eventcode not in (select eventcode from pshr.mast_event where egrp = 'LEAVE') ";
}
else if (drpotp.SelectedValue == "2")
{
//sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name, pshr.get_desg(e.cdesgcode) as Desg, "+
// "pshr.get_org(e.cloccode) as Loc FROM pshr.empperso e where e.cloccode = 999999999 order by e.cloccode";
sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name, pshr.get_desg(e.cdesgcode) as Desg, " +
"pshr.get_org(e.cloccode) as Loc , h.fromdate, h.todate FROM pshr.empperso e,pshr.emphistory h where e.empid = h.empid and e.recstatus = 10 and h.rowno = select rowno from emphistory where empid = e.empid)" +
"and h.eventcode in (select eventcode from mast_event where EGRP = 'LEAVE') ";
}
else if (drpotp.SelectedValue == "3")
{
sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name, pshr.get_desg(e.cdesgcode) as Desg, " +
"pshr.get_org(e.cloccode) as Loc FROM pshr.empperso e,pshr.mast_loc l where e.recstatus =10 and e.cloccode = l.loccode and l.orgcode in (45,46) order by l.loccode";
}
else if (drpotp.SelectedValue == "4")
{
sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name, pshr.get_desg(e.cdesgcode) as Desg, " +
"pshr.get_org(e.cloccode) as Loc FROM pshr.empperso e, pshr.emphistory h where e.recstatus = 10 and e.empid = h.empid " +
"and h.fromdate is null and h.oonum is not null";
}
else if (drpotp.SelectedValue == "5")
{
sql = "SELECT e.empid as Empid, pshr.get_fullname(e.empid) as Name, pshr.get_desg(e.cdesgcode) as Desg, " +
"pshr.get_org(e.cloccode) as Loc FROM pshr.empperso e, pshr.emphistory h where e.recstatus = 10 and e.empid = h.empid " +
"and h.eventcode = 17 and h.rowno = (select max(rowno) from pshr.emphistory where empid = h.empid) ";
}
OraDBConnection.FillGrid(ref gvData, sql);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string sql = "select empid,firstname,fathername,dob from pshr.empperso where cdesgcode = " + drpDesg.SelectedValue;
OraDBConnection.FillGrid(ref gv_redesig, sql);
}
