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)
|
{
|
string sql = string.Format(_queryBuilding, id);
|
|
return SQLiteHelper.GetDataTable(sql, 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;
|
}
|
}
|
}
|
}
|
