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

namespace Dao
{
    /// <summary>
    /// 订单操作
    /// </summary>
    public class GwOrderDao : IDisposable
    {
        public void Dispose()
        {
        }

        private static GwOrderDao _instance;
        public static GwOrderDao Instance
        {
            get {
                if (_instance == null)
                {
                    _instance = new GwOrderDao();
                }
                return _instance;
            }
        }


        //flag: 0-待审核订单(已提交、审核中)；1-已完成订单（审核通过、审核不通过、取消）
        public List<GwOrder> LoadInfoList(string spId, string clientId, string compay, int flag, DateTime startDate, DateTime endDate, int pageSize, int PageIndex, string permissionsSQL, out int recordcount)
        {
            
            List<GwOrder> list = new List<GwOrder>();
            recordcount = 0;
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" FROM ( ");
                stringBuilder.Append("SELECT GWO.*, GWC.CLIENT_NAME, GWC.COMPANY, GWOA.AUDITOR  ");
                stringBuilder.Append(" FROM GW_ORDER GWO ");
                stringBuilder.Append(" LEFT JOIN (select ORDER_ID, wm_concat(SU.USER_NAME) AUDITOR from (select ORDER_ID, AUDITOR FROM GW_ORDER_AUDIT order by ORDER_ID, AUDIT_SORT) GOA LEFT JOIN SYS_USER SU ON SU.ACCOUNT = GOA.AUDITOR group by ORDER_ID ) GWOA ON GWOA.ORDER_ID = GWO.ORDER_ID   ");
                stringBuilder.Append(" LEFT JOIN GW_SP GWS ON GWS.SP_ID = GWO.SP_ID ");
                stringBuilder.Append(" LEFT JOIN GW_CLIENT GWC ON GWC.CLIENT_ID = GWO.CLIENT_ID ");
                stringBuilder.Append(" WHERE 1=1 ");

                if (flag == 0)
                {
                    stringBuilder.Append(" AND GWO.STATUS IN (0, 1) ");
                }
                else if (flag == 1)
                {
                    stringBuilder.Append(" AND GWO.STATUS IN (2, 3, 4) ");
                }

                /**
                if ((startDate != null) && (endDate != null))
                {
                    stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + startDate + " AND " + endDate + " ");
                 }
                else if ((startDate != null) && (endDate == null) )
                {
                    stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + startDate + "'AND " + startDate + "");
                }
                else if ((endDate != null) && (startDate == null) )
                {
                    stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN " + endDate + " AND " + endDate + " ");
                }
                **/
                stringBuilder.Append(" AND GWO.CREATE_TIME BETWEEN :START_TIME AND :END_TIME ");

                if ((spId != null) && (spId != ""))
                {
                    stringBuilder.Append(" AND GWO.SP_ID like '%" + spId + "%' " );
                }

                if ((clientId != null) && (clientId != ""))
                {
                    stringBuilder.Append(" and GWO.CLIENT_ID like '%" + clientId + "%'");
                }
                if ((compay != null) && (compay != ""))
                {
                    stringBuilder.Append(" and GWC.COMPANY like '%" + compay + "%'");
                }
                //客户权限SQL脚本
                stringBuilder.Append(permissionsSQL);

                stringBuilder.Append(" ) T ");

