public static List <SupplierServiceUI> GetServiceSuppliersByDistance(Int64 ServiceId, Int64 CityId, Geometry.Point Location, List <SupplierServiceUI> excludedSuppliers) { List <Int64> HomeServiceId = Service.FetchAllHomeServices(); List <SupplierServiceUI> suppliersService = new List <SupplierServiceUI>(); var excludedSupplierIds = excludedSuppliers.Select(x => x.SupplierId); const string DISTANCE = "distance"; Query qry = new Query(AppSupplier.TableSchema); qry.Select(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.SupplierId, AppSupplier.Columns.SupplierId, true); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.BusinessName, AppSupplier.Columns.BusinessName); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Street, AppSupplier.Columns.Street); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Description, AppSupplier.Columns.Description); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.ProfileImage, AppSupplier.Columns.ProfileImage); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.HouseNum, AppSupplier.Columns.HouseNum); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Phone, AppSupplier.Columns.Phone); qry.AddSelect(City.TableSchema.SchemaName, City.Columns.CityName, City.Columns.CityName); qry.AddSelect(SupplierService.TableSchema.SchemaName, SupplierService.Columns.ServiceId, SupplierService.Columns.ServiceId); qry.AddSelectLiteral("(SELECT avg(" + Comment.Columns.Rate + ") from " + Comment.TableSchema.SchemaName + " where " + Comment.TableSchema.SchemaName + "." + Comment.Columns.SupplierId + "=" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.SupplierId + " AND " + Comment.TableSchema.SchemaName + "." + Comment.Columns.Status + "=" + (int)CommentStatus.Approved + ")", "AvgRate"); qry.AddSelectLiteral("(SELECT Count(" + Comment.Columns.Rate + ") from " + Comment.TableSchema.SchemaName + " where " + Comment.TableSchema.SchemaName + "." + Comment.Columns.SupplierId + "=" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.SupplierId + " AND " + Comment.TableSchema.SchemaName + "." + Comment.Columns.Status + "=" + (int)CommentStatus.Approved + ")", "numberOfComments"); qry.AddSelectLiteral( "( 6371 * acos ( cos ( radians(" + Location.X + ") ) * cos( radians( X(" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.AddressLocation + ") ) ) " + "* cos( radians( Y(" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.AddressLocation + ") ) - radians(" + Location.Y + ") ) " + "+ sin ( radians(" + Location.X + ") ) * sin( radians( X(" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.AddressLocation + ") ) ) )) AS " + DISTANCE); qry.Join(JoinType.LeftJoin, AppSupplier.TableSchema, AppSupplier.Columns.CityId, AppSupplier.TableSchema.SchemaName, City.TableSchema, City.Columns.CityId, City.TableSchema.SchemaName); qry.Join(JoinType.LeftJoin, AppSupplier.TableSchema, AppSupplier.Columns.SupplierId, AppSupplier.TableSchema.SchemaName, Comment.TableSchema, Comment.Columns.SupplierId, Comment.TableSchema.SchemaName); qry.Join(JoinType.LeftJoin, AppSupplier.TableSchema, AppSupplier.Columns.SupplierId, AppSupplier.TableSchema.SchemaName, SupplierService.TableSchema, SupplierService.Columns.SupplierId, SupplierService.TableSchema.SchemaName); qry.Having("(" + DISTANCE + " <= " + Settings.GetSettingInt32(Settings.Keys.SUPPLIER_RADIUS, 10) + (HomeServiceId.Contains(ServiceId) ? " OR " + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.SupplierId + " IN ( SELECT " + SupplierHomeServiceCity.Columns.SupplierId + " FROM " + SupplierHomeServiceCity.TableSchema.SchemaName + " WHERE " + SupplierHomeServiceCity.Columns.CityId + " = " + CityId + ")" : "") + ") AND " + SupplierService.TableSchema.SchemaName + "." + SupplierService.Columns.ServiceId + " = " + ServiceId); if (excludedSupplierIds.Count() > 0) { qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.SupplierId, WhereComparision.NotIn, excludedSupplierIds.ToArray()); } qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsDeleted, WhereComparision.EqualsTo, false); qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsLocked, WhereComparision.EqualsTo, false); qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsService, WhereComparision.EqualsTo, true); qry.Randomize(); qry.Distinct(); try { using (DataReaderBase reader = qry.ExecuteReader()) { while (reader.Read()) { SupplierServiceUI supplierService = new SupplierServiceUI { SupplierId = Convert.ToInt64(reader["SupplierId"]), BusinessName = Convert.ToString(reader["BusinessName"]), Phone = Convert.ToString(reader["Phone"]), HouseNum = Convert.ToString(reader["HouseNum"]), CityName = Convert.ToString(reader["CityName"]), Street = Convert.ToString(reader["Street"]), AvgRate = string.IsNullOrEmpty(reader["AvgRate"].ToString()) ? 0 : Convert.ToDouble(reader["AvgRate"]), Distance = Convert.ToDecimal(reader[DISTANCE]), Description = Convert.ToString(reader[AppSupplier.Columns.Description]), NumberOfComments = Convert.ToInt32(reader["numberOfComments"]), ProfileImage = Convert.ToString(reader[AppSupplier.Columns.ProfileImage]) }; suppliersService.Add(supplierService); } } } catch (Exception ex) { } return(suppliersService.OrderBy(x => x.Distance).ToList()); }
public static List <SupplierServiceUI> GetSuppliersPromotedOfCity(long cityId, int serviceId, int limit) { Query qry = new Query(SupplierPromotedArea.TableSchema); qry.Join(JoinType.InnerJoin, SupplierPromotedArea.TableSchema, SupplierPromotedArea.Columns.PromotedAreaId, SupplierPromotedArea.TableSchema.SchemaName, PromotedArea.TableSchema, PromotedArea.Columns.Id, PromotedArea.TableSchema.SchemaName); qry.Join(JoinType.InnerJoin, SupplierPromotedArea.TableSchema, SupplierPromotedArea.Columns.SupplierId, SupplierPromotedArea.TableSchema.SchemaName, AppSupplier.TableSchema, AppSupplier.Columns.SupplierId, AppSupplier.TableSchema.SchemaName); qry.Join(JoinType.InnerJoin, SupplierPromotedArea.TableSchema, SupplierPromotedArea.Columns.PromotedAreaId, SupplierPromotedArea.TableSchema.SchemaName, City.TableSchema, City.Columns.PromotedAreaId, City.TableSchema.SchemaName); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.SupplierId, AppSupplier.Columns.SupplierId); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.HouseNum, AppSupplier.Columns.HouseNum); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Street, AppSupplier.Columns.Street); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Description, AppSupplier.Columns.Description); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.ProfileImage, AppSupplier.Columns.ProfileImage); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.Phone, AppSupplier.Columns.Phone); qry.AddSelect(City.TableSchema.SchemaName, City.Columns.CityName, City.Columns.CityName); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.BusinessName, AppSupplier.Columns.BusinessName); qry.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.ContactName, AppSupplier.Columns.ContactName); qry.AddSelect(PromotedArea.TableSchema.SchemaName, PromotedArea.Columns.Id, "PromotedAreaId"); qry.AddSelectLiteral("(SELECT avg(" + Comment.Columns.Rate + ") from " + Comment.TableSchema.SchemaName + " where " + Comment.TableSchema.SchemaName + "." + Comment.Columns.SupplierId + "=" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.SupplierId + " AND " + Comment.TableSchema.SchemaName + "." + Comment.Columns.Status + "=" + (int)CommentStatus.Approved + ")", "AvgRate"); qry.AddSelectLiteral("(SELECT Count(" + Comment.Columns.Rate + ") from " + Comment.TableSchema.SchemaName + " where " + Comment.TableSchema.SchemaName + "." + Comment.Columns.SupplierId + "=" + AppSupplier.TableSchema.SchemaName + "." + AppSupplier.Columns.SupplierId + " AND " + Comment.TableSchema.SchemaName + "." + Comment.Columns.Status + "=" + (int)CommentStatus.Approved + ")", "numberOfComments"); qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsService, WhereComparision.EqualsTo, true); qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsLocked, WhereComparision.EqualsTo, false); qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.IsDeleted, WhereComparision.EqualsTo, false); qry.AddWhere(City.TableSchema.SchemaName, City.Columns.CityId, WhereComparision.EqualsTo, cityId); qry.AddWhere(SupplierPromotedArea.TableSchema.SchemaName, SupplierPromotedArea.Columns.ServiceId, WhereComparision.EqualsTo, serviceId); qry.AddWhere(SupplierPromotedArea.TableSchema.SchemaName, SupplierPromotedArea.Columns.Deleted, WhereComparision.EqualsTo, null); var wl1 = new WhereList().OR(SupplierPromotedArea.TableSchema.SchemaName, SupplierPromotedArea.Columns.Deleted, WhereComparision.EqualsTo, null) .OR(SupplierPromotedArea.TableSchema.SchemaName, SupplierPromotedArea.Columns.Deleted, WhereComparision.GreaterThan, DateTime.Now); qry.AddWhere(WhereCondition.AND, wl1); qry.LimitRows(limit); qry.Randomize(); var lstSuppliers = new List <SupplierServiceUI>(); using (DataReaderBase reader = qry.ExecuteReader()) { while (reader.Read()) { var supplier = new SupplierServiceUI(); supplier.SupplierId = (reader[AppSupplier.Columns.SupplierId] != null ? (long)reader[AppSupplier.Columns.SupplierId] : 0); supplier.BusinessName = (reader[AppSupplier.Columns.BusinessName] != null ? reader[AppSupplier.Columns.BusinessName].ToString() : ""); supplier.Phone = (reader[AppSupplier.Columns.Phone] != null ? reader[AppSupplier.Columns.Phone].ToString() : ""); supplier.CityName = (reader[City.Columns.CityName] != null ? reader[City.Columns.CityName].ToString() : ""); supplier.Street = (reader[AppSupplier.Columns.Street] != null ? reader[AppSupplier.Columns.Street].ToString() : ""); supplier.HouseNum = (reader[AppSupplier.Columns.HouseNum] != null ? reader[AppSupplier.Columns.HouseNum].ToString() : ""); supplier.AvgRate = string.IsNullOrEmpty(reader["AvgRate"].ToString()) ? 0 : Convert.ToDouble(reader["AvgRate"]); supplier.Description = Convert.ToString(reader[AppSupplier.Columns.Description]); supplier.NumberOfComments = Convert.ToInt32(reader["numberOfComments"]); supplier.ProfileImage = Convert.ToString(reader[AppSupplier.Columns.ProfileImage]); lstSuppliers.Add(supplier); } } return(lstSuppliers); }