qlhb/Ropin.Inspection.Repository/TdevDevStoreRepository.cs

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<TDEV_DevStore, string>, ITdevDevStoreRepository
    {
        public TdevDevStoreRepository(InspectionDbContext DbContext) : base(DbContext)
        {

        }

        public Task<TdevDevStoreDetailViewModel> 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<TdevDevStoreDetailViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(model);
        }

        public Task<TsysUserViewModel> 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<TsysUserViewModel>(DbContext.Database, sql, parameters).FirstOrDefault();
            return Task.FromResult(model);
        }
        public Task<int> 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<IEnumerable<TdevDevStoreViewModel>> 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<TdevDevStoreViewModel> model = EntityFrameworkCoreExtensions.GetList<TdevDevStoreViewModel>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(model);
        }

        public Task<IEnumerable<DevStoreStatusGroup>> 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<DevStoreStatusGroup> model = EntityFrameworkCoreExtensions.GetList<DevStoreStatusGroup>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(model);
        }
    }
}