<?php
ini_set('display_errors', 1);


// process of entry
# Condition 1 : Insert
// Validate input
// Input fields are : transactiontype, narration, date, entry_by, debitrecords (arrray), creditrecords (Array), filesattached (array), send_msg / mail (bool)
// Validate the fields, based on the transactiontype fetch the counter of such transactiontype from transactioncounter, 
// Create uniqueid with it
// Insert debitrecords (array), creditrecords(Array) into transactionrecord
// update the ledgerdetails (ledgers involved in the transaction with race condition)
// echo success


//require_once '../../../resources/base_path_include.php';
//require_once $base_path . 'modules/accounting/functions/basic_balance_function.php';
// this one is for one ledger search



$globals_ledger_ids_already_fetched = [];
$globals_ledger_names_already_fetched = [];

function getLedgerName($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $ledgerid)
{

    if (in_array($ledgerid, $GLOBALS['globals_ledger_ids_already_fetched'])) {
        return $GLOBALS['globals_ledger_names_already_fetched'][array_search($ledgerid, $GLOBALS['globals_ledger_ids_already_fetched']) . ''];
    } else {
        $ledgername = GetBasicLedgerDetail($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, ['ledger_name'])[0]['ledger_name'];
        $GLOBALS['globals_ledger_ids_already_fetched'][] = $ledgerid;
        $GLOBALS['globals_ledger_names_already_fetched'][] = $ledgername;
        return $ledgername;
    }
}

function GetBasicLedgerDetail($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $additionaldata)
{
    $sql = "Select ";
    $array_counter = 0;
    foreach ($additionaldata as $column_name) {
        if ($array_counter == 0) {
            $sql = $sql . " " . $column_name;
        } else {
            $sql = $sql . ", " . $column_name;
        }
        $array_counter++;
    }

    $sql = $sql . " FROM  act_ledgerdetails WHERE id = ? and  workspace_id = ? and company_id =? and branch_id =? and book_id  =?";

    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id));
    $result = $stmt->fetchAll();

    if (count($result) == 1) {
        $array_return = [];

        foreach ($additionaldata as $column_name) {
            $array_return[] = array('' . $column_name . '' => $result[0]['' . $column_name]);
        }

        return $array_return;
    } else {
        errorreturn("Ledgerid is not exists in ledgerdetail");
    }
}

function fetchLedgerOpeningBalanceSpecificDate($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $ledger_opening_balance, $dateselected, $book_opening_date)
{
    // if opening_bal is received null it is calculated here
    if ($ledger_opening_balance == null) {
        $ledger_opening_balance = GetBasicLedgerDetail($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, ['ledger_opening_bal'])[0]['ledger_opening_bal'];
        $book_opening_balance = $ledger_opening_balance;
    }

    // should also get the book openingdate and the dateselected variable
    // get openingbalance from getLedgerBasicDetail from parent file do not call it in here.


    // first check id book opening date == dateselected , return the same openingbalance receieved in paramters,
    // else sum all the transactions before the selected date grouped by balancetype
    // openingbalance + sum of dr (amount) - sum of cr (abs(amount))
    // errorreturn($book_opening_date."    ".$book_opening_date);
    if ($book_opening_date == $dateselected) {
        $ledger_opening_balance  = $ledger_opening_balance;
    } else {

        $sql = "Select closing_balance from act_ledgerbalances where ledgerid = ? and workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and date < ? order by date desc limit 1";
        $stmt =  $specific_db_conn->prepare($sql);
        $stmt->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $dateselected));
        $stmt = $stmt->fetchAll();
        $ledger_opening_balance = floatval($stmt[0]['closing_balance']);

        $sql = "Select COALESCE(sum(amount), 0) as total from act_transactionrecord where ledgerid = ? and balancetype = ?";
        $sql = $specific_db_conn->prepare($sql);
        $sql->execute(array($ledgerid, 'dr'));
        $debit_amount = $sql->fetchAll()[0]['total'];
        $sql->execute(array($ledgerid, 'cr'));
        $credit_amount = $sql->fetchAll()[0]['total'];

        // errorreturn($ledger_opening_balance."       ".($debit_amount - $credit_amount + $book_opening_balance));
    }
    return $ledger_opening_balance;
}

