using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using DSWeb.MvcShipping.Models.MsOpBillProfit;
using DSWeb.MvcShipping.Models.InfoSaleProfitSharePlan;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.Areas.CommMng.Models;
using HcUtility.Comm;
using System.Data.SqlClient;
using DSWeb.DataAccess;
using System.IO;
using HcUtility.Core;
using DSWeb.Areas.CommMng.DAL;
using DSWeb.MvcShipping.DAL.MsInfoSaleProfitShareSet;
using DSWeb.MvcShipping.DAL.MsSysParamSet;

namespace DSWeb.MvcShipping.DAL.MsRptSaleJieSuanTiChengDAL
{
    /// <summary>
    /// 鼎视结算提成报表//需求编号：SR2017072100012
    /// </summary>
    public class MsRptSaleJieSuanTiChengDAL
    {
        #region 查询
        static public List<MsOpBillProfit> GetDataList(string strCondition, string strStlCondition, string DStlCondition, string DStlCondition2, string INTEREST_FEE, string userid, string usercode, string companyid, string sort = null)
        {
            var rangstr = GetRangDAStr("index", userid, usercode, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            var strSql = new StringBuilder();
            strSql.Append("SELECT B.BSNO, B.OPLB,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.CUSTOMDATE,B.FEESTATUSREF,B.BLFRT,B.BSSOURCEDETAIL");
            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.CNTR1,B.CNTR2,B.CNTR3,B.CNTR4,B.CNTR5,B.CNTR6,B.CNTR7,B.CNTR8,B.CNTR9,B.CNTR10,B.OTCNTR,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS");
            strSql.Append(",B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR");
            strSql.Append(",B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ");
            strSql.Append(",f.ISFEE ");
            strSql.Append(",(CASE f.ISFEE WHEN 1 THEN '已发放' else '未发放' end) as ISSALEFEE ");//ISFEEREF
            strSql.Append(",SETTLETIME_T=SUBSTRING(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23),1,7)");//提成月份
            strSql.Append(",SETTLETIME_Y=(case when (STLNAME='票结' or STLNAME='现结买单' or STLNAME='') then dateadd(day,14,b.ETD) else (Datename(year,dateadd(month,1,b.ETD))+'-'+Datename(month,dateadd(month,1,b.ETD))+'-'+'15') end)");//应结算时间
            strSql.Append(",SETTLETIME=(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23))");//实际结算时间
            strSql.Append(",INTEREST=isnull(Convert(decimal(18,2),(B.TTLDR*" + INTEREST_FEE + "*DATEDIFF(day, (case when (STLNAME='票结' or STLNAME='现结买单' or STLNAME='') then dateadd(day,14,b.ETD) else (Datename(year,dateadd(month,1,b.ETD))+'-'+Datename(month,dateadd(month,1,b.ETD))+'-'+'15') end), (CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23))))),0)");//资金占用费=(合计应收*0.0003*(应结算时间-实际结算时间))，应结费日期（票结客户开船后14天，月结客户次月15日）每日万分之三，提前结费的是负数，拖后的为正数。
            strSql.Append(" FROM v_op_bill_gain B ");
            strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO)  ");
            strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where 1=1 ");
            strSql.Append(" and not EXISTS (select bsno from op_amend g where  g.bsno=B.BSNO and EXISTS (select 1 from v_op_bill_gain P where G.PARENTID=P.BSNO AND P.OPTYPE='普通货' and P.TTLDR<>P.STLTTLDR)) ");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            if (!string.IsNullOrEmpty(strStlCondition))
            {
                strSql.Append(" and " + strStlCondition);
            }
            //
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by ETD desc");
            }
            return SetData(strSql);
        }

