<?php
session_start();
require('conn_auth.php');
include 'db_query_script.php';

function clean($str) {
global $conn;
    return $conn->real_escape_string($str);
}


switch($_REQUEST['action']){
case "Check username":
$selUser = $conn->query("select * from user_table where username = '" . $_POST['username'] . "'") or die($conn->error);
if($selUser->num_rows>0){
echo 1;
}else{
echo 0;
}
break;
case "Login User":
//Include mail sending files
include 'send_mail_log.php';
$feedback = array();
$username = $_POST['username'];
$passwrd = $_POST['passwrd'];

$selPass = $conn->query("select * from user_table where username = '$username' && pass = '$passwrd'");
if($selPass->num_rows>0){
$rowD = $selPass->fetch_assoc();
$_SESSION['user_id'] = $rowD['user_id'];
$_SESSION['username'] = $rowD['username'];
$_SESSION['fullname'] = $rowD['fullname'];

if($rowD['access_lvl'] != 3){
//Send otp emails 
sendLoginOTP($rowD['email'], $rowD['username'], $rowD['fullname']);
}

//Assign values to variables. 
$feedback["logmsg"] = 1;
$feedback["user_id"] = $rowD['user_id'];
$feedback["access_lvl"] = $rowD['access_lvl'];
$feedback["accnt_nmbr"] = $rowD['username'];
}else{
$feedback["logmsg"] = 0;
$feedback["user_id"] = 0;
$feedback["access_lvl"] = 0;
}
echo json_encode($feedback);
break;
case "Start transfer":
$sel_credit = $conn->query("select *, sum(amt_cred) as totalCredit, sum(amt_dep) as totalDebit from credit_info where accnt_nmb = '" . $_POST['acct_nmb'] . "' group by accnt_nmb") or die($conn->error);
$rowBal = $sel_credit->fetch_array();
$bal = $rowBal['totalCredit'] - $rowBal['totalDebit'];
if($bal < $_POST['amt_trans']){
echo 0;
}else{
$insSql = $conn->query("insert into transfer_info (trans_id, acct_nmb, to_accnt, ben_name, ben_phn, bank_name, country_user, swift_code, sort_code, amt_trans, trans_stat, date_trans) values (NULL, '" . $_POST['acct_nmb'] . "', '" . $_POST['to_accnt'] . "', '" . $_POST['ben_name'] . "', '" . $_POST['ben_phn'] . "', '" . $_POST['bank_name'] . "', '" . $_POST['countryCode'] . "', '" . $_POST['swift_code'] . "', 'Nill', '" . $_POST['amt_trans'] . "', 'pending', '" . date("Y-m-d H:i:s") . "')") or die($conn->error);
if($insSql){
$lastCon = $conn->insert_id;
echo $lastCon;
}
}
break;

case "Start inter bank transfer":

include 'customInfo.php';
include 'outputfunction.php'; 

  $to_accnt = $_POST['to_accnt'];
  $ben_name = $_POST['ben_name']; 
  $amt_transfer = $_POST['amt_transfer']; 
  $acct_nmbr = $_POST['acct_nmbr'];
  $bank_name = $_POST['bank_name']; ; 
  $swift_code = $_POST['swift_code']; 

  $trans_ref = substr(md5(rand()*time()), 0, 10); 
  $feedback = array(); 
$mdata = array(
            "refern_id" => $trans_ref, 
            "acct_nmb" => $acct_nmbr, 
            "to_accnt" => $to_accnt, 
            "ben_name" => $ben_name, 
            "ben_email" => 'self',
            "ben_phn" => 'self', 
            "bank_name" => $bank_name, 
            "country_user" => 'self', 
            "swift_code" => $swift_code, 
            "sort_code" => 'Nill',
            "iban" => 'Nill', 
            "amt_trans" => $amt_transfer, 
            "trans_stat" => 'Pending', 
            "date_trans" => date("Y-m-d H:i:s")
            );
$insData = enterUserData("transfer_info", $mdata, 1, 0);
$lastId = $conn->insert_id;  

if($insData == 1){
  $mdata = array(
            "transn_ref" => $trans_ref, 
            "transferId" => $lastId, 
            "payee_name" => $ben_name, 
            "payee_accnt" => $to_accnt,
            "cust_name" => 'self',
            "accnt_nmb" => $acct_nmbr,
            "trans_type" => 'Interbank transfer',
            "narratn" => "Inter-bank funds transfer wth ref no: " . $trans_ref, 
            "amt_cred" => 0, 
            "amt_dep" => $amt_transfer, 
            "dat_pay" => date("Y-m-d H:i:s")
            );
$enterData = enterUserData("credit_info", $mdata, 1, 0);


$feedback["status"] = 1;
$feedback["transID"] = $lastId; 
$feedback["refNmbr"] =  $trans_ref; 
}else{
$feedback["status"] = 0;
}
echo json_encode($feedback); 
break;

case "International transfer complete":
 include 'customInfo.php';
include 'outputfunction.php'; 

        // Select from transfer info...
  $refnmbr = $_GET['refnmbr'];
  $transId  = $_GET['transfID'];

  $seltrans = $conn->query("select * from transfer_info where trans_id  = '$transId'") or die($conn->error);
  $rowTrans = $seltrans->fetch_assoc(); 

  $mdata = array(
            "transn_ref" => $refnmbr, 
            "transferId" => $transId, 
            "payee_name" => $rowTrans['ben_name'], 
            "payee_accnt" => $rowTrans['to_accnt'],
            "cust_name" => 'self',
            "accnt_nmb" => $rowTrans['acct_nmb'],
            "trans_type" => 'International funds transfer',
            "narratn" => "International bank funds transfer wth ref no: " . $refnmbr, 
            "amt_cred" => 0, 
            "amt_dep" => $rowTrans['amt_trans'], 
            "dat_pay" => date("Y-m-d H:i:s")
            );
$insData = enterUserData("credit_info", $mdata, 1, 0);
//Udate transfer as complete
$update = $conn->query("update transfer_info set trans_stat = 'Completed' where trans_id  = '$transId'") or die($conn->error); 
include 'send_mail_transfer.php';
header("location:transfer-history?id=" . $transId. "&msg=success"); 
break; 

  case "Interbank transfer complete":
  include 'customInfo.php';
  include 'outputfunction.php'; 

    // Select from transfer info...
  $refnmbr = $_GET['refnmbr'];
  $transId  = $_GET['transfID'];

  $seltrans = $conn->query("select * from transfer_info where trans_id  = '$transId'") or die($conn->error);
  $rowTrans = $seltrans->fetch_assoc(); 

//   $mdata = array(
//             "transn_ref" => $refnmbr, 
//             "transferId" => $transId, 
//             "payee_name" => $rowTrans['ben_name'], 
//             "payee_accnt" => $rowTrans['to_accnt'],
//             "cust_name" => 'self',
//             "accnt_nmb" => $rowTrans['acct_nmb'],
//             "trans_type" => 'Interbank transfer',
//             "narratn" => "Inter-bank funds transfer wth ref no: " . $refnmbr, 
//             "amt_cred" => 0, 
//             "amt_dep" => $rowTrans['amt_trans'], 
//             "dat_pay" => date("Y-m-d H:i:s")
//             );
// $insData = enterUserData("credit_info", $mdata, 1, 0);


//Udate transfer as complete
$update = $conn->query("update transfer_info set trans_stat = 'Completed' where trans_id  = '$transId'") or die($conn->error); 
include 'send_mail_transfer.php';
// header("location:transfer-history?id=" . $transId. "&msg=success");
header("location:transfer-reciept?id=" . $transId. "&msg=success"); 
break;

case "Validate transfer Pin":
include 'customInfo.php';
include 'outputfunction.php'; 

  $pin = $_POST['pin']; 
  $stage_tr = $_POST['stage_tr'];
  $transId = $_POST['transId']; 

  if($stage_tr == 1){
$stat = "Stage 1 complete";
  }else if($stage_tr == 2){
$stat = "Stage 2 complete";
  }else if($stage_tr == 3){
$stat = "Stage 3 complete";
  }else if($stage_tr == 4){
$stat = "Stage 4 complete";
  }
// Check if pin exists
  $pinExists =$conn->query("select * from transfer_pin where pin = '$pin' && stage_tr = '$stage_tr'") or die($conn->error);
 if($pinExists->num_rows){
 // Update pin
 $updatePin =  $conn->query("update transfer_info set trans_stat = '$stat' where trans_id  = '$transId'") or die($conn->error);
echo 1;
 }else{
echo 0;
 }
  break;

case "Validate login Pin":
  $pin  = $_POST['pin'];
  $macctn_nmbr = $_POST['macctn_nmbr'];

 $pinExists =$conn->query("select * from login_pin where pin = '$pin' && accnt = '$macctn_nmbr'") or die($conn->error);
 if($pinExists->num_rows){
echo 1;
 }else{
echo 0;
 }

  break;

case "Start international bank transfer":
include 'customInfo.php';
include 'outputfunction.php'; 

  $sender_acctn = $_POST['sender_acctn'];  
  $to_accnt = $_POST['to_accnt'];
  $ben_name = $_POST['ben_name'];
  $sel_country = $_POST['sel_country'];
  $accnt_type = $_POST['accnt_type'];
  $ben_phnmbr = $_POST['ben_phnmbr'];
  $ben_email  = $_POST['ben_email'];
  $bank_name = $_POST['bank_name'];
  $routing_nmbr = $_POST['routing_nmbr'];
  $amt_transfer= $_POST['amt_transfer'];
   $trans_ref = substr(md5(rand()*time()), 0, 10); 
  $feedback = array(); 

$mdata = array(
            "refern_id" => $trans_ref, 
            "acct_nmb" => $sender_acctn, 
            "to_accnt" => $to_accnt, 
            "ben_name" => $ben_name, 
            "ben_email" => $ben_email,
            "ben_phn" => $ben_phnmbr, 
            "bank_name" => $bank_name, 
            "country_user" => $sel_country, 
            "swift_code" => $routing_nmbr, 
            "sort_code" => $accnt_type,
            "amt_trans" => $amt_transfer, 
            "trans_stat" => 'Pending', 
            "date_trans" => date("Y-m-d H:i:s")
            );

$insData = enterUserData("transfer_info", $mdata, 1, 0);
$lastId = $conn->insert_id;  

if($insData == 1){
$feedback["status"] = 1;
$feedback["transID"] = $lastId; 
$feedback["refNmbr"] =  $trans_ref; 
}else{
$feedback["status"] = 0;
}
echo json_encode($feedback);
break;

case "Create account":
// include 'insert_transactn.php'; 
// include 'send_mail_log.php';
  
  $account_type = clean($_POST['account_type']);
  $prefix = clean($_POST['prefix']);
  $fname = clean($_POST['fname']);
  $lname = clean($_POST['lname']);
  $mname = clean($_POST['mname']);
  $phmbr = clean($_POST['phmbr']);
  $email = clean($_POST['email']);
  $addrs = clean($_POST['addrs']);
  $addrs2 = clean($_POST['addrs2']);
  $city = clean($_POST['city']);
  $state = clean($_POST['state']);
  $sel_country = clean($_POST['sel_country']); 
  $id_card_no = clean($_POST['id_card_no']);
  $id_card_type = clean($_POST['id_card_type']);
  $dob = clean($_POST['dob']); 
  $gndr = clean($_POST['gndr']);
  $natnal = clean($_POST['natnal']);
  $occupm = clean($_POST['occupm']);
  $income_source = clean($_POST['income_source']);
  $joint_account = $_POST['joint_account'];
  $account_name = clean($_POST['account_name']);
  $passport_name = $_FILES['passport']['name'];
  $passport_tmp_name = $_FILES['passport']['tmp_name'];
  $upload_url = "upload/". $passport_name;


   
   $fullname = $prefix . " " . $fname . " " . $mname . " " . $lname; 
   $passwrd = substr(md5(rand()*time()), 0, 7);

  // $dob = $_POST['yy_date'] . "-".  $_POST['mm_date'] . "-" . $_POST['dd_date'];    
  // $fullname  = clean($_POST['fname']);
  // $gnder = clean($_POST['gnder']);
  // $email  = clean($_POST['email']);
  // $phnmbr  = clean($_POST['phn_nmbr']);
  // $username = clean($_POST['username']);
  // $passwrd = clean($_POST['passwrd']);

$selExistEmail = selData("customer_info", "email", $email, "user_id");
if(!$selExistEmail->num_rows){
if(move_uploaded_file($passport_tmp_name, $upload_url)){
$mdata = array(
            "fullname" => $fullname, 
            "sexs" => $gndr,
            "addrs" => $addrs, 
            "email" => $email, 
            "phn_nmb" => $phmbr, 
            "username" => $email, 
            "pass" => $passwrd, 
            "access_lvl" => 1
            );
$insData = insertVal("user_table", $mdata, 1, 0);

$userId = $conn->insert_id;

$userData = array(
            "cust_name" => $fullname, 
            "user_id" => $userId,
            "email" => $email, 
            "phn_nmb" => $phmbr, 
            "addrs" => $addrs, 
            "addrs_2"=> $addrs2, 
            "day_birth" => $dob, 
            "country" => $sel_country,
            "id_card_no" => $id_card_no,  
            "id_card_type" => $id_card_type,
            "natnal" => $natnal,
            "occupm" => $occupm,   
            "income_source" => $income_source,
            "joint_account" => $joint_account,
            "account_name" => $account_name,
            "acct_nmb" => "", 
            "accnt_type" => $account_type,
            "cust_photo" => $passport_name,  
            "stat" => 'Pending', 
            "date_create" => date("Y-m-d H:i:s")
            );
$insData = insertVal("customer_info", $userData, 1, 0);
echo 1; 
}else{
echo -2;  
}
}else{
echo 0;
}
break;

case "Validate OTP code":
$userID = $_POST["userID"];
$otp_code = $_POST["otp_code"];
$response = array();

$fetchUser =$conn->query("select * from user_table where user_id = '$userID'") or die($conn->error);
$getUsers = $fetchUser->fetch_assoc();

$fetchCustomer = $conn->query("select * from customer_info where customer_id = '"  . $getUsers['cust_id'] . "'") or die($conn->error);
$rowCustmr = $fetchCustomer->fetch_assoc(); 

if($rowCustmr['tok_code'] == $otp_code){
$response['pagemsg'] = 1;
}else{
$response['pagemsg'] = 0;
}
echo json_encode($response);
break;

case "Edit user profile":
$ins = $conn->query("update customer_info set

 cust_name = '" . $_POST['cust_name'] . "', 
 email = '" . $_POST['email'] . "', 
 phn_nmb = '" . $_POST['phn_nmb'] . "', 
 addrs = '" . $_POST['addrs'] . "', 
 username = '" . $_POST['username'] . "', 
 pass = '" . $_POST['pass'] . "', 
 country = '" . $_POST['country'] . "',
 swift_code = '" . $_POST['swift_code'] . "', 
 zip_code = '" . $_POST['zip_code'] . "' where customer_id = '" . $_POST['cust_id']. "'") or die($conn->error);
header("location:edit-profile");
break;

case "Logout user":

session_unset();
session_destroy();
header("location:login");
break;

case "logout":
session_unset();
session_destroy();
header("location:home");
break;

case "Enter contact details":
$fname = $_POST['fname']; 
$email = $_POST['email']; 
$title = $_POST['title']; 
$msgbd = $_POST['msgbd']; 

$insSql = $conn->query("insert into feedback (fname, email, title, msg, stat, date_cr) values ('$fname', '$email', '$title', '$msgbd', 'Pending', '" . date("Y-m-d H:i:s") . "')") or die($conn->error); 

header("location:contact?rd=true"); 
break;

case "Enter feedback":
$fullname = $_POST['fullname']; 
$email = $_POST['email'];
$subj = $_POST['subj'];
$msg = $_POST['msg']; 
$ticket_id = substr(md5(rand()*time()), 0, 9); 


$insSql = $conn->query("insert into contact_us (ticket_id, name, email, title, msg, stat, date_cr) values ('$ticket_id', '$fullname', '$email', '$subj', 'msg', 'Pending', '" . date("Y-m-d H:i:s") . "')") or die($conn->error);   

header("location:open-ticket?msg=true&td=". $ticket_id);
break;

case "Confirm Code":
$selCode = $conn->query("select * from bill_code where code_n = '" . $_POST['code_id'] . "' && stage_name = 1") or die($conn->error);
if(!$selCode->num_rows){
echo 0;
}else{
//Update code confirmed 
 $updateAcc = $conn->query("update transfer_info set trans_stat = 'Stage 1 confirmed' where trans_id = '" . $_POST['trans_id'] . "'") or die($conn->error); 
 echo $_POST['trans_id']; 
}
break;
case "Confirm Tax":
$selCode = $conn->query("select * from bill_code where code_n = '" . $_POST['tax_nmb'] . "' && stage_name = 2") or die($conn->error);
if(!$selCode->num_rows){
echo 0;
}else{
$updateAcc = $conn->query("update transfer_info set trans_stat = 'Stage 2 confirmed' where trans_id = '" . $_POST['trans_id'] . "'") or die($conn->error);  
echo $_POST['trans_id']; 
}
break;
case "Confirm Clearance":
$selCode = $conn->query("select * from bill_code where code_n = '" . $_POST['clearance_nmb'] . "' && stage_name = 3") or die($conn->error);
if(!$selCode->num_rows){
echo 0;
}else{
$updateAcc = $conn->query("update transfer_info set trans_stat = 'Stage 3 confirmed' where trans_id = '" . $_POST['trans_id'] . "'") or die($conn->error);      
echo $_POST['trans_id']; 
}
break;
case "Upload profile": 
$size = $_FILES['profile_img']['size'];
$file = $_FILES['profile_img']['name'];
if(move_uploaded_file($_FILES['profile_img']['tmp_name'], "upload/". $file )){
$updateImg = $conn->query("update customer_info set 
cust_photo = '$file' where customer_id = '" . $_POST['customer_id'] . "'") or die($conn->error);
echo 1;
}else{
echo "Could not upload file";
}
break;
case "Change Password": 
$update = $conn->query("update user_table set 
pass = '" . $_POST['new_passwrd'] . "' where user_id = '" . $_SESSION['user_id'] . "'") or die($conn->error); 
session_unset(); 
session_destroy(); 
break;
case "create tables":
  $createtb = $conn->query("create table if not exists transfer_pin (
                                                               tr_pin_id int(11) NOT NULL auto_increment, 
                                                               pin varchar(200) NOT NULL default '', 
                                                               accnt varchar(200) NOT NULL default '', 
                                                               stat varchar(200) NOT NULL default '', 
                                                               date_cr datetime NOt NULL default current_timestamp, 
                                                                primary key (tr_pin_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);

  $createtb = $conn->query("create table if not exists myself_hep (
                                                               help_entry_id int(11) NOT NULL auto_increment, 
                                                               title varchar(200) NOT NULL default '', 
                                                               descr text NOT NULL default '', 
                                                               stat varchar(200) NOT NULL default '', 
                                                               date_cr datetime NOT NULL default current_timestamp, 
                                                                primary key (help_entry_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);

$createtb = $conn->query("create table if not exists contact_us (
                                                               contact_id int(11) NOT NULL auto_increment, 
                                                               name varchar(200) NOt NULL default '', 
                                                               email varchar(200) NOt NULL default '', 
                                                               title varchar(200) NOt NULL default '', 
                                                               msg text NOt NULL default '', 
                                                               stat varchar(200) NOt NULL default '', 
                                                               date_cr datetime NOt NULL default current_timestamp, 
                                                                primary key (contact_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);

$createtb = $conn->query("create table if not exists login_pin (
                                                               login_pin_id int(11) NOT NULL auto_increment, 
                                                                pin varchar(200) NOT NULL default '', 
                                                               accnt varchar(200) NOT NULL default '', 
                                                               stat varchar(200) NOT NULL default '', 
                                                               date_cr datetime NOt NULL default current_timestamp, 
                                                                primary key (login_pin_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);

 $createtb = $conn->query("create table if not exists user_notificatn (
                                                               notifcatn_id int(11) NOT NULL auto_increment, 
                                                               title varchar(200) NOT NULL default '', 
                                                               descr text NOT NULL default '', 
                                                               stat varchar(200) NOT NULL default '', 
                                                               date_cr datetime NOT NULL default current_timestamp, 
                                                               primary key (notifcatn_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);

 $createtb = $conn->query("create table if not exists feedback (
                                                               feedback_id int(11) NOT NULL auto_increment, 
                                                               fname varchar(200) NOT NULL default '',
                                                               email varchar(200) NOT NULL default '',
                                                               title varchar(200) NOT NULL default '',
                                                               msg text NOT NULL default '',
                                                               stat varchar(200) NOT NULL default '',
                                                               date_cr varchar(200) NOT NULL default '',
                                                               primary key (feedback_id))
                                                                Engine = MyISAM   
                                                                ") or die($conn->error);


  echo "Table created";
  break;

default: 
echo "No action has been requested.";
break;
}

?>