using CallCenterApi.DB;
using CallCenterApi.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace CallCenterApi.DAL
{
   public  class ManagementUser
    {
        /// <summary>
        /// 得到一个管理员账号对象实体
        /// </summary>
        public CallCenterApi.Model.ManagementUser DataRowToModelarchives(DataRow row)
        {
            Model.ManagementUser model = new Model.ManagementUser();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }

        /// <summary>
        /// 根据Openid 获取用户
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public Model.ManagementUser GetModelByOpenid(string openid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ManagementUser ");
            strSql.Append(" where openid=@openid");
            SqlParameter[] parameters = {
                    new SqlParameter("@openid", SqlDbType.VarChar,300)
            };
            parameters[0].Value = openid;

            Model.ManagementUser model = new Model.ManagementUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.ManagementUser DataRowToModel(DataRow row)
        {
            Model.ManagementUser model = new Model.ManagementUser();
            if (row != null)
            {
                if (row["id"] != null)
                {
                    model.id =Convert.ToInt32(row["id"]);
                }
                if (row["username"] != null)
                {
                    model.username = row["username"].ToString();
                }
                if (row["userpower"] != null)
                {
                    model.userpower = row["userpower"].ToString();
                }
                if (row["handleper"] != null)
                {
                    model.handleper = row["handleper"].ToString();
                }

                if (row["handdate"] != null)
                {
                    model.handdate =Convert.ToDateTime(row["handdate"]);
                }
                if (row["ifdeler"] != null)
                {
                    model.ifdeler = Convert.ToInt32(row["ifdeler"]);
                }

                if (row["openid"] != null)
                {
                    model.openid = row["openid"].ToString();
                }

            }
            return model;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.ManagementUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ManagementUser set ");
            strSql.Append("username=@username,");
            strSql.Append("userpower=@userpower,");
            strSql.Append("handleper=@handleper,");
            strSql.Append("handdate=@handdate,");
            strSql.Append("ifdeler=@ifdeler,");
            strSql.Append("openid=@openid");

            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.VarChar,100),
                    new SqlParameter("@userpower", SqlDbType.VarChar,200),
                    new SqlParameter("@handleper", SqlDbType.VarChar,100),
                    new SqlParameter("@handdate", SqlDbType.DateTime),
                    new SqlParameter("@ifdeler", SqlDbType.Int,4),
                    new SqlParameter("@openid", SqlDbType.VarChar,300),
                    new SqlParameter("@id",SqlDbType.Int,4)};
            parameters[0].Value = model.username;
            parameters[1].Value = model.userpower;
            parameters[2].Value = model.handleper;
            parameters[3].Value = model.handdate;
            parameters[4].Value = model.ifdeler;
            parameters[5].Value = model.openid;
            parameters[6].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 根据名称得到一个对象实体
        /// </summary>
        public Model.ManagementUser GetModel(string username)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ManagementUser ");
            strSql.Append(" where username=@username");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NVarChar,100)
            };
            parameters[0].Value = username;

            Model.T_Sys_UserAccount model = new Model.T_Sys_UserAccount();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据名称得到一个对象实体
        /// </summary>
        public Model.ManagementUser GetModelname(string username)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ManagementUser ");
            strSql.Append(" where username like  @username");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NVarChar,100)
            };
            parameters[0].Value = username;

            Model.T_Sys_UserAccount model = new Model.T_Sys_UserAccount();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据id得到一个对象实体
        /// </summary>
        public Model.ManagementUser GetModels(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ManagementUser ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;
            
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据用户名密码得到一个对象实体
        /// </summary>
        public Model.ManagementUser GetManageCustomerModelwx(string username, string userpower)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from ManagementUser ");
            strSql.Append("  where username=@username and userpower=@userpower");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NVarChar,100),
                     new SqlParameter("@userpower", SqlDbType.NVarChar,200)
            };
            parameters[0].Value = username;
            parameters[1].Value = userpower;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.ManagementUser model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ManagementUser(");
            strSql.Append(" username, userpower, handleper,handdate,ifdeler,openid)");
            strSql.Append(" values (");
            strSql.Append("@username,@userpower,@handleper,@handdate,@ifdeler,@openid)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.VarChar,100),
                    new SqlParameter("@userpower", SqlDbType.VarChar,200),
                    new SqlParameter("@handleper", SqlDbType.VarChar,100),
                    new SqlParameter("@handdate", SqlDbType.DateTime),
                    new SqlParameter("@ifdeler", SqlDbType.Int,4),
                    new SqlParameter("@openid", SqlDbType.VarChar,300)};
            parameters[0].Value = model.username;
            parameters[1].Value = model.userpower;
            parameters[2].Value = model.handleper;
            parameters[3].Value = model.handdate;
            parameters[4].Value = model.ifdeler;
            parameters[5].Value = model.openid;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,username, userpower, handleper,handdate,ifdeler,openid ");
            strSql.Append(" FROM ManagementUser ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ManagementUser set ifdeler=1");
            strSql.Append(" where id in (" + ids + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取管理员与客户关联信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetManageCustomer()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select a.*,b.username,c.F_CompanyName from ManageCustomer a left join ManagementUser b on a.mangid=b.id left join T_Cus_CustomerBaseNew c on a.cusmid=c.F_CustomerId ");
          
            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 添加管理员用户与客户关联信息
        /// </summary>
        /// <returns></returns>
        public int AddManageCustomer(ManageCustomer model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ManageCustomer(");
            strSql.Append(" mangid,cusmid)");
            strSql.Append(" values (");
            strSql.Append("@mangid,@cusmid)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@mangid", SqlDbType.Int ,4),
                    new SqlParameter("@cusmid", SqlDbType.Int ,4)};
            parameters[0].Value = model.mangid;
            parameters[1].Value = model.cusmid;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据id得到一个对象实体
        /// </summary>
        public Model.ManageCustomer GetManageCustomerModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 a.*,b.username,c.F_CompanyName,c.F_CustomerCode from ManageCustomer a left join ManagementUser b on a.mangid=b.id left join T_Cus_CustomerBaseNew c on a.cusmid=c.F_CustomerId  ");
            strSql.Append(" where a.id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            Model.ManageCustomer model = new Model.ManageCustomer();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelcustomerBorrow(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
      
        /// <summary>
        /// 获取管理员与客户关联表实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.ManageCustomer DataRowToModelcustomerBorrow(DataRow row)
        {
            Model.ManageCustomer model = new Model.ManageCustomer();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }
        /// <summary>
        /// 修改管理员与客户关联数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateManageCustomer(ManageCustomer model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ManageCustomer set ");
            strSql.Append("mangid=@mangid,");
            strSql.Append("cusmid=@cusmid");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@mangid", SqlDbType.Int ,4),
                    new SqlParameter("@cusmid", SqlDbType.Int,4),
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.mangid;
            parameters[1].Value = model.cusmid;
            parameters[2].Value = model.id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个管理员账号对象实体
        /// </summary>
        public CallCenterApi.Model.ManageCustomer DataRowToModelarchives1(DataRow row)
        {
            Model.ManageCustomer model = new Model.ManageCustomer();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteManaList(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ManageCustomer ");
            strSql.Append(" where id in (@id)");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = ids;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetmanagecustomerList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  a.*,b.username,c.F_CompanyName,c.F_CustomerCode from ManageCustomer a left join ManagementUser b on a.mangid=b.id left join T_Cus_CustomerBaseNew c on a.cusmid=c.F_CustomerId  ");
           
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
    }
}