public static IEnumerable <RepairRecordDetail> GetKanBan(int workshopId = -1, DateTime startTime = default(DateTime), DateTime endTime = default(DateTime), IEnumerable <string> fields = null) { var field = fields != null && fields.Any() ? fields.Select(x => $"a.`{x}`").Join(", ") : RepairRecord.GetField(new List <string> { "DeviceCode" }, "a."); var sql = $"SELECT {field}, IFNULL(d.`Code`, a.DeviceCode) DeviceCode, IFNULL(c.FaultTypeName, b.FaultTypeName) FaultTypeName, IFNULL(c.FaultDescription, b.FaultDescription) FaultDescription FROM `fault_device_repair` a " + $"JOIN `fault_type` b ON a.FaultTypeId = b.Id " + $"LEFT JOIN `fault_type` c ON a.FaultTypeId1 = c.Id " + $"LEFT JOIN `device` d ON a.DeviceId = d.Id"; sql += $" WHERE `State` != @state " + $"{(workshopId == -1 ? "" : (" AND a.WorkshopId = @workshopId"))}" + $"{((startTime == default(DateTime) || endTime == default(DateTime)) ? "" : " AND a.FaultTime >= @startTime AND a.FaultTime <= @endTime")} " + $"AND a.`MarkedDelete` = 0 AND a.`Cancel` = 0;"; var faults = ServerConfig.ApiDb.Query <RepairRecordDetail>(sql, new { state = RepairStateEnum.Complete, workshopId, startTime, endTime, }); var maintainers = MaintainerHelper.Instance.GetAll <Maintainer>().ToArray(); foreach (var fault in faults) { var mans = maintainers.Where(x => fault.Maintainers.Any(y => y == x.Account)); fault.Name = mans.Select(x => x.Name).Join() ?? ""; fault.Account = mans.Select(x => x.Account).Join() ?? ""; fault.Phone = mans.Select(x => x.Phone).Join() ?? ""; } return(faults); }
public static IEnumerable <RepairRecordDetail> GetRepairRecordDetails(int workshopId, DateTime startTime = default(DateTime), DateTime endTime = default(DateTime), int condition = 0, string code = "", DateTime fStartTime = default(DateTime), DateTime fEndTime = default(DateTime), DateTime eStartTime = default(DateTime), DateTime eEndTime = default(DateTime), int qId = 0, string maintainer = "-1", string faultSolver = "-1", int faultType = -1, int priority = -1, int grade = -1, int cancel = -1, IEnumerable <string> fields = null) { var field = fields != null && fields.Any() ? fields.Select(x => $"a.`{x}`").Join(", ") : RepairRecord.GetField(new List <string> { "DeviceCode" }, "a."); if (!maintainer.IsNullOrEmpty() && maintainer != "-1") { maintainer = $"%{maintainer},%"; } if (!faultSolver.IsNullOrEmpty() && faultSolver != "-1") { faultSolver = $"%{faultSolver},%"; } var delete = cancel == 1 ? 1 : 0; var sql = $"SELECT {field}, IFNULL(d.`Code`, a.DeviceCode) DeviceCode, b.FaultTypeName, b.FaultDescription, c.FaultTypeName FaultTypeName1, c.FaultDescription FaultDescription1 FROM `fault_device_repair` a " + $"JOIN `fault_type` b ON a.FaultTypeId = b.Id " + $"JOIN `fault_type` c ON a.FaultTypeId1 = c.Id " + $"LEFT JOIN `device` d ON a.DeviceId = d.Id"; sql += $" WHERE `State` = @fState " + $"{(workshopId == -1 ? "" : (" AND a.WorkshopId " + (condition == 0 ? "=" : "!=") + " @workshopId"))}" + $"{(cancel == -1 ? "" : " AND a.Cancel = @cancel")}" + $"{((startTime == default(DateTime) || endTime == default(DateTime)) ? "" : " AND a.SolveTime >= @startTime AND a.SolveTime <= @endTime")}" + $"{(code.IsNullOrEmpty() ? "" : (" AND a.DeviceCode " + (condition == 0 ? "=" : "!=") + " @code"))}" + $"{((fStartTime == default(DateTime) || fEndTime == default(DateTime)) ? "" : " AND a.FaultTime >= @startTime AND a.FaultTime <= @endTime")}" + $"{(faultType == -1 ? "" : (" AND a.FaultTypeId " + (condition == 0 ? "=" : "!=") + " @faultType"))}" + $"{(priority == -1 ? "" : (" AND a.Priority " + (condition == 0 ? "=" : "!=") + " @priority"))}" + $"{(grade == -1 ? "" : (" AND a.Grade " + (condition == 0 ? "=" : "!=") + " @grade"))}" + $"{((maintainer.IsNullOrEmpty() || maintainer == "-1") ? "" : (" AND CONCAT(a.Maintainer, \",\") " + (condition == 0 ? "LIKE " : " NOT LIKE ") + " @maintainer"))}" + $"{((faultSolver.IsNullOrEmpty() || faultSolver == "-1") ? "" : (" AND CONCAT(a.FaultSolver, \",\") " + (condition == 0 ? "LIKE " : " NOT LIKE ") + " @faultSolver"))}" + $"{((eStartTime == default(DateTime) || eEndTime == default(DateTime)) ? "" : " AND a.EstimatedTime >= @eStartTime AND a.EstimatedTime <= @eEndTime")}" + $"{(qId == 0 ? "" : (" AND a.Id " + (condition == 0 ? "=" : "!=") + " @qId"))} AND a.MarkedDelete = @delete"; var faults = ServerConfig.ApiDb.Query <RepairRecordDetail>(sql, new { fState = RepairStateEnum.Complete, workshopId, cancel, startTime, endTime, condition, code, fStartTime, fEndTime, faultType, priority, grade, maintainer, faultSolver, eStartTime, eEndTime, qId, delete }); var maintainers = MaintainerHelper.Instance.GetAll <Maintainer>().ToArray(); foreach (var fault in faults) { var mans = maintainers.Where(x => fault.Maintainers.Any(y => y == x.Account)); fault.Name = mans.Select(x => x.Name).Join() ?? ""; fault.Account = mans.Select(x => x.Account).Join() ?? ""; fault.Phone = mans.Select(x => x.Phone).Join() ?? ""; } return(faults); }