/// <summary> /// 通过出差单号获取出差所有人员组成的字符串 /// </summary> /// <param name="strBillCode"></param> /// <returns></returns> public string GetPersionStrByTravelAppCode(string strBillCode) { try { string strSql = "select (select '['+userCode+']'+userName from bill_users where userCode=bill_travelApplication.travelPersionCode) as userName from bill_travelApplication where maincode='" + strBillCode + "'"; DataTable dtRel = new sqlHelper.sqlHelper().GetDataTable(strSql, null); StringBuilder sb = new StringBuilder(); int iRows = dtRel.Rows.Count; if (iRows > 0) { for (int i = 0; i < iRows; i++) { sb.Append(dtRel.Rows[i][0].ToString()); sb.Append(","); } return(sb.ToString().Substring(0, sb.Length - 1)); } else { return(""); } } catch (Exception ex) { return(""); } }
/// <summary> /// 获取bill_main对应的决算flowid /// </summary> /// <param name="yslx"></param> /// <returns></returns> public string getJSFlowId(string yslx) { string strflowid = "ybbx"; if (string.IsNullOrEmpty(yslx)) { strflowid = "ybbx"; } else { string strflow = new sqlHelper.sqlHelper().GetCellValue("select note2 from bill_datadic where dictype='18' and diccode='" + yslx + "'"); if (!string.IsNullOrEmpty(strflow)) { strflowid = strflow; } } return(strflowid); //switch (yslx) //{ // case "01": return "srys"; // case "02": return "ys"; // case "03": return "zcys"; // case "04": return "chys"; // case "05": return "wlys"; // default: return "ys"; //} }
/// <summary> /// 部门预算驳回 /// </summary> /// <param name="lstYbbxmxb">集合</param> /// <param name="strErrorMsg">错误信息</param> /// <returns></returns> public int DeptBudgetRevert(string strDept, string Year, string CurrentUserCode, out string strErrorMsg) { strErrorMsg = ""; try { sqlHelper.sqlHelper server = new sqlHelper.sqlHelper(); return(server.ExecuteNonQuery("exec DeptBudgetRevert '" + Year + "','" + strDept + "','" + CurrentUserCode + "'")); } catch (Exception ex) { strErrorMsg = ex.Message; return(0); } }
protected void btnSave_Click(object sender, EventArgs e) { string ymm = this.ysmm.Text.Trim(); string yhm = this.yhm.Text.Trim(); //检查原始密码是否正确 string rel = new sqlHelper.sqlHelper().GetCellValue("select count(*) from bill_users where usercode='" + yhm + "' and userpwd='" + ymm + "'"); if (rel == "0") { ScriptManager.RegisterStartupScript(this, this.GetType(), "a", "alert('对不起,原密码输入错误');", true); return; } new sqlHelper.sqlHelper().ExecuteNonQuery("update bill_users set userpwd='" + this.xmm.Text.Trim() + "' where usercode='" + yhm + "'"); ScriptManager.RegisterStartupScript(this, this.GetType(), "a", "alert('密码修改成功');window.location.href='../Index.aspx'", true); }
/// <summary> /// 根据出差申请单号获取报告单号 /// </summary> /// <param name="strBillCode"></param> /// <returns></returns> public string GetAppCodeByReportCode(string strBillCode) { try { string strSql = "select maincode from Bill_TravelApplication where ReportCode='" + strBillCode + "'"; object objRel = new sqlHelper.sqlHelper().ExecuteScalar(strSql); if (objRel == null) { return(""); } else { return(objRel.ToString()); } } catch (Exception ex) { return(""); } }
/// <summary> /// 根据部门求话费金额 /// </summary> /// <param name="gcbh"></param> /// <param name="deptCode"></param> /// <returns></returns> //public decimal GetYueHf_dept(string gcbh, string deptCode) //{ // string strflowid = ""; // DateTime begDate = new DateTime(); // DateTime endDate = new DateTime(); // string strcn = new ConfigBLL().GetValueByKey("CYLX");//是否是财年 // if (!string.IsNullOrEmpty(strcn) && strcn == "Y") // { // string strsql = " select * from bill_ysgc where gcbh='" + gcbh + "'"; // DataTable dttime = new sqlHelper.sqlHelper().GetDataTable(strsql, null);//根据计划过程获取开始结束时间 // if (dttime != null && dttime.Rows.Count > 0) // { // begDate = Convert.ToDateTime(dttime.Rows[0]["kssj"].ToString()); // endDate = Convert.ToDateTime(dttime.Rows[0]["jzsj"].ToString()); // } // } // else // { // string[] temp = GetYsYearMonth(gcbh); // string[] yf = temp[2].Split('|'); // begDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[0]), 1); // endDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[yf.Length - 1]), 1).AddMonths(1); // } // //返回花费金额 // string je = new sqlHelper.sqlHelper().GetCellValue("exec dz_deptsyje '" + begDate + "','" + endDate + "','" + deptCode + "'"); // decimal deje = 0; // decimal.TryParse(je, out deje); // return deje; //} public decimal GetYueHf_tf(string gcbh, string deptCode, string kmCode, string strdydj) { string strflowid = ""; DateTime begDate = new DateTime(); DateTime endDate = new DateTime(); string strcn = new ConfigBLL().GetValueByKey("CYLX");//是否是财年 if (!string.IsNullOrEmpty(strcn) && strcn == "Y" && (!string.IsNullOrEmpty(strdydj))) { string strsql = " select * from bill_ysgc where gcbh='" + gcbh + "'"; DataTable dttime = new sqlHelper.sqlHelper().GetDataTable(strsql, null);//根据计划过程获取开始结束时间 if (dttime != null && dttime.Rows.Count > 0) { begDate = Convert.ToDateTime(dttime.Rows[0]["kssj"].ToString()); endDate = Convert.ToDateTime(dttime.Rows[0]["jzsj"].ToString()); } } else { string[] temp = GetYsYearMonth(gcbh); string[] yf = temp[2].Split('|'); begDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[0]), 1); endDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[yf.Length - 1]), 1).AddMonths(1); } //返回花费金额 //hfje=new sqlHelper.sqlHelper().GetCellValue("exec dz_hfje '"++"','','',''") //根据对应单据找出对应决算flowid if (!string.IsNullOrEmpty(strdydj)) { MainDal maindal = new MainDal(); strflowid = "tfsq";// maindal.getJSFlowId(strdydj); return(ysDal.GetYueHfje(begDate, endDate, deptCode, kmCode, strflowid)); } else { return(ysDal.GetYueHfje(begDate, endDate, deptCode, kmCode)); } }
/// <summary> /// 根据参数获取dataTable 并返回页面导航字符串 /// </summary> /// <param name="sql">获取数据的sql</param> /// <param name="parms">sql参数(方便参数化查询)</param> /// <param name="url">url</param> /// <param name="pageNav">输出参数 页码导航</param> /// <returns>请求页面的数据,并返回页面导航</returns> public static DataTable GetPageData(string sql, string url, out string pageNav) { sqlHelper.sqlHelper server = new sqlHelper.sqlHelper(); string pageStr = HttpContext.Current.Request["page"]; int pageIndex = 0; if (!string.IsNullOrEmpty(pageStr)) { pageIndex = Convert.ToInt32(pageStr); } DataTable dt; int count = 0; if (string.IsNullOrEmpty(sql)) { pageNav = ""; return(null); } string conSql = "select count(*) from (" + sql + ") as t"; string dtSql = "select * from ({0}) as t where t.crow>{1} and t.crow <={2}"; dtSql = string.Format(dtSql, sql, pageIndex * 10, (pageIndex + 1) * 10); count = Convert.ToInt32(server.GetCellValue(conSql)); dt = server.GetDataTable(dtSql, null); StringBuilder sb = new StringBuilder(); int pageCount = count % 10 == 0 ? (count / 10) : (count / 10 + 1); int prevNum = pageIndex - 1; int nextNum = pageIndex + 1; sb.Append("<p id='page'>"); if (prevNum >= 0) { if (url.IndexOf("?") == -1) { sb.Append("<a href='" + url + "?page=" + prevNum + "' class='n'><上一页</a>"); } else { sb.Append("<a href='" + url + "&page=" + prevNum + "' class='n'><上一页</a>"); } } if (pageCount > 1) { if (pageCount <= 7) { for (int i = 0; i < pageCount; i++) { sb.Append("<a href='" + url + "'> <span class='pc'>" + (i + 1) + "</span></a> "); if (i == pageIndex) { sb.Append("<strong><span class='pc'>" + (i + 1) + "</span></strong> "); } } } else { if (pageIndex + 1 < 5) { for (int i = 0; i < 5; i++) { sb.Append("<a href='" + url + "'> <span class='pc'>" + (i + 1) + "</span></a> "); if (i == pageIndex) { sb.Append("<strong><span class='pc'>" + (i + 1) + "</span></strong> "); } } sb.Append("…<a href='" + url + "'> <span class='pc'>" + pageCount + "</span></a> "); } else if (pageIndex + 1 > pageCount - 4) { sb.Append("<a href='" + url + "'> <span class='pc'>" + 1 + "</span></a> …"); for (int i = pageCount - 5; i < pageCount; i++) { sb.Append("<a href='" + url + "'> <span class='pc'>" + (i + 1) + "</span></a> "); if (i == pageIndex) { sb.Append("<strong><span class='pc'>" + (i + 1) + "</span></strong> "); } } } else { sb.Append("<a href='" + url + "'> <span class='pc'>" + 1 + "</span></a> …"); for (int i = pageIndex - 2; i <= pageIndex + 2; i++) { sb.Append("<a href='" + url + "'> <span class='pc'>" + (i + 1) + "</span></a> "); if (pageIndex == i) { sb.Append("<strong><span class='pc'>" + (i + 1) + "</span></strong> "); } } sb.Append("…<a href='" + url + "'> <span class='pc'>" + pageCount + "</span></a> "); } } } if (nextNum <= pageCount - 1) { if (url.IndexOf("?") == -1) { sb.Append("<a href='" + url + "?page=" + nextNum + "' class='n'>下一页></a>"); } else { sb.Append("<a href='" + url + "&page=" + nextNum + "' class='n'>下一页></a>"); } } sb.Append("<span class='nums'>共" + count + "条</span>"); sb.Append("</p>"); pageNav = sb.ToString(); return(dt); }
/// <summary> /// 根据月份,年份获得预算过程编号 /// </summary> /// <param name="year">年</param> /// <param name="month">月</param> /// <returns>过程年编号,季度编号,月编号</returns> public string GetYsgcCode(DateTime dt) { string year = dt.Year.ToString(); int month = dt.Month; //判断是否有财务年度的设置,如果有则先转换 string strcn = new ConfigBLL().GetValueByKey("CYLX"); if (!string.IsNullOrEmpty(strcn) && strcn == "Y") { //1.根据申请日期转换成财年日期 string strsql = " select year_moth from bill_Cnpz where beg_time<= '" + dt.ToString("yyyy-MM-dd") + "' and end_time>= '" + dt.ToString("yyyy-MM-dd") + "'"; string yearmonth = new sqlHelper.sqlHelper().GetCellValue(strsql); if (!string.IsNullOrEmpty(yearmonth)) { year = yearmonth.Substring(0, 4); month = int.Parse(yearmonth.Substring(5, 2)); } //else //{ // return "-1"; //} } string[] ret = new string[3]; string temp = ""; //月度编号 string tempjd = ""; //季度编号 switch (month) { case 1: temp = "0006"; tempjd = "0002"; break; case 2: temp = "0007"; tempjd = "0002"; break; case 3: temp = "0008"; tempjd = "0002"; break; case 4: temp = "0009"; tempjd = "0003"; break; case 5: temp = "0010"; tempjd = "0003"; break; case 6: temp = "0011"; tempjd = "0003"; break; case 7: temp = "0012"; tempjd = "0004"; break; case 8: temp = "0013"; tempjd = "0004"; break; case 9: temp = "0014"; tempjd = "0004"; break; case 10: temp = "0015"; tempjd = "0005"; break; case 11: temp = "0016"; tempjd = "0005"; break; case 12: temp = "0017"; tempjd = "0005"; break; } ret[0] = year + "0001"; ret[1] = year + tempjd; ret[2] = year + temp; //根据预算到的时间点返回对应的过程编号 string config = (new SysManager()).GetsysConfigBynd(year)["MonthOrQuarter"]; switch (config) { case "0": return(ret[0]); //0表示预算到年 case "1": return(ret[1]); //1表示预算到季度 case "2": return(ret[2]); //2表示预算到月度 default: return(ret[2]); } }
/// <summary> /// /// </summary> /// <param name="gcbh"></param> /// <param name="deptCode"></param> /// <param name="kmCode"></param> /// <param name="billdate"></param> /// <returns></returns> public decimal GetYueHf(string gcbh, string deptCode, string kmCode, string strdydj) { string strflowid = ""; DateTime begDate = new DateTime(); DateTime endDate = new DateTime(); string strcn = new ConfigBLL().GetValueByKey("CYLX");//是否是财年 if (!string.IsNullOrEmpty(strcn) && strcn == "Y" && (!string.IsNullOrEmpty(strdydj))) { string strsql = " select * from bill_ysgc where gcbh='" + gcbh + "'"; DataTable dttime = new sqlHelper.sqlHelper().GetDataTable(strsql, null);//根据计划过程获取开始结束时间 if (dttime != null && dttime.Rows.Count > 0) { begDate = Convert.ToDateTime(dttime.Rows[0]["kssj"].ToString()); endDate = Convert.ToDateTime(dttime.Rows[0]["jzsj"].ToString()); } } else { string[] temp = GetYsYearMonth(gcbh); string[] yf = temp[2].Split('|'); begDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[0]), 1); endDate = new DateTime(Int32.Parse(temp[0]), Int32.Parse(yf[yf.Length - 1]), 1).AddMonths(1); } //返回花费金额 //hfje=new sqlHelper.sqlHelper().GetCellValue("exec dz_hfje '"++"','','',''") bool dz_syys_flg = new ConfigBLL().GetValueByKey("dz_syys_flg").Equals("1");//大智剩余预算取值方式flg 大智的取值通过存储过程 dz_hfje if (!dz_syys_flg) { //根据对应单据找出对应决算flowid if (!string.IsNullOrEmpty(strdydj)) { MainDal maindal = new MainDal(); strflowid = maindal.getJSFlowId(strdydj); return(ysDal.GetYueHfje(begDate, endDate, deptCode, kmCode, strflowid)); } else { return(ysDal.GetYueHfje(begDate, endDate, deptCode, kmCode)); } } else { decimal deje = 0; ////string nd = ""; ////string strcnyf = ""; ////根据过程编号获取年月 // // exec [dz_hfje_bxd] '0119','2017','020107','2017-02' // //string strsql_new = @"exec dz_hfje_bxd '" + begDate + "','" + endDate + "','" + deptCode + "','" + kmCode + "'"; // try // { // string strcnyfsql = @"select nian + '-' +right('0'+ yue,2) as cnyf ,* // from bill_ysgc where gcbh='" + gcbh + "' and ysType = 2 "; // DataTable dtysgc = new sqlHelper.sqlHelper().GetDataTable(strcnyfsql, null); // if (dtysgc!=null&&dtysgc.Rows.Count>0) // { // nd = dtysgc.Rows[0]["nian"].ToString(); // strcnyf = dtysgc.Rows[0]["cnyf"].ToString(); // } // IList<yskmhf> ysmxb_hf = new Bll.newysgl.bill_ysmxbBll().getkmje(deptCode, nd); // string strje = ysmxb_hf.Where(p => p.fykm == kmCode && p.cnyf == strcnyf).Sum(p => p.je).ToString(); // if (decimal.TryParse(strje, out deje)) // { // return deje; // } // else // { // return 0; // } // } // catch (Exception) // { // return 0; // } string strsql = @"exec dz_hfje '" + begDate + "','" + endDate + "','" + deptCode + "','" + kmCode + "'"; try { string je = new sqlHelper.sqlHelper().GetCellValue(strsql); if (!string.IsNullOrEmpty(je)) { decimal.TryParse(je, out deje); } return(deje); } catch (Exception) { return(0); } } }