<?php
/**
 * Created by PhpStorm.
 * User: Ludah
 * Date: 9/20/2017
 * Time: 10:40 PM
 */

/*
 * KYC Functions
 *
 */

function get_customer_daily_transaction($dbc,$customerID){
    $q = mysqli_query($dbc, "SELECT SUM(sendersAmount) AS total, SUM(fee) AS fee FROM maintransaction_tbl WHERE transactionStatus = 'COMPLETED' AND senderID = '$customerID' AND (DATE(dateTime) = DATE(NOW()))");
    $mData = mysqli_fetch_assoc($q);

    $qq = mysqli_query($dbc,"SELECT SUM(amount) AS total FROM airTimeTransaction_tbl WHERE senderID = '$customerID' AND (DATE(dateTime) = DATE(NOW()))");
    $aData = mysqli_fetch_assoc($qq);

    $qqq = mysqli_query($dbc,"SELECT SUM(senderAmount) AS total, SUM(fee) AS fee FROM bank_transactions_tbl WHERE (serviceType = 'WTB' AND senderID = '$customerID') AND (DATE(dateTime) = DATE(NOW()))");
    $bData = mysqli_fetch_assoc($qqq);

    $qqqq = mysqli_query($dbc,"SELECT SUM(amount) AS total FROM hub_transaction_tbl WHERE (transactionType = 'PUSH' AND customerID = '$customerID') AND (DATE(completed_date) = DATE(NOW())) AND status = 'COMPLETED'");
    $cData = mysqli_fetch_assoc($qqqq);


    $result = $mData['total'] + $aData['total'] + $bData['total'] + $bData['fee'] + $cData['total'] + $mData['fee'];

    return $result;
}

function get_customer_monthly_transaction($dbc,$customerID){
    $q = mysqli_query($dbc, "SELECT SUM(sendersAmount) AS total, SUM(fee) AS fee FROM maintransaction_tbl WHERE transactionStatus = 'COMPLETED' AND senderID = '$customerID' AND (MONTH(dateTime) = MONTH(NOW()))");
    $mData = mysqli_fetch_assoc($q);

    $qq = mysqli_query($dbc,"SELECT SUM(amount) AS total FROM airTimeTransaction_tbl WHERE senderID = '$customerID' AND (MONTH(dateTime) = MONTH(NOW()))");
    $aData = mysqli_fetch_assoc($qq);

    $qqq = mysqli_query($dbc,"SELECT SUM(senderAmount) AS total, SUM(fee) AS fee  FROM bank_transactions_tbl WHERE (serviceType = 'WTB' AND senderID = '$customerID') AND (MONTH(dateTime) = MONTH(NOW()))");
    $bData = mysqli_fetch_assoc($qqq);

    $qqqq = mysqli_query($dbc,"SELECT SUM(amount) AS total FROM hub_transaction_tbl WHERE (transactionType = 'PUSH' AND customerID = '$customerID') AND (MONTH(completed_date) = MONTH(NOW())) AND status = 'COMPLETED'");
    $cData = mysqli_fetch_assoc($qqqq);
    $result = $mData['total'] + $aData['total'] + $bData['total'] + $bData['fee'] + $mData['fee'] ;

    return $result;
}
/*
 * End of KYC Functions
 *
 */

function total_daily_main_transaction_by_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT sum(sendersAmount) as total FROM maintransaction_tbl WHERE senderCountryCode = '$countryCode' AND (DATE(dateTime) = DATE(NOW())) AND transactionStatus = 'COMPLETED'
	AND (transactionTypes = 'MT' OR transactionTypes = 'BP' OR transactionTypes = 'CO' OR transactionTypes = 'RP')");
    $data = mysqli_fetch_assoc($q);

    $q1 = mysqli_query($dbc,"SELECT sum(recipientAmount) as total FROM maintransaction_tbl WHERE recipientCountryCode = '$countryCode' AND senderCountryCode != '$countryCode' AND (DATE(dateTime) = DATE(NOW())) AND transactionStatus = 'COMPLETED'
	AND (transactionTypes = 'MT' OR transactionTypes = 'BP' OR transactionTypes = 'CO' OR transactionTypes = 'RP')");
    $data1 = mysqli_fetch_assoc($q1);

    if($data['total'] == ""){
        $data['total'] = "0.00";
    }
    if($data1['total'] == ""){
        $data1['total'] = "0.00";
    }
    $sum = $data['total'] + $data1['total'];
    return $sum;
}

function total_daily_airtime_transaction_by_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT sum(sendersAmount) as total FROM maintransaction_tbl WHERE transactionTypes = 'AIRTIME' AND senderCountryCode = '$countryCode' AND (DATE(dateTime) = DATE(NOW()))");
    $data = mysqli_fetch_assoc($q);
    if($data['total'] == ""){
        $data['total'] = "0.00";
    }
    return $data['total'];
}
function total_daily_bank_transaction_by_country($dbc){
    $q = mysqli_query($dbc,"SELECT sum(recipientAmount) as total FROM maintransaction_tbl WHERE  (DATE(dateTime) = DATE(NOW())) AND transactionStatus = 'COMPLETED' AND (transactionTypes = 'BTW' OR transactionTypes = 'BTB' OR transactionTypes = 'WTB')");
    $data = mysqli_fetch_assoc($q);
    if($data['total'] == ""){
        $data['total'] = "0.00";
    }
    return $data['total'];
}
function total_daily_commission_by_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT sum(amount) as total FROM payglobal_commission_tbl WHERE countryCode = '$countryCode' AND (DATE(dateTime) = DATE(NOW()))");
    $data = mysqli_fetch_assoc($q);
    if($data['total'] == ""){
        $data['total'] = "0.00";
    }
    return $data['total'];
}

function total_customers_by_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT count(id) AS total FROM customer_tbl WHERE countryCode = '$countryCode'");
    $data = mysqli_fetch_assoc($q);
    return $data['total'];
}

function total_main_transactions_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT SUM(sendersAmount) AS total FROM maintransaction_tbl WHERE transactionStatus = 'COMPLETED' AND senderCountryCode = '$countryCode'");
    $q1 = mysqli_query($dbc,"SELECT SUM(recipientAmount) AS total FROM maintransaction_tbl WHERE transactionStatus = 'COMPLETED' AND recipientCountryCode = '$countryCode' AND senderCountryCode != '$countryCode'");
    $data = mysqli_fetch_assoc($q);
    $data1 = mysqli_fetch_assoc($q1);
    $sum = $data['total'] + $data1['total'];
    return $sum;
}
function total_daily_bank_commission_by_country($dbc,$countryCode){
    $q = mysqli_query($dbc,"SELECT sum(fee) as total FROM bank_commission_tbl WHERE (DATE(dateTime) = DATE(NOW()))");
    $data = mysqli_fetch_assoc($q);
    if($data['total'] == ""){
        $data['total'] = "0.00";
    }
    return $data['total'];
}

