using Common;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace Dao
{
  public class GwStatisV3Dao : IDisposable
  {
    public DataTable Query(string clientID)
    {
      return OracleHelper.Execute("SELECT * FROM\r\n(\r\nselect STATIS_TIME,\r\nNVL(SUM(AP_STATUS0),0) as AP_STATUS0,\r\nNVL(SUM(AP_STATUS1),0) as AP_STATUS1,\r\nNVL(SUM(AP_STATUS2),0) as AP_STATUS2,\r\nNVL(SUM(AP_STATUS3),0) as AP_STATUS3,\r\nNVL(SUM(AP_STATUS4),0) as AP_STATUS4,\r\nNVL(SUM(AP_STATUS5),0) as AP_STATUS5\r\n from GW_SM_STATIS_V3 where CLIENT_ID=:CLIENT_ID GROUP BY STATIS_TIME ORDER BY STATIS_TIME DESC) WHERE ROWNUM < 30 ORDER BY STATIS_TIME ASC", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID));
    }

    //public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, StatisOption so)
    public List<GwStatisV3Item> Query(DateTime startTime, DateTime endTime, string clientID, string spID, string apID, string opID, string timeExpression, string permissionsSQL, StatisOption so)
    {
      List<GwStatisV3Item> list = new List<GwStatisV3Item>();
            //using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_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}{3}{4} ORDER BY 1 ASC", (object) timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object) ",AP_ID" : (object) "", (so & StatisOption.OPID) == StatisOption.OPID ? (object) ",OP_ID" : (object) "", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object) ",CLIENT_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(":CLIENT_ID", (object) clientID), new OracleParameter(":AP_ID", (object) apID), new OracleParameter(":OP_ID", (object) opID)))


            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(string.Format("SELECT TO_CHAR(STATIS_TIME,'{0}') AS TITLE {1}{2}{3}{4},\r\nSUM(AP_STATUS0) AP_STATUS0,\r\nSUM(AP_STATUS1) AP_STATUS1,\r\nSUM(AP_STATUS2) AP_STATUS2,\r\nSUM(AP_STATUS3) AP_STATUS3,\r\nSUM(AP_STATUS4) AP_STATUS4,\r\nSUM(AP_STATUS5) AP_STATUS5,\r\nSUM(OP_STATUS0) OP_STATUS0,\r\nSUM(OP_STATUS1) OP_STATUS1,\r\nSUM(OP_STATUS2) OP_STATUS2,\r\nSUM(OP_STATUS3) OP_STATUS3,\r\nSUM(OP_STATUS4) OP_STATUS4,\r\nSUM(OP_STATUS5) OP_STATUS5 \r\nFROM GW_SM_STATIS_V3 WHERE (SP_ID=:SP_ID OR :SP_ID IS NULL) AND (CLIENT_ID=:CLIENT_ID OR :CLIENT_ID IS NULL) AND (AP_ID=:AP_ID OR :AP_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}{3}{4} ", (object)timeExpression, (so & StatisOption.APID) == StatisOption.APID ? (object)",AP_ID" : (object)"", (so & StatisOption.OPID) == StatisOption.OPID ? (object)",OP_ID" : (object)"", (so & StatisOption.ClientID) == StatisOption.ClientID ? (object)",CLIENT_ID" : (object)"", (so & StatisOption.SpID) == StatisOption.SpID ? (object)",SP_ID" : (object)"") );

            //客户权限SQL脚本
            stringBuilder.Append(permissionsSQL);

            stringBuilder.Append(" ORDER BY 1 ASC ");

            using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":START_TIME", (object)startTime), new OracleParameter(":END_TIME", (object)endTime), new OracleParameter(":SP_ID", (object)spID), new OracleParameter(":CLIENT_ID", (object)clientID), new OracleParameter(":AP_ID", (object)apID), new OracleParameter(":OP_ID", (object)opID)))
            {
        GwStatisV3Item gwStatisV3Item1 = new GwStatisV3Item();
        gwStatisV3Item1.Title = "合计";
        while (reader != null && ((DbDataReader) reader).Read())
        {
          GwStatisV3Item gwStatisV3Item2 = new GwStatisV3Item();
          OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
          gwStatisV3Item2.Title = oracleReaderWrapper.GetString("TITLE", "");
          gwStatisV3Item2.APID = oracleReaderWrapper.GetInt("AP_ID", 0);
          gwStatisV3Item2.OPID = oracleReaderWrapper.GetInt("OP_ID", 0);
          gwStatisV3Item2.SpID = oracleReaderWrapper.GetString("SP_ID", "");
          gwStatisV3Item2.ClientID = oracleReaderWrapper.GetString("CLIENT_ID", "");
          gwStatisV3Item2.APStatus0 = oracleReaderWrapper.GetInt("AP_STATUS0", 0);
          gwStatisV3Item2.APStatus1 = oracleReaderWrapper.GetInt("AP_STATUS1", 0);
          gwStatisV3Item2.APStatus2 = oracleReaderWrapper.GetInt("AP_STATUS2", 0);
          gwStatisV3Item2.APStatus3 = oracleReaderWrapper.GetInt("AP_STATUS3", 0);
          gwStatisV3Item2.APStatus4 = oracleReaderWrapper.GetInt("AP_STATUS4", 0);
          gwStatisV3Item2.APStatus5 = oracleReaderWrapper.GetInt("AP_STATUS5", 0);
          gwStatisV3Item2.OPStatus0 = oracleReaderWrapper.GetInt("OP_STATUS0", 0);
          gwStatisV3Item2.OPStatus1 = oracleReaderWrapper.GetInt("OP_STATUS1", 0);
          gwStatisV3Item2.OPStatus2 = oracleReaderWrapper.GetInt("OP_STATUS2", 0);
          gwStatisV3Item2.OPStatus3 = oracleReaderWrapper.GetInt("OP_STATUS3", 0);
          gwStatisV3Item2.OPStatus4 = oracleReaderWrapper.GetInt("OP_STATUS4", 0);
          gwStatisV3Item2.OPStatus5 = oracleReaderWrapper.GetInt("OP_STATUS5", 0);
          list.Add(gwStatisV3Item2);
          gwStatisV3Item1.APStatus0 += gwStatisV3Item2.APStatus0;
          gwStatisV3Item1.APStatus1 += gwStatisV3Item2.APStatus1;
          gwStatisV3Item1.APStatus2 += gwStatisV3Item2.APStatus2;
          gwStatisV3Item1.APStatus3 += gwStatisV3Item2.APStatus3;
          gwStatisV3Item1.APStatus4 += gwStatisV3Item2.APStatus4;
          gwStatisV3Item1.APStatus5 += gwStatisV3Item2.APStatus5;
          gwStatisV3Item1.OPStatus0 += gwStatisV3Item2.OPStatus0;
          gwStatisV3Item1.OPStatus1 += gwStatisV3Item2.OPStatus1;
          gwStatisV3Item1.OPStatus2 += gwStatisV3Item2.OPStatus2;
          gwStatisV3Item1.OPStatus3 += gwStatisV3Item2.OPStatus3;
          gwStatisV3Item1.OPStatus4 += gwStatisV3Item2.OPStatus4;
          gwStatisV3Item1.OPStatus5 += gwStatisV3Item2.OPStatus5;
        }
        list.Add(gwStatisV3Item1);
      }
      return list;
    }

    public List<GwRealtimeStatisItemOut> GetRealtimeStatis5mOut()
    {
      List<GwRealtimeStatisItemOut> list = new List<GwRealtimeStatisItemOut>();
      using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select A.*,NVL(b.OP_OUT_COUNT,0) OP_OUT_COUNT,NVL(B.OP_SUCCESS_COUNT,0) OP_SUCCESS_COUNT,NVL(b.OP_FAILED_COUNT,0) OP_FAILED_COUNT FROM GW_OP A LEFT JOIN\r\n                    (select OP_ID,NVL(SUM(OP_FEE_COUNT),0) OP_OUT_COUNT,\r\n\t                    NVL(SUM(CASE WHEN STATUS=4 THEN OP_FEE_COUNT ELSE 0 END),0) OP_SUCCESS_COUNT,\r\n\t                    NVL(SUM(CASE WHEN STATUS=5 THEN OP_FEE_COUNT ELSE 0 END),0) OP_FAILED_COUNT\r\n                    from LG_SM_{0} where OP_SUBMIT_TIME > SYSDATE - 5.0/1440 GROUP BY OP_ID) b ON A.OP_ID = b.OP_ID WHERE a.OP_STATUS=1 ORDER BY a.OP_ID ASC", (object) DateTime.Now.ToString("yyyyMMdd")), OracleHelper.Connection))
      {
        while (reader != null && ((DbDataReader) reader).Read())
        {
          OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
          int int1 = oracleReaderWrapper.GetInt("OP_ID", 0);
          int int2 = oracleReaderWrapper.GetInt("OP_OUT_COUNT", 0);
          int int3 = oracleReaderWrapper.GetInt("OP_SUCCESS_COUNT", 0);
          int int4 = oracleReaderWrapper.GetInt("OP_FAILED_COUNT", 0);
          JToken jtoken = JsonConvert.DeserializeObject<JToken>(oracleReaderWrapper.GetString("ALARM_SETTING", ""));
          int num1 = 0;
          int num2 = 0;
          int num3 = 0;
          if (jtoken != null)
          {
            num1 = jtoken.Value<int>((object) "opOutThreshold");
            num2 = jtoken.Value<int>((object) "opSuccessRatioThreshold");
            num3 = jtoken.Value<int>((object) "opReportRatioThreshold");
          }
          list.Add(new GwRealtimeStatisItemOut()
          {
            OpID = int1,
            OpName = oracleReaderWrapper.GetString("OP_NAME", ""),
            OpOutCount = int2,
            OpSuccessCount = int3,
            OpFailedCount = int4,
            OpOutThreshold = num1,
            OpSuccessRatioThreshold = num2,
            OpReportRatioThreshold = num3
          });
        }
      }
      return list;
    }

    public List<GwRealtimeStatisItemIn> GetRealtimeStatis5mIn()
    {
      List<GwRealtimeStatisItemIn> list = new List<GwRealtimeStatisItemIn>();
      using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select A.*,NVL(B.AP_IN_COUNT,0) AP_IN_COUNT,NVL(b.AP_PENDING_COUNT,0) AP_PENDING_COUNT FROM GW_AP A LEFT JOIN\r\n                        (SELECT AP_ID,NVL(SUM(AP_FEE_COUNT),0) AP_IN_COUNT, NVL(SUM(CASE WHEN STATUS=0 THEN AP_FEE_COUNT ELSE 0 END),0) AP_PENDING_COUNT FROM LG_SM_{0} where AP_SUBMIT_TIME > SYSDATE - 5.0/1440 GROUP BY AP_ID) b ON A.AP_ID = b.AP_ID", (object) DateTime.Now.ToString("yyyyMMdd")), OracleHelper.Connection))
      {
        while (reader != null && ((DbDataReader) reader).Read())
        {
          OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
          int int1 = oracleReaderWrapper.GetInt("AP_ID", 0);
          int int2 = oracleReaderWrapper.GetInt("AP_IN_COUNT", 0);
          int int3 = oracleReaderWrapper.GetInt("AP_PENDING_COUNT", 0);
          JToken jtoken = JsonConvert.DeserializeObject<JToken>(oracleReaderWrapper.GetString("ALARM_SETTING", ""));
          int num1 = 0;
          int num2 = int.MaxValue;
          if (jtoken != null)
          {
            num1 = jtoken.Value<int>((object) "apInThreshold");
            num2 = jtoken.Value<int>((object) "apPendingThreshold");
          }
          list.Add(new GwRealtimeStatisItemIn()
          {
            ApID = int1,
            ApName = oracleReaderWrapper.GetString("AP_NAME", ""),
            ApInCount = int2,
            ApPendingCount = int3,
            ApInThreshold = num1,
            ApPendingThreshold = num2
          });
        }
      }
      return list;
    }

    public void Dispose()
    {
    }

    public int GetTotalCount(string clientID, DateTime dateTime)
    {
      return Convert.ToInt32(OracleHelper.ExecuteScalar("SELECT NVL(SUM(AP_STATUS0 + AP_STATUS1 + AP_STATUS2 + AP_STATUS3 + AP_STATUS4 + AP_STATUS5),0) FROM  GW_SM_STATIS_V3 WHERE CLIENT_ID=:CLIENT_ID AND STATIS_TIME >= :STATIS_TIME", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID), new OracleParameter(":STATIS_TIME", (object) dateTime)));
    }

    public DataTable GetClientCount(string clientID)
    {
      return OracleHelper.Execute("select sp_id from GW_SM_STATIS_V3 where CLIENT_ID=:CLIENT_ID group by sp_id", OracleHelper.Connection, new OracleParameter(":CLIENT_ID", (object) clientID));
    }
  }
}