qlhb/Ropin.Inspection.Repository/SYS/TsysRoleRepository.cs

using Ropin.Inspection.Model;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Repository.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Ropin.Inspection.Repository
{
    public class TsysRoleRepository : RepositoryBase<TSYS_Role, Guid>, ITsysRoleRepository
    {

        public TsysRoleRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }
        public Task<IEnumerable<TsysRoleOrgLicenseViewModel>> GetBylicenseCodeAsync(string licenseCode,string status)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] {
                new MySqlConnector.MySqlParameter("licenseCode", licenseCode),new MySqlConnector.MySqlParameter("status", status) };
            string sql = @"SELECT A.*,B.C_TypeCode AS LicenseTypeCode,B.C_Name AS LicenseName,C.C_Name AS OrgName,D.C_Name AS LicenseTypeName FROM TSYS_Role A 
LEFT JOIN TSYS_License B ON A.C_LicenseCode = B.C_Code
LEFT JOIN TSYS_Org C ON C.C_LicenseCode = B.C_Code
LEFT JOIN TSYS_LicenseType D ON D.C_Code = B.C_TypeCode
WHERE B.C_Status = '1' AND C.C_Status = '1' AND D.C_Status = '1' "; 
            if (!string.IsNullOrEmpty(licenseCode))
            {
                sql += " AND A.C_LicenseCode =@licenseCode ";
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql += " AND A.C_Status =@status ";
            }
            //sql += " GROUP BY A.C_Code  ORDER BY B.C_Name, C.C_Name";
            sql += " GROUP BY A.C_Code  ORDER BY  A.I_Sort";
            var contentlist = EntityFrameworkCoreExtensions.GetList<TsysRoleOrgLicenseViewModel>(DbContext.Database, sql, parameters);
            return Task.FromResult(contentlist);

        }
    }
}