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

namespace dccdc.DAL
{
    public class DiQuDal
    {
        //--获取省分页数据
        public List<Sz_province> getSzprovince(int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by provinceCode) as rownum from Sz_province where 1=1";
           
            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_province>(sql).ToList();
            }
        }
        //--获取所有省数据
        public List<Sz_province> getSzprovinceAllList()
        {
            string sql = "select * from Sz_province";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_province>(sql).ToList();
            }
        }

        public Sz_province getProvince(string code)
        {
            string sql = "select * from Sz_province where provinceCode = "+code;

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_province>(sql).ToList().FirstOrDefault();
            }
        }

        //--获取所有省总和数
        public int getSzprovinceCount()
        {
            string sql = "select count(1) from Sz_province";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<int> list =  conn.Query<int>(sql).ToList();
                return list.FirstOrDefault();
            }
        }

        public Sz_province getSzprovincebyCode(int provinceCode)
        {
            string sql = "select * from Sz_province where provinceCode = @provinceCode";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<Sz_province> list = conn.Query<Sz_province>(sql,new { provinceCode = provinceCode }).ToList();
                return list.FirstOrDefault();
            }
        }

        public List<Sz_city> getSzcityList(int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by cityCode) as rownum from Sz_city where 1=1";

            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_city>(sql).ToList();
            }
        }
        private string getnewcode(IDbConnection conn,string tablename,string parenttype,string pkcode,string type)
        {
            string sqlmax = "SELECT max("+type+"code) from "+ tablename;
            if (parenttype != null)
            {
                sqlmax += " where " + parenttype + " = @code"; 
            }
            List<string> list = conn.Query<string>(sqlmax, new { code = pkcode }).ToList();
            string maxstring = list.FirstOrDefault();
            long maxlong = Convert.ToInt64(maxstring);
            switch (type)
            {
                case "province":
                    maxlong = maxlong + 100000000;
                    break;
                case "city":
                    maxlong = maxlong + 1000000;
                    break;
                case "county":
                    maxlong = maxlong + 10000;
                    break;
                case "town":
                    maxlong = maxlong + 100;
                    break;
            }
            return maxlong.ToString();
        }

        public object saveCity(Sz_city city)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "";
                if (city.cityCode != null && city.cityCode != "")
                {
                    sql = "UPDATE SZ_CITY SET provinceCode = '" + city.provinceCode
                        +"',cityName = '" + city.cityName + "' WHERE cityCode =@cityCode ";
                }
                else
                {
                    city.cityCode = this.getnewcode(conn, "Sz_city", "provinceCode", city.provinceCode, "city");
                    sql = "INSERT INTO SZ_CITY (provinceCode,cityName,cityCode) VALUES ('"
                        + city.provinceCode + "','"+city.cityName+ "' ,@cityCode )";
                }
                try
                {
                    int result = conn.Execute(sql, new { cityCode = city.cityCode });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object saveProvince(Sz_province province)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "";
                if (province.provinceCode != null && province.provinceCode != "")
                {
                    sql = "UPDATE SZ_PROVINCE SET PROVINCENAME = '"
                        + province.provinceName + "' WHERE PROVINCECODE =@provinceCode ";
                }else
                {
                    province.provinceCode = this.getnewcode(conn, "SZ_PROVINCE", null,"0", "province");
                    sql = "INSERT INTO SZ_PROVINCE (provinceName,provinceCode) VALUES ('"
                        + province.provinceName + "', @provinceCode )";
                }
                try
                {
                    int result = conn.Execute(sql, new { provinceCode = province.provinceCode });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public object saveTown(Sz_town town)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "";
                if (town.townCode != null && town.townCode != "")
                {
                    sql = "UPDATE SZ_town SET countyCode = '" + town.countyCode
                        + "',townName = '" + town.townName + "' WHERE townCode =@townCode ";
                }
                else
                {
                    town.townCode = this.getnewcode(conn, "Sz_town", "countyCode",town.countyCode, "town");

                    sql = "INSERT INTO SZ_town (countyCode,townName,townCode) VALUES ('"
                    + town.countyCode + "','" + town.townName + "' ,@townCode)";
                }
                try
                {
                    int result = conn.Execute(sql, new { townCode = town.townCode });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public void SzcityAdd(Sz_city sf)
        {
            throw new NotImplementedException();
        }

        public object saveCounty(Sz_county county)
        {
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                string sql = "";
                if (county.countyCode != null && county.countyCode != "")
                {
                    sql = "UPDATE SZ_County SET cityCode = '" + county.cityCode
                        + "',countyName = '" + county.countyName + "' WHERE countyCode =@countyCode ";
                }
                else
                {
                    county.countyCode = this.getnewcode(conn, "Sz_county", "cityCode",county.cityCode, "county");

                    sql = "INSERT INTO SZ_County (cityCode,countyName,countyCode) VALUES ('"
                        + county.cityCode + "','" + county.countyName + "' ,@countyCode )";
                }
                try
                {
                    int result = conn.Execute(sql, new { countyCode = county.countyCode });
                    if (result > 0)
                        return new { State = 1, Message = "操作成功！" };
                    else
                        return new { State = 0, Message = "操作失败！" };
                }
                catch (Exception ex)
                {
                    return new { State = 0, Message = ex.Message };
                }
            }
        }

        public int getSzcityCount()
        {
            string sql = "select count(1) from Sz_city";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<int> list = conn.Query<int>(sql).ToList();
                return list.FirstOrDefault();
            }
        }

        public Sz_city getSzcityBycityCode(string code)
        {
            string sql = "select * from Sz_city where cityCode = @cityCode";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<Sz_city> list = conn.Query<Sz_city>(sql, new { cityCode = code }).ToList();
                return list.FirstOrDefault();
            }
        }

        public List<Sz_city> getSzcityByShengID(int id)
        {
            string sql = "select * from Sz_city where provinceCode = @provinceCode";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_city>(sql, new { provinceCode = id }).ToList();
                
            }
        }

        public List<Sz_county> getSzcountyList(int page, int pagesize)
        {
            string sql = "select *,row_number() over(order by countyCode) as rownum from Sz_county where 1=1";

            sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Sz_county>(sql).ToList();
            }
        }

        public List<Sz_county> getSzcountyByShiID(int cityCode)
        {
            throw new NotImplementedException();
        }

        public int getSzcountyCount()
        {
            string sql = "select count(1) from Sz_county";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<int> list = conn.Query<int>(sql).ToList();
                return list.FirstOrDefault();
            }
        }

        public Sz_county getSzcounty(string countyCode)
        {
            string sql = "select * from Sz_county where countyCode = @countyCode";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                List<Sz_county> list = conn.Query<Sz_county>(sql, new { countyCode = countyCode }).ToList();
                return list.FirstOrDefault();
            }
        }

        //-----获取地区的数
        public List<DQmodel> getDqModelList()
        {
            List<DQmodel> list = new List<DQmodel>();
            string sql = "SELECT 'P' AS [TYPE],'0' AS PID ,provinceCode AS DQID, provinceName AS [text] FROM Sz_province "+
                         "UNION ALL (SELECT 'C' AS[TYPE], provinceCode AS PID, cityCode AS DQID, cityName AS [text] FROM Sz_city)" +
                         "UNION ALL (SELECT 'O' AS[TYPE], cityCode AS PID, countyCode AS DQID, countyName AS [text] FROM Sz_county) " +
                         "UNION ALL (SELECT 'T' AS[TYPE], countyCode AS PID, townCode AS DQID, townName AS [text] FROM Sz_town) ";
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                list  = conn.Query<DQmodel>(sql).ToList();
            }
            return list;
        }

        //-----获取地区的数
        public List<DQmodel> getDqModelList(DQmodel dqmodel)
        {
            List<DQmodel> list = new List<DQmodel>();
            string sql = "";
            if (dqmodel.dqid == null || dqmodel.dqid == "")
            {
                sql = "SELECT 'P' AS [TYPE],'0' AS PID ,provinceCode AS DQID, provinceName AS [text] FROM Sz_province " +
                         "UNION ALL (SELECT 'C' AS[TYPE], provinceCode AS PID, cityCode AS DQID, cityName AS [text] FROM Sz_city)" +
                         "UNION ALL (SELECT 'O' AS[TYPE], cityCode AS PID, countyCode AS DQID, countyName AS [text] FROM Sz_county) ";
            }else 
            {

            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                list = conn.Query<DQmodel>(sql).ToList();
            }
            return list;
        }

    }
}