using Dapper;
using dccdc.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;

namespace dccdc.DAL
{
    public class ERPUserDal
    {
        public List<ERPUser> GetAllList(string key)
        {
            string sql = "select * from erpuser where 1=1";
            if (key != "")
            {
                sql += " and Department=@key";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<ERPUser>(sql, new { key = key }).ToList();
            }
        }

        public ERPUser login(string username, string pwd)
        {
            //throw new NotImplementedException();
            string sql = "select * from oa.dbo.erpuser where UserName=@username";
            using (var db = CommHelper.GetSqlConnection("OADB"))
            {
                var m = db.Query<Models.ERPUser>(sql, new { username = username }).FirstOrDefault();
                return m;
            }
        }

        public ERPUser getbyusername(string username)
        {
            //throw new NotImplementedException();
            string sql = "select * from oa.dbo.erpuser where UserName=@username";
            using (var db = CommHelper.GetSqlConnection("OADB"))
            {
                var m = db.Query<Models.ERPUser>(sql, new { username = username }).FirstOrDefault();
                return m;
            }
        }
        public ERPUser login2(string yhid)
        {
            //throw new NotImplementedException();
            string sql = "select * from oa.dbo.erpuser where id=@id";
            using (var db = CommHelper.GetSqlConnection("OADB"))
            {
                var m = db.Query<Models.ERPUser>(sql, new { id = yhid  }).FirstOrDefault();
                return m;
            }
        }

        public List<ERPUser> getListByDepname(string ksmc)
        {
            //throw new NotImplementedException();
            string sql = "select * from erpuser where 1=1";
            if (ksmc != "")
            {
                sql += " and Department=@key";
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<ERPUser>(sql, new { key = ksmc }).ToList();
            }
        }

        public List<ERPUser> GetSelectList()
        {
            string sql = "select ID,TrueName from erpuser";
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<ERPUser>(sql).ToList();
            }
        }

        public List<ERPUser> GetUserDepart(string where)
        {
            string sql = "select a.*, b.ID as bid, b.dirid from erpuser a left join erpbumen b on a.department= b.bumenname ";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<ERPUser>(sql).ToList();
            }
        }


        public string GetTrueNameById(int id)
        {
            string sql = "select TrueName from erpuser where ID =" + id;
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<string>(sql).FirstOrDefault();
            }
        }

        public List<string> GetQuanxian(int id)
        {
            string sql = "select replace(jiaose,',',''',''')  from erpuser where ID =" + id;
            List<string> qx = new List<string>();
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                string jueses = conn.Query<string>(sql).FirstOrDefault();
                if (string.IsNullOrEmpty(jueses))
                    return qx;

                string sql2 = "select * from erpjiaose where jiaosename in ('"+ jueses + "')";
                List<ERPJiaoSeModel> models = conn.Query<ERPJiaoSeModel>(sql2).ToList();
                foreach (ERPJiaoSeModel model in models)
                {
                    qx.AddRange(model.QuanXian.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToList());
                }
                return qx;
            }
        }

        public int GetERPUser(string openid)
        {

            string sql = "select id from ERPUser where 1=1";
             if(!string.IsNullOrEmpty(openid))
            {
                sql += "and openid ='"+openid+"'";
                
            }
            if (string.IsNullOrEmpty(openid)) 
            {
                return 0;
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                var data = conn.ExecuteScalar(sql);
                if (data==null) 
                {
                    return 0;
                }
                return Convert.ToInt32(data);
            }
        }

        public string GetSignation(int id)
        {
            string sql = "select signation from erpuser where ID =" + id;
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<string>(sql).FirstOrDefault();
            }
        }
        public bool SetSignation(int id, string qianming)
        {
            string sql = @"update erpuser set signation = @signation where id=@id";
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                try
                {
                    int result = conn.Execute(sql, new { @id = id, @signation = qianming });
                    if (result > 0)
                        return true;
                    else
                        return false;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }

        public string GetXcx(int id)
        {
            string sql = "select xcx from erpuser where ID =" + id;
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<string>(sql).FirstOrDefault();
            }
        }
        public bool SetXcx(int id, string xcxs)
        {
            string sql = @"update erpuser set xcx = @xcx where id=@id";
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                try
                {
                    int result = conn.Execute(sql, new { @id = id, @xcx = xcxs  });
                    if (result > 0)
                        return true;
                    else
                        return false;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }

        public string getQX(string jiaoSe)
        {
            //throw new NotImplementedException();
            string sql = "select QuanXian from ERPJiaoSe where JiaoSeName in(" + "'" + jiaoSe.Replace(",", "','") + "'" + ")";
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                try
                {
                    var result = conn.Query<string>(sql).ToList();
                    string rs = "";
                    result.ForEach(t => {
                        rs += t;
                    });
                    return rs;
                }
                catch
                {
                    return "";
                }
            }
        }

        public List<ERPUser> getOpenids(string sql)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                return conn.Query<ERPUser>(sql).ToList();
            }
        }

        public bool Setopenid(string id, string openid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ERPUser set ");
            strSql.Append("openid=@openid,openidtype=1,openidtime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
            strSql.Append(" where ID=@id ");

            using (IDbConnection conn = CommHelper.GetSqlConnection("OADB"))
            {
                try
                {
                    int result = conn.Execute(strSql.ToString(), new { @id = id, openid = openid });
                    if (result > 0)
                        return true;
                    else
                        return false;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
        public string getUserName(String userid)
        {
            string sql = "select TrueName from oa.dbo.erpuser where ID=" + userid;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<string>(sql).FirstOrDefault();
            }
                
        }
    }
}