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;
}));
}
