public static DataTable GetActiveMembershipsDataTables()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
return(bridge.Instance.ExecuteRawCommandToDataTable("SELECT * FROM Memberships WHERE Status = 1"));
}
}
/// <summary>
/// Handles direct referrals too, IT IS Fired after every successful watch ad action
/// </summary>
public void IClicked(PtcAdvert Ad)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
//Get all rented referrals "fired by" (normal and bots) the user
string Command = "SELECT * FROM RentedReferrals WHERE BotClass <> 0 AND FiredBy = '" + Username + "'";
DataTable result = bridge.Instance.ExecuteRawCommandToDataTable(Command);
var InterestingList = TableHelper.GetListFromDataTable <RentedReferral>(result, 100, true);
foreach (RentedReferral referral in InterestingList)
{
//Referral management
referral.LastClick = DateTime.Now;
referral.TotalClicks++;
//Stats
List <int> statlist = TableHelper.GetIntListFromString(referral.ClicksStats);
statlist[0]++;
referral.ClicksStats = TableHelper.GetStringFromIntList(statlist);
referral.Save();
//Add money to owneruser if can
Member OwnerUser = new Member(referral.OwnerUsername);
HandleOwnerUser(OwnerUser, Ad);
}
}
}
public static void AddRange(string dataTableCommand, string note, BalanceType target, BalanceLogType balanceLogType)
{
using (var parser = ParserPool.Acquire(Database.Client))
{
AddRange(parser.Instance, dataTableCommand, note, target, balanceLogType);
}
}
public static void ExecuteRawCommandNonQuery(string query)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
bridge.Instance.ExecuteRawCommandNonQuery(query);
}
}
public static void UpdateForumDB(BDProperty property, string value)
{
try
{
using (var bridge = ParserPool.Acquire(Database.Forum))
{
string command = "";
switch (property)
{
case BDProperty.Email:
command = @" UPDATE yaf_Registry SET VALUE = '" + value + "' WHERE RegistryID = 7 ";
break;
case BDProperty.Name:
command = @" UPDATE yaf_Board SET NAME = '" + value + "' WHERE BoardID = 1 ";
break;
case BDProperty.BaseURL:
command = @" UPDATE yaf_Registry SET VALUE = '" + value + "' WHERE RegistryID = 233 ";
break;
}
bridge.Instance.ExecuteRawCommandNonQuery(command);
}
}
catch (Exception e) { }
}
private static void EditForumRankID(string username, int rankID)
{
using (var bridge = ParserPool.Acquire(Database.Forum))
{
bridge.Instance.ExecuteRawCommandNonQuery(String.Format("UPDATE yaf_User SET RankID = {0} WHERE Name = '{1}'", rankID, username));
}
}
private void RentBotReferrals(int count, int howmanydays = -1)
{
DateTime rentTime = DateTime.Now.AddDays(AppSettings.RentedReferrals.CanBeRentedFor.Days);
if (howmanydays != -1)
{
rentTime = DateTime.Now.AddDays(howmanydays);
}
var dict = new Dictionary <string, object>();
using (var bridge = ParserPool.Acquire(Database.Client))
{
for (int i = 0; i < count; ++i)
{
int botclass = GetBotClass();
string firedbyName = (activeUsers.Count > 1) ? (activeUsers[activeUsersIndex++ % (activeUsers.Count - 1)]).Name : Username;
//Insert to RentedReferrals table
dict.Clear();
dict.Add("OwnerUsername", Username);
dict.Add("FiredBy", firedbyName);
dict.Add("BotClass", botclass);
dict.Add("ReferralSince", DateTime.Now);
dict.Add("ExpireDate", rentTime);
bridge.Instance.Insert(RentedReferral.TableName, dict);
ErrorLogger.Log(Username + " rented 1 bot referral (class " + botclass + ")", LogType.RefTrack);
}
}
}
private static int CountOf <T>(string tableName, Dictionary <string, object> where) where T : BaseTableObject
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
return(bridge.Instance.Count(tableName, where));
}
}
public static DataTable GetDataTableFromRawQuery(string query)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
return(bridge.Instance.ExecuteRawCommandToDataTable(query));
}
}
/// <exception cref="DbException" />
public static bool RowExists(string tableName, Dictionary <string, object> where)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
return(bridge.Instance.Count(tableName, where) > 0);
}
}
protected DataTable GetDataTable(string query)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
return(bridge.Instance.ExecuteRawCommandToDataTable(query));
}
}
private static List <T> SelectRows <T>(string tableName, Dictionary <string, object> where) where T : BaseTableObject
{
DataTable allObjectsTable;
Database ToBeUsed = Database.Client;
if (tableName == "AuthenticationPairs" || tableName == "PowerPacks")
{
ToBeUsed = Database.Service;
}
using (var bridge = ParserPool.Acquire(ToBeUsed))
{
allObjectsTable = bridge.Instance.Select(tableName, where);
}
List <T> list = new List <T>(allObjectsTable.Rows.Count);
var TType = typeof(T);
foreach (DataRow row in allObjectsTable.Rows)
{
list.Add(Activator.CreateInstance(TType, row, true) as T);
}
return(list);
}
/// <exception cref="DbException" />
public static void DeleteRows(string tableName, Dictionary <string, object> where)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
bridge.Instance.Delete(tableName, where);
}
}
protected override object GetDataFromSource()
{
DataTable usersAds;
var cache = new AdminIdCache();
string query;
using (var bridge = ParserPool.Acquire(Database.Client))
{
if (TitanFeatures.IsClickmyad)
{
query = string.Format(@"SELECT DISTINCT Id, Status, TargetUrl, ConstantImagePath, NormalImagePath,
CreatorUserId, Title, Description,
StartPageDate, Priority
FROM AdPacksAdverts WHERE Status = {0};", (int)AdvertStatus.Active);
}
else
{
query = string.Format(@"SELECT DISTINCT apa.Id, apa.Status, apa.TargetUrl, apa.ConstantImagePath, apa.NormalImagePath,
apa.CreatorUserId, apa.Title, apa.Description,
apa.StartPageDate, apa.Priority
FROM AdPacksAdverts apa
JOIN AdPacks ap ON apa.Id = ap.AdPacksAdvertId
WHERE ap.TotalClicks < ap.ClicksBought
AND (apa.CreatorUserId != {0} OR (apa.CreatorUserId = {0} AND apa.Priority != 0))
AND apa.Status = {1}
;", (int)cache.Get(), (int)AdvertStatus.Active);
}
usersAds = bridge.Instance.ExecuteRawCommandToDataTable(query);
}
return(usersAds);
}
private static void RecalculateStatistics(string columnName, bool isMoney)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
bridge.Instance.ExecuteRawCommandNonQuery(TableHelper.GetRawRecalculateCommand(Member.TableName, columnName, isMoney));
}
}
public RevenueSharesStatisticsManager()
{
//Total
TotalSoldAdPacks = TableHelper.CountOf <AdPack>();
using (var bridge = ParserPool.Acquire(Database.Client))
{
//Active
ActiveAdPacks = (int)bridge.Instance.ExecuteRawCommandScalar(@"
SELECT COUNT(*)
FROM AdPacks
WHERE
MoneyReturned < MoneyToReturn");
//Finished
FinishedAdPacks = TotalSoldAdPacks - ActiveAdPacks;
//Eligible
DistributionSQLHelper helper = new DistributionSQLHelper(bridge.Instance);
helper.SetStartingDistributionPriority();
EligibleAdPacks = (int)bridge.Instance.ExecuteRawCommandScalar(@"
SELECT COUNT(*)
FROM AdPacks
WHERE
DistributionPriority > 0");
Money.TryParse(TableHelper.SelectScalar("SELECT SUM(MoneyToReturn - MoneyReturned) FROM AdPacks").ToString(), out TotalOwed);
TotalOwed = TotalOwed == null ? Money.Zero : TotalOwed;
}
}
/// <summary>
/// Return current banner that should be displayed, NULL otherwise
/// </summary>
/// <param name="type"></param>
/// <param name="position">if you want to get different position than 1st banner (e.g. you have multiple banner controls)</param>
/// <returns></returns>
public static BannerAdvert GetCurrentBanner(BannerAdvertDimensions dimensions, int position = 1)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
var currentSql = bridge.Instance.ExecuteRawCommandToDataTable(BannerAuctionSQLManager.GetCurrentSQL(dimensions));
var currentList = TableHelper.GetListFromDataTable <BannerAuction>(currentSql, 1);
if (currentList.Count > 0)
{
BannerAuction auction = currentList[0];
BannerBid bid = auction.GetHighestBid(position);
if (bid != null)
{
if (!bid.RefAndPoolsCredited)
{
BannerCrediter crediter = new BannerCrediter(new Member(bid.Username));
Money moneyLeftForPools = crediter.CreditReferer(bid.BidValue);
PoolDistributionManager.AddProfit(ProfitSource.Banners, moneyLeftForPools);
bid.RefAndPoolsCredited = true;
bid.Save();
}
return(new BannerAdvert(bid.BannerAdvertId));
}
}
return(null);
}
}
public override string[] GetUsersInRole(string roleName)
{
var result = new List <string>();
if (roleName == REGISTERED)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
var dt = bridge.Instance.ExecuteRawCommandToDataTable("SELECT Username FROM Users");
foreach (DataRow dr in dt.Rows)
{
result.Add(dr[0].ToString());
}
}
}
else if (roleName == ADMINISTRATORS)
{
var resultList = TableHelper.SelectRows <Member>(TableHelper.MakeDictionary(Member.Columns.IsForumAdministrator, true));
foreach (var member in resultList)
{
result.Add(member.Name);
}
}
else if (roleName == MODERATORS)
{
var resultList = TableHelper.SelectRows <Member>(TableHelper.MakeDictionary(Member.Columns.IsForumModerator, true));
foreach (var member in resultList)
{
result.Add(member.Name);
}
}
return(result.ToArray());
}
public static List <T> GetListFromRawQuery <T>(string query) where T : BaseTableObject
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
var result = bridge.Instance.ExecuteRawCommandToDataTable(query);
return(TableHelper.GetListFromDataTable <T>(result));
}
}
protected override object GetDataFromSource()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
string command = "SELECT TOP 1 [Username] FROM [Users] ORDER BY [RegisterDate] DESC";
return(bridge.Instance.ExecuteRawCommandScalar(command).ToString());
}
}
public static List <ShoutboxMessage> GetLatestRecords(int count)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
DataTable dt = bridge.Instance.ExecuteRawCommandToDataTable(SELECT_SQL_COMMAND);
return(TableHelper.GetListFromDataTable <ShoutboxMessage>(dt, count));
}
}
private static void UpdateDomain(string oldDomain, string newDomain)
{
using (var bridge = ParserPool.Acquire(Database.Service))
{
var result = bridge.Instance.ExecuteRawCommandScalar(String.Format("UPDATE [AuthenticationPairs] SET WebsiteDomain = '{0}' WHERE WebsiteDomain = '{1}'",
newDomain, oldDomain));
}
}
protected override object GetDataFromSource()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
string command = "SELECT SUM(TotalClicks) FROM Users";
return(Convert.ToInt32(bridge.Instance.ExecuteRawCommandScalar(command)));
}
}
public static List <string> GetStringListFromRawQuery(string query)
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
var result = bridge.Instance.ExecuteRawCommandToDataTable(query);
return(TableHelper.GetStringListFromDataTable(result));
}
}
public List <BannerBid> GetBids()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
var highestSql = bridge.Instance.ExecuteRawCommandToDataTable("SELECT * FROM BannerBids WHERE BannerAuctionId = " + this.Id + " ORDER BY BidValue DESC");
return(TableHelper.GetListFromDataTable <BannerBid>(highestSql, 100, true));
}
}
public static List <ContestParticipant> GetForumParticipants(Contest contest)
{
try
{
var users = TableHelper.GetListFromQuery <Member>(@"WHERE Users.AccountStatusInt = 9
OR Users.AccountStatusInt = 1
OR Users.AccountStatusInt = 10");
var participants = new List <string>();
foreach (Member user in users)
{
if (contest.CanMemberParticipate(user, true))
{
participants.Add(user.Name);
}
}
DataTable result;
using (var bridge = ParserPool.Acquire(Database.Forum))
{
result = bridge.Instance.ExecuteRawCommandToDataTable(@"select COUNT(m.MessageID), us.Name from yaf_Message m
INNER JOIN yaf_Topic t ON t.TopicID = m.TopicID
INNER JOIN yaf_User us ON us.UserID = m.UserID
INNER JOIN yaf_Forum f ON f.ForumID = t.ForumID
WHERE m.IsDeleted = 0
AND m.IsApproved = 1
AND m.Posted > '" + contest.DateStart.ToDBString() +
"' AND m.Posted < '" + contest.DateEnd.ToDBString() +
"' AND f.ForumID in (select ForumId from ForumContests where ContestId = " + contest.Id + @")
GROUP BY us.Name;");
}
List <ContestParticipant> list = new List <ContestParticipant>(result.Rows.Count);
foreach (DataRow row in result.Rows)
{
ContestParticipant participant = new ContestParticipant();
participant.Username = (string)row[1];
if (participants.Contains(participant.Username))
{
participant.Points = new Money(Convert.ToDecimal(row[0]));
participant.LatestAction = DateTime.Now.Zero();
participant.ContestId = contest.Id;
list.Add(participant);
}
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
protected override object GetDataFromSource()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
string command = "SELECT COUNT(*) FROM Users WHERE RegisterDate BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)" +
" AND DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1)";
return(int.Parse(bridge.Instance.ExecuteRawCommandScalar(command).ToString()));
}
}
/// <exception cref="DbException" />
public static void DeleteRows <T>(Dictionary <string, object> where) where T : BaseTableObject
{
string tableName = GetTableName <T>();
using (var bridge = ParserPool.Acquire(Database.Client))
{
bridge.Instance.Delete(tableName, where);
}
}
protected override object GetDataFromSource()
{
using (var bridge = ParserPool.Acquire(Database.Client))
{
string command = "SELECT SUM(Balance3) FROM Users";
string tempresult = bridge.Instance.ExecuteRawCommandScalar(command).ToString();
return(Money.Parse(tempresult));
}
}
public static object SelectScalar(string query)
{
object returnObject;
using (var bridge = ParserPool.Acquire(Database.Client))
{
returnObject = (bridge.Instance.ExecuteRawCommandScalar(query));
}
return(returnObject);
}