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

namespace dccdc.DAL
{
    public class townDal
    {
        public Models.Sz_town getM(string id)
        {
            //throw new NotImplementedException();
            string sql = "select townCode,townName,countyCode,x ,y from Sz_town where townCode=@townCode";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.Sz_town>(sql, new { townCode = id }).FirstOrDefault();
            }
        }

        public object svae(Sz_town m)
        {
            //throw new NotImplementedException();
            try
            {
                //throw new NotImplementedException();
                string sql = "select * from Sz_town where townCode=@townCode";
                m.countyCode = "3714020000";
                using (var conn = CommHelper.GetSqlConnection())
                {
                    var m1 = conn.Query<Models.Sz_town>(sql, new { townCode = m.townCode }).FirstOrDefault();
                    if (m1 == null)
                    {
                        sql = "insert into Sz_town(townCode,townName,countyCode,x,y) values(@townCode,@townName,@countyCode,@x,@y)";
                    }
                    else
                    {
                        sql = "update Sz_town set townName=@townName,countyCode=@countyCode,x=@x,y=@y where townCode=@townCode";
                    }
                    conn.Execute(sql, m);
                    return new { State = 1, Message = "保存成功！" };

                }
            }
            catch (Exception ex)
            {
                return new { State = 0, Message = ex.Message };
            }
        }

        public int getCount(string townname)
        {
            string sql = "select count(1) from Sz_town where 1=1  and countyCode='3714020000'";
            if(!string.IsNullOrEmpty(townname))
            {
                sql += " townName like @townName";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.ExecuteScalar<int>(sql, new { townName = "%" + townname + "%" });
            }

        }

        /// <summary>
        /// 根据街道、乡镇名称获取辖区街道乡镇列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="townname"></param>
        /// <returns></returns>
        public List<Sz_town> getList(int page, int pagesize, string townname)
        {
            string sql = "select *,row_number() over(order by townCode desc) as rownum from Sz_town where 1=1 and countyCode='3714020000'";
            if (!string.IsNullOrEmpty(townname))
            {
                sql += " and townName like @townName";
            }
            sql = "select * from ( " + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.Sz_town>(sql, new { townName = "%" + townname + "%" }).ToList();
            }
        }

        public object del(string id)
        {
            //throw new NotImplementedException();

            string sql = "delete from Sz_town where townCode=@townCode";
            try
            {
                using (var conn = CommHelper.GetSqlConnection())
                {
                    conn.Execute(sql, new { townCode = id });
                    return new { State = 1, Message = "删除成功！" };                }
            }
            catch(Exception ex)
            {
                return new { State = 0, Message = ex.Message };
            }
        }

        public object getgeo()
        {
            //throw new NotImplementedException();
            string sql = "select * from Sz_town where countyCode='3714020000'";
            Dictionary<string, decimal[]> ts = new Dictionary<string, decimal[]>();
            using (var conn = CommHelper.GetSqlConnection())
            {
                var towns = conn.Query<Models.Sz_town>(sql).ToList();
                towns.ForEach(t => {
                    if(!string.IsNullOrEmpty(t.x)&&!string.IsNullOrEmpty(t.y))
                    {
                        ts.Add(t.townName, new decimal[] { decimal.Parse(t.x), decimal.Parse(t.y) });
                    }
                });
            }
            return ts;
        }
    }
}