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 GwProductDao : IDisposable
    {
        public void Dispose()
        {
        }

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


        public List<GwProduct> LoadInfoList(int classes, string name, int pageSize, int PageIndex, out int recordcount)
        {
            
            List<GwProduct> list = new List<GwProduct>();
            recordcount = 0;
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from GW_PRODUCT where 1=1");
                if (classes == -1)
                {
                    builder.Append(" AND (CLASSES != " + classes + " OR CLASSES IS NULL) " );
                }
                else
                {
                    builder.Append(" AND CLASSES = " + classes + " ");
                }
                if ((name != null) && (name != ""))
                {
                    builder.Append(" and name like '%" + name + "%'");
                }
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection))
                {
                    while (reader.Read())
                    {
                        recordcount = this.ReadCount(reader);
                    }
                }
                using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString() + " order by path, sort ", OracleHelper.Connection ))
                {
                    while (reader2.Read())
                    {
                        GwProduct o = new GwProduct();
                        if (this.ReadInfo(reader2, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

        /// <summary>
        /// 获取产品及产品分类列表
        /// </summary>
        /// <param name="clientID"></param>
        /// <returns></returns>
        public List<GwProduct> GwProductList(int classes, string name )
        {
            List<GwProduct> list = new List<GwProduct>();
            //recordcount = 0;    //
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM GW_PRODUCT WHERE CLASSES=:CLASSES AND NAME=:NAME ORDER BY ID ");
                OracleParameter[] oracleParameterArray = new OracleParameter[2]
                {
                    new OracleParameter(":CLASSES", (object) classes), new OracleParameter(":NAME", (object) ("%" + name + "%")) 
                };


                //using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection, cmdParms))
                //{
                //    while (reader.Read())
                //    {
                //        recordcount = this.ReadCount(reader);
                //    }
                //}

                using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString() +" order by path, sort ", OracleHelper.Connection, oracleParameterArray))
                {
                    while (((DbDataReader)reader).Read())
                    {
                        GwProduct o = new GwProduct();
                        if (this.ReadInfo(reader, o))
                            list.Add(o);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return list;
            }
            return list;
        }

        //根据条件获取产品或产品分类的ID和名称
        public List<GwProduct> getAllList(GwProduct bean)
        {

            List<GwProduct> list = new List<GwProduct>();
            try
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("from GW_PRODUCT where 1=1");
                if (bean.Classes == -1)
                {
                    builder.Append(" AND (CLASSES != " + bean.Classes + " OR CLASSES IS NULL) ");
                }
                else
                {
                    builder.Append(" AND CLASSES = " + bean.Classes + " ");
                }

                if (!string.IsNullOrEmpty(bean.ParentId) )
                {
                    builder.Append(" AND PARENT_ID = " + bean.ParentId + " ");
                }

                if (bean.IsDefault == -1)
                {
                    builder.Append(" AND (IS_DEFAULT != " + bean.IsDefault + " OR IS_DEFAULT IS NULL) ");
                }
                else
                {
                    builder.Append(" AND IS_DEFAULT = " + bean.IsDefault + " ");
                }

                if (bean.IsEnable == -1)
                {
                    builder.Append(" AND (IS_ENABLE != " + bean.IsEnable + " OR IS_ENABLE IS NULL) ");
                }
                else
                {
                    builder.Append(" AND IS_ENABLE = " + bean.IsEnable + " ");
                }

                using (OracleDataReader reader = OracleHelper.ExecuteReader("select id, name, classes, is_enable, is_default " + builder.ToString() + " order by path, sort ", OracleHelper.Connection))
                {
                    while (reader.Read())
                    {
                        GwProduct o = new GwProduct();
                        if (this.ReadInfo(reader, o))
                        {
                            list.Add(o);
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Error(exception);
                return list;
            }
            return list;

        }

        /// <summary>
        /// 查询启用的默认的产品
        /// </summary>
        /// <param name="clientID"></param>
        /// <returns></returns>
        public List<string> GetDefaultProduct()
        {
            List<string> list = new List<string>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select id from GW_PRODUCT t where is_default=1 and is_enable=1 and classes=1"), OracleHelper.Connection))
            {
                while (((DbDataReader)reader).Read())
                {
                    string id = reader.GetString(0);
                    if(!string.IsNullOrEmpty(id))
                    {
                        list.Add(id);
                    }
                    
                }
            }
            return list;
        }

        //添加产品或产品分类信息
        public bool Add(GwProduct o)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("INSERT INTO GW_PRODUCT ( ");
            stringBuilder.Append(" ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, ROUTER_TYPE, CM_OP_ID, CU_OP_ID, CT_OP_ID ");
            stringBuilder.Append(" , CM_GROUPID, CU_GROUPID, CT_GROUPID, CM_EXT_PARAMS, CU_EXT_PARAMS, CT_EXT_PARAMS, PRICE, GIVING_NUM, REMARK, CREATE_TIME, CREATOR " );
            //stringBuilder.Append(" , UPDATE_TIME, UPDATEOR ");
            stringBuilder.Append(" ) ");
            stringBuilder.Append(" VALUES (:ID, :NAME, :ICON, :INSTRUCTIONS, :CLASSES, :PARENT_ID, :PATH, :IS_DEFAULT, :IS_ENABLE, :SORT, :ROUTER_TYPE, :CM_OP_ID, :CU_OP_ID, :CT_OP_ID " );
            stringBuilder.Append(" , :CM_GROUPID, :CU_GROUPID, :CT_GROUPID, :CM_EXT_PARAMS, :CU_EXT_PARAMS, :CT_EXT_PARAMS, :PRICE, :GIVING_NUM, :REMARK, :CREATE_TIME, :CREATOR " );
            //stringBuilder.Append(" , :UPDATE_TIME, :UPDATEOR ");
            stringBuilder.Append("  ) ");

            string sql = stringBuilder.ToString();

            return OracleHelper.ExecuteSql(sql, 
                OracleHelper.Connection,
                new OracleParameter(":ID", (object)o.Id),
                new OracleParameter(":NAME", (object)o.Name),
                new OracleParameter(":ICON", (object)o.Icon),
                new OracleParameter(":INSTRUCTIONS", (object)o.Instructions),
                new OracleParameter(":CLASSES", (object)o.Classes),
                new OracleParameter(":PARENT_ID", (object)o.ParentId),
                new OracleParameter(":PATH", (object)o.Path),
                new OracleParameter(":IS_DEFAULT", (object)o.IsDefault),
                new OracleParameter(":IS_ENABLE", (object)o.IsEnable),
                new OracleParameter(":SORT", (object)o.Sort),
                new OracleParameter(":ROUTER_TYPE", (object)o.RouterType),
                new OracleParameter(":CM_OP_ID", (object)o.CmOpId),
                new OracleParameter(":CU_OP_ID", (object)o.CuOpId),
                new OracleParameter(":CT_OP_ID", (object)o.CtOpId),
                new OracleParameter(":CM_GROUPID", (object)o.CmGroupid),
                new OracleParameter(":CU_GROUPID", (object)o.CuGroupid),
                new OracleParameter(":CT_GROUPID", (object)o.CtGroupid),
                new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams),
                new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams),
                new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams),
                new OracleParameter(":PRICE", (object)o.Price),
                new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
                new OracleParameter(":REMARK", (object)o.Remark),
                new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                new OracleParameter(":CREATOR", (object)o.Creator)
                //, new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime)
                //, new OracleParameter(":UPDATEOR", (object)o.Updateor)
                ) > 0;
        }

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

        //更新产品或产品分类信息
        public bool Update(GwProduct o)
        {
            OracleParameter[] cmdParms = new OracleParameter[] {
                new OracleParameter(":ID", (object)o.Id),
                new OracleParameter(":NAME", (object)o.Name),
                new OracleParameter(":ICON", (object)o.Icon),
                new OracleParameter(":INSTRUCTIONS", (object)o.Instructions),
                new OracleParameter(":CLASSES", (object)o.Classes),
                new OracleParameter(":PARENT_ID", (object)o.ParentId),
                new OracleParameter(":PATH", (object)o.Path),
                new OracleParameter(":IS_DEFAULT", (object)o.IsDefault),
                new OracleParameter(":IS_ENABLE", (object)o.IsEnable),
                new OracleParameter(":SORT", (object)o.Sort),
                new OracleParameter(":ROUTER_TYPE", (object)o.RouterType),
                new OracleParameter(":CM_OP_ID", (object)o.CmOpId),
                new OracleParameter(":CU_OP_ID", (object)o.CuOpId),
                new OracleParameter(":CT_OP_ID", (object)o.CtOpId),
                new OracleParameter(":CM_GROUPID", (object)o.CmGroupid),
                new OracleParameter(":CU_GROUPID", (object)o.CuGroupid),
                new OracleParameter(":CT_GROUPID", (object)o.CtGroupid),
                new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams),
                new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams),
                new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams),
                new OracleParameter(":PRICE", (object)o.Price),
                new OracleParameter(":GIVING_NUM", (object)o.GivingNum),
                new OracleParameter(":REMARK", (object)o.Remark),
                //new OracleParameter(":CREATE_TIME", (object)o.CreateTime),
                //new OracleParameter(":CREATOR", (object)o.Creator),
                new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime),
                new OracleParameter(":UPDATEOR", (object)o.Updateor)
            };

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("UPDATE GW_PRODUCT SET ");
            stringBuilder.Append(" NAME=:NAME ");
            stringBuilder.Append(" , ICON =:ICON ");
            stringBuilder.Append(" , INSTRUCTIONS =:INSTRUCTIONS ");
            stringBuilder.Append(" , CLASSES =:CLASSES ");
            stringBuilder.Append(" , PARENT_ID =:PARENT_ID ");
            stringBuilder.Append(" , PATH=:PATH, IS_DEFAULT=:IS_DEFAULT, IS_ENABLE=:IS_ENABLE, SORT=:SORT, ROUTER_TYPE=:ROUTER_TYPE ");
            stringBuilder.Append(" , CM_OP_ID =:CM_OP_ID, CU_OP_ID=:CU_OP_ID, CT_OP_ID=:CT_OP_ID ");
            stringBuilder.Append(" , CM_GROUPID=:CM_GROUPID, CU_GROUPID=:CU_GROUPID, CT_GROUPID=:CT_GROUPID, CM_EXT_PARAMS=:CM_EXT_PARAMS ");
            stringBuilder.Append(" , CU_EXT_PARAMS =:CU_EXT_PARAMS, CT_EXT_PARAMS=:CT_EXT_PARAMS, PRICE=:PRICE, GIVING_NUM=:GIVING_NUM, REMARK=:REMARK ");
            //stringBuilder.Append(" , CREATE_TIME =:CREATE_TIME, CREATOR=:CREATOR ");
            stringBuilder.Append(" , UPDATE_TIME=:UPDATE_TIME, UPDATEOR=:UPDATEOR ");
            stringBuilder.Append(" WHERE ID=:ID ");

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

        //获取信息
        public GwProduct Get(string productId)
        {
            GwProduct o = new GwProduct();
            if (string.IsNullOrEmpty(productId))
                return o;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where ID=:ID"), OracleHelper.Connection, new OracleParameter(":ID", (object)productId)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //更新启用或停用
        public bool UpdateStatus(string id, string isEnable)
        {
            if (string.IsNullOrEmpty(id))
                return false;
            return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET IS_ENABLE=:IS_ENABLE where ID=:ID", OracleHelper.Connection, new OracleParameter(":IS_ENABLE", (object)isEnable), new OracleParameter(":ID", (object)id)) > 0;
        }

        //更新关联的产品路径PATH
        public bool UpdatePath(string id, string path)
        {
            if (string.IsNullOrEmpty(id))
                return false;
            if (string.IsNullOrEmpty(path))
                return false;

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("UPDATE GW_PRODUCT SET PATH = :PATH||','||PARENT_ID ");
            stringBuilder.Append(" WHERE ID IN ( ");
            stringBuilder.Append(" SELECT ID FROM GW_PRODUCT START WITH ID = :ID  CONNECT BY PRIOR ID = PARENT_ID ");
            stringBuilder.Append(" ) ");
            stringBuilder.Append(" AND ID NOT IN (:ID) ");

            return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":PATH", (object)path), new OracleParameter(":ID", (object)id), new OracleParameter(":ID", (object)id)) > 0;
        }

        //判断是否在产品中使用
        public int GwProductUseCount(string productId)
        {
            string str = string.Format("SELECT count(*) as Count  from GW_PRODUCT where PARENT_ID=:PARENT_ID ");
            List<OracleParameter> list = new List<OracleParameter>();
            int num = 0;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PARENT_ID", (object)productId)) )
            {
                while (((DbDataReader)reader).Read())
                    num = this.ReadCount(reader);
            }
            return num;
        }

        //判断是否在客户账号中使用
        public int GwSpUseCount(string productId)
        {
            string str = string.Format("SELECT count(*) as Count  from GW_SP where PRODUCT_ID=:PRODUCT_ID ");
            List<OracleParameter> list = new List<OracleParameter>();
            int num = 0;
            using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PRODUCT_ID", (object)productId)))
            {
                while (((DbDataReader)reader).Read())
                    num = this.ReadCount(reader);
            }
            return num;
        }

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

        //数据封装
        private bool ReadInfo(OracleDataReader reader, GwProduct o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.Id = oracleReaderWrapper.GetString("ID", "");
            o.Name = oracleReaderWrapper.GetString("NAME", "");
            o.Icon = oracleReaderWrapper.GetString("ICON", "");
            o.Instructions = oracleReaderWrapper.GetString("INSTRUCTIONS", "");
            o.Classes = oracleReaderWrapper.GetInt("CLASSES", 0);
            o.ParentId = oracleReaderWrapper.GetString("PARENT_ID", "");
            o.Path = oracleReaderWrapper.GetString("PATH", "");
            o.IsDefault = oracleReaderWrapper.GetInt("IS_DEFAULT", 0);
            o.IsEnable = oracleReaderWrapper.GetInt("IS_ENABLE", 0);
            o.Sort = oracleReaderWrapper.GetInt("SORT", 0);
            o.RouterType = oracleReaderWrapper.GetInt("ROUTER_TYPE", 0);
            o.CmOpId = oracleReaderWrapper.GetInt("CM_OP_ID", 0);
            o.CuOpId = oracleReaderWrapper.GetInt("CU_OP_ID", 0);
            o.CtOpId = oracleReaderWrapper.GetInt("CT_OP_ID", 0);
            o.CmGroupid = oracleReaderWrapper.GetInt("CM_GROUPID", 0);
            o.CuGroupid = oracleReaderWrapper.GetInt("CU_GROUPID", 0);
            o.CtGroupid = oracleReaderWrapper.GetInt("CT_GROUPID", 0);
            o.CmExtParams = oracleReaderWrapper.GetString("CM_EXT_PARAMS", "");
            o.CuExtParams = oracleReaderWrapper.GetString("CU_EXT_PARAMS", "");
            o.CtExtParams = oracleReaderWrapper.GetString("CT_EXT_PARAMS", "");
            o.Price = oracleReaderWrapper.GetInt("PRICE", 0);
            o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0);
            o.Remark = oracleReaderWrapper.GetString("REMARK", "");
            o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME");
            o.Creator = oracleReaderWrapper.GetString("CREATOR", "");
            o.UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME");
            o.Updateor = oracleReaderWrapper.GetString("UPDATEOR", "");
            return true;
        }

    }

}