        static public List<MsOpBillProfit> GetDataList2(string strCondition, string strStlCondition, string DStlCondition, string DStlCondition2, string INTEREST_FEE, string userid, string usercode, string companyid, string sort = null)
        {
            var rangstr = GetRangDAStr("index", userid, usercode, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            var strSql = new StringBuilder();
            strSql.Append("SELECT B.BSNO, B.OPLB,B.OPTYPE,B.OPLBNAME, B.BSTYPE,B.CUSTOMERNAME, B.MBLNO, B.HBLNO,B.VESSEL, B.VOYNO, B.INPUTBY, B.BSSTATUS, B.SALE, B.OP, B.DOC,");
            strSql.Append("B.ETD,B.OPDATE,B.CUSTOMDATE,B.FEESTATUSREF,B.BLFRT,B.BSSOURCEDETAIL");
            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.CNTR1,B.CNTR2,B.CNTR3,B.CNTR4,B.CNTR5,B.CNTR6,B.CNTR7,B.CNTR8,B.CNTR9,B.CNTR10,B.OTCNTR,B.TRADETYPE,B.GOODSNAME");
            strSql.Append(",B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR");
            strSql.Append(",B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ");
            strSql.Append(",f.ISFEE ");
            strSql.Append(",(CASE f.ISFEE WHEN 1 THEN '已发放' else '未发放' end) as ISSALEFEE ");//ISFEEREF
            strSql.Append(",B.SETTLETIME_T");//提成月份
            strSql.Append(",B.SETTLETIME_Y");//应结算时间
            strSql.Append(",B.SETTLETIME");//实际结算时间
            strSql.Append(",INTEREST=B.INTEREST*" + INTEREST_FEE + "");//资金占用费=(合计应收*0.0003*(应结算时间-实际结算时间))，应结费日期（票结客户开船后14天，月结客户次月15日）每日万分之三，提前结费的是负数，拖后的为正数。
            strSql.Append(" FROM v_op_bill_gain_ds B ");
            //strSql.Append("left join v_fee_do_settlement sd on (sd.BSNO=B.BSNO)  ");
            //strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO)  ");

            strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where 1=1 ");
            //strSql.Append(" and not EXISTS (select bsno from op_amend g where  g.bsno=B.BSNO and EXISTS (select 1 from v_op_bill_gain P where G.PARENTID=P.BSNO AND P.OPTYPE='普通货' and P.TTLDR<>P.STLTTLDR)) ");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            if (!string.IsNullOrEmpty(strStlCondition))
            {
                strSql.Append(" and " + strStlCondition);
            }

            //strSql.Append(" GROUP BY B.BSNO, B.OPLB,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,");
            //strSql.Append("B.DOC,B.ETD,B.OPDATE,B.CUSTOMDATE,B.FEESTATUSREF,B.BLFRT,B.BSSOURCEDETAIL,B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER,");
            //strSql.Append("B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.TEU,B.CNTRTOTAL,B.CORPID,B.CNTR1,B.CNTR2,B.CNTR3,B.CNTR4,B.CNTR5,B.CNTR6,B.CNTR7,B.CNTR8,B.CNTR9,");
            //strSql.Append("B.CNTR10,B.OTCNTR,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS,B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,");
            //strSql.Append("B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR,B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,");
            //strSql.Append("B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ,f.ISFEE ,(CASE f.ISFEE WHEN 1 THEN '已发放' else '未发放' end) ,SUBSTRING(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23),1,7),");
            //strSql.Append("(case when (STLNAME='票结' or STLNAME='现结买单' or STLNAME='') then dateadd(day,14,b.ETD) else (Datename(year,dateadd(month,1,b.ETD))+'-'+Datename(month,dateadd(month,1,b.ETD))+'-'+'15') end),(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23)) ");


            //
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by ETD desc");
            }
            return SetData(strSql);
        }


   


