static public string CodeForTicket()
{
string result = null;
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
conn.beginTransaction();
Query qry = new Query(GymTicketCodeBank.TableSchema)
.Select(GymTicketCodeBank.Columns.Barcode)
.LimitRows(1)
.Hint(QueryHint.ForUpdate);
object code = qry.ExecuteScalar(conn);
if (!code.IsNull())
{
qry.Delete().Where(GymTicketCodeBank.Columns.Barcode, code).Execute(conn);
conn.commitTransaction();
result = (string)code;
}
}
if (result != null)
{
return(result);
}
else
{
GenerateNextCodeSetIfNeeded();
return(CodeForTicket());
}
}
public static List <AppSupplierTokenUI> GetAllAPNSAppSupplierTokenUI()
{
List <AppSupplierTokenUI> AppSuppliersTokensUI = new List <AppSupplierTokenUI>();
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query qry = Query.New <AppSupplierAPNSToken>()
.Select(AppSupplierAPNSToken.Columns.Token)
.AddSelect(AppSupplierAPNSToken.Columns.SupplierId)
.AddSelect(AppSupplier.TableSchema.SchemaName, AppSupplier.Columns.UnreadNotificationCount, AppSupplier.Columns.UnreadNotificationCount)
.Join(JoinType.InnerJoin, AppSupplier.TableSchema, AppSupplier.TableSchema.SchemaName, new JoinColumnPair(AppSupplierAPNSToken.TableSchema, AppSupplierAPNSToken.Columns.SupplierId, AppSupplier.Columns.SupplierId));
using (DataReaderBase reader = qry.ExecuteReader())
{
while (reader.Read())
{
AppSupplierTokenUI AppSupplierTokensUI = new AppSupplierTokenUI();
AppSupplierTokensUI.SupplierId = Convert.ToInt64(reader[AppSupplierAPNSToken.Columns.SupplierId]);
AppSupplierTokensUI.Token = reader[AppSupplierAPNSToken.Columns.Token].ToString();
AppSupplierTokensUI.UnreadNotificationCount = Convert.ToInt32(reader[AppSupplier.Columns.UnreadNotificationCount]);
AppSuppliersTokensUI.Add(AppSupplierTokensUI);
}
}
}
return(AppSuppliersTokensUI);
}
public static int CreateCart(Int64 AppUserId, Int64 TempAppUserId, Int64 supplierId, decimal totalPrice, Dictionary <Int64, int> LstProduct)
{
var cart = new Cart
{
UserId = AppUserId > 0 ? AppUserId : (long?)null,
CartTotalPrice = totalPrice,
SupplierId = supplierId,
CreatedDate = DateTime.Now,
TempUserId = TempAppUserId > 0 ? TempAppUserId : (long?)null
};
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
cart.Save(conn);
}
foreach (KeyValuePair <Int64, int> item in LstProduct)
{
CartProduct cartProduct = new CartProduct();
cartProduct.CartId = cart.CartId;
cartProduct.ProductAmount = item.Value;
cartProduct.ProductId = item.Key;
cartProduct.Save();
}
return(cart.CartId);
}
private static void SendApnsNotification(string Message, Int32 Badge, string Sound, string ActionType)
{
List <string> tokens = new List <string>();
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query qry = Query.New <AppSupplierAPNSToken>()
.Select(AppSupplierAPNSToken.Columns.Token);
using (DataReaderBase reader = qry.ExecuteReader())
{
if (reader.Read())
{
tokens.Add(reader.GetString(0));
}
}
}
foreach (string token in tokens)
{
NotificationService service = APNSServiceSupplier.SharedInstance;
service.SendMessage(token,
Message,
Badge,
(Sound == null || Sound.Length == 0) ? @"default" : Sound,
delegate()
{
Query.New <AppSupplierAPNSToken>().Delete()
.Where(AppSupplierAPNSToken.Columns.Token, token).Execute();
});
}
}
public static void CreateOrUpgrade()
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
conn.BeginTransaction();
foreach (TableSchema schema in Schemas)
{
if (!conn.CheckIfTableExists(schema.SchemaName))
{
try { new Query(schema).CreateTable().Execute(conn); }
catch { }
try
{
if (schema.Indexes.Count > 0 || schema.ForeignKeys.Count > 0)
{
conn.ExecuteScript(new Query(schema).CreateIndexes().ToString());
}
}
catch { }
}
else
{
foreach (TableSchema.Column col in schema.Columns)
{
try { new Query(schema).AddColumn(col).Execute(conn); }
catch { }
try { new Query(schema).ChangeColumn(col).Execute(conn); }
catch { }
}
foreach (TableSchema.Index idx in schema.Indexes)
{
try { new Query(schema).CreateIndex(idx).Execute(conn); }
catch { }
}
foreach (TableSchema.ForeignKey key in schema.ForeignKeys)
{
try { new Query(schema).CreateForeignKey(key).Execute(conn); }
catch { }
}
}
}
foreach (string key in Permissions.SystemPermissionKeys)
{
try
{
if (PermissionCollection.Where(Permission.Columns.Key, key).Count == 0)
{
new Permission(key).Save(conn);
}
}
catch { }
}
conn.CommitTransaction();
}
}
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);
}
}
static public void GenerateNextCodeSetIfNeeded()
{
Int64 count = Query.New <GymTicketCodeBank>().GetCount(GymTicketCodeBank.Columns.GymTicketCodeBankId);
if (count >= MinCount)
{
return;
}
Int64 latest = FromBase34((Query.New <GymTicketCodeBankPermanent>().GetMax(GymTicketCodeBank.Columns.Barcode) as string) ?? @"");
List <Int64> codes = new List <Int64>();
try
{
for (Int64 j = 0; j < AmountToGenerate; j++)
{
latest++;
codes.Add(latest);
}
}
catch {}
Random rng = new Random();
int n = codes.Count, k;
Int64 temp;
while (n > 1)
{
k = rng.Next(n--);
temp = codes[n];
codes[n] = codes[k];
codes[k] = temp;
}
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
conn.beginTransaction();
Query qry1 = Query.New <GymTicketCodeBank>();
Query qry2 = Query.New <GymTicketCodeBankPermanent>();
foreach (Int64 code in codes)
{
qry1.ClearInsertAndUpdate().Insert(GymTicketCodeBank.Columns.Barcode, ToBase34(code, CodeDigits)).Execute(conn);
qry2.ClearInsertAndUpdate().Insert(GymTicketCodeBankPermanent.Columns.Barcode, ToBase34(code, CodeDigits)).Execute(conn);
}
conn.commitTransaction();
}
}
protected void btnRestore_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (fuRestoreFile.PostedFile == null || fuRestoreFile.PostedFile.ContentLength == 0)
{
Master.MessageCenter.DisplayWarningMessage(DatabaseBackupStrings.GetText(@"BackupFileEmptyErrorMessage"));
return;
}
string fullSqlPath = Files.AquireUploadFileName(@"MySqlBackup.sql", Settings.GetSetting(Settings.Keys.SECURE_UPLOAD_FOLDER), null, true, true, false);
fuRestoreFile.PostedFile.SaveAs(fullSqlPath);
Files.TemporaryFileDeleter fileDeleter = new Files.TemporaryFileDeleter(fullSqlPath);
try
{
string sql = @"";
if (Path.GetExtension(fuRestoreFile.PostedFile.FileName).ToLower() == @".gz")
{
using (GZipStream gzipStream = new GZipStream(File.Open(fullSqlPath, FileMode.Open, FileAccess.Read, FileShare.Read), CompressionMode.Decompress))
{
using (StreamReader reader = new StreamReader(gzipStream, Encoding.UTF8, true))
{
sql = reader.ReadToEnd();
}
}
}
else
{
sql = File.ReadAllText(fullSqlPath, Encoding.UTF8);
}
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
conn.ExecuteScript(sql);
}
fileDeleter.DeleteFile();
Master.MessageCenter.DisplaySuccessMessage(DatabaseBackupStrings.GetText(@"RestoreFinishedMessage"));
}
catch (System.Exception ex)
{
Master.MessageCenter.DisplayErrorMessage(string.Format(DatabaseBackupStrings.GetText(@"RestoreGeneralError"), ex.Message));
}
}
}
public static Int64 AddNewMessage(Int64 bidId, Int64 supplierId, long originalSupplierId = 0, string state = START_STAGE)
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
//BidMessage message = get_message_by_bid_and_supplier(bidId, supplierId);
// if (message != null) return 0;
BidMessage message = new BidMessage();
message.BidId = bidId;
message.SupplierId = supplierId;
message.Stage = state;
message.IsActive = true;
message.ExpirationTime = DateTime.Now;
message.SendingTime = DateTime.Now;
message.OriginalSupplierId = originalSupplierId == 0? supplierId : originalSupplierId;
message.Save(conn);
return(message.MessageId);
}
}
public static bool AddCreditToAppUser(Int64 AppUserId, decimal Credit)
{
if (Credit == 0)
{
return(true);
}
using (ConnectorBase connector = ConnectorBase.NewInstance())
{
Query qry = new Query(AppUserBalance.TableSchema)
.Update(AppUserBalance.Columns.Credit, connector.encloseFieldName(AppUserBalance.Columns.Credit) + (Credit > 0 ? (@"+" + Credit) : (@"-" + -Credit)), true)
.Where(AppUserBalance.Columns.AppUserId, AppUserId);
if (Credit < 0)
{
qry.AddWhere(WhereCondition.AND, connector.encloseFieldName(AppUserBalance.Columns.Credit) + (Credit > 0 ? (@"+" + Credit) : (@"-" + -Credit)), ValueObjectType.Literal, WhereComparision.GreaterThanOrEqual, 0, ValueObjectType.Value);
}
int changes = qry.Execute(connector);
return(changes > 0);
}
}
public static bool RefundTicket(Int64 GymTicketId)
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query qry = Query.New <GymTicket>()
.Update(GymTicket.Columns.Status, GymTicketStatus.Refunded)
.Update(GymTicket.Columns.RedeemDate, DateTime.UtcNow)
.Where(GymTicket.Columns.GymTicketId, GymTicketId)
.AND(GymTicket.Columns.Status, dg.Sql.WhereComparision.NotEqualsTo, GymTicketStatus.Refunded);
bool result = qry.Execute(conn) == 1;
if (result)
{
decimal Amount = 0m;
Int64 AppUserId = 0;
using (DataReaderBase reader =
Query.New <GymTicket>()
.Select(GymTicket.Columns.Amount)
.AddSelect(GymTicket.Columns.AppUserId)
.Where(GymTicket.Columns.GymTicketId, GymTicketId)
.LimitRows(1)
.ExecuteReader(conn))
{
if (reader.Read())
{
Amount = reader.GetDecimal(0);
AppUserId = reader.GetInt64(1);
}
}
if (AppUserId > 0)
{
AppUserCreditController.AddCreditToAppUser(AppUserId, Amount);
}
}
return(result);
}
}
public static void UpdateUnreadNotificationCount(int Increment)
{
List <Int64> appUsersIds = new List <Int64>();
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query q1 = Query.New <AppUserGcmToken>()
.Select(AppUserGcmToken.Columns.AppUserId);
using (DataReaderBase reader = q1.ExecuteReader())
{
while (reader.Read())
{
appUsersIds.Add(Convert.ToInt64(reader.GetString(0)));
}
}
}
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query q2 = Query.New <AppUserAPNSToken>()
.Select(AppUserAPNSToken.Columns.AppUserId);
using (DataReaderBase reader = q2.ExecuteReader())
{
while (reader.Read())
{
appUsersIds.Add(Convert.ToInt64(reader.GetString(0)));
}
}
}
appUsersIds = appUsersIds.Distinct().ToList();
if (appUsersIds.Count > 0)
{
Query.New <AppUser>()
.Update(AppUser.Columns.UnreadNotificationCount, new dg.Sql.Phrases.Add(AppUser.Columns.UnreadNotificationCount, Increment))
.Where(AppUser.TableSchema.SchemaName, AppUser.Columns.AppUserId, WhereComparision.In, appUsersIds)
.Execute();
}
}
static public void UserLoggedIn(Int64 AppUserId, DateTime PreviousLastLogin)
{
DateTime dateTime = DateTime.UtcNow;
if (PreviousLastLogin.Year == dateTime.Year &&
PreviousLastLogin.Month == dateTime.Month &&
PreviousLastLogin.Day == dateTime.Day &&
PreviousLastLogin.Hour == dateTime.Hour)
{
return;
}
try
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
//dateTime = dateTime.AddMilliseconds(-dateTime.Millisecond).AddSeconds(-dateTime.Second).AddMinutes(-dateTime.Minute);
//Query qryUpdate = Query.New<AppUserHourlyActivity>()
// .Update(AppUserHourlyActivity.Columns.ActiveUsers, conn.EncloseFieldName(AppUserHourlyActivity.Columns.ActiveUsers) + @"+" + 1)
// .Where(AppUserHourlyActivity.Columns.Date, dateTime);
//if (qryUpdate.Execute(conn) == 0)
//{
// Query qryInsert = Query.New<AppUserHourlyActivity>()
// .Insert(AppUserHourlyActivity.Columns.ActiveUsers, conn.EncloseFieldName(AppUserHourlyActivity.Columns.ActiveUsers) + @"+" + 1)
// .Insert(AppUserHourlyActivity.Columns.Date, dateTime);
// try
// {
// qryInsert.Execute();
// }
// catch
// {
// qryUpdate.Execute();
// }
//}
}
}
catch { }
}
public static void CreateOrUpgrade()
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
conn.BeginTransaction();
foreach (TableSchema schema in Schemas)
{
if (!conn.CheckIfTableExists(schema.SchemaName))
{
try { new Query(schema).CreateTable().Execute(conn); }
catch { }
try
{
if (schema.Indexes.Count > 0 || schema.ForeignKeys.Count > 0)
{
conn.ExecuteScript(new Query(schema).CreateIndexes().ToString());
}
}
catch { }
}
else
{
foreach (TableSchema.Column col in schema.Columns)
{
try { new Query(schema).AddColumn(col).Execute(conn); }
catch { }
try { new Query(schema).ChangeColumn(col).Execute(conn); }
catch { }
}
foreach (TableSchema.Index idx in schema.Indexes)
{
try { new Query(schema).CreateIndex(idx).Execute(conn); }
catch { }
}
foreach (TableSchema.ForeignKey key in schema.ForeignKeys)
{
try { new Query(schema).CreateForeignKey(key).Execute(conn); }
catch { }
}
}
}
foreach (string key in Permissions.SystemPermissionKeys)
{
try
{
if (PermissionCollection.Where(Permission.Columns.Key, key).Count == 0)
{
new Permission(key).Save(conn);
}
}
catch { }
}
string dropTriggers = @"
DROP TRIGGER IF EXISTS tg_trempmember_CountersInsert;
DROP TRIGGER IF EXISTS tg_trempmember_CountersUpdate;
DROP TRIGGER IF EXISTS tg_trempmember_CountersDelete;
DROP TRIGGER IF EXISTS tg_trempjoinrequest_CountersInsert;
DROP TRIGGER IF EXISTS tg_trempjoinrequest_CountersUpdate;
DROP TRIGGER IF EXISTS tg_trempjoinrequest_CountersDelete;
DROP TRIGGER IF EXISTS tg_groupmemberrequest_CountersInsert;
DROP TRIGGER IF EXISTS tg_groupmemberrequest_CountersUpdate;
DROP TRIGGER IF EXISTS tg_groupmemberrequest_CountersDelete;
DROP TRIGGER IF EXISTS tg_groupadminrequest_CountersInsert;
DROP TRIGGER IF EXISTS tg_groupadminrequest_CountersUpdate;
DROP TRIGGER IF EXISTS tg_groupadminrequest_CountersDelete;
DROP TRIGGER IF EXISTS tg_tremprequestmatch_CountersInsert;
DROP TRIGGER IF EXISTS tg_tremprequestmatch_CountersUpdate;
DROP TRIGGER IF EXISTS tg_tremprequestmatch_CountersDelete;
DROP TRIGGER IF EXISTS tg_groupmember_CountersInsert;
DROP TRIGGER IF EXISTS tg_groupmember_CountersUpdate;
DROP TRIGGER IF EXISTS tg_groupmember_CountersDelete;
DROP TRIGGER IF EXISTS tg_tremp_Insert;
DROP TRIGGER IF EXISTS tg_tremp_Update;
DROP TRIGGER IF EXISTS tg_tremp_Delete;
DROP TRIGGER IF EXISTS tg_tremprequest_Insert;
DROP TRIGGER IF EXISTS tg_tremprequest_Update;
DROP TRIGGER IF EXISTS tg_tremprequest_Delete;
";
string createTriggers = @"
DELIMITER $$
CREATE TRIGGER tg_trempmember_CountersInsert
AFTER INSERT ON tbl_trempmember
FOR EACH ROW
BEGIN
set @TrempId=NEW.TrempId;
UPDATE tbl_tremp SET BookedSeatsCount = BookedSeatsCount + 1 WHERE TrempId = @TrempId;
END
$$
CREATE TRIGGER tg_trempmember_CountersUpdate
AFTER UPDATE ON tbl_trempmember
FOR EACH ROW
BEGIN
set @TrempId=NEW.TrempId;
set @OldTrempId=OLD.TrempId;
IF (@TrempId <> @TrempId) THEN
UPDATE tbl_tremp SET BookedSeatsCount = BookedSeatsCount - 1 WHERE TrempId = @OldTrempId;
UPDATE tbl_tremp SET BookedSeatsCount = BookedSeatsCount + 1 WHERE TrempId = @TrempId;
END IF;
END
$$
CREATE TRIGGER tg_trempmember_CountersDelete
AFTER DELETE ON tbl_trempmember
FOR EACH ROW
BEGIN
set @OldTrempId=OLD.TrempId;
UPDATE tbl_tremp SET BookedSeatsCount = BookedSeatsCount - 1 WHERE TrempId = @OldTrempId;
END
$$
CREATE TRIGGER tg_trempjoinrequest_CountersInsert
AFTER INSERT ON tbl_trempjoinrequest
FOR EACH ROW
BEGIN
set @TrempId=NEW.TrempId;
UPDATE tbl_tremp SET MemberRequestsCount = MemberRequestsCount + 1 WHERE TrempId = @TrempId;
END
$$
CREATE TRIGGER tg_trempjoinrequest_CountersUpdate
AFTER UPDATE ON tbl_trempjoinrequest
FOR EACH ROW
BEGIN
set @TrempId=NEW.TrempId;
set @OldTrempId=OLD.TrempId;
IF (@TrempId <> @TrempId) THEN
UPDATE tbl_tremp SET MemberRequestsCount = MemberRequestsCount - 1 WHERE TrempId = @OldTrempId;
UPDATE tbl_tremp SET MemberRequestsCount = MemberRequestsCount + 1 WHERE TrempId = @TrempId;
END IF;
END
$$
CREATE TRIGGER tg_trempjoinrequest_CountersDelete
AFTER DELETE ON tbl_trempjoinrequest
FOR EACH ROW
BEGIN
set @OldTrempId=OLD.TrempId;
UPDATE tbl_tremp SET MemberRequestsCount = MemberRequestsCount - 1 WHERE TrempId = @OldTrempId;
END
$$
CREATE TRIGGER tg_groupmemberrequest_CountersInsert
AFTER INSERT ON tbl_groupmemberrequest
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
UPDATE tbl_group SET MemberRequestsCount = MemberRequestsCount + 1 WHERE GroupId = @GroupId;
END
$$
CREATE TRIGGER tg_groupmemberrequest_CountersUpdate
AFTER UPDATE ON tbl_groupmemberrequest
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
set @OldGroupId=OLD.GroupId;
IF (@GroupId <> @GroupId) THEN
UPDATE tbl_group SET MemberRequestsCount = MemberRequestsCount - 1 WHERE GroupId = @OldGroupId;
UPDATE tbl_group SET MemberRequestsCount = MemberRequestsCount + 1 WHERE GroupId = @GroupId;
END IF;
END
$$
CREATE TRIGGER tg_groupmemberrequest_CountersDelete
AFTER DELETE ON tbl_groupmemberrequest
FOR EACH ROW
BEGIN
set @OldGroupId=OLD.GroupId;
UPDATE tbl_group SET MemberRequestsCount = MemberRequestsCount - 1 WHERE GroupId = @OldGroupId;
END
$$
CREATE TRIGGER tg_groupadminrequest_CountersInsert
AFTER INSERT ON tbl_groupadminrequest
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
UPDATE tbl_group SET AdminRequestsCount = AdminRequestsCount + 1 WHERE GroupId = @GroupId;
END
$$
CREATE TRIGGER tg_groupadminrequest_CountersUpdate
AFTER UPDATE ON tbl_groupadminrequest
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
set @OldGroupId=OLD.GroupId;
IF (@GroupId <> @GroupId) THEN
UPDATE tbl_group SET AdminRequestsCount = AdminRequestsCount - 1 WHERE GroupId = @OldGroupId;
UPDATE tbl_group SET AdminRequestsCount = AdminRequestsCount + 1 WHERE GroupId = @GroupId;
END IF;
END
$$
CREATE TRIGGER tg_groupadminrequest_CountersDelete
AFTER DELETE ON tbl_groupadminrequest
FOR EACH ROW
BEGIN
set @OldGroupId=OLD.GroupId;
UPDATE tbl_group SET AdminRequestsCount = AdminRequestsCount - 1 WHERE GroupId = @OldGroupId;
END
$$
CREATE TRIGGER tg_tremprequestmatch_CountersInsert
AFTER INSERT ON tbl_tremprequestmatch
FOR EACH ROW
BEGIN
set @TrempRequestId=NEW.TrempRequestId;
UPDATE tbl_tremprequest SET FoundMatchesCount = FoundMatchesCount + 1 WHERE TrempRequestId = @TrempRequestId;
END
$$
CREATE TRIGGER tg_tremprequestmatch_CountersUpdate
AFTER UPDATE ON tbl_tremprequestmatch
FOR EACH ROW
BEGIN
set @TrempRequestId=NEW.TrempRequestId;
set @OldTrempRequestId=OLD.TrempRequestId;
IF (@TrempRequestId <> @TrempRequestId) THEN
UPDATE tbl_tremprequest SET FoundMatchesCount = FoundMatchesCount - 1 WHERE TrempRequestId = @OldTrempRequestId;
UPDATE tbl_tremprequest SET FoundMatchesCount = FoundMatchesCount + 1 WHERE TrempRequestId = @TrempRequestId;
END IF;
END
$$
CREATE TRIGGER tg_tremprequestmatch_CountersDelete
AFTER DELETE ON tbl_tremprequestmatch
FOR EACH ROW
BEGIN
set @OldTrempRequestId=OLD.TrempRequestId;
UPDATE tbl_tremprequest SET FoundMatchesCount = FoundMatchesCount - 1 WHERE TrempRequestId = @OldTrempRequestId;
END
$$
CREATE TRIGGER tg_groupmember_CountersInsert
AFTER INSERT ON tbl_groupmember
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
UPDATE tbl_group SET MemberCount = MemberCount + 1 WHERE GroupId = @GroupId;
END
$$
CREATE TRIGGER tg_groupmember_CountersUpdate
AFTER UPDATE ON tbl_groupmember
FOR EACH ROW
BEGIN
set @GroupId=NEW.GroupId;
set @OldGroupId=OLD.GroupId;
IF (@GroupId <> @GroupId) THEN
UPDATE tbl_group SET MemberCount = MemberCount - 1 WHERE GroupId = @OldGroupId;
UPDATE tbl_group SET MemberCount = MemberCount + 1 WHERE GroupId = @GroupId;
END IF;
END
$$
CREATE TRIGGER tg_groupmember_CountersDelete
AFTER DELETE ON tbl_groupmember
FOR EACH ROW
BEGIN
set @OldGroupId=OLD.GroupId;
UPDATE tbl_group SET MemberCount = MemberCount - 1 WHERE GroupId = @OldGroupId;
END
$$
CREATE TRIGGER tg_tremp_Insert
AFTER INSERT ON tbl_Tremp
FOR EACH ROW
BEGIN
SET @TrempId=NEW.TrempId;
SET @FromAddressLatitude=NEW.FromAddressLatitude;
SET @FromAddressLongitude=NEW.FromAddressLongitude;
SET @ToAddressLatitude=NEW.ToAddressLatitude;
SET @ToAddressLongitude=NEW.ToAddressLongitude;
IF (@FromAddressLatitude IS NOT NULL AND @FromAddressLongitude IS NOT NULL AND @ToAddressLatitude IS NOT NULL AND @ToAddressLongitude IS NOT NULL) THEN
INSERT INTO tbl_SpatialTremp (TrempId, FromAddress, ToAddress) VALUES(@TrempId,POINT(@FromAddressLatitude,@FromAddressLongitude),POINT(@ToAddressLatitude,@ToAddressLongitude));
END IF;
END
$$
CREATE TRIGGER tg_tremp_Update
AFTER UPDATE ON tbl_Tremp
FOR EACH ROW
BEGIN
SET @TrempId=NEW.TrempId;
SET @FromAddressLatitude=NEW.FromAddressLatitude;
SET @FromAddressLongitude=NEW.FromAddressLongitude;
SET @ToAddressLatitude=NEW.ToAddressLatitude;
SET @ToAddressLongitude=NEW.ToAddressLongitude;
SET @OldTrempId=OLD.TrempId;
SET @OldFromAddressLatitude=OLD.FromAddressLatitude;
SET @OldFromAddressLongitude=OLD.FromAddressLongitude;
SET @OldToAddressLatitude=OLD.ToAddressLatitude;
SET @OldToAddressLongitude=OLD.ToAddressLongitude;
IF (@TrempId <> @OldTrempId OR @FromAddressLatitude <> @OldFromAddressLatitude OR @FromAddressLongitude <> @OldFromAddressLongitude OR @ToAddressLatitude <> @OldToAddressLatitude OR @ToAddressLongitude <> @OldToAddressLongitude) THEN
DELETE FROM tbl_SpatialTremp WHERE TrempId = @OldTrempId;
IF (@FromAddressLatitude IS NOT NULL AND @FromAddressLongitude IS NOT NULL AND @ToAddressLatitude IS NOT NULL AND @ToAddressLongitude IS NOT NULL) THEN
INSERT INTO tbl_SpatialTremp (TrempId, FromAddress, ToAddress) VALUES(@TrempId,POINT(@FromAddressLatitude,@FromAddressLongitude),POINT(@ToAddressLatitude,@ToAddressLongitude));
END IF;
END IF;
END
$$
CREATE TRIGGER tg_tremp_Delete
AFTER DELETE ON tbl_Tremp
FOR EACH ROW
BEGIN
SET @OldTrempId=OLD.TrempId;
DELETE FROM tbl_SpatialTremp WHERE TrempId = @OldTrempId;
END
$$
CREATE TRIGGER tg_tremprequest_Insert
AFTER INSERT ON tbl_TrempRequest
FOR EACH ROW
BEGIN
SET @TrempRequestId=NEW.TrempRequestId;
SET @FromAddressLatitude=NEW.FromAddressLatitude;
SET @FromAddressLongitude=NEW.FromAddressLongitude;
SET @ToAddressLatitude=NEW.ToAddressLatitude;
SET @ToAddressLongitude=NEW.ToAddressLongitude;
IF (@FromAddressLatitude IS NOT NULL AND @FromAddressLongitude IS NOT NULL AND @ToAddressLatitude IS NOT NULL AND @ToAddressLongitude IS NOT NULL) THEN
INSERT INTO tbl_SpatialTrempRequest (TrempRequestId, FromAddress, ToAddress) VALUES(@TrempRequestId,POINT(@FromAddressLatitude,@FromAddressLongitude),POINT(@ToAddressLatitude,@ToAddressLongitude));
END IF;
END
$$
CREATE TRIGGER tg_tremprequest_Update
AFTER UPDATE ON tbl_TrempRequest
FOR EACH ROW
BEGIN
SET @TrempRequestId=NEW.TrempRequestId;
SET @FromAddressLatitude=NEW.FromAddressLatitude;
SET @FromAddressLongitude=NEW.FromAddressLongitude;
SET @ToAddressLatitude=NEW.ToAddressLatitude;
SET @ToAddressLongitude=NEW.ToAddressLongitude;
SET @OldTrempRequestId=OLD.TrempRequestId;
SET @OldFromAddressLatitude=OLD.FromAddressLatitude;
SET @OldFromAddressLongitude=OLD.FromAddressLongitude;
SET @OldToAddressLatitude=OLD.ToAddressLatitude;
SET @OldToAddressLongitude=OLD.ToAddressLongitude;
IF (@TrempRequestId <> @OldTrempRequestId OR @FromAddressLatitude <> @OldFromAddressLatitude OR @FromAddressLongitude <> @OldFromAddressLongitude OR @ToAddressLatitude <> @OldToAddressLatitude OR @ToAddressLongitude <> @OldToAddressLongitude) THEN
DELETE FROM tbl_SpatialTrempRequest WHERE TrempRequestId = @OldTrempRequestId;
IF (@FromAddressLatitude IS NOT NULL AND @FromAddressLongitude IS NOT NULL AND @ToAddressLatitude IS NOT NULL AND @ToAddressLongitude IS NOT NULL) THEN
INSERT INTO tbl_SpatialTrempRequest (TrempRequestId, FromAddress, ToAddress) VALUES(@TrempRequestId,POINT(@FromAddressLatitude,@FromAddressLongitude),POINT(@ToAddressLatitude,@ToAddressLongitude));
END IF;
END IF;
END
$$
CREATE TRIGGER tg_tremprequest_Delete
AFTER DELETE ON tbl_TrempRequest
FOR EACH ROW
BEGIN
SET @OldTrempRequestId=OLD.TrempRequestId;
DELETE FROM tbl_SpatialTrempRequest WHERE TrempRequestId = @OldTrempRequestId;
END
$$
DELIMITER ;";
conn.ExecuteScript(dropTriggers);
conn.ExecuteScript(createTriggers);
conn.CommitTransaction();
}
}
private static void SendGcmNotification(Int64 SupplierId, string CollapseKey, Int32 Badge, string ActionType, Dictionary <string, object> CustomItems, string timeToLive = null)
{
List <string> tokens = new List <string>();
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query qry = Query.New <AppSupplierGcmToken>()
.Select(AppSupplierGcmToken.Columns.Token)
.Where(AppSupplierGcmToken.Columns.SupplierId, SupplierId);
using (DataReaderBase reader = qry.ExecuteReader())
{
while (reader.Read())
{
tokens.Add(reader.GetString(0));
}
}
}
if (tokens.Count > 0)
{
dg.Utilities.GoogleCloudMessaging.HttpNotificationPayload payload = new dg.Utilities.GoogleCloudMessaging.HttpNotificationPayload(tokens.ToArray());
payload.CollapseKey = CollapseKey;
payload.RestrictedPackageName = GcmServiceSupplier.PackageName;
payload.AddCustom("badge", Badge);
if (timeToLive != null)
{
payload.TimeToLive = Convert.ToInt32(timeToLive) * 60;
}
if (ActionType != null)
{
payload.AddCustom("app-action", ActionType);
}
if (CustomItems != null)
{
foreach (string key in CustomItems.Keys)
{
if (CustomItems[key] == null)
{
continue;
}
payload.AddCustom(key, CustomItems[key]);
}
}
HttpNotificationService service = GcmServiceSupplier.SharedInstance;
service.SendMessage(payload, delegate(NotificationDeliveryResult x)
{
if (x.HttpStatusCode == HttpStatusCode.OK)
{
try
{
JObject response = JObject.Parse(x.Response);
if (response != null)
{
if (response.Value <int>("failure") > 0 || response.Value <int>("canonical_ids") > 0)
{
JArray results = response["results"] as JArray;
int resultIndex = 0;
foreach (JObject result in results)
{
JToken jToken;
if (result.TryGetValue("registration_id", out jToken))
{
Query.New <AppSupplierGcmToken>()
.Update(AppSupplierGcmToken.Columns.Token, jToken.Value <string>())
.Where(AppSupplierGcmToken.Columns.Token, payload.RegistrationIds[resultIndex])
.Execute();
}
else
{
if (result.TryGetValue("error", out jToken))
{
if (jToken.Value <string>() == "NotRegistered")
{
Query.New <AppSupplierGcmToken>()
.Delete()
.Where(AppSupplierGcmToken.Columns.Token, payload.RegistrationIds[resultIndex])
.Execute();
}
}
}
resultIndex++;
}
}
}
}
catch (Exception) { }
}
});
}
}
private static void SendApnsNotification(Int64 SupplierId, string MessageLocKey, object[] MessageLocArgs, Int32 Badge, string Sound, string ActionType, Dictionary <string, object> CustomItems)
{
List <string> tokens = new List <string>();
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Query qry = Query.New <AppSupplierAPNSToken>()
.Select(AppSupplierAPNSToken.Columns.Token)
.Where(AppSupplierAPNSToken.Columns.SupplierId, SupplierId);
using (DataReaderBase reader = qry.ExecuteReader())
{
while (reader.Read())
{
tokens.Add(reader.GetString(0));
}
}
}
NotificationAlert alert = new NotificationAlert();
alert.LocalizedKey = MessageLocKey;
if (MessageLocArgs != null)
{
alert.LocalizedArgs.AddRange(MessageLocArgs);
}
Dictionary <string, object[]> customItems = null;
if (ActionType != null)
{
customItems = new Dictionary <string, object[]>();
customItems[@"app-action"] = new object[] { ActionType };
}
if (CustomItems != null)
{
foreach (string key in CustomItems.Keys)
{
if (CustomItems[key] == null)
{
continue;
}
if (customItems == null)
{
customItems = new Dictionary <string, object[]>();
}
customItems[key] = new object[] { CustomItems[key] };
}
}
foreach (string token in tokens)
{
NotificationService service = APNSServiceSupplier.SharedInstance;
service.SendMessage(token,
alert,
Badge,
(Sound == null || Sound.Length == 0) ? @"default" : Sound,
customItems,
delegate()
{
Query qry = Query.New <AppSupplierAPNSToken>().Delete()
.Where(AppSupplierAPNSToken.Columns.Token, token);
});
}
}
protected void btnBackup_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
try
{
using (ConnectorBase conn = ConnectorBase.NewInstance())
{
Response.Clear();
Response.AddHeader(@"content-disposition", @"attachment;filename=MySqlBackup_" + DateTime.UtcNow.ToString(@"yyyy_MM_dd_HH_mm_ss") + (chkBackupToGzip.Checked ? @".gz" : ".sql"));
Response.Charset = @"UTF-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
if (chkBackupToGzip.Checked)
{
Response.ContentType = @"application/x-gzip";
}
else
{
Response.ContentType = @"application/octet-stream";
}
MySqlBackup.BackupOptions options = new MySqlBackup.BackupOptions();
options.BOM = true;
options.WrapInTransaction = true;
options.ExportTableCreate = true;
options.ExportTableDrop = true;
options.ExportTableData = true;
options.ExportRoutines = true;
options.ExportTriggers = true;
if (chkBackupToGzip.Checked)
{
using (GZipStream gzipStream = new GZipStream(Response.OutputStream, CompressionMode.Compress))
{
MySqlBackup.GenerateBackup((MySqlConnector)conn, gzipStream, options);
}
}
else
{
MySqlBackup.GenerateBackup((MySqlConnector)conn, Response.OutputStream, options);
}
Response.End();
}
}
catch (DbException ex)
{
Response.ContentType = @"text/html";
try
{
Response.Headers.Remove(@"content-disposition");
}
catch
{
Response.ClearHeaders();
}
Response.Clear();
Master.MessageCenter.DisplayErrorMessage(ex.Message);
}
}
}