function fetchLedgerClosingBalanceSpecificDate($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $dateselected)
{

    $sql = "Select closing_balance from act_ledgerbalances where ledgerid = ? and workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and date <= ? order by date desc limit 1";
    $stmt =  $specific_db_conn->prepare($sql);
    $stmt->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $dateselected));
    $stmt = $stmt->fetchAll();
    $ledger_closing_balance = floatval($stmt[0]['closing_balance']);

    return ($ledger_closing_balance);
}


function GetBasicCurrencyFormalName($specific_db_conn, $workspace_id, $company_id, $branch_id, $additional_data)
{

    $sql = "Select ";
    $array_counter = 0;
    foreach ($additional_data as $column_name) {
        if ($array_counter == 0) {
            $sql = $sql . " " . $column_name;
        } else {
            $sql = $sql . ", " . $column_name;
        }
        $array_counter++;
    }


    $sql = $sql . " FROM  branch_info WHERE id = ? and  workspace_id = ? and company_id =?";
    //echo $sql;die();
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($branch_id, $workspace_id, $company_id));
    $result = $stmt->fetchAll();

    if (count($result) == 1) {
        $array_return = [];

        foreach ($additional_data as $column_name) {
            $array_return[] = array('' . $column_name . '' => $result[0]['' . $column_name]);
        }

        return $array_return;
        //print_r($array_return);die();
    } else {
        errorreturn("BookId is not exists in Branch_info");
    }
}


