<?php 
define('DOCUMENT_ROOT_CLASS','../../');
define('DOCUMENT_ROOT_INC_FOLDER','inc/');

$filepath = realpath(dirname(__FILE__));
require "../../inc/header.php";
$msg = Session::get('msg');
Session::set("msg", NULL);
if(!empty($_GET['action']) and @$_GET['action'] == "export"){
    @unlink('../../uploads/product-services/product-images/Products-Details-List.xls');
    require('../../lib/PHPExcel-1.8/Classes/PHPExcel.php');
    require_once '../../lib/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getActiveSheet()->setTitle('Products Details List');
    
    ob_start();
    $objPHPExcel->setActiveSheetIndex(0); // Create new worksheet
    $head = 0;
    $table_head = array("Label / Product Name","Product Ref#","Barcode Value","Selling Price","Physical Stock","Virtual Stock","Product Description");
    foreach($table_head as $value)
    {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($head, 2, $value);   
        $head++;
    }
    $firstRowData = "Products Details List";

    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:G1');
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $firstRowData);
    
    $body = 3;//Add some data
    $export_data = $commonFunctions->getData('invty_pdt_details', "");
    foreach($export_data as $row)
    {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$body,$row['label']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$body,$row['reference_number']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$body,$row['barcode_value']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$body,$row['price']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$body,$row['virtual_stock']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$body,$row['physical_stock']);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$body,$row['description']);
        $body++;
    }
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    // If you want to output e.g. a PDF file, simply do:
    $objWriter->save('../../uploads/product-services/product-images/Products-Details-List.xls');
    echo "<script>window.open('../../uploads/product-services/product-images/Products-Details-List.xls');</script>";
}
if(!empty($_POST['action']) and @$_POST['action'] == "import"){
    require '../../lib/Excel-Library/php-excel-reader/excel_reader2.php';
    require '../../lib/Excel-Library/SpreadsheetReader.php';
    
    if(isset($_FILES['excel_file']['name']) && $_FILES['excel_file']['name'] != "") {
        $allowedExtensions = array("xls","xlsx");
        $ext = pathinfo($_FILES['excel_file']['name'], PATHINFO_EXTENSION);
        if(in_array($ext, $allowedExtensions)) {
            $file_size = $_FILES['excel_file']['size'] / 1024;
            if($file_size < 2048) {
                $file = "../../uploads/product-services/product-images/".$_FILES['excel_file']['name'];
                $isUploaded = copy($_FILES['excel_file']['tmp_name'], $file);
                if($isUploaded) {
                    include("../../lib/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php");
                    try {
                        //Load the excel(.xls/.xlsx) file
                        $objPHPExcel = PHPExcel_IOFactory::load($file);
                    } catch (Exception $e) {
                        die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage());
                    }

                    error_reporting(E_ALL);
                    ini_set('display_errors', TRUE);
                    ini_set('display_startup_errors', TRUE);

                    //An excel file may contains many sheets, so you have to specify which one you need to read or work with.
                    $sheet = $objPHPExcel->getSheet(0);
                    //It returns the highest number of rows
                    $total_rows = $sheet->getHighestRow();
                    //It returns the highest number of columns
                    $highest_column = $sheet->getHighestColumn();
                    $affected_rows = 0;
                    for($row =2; $row <= $total_rows; $row++) {
                        $single_row = $sheet->rangeToArray('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                        $label = $single_row[0][0];
                        $sales_status = ($single_row[0][1] == 1)?1:0;
                        $purchase_status = ($single_row[0][2] == 1)?1:0;
                        $lot_serial_status = ($single_row[0][3] == 1)?1:0;
                        $barcode_type = 0;
                        if(!empty($single_row[0][4])){
                            $barcodeArr = $commonFunctions->getName("'".$single_row[0][4]."'",'sys_erp_adm_barcode_type','id','label');
                            if(!empty($barcodeArr)){
                                $barcode_type = $barcodeArr;
                            }
                        }

                        $reference_number = $single_row[0][5];
                        $barcode_value = $single_row[0][6];
                        $description = $single_row[0][7];
                        $default_warehouse_id = 0;
                        if(!empty($single_row[0][8])){
                            $warehouse = $commonFunctions->getName($single_row[0][8],'invty_wrhus','id','short_name_location');
                            if(!empty($warehouse)){
                                $default_warehouse_id = $warehouse;
                            }
                        }                        
                        $stock_limit_alert = !empty($single_row[0][9])?$single_row[0][9]:0;
                        $desired_optimal_stock = !empty($single_row[0][10])?$single_row[0][10]:0;
                        $nature = ($single_row[0][11] == 1)?1:0;
                        $note = $single_row[0][12];

                        $tag_id = 0;
                        if(!empty($single_row[0][13])){
                            $tag = $commonFunctions->getName($single_row[0][13],'invty_pdt_tag','id','label');
                            if(!empty($tag)){
                                $tag_id = $tag;
                            }
                        }

                        $price = !empty($single_row[0][14])?$single_row[0][14]:0;
                        $min_price = !empty($single_row[0][15])?$single_row[0][15]:0;
                        $tax_management_id = $single_row[0][16];

                        $duration = $single_row[0][17];
                        $selling_type = ($single_row[0][18] == 1)?1:0;
                        $buying_price = !empty($single_row[0][19])?$single_row[0][19]:0;
                        $cost_price = !empty($single_row[0][20])?$single_row[0][20]:0;
                        $public_url = $single_row[0][21];
                        $weight = $single_row[0][22];
                        $surface = $single_row[0][23];
                        $volume = $single_row[0][24];
                        $size_length = $single_row[0][25];
                        $size_width = $single_row[0][26];
                        $size_height = $single_row[0][27];
                        $customcode = $single_row[0][28];
                        $country_id = 0;
                        if(!empty($single_row[0][29])){
                            $country = $commonFunctions->getName($single_row[0][29],'sys_erp_adm_countries','rowid','label');
                            if(!empty($country)){
                                $country_id = $country;
                            }
                        }
                        $added_by = $_SESSION['id'];
                        $accountancy_code_sell = $single_row[0][30];
                        $accountancy_code_purchase = $single_row[0][31];
                        $virtual_stock = !empty($single_row[0][32])?$single_row[0][32]:0;;
                        $physical_stock = !empty($single_row[0][33])?$single_row[0][33]:0;
                        $commonFunctions->addImportData('invty_pdt_details'," (product_type,reference_number,label,sales_status,purchase_status,lot_serial_status,barcode_type,barcode_value,description,public_url,default_warehouse_id,stock_limit_alert,desired_optimal_stock,nature,weight,size_length,size_width,size_height,surface,volume,customcode,country_id,note,tag_id,price,min_price,tax_management_id,accountancy_code_sell,accountancy_code_purchase,duration,buying_price,cost_price,selling_type,added_by,virtual_stock,physical_stock ) VALUES ('1','".$reference_number."','".$label."','".$sales_status."','".$purchase_status."','".$lot_serial_status."','".$barcode_type."','".$barcode_value."','".$description."','".$public_url."','".$default_warehouse_id."','".$stock_limit_alert."','".$desired_optimal_stock."','".$nature."','".$weight."','".$size_length."','".$size_width."','".$size_height."','".$surface."','".$volume."','".$customcode."','".$country_id."','".$note."','".$tag_id."','".$price."','".$min_price."','".$tax_management_id."','".$accountancy_code_sell."','".$accountancy_code_purchase."','".$duration."','".$buying_price."','".$cost_price."','".$selling_type."','".$added_by."','".$virtual_stock."','".$physical_stock."') ",'');
                        $affected_rows = $affected_rows + 1;
                    }
                    Session::set('msg', '<div class="alert alert-success alert-dismissible mt-3" id="flash-msg">
                        <a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a>
                        <strong>Success !</strong> Wow, Your Information added Successfully !<br>Added Records - '.$affected_rows.'</div>');
                    @unlink($file);
                    echo "<script>window.location.href='';</script>";
                }else{
                    Session::set('msg', '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg"><a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a><strong>Error !</strong> File not uploaded!</div>');
                }
            }else{
                Session::set('msg', '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg"><a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a><strong>Error !</strong> Maximum file size should not cross 2 MB on size!</div>');
            }
        }else{
            Session::set('msg', '<div class="alert alert-danger alert-dismissible mt-3" id="flash-msg"><a href="#" class="close" data-dismiss="alert" aria-label="close">&times;</a><strong>Error !</strong> Please select valid format!</div>');
        }
    }    
}

?>

    <!-- Page-Title -->
    <div class="row">
        <div class="col-sm-12">
            <div class="page-title-box">
                <div class="row">
                    <div class="col">
                        <h4 class="page-title"><?php echo "Import Products"; ?></h4>
                        <ol class="breadcrumb">
                            <li class="breadcrumb-item"><a href="javascript:void(0);">Products & Services</a></li>
                            <li class="breadcrumb-item"><a href="javascript:void(0);">Products</a></li>
                            <li class="breadcrumb-item"><a href="javascript:void(0);"><?php echo "Import Products"; ?></a></li>
                        </ol>
                    </div><!--end col-->
                    <div class="col-auto align-self-center">
                        <!-- <button type="button" class="btn btn-sm btn-outline-primary">
                            <i class="align-self-center ti-export"></i>
                        </button> -->

                        <!--a href="product.php?action=export" class="btn btn-sm btn-outline-primary">
                            <i data-feather="download" class="align-self-center icon-xs"></i>
                        </a-->
                    </div><!--end col-->  
                </div><!--end row-->                                                              
            </div><!--end page-title-box-->
        </div><!--end col-->
    </div><!--end row-->
    <!-- end page title end breadcrumb -->
    <div class="row">
        <div class="col-12">
            <div class="card">
                <div class="card-header">
                    <h4 class="card-title"> 
                        <div class="form-row">
                            <div class="form-group searchForm col-md-6">    
                                <a class="btn btn-sm btn-soft-primary" href="product.php?state=add" role="button" ><i class="fas fa-plus mr-2"></i> Add New Product</a>
                            </div>  
                            <div class="form-group searchForm col-md-6">
                                
                            </div>
                        </div>
                    </h4>
                </div>
       
                <div class="card-body">
                    <div class="form-row">
                        
                        <div class="col-md-8">
                            <form id="ImportForm" enctype="multipart/form-data" method="post">
                                <div class="form-row">
                                    <div class="col-md-12">
                                        <span><b>Note - </b></span>
                                        <br>
                                        <span>1. File format should be .xls file.</span>
                                        <br>
                                        <span>2. File not more than 2MB.</span>
                                        <br>
                                        <div id="errorMessage"><?php echo !empty($msg)?$msg:''; ?></div>
                                        <br>
                                        <br>
                                    </div>
                                    <div class="col-md-6">
                                        <input type="hidden" name="action" value="import">
                                        <input type="file" name="excel_file" id="inpFile" required="">
                                    </div>
                                    <div class="col-md-6">
                                        <button type="submit" class="btn btn-sm btn-outline-success">Import Excel Data</button>
                                        <button type="button" class="btn btn-sm btn-outline-primary">Sample Excel Format</button>
                                    </div>
                                </div>
                            </form>
                        </div>
                        
                        <div class="col-md-4"></div>
                    </div>
                </div>
            </div>
        </div> <!-- end col -->
    </div> <!-- end row -->

<?php require "../../inc/footer.php"; ?>
<script type="text/javascript">
    function redirect_(uri){
        window.location.href = uri;
    }
    $("#clearbtn").click(function(){
        $("#search").val('');
        $("#searchForm").submit();
    });
</script>