//add新加2017-10-23 利润统计
using Common;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data.Common;

namespace Dao
{
    public class GwStatisProfitDao : IDisposable
    {
        public List<GwStatisProfitItem> Query(DateTime startTime, DateTime endTime, string spID, string opID, string timeExpression, StatisOption so)
        {
            List<GwStatisProfitItem> list = new List<GwStatisProfitItem>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2},\r\n            SUM(AP_FEE0) AP_FEE0, \r\n            SUM(AP_FEE1) AP_FEE1, \r\n            SUM(AP_FEE2) AP_FEE2, \r\n            SUM(AP_FEE3) AP_FEE3, \r\n            SUM(AP_FEE4) AP_FEE4, \r\n            SUM(AP_FEE5) AP_FEE5,\r\n            SUM(OP_FEE0) OP_FEE0,  \r\n            SUM(OP_FEE1) OP_FEE1,\r\n            SUM(OP_FEE2) OP_FEE2,\r\n            SUM(OP_FEE3) OP_FEE3,\r\n            SUM(OP_FEE4) OP_FEE4,\r\n            SUM(OP_FEE5) OP_FEE5\r\n            FROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (OP_ID=:OP_ID OR :OP_ID IS NULL)  AND STATIS_TIME BETWEEN :START_TIME AND :END_TIME GROUP BY TO_CHAR(STATIS_TIME,'{0}') {1}{2} ORDER BY 1 ASC", (object)timeExpression, (so & StatisOption.OPID) == StatisOption.OPID ? (object)",OP_ID" : (object)"", (so & StatisOption.SpID) == StatisOption.SpID ? (object)",SP_ID" : (object)""), OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":OP_ID", (object)opID)))
            {
                GwStatisProfitItem statisProfitItem1 = new GwStatisProfitItem();
                statisProfitItem1.Title = "合计";
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    GwStatisProfitItem statisProfitItem2 = new GwStatisProfitItem();
                    OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
                    statisProfitItem2.Title = oracleReaderWrapper.GetString("TITLE", "");
                    statisProfitItem2.OPID = oracleReaderWrapper.GetInt("OP_ID", 0);
                    statisProfitItem2.SpID = oracleReaderWrapper.GetString("SP_ID", "");
                    statisProfitItem2.APFee0 = oracleReaderWrapper.GetInt("AP_Fee0", 0);
                    statisProfitItem2.APFee1 = oracleReaderWrapper.GetInt("AP_Fee1", 0);
                    statisProfitItem2.APFee2 = oracleReaderWrapper.GetInt("AP_Fee2", 0);
                    statisProfitItem2.APFee3 = oracleReaderWrapper.GetInt("AP_Fee3", 0);
                    statisProfitItem2.APFee4 = oracleReaderWrapper.GetInt("AP_Fee4", 0);
                    statisProfitItem2.APFee5 = oracleReaderWrapper.GetInt("AP_Fee5", 0);
                    statisProfitItem2.OPFee0 = oracleReaderWrapper.GetInt("OP_Fee0", 0);
                    statisProfitItem2.OPFee1 = oracleReaderWrapper.GetInt("OP_Fee1", 0);
                    statisProfitItem2.OPFee2 = oracleReaderWrapper.GetInt("OP_Fee2", 0);
                    statisProfitItem2.OPFee3 = oracleReaderWrapper.GetInt("OP_Fee3", 0);
                    statisProfitItem2.OPFee4 = oracleReaderWrapper.GetInt("OP_Fee4", 0);
                    statisProfitItem2.OPFee5 = oracleReaderWrapper.GetInt("OP_Fee5", 0);
                    list.Add(statisProfitItem2);
                    statisProfitItem1.APFee0 += statisProfitItem2.APFee0;
                    statisProfitItem1.APFee1 += statisProfitItem2.APFee1;
                    statisProfitItem1.APFee2 += statisProfitItem2.APFee2;
                    statisProfitItem1.APFee3 += statisProfitItem2.APFee3;
                    statisProfitItem1.APFee4 += statisProfitItem2.APFee4;
                    statisProfitItem1.APFee5 += statisProfitItem2.APFee5;
                    statisProfitItem1.OPFee0 += statisProfitItem2.OPFee0;
                    statisProfitItem1.OPFee1 += statisProfitItem2.OPFee1;
                    statisProfitItem1.OPFee2 += statisProfitItem2.OPFee2;
                    statisProfitItem1.OPFee3 += statisProfitItem2.OPFee3;
                    statisProfitItem1.OPFee4 += statisProfitItem2.OPFee4;
                    statisProfitItem1.OPFee5 += statisProfitItem2.OPFee5;
                }
                list.Add(statisProfitItem1);
            }
            return list;
        }

        public void Dispose()
        {
        }
    }
}