private string GetSql()
{
string flowid = "ccsq";
string usercode = Session["userCode"].ToString();
//IList<string> list = new WorkFlowRecordManager().GetAppBill(usercode, request);
string strStatus = "1";//this.rdoStatusNow.Checked ? "1" : "2";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, flowid, strStatus);
StringBuilder sb = new StringBuilder();
if (list.Count > 0)
{
sb.Append("select convert(varchar(10),a.billdate,121) as billDate,a.billcode,a.flowID , (select '['+userCode+']'+userName from bill_users where userCode=a.billUser) as billUser ,a.billJe, isnull((select '['+deptcode+']'+deptName from bill_departments where deptcode=a.billDept),a.billdept) as billdept, Row_number()over(order by a.billdate desc,a.billName desc) as crow ,b.travelDate,b.arrdess,b.reasion,b.needAmount,b.travelplan from bill_main a ,(select distinct arrdess,reasion,travelDate,needamount,travelplan,maincode from bill_travelApplication) b where a.billCode=b.maincode and a.flowid='" + flowid + "' and a.billcode in(");
foreach (string billcode in list)
{
sb.Append("'");
sb.Append(billcode);
sb.Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
return(sb.ToString());
}
else
{
return(null);
}
}
private string GetSql()
{
string flowid = "ystz";
string usercode = Session["userCode"].ToString();
//IList<string> list = new WorkFlowRecordManager().GetAppBill(usercode, request);
string strStatus = "1";//this.rdoStatusNow.Checked ? "1" : "2";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, flowid, strStatus);
StringBuilder sb = new StringBuilder();
if (list.Count > 0)
{
sb.Append(" select convert(char(10), billDate,121) as showdate,*,isnull((select '['+deptCode+']'+deptname from bill_departments where deptCode=billDept),billDept)as showDept,(select top 1 (xmmc) from bill_ysgc a,bill_ysmxb b where a.gcbh=b.gcbh and b.billcode=bill_main.billcode ) as ysgc , Row_number()over(order by billdate desc,billName desc) as crow from bill_main where flowid='" + flowid + "' and billcode in(");
foreach (string billcode in list)
{
sb.Append("'");
sb.Append(billcode);
sb.Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
return(sb.ToString());
}
else
{
return(null);
}
}
private string GetSql()
{
string flowid = "lscg";
string usercode = Session["userCode"].ToString();
//IList<string> list = new WorkFlowRecordManager().GetAppBill(usercode, request);
string strStatus = "1";//this.rdoStatusNow.Checked ? "1" : "2";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, flowid, strStatus);
StringBuilder sb = new StringBuilder();
if (list.Count > 0)
{
sb.Append("select convert(varchar(10),a.billdate,121) as billDate,a.billcode,a.flowID , (select '['+userCode+']'+userName from bill_users where userCode=a.billUser) as billUser ,a.billJe, isnull((select '['+deptcode+']'+deptName from bill_departments where deptcode=a.billDept),a.billdept) as billdept, Row_number()over(order by a.billdate desc,a.billName desc) as crow, (select case when len(Convert(varchar(2000),zynr))>20 then substring(zynr,0,20)+'...' else zynr end from bill_lscg where cgbh =a.billCode) as zynr from bill_main a,bill_lscg b where a.billCode=b.cgbh and a.flowid='" + flowid + "' and a.billcode in(");
foreach (string billcode in list)
{
sb.Append("'");
sb.Append(billcode);
sb.Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
return(sb.ToString());
}
else
{
return(null);
}
}
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType != ListItemType.Header && e.Item.ItemType != ListItemType.Footer)
{
HiddenField hf = e.Item.FindControl("hfCode") as HiddenField;
string code = hf.Value.Trim();
if (!string.IsNullOrEmpty(code))
{
StringBuilder sb = new StringBuilder();
string status = "";
DataTable workflow = server.GetDataTable("select * from workflowrecord where billCode='" + code + "'", null);
if (workflow.Rows.Count == 0)
{
sb.Append("<div class='checkStatus' ><lable>审批状态：未提交</lable></div>");
}
else
{
status = workflow.Rows[0]["rdState"].ToString();
if (status == "1")
{
WorkFlowRecordManager bll = new WorkFlowRecordManager();
string state = bll.WFState(code);
sb.Append("<div class='checkStatus' ><lable>审批状态：" + state + "</lable></div>");
}
else if (status == "2")
{
sb.Append("<div class='checkStatus'><lable>审批状态：审核通过</lable></div>");
}
else if (status == "3")
{
string flowid = server.GetCellValue("select flowid from bill_main where billCode='" + code + "'");
sb.Append("<div class='checkStatus'><lable>审批状态：驳回</lable></div>");
sb.Append("<div style='text-align:center'><a data-role='button' data-inline='true' data-theme='d' onclick=\"RevokeCheck(this,'" + flowid + "','" + code + "')\"><img src='../images/metro/Editor.png' />审核撤销</a></div>");
}
}
Label lb = e.Item.FindControl("lbmx") as Label;
lb.Text = sb.ToString();
HtmlContainerControl hcc = e.Item.FindControl("optionDiv") as HtmlContainerControl;
if (status == "3")
{
hcc.Attributes.CssStyle.Add("display", "none");
}
else if (!string.IsNullOrEmpty(status))
{
hcc.Visible = false;
}
}
}
}
private int GetData()
{
string date = (Convert.ToString(DateTime.Now)).Substring(0, 4) + "0001";
string request = Request.QueryString["flowid"];
if (string.IsNullOrEmpty(request))
{
request = "ybbx";
}
string flowid = request;
string usercode = Session["userCode"].ToString();
string strStatus = "1";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, request, strStatus);
return(list.Count);
}
private string GetSql()
{
string request = Request.QueryString["flowid"];
string strisdz = "";
if (!string.IsNullOrEmpty(Request["isdz"]))
{
strisdz = Request["isdz"].ToString();
}
if (string.IsNullOrEmpty(request))
{
request = "ybbx";
}
string flowid = request;
if (flowid == "xmzf")
{
request = "ybbx";
}
string usercode = Session["userCode"].ToString();
//IList<string> list = new WorkFlowRecordManager().GetAppBill(usercode, request);
string strStatus = "1";//this.rdoStatusNow.Checked ? "1" : "2";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, request, strStatus);
StringBuilder sb = new StringBuilder();
if (list.Count > 0)
{
if (strisdz == "1")
{
// string sql = @"select Row_Number()over(order by billdate desc,billName desc) as crow ,sum(je) as billJe,billname,flowid,stepid,billuser,billdate,isgk,(select bxzy from bill_ybbxmxb
//where bill_ybbxmxb.billCode=(select top 1 billcode from bill_main where billname=main.billName)) as bxzy,
//(select xmmc from bill_ysgc where gcbh=billName) as billName2,(select top 1 billdept from bill_main where billname=main.billname) as billdept,
//(select '['+usercode+']'+username from bill_users where usercode=billuser) as billUserName
//from bill_main main inner join bill_ybbxmxb_fykm
//fykm on main.billcode=fykm.billcode where flowid='" + flowid + "' and (billUser='******' or main.billcode in (select billCode from bill_ybbxmxb where bxr='" + Session["userCode"].ToString().Trim() + "') )";
sb.Append(@"select (case a.flowID when 'ys' then (select '['+gcbh+']'+xmmc from bill_ysgc where gcbh=a.billName) else a.billName end) as tbillName
, convert(varchar(10),a.billdate,121) as tbilldate,sum(billJe) as billJe ,a.billName,a.flowID , (select '['+userCode+']'+userName from bill_users
where userCode=a.billUser) as billUser
,(select bxsm from bill_ybbxmxb where bill_ybbxmxb.billCode=(select top 1 billcode from bill_main where billname=a.billName)) as bxsm
, isnull((select '['+deptcode+']'+deptName from bill_departments where deptcode=a.billDept),a.billdept) as billdept, a.isgk
, Row_number()over(order by a.billdate ,a.billName ) as crow, (select case when len(bxzy)>20 then substring(bxzy,0,20)+'...' else bxzy end from bill_ybbxmxb
where billCode =(select top 1 billcode from bill_main where billname=a.billname)) as bxzy from bill_main a,bill_ybbxmxb b
where a.billCode=b.billcode and a.flowid='" + flowid + "' and a.billname in(");
}
else
{
sb.Append("select (case a.flowID when 'ys' then (select '['+gcbh+']'+xmmc from bill_ysgc where gcbh=a.billName) else a.billName end) as tbillName, convert(varchar(10),a.billdate,121) as tbilldate,a.billcode,a.billName,a.flowID , (select '['+userCode+']'+userName from bill_users where userCode=a.billUser) as billUser ,a.billJe, isnull((select '['+deptcode+']'+deptName from bill_departments where deptcode=a.billDept),a.billdept) as billdept, a.isgk, Row_number()over(order by a.billdate ,a.billName ) as crow, (select case when len(bxzy)>20 then substring(bxzy,0,20)+'...' else bxzy end from bill_ybbxmxb where billCode =a.billCode) as bxzy from bill_main a,bill_ybbxmxb b where a.billCode=b.billcode and a.flowid='" + flowid + "' and a.billcode in(");
}
foreach (string billcode in list)
{
sb.Append("'");
sb.Append(billcode);
sb.Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
if (strisdz == "1")
{
sb.Append(" group by billname,flowid,stepid,billuser,billdate,isgk,billdept");
}
return(sb.ToString());
}
else
{
return(null);
}
}
protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType != ListItemType.Header && e.Item.ItemType != ListItemType.Footer)
{
HiddenField hf = e.Item.FindControl("hfCode") as HiddenField;
if (Request["isdz"].ToString() == "1")
{
hf = e.Item.FindControl("hfname") as HiddenField;
}
string code = hf.Value.Trim();
HiddenField billdate = e.Item.FindControl("hiddatetime") as HiddenField;
HiddenField hiddeptCode = e.Item.FindControl("hidbillDept") as HiddenField;
string billDate = billdate.Value;
string deptCode = hiddeptCode.Value;
if (!string.IsNullOrEmpty(code))
{
string strsql = @"select mxGuid ,fykm as yskmCode,(select '['+yskmCode+']'+yskmMc as yskm from bill_yskm where yskmCode=f.fykm ) as yskmMc,isnull(je ,0) as je from bill_ybbxmxb_fykm f where 1=1";
if (Request["isdz"].ToString() == "1")
{
strsql += " and billCode in(select billcode from bill_main where billname=('" + code + "'))";
}
else
{
strsql += " and billCode='" + code + "'";
}
DataTable dt = server.GetDataTable(strsql, null);
if (dt.Rows.Count > 0)
{
string strworksql = @"select * from workflowrecord where billCode='" + code + "'";
DataTable workflow = server.GetDataTable(strworksql, null);
string status = "";
StringBuilder sb = new StringBuilder();
for (int i = 0; i < dt.Rows.Count; i++)
{
//预算金额
string strdydj = "02";
deptCode = string.IsNullOrEmpty(deptCode) ? "" : deptCode.Split(']')[0].Trim('[');
string kmCode = string.IsNullOrEmpty(dt.Rows[i]["yskmMc"].ToString()) ? "" : dt.Rows[i]["yskmMc"].ToString().Split(']')[0].Trim('[');
string gcbh = ysmgr.GetYsgcCode(DateTime.Parse(billDate));
if (!string.IsNullOrEmpty(Request["flowid"]))
{
if (Request["flowid"].ToString() == "ybbx")
{
strdydj = "02";
}
}
decimal ysje = ysje = ysmgr.GetYueYs(gcbh, deptCode, kmCode); //预算金额
decimal hfje = ysmgr.GetYueHf(gcbh, deptCode, kmCode, strdydj); //花费金额
//是否启用销售提成模块
bool hasSaleRebate = new ConfigBLL().GetValueByKey("HasSaleRebate").Equals("1");
decimal syje = ysje - hfje;
sb.Append("<div class='div-yskm'>");
sb.Append("<table class='tab-yskm'>");
//sb.Append("<tr><td class='tdOdd'>费用科目:</td><td>" + Convert.ToString(dt.Rows[i]["yskmMc"]) + "</td></tr>");
sb.Append("<tr><td>" + Convert.ToString(dt.Rows[i]["yskmMc"]) + "预算金额：￥(" + Convert.ToDecimal(ysje).ToString("N02") + ") 剩余金额：￥(" + Convert.ToDecimal(syje).ToString("N02") + ")" + "报销金额：￥(" + Convert.ToDecimal(dt.Rows[i]["je"]).ToString("N02") + ")</td></tr>");
sb.Append("</table>");
sb.Append("</div>");
DataTable temp = server.GetDataTable("select (select '['+deptCode+']'+deptName from bill_departments where deptCode=a.deptCode) as Dept,isnull(je,0) as je from bill_ybbxmxb_fykm_dept a where kmmxGuid ='" + Convert.ToString(dt.Rows[i]["mxGuid"]) + "'", null);
if (temp.Rows.Count > 0)
{
sb.Append("<div class='div-hs'>");
sb.Append("<h5>核算部门</h5>");
sb.Append("<table class='tab-hs ItemTable'>");
// sb.Append("<tr><th class='tdOdd'>部门</th><th>核算金额</th></tr>");
for (int j = 0; j < temp.Rows.Count; j++)
{
sb.Append("<tr><td class='tdOdd'>" + Convert.ToString(temp.Rows[j]["Dept"]) + ": " + Convert.ToDecimal(temp.Rows[j]["je"]).ToString("N02") + "￥</td></tr>");
}
sb.Append("</table>");
sb.Append("</div>");
}
temp = server.GetDataTable("select distinct '['+b.xmCode+']'+xmName as xmCode,isnull(je,0) as je from bill_ybbxmxb_hsxm a,bill_xm b where a.xmcode=b.xmcode and kmmxGuid='" + Convert.ToString(dt.Rows[i]["mxGuid"]) + "'", null);
if (temp.Rows.Count > 0)
{
sb.Append("<div class='div-hs'>");
sb.Append("<h5>核算项目</h5>");
sb.Append("<table class='tab-hs ItemTable'>");
// sb.Append("<tr><th class='tdOdd'>项目</th><th>核算金额</th></tr>");
for (int j = 0; j < temp.Rows.Count; j++)
{
sb.Append("<tr><td class='tdOdd'>" + Convert.ToString(temp.Rows[j]["xmCode"]) + ": " + Convert.ToDecimal(temp.Rows[j]["je"]).ToString("N02") + "￥</td></tr>");
}
sb.Append("</table>");
sb.Append("</div>");
}
}
if (workflow.Rows.Count == 0)
{
sb.Append("<div class='checkStatus' ><lable>审批状态：未提交</lable></div>");
}
else
{
status = workflow.Rows[0]["rdState"].ToString();
if (status == "1")
{
WorkFlowRecordManager bll = new WorkFlowRecordManager();
string state = bll.WFState(code);
sb.Append("<div class='checkStatus' ><lable>审批状态：" + state + "</lable></div>");
}
else if (status == "2")
{
sb.Append("<div class='checkStatus'><lable>审批状态：审核通过</lable></div>");
}
else if (status == "3")
{
string flowid = server.GetCellValue("select flowid from bill_main where billCode='" + code + "'");
sb.Append("<div class='checkStatus'><lable>审批状态：驳回</lable></div>");
sb.Append("<div style='text-align:center'><a data-role='button' data-inline='true' data-theme='d' onclick=\"RevokeCheck(this,'" + flowid + "','" + code + "')\"><img src='../images/metro/Editor.png' />审核撤销</a></div>");
}
}
Label lb = e.Item.FindControl("lbmx") as Label;
lb.Text = sb.ToString();
HtmlContainerControl hcc = e.Item.FindControl("optionDiv") as HtmlContainerControl;
if (status == "3")
{
hcc.Attributes.CssStyle.Add("display", "none");
}
else if (!string.IsNullOrEmpty(status))
{
hcc.Visible = false;
}
}
}
}
}
private int GetSql(string type)
{
string strisdz = "";
if (!string.IsNullOrEmpty(Request["isdz"]))
{
strisdz = Request["isdz"].ToString();
}
string date = (Convert.ToString(DateTime.Now)).Substring(0, 4) + "0001";
string request = type;
string flowid = request;
if (flowid == "xmzf")
{
request = "ybbx";
}
string usercode = Session["userCode"].ToString();
string strStatus = "1";//this.rdoStatusNow.Checked ? "1" : "2";
IList <string> list = new WorkFlowRecordManager().GetAppBill(usercode, request, strStatus);
StringBuilder sb = new StringBuilder();
if (list.Count < 1)
{
if (flowid == "gkbx" || flowid == "tfsq" || (strisdz == "1" && (flowid == "ybbx" || flowid == "yksq_dz")))
{
sb.Append(@"select (select top 1 billcode from bill_main where billname=main.billName) as billcode, sum(billJe) as billJe
,convert(varchar(10),billdate,121) as tbilldate,billname as tbillName,flowid,stepid,billuser,billdate,isgk, billdept as gkdept
,(select bxzy from bill_ybbxmxb where bill_ybbxmxb.billCode=(select top 1 billcode from bill_main where billname=main.billName)) as bxzy
,(select xmmc from bill_ysgc where gcbh=billName) as billName,(select top 1 billdept from bill_main where billname=main.billname) as billdept
,Row_number()over(order by billdate,billName desc) as crow from bill_main main where flowid='" + flowid + "' and 1=2");
}
else
{
sb.Append(@" select convert(varchar(10),billdate,121) as tbilldate,*,(case flowID when 'ys' then (select '['+gcbh+']'+xmmc from bill_ysgc
where gcbh=bill_main.billName) else billName end) as tbillName ,Row_number()over(order by billdate,billName desc) as crow
from bill_main
where flowid='" + flowid + "' and 1=2 ");
}
}
else
{
if (flowid == "gkbx" || flowid == "tfsq" || (strisdz == "1" && (flowid == "ybbx" || flowid == "yksq_dz")))
{
sb.Append(@"select (select top 1 billcode from bill_main where billname=main.billName) as billcode, sum(billJe) as billJe
,convert(varchar(10),billdate,121) as tbilldate,billname as tbillName,flowid,stepid,billuser,billdate,isgk,(billdept) as gkdept
,(select bxzy from bill_ybbxmxb where bill_ybbxmxb.billCode=(select top 1 billcode from bill_main where billname=main.billName)) as bxzy
,(select xmmc from bill_ysgc where gcbh=billName) as billName,(select top 1 billdept from bill_main where billname=main.billname) as billdept
,Row_number()over(order by billdate,billName desc) as crow from bill_main main where flowid='" + flowid + "' and billname in(");
}
else
{
sb.Append(@"select convert(varchar(10),billdate,121) as tbilldate,*,(case flowID when 'ys' then (select '['+gcbh+']'+xmmc from bill_ysgc
where gcbh=bill_main.billName) else billName end) as tbillName,Row_number()over(order by billdate,billName desc) as crow
from bill_main where flowid='" + flowid + "' and billcode in(");
}
foreach (string billcode in list)
{
sb.Append("'");
sb.Append(billcode);
sb.Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
}
if (flowid == "gkbx" || flowid == "tfsq" || (strisdz == "1" && (flowid == "ybbx" || flowid == "yksq_dz")))
{
sb.Append(" group by billname,flowid,stepid,billuser,billdate,isgk,billdept ");
}
string strsqlcount = "select count(*) from ( {0} ) t ";
//string strsqlframe = "select * from ( {0} ) t where t.crow>{1} and t.crow<={2} order by billName desc";
strsqlcount = string.Format(strsqlcount, sb.ToString());
//strsqlframe = string.Format(strsqlframe, sb.ToString(), pagefrm, pageto);
return(int.Parse(server.GetCellValue(strsqlcount)));
// return server.GetDataTable(strsqlframe, null);
}