using Microsoft.EntityFrameworkCore; using Ropin.Inspection.Model; using Ropin.Inspection.Model.Entities; using Ropin.Inspection.Model.SearchModel; using Ropin.Inspection.Model.ViewModel; using System; using System.Collections.Generic; using System.Linq; using System.Security.Cryptography.X509Certificates; using System.Text; using System.Threading.Tasks; namespace Ropin.Inspection.Repository { public class TmtnRepairOrderRepository : RepositoryBase, ITmtnRepairOrderRepository { public TmtnRepairOrderRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task GetRepairStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = ""; sql = @"SELECT CASE WHEN ISNULL(SUM(if(C_Status='1',1,0))) THEN 0 ELSE SUM(if(C_Status='1',1,0)) END AS Approval, CASE WHEN ISNULL(SUM(if(C_Status='2',1,0))) THEN 0 ELSE SUM(if(C_Status='2',1,0)) END AS Confirm , CASE WHEN ISNULL(SUM(if(C_Status='3',1,0))) THEN 0 ELSE SUM(if(C_Status='3',1,0)) END AS RepairOn , CASE WHEN ISNULL(SUM(if(C_Status='4',1,0))) THEN 0 ELSE SUM(if(C_Status='4',1,0)) END AS Complete, CASE WHEN ISNULL(SUM(if(C_Status='5',1,0))) THEN 0 ELSE SUM(if(C_Status='5',1,0)) END AS Cancel, CASE WHEN ISNULL(SUM(if(C_Status='6',1,0))) THEN 0 ELSE SUM(if(C_Status='6',1,0)) END AS RepairRework, CASE WHEN ISNULL(SUM(if(C_Status='7',1,0))) THEN 0 ELSE SUM(if(C_Status='7',1,0)) END AS RepairCompleted FROM ( SELECT A.C_Status from TMTN_RepairOrder A INNER JOIN TDEV_DevStore B ON A.C_DevStoreCode = B.C_ID WHERE B.C_StoreCode = @storeCode AND B.C_Status != '0' AND A.D_CreateOn >= date_sub(NOW(),INTERVAL 6 MONTH) )C "; RepairStatistics record; record = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(record); } public Task> GetRecord12MonthStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = @"SELECT date_format(click_date,'%m') AS SpecificMonth, SUM(CASE C_Status WHEN 4 THEN 1 ELSE 0 END) AS RecordCount from( SELECT DATE_SUB(CURDATE(), INTERVAL 12 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 month) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 month) AS click_date ) A LEFT JOIN (SELECT R.D_CreateOn,R.C_Status from TMTN_RepairOrder R LEFT JOIN TDEV_DevStore D ON R.C_DevStoreCode = D.C_ID WHERE D.C_StoreCode = @storeCode) B ON DATE_FORMAT(A.click_date, '%Y-%m') = DATE_FORMAT(B.D_CreateOn, '%Y-%m') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordlist); } public Task> GetRecords30DaysStatisticsAsync(string storeCode) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)}; string sql = @"SELECT date_format(click_date,'%Y%m%d') AS SpecificDay, SUM(if(C_Status='4',1,0)) AS Normal, SUM(if(C_Status='3',1,0)) AS Abnormal, count(C_ID) as Total from( SELECT DATE_SUB(CURDATE(), INTERVAL 31 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 30 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 29 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 28 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 27 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 26 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 25 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 24 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 23 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 22 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 21 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 20 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 19 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 18 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 17 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 16 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 15 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 14 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 13 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 12 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 11 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 10 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 9 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 8 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 7 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 6 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 day) AS click_date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 0 day) AS click_date ) A LEFT JOIN ( SELECT B.* FROM TDEV_DevStore A INNER JOIN TMTN_RepairOrder B ON B.C_DevStoreCode = A.C_ID WHERE A.C_StoreCode = @storeCode AND A.C_Status = '1' ) B ON DATE_FORMAT(A.click_date, '%Y-%m-%d') = DATE_FORMAT(B.D_CreateOn, '%Y-%m-%d') GROUP BY DATE_FORMAT(A.click_date, '%Y-%m-%d')"; IEnumerable recordlist; recordlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(recordlist); } public Task> GetRecordsConditionAsync(TmtnRepairOrderRecordSearchModel searchModel, string licenseCode) { var sysUsers = DbContext.Set().AsNoTracking().Where(x=>x.C_Status == "1"); var repairOrderItemSet = DbContext.Set(); MySqlConnector.MySqlParameter[] parameters = new[] { //new MySqlConnector.MySqlParameter("start", searchModel.Start), //new MySqlConnector.MySqlParameter("end", searchModel.End), new MySqlConnector.MySqlParameter("spotCode", searchModel.C_SpotID), new MySqlConnector.MySqlParameter("userCode", searchModel.C_UserID), new MySqlConnector.MySqlParameter("areaCode", searchModel.C_AreaCode), new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("DevStoreCode", searchModel.C_DevStoreCode), new MySqlConnector.MySqlParameter("Status", searchModel.C_Status), new MySqlConnector.MySqlParameter("RepairOrderCode", "%"+searchModel.C_RepairOrderCode+"%"), new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"), new MySqlConnector.MySqlParameter("Start", searchModel.Start), new MySqlConnector.MySqlParameter("End", searchModel.End)}; //SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code //string sql = "SELECT A.*,B.C_GPS,B.C_Name,B.C_Number,B.C_Position,B.C_QRCode,B.C_Remark,B.C_ImageUrl, D.C_Name AS C_CreateByName,E.C_Name AS C_LastUpdatedByName FROM TISP_Record A LEFT JOIN TISP_Spot B ON A.C_SpotCode = B.C_Code LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User E ON E.C_UserID = A.C_LastUpdatedBy LEFT JOIN TISP_SpotRoute F ON F.C_SpotCode = A.C_SpotCode WHERE 1=1 AND A.D_CreateOn between @start and @end"; // string sql = @"SELECT A.*,F.C_Name AS DevName,F.C_NumberCode AS DevNumberCode,F.C_OpsTempCode,F.C_PlanTempCode,F.C_RepairTempCode,F.C_RunTempCode,E.C_ImagePath AS DevTempImage, D.C_Name AS CreateByName,E.C_Name AS LastUpdatedByName //FROM TMTN_RepairOrder A //LEFT JOIN TDEV_DevStore F ON F.C_ID = A.C_DevStoreCode //LEFT JOIN TDEV_DeviceTemplate E ON E.C_ID = F.C_DevTempCode //LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy //LEFT JOIN TSYS_User C ON C.C_UserID = A.C_LastUpdatedBy //WHERE F.C_StoreCode = @StoreCode"; string sql = @" SELECT A.*,F.C_Url AS DevStoreImage,F.C_Name AS DevName,F.C_NumberCode AS DevNumberCode,F.C_OpsTempCode,F.C_PlanTempCode,F.C_RepairTempCode,F.C_RunTempCode,E.C_ImagePath AS DevTempImage, E.C_Manufacturer,E.C_Parameter,E.C_Marker ,D.C_Name AS CreateByName,E.C_Name AS LastUpdatedByName FROM TMTN_RepairOrder A LEFT JOIN TDEV_DevStore F ON F.C_ID = A.C_DevStoreCode LEFT JOIN TDEV_DevSpot A0 ON A0.C_DevStoreCode = A.C_DevStoreCode INNER JOIN TISP_Spot B0 ON A0.C_SpotCode = B0.C_Code LEFT JOIN TDEV_DeviceTemplate E ON E.C_ID = F.C_DevTempCode LEFT JOIN TSYS_User D ON D.C_UserID = A.C_CreateBy LEFT JOIN TSYS_User C ON C.C_UserID = A.C_LastUpdatedBy WHERE F.C_StoreCode = @StoreCode "; if (!string.IsNullOrEmpty(searchModel.C_DevStoreCode))//DevStoreCode OR C_QRCode { sql += " AND (A.C_DevStoreCode = @DevStoreCode OR B0.C_QRCode = @DevStoreCode)"; } //if (searchModel.Abnormal) //{ // sql += " AND A.C_LastUpdatedBy IS NOT NULL"; //} //if (!string.IsNullOrEmpty(searchModel.C_AreaCode)) //{ // sql += " AND A.C_AreaCode = @areaCode"; //} if (!string.IsNullOrEmpty(searchModel.C_Status)) { if (searchModel.C_Status=="!1") { sql += " AND A.C_Status != 1 "; } else { sql += " AND A.C_Status = @Status "; } } if (!string.IsNullOrEmpty(searchModel.C_RepairOrderCode)) { sql += " AND A.C_ID Like @RepairOrderCode"; } if (!string.IsNullOrEmpty(searchModel.C_Name)) { sql += " AND A.C_Name Like @name "; } if (searchModel.Start != DateTime.MinValue && searchModel.End != DateTime.MinValue) { sql += " AND A.D_CreateOn BETWEEN @Start AND @End"; } sql += " GROUP BY A.C_ID ORDER BY A.D_CreateOn DESC"; IEnumerable spotlist = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); //foreach (var vm in spotlist) //{ // if (vm == null) // continue; // var orderItem_group_datas = from orderItem in repairOrderItemSet.AsNoTracking().Where(t => t.C_RepairCode == vm.C_ID) // join user in sysUsers on orderItem.C_CreateBy equals user.C_UserID // where orderItem.C_RepairCode == vm.C_ID // orderby orderItem.D_CreateOn ascending // group orderItem by new { orderItem.C_ID,orderItem.C_Remark,orderItem.C_RepairRecord,orderItem.C_Status,orderItem.C_Url,orderItem.D_CreateOn, user.C_Name} into sg // select new RepairOrderItemViewMode // { // C_ID = sg.Key.C_ID, // D_CreateOn = sg.Key.D_CreateOn, // C_Remark = sg.Key.C_Remark, // C_Url = sg.Key.C_Url, // C_Status = sg.Key.C_Status, // C_RepairRecord = sg.Key.C_RepairRecord, // CreateUserName = sg.Key.C_Name, // }; // vm.RepairOrderRecordItems = orderItem_group_datas; // var store_dev = (from devStore in DbContext.Set().AsNoTracking().Where(t => t.C_ID == vm.C_DevStoreCode) // join store in DbContext.Set().AsNoTracking() on devStore.C_StoreCode equals store.C_ID.ToString() // select new // { // store.C_LicenseCode, // store.C_Name, // devStore.D_CreateOn // }).FirstOrDefault(); // vm.StoreName = store_dev.C_Name; // vm.DevCreateOn = store_dev.D_CreateOn; // var devUserName = (from user in sysUsers // join userRole in DbContext.Set().AsNoTracking() on user.C_UserID equals userRole.C_UserCode // join role in DbContext.Set().AsNoTracking().Where(t=>t.C_LicenseCode == store_dev.C_LicenseCode && t.C_Status == "1") on userRole.C_RoleCode equals role.C_Code // select new // { // RoleName = role.C_Name, // user.C_Name, // user.C_Mobile // }).ToList(); // devUserName.ToList().ForEach(x=>{ // if(x.RoleName.Contains("设备管理员")) // vm.DevManager += x.C_Name + " " + x.C_Mobile + " "; // if(x.RoleName.Contains("设备运维员")) // vm.DevOpser += x.C_Name + " " + x.C_Mobile + " "; // }); //} searchModel.TotalCount = spotlist.First() != null ? spotlist.ToList().Count : 0; return Task.FromResult(searchModel.IsPagination ? spotlist.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : spotlist); } } public class TmtnRepairOrderRepositoryItemApp : RepositoryBase, ITmtnRepairOrderItemAppRepository { public TmtnRepairOrderRepositoryItemApp(InspectionDbContext DbContext) : base(DbContext) { } } }