public static List <AdCompany> GetAllCompaniesUI(bool IsSearch = false, string SearchName = "", string SearchPhone = "", int PageSize = 0, int CurrentPageIndex = 0)
{
Query qry = new Query(AdCompany.TableSchema);
qry.SelectAllTableColumns();
qry.Distinct();
var wl1 = new WhereList().OR(AdCompany.TableSchema.SchemaName, AdCompany.Columns.Deleted, WhereComparision.EqualsTo, null)
.OR(AdCompany.TableSchema.SchemaName, AdCompany.Columns.Deleted, WhereComparision.GreaterThan, DateTime.Now);
qry.AddWhere(WhereCondition.AND, wl1);
if (IsSearch == true)
{
WhereList wl = new WhereList();
wl.OR(AdCompany.Columns.ContactPhone, WhereComparision.Like, SearchPhone)
.OR(AdCompany.Columns.Phone, WhereComparision.Like, SearchPhone);
qry.AND(wl);
qry.AddWhere(AdCompany.Columns.ContactName, WhereComparision.Like, SearchName);
}
if (PageSize > 0)
{
qry.LimitRows(PageSize).OffsetRows(PageSize * CurrentPageIndex);
}
List <AdCompany> list = new List <AdCompany>();
using (DataReaderBase reader = qry.ExecuteReader())
{
while (reader.Read())
{
list.Add(new AdCompany
{
CompanyId = Convert.ToInt64(reader[AdCompany.Columns.CompanyId]),
BusinessName = Convert.ToString(reader[AdCompany.Columns.BusinessName]),
Email = Convert.ToString(reader[AdCompany.Columns.Email]),
ContactPhone = Convert.ToString(reader[AdCompany.Columns.ContactPhone]),
Phone = Convert.ToString(reader[AdCompany.Columns.Phone]),
ContactName = Convert.ToString(reader[AdCompany.Columns.ContactName]),
CreatedDate = reader[AdCompany.Columns.CreatedDate] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader[AdCompany.Columns.CreatedDate]),
Description = Convert.ToString(reader[AdCompany.Columns.Description])
});
}
}
return(list);
}
public static List <SupplierServiceUI> GetServiceSuppliersAndNumEvents(string SearchName = "", string SearchPhone = "", string SearchId = "", string SearchCity = "", DateTime from = new DateTime(), DateTime to = new DateTime(), bool IsSearch = false, int PageSize = 0, int CurrentPageIndex = 0, Int64 SupplierId = 0)
{
Query qry = new Query(AppSupplier.TableSchema);
qry.SelectAllTableColumns();
qry.AddSelectLiteral("COUNT(IF( " + SupplierEvent.TableSchema.SchemaName + "." + SupplierEvent.Columns.EventType + "= '" + SupplierEvent.CLICK + "',1, NULL)) 'ClickNum'");
qry.AddSelectLiteral("COUNT(IF( " + SupplierEvent.TableSchema.SchemaName + "." + SupplierEvent.Columns.EventType + "= '" + SupplierEvent.PHONE_CALL + "',1, NULL)) 'ClickToCallNum'");
qry.AddSelect(City.TableSchema.SchemaName, City.Columns.CityName, "CityName");
qry.Join(JoinType.LeftJoin, SupplierEvent.TableSchema, AppSupplier.Columns.SupplierId, AppSupplier.TableSchema.SchemaName, SupplierEvent.TableSchema, SupplierEvent.Columns.SupplierId, SupplierEvent.TableSchema.SchemaName);
qry.Join(JoinType.InnerJoin, AppSupplier.TableSchema, AppSupplier.Columns.CityId, AppSupplier.TableSchema.SchemaName, City.TableSchema, City.Columns.CityId, City.TableSchema.SchemaName);
qry.Where(AppSupplier.Columns.IsService, true);
qry.GroupBy(AppSupplier.Columns.SupplierId);
if (IsSearch == true)
{
WhereList wl = new WhereList();
if (SearchPhone != "")
{
wl.OR(AppSupplier.Columns.ContactPhone, WhereComparision.Like, "%" + SearchPhone + "%")
.OR(AppSupplier.Columns.Phone, WhereComparision.Like, "%" + SearchPhone + "%");
qry.AND(wl);
}
WhereList wl1 = new WhereList();
if (SearchName != "")
{
wl1.OR(AppSupplier.Columns.ContactName, WhereComparision.Like, "%" + SearchName + "%")
.OR(AppSupplier.Columns.BusinessName, WhereComparision.Like, "%" + SearchName + "%");
qry.AND(wl1);
}
if (SearchId != "")
{
qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.SupplierId, WhereComparision.Like, "%" + SearchId + "%");
}
if (SearchCity != "")
{
qry.AddWhere(City.TableSchema.SchemaName, City.Columns.CityName, WhereComparision.Like, "%" + SearchCity);
}
if (SupplierId != 0)
{
qry.AddWhere(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.SupplierId, WhereComparision.EqualsTo, SupplierId);
}
if (from != DateTime.MinValue)
{
qry.AddWhere(SupplierEvent.Columns.EventTime, WhereComparision.GreaterThanOrEqual, from.Date);
}
if (to != DateTime.MinValue)
{
qry.AddWhere(SupplierEvent.Columns.EventTime, WhereComparision.LessThanOrEqual, to.AddHours(24).Date);
}
}
if (PageSize > 0)
{
qry.LimitRows(PageSize).OffsetRows(PageSize * CurrentPageIndex);
}
// qry.OrderBy(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.CreateDate, SortDirection.DESC);
List <SupplierServiceUI> list = new List <SupplierServiceUI>();
using (DataReaderBase reader = qry.ExecuteReader())
{
while (reader.Read())
{
//Query q = new Query(Order.TableSchema).Join(JoinType.LeftJoin, Bid.TableSchema, Bid.TableSchema.SchemaName,
//new JoinColumnPair(Order.TableSchema.SchemaName, Order.Columns.BidId, Bid.Columns.BidId));
//q.SelectAllTableColumns();
//q.Where(Order.Columns.SupplierId, Convert.ToInt64(reader["SupplierId"]))
//.AddWhere(Bid.Columns.EndDate, WhereComparision.GreaterThanOrEqual, DateTime.UtcNow);
//if (from != DateTime.MinValue) q.AddWhere(Bid.Columns.EndDate, WhereComparision.GreaterThanOrEqual, from.Date);
//if (to != DateTime.MinValue) q.AddWhere(Bid.Columns.EndDate, WhereComparision.LessThanOrEqual, to.AddHours(24).Date);
//Int64 ActiveOrder = q.GetCount(Order.Columns.OrderId);
list.Add(new SupplierServiceUI
{
SupplierId = Convert.ToInt64(reader["SupplierId"]),
BusinessName = Convert.ToString(reader["BusinessName"]),
ContactPhone = Convert.ToString(reader["ContactPhone"]),
Phone = Convert.ToString(reader["Phone"]),
ContactName = Convert.ToString(reader["ContactName"]),
CityId = Convert.ToInt64(reader["CityId"]),
CityName = Convert.ToString(reader["CityName"]),
ClickNum = Convert.ToInt64(reader["ClickNum"]),
ClickToCallNum = Convert.ToInt64(reader["ClickToCallNum"])
});
}
}
return(list);
}
public WhereList OR(string columnName, object betweenValue, object andValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(columnName, betweenValue, andValue);
}
public WhereList OR(string tableName, string columnName, WhereComparison comparison, string otherTableName, string otherColumnName)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(tableName, columnName, comparison, otherTableName, otherColumnName);
}
public WhereList OR(string tableName, string columnName, WhereComparison comparison, object columnValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(tableName, columnName, comparison, columnValue);
}
public WhereList OR(WhereList whereList)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(whereList);
}
public WhereList OR(IPhrase phrase, WhereComparison comparison, string tableName, string columnName)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase, comparison, tableName, columnName);
}
public WhereList OR(IPhrase phrase, WhereComparison comparison, object value, ValueObjectType valueType)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase, comparison, value, valueType);
}
public WhereList OR(IPhrase phrase)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(phrase);
}
public WhereList OR(string literalExpression)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(literalExpression);
}
public WhereList OR(string columnName, object columnValue)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(columnName, columnValue);
}
public WhereList OR(object thisObject, ValueObjectType thisObjectType, WhereComparison comparison, object thatObject, ValueObjectType thatObjectType)
{
var wl = new WhereList();
wl.Add(this);
return wl.OR(thisObject, thisObjectType, comparison, thatObject, thatObjectType);
}
public static List <AppUserUI> GetAllAppUserUI(DateTime from = new DateTime(), DateTime to = new DateTime(), string SearchName = "", string SearchPhone = "", int PageSize = 0, int CurrentPageIndex = 0)
{
List <AppUserUI> app_users = new List <AppUserUI>();
Query q = new Query(AppUser.TableSchema)
.Select(AppUser.Columns.AppUserId)
.AddSelect(AppUser.Columns.ProfileImage)
.AddSelect(AppUser.Columns.Email)
.AddSelect(AppUser.Columns.IsLocked)
.AddSelect(AppUser.Columns.FirstName)
.AddSelect(AppUser.Columns.LastName)
.AddSelect(AppUser.Columns.Phone)
.AddSelect(AppUser.Columns.LastLogin)
.AddSelect(AppUser.Columns.IsAdv)
.AddSelect(AppUser.Columns.CreateDate)
.AddSelect(AppUser.Columns.Street)
.AddSelect(AppUser.Columns.HouseNum)
.AddSelect(AppUser.Columns.Floor)
.AddSelect(AppUser.Columns.ApartmentNumber)
.AddWhere(AppUser.Columns.IsDeleted, false)
.Join(JoinType.LeftJoin, AppUser.TableSchema, AppUser.Columns.CityId, AppUser.TableSchema.SchemaName, City.TableSchema, City.Columns.CityId, City.TableSchema.SchemaName)
.AddSelect(City.TableSchema.SchemaName, City.Columns.CityName, City.Columns.CityName);
if (SearchName != "" && SearchPhone != "")
{
WhereList wl = new WhereList();
wl.OR(AppUser.Columns.FirstName, WhereComparision.Like, SearchName)
.OR(AppUser.Columns.LastName, WhereComparision.Like, SearchName);
q.AND(wl);
q.AddWhere(AppUser.Columns.Phone, WhereComparision.Like, SearchPhone);
}
if (from != DateTime.MinValue)
{
q.AddWhere(AppUser.Columns.CreateDate, WhereComparision.GreaterThanOrEqual, from);
}
if (to != DateTime.MinValue)
{
to = to.Date + new TimeSpan(23, 59, 59);
q.AddWhere(AppUser.Columns.CreateDate, WhereComparision.LessThanOrEqual, to);
}
if (PageSize > 0)
{
q.LimitRows(PageSize).OffsetRows(PageSize * CurrentPageIndex);
}
using (DataReaderBase reader = q.ExecuteReader())
{
while (reader.Read())
{
AppUserUI app_user_ui = new AppUserUI();
app_user_ui.AppUserId = Convert.ToInt64(reader[AppUser.Columns.AppUserId]);
app_user_ui.Email = reader[AppUser.Columns.Email] == null ? "" : reader[AppUser.Columns.Email].ToString();
app_user_ui.IsLocked = Convert.ToBoolean(reader[AppUser.Columns.IsLocked]);
app_user_ui.FirstName = reader[AppUser.Columns.FirstName] == null ? "" : reader[AppUser.Columns.FirstName].ToString();
app_user_ui.LastName = reader[AppUser.Columns.LastName] == null ? "" : reader[AppUser.Columns.LastName].ToString();
app_user_ui.Phone = reader[AppUser.Columns.Phone] == null ? "" : reader[AppUser.Columns.Phone].ToString();
app_user_ui.LastLogin = reader[AppUser.Columns.LastLogin] == null ? "" : Convert.ToDateTime(reader[AppUser.Columns.LastLogin]).ToLocalTime().ToString(@"dd/MM/yyyy HH:mm");
app_user_ui.IsAdv = Convert.ToBoolean(reader[AppUser.Columns.IsAdv]);
app_user_ui.ApartmentNumber = reader[AppUser.Columns.ApartmentNumber] == null ? "" : reader[AppUser.Columns.ApartmentNumber].ToString();
app_user_ui.Street = reader[AppUser.Columns.Street] == null ? "" : reader[AppUser.Columns.Street].ToString();
app_user_ui.HouseNum = reader[AppUser.Columns.HouseNum] == null ? "" : reader[AppUser.Columns.HouseNum].ToString();
app_user_ui.Floor = reader[AppUser.Columns.Floor] == null ? "" : reader[AppUser.Columns.Floor].ToString();
app_user_ui.CityName = reader[City.Columns.CityName] == null ? "" : reader[City.Columns.CityName].ToString();
app_user_ui.CreateDate = reader[AppUser.Columns.CreateDate] == null ? "" : Convert.ToDateTime(reader[AppUser.Columns.CreateDate]).ToLocalTime().ToString(@"dd/MM/yyyy");
app_users.Add(app_user_ui);
}
}
return(app_users);
}