using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Community.DAL
{
    public class QueryDAL
    {
        private const string _queryBuilding = "select b.Id,r.Province,r.City,r.County,r.Street,r.Community,b.Name from Building b inner join Region r on b.Region=r.Id where b.Id={0}";

        private const string _queryHouse = "select h.Id,h.FullName,s.Status,h.Remark from House h inner join Building b on h.Building=b.Id inner join HouseState s on h.HouseState=s.Id where b.Name=@Name and h.Unit=@Unit and h.HouseNum=@num";

        private const string _queryHouseholdByHouse = "select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark from Household h inner join HoldType t on h.HoldType=t.Id where House={0}";

        private const string _queryHoseholdByName = "select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark from Household h inner join HoldType t on h.HoldType=t.Id where h.Name like @Name limit 20";

        private const string _queryHouseholdByBuilding = "select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark,b.Name BName,o.Unit,o.HouseNum from Household h inner join HoldType t on h.HoldType=t.Id inner join House o on h.House=o.Id inner join Building b on o.Building=b.Id where b.Name=@Name and o.Unit=@Unit and o.HouseNum=@num";

        private const string _queryHouseholdByUserId = "select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark,b.Name BName,o.Unit,o.HouseNum from Household h inner join HoldType t on h.HoldType=t.Id inner join House o on h.House=o.Id inner join Building b on o.Building=b.Id where o.Id=(select House from Household where Id={0})";

        private const string _countGender = "select hh.Gender,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name=@Name group by hh.Gender order by Count";

        private const string _countNation = "select hh.Nation,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name=@Name group by Nation having count(1)>2 order by Count";

        private const string _countPolitical = "select hh.Political,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name=@Name group by Political order by Count";

        private const string _countHouse = "select Count(1) Count from Building b inner join House h on h.Building=b.Id where b.Name=@Name";

        private const string _countStatus = "select t.Status,Count(1) Count from Household hh inner join HoldType t on hh.HoldType=t.Id inner join House h on hh.House=h.Id inner join Building b on h.Building=b.Id where b.Name=@Name group by HoldType order by Count";

        public static DataTable QueryBuildingById(int id)
        {
            try
            {
                string sql = string.Format(_queryBuilding, id);

                return SQLiteHelper.GetDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable QueryHouse(string name, string unit, string num)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                SQLiteParameter s2 = new SQLiteParameter("@Unit");
                s2.Value = unit;

                SQLiteParameter s3 = new SQLiteParameter("@num");
                s3.Value = num;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1, s2, s3 };

                return SQLiteHelper.GetDataTable(_queryHouse, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable QueryHousehold(int id)
        {
            try
            {
                string sql = string.Format(_queryHouseholdByHouse, id);

                return SQLiteHelper.GetDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable QueryHousehold(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = string.Format("%{0}%", name);

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_queryHoseholdByName, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable QueryHouseholdByBuilding(string name, string unit, string num)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                SQLiteParameter s2 = new SQLiteParameter("@Unit");
                s2.Value = unit;

                SQLiteParameter s3 = new SQLiteParameter("@num");
                s3.Value = num;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1, s2, s3 };

                return SQLiteHelper.GetDataTable(_queryHouseholdByBuilding, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable QueryHouseholdByUserId(int id)
        {
            try
            {
                string sql = string.Format(_queryHouseholdByUserId, id);

                return SQLiteHelper.GetDataTable(sql, null);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable CountGender(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_countGender, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable CountNation(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_countNation, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable CountPolitical(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_countPolitical, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable CountHouse(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_countHouse, list.ToArray());
            }
            catch
            {
                return null;
            }
        }

        public static DataTable CountStatus(string name)
        {
            try
            {
                SQLiteParameter s1 = new SQLiteParameter("@Name");
                s1.Value = name;

                List<SQLiteParameter> list = new List<SQLiteParameter>() { s1 };

                return SQLiteHelper.GetDataTable(_countStatus, list.ToArray());
            }
            catch
            {
                return null;
            }
        }
    }
}