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 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);
}
}
}
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);
}
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 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 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);
}
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 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 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 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();
}
}
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();
}
}
}
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);
}
private void FillCCsOpen()
{
string sql = "select name, name || '(' || tags || ')' || '-' || type as txtval from cadre.bigcc order by type, name";
ddOpen.DataSource = OraDBConnection.GetData(sql);
ddOpen.DataTextField = "txtval";
ddOpen.DataValueField = "name";
ddOpen.DataBind();
}
private void FillCCsCreate()
{
string sql = "select name, name || '(' || tags || ')' as txtval from cadre.bigcc where type='T' order by name";
ddCreate.DataSource = OraDBConnection.GetData(sql);
ddCreate.DataTextField = "txtval";
ddCreate.DataValueField = "name";
ddCreate.DataBind();
ddCreate.Items.Insert(0, new ListItem("New", "New"));
}
private void FillDesg()
{
string sql = "select desgcode, desgabb from pshr.mast_desg where adesg='A' order by desgabb";
DataSet ds = OraDBConnection.GetData(sql);
drpDesg.DataSource = ds.Tables[0];
drpDesg.DataTextField = "desgabb";
drpDesg.DataValueField = "desgcode";
drpDesg.DataBind();
}
private static bool Generate_Grades(int cad_cd)
{
string sql;
DataSet ds_sel;
sql = "DELETE FROM cadre.empgrdno";
OraDBConnection.ExecQry(sql);
sql = "SELECT s.empid, s.senno, s.sendate, d.cadrecode, d.hecode, e.revcategory, e.branchcode " +
"FROM pshr.empsen s, pshr.mast_desg d, pshr.empperso e, pshr.mast_cadre x " +
"WHERE s.empid=e.empid AND e.cdesgcode = d.desgcode AND s.senno BETWEEN '1' AND '999999' AND " +
"d.cadrecode = (SELECT u.cadregrp FROM mast_cadre u WHERE u.cadrecode = " + cad_cd + ") " +
"AND d.cadrecode = x.cadrecode " +
"AND e.branchcode = (SELECT j.branchcode FROM mast_cadre j WHERE j.cadrecode = " + cad_cd + ") " +
"AND e.recstatus = 10 ORDER BY senno";
ds_sel = OraDBConnection.GetData(sql);
if (ds_sel.Tables[0].Rows.Count == 0)
{
return(false);
}
int senno = 0;
foreach (DataRow drow in ds_sel.Tables[0].Rows)
{
senno++;
sql = string.Format("INSERT INTO cadre.empgrdno VALUES({0}, '0', {1}, {2}, {3}, {4}, {5})",
drow["empID"], (drow["hecode"] == System.DBNull.Value) ? 99 : drow["hecode"], drow["cadrecode"], senno, drow["revcategory"], drow["branchcode"]);
OraDBConnection.ExecQry(sql);
}
senno = 0;
sql = " SELECT * FROM cadre.empgrdno ORDER BY hecode, senno";
ds_sel = OraDBConnection.GetData(sql);
int grp_cd = 9999;
foreach (DataRow drow in ds_sel.Tables[0].Rows)
{
if (int.Parse(drow["hecode"].ToString()) != grp_cd)
{
senno = 1;
grp_cd = int.Parse(drow["hecode"].ToString());
}
else
{
senno++;
}
sql = "UPDATE cadre.empgrdno SET grdno = " + senno + " WHERE empid = " + drow["empid"];
OraDBConnection.ExecQry(sql);
}
return(true);
}
protected void btnShow_Click(object sender, EventArgs e)
{
DataSet ds;
string sql = "select pshr.get_fullname(empid) as name, pshr.get_desg(cdesgcode) as desg, pshr.get_org(cloccode) as loc, seniorityno from pshr.empperso where empid = " + txtEmpID.Text;
ds = OraDBConnection.GetData(sql);
lblNameDesg.Text = string.Format("{0}, {1}", ds.Tables[0].Rows[0]["name"].ToString(), ds.Tables[0].Rows[0]["desg"].ToString());
lblLoc.Text = ds.Tables[0].Rows[0]["loc"].ToString();
lblSen.Text = "Sen. No. " + ds.Tables[0].Rows[0]["seniorityno"].ToString();
update.Visible = true;
}
private void FillGrid()
{
string sql;
DataSet ds;
sql = "select pno as \"Proposal_No\",pname as \"Proposal_Name\"," +
"to_char(pdate,'DD-Mon-YYYY') as \"Creation_Date\",TYPE from cadre.proposals" +
" WHERE status='" + rbStatus.SelectedValue + "' and type='" + PropType.Value + "'";
ds = OraDBConnection.GetData(sql);
gvProposals.DataSource = ds;
gvProposals.DataBind();
}
private void FillListAll(string filter = "")
{
string sql;
sql = "select notes,sno from cadre.notes_person " + "where upper(notes) like upper('%" + filter + "%') order by sno";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
lstAll.Items.Clear();
lstAll.DataSource = ds.Tables[0];
lstAll.DataTextField = "notes";
lstAll.DataValueField = "sno";
lstAll.DataBind();
}
public static string GetLocs2()
{
StringBuilder sbLocs = new StringBuilder();
string sql = "select loccode,locname from pshr.mast_loc where aloc=1 order by locname";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
foreach (System.Data.DataRow drow in ds.Tables[0].Rows)
{
sbLocs.AppendFormat("{0}-{1}:", drow["locname"], drow["loccode"]);
}
return(sbLocs.ToString());
}
private void MakeReport(bool save = false)
{
string oonum = "-";
string oodate = "-";
string propno = this.propno.ToString();
string type;
string pdfPath;
if (save)
{
oonum = txtoonum.Text;
oodate = txtoodate.Text;
}
//string sql = "select * from cadre.ret_list where propno = " + propno + " order by empid";
string sql = "select rownum as \"#\", '" + oonum + "' as onum, '" + oodate + "' as odate, a.* from " +
"(SELECT e.empid, " +
"firstname||' '|| decode(middlename, NULL, '', middlename||' ') || lastname AS name, " +
"to_char(e.dob,'dd/MM/YYYY') AS DOB, md.desgtext as DESG, ml.locname as LOC, " +
"to_char(rl.dor,'dd/MM/YYYY') as DOR, " +
"rp.type as reptype " +
"FROM cadre.ret_list rl, pshr.mast_loc ml, pshr.mast_desg md, pshr.empperso e, cadre.ret_proposals rp " +
"WHERE rl.empid = e.empid " +
"AND e.cloccode = ml.loccode " +
"AND e.cdesgcode = md.desgcode " +
"AND rl.propno = " + propno +
" AND rp.pno = rl.propno" +
" order by md.hecode,e.empid) a";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//select report according to type (voluntary or supperannuation)
type = ds.Tables[0].Rows[0]["reptype"].ToString();
if (save)
{
pdfPath = Server.MapPath("office_orders\\" + oonum + "-BEG-3" + oodate + ".pdf");
}
else
{
pdfPath = Server.MapPath("office_orders\\preview_ret-" + propno + "-" + DateTime.Now.ToString("yyyyMMdd-HHmmssfff") + ".pdf");
}
CrystalReportSource CrystalReportSource1 = new CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rpt_ret_vol.rpt");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
CrystalReportSource1.Dispose();
Utils.DownloadFile(pdfPath);
}
private void OpenCC(string name, string source)
{
/*We may be here because:
* 1. Create New CC -- source == "New"
* 2. Create from Template -- source == "T"
* 3. Open a Template --Source == "T"
* 4. Open a CC -- Source = "N"
* 5. Search and then open a Template -- Source = "T"
* 6. Search and then open a CC -- Source == "N"
*/
string sql;
DataRow drow;
EmptyDataFields();
DisableDataFields(false);
lblmsg.Text = string.Empty;
div_data.Visible = true;
hidSource.Text = source;
if (source == "New")
{
btnSave.Enabled = true;
btnMakeTemplate.Enabled = true;
heading.InnerText = "CC";
}
else if (source == "N")
{
sql = string.Format("select name, tags, data, type from cadre.bigcc where name = '{0}'", name);
drow = OraDBConnection.GetData(sql).Tables[0].Rows[0];
CC_name.Text = drow["name"].ToString();
CC_tags.Text = drow["tags"].ToString();
CC_data.Text = drow["data"].ToString();
btnSave.Enabled = true;
btnMakeTemplate.Enabled = true;
heading.InnerText = "CC";
}
else if (source == "T")
{
sql = string.Format("select name, tags, data, type from cadre.bigcc where name = '{0}'", name);
drow = OraDBConnection.GetData(sql).Tables[0].Rows[0];
CC_name.Text = drow["name"].ToString();
CC_tags.Text = drow["tags"].ToString();
CC_data.Text = drow["data"].ToString();
btnSave.Enabled = false;
btnMakeTemplate.Enabled = false;
heading.InnerText = "Template";
}
}
public static string GetDesgs2()
{
StringBuilder sbLocs = new StringBuilder();
//string sql = "select desgcode, desgtext from pshr.mast_desg where gazcode=10 order by hecode";
string sql = "select desgcode, desgtext from pshr.mast_desg order by hecode";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
foreach (System.Data.DataRow drow in ds.Tables[0].Rows)
{
sbLocs.AppendFormat("{0}-{1}:", drow["desgtext"], drow["desgcode"]);
}
return(sbLocs.ToString());
}
void ShowLastUploads()
{
string userID = Session["userID"].ToString();
string sql = string.Empty;
DataSet ds;
sql = String.Format("select rownum as \"#\", a.* from (select categ,insrec as ins,duprec as dup,errrec as err,to_char(dated,'hh:mi:ss pm') time " +
"from USERREC where userid = '{0}' and to_char(dated,'yyyymmdd') = to_char(sysdate, 'yyyymmdd') " +
"order by dated) a", userID);
ds = OraDBConnection.GetData(sql);
gvLastUploads.DataSource = ds.Tables[0];
gvLastUploads.DataBind();
}
protected void btnArchive_Click(object sender, EventArgs e)
{
string acno = lblAcNo.Text;
string tbl = lblCat.Text;
string userid = Session[common.strUserID].ToString();
string sql = string.Empty;
string sqlCond = string.Empty;
string sqlDel = string.Empty;
string sqlIns = string.Empty;
string sqlLog = string.Empty;
string year = DateTime.Now.Year.ToString();
DataSet ds;
Dictionary <string, string> dict_acno = new Dictionary <string, string>()
{
{ "DSBELOW10KW", "ACCOUNTNO" },
{ "DSABOVE10KW", "ACCOUNTNO" },
{ "LS", "ACCOUNTNO" },
{ "MS", "PRT_AC_NO" },
{ "SP", "ACNO" }
};
if (!dict_acno.ContainsKey(tbl))
{
lblMsg.Text = "Invalid Category";
return;
}
sqlCond = string.Format(" upper({0}) = upper('{1}') and upper(userid) = upper('{2}') ", dict_acno[tbl], acno, userid);
sql = string.Format("select count(*) from {0} where {1}", tbl, sqlCond);
ds = OraDBConnection.GetData(sql);
if (ds.Tables[0].Rows[0][0].ToString() != "1")
{
lblMsg.Text = "No current bill or not uploaded by this user";
return;
}
sqlIns = string.Format("insert into ARCBILL.ARC_{0}_{1} select * from {1} where {2}", year, tbl, sqlCond);
sqlDel = string.Format("delete from {0} where {1}", tbl, sqlCond);
sqlLog = string.Format("insert into ARCHIVEBILL_LOG values ('{0}','{1}', sysdate)", acno, userid);
sql = string.Format("BEGIN {0}; {1}; {2}; END;", sqlIns, sqlDel, sqlLog);
try
{
OraDBConnection.ExecQry(sql);
lblMsg.Text = "Record Archived";
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
protected void btnOPList0_Click(object sender, EventArgs e)
{
//TestOPList();
//return;
string file = Server.MapPath("office_orders\\auto_oplist.xlsx");
string sql = "select loccode,locabb from pshr.mast_loc where loccode like '___000000' and aloc=1 and loctype in (11,20) order by locabb";
DataSet ds = OraDBConnection.GetData(sql);
bool retVal;
if (File.Exists(file))
{
File.Delete(file);
}
FileInfo newFile = new FileInfo(file);
ExcelWorksheet sheet;
const int NUM_COLS = 16;
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
foreach (DataRow drow in ds.Tables[0].Rows)
{
sheet = xlPackage.Workbook.Worksheets.Add(drow["locabb"].ToString());
retVal = CreateOpListWorkSheet(drow["loccode"].ToString(), sheet);
if (!retVal)
{
xlPackage.Workbook.Worksheets.Delete(sheet);
}
}
//combined sheet
ExcelWorksheet combined = xlPackage.Workbook.Worksheets.Add("Combined");
ExcelWorksheet origSheet;
//headings
xlPackage.Workbook.Worksheets[1].Cells[4, 1, 4, NUM_COLS].Copy(combined.Cells[1, 1]);
//sheets loop
for (int i = 1, currRow = 2; i <= xlPackage.Workbook.Worksheets.Count - 1; i++)
{
origSheet = xlPackage.Workbook.Worksheets[i];
for (int r = 5; origSheet.Cells[r, 1].Value != null; r++, currRow++)
{
origSheet.Cells[r, 1, r, NUM_COLS].Copy(combined.Cells[currRow, 1]);
}
}
xlPackage.Save();
}
btnOPList0.Enabled = true;
Utils.DownloadFile(file, true, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
private void fillVendors()
{
DataSet ds;
string sql = "select vid as ven_val, vname as ven_text from ONLINEBILL.MASTER_PAYMENT_VENDOR order by vname";
ds = OraDBConnection.GetData(sql);
drpVendor.DataSource = ds;
drpVendor.DataValueField = "ven_val";
drpVendor.DataTextField = "ven_text";
drpVendor.DataBind();
ds.Dispose();
drpVendor.Items.Insert(0, new ListItem("All", "ALL"));
}
private void FillListAll()
{
string sql;
sql = "select loc,sno from cadre.cclist_person where sno not in " +
"(select ccnum from cadre.cclist_proposal_person where proposalno = " + prono +
") order by sno";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
lstAll.Items.Clear();
lstAll.DataSource = ds.Tables[0];
lstAll.DataTextField = "loc";
lstAll.DataValueField = "sno";
lstAll.DataBind();
}
private void FillEventData()
{
long empid = Convert.ToInt64(txtEmpID.Text.Trim());
int rowno = Convert.ToInt32(GridView1.SelectedRow.Cells[1].Text.Trim());
string sql = String.Format("SELECT eventcode,to_char(fromdate,'DD-MON-YYYY') as fromdate,to_char(todate,'DD-MON-YYYY') as todate," +
"get_desg(desgcode) ||'-'|| desgcode AS desg,get_post(loccode) ||'-'|| loccode AS ploc," +
"get_post(pcloccode) ||'-'|| pcloccode AS pcloc,pcloccode, pshr.get_desg(sancdesg) AS sancdesgt," +
"sancdesg,sancindx,oonum,to_char(odate,'DD-MON-YYYY') as odate " +
"FROM emphistory WHERE empid={0} AND rowno={1}", empid, rowno);
System.Data.DataSet ds = OraDBConnection.GetData(sql);
if (ds.Tables[0].Rows.Count != 1)
{
lblmsg.Text = "Error: More than one row found";
return;
}
System.Data.DataRow row = ds.Tables[0].Rows[0];
//fill eventcode
drpEvent.SelectedIndex = drpEvent.Items.IndexOf(drpEvent.Items.FindByValue(row["eventcode"].ToString()));
//fill date of relieving
txtDoR.Text = row["fromdate"].ToString();
//fill date of joining
txtDoJ.Text = row["todate"].ToString();
//fill desgcode
txtdesg.Text = (row["desg"].ToString() == "-") ? "" : row["desg"].ToString();
//fill posting location
txtploc.Text = (row["ploc"].ToString() == "-") ? "" : row["ploc"].ToString();
//fill pay charge location
txtpcloc.Text = (row["pcloc"].ToString() == "-") ? "" : row["pcloc"].ToString();
//fill hidden field hidsancpost with sanc desg and indx
hidSancPost.Value = row["sancdesg"].ToString() + "-" + row["sancindx"].ToString();
//fill sanctioned designation
FillSancDesgs(row["pcloccode"].ToString());
drpSancDesg.SelectedIndex = drpSancDesg.Items.IndexOf(drpSancDesg.Items.FindByValue(hidSancPost.Value));
//fill O/o Num
txtOoNum.Text = row["oonum"].ToString();
//fill O/o Date
txtOoDate.Text = row["odate"].ToString();
}
