public async Task <List <Product> > GetAllProducts(int partnerId) { List <Product> list = new List <Product>(); try { string sql = @"select * from (select distinct p.* from Agents ag join ProductAgentAssignment paa on ag.Id =paa.AgentId join Products p on paa.ProductId=p.Id join PosAssignments poa on ag.Id = poa.MerchantId join PosUnits po on poa.POSId=po.Id join PosUsers pu on ag.Id = pu.MerchantId cross apply (select top 1 * from PinStock t where t.AgentId=ag.ParentId and t.ProductId=p.Id and t.Status =2) x where ag.SolutionPartnerId= @partnerId and poa.Status=1 and p.ActiveStatus=1 and po.ActiveStatus=1 and ag.ActiveStatus=1 and pu.ActiveStatus=1)x ORDER BY x.Name"; var parameters = new[] { new SqlParameter { ParameterName = "@partnerId", Value = partnerId } }; list = await _db.Set <Product>().FromSql(sql, parameters.ToArray <object>()).ToListAsync(); } catch (Exception ex) { throw ex; } return(list); }
public async Task <List <Agent> > GetAllActivePartners() { List <Agent> list = new List <Agent>(); try { string sql = "select * from agents where ISNULL(parentid,0)=0"; list = await _db.Set <Agent>().FromSql(sql).ToListAsync(); } catch (Exception ex) { throw ex; } return(list); }