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);
}