function getLedger($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $search_string, $limit, $offset, $additionaldata)
{
    $sql = "Select ";
    $array_counter = 0;
    foreach ($additionaldata as $column_name) {
        if ($array_counter == 0) {
            $sql = $sql . " " . $column_name;
        } else {
            $sql = $sql . ", " . $column_name;
        }
        $array_counter++;
    }

    $sql_count = $sql . " FROM  act_ledgerdetails WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =? and 
    (ledger_name LIKE ? or ledger_pan LIKE ? or mobile LIKE  ?    or email LIKE ?)";

    $stmt_count = $specific_db_conn->prepare($sql_count);
    $stmt_count->execute(array($workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%',  '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%'));
    $result_count = $stmt_count->fetchAll();
    $totalrows = count($result_count);

    if ($limit == 'all') {

        $sql = $sql . " FROM  act_ledgerdetails WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =?  and 
    (ledger_name LIKE ? or ledger_pan LIKE ? or mobile LIKE  ? or email LIKE ? or ledger_main_ledgerhead LIKE ?)  ORDER BY id ASC  ";

        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%'));
        $result = $stmt->fetchAll();
    } else {


        $sql = $sql . " FROM  act_ledgerdetails WHERE workspace_id = ? and company_id =? and branch_id =? and book_id  =?  and 
        (ledger_name LIKE ? or ledger_pan LIKE ? or mobile LIKE  ? or email LIKE ?  or ledger_main_ledgerhead LIKE ?)  ORDER BY id ASC    LIMIT " . $offset . " ," . $limit . "";

        $stmt = $specific_db_conn->prepare($sql);
        $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%', '%' . $search_string . '%'));
        $result = $stmt->fetchAll();
    }

    $array_return = [];
    foreach ($result as $res) {
        $fieldarray = [];
        foreach ($additionaldata as $column_name) {
            if (in_array($column_name, array('ledger_opening_bal', 'ledger_current_bal')))
                $fieldarray[$column_name] = $res[$column_name];
            else
                $fieldarray[$column_name] = $res[$column_name];
        }
        array_push($array_return, $fieldarray);
    }

    if (count($result) < 50) {
        return array('total_rows' => $totalrows, 'ledger_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'ledger_info' => $array_return, 'maxreach' => 0);
    }
}


function getSpecificLedger($conn, $specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id, $addresses, $unformatted_bal = false)
{
    // reeturns all data from the specific row of the profile
    $sql = $specific_db_conn->prepare("SELECT * FROM  act_ledgerdetails  where id = ? and workspace_id =? and  company_id =? and branch_id =? and book_id = ?");
    $sql->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id));
    $rows = $sql->fetchAll();
    $ledger_addresses = [];
    if (count($rows) == 1) {
        $ledger_opening_bal = $rows[0]['ledger_opening_bal'];

        if ($addresses == 1) {



            $sql = $specific_db_conn->prepare("SELECT * FROM  act_ledger_addresses  where ledgerid = ? and workspace_id =? and  company_id =? and branch_id =? and book_id = ?");
            $sql->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id));
            $result_addreses = $sql->fetchAll();
            foreach ($result_addreses as $rows_addresses) {

                $country = $rows_addresses['country_id'];
                $state = $rows_addresses['state_id'];

                $sql = $conn->prepare("SELECT * FROM  country_info  where id = ?");
                $sql->execute(array($country));
                $rows_country = $sql->fetchAll();
                if (count($rows_country) == 1) {
                    $country_name = $rows_country[0]['name'];
                    $country_info = array('id' => $country, 'label' => $country_name);
                }
                $sql = $conn->prepare("SELECT * FROM  state_info  where id = ? and country_id =? ");
                $sql->execute(array($state, $country));
                $rows_state = $sql->fetchAll();
                if (count($rows_state) == 1) {
                    $state_name = $rows_state[0]['state'];
                    $state_info = array('id' => $state, 'label' => $state_name);
                }

                $ledger_addresses[] =   array(
                    "id" => $rows_addresses['id'], "name" => $rows_addresses['name'], "address_ln_1" => $rows_addresses['address_ln_1'], "address_ln_2" => $rows_addresses['address_ln_2'],
                    "state_id" => $state_info, "country_id" => $country_info, "postal_code" => $rows_addresses['postal_code'], "ledger_gst" => $rows_addresses['ledger_gst'], "mobile" => $rows_addresses['mobile'], "email" => $rows_addresses['email'],
                    "pincode" => $rows_addresses['pincode']
                );
            }

            return array(
                'ledger_id' => $rows[0]['id'], 'ledger_name' => $rows[0]['ledger_name'],
                'ledger_pan' => $rows[0]['ledger_pan'], 'ledger_gst' => $rows[0]['ledger_gst'], 'mobile' => $rows[0]['mobile'], 'email' => $rows[0]['email'],
                'balancetype' => getBalanceType($rows[0]['ledger_opening_bal']),

                'ledger_opening_bal' => $ledger_opening_bal,  'ledger_parent_group' => $rows[0]['ledger_parent_group'],
                'ledger_main_ledgerhead' => $rows[0]['ledger_main_ledgerhead'], 'param_1' => $rows[0]['param_1'], 'param_2' => $rows[0]['param_2'],'pincode' => $rows[0]['pincode'], 'ledger_addresses' => $ledger_addresses
            );
        } else {


            return array(
                'ledger_id' => $rows[0]['id'], 'ledger_name' => $rows[0]['ledger_name'],
                'ledger_pan' => $rows[0]['ledger_pan'], 'ledger_gst' => $rows[0]['ledger_gst'], 'mobile' => $rows[0]['mobile'], 'email' => $rows[0]['email'],
                'balancetype' => getBalanceType($rows[0]['ledger_opening_bal']),

                'ledger_opening_bal' => $ledger_opening_bal,  'ledger_parent_group' => $rows[0]['ledger_parent_group'], 'ledger_main_ledgerhead' => $rows[0]['ledger_main_ledgerhead'], 'param_1' => $rows[0]['param_1'], 'param_2' => $rows[0]['param_2'],
                'pincode' => $rows[0]['pincode']
            );
        }
    } else {
        errorreturn("Enter Invalid Ledger Details");
    }
}


