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