public static void CreditWriters()
{
//Credit
var amount = "(SELECT [amount] FROM CTE WHERE CTE.userId = Users.UserId)";
var updateCommand = String.Format(@"
UPDATE Users SET
Users.Balance1 = Users.Balance1 + {0},
Users.TotalEarned = Users.TotalEarned + {0},
Users.StatsEarned = cast(({0}+CONVERT(decimal(19,8),(CAST(SUBSTRING(Users.StatsEarned,0, CHARINDEX('#', Users.StatsEarned)) AS varchar(30))))) as varchar(1000)) + SUBSTRING(Users.StatsEarned,CHARINDEX('#', Users.StatsEarned), 1000)
WHERE Users.UserId IN (SELECT DISTINCT UserId FROM CTE);", amount);
TableHelper.ExecuteRawCommandNonQuery(GetWritersCTE() + updateCommand);
//Add Balance Logs
var balanceLogsCommand = GetWritersCTE() + "SELECT * FROM CTE;";
BalanceLogManager.AddRange(balanceLogsCommand, "Article writing", BalanceType.MainBalance, BalanceLogType.ArticleWriting);
//Add Money credited to articles table
var creditArticlesQuery = @"
WITH CTE AS (
SELECT av.ArticleId AS Article, FLOOR((COUNT(av.Id))/1000) AS Quantity, m.ArticleCreatorCPM AS PricePerQuantity FROM ArticleViews av
INNER JOIN Articles a ON av.ArticleId = a.Id
INNER JOIN Users u ON u.UserId = a.CreatorUserId
INNER JOIN Memberships m ON m.MembershipId = u.UpgradeId
WHERE av.CreatorCredited = 0
GROUP BY av.ArticleId, m.ArticleCreatorCPM HAVING FLOOR((COUNT(av.Id))/1000) > 0
)
UPDATE Articles SET CreatorMoneyEarned = CreatorMoneyEarned + (SELECT Quantity * PricePerQuantity FROM CTE WHERE Articles.Id = CTE.Article)
WHERE Id IN (SELECT DISTINCT Article FROM CTE);";
TableHelper.ExecuteRawCommandNonQuery(creditArticlesQuery);
//Mark views as credited
var query = @"WITH CTE AS
( SELECT av.ArticleId AS Article, FLOOR((COUNT(av.Id))/1000) AS Quantity FROM ArticleViews av
INNER JOIN Articles a ON av.ArticleId = a.Id
WHERE av.CreatorCredited = 0
GROUP BY av.ArticleId HAVING FLOOR((COUNT(av.Id))/1000) > 0
)
UPDATE ArticleViews SET ArticleViews.CreatorCredited = 1
WHERE ArticleViews.ArticleId IN (SELECT DISTINCT Article FROM CTE) AND ArticleViews.Id IN (
SELECT TOP (SELECT Quantity*1000 FROM CTE WHERE ArticleViews.ArticleId = CTE.Article) av.Id FROM ArticleViews av
WHERE av.ArticleId IN (SELECT Article FROM CTE WHERE Article = ArticleViews.ArticleId) ORDER BY av.ViewDate ASC
)
";
TableHelper.ExecuteRawCommandNonQuery(query);
}
public static void CRON()
{
try
{
if (!AppSettings.TitanFeatures.AdvertMarketplaceEnabled)
{
return;
}
AppSettings.Marketplace.Reload();
if (AppSettings.Marketplace.MarketplaceFundsExpireAfterDays == -1)
{
return;
}
var serverTime = AppSettings.ServerTime.ToDBString();
var balanceUpdateCommand = string.Format(@"UPDATE Users SET MarketplaceBalance = MarketplaceBalance -
(SELECT SUM(Amount) FROM MarketplaceBalanceLogs mbl
WHERE mbl.UserId = Users.UserId
AND '{0}' > DateAdd(d, {1}, DateAdded))", serverTime, AppSettings.Marketplace.MarketplaceFundsExpireAfterDays);
var balanceLogsCommand = string.Format(@"SELECT
mbl.UserId AS userId,
- SUM(mbl.Amount) AS amount,
u.MarketplaceBalance AS state
FROM MarketplaceBalanceLogs mbl
JOIN Users u
ON mbl.UserId = u.UserId
WHERE '{0}' > DateAdd(d, 10, DateAdded)
GROUP BY mbl.UserId, u.MarketplaceBalance", serverTime);
var clearMBLCommand = string.Format(@"DELETE FROM MarketplaceBalanceLogs WHERE '{0}' > DateAdd(d, {1}, DateAdded);",
serverTime, AppSettings.Marketplace.MarketplaceFundsExpireAfterDays);
TableHelper.ExecuteRawCommandNonQuery(balanceLogsCommand);
using (var bridge = ParserPool.Acquire(Database.Client))
{
BalanceLogManager.AddRange(bridge.Instance, balanceLogsCommand, "Balance expiration", BalanceType.MarketplaceBalance, BalanceLogType.Other);
}
TableHelper.ExecuteRawCommandNonQuery(clearMBLCommand);
}
catch (Exception ex) { ErrorLogger.Log(ex); }
}
private static void RewardMembers()
{
string idsToReward = GetUserIdsToReward();
if (string.IsNullOrWhiteSpace(idsToReward))
{
return;
}
string amountQuery = @"(SELECT Reward FROM ReferralLeaderShipLevels WHERE Number = (CASE Users.LeadershipLevelId WHEN -1 THEN 1 ELSE (SELECT Number + 1 FROM ReferralLeadershipLevels WHERE Id = Users.LeadershipLevelId) END ))";
string rewardQuery = string.Format(@"UPDATE
Users
SET
Users.Balance1 = Users.Balance1 + {0},
Users.TotalEarned = Users.TotalEarned + {0},
Users.StatsEarned = cast(({0}+CONVERT(decimal(10,4),(CAST(SUBSTRING(Users.StatsEarned,0, CHARINDEX('#', Users.StatsEarned)) AS varchar(10))))) as varchar(1000)) +
SUBSTRING(Users.StatsEarned,CHARINDEX('#', Users.StatsEarned), 1000)
WHERE
Users.UserId IN ({1})", amountQuery, idsToReward);
TableHelper.ExecuteRawCommandNonQuery(rewardQuery);
string balanceLogHelperQuery = string.Format(@"SELECT
Users.UserId AS userId,
{0} as amount,
Users.Balance1 AS state
FROM Users
WHERE Users.UserId IN ({1})", amountQuery, idsToReward);
using (var bridge = ParserPool.Acquire(Database.Client))
{
BalanceLogManager.AddRange(bridge.Instance, balanceLogHelperQuery, "Leadership Reward", BalanceType.MainBalance, BalanceLogType.LeadershipReward);
}
string updateLeadershipIdQuery = string.Format(@"
UPDATE Users
SET
Users.LeadershipLevelId =
(SELECT Id FROM ReferralLeaderShipLevels
WHERE Number = CASE Users.LeadershipLevelId WHEN -1 THEN 1 ELSE (SELECT NextNumber FROM ReferralLeadershipLevels WHERE Id = Users.LeadershipLevelId) END),
Users.LeadershipResetTime = GETDATE()
WHERE UserId IN ({0})", idsToReward);
TableHelper.ExecuteRawCommandNonQuery(updateLeadershipIdQuery);
}
public static void CRON()
{
if (TitanFeatures.IsEpadilla)
{
//1. Send info about balances to S4DS
var UsersToSend = TableHelper.GetListFromRawQuery <Member>("SELECT * FROM Users WHERE Balance1 > 0");
foreach (var userToSend in UsersToSend)
{
try
{
CreditS4DSPacks(userToSend);
}
catch (Exception ex)
{
ErrorLogger.Log(ex);
}
}
try
{
//2. Reset all balances to 0
using (var parser = ParserPool.Acquire(Database.Client).Instance)
{
string dtCommand = String.Format(@"
SELECT
U.UserId AS userId,
-1 * U.Balance1 AS amount,
0.00000000 AS state
FROM Users U
WHERE
U.Balance1 > 0");
BalanceLogManager.AddRange(parser, dtCommand, "S4DS daily deduction", BalanceType.MainBalance, BalanceLogType.Other);
}
}
catch (Exception ex)
{
ErrorLogger.Log(ex);
}
TableHelper.ExecuteRawCommandNonQuery("UPDATE Users SET Balance1 = 0 WHERE Balance1 > 0");
}
}
public static void CreditInfluencers()
{
//Credit
var amount = "(SELECT [amount] FROM CTE WHERE CTE.userId = Users.UserId)";
var updateCommand = String.Format(@"
UPDATE Users SET
Users.Balance1 = Users.Balance1 + {0},
Users.TotalEarned = Users.TotalEarned + {0},
Users.StatsEarned = cast(({0}+CONVERT(decimal(19,8),(CAST(SUBSTRING(Users.StatsEarned,0, CHARINDEX('#', Users.StatsEarned)) AS varchar(30))))) as varchar(1000)) + SUBSTRING(Users.StatsEarned,CHARINDEX('#', Users.StatsEarned), 1000),
Users.StatsArticlesTotalSharesMoney = cast(({0}+CONVERT(decimal(19,8),(CAST(SUBSTRING(Users.StatsArticlesTotalSharesMoney,0, CHARINDEX('#', Users.StatsArticlesTotalSharesMoney)) AS varchar(30))))) as varchar(1000)) + SUBSTRING(Users.StatsArticlesTotalSharesMoney,CHARINDEX('#', Users.StatsArticlesTotalSharesMoney), 1000)
WHERE Users.UserId IN (SELECT DISTINCT UserId FROM CTE);", amount);
TableHelper.ExecuteRawCommandNonQuery(GetInfluencersCTE() + updateCommand);
//Add Balance Logs
var balanceLogsCommand = GetInfluencersCTE() + "SELECT * FROM CTE;";
BalanceLogManager.AddRange(balanceLogsCommand, "Article sharing", BalanceType.MainBalance, BalanceLogType.ArticleSharing);
//Mark views as credited
var query = @"WITH CTE AS
( SELECT av.ArticleId AS Article, av.InfluencerUserId AS Influencer, FLOOR((COUNT(av.Id))/1000) AS Quantity FROM ArticleViews av
WHERE av.InfluencerUserId > 0 AND av.InfluencerCredited = 0
GROUP BY av.ArticleId, av.InfluencerUserId HAVING FLOOR((COUNT(av.Id))/1000) > 0
)
UPDATE ArticleViews SET ArticleViews.InfluencerCredited = 1
WHERE
ArticleViews.ArticleId IN (SELECT DISTINCT Article FROM CTE) AND
ArticleViews.InfluencerUserId IN (SELECT DISTINCT Influencer FROM CTE) AND
ArticleViews.Id IN (
SELECT TOP
(SELECT Quantity*1000 FROM CTE WHERE ArticleViews.ArticleId = CTE.Article AND ArticleViews.InfluencerUserId = CTE.Influencer)
av.Id FROM ArticleViews av
WHERE av.ArticleId IN (SELECT Article FROM CTE WHERE Article = ArticleViews.ArticleId) AND av.InfluencerUserId IN (SELECT Influencer FROM CTE WHERE Influencer = ArticleViews.InfluencerUserId)
ORDER BY av.ViewDate ASC
)
";
TableHelper.ExecuteRawCommandNonQuery(query);
}
public Money DistributeUsingPriority(Money amount, int AdPackTypeId)
{
//Updates all adpacks
string AdPacksUpdateCommnad = String.Format(@"
UPDATE AdPacks
SET
MoneyReturned = MoneyReturned + (FLOOR({0} * ({1} * DistributionPriority)) / {0})
WHERE
DistributionPriority > 0 AND AdPackTypeId = {2}", MaxMultiplier, amount.ToClearString(), AdPackTypeId);
//Update all member balances
string MemberBalanceUpdateCommand = String.Format(@"
WITH CTE AS (
SELECT
P.UserId AS userId,
SUM(FLOOR({0} * ({1} * DistributionPriority)) / {0}) AS amount
FROM AdPacks P
WHERE
DistributionPriority > 0 AND AdPackTypeId = {4}
GROUP BY P.UserId
)
UPDATE
Users
SET
Users.Balance3 = Users.Balance3 + {3},
Users.Balance1 = Users.Balance1 + ({2} - {3}) * (1 - {5}),
Users.MarketplaceBalance = Users.MarketplaceBalance + ({2} - {3}) * {5},
Users.TotalEarned = Users.TotalEarned + ({2} - {3}) * (1 - {5}),
Users.StatsEarned = cast(({2}+CONVERT(decimal(19,8),(CAST(SUBSTRING(Users.StatsEarned,0, CHARINDEX('#', Users.StatsEarned)) AS varchar(30))))) as varchar(1000)) +
SUBSTRING(Users.StatsEarned,CHARINDEX('#', Users.StatsEarned), 1000),
Users.StatsRevShareCurrentWeekIncome = Users.StatsRevShareCurrentWeekIncome + {2},
Users.StatsRevShareCurrentMonthIncome = Users.StatsRevShareCurrentMonthIncome + {2}
WHERE
Users.UserId IN (SELECT DISTINCT UserID from CTE)", MaxMultiplier,
amount.ToClearString(),
AmountToEarn,
AmountToRepurchaseBalance(AdPackTypeId),
AdPackTypeId,
MarketplaceBalanceReturnPercentage(AdPackTypeId));
//BalanceLogs, add fast /MAIN BALANCE/
string dtCommand = String.Format(@"
SELECT
P.UserId AS userId,
(" + AmountCalculated + @" - " + AmountCalculatedForRepurchase(AdPackTypeId) + @") * (1 - " + MarketplaceBalanceReturnPercentage(AdPackTypeId) + @") AS amount,
U.Balance1 AS state
FROM AdPacks P
JOIN Users U
ON P.UserID = U.UserID
WHERE
DistributionPriority > 0 AND AdPackTypeId = " + AdPackTypeId + @"
GROUP BY P.UserId, U.Balance1, U.UpgradeId", amount.ToClearString());
//BalanceLogs, add fast /PURCHASE BALANCE/
string dtCommandAdBalance = String.Format(@"
SELECT
P.UserId AS userId,
" + AmountCalculatedForRepurchase(AdPackTypeId) + @" AS amount,
U.Balance3 AS state
FROM AdPacks P
JOIN Users U
ON P.UserID = U.UserID
WHERE
DistributionPriority > 0 AND AdPackTypeId = " + AdPackTypeId + @"
GROUP BY P.UserId, U.Balance3, U.UpgradeId", amount.ToClearString());
string dtCommandMarketplaceBalance = String.Format(@"
SELECT
P.UserId AS userId,
(" + AmountCalculated + @" - " + AmountCalculatedForRepurchase(AdPackTypeId) + @") * " + MarketplaceBalanceReturnPercentage(AdPackTypeId) + @" AS amount,
U.MarketplaceBalance AS state
FROM AdPacks P
JOIN Users U
ON P.UserID = U.UserID
WHERE
DistributionPriority > 0 AND AdPackTypeId = " + AdPackTypeId + @"
GROUP BY P.UserId, U.MarketplaceBalance, U.UpgradeId", amount.ToClearString());
parser.ExecuteRawCommandNonQuery(MemberBalanceUpdateCommand);
BalanceLogManager.AddRange(parser, dtCommand, AppSettings.RevShare.AdPack.AdPackName + " revenue", BalanceType.MainBalance, BalanceLogType.AdPackROI);
BalanceLogManager.AddRange(parser, dtCommandAdBalance, AppSettings.RevShare.AdPack.AdPackName + " revenue", BalanceType.PurchaseBalance, BalanceLogType.AdPackROI);
if (AppSettings.Payments.MarketplaceBalanceEnabled)
{
BalanceLogManager.AddRange(parser, dtCommandMarketplaceBalance, AppSettings.RevShare.AdPack.AdPackName + " revenue", BalanceType.MarketplaceBalance, BalanceLogType.AdPackROI);
}
parser.ExecuteRawCommandNonQuery(AdPacksUpdateCommnad);
//How much distributed
string selectDistributionAmount = String.Format(@"
SELECT SUM(FLOOR({0} * ({1} * DistributionPriority)) / {0}) FROM AdPacks
WHERE
DistributionPriority > 0 AND AdPackTypeId = {2}", MaxMultiplier, amount.ToClearString(), AdPackTypeId);
object result = parser.ExecuteRawCommandScalar(selectDistributionAmount);
if (result is DBNull)
{
return(Money.Zero);
}
return(Money.Parse(result.ToString()));
}