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, ITpntStoreOrgRepository { public TpntStoreOrgRepository(InspectionDbContext dbContext) : base(dbContext) { } public Task 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> 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 result = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql.ToString(), parameters); return Task.FromResult(result); } public Task> 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 result = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, null); return Task.FromResult(result); } public Task> 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 result = EntityFrameworkCoreExtensions.GetList(DbContext.Database, sql, parameters); return Task.FromResult(result); } } }