dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
DS7_HYRD
ShenZhenCHJY
DS7_JinGang
dev
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
DS7/DSWeb/Areas/MvcShipping/Controllers/MsRptNoTotalZlController.cs

2356 lines
216 KiB
C#
Raw Permalink Normal View History Unescape Escape

'first'
2 years ago
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
{
/// <summary>
/// 欠费账龄分析查询
/// </summary>
[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<CustomDbParamter>();
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 '旬结' ELSE CASE WHEN A.ACCTYPE='月结' THEN '月结 间隔'+A.ACCMONTH+'月 账期日'+ACCDAY ELSE A.ACCTYPE+' 账期日'+A.ACCDAY END END END) ZLSTR ");
strSql.Append(",(CASE ISNULL(A.ACCTYPE,'') WHEN '月结' then A.accmonth*30 + A.accday WHEN '季结' then A.accmonth*90 + A.accday WHEN '旬结' then 10 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) ");
strSql.Append("LEFT JOIN (SELECT I.SHORTNAME,cast(ACCTYPE as varchar) ACCTYPE,cast(ACCMONTH as varchar) ACCMONTH,cast(ACCDAYS as varchar) ACCDAY,row_number() over (partition by SHORTNAME order by isnull(BGNDATE,getdate()) DESC) as rowno FROM Info_Client_ACCDATE A LEFT JOIN INFO_CLIENT I ON (I.GID=A.LINKGID) WHERE ISNULL(I.SHORTNAME, '') != '') A ON (A.SHORTNAME=F.CUSTOMERNAME and A.rowno=1) ");
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<CustomDbParamter>();
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 WHEN '旬结' then 10 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(ACCTYPE as varchar) ACCTYPE,cast(ACCMONTH as varchar) ACCMONTH,cast(ACCDAYS as varchar) ACCDAY,row_number() over (partition by SHORTNAME order by isnull(BGNDATE,getdate()) DESC) as rowno FROM Info_Client_ACCDATE A LEFT JOIN INFO_CLIENT I ON (I.GID=A.LINKGID) WHERE ISNULL(I.SHORTNAME, '') != '') A ON (A.SHORTNAME=F.CUSTOMERNAME and A.rowno=1) ");
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<CustomDbParamter>();
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<CustomDbParamter>();
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,VSSQL ");
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 = "";
string VSSQL = "";
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"]);
VSSQL = Convert.ToString(reader["VSSQL"]);
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 + "') ";
}
VSSQL = VSSQL.Trim();
if (!string.IsNullOrEmpty(VSSQL))
{
if (!string.IsNullOrEmpty(str))
{
str = str + " and (" + VSSQL + ") ";
}
else
{
str = " (" + VSSQL + ") ";
}
}
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<SyszlSet>();
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<SyszlSet>(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
}
}