<?php

function insert_online_customers($branch_id, $pos_customers) {
    global $db;
    $succ_ids = array();
    if (!empty($pos_customers)) {
        //start transaction
        $customer = new Customer;
        $insert_count = $update_count = $insert_err_count = $update_err_count = 0;
        $err_message = "";
        foreach ($pos_customers as $cust_row) {
            $cust_id = $cust_row['customer_id'];
            $check_customer = $db->query_execute('select customer_id from tbl_pos_customers where online_customer_id = ?', array($cust_id));
            if ($db->query_result_count($check_customer) == 0) {//insert
                $insert = $customer->setdata($cust_row)->addCustomerPOS();
                if (is_numeric($insert)) {
                    $succ_ids[$cust_id] = $insert;
                    $insert_count++;
                } else {
                    $err_message .= "<br/>Insert pos customer [{$cust_id}] Error: " . implode(',', $insert);
                    $insert_err_count++;
                }
            } else {
                $check_cust_row = $db->query_fetch($check_customer);
                $cust_row['customer_id'] = $check_cust_row['customer_id'];
                $update = $customer->setdata($cust_row)->updateCustomerPOS();
                if (empty($update)) {
                    $succ_ids[$cust_id] = $check_cust_row['customer_id'];
                    $update_count++;
                } else {
                    $err_message .= "<br/>Update pos customer [{$cust_id}] Error: " . implode(',', $update);
                    $update_err_count++;
                }
            }
        }
        echo "<div style='border:1px solid black;background:#ccc;padding:5px;margin:10px 0px;'><h2>pos customers</h2>All Count: " . count($pos_customers);
        echo ($insert_count > 0 ? "<br/>Inserted Count: {$insert_count}" : "") . ($update_count > 0 ? "<br/>Updated Count: {$update_count}" : "");
        echo "  <div style='background:red;padding:5px;margin:5px;'>";
        echo ($insert_err_count > 0 ? "<br/>Inserted Error Count: {$insert_err_count}" : "") . ($update_err_count > 0 ? "<br/>Updated Error Count: {$update_err_count}" : "");
        echo (check_is_debug() && !empty($err_message) ? "<br/>{$err_message}" : '');
        echo "  </div>";
        echo "</div>";
    }
    return $succ_ids;
}

function insert_online_invoices($branch_id, $invoices, $online_inv_cust_ids) {
    global $db;
    $succ_ids = array();
    if (!empty($invoices)) {
        $insert_count = $update_count = $insert_err_count = $update_err_count = 0;
        $err_message = "";
        $invoice = new Invoice;
        $cust_arr = array();
        if (!empty($online_inv_cust_ids)) {
            $query_customers = $db->query_execute('select * from tbl_pos_customers 
                where online_customer_id in (' . implode(',', array_keys($online_inv_cust_ids)) . ') ');
            while ($cust_row = $db->query_fetch($query_customers))
                $cust_arr[$cust_row['online_customer_id']] = $cust_row['customer_id'];
        }
        //start transaction
        foreach ($invoices as $inv_id => $inv_row) {
            $inv_row['Invoice_Currency_Rate'] = $inv_row['current_rate'];
            $inv_row['shipping'] = $inv_row['Invoice_Shipping'];
            $inv_row['shipping_account'] = $inv_row['shipping_Account'];
            //$inv_row['Account_Code'] = $inv_row['sales_Account'];
            $inv_row['is_data_collector'] = 0;
            $inv_row['is_delivery_notified'] = 0;
            $inv_row['pending_status'] = 4;
            $inv_row['pos_customer_id'] = !empty($cust_arr[$inv_row['pos_customer_id']]) ? $cust_arr[$inv_row['pos_customer_id']] : 0;
            $inv_row['online_invoice_id'] = $inv_row['Invoice_ID_Auto'];
            $inv_row['is_main_transfer'] = 1;
            $inv_row['app_branch_id'] = $branch_id;
            $check_invoice = $db->query_execute('select Invoice_ID_Auto from tbl_invoices 
                where online_invoice_id = ? ', array($inv_row['Invoice_ID_Auto']));
            if ($db->query_result_count($check_invoice) == 0) {
                $insert = $invoice->setdata($inv_row)->insertSalesInvoice();
                if (is_numeric($insert[0])) {
                    $succ_ids[$inv_id] = $insert[0];
                    $insert_count++;
                } else {
                    $err_message .= "<br/>Insert Online Invoice [{$inv_id}] Error: " . implode(',', $insert);
                    $insert_err_count++;
                }
            } else {
                $check_inv_row = $db->query_fetch($check_invoice);
                $inv_row['Invoice_ID_Auto'] = $check_inv_row['Invoice_ID_Auto'];
                $update = $invoice->setdata($inv_row)->editInvoice();
                if (is_numeric($update[0])) {
                    $succ_ids[$inv_id] = $update[0];
                    $update_count++;
                } else {
                    $err_message .= "<br/>Update Online Invoice [{$inv_id}] Error: " . implode(',', $update);
                    $update_err_count++;
                }
            }
        }
        echo "<div style='border:1px solid black;background:#ccc;padding:5px;margin:10px 0px;'><h2>Invoices</h2>All Count: " . count($invoices);
        echo ($insert_count > 0 ? "<br/>Inserted Count: {$insert_count}" : "") . ($update_count > 0 ? "<br/>Updated Count: {$update_count}" : "");
        echo "  <div style='background:red;padding:5px;margin:5px;'>";
        echo ($insert_err_count > 0 ? "<br/>Inserted Error Count: {$insert_err_count}" : "") . ($update_err_count > 0 ? "<br/>Updated Error Count: {$update_err_count}" : "");
        echo (check_is_debug() && !empty($err_message) ? "<br/>{$err_message}" : '');
        echo "  </div>";
        echo "</div>";
        //end transaction
    }
    return $succ_ids;
}

function insert_online_transfers($branch_id, $transfers) {
    global $db;
    $succ_ids = array();
    if (!empty($transfers)) {
        $insert_count = $update_count = $insert_err_count = $update_err_count = 0;
        $err_message = "";
        //start transaction
        $warehouseDepot = new WarehouseDepot;
        //start transaction
        foreach ($transfers as $trnsfr_id => $trnsfr_row) {
//            $trnsfr_row['app_request_id'] = $trnsfr_row['Request_ID'];
//            $trnsfr_row['app_user_id'] = $trnsfr_row['Logged_ID'];
//            $trnsfr_row['app_branch_id'] = $branch_id;
            $trnsfr_row['request_prefix'] = $trnsfr_row['serial_prefix'];
            $trnsfr_row['online_request_id'] = $trnsfr_row['Request_ID'];
            $trnsfr_row['is_confirmed'] = ($trnsfr_row['Status_ID'] == 3 ? 1 : 0);
            $trnsfr_row['is_data_collector'] = 0;
            //insert transfer and set is_main_transfer = 1 to don't transfer to server again
            $trnsfr_row['is_main_transfer'] = 1;
            $trnsfr_row[0] = $trnsfr_row['Logged_ID'];
            $check_request = $db->query_execute('select Request_ID from tbl_stock_request 
                where online_request_id = ? ', array($trnsfr_row['Request_ID']));
            if ($db->query_result_count($check_request) == 0) {
                $insert = $warehouseDepot->setdata($trnsfr_row)->createRequest();
                if (is_numeric($insert[0])) {
                    $succ_ids[$trnsfr_id] = $insert[0];
                    $insert_count++;
                } else {
                    $err_message .= "<br/>Insert Online Transfers [{$trnsfr_id}] Error: " . implode(',', $insert);
                    $insert_err_count++;
                }
//                if (!empty($insert) && is_numeric($insert[0]))
//                    $succ_ids[$trnsfr_id] = $insert[0];
//                $insert_count++;
            } else {
                $check_req_row = $db->query_fetch($check_request);
                $trnsfr_row['Request_ID'] = $check_req_row['Request_ID'];
                $update = $warehouseDepot->setdata($trnsfr_row)->editRequest();
                if (is_numeric($update[0])) {
                    $succ_ids[$trnsfr_id] = $update[0];
                    $update_count++;
                } else {
                    $err_message .= "<br/>Update Online Transfers [{$trnsfr_id}] Error: " . implode(',', $update);
                    $update_err_count++;
                }
            }
        }
        echo "<div style='border:1px solid black;background:#ccc;padding:5px;margin:10px 0px;'><h2>Transfers</h2>All Count: " . count($transfers);
        echo ($insert_count > 0 ? "<br/>Inserted Count: {$insert_count}" : "") . ($update_count > 0 ? "<br/>Updated Count: {$update_count}" : "");
        echo "  <div style='background:red;padding:5px;margin:5px;'>";
        echo ($insert_err_count > 0 ? "<br/>Inserted Error Count: {$insert_err_count}" : "") . ($update_err_count > 0 ? "<br/>Updated Error Count: {$update_err_count}" : "");
        echo (check_is_debug() && !empty($err_message) ? "<br/>{$err_message}" : '');
        echo "  </div>";
        echo "</div>";
    }
    return $succ_ids;
}

function insert_online_pospond_invoices($branch_id, $pospond_invoices) {
    global $db;
    $succ_ids = array();
    if (!empty($pospond_invoices)) {
        $insert_count = $update_count = $insert_err_count = $update_err_count = 0;
        $err_message = "";
        $invoicepos = new InvoiceposSMCCustom;
        //start transaction
        foreach ($pospond_invoices as $inv_row) {
            $update = $invoicepos->setdata($inv_row)->update_inv_postpone_status($inv_row['Invoice_ID_Auto'], $inv_row['posponted_type']);
            if (empty($update)) {
                $succ_ids[] = $inv_row['Invoice_ID_Auto'];
                $insert_count++;
            } else {
                $err_message .= "<br/>Insert Postponed Invoice [{$inv_row['Invoice_ID_Auto']}] Error: " . implode(',', $update);
                $insert_err_count++;
            }
        }
        echo "<div style='border:1px solid black;background:#ccc;padding:5px;margin:10px 0px;'><h2>Postpond Invoices</h2>All Count: " . count($pospond_invoices);
        echo ($insert_count > 0 ? "<br/>Inserted Count: {$insert_count}" : "") . ($update_count > 0 ? "<br/>Updated Count: {$update_count}" : "");
        echo "  <div style='background:red;padding:5px;margin:5px;'>";
        echo ($insert_err_count > 0 ? "<br/>Inserted Error Count: {$insert_err_count}" : "") . ($update_err_count > 0 ? "<br/>Updated Error Count: {$update_err_count}" : "");
        echo (check_is_debug() && !empty($err_message) ? "<br/>{$err_message}" : '');
        echo "  </div>";
        echo "</div>";
        //end transaction
    }
    return $succ_ids;
}

function get_last_end_day() {
    global $db;
    $query = $db->query_execute('select close_time from tbl_end_of_shift where type=1 order by close_time desc limit 1');
    $row = $db->query_fetch($query);
    return (empty($row) ? '2000-01-01 00:00:00' : $row['close_time']);
}

function delete_invoices() {
    global $db;
    $db->transaction_start();
    //
    $ignore_ids = array();
    $invoices_ids = array();
    //get last invoice for each prefix to prevent deleting them
    $query_last_inv = $db->query_execute('select Invoice_ID_Auto from tbl_invoices 
        where concat(invoice_prefix,"-",Invoice_ID) 
        in (select concat(invoice_prefix,"-",max(Invoice_ID)) from tbl_invoices group by invoice_prefix)');
    while ($row = $db->query_fetch($query_last_inv))
        $ignore_ids[] = $row['Invoice_ID_Auto'];
    $last_inv = implode(',', $ignore_ids);
    //
    $query = $db->query_execute('select Invoice_ID_Auto from tbl_invoices where is_main_transfer = "1" and date_time < "' . get_last_end_day() . '"');
    while ($row = $db->query_fetch($query))
        $invoices_ids[] = $row['Invoice_ID_Auto'];
    if (empty($invoices_ids))
        return[];
    $invoices = implode(',', $invoices_ids);
    //delete accounting transactions
    $db->query_execute("delete from tbl_accounting_transactions where main_id in (" . $invoices . ") 
        and main_type='inv'  and main_id not in (" . $last_inv . ")");
    //delete stock transactions
    $db->query_execute("delete from tbl_stock_product_transactions 
            where Transaction_Type_ID in (2,11,43) and main_id in (" . $invoices . ") and  main_id not in (" . $last_inv . ")");
    //update total product quantity
    $db->query_execute("UPDATE tbl_stock_products p 
        join tbl_invoice_products_details d on d.Product_ID = p.Product_ID
        SET p.Product_Quantity = p.Product_Quantity + d.Product_Quantity
        WHERE d.Invoice_ID_Auto in (" . $invoices . ") and  d.Invoice_ID_Auto not in (" . $last_inv . ")");
    //update product location
    $db->query_execute("insert into tbl_stock_product_location 
                (Product_Depot_ID,Location_Product_ID,Product_Depot_Quantity)
                    select depot_id,Product_ID,Product_Quantity from tbl_invoice_products_details
                    where Invoice_ID_Auto in (" . $invoices . ") and  Invoice_ID_Auto  not in (" . $last_inv . ")
                on duplicate key update Product_Depot_Quantity = Product_Depot_Quantity + values(Product_Depot_Quantity) ");
    //delete from criteria
    $db->query_execute("DELETE criteria FROM  tbl_invoice_product_criteria criteria
                    inner join tbl_invoice_products_details details on details.Entery_ID = criteria.Entery_ID
                    inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto 
                where invoices.Invoice_ID_Auto  in (" . $invoices . ") and  invoices.Invoice_ID_Auto  not in (" . $last_inv . ")");
    //delete from options
    $db->query_execute("DELETE options 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 invoices.Invoice_ID_Auto  in (" . $invoices . ") and  invoices.Invoice_ID_Auto  not in (" . $last_inv . ")");
    //delete add ons
    $db->query_execute("DELETE add_ons FROM  tbl_invoice_product_add_ons add_ons
                inner join tbl_invoice_products_details details on details.Entery_ID = add_ons.Entery_ID
                inner join tbl_invoices invoices on invoices.Invoice_ID_Auto = details.Invoice_ID_Auto 
            where invoices.Invoice_ID_Auto  in (" . $invoices . ") and  invoices.Invoice_ID_Auto  not in (" . $last_inv . ")");
    //delete invoice details
    $db->query_execute("delete from tbl_invoice_products_details where Invoice_ID_Auto in (" . $invoices . ") and Invoice_ID_Auto not in (" . $last_inv . ") ");
    //delete shipping invoices
    $db->query_execute("delete from tbl_invoice_shipping where Invoice_ID_Auto in (" . $invoices . ") and  Invoice_ID_Auto not in (" . $last_inv . ") ");
    //delete main invoice
    $del_stmt = $db->query_execute("delete from tbl_invoices  where Invoice_ID_Auto  in (" . $invoices . ") and  Invoice_ID_Auto  not in (" . $last_inv . ") ");
    echo "<br/>Deleted Invoices After Sync.: " . $db->query_result_count($del_stmt) . "<br/>";
    $error = $db->get_error_arr();
    if (empty($error))
        $db->transaction_end();
    else
        $db->transaction_rollback();
    return array_unique($error);
}

function delete_returns() {
    global $db;
    $db->transaction_start();
    //
    $ignore_ids = array();
    $return_ids = array();
    //get last return for each prefix to prevent deleting them
    $query_last_inv = $db->query_execute('select Return_Invoice_ID from tbl_accounting_return_invoices 
        where Is_Customer_Invoice = 1 and concat(invoice_prefix,"-",Return_ID) 
        in (select concat(invoice_prefix,"-",max(Return_ID)) from tbl_accounting_return_invoices group by invoice_prefix)');
    while ($row = $db->query_fetch($query_last_inv))
        $ignore_ids[] = $row['Return_Invoice_ID'];
    $last_inv = implode(',', $ignore_ids);
    //
    $query = $db->query_execute('select Return_Invoice_ID from tbl_accounting_return_invoices where Is_Customer_Invoice = 1 
        and is_main_transfer = "1" and date_time < "' . get_last_end_day() . '"');
    while ($row = $db->query_fetch($query))
        $return_ids[] = $row['Return_Invoice_ID'];
    if (empty($return_ids))
        return[];
    $returns = implode(',', $return_ids);
    //delete transactions
    $db->query_execute("delete from tbl_accounting_transactions where main_id in (" . $returns . ") 
        and main_type='rin'  and main_id  not in (" . $last_inv . ")");
    //delete stock transactions
    $db->query_execute("delete from tbl_stock_product_transactions 
            where Transaction_Type_ID in (9,14) and main_id in (" . $returns . ") and  main_id  not in (" . $last_inv . ")");
    //update total product quantity
    $db->query_execute("UPDATE tbl_stock_products p 
        join tbl_accounting_return_items d on d.Product_ID = p.Product_ID
        SET p.Product_Quantity = p.Product_Quantity - d.Product_Quantity
        WHERE d.Return_Invoice_ID in (" . $returns . ") and  d.Return_Invoice_ID  not in (" . $last_inv . ")");
    //update product location
    $db->query_execute("insert into  
        tbl_stock_product_location (Product_Depot_ID,Location_Product_ID,Product_Depot_Quantity)
            select Depot_ID,Product_ID,Return_Product_Quantity from tbl_accounting_return_items i
            join tbl_accounting_return_invoices r on i.Return_Invoice_ID = r.Return_Invoice_ID
            where r.Return_Invoice_ID in (" . $returns . ") and  r.Return_Invoice_ID  not in (" . $last_inv . ")
        on duplicate key update Product_Depot_Quantity = Product_Depot_Quantity + values(Product_Depot_Quantity)");
    //delete return details
    $db->query_execute("delete from tbl_accounting_return_items where Return_Invoice_ID in (" . $returns . ") and  Return_Invoice_ID not in (" . $last_inv . ") ");
    //delete main return
    $del_stmt = $db->query_execute("delete from tbl_accounting_return_invoices  where Return_Invoice_ID  in (" . $returns . ") and  Return_Invoice_ID not in (" . $last_inv . ") ");
    echo "<br/>Deleted Return Invoices After Sync.: " . $db->query_result_count($del_stmt) . "<br/>";
    $error = $db->get_error_arr();
    if (empty($error))
        $db->transaction_end();
    else
        $db->transaction_rollback();
    return array_unique($error);
}

function delete_receipts() {
    global $db;
    $db->transaction_start();
    //
    $ignore_ids = array();
    $receipts_ids = array();
    //get last receipt for each prefix to prevent deleting them
    $query_last_inv = $db->query_execute('select pay_rec_id from tbl_payment_receipt 
        where is_receipt = 1 and concat(prefix,"-",pay_rec_nb) 
        in (select concat(prefix,"-",max(pay_rec_nb)) from tbl_payment_receipt group by prefix)');
    while ($row = $db->query_fetch($query_last_inv))
        $ignore_ids[] = $row['pay_rec_id'];
    $last_inv = implode(',', $ignore_ids);
    //
    $query = $db->query_execute('select pay_rec_id from tbl_payment_receipt where is_receipt = 1 and is_main_transfer = "1" and date_time < "' . get_last_end_day() . '"');
    while ($row = $db->query_fetch($query))
        $receipts_ids[] = $row['pay_rec_id'];
    if (empty($receipts_ids))
        return[];
    $receipts = implode(',', $receipts_ids);
    //delete transactions
    $db->query_execute("delete from tbl_accounting_transactions where main_id in (" . $receipts . ") 
        and main_type='rec'  and main_id not in (" . $last_inv . ")");
    //delete receipt details
    $db->query_execute("delete from tbl_invoice_payments where record_id in (" . $receipts . ") and  record_id not in (" . $last_inv . ") and type in ('rec','pay')");
    //delete receipt details
    $db->query_execute("delete from tbl_payment_receipt_details where pay_rec_id in (" . $receipts . ") and  pay_rec_id not in (" . $last_inv . ") ");
    //delete main receipt
    $del_stmt = $db->query_execute("delete from tbl_payment_receipt  where pay_rec_id  in (" . $receipts . ") and  pay_rec_id not in (" . $last_inv . ") ");
    echo "<br/>Deleted Payment/Receipt After Sync.: " . $db->query_result_count($del_stmt) . "<br/>";
    $error = $db->get_error_arr();
    if (empty($error))
        $db->transaction_end();
    else
        $db->transaction_rollback();
    return array_unique($error);
}

function delete_dep_with() {
    global $db;
    $db->transaction_start();
    //
    $ignore_ids = array();
    $dep_with_ids = array();
    //get last receipt for each prefix to prevent deleting them
    $query_last_inv = $db->query_execute('select Deposit_WithDrawal_ID from tbl_accounting_bank_deposit_withdrawal 
        where concat(dw_prefix,"-",dw_number) 
        in (select concat(dw_prefix,"-",max(dw_number)) from tbl_accounting_bank_deposit_withdrawal group by dw_prefix)');
    while ($row = $db->query_fetch($query_last_inv))
        $ignore_ids[] = $row['Deposit_WithDrawal_ID'];
    $last_inv = implode(',', $ignore_ids);
    //
    $query = $db->query_execute('select Deposit_WithDrawal_ID from tbl_accounting_bank_deposit_withdrawal where is_main_transfer = "1" and date_time < "' . get_last_end_day() . '"');
    while ($row = $db->query_fetch($query))
        $dep_with_ids[] = $row['Deposit_WithDrawal_ID'];
    if (empty($dep_with_ids))
        return[];
    $dep_with = implode(',', $dep_with_ids);
    //delete transactions
    $db->query_execute("delete from tbl_accounting_transactions where main_id in (" . $dep_with . ") 
        and main_type='dwt'  and main_id not in (" . $last_inv . ")");
    //delete deposit/withdrawal
    $del_stmt = $db->query_execute("delete from tbl_accounting_bank_deposit_withdrawal where Deposit_WithDrawal_ID in (" . $dep_with . ") and  Deposit_WithDrawal_ID not in (" . $last_inv . ") ");
    echo "<br/>Deleted Deposit/Withdraw After Sync.: " . $db->query_result_count($del_stmt) . "<br/>";
    //
    $error = $db->get_error_arr();
    if (empty($error))
        $db->transaction_end();
    else
        $db->transaction_rollback();
    return array_unique($error);
}