                OracleParameter[] parameterArray = new OracleParameter[] { new OracleParameter(":START_TIME", startDate), new OracleParameter(":END_TIME", endDate) };

                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, parameterArray))
                {
                    while (reader.Read())
                    {
                        recordcount = this.ReadCount(reader);
                    }
                }
                using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString() + " order by CREATE_TIME desc ", OracleHelper.Connection, parameterArray))
                {
                    while (reader2.Read())
                    {
                        GwOrder o = new GwOrder();
                        if (this.ReadInfo(reader2, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// flag: 0-待审核订单(已提交、审核中)；1-已完成订单（审核通过、审核不通过、取消）
        /// <returns></returns>
        public List<GwOrder> List(GwOrder gwOrder, GwSp gwSp, GwClient gwClient, int flag)
        {
            List<GwOrder> list = new List<GwOrder>();
            //recordcount = 0;    //
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT GWO.*  ");
                stringBuilder.Append(" FROM GW_ORDER GWO ");
                stringBuilder.Append(" LEFT JOIN GW_SP GWS ON GWS.SP_ID = GWO.SP_ID ");
                stringBuilder.Append(" LEFT JOIN GW_CLIENT GWC ON GWC.CLIENT_ID = GWO.CLIENT_ID ");
                stringBuilder.Append(" WHERE 1=1 ");

                if(flag == 0)
                {
                    stringBuilder.Append(" AND GWO.STATUS IN (0, 1) ");
                }
                else if (flag == 1)
                {
                    stringBuilder.Append(" AND GWO.STATUS IN (2, 3, 4) ");
                }

                stringBuilder.Append(" ORDER BY CREATE_TIME DESC ");

                using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString() , OracleHelper.Connection))
                {
                    while (((DbDataReader)reader).Read())
                    {
                        GwOrder o = new GwOrder();
                        if (this.ReadInfo(reader, o))
                            list.Add(o);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return list;
            }
            return list;
        }


        //添加信息
        public bool Add(GwOrder o)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO GW_ORDER ( ");
            stringBuilder.Append(" ORDER_ID, SP_ID, CLIENT_ID, PRODUCT_ID, COMBO_ID, BASIC_NUM, GIVING_NUM, PRICE, ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL, PAY_AMOUNT, BEFORE_BALANCE_NUM ");
            stringBuilder.Append(" , CREATOR, CREATE_TIME, STATUS, REMARK ");
            //stringBuilder.Append(" , AUDITOR, AUDIT_TIME ");
            stringBuilder.Append(" ) ");
            stringBuilder.Append(" VALUES (:ORDER_ID, :SP_ID, :CLIENT_ID, :PRODUCT_ID, :COMBO_ID, :BASIC_NUM, :GIVING_NUM, :PRICE, :ORDER_NUM_TOTAL, :ORDER_AMOUNT_TOTAL, :PAY_AMOUNT, :BEFORE_BALANCE_NUM ");
            stringBuilder.Append(" , :CREATOR, :CREATE_TIME, :STATUS, :REMARK ");
            //stringBuilder.Append(" , :AUDITOR, :AUDIT_TIME ");
            stringBuilder.Append("  ) ");

            string sql = stringBuilder.ToString();

            return OracleHelper.ExecuteSql(sql, 
                OracleHelper.Connection,
                new OracleParameter(":ORDER_ID", (object)o.OrderId),
                new OracleParameter(":SP_ID", (object)o.SpId),
                new OracleParameter(":CLIENT_ID", (object)o.ClientId),
                new OracleParameter(":PRODUCT_ID", (object)o.ProductId),
                new OracleParameter(":COMBO_ID", (object)o.ComboId),
                new OracleParameter(":BASIC_NUM", (object)o.BasicNum),
                new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
                new OracleParameter(":PRICE", (object)o.Price),
                new OracleParameter(":ORDER_NUM_TOTAL", (object)o.OrderNumTotal),
                new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)o.OrderAmountTotal),
                new OracleParameter(":PAY_AMOUNT", (object)o.PayAmount),
                new OracleParameter(":BEFORE_BALANCE_NUM", (object)o.BeforeBalanceNum),
                new OracleParameter(":CREATOR", (object)o.Creator),
                new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                new OracleParameter(":STATUS", (object)o.Status),
                new OracleParameter(":REMARK", (object)o.Remark)
                ) > 0;
        }

        //删除
        public bool Delete(string orderId)
        {
            if (string.IsNullOrEmpty(orderId) )
                return false;
            return OracleHelper.ExecuteSql("delete from GW_ORDER where ORDER_ID=:ORDER_ID", OracleHelper.Connection, new OracleParameter(":ORDER_ID", (object)orderId)) > 0;
        }

        //取消
        public bool UpdateStatus(int status, string orderId)
        {
            if (string.IsNullOrEmpty(orderId))
                return false;
            //status:4-取消
            return OracleHelper.ExecuteSql("update GW_ORDER SET STATUS = :STATUS where ORDER_ID=:ORDER_ID", OracleHelper.Connection, new OracleParameter(":STATUS", (object)status), new OracleParameter(":ORDER_ID", (object)orderId)) > 0;
        }

        //更新订单修改前余额
        public bool UpdateBalanceAndStatus(Int64 basicNum, Int64 givingNum, Int64 orderNumTotal, Int64 orderAmountTotal, Int64 payAmount , Int64 beforeGivingNum, int orderStatus, string orderId )
        {
            if (string.IsNullOrEmpty(orderId))
                return false;
            //status:4-取消
            return OracleHelper.ExecuteSql("update GW_ORDER SET BASIC_NUM=:BASIC_NUM, GIVING_NUM=:GIVING_NUM, ORDER_NUM_TOTAL=:ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL=:ORDER_AMOUNT_TOTAL, PAY_AMOUNT=:PAY_AMOUNT, BEFORE_BALANCE_NUM=:BEFORE_BALANCE_NUM, STATUS = :STATUS where ORDER_ID=:ORDER_ID", OracleHelper.Connection
                , new OracleParameter(":BASIC_NUM", (object)basicNum)
                , new OracleParameter(":GIVING_NUM", (object)givingNum)
                , new OracleParameter(":ORDER_NUM_TOTAL", (object)orderNumTotal)
                , new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)orderAmountTotal)
                , new OracleParameter(":PAY_AMOUNT", (object)payAmount)
                , new OracleParameter(":BEFORE_BALANCE_NUM", (object)beforeGivingNum)
                , new OracleParameter(":STATUS", (object)orderStatus)
                , new OracleParameter(":ORDER_ID", (object)orderId)
                ) > 0;
        }

        //更新信息
        public bool Update(GwOrder o)
        {
            OracleParameter[] cmdParms = new OracleParameter[] {
                new OracleParameter(":ORDER_ID", (object)o.OrderId),
                new OracleParameter(":SP_ID", (object)o.SpId),
                new OracleParameter(":CLIENT_ID", (object)o.ClientId),
                new OracleParameter(":PRODUCT_ID", (object)o.ProductId),
                new OracleParameter(":COMBO_ID", (object)o.ComboId),
                new OracleParameter(":BASIC_NUM", (object)o.BasicNum),
                new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
                new OracleParameter(":PRICE", (object)o.Price),
                new OracleParameter(":ORDER_NUM_TOTAL", (object)o.OrderNumTotal),
                new OracleParameter(":ORDER_AMOUNT_TOTAL", (object)o.OrderAmountTotal),
                new OracleParameter(":PAY_AMOUNT", (object)o.PayAmount),
                new OracleParameter(":BEFORE_BALANCE_NUM", (object)o.BeforeBalanceNum),
                new OracleParameter(":CREATOR", (object)o.Creator),
                new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                new OracleParameter(":STATUS", (object)o.Status),
                new OracleParameter(":REMARK", (object)o.Remark)
            };

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("UPDATE GW_ORDER SET ");
            //stringBuilder.Append(" ORDER_ID=:ORDER_ID ");
            stringBuilder.Append(" , SP_ID =:SP_ID ");
            stringBuilder.Append(" , CLIENT_ID =:CLIENT_ID ");
            stringBuilder.Append(" , PRODUCT_ID =:PRODUCT_ID ");
            stringBuilder.Append(" , COMBO_ID =:COMBO_ID ");
            stringBuilder.Append(" , BASIC_NUM=:BASIC_NUM, GIVING_NUM=:GIVING_NUM, PRICE=:PRICE, ORDER_NUM_TOTAL=:ORDER_NUM_TOTAL, ORDER_AMOUNT_TOTAL=:ORDER_AMOUNT_TOTAL ");
            stringBuilder.Append(" , PAY_AMOUNT =:PAY_AMOUNT, BEFORE_BALANCE_NUM =:BEFORE_BALANCE_NUM, CREATOR=:CREATOR, CREATE_TIME=:CREATE_TIME ");
            stringBuilder.Append(" , STATUS=:STATUS, REMARK=:REMARK ");
            stringBuilder.Append(" WHERE ORDER_ID=:ORDER_ID ");

            string sql = stringBuilder.ToString();
            return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0);
        }

        //获取信息
        public GwOrder Get(string orderId)
        {
            GwOrder o = new GwOrder();
            if (string.IsNullOrEmpty(orderId))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select gwo.*, gc.COMPANY, gp.name PRODUCT_NAME from gw_order gwo left join gw_client gc on gc.client_id = gwo.client_id left join gw_product gp on gp.id = gwo.product_id where gwo.ORDER_ID=:ORDER_ID"), OracleHelper.Connection, new OracleParameter(":ORDER_ID", (object)orderId)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //今日已完成订单数及总额
        public List<Dictionary<string, object>> GetOrderDay(string permissionsSQL)
        {
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT COUNT(1) order_num, SUM(order_amount_total) order_amount_total FROM ( ");
            builder.Append(" SELECT a.order_id, a.client_id, a.status, a.order_amount_total ");
            builder.Append(" , CASE WHEN b.update_time is null then TO_CHAR(a.create_time, 'yyyyMMdd') ELSE TO_CHAR(b.update_time, 'yyyyMMdd') END AS update_time ");
            builder.Append(" FROM gw_order a ");
            builder.Append(" LEFT JOIN ( ");
            builder.Append(" SELECT order_id, max(audit_time) update_time FROM gw_order_audit goa ");
            builder.Append(" GROUP BY order_id ");
            builder.Append(" ) b ON b.order_id = a.order_id ");
            builder.Append(" ) ");
            builder.Append(" WHERE 1=1 AND status=2 ");
            //builder.Append(" AND update_time=:UPDATE_TIME ");
            builder.Append(permissionsSQL);

            //using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection, new OracleParameter(":UPDATE_TIME", (object)DateTime.Now.ToString("yyyyMMdd"))))
            using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
                    int orderNum = oracleReaderWrapper.GetInt("ORDER_NUM", 0);
                    int orderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);

                    Dictionary<string, object> map = new Dictionary<string, object>();
                    map.Add("orderNum", orderNum);
                    map.Add("orderAmountTotal", orderAmountTotal);
                    list.Add(map);
                }
            }
            return list;
        }

        //待审核订单数及总额
        public List<Dictionary<string, object>> GetOrderAudit(string permissionsSQL)
        {
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            StringBuilder builder = new StringBuilder();
            builder.Append(" SELECT COUNT(1) order_num, SUM(order_amount_total) order_amount_total FROM ( ");
            builder.Append(" SELECT a.order_id, a.client_id, a.status, a.order_amount_total ");
            //builder.Append(" , CASE WHEN b.update_time is null then TO_CHAR(a.create_time, 'yyyyMMdd') ELSE TO_CHAR(b.update_time, 'yyyyMMdd') END AS update_time ");
            builder.Append(" FROM gw_order a ");
            builder.Append(" LEFT JOIN ( ");
            builder.Append(" SELECT order_id, max(audit_time) update_time FROM gw_order_audit goa ");
            builder.Append(" GROUP BY order_id ");
            builder.Append(" ) b ON b.order_id = a.order_id ");
            builder.Append(" ) ");
            builder.Append(" WHERE 1=1 AND status IN (0,1) ");
            builder.Append(permissionsSQL);

            using (OracleDataReader reader = OracleHelper.ExecuteReader(builder.ToString(), OracleHelper.Connection))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
                    int orderNum = oracleReaderWrapper.GetInt("ORDER_NUM", 0);
                    int orderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);

                    Dictionary<string, object> map = new Dictionary<string, object>();
                    map.Add("orderNum", orderNum);
                    map.Add("orderAmountTotal", orderAmountTotal);
                    list.Add(map);
                }
            }
            return list;
        }

        //统计记录数
        private int ReadCount(OracleDataReader reader)
        {
            OracleReaderWrapper wrapper = new OracleReaderWrapper(reader);
            return wrapper.GetInt("count", 0);
        }

        //数据封装
        private bool ReadInfo(OracleDataReader reader, GwOrder o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.OrderId = oracleReaderWrapper.GetString("ORDER_ID", "");
            o.SpId = oracleReaderWrapper.GetString("SP_ID", "");
            o.ClientId = oracleReaderWrapper.GetString("CLIENT_ID", ""); 
            o.Company = oracleReaderWrapper.GetString("COMPANY", "");
            o.ClientName = oracleReaderWrapper.GetString("CLIENT_NAME", "");
            o.ProductId = oracleReaderWrapper.GetString("PRODUCT_ID", "");
            o.ProductName = oracleReaderWrapper.GetString("PRODUCT_NAME", "");
            o.ComboId = oracleReaderWrapper.GetString("COMBO_ID", "");
            o.BasicNum = oracleReaderWrapper.GetInt("BASIC_NUM", 0);
            o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0);
            o.Price = oracleReaderWrapper.GetInt("PRICE", 0);
            o.OrderNumTotal = oracleReaderWrapper.GetInt("ORDER_NUM_TOTAL", 0);
            o.OrderAmountTotal = oracleReaderWrapper.GetInt("ORDER_AMOUNT_TOTAL", 0);
            o.PayAmount = oracleReaderWrapper.GetInt("PAY_AMOUNT", 0);
            o.BeforeBalanceNum = oracleReaderWrapper.GetInt("BEFORE_BALANCE_NUM", 0);
            o.Creator = oracleReaderWrapper.GetString("CREATOR", "");
            o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
            o.Auditor = oracleReaderWrapper.GetString("AUDITOR", "");   //各级审核人
            //o.AuditTime = oracleReaderWrapper.GetDateTime("AUDIT_TIME");
            o.Status = oracleReaderWrapper.GetInt("STATUS", 0);
            o.Remark = oracleReaderWrapper.GetString("REMARK", "");
            return true;
        }

    }

}