qlhb/Ropin.Inspection.Repository/PNT/TpntStoreOrgRepository.cs

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

namespace Ropin.Inspection.Repository
{
    public  class TpntStoreOrgRepository : RepositoryBase<TPNT_StoreOrg, Guid>, ITpntStoreOrgRepository
    {
        public TpntStoreOrgRepository(InspectionDbContext dbContext) : base(dbContext)
        {

        }       

        public Task<bool> DeleteByOrgStoreCodeAsync(Guid orgCode,string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("orgCode", orgCode),
            new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = "DELETE FROM TPNT_StoreOrg where C_OrgCode = @orgCode and C_StoreCode =@storeCode ";
            int iResult = EntityFrameworkCoreExtensions.ExecuteSqlNoQuery(DbContext.Database, sql, parameters);
            if(iResult>0)
                return Task.FromResult(true);
            else
                return Task.FromResult(false);
        }

        public Task<IEnumerable<TpntStoreViewModel>> GetStoresByOrgCodeAsync(TpntStoreSearchModel searchModel)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { 
                new MySqlConnector.MySqlParameter("orgCode", searchModel?.orgCode),
                new MySqlConnector.MySqlParameter("Status", searchModel?.C_Status)
            };
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT B.C_Type AS OrgTypeCode, C.*,D.C_Name AS TypeName,G.C_Code AS ProvCode,G.C_Name AS ProvName,F.C_Code AS CityCode,F.C_Name AS CityName,E.C_Code AS AreaCode,E.C_Name AS AreaName,A.C_Type AS StoreOrgType FROM TPNT_StoreOrg A 
INNER JOIN TSYS_Org B ON B.C_Code = A.C_OrgCode
INNER JOIN TPNT_Store C ON C.C_Code = A.C_StoreCode
INNER JOIN TPNT_Type D ON D.C_Code = C.C_TypeCode
INNER JOIN TBDM_Area E on E.C_Code = C.C_AreaCode
INNER JOIN TBDM_City F ON F.C_Code = E.C_CityCode
INNER JOIN TBDM_Prov G ON G.C_Code = F.C_ProvCode
WHERE A.C_OrgCode = @orgCode AND B.C_Status = '1' AND D.C_Status = '1' ");
            if (!string.IsNullOrEmpty(searchModel.C_Status))
            {
                sql.Append("  AND C.C_Status = @Status  ");
            }
            else
            {
                sql.Append("  AND C.C_Status = '1'  ");
            }
            if (!string.IsNullOrEmpty(searchModel.C_Name))
            {
                sql.Append($" AND C.C_Name  like  '%{searchModel.C_Name}%'  ");
            }
            sql.Append(" group by C.C_ID order BY C.I_Sort asc");
            IEnumerable<TpntStoreViewModel> result = EntityFrameworkCoreExtensions.GetList<TpntStoreViewModel>(DbContext.Database, sql.ToString(), parameters);
            return Task.FromResult(result);
        }
        public Task<IEnumerable<TpntStoreViewModel>> GetAllStoresAsync()
        {
            
            string sql = @"SELECT B.C_Type AS OrgTypeCode, C.*,D.C_Name AS TypeName,G.C_Code AS ProvCode,G.C_Name AS ProvName,F.C_Code AS CityCode,F.C_Name AS CityName,E.C_Code AS AreaCode,E.C_Name AS AreaName FROM TPNT_StoreOrg A 
INNER JOIN TSYS_Org B ON B.C_Code = A.C_OrgCode
INNER JOIN TPNT_Store C ON C.C_Code = A.C_StoreCode
INNER JOIN TPNT_Type D ON D.C_Code = C.C_TypeCode
INNER JOIN TBDM_Area E on E.C_Code = C.C_AreaCode
INNER JOIN TBDM_City F ON F.C_Code = E.C_CityCode
INNER JOIN TBDM_Prov G ON G.C_Code = F.C_ProvCode
WHERE B.C_Status = '1' AND C.C_Status = '1' AND D.C_Status = '1' group by C.C_ID  order BY C.I_Sort asc ";

            IEnumerable<TpntStoreViewModel> result = EntityFrameworkCoreExtensions.GetList<TpntStoreViewModel>(DbContext.Database, sql, null);
            return Task.FromResult(result);
        }
        public Task<IEnumerable<TsysOrganizeViewModel>> GetOrgsByStoreCodeAsync(string storeCode)
        {
            MySqlConnector.MySqlParameter[] parameters = new[] { new MySqlConnector.MySqlParameter("storeCode", storeCode)};
            string sql = @"SELECT B.* FROM TPNT_StoreOrg A 
INNER JOIN TSYS_Org B ON B.C_Code = A.C_OrgCode
INNER JOIN TPNT_Store C ON C.C_Code = A.C_StoreCode
WHERE A.C_StoreCode = @storeCode AND B.C_Status = '1' AND C.C_Status = '1'";
            IEnumerable<TsysOrganizeViewModel> result = EntityFrameworkCoreExtensions.GetList<TsysOrganizeViewModel>(DbContext.Database, sql, parameters);
            return Task.FromResult(result);
        }

    }
}