<?php
require_once '../../../resources/base_path_include.php';

require_once $base_path_to_resources . "includes/config.php"; // use require for mandatory files
require_once $base_path_to_resources . 'lib/function.php';
require_once $base_path_to_resources . 'lib/export/print/book_template/template_1/header.php';
require_once $base_path . "vendor/autoload.php";
require_once $base_path_to_resources . "lib/currency_functions.php";

//valdation file
require_once $base_path . 'modules/accounting/stock_management/functions/stock_function.php';
require_once $base_path . 'modules/accounting/stock_management/inventory_regions/region_functions.php';
require_once $base_path . 'modules/accounting/book_management/basic_book_info_function.php';
require_once $base_path . 'modules/accounting/reports/valuation_methods/closing_stock_summary/fifo_summary_function.php';
require_once $base_path . 'modules/accounting/functions/basic_balance_function.php';

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use \PhpOffice\PhpSpreadsheet\Style\Border;
use \PhpOffice\PhpSpreadsheet\Style\Font;


$ledgerFields = array('stock_name'=>'Name',
    "opening_stock"=>'Opening Stock',
     "closing_stock"=>'Closing Stock',
     "current_balance"=>'Current Balance',
    'unit_name'=>'Unit Name',
    'opening_qty'=>'Opening Quantity',
    'opening_balance'=>'Opening Balance',
    
    'current_qty'=>'Current Quantity',
    'parent_group'=>'Parent Group',
    'intra_tax_ledgerids'=>'Intra Tax Ledgerids',
    'inter_tax_ledgerids'=>'Inter Tax Ledgerids',
    'other_tax_ledgerids'=>'Other Tax Ledgerids',
    
    );
    
    $additionaldata = array();
    $additionaldata = ['id', 'stock_name', 'current_qty'];
    $additionaldata = array_merge($additionaldata, $postdata['additional_data']);
    GetStockDetail($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id, $search_string, $additionaldata, $limit, null, $appended_region_bool,$book_opening_date,$book_closing_date);
    
     $data = GetStockDetail($specific_db_conn,$workspace_id,$company_id,$branch_id,$book_id, $search_string,$additionaldata,$limit,$offset,$page,null,$book_opening_date,$book_closing_date);
    // print_r($data['ledger_info']);
    $ledgers = $data['stock_info'];
    $fields_to_remove = ['id','barcode_match'];
    
    $ledgers=removeKeys($ledgers,$fields_to_remove);
   
    $values=getArrayValues($ledgers);
    
    $keys = getArrayKeys($ledgers[0]);
    $formattedKeys = getArrayKeys($ledgers[0]);
    
    foreach ($keys as $index => $k) {
        $formattedKeys[$index] = getCorrespondingValue($ledgerFields, $k);
    }
    // return array of keys and values
    //print_r(array("values" => $values,"keys" => $formattedKeys));
    $data = array("values" => $values,"keys" => $formattedKeys);
    
    createExcel($specific_db_conn,$data, $workspace_id, $company_id, $branch_id, $book_id,$function_code);
    
