using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL;
using System.Data.SqlClient;
using System.Data;
using CY.Model;
using System.Transactions;


namespace CY.SQLDAL
{
    public class OA_ShiJuanBiaoQianDAL : IOA_ShiJuanBiaoQianDAL
    {

        private Database _dataBase = null;
        #region 常量
        /// <summary>
        /// 查询目标 
        /// </summary>
        const string SELECTTARGET = " t.* ";
        /// <summary>
        /// 查询来源
        /// </summary>
        const string FROMSOUCEBEFORE = " ( select  ok.* " +
            
          " from [OA_ShiJuanBiaoQian] ok  " +

        " where 0=0  ";
        const string FROMSOUCEEND = ") as t  ";
        /// <summary>
        /// 分页默认排序字段
        /// </summary>
        const string ORDERBY = " CreateTime desc  ";
        #endregion
        public OA_ShiJuanBiaoQianDAL()
        {
            _dataBase = new Database();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_ShiJuanBiaoQian trueModel = model as Model.OA_ShiJuanBiaoQian;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter CreateTime = null;
            if (trueModel.CreateTime.HasValue)
            {
                CreateTime = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
            }
            else
            {
                CreateTime = new SqlParameter("@CreateTime", DBNull.Value);
            }

            SqlParameter LastUpdateTime = null;
            if (trueModel.LastUpdateTime.HasValue)
            {
                LastUpdateTime = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
            }
            else
            {
                LastUpdateTime = new SqlParameter("@LastUpdateTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {

               //  new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@FirmId",trueModel.FirmId),
                   new SqlParameter("@Bianhao",string.IsNullOrEmpty(trueModel.Bianhao)?"":trueModel.Bianhao),

                    new SqlParameter("@Quyu",string.IsNullOrEmpty(trueModel.Quyu)?"":trueModel.Quyu),
                     new SqlParameter("@Xuexiao",string.IsNullOrEmpty(trueModel.Xuexiao)?"":trueModel.Xuexiao),
                      new SqlParameter("@Nianji",string.IsNullOrEmpty(trueModel.Nianji)?"":trueModel.Nianji),
                       new SqlParameter("@Kemu",string.IsNullOrEmpty(trueModel.Kemu)?"":trueModel.Kemu),
                        new SqlParameter("@Da",string.IsNullOrEmpty(trueModel.Da)?"":trueModel.Da),
                         new SqlParameter("@Xiao",string.IsNullOrEmpty(trueModel.Xiao)?"":trueModel.Xiao),
                             new SqlParameter("@Creater",string.IsNullOrEmpty(trueModel.Creater)?"":trueModel.Creater),
                             CreateTime,
                                new SqlParameter("@Updater",string.IsNullOrEmpty(trueModel.Updater)?"":trueModel.Updater), 
                                LastUpdateTime,
                                 
                                       

            };
            string sql = "Insert Into OA_ShiJuanBiaoQian ([FirmId],[Bianhao],[Quyu],[Xuexiao], [Nianji], [Kemu],[Da], [Xiao],  [Creater],[CreateTime],[Updater],[LastUpdateTime] )"
                                             + " Values ( @FirmId,@Bianhao,@Quyu, @Xuexiao, @Nianji, @Kemu, @Da, @Xiao,   @Creater,@CreateTime,@Updater,@LastUpdateTime  )";
            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_ShiJuanBiaoQian trueModel = model as Model.OA_ShiJuanBiaoQian;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter CreateTime = null;
            if (trueModel.CreateTime.HasValue)
            {
                CreateTime = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
            }
            else
            {
                CreateTime = new SqlParameter("@CreateTime", DBNull.Value);
            }

            SqlParameter LastUpdateTime = null;
            if (trueModel.LastUpdateTime.HasValue)
            {
                LastUpdateTime = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
            }
            else
            {
                LastUpdateTime = new SqlParameter("@LastUpdateTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {

                  new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@FirmId",trueModel.FirmId),
                   new SqlParameter("@Bianhao",string.IsNullOrEmpty(trueModel.Bianhao)?"":trueModel.Bianhao),

                    new SqlParameter("@Quyu",string.IsNullOrEmpty(trueModel.Quyu)?"":trueModel.Quyu),
                     new SqlParameter("@Xuexiao",string.IsNullOrEmpty(trueModel.Xuexiao)?"":trueModel.Xuexiao),
                      new SqlParameter("@Nianji",string.IsNullOrEmpty(trueModel.Nianji)?"":trueModel.Nianji),
                       new SqlParameter("@Kemu",string.IsNullOrEmpty(trueModel.Kemu)?"":trueModel.Kemu),
                        new SqlParameter("@Da",string.IsNullOrEmpty(trueModel.Da)?"":trueModel.Da),
                         new SqlParameter("@Xiao",string.IsNullOrEmpty(trueModel.Xiao)?"":trueModel.Xiao),
                             new SqlParameter("@Creater",string.IsNullOrEmpty(trueModel.Creater)?"":trueModel.Creater),
                             CreateTime,
                                new SqlParameter("@Updater",string.IsNullOrEmpty(trueModel.Updater)?"":trueModel.Updater),
                                LastUpdateTime,



            };
            string sql = "Update OA_ShiJuanBiaoQian Set [FirmId]=@FirmId,[Bianhao]=@Bianhao,[Quyu]=@Quyu,[Xuexiao]=@Xuexiao, [Nianji]=@Nianji, [Kemu]=@Kemu,[Da]=@Da, [Xiao]=@Xiao , [Creater]=@Creater,[CreateTime]=@CreateTime,[Updater]=@Updater,[LastUpdateTime]=@LastUpdateTime where [Keyid] =@Keyid ";

            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 根据编号获得信息
        /// </summary>
        /// <param name="Keyid">编号</param>
        /// <returns></returns>
        public OA_ShiJuanBiaoQian GetModelByKeyid(Guid? keyid)
        {
            try
            {
                string condition = "  ";
                if (!keyid.Equals(Guid.Empty))
                {
                    condition = " Keyid='" + keyid + "'";
                }
                else
                {
                    return null;
                }

                //  return null;//错误数据返会空 

                IList<OA_ShiJuanBiaoQian> result = _dataBase.SelectModel<OA_ShiJuanBiaoQian>("*", "OA_ShiJuanBiaoQian", condition) as IList<OA_ShiJuanBiaoQian>;//执行查询

                return (null == result || result.Count == 0) ? null : result[0];//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_ShiJuanBiaoQian> SelectAllModel(Infrastructure.Query.Query query)
        {
            return _dataBase.SelectModel<Model.OA_ShiJuanBiaoQian>(" * ", " OA_ShiJuanBiaoQian ") as IList<Model.OA_ShiJuanBiaoQian>;//执行查询
        }

        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_ShiJuanBiaoQian> GetModelByformid(Guid FirmId)
        {
            return _dataBase.SelectModel<Model.OA_ShiJuanBiaoQian>(" * ", " OA_ShiJuanBiaoQian ", " FirmId = '" + FirmId + "'") as IList<Model.OA_ShiJuanBiaoQian>;//执行查询
        }


        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_ShiJuanBiaoQian> GetModelByids(string ids)
        {
            return _dataBase.SelectModel<Model.OA_ShiJuanBiaoQian>(" * ", " OA_ShiJuanBiaoQian ", " Keyid in  (" + ids + ")") as IList<Model.OA_ShiJuanBiaoQian>;//执行查询
        }


        /// <summary>
        /// 根据编号批量删除数据
        /// </summary>
        /// <param name="currentOperator">当前操作人</param>
        /// <param name="ids">编号集合</param>
        /// <returns></returns>
        public bool DeleteDataByIds(string currentOperator, params int[] ids)
        {
            return Database.DeleteDataByIds<OA_ShiJuanBiaoQian>(this, currentOperator, ids);
        }



        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_ShiJuanBiaoQian> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            if (null == pagination || null == query || null == query.Criteria || 1 > query.Criteria.Count)
                return null;
            //query.Criteria 首个元素必须是排序字段，其值为结果排序字段

            int maxParamIndex = query.Criteria.Count - 1;//最大索引

            string[] orderbys = new string[] { ORDERBY };
            string resultOrderBy = "";//结果集排序方式

            if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
            {
                orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
                resultOrderBy = query.Criteria[maxParamIndex].Value.ToString();//= 1 == orderbys.Length ? resultOrderBy : orderbys[1];
            }
            string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件


            

            return _dataBase.SelectModelPage<Model.OA_ShiJuanBiaoQian>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_ShiJuanBiaoQian trueModel = model as Model.OA_ShiJuanBiaoQian;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            string sql = "Delete OA_ShiJuanBiaoQian Where [Keyid] = @Keyid ";


            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
    }
}