public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.CityId, CityId);
qry.Update(Columns.SupplierId, SupplierId);
qry.Update(Columns.ServiceId, ServiceId);
qry.Where(Columns.CityId, CityId);
qry.AND(Columns.SupplierId, SupplierId);
qry.AND(Columns.ServiceId, ServiceId);
qry.Execute(conn);
}
public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.ProductId, ProductId);
qry.Update(Columns.FilterId, FilterId);
qry.Update(Columns.SubFilterId, SubFilterId);
qry.Where(Columns.ProductId, ProductId);
qry.AND(Columns.FilterId, FilterId);
qry.AND(Columns.SubFilterId, SubFilterId);
qry.Execute(conn);
}
//获取登陆用户包含员工Id
public static string getEmployeeOfMaster(string MasterName, string MasterPsd)
{
Query q = Master.Query();
q.AddWhere(Master.Columns.MasterName, MasterName);
q.AND(Master.Columns.MasterPsd, MasterPsd);
DataTable dt = q.ExecuteDataSet().Tables[0];
return(dt.Rows[0]["EmployeeId"].ToString());
}
public static bool RedeemTicket(Int32 GymAccountId, Int32 GymId, string Barcode)
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Barcode = CleanupBarcode(Barcode);
Query qry = Query.New <GymTicket>()
.Update(GymTicket.Columns.Status, GymTicketStatus.Redeemed)
.Update(GymTicket.Columns.RedeemDate, DateTime.UtcNow)
.Where(GymTicket.TableSchema.SchemaName, GymTicket.Columns.Barcode, dg.Sql.WhereComparision.EqualsTo, Barcode)
.AND(GymTicket.TableSchema.SchemaName, GymTicket.Columns.Status, dg.Sql.WhereComparision.EqualsTo, GymTicketStatus.Available);
if (GymAccountId > 0)
{
qry.Join(JoinType.InnerJoin, Gym.TableSchema, Gym.TableSchema.SchemaName,
new JoinColumnPair(GymTicket.TableSchema, GymTicket.Columns.GymId, Gym.Columns.GymId))
.AND(Gym.TableSchema.SchemaName, Gym.Columns.GymAccountId, dg.Sql.WhereComparision.EqualsTo, GymAccountId);
}
if (GymId > 0)
{
qry.AND(GymTicket.TableSchema.SchemaName, GymTicket.Columns.GymId, dg.Sql.WhereComparision.EqualsTo, GymId);
}
bool result = qry.Execute(conn) == 1;
try
{
Int64 AppUserId = 0;
using (DataReaderBase reader =
Query.New <GymTicket>()
.Select(GymTicket.Columns.GymId)
.AddSelect(GymTicket.Columns.AppUserId)
.Where(GymTicket.Columns.Barcode, Barcode)
.LimitRows(1)
.ExecuteReader(conn))
{
if (reader.Read())
{
GymId = reader.GetInt32(0);
AppUserId = reader.GetInt64(1);
}
}
if (AppUserId > 0)
{
Int64 GymCheckinId;
GymCheckinController.CheckinPointsAction[] PointsGenerated;
GymCheckinController.Checkin(conn, AppUserId, GymId, Barcode, null, out GymCheckinId, out PointsGenerated);
}
}
catch { }
return(result);
}
}
public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.AppUserId, AppUserId);
qry.Update(Columns.Token, Token);
qry.Where(Columns.AppUserId, AppUserId);
qry.AND(Columns.Token, Token);
qry.Execute(conn);
}
public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.CategoryYad2Id, CategoryYad2Id);
qry.Update(Columns.ProductId, ProductId);
qry.Where(Columns.CategoryYad2Id, CategoryYad2Id);
qry.AND(Columns.ProductId, ProductId);
qry.Execute(conn);
}
public static UserCollection GetUserList(bool includeLocked, int limit, int offset)
{
Query qry = new Query(User.TableSchema);
if (!includeLocked)
{
qry.AND(User.Columns.IsLocked, false);
}
qry.OrderBy(User.Columns.Email, SortDirection.ASC);
qry.LimitRows(limit).OffsetRows(offset);
return(UserCollection.FetchByQuery(qry));
}
public static Query GetQuery(int hostID, int?userID, Nullable <ActionType> actionType, int?storyID, int?chatID)
{
Query query = new Query(UserAction.Schema).WHERE(UserAction.Columns.HostID, hostID).ORDER_BY(UserAction.Columns.CreatedOn, "DESC");
if (userID.HasValue)
{
query = query.AND(UserAction.Columns.UserID, userID);
}
if (actionType.HasValue)
{
query = query.AND(UserAction.Columns.UserActionTypeID, (int)actionType.Value);
}
if (storyID.HasValue)
{
query = query.AND(UserAction.Columns.StoryID, storyID);
}
if (chatID.HasValue)
{
query = query.AND(UserAction.Columns.ChatID, chatID);
}
return(query);
}
public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.SupplierId, SupplierId);
qry.Update(Columns.ProductId, ProductId);
qry.Update(Columns.Price, Price);
qry.Update(Columns.Gift, Gift);
qry.Update(Columns.CreateDate, CreateDate);
qry.Where(Columns.SupplierId, SupplierId);
qry.AND(Columns.ProductId, ProductId);
qry.Execute(conn);
}
/// <summary>
/// pass filter field AND contact id for selecting by user
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void objdsOrders_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
Int32 _userid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).UserId : -1;
Int32 _companyid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).CompanyId : -1;
//e.InputParameters["ContactID"] = _userid;
Query _qry = new Query(PublishipAdvanceOrderTable.Schema);
if (this.dxcbofields.Text.ToString() != string.Empty && this.txtQuickSearch.Text.ToString() != string.Empty)
{
string _fieldname = this.dxcbofields.SelectedItem.GetValue("fieldname").ToString();
string _txtsearch = this.txtQuickSearch.Text.ToLower();
_qry.AND(_fieldname, Comparison.Equals, _txtsearch);
}
if (!string.IsNullOrEmpty((string)this.dxcbocompany.Value))
{
_qry.AND("CompanyID", Comparison.Equals, this.dxcbocompany.Value.ToString());
}
e.InputParameters["qry"] = _qry;
}
public override void Update(ConnectorBase conn)
{
Query qry = new Query(TableSchema);
qry.Update(Columns.SupplierName, SupplierName);
qry.Update(Columns.DeviationPercentage, DeviationPercentage);
qry.Update(Columns.ProductName, ProductName);
qry.Update(Columns.ActualPrice, ActualPrice);
qry.Update(Columns.TimeOfApproval, TimeOfApproval);
qry.Update(Columns.IsApproved, IsApproved);
qry.Update(Columns.RecommendedPrice, RecommendedPrice);
qry.Where(Columns.SupplierId, SupplierId);
qry.AND(Columns.ProductId, ProductId);
qry.Execute(conn);
}
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 IEnumerable <WormholeConnection> GetWormholeConnectionsBetweenSystems(IList <SolarSystemViewModel> allSystems)
{
var systemNames = allSystems.Select(x => x.Name)
.ToArray();
using (var db = App.CreateStorageEngine())
{
var collection = db.GetCollection <WormholeConnection>();
var connections =
collection.Find(
Query.AND(
Query.In(FIRST_SYSTEM_PROPERTY_NAME, systemNames),
Query.In(SECOND_SYSTEM_PROPERTY_NAME, systemNames)));
return(CleanupConnections(connections, collection));
}
}
public IEnumerable <WormholeConnection> GetWormholeConnectionsBetweenSystemsWithout(IList <SolarSystemViewModel> allSystems,
IEnumerable <SolarSystemViewModel> without)
{
var systemNames = allSystems.Select(x => x.Name)
.ToArray();
var withoutNames = without.Select(x => x.Name)
.ToArray();
using (var db = App.CreateStorageEngine())
{
var collection = db.GetCollection <WormholeConnection>();
var connections = collection.Find(
Query.AND(
Query.In(FIRST_SYSTEM_PROPERTY_NAME, systemNames),
Query.In(SECOND_SYSTEM_PROPERTY_NAME, systemNames)))
.Where(x => !withoutNames.Contains(x.FirstSystem) || !withoutNames.Contains(x.SecondSystem));
return(CleanupConnections(connections, collection));
}
}
private Query ReadInlineQuery(StringScanner s)
{
var left = this.ReadOneQuery(s);
if (s.Scan(@"\s*\)\s*").Length > 0)
{
return(left);
}
var oper = s.Scan(@"\s*(and|or)\s*").Trim();
if (oper.Length == 0)
{
throw new ApplicationException("Invalid query operator");
}
return(oper == "and" ?
Query.AND(left, this.ReadInlineQuery(s)) :
Query.OR(left, this.ReadInlineQuery(s)));
}
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 DataTable get_datatable_subsonic()
{
//using a collection as it's the only way to directly apply a query string!
//query to grid
//Int32 _page = this.gridOrder.PageIndex;
//Int32 _pagesize = this.gridOrder.SettingsPager.PageSize;
//
String _companyid = "";
String _filter = "";
//check to see if we have a company id
//user MUST be logged in for advanced searches
Query _qry = new Query(Views.ViewOrder);
//need to switch this off when we are testing!
Page.Session.Remove("user");
if (Page.Session["user"] != null)
{
_companyid = ((UserClass)Page.Session["user"]).CompanyId.ToString();
_qry.WHERE("CompanyID = " + _companyid);
//_query = "WHERE CompanyId = " + _companyid;
}
if (Session["filter"] != null)
{
_filter = Session["filter"].ToString();
if (_companyid != string.Empty) { _qry.AND(_filter); } else { _qry.WHERE(_filter); }
//if (_companyid != string.Empty) { _query+= "AND " + _filter; } else {_query = " WHERE " + _filter ; }
}
//InlineQuery _qry = new InlineQuery();
//_qry.PageIndex = _page+1; //gridview page is 0 based, but subsonic is 1 based
//_qry.PageSize = _pagesize;
ViewOrderCollection _order = new ViewOrderCollection();
_order.LoadAndCloseReader(_qry.ExecuteReader());
DataTable _dt = (DataTable)_order.ToDataTable();
return _dt;
}
/// <summary>
/// pass filter field AND contact id for selecting by user
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void objdsOrders_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
Int32 _userid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).UserId : -1;
Int32 _companyid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).CompanyId : -1;
//e.InputParameters["ContactID"] = _userid;
Query _qry = new Query(PublishipAdvanceOrderTable.Schema);
if (this.dxcbofields.Text.ToString() != string.Empty && this.txtQuickSearch.Text.ToString() != string.Empty)
{
string _fieldname = this.dxcbofields.SelectedItem.GetValue("fieldname").ToString();
string _txtsearch = this.txtQuickSearch.Text.ToLower();
_qry.AND(_fieldname, Comparison.Equals, _txtsearch);
}
if (!string.IsNullOrEmpty((string)this.dxcbocompany.Value))
{
_qry.AND("CompanyID", Comparison.Equals, this.dxcbocompany.Value.ToString());
}
e.InputParameters["qry"] = _qry;
}
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);
}
public static Query GetQuery(int hostID, int? userID, Nullable<ActionType> actionType, int? storyID, int? chatID)
{
Query query = new Query(UserAction.Schema).WHERE(UserAction.Columns.HostID, hostID).ORDER_BY(UserAction.Columns.CreatedOn, "DESC");
if (userID.HasValue)
query = query.AND(UserAction.Columns.UserID, userID);
if (actionType.HasValue)
query = query.AND(UserAction.Columns.UserActionTypeID, (int)actionType.Value);
if (storyID.HasValue)
query = query.AND(UserAction.Columns.StoryID, storyID);
if (chatID.HasValue)
query = query.AND(UserAction.Columns.ChatID, chatID);
return query;
}