function  InsertLedgerAddress($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $addresses) // function used to Insert / Update / Delet Ledger associated address (Create / Alter Ledger api)
{

    foreach ($addresses as $data) {

        $id = (isset($data['id']) and !empty($data['id'])) ? $data['id'] : null;
        $name = ucwords(trim($data['name'])); // should be ucwords mandatory
        $address_ln_1 = ucwords(trim($data['address_ln_1'])); // ucwords and mandatory
        $address_ln_2 = (isset($data['address_ln_2']) and !empty($data['address_ln_2'])) ? ucwords(trim($data['address_ln_2'])) : null;  // ucwords NOT mandatory can be null
        $state_id = $data['state_id']; // mandatory also to be validated from the list we have
        $country_id = $data['country_id']; // mandatory also to be validated from the list we have
        $postal_code = (isset($data['postal_code']) and !empty($data['postal_code'])) ? $data['postal_code'] : null;
        $ledger_gst = (isset($data['ledger_gst']) and !empty($data['ledger_gst'])) ? strtoupper($data['ledger_gst']) : null; // NOT MANDATORY to be validated if received from regex of gst
        $email = (isset($data['email']) and !empty($data['email'])) ? $data['email'] : null;
        $mobile = (isset($data['mobile']) and !empty($data['mobile'])) ? $data['mobile'] : null;
        $pincode = (isset($data['pincode']) and !empty($data['pincode'])) ? $data['pincode'] : null;

        // if ($id == null or (isset($data['function']) and $data['function'] == 'update')) { //validate country id
        //     $sql_country = "SELECT * FROM country_info WHERE id = ?";
        //     $stmt_country = $conn->prepare($sql_country);
        //     $stmt_country->execute(array($country_id));
        //     $result_country = $stmt_country->fetchAll();
        //     if (count($result_country) < 1) {
        //         errorreturn("Country Info Not Found here.");
        //     }
        //     //validate country id
        //     $sql_state = "SELECT * FROM state_info WHERE id = ? and country_id =?";
        //     $stmt_state = $conn->prepare($sql_state);
        //     $stmt_state->execute(array($state_id, $country_id));
        //     $result_state = $stmt_state->fetchAll();
        //     if (count($result_state) < 1) {
        //         errorreturn("State Info Not Found.");
        //     }

        //     if ($email != null) {
        //         if (!CheckEMailIsValidOrNot($email)) {
        //             errorreturn('Enter valid Email Id');
        //         }
        //     }

        //     if ($ledger_gst != null) {

        //         $mobile = $data['mobile'];

        //         if (strlen($mobile) != 13 or $mobile <= 0) {
        //             errorreturn('Enter valid 12 digit mobile number ' . $mobile);
        //         }

        //         if (!BranchGstValidation($ledger_gst)) { // for english chars + numbers only
        //             //eg House Name, Street Name
        //             errorreturn('Invalid GST Number');
        //         }
        //     } else {

        //         $mobile = (isset($data['mobile']) and !empty($data['mobile'])) ? $data['mobile'] : null;
        //     }

        //     if (!CheckAddressValidation($address_ln_1)) {
        //         //eg House Name, Street Name
        //         errorreturn('Invalid Address' .$address_ln_1);
        //     }

        //     if($address_ln_2 != null){
        //     if (!CheckAddressValidation($address_ln_2)) {
        //         //eg House Name, Street Name
        //         errorreturn('Invalid Address' .$address_ln_2);
        //     }
        //     }
        // }
        // steps 
        #1. Validate all records before 
        // $gst_values  = []; // all gsts from update or insert
        //  two cases
        // Update / Delete
        // the id mentioned should not exist in billing_id or shipping_id from transactiondata
        // proceed
        // Insert : should check if the gst previously exists then errorreturn
        #2. Then Insert Update Delete

        if ($id != null) {
            // UPDATE / DELETE
            // must receive function  here
            if (isset($data['function']) and $data['function'] == 'update') {

                $sql = "UPDATE act_ledger_addresses SET ledgerid=?,name=?,address_ln_1=?,address_ln_2=?,state_id=?,country_id=?,ledger_gst=?,postal_code=?,mobile=?,email=?,pincode =? WHERE id=? and workspace_id  =? and company_id =? and branch_id =? and book_id =? ";
                $specific_db_conn->prepare($sql)->execute(array(
                    $ledgerid, $name, $address_ln_1, $address_ln_2, $state_id, $country_id, $ledger_gst, $postal_code, $mobile, $email,$pincode,$id, $workspace_id, $company_id, $branch_id, $book_id
                ));
            } else if (isset($data['function']) and $data['function'] == 'delete') {

                $sql = "DELETE FROM act_ledger_addresses WHERE id=? and workspace_id = ?  and company_id =? and branch_id =? and book_id =? ";
                $stmt = $specific_db_conn->prepare($sql);
                $stmt->execute(array($id, $workspace_id, $company_id, $branch_id, $book_id));
                $act_group = $stmt->rowCount();

                if ($act_group >= 1) {
                    //  return "Delete Ledger Addresses  Successfully";
                } else {
                    errorreturn("Unable to delete ledger addresses because of the presence of transaction.");
                }
            }
        } else {

            // GST validation
            if ($data['function'] != "delete") {

                $sql = "SELECT ledger_gst FROM  act_ledger_addresses WHERE (ledger_gst = ?";
                $array_param = [$ledger_gst];
                $sql = $sql . ") and workspace_id  =? and company_id =? and branch_id =? and book_id =?";
                $array_param[] = $workspace_id;
                $array_param[] = $company_id;
                $array_param[] = $branch_id;
                $array_param[] = $book_id;
                $res = $specific_db_conn->prepare($sql);
                $res->execute($array_param);
                $result1 = $res->fetchAll();
                if (count($result1) >= 1) {
                    errorreturn("GST already exists in Ledger Address");
                }

                // insert query of stock transaction 
                $sql = "INSERT INTO  act_ledger_addresses(workspace_id, company_id, branch_id, book_id,ledgerid,name,address_ln_1,address_ln_2,state_id,country_id,postal_code,ledger_gst,mobile,email,pincode)
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                $stmt =  $specific_db_conn->prepare($sql);
                $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, $ledgerid, $name, $address_ln_1, $address_ln_2, $state_id, $country_id, $postal_code, $ledger_gst, $mobile, $email,$pincode));
            }
        }
    }
}