function createExcel($specific_db_conn, $data, $workspace_id, $company_id, $branch_id, $book_id, $function_code)
{

    $values_count = count($data['values']);

    $keys_count = count($data['keys']);

    $values = $data['values'];

    $keys = $data['keys'];

    $book_info = GetBasicBook($specific_db_conn, $workspace_id, $company_id, $branch_id, $book_id);
    $sql = "SELECT * FROM act_book_settings WHERE workspace_id = ? AND company_id = ? AND branch_id = ? AND book_id = ?";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result = $stmt->fetchAll();
    // print_R($result);
    if (count($result) == 1) {

        $report_print_company_name = $result[0]['report_print_company_name'];
        $report_print_branch_name = $result[0]['report_print_branch_name'];
         $report_print_book_name = $result[0]['report_print_book_name'];
        $logo = $result[0]['logo'];
        $print_logo_bool = $result[0]['print_logo_bool'];
        if ($result[0]['logo'] !=  null) {
            if ($print_logo_bool == 1) {
                $link = 'https://rejoicebilldesk.in/rjlbilldesk_development/';

                $file = $link . $logo;
            }
        } else {

            $file =  null;
        }
        if ($report_print_company_name == 1) {
            $company_name = $book_info['book_basic_info']['company_name'];
        } else {

            $company_name =  null;
        }

        if ($report_print_branch_name == 1) {
            $branch_name = $book_info['book_basic_info']['branch_name'];
        } else {

            $branch_name =  null;
        }
        
         if ($report_print_book_name == 1) {
            $book_name = $book_info['book_basic_info']['book_name'];
            $book_opening_date = $book_info['book_basic_info']['book_opening_date'];
            $book_closing_date = $book_info['book_basic_info']['book_closing_date'];
            $currencySystem = $book_info['book_basic_info']['currency_format'];
            $number_of_decimal_places = $book_info['book_basic_info']['number_of_decimal_places'];
            $base_currency_symbol = $book_info['book_basic_info']['base_currency_symbol'];
        } else {

            $book_name =  null;
            $book_opening_date =  null;
            $book_closing_date =  null;
        }
        
    } else {
        errorreturn('Branch or company not found.');
    }
   
    $columns = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');


    // Create a new Spreadsheet instance
    $spreadsheet = new Spreadsheet();

    // Create a new Spreadsheet instance
    $spreadsheet = new Spreadsheet();

    // Set values in the spreadsheet
    $sheet = $spreadsheet->getActiveSheet();
    // Set the column width for columns A to G
    for ($column = 'A'; $column <= 'G'; $column++) {
        $sheet->getColumnDimension($column)->setWidth(19); // Set the width as desired (15 in this example)
    }
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', $company_name)
        ->setCellValue('A2', $branch_name)
        ->setCellValue('A4', $book_name)
        ->setCellValue('A5', $book_opening_date  .  '-'  .  $book_closing_date)

        ->getStyle('A4', $book_name)->getFont()->setBold(true);

    // Get the cell style and font
    $style = $sheet->getStyle('A1');
    $font = $style->getFont();
    // Set the font to bold
    $font->setBold(true);
    $rowCount = 6;
    $column = 'A';
    // Set column headers
    foreach ($keys as $item) {
        //for($col = 1; $col <= $keys_count; $col++) {

        // $columnName = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);

        $sheet->setCellValue($column . $rowCount, $item);
        $sheet->getStyle($column . $rowCount, $item)->getFont()->setBold(true);
        $column++;

        // }
    }

    $rowCount = 7;
    // Set row data
    foreach ($values as $v) {
        $column = 'A';
        foreach ($v as $item1) {
            if (is_numeric($item1)) {
                $item_data = formatCurrency($item1, $currencySystem, $number_of_decimal_places, null);
                $sheet->setCellValue($column . $rowCount, $item_data);
                $alignment = $sheet->getStyle($column . $rowCount)->getAlignment();
                $alignment->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
                
            } else {
                $item_data = $item1;
                $sheet->setCellValue($column . $rowCount, $item_data);
                 $alignment = $sheet->getStyle($column . $rowCount)->getAlignment();
                       }
            
           
            $column++;
        }
        $rowCount++;
    }
    // Set headers for download

    // Start output buffering
    ob_start();

    // Create Excel writer object
    $writer = new Xlsx($spreadsheet);

    // Save the spreadsheet to a variable instead of a file
    $writer->save('php://output');

    // Get the content that was written to the output buffer
    $excelData = ob_get_contents();

    // Clear the output buffer
    ob_end_clean();

    // Set appropriate headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Stock Report - Rejoice Finware.xlsx"');
    header('Cache-Control: max-age=0');

    // Output the Excel file data
    echo $excelData;
    exit;
}
