346 lines
13 KiB
C#
346 lines
13 KiB
C#
|
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;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|