<?php

/**
 * __data_type:
 *      'upld': process is to upload data from branch to server so we get data from before few minutes and not yet transfered
 *      'rstr' process is to restore data from server to branch so we get all data related to that branch
 */
$pay_prefix_arr = array('Cash', 'Check', 'Card', 'shipping', 'Other');

/**
 * get invoices
 */
function fill_arr_frm_db__invoices(&$data) {
    global $db, $pay_prefix_arr;
    //
    $data['invoices'] = array();
    //used on server to get online customer ids | and on branch restore to get offline customer ids
    if (!isset($data['onlin_oflin_cstmr_ids']))
        $data['onlin_oflin_cstmr_ids'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and i.app_branch_id = '{$data['branch_id']}' ";
    else
        $cond .= " and i.is_main_transfer = '0' ";
    if (!empty($data['__time_before']))
        $cond .= " and i.date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT i.*,c.Customer_Name,ac.Account_ID as Customer_Account,i.Account_ID as Account_Code,
        cc.Currency_Symbol as CurrencySymbole,i.pos_customer_id FROM tbl_invoices i 
            inner join tbl_customers c on c.Customer_ID = i.Customer_ID 
            inner join tbl_chart_of_accounts ac on ac.main_id = i.Customer_ID and ac.main_type = 'cust' and ac.sub_type='main' 
            inner join tbl_common_currency cc on cc.Currency_ID = i.Invoice_Currency
        where 1=1 {$cond} order by Invoice_ID_Auto asc ");
    $tmp_inv_ids = '';
    while ($row = $db->query_fetch($stmt)) {
        foreach ($pay_prefix_arr as $pay_prfx) {
            $row[$pay_prfx . '_Amount'] = 0;
            $row[$pay_prfx . '_Account'] = null;
        }
        $row['is_postponed_change'] = 0;
        $row['is_branch_transfer'] = 1; //to don't send it from server to branch again
        $data['invoices'][$row['Invoice_ID_Auto']] = $row;
        if (!empty($row['pos_customer_id']))
            $data['onlin_oflin_cstmr_ids'][$row['pos_customer_id']] = '';
        $tmp_inv_ids .= (empty($tmp_inv_ids) ? '' : ',') . $row['Invoice_ID_Auto'];
    }
    if ($data['__data_type'] == 'rstr' && !empty($data['onlin_oflin_cstmr_ids'])) {
        $stmt = $db->query_execute("SELECT online_customer_id, offline_customer_id from tbl_pos_customers_sync
                    where branch_id = '{$data['branch_id']}' and online_customer_id in (" . implode(',', array_keys($data['onlin_oflin_cstmr_ids'])) . ") ");
        while ($row = $db->query_fetch($stmt))
            $data['onlin_oflin_cstmr_ids'][$row['online_customer_id']] = $row['offline_customer_id'];
    }
    //get invoices details | payment, shipping details
    if (!empty($tmp_inv_ids)) {
        $tmp_entry_ids = '';
        $stmt = $db->query_execute("SELECT *,Item_Quantity_Current as Item_Quantity
                , Product_Discount as Item_Discount,Product_Net_Price as Item_Price,vat_amount as vat_val
            FROM tbl_invoice_products_details where Invoice_ID_Auto in ({$tmp_inv_ids}) order by Entery_ID asc");
        while ($row = $db->query_fetch($stmt)) {
            $data['invoices'][$row['Invoice_ID_Auto']]['Products'][$row['Entery_ID']] = $row;
            $tmp_entry_ids .= (empty($tmp_entry_ids) ? '' : ',') . $row['Entery_ID'];
        }
        if (!empty($tmp_entry_ids)) {
            $stmt = $db->query_execute("select criteria.*, details.Invoice_ID_Auto from tbl_invoice_product_criteria criteria
                                        inner join tbl_invoice_products_details details on details.Entery_ID = criteria.Entery_ID
                                    where criteria.Entery_ID IN({$tmp_entry_ids})");
            while ($row = $db->query_fetch($stmt))
                $data['invoices'][$row['Invoice_ID_Auto']]['Products'][$row['Entery_ID']]['criteria_details'][] = $row;
            // invoice details options
            $stmt = $db->query_execute("select options.UOM_ID as uom_id,options.product_id
                ,options.option_quantity as qty,invoices.Invoice_ID_Auto,options.option_id,options.invoice_detail_id
                from tbl_invoice_product_options options
                inner join tbl_invoice_products_details details on details.Entery_ID = options.invoice_detail_id
                inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto
                where options.invoice_detail_id IN({$tmp_entry_ids})");
            while ($row = $db->query_fetch($stmt))
                $data['invoices'][$row['Invoice_ID_Auto']]['Products'][$row['invoice_detail_id']]['options_arr'][] = $row;
        }
        //
        $stmt = $db->query_execute("SELECT Transaction_Amount, Transaction_Reference, Account_ID, main_id  FROM tbl_accounting_transactions
            where main_type = 'inv' and Transaction_Reference in ('check', 'cash', 'credit', 'ship','other') and main_id in ({$tmp_inv_ids}) ");
        while ($row = $db->query_fetch($stmt)) {
            $pay_prfx = 'Cash';
            if ($row['Transaction_Reference'] == 'check')
                $pay_prfx = 'Check';
            elseif ($row['Transaction_Reference'] == 'credit')
                $pay_prfx = 'Card';
            elseif ($row['Transaction_Reference'] == 'ship')
                $pay_prfx = 'shipping';
            elseif ($row['Transaction_Reference'] == 'other')
                $pay_prfx = 'Other';
//            elseif ($row['Transaction_Reference'] == 'inv')
//                $pay_prfx = 'sales';
            //
            $data['invoices'][$row['main_id']][$pay_prfx . '_Amount'] = $row['Transaction_Amount'];
            $data['invoices'][$row['main_id']][$pay_prfx . '_Account'] = $row['Account_ID'];
        }
    }
}

/**
 * get return invoices
 */
function fill_arr_frm_db__return_invoices(&$data) {
    global $db, $pay_prefix_arr;
    //
    $data['return'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and i.app_branch_id = '{$data['branch_id']}' and i.Is_Customer_Invoice = 1 ";
    else
        $cond .= " and i.is_main_transfer = '0' and i.Is_Customer_Invoice = 1 ";
    if (!empty($data['__time_before']))
        $cond .= " and i.date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT i.*,ac.Account_ID as Person_Account FROM tbl_accounting_return_invoices i 
            inner join tbl_chart_of_accounts ac on ac.main_id = i.Person_ID and ac.main_type='cust' and ac.sub_type='main' 
        where 1=1 {$cond} order by Return_Invoice_ID asc");
    $tmp_ret_ids = '';
    while ($row = $db->query_fetch($stmt)) {
        foreach ($pay_prefix_arr as $pay_prfx) {
            $row[$pay_prfx . '_Amount'] = 0;
            $row[$pay_prfx . '_Account'] = null;
        }
        $data['return'][$row['Return_Invoice_ID']] = $row;
        $tmp_ret_ids .= (empty($tmp_ret_ids) ? '' : ',') . $row['Return_Invoice_ID'];
    }
    //get return invoice details | payment details
    if (!empty($tmp_ret_ids)) {
        $stmt = $db->query_execute("SELECT *,Item_Quantity_Current as Return_Product_Quantity
                , Product_Discount as Item_Discount,unit_price as Unit_Cost,vat_amount as vat_val
            FROM tbl_accounting_return_items where Return_Invoice_ID in ({$tmp_ret_ids}) order by Return_Item_ID asc");
        while ($row = $db->query_fetch($stmt))
            $data['return'][$row['Return_Invoice_ID']]['Products'][] = $row;
        //
        $stmt = $db->query_execute("SELECT Transaction_Amount, Transaction_Reference, Account_ID, main_id  FROM tbl_accounting_transactions
            where main_type = 'rin' and Transaction_Reference in ('check', 'cash', 'credit', 'ship','ret') and main_id in ({$tmp_ret_ids}) ");
        while ($row = $db->query_fetch($stmt)) {
            $pay_prfx = 'Cash';
            if ($row['Transaction_Reference'] == 'check')
                $pay_prfx = 'Check';
            elseif ($row['Transaction_Reference'] == 'credit')
                $pay_prfx = 'Card';
            elseif ($row['Transaction_Reference'] == 'ret')
                $pay_prfx = 'sales';
            //
            $data['return'][$row['main_id']][$pay_prfx . '_Amount'] = $row['Transaction_Amount'];
            $data['return'][$row['main_id']][$pay_prfx . '_Account'] = $row['Account_ID'];
        }
    }
}

/**
 * get transfers, only confirmed [status 3] one and not yet transfered 
 */
function fill_arr_frm_db__transfers(&$data) {
    global $db;
    //
    $data['transfers'] = array();
    $cond = '';
    //if is_main_transfer is: 2 transfered to [tranfer_from] only | 3 transfered to [tranfer_to] only
    //0 no transfered yet | 1 transfered to both branches
    //it can be created on server directly or on [from/to] branch
    if ($data['__data_type'] == 'rstr')
        $cond .= " and app_branch_id = '{$data['branch_id']}' 
            and ( (to_branch_id = '{$data['branch_id']}' and is_main_transfer in (1,2))
                || (Request_From in (" . get_depot_by_branch($data['branch_id']) . ") and is_main_transfer in (1,3)) ) ";
    else
        $cond .= " and is_main_transfer = '0' and to_branch_id = '{$data['branch_id']}' ";
    if (!empty($data['__time_before']))
        $cond .= " and date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT * FROM tbl_stock_request where 1=1 {$cond} and Status_ID = 3 order by Request_ID asc");
    $tmp_trnsfr_ids = '';
    while ($row = $db->query_fetch($stmt)) {
        $data['transfers'][$row['Request_ID']] = $row;
        $tmp_trnsfr_ids .= (empty($tmp_trnsfr_ids) ? '' : ',') . $row['Request_ID'];
    }
    if (!empty($tmp_trnsfr_ids)) {
        $tmp_entry_ids = '';
        $stmt = $db->query_execute("SELECT *, Request_UOM as UOM_ID,Current_Quantity as Request_Qty 
            FROM tbl_stock_request_details where Request_ID in ({$tmp_trnsfr_ids}) order by Entry_ID asc");
        while ($row = $db->query_fetch($stmt)) {
            $data['transfers'][$row['Request_ID']]['Products'][$row['Entry_ID']] = $row;
            $tmp_entry_ids .= (empty($tmp_entry_ids) ? '' : ',') . $row['Entry_ID'];
        }
        if (!empty($tmp_entry_ids)) {
            $stmt = $db->query_execute("select criteria.*, details.Request_ID from tbl_request_product_criteria criteria
                                        inner join tbl_stock_request_details details on details.Entry_ID = criteria.Entry_ID
                                    where criteria.Entry_ID IN({$tmp_entry_ids})");
            while ($row = $db->query_fetch($stmt))
                $data['transfers'][$row['Request_ID']]['Products'][$row['Entry_ID']]['criteria_details'][] = $row;
        }
    }
    //above we get the transfer that from that branch id only and for the others we update 
    //them as not sync. so on next sync. they will be downloaded
    if ($data['__data_type'] == 'rstr') {
        $db->query_execute("update tbl_stock_request set is_main_transfer = if(is_main_transfer=1,3,0)
            where app_branch_id <> '{$data['branch_id']}' and Status_ID = 3
                and to_branch_id = '{$data['branch_id']}' and is_main_transfer in (1,2) ");
        $db->query_execute("update tbl_stock_request set is_main_transfer = if(is_main_transfer=1,2,0)
            where app_branch_id <> '{$data['branch_id']}' and Status_ID = 3
                and Request_From in (" . get_depot_by_branch($data['branch_id']) . ") and is_main_transfer in (1,3) ");
    }
}

/**
 * get payment & receipts
 */
function fill_arr_frm_db__payments_receipts(&$data) {
    global $db;
    //
    $data['pay_rec'] = array();
    if (!isset($data['onlin_oflin_cstmr_ids']))
        $data['onlin_oflin_cstmr_ids'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and p.app_branch_id = '{$data['branch_id']}' ";
    else
        $cond .= " and p.is_main_transfer = '0' ";
    if (!empty($data['__time_before']))
        $cond .= " and p.date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT p.*,c.Account_Name ,payment_date as Payment_Date,is_actual as Status_ID,
        is_receipt as IsReceipt, p.total_amount as total_payment
        FROM tbl_payment_receipt p
            join tbl_chart_of_accounts c on p.Account_ID = c.Account_ID
        where 1=1 {$cond} order by pay_rec_id asc");
    while ($row = $db->query_fetch($stmt)) {
        if (!empty($row['pos_customer_id']))
            $data['onlin_oflin_cstmr_ids'][$row['pos_customer_id']] = '';
        $data['pay_rec'][$row['pay_rec_id']] = $row;
    }
    //
    if (!empty($data['pay_rec'])) {
        $stmt = $db->query_execute("SELECT p.*, payment_account_id as Payment_Type_ID,
        payment_amount as Payment_Amount,payment_note as Payment_Note, cheque_nb as Check_Number,
        payment_due_date as Check_Date, current_rate as ex_rate
        FROM tbl_payment_receipt_details p
        where pay_rec_id in (" . (implode(',', (array_keys($data['pay_rec'])))) . ") order by payment_detail_id asc");
        while ($row = $db->query_fetch($stmt)) {
            $data['pay_rec'][$row['pay_rec_id']]['payment'][] = $row;
        }
    }
    //
    if ($data['__data_type'] == 'rstr' && !empty($data['onlin_oflin_cstmr_ids'])) {
        $stmt = $db->query_execute("SELECT online_customer_id, offline_customer_id from tbl_pos_customers_sync
                    where branch_id = '{$data['branch_id']}' and online_customer_id in (" . implode(',', array_keys($data['onlin_oflin_cstmr_ids'])) . ") ");
        while ($row = $db->query_fetch($stmt))
            $data['onlin_oflin_cstmr_ids'][$row['online_customer_id']] = $row['offline_customer_id'];
    }
    //payment on invoices
    if (!empty($data['pay_rec'])) {
        $query_pay_inv = $db->query_execute('select p.record_id, p.inv_pur_id as inv_id,
            p.Payment_Amount as inv_amount, i.Invoice_Currency as inv_currency, i.invoice_app_id, i.app_branch_id
            from tbl_invoice_payments p
                inner join tbl_invoices i on i.Invoice_ID_Auto = p.inv_pur_id and p.type= "rec"
            where record_id in (' . implode(',', array_keys($data['pay_rec'])) . ')');
        while ($row_pay_inv = $db->query_fetch($query_pay_inv)) {
            if ($data['__data_type'] == 'rstr') {
                if (empty($row_pay_inv['invoice_app_id']) || $row_pay_inv['app_branch_id'] != $data['branch_id'])
                    continue;
                $row_pay_inv['inv_id'] = $row_pay_inv['invoice_app_id'];
            }
            $data['pay_rec'][$row_pay_inv['record_id']]['close_arr'][] = $row_pay_inv;
        }
    }
}

/**
 * get deposit & withdrawal
 */
function fill_arr_frm_db__deposit_withdrawal(&$data) {
    global $db;
    //
    $data['dep_with'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and app_branch_id = '{$data['branch_id']}' ";
    else
        $cond .= " and is_main_transfer = '0' ";
    if (!empty($data['__time_before']))
        $cond .= " and date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT * FROM `tbl_accounting_bank_deposit_withdrawal`
        where 1=1 {$cond} order by Deposit_WithDrawal_ID asc");
    while ($row = $db->query_fetch($stmt))
        $data['dep_with'][$row['Deposit_WithDrawal_ID']] = $row;
}

/**
 * get cash drawer data
 */
function fill_arr_frm_db__cashdrawer(&$data) {
    global $db;
    //
    $data['cash_drawer'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and app_branch_id = '{$data['branch_id']}' ";
    else
        $cond .= " and is_main_transfer <> '2' ";
    $stmt = $db->query_execute("SELECT * FROM `tbl_cash_drawer_amount` WHERE 1=1 {$cond} ");
    while ($row = $db->query_fetch($stmt))
        $data['cash_drawer'][$row['cash_drawer_amount_id']] = $row;
}

/**
 * get pos customers
 */
function fill_arr_frm_db__pos_customers(&$data) {
    global $db;
    //
    $data['pos_customers'] = array();
    if ($data['__data_type'] == 'rstr')
        $query = "SELECT c.*, s.offline_customer_id FROM `tbl_pos_customers` c
                    inner join tbl_pos_customers_sync s on s.online_customer_id = c.customer_id
                WHERE /*s.is_sync = 1 and*/ s.branch_id = '{$data['branch_id']}' and offline_customer_id <> 0 ";
    else
        $query = "SELECT * FROM `tbl_pos_customers` WHERE `is_main_transfer` = 0 ";
    $stmt = $db->query_execute($query);
    while ($row = $db->query_fetch($stmt))
        $data['pos_customers'][$row['customer_id']] = $row;
}

/**
 * get delete log [invoice, return, ..]
 */
function fill_arr_frm_db__delete_log(&$data) {
    global $db;
    //
    $data['delete_log'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        return;
    $cond .= " and is_main_transfer = '0' and `action` = 2 and `type` in ('inv','ret') ";
    $stmt = $db->query_execute("SELECT * FROM `tbl_main_log` WHERE 1=1 {$cond} ");
    while ($row = $db->query_fetch($stmt))
        $data['delete_log'][$row['log_id']] = $row;
}

/**
 * get updated users, like when user change password at branch
 */
function fill_arr_frm_db__users(&$data) {
    global $db;
    //
    $data['users'] = array();
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        return;
    $cond .= " and is_main_transfer = '0' ";
    $stmt = $db->query_execute("SELECT * FROM `tbl_common_users` WHERE 1=1 {$cond} ");
    while ($row = $db->query_fetch($stmt))
        $data['users'][$row['User_ID']] = $row;
}

/**
 * get end of shift/day
 */
function fill_arr_frm_db__end_shift_day(&$data) {
    global $db;
    //
    $data['end_shift_day'] = array();
    //we put it here just to initialize it to an empty array
    if (get_settings('activate_sync_end_of_shift_day') != 1)
        return;
    $cond = '';
    if ($data['__data_type'] == 'rstr')
        $cond .= " and app_branch_id = '{$data['branch_id']}' ";
    else
        $cond .= " and is_main_transfer = '0' ";
    if (!empty($data['__time_before']))
        $cond .= " and date_time < '{$data['__time_before']}' ";
    $stmt = $db->query_execute("SELECT * FROM `tbl_end_of_shift`
        where 1=1 {$cond} order by shift_id asc");
    while ($row = $db->query_fetch($stmt))
        $data['end_shift_day'][$row['shift_id']] = $row;
}
