public List <BLL.DTO.TransactionDto> GetCustomerTransactions(int CustomerId, DateTime ToDate, DateTime Fromdate, int isCust)
{
try
{
string sql = "select CustomerId,FromAccount, ToAccount, MerchantId, Amount from Transaction where CustomerId=@CustomerId and Status=@Status and CONVERT(DATE, CreatedOn) BETWEEN @FromDate AND @ToDate order by CreatedOn desc";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@CustomerId", CustomerId);
c.AddParam("@FromDate", Fromdate);
c.AddParam("@ToDate", ToDate);
c.AddParam("@Status", 1);
//for customer Status is 1
//for merchant Status is 0
var ds = c.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
var list = Helper.ConvertDataTable <BLL.DTO.TransactionDto>(ds.Tables[0]);
return(list);
}
else
{
// new ErrorLog("Get Customer error: " + ex.ToString());
List <BLL.DTO.TransactionDto> lt = new List <BLL.DTO.TransactionDto>();
return(lt);
}
}
catch (Exception ex)
{
new ErrorLog("Get trxn Customer error: " + ex.ToString());
List <BLL.DTO.TransactionDto> lt = new List <BLL.DTO.TransactionDto>();
return(lt);
//throw;
}
}
public Tuple <bool, List <CustomerCreation> > GetCustomer(string BVN)
{
string sql = "select * from Customer where BVN=@BVN and Status=@Status";
try
{
MSQconn con = new MSQconn(ConString);
con.SetSQL(sql);
con.AddParam("@BVN", BVN);
con.AddParam("@Status", 1);
DataSet ds = con.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//var list = ds.Tables[0].Select().ToArray();
var list = Helper.ConvertDataTable <CustomerCreation>(ds.Tables[0]);
return(Tuple.Create(true, list));
}
else
{
List <CustomerCreation> lt = new List <CustomerCreation>();
return(Tuple.Create(false, lt));
}
}
catch (Exception ex)
{
new ErrorLog("Get customer details error: " + ex.ToString());
List <CustomerCreation> lt = new List <CustomerCreation>();
return(Tuple.Create(false, lt));
}
}
public bool ValidateDuplicateEnrollment(MerchantCreation r)
{
try
{
MSQconn con = new MSQconn(ConString);
string sql = "Select * from Merchant where PhoneNumber=@PhoneNumber or SettlementAccount=@SettlementAccount or EmailAddress=@EmailAddress";
con.SetSQL(sql);
// Dbcon.AddParam("@bvn", r.bvn);
con.AddParam("@PhoneNumber", r.PhoneNumber);
con.AddParam("@SettlementAccount", r.SettlementAccount);
con.AddParam("@EmailAddress", r.EmailAddress);
// Dbcon.AddParam("@HandleUsername", r.HandleUsername);
DataSet ds = con.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//duplicate
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
new ErrorLog("ValidateDuplicateEnrollment error:" + ex.ToString());
return(false);
}
}
public Tuple <bool, List <MerchantCreation> > GetmerchantAcct(string PhoneNumber)
{
string sql = "select * from Merchant where Username=@Username and Status=@Status";
try
{
MSQconn con = new MSQconn(ConString);
con.SetSQL(sql);
con.AddParam("@PhoneNumber", PhoneNumber);
con.AddParam("@Status", 1);
DataSet ds = con.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//var list = ds.Tables[0].Select().ToArray();
var list = Helper.ConvertDataTable <MerchantCreation>(ds.Tables[0]);
return(Tuple.Create(true, list));
}
else
{
List <MerchantCreation> lt = new List <MerchantCreation>();
return(Tuple.Create(false, lt));
//DataRow[] lt =null;
//return Tuple.Create(true, lt);
}
}
catch (Exception ex)
{
new ErrorLog("Login merchant error: " + ex.ToString());
List <MerchantCreation> lt = new List <MerchantCreation>();
return(Tuple.Create(false, lt));
}
}
public int CreateMerchant(MerchantCreation merchant)
{
//validate duplicate
//insert merchant
try
{
bool duplicated = ValidateDuplicateEnrollment(merchant);
if (duplicated)
{
return(99); //duplicate exists code
}
MSQconn con = new MSQconn(ConString);
string sql = "insert into Merchant (Name,SettlementAccount,PhoneNumber,EmailAddress,Username,Password,CreatedOn,CreatedBy,Status) values (@Name,@SettlementAccount,@PhoneNumber,@EmailAddress,@Username,@Password,@CreatedOn,@CreatedBy,@Status)";
con.SetSQL(sql);
merchant.Password = Helper.Encrypt(merchant.Password);
con.AddParam("@Name", merchant.Name);
con.AddParam("@SettlementAccount", merchant.SettlementAccount);
con.AddParam("@PhoneNumber", merchant.PhoneNumber);
con.AddParam("@EmailAddress", merchant.EmailAddress);
con.AddParam("@Username", merchant.Username);
con.AddParam("@Password", merchant.Password);
con.AddParam("@CreatedOn", merchant.CreatedOn);
con.AddParam("@Status", merchant.Status);
var resp = con.Insert();
int r = Convert.ToInt16(resp);
return(r);
}
catch (Exception ex)
{
new ErrorLog("merchant creation error:" + ex.ToString());
return(99); //error occored
}
}
public OtpResponse GetOtp(string Otp)
{
OtpResponse response = new OtpResponse();
DataSet ds = null;
try
{
string sql = "select * FROM Otp WHERE GeneratedOtp=@Otp";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@Otp", Otp);
ds = c.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
response.Bvn = ds.Tables[0].Rows[0]["Bvn"].ToString();
response.Otp = ds.Tables[0].Rows[0]["GeneratedOtp"].ToString();
response.IsUsed = Convert.ToInt16(ds.Tables[0].Rows[0]["IsUsed"]);
}
}
catch (Exception ex)
{
return(response);
}
return(response);
}
public bool ValidateDuplicateEnrollment(string bvn)
{
try
{
MSQconn con = new MSQconn(ConString);
string sql = "Select * from Customer where BVN=@BVN";
con.SetSQL(sql);
con.AddParam("@BVN", bvn);
DataSet ds = con.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
//duplicate
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
new ErrorLog("ValidateDuplicateEnrollment error:" + ex.ToString());
return(false);
}
}
public int Insert(string bvn, string otp)
{
int result = 0;
try
{
string sql = "insert into Otp(Bvn,GeneratedOtp) values (@Bvn,@GeneratedOtp)";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@Bvn", bvn);
c.AddParam("@GeneratedOtp", otp);
result = Convert.ToInt32(c.Insert());
}
catch (Exception ex)
{
//new ErrorLog(ex.ToString());
//throw;
}
return(result);
}
public long ProfileStaff(int EmployerId, string BVN)
{
int result = 0;
try
{
MSQconn con = new MSQconn(ConString);
string sql = @"insert into Staff (EmployerId,BVN) values (@EmployerId,@BVN)";
con.SetSQL(sql);
con.AddParam("@EmployerId", EmployerId);
con.AddParam("@BVN", BVN);
var resp = con.Insert();
result = Convert.ToInt16(resp);
}
catch (Exception ex)
{
return(0);
}
return(result);
}
public long InsertIncident(int EmployerId, string BVN, string Type)
{
int result = 0;
try
{
MSQconn con = new MSQconn(ConString);
string sql = @"insert into Incident (EmployerId,BVN,Type) values (@EmployerId,@BVN,@Type)";
con.SetSQL(sql);
con.AddParam("@EmployerId", EmployerId);
con.AddParam("@BVN", BVN);
con.AddParam("@Type", Type);
var resp = con.Insert();
result = Convert.ToInt16(resp);
}
catch (Exception ex)
{
return(0);
}
return(result);
}
public long ProfileEmployer(string Name, string BVN, string Address, string Type)
{
int result = 0;
try
{
MSQconn con = new MSQconn(ConString);
string sql = @"insert into Employer (Name,BVN,Address,Type) values (@Name,@BVN,@Address,@Type)";
con.SetSQL(sql);
con.AddParam("@Name", Name);
con.AddParam("@BVN", BVN);
con.AddParam("@Address", Address);
con.AddParam("@Type", Type);
var resp = con.Insert();
result = Convert.ToInt16(resp);
}
catch (Exception ex)
{
return(0);
}
return(result);
}
public int Update(string Otp)
{
int result = 0;
try
{
string sql = "Update Otp SET IsUsed = 1 WHERE GeneratedOtp = @Otp";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@Otp", Otp);
var resp = c.Update();
result = Convert.ToInt32(resp);
}
catch (Exception ex)
{
}
return(result);
}
public bool SetPanicFinger(string PanicFingerPsoition, string BVN)
{
//update the panic finger
string sql = "update Customer set PanicFinger=@PanicFinger where BVN =@BVN and Status=1";
MSQconn con = new MSQconn(ConString);
con.SetSQL(sql);
con.AddParam("@BVN", BVN);
int resp = con.Update();
if (resp == 1)
{
return(true);
}
else
{
return(false);
}
}
public Tuple <bool, List <BvnSearchResp> > GetVoters(string bvn = "")
{
try
{
MSQconn con = new MSQconn(ConString);
string sql = "";
if (bvn == "")
{
sql = @"SELECT *,[BVN],[RegistrationDate],[FirstName],[MiddleName],[DateOfBirth],[Email],[Gender],[LgaOfOrigin],[LgaOfResidence],[ResidentialAddress],[StateOfOrigin],[StateOfResidence],[Base64Image] FROM InecReg";
}
else
{
sql = "select * from InecReg where BVN=@BVN order by desc ";
}
con.SetSQL(sql);
con.AddParam("@BVN", bvn);
// con.AddParam("@Status", 1);
DataSet ds = con.Select();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
var list = Helper.ConvertDataTable <BvnSearchResp>(ds.Tables[0]);
return(Tuple.Create(true, list));
}
else
{
List <BvnSearchResp> lt = new List <BvnSearchResp>();
return(Tuple.Create(false, lt));
}
}
catch (Exception ex)
{
new ErrorLog(ex);
List <BvnSearchResp> lt = new List <BvnSearchResp>();
return(Tuple.Create(false, lt));
}
}
public void LogTrxn(string CustomerId, string FromAccount, string ToAccount, int MerchantId, string Amount, string Status)
{
try
{
string sql = "insert into Transaction(CustomerId,FromAccount, ToAccount, MerchantId, Amount, Status) values (@CustomerId,@FromAccount, @ToAccount,@MerchantId, @Amount, @Status) ";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@CustomerId", CustomerId);
c.AddParam("@FromAccount", FromAccount);
c.AddParam("@ToAccount", ToAccount);
c.AddParam("@MerchantId", MerchantId);
c.AddParam("@Amount", Convert.ToDecimal(Amount));
c.AddParam("@Amount", Amount);
c.AddParam("@Status", Status);
var resp = c.Insert();
int respo = Convert.ToInt32(resp);
}
catch (Exception ex)
{
new ErrorLog(ex.ToString());
//throw;
}
}
public void LogInecRegistration(BvnSearchResp RegDetails)
{
try
{
string sql = "insert into InecReg (BVN,RegistrationDate, FirstName, MiddleName, DateOfBirth, Email,Gender,LgaOfOrigin,LgaOfResidence,ResidentialAddress,StateOfOrigin,StateOfResidence,Base64Image) values (@BVN,@RegistrationDate, @FirstName, @MiddleName, @DateOfBirth, @Email,@Gender,@LgaOfOrigin,@LgaOfResidence,@ResidentialAddress,@StateOfOrigin,@StateOfResidence,@Base64Image) ";
MSQconn c = new MSQconn(ConString);
c.SetSQL(sql);
c.AddParam("@BVN", RegDetails.BVN);
c.AddParam("@RegistrationDate", RegDetails.RegistrationDate);
c.AddParam("@FirstName", RegDetails.FirstName);
c.AddParam("@MiddleName", RegDetails.MiddleName);
c.AddParam("@DateOfBirth", RegDetails.DateOfBirth);
c.AddParam("@Email", RegDetails.Email);
c.AddParam("@Gender", RegDetails.Gender);
c.AddParam("@LgaOfOrigin", RegDetails.LgaOfOrigin);
c.AddParam("@LgaOfResidence", RegDetails.LgaOfResidence);
c.AddParam("@ResidentialAddress", RegDetails.ResidentialAddress);
c.AddParam("@StateOfOrigin", RegDetails.StateOfOrigin);
c.AddParam("@StateOfResidence", RegDetails.StateOfResidence);
c.AddParam("@Base64Image", RegDetails.Base64Image);
var resp = c.Insert();
int respo = Convert.ToInt32(resp);
}
catch (Exception ex)
{
new ErrorLog(ex.ToString());
//throw;
}
}
public Tuple <bool, List <CustomerCreation> > CreateCustomer(CustomerCreation Customer)
{
try
{
//var duplicate = ValidateDuplicateEnrollment(Customer.BVN);
//if (duplicate)
//{
// List<CustomerCreation> lt = new List<CustomerCreation>();
// return Tuple.Create(false, lt);
//}
MSQconn con = new MSQconn(ConString);
if (Customer.MaxAmount < 0)
{
Customer.MaxAmount = Convert.ToDecimal(ConfigurationManager.AppSettings["DefaultMaxAmount"]);
}
Customer.CreatedBy = "TeamDaze Admin";
string sql = @"insert into Customer (FirstName,LastName,BVN,PhoneNumber,EmailAddress,PanicFinger,CreatedBy,Status,
CreatedOn, EnrollmentType, CardType, CardToken) values (@FirstName,@LastName,@BVN,@PhoneNumber,@EmailAddress,@PanicFinger,@CreatedBy,@Status,
@CreatedOn, @EnrollmentType, @CardType, @CardToken)";
con.SetSQL(sql);
con.AddParam("@FirstName", Customer.FirstName);
con.AddParam("@LastName", Customer.LastName);
con.AddParam("@BVN", Customer.BVN);
con.AddParam("@PhoneNumber", Customer.PhoneNumber);
con.AddParam("@EmailAddress", Customer.EmailAddress);
con.AddParam("@PanicFinger", Customer.PanicFinger);
con.AddParam("@CreatedBy", Customer.CreatedBy);
con.AddParam("@MaxAmount", Customer.MaxAmount);
con.AddParam("@Status", 1);
con.AddParam("@CreatedOn", DateTime.Now);
con.AddParam("@EnrollmentType", Customer.EnrollmentType);
con.AddParam("@CardType", Customer.CardType);
con.AddParam("@CardToken", Customer.CardToken);
var resp = con.Insert();
int r = Convert.ToInt16(resp);
if (r == 1)
{
List <CustomerCreation> lt = new List <CustomerCreation>();
lt.Add(Customer);
return(Tuple.Create(true, lt));
}
else
{
List <CustomerCreation> lt = new List <CustomerCreation>();
return(Tuple.Create(false, lt));
}
}
catch (Exception ex)
{
new ErrorLog("Customer creation error:" + ex.ToString());
List <CustomerCreation> lt = new List <CustomerCreation>();
return(Tuple.Create(false, lt));
}
}