using Ropin.Inspection.Common; using Ropin.Inspection.Model; using Ropin.Inspection.Model.Entities; using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Security.Claims; using System.Text; using System.Threading.Tasks; namespace Ropin.Inspection.Repository { public class TdevDevStoreRepository : RepositoryBase, ITdevDevStoreRepository { public TdevDevStoreRepository(InspectionDbContext DbContext) : base(DbContext) { } public Task GetDevStoreByQRCodeAsync(string qRCode, string storeCode) { //WHERE A.C_ID = '1e2aa77d-2838-48eb-a012-c1fecac679d3' OR (A.C_NumberCode = '2021083005' AND D.C_StoreCode = '4d05189f-0f7a-4c30-bd14-7ffc76553e98') OR (D.C_QRCode = '412050000391' AND D.C_StoreCode = '4d05189f-0f7a-4c30-bd14-7ffc76553e98') MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("qRCode", qRCode), new MySqlConnector.MySqlParameter("storeCode", storeCode) }; string sql = @"SELECT A.C_ID,A.C_GPS, B.*,A.C_Name AS DevStoreName,A.C_NumberCode AS DevStoreNumberCode,A.C_Remark AS DevStoreRemark,A.C_Status AS DevStoreStatus,A.C_Url AS DevStoreUrl,D.C_Code AS SpotID From TDEV_DevStore A INNER JOIN TDEV_DeviceTemplate B ON B.C_ID = A.C_DevTempCode INNER JOIN TDEV_DevSpot C ON C.C_DevStoreCode = A.C_ID INNER JOIN TISP_Spot D ON D.C_Code = C.C_SpotCode WHERE (A.C_ID = @qRCode) OR (A.C_NumberCode = @qRCode AND D.C_StoreCode = @storeCode) OR (D.C_QRCode = @qRCode AND D.C_StoreCode = @storeCode) "; TdevDevStoreDetailViewModel model = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(model); } public Task GetUserByDevStoreIdAndRoleNameAsync(string devStoreId, string roleName) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("devStoreId", devStoreId), new MySqlConnector.MySqlParameter("roleName", roleName) }; string sql = @"SELECT B.* from TPNT_StoreOrg A LEFT JOIN TSYS_User B ON A.C_OrgCode = B.C_OrgCode LEFT JOIN TSYS_UserRole C ON C.C_UserCode = B.C_UserID LEFT JOIN TSYS_Role D ON D.C_Code = C.C_RoleCode LEFT JOIN TDEV_DevStore E ON E.C_StoreCode = A.C_StoreCode WHERE E.C_ID = @devStoreId AND D.C_Name = '设备管理员' AND B.C_Status = '1' AND D.C_Status = '1' "; TsysUserViewModel model = EntityFrameworkCoreExtensions.SqlQuery(DbContext.Database, sql, parameters).FirstOrDefault(); return Task.FromResult(model); } public Task UpdateDevStoreStatus(string devId,string status) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("devId", devId), new MySqlConnector.MySqlParameter("status", status) }; string sql = "UPDATE TDEV_DevStore SET C_Status=@status where C_ID=@devId "; int result = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters); return Task.FromResult(result); } public Task> GetConditionAsync(TdevDevStoreSearchModel searchModel, string userLicenseTypeCode,string ApiUserId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Status", searchModel.C_Status), new MySqlConnector.MySqlParameter("DevTempCode", searchModel.C_DevTempCode), new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("id", searchModel.C_ID), new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"), new MySqlConnector.MySqlParameter("OrgCode", searchModel.C_OrgCode) }; StringBuilder sql = new StringBuilder(); sql.Append(@" select A.*,dd.C_Name as c_DevTempName,ops.C_Name as C_OpsTempName, rep.C_Name as C_RepairTempName, run.C_Name as C_RunTempName, plan.C_Name as C_PlanTempName from TDEV_DevStore A LEFT JOIN TPNT_Store B on (A.C_StoreCode=B.C_Code) LEFT JOIN TDEV_DeviceTemplate dd on (A.C_DevTempCode=dd.C_ID) LEFT JOIN TDEV_MaintenanceTemplate ops on (A.C_OpsTempCode=ops.C_ID) LEFT JOIN TDEV_MaintenanceTemplate rep on (A.C_RepairTempCode=rep.C_ID) LEFT JOIN TDEV_MaintenanceTemplate run on (A.C_RunTempCode=run.C_ID) LEFT JOIN TDEV_MaintenancePlan plan on (A.C_PlanTempCode=plan.C_ID) where 1=1"); #region 添加条件查询 if (!string.IsNullOrEmpty(searchModel.C_Status)) { sql.Append(" AND A.C_Status=@Status "); } else { if (searchModel.type != "details") { sql.Append(" AND A.C_Status!='0' "); } } if (!string.IsNullOrEmpty(searchModel.C_DevTempCode)) { sql.Append(" AND A.C_DevTempCode=@DevTempCode "); } if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql.Append(" AND A.C_StoreCode=@StoreCode "); } if (!string.IsNullOrEmpty(searchModel.C_ID)) { sql.Append(" AND A.C_ID=@id"); } if (!string.IsNullOrEmpty(searchModel.C_Name)) { sql.Append(" AND A.C_Name like @name "); } if (userLicenseTypeCode != null && userLicenseTypeCode != TsysLicenseType.SYSTEM) { sql.Append(" AND A.C_UserConfig like '%"+ ApiUserId + "%' "); } if (!string.IsNullOrEmpty(searchModel.C_OrgCode)) { sql.Append(" and A.C_StoreCode in (select A.C_StoreCode from TPNT_StoreOrg A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where A.C_OrgCode=@OrgCode and B.C_Status!=0) "); } #endregion sql.Append(" order by B.I_Sort,A.I_Sort,A.C_Name asc "); IEnumerable model = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); return Task.FromResult(model); } public Task> GetDevStoreStatusCount(TdevDevStoreSearchModel searchModel, string userLicenseTypeCode, string ApiUserId) { MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("Status", searchModel.C_Status), new MySqlConnector.MySqlParameter("DevTempCode", searchModel.C_DevTempCode), new MySqlConnector.MySqlParameter("StoreCode", searchModel.C_StoreCode), new MySqlConnector.MySqlParameter("name", "%"+searchModel.C_Name+"%"), new MySqlConnector.MySqlParameter("OrgCode", searchModel.C_OrgCode) }; StringBuilder sql = new StringBuilder(); sql.Append(@"select A.C_Status as status,count(A.C_Status) as cut from TDEV_DevStore A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where 1=1 "); #region 添加条件查询 if (searchModel.type != "details") { sql.Append(" AND A.C_Status!='0' "); } if (!string.IsNullOrEmpty(searchModel.C_DevTempCode)) { sql.Append(" AND A.C_DevTempCode=@DevTempCode "); } if (!string.IsNullOrEmpty(searchModel.C_StoreCode)) { sql.Append(" AND A.C_StoreCode=@StoreCode "); } if (!string.IsNullOrEmpty(searchModel.C_Name)) { sql.Append(" AND A.C_Name like @name "); } if (userLicenseTypeCode != null && userLicenseTypeCode != TsysLicenseType.SYSTEM) { sql.Append(" AND A.C_UserConfig like '%" + ApiUserId + "%' "); } if (!string.IsNullOrEmpty(searchModel.C_OrgCode)) { sql.Append(" and A.C_StoreCode in (select A.C_StoreCode from TPNT_StoreOrg A LEFT JOIN TPNT_Store B on A.C_StoreCode=B.C_Code where A.C_OrgCode=@OrgCode and B.C_Status!=0) "); } #endregion sql.Append(" group by A.C_Status "); IEnumerable model = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); return Task.FromResult(model); } } }