/// <summary>
/// 查询城市对应的行政区
/// </summary>
/// <param name="cityID"></param>
/// <returns></returns>
public List <Model.Area> QueryAreaList(int cityID)
{
List <Area> list = new List <Area>();
string sql = "SELECT [AreaId],[AreaName] FROM [FxtDataCenter].[dbo].[SYS_Area] where CityId = " + cityID;
DataTable dt_area = DBUtils.getData(CenterUrl, sql);
logger.Debug("QueryAreaList SQL:" + sql);
list = ConvUtils <Area> .ConvertToList(dt_area);
return(list);
}
/// <summary>
/// 查询城市所有楼盘名称信息
/// </summary>
/// <param name="cityID"></param>
/// <returns></returns>
public List <Model.DataProject> QueryDataProjectList(int cityID, int AreaID,
string tableName)
{
List <DataProject> list = new List <DataProject>();
string sql = "SELECT [ProjectId],[ProjectName],[OtherName],[PinYin],[PinYinAll] FROM [FXTProject]." + tableName + @" where CityId = " + cityID;
logger.DebugFormat("QueryDataProjectList CityID={0}, AreaID={1}, tname={2}, SQL={3}",
cityID, AreaID, tableName, sql);
DataTable data = DBUtils.getData(CaseUrl, sql);
list = ConvUtils <DataProject> .ConvertToList(data);
return(list);
}
/// <summary>
/// 查询城市信息
/// </summary>
/// <returns></returns>
public List <City> QueryCityList()
{
List <City> list = new List <City>();
string sql = @"SELECT
t1.CityId,t1.CityName,t1.ProvinceId,t2.ProjectTable,t2.CaseTable
FROM [FxtDataCenter].[dbo].[SYS_City] t1
inner join [FxtDataCenter].dbo.SYS_City_Table t2
on t1.CityId = t2.CityId";
DataTable dt_city = DBUtils.GetData(CenterUrl, sql);
list = ConvUtils <City> .ConvertToList(dt_city);
return(list);
}
/// <summary>
/// 查询字段信息映射
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public List <Model.SysCode> QuerySysCodeList(string strWhere)
{
Dictionary <string, int> dict = new Dictionary <string, int>();
string sql = "SELECT [Code],[CodeName] FROM [FxtDataCenter].[dbo].[SYS_Code] where 1 = 1 ";
if (!strWhere.Equals(""))
{
sql = sql + strWhere;
logger.DebugFormat("QuerySysCodeList SQL: {0}", sql);
}
else
{
return(null);
}
return(ConvUtils <SysCode> .ConvertToList(DBUtils.getData(CenterUrl, sql)));
}
/// <summary>
/// 分页查询城市数据
/// </summary>
/// <param name="page"></param>
/// <returns></returns>
public List <Model.City> PagingQueryCityList(int page)
{
int pageSize = 50;
List <City> list = new List <City>();
string sql = @"
SELECT top " + pageSize + @" t1.CityId,t1.CityName,t1.ProvinceId,t2.ProjectTable,t2.CaseTable
FROM [FxtDataCenter].[dbo].[SYS_City] t1 inner join [FxtDataCenter].dbo.SYS_City_Table t2 on t1.CityId = t2.CityId
WHERE t1.CityId NOT IN
(
SELECT top " + (page - 1) * pageSize + @" t1.CityId FROM [FxtDataCenter].[dbo].[SYS_City] t1 inner join [FxtDataCenter].dbo.SYS_City_Table t2 on t1.CityId = t2.CityId
)
";
DataTable dt_city = DBUtils.getData(CenterUrl, sql);
list = ConvUtils <City> .ConvertToList(dt_city);
return(list);
}
/// <summary>
/// 分页查询城市所有楼盘名称信息
/// </summary>
/// <param name="CityID"></param>
/// <param name="AreaID"></param>
/// <param name="tName"></param>
/// <param name="page"></param>
/// <returns></returns>
public List <Model.DataProject> PagingQueryProjectList(int CityID, int AreaID,
string tName, int page)
{
List <DataProject> list = new List <DataProject>();
string sql = @"SELECT TOP " + 100 + @"
[ProjectId],
[ProjectName],
[OtherName],
[PinYin],
[PinYinAll]
FROM [FXTProject].[#tName#] WHERE ProjectId NOT IN
(SELECT TOP " + (page - 1) * 100 + @" [ProjectId] FROM [FXTProject].[#tName#] WHERE CityID = [#CityID#])
AND CityID = [#CityID#]";
sql = sql.Replace("[#tName#]", tName).Replace("[#CityID#]", Convert.ToString(CityID));
DataTable data = DBUtils.getData(CaseUrl, sql);
list = ConvUtils <DataProject> .ConvertToList(data);
return(list);
}
/// <summary>
/// 分页获取楼盘网络名称列表
/// </summary>
/// <param name="cityId">城市id</param>
/// <param name="pageNumber">页码</param>
/// <param name="pageSize">每页数量</param>
/// <returns></returns>
public List <SYS_ProjectMatch> GetNetworkNames(int cityId, int pageNumber, int pageSize)
{
if (pageNumber < 1)
{
pageNumber = 1;
}
if (pageSize < 1)
{
pageSize = 100;
}
var colNames = "ProjectNameId,NetName,ProjectName,CityId";
var strWhere = string.Format("where CityId={0} and FXTCompanyId={1}", cityId, 25);
int startIndex = (pageNumber - 1) * pageSize + 1;
int endIndex = pageNumber * pageSize;
string pageSql = string.Format("Select {0} From (Select {0}, row_number() over(Order By [{1}]) as rowNumber From [{2}].[dbo].[{3}] {4}) XCode_T1 Where rowNumber Between {5} And {6}", colNames, "Id", "fxtproject", "SYS_ProjectMatch", strWhere, startIndex, endIndex);
DataTable data = DBUtils.GetData(CaseUrl, pageSql);
var list = ConvUtils <SYS_ProjectMatch> .ConvertToList(data);
return(list);
}
/// <summary>
/// 分页查询案例数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="paramMaps"></param>
/// <returns></returns>
public List <Model.DataCase> PagingQueryDataCase(int pageIndex, int pageSize, Dictionary <string, string> paramMaps)
{
string sql = @"SELECT TOP " + pageSize + @" * FROM
(
SELECT [ProjectName]
,[行政区] as 'AreaName'
,case when 所在楼层_列表页 is not null and 所在楼层_列表页 <> '' then 所在楼层_列表页
when (所在楼层_列表页 is null or 所在楼层_列表页 = '') and (所在楼层_详情页 is not null and 所在楼层_详情页 <> '') then 所在楼层_详情页
else null end as 'FloorNumber'
,case when 总楼层_列表页 is not null and 总楼层_列表页 <> '' then 总楼层_列表页
when (总楼层_列表页 is null or 总楼层_列表页 = '') and (总楼层_详情页 is not null and 总楼层_详情页 <> '') then 总楼层_详情页
else null end as 'TotalFloor'
,convert(nvarchar(10),[案例时间],21) as 'CaseDate'
,[用途描述] as 'PurposeName'
,[面积] as 'BuildingArea'
,[单价] as 'UnitPrice'
,[总价_万]*10000 as 'TotalPrice'
,[案例类型描述] as 'CaseTypeName'
,[朝向归类] as 'FrontName'
,[建筑类型描述] as 'BuildingTypeName'
,[户型归类] as 'HouseTypeName'
,[结构归类] as 'StructureName'
,[建筑年代] as 'BuildingDate'
,[装修归类] as 'ZhuangXiu'
,[币种描述] as 'MoneyUnitName'
,[配套设施] as 'PeiTao'
,[数据来源] as 'SourceName'
,[来源电话] as 'SourcePhone'
,[数据链接] as 'SourceLink'
,城市表.CityId as 'CityID'
,案例表.案例统计周 as 'RecordWeek'
,案例表.[房源编号] as 'HouseIndex'
FROM [FxtData_Case].[dbo].[住宅案例_出售] as 案例表
left join FxtDataCenter.dbo.SYS_City as 城市表
on 案例表.CityId=城市表.CityId
left join dbo.户型 as 户型表
on 案例表.户型=户型表.code
left join dbo.朝向 as 朝向表
on 案例表.朝向=朝向表.code
left join dbo.结构 as 结构表
on 案例表.结构=结构表.code
left join dbo.装修 as 装修表
on 案例表.装修=装修表.code
left join dbo.用途 as 用途表
on 案例表.用途=用途表.code
left join dbo.建筑类型 as 建筑类型表
on 案例表.建筑类型=建筑类型表.code
left join dbo.币种 as 币种表
on 案例表.币种=币种表.code
left join dbo.案例类型 as 案例类型表
on 案例表.案例类型=案例类型表.code
) t1
WHERE 1=1 AND t1.HouseIndex NOT IN
(
SELECT TOP " + (pageIndex - 1) * pageSize + @" t2.HouseIndex FROM
(
SELECT convert(nvarchar(10),[案例时间],21) as 'CaseDate'
,城市表.CityId as 'CityID'
,案例表.[房源编号] as 'HouseIndex'
FROM [FxtData_Case].[dbo].[住宅案例_出售] as 案例表
left join FxtDataCenter.dbo.SYS_City as 城市表
on 案例表.CityId=城市表.CityId
left join dbo.户型 as 户型表
on 案例表.户型=户型表.code
left join dbo.朝向 as 朝向表
on 案例表.朝向=朝向表.code
left join dbo.结构 as 结构表
on 案例表.结构=结构表.code
left join dbo.装修 as 装修表
on 案例表.装修=装修表.code
left join dbo.用途 as 用途表
on 案例表.用途=用途表.code
left join dbo.建筑类型 as 建筑类型表
on 案例表.建筑类型=建筑类型表.code
left join dbo.币种 as 币种表
on 案例表.币种=币种表.code
left join dbo.案例类型 as 案例类型表
on 案例表.案例类型=案例类型表.code
)t2
WHERE 1=1
and t2.CityId =[#CityID#]
and t2.CaseDate >= '[#StartTime#]'
and t2.CaseDate < '[#EndTime#]'
order by t2.CaseDate
)
and t1.CityId =[#CityID#]
and t1.CaseDate >= '[#StartTime#]'
and t1.CaseDate < '[#EndTime#]'
order by t1.CaseDate
";
foreach (KeyValuePair <string, string> item in paramMaps)
{
logger.DebugFormat("PagingQueryDataCase key={0}, Value = {1}", item.Key, item.Value);
sql = sql.Replace(item.Key, item.Value);
}
logger.DebugFormat("PagingQueryDataCase SQL: {0}", sql);
List <Model.DataCase> list = new List <Model.DataCase>();
try
{
DataTable dt_housecase = DBUtils.getData(CaseUrl, sql);
list = ConvUtils <Model.DataCase> .ConvertToList(dt_housecase);
logger.DebugFormat("PagingQueryDataCase list Count={0}", list.Count);
}
catch (Exception e)
{
logger.Error(e);
logger.ErrorFormat("PagingQueryDataCase SQL: {0}", sql);
}
return(list);
}
/// <summary>
/// 分页查询案例数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="paramMaps"></param>
/// <returns></returns>
public List <DataCase> PagingQueryDataCase(int pageIndex, int pageSize, Dictionary <string, string> paramMaps, out bool flag)
{
flag = false;
var pageSql = string.Format(@"
Select * From (
SELECT [ProjectName]
,[行政区] as 'AreaName'
,case when 所在楼层_列表页 is not null and 所在楼层_列表页 <> '' then 所在楼层_列表页
when (所在楼层_列表页 is null or 所在楼层_列表页 = '') and (所在楼层_详情页 is not null and 所在楼层_详情页 <> '') then 所在楼层_详情页
else null end as 'FloorNumber'
,case when 总楼层_列表页 is not null and 总楼层_列表页 <> '' then 总楼层_列表页
when (总楼层_列表页 is null or 总楼层_列表页 = '') and (总楼层_详情页 is not null and 总楼层_详情页 <> '') then 总楼层_详情页
else null end as 'TotalFloor'
,convert(nvarchar(10),[案例时间],21) as 'CaseDate'
,[用途描述] as 'PurposeName'
,[面积] as 'BuildingArea'
,[单价] as 'UnitPrice'
,[总价_万]*10000 as 'TotalPrice'
,[案例类型描述] as 'CaseTypeName'
,[朝向归类] as 'FrontName'
,[建筑类型描述] as 'BuildingTypeName'
,[户型归类] as 'HouseTypeName'
,[结构归类] as 'StructureName'
,[建筑年代] as 'BuildingDate'
,[装修归类] as 'ZhuangXiu'
,[币种描述] as 'MoneyUnitName'
,[配套设施] as 'PeiTao'
,[数据来源] as 'SourceName'
,[来源电话] as 'SourcePhone'
,[数据链接] as 'SourceLink'
,案例表.CityId as 'CityID'
,案例表.案例统计周 as 'RecordWeek'
,案例表.[房源编号] as 'HouseIndex'
,row_number() over(Order By [案例时间]) as rowNumber
FROM [FxtData_Case].[dbo].[住宅案例_出售] as 案例表 with (nolock)
left join FxtDataCenter.dbo.SYS_City as 城市表 with (nolock)
on 案例表.CityId=城市表.CityId
left join dbo.户型 as 户型表 with (nolock)
on 案例表.户型=户型表.code
left join dbo.朝向 as 朝向表 with (nolock)
on 案例表.朝向=朝向表.code
left join dbo.结构 as 结构表 with (nolock)
on 案例表.结构=结构表.code
left join dbo.装修 as 装修表 with (nolock)
on 案例表.装修=装修表.code
left join dbo.用途 as 用途表 with (nolock)
on 案例表.用途=用途表.code
left join dbo.建筑类型 as 建筑类型表 with (nolock)
on 案例表.建筑类型=建筑类型表.code
left join dbo.币种 as 币种表 with (nolock)
on 案例表.币种=币种表.code
left join dbo.案例类型 as 案例类型表 with (nolock)
on 案例表.案例类型=案例类型表.code
Where 案例表.CityId = {0} and 案例时间>='{1}' and 案例时间 <= '{2}'
) XCode_T1
Where rowNumber Between {3} And {4}
", paramMaps["[#CityID#]"], paramMaps["[#StartTime#]"], paramMaps["[#EndTime#]"], (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
logger.DebugFormat("PagingQueryDataCase SQL: {0}", pageSql);
List <DataCase> list = new List <DataCase>();
try
{
DataTable dt_housecase = DBUtils.GetData(CaseUrl, pageSql);
if (dt_housecase.Columns.Contains("rowNumber"))
{
dt_housecase.Columns.Remove("rowNumber");
}
list = ConvUtils <DataCase> .ConvertToList(dt_housecase);
logger.DebugFormat("PagingQueryDataCase list Count={0}", list.Count);
}
//catch (SqlException dbex)
//{
//}
//catch (Win32Exception win32ex)
//{
//}
catch (Exception e)
{
//if (e.Message.Contains("Timeout") || e.Message.Contains("等待的操作过时"))
//{
//}
flag = true;
logger.Error(e);
}
return(list);
}
