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/MsRptFeeSettleStatusControl...

687 lines
32 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 System.IO;
namespace DSWeb.MvcShipping.Controllers
{
/// <summary>
/// 结算情况查询
/// </summary>
[JsonRequestBehavior]
public class MsRptFeeSettleStatusController : Controller
{
//
// GET: /MvcShipping/MsRptOpProfit
public ActionResult Index()
{
return View();
}
public ActionResult ZnjIndex()
{
return View();
}
//
// GET：/RptMng/MsRptPcHeadQry/QryData
public ContentResult BsListData(int start, int limit, string condition, string sort, string printstr,string sortstr)
{
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 strSql = new StringBuilder();
strSql.Append(" SELECT B.BSNO,B.OPTYPE, B.OPLBNAME, B.BSTYPE,B.CUSTOMERNAME, B.CUSTNO, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.CREATETIME, B.BSSTATUS, B.SALE, B.OP, B.DOC ");
strSql.Append(" ,case B.ETD when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.ETD,23) end as ETD ");
strSql.Append(" ,case B.OPDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.OPDATE,23) end as OPDATE,B.ENTERP ");
strSql.Append(" ,case B.CUSTOMDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.CUSTOMDATE,23) end as CUSTOMDATE ");
strSql.Append(" ,B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER, B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.CBM,B.TEU,B.CNTRTOTAL,B.CORPID ");
strSql.Append(" ,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS ");
strSql.Append(" ,(CASE B.FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS RMBCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS USDDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS USDCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS OTDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS OTCR ");
strSql.Append(" ,s.SETTLETIME,s.BILLNO,s.CUSTOMERNAME CUSTNAME,s.SETTLENO ");
strSql.Append(",(select ShowName from [user] where GID=s.SETTLEUSER) as SETTLEUSERREF");
strSql.Append(",(select STLNAME from [code_stlmode] where STLCODE=s.SETTLETYPE) as SETTLETYPEREF");
strSql.Append(",(select BANKNAME from [sys_bank] where GID=s.ACCOUNTRMB) as ACCOUNTRMBREF");
strSql.Append(",(select BANKNAME from [sys_bank] where GID=s.ACCOUNTUSD) as ACCOUNTUSDREF");
strSql.Append(" FROM CH_FEE_DO D ");
strSql.Append(" LEFT JOIN ch_fee f on f.gid=D.feeid ");
strSql.Append(" INNER JOIN V_OP_BILL B ON (B.BSNO=D.BSNO) ");
strSql.Append(" left JOIN ch_fee_settlement s ON (s.BILLNO=D.BILLNO) ");
strSql.Append(" Where D.CATEGORY in(1,2,3,8,9) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" AND " + condition);
}
strSql.Append(" GROUP BY B.BSNO,B.OPTYPE,B.OPLBNAME, B.BSTYPE,B.CUSTOMERNAME, B.CUSTNO, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.CREATETIME, B.BSSTATUS, B.SALE, B.OP, B.DOC ");
strSql.Append(" , B.ETD,B.OPDATE,B.ENTERP,B.CUSTOMDATE,B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER, B.BSSOURCE ");
strSql.Append(" , B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.CBM,B.TEU,B.CNTRTOTAL,B.CORPID,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS,s.SETTLETIME,s.BILLNO,s.CUSTOMERNAME,s.SETTLENO,s.SETTLEUSER,s.SETTLETYPE,s.ACCOUNTRMB,s.ACCOUNTUSD ");
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
{
if (!string.IsNullOrEmpty(sortstr))
{
strSql.Append(" order by " + sortstr);
}
else
{
strSql.Append(" order by B.CUSTOMERNAME,B.OPDATE,B.MBLNO ");
}
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.CUSTOMERNAME,B.OPDATE,B.MBLNO ");
}
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 GetSqlStr(string condition)
{
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 jsonRespose = new JsonResponse
{
Success = true,
Message = "完成",
Data = condition
};
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
}
public ContentResult FeeListData(int start, int limit, string condition, string sort, string printstr,string sortstr)
{
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 strSql = new StringBuilder();
strSql.Append("SELECT B.BSNO, B.OPLB,B.OPTYPE, B.OPLBNAME, B.BSTYPE,B.BLTYPE,B.CUSTOMERNAME, B.CUSTNO, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.CREATETIME, B.BSSTATUS, B.SALE, B.OP, B.DOC,");
strSql.Append("case B.ETD when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.ETD,23) end as ETD");
strSql.Append(",case B.OPDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.OPDATE,23) end as OPDATE,B.ENTERP");
strSql.Append(",case B.CUSTOMDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.CUSTOMDATE,23) end as CUSTOMDATE");
strSql.Append(",B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER, B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.TEU,B.CNTRTOTAL,B.CORPID");
strSql.Append(",B.TRADETYPE,B.GOODSNAME");
strSql.Append(",F.GID,F.FEETYPE,F.FEENAME,F.FEEDESCRIPTION,F.CUSTOMERNAME AS CUSTNAME,F.UNIT,F.UNITPRICE,F.QUANTITY,F.AMOUNT,F.CURRENCY,F.EXCHANGERATE");
strSql.Append(",F.TAXRATE,F.NOTAXAMOUNT,F.ACCTAXRATE,F.REMARK,D.ORIGAMOUNT SETTLEMENT,F.INVOICE,F.ORDERAMOUNT,F.ORDERINVOICE,F.ENTEROPERATOR,F.FEESTATUS,D.EXCHANGERATE RATE");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=1 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBDR ");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=2 THEN (CASE F.CURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLRMBCR ");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=1 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLUSDDR ");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=2 THEN (CASE F.CURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLUSDCR ");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=1 THEN (CASE WHEN F.CURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLOTDR ");
strSql.Append(" ,isnull(CASE WHEN F.FEETYPE=2 THEN (CASE WHEN F.CURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END,0) AS STLOTCR ");
strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=F.FeeStatus) as FeeStatus_Ref");
strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=F.FeeType) as FeeType_Ref");
strSql.Append(" ,s.SETTLETIME,s.BILLNO,s.SETTLENO ");
strSql.Append(",(select ShowName from [user] where GID=s.SETTLEUSER) as SETTLEUSERREF");
strSql.Append(",(select STLNAME from [code_stlmode] where STLCODE=s.SETTLETYPE) as SETTLETYPEREF");
strSql.Append(" FROM CH_FEE_DO D ");
strSql.Append(" left join CH_FEE F on F.GID=D.FEEID ");
strSql.Append(" INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) ");
strSql.Append(" left JOIN ch_fee_settlement s ON (s.BILLNO=D.BILLNO) ");
strSql.Append(" Where D.CATEGORY in(1,2,3,8,9) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" and " + condition);
}
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
{
if (!string.IsNullOrEmpty(sortstr))
{
strSql.Append(" order by " + sortstr);
}
else
{
strSql.Append(" order by F.CUSTOMERNAME,B.OPDATE,B.MBLNO");
}
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,B.OPDATE,B.MBLNO");
}
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)
{
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 strSql = new StringBuilder();
strSql.Append("SELECT F.CUSTOMERNAME AS CUSTNAME ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLUSDDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLUSDCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLOTDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLOTCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN ISNULL(D.ORIGAMOUNT,0)*F.EXCHANGERATE ELSE 0 END),0) AS STLTTLDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN ISNULL(D.ORIGAMOUNT,0)*F.EXCHANGERATE ELSE 0 END),0) AS STLTTLCR ");
strSql.Append(" FROM CH_FEE_DO D ");
strSql.Append(" left join CH_FEE F on F.GID=D.FEEID ");
strSql.Append("INNER JOIN V_OP_BILL B ON (B.BSNO=D.BSNO) ");
strSql.Append(" left JOIN ch_fee_settlement s ON (s.BILLNO=D.BILLNO) ");
strSql.Append(" Where D.CATEGORY in(1,2,3,8,9) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" and " + condition);
}
strSql.Append(" Group by F.CUSTOMERNAME ");
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 ZnjFeeListData(int start, int limit, string condition, string sort, string printstr, string sortstr)
{
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 strSql = new StringBuilder();
strSql.Append("SELECT B.BSNO,B.PARENTID, B.OPLB,B.OPTYPE, B.OPLBNAME, B.BSTYPE,B.BLTYPE,B.CUSTOMERNAME, B.CUSTNO, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.CREATETIME, B.BSSTATUS, B.SALE, B.OP, B.DOC,");
strSql.Append("case B.ETD when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.ETD,23) end as ETD");
strSql.Append(",(CASE B.FEESTATUS WHEN 1 THEN '锁定' else '未锁定' end) as FEESTATUSREF ");
strSql.Append(",case B.OPDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.OPDATE,23) end as OPDATE,B.ENTERP");
strSql.Append(",case B.CUSTOMDATE when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(100), B.CUSTOMDATE,23) end as CUSTOMDATE");
strSql.Append(",B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER, B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.TEU,B.CNTRTOTAL,B.CORPID");
strSql.Append(",B.TRADETYPE,B.GOODSNAME");
strSql.Append(",F.GID,F.FEETYPE,F.FEENAME,F.FEEDESCRIPTION,F.CUSTOMERNAME AS CUSTNAME,F.UNIT,F.UNITPRICE,F.QUANTITY,F.AMOUNT,F.CURRENCY,F.EXCHANGERATE");
strSql.Append(",F.TAXRATE,F.NOTAXAMOUNT,F.ACCTAXRATE,F.REMARK,D.ORIGAMOUNT SETTLEMENT,F.INVOICE,F.ORDERAMOUNT,F.ORDERINVOICE,F.ENTEROPERATOR,F.FEESTATUS,D.EXCHANGERATE RATE");
strSql.Append(",ISNULL((SELECT SUM(ISNULL(AMOUNT,0)) FROM CH_FEE WHERE CURRENCY='USD' AND FEENAME='海运费' and FEETYPE=2 AND BSNO=B.BSNO),0) AS USDHYFCR,");
strSql.Append("ISNULL((SELECT SUM(ISNULL(SETTLEMENT,0)) FROM CH_FEE WHERE CURRENCY='USD' AND FEENAME='海运费' and FEETYPE=2 AND BSNO=B.BSNO),0) AS STLUSDHYFCR,");
strSql.Append("ISNULL((SELECT SUM(ISNULL(AMOUNT-SETTLEMENT,0)) FROM CH_FEE WHERE CURRENCY='USD' AND FEENAME='海运费' and FEETYPE=2 AND BSNO=B.BSNO),0) AS BALUSDHYFCR");
strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=F.FeeStatus) as FeeStatus_Ref");
strSql.Append(",(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99020 and EnumValueID=F.FeeType) as FeeType_Ref");
strSql.Append(" ,s.SETTLETIME,s.BILLNO,s.SETTLENO ");
strSql.Append(",(select ShowName from [user] where GID=s.SETTLEUSER) as SETTLEUSERREF");
strSql.Append(",(select STLNAME from [code_stlmode] where STLCODE=s.SETTLETYPE) as SETTLETYPEREF");
strSql.Append(" FROM CH_FEE_DO D ");
strSql.Append(" left join CH_FEE F on F.GID=D.FEEID ");
strSql.Append(" INNER JOIN V_OP_BILL B ON (F.BSNO=B.BSNO) ");
strSql.Append(" left JOIN ch_fee_settlement s ON (s.BILLNO=D.BILLNO) ");
strSql.Append(" Where D.CATEGORY in(1,2,3,8,9) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" and " + condition);
}
if ((!string.IsNullOrEmpty(printstr)) && (printstr == "true"))
{
if (!string.IsNullOrEmpty(sortstr))
{
strSql.Append(" order by " + sortstr);
}
else
{
strSql.Append(" order by F.CUSTOMERNAME,B.OPDATE,B.MBLNO");
}
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,B.OPDATE,B.MBLNO");
}
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 SumListData(int start, int limit, string condition, string sort)
{
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 strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(" isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'RMB' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLRMBCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLUSDDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE D.ORIGCURRENCY WHEN 'USD' THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLUSDCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLOTDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN (CASE WHEN D.ORIGCURRENCY not in('RMB','USD') THEN ISNULL(D.ORIGAMOUNT,0) ELSE 0 END) ELSE 0 END),0) AS STLOTCR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=1 THEN ISNULL(D.ORIGAMOUNT,0)*F.EXCHANGERATE ELSE 0 END),0) AS STLTTLDR ");
strSql.Append(" ,isnull(SUM(CASE WHEN D.FEETYPE=2 THEN ISNULL(D.ORIGAMOUNT,0)*F.EXCHANGERATE ELSE 0 END),0) AS STLTTLCR ");
strSql.Append(" FROM CH_FEE_DO D ");
strSql.Append(" left join CH_FEE F on F.GID=D.FEEID ");
strSql.Append("INNER JOIN V_OP_BILL B ON (B.BSNO=D.BSNO) ");
strSql.Append(" left JOIN ch_fee_settlement s ON (s.BILLNO=D.BILLNO) ");
strSql.Append(" Where D.CATEGORY in(1,2,3,8,9) ");
if (!string.IsNullOrEmpty(condition))
{
strSql.Append(" and " + 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]='modNoTotalreportRange' 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 + "'";
}
}
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"]) + "'";
}
else
{
str = str + " or b.Corpid='" + 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"]) + "' OR b.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.INPUTBY='" + Convert.ToString(reader["SHOWNAME"]) + "' ";
}
else
{
str = str + " or b.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR b.INPUTBY='" + Convert.ToString(reader["SHOWNAME"]) + "' ";
};
}
str = str + ")";
reader.Close();
}
}
else if (visiblerange == "1")
{
str = " UPPER(B.Corpid)='" + companyid + "'";
}
VSSQL = VSSQL.Trim();
if (!string.IsNullOrEmpty(VSSQL))
{
if (!string.IsNullOrEmpty(str))
{
str = str + " and (" + VSSQL + ") ";
}
else
{
str = " (" + VSSQL + ") ";
}
}
return str;
}
#region 参照部分
#endregion
}
}
Reference in New Issue View Git Blame Copy Permalink