/// <summary>
/// 查询前9项
/// </summary>
/// <param name="index">页数</param>
/// <param name="pagesize">页大小</param>
/// <returns></returns>
public async Task <ResponseModel <T4_House_Part> > QueryPage(GenericQueryModel queryBody, int index, int pagesize)
{
string wheresql = Build(queryBody);
// 获取修改的列
List <t4_code> fileds = await new t4_code().SelectAsync <t4_code>();
// 获取前9项实体属性
Type typeInfo = typeof(T4_House_Part);
var properties = typeInfo.GetProperties().ToList();
// 获取房源
List <T4_House_Part> houseList = new T4_House_Part().SelectPart <T4_House_Part>(typeof(T4_House).Name, index, pagesize, wheresql, "column1");
Parallel.ForEach(houseList, info =>
{
// 获取修改历史信息
List <t4_json> jsonList = new t4_json(true).Select <t4_json>(info.column1, "houseid");
Parallel.ForEach(jsonList, p =>
{
List <T4_ModifyJsonModel> modifyInfoList =
JsonConvert.DeserializeObject <List <T4_ModifyJsonModel> >(p.jsonstr);
T4_ModifyJsonModel mdify = modifyInfoList.OrderByDescending(a => a.Column207).FirstOrDefault();
t4_code columnName = fileds.Where(o => o.id == p.codeid).FirstOrDefault();
PropertyInfo proInfo = properties.Where(o => o.Name == columnName.name).FirstOrDefault();
if (proInfo != null)
{
proInfo.SetValue(info, mdify.Column205);
}
});
});
ResponseModel <T4_House_Part> resModel = new ResponseModel <T4_House_Part>(houseList);
return(resModel);
}
public async Task <ResponseModel <t6_house> > QueryPageLike(GenericQueryModel queryBody, int index, int pagesize)
{
string wheresql = GenericQueryAnalizer.Build(queryBody);
string sql = $@"select house.column1,house.column2,house.column3,house.column4,house.column5,house.column6,house.column7,house.column8,house.column9,house.column10,house.column11,house.column12,house.column13,house.column14
from t6_house as house
join (select max(column1) as column1,column2
from t6_house
where 1=1 {wheresql}
group by column2
order by column1 desc offset {pagesize * (index - 1)} ROW FETCH NEXT {pagesize} rows only
) as id
on house.column1 = id.column1";
List <t6_house> list = dataService.GetModelList <t6_house>(sql);
return(new ResponseModel <t6_house>(list));
}
public async void QueryPageTime()
{
GenericQueryModel query = new GenericQueryModel();
query.TryAddQuery("column3", "1", "like");
List <TimeSpan> listTime = new List <TimeSpan>();
List <ResponseModel <T4_House_Part> > result = new List <ResponseModel <T4_House_Part> >();
for (int i = 100; i < 400; i = i + 100)
{
Stopwatch sw = new Stopwatch();
sw.Start();
result.Add(await zeroJsonService.QueryPage(query, i, 150));
sw.Stop();
listTime.Add(sw.Elapsed);
}
}
/// <summary>
/// 模糊查询
/// </summary>
/// <param name="queryBody"></param>
/// <param name="index"></param>
/// <param name="pagesize"></param>
/// <returns></returns>
public async Task <ResponseModel <t1_history_nunety> > QueryPageLike(GenericQueryModel queryBody, int index, int pagesize)
{
string whereSql = WhereSql <t1_code, t1_history>(queryBody);
string sql = $@"select code.name,history.value,history.houseid,history.createtime
from t1_history as history
join (select house.houseid
from ({whereSql}) as house
order by house.houseid desc offset {pagesize * (index - 1)} row fetch next {pagesize} rows only
) as houseid
on history.houseid = houseid.houseid
left join t1_code as code
on history.codeid = code.id
where history.codeid<15
";
List <t1_history_nunety> listModel = new List <t1_history_nunety>();
List <nunety> nunetyModel = dataService.GetModelList <nunety>(sql);
var houseList = nunetyModel.GroupBy(o => o.houseid);
var objHouse = new Object();
Parallel.ForEach(houseList, house => //根据房ID分组然后遍历
{
t1_history_nunety model = new t1_history_nunety();
model.houseid = house.Key;
Type type = model.GetType();
var codeList = house.GroupBy(o => o.name);
foreach (var code in codeList) //根据code分组然后遍历
{
var v = code.Where(o => o.createtime == code.Max(j => j.createtime)).
Select(o => o.value).FirstOrDefault();//每组name取修改时间最大的一个
PropertyInfo Info = type.GetProperty(code.Key.ToLower());
if (Info != null)
{
Info.SetValue(model, v.ToString(), null);
}
}
lock (objHouse)
{
listModel.Add(model);
}
});
return(new ResponseModel <t1_history_nunety>(listModel));
}
public async void QueryPageLikeTime()
{
List <TimeSpan> listTime = new List <TimeSpan>();
List <ResponseModel <t6_house> > result = new List <ResponseModel <t6_house> >();
for (int i = 1; i < 5; i = i + 1)
{
Stopwatch sw = new Stopwatch();
GenericQueryModel queryBody = new GenericQueryModel()
{
new GenericQueryItem()
{
Name = "column3", Value = "1".ToString(), QueryType = "like"
}
};
sw.Start();
result.Add(await zeroXService.QueryPageLike(queryBody, i, 15));
sw.Stop();
listTime.Add(sw.Elapsed);
}
}
/// <summary>
/// 纯90度表条件拼接
/// </summary>
/// <typeparam name="CodeTable">字段表</typeparam>
/// <typeparam name="T">值表</typeparam>
/// <param name="queryBody">条件</param>
/// <param name="resltName">最终结果的字段名</param>
/// <param name="codeTableRelationName">字段表的关联字段名</param>
/// <param name="codeTableNanmeName">字段表的条件字段名</param>
/// <param name="tRelationName">值表的关联字段名</param>
/// <param name="tValueName">值表值字段名</param>
/// <returns></returns>
private string WhereSql <CodeTable, T>(GenericQueryModel queryBody,
string resltName = "houseid",
string codeTableRelationName = "id",
string codeTableNanmeName = "name",
string tRelationName = "codeid",
string tValueName = "value"
) where CodeTable : class
{
if (typeof(CodeTable).GetProperty(codeTableRelationName) == null ||
typeof(CodeTable).GetProperty(codeTableNanmeName) == null ||
typeof(T).GetProperty(tRelationName) == null ||
typeof(T).GetProperty(resltName) == null
)
{
return("");
}
List <CodeTable> list = dataService.GetModelList <CodeTable>($"select {codeTableRelationName},{codeTableNanmeName} from {typeof(CodeTable).Name}");
string tTableName = typeof(T).Name;
string whereSql = "del";
string table = $" select distinct {resltName} from {tTableName} where ";
foreach (var item in queryBody)
{
switch (item.QueryType.Trim())
{
case "like":
whereSql =
whereSql + " InterSect (" + table +
$@" {tRelationName} = '{list.Where(o => o.GetType().GetProperty("name").GetValue(o).ToString() == item.Name).
Select(o => o.GetType().GetProperty("id").GetValue(o)).FirstOrDefault()}' " +
" and " +
$" {tValueName} like '%{item.Value}%') ";
break;
}
}
return(whereSql.Replace("del InterSect", " "));
}
public async Task <ResponseModel <t6_house> > QyeryMethodLike(GenericQueryModel queryBody, int index, int pagesize)
{
return(null);
}
public async Task <ResponseModel <t6_house> > QueryPageLike([FromBody] GenericQueryModel queryBody, int index = 1, int pagesize = 15)
{
return(await zeroXService.QueryPageLike(queryBody, index, pagesize));
}
public Task <ResponseModel <T4_House_Part> > QueryPage([FromBody] GenericQueryModel queryBody, int index = 1, int pagesize = 15)
{
return(zeroJsonService.QueryPage(queryBody, index, pagesize));
}
public async Task <ResponseModel <t6_house> > QyeryMethodLike([FromBody] GenericQueryModel queryBody, int index, int pagesize)
{
return(await zeroX3Service.QyeryMethodLike(queryBody, index, pagesize));
}
public async Task <ResponseModel <t1_history_nunety> > QueryPageLike([FromBody] GenericQueryModel queryBody, int index = 1, int pagesize = 15)
{
return(await zeroService.QueryPageLike(queryBody, index, pagesize));
}