public async Task <ApiResult> ListEqpByIDs(EqpQueryByIDParm parm) { ApiResult ret = new ApiResult(); try { parm.page = parm.page == 0 ? 1 : parm.page; parm.rows = parm.rows == 0 ? PAGESIZE : parm.rows; parm.sort = string.IsNullOrWhiteSpace(parm.sort) ? "id" : parm.sort; parm.order = parm.order.ToLower() == "desc" ? "desc" : "asc"; EqpView ev = await _eqpRepo.ListEqpByIDs(parm); List <Equipment> eqps = ev.rows; List <AllArea> laa = await _eqpRepo.GetAllArea(); foreach (var item in eqps) { var tmp = laa.Where(a => a.Tablename == item.LocationBy && a.ID == item.Location) .FirstOrDefault(); if (tmp != null) { item.LocationName = tmp.AreaName; } } ret.data = ev; return(ret); } catch (Exception ex) { ret.code = Code.Failure; ret.msg = ex.Message; return(ret); } }
public async Task <EqpView> ListEqpByIDs(EqpQueryByIDParm parm) { return(await WithConnection(async c => { StringBuilder sql = new StringBuilder(); sql.Append("SELECT distinct a.*,u1.user_name as created_name,u2.user_name as updated_name, ") .Append(" et.type_name,d.name as sub_code_name,ot.name,f1.name as supplierName,f2.name as manufacturerName ") .Append(" FROM equipment a ") .Append(" left join user u1 on a.created_by=u1.id ") .Append(" left join user u2 on a.updated_by=u2.id ") .Append(" left join equipment_type et on et.id=a.eqp_type ") .Append(" left join org_tree ot on ot.id=a.team ") .Append(" left join firm f1 on f1.id=a.Supplier ") .Append(" left join firm f2 on f2.id=a.Manufacturer ") .Append(" left join dictionary_tree d on a.sub_system=d.id "); StringBuilder whereSql = new StringBuilder(); whereSql.Append(" where a.is_del=" + (int)IsDeleted.no); if (parm.IDs != null && parm.IDs.Count > 0) { whereSql.Append(" AND a.id in @IDs"); } sql.Append(whereSql) .Append(" order by a." + parm.sort + " " + parm.order) .Append(" limit " + (parm.page - 1) * parm.rows + "," + parm.rows); List <Equipment> ets = (await c.QueryAsync <Equipment>(sql.ToString(), new{ IDs = parm.IDs })).ToList(); sql.Clear(); sql.Append("select count(*) FROM equipment a "); int total = await c.QueryFirstOrDefaultAsync <int>( sql.ToString() + whereSql.ToString(), new { IDs = parm.IDs }); EqpView ret = new EqpView(); ret.rows = ets; ret.total = total; return ret; })); }
public async Task <EqpView> GetPageByParm(EqpQueryParm parm) { return(await WithConnection(async c => { StringBuilder sql = new StringBuilder(); sql.Append("SELECT distinct a.*,u1.user_name as created_name,u2.user_name as updated_name, ") .Append(" et.type_name,d.name as sub_code_name,ot.name,f1.name as supplierName,f2.name as manufacturerName, ") .Append(" l.line_name FROM equipment a ") .Append(" left join user u1 on a.created_by=u1.id ") .Append(" left join user u2 on a.updated_by=u2.id ") .Append(" left join equipment_type et on et.id=a.eqp_type ") .Append(" left join org_tree ot on ot.id=a.team ") .Append(" left join metro_line l on l.id=a.line ") .Append(" left join firm f1 on f1.id=a.Supplier ") .Append(" left join firm f2 on f2.id=a.Manufacturer ") .Append(" left join dictionary_tree d on a.sub_system=d.id "); StringBuilder whereSql = new StringBuilder(); whereSql.Append(" where a.is_del=" + (int)IsDeleted.no); if (parm.SearchSubSystem != null) { whereSql.Append(" and a.sub_system =" + parm.SearchSubSystem); } if (parm.SearchType != null) { whereSql.Append(" and a.eqp_type =" + parm.SearchType); } if (!string.IsNullOrWhiteSpace(parm.SearchCode)) { whereSql.Append(" and a.eqp_code like '%" + parm.SearchCode + "%' "); } if (parm.SearchLocation != null && parm.SearchLocationBy != null) { whereSql.Append(" and a.location=" + parm.SearchLocation) .Append(" and a.location_by =" + parm.SearchLocationBy); } if (parm.SearchTopOrg != null) { whereSql.Append(" and a.top_org=" + parm.SearchTopOrg); } //if (parm.SearchLine != null) //{ // whereSql.Append(" and a.line=" + parm.SearchLine); //} if (!string.IsNullOrWhiteSpace(parm.LocationPath)) { string[] local = parm.LocationPath.Split(','); for (int i = 0; i < local.Length; ++i) { whereSql.Append(" AND FIND_IN_SET(" + local[i] + ",a.location_path)=" + (i + 2)); } } sql.Append(whereSql) .Append(" order by a." + parm.sort + " " + parm.order) .Append(" limit " + (parm.page - 1) * parm.rows + "," + parm.rows); List <Equipment> ets = (await c.QueryAsync <Equipment>(sql.ToString())).ToList(); sql.Clear(); sql.Append("select count(*) FROM equipment a "); int total = await c.QueryFirstOrDefaultAsync <int>( sql.ToString() + whereSql.ToString()); EqpView ret = new EqpView(); ret.rows = ets; ret.total = total; return ret; })); }