protected void btnOPList_Click(object sender, EventArgs e)
{
string desglist = drpDesgs.SelectedValue;
string sql = "select rownum as srno, a.* from (select pshr.get_fullname(e.empid) as name, " +
"md.desgabb as degn, e.empid as code, pshr.get_qual(e.empid) as qual, " +
"to_char(e.dob,'dd-mm-yyyy') as DOB, pshr.get_org(cloccode) as loc, " +
"to_char(pshr.get_since_lastloc(e.empid),'dd-mm-yyyy') as since, " +
"eaddr.phonecell as mobile " +
"from pshr.empperso e, PSHR.empaddr eaddr, PSHR.mast_desg md where " +
"e.cdesgcode in (" + desglist + ") " +
"and eaddr.empid = e.empid " +
"and md.desgcode = e.cdesgcode and " +
"e.recstatus = 10 order by md.hecode, e.empid) a";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
DataGrid dg = new DataGrid();
dg.DataSource = ds;
dg.DataBind();
Response.AddHeader("content-disposition", "attachment;filename=mappinglist.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringwrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlwrite = new System.Web.UI.HtmlTextWriter(stringwrite);
//htmlwrite.WriteLine("TITLE");
dg.RenderControl(htmlwrite);
Response.Write(stringwrite.ToString());
Response.End();
dg.Dispose();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string desg = drpDesg.SelectedValue;
string startdt = dt_start.Text;
string enddt = dt_end.Text;
string sql = String.Empty;
//reset fromdate,todate and olddesg
sql = "update cadre.rd_proposals set fromdate = null, todate = null, olddesg=null";
OraDBConnection.ExecQry(sql);
//delete earlier entered records (if any) for this proposal
sql = "delete from cadre.prop_redesig where propno = " + propno;
OraDBConnection.ExecQry(sql);
//insert matched records in prop_redesig
sql = String.Format("insert into CADRE.prop_redesig SELECT e.empid, e.firstname || ' ' || e.middlename || ' ' || e.lastname AS name, " +
"to_char(e.dob,'dd-Mon-YYYY') as DOB, m.locabb, to_char(h.fromdate,'dd-Mon-YYYY') AS Promoted_On, {3}, {0} FROM " +
"(SELECT empid, MIN(fromdate) AS fromdate FROM emphistory WHERE desgcode = {0} GROUP BY empid) h, empperso e, mast_loc m " +
"WHERE e.cdesgcode = {0} AND e.recstatus = 10 AND h.empid = e.empid AND m.loccode = e.cloccode " +
"AND h.fromdate BETWEEN to_date('{1}','dd/mm/yyyy') AND to_date('{2}','dd/mm/yyyy')", desg, startdt, enddt, propno);
OraDBConnection.ExecQry(sql);
//enter fromdate, todate and old desg in rd_proposals
sql = string.Format("update cadre.rd_proposals set fromdate = to_date('{0}','dd/mm/yyyy')," +
"todate = to_date('{1}','dd/mm/yyyy'), olddesg={2} where pno={3}",
startdt, enddt, desg, propno);
OraDBConnection.ExecQry(sql);
FillGrid();
Search_Visible(false);
Controls_Vis(gv_redesig.Rows.Count > 0);
}
protected void btnCreate_Click(object sender, EventArgs e)
{
if (txtpropname.Text == "")
{
Utils.ShowMessageBox(this, "Enter Proposal Name");
return;
}
//status: U-Unsaved, S-Saved
//type: TP-Transfer/Promotion, RD-Redesignation
string sql = "insert into cadre.proposals(pno,pname,pdate,status,type) values" +
"((select nvl(max(pno),0)+1 from cadre.proposals)," +
"'" + txtpropname.Text + "',sysdate,'U','" + PropType.Value + "')";
bool ret = false;
try
{
ret = OraDBConnection.ExecQry(sql);
if (ret == false)
{
throw new Exception("Error in Creating Proposal");
}
}
catch (Exception ex)
{
Utils.ShowMessageBox(this, ex.Message);
return;
}
panNewProp.Visible = false;
txtpropname.Text = "";
FillGrid();
}
private bool ResetRownos(long empid)
{
string sql;
bool res;
sql = @"BEGIN
DELETE FROM cadre.temp_setrowno WHERE empid = " + empid + @";
INSERT INTO cadre.temp_setrowno
SELECT empid,
rowno,
rownum
FROM
(SELECT rowno,empid FROM emphistory WHERE empid = " + empid + @" ORDER BY rowno
);
UPDATE emphistory eh
SET eh.rowno =
(SELECT tsr.newrowno
FROM CADRE.temp_setrowno tsr
WHERE tsr.empid = eh.empid
AND tsr.oldrowno = eh.rowno
)
WHERE eh.empid=" + empid + @";
END;";
res = OraDBConnection.ExecQry(sql);
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
string sql;
System.Data.DataSet ds;
System.Data.DataRow drow;
if (!String.IsNullOrEmpty(Convert.ToString(Session["proposalno"])))
{
propno = Session["proposalno"].ToString();
}
else
{
Response.Redirect("~/redesig_proposals.aspx.cs");
}
if (!IsPostBack)
{
//check if entries for this proposal no. exist in DB
sql = "select to_char(fromdate,'dd-Mon-YYYY') as From_Date,to_char(todate,'dd-Mon-YYYY') as To_Date,olddesg from cadre.rd_proposals where pno = " + propno;
ds = OraDBConnection.GetData(sql);
if (ds.Tables[0].Rows.Count == 1)
{
drow = ds.Tables[0].Rows[0];
drpDesg.SelectedIndex = drpDesg.Items.IndexOf(drpDesg.Items.FindByValue(drow["olddesg"].ToString()));
dt_start.Text = drow["From_Date"].ToString();
dt_end.Text = drow["To_Date"].ToString();
Search_Visible(false);
Controls_Vis(true);
FillGrid();
}
else
{
Search_Visible(true);
}
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string sql;
string role = Session["role"].ToString();
//string date = drpMonth.SelectedValue;
string date = "";
sql = "BEGIN ";
//delete earlier entered records (if any) for this proposal
sql += " delete from cadre.ret_list where propno = " + propno + "; ";
sql += "INSERT INTO cadre.ret_list " +
"SELECT e.empid,last_day (add_months(e.dob - 1, md.retdage * 12)) as DOR, " + propno + ", cdesgcode,null " +
"FROM pshr.empperso e, pshr.mast_desg md " +
"WHERE last_day (add_months(e.dob - 1, md.retdage * 12)) = '" + date + "'" +
"AND e.cdesgcode = md.desgcode " +
"AND e.recstatus = 10" +
"AND e.cdesgcode in (SELECT desgcode from cadre.person_auth_desgs where role = '" + role + "'); ";
sql += string.Format(" update cadre.ret_proposals set retdate = '{0}' where pno={1}; ", date, propno);
sql += " END;";
OraDBConnection.ExecQry(sql);
Controls_Vis(true);
FillGrid();
}
private void LockSelectiveEventData()
{
if (btnFinal.Text == TEXT_BTN_DELETE)
{
return;
}
string sql = "SELECT rdate,jdate,desg,loc FROM pshr.mast_event WHERE " +
"eventcode=" + drpEvent.SelectedValue;
System.Data.DataSet ds = OraDBConnection.GetData(sql);
if (ds != null && ds.Tables[0].Rows.Count == 1)
{
System.Data.DataRow row = ds.Tables[0].Rows[0];
txtDoR.Enabled = (row["rdate"].ToString() == "1");
txtDoR.BackColor = (row["rdate"].ToString() == "1") ? Color.White : Color.LightGray;
txtDoJ.Enabled = (row["jdate"].ToString() == "1");
txtDoJ.BackColor = (row["jdate"].ToString() == "1") ? Color.White : Color.LightGray;
txtdesg.Enabled = (row["desg"].ToString() == "1");
txtdesg.BackColor = (row["desg"].ToString() == "1") ? Color.White : Color.LightGray;
drpSancDesg.Enabled = txtpcloc.Enabled = txtploc.Enabled = (row["loc"].ToString() == "1");
drpSancDesg.BackColor = txtpcloc.BackColor = txtploc.BackColor =
(row["loc"].ToString() == "1") ? Color.White : Color.LightGray;
txtOoDate.Enabled = txtOoNum.Enabled = true;
txtOoDate.BackColor = txtOoNum.BackColor = Color.White;
}
}
private void fillCategories(string type = "sapd")
{
DataSet ds;
string sql;
//string sql = "select "+
//"distinct decode(if_sap,'Y','SAP_','N','NonSAP_') || upper(trim(category)) as cat_text,"+
//"upper(trim(category)) as cat_val,"+
//"if_sap from payment order by if_sap desc, cat_text";
if (type == "sapd" || type == "saps")
{
sql = "select distinct 'SAP_' || substr(trim(category),1,2) as cat_text, " +
"substr(trim(category),1,2) as cat_val from vpayment1617 where if_sap = 'Y'";
txtLoc.Attributes.Add("Placeholder", "e.g 12, 1234");
}
else
{
sql = "select distinct 'Non_SAP_' || trim(tbl_name) as cat_text, " +
"trim(tbl_name) as cat_val from vpayment1617 where if_sap='N' ";
txtLoc.Attributes.Add("Placeholder", "e.g U, U31");
}
ds = OraDBConnection.GetData(sql);
drpCategory.DataSource = ds;
drpCategory.DataValueField = "cat_val";
drpCategory.DataTextField = "cat_text";
drpCategory.DataBind();
ds.Dispose();
drpCategory.Items.Insert(0, new ListItem("All", "ALL"));
}
private void SaveCC(bool template, bool saveas = false)
{
string name = saveas?txtSaveAsName.Text:CC_name.Text;
string tags = CC_tags.Text.Replace("'", "''");
string data = CC_data.Text.Replace("'", "''");
string sql;
string type = template ? "T" : "N";
if (!saveas && hidSource.Text == "T")
{
lblmsg.Text = "Cannot change template.";
return;
}
sql = string.Format("merge into cadre.bigcc B using " +
"(select '{0}' as n, '{1}' as t, '{2}' as d , 'N' as ty from dual) D " +
"on (B.name = D.n and B.type='N') " +
"when matched then update set B.tags = D.t, B.data = D.d " +
"when not matched then insert (name, tags, data, type, addedon) values (D.n, D.t, D.d, D.ty, sysdate)"
, name, tags, data);
try
{
OraDBConnection.ExecQry(sql);
lblmsg.Text = template?"Template Saved":"CC Saved";
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
DisableDataFields(true);
}
protected void Page_Load(object sender, EventArgs e)
{
string sql;
System.Data.DataSet ds;
System.Data.DataRow drow;
if (!String.IsNullOrEmpty(Convert.ToString(Session["proposalno"])))
{
propno = Session["proposalno"].ToString();
}
else
{
Response.Redirect("~/retirement_proposals.aspx");
}
if (!IsPostBack)
{
//check if entries for this proposal no. exist in DB
sql = "select to_char(fromdate,'dd-Mon-YYYY') as From_Date,to_char(todate,'dd-Mon-YYYY') as To_Date,type from cadre.ret_proposals where pno = " + propno;
ds = OraDBConnection.GetData(sql);
if (ds.Tables[0].Rows.Count == 1)
{
drow = ds.Tables[0].Rows[0];
Controls_Vis(true);
FillGrid();
}
}
}
protected void btnDel_Click(object sender, EventArgs e)
{
string aloc;
if (drpLocs.SelectedValue == "")
{
Utils.ShowMessageBox(this, "No Location To Delete");
return;
}
aloc = OraDBConnection.GetScalar("select aloc from pshr.mast_loc where loccode = " + drpLocs.SelectedValue);
if (aloc != "2")
{
Utils.ShowMessageBox(this, "Not Permitted");
}
else if (aloc == "2")
{
if (OraDBConnection.ExecQry("delete from pshr.mast_loc where loccode = " + drpLocs.SelectedValue))
{
Utils.ShowMessageBox(this, "Location Deleted");
}
else
{
Utils.ShowMessageBox(this, "Error Deleting Location");
}
}
FillLocations(drploctype.SelectedValue, chkShowAll.Checked, txtFilter.Text);
}
private void resetPass()
{
string userID = panReset_drpUsers.SelectedValue;
string pass1 = panReset_txtPass1.Value;
string pass2 = panReset_txtPass2.Value;
string sql = string.Empty;
if (userID == "0")
{
return;
}
if (pass1 != pass2)
{
panReset_lblMsg.Text = "Error: Passwords does not match.";
return;
}
if (pass1.Length < 3 || pass1.Length > 64)
{
panReset_lblMsg.Text = "Error: Password length must be between 3 and 64.";
return;
}
sql = string.Format("update onlinebill.users set pass='******' where userid='{1}'", pass1, userID);
try
{
OraDBConnection.ExecQry(sql);
panReset_lblMsg.Text = "Password changed successfully.";
}
catch (Exception ex)
{
panReset_lblMsg.Text = "Error: " + ex.Message;
return;
}
}
private void activateUser()
{
string userID = panActivate_drpUsers.SelectedValue;
string sql;
if (userID == "0")
{
return;
}
if (userID == common.strAdminName)
{
panActivate_lblActive.Text = "Error: Cannot deactive admin.";
return;
}
sql = string.Format("update onlinebill.users set active = decode(active,0,1,0) where userid='{0}'", userID);
try
{
OraDBConnection.ExecQry(sql);
panActivate_lblActive.Text = "User activation status changed successfully";
}
catch (Exception ex)
{
panActivate_lblActive.Text = "Error: " + ex.Message;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string sql = "select h.empid, pshr.get_fullname(h.empid),pshr.get_categtxt(soccategory),p.dob,pshr.get_dojp(h.empid), pshr.get_desg(p.cdesgcode), p.cloccode, " +
"pshr.get_org(p.cloccode) as posting,h.pcloccode,pshr.get_org(h.pcloccode) as paychrg,pshr.get_desg(h.sancdesg) as sancdesg,h.sancindx, h.oonum, " +
" h.odate,cadre.get_hecode(p.cdesgcode) as hecode,p.branchcode,pshr.get_branch(p.empid) " +
"from pshr.empperso p, emphistory h where p.empid = h.empid and " +
"h.empid = 102430 and " +
"h.rowno = (select max(rowno) from pshr.emphistory where empid = p.empid and oonum is not null) order by hecode,p.empid ";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
CrystalReportSource CrystalReportSource1 = new CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("rptpplist.rpt");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
string pdfPath = Server.MapPath("a.pdf");
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, 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();
}
private void FillGrid()
{
string sql;
DataSet ds;
if (rbStatus.SelectedValue == "U")
{
sql = "select pno as \"Proposal_No\",pname as \"Proposal_Name\", " +
"to_char(pdate,'DD-Mon-YYYY') as \"Creation_Date\" , " +
"(select count(*) from cadre.propcadrmap where propno = pno) as entry_count" +
" from cadre.tp_proposals " +
" WHERE status='" + rbStatus.SelectedValue + "' order by pno";
ds = OraDBConnection.GetData(sql);
gvProposals.DataSource = ds;
gvProposals.DataBind();
gvProposals.Visible = true;
gvProposals_Saved.Visible = false;
}
else
{
sql = "select pno as \"Proposal_No\",pname as \"Proposal_Name\", " +
"to_char(pdate,'DD-Mon-YYYY') as \"Creation_Date\" , " +
"(select count(*) from cadre.propcadrmap where propno = pno) as entry_count,oonum, " +
"to_char(oodate,'dd-Mon-yyyy') as oodate " +
" from cadre.tp_proposals " +
" WHERE status='" + rbStatus.SelectedValue + "' order by pno desc";
ds = OraDBConnection.GetData(sql);
gvProposals_Saved.DataSource = ds;
gvProposals_Saved.DataBind();
gvProposals.Visible = false;
gvProposals_Saved.Visible = true;
}
}
private void MakeReport(bool save = false)
{
string oonum = "-";
string oodate = "-";
string propno = this.propno.ToString();
if (save)
{
oonum = txtoonum.Text;
oodate = txtoodate.Text;
}
string sql = "select * from cadre.prop_redesig where propno = " + propno + " order by fdate,empid";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
string pdfPath;
if (save)
{
pdfPath = Server.MapPath("office_orders\\" + oonum + "-BEG-I" + oodate + ".pdf");
}
else
{
pdfPath = Server.MapPath("office_orders\\preview_rdg-" + propno + "-" + DateTime.Now.ToString("yyyyMMdd-HHmmssfff") + ".pdf");
}
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rptredesig.rpt");
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)
{
if (!String.IsNullOrEmpty(Convert.ToString(Session["proposalno"])))
{
propno = Session["proposalno"].ToString();
}
else
{
Response.Redirect("~/retirement_proposals.aspx");
}
if (!IsPostBack)
{
string dt;
string sql;
DataSet ds;
sql = "select nvl(to_char(retdate,'dd-Mon-yyyy'),'0') as Ret_Date,type " +
"from cadre.ret_proposals where pno = " + propno;
ds = OraDBConnection.GetData(sql);
dt = ds.Tables[0].Rows[0]["Ret_Date"].ToString();
if (dt != "0")
{
//drpMonth.Items.Add(new ListItem(dt));
}
else
{
FillMonths();
}
Controls_Vis(true);
FillGrid();
}
}
//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 txtEmpID_TextChanged(object sender, EventArgs e)
{
string sql;
string empid;
if (Session["loginy"] == null ||
Session["loginy"].ToString().Length == 0 ||
Session["loginy"].ToString() != "1")
{
Response.Redirect("Login.aspx");
return;
}
empid = txtEmpID.Text;
//clear past info
ClearBasicInfo();
ClearEventData();
panEventGrid.Visible = false;
panEventData.Visible = false;
//if empid doesn't exist then return
sql = string.Format("SELECT count(*) FROM empperso WHERE empid='{0}'", empid);
if (OraDBConnection.GetScalar(sql) != "1")
{
return;
}
FillBasicInfo(empid);
FillEventGrid(empid);
panEventGrid.Visible = true;
panEventData.Visible = false;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string empid = txtEmpID.Text;
string desg = drpDesg.SelectedValue;
string sql = String.Empty;
string res;
panEmpHist.Visible = false;
//show error if empid doesn't exist or desg doesn't match or recstatus != 10
sql = string.Format("select count(*) from pshr.empperso where empid = '{0}' and cdesgcode = {1} and recstatus=10", empid, desg);
res = OraDBConnection.GetScalar(sql);
if (empid == "" || res != "1")
{
lblDesgMsg.Text = "Invalid EmpID";
return;
}
sql = string.Format("select count(*) from cadre.prop_redesig where empid = {0} and propno = {1}", empid, propno);
if (OraDBConnection.GetScalar(sql) != "0")
{
lblDesgMsg.Text = "EmpID Already Added";
return;
}
sql = String.Format("insert into CADRE.prop_redesig SELECT e.empid, e.firstname || ' ' || e.middlename || ' ' || e.lastname AS name, " +
"to_char(e.dob,'dd-Mon-YYYY') as DOB, m.locabb, to_char(h.fromdate,'dd-Mon-YYYY') AS Promoted_On, {1}, {0} FROM " +
"(SELECT empid, MIN(fromdate) AS fromdate FROM emphistory WHERE desgcode = {0} GROUP BY empid) h, empperso e, mast_loc m " +
"WHERE e.cdesgcode = {0} AND h.empid = e.empid AND m.loccode = e.cloccode and e.empid={2}", desg, propno, empid);
OraDBConnection.ExecQry(sql);
lblDesgMsg.Text = "EmpID Added";
FillGrid();
}
private void FillBasicInfo(string empid)
{
string sql = string.Format("select pshr.get_fullname({0}) as name,pshr.get_desg(cdesgcode) as desg,pshr.get_post(cloccode) as loc," +
"fathername from pshr.empperso where empid='{0}'", empid);
System.Data.DataSet ds = new System.Data.DataSet();
ds = OraDBConnection.GetData(sql);
if (ds.Tables[0].Rows.Count != 1)
{
return;
}
lblName.Text = ds.Tables[0].Rows[0]["name"].ToString();
lblDesg.Text = ds.Tables[0].Rows[0]["desg"].ToString();
lblCLoc.Text = ds.Tables[0].Rows[0]["loc"].ToString();
lblFName.Text = ds.Tables[0].Rows[0]["fathername"].ToString();
//get pay charge location from cadrmap
//sql = string.Format("SELECT get_post(loccode) FROM cadre.cadr WHERE rowno IN "+
// "(SELECT rowno FROM cadre.cadrmap WHERE empid={0})", empid);
sql = string.Format("select pshr.get_post(pcloccode) from emphistory where empid = {0}" +
" and rowno = (select max(rowno) from pshr.emphistory where empid={0})", empid);
lblPCLoc.Text = OraDBConnection.GetScalar(sql);
}
private void fillUsers(DropDownList drpUsers, bool fillOnlyActive = true, bool addAdmin = false)
{
DataSet ds;
bool isAdmin = Session[common.strUserID].ToString().Equals(common.strAdminName);
string sql = string.Empty;
string strAddAdmin = addAdmin ? string.Empty : string.Format("and upper(userid) <> '{0}'", common.strAdminName);
string strFillOnlyActive = fillOnlyActive ? "and active=1" : string.Empty;
string strUser = isAdmin ? string.Empty : string.Format("and upper(userid) = '{0}'", Session[common.strUserID].ToString());
string strOnlyExisting = "and userid in (select distinct userid from onlinebill.userrec)";
sql = string.Format("select userid||' (' || offcname || ')' as usern," +
" userid from onlinebill.users where 1=1 {0} {1} {2} {3} order by userid",
strAddAdmin, strFillOnlyActive, strUser, strOnlyExisting);
ds = OraDBConnection.GetData(sql);
drpUsers.DataSource = ds;
drpUsers.DataTextField = "usern";
drpUsers.DataValueField = "userid";
drpUsers.DataBind();
if (isAdmin)
{
drpUsers.Items.Insert(0, new ListItem("All Users", "ALL"));
}
ds.Clear();
ds.Dispose();
}
protected void btnAddLoc_Click(object sender, EventArgs e)
{
string sql;
if (btnAddnote.Text == "Add Note")
{
if (txtNewnote.Text.Length > 0)
{
sql = "insert into cadre.notes_person(sno,notes,ccnum) values(" + GetSNo() + ",'" + txtNewnote.Text + "',-1)";
OraDBConnection.ExecQry(sql);
}
txtNewnote.Text = "";
}
if (btnAddnote.Text == "Update Note")
{
if (txtNewnote.Text.Length > 0)
{
sql = "update cadre.notes_person set notes = '" + txtNewnote.Text + "' where sno = " + lbllstsno.Text;
OraDBConnection.ExecQry(sql);
}
txtNewnote.Text = "";
lbllstsno.Text = "";
btnAddnote.Text = "Add Note";
}
FillListAll();
}
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 = "";
}
public static DataSet GetSeniorityList(int reqdesgcode)
{
string sql;
sql = "SELECT cadrecode FROM pshr.mast_desg WHERE desgcode = " + reqdesgcode;
int cadrecd = int.Parse(OraDBConnection.GetScalar(sql).ToString());
Generate_Grades(cadrecd);
Post(reqdesgcode);
if (cadrecd != last_cadre_cd)
{
Generate_Grades(cadrecd);
Post(reqdesgcode);
last_cadre_cd = cadrecd;
}
sql = "SELECT p.empid empID,s.senno,p.firstname||' '||p.middlename||' '||p.lastname full_name, " +
" r.REVCATEGTEXT, p.dob,p.doj,o.DTPOST,d.desgtext desg, " +
" decode(d.servcode,30, b.branchtext,' ') branch, d.hecode , c.cadrename, pshr.get_post(cloccode) as location" +
" FROM empperso p, cadre.empgrdno s, empposting o, mast_desg d, mast_cadre c, " +
" mast_branch b, mast_loc l, MAST_REVCATEG r WHERE p.empid = s.empid AND " +
" s.empid = o.empid AND o.pdesgcode = d.desgcode AND s.senno between 1 AND 900000 AND " +
" o.ploccode = l.loccode AND p.branchcode = b.branchcode AND " +
" c.cadrecode = d.cadrecode AND p.REVCATEGORY = r.REVCATEGCODE AND " +
" d.desgcode = " + reqdesgcode + " ORDER BY senno";
DataSet ds = OraDBConnection.GetData(sql);
return(ds);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (txtoonum.Text.Length < 1)
{
Utils.ShowMessageBox(this, "Enter O/o Number");
return;
}
DateTime checkdate;
if (DateTime.TryParse(txtoodate.Text, out checkdate) == false)
{
Utils.ShowMessageBox(this, "Enter a valid date");
return;
}
//mark proposal as saved
string sql = string.Format("update cadre.ret_proposals set status='S',oonum='{0}',oodate='{1}' where pno={2}",
txtoonum.Text, txtoodate.Text, propno);
if (OraDBConnection.ExecQry(sql) == false)
{
Utils.ShowMessageBox(this, "Error marking proposal as saved");
return;
}
MakeReport(true);
}
private void InsertIntoCadrMap(string empid, string rowno)
{
string sql;
sql = string.Format("INSERT INTO cadre.cadrmap(empid,rowno) VALUES('{0}','{1}')", empid, rowno);
OraDBConnection.ExecQry(sql);
}
//private enum MEvents
//{
// CTRP = 36,
// CPRO = 28,
// //Retirement
// RMIN = 11,
// RSUP = 12,
// RVOR = 13,
// RPRB = 14,
// REGN = 15,
// REXP = 16,
// RMIS = 89,
// //Leave
// LELS = 2,
// LMTL =3,
// LJON = 10,
// LJONRC = 72,
// LJONTR = 73,
// LJONPR = 74,
// LJONSP = 75,
// LJONRT = 76,
// LJONEX = 77,
// JELWS = 87,
//};
private void FillSancDesgs(string lcode)
{
//string sql = "select pshr.get_desg(desgcode)||'-'||indx||'-'||substr(cadre.get_branch(branch),1,2)||'-'||ptype "
// + "as desg,desgcode,indx from cadre.cadr where loccode ='" + lcode + "' order by scaleid desc";
//removed md.gazcode=10
string sql = @"SELECT a.desg || nvl2(b.empid,'(' ||b.empid ||')',' ') AS desg,a.desgcode AS desgcode
FROM
(SELECT pshr.get_desg(cd.desgcode) ||'-'||cd.indx||'-'||SUBSTR(cadre.get_branch(cd.branch),1,2)||'-'||cd.ptype AS desg,
cd.desgcode||'-'||cd.indx as desgcode,
cd.indx as indx,
cd.rowno rowno,
md.hecode as hc
FROM cadre.cadr cd ,PSHR.mast_desg md
WHERE cd.loccode ='" + lcode + @"'
and (hia is null or hia = 0) and cd.desgcode=md.desgcode
ORDER BY hc,indx
) a
LEFT OUTER JOIN cadre.cadrmap b ON a.rowno = b.rowno";
using (System.Data.DataSet ds = OraDBConnection.GetData(sql))
{
drpSancDesg.Items.Clear();
drpSancDesg.DataSource = ds.Tables[0];
drpSancDesg.DataTextField = "desg";
drpSancDesg.DataValueField = "desgcode";
drpSancDesg.DataBind();
}
}
private string GetRowNo(string pcloccode, string sancdesg, string sancindx)
{
//get the rowno of other person
string sql = string.Format("select rowno from cadre.cadr where loccode='{0}' and desgcode='{1}' and indx='{2}'",
pcloccode, sancdesg, sancindx);
return(OraDBConnection.GetScalar(sql));
}
private void VacantCadrMap(long empid, int eventcode)
{
if (eventcode >= 11 && eventcode <= 16)
{
string sql = "delete from cadre.cadrmap where empid=" + empid.ToString();
OraDBConnection.ExecQry(sql);
}
}