function checkAddresses($specific_db_conn, $id, $workspace_id, $company_id, $branch_id, $book_id, $addresses)
{

    if ($addresses != null)
        $addresses = $addresses[0]['addresses'];

    $sql = "SELECT * FROM  act_ledger_addresses WHERE workspace_id = ? and company_id =? and branch_id =? and book_id =? and ledgerid = ?";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, $id));
    $result = $stmt->fetchAll();
    if (count($result) == 1) {
        $addresses_rows = array(
            'name' => $result[0]['name'], 'address_ln_1' =>  $result[0]['address_ln_1'],
            'address_ln_2' =>  $result[0]['address_ln_2'], 'state_id' =>  $result[0]['state_id'], 'country_id' =>  $result[0]['country_id'],
            'postal_code' =>  $result[0]['postal_code'], 'ledger_gst' =>  $result[0]['ledger_gst'], 'mobile' =>  $result[0]['mobile'], 'email' =>  $result[0]['email']
        );
        $differences = array_diff_assoc($addresses_rows, $addresses);
        if (empty($differences))
            return false;
        else
            return true;
    } else {
        if ($addresses == null)
            return false;
        else
            return true;
    }
}


$global_ledgerhead_ledgerid_already_fetched_array = [];
$global_ledgerhead_already_fetched_array = [];
function getLedgerSpecificLedgerhead($specific_db_conn, $ledgerid, $workspace_id, $company_id, $branch_id, $book_id)
{
    if (in_array($ledgerid, $GLOBALS['global_ledgerhead_ledgerid_already_fetched_array'])) {
        return $GLOBALS['global_ledgerhead_already_fetched_array'][array_search($ledgerid, $GLOBALS['global_ledgerhead_ledgerid_already_fetched_array']) . ''];
    } else {
        $sql = $specific_db_conn->prepare("SELECT ledger_main_ledgerhead FROM  act_ledgerdetails  where id = ? and workspace_id =? and  company_id =? and branch_id =? and book_id = ?");
        $sql->execute(array($ledgerid, $workspace_id, $company_id, $branch_id, $book_id));
        $rows = $sql->fetchAll();
        if (count($rows) == 1) {
            $GLOBALS['global_ledgerhead_ledgerid_already_fetched_array'][] = $ledgerid;
            $GLOBALS['global_ledgerhead_already_fetched_array'][] = $rows[0]['ledger_main_ledgerhead'];
            return $rows[0]['ledger_main_ledgerhead'];
        } else {
            errorreturn('Unable to find ledger : ' . $ledgerid);
        }
    }
}
