// Decompiled with JetBrains decompiler
// Type: Dao.GwStatisV3Dao
// Assembly: Dao, Version=3.2.1.428, Culture=neutral, PublicKeyToken=c1a16487c920c3dc
// MVID: 22E6EB3F-6D25-4B57-BE09-AD2F5391CFA5
// Assembly location: C:\Users\Administrator\Desktop\wwww\bin\Dao.dll

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 Model;
using System.Text;

namespace Dao
{
  public class GwProductDao : IDisposable
  {

        public List<GwProduct> LoadInfoList(string name, string classes, string is_default, string op_group_id, int pageIndex, int pageSize, out int recordCount)
        {
            List<GwProduct> list1 = new List<GwProduct>();
            recordCount = 0;
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("from GW_PRODUCT where 1=1");
            List<OracleParameter> list2 = new List<OracleParameter>();
            if (name != null && name != "")
            {
                list2.Add(new OracleParameter(":name", (object)name));
                stringBuilder.Append(" and name=:name");
            }
            if (classes != null && classes != "")
            {
                list2.Add(new OracleParameter(":classes", (object)int.Parse(classes)) );
                stringBuilder.Append(" and classes=:classes");
            }
            if (is_default != null && is_default != "")
            {
                list2.Add(new OracleParameter(":is_default", (object)int.Parse(is_default)));
                stringBuilder.Append(" and is_default=:is_default");
            }
            if (op_group_id != null && op_group_id != "")
            {
                list2.Add(new OracleParameter(":op_group_id", (object)int.Parse(op_group_id)));
                stringBuilder.Append(" and op_group_id=:op_group_id");
            }

            stringBuilder.Append(" order by PATH ");
            using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, list2.ToArray()))
            {
                while (((DbDataReader)reader).Read())
                    recordCount = this.ReadCount(reader);
            }
            using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, list2.ToArray()))
            {
                while (((DbDataReader)reader).Read())
                {
                    GwProduct o = new GwProduct();
                    if (this.ReadInfo(reader, o))
                        list1.Add(o);
                }
            }
            return list1;
        }

        private int ReadCount(OracleDataReader reader)
        {
            int @int;
            try
            {
                @int = new OracleReaderWrapper(reader).GetInt("count", 0);
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return 0;
            }
            return @int;
        }


        public DataTable getClientProduct(string clientID)
        {
            string sql = "";
            sql += "select aa.*, c.balance, (c.basic_num + c.giving_num ) available_num from "
                + "( "
                + " select a.id client_product_id, a.client_id, a.sp_id, a.activate_status, "
                + " b.id product_id, b.name, b.icon, b.instructions, b.classes, b.parent_id, "
                + " b.path, b.is_default, b.is_enable, b.sort, b.giving_num, b.remark "
                + " from gw_client_product a, gw_product b "
                + " where a.product_id = b.id "
                + " ) aa "
                + " left join gw_sp c "
                + " on aa.sp_id = c.id and aa.client_id = :clientId ";  //参数：客户ID

            return OracleHelper.Execute(sql, OracleHelper.Connection, new OracleParameter(":clientId", (object)clientID));
        }

        /**
         * 获取产品分类列表
         * */
        public List<GwProduct> getGwProductClassList()
        {
            List<GwProduct> list = new List<GwProduct>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where IS_ENABLE = 1 AND CLASSES=0 "), OracleHelper.Connection ))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    GwProduct o = new GwProduct();
                    if (this.ReadInfo(reader, o))
                        list.Add(o);
                }
            }
            return list;
        }

        /**
         * 获取产品列表
         * */
        public List<GwProduct> getGwProductList()
        {
            List<GwProduct> list = new List<GwProduct>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where IS_ENABLE = 1 AND CLASSES=1 "), OracleHelper.Connection))
            {
                while (reader != null && ((DbDataReader)reader).Read())
                {
                    GwProduct o = new GwProduct();
                    if (this.ReadInfo(reader, o))
                        list.Add(o);
                }
            }
            return list;
        }

        /**
         * 根据产品ID获取产品信息
         * */
        public GwProduct find(string productId)
        {
            GwProduct o = new GwProduct();
            if (string.IsNullOrEmpty(productId))
                throw new ArgumentException("产品ID为空！");
            using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where ID=:productId"), OracleHelper.Connection, new OracleParameter(":productId", (object)productId)))
            {
                if (((DbDataReader)reader).Read())
                {
                    this.ReadInfo(reader, o);
                    return o;
                }
            }
            return o;
        }

        //修改
        public bool Update(GwProduct o)
        {
            string sql = "";
            sql += "update GW_PRODUCT set NAME=:NAME ,ICON=:ICON, INSTRUCTIONS=:INSTRUCTIONS, CLASSES=:CLASSES, PARENT_ID=:PARENT_ID, PATH=:PATH ";
            sql += " , IS_DEFAULT=:IS_DEFAULT, IS_ENABLE=:IS_ENABLE, SORT=:SORT, OP_GROUP_ID=:OP_GROUP_ID, API_SP_XH_ID=:API_SP_XH_ID, GIVING_NUM=:GIVING_NUM, REMARK=:REMARK ";
            sql += " where ID=:ID ";

            return OracleHelper.ExecuteSql(sql, OracleHelper.Connection, 
                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.parent_id), 
                new OracleParameter(":PATH", (object)o.path), 
                new OracleParameter(":IS_DEFAULT", (object)o.is_default), 
                new OracleParameter(":IS_ENABLE", (object)o.is_enable), 
                new OracleParameter(":SORT", (object)o.sort), 
                new OracleParameter(":OP_GROUP_ID", (object)o.op_group_id), 
                new OracleParameter(":API_SP_XH_ID", (object)o.api_sp_xh_id), 
                new OracleParameter(":GIVING_NUM", (object)o.giving_num), 
                new OracleParameter(":REMARK", (object)o.remark),
                new OracleParameter(":ID", (object)o.id)
                ) > 0;
        }

        //添加
        public bool Add(GwProduct o)
        {
            string sql = "";
            sql += "insert into GW_PRODUCT(ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, OP_GROUP_ID, API_SP_XH_ID, GIVING_NUM, REMARK ) ";
            sql += " values(:ID, :NAME, :ICON, :INSTRUCTIONS, :CLASSES, :PARENT_ID, :PATH, :IS_DEFAULT, :IS_ENABLE, :SORT, :OP_GROUP_ID, :API_SP_XH_ID, :GIVING_NUM, :REMARK) ";

            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.parent_id),
                new OracleParameter(":PATH", (object)o.path),
                new OracleParameter(":IS_DEFAULT", (object)o.is_default),
                new OracleParameter(":IS_ENABLE", (object)o.is_enable),
                new OracleParameter(":SORT", (object)o.sort),
                new OracleParameter(":OP_GROUP_ID", (object)o.op_group_id),
                new OracleParameter(":API_SP_XH_ID", (object)o.api_sp_xh_id),
                new OracleParameter(":GIVING_NUM", (object)o.giving_num),
                new OracleParameter(":REMARK", (object)o.remark)
                ) > 0;
        }

        //更新产品启停状态
        public bool UpdateIs_enable(string productId, int is_enable)
        {
            return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET IS_ENABLE=:IS_ENABLE WHERE ID=:ID", OracleHelper.Connection, new OracleParameter(":IS_ENABLE", (object)is_enable), new OracleParameter(":ID", (object)productId)) > 0;
        }

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

        //检查产品名称
        public bool IsNameExists(string name)
        {
            if (string.IsNullOrEmpty(name) || string.Equals("0", name))
                return true;
            using (OracleDataReader oracleDataReader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where NAME=:NAME and IS_ENABLE=1 "), OracleHelper.Connection, new OracleParameter(":NAME", (object)name)))
                return ((DbDataReader)oracleDataReader).Read();
        }

        public void Dispose()
        {

        }

        /**
         * 数据转换封装
         * */
        private bool ReadInfo(OracleDataReader reader, GwProduct gwProduct)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            gwProduct.id = oracleReaderWrapper.GetString("id", "");
            gwProduct.name = oracleReaderWrapper.GetString("name", "");
            gwProduct.icon = oracleReaderWrapper.GetString("icon", "");
            gwProduct.instructions = oracleReaderWrapper.GetString("instructions", "");
            gwProduct.classes = oracleReaderWrapper.GetInt("classes", 0);
            gwProduct.parent_id = oracleReaderWrapper.GetString("parent_id", "");
            gwProduct.path = oracleReaderWrapper.GetString("path", "");
            gwProduct.is_default = oracleReaderWrapper.GetInt("is_default", -1);
            gwProduct.is_enable = oracleReaderWrapper.GetInt("is_enable", -1);
            gwProduct.sort = oracleReaderWrapper.GetInt("sort", 0);
            gwProduct.giving_num = oracleReaderWrapper.GetInt("giving_num", 0);
            gwProduct.op_group_id = oracleReaderWrapper.GetInt("op_group_id", -1);
            gwProduct.remark = oracleReaderWrapper.GetString("remark", "");
            gwProduct.api_sp_xh_id = oracleReaderWrapper.GetString("api_sp_xh_id", "");

            return true;
        }

        public DataTable GetClientCount(string clientID)
        {
          return OracleHelper.Execute("select * from gw_client_product a where a.client_id = :clientId", OracleHelper.Connection, new OracleParameter(":clientId", (object) clientID) );
        }

        //导出查询
        public DataTable LoadGwProductDataTable(string name, string classes, string is_default, string op_group_id)
        {

            StringBuilder stringBuilder = new StringBuilder();
            //stringBuilder.Append("select ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, OP_GROUP_ID, API_SP_XH_ID, GIVING_NUM, REMARK from GW_PRODUCT where 1=1");
            
            stringBuilder.Append(" select * from ( ");
            stringBuilder.Append(" select ID, NAME, ICON, INSTRUCTIONS,  ");
            stringBuilder.Append(" case CLASSES when 0 then '0-产品分类' ");
            stringBuilder.Append(" else '1-产品' ");
            stringBuilder.Append(" END AS ");
            stringBuilder.Append(" CLASSES,  ");
            stringBuilder.Append(" type_id||'-'||type_name PARENT_ID, ");
            stringBuilder.Append(" PATH,  ");
            stringBuilder.Append(" case IS_DEFAULT when 0 then '0-否' ");
            stringBuilder.Append(" else '1-默认' ");
            stringBuilder.Append(" END AS ");
            stringBuilder.Append(" IS_DEFAULT,  ");
            stringBuilder.Append(" case IS_ENABLE when 0 then '0-已停用' ");
            stringBuilder.Append(" else '1-已启用' ");
            stringBuilder.Append(" END AS ");
            stringBuilder.Append(" IS_ENABLE,  ");
            stringBuilder.Append(" SORT,  ");
            stringBuilder.Append(" (select group_id||'-'||group_name from gw_op_group where group_id=OP_GROUP_ID ) ");
            stringBuilder.Append(" OP_GROUP_ID,  ");
            stringBuilder.Append(" (select xh_id||'-'||xh_name from sys_xh where xh_id=API_SP_XH_ID ) ");
            stringBuilder.Append(" API_SP_XH_ID,  ");
            stringBuilder.Append(" GIVING_NUM, REMARK  ");
            stringBuilder.Append(" from GW_PRODUCT a ");
            stringBuilder.Append(" left join (select id type_id, name type_name from gw_product where classes=0) b ");
            stringBuilder.Append(" on a.PARENT_ID = b.type_id ");
            stringBuilder.Append(" ) aa ");
            stringBuilder.Append(" where 1=1 ");
            

            OracleParameter[] oracleParameterArray = new OracleParameter[3];
            if (name != null && name != "")
            {
                oracleParameterArray[0] = new OracleParameter(":name", (OracleDbType)126);
                ((DbParameter)oracleParameterArray[0]).Value = (object)name;
                stringBuilder.Append(" and name=:name");
            }
            if (classes != null && classes != "")
            {
                oracleParameterArray[1] = new OracleParameter(":classes", (OracleDbType)112);
                ((DbParameter)oracleParameterArray[1]).Value = (object)classes;
                stringBuilder.Append(" and classes=:classes");
            }
            if (is_default != null && is_default != "")
            {
                oracleParameterArray[1] = new OracleParameter(":is_default", (OracleDbType)112);
                ((DbParameter)oracleParameterArray[1]).Value = (object)is_default;
                stringBuilder.Append(" and is_default=:is_default");
            }
            if (op_group_id != null && op_group_id != "")
            {
                oracleParameterArray[2] = new OracleParameter(":op_group_id", (OracleDbType)112);
                ((DbParameter)oracleParameterArray[2]).Value = (object)op_group_id;
                stringBuilder.Append(" and op_group_id=:op_group_id");
            }
            return OracleHelper.Execute(PubConstant.doOracleSql(100000, stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray);
        }
    }
}