        private static List<MsOpBillProfit> SetData(StringBuilder strSql)
        {
            var headList = new List<MsOpBillProfit>();
            Database db = DatabaseFactory.CreateDatabase();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSql.ToString();
            cmd.CommandTimeout = 1200000;   //要加这一句

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    MsOpBillProfit data = new MsOpBillProfit();
                    #region Set DB data to Object
                    data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号
                    data.FEESTATUSREF = Convert.ToString(reader["FEESTATUSREF"]);
                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
                    data.OPTYPE = Convert.ToString(reader["OPTYPE"]);
                    data.OPLB = Convert.ToString(reader["OPLB"]);
                    data.OPLBNAME = Convert.ToString(reader["OPLBNAME"]);
                    data.MBLNO = Convert.ToString(reader["MBLNO"]);
                    data.HBLNO = Convert.ToString(reader["HBLNO"]);
                    data.CUSTOMNO = Convert.ToString(reader["CUSTOMNO"]);
                    data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
                    data.VESSEL = Convert.ToString(reader["VESSEL"]);
                    data.VOYNO = Convert.ToString(reader["VOYNO"]);
                    data.CARRIER = Convert.ToString(reader["CARRIER"]);
                    data.FORWARDER = Convert.ToString(reader["FORWARDER"]);
                    data.ETD = Convert.ToString(reader["ETD"]);
                    data.OPDATE = Convert.ToString(reader["OPDATE"]);
                    data.CUSTOMDATE = Convert.ToString(reader["CUSTOMDATE"]);
                    data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
                    data.PORTDISCHARGE = Convert.ToString(reader["PORTDISCHARGE"]);
                    data.LANE = Convert.ToString(reader["LANE"]);
                    data.BLFRT = Convert.ToString(reader["BLFRT"]);
                    data.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]);
                    data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
                    data.OP = Convert.ToString(reader["OP"]);
                    data.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]);
                    data.DOC = Convert.ToString(reader["DOC"]);
                    data.SALE = Convert.ToString(reader["SALE"]);
                    data.BSSOURCE = Convert.ToString(reader["BSSOURCE"]);
                    data.BSSOURCEDETAIL = Convert.ToString(reader["BSSOURCEDETAIL"]);
                    data.CORPID = Convert.ToString(reader["CORPID"]);
                    if (reader["RMBDR"] != DBNull.Value)
                        data.RMBDR = Convert.ToDecimal(reader["RMBDR"]);
                    if (reader["RMBCR"] != DBNull.Value)
                        data.RMBCR = Convert.ToDecimal(reader["RMBCR"]);
                    if (reader["STLRMBDR"] != DBNull.Value)
                        data.STLRMBDR = Convert.ToDecimal(reader["STLRMBDR"]);
                    if (reader["STLRMBCR"] != DBNull.Value)
                        data.STLRMBCR = Convert.ToDecimal(reader["STLRMBCR"]);
                    if (reader["RMBNODR"] != DBNull.Value)
                        data.RMBNODR = Convert.ToDecimal(reader["RMBNODR"]);
                    if (reader["RMBNOCR"] != DBNull.Value)
                        data.RMBNOCR = Convert.ToDecimal(reader["RMBNOCR"]);
                    if (reader["RMBPROFIT"] != DBNull.Value)
                        data.RMBPROFIT = Convert.ToDecimal(reader["RMBPROFIT"]);
                    if (reader["USDDR"] != DBNull.Value)
                        data.USDDR = Convert.ToDecimal(reader["USDDR"]);
                    if (reader["USDCR"] != DBNull.Value)
                        data.USDCR = Convert.ToDecimal(reader["USDCR"]);
                    if (reader["STLUSDDR"] != DBNull.Value)
                        data.STLUSDDR = Convert.ToDecimal(reader["STLUSDDR"]);
                    if (reader["STLUSDCR"] != DBNull.Value)
                        data.STLUSDCR = Convert.ToDecimal(reader["STLUSDCR"]);
                    if (reader["USDNODR"] != DBNull.Value)
                        data.USDNODR = Convert.ToDecimal(reader["USDNODR"]);
                    if (reader["USDNOCR"] != DBNull.Value)
                        data.USDNOCR = Convert.ToDecimal(reader["USDNOCR"]);
                    if (reader["USDPROFIT"] != DBNull.Value)
                        data.USDPROFIT = Convert.ToDecimal(reader["USDPROFIT"]);
                    if (reader["OTDR"] != DBNull.Value)
                        data.OTDR = Convert.ToDecimal(reader["OTDR"]);
                    if (reader["OTCR"] != DBNull.Value)
                        data.OTCR = Convert.ToDecimal(reader["OTCR"]);
                    if (reader["STLOTDR"] != DBNull.Value)
                        data.STLOTDR = Convert.ToDecimal(reader["STLOTDR"]);
                    if (reader["STLOTCR"] != DBNull.Value)
                        data.STLOTCR = Convert.ToDecimal(reader["STLOTCR"]);
                    if (reader["OTNODR"] != DBNull.Value)
                        data.OTNODR = Convert.ToDecimal(reader["OTNODR"]);
                    if (reader["OTNOCR"] != DBNull.Value)
                        data.OTNOCR = Convert.ToDecimal(reader["OTNOCR"]);
                    if (reader["OTPROFIT"] != DBNull.Value)
                        data.OTPROFIT = Convert.ToDecimal(reader["OTPROFIT"]);
                    if (reader["TTLDR"] != DBNull.Value)
                        data.TTLDR = Convert.ToDecimal(reader["TTLDR"]);
                    if (reader["TTLCR"] != DBNull.Value)
                        data.TTLCR = Convert.ToDecimal(reader["TTLCR"]);
                    if (reader["STLTTLDR"] != DBNull.Value)
                        data.STLTTLDR = Convert.ToDecimal(reader["STLTTLDR"]);
                    if (reader["STLTTLCR"] != DBNull.Value)
                        data.STLTTLCR = Convert.ToDecimal(reader["STLTTLCR"]);
                    if (reader["TTLNODR"] != DBNull.Value)
                        data.TTLNODR = Convert.ToDecimal(reader["TTLNODR"]);
                    if (reader["TTLNOCR"] != DBNull.Value)
                        data.TTLNOCR = Convert.ToDecimal(reader["TTLNOCR"]);
                    if (reader["TTLPROFIT"] != DBNull.Value)
                        data.TTLPROFIT = Convert.ToDecimal(reader["TTLPROFIT"]);
                    data.PROFITRATE = Convert.ToString(reader["PROFITRATE"]);
                    if (reader["SETTLETIME"] != DBNull.Value)
                        data.SETTLETIME = Convert.ToDateTime(reader["SETTLETIME"]).ToString("yyyy-MM-dd");
                    data.ISSALEFEE = Convert.ToString(reader["ISSALEFEE"]);//ISFEEREF
                    data.SETTLETIME_T = Convert.ToString(reader["SETTLETIME_T"]);
                    if (reader["SETTLETIME_Y"] != DBNull.Value)
                    {
                        data.SETTLETIME_Y = Convert.ToDateTime(reader["SETTLETIME_Y"]).ToString("yyyy-MM-dd");//应结算时间
                    }
                    data.INTEREST = Convert.ToDecimal(reader["INTEREST"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 查询_合计
        static public List<MsOpBillProfit> GetDataListSum(string strCondition, string strStlCondition, string DStlCondition, string DStlCondition2, string INTEREST_FEE, string userid, string usercode, string companyid, string sort = null)
        {
            var rangstr = GetRangDAStr("index", userid, usercode, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            var strSql = new StringBuilder();
            strSql.Append("SELECT newid() as BSNO,OPTYPE");
            strSql.Append(",sum(RMBDR) as RMBDR,sum(RMBCR) as RMBCR,sum(STLRMBDR) as STLRMBDR,sum(STLRMBCR) as STLRMBCR,sum(USDDR) as USDDR,sum(USDCR) as USDCR,sum(STLUSDDR) as STLUSDDR,sum(STLUSDCR) as STLUSDCR,sum(OTDR) as OTDR,sum(OTCR) as OTCR,sum(STLOTDR) as STLOTDR,sum(STLOTCR) as STLOTCR,sum(TTLDR) as TTLDR,sum(TTLCR) as TTLCR,sum(STLTTLDR) as STLTTLDR,sum(STLTTLCR) as STLTTLCR");
            strSql.Append(",sum(USDPROFIT) as USDPROFIT,sum(RMBPROFIT) as RMBPROFIT,sum(OTPROFIT) as OTPROFIT,sum(TTLPROFIT) as TTLPROFIT");
            //strSql.Append(",sum(PROFITRATE) as PROFITRATE");
            strSql.Append(",sum(RMBNODR) as RMBNODR,sum(USDNODR) as USDNODR,sum(OTNODR) as OTNODR,sum(TTLNODR) as TTLNODR,sum(RMBNOCR) as RMBNOCR,sum(USDNOCR) as USDNOCR,sum(OTNOCR) as OTNOCR,sum(TTLNOCR) as TTLNOCR ");
            strSql.Append(",sum(INTEREST) as INTEREST");
            strSql.Append(" from (");
            strSql.Append("SELECT B.BSNO,B.OPTYPE");
            strSql.Append(",B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR");
            strSql.Append(",B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ");
            strSql.Append(",INTEREST=(INTEREST*" + INTEREST_FEE + ")");//资金占用费=(合计应收*0.0003*(应结算时间-实际结算时间))，应结费日期（票结客户开船后14天，月结客户次月15日）每日万分之三，提前结费的是负数，拖后的为正数。
            strSql.Append(" FROM v_op_bill_gain_ds B ");
            //strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO)  ");
            strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where 1=1 ");
            //            strSql.Append(" and B.BSNO not in (select bsno from v_op_bill_gain where OPTYPE='更改单' and CUSTNO in (select CUSTNO from v_op_bill_gain where OPTYPE='普通货' and TTLDR<>STLTTLDR))");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。
//            strSql.Append(" and not EXISTS (select bsno from op_amend g where  g.bsno=B.BSNO and EXISTS (select 1 from v_op_bill_gain P where G.PARENTID=P.BSNO AND P.OPTYPE='普通货' and P.TTLDR<>P.STLTTLDR)) ");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            if (!string.IsNullOrEmpty(strStlCondition))
            {
                strSql.Append(" and " + strStlCondition);
            }
            strSql.Append(") as a group by OPTYPE order by OPTYPE");
            return SetDataSum(strSql);
        }

        static public List<MsOpBillProfit> GetDataListSum2(string strCondition, string strStlCondition, string DStlCondition, string DStlCondition2, string INTEREST_FEE, string userid, string usercode, string companyid, string sort = null)
        {
            var rangstr = GetRangDAStr("index", userid, usercode, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            var strSql = new StringBuilder();
            strSql.Append("SELECT newid() as BSNO,OPTYPE");
            strSql.Append(",sum(RMBDR) as RMBDR,sum(RMBCR) as RMBCR,sum(STLRMBDR) as STLRMBDR,sum(STLRMBCR) as STLRMBCR,sum(USDDR) as USDDR,sum(USDCR) as USDCR,sum(STLUSDDR) as STLUSDDR,sum(STLUSDCR) as STLUSDCR,sum(OTDR) as OTDR,sum(OTCR) as OTCR,sum(STLOTDR) as STLOTDR,sum(STLOTCR) as STLOTCR,sum(TTLDR) as TTLDR,sum(TTLCR) as TTLCR,sum(STLTTLDR) as STLTTLDR,sum(STLTTLCR) as STLTTLCR");
            strSql.Append(",sum(USDPROFIT) as USDPROFIT,sum(RMBPROFIT) as RMBPROFIT,sum(OTPROFIT) as OTPROFIT,sum(TTLPROFIT) as TTLPROFIT");
            //strSql.Append(",sum(PROFITRATE) as PROFITRATE");
            strSql.Append(",sum(RMBNODR) as RMBNODR,sum(USDNODR) as USDNODR,sum(OTNODR) as OTNODR,sum(TTLNODR) as TTLNODR,sum(RMBNOCR) as RMBNOCR,sum(USDNOCR) as USDNOCR,sum(OTNOCR) as OTNOCR,sum(TTLNOCR) as TTLNOCR ");
            strSql.Append(",sum(INTEREST) as INTEREST");
            strSql.Append(" from (");
            strSql.Append("SELECT B.BSNO,B.OPTYPE");
            strSql.Append(",B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR");
            strSql.Append(",B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ");
            strSql.Append(",INTEREST=sum(isnull(Convert(decimal(18,2),(sd.ORIGAMOUNT*sd.ORIGEXCHANGERATE*" + INTEREST_FEE + "*DATEDIFF(day, (case when (STLNAME='票结' or STLNAME='现结买单' or STLNAME='') then dateadd(day,14,b.ETD) else (Datename(year,dateadd(month,1,b.ETD))+'-'+Datename(month,dateadd(month,1,b.ETD))+'-'+'15') end), (CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else sd.SETTLETIME end), 23))))),0))");//资金占用费=(合计应收*0.0003*(应结算时间-实际结算时间))，应结费日期（票结客户开船后14天，月结客户次月15日）每日万分之三，提前结费的是负数，拖后的为正数。
            strSql.Append(" FROM v_op_bill_gain B ");
            strSql.Append("left join v_fee_do_settlement sd on (sd.BSNO=B.BSNO)  ");
            strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO)  ");
            strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where 1=1 ");
            strSql.Append(" and not EXISTS (select bsno from op_amend g where  g.bsno=B.BSNO and EXISTS (select 1 from v_op_bill_gain P where G.PARENTID=P.BSNO AND P.OPTYPE='普通货' and P.TTLDR<>P.STLTTLDR)) ");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            if (!string.IsNullOrEmpty(strStlCondition))
            {
                strSql.Append(" and " + strStlCondition);
            }
            strSql.Append(") as a group by OPTYPE order by OPTYPE");
            return SetDataSum(strSql);
        }

        private static List<MsOpBillProfit> SetDataSum(StringBuilder strSql)
        {
            var headList = new List<MsOpBillProfit>();
            Database db = DatabaseFactory.CreateDatabase();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSql.ToString();
            cmd.CommandTimeout = 1200000;   //要加这一句

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    MsOpBillProfit data = new MsOpBillProfit();
                    #region Set DB data to Object
                    data.BSNO = Convert.ToString(reader["BSNO"]);//业务编号
                    data.OPTYPE = Convert.ToString(reader["OPTYPE"]);
                    if (reader["RMBDR"] != DBNull.Value)
                        data.RMBDR = Convert.ToDecimal(reader["RMBDR"]);
                    if (reader["RMBCR"] != DBNull.Value)
                        data.RMBCR = Convert.ToDecimal(reader["RMBCR"]);
                    if (reader["STLRMBDR"] != DBNull.Value)
                        data.STLRMBDR = Convert.ToDecimal(reader["STLRMBDR"]);
                    if (reader["STLRMBCR"] != DBNull.Value)
                        data.STLRMBCR = Convert.ToDecimal(reader["STLRMBCR"]);
                    if (reader["RMBNODR"] != DBNull.Value)
                        data.RMBNODR = Convert.ToDecimal(reader["RMBNODR"]);
                    if (reader["RMBNOCR"] != DBNull.Value)
                        data.RMBNOCR = Convert.ToDecimal(reader["RMBNOCR"]);
                    if (reader["RMBPROFIT"] != DBNull.Value)
                        data.RMBPROFIT = Convert.ToDecimal(reader["RMBPROFIT"]);
                    if (reader["USDDR"] != DBNull.Value)
                        data.USDDR = Convert.ToDecimal(reader["USDDR"]);
                    if (reader["USDCR"] != DBNull.Value)
                        data.USDCR = Convert.ToDecimal(reader["USDCR"]);
                    if (reader["STLUSDDR"] != DBNull.Value)
                        data.STLUSDDR = Convert.ToDecimal(reader["STLUSDDR"]);
                    if (reader["STLUSDCR"] != DBNull.Value)
                        data.STLUSDCR = Convert.ToDecimal(reader["STLUSDCR"]);
                    if (reader["USDNODR"] != DBNull.Value)
                        data.USDNODR = Convert.ToDecimal(reader["USDNODR"]);
                    if (reader["USDNOCR"] != DBNull.Value)
                        data.USDNOCR = Convert.ToDecimal(reader["USDNOCR"]);
                    if (reader["USDPROFIT"] != DBNull.Value)
                        data.USDPROFIT = Convert.ToDecimal(reader["USDPROFIT"]);
                    if (reader["OTDR"] != DBNull.Value)
                        data.OTDR = Convert.ToDecimal(reader["OTDR"]);
                    if (reader["OTCR"] != DBNull.Value)
                        data.OTCR = Convert.ToDecimal(reader["OTCR"]);
                    if (reader["STLOTDR"] != DBNull.Value)
                        data.STLOTDR = Convert.ToDecimal(reader["STLOTDR"]);
                    if (reader["STLOTCR"] != DBNull.Value)
                        data.STLOTCR = Convert.ToDecimal(reader["STLOTCR"]);
                    if (reader["OTNODR"] != DBNull.Value)
                        data.OTNODR = Convert.ToDecimal(reader["OTNODR"]);
                    if (reader["OTNOCR"] != DBNull.Value)
                        data.OTNOCR = Convert.ToDecimal(reader["OTNOCR"]);
                    if (reader["OTPROFIT"] != DBNull.Value)
                        data.OTPROFIT = Convert.ToDecimal(reader["OTPROFIT"]);
                    if (reader["TTLDR"] != DBNull.Value)
                        data.TTLDR = Convert.ToDecimal(reader["TTLDR"]);
                    if (reader["TTLCR"] != DBNull.Value)
                        data.TTLCR = Convert.ToDecimal(reader["TTLCR"]);
                    if (reader["STLTTLDR"] != DBNull.Value)
                        data.STLTTLDR = Convert.ToDecimal(reader["STLTTLDR"]);
                    if (reader["STLTTLCR"] != DBNull.Value)
                        data.STLTTLCR = Convert.ToDecimal(reader["STLTTLCR"]);
                    if (reader["TTLNODR"] != DBNull.Value)
                        data.TTLNODR = Convert.ToDecimal(reader["TTLNODR"]);
                    if (reader["TTLNOCR"] != DBNull.Value)
                        data.TTLNOCR = Convert.ToDecimal(reader["TTLNOCR"]);
                    if (reader["TTLPROFIT"] != DBNull.Value)
                        data.TTLPROFIT = Convert.ToDecimal(reader["TTLPROFIT"]);
                    //data.PROFITRATE = Convert.ToString(reader["PROFITRATE"]);
                    data.INTEREST = Convert.ToDecimal(reader["INTEREST"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 打印
        static public string GetDataListStr(string strCondition, string strStlCondition, string DStlCondition, string DStlCondition2, string INTEREST_FEE, string userid, string usercode, string companyid, string sort = null)
        {
            var rangstr = GetRangDAStr("index", userid, usercode, companyid);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            var strSql = new StringBuilder();
            strSql.Append("SELECT B.BSNO as 编号,B.OPLBNAME as 业务类型,B.OPTYPE as 单据类型,B.FEESTATUSREF as 费用锁定,B.OPDATE as 业务日期,B.CUSTOMERNAME as 委托单位,B.MBLNO as 主提单号,B.SALE as 业务员,B.RMBDR as RMB应收,B.RMBCR as RMB应付,B.RMBPROFIT as RMB利润,B.USDDR as USD应收,B.USDCR as USD应付,B.USDPROFIT as USD利润,B.OTDR as 其他币别应收,B.OTCR as 其他币别应付,B.OTPROFIT as 其他币别利润,B.TTLDR as 合计应收,B.TTLCR as 合计应付,B.TTLPROFIT as 合计利润,B.PROFITRATE as 利润率,B.STLRMBDR as RMB已收,B.RMBNODR as RMB未收,B.STLUSDDR as USD已收,B.USDNODR as USD未收,B.STLOTDR as 其他已收,B.OTNODR as 其他未收,B.STLTTLDR as 合计已收,B.TTLNODR as 合计未收,B.STLRMBCR as RMB已付,B.RMBNOCR as RMB未付,B.STLUSDCR as USD已付,B.USDNOCR as USD未付,B.STLOTCR as 其他币别已付,B.OTNOCR as 其他未付,B.STLTTLCR as 合计已付,B.TTLNOCR as 合计未付");
            strSql.Append(",(CASE f.ISFEE WHEN 1 THEN '已发放' else '未发放' end) as 提成发放 ");
            strSql.Append(",提成月份=B.SETTLETIME_T");//提成月份
            strSql.Append(",应结算时间=B.SETTLETIME_Y");//应结算时间
            strSql.Append(",实际结算时间=B.SETTLETIME");//实际结算时间
            strSql.Append(",资金占用费=B.INTEREST*" + INTEREST_FEE + "");//资金占用费=(合计应收*0.0003*(应结算时间-实际结算时间))，应结费日期（票结客户开船后14天，月结客户次月15日）每日万分之三，提前结费的是负数，拖后的为正数。
            strSql.Append(" FROM v_op_bill_gain_ds B ");
            //strSql.Append("LEFT JOIN V_MAX_SETTLEMENTTIME_BSDR T ON (T.BSNO=B.BSNO)  ");
            //strSql.Append("left join v_fee_do_settlement sd on (sd.BSNO=B.BSNO)  ");
            strSql.Append("LEFT JOIN op_sale_proftfee f ON (f.BSNO=B.BSNO) where 1=1 ");
            strSql.Append(" and not EXISTS (select bsno from op_amend g where  g.bsno=B.BSNO and EXISTS (select 1 from v_op_bill_gain P where G.PARENTID=P.BSNO AND P.OPTYPE='普通货' and P.TTLDR<>P.STLTTLDR)) ");//有更改单原票未发提成：也要看更改的原票，是不是已经提成了，没提成可以忽略，等原票收回一起。因此原票的合计应收与合计已收不等的更改单不显示。
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            if (!string.IsNullOrEmpty(strStlCondition))
            {
                strSql.Append(" and " + strStlCondition);
            }

            //strSql.Append(" GROUP BY B.BSNO, B.OPLB,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,");
            //strSql.Append("B.DOC,B.ETD,B.OPDATE,B.CUSTOMDATE,B.FEESTATUSREF,B.BLFRT,B.BSSOURCEDETAIL,B.CUSTSERVICE, B.PORTLOAD, B.PORTDISCHARGE, B.CUSTOMNO, B.ACCDATE,B.CARRIER,");
            //strSql.Append("B.BSSOURCE, B.LANE, B.FORWARDER,B.NETWEIGHT,B.KGS,B.TEU,B.CNTRTOTAL,B.CORPID,B.CNTR1,B.CNTR2,B.CNTR3,B.CNTR4,B.CNTR5,B.CNTR6,B.CNTR7,B.CNTR8,B.CNTR9,");
            //strSql.Append("B.CNTR10,B.OTCNTR,B.TRADETYPE,B.GOODSNAME,B.FEESTATUS,B.RMBDR,B.RMBCR,B.STLRMBDR,B.STLRMBCR,B.USDDR,B.USDCR,B.STLUSDDR,B.STLUSDCR,B.OTDR,B.OTCR,B.STLOTDR,");
            //strSql.Append("B.STLOTCR,B.TTLDR,B.TTLCR,B.STLTTLDR,B.STLTTLCR,B.USDPROFIT,B.RMBPROFIT,B.OTPROFIT,B.TTLPROFIT,B.PROFITRATE,B.RMBNODR,B.USDNODR,B.OTNODR,B.TTLNODR,");
            //strSql.Append("B.RMBNOCR,B.USDNOCR,B.OTNOCR,B.TTLNOCR ,f.ISFEE ,(CASE f.ISFEE WHEN 1 THEN '已发放' else '未发放' end) ,SUBSTRING(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23),1,7),");
            //strSql.Append("(case when (STLNAME='票结' or STLNAME='现结买单' or STLNAME='') then dateadd(day,14,b.ETD) else (Datename(year,dateadd(month,1,b.ETD))+'-'+Datename(month,dateadd(month,1,b.ETD))+'-'+'15') end),(CONVERT(varchar(20), (case when (OPTYPE='更改单' and TTLDR=0) then B.CREATETIME else T.SETTLETIME end), 23)) ");

            //
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by ETD desc");
            }
            return strSql.ToString();
        }
        #endregion
        
        #region 发放提成
        public static DBResult SetFee(List<MsOpBillProfit> boday, String USERID)
        {
            var result = new DBResult();

            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                try
                {
                    if (boday != null)
                    {

                        var cmdInsert =
                            db.GetSqlStringCommand(
                                   @"insert into op_sale_proftfee (GID,BSNO,ISFEE,INPUTDATE,INPUTBY) 
                                   values (@GID,@BSNO,@ISFEE,@INPUTDATE,@INPUTBY) ");

                        var cmddelete =
                            db.GetSqlStringCommand(
                                   @"delete from op_sale_proftfee where BSNO=@BSNO ");

                        foreach (var enumValue in boday)
                        {

                            cmddelete.Parameters.Clear();
                            db.AddInParameter(cmddelete, "@BSNO", DbType.String, enumValue.BSNO);
                            db.ExecuteNonQuery(cmddelete, tran);
                            cmdInsert.Parameters.Clear();
                            db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString());
                            db.AddInParameter(cmdInsert, "@BSNO", DbType.String, enumValue.BSNO);
                            db.AddInParameter(cmdInsert, "@ISFEE", DbType.Boolean, true);
                            db.AddInParameter(cmdInsert, "@INPUTDATE", DbType.DateTime, DateTime.Now);
                            db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, USERID);
                            db.ExecuteNonQuery(cmdInsert, tran);

                        }
                    }

                    result = new DBResult();
                    result.Success = true;
                    result.Message = "设置完成";

                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();

                    result.Success = false;
                    result.Message = "设置出现错误，请重试或联系系统管理员";

                    return result;
                }
            }

            result.Success = true;
            result.Message = "设置完成";

            return result;
        }
        #endregion

        #region 取消发放
        public static DBResult CancelSetFee(List<MsOpBillProfit> boday, String USERID)
        {
            var result = new DBResult();

            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                try
                {


                    if (boday != null)
                    {


                        var cmdupdate =
                            db.GetSqlStringCommand(
                                   @"update op_sale_proftfee set ISFEE=0,CANCELDATE=@CANCELDATE,CANCELBY=@CANCELBY where BSNO=@BSNO ");

                        foreach (var enumValue in boday)
                        {
                            //if (enumValue.FCOP == "")
                            //{
                            //    cmddelete.Parameters.Clear();
                            //    db.AddInParameter(cmddelete, "@BSNO", DbType.String, enumValue.BSNO);
                            //    db.ExecuteNonQuery(cmddelete, tran);
                            //}
                            //else {
                            cmdupdate.Parameters.Clear();
                            db.AddInParameter(cmdupdate, "@BSNO", DbType.String, enumValue.BSNO);
                            db.AddInParameter(cmdupdate, "@CANCELDATE", DbType.DateTime, DateTime.Now);
                            db.AddInParameter(cmdupdate, "@CANCELBY", DbType.String, USERID);
                            db.ExecuteNonQuery(cmdupdate, tran);
                            //}

                        }
                    }

                    result = new DBResult();
                    result.Success = true;
                    result.Message = "设置完成";

                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();

                    result.Success = false;
                    result.Message = "设置出现错误，请重试或联系系统管理员";

                    return result;
                }
            }

            result.Success = true;
            result.Message = "设置完成";

            return result;
        }
        #endregion

        #region 权限
        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 ");
            strSql.Append("  from VW_User_Authority ");
            strSql.Append("  where [NAME]='modSaleProfitShare' and USERID='" + userid + "' and ISDELETE=0");

            string visiblerange = "4";
            string operaterange = "4";

            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"]);
                    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 == "1")
            {
                str = " UPPER(B.Corpid)='" + companyid + "'";
            }

            return str;
        }
        #endregion

    }
}