public ConsumerAuthEN GetAuthKey(string Token)
{
ConsumerAuthEN AuthKey = new ConsumerAuthEN();
try
{
connection.Cnn.Open();
AuthKey = connection.Cnn.Query <ConsumerAuthEN>(@"SELECT TOP 1 ConsumerAuthID, ConsumerID, ConsumerAuthKey, RegDate
FROM Consumer.ConsumerAuth WHERE ConsumerAuthKey = @consumerAuthKey",
new
{
consumerAuthKey = Token,
}).FirstOrDefault();
EventViewerLoggerDAL.LogError("GetAuthKey... ConsumerID: " + AuthKey.ConsumerID.ToString());
}
catch (Exception ex)
{
Console.WriteLine("Error GetAuthKey: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
AuthKey = null;
}
finally
{
connection.Cnn.Close();
}
return(AuthKey);
}
public List <ForbiddenNicknameEN> GetForbiddenNickname(string pForbidden)
{
List <ForbiddenNicknameEN> forbidden = new List <ForbiddenNicknameEN>();
pForbidden = pForbidden.Replace(@"-", string.Empty);
pForbidden = pForbidden.Replace(@"_", string.Empty);
pForbidden = pForbidden.Replace(@".", string.Empty);
pForbidden = pForbidden.Trim();
try
{
forbidden = cnn.Cnn.Query <ForbiddenNicknameEN>("SpGetForbiddenNickname", new { Nickname = pForbidden.Trim() },
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
forbidden = null;
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("GetForbiddenNickname: " + ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(forbidden);
}
/// <summary>
/// Inserts new authentication entry to logs (dbo.SessionsLog)
/// </summary>
/// <param name="pPersonID">Vendor ID</param>
/// <param name="pDeviceIP">External device's IP Address</param>
/// <param name="pDeviceInfo">Brand, model, OS version.</param>
/// <param name="pDeviceID">Device identification number.</param>
/// <returns>Returns inserted 'SessionsLog' row's ID.</returns>
public int InsertAuthenticationLog(int pPersonID, string pDeviceIP, string pDeviceInfo, string pDeviceID, bool pMobileDevice, DateTime pDate)
{
int sessionID = default(int);
Connection connection = new Connection();
try
{
SessionEN session = new SessionEN();
session.PersonID = pPersonID;
session.RegDate = pDate;
session.ActiveSession = true;
session.DeviceInfo = pDeviceInfo;
session.MobileDevice = pMobileDevice;
session.DeviceIP = pDeviceIP;
session.DeviceID = pDeviceID;
connection.Cnn.Open();
sessionID = connection.Cnn.Insert(session) ?? default(int);
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(sessionID);
}
public FacebookConsumerEN GetFacebookConsumer(string decodedProfileId)
{
FacebookConsumerEN ConsumerDecoded = new FacebookConsumerEN();
try
{
connection.Cnn.Open();
ConsumerDecoded = connection.Cnn.Query <FacebookConsumerEN>(@"select ConsumerID, Phone, CountryID, DeviceID, URL, Email, ProfileID, UserID, Firstname, MiddleName, Lastname, Nickname from [Consumer].[Consumer] where ProfileID = @profileID",
new
{
profileID = decodedProfileId
}).FirstOrDefault();
connection.Cnn.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error GetFacebookConsumer: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
ConsumerDecoded = null;
}
finally
{
connection.Cnn.Close();
}
return(ConsumerDecoded);
}
public bool MarkNotificationAsRead(int pNotificationID)
{
bool read = false;
int result = 0;
try
{
connection.Cnn.Open();
result = connection.Cnn.Execute("SpMarkNotificationAsRead", new { notifID = pNotificationID },
commandType: CommandType.StoredProcedure);
read = (result > 0) ? true : false;
}
catch (Exception ex)
{
Console.WriteLine("CampaignDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(read);
}
public string GenerateConsumerToken(int pConsumerID)
{
string consumerToken = "";
try
{
var parameters = new DynamicParameters();
parameters.Add("consumerid", pConsumerID);
parameters.Add("token", dbType: DbType.String, direction: ParameterDirection.Output, size: 10);
connection.Cnn.Query <String>("SP_Token_Phone_YCR", parameters, commandType: CommandType.StoredProcedure);
consumerToken = parameters.Get <String>("token");
}
catch (Exception ex)
{
consumerToken = "";
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("GenerateConsumerToken: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(consumerToken);
}
public ConsumerEN UpdateConsumerProfile(string pPhone, int pCountryID, string pDeviceID, string pURL, string pEmail, string pProfileID, string pUserID, string pFirstName, string pMiddleName, string pLastName)
{
ConsumerEN updateThisConsumer = new ConsumerEN();
try
{
updateThisConsumer = cnn.Cnn.Query <ConsumerEN>("SpUpdateConsumer",
new
{
Phone = pPhone,
CountryID = pCountryID,
DeviceID = pDeviceID,
URL = pURL,
Email = pEmail,
ProfileID = pProfileID,
UserID = pUserID,
Firstname = pFirstName,
MiddleName = pMiddleName,
Lastname = pLastName
}, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
updateThisConsumer = null;
Console.WriteLine("Error RegisterConsumerDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(updateThisConsumer);
}
public static IEnumerable <T> Query <T>(string sql, object param = null)
{
Constants constants = new Constants();
string connectionstring = constants.getConnectionString();
using (SqlConnection sconn = new SqlConnection(connectionstring))
{
SqlTransaction transaction;
sconn.Open();
transaction = sconn.BeginTransaction("RGTransaction");
try
{
var query = sconn.Query <T>(sql, param, transaction);
transaction.Commit();
sconn.Close();
return(query);
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine("Error Connection: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
return(null);
}
}
}
public ConsumerAuthKeyEN InsertConsumerAuth(ConsumerAuthKeyEN consumerAuth)
{
try
{
cnn.Cnn.Open();
ConsumerAuthKeyEN insertConsumerAuth = new ConsumerAuthKeyEN();
insertConsumerAuth.ConsumerAuthID = consumerAuth.ConsumerAuthID;
insertConsumerAuth.ConsumerID = consumerAuth.ConsumerID;
insertConsumerAuth.ConsumerAuthKey = consumerAuth.ConsumerAuthKey;
insertConsumerAuth.RegDate = consumerAuth.RegDate;
int?authKey = cnn.Cnn.Insert(insertConsumerAuth);
}
catch (Exception ex)
{
Console.WriteLine("RegisterConsumerDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(consumerAuth);
}
public int?AirTimeReporting(string pStoreName, string pAddressStore, decimal pLongitude, decimal pLatitude, string pFirebaseID, int pConsumerID, DateTime pRegDate, DateTime pModDate, ref string error)
{
//int result = 0;
StoreReportDetailsEN storeReport = new StoreReportDetailsEN();
int?resultInsert = default(int);
try
{
con.Cnn.Open();
//result = con.Cnn.Execute("SpInsertAirTimeReporting",
// new
// {
// StoreName = pStoreName,
// AddressStore = pAddressStore,
// Longitude = pLongitude,
// Latitude = pLatitude,
// FirebaseID = pFirebaseID,
// ConsumerID = pConsumerID
// },
// commandType: CommandType.StoredProcedure);
storeReport.StoreName = pStoreName;
storeReport.AddressStore = pAddressStore;
storeReport.Longitude = pLatitude;
storeReport.Latitude = pLatitude;
storeReport.FirebaseID = pFirebaseID;
storeReport.ConsumerID = pConsumerID;
storeReport.RegDate = DateTime.Now;
storeReport.ModDate = DateTime.Now;
resultInsert = con.Cnn.Insert(storeReport);
}
catch (Exception ex)
{
resultInsert = default(int);
error = ex.Message;
Console.WriteLine("Error StoreDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
con.Cnn.Close();
}
return(resultInsert);
}
public AuthConsumerEN GetConsumerAuthByToken(string pToken)
{
AuthConsumerEN authData = null;
try
{
cnn.Cnn.Open();
authData = cnn.Cnn.GetList <AuthConsumerEN>().Where(au => au.ConsumerAuthKey == pToken).FirstOrDefault();
}
catch (Exception ex)
{
authData = null;
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("GetConsumerByAuthToken: " + ex.Message);
}
return(authData);
}
public ConsumerTokenEN GetLastConsumerSmsToken(int pConsumerID)
{
ConsumerTokenEN consumerToken = null;
try
{
consumerToken = connection.Cnn.GetList <ConsumerTokenEN>().Where(f => f.ConsumerID == pConsumerID).LastOrDefault();
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("GetLastConsumerToken: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(consumerToken);
}
/// <summary>
/// Assigns the campaign ID (notification content) to each valid user.
/// </summary>
/// <param name="pISO2Code">User country's ISO2 code</param>
/// <param name="pCampaignID">Data base entry's ID of campaign</param>
/// <returns></returns>
public bool InsertUsersNotifications(string pISO2Code, int pCampaignID)
{
bool result = false;
SqlConnection sqlConnection = connection.Cnn;
int transactionResult = 0;
sqlConnection.Open();
using (var transaction = sqlConnection.BeginTransaction())
{
try
{
transactionResult = connection.Cnn.Execute("SpInsertUsersNotifications",
new
{
iso2code = pISO2Code,
campaignID = pCampaignID
},
transaction,
commandType: CommandType.StoredProcedure);
transaction.Commit();
result = (transactionResult > 0) ? true : false;
}
catch (Exception ex)
{
transactionResult = 0;
result = false;
transaction.Rollback();
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
sqlConnection.Close();
}
}
return(result);
}
public bool InsertPurchase(int pPersonID, int pBankID, decimal pAmount, int pCountryID, string pDepositor, string pBankReference, DateTime pDepositDate)
{
bool result = false;
try
{
connection.Cnn.Open();
using (SqlTransaction transaction = connection.Cnn.BeginTransaction())
{
var insertPurchase = connection.Cnn.Query("SpBankDeposit",
new
{
PersonID = pPersonID,
BankID = pBankID,
Amount = pAmount,
RegDate = DateTime.Now,
CountryID = pCountryID,
Name = pDepositor,
Number = pBankReference,
ImgReference = String.Empty,
DepositDate = pDepositDate
}, transaction, commandType: CommandType.StoredProcedure);
transaction.Commit();
result = true;
}
}
catch (Exception ex)
{
result = false;
Console.WriteLine(ex.Message);
EventViewerLoggerDAL.LogError("Error BankDepositDAL: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(result);
}
public int InsertExternalClient(ExternalClientEN pClient)
{
int clientID = default(int);
try
{
connection.Cnn.Open();
clientID = connection.Cnn.Insert(pClient) ?? default(int);
}
catch (Exception ex)
{
Console.WriteLine("ExternalClientDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(clientID);
}
public AuthConsumerEN GetAuthConsumer(int pConsumerID)
{
AuthConsumerEN getAuthConsumer = new AuthConsumerEN();
try
{
getAuthConsumer = cnn.Cnn.Query <AuthConsumerEN>("SpGetConsumerToken",
new { ConsumerID = pConsumerID }, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
Console.WriteLine("Error RegisterConsumerDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(getAuthConsumer);
}
public int UpdateSession(SessionEN pSession)
{
int updated = default(int);
try
{
connection.Cnn.Open();
updated = connection.Cnn.Update(pSession);
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("UpdateSession: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(updated);
}
public int UpdateToken(ConsumerTokenEN pNewToken)
{
int resultUpdate = default(int);
try
{
resultUpdate = connection.Cnn.Update(pNewToken);
}
catch (Exception ex)
{
resultUpdate = 0;
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("UpdateToken: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(resultUpdate);
}
public PersonEN VerifyPerson(PersonEN pPerson)
{
PersonEN person = new PersonEN();
try
{
connection.Cnn.Open();
person = connection.Cnn.Query <PersonEN>("SpVerifyVendorCredentials", new { email = pPerson.Email, passwordSalt = pPerson.Password },
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
Console.WriteLine("VerifyPerson : " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(person);
}
public string GetRocketDealerPin(int PersonID)
{
string DealerPin = "";
try
{
connection.Cnn.Open();
DealerPin = connection.Cnn.Query <string>("SpGetRocketDealerPin", new { personID = PersonID }, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
Console.WriteLine("Error SaleDAL: " + ex.Message);
EventViewerLoggerDAL.LogError("SpGetRocketDealerPin: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(DealerPin);
}
public List <BankEN> GetBanks(int pUserCountryID)
{
List <BankEN> banksList = new List <BankEN>();
try
{
banksList = cnn.Cnn.Query <BankEN>("SpGetBanks",
new { userCountryID = pUserCountryID }, commandType: CommandType.StoredProcedure).AsList();
}
catch (Exception ex)
{
Console.WriteLine("Error BankDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(banksList);
}
public SessionEN GetSessionByID(int pSessionID)
{
SessionEN session = new SessionEN();
try
{
connection.Cnn.Open();
session = connection.Cnn.GetList <SessionEN>().Where(s => s.SessionID == pSessionID).FirstOrDefault();
}
catch (Exception ex)
{
Console.WriteLine(ex.InnerException);
EventViewerLoggerDAL.LogError("GetSessionByID: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(session);
}
public ConsumerEN GetConsumerByProfileID(string pProfileID)
{
ConsumerEN getConsumer = new ConsumerEN();
try
{
getConsumer = cnn.Cnn.Query <ConsumerEN>("SpGetConsumer",
new { ProfileID = pProfileID }, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
Console.WriteLine("Error RegisterConsumerDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(getConsumer);
}
public List <OperatorEN> GetOperators(int pCountryID)
{
List <OperatorEN> operatorList = new List <OperatorEN>();
try
{
operatorList = cnn.Cnn.Query <OperatorEN>("SpGetOperators",
new { countryID = pCountryID }, commandType: CommandType.StoredProcedure).AsList();
}
catch (Exception ex)
{
Console.WriteLine("Error OperatorDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(operatorList);
}
public ConsumerEN InsertConsumer(string pPhone, int pCountryID, string pDeviceID, string pURL, string pEmail, string pProfileID, string pUserID, string pFirstName, string pMiddleName, string pLastName, string pNickName)
{
ConsumerEN registerConsumer = new ConsumerEN();
try
{
cnn.Cnn.Open();
int?resultInsert = default(int);
registerConsumer.Phone = pPhone;
registerConsumer.CountryID = pCountryID;
registerConsumer.Active = false;
registerConsumer.DeviceID = pDeviceID;
registerConsumer.RegistrationDate = DateTime.Now;
registerConsumer.ModificationDate = DateTime.Now;
registerConsumer.URL = pURL;
registerConsumer.Email = pEmail;
registerConsumer.ProfileID = pProfileID;
registerConsumer.UserID = pUserID;
registerConsumer.FirstName = pFirstName;
registerConsumer.MiddleName = pMiddleName;
registerConsumer.LastName = pLastName;
registerConsumer.Nickname = pNickName;
//Insertando en base de datos, si resultado exitoso, resultInsert = 1
resultInsert = cnn.Cnn.Insert(registerConsumer);
}
catch (Exception ex)
{
Console.WriteLine("RegisterConsumerDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
cnn.Cnn.Close();
}
return(registerConsumer);
}
public List <ProductEN> GetProductsByCountryID(int pCountryID)
{
List <ProductEN> productsList = new List <ProductEN>();
try
{
connection.Cnn.Open();
productsList = connection.Cnn.Query <ProductEN>("SpGetProductsByCountryID", new { countryID = pCountryID },
commandType: CommandType.StoredProcedure).AsList();
}
catch (Exception ex)
{
Console.WriteLine("ProductDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(productsList);
}
public int InsertFacebookProfile(FacebookEN pProfileData)
{
int inserted = default(int);
try
{
connection.Cnn.Open();
inserted = connection.Cnn.Insert(pProfileData) ?? default(int);
}
catch (Exception ex)
{
inserted = 0;
Console.WriteLine(ex.Message);
EventViewerLoggerDAL.LogError("InsertFacebookProfile: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(inserted);
}
public int UpdateFacebookProfile(FacebookEN pNewProfileData)
{
int updated = default(int);
try
{
connection.Cnn.Open();
updated = connection.Cnn.Update(pNewProfileData);
}
catch (Exception ex)
{
updated = 0;
Console.WriteLine(ex.Message);
EventViewerLoggerDAL.LogError("UpdateFacebookProfile: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(updated);
}
public FacebookEN GetFacebookProfile(int pPersonID)
{
FacebookEN profile = null;
try
{
connection.Cnn.Open();
profile = connection.Cnn.GetList <FacebookEN>().Where(f => f.PersonID == pPersonID).FirstOrDefault();
}
catch (Exception ex)
{
profile = null;
Console.WriteLine(ex.Message);
EventViewerLoggerDAL.LogError("GetFacebookProfile: " + ex.Message);
}
finally
{
connection.Cnn.Close();
}
return(profile);
}
public List <SouvenirEN> GetSouvenirsOwnedByConsumer(int consumerID, ref string error)
{
List <SouvenirEN> result = new List <SouvenirEN>();
try
{
con.Cnn.Open();
result = con.Cnn.Query <SouvenirEN>("SpGetSouvenirsOwnedByConsumer", new { ConsumerID = consumerID }, commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
Console.WriteLine("Error SouvenirDAL: " + ex.Message);
EventViewerLoggerDAL.LogError(ex.Message);
}
finally
{
con.Cnn.Close();
}
return(result);
}
