using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Web; using System.Web.Mvc; using DSWeb.MvcShipping.Helper; using DSWeb.MvcShipping.Comm.Cookie; using DSWeb.Areas.CommMng.DAL; using DSWeb.Areas.RptMng.Comm; using HcUtility.Comm; using HcUtility.Core; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.EntityDA; using DSWeb.Areas.CommMng.Models; using DSWeb.MvcShipping.Models.SysZlSet; using System.IO; using DSWeb.MvcShipping.DAL.MsSysParamSet; namespace DSWeb.MvcShipping.Controllers { ///

/// 欠费账龄分析查询 ///

[JsonRequestBehavior] public class MsRptNoTotalZlController : Controller { // // GET: public ActionResult Index() { return View(); } // // GET：/RptMng/MsRptPcHeadQry/QryData public ActionResult ZlSet() { return View(); } public ContentResult SaleListData(int start, int limit, string condition, string sort, string printstr, string sortstr, string strdate) { if (Convert.ToString(Session["COMPANYID"]).ToString().Trim() == "" || Convert.ToString(Session["USERID"]).ToString().Trim() == "" || Convert.ToString(Session["CODENAME"]).ToString().Trim() == "" || Convert.ToString(Session["SHOWNAME"]).ToString().Trim() == "" || Convert.ToString(Session["DEPTNAME"]).ToString().Trim() == "") { var jsonRespose2 = new JsonResponse { Success = false, Message = "登录超时，请退出系统重新登录！" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose2) }; } var strDa = GetRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); if (!string.IsNullOrEmpty(strDa)) { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and " + strDa; } else { condition = strDa; } } var NORPTFEESTATUS = MsSysParamSetDAL.GetData("PARAMNAME='NORPTFEESTATUS'"); if (NORPTFEESTATUS.PARAMVALUE == "1") { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and F.FEESTATUS<>1 "; } else { condition = " F.FEESTATUS<>1 "; } } var zlset = GetZlSet(Convert.ToString(Session["COMPANYID"])); if (zlset.ZL1NAME == "" || zlset.ZL1NAME == String.Empty || zlset.ZL1DAY == 0) { var jsonRespose = new JsonResponse { Success = false, Message = "账龄参数未正确设置,不能统计数据", Data = "" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var otherday = zlset.ZL1DAY; var strSql = new StringBuilder(); strSql.Append("SELECT B.SALE"); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) end) as ZL1TTL "); if (zlset.ZL2NAME != "" && zlset.ZL2NAME != String.Empty && zlset.ZL2DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL2TTL "); otherday = zlset.ZL2DAY; if (zlset.ZL3NAME != "" && zlset.ZL3NAME != String.Empty && zlset.ZL3DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE ,0),2) as numeric(20,2)) END) as ZL3TTL "); otherday = zlset.ZL3DAY; if (zlset.ZL4NAME != "" && zlset.ZL4NAME != String.Empty && zlset.ZL4DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL4TTL "); otherday = zlset.ZL4DAY; if (zlset.ZL5NAME != "" && zlset.ZL5NAME != String.Empty && zlset.ZL5DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL5TTL "); otherday = zlset.ZL5DAY; if (zlset.ZL6NAME != "" && zlset.ZL6NAME != String.Empty && zlset.ZL6DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL6TTL "); otherday = zlset.ZL6DAY; if (zlset.ZL7NAME != "" && zlset.ZL7NAME != String.Empty && zlset.ZL7DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL7TTL "); otherday = zlset.ZL7DAY; } } } } } } if (zlset.ZL8NAME != "" && zlset.ZL8NAME != String.Empty ) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL8TTL "); } strSql.Append(",sum(case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLUSD "); strSql.Append(",sum(case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLRMB "); strSql.Append(",sum(cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2))) as ZLTTLTTL "); strSql.Append(" FROM CH_FEE F "); strSql.Append("INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" Where " + condition); } strSql.Append(" Group by B.SALE "); if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true")) { if (!string.IsNullOrEmpty(sortstr)) { strSql.Append(" order by " + sortstr); } else { strSql.Append(" order by B.SALE"); } var jsonRespose = new JsonResponse { Success = true, Message = "完成", Data = strSql.ToString() }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by B.SALE"); } var dbparams = new List(); var paramps_sSQL = new CustomDbParamter(); paramps_sSQL.ParameterName = "@sSQL"; paramps_sSQL.DbType = DbType.String; paramps_sSQL.Direction = ParameterDirection.Input; paramps_sSQL.Value = strSql.ToString(); dbparams.Add(paramps_sSQL); var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set"); var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true); return new ContentResult() { Content = json }; } } } public ContentResult CustListData(int start, int limit, string condition, string sort, string printstr,string sortstr,string strdate) { var strDa = GetRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); if (!string.IsNullOrEmpty(strDa)) { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and " + strDa; } else { condition = strDa; } } var NORPTFEESTATUS = MsSysParamSetDAL.GetData("PARAMNAME='NORPTFEESTATUS'"); if (NORPTFEESTATUS.PARAMVALUE == "1") { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and F.FEESTATUS<>1 "; } else { condition = " F.FEESTATUS<>1 "; } } var zlset = GetZlSet(Convert.ToString(Session["COMPANYID"])); if (zlset.ZL1NAME == "" || zlset.ZL1NAME == String.Empty || zlset.ZL1DAY == 0) { var jsonRespose = new JsonResponse { Success = false, Message = "账龄参数未正确设置,不能统计数据", Data ="" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var otherday = zlset.ZL1DAY; var strSql = new StringBuilder(); strSql.Append("SELECT F.CUSTOMERNAME AS CUSTNAME "); strSql.Append(",(CASE WHEN ISNULL(A.ACCTYPE,'')='' THEN '票结' ELSE CASE WHEN A.ACCTYPE='月结' THEN '月结间隔'+A.ACCMONTH+'月账期日'+ACCDAY ELSE A.ACCTYPE+' 账期日'+A.ACCDAY END END) ZLSTR "); strSql.Append(",(CASE ISNULL(A.ACCTYPE,'') WHEN '月结' then A.accmonth*30 + A.accday WHEN '季结' then A.accmonth*90 + A.accday else A.accday end) CONTRACTDAY "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) end) as ZL1TTL "); if (zlset.ZL2NAME != "" && zlset.ZL2NAME != String.Empty && zlset.ZL2DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL2TTL "); otherday = zlset.ZL2DAY; if (zlset.ZL3NAME != "" && zlset.ZL3NAME != String.Empty && zlset.ZL3DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE ,0),2) as numeric(20,2)) END) as ZL3TTL "); otherday = zlset.ZL3DAY; if (zlset.ZL4NAME != "" && zlset.ZL4NAME != String.Empty && zlset.ZL4DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL4TTL "); otherday = zlset.ZL4DAY; if (zlset.ZL5NAME != "" && zlset.ZL5NAME != String.Empty && zlset.ZL5DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL5TTL "); otherday = zlset.ZL5DAY; if (zlset.ZL6NAME != "" && zlset.ZL6NAME != String.Empty && zlset.ZL6DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL6TTL "); otherday = zlset.ZL6DAY; if (zlset.ZL7NAME != "" && zlset.ZL7NAME != String.Empty && zlset.ZL7DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL7TTL "); otherday = zlset.ZL7DAY; } } } } } } if (zlset.ZL8NAME != "" && zlset.ZL8NAME != String.Empty) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL8TTL "); } strSql.Append(",sum(case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLUSD "); strSql.Append(",sum(case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLRMB "); strSql.Append(",sum(cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2))) as ZLTTLTTL "); strSql.Append(" FROM CH_FEE F "); strSql.Append("INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) "); strSql.Append("LEFT JOIN (SELECT I.SHORTNAME,cast(MAX(ACCTYPE) as varchar) ACCTYPE,cast(MAX(ACCMONTH)as varchar) ACCMONTH,cast(MAX(ACCDAYS) as varchar) ACCDAY FROM Info_Client_ACCDATE A LEFT JOIN INFO_CLIENT I ON (I.GID=A.LINKGID) WHERE ISNULL(I.SHORTNAME, '') != '' GROUP BY I.SHORTNAME) A ON (A.SHORTNAME=F.CUSTOMERNAME) "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" Where " + condition); } strSql.Append(" Group by F.CUSTOMERNAME "); strSql.Append(",A.ACCTYPE,A.ACCMONTH,A.ACCDAY "); if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true")) { if (!string.IsNullOrEmpty(sortstr)) { strSql.Append(" order by " + sortstr); } else { strSql.Append(" order by F.CUSTOMERNAME"); } var jsonRespose = new JsonResponse { Success = true, Message = "完成", Data = strSql.ToString() }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by F.CUSTOMERNAME"); } var dbparams = new List(); var paramps_sSQL = new CustomDbParamter(); paramps_sSQL.ParameterName = "@sSQL"; paramps_sSQL.DbType = DbType.String; paramps_sSQL.Direction = ParameterDirection.Input; paramps_sSQL.Value = strSql.ToString(); dbparams.Add(paramps_sSQL); var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set"); var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true); return new ContentResult() { Content = json }; } } } public ContentResult SaleCustListData(int start, int limit, string condition, string sort, string printstr,string sortstr,string strdate) { var strDa = GetRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); if (!string.IsNullOrEmpty(strDa)) { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and " + strDa; } else { condition = strDa; } } var NORPTFEESTATUS = MsSysParamSetDAL.GetData("PARAMNAME='NORPTFEESTATUS'"); if (NORPTFEESTATUS.PARAMVALUE == "1") { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and F.FEESTATUS<>1 "; } else { condition = " F.FEESTATUS<>1 "; } } var zlset = GetZlSet(Convert.ToString(Session["COMPANYID"])); if (zlset.ZL1NAME == "" || zlset.ZL1NAME == String.Empty || zlset.ZL1DAY == 0) { var jsonRespose = new JsonResponse { Success = false, Message = "账龄参数未正确设置,不能统计数据", Data = "" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var otherday = zlset.ZL1DAY; var strSql = new StringBuilder(); strSql.Append("SELECT B.SALE,F.CUSTOMERNAME AS CUSTNAME "); strSql.Append(",(CASE ISNULL(A.ACCTYPE,'') WHEN '月结' then A.accmonth*30 + A.accday WHEN '季结' then A.accmonth*90 + A.accday else A.accday end) CONTRACTDAY "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) end) as ZL1TTL "); if (zlset.ZL2NAME != "" && zlset.ZL2NAME != String.Empty && zlset.ZL2DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL2TTL "); otherday = zlset.ZL2DAY; if (zlset.ZL3NAME != "" && zlset.ZL3NAME != String.Empty && zlset.ZL3DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL3TTL "); otherday = zlset.ZL3DAY; if (zlset.ZL4NAME != "" && zlset.ZL4NAME != String.Empty && zlset.ZL4DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL4TTL "); otherday = zlset.ZL4DAY; if (zlset.ZL5NAME != "" && zlset.ZL5NAME != String.Empty && zlset.ZL5DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL5TTL "); otherday = zlset.ZL5DAY; if (zlset.ZL6NAME != "" && zlset.ZL6NAME != String.Empty && zlset.ZL6DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE ,0),2) as numeric(20,2)) END) as ZL6TTL "); otherday = zlset.ZL6DAY; if (zlset.ZL7NAME != "" && zlset.ZL7NAME != String.Empty && zlset.ZL7DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL7TTL "); otherday = zlset.ZL7DAY; } } } } } } if (zlset.ZL8NAME != "" && zlset.ZL8NAME != String.Empty) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL8TTL "); } strSql.Append(",sum(case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLUSD "); strSql.Append(",sum(case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLRMB "); strSql.Append(",sum(cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2))) as ZLTTLTTL "); strSql.Append(" FROM CH_FEE F "); strSql.Append("INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) "); strSql.Append("LEFT JOIN (SELECT I.SHORTNAME,cast(MAX(ACCTYPE) as varchar) ACCTYPE,cast(MAX(ACCMONTH)as varchar) ACCMONTH,cast(MAX(ACCDAYS) as varchar) ACCDAY FROM Info_Client_ACCDATE A LEFT JOIN INFO_CLIENT I ON (I.GID=A.LINKGID) WHERE ISNULL(I.SHORTNAME, '') != '' GROUP BY I.SHORTNAME) A ON (A.SHORTNAME=F.CUSTOMERNAME) "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" Where " + condition); } strSql.Append(" Group by B.SALE,F.CUSTOMERNAME "); strSql.Append(",A.ACCTYPE,A.ACCMONTH,A.ACCDAY "); if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true")) { if (!string.IsNullOrEmpty(sortstr)) { strSql.Append(" order by " + sortstr); } else { strSql.Append(" order by B.SALE,F.CUSTOMERNAME"); } var jsonRespose = new JsonResponse { Success = true, Message = "完成", Data = strSql.ToString() }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var sortstring = DatasetSort.Getsortstring(sort); if (!string.IsNullOrEmpty(sortstring)) { strSql.Append(" order by " + sortstring); } else { strSql.Append(" order by B.SALE,F.CUSTOMERNAME"); } var dbparams = new List(); var paramps_sSQL = new CustomDbParamter(); paramps_sSQL.ParameterName = "@sSQL"; paramps_sSQL.DbType = DbType.String; paramps_sSQL.Direction = ParameterDirection.Input; paramps_sSQL.Value = strSql.ToString(); dbparams.Add(paramps_sSQL); var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set"); var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true); return new ContentResult() { Content = json }; } } } public string GetPopSaleDetailInfo(string feeType, string param, string saler) { var strSql = new StringBuilder(); /*strSql.AppendLine("SELECT (SELECT OPDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS OPDATE, "); strSql.AppendLine("(SELECT CUSTOMERNAME AS CUSTNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTNAME, "); strSql.AppendLine("(SELECT SALE FROM v_op_bill WHERE BSNO=F.BSNO) AS SALE, "); strSql.AppendLine("(SELECT BSSOURCE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSSOURCE, "); strSql.AppendLine("(SELECT OPLBNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS OPLBNAME, "); strSql.AppendLine("(SELECT BSTYPE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSTYPE, "); strSql.AppendLine("(SELECT PORTDISCHARGE FROM v_op_bill WHERE BSNO=F.BSNO) AS PORTDISCHARGE, "); strSql.AppendLine("(SELECT CUSTOMDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTOMDATE, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("(SELECT MBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS MBLNO, "); strSql.AppendLine("(SELECT HBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS HBLNO, "); strSql.AppendLine("(SELECT TEU FROM v_op_bill WHERE BSNO=F.BSNO) AS TEU, "); strSql.AppendLine("(SELECT KGS FROM v_op_bill WHERE BSNO=F.BSNO) AS KGS, "); strSql.AppendLine("(SELECT GOODSNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS GOODSNAME, "); strSql.AppendLine("(SELECT OP FROM v_op_bill WHERE BSNO=F.BSNO) AS OP, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE SALE='" + saler + "')"); strSql.AppendLine("ORDER BY ENTERDATE ASC ");*/ /*strSql.AppendLine("where " + condition); strSql.AppendLine("order by B.SALE");*/ strSql.AppendLine("SELECT CONVERT(varchar(100), B.OPDATE, 23) AS OPDATE,B.CUSTOMERNAME AS CUSTNAME,B.SALE,B.BSSOURCE,B.OPLBNAME,B.BSTYPE,B.PORTDISCHARGE,CONVERT(varchar(100), B.CUSTOMDATE, 23) AS CUSTOMDATE,B.MBLNO,B.HBLNO,B.TEU,B.KGS,B.GOODSNAME,B.OP, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("left join v_op_bill as B on F.BSNO=B.BSNO "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); //strSql.AppendLine("AND B.CUSTOMERNAME='" + customer + "' "); strSql.AppendLine("AND B.SALE='" + saler + "' "); strSql.AppendLine("ORDER BY ENTERDATE ASC "); Database db = DatabaseFactory.CreateDatabase(); string evList = string.Empty; evList = "["; using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "SALE:\"" + Convert.ToString(reader["SALE"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; return evList; } public string GetPopCustDetailInfo(string feeType, string param, string customer) { var strSql = new StringBuilder(); /*strSql.AppendLine("SELECT (SELECT OPDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS OPDATE, "); strSql.AppendLine("(SELECT CUSTOMERNAME AS CUSTNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTNAME, "); strSql.AppendLine("(SELECT SALE FROM v_op_bill WHERE BSNO=F.BSNO) AS SALE, "); strSql.AppendLine("(SELECT BSSOURCE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSSOURCE, "); strSql.AppendLine("(SELECT OPLBNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS OPLBNAME, "); strSql.AppendLine("(SELECT BSTYPE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSTYPE, "); strSql.AppendLine("(SELECT PORTDISCHARGE FROM v_op_bill WHERE BSNO=F.BSNO) AS PORTDISCHARGE, "); strSql.AppendLine("(SELECT CUSTOMDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTOMDATE, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("(SELECT MBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS MBLNO, "); strSql.AppendLine("(SELECT HBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS HBLNO, "); strSql.AppendLine("(SELECT TEU FROM v_op_bill WHERE BSNO=F.BSNO) AS TEU, "); strSql.AppendLine("(SELECT KGS FROM v_op_bill WHERE BSNO=F.BSNO) AS KGS, "); strSql.AppendLine("(SELECT GOODSNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS GOODSNAME, "); strSql.AppendLine("(SELECT OP FROM v_op_bill WHERE BSNO=F.BSNO) AS OP, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine("ORDER BY ENTERDATE ASC ");*/ /*strSql.AppendLine("where " + condition); strSql.AppendLine("order by B.SALE");*/ strSql.AppendLine("SELECT B.OPDATE,B.CUSTOMERNAME AS CUSTNAME,B.SALE,B.BSSOURCE,B.OPLBNAME,B.BSTYPE,B.PORTDISCHARGE,B.CUSTOMDATE,B.MBLNO,B.HBLNO,B.TEU,B.KGS,B.GOODSNAME,B.OP, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("left join v_op_bill as B on F.BSNO=B.BSNO "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND B.CUSTOMERNAME='" + customer + "' "); //strSql.AppendLine("AND B.SALE='王慧' "); strSql.AppendLine("ORDER BY ENTERDATE ASC "); Database db = DatabaseFactory.CreateDatabase(); string evList = string.Empty; evList = "["; using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; return evList; } public string GetPopSaleCustDetailInfo(string feeType, string param, string sale, string customer) { var strSql = new StringBuilder(); /*strSql.AppendLine("SELECT (SELECT OPDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS OPDATE, "); strSql.AppendLine("(SELECT CUSTOMERNAME AS CUSTNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTNAME, "); strSql.AppendLine("(SELECT SALE FROM v_op_bill WHERE BSNO=F.BSNO) AS SALE, "); strSql.AppendLine("(SELECT BSSOURCE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSSOURCE, "); strSql.AppendLine("(SELECT OPLBNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS OPLBNAME, "); strSql.AppendLine("(SELECT BSTYPE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSTYPE, "); strSql.AppendLine("(SELECT PORTDISCHARGE FROM v_op_bill WHERE BSNO=F.BSNO) AS PORTDISCHARGE, "); strSql.AppendLine("(SELECT CUSTOMDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTOMDATE, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("(SELECT MBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS MBLNO, "); strSql.AppendLine("(SELECT HBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS HBLNO, "); strSql.AppendLine("(SELECT TEU FROM v_op_bill WHERE BSNO=F.BSNO) AS TEU, "); strSql.AppendLine("(SELECT KGS FROM v_op_bill WHERE BSNO=F.BSNO) AS KGS, "); strSql.AppendLine("(SELECT GOODSNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS GOODSNAME, "); strSql.AppendLine("(SELECT OP FROM v_op_bill WHERE BSNO=F.BSNO) AS OP, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine("ORDER BY ENTERDATE ASC ");*/ /*strSql.AppendLine("where " + condition); strSql.AppendLine("order by B.SALE");*/ strSql.AppendLine("SELECT B.OPDATE,B.CUSTOMERNAME AS CUSTNAME,B.SALE,B.BSSOURCE,B.OPLBNAME,B.BSTYPE,B.PORTDISCHARGE,B.CUSTOMDATE,B.MBLNO,B.HBLNO,B.TEU,B.KGS,B.GOODSNAME,B.OP, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("left join v_op_bill as B on F.BSNO=B.BSNO "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND B.CUSTOMERNAME='" + customer + "' "); strSql.AppendLine("AND B.SALE='" + sale + "' "); strSql.AppendLine("ORDER BY ENTERDATE ASC "); Database db = DatabaseFactory.CreateDatabase(); string evList = string.Empty; evList = "["; using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "SALE:\"" + Convert.ToString(reader["SALE"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; return evList; } /* Database db = DatabaseFactory.CreateDatabase(); string evList = string.Empty; evList = "["; using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "SALE:\"" + Convert.ToString(reader["SALE"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "RMBDRAMOUNT:\"" + Convert.ToString(reader["RMBDRAMOUNT"]) + "\","; evList += "STLRMBDRAMOUNT:\"" + Convert.ToString(reader["STLRMBDRAMOUNT"]) + "\","; evList += "BALRMBDRAMOUNT:\"" + Convert.ToString(reader["BALRMBDRAMOUNT"]) + "\","; evList += "USDDRAMOUNT:\"" + Convert.ToString(reader["USDDRAMOUNT"]) + "\","; evList += "STLUSDDRAMOUNT:\"" + Convert.ToString(reader["STLUSDDRAMOUNT"]) + "\","; evList += "BALUSDDRAMOUNT:\"" + Convert.ToString(reader["BALUSDDRAMOUNT"]) + "\","; evList += "TTLDRAMOUNT:\"" + Convert.ToString(reader["TTLDRAMOUNT"]) + "\","; evList += "STLTTLDRAMOUNT:\"" + Convert.ToString(reader["STLTTLDRAMOUNT"]) + "\","; evList += "BALTTLDRAMOUNT:\"" + Convert.ToString(reader["BALTTLDRAMOUNT"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; return evList; }*/ public string GetPopCustDetailInfo33(string feeType, string param, string customer) { var strSql = new StringBuilder(); /*strSql.AppendLine("SELECT (SELECT OPDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS OPDATE, "); strSql.AppendLine("(SELECT CUSTOMERNAME AS CUSTNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTNAME, "); strSql.AppendLine("(SELECT SALE FROM v_op_bill WHERE BSNO=F.BSNO) AS SALE, "); strSql.AppendLine("(SELECT BSSOURCE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSSOURCE, "); strSql.AppendLine("(SELECT OPLBNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS OPLBNAME, "); strSql.AppendLine("(SELECT BSTYPE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSTYPE, "); strSql.AppendLine("(SELECT PORTDISCHARGE FROM v_op_bill WHERE BSNO=F.BSNO) AS PORTDISCHARGE, "); strSql.AppendLine("(SELECT CUSTOMDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTOMDATE, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("(SELECT MBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS MBLNO, "); strSql.AppendLine("(SELECT HBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS HBLNO, "); strSql.AppendLine("(SELECT TEU FROM v_op_bill WHERE BSNO=F.BSNO) AS TEU, "); strSql.AppendLine("(SELECT KGS FROM v_op_bill WHERE BSNO=F.BSNO) AS KGS, "); strSql.AppendLine("(SELECT GOODSNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS GOODSNAME, "); strSql.AppendLine("(SELECT OP FROM v_op_bill WHERE BSNO=F.BSNO) AS OP, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine("ORDER BY ENTERDATE ASC ");*/ /*strSql.AppendLine("where " + condition); strSql.AppendLine("order by B.SALE");*/ strSql.AppendLine("SELECT CONVERT(varchar(100), B.OPDATE, 23) AS OPDATE,B.CUSTOMERNAME AS CUSTNAME,B.SALE,B.BSSOURCE,B.OPLBNAME,B.BSTYPE,B.PORTDISCHARGE,CONVERT(varchar(100), B.CUSTOMDATE, 23) AS CUSTOMDATE,B.MBLNO,B.HBLNO,B.TEU,B.KGS,B.GOODSNAME,B.OP, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("left join v_op_bill as B on F.BSNO=B.BSNO "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND B.CUSTOMERNAME='" + customer + "' "); //strSql.AppendLine("AND B.SALE='王慧' "); strSql.AppendLine("ORDER BY ENTERDATE ASC "); Database db = DatabaseFactory.CreateDatabase(); string evList = string.Empty; evList = "["; /* using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "RMBDRAMOUNT:\"" + Convert.ToString(reader["RMBDRAMOUNT"]) + "\","; evList += "STLRMBDRAMOUNT:\"" + Convert.ToString(reader["STLRMBDRAMOUNT"]) + "\","; evList += "BALRMBDRAMOUNT:\"" + Convert.ToString(reader["BALRMBDRAMOUNT"]) + "\","; evList += "USDDRAMOUNT:\"" + Convert.ToString(reader["USDDRAMOUNT"]) + "\","; evList += "STLUSDDRAMOUNT:\"" + Convert.ToString(reader["STLUSDDRAMOUNT"]) + "\","; evList += "BALUSDDRAMOUNT:\"" + Convert.ToString(reader["BALUSDDRAMOUNT"]) + "\","; evList += "TTLDRAMOUNT:\"" + Convert.ToString(reader["TTLDRAMOUNT"]) + "\","; evList += "STLTTLDRAMOUNT:\"" + Convert.ToString(reader["STLTTLDRAMOUNT"]) + "\","; evList += "BALTTLDRAMOUNT:\"" + Convert.ToString(reader["BALTTLDRAMOUNT"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; */ return evList; } public string GetPopSaleCustDetailInfo22(string feeType, string param, string sale, string customer) { // var strSql = new StringBuilder(); /*strSql.AppendLine("SELECT (SELECT OPDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS OPDATE, "); strSql.AppendLine("(SELECT CUSTOMERNAME AS CUSTNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTNAME, "); strSql.AppendLine("(SELECT SALE FROM v_op_bill WHERE BSNO=F.BSNO) AS SALE, "); strSql.AppendLine("(SELECT BSSOURCE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSSOURCE, "); strSql.AppendLine("(SELECT OPLBNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS OPLBNAME, "); strSql.AppendLine("(SELECT BSTYPE FROM v_op_bill WHERE BSNO=F.BSNO) AS BSTYPE, "); strSql.AppendLine("(SELECT PORTDISCHARGE FROM v_op_bill WHERE BSNO=F.BSNO) AS PORTDISCHARGE, "); strSql.AppendLine("(SELECT CUSTOMDATE FROM v_op_bill WHERE BSNO=F.BSNO) AS CUSTOMDATE, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("(SELECT MBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS MBLNO, "); strSql.AppendLine("(SELECT HBLNO FROM v_op_bill WHERE BSNO=F.BSNO) AS HBLNO, "); strSql.AppendLine("(SELECT TEU FROM v_op_bill WHERE BSNO=F.BSNO) AS TEU, "); strSql.AppendLine("(SELECT KGS FROM v_op_bill WHERE BSNO=F.BSNO) AS KGS, "); strSql.AppendLine("(SELECT GOODSNAME FROM v_op_bill WHERE BSNO=F.BSNO) AS GOODSNAME, "); strSql.AppendLine("(SELECT OP FROM v_op_bill WHERE BSNO=F.BSNO) AS OP, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT " + param + "DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO IN (SELECT BSNO FROM v_op_bill WHERE CUSTOMERNAME='" + customer + "')"); strSql.AppendLine("ORDER BY ENTERDATE ASC ");*/ /*strSql.AppendLine("where " + condition); strSql.AppendLine("order by B.SALE");*/ /* strSql.AppendLine("SELECT CONVERT(varchar(100), B.OPDATE, 23) AS OPDATE,B.CUSTOMERNAME AS CUSTNAME,B.SALE,B.BSSOURCE,B.OPLBNAME,B.BSTYPE,B.PORTDISCHARGE,CONVERT(varchar(100), B.CUSTOMDATE, 23) AS CUSTOMDATE,B.MBLNO,B.HBLNO,B.TEU,B.KGS,B.GOODSNAME,B.OP, "); strSql.AppendLine("(SELECT case when isnull(C1.AMOUNT,0)<>isnull(C1.SETTLEMENT,0) then (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, GETDATE()) end) "); strSql.AppendLine("else (case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else DATEDIFF([day],ENTERDATE, S.SETTLETIME) end) end FROM ch_fee AS C1 LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C1.CUSTOMERNAME AND S.BSNO=C1.BSNO) WHERE C1.GID=F.GID) AS ACTUALDAY, "); strSql.AppendLine("(SELECT case ENTERDATE when '1900-01-01 00:00:00.000' then 0 else case when isnull(C2.AMOUNT,0)<>isnull(C2.SETTLEMENT,0) then DATEDIFF([day],ENTERDATE,(CASE C.STLNAME WHEN '月结' THEN "); strSql.AppendLine("DATEADD(month,1,DATEADD(day,ISNULL(CAST(C.STLDATE AS INT),1),DATEADD(day,-Day(ENTERDATE)+1,ENTERDATE))) "); strSql.AppendLine("WHEN '半月结' THEN (CASE WHEN DATEPART(DAY, GETDATE())<=15 THEN DATEADD(day,ISNULL(CAST(C.STLFIRSTHALFDATE AS INT),16),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE)) "); strSql.AppendLine("ELSE DATEADD(MONTH,1,DATEADD(day,ISNULL(CAST(C.STLMIDDLEDATE AS INT),1),DATEADD(Day,-Day(ENTERDATE)+1,ENTERDATE))) END) "); strSql.AppendLine("ELSE GETDATE() END)) else (DATEDIFF([day],ENTERDATE, S.SETTLETIME)) end end FROM ch_fee AS C2 LEFT JOIN info_client C ON (C.SHORTNAME=C2.CUSTOMERNAME) "); strSql.AppendLine("LEFT JOIN V_MAX_SETTLEMENTTIME S ON (S.CUSTOMERNAME=C2.CUSTOMERNAME AND S.BSNO=C2.BSNO) WHERE C2.GID=F.GID) AS NODAY, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS RMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS RMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END,0) AS BALRMBDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'RMB' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END) ELSE 0 END),0) AS BALRMBDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALRMBDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END,0) AS USDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT,0) ELSE 0 END)ELSE 0 END),0) AS USDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS USDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS STLUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS STLUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END,0) AS BALUSDDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN ISNULL(AMOUNT-SETTLEMENT,0) ELSE 0 END)ELSE 0 END),0) AS BALUSDDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALUSDDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END,0) AS OTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT,0) END) END)ELSE 0 END),0) AS OTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS OTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END,0) AS STLOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(SETTLEMENT,0) END) END)ELSE 0 END),0) AS STLOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END,0) AS BALOTDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN (CASE CURRENCY WHEN 'USD' THEN 0 ELSE (CASE CURRENCY WHEN 'RMB' THEN 0 ELSE ISNULL(AMOUNT-SETTLEMENT,0) END) END) ELSE 0 END),0) AS BALOTDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALOTDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS TTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS TTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS TTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS STLTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS STLTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS STLTTLDRAMOUNT, "); strSql.AppendLine("isnull(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END,0) AS BALTTLDR, "); strSql.AppendLine("(SELECT isnull(SUM(CASE FEETYPE WHEN 1 THEN ISNULL(AMOUNT-SETTLEMENT,0) * ISNULL(EXCHANGERATE,0) ELSE 0 END),0) AS BALTTLDR "); strSql.AppendLine("FROM CH_FEE F where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND F.BSNO = B.BSNO "); strSql.AppendLine(") AS BALTTLDRAMOUNT "); strSql.AppendLine("FROM CH_FEE F "); strSql.AppendLine("left join v_op_bill as B on F.BSNO=B.BSNO "); strSql.AppendLine("where datediff(day,ENTERDATE,getdate())<=(SELECT TOP 1 ZL1DAY FROM sys_zlset WHERE COMPANYID='" + Convert.ToString(Session["COMPANYID"]) + "') "); strSql.AppendLine("AND F.FEETYPE='" + feeType + "' and F.AMOUNT<>F.SETTLEMENT and (F.FEESTATUS=0 or F.FEESTATUS=8 or F.FEESTATUS=9) "); strSql.AppendLine("AND B.CUSTOMERNAME='" + customer + "' "); strSql.AppendLine("AND B.SALE='" + sale + "' "); strSql.AppendLine("ORDER BY ENTERDATE ASC "); Database db = DatabaseFactory.CreateDatabase(); */ string evList = string.Empty; /* evList = "["; using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { var i = 1; while (reader.Read()) { evList += "{"; //evList += "MONTHNAME:\"" + Convert.ToString(reader["MONTHNAME"]) + "\",MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\",CUSTOMERNAME:\"" + Convert.ToString(reader["CUSTOMERNAME"]) + "\",CNTRTOTAL:\"" + Convert.ToString(reader["CNTRTOTAL"]) + "\",PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\",OP:\"" + Convert.ToString(reader["OP"]) + "\",SALE:\"" + Convert.ToString(reader["SALE"]) + "\",BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\",BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\",LANE:\"" + Convert.ToString(reader["LANE"]) + "\",TEU:\"" + Convert.ToString(reader["TEU"]) + "\",INPUTBY:\"" + Convert.ToString(reader["INPUTBY"]) + "\",TEUAMOUNT:\"" + Convert.ToString(reader["TEUAMOUNT"]) + "\""; evList += "SALE:\"" + Convert.ToString(reader["SALE"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "BSSOURCE:\"" + Convert.ToString(reader["BSSOURCE"]) + "\","; evList += "OPLBNAME:\"" + Convert.ToString(reader["OPLBNAME"]) + "\","; evList += "BSTYPE:\"" + Convert.ToString(reader["BSTYPE"]) + "\","; evList += "PORTDISCHARGE:\"" + Convert.ToString(reader["PORTDISCHARGE"]) + "\","; evList += "OPDATE:\"" + Convert.ToString(reader["OPDATE"]) + "\","; evList += "CUSTOMDATE:\"" + Convert.ToString(reader["CUSTOMDATE"]) + "\","; evList += "ACTUALDAY:\"" + Convert.ToString(reader["ACTUALDAY"]) + "\","; evList += "NODAY:\"" + Convert.ToString(reader["NODAY"]) + "\","; evList += "CUSTNAME:\"" + Convert.ToString(reader["CUSTNAME"]) + "\","; evList += "MBLNO:\"" + Convert.ToString(reader["MBLNO"]) + "\","; evList += "HBLNO:\"" + Convert.ToString(reader["HBLNO"]) + "\","; evList += "TEU:\"" + Convert.ToString(reader["TEU"]) + "\","; evList += "KGS:\"" + Convert.ToString(reader["KGS"]) + "\","; evList += "GOODSNAME:\"" + Convert.ToString(reader["GOODSNAME"]) + "\","; evList += "RMBDR:\"" + Convert.ToString(reader["RMBDR"]) + "\","; evList += "STLRMBDR:\"" + Convert.ToString(reader["STLRMBDR"]) + "\","; evList += "BALRMBDR:\"" + Convert.ToString(reader["BALRMBDR"]) + "\","; evList += "USDDR:\"" + Convert.ToString(reader["USDDR"]) + "\","; evList += "STLUSDDR:\"" + Convert.ToString(reader["STLUSDDR"]) + "\","; evList += "BALUSDDR:\"" + Convert.ToString(reader["BALUSDDR"]) + "\","; evList += "OTDR:\"" + Convert.ToString(reader["OTDR"]) + "\","; evList += "STLOTDR:\"" + Convert.ToString(reader["STLOTDR"]) + "\","; evList += "BALOTDR:\"" + Convert.ToString(reader["BALOTDR"]) + "\","; evList += "TTLDR:\"" + Convert.ToString(reader["TTLDR"]) + "\","; evList += "STLTTLDR:\"" + Convert.ToString(reader["STLTTLDR"]) + "\","; evList += "BALTTLDR:\"" + Convert.ToString(reader["BALTTLDR"]) + "\","; evList += "RMBDRAMOUNT:\"" + Convert.ToString(reader["RMBDRAMOUNT"]) + "\","; evList += "STLRMBDRAMOUNT:\"" + Convert.ToString(reader["STLRMBDRAMOUNT"]) + "\","; evList += "BALRMBDRAMOUNT:\"" + Convert.ToString(reader["BALRMBDRAMOUNT"]) + "\","; evList += "USDDRAMOUNT:\"" + Convert.ToString(reader["USDDRAMOUNT"]) + "\","; evList += "STLUSDDRAMOUNT:\"" + Convert.ToString(reader["STLUSDDRAMOUNT"]) + "\","; evList += "BALUSDDRAMOUNT:\"" + Convert.ToString(reader["BALUSDDRAMOUNT"]) + "\","; evList += "TTLDRAMOUNT:\"" + Convert.ToString(reader["TTLDRAMOUNT"]) + "\","; evList += "STLTTLDRAMOUNT:\"" + Convert.ToString(reader["STLTTLDRAMOUNT"]) + "\","; evList += "BALTTLDRAMOUNT:\"" + Convert.ToString(reader["BALTTLDRAMOUNT"]) + "\","; evList += "OP:\"" + Convert.ToString(reader["OP"]) + "\""; evList += "},"; i = i + 1; } reader.Close(); } evList = evList.Substring(0, evList.Length - 1); evList += "]"; */ return evList; } public ContentResult SumListData(int start, int limit, string condition, string sort,string strdate) { var strDa = GetRangDAStr("index", Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); if (!string.IsNullOrEmpty(strDa)) { if (!string.IsNullOrEmpty(condition)) { condition = condition + " and " + strDa; } else { condition = strDa; } } var zlset = GetZlSet(Convert.ToString(Session["COMPANYID"])); if (zlset.ZL1NAME == "" || zlset.ZL1NAME == String.Empty || zlset.ZL1DAY == 0) { var jsonRespose = new JsonResponse { Success = false, Message = "账龄参数未正确设置,不能统计数据", Data = "" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var otherday = zlset.ZL1DAY; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL1RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) end) as ZL1TTL "); if (zlset.ZL2NAME != "" && zlset.ZL2NAME != String.Empty && zlset.ZL2DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL2RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL2DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL1DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL2TTL "); otherday = zlset.ZL2DAY; if (zlset.ZL3NAME != "" && zlset.ZL3NAME != String.Empty && zlset.ZL3DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL3RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL3DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL2DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL3TTL "); otherday = zlset.ZL3DAY; if (zlset.ZL4NAME != "" && zlset.ZL4NAME != String.Empty && zlset.ZL4DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL4RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL4DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL3DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL4TTL "); otherday = zlset.ZL4DAY; if (zlset.ZL5NAME != "" && zlset.ZL5NAME != String.Empty && zlset.ZL5DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL5RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL5DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL4DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL5TTL "); otherday = zlset.ZL5DAY; if (zlset.ZL6NAME != "" && zlset.ZL6NAME != String.Empty && zlset.ZL6DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL6RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL6DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL5DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL6TTL "); otherday = zlset.ZL6DAY; if (zlset.ZL7NAME != "" && zlset.ZL7NAME != String.Empty && zlset.ZL7DAY != 0) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL7RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())<=" + zlset.ZL7DAY + " and datediff(day,B." + strdate + ",getdate())>" + zlset.ZL6DAY + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL7TTL "); otherday = zlset.ZL7DAY; } } } } } } if (zlset.ZL8NAME != "" && zlset.ZL8NAME != String.Empty) { strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8USD "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then (case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) else 0 end) as ZL8RMB "); strSql.Append(",sum(case when datediff(day,B." + strdate + ",getdate())>" + otherday.ToString() + " then cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2)) END) as ZL8TTL "); } strSql.Append(",sum(case F.CURRENCY when 'USD' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLUSD "); strSql.Append(",sum(case F.CURRENCY when 'RMB' then (F.AMOUNT-F.SETTLEMENT) else 0 end) as ZLTTLRMB "); strSql.Append(",sum(cast(round(isnull((F.AMOUNT-F.SETTLEMENT)*F.EXCHANGERATE,0),2) as numeric(20,2))) as ZLTTLTTL "); strSql.Append(" FROM CH_FEE F "); strSql.Append("INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) "); if (!string.IsNullOrEmpty(condition)) { strSql.Append(" Where " + condition); } var dbparams = new List(); var paramps_sSQL = new CustomDbParamter(); paramps_sSQL.ParameterName = "@sSQL"; paramps_sSQL.DbType = DbType.String; paramps_sSQL.Direction = ParameterDirection.Input; paramps_sSQL.Value = strSql.ToString(); dbparams.Add(paramps_sSQL); var dbRptResult = PubSysDAL.GetMsSqlPrcDataSet("sMsExesqlQry", dbparams, "Result_Set"); var json = RptHelper.GetRptJsonResult(start, limit, dbRptResult, "Result_Set", true); return new ContentResult() { Content = json }; } } public static string GetRangDAStr(string tb, string userid, string usercode, string companyid) { string str = ""; var strSql = new StringBuilder(); strSql.Append("SELECT "); strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID "); strSql.Append(" from VW_User_Authority "); strSql.Append(" where [NAME]='modNoTotalZlRange' and USERID='" + userid + "' and ISDELETE=0"); string visiblerange = "4"; string operaterange = "4"; string AUTHORITYID = ""; Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { visiblerange = Convert.ToString(reader["VISIBLERANGE"]); operaterange = Convert.ToString(reader["OPERATERANGE"]); AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]); break; } reader.Close(); } if (visiblerange == "4") { str = "1=2"; } else if (visiblerange == "3") { str = " (B.OP='" + usercode + "' OR B.SALE='" + usercode + "')"; } else if (visiblerange == "2") { if (tb == "index") { var rangeDa = new RangeDA(); var deptname = rangeDa.GetDEPTNAME(userid); var userstr = new StringBuilder(); userstr.Append(" select SHOWNAME from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "'"; } else { str = str + " or B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "'"; }; } str =str+ ")"; reader.Close(); } } else { str = " (UPPER(B.Corpid)='" + companyid + "' or B.SALECORPID='" + companyid + "') "; } } else if (visiblerange == "5") { var userstr = new StringBuilder(); userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (B.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "' or B.SALECORPID='" + Convert.ToString(reader["COMPANYID"]) + "' "; } else { str = str + " or B.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "' or B.SALECORPID='" + Convert.ToString(reader["COMPANYID"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "6") { var userstr = new StringBuilder(); userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1"); Database userdb = DatabaseFactory.CreateDatabase(); using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString())) { str = ""; while (reader.Read()) { if (str == "") { str = " (B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "'"; } else { str = str + " or B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "'"; }; } str = str + ")"; reader.Close(); } } else if (visiblerange == "1") { str = " (UPPER(B.Corpid)='" + companyid + "' or B.SALECORPID='" + companyid + "') "; } return str; } #region 账龄设置 public ContentResult GetZlSet() { SyszlSet head = null; head = GetZlSet(Convert.ToString(Session["COMPANYID"])); var json = JsonConvert.Serialize( new { Success = true, Message = "查询成功", data = head }); return new ContentResult() { Content = json }; } static public SyszlSet GetZlSet(string companyid) { var strSql = new StringBuilder(); strSql.Append("SELECT GID,ZL1NAME,ZL1DAY,ZL2NAME,ZL2DAY,ZL3NAME,ZL3DAY,ZL4NAME,ZL4DAY,ZL5NAME,ZL5DAY,ZL6NAME,ZL6DAY,ZL7NAME,ZL7DAY,ZL8NAME,ZL8DAY from sys_zlset"); strSql.Append(" Where COMPANYID='" + companyid + "'"); Database db = DatabaseFactory.CreateDatabase(); var evList = new List(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { var evData = new SyszlSet(); evData.GID = Convert.ToString(reader["GID"]); evData.ZL1NAME = Convert.ToString(reader["ZL1NAME"]); if (reader["ZL1DAY"] != DBNull.Value) evData.ZL1DAY = Convert.ToInt16(reader["ZL1DAY"]); evData.ZL2NAME = Convert.ToString(reader["ZL2NAME"]); if (reader["ZL2DAY"] != DBNull.Value) evData.ZL2DAY = Convert.ToInt16(reader["ZL2DAY"]); evData.ZL3NAME = Convert.ToString(reader["ZL3NAME"]); if (reader["ZL3DAY"] != DBNull.Value) evData.ZL3DAY = Convert.ToInt16(reader["ZL3DAY"]); evData.ZL4NAME = Convert.ToString(reader["ZL4NAME"]); if (reader["ZL4DAY"] != DBNull.Value) evData.ZL4DAY = Convert.ToInt16(reader["ZL4DAY"]); evData.ZL5NAME = Convert.ToString(reader["ZL5NAME"]); if (reader["ZL5DAY"] != DBNull.Value) evData.ZL5DAY = Convert.ToInt16(reader["ZL5DAY"]); evData.ZL6NAME = Convert.ToString(reader["ZL6NAME"]); if (reader["ZL6DAY"] != DBNull.Value) evData.ZL6DAY = Convert.ToInt16(reader["ZL6DAY"]); evData.ZL7NAME = Convert.ToString(reader["ZL7NAME"]); if (reader["ZL7DAY"] != DBNull.Value) evData.ZL7DAY = Convert.ToInt16(reader["ZL7DAY"]); evData.ZL8NAME = Convert.ToString(reader["ZL8NAME"]); if (reader["ZL8DAY"] != DBNull.Value) evData.ZL8DAY = Convert.ToInt16(reader["ZL8DAY"]); evList.Add(evData); } reader.Close(); } if (evList.Count > 0) return evList[0]; return new SyszlSet(); } public ContentResult SaveZlSet(string data) { var headData = JsonConvert.Deserialize(data); var isPost = true; var errorstr = ""; headData.COMPANYID= Convert.ToString(Session["COMPANYID"]); var result = new DBResult(); if (isPost) { Database db = DatabaseFactory.CreateDatabase(); using (var conn = db.CreateConnection()) { conn.Open(); var tran = conn.BeginTransaction(); try { var cmdDelete = db.GetSqlStringCommand( @"delete from sys_zlset where COMPANYID=@COMPANYID "); cmdDelete.Parameters.Clear(); db.AddInParameter(cmdDelete, "@COMPANYID", DbType.String, Convert.ToString(Session["COMPANYID"])); db.ExecuteNonQuery(cmdDelete, tran); var cmdInsert = db.GetSqlStringCommand( @"insert into sys_zlset (GID,COMPANYID ,ZL1NAME,ZL1DAY,ZL2NAME,ZL2DAY,ZL3NAME,ZL3DAY,ZL4NAME,ZL4DAY,ZL5NAME,ZL5DAY,ZL6NAME,ZL6DAY,ZL7NAME,ZL7DAY,ZL8NAME,ZL8DAY) values (@GID,@COMPANYID ,@ZL1NAME,@ZL1DAY,@ZL2NAME,@ZL2DAY,@ZL3NAME,@ZL3DAY,@ZL4NAME,@ZL4DAY,@ZL5NAME,@ZL5DAY,@ZL6NAME,@ZL6DAY,@ZL7NAME,@ZL7DAY,@ZL8NAME,@ZL8DAY) "); cmdInsert.Parameters.Clear(); db.AddInParameter(cmdInsert, "@GID", DbType.String, headData.GID); db.AddInParameter(cmdInsert, "@COMPANYID", DbType.String, headData.COMPANYID); db.AddInParameter(cmdInsert, "@ZL1NAME", DbType.String, headData.ZL1NAME); db.AddInParameter(cmdInsert, "@ZL1DAY", DbType.Int16, headData.ZL1DAY); db.AddInParameter(cmdInsert, "@ZL2NAME", DbType.String, headData.ZL2NAME); db.AddInParameter(cmdInsert, "@ZL2DAY", DbType.Int16, headData.ZL2DAY); db.AddInParameter(cmdInsert, "@ZL3NAME", DbType.String, headData.ZL3NAME); db.AddInParameter(cmdInsert, "@ZL3DAY", DbType.Int16, headData.ZL3DAY); db.AddInParameter(cmdInsert, "@ZL4NAME", DbType.String, headData.ZL4NAME); db.AddInParameter(cmdInsert, "@ZL4DAY", DbType.Int16, headData.ZL4DAY); db.AddInParameter(cmdInsert, "@ZL5NAME", DbType.String, headData.ZL5NAME); db.AddInParameter(cmdInsert, "@ZL5DAY", DbType.Int16, headData.ZL5DAY); db.AddInParameter(cmdInsert, "@ZL6NAME", DbType.String, headData.ZL6NAME); db.AddInParameter(cmdInsert, "@ZL6DAY", DbType.Int16, headData.ZL6DAY); db.AddInParameter(cmdInsert, "@ZL7NAME", DbType.String, headData.ZL7NAME); db.AddInParameter(cmdInsert, "@ZL7DAY", DbType.Int16, headData.ZL7DAY); db.AddInParameter(cmdInsert, "@ZL8NAME", DbType.String, headData.ZL8NAME); db.AddInParameter(cmdInsert, "@ZL8DAY", DbType.Int16, headData.ZL8DAY); db.ExecuteNonQuery(cmdInsert, tran); tran.Commit(); result.Success = true; result.Message = "保存成功！"; } catch (Exception) { tran.Rollback(); result.Success = false; result.Message = "保存出现错误，请重试或联系系统管理员"; } } var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } else { var jsonRespose = new JsonResponse { Success = false, Message = errorstr + "重复,不允许保存！" }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } } #endregion #region 参照部分 #endregion } }