public static UndercoverResult Init(string clickID)
{
CCUndercoverAgent.Log(clickID, "CC_NEW:: Starting for : " + clickID);
UndercoverResult result = new UndercoverResult();
CCSubmitDirect db = CCSubmitDirect.Instance;
DirectContainer dc = db.LoadContainer(string.Format("SELECT affid, pubid, referrer FROM livesports.cc_client WHERE clickid='{0}' ORDER BY clientid DESC LIMIT 1;", clickID));
if (!dc.HasValue)
{
CCUndercoverAgent.Log(clickID, "CC_NEW:: There is no entries for clickID: " + clickID);
return(result);
}
string affID = !string.IsNullOrEmpty(dc.GetString("affid")) ? dc.GetString("affid") : GetParamByName(dc.GetString("referrer"), "affiliate_id");
string pubID = !string.IsNullOrEmpty(dc.GetString("pubid")) ? dc.GetString("pubid") : GetParamByName(dc.GetString("referrer"), "utm_campaign");
if (string.IsNullOrEmpty(affID))
{
CCUndercoverAgent.Log(clickID, "CC_NEW:: There is no AffID for clickID: " + clickID);
return(result);
}
DirectContainer _directContainer;
if (!string.IsNullOrEmpty(pubID))
{
_directContainer = db.LoadContainer("SELECT * FROM [].cc_undercover WHERE (aff_id={0} AND pub_id={1}) OR (aff_id={0} AND pub_id IS NULL) ORDER BY pub_id DESC LIMIT 1;", int.Parse(affID), pubID);
}
else
{
_directContainer = db.LoadContainer("SELECT * FROM [].cc_undercover WHERE (aff_id={0} AND pub_id IS NULL) ORDER BY pub_id DESC LIMIT 1;", int.Parse(affID));
}
if (_directContainer.HasValue && _directContainer.GetDouble("tcost").HasValue)
{
try
{
return(GetByVariableTCost(_directContainer, affID));
}
catch (Exception e)
{
Log(clickID, "[FATAL WITH tcost] " + e.ToString());
}
}
Log(clickID, "CCUndercover will go to old way, for clickID = " + clickID + ", affID=" + affID);
return(result);
}
public static bool CheckIfEmailBlacklist(string email, CCSubmitDirect db = null)
{
if (db == null)
{
db = CCSubmitDirect.Instance;
}
if (string.IsNullOrEmpty(email))
{
return(false);
}
if (EmailBlacklist == null)
{
DirectContainer dc = db.LoadContainer("SELECT email FROM livesports.ls_email_blacklist;");
if (dc == null || !dc.HasValue)
{
return(false);
}
EmailBlacklist = new Dictionary <string, int>();
foreach (var row in dc.Rows)
{
if (!EmailBlacklist.ContainsKey(row.GetString("email")))
{
EmailBlacklist.Add(row.GetString("email"), 1);
}
}
}
return(EmailBlacklist.ContainsKey(email));
}
static void Main(string[] args)
{
CCSubmitDirect db = CCSubmitDirect.Instance;
DirectContainer dc = db.LoadContainer("SELECT leadid, country, countryid FROM [].tm_lead order by leadid desc;");
int count = dc.RowsCount;
foreach (var row in dc.Rows)
{
string country = row.GetString("country");
if (string.IsNullOrEmpty(country) || row.GetInt("countryid").HasValue)
{
continue;
}
int?id = LeadManager.GetCountryID(country, db);
if (id.HasValue)
{
db.Transactional.Execute("UPDATE [].tm_lead SET countryid={0} WHERE leadid={1}", id, row.GetInt("leadid").Value);
}
if (db.Transactional.Count >= 500)
{
db.Transactional.Run();
}
}
int a = 0;
}
public static List <DynamicCsv> QueryLeadsByMsisdnAndEmail(List <DynamicCsv> csvList)
{
var csvListToUpdate = new List <DynamicCsv>();
var tableTypeString = "[].tm_lead";
string msisdnParams;
string emailParams;
var result = CsvListToEMailAndMsisdnParams(csvList);
msisdnParams = result.msisdns;
emailParams = result.emails;
//CCSubmitDirect db = CCSubmitDirect.Instance;
CCSubmitConnectionString.Type = CCSubmitConnectionStringType.LocalDV;
CCSubmitDirect db = CCSubmitDirect.Instance;
var query = "SELECT * FROM " + tableTypeString + " ";
if (msisdnParams != "" && emailParams != "")
{
query += "WHERE (msisdn in(" + msisdnParams + ") and (email = '' or email is null)) OR (email in(" + emailParams + ") and (msisdn = '' or msisdn is null))";
}
else if (msisdnParams != "" && emailParams == "")
{
query += "WHERE (msisdn in(" + msisdnParams + ") and (email = '' or email is null))";
}
else if (msisdnParams == "" && emailParams != "")
{
query += "WHERE (email in(" + emailParams + ") and (msisdn = '' or msisdn is null))";
}
else
{
return(new List <DynamicCsv>());
}
DirectContainer dc = db.LoadContainer(query);
if (dc.RowsCount > 0)
{
foreach (var row in dc.Rows)
{
var csvItem = csvList.FirstOrDefault(record => record.Msisdn == row.GetString("msisdn") || record.Email == row.GetString("email"));
if (csvItem != null)
{
csvItem.SetLeadId((int)row.GetInt("leadid"));
}
csvListToUpdate.Add(csvList.FirstOrDefault());
}
return(csvListToUpdate);
}
else
{
return(csvListToUpdate);
}
}
public static UndercoverResult Init(ActionDM action, PostbackDM postback)
{
postback.Log("CC_NEW:: Starting for : " + action.trackingid);
UndercoverResult result = new UndercoverResult();
CCSubmitDirect db = CCSubmitDirect.Instance;
string affID = action.affid;
string pubID = action.pubid;
if (string.IsNullOrEmpty(affID))
{
postback.Log("CC_NEW:: There is no AffID for clickID: " + action.trackingid);
return(result);
}
DirectContainer _directContainer;
if (!string.IsNullOrEmpty(pubID))
{
_directContainer = db.LoadContainer("SELECT * FROM [].cc_undercover WHERE (affid={0} AND pubid={1}) OR (affid={0} AND pubid IS NULL) ORDER BY pubid DESC LIMIT 1;", int.Parse(affID), pubID);
}
else
{
_directContainer = db.LoadContainer("SELECT * FROM [].cc_undercover WHERE (affid={0} AND pubid IS NULL) ORDER BY pubid DESC LIMIT 1;", affID);
}
if (_directContainer.HasValue && _directContainer.GetDouble("tcost").HasValue)
{
try
{
return(GetByVariableTCost(_directContainer, action, postback));
}
catch (Exception e)
{
postback.Log("[FATAL WITH tcost] " + e.ToString());
}
}
postback.Log("CCUndercover will go to old way, for clickID = " + action.trackingid + ", affID=" + affID);
return(result);
}
public LeadEntry(int leadID, CCSubmitDirect db = null)
{
this._id = leadID;
if (db == null)
{
this._database = CCSubmitDirect.Instance;
}
else
{
this._database = db;
}
this.Container = db.LoadContainer("SELECT * FROM [].tm_lead WHERE leadid=" + this._id);
}
public static List <DynamicCsv> QueryUnsubscribedUsersByEmail(PaymentProviderEnum subscribed, PaymentProviderEnum unsubscribed)
{
var emailList = new List <DynamicCsv>();
var tableName = "[].cc_client";
CCSubmitConnectionString.Type = CCSubmitConnectionStringType.LocalDV;
CCSubmitDirect db = CCSubmitDirect.Instance;
//ForTestDirect db = ForTestDirect.Instance;
var query = "SELECT * FROM " + tableName +
" WHERE payment_provider == " + (int)unsubscribed +
" AND times_charged = 0 " +
" AND email IN ( SELECT email FROM " + tableName +
" WHERE payment_provider = " + (int)subscribed +
" AND times_charged > 0 " +
" AND email != '' " +
" AND email IS NOT NULL);";
DirectContainer dc = db.LoadContainer(query);
if (dc.RowsCount > 0)
{
foreach (var row in dc.Rows)
{
var dynamicCsv = new DynamicCsv()
{
Email = row.GetString("email"),
FirstName = row.GetString("firstname"),
LastName = row.GetString("lastname"),
Country = row.GetString("country"),
Msisdn = row.GetString("msisdn"),
Address = row.GetString("address"),
City = row.GetString("city"),
Zip = row.GetString("zip")
};
emailList.Add(dynamicCsv);
}
return(emailList);
}
else
{
return(emailList);
}
}
public static List <string> QueryMsisdnsFromTm_Lead(string msisdnParams)
{
var tableTypeString = "[].tm_lead";
var listMsisdn = new List <string>();
CCSubmitDirect db = CCSubmitDirect.Instance;
DirectContainer dc = db.LoadContainer("SELECT msisdn FROM " + tableTypeString + " " +
"WHERE msisdn in (" + msisdnParams + ")");
foreach (var row in dc.Rows)
{
listMsisdn.Add(row.GetString("msisdn"));
}
return(listMsisdn);
}
public static void Update2(int actionid, string key, string value, DirectContainer cache = null, CCSubmitDirect db = null)
{
if (db == null)
{
db = CCSubmitDirect.Instance;
}
if (cache == null)
{
cache = db.LoadContainer("SELECT * FROM [].tm_lead_action WHERE actionid=" + actionid);
}
string new_value = (value.Equals("++") ? (cache.GetInt(key).Value + 1).ToString() : value);
db.Execute("INSERT INTO [].tm_action_history (actionid, name, old_value, new_value)", actionid, key, cache.GetString(key), new_value);
if (value.Equals("++"))
{
db.Execute("UPDATE [].tm_lead_action SET " + key + "=" + key + "+1 WHERE actionid={1}", value, actionid);
}
else
{
db.Execute("UPDATE [].tm_lead_action SET " + key + "={0} WHERE actionid={1}", value, actionid);
}
}
public DatabaseLoaders()
{
CCSubmitDirect db = CCSubmitDirect.Instance;
DirectContainer dc_countries = db.LoadContainer(
@"SELECT c.countryid, c.name, c.code FROM livesports.tm_country_used AS u
LEFT OUTER JOIN livesports.tm_country AS c ON u.countryid=c.countryid;");
foreach (var c in dc_countries.Rows)
{
Countries.Add(new CountryModel()
{
ID = c.GetInt("countryid").Value,
Name = c.GetString("name")
});
}
DirectContainer dc_lander = db.LoadContainer(@"SELECT * FROM livesports.tm_lander;");
foreach (var c in dc_lander.Rows)
{
Landers.Add(new LanderModel()
{
ID = c.GetInt("landerid").Value,
Name = c.GetString("name")
});
}
DirectContainer dc_prelanders = db.LoadContainer(@"SELECT * FROM livesports.tm_prelander;");
foreach (var c in dc_prelanders.Rows)
{
Prelanders.Add(new PrelanderModel()
{
ID = c.GetInt("prelanderid").Value,
Name = c.GetString("name")
});
}
DirectContainer dc_prelandertype = db.LoadContainer(@"SELECT * FROM livesports.tm_prelandertype;");
foreach (var c in dc_prelandertype.Rows)
{
PrelanderTypes.Add(new PrelanderTypeModel()
{
ID = c.GetInt("prelandertypeid").Value,
Name = c.GetString("name")
});
}
DirectContainer dc_services = db.LoadContainer(@"SELECT * FROM livesports.tm_service;");
foreach (var c in dc_services.Rows)
{
Services.Add(new ServiceModel()
{
ID = c.GetInt("serviceid").Value,
Name = c.GetString("name")
});
}
}
public static int?GetCountryID(string country, CCSubmitDirect db = null)
{
if (string.IsNullOrEmpty(country))
{
return(null);
}
if (db == null)
{
db = CCSubmitDirect.Instance;
}
lock (LOCK_OBJ)
{
if (country_map == null)
{
country_map = new Dictionary <string, int>();
DirectContainer dc = db.LoadContainer(@"SELECT c.countryid, c.name, c.code FROM livesports.tm_country_used AS u
LEFT OUTER JOIN livesports.tm_country AS c ON u.countryid=c.countryid");
foreach (var r in dc.Rows)
{
if (!country_map.ContainsKey(r.GetString("name").ToLower()))
{
country_map.Add(r.GetString("name").ToLower(), r.GetInt("countryid").Value);
}
if (!country_map.ContainsKey(r.GetString("code").ToLower()))
{
country_map.Add(r.GetString("code").ToLower(), r.GetInt("countryid").Value);
}
}
}
}
if (country_map.ContainsKey(country.ToLower()))
{
return(country_map[country.ToLower()]);
}
lock (LOCK_OBJ)
{
DirectContainer dcc = db.LoadContainer(string.Format(@"SELECT countryid, name, code FROM livesports.tm_country WHERE name LIKE '%{0}%' OR code='{1}'", country.ToLower(), country.ToLower()));
if (!dcc.HasValue)
{
return(null);
}
if (db.LoadInt("SELECT COUNT(*) FROM [].tm_country_used WHERE countryid={0}", dcc.GetInt("countryid").Value).Value == 0)
{
db.Execute("INSERT INTO [].tm_country_used (countryid)", dcc.GetInt("countryid").Value);
}
if (!country_map.ContainsKey(dcc.GetString("name").ToLower()))
{
country_map.Add(dcc.GetString("name").ToLower(), dcc.GetInt("countryid").Value);
}
if (!country_map.ContainsKey(dcc.GetString("code").ToLower()))
{
country_map.Add(dcc.GetString("code").ToLower(), dcc.GetInt("countryid").Value);
}
return(dcc.GetInt("countryid").Value);
}
}