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