public override CreditHistoryList GetCreditHistoryUsed(long userId)
{
CreditHistoryList ret = new CreditHistoryList();
using (MySqlConnection conn = new MySqlConnection(GetConnectionString()))
{
using (MySqlCommand cmd = new MySqlCommand(QRY_GET_CREDITHISTORY_USED, conn))
{
cmd.Parameters.Add("@userid", MySqlDbType.Int64).Value = userId;
try
{
conn.Open();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
long creditHistoryId = rdr.GetInt64(0);
long newUserId = rdr.GetInt64(1);
long productId = rdr.GetInt64(2);
int credits = rdr.GetInt32(3);
DateTime purchaseDate = rdr.GetDateTime(4);
int expired = rdr.GetInt32(5);
long transactionId = rdr.GetInt64(6);
ret.Add(new CreditHistory(creditHistoryId, newUserId, productId, credits, purchaseDate, expired, transactionId));
}
}
}
catch (Exception ex)
{
Logger.Instance.Write(LogLevel.Error, ex, "GetCreditHistoryUsed<Exception>");
}
finally
{
conn.Close();
}
}
}
return ret;
}
public override List<CreditHistory> GetCreditHistory(long userId)
{
CreditHistoryList res = new CreditHistoryList();
CreditHistoryList ret = new CreditHistoryList();
CreditHistoryList added = GetCreditHistoryAdded(userId);
CreditHistoryList used = GetCreditHistoryUsed(userId);
// Now we have all the credits added and used for the specified user
foreach (CreditHistory ch in added)
{
bool found = true;
int credits = ch.Credits;
while (found)
{
if (used.Count > 0 && used[0].PurchaseDate < ch.PurchaseDate.AddYears(1))
{
credits -= used[0].Credits;
used.RemoveAt(0);
}
else
{
CreditHistory creditHistory = new CreditHistory(ch.CreditHistoryId,
ch.UserId,
ch.ProductId,
credits,
ch.PurchaseDate,
ch.Expired,
ch.TransactionId);
Transaction transaction = null;
using (MySqlConnection conn = new MySqlConnection(GetConnectionString()))
{
string QRY_GET_TRANSACTION_BY_USERID_AND_DATE = string.Empty;
if (creditHistory.TransactionId == 0)
QRY_GET_TRANSACTION_BY_USERID_AND_DATE = @"
SELECT * FROM `transaction`
WHERE `user_id`=@userid
AND `status`='OK'
AND (`date` BETWEEN SUBDATE(@transdate, interval 5 minute) AND ADDDATE(@transdate, interval 5 minute))
LIMIT 1
";
else
QRY_GET_TRANSACTION_BY_USERID_AND_DATE = @"
SELECT * FROM `transaction`
WHERE `user_id`=@userid
AND `status`='OK'
AND `transaction_id`=@transactionId
";
using (MySqlCommand cmd = new MySqlCommand(QRY_GET_TRANSACTION_BY_USERID_AND_DATE, conn))
{
cmd.Parameters.Add("@userid", MySqlDbType.Int64).Value = userId;
cmd.Parameters.Add("@transdate", MySqlDbType.DateTime).Value = creditHistory.PurchaseDate;
cmd.Parameters.Add("@transactionId", MySqlDbType.Int64).Value = creditHistory.TransactionId;
try
{
conn.Open();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
transaction = new Transaction();
transaction.TransactionId = rdr.GetInt64(0);
transaction.UserId = userId;
transaction.Amount = rdr.GetDecimal(2);
transaction.Date = rdr.GetDateTime(3);
transaction.Description = rdr.GetString(4);
transaction.ProductId = rdr.GetInt64(5);
transaction.Status = rdr.GetString(6);
transaction.StatusCode = rdr.GetString(7);
transaction.Merchant = rdr.GetString(8);
transaction.PaymentId = rdr.GetString(9);
transaction.TransactionReference = rdr.GetString(10);
transaction.PaymentMethod = rdr.GetString(11);
}
}
}
catch (Exception ex)
{
Logger.Instance.Write(LogLevel.Error, ex, "GetCreditHistory<exception>");
}
finally
{
conn.Close();
}
}
}
if (transaction != null)
{
creditHistory.Description = transaction.Description.Replace(".", ",") ?? "";
creditHistory.InvoiceFile = string.Format("INV{0}.pdf", transaction.PaymentId ?? "");
}
found = false;
ret.Add(creditHistory);
}
}
}
List<CreditHistory> TempRes = (from obj in ret
orderby obj.CreditHistoryId descending
select obj).ToList<CreditHistory>();
return TempRes;
}