public QueryObject <ShopWithOwner> QueryShopWithOwner(ShopRequest request)
{
var sql =
@"select t.*, u.DisplayName as OwnerDisplayName, u.Mobile as OwnerMobileNumber from shops t inner join users u on t.ShopId = u.ShopId where
u.IsMobileAvailable = 1
and (@Name is null or t.Name = @Name)
and (@State is null or t.State = @State)
and (@ShopId is null or t.ShopId = @ShopId)
and (@NameWith is null or t.Name like '%'+ @NameWith + '%')
and (@DisplayNameWith is null or t.DisplayName like '%'+ @DisplayNameWith + '%')
and (@ShopIds is null or t.ShopId in (@ShopIds))";
return(new QueryObject <ShopWithOwner>(_databaseInstance, sql, request));
}
public DataTables <Shop> NewQuery(ShopRequest request)
{
SqlParameter[] param =
{
new SqlParameter("@Name", request.Name),
new SqlParameter("@NameWith", request.NameWith),
new SqlParameter("@DisplayNameWith", request.DisplayNameWith),
new SqlParameter("@State", request.State),
new SqlParameter("@pageIndex", request.PageIndex),
new SqlParameter("@pageSize", request.PageSize),
new SqlParameter("@IsMobileAvailable", request.IsMobileAvailable)
};
StoreProcedure sp = new StoreProcedure("P_getShops", param);
return(_databaseInstance.GetTables <Shop>(sp));
}
public QueryObject <Shop> Query(ShopRequest request)
{
var sql =
@"select t.* from shops t where
(@Name is null or t.Name = @Name)
and (@State is null or t.State = @State)
and (@IsBuildIn is null or t.BuildIn = @IsBuildIn)
and (@IsMobileAvailable is null
or ( @IsMobileAvailable = 1 and exists (select * from users u where u.shopId = t.shopId and u.IsMobileAvailable = 1))
or ( @IsMobileAvailable = 0 and not exists (select * from users u where u.shopId = t.shopId and u.IsMobileAvailable = 1))
)
and (@ShopId is null or t.ShopId = @ShopId)
and (@NameWith is null or t.Name like '%'+ @NameWith + '%')
and (@DisplayNameWith is null or t.DisplayName like '%'+ @DisplayNameWith + '%')
and (@ShopIds is null or t.ShopId in (@ShopIds))";
return(new QueryObject <Shop>(_databaseInstance, sql, request));
}