/***
* 查询逻辑是检查结果通过两张映射表直接拿到检查区域，再去关联检查员信息，如果他的所属区域包含在任务范围内，此条任务可查看。
* 复查和检查有一点不同的是检查是无论检查结果有没有不合格的都要查询出来，但是复查不一样，如果这个任务内他所负责的区域内所有的房子
* 都是合格的，那么他应该卡不见这个任务，所以用exist内的子查询实现这个条件。
***/
public List <wy_check_task> GetTaskInfo(string OPEN_ID, int page = 1, int limit = 10)
{
//var list = DB.Db().Queryable<wy_check_task, wy_map_checkplandetail, wy_checkplan_detail, wy_map_region, wy_region_director>
// ((a, b, c, d, e) => new object[]
// {
// JoinType.Inner,a.TASK_ID==b.TASK_ID,
// JoinType.Inner,b.PLAN_DETAIL_ID==c.PLAN_DETAIL_ID&&c.IS_DELETE==0,
// JoinType.Inner,c.PLAN_DETAIL_ID==d.PLAN_DETAIL_ID,
// JoinType.Inner,d.REGION_CODE==e.SSQY&&e.WX_OPEN_ID==OPEN_ID
// })
// .Where(a => a.IS_DELETE == 0)
// .GroupBy(a => new { a.TASK_ID })
// .Skip((page - 1) * limit).Take(limit).ToList();
string sql = "SELECT DISTINCT a.* FROM wy_check_task a " +
" JOIN wy_map_checkplandetail b ON a.TASK_ID=b.TASK_ID" +
" JOIN wy_map_region c ON b.PLAN_DETAIL_ID=c.PLAN_DETAIL_ID" +
" JOIN wy_region_director d ON c.REGION_CODE=d.SSQY AND d.IS_DELETE=0 AND d.WX_OPEN_ID='" + OPEN_ID + "'" +
" WHERE a.IS_DELETE=0" +
" AND EXISTS (" +
" SELECT 1 FROM wy_check_result e" +
" JOIN wy_houseinfo f ON e.FWID=f.FWID AND f.IS_DELETE=0" +
" JOIN wy_region_director g ON f.SSQY=g.SSQY AND f.IS_DELETE=0 " +
" WHERE e.TASK_ID=a.TASK_ID AND g.WX_OPEN_ID=d.WX_OPEN_ID AND e.JCJG!=1)" +
" ORDER BY a.CJSJ";
var list = DB.Db().SqlQueryable <wy_check_task>(sql).Skip((page - 1) * limit).Take(limit).ToList();
return(list);
}
public List <object> GetShopDetails(string OpenId)
{
var List = _db.Db().Queryable <wy_shopinfo, wy_houseinfo, tax_dictionary, tax_dictionary, tax_dictionary>((sh, ho, d1, d2, d3) => new object[] {
JoinType.Left, sh.FWID == ho.FWID,
JoinType.Left, ho.LSFGS == d1.Code && d1.ParentCode == "LSFGS",
JoinType.Left, ho.JGLX == d2.Code && d2.ParentCode == "JGLX",
JoinType.Left, ho.SSQY == d3.Code && d3.ParentCode == "SSQY",
}).Where((sh, ho, d1, d2, d3) => sh.OPEN_ID == OpenId && sh.IS_DELETE == 0 && ho.IS_DELETE == 0)
.Select <object>((sh, ho, d1, d2, d3) => new
{
ho.FWID, ho.FWSX, ho.FWMC, ho.FWBH, SSQY = d3.Name, ho.JZMJ, LSFGS = d1.Name,
ho.ZLWZ, JGLX = d2.Name, ho.ZCYZ, sh.CZ_SHID, sh.SHOP_NAME, sh.SHOPBH, sh.ZHXM,
sh.JYNR, sh.SHOP_STATUS, ho.WATER_NUMBER, ho.ELE_NUMBER, ho.CID
}).ToList();
return(List);
}
public Dictionary <string, object> GetRecheckReviewData(string openId)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
try
{
var list = _Db.Db().Queryable <wy_shopinfo, wy_houseinfo, wy_check_result, wy_check_task>((a, b, c, d) => new object[] {
JoinType.Inner, a.CZ_SHID == b.CZ_SHID && a.OPEN_ID == openId && b.IS_DELETE == 0 && a.IS_DELETE == 0,
JoinType.Inner, c.FWID == b.FWID && c.JCJG != 1 && c.IS_REVIEW != 1,
JoinType.Inner, d.TASK_ID == c.TASK_ID
}).OrderBy((a, b, c, d) => c.CJSJ, OrderByType.Desc).Select((a, b, c, d) => new { b.FWBH, b.FWMC, d.RWBH, d.RWMC, c.CJSJ, c.RESULT_ID, c.CJR }).ToList();
dic.Add("list", list);
}
catch (Exception ex)
{
dic.Add("list", null);
return(dic);
}
return(dic);
}
/// <summary>
/// 默认Using方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="func"></param>
/// <returns></returns>
public T DBAble <T>(Func <SqlSugarClient, T> func)
{
using (var db = _db.Db()) {
return(func(db));
}
}
public List <wy_check_task> GetTaskInfo(string status, string statrtime, string endtime, string OPEN_ID, int page, int limit)
{
var list = DB.Db().Queryable <wy_check_task, wy_map_checkplandetail, wy_checkplan_detail, wy_map_region, wy_region_director>
((a, b, c, d, e) => new object[]
{
JoinType.Inner, a.TASK_ID == b.TASK_ID,
JoinType.Inner, b.PLAN_DETAIL_ID == c.PLAN_DETAIL_ID && c.IS_DELETE == 0,
JoinType.Inner, c.PLAN_DETAIL_ID == d.PLAN_DETAIL_ID,
JoinType.Inner, d.REGION_CODE == e.SSQY && e.WX_OPEN_ID == OPEN_ID
})
.WhereIF(!string.IsNullOrEmpty(statrtime), a => a.RWKSSJ >= DateTime.Parse(statrtime))
.WhereIF(!string.IsNullOrEmpty(endtime), a => a.RWJSSJ <= DateTime.Parse(endtime))
.Where(a => a.IS_DELETE == 0)
.GroupBy(a => new { a.TASK_ID })
.OrderBy(a => new { a.CJSJ, a.RWBH }, OrderByType.Desc)
.Skip((page - 1) * limit).Take(limit).ToList();
return(list);
}
/// <summary>
/// 数据字典
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public Dictionary <string, object> GetBaseData(DictionaryModel model)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
var areaList = _Db.Db().Queryable <tax_dictionary>().Where(n => n.ParentCode == model.areaCode).Select(m => new { value = m.Code, text = m.Name }).ToList();
var companyList = _Db.Db().Queryable <tax_dictionary>().Where(n => n.ParentCode == model.companyCode).Select(m => new { value = m.Code, text = m.Name }).ToList();
dic.Add("areaList", areaList);
dic.Add("companyList", companyList);
return(dic);
}
