private void DeleteDoubleoptTransaction(int transactionID)
{
using (CDB cdb = new CDB("Delete double OPT transaction"))
{
cdb.Execute(string.Format("Delete_SubscriptionDoubleOptTransaction {0}", transactionID), CDB.exmReturnTypes.RETURN_NON_QUERY);
}
}
public SubscriptionService(int id)
{
CDB DB = new CDB("GetActiveServices");
SqlDataReader reader = (SqlDataReader)DB.Execute("SUB_GetActiveSubscriptionService " + id, CDB.exmReturnTypes.RETURN_READER);
if (reader.Read())
{
var service = new SubscriptionService(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), (BillingFrequencies)reader.GetInt32(3), (BillingTimes)reader.GetInt32(4), reader.GetDecimal(5), reader.GetInt32(6), reader.GetInt32(7), reader.GetString(8), (Statuses)reader.GetInt32(9));
_ID = service.ID;
_Description = service.Description;
_GroupID = service.GroupID;
_BillingFrequency = service.BillingFrequency;
_BillingTime = service.BillingTime;
_BillingAmount = service.BillingAmount;
_BillingDayOfMonth = service.BillingDayOfMonth;
_PaymentSystemClientID = service.PaymentSystemClientID;
_ResponseQueue = new CQueueWrapper(service.ResponseQueue.QueueName);
_Status = service.Status;
}
reader.Close();
reader = null;
DB.Close();
DB = null;
}
private void AddSubscriptionInteraction(int subscriptionID, int requestInputMechanismID, InteractionTypes interactionType)
{
CDB DB = new CDB("AddSubscriptionInteraction");
DB.Execute("SUB_AddSubscriptionInteraction " + subscriptionID.ToString() + "," + requestInputMechanismID.ToString() + "," + ((int)interactionType).ToString(), CDB.exmReturnTypes.RETURN_NON_QUERY);
DB.Close();
DB = null;
}
private void UpdateSubscriptionStatus(int subscriptionID, int statusID)
{
CDB DB = new CDB("UpdateSubscriptionStatus");
DB.Execute("SUB_UpdateSubscriptionStatus " + subscriptionID.ToString() + "," + statusID.ToString(), CDB.exmReturnTypes.RETURN_NON_QUERY);
DB.Close();
DB = null;
}
private void UpdateRebillingStatus(int billingRetryID, int retryCount, int statusID)
{
CDB cdb = new CDB("Update Subscription REbilling Status");
cdb.Execute("sub_UpdateBillingContinuesStatus " + billingRetryID + "," + statusID + "," + retryCount, CDB.exmReturnTypes.RETURN_NON_QUERY);
cdb.Close();
}
private void AddBillingRequest(int subscriptionID, int paymentSystemTransactionID, bool initialBilling, int exactPaymentTransactionId, int exactPaymentStatusID, decimal exactPaymentAmount)
{
CDB DB = new CDB("Add Subscription rebilling Request");
DB.Execute("SUB_AddBillingRequest " + subscriptionID.ToString() + "," + paymentSystemTransactionID.ToString() + "," + (initialBilling ? "1" : "0") + "," + exactPaymentTransactionId + "," + exactPaymentStatusID + "," + exactPaymentAmount, CDB.exmReturnTypes.RETURN_NON_QUERY);
DB.Close();
DB = null;
}
private void AddBillingRetry(int subscriptionID, bool initialBilling, decimal amount)
{
CDB DB = new CDB("AddBillingRetry");
DB.Execute("SUB_AddBillingRetry " + subscriptionID.ToString() + "," + (initialBilling ? "1" : "0") + "," + amount.ToString(), CDB.exmReturnTypes.RETURN_NON_QUERY);
DB.Close();
DB = null;
}
private void UpdateSubscriptionDateTimes(int subscriptionID, DateTime expiryDateTime, DateTime nextBillingDateTime, int trackingId)
{
CDB DB = new CDB(this.ToString());
DB.Execute("SUB_UpdateSubscriptionDateTimes " + subscriptionID.ToString() + ",'" + expiryDateTime.ToString("yyyy-MM-dd HH:mm:ss:fff") + "','" + nextBillingDateTime.ToString("yyyy-MM-dd HH:mm:ss:fff") + "'," + trackingId, CDB.exmReturnTypes.RETURN_NON_QUERY);
DB.Close();
DB = null;
}
private bool CanUnsubScribeLockedUser(int subscriptionID)
{
CDB cdb = new CDB("subscriptions");
bool unsubscribe = (bool)cdb.Execute("exm_CanUnsubLocked " + subscriptionID, CDB.exmReturnTypes.RETURN_SCALAR);
cdb.Close();
return(unsubscribe);
}
public bool IsSubscribed(string mobileNumber, int serviceID)
{
CDB cdb = new CDB("subscription");
bool isSub = (bool)cdb.Execute("SUB_IsSubscribed '" + mobileNumber + "'," + serviceID, CDB.exmReturnTypes.RETURN_SCALAR);
cdb.Close();
return(isSub);
}
private int AddMessageTransaction(int serviceID, int subscriptionID, string mobileNumber, int mobileNetworkID, string extraDigits, string destinationNumber, string message)
{
CDB DB = new CDB("subscriptions");
int transactionID = (int)DB.Execute("SUB_AddMessageTransaction " + (serviceID == 0 ? "null" : serviceID.ToString()) + "," + (subscriptionID == 0 ? "null" : subscriptionID.ToString()) + ",'" + mobileNumber + "'," + mobileNetworkID.ToString() + "," + (extraDigits == "" ? "null" : "'" + extraDigits + "'") + "," + (destinationNumber == "" ? "null" : "'" + destinationNumber + "'") + ",'" + message.Replace("'", "''") + "'", CDB.exmReturnTypes.RETURN_SCALAR);
DB.Close();
DB = null;
return(transactionID);
}
private bool CheckAndExtendBillingRetry(int billingRetryID)
{
CDB DB = new CDB("CheckAndExtendBillingRetry");
bool cancelled = (bool)DB.Execute("Sub_SubscriptionCheckAndExtend " + billingRetryID.ToString(), CDB.exmReturnTypes.RETURN_SCALAR);
DB.Close();
DB = null;
return(cancelled);
}
private Statuses GetSubscriptionStatus(int subscriptionID)
{
CDB DB = new CDB("GetSubscriptionStatus");
Statuses status = (Statuses)(int)DB.Execute("SUB_GetSubscriptionStatus " + subscriptionID.ToString(), CDB.exmReturnTypes.RETURN_SCALAR);
DB.Close();
DB = null;
return(status);
}
public static Hashtable GetActiveServices()
{
Hashtable hashtable = new Hashtable();
CDB DB = new CDB("GetActiveServices");
SqlDataReader reader = (SqlDataReader)DB.Execute("SUB_GetActiveSubscriptionServices", CDB.exmReturnTypes.RETURN_READER);
while (reader.Read())
{
SubscriptionService service = new SubscriptionService(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), (BillingFrequencies)reader.GetInt32(3), (BillingTimes)reader.GetInt32(4), reader.GetDecimal(5), reader.GetInt32(6), reader.GetInt32(7), reader.GetString(8), (Statuses)reader.GetInt32(9));
hashtable.Add(service.ID, service);
}
reader.Close();
reader = null;
DB.Close();
DB = null;
return(hashtable);
}
private int AddSubscription(int serviceID, string mobileNumber, int mobileNetworkID, int reference, string shortCode, string keyword)
{
CDB DB = new CDB("Typheous - ");
int subscriptionID = (int)DB.Execute(
"SUB_AddSubscription "
+ " @SubscriptionServiceID=" + serviceID.ToString()
+ ",@MobileNumber=" + mobileNumber
+ ",@MobileNetworkID=" + mobileNetworkID.ToString()
+ ",@StatusID=" + (int)Statuses.Pending
+ ",@Reference=" + reference.ToString()
// @MarketingIDRef does not get set here, but that is OK, as it gets defaulted to 0 in the stored procedure
+ ",@ShortCode=" + shortCode
+ ",@Keyword=" + keyword
, CDB.exmReturnTypes.RETURN_SCALAR
);
DB.Close();
DB = null;
return(subscriptionID);
}
private DateTime GetSubscriptionExpiryDateTime(int subscriptionID)
{
CDB DB = new CDB("GetSubscriptionExpiryDateTime");
DateTime dateTime = DateTime.MinValue;
object dt = DB.Execute("SUB_GetSubscriptionExpiryDateTime " + subscriptionID.ToString(), CDB.exmReturnTypes.RETURN_SCALAR);
if (dt != null)
{
if (dt != DBNull.Value)
{
dateTime = (DateTime)dt;
}
}
DB.Close();
DB = null;
return(dateTime);
}
public int InsertDoubleOptTRTransaction(int parentTransactionID, string mobileNumber, int subscriptionServiceID, DoubleOptStatus status, string exception, int?subscriptionID, InputMechanisms inputMechanism)
{
int transactionID = 0;
if (!string.IsNullOrEmpty(exception))
{
exception = "'" + exception.Replace("'", "''") + "'";
}
else
{
exception = "NULL";
}
using (CDB cdb = new CDB("Insert double OPT TR Transaction"))
{
string queryString = string.Format("Insert_trSubscriptionDoubleOpt {0},'{1}',{2},{3},{4},{5},{6}", parentTransactionID, mobileNumber, subscriptionServiceID, (int)status, exception, subscriptionID == null ? "NULL" : subscriptionID.ToString(), (int)inputMechanism);
transactionID = int.Parse(cdb.Execute(queryString, CDB.exmReturnTypes.RETURN_SCALAR).ToString());
}
return(transactionID);
}
private void SendServiceMessage(int serviceID, int subscriptionID, string mobilenumber, int mobileNetworkID, string extraDigits, string destinationNumber, ServiceMessages serviceMessage, string overRideMessage)
{
string messages = null;
if (overRideMessage == null)
{
CDB DB = new CDB("subscription");
messages = (string)DB.Execute("SUB_GetSubscriptionServiceMessage " + serviceID.ToString() + "," + ((int)serviceMessage).ToString(), CDB.exmReturnTypes.RETURN_SCALAR);
DB.Close();
DB = null;
}
else
{
messages = overRideMessage;
}
if (messages == null)
{
return;
}
if (messages.Length == 0)
{
return;
}
messages = messages.Trim();
ArrayList message = new ArrayList();
while (messages.Length > 0)
{
int index = messages.IndexOf("###");
if (index > 0)
{
string msg = messages.Substring(0, index).Trim();
if (msg.Length > 160)
{
msg = msg.Substring(0, 160);
}
message.Add(msg);
messages = messages.Substring(index + 3).Trim();
}
else
{
messages = messages.Trim();
if (messages.Length > 160)
{
messages = messages.Substring(0, 160);
}
message.Add(messages);
break;
}
}
foreach (string msg in message)
{
if (mobileNetworkID == 3 && msg == "Welcome to UNREAL! To access all the specials, go to www.exactmobile.mobi/unreal (on WAP) or dial *120*120#. Cost: R10/week (R7.50/week for Club members).")
{
SendMessage(serviceID, subscriptionID, mobilenumber, mobileNetworkID, extraDigits, destinationNumber, "Welcome to UNREAL! To access all the great specials, go to www.exactmobile.mobi/unreal by WAP on yr phone. Cost: R10/week (R7.50/week for Club members).");
}
else
{
SendMessage(serviceID, subscriptionID, mobilenumber, mobileNetworkID, extraDigits, destinationNumber, msg);
}
}
}
private DateTime CalculateAndUpdateSubscriptionDateTimes(int subscriptionID, SubscriptionService service)
{
DateTime expiryDateTime = DateTime.MinValue;
DateTime lastBillingDateTime = DateTime.MinValue;
DateTime nextBillingDateTime = DateTime.MinValue;
CDB DB = new CDB("Get Subscription Date - ");
SqlDataReader reader = (SqlDataReader)DB.Execute("SUB_GetSubscriptionDateTimes " + subscriptionID.ToString(), CDB.exmReturnTypes.RETURN_READER);
if (reader.Read())
{
expiryDateTime = reader.IsDBNull(0) ? DateTime.MinValue : reader.GetDateTime(0);
lastBillingDateTime = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
nextBillingDateTime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
}
reader.Close();
reader = null;
DB.Close();
DB = null;
if (expiryDateTime < DateTime.Now)
{
expiryDateTime = DateTime.Now;
}
switch (service.BillingFrequency)
{
case BillingFrequencies.Daily:
expiryDateTime = expiryDateTime.AddDays(1D);
break;
case BillingFrequencies.Weekly:
expiryDateTime = expiryDateTime.AddDays(7D);
break;
case BillingFrequencies.Monthly:
expiryDateTime = expiryDateTime.AddMonths(1);
break;
}
nextBillingDateTime = expiryDateTime;
switch (service.BillingFrequency)
{
case BillingFrequencies.Weekly:
case BillingFrequencies.Monthly:
nextBillingDateTime = expiryDateTime.AddHours(-48D);
break;
default:
nextBillingDateTime = expiryDateTime;
break;
}
// This has been added to set all future billings @ 1AM
nextBillingDateTime = new DateTime(nextBillingDateTime.Year, nextBillingDateTime.Month, nextBillingDateTime.Day, 00, 01, 01);
UpdateSubscriptionDateTimes(subscriptionID, expiryDateTime, nextBillingDateTime, trackId);
return(expiryDateTime);
}
