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 Blame History Unescape Escape

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

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
}
}
Reference in New Issue View Git Blame Copy Permalink