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

namespace Dao
{
    public class GwMobileAreaDao : IDisposable
    {
        public List<GwMobileArea> MobilAreaPageList(string mobileSegment, string city, string province, string cardType, out int recordcount, int pageSize, int PageIndex)
        {
            List<GwMobileArea> list = new List<GwMobileArea>();
            recordcount = 0;
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("from GW_MOBILEAREA where 1=1");
                OracleParameter[] oracleParameterArray = new OracleParameter[4];
                if (!string.IsNullOrEmpty(mobileSegment))
                {
                    oracleParameterArray[0] = new OracleParameter(":MOBILE_SEGMENT", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[0]).Value = (object)("%" + mobileSegment + "%");
                    stringBuilder.Append(" and MOBILE_SEGMENT like :MOBILE_SEGMENT");
                }
                if (!string.IsNullOrEmpty(city))
                {
                    oracleParameterArray[1] = new OracleParameter(":CITY", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[1]).Value = (object)city;
                    stringBuilder.Append(" and CITY=:CITY ");
                }
                if (!string.IsNullOrEmpty(province))
                {
                    oracleParameterArray[2] = new OracleParameter(":PROVINCE", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[2]).Value = (object)province;
                    stringBuilder.Append(" and PROVINCE=:PROVINCE ");
                }
                if (!string.IsNullOrEmpty(cardType))
                {
                    oracleParameterArray[3] = new OracleParameter(":CARD_TYPE", (OracleDbType)126);
                    ((DbParameter)oracleParameterArray[3]).Value = (object)cardType;
                    stringBuilder.Append(" and CARD_TYPE=:CARD_TYPE");
                }
                using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection, oracleParameterArray))
                {
                    while (((DbDataReader)reader).Read())
                        recordcount = this.ReadCount(reader);
                }
                using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection, oracleParameterArray))
                {
                    while (((DbDataReader)reader).Read())
                    {
                        GwMobileArea o = new GwMobileArea();
                        if (this.ReadInfo(reader, o))
                            list.Add(o);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
                return list;
            }
            return list;
        }

        public List<GwMobileArea> GetMobilAreaList()
        {
            string sql = "SELECT distinct PROVINCE FROM GW_MOBILEAREA";
            List<GwMobileArea> list = new List<GwMobileArea>();
            using (OracleDataReader reader = OracleHelper.ExecuteReader(sql, OracleHelper.Connection))
            {
                while (((DbDataReader)reader).Read())
                {
                    GwMobileArea o = new GwMobileArea();
                    if (this.ReadInfo(reader, o))
                        list.Add(o);
                }
            }
            return list;
        }

        public bool UpdateMobileArea(GwMobileArea o)
        {
            return OracleHelper.ExecuteSql("update GW_MOBILEAREA set SEGMENT=:SEGMENT,CITY=:CITY,PROVINCE=:PROVINCE,CARD_TYPE=:CARD_TYPE,LAST_UPDATE_TIME=sysdate where ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)o.ID), new OracleParameter(":MOBILE_SEGMENT", (object)o.MobileSegment), new OracleParameter(":CITY", (object)o.City), new OracleParameter(":PROVINCE", (object)o.Province), new OracleParameter(":CARD_TYPE", (object)o.CardType)) > 0;
        }

        public GwMobileArea GetMobileArea(int id)
        {
            GwMobileArea o = new GwMobileArea();
            using (OracleDataReader reader = OracleHelper.ExecuteReader("SELECT * FROM GW_MOBILEAREA WHERE ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)id)))
            {
                if (((DbDataReader)reader).Read())
                {
                    if (!this.ReadInfo(reader, o))
                        throw new Exception("ReadInfo发生异常");
                    return o;
                }
            }
            return o;
        }

        public bool AddMobileArea(GwMobileArea item)
        {
            return OracleHelper.ExecuteSql("INSERT INTO GW_MOBILEAREA(ID,SEGMENT,CITY,PROVINCE,CARD_TYPE,LAST_UPDATE_TIME,IS_DELETED) VALUES(GW_MOBILEAREA_ID_SEQ.NEXTVAL,:SEGMENT,:CITY,:PROVINCE,:CARD_TYPE,            sysdate,IS_DELETED)", OracleHelper.Connection, new OracleParameter(":MOBILE_SEGMENT", (object)item.MobileSegment), new OracleParameter(":CITY", (object)item.City), new OracleParameter(":PROVINCE", (object)item.Province), new OracleParameter(":CARD_TYPE", (object)item.CardType), new OracleParameter(":IS_DELETED", (object)item.IsDeleted)) > 0;
        }

        private bool ReadInfo(OracleDataReader reader, GwMobileArea o)
        {
            OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader);
            o.ID = oracleReaderWrapper.GetInt("ID", 0);
            o.MobileSegment = oracleReaderWrapper.GetString("MOBILE_SEGMENT", "");
            o.City = oracleReaderWrapper.GetString("CITY", "");
            o.Province = oracleReaderWrapper.GetString("PROVINCE", "");
            o.CardType = oracleReaderWrapper.GetString("CARD_TYPE", "");
            o.LastUpdateTime = oracleReaderWrapper.GetDateTime("LAST_UPDATE_TIME");
            o.IsDeleted = oracleReaderWrapper.GetInt("IS_DELETED", 0);
            return true;
        }

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

        public void Dispose()
        {
        }
    }
}