<?php

class Material {

    protected $info = array();
    protected $AllAccounts = array();
    private $db;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
    }

    public function setData($data) {
        $this->info = $data;
        return $this;
    }

    public function addUOM() {
        $arr = [&$this->info['UOM']];
        $this->db->prepare_Stmt('INSERT INTO tbl_stock_uom ( UOM_Name ) VALUES (?)', $arr);
        return $this->db->error();
    }

    public function convertxlsxtoCSV($infile, $outfile) {
        $fileType = PHPExcel_IOFactory::identify($infile);
        $objReader = PHPExcel_IOFactory::createReader($fileType);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($infile);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
        $objWriter->save($outfile);
    }

    public function uploadExcelSheet() {
        $fileDetails = require dirname(__FILE__) . '/../../libs/PHPExcel/PHPExcel/IOFactory.php';
        $Fileextension = $_FILES['Document_File_Name']['tmp_name'];
        $excel = PHPExcel_IOFactory::load($Fileextension);
        $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
        $writer->setDelimiter(";");
        $writer->setEnclosure("");
        self::convertxlsxtoCSV($Fileextension, 'output.csv');
        if (($getfile = fopen('output.csv', "r")) !== FALSE) {
            $header = fgetcsv($getfile, 1000, ",");
            //        echo "<pre>";
            //        print_r($header);
            //        die("ok");
            while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
                $col1 = $data[0];
                $col2 = $data[1];
                $col3 = $data[2];
                $col4 = $data[3];
                $col5 = $data[4];
                $col6 = $data[5];
                $col7 = $data[6];
                $col8 = $data[7];
                $dateTimeStamp = ($col8 - 25569) * 86400;
                $mydate = date('Y-m-d', $dateTimeStamp);
                if (!self::MaterialExists($col1, $col3)) {
                    $query = $this->db->query("INSERT INTO
																				tbl_stock_material
																	(
																				Material_Code,
                                                                                Material_Brand,
                                                                                Material_Name,
                                                                                Material_Category_ID,
                                                                                Material_Type_ID,
                                                                                UOM_ID,
                                                                                Material_Quantity,
                                                                                Material_Expiry_Date,
																				Logged_ID
																	)
																	VALUES
																	(
																				'" . $col1 . "',
																				'" . $col2 . "',
																				'" . $col3 . "',
																				'" . $col4 . "',
																				'" . $col5 . "',
																				'" . $col6 . "',
																				'" . $col7 . "',
																				'" . $mydate . "',
																				'" . $this->info[0] . "'
																	)
																				");
                }
            }
            return true;
        }
    }

    

    private function MaterialExists($code = null, $name = null) {
        $query = $this->db->query('SELECT * FROM tbl_stock_material WHERE Status_ID = 1 AND Material_Code = "' . (!is_null($code) ? $code : $this->info['Product_Code']) . '" AND Material_Name = "' . (!is_null($name) ? $name : $this->info['Product_Name']) . '"');
        $result = $this->db->rowsno($query);
        if ($result != 0)
            return true;
        return false;
    }

   


    public function getMaterialType() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_stock_material_type');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function getUOMByProductID($product_id, $uom_id) {
        $query = $this->db->query('SELECT conversion_amount FROM tbl_uom_conversions WHERE Product_ID = "' . $product_id . '" AND uom_from = "' . $uom_id . '"');
        return $this->db->assoc($query)['conversion_amount'];
    }

    public function getMaterialUOM() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_stock_uom order by UOM_ID');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

  

    public function getMaterial($CatID) {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_stock_material WHERE Material_Category_ID = "' . smc_escape($CatID) . '" AND Status_ID = 1');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }



    

    public function getMaterialsToReorder() {
        $AllAccounts = array();
        $query = $this->db->query('SELECT 
                                        m.Material_ID,
                                        m.Material_Code,
                                        m.Material_Name,
                                        CONCAT(m.Material_Quantity," ",u.UOM_Name),
                                        CONCAT(m.Material_Price," ",c.Currency_Symbol),
                                        CONCAT(m.Material_Reorder_Level," ",u.UOM_Name)
                                    FROM 
                                        tbl_stock_material m
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        m.Currency_ID = c.Currency_ID
                                    LEFT JOIN 
                                        tbl_stock_uom u
                                    ON 
                                        m.UOM_ID = u.UOM_ID
                                    WHERE 
                                        Material_Reorder_Level >= Material_Quantity 
                                    AND 
                                        Status_ID = 1');
        while ($row = $this->db->fetch_row($query)) {
            $AllAccounts[] = $row;
        }
        return $AllAccounts;
    }


  

    public function viewAvailableQuantity($material_id) {
        $available_quantity = 0;
        $query = $this->db->query('SELECT Material_Quantity FROM tbl_stock_material WHERE Material_ID = "' . $material_id . '"');
        $row = $this->db->assoc($query);
        $available_quantity = $row['Material_Quantity'];
        return $available_quantity;
    }



    public function getLastOfType($table, $ColName) {
        $query = $this->db->query("SELECT * FROM $table ORDER BY $ColName DESC limit 1");
        $row = $this->db->assoc($query);
        $retrievedData[] = $row;
        return $retrievedData;
    }

    public function getType($CatID) {
        $data = array();
        if ($CatID == 0) {
            $query = $this->db->query('SELECT
                                            Product_ID AS ID,
                                            Product_Code AS Code,
                                            Product_Name AS Name
                                       FROM 
                                            tbl_stock_products 
                                       WHERE 
                                            Status_ID = 1
                                       ORDER BY
                                            Product_ID
                                            ');
        } else {
            $query = $this->db->query('SELECT 
                                        Material_ID AS ID,
                                        Material_Code AS Code,
                                        Material_Name As Name
                                   FROM 
                                        tbl_stock_material 
                                   WHERE 
                                        Material_Category_ID = "' . $CatID . '"
                                    AND 
                                        Status_ID = 1');
        }
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewListVendorPurchaseByMaterial($material_id) {
        $data = array();
        $query = $this->db->query('SELECT
										s.Supplier_Name,
										i.Purchase_Date,
										p.Item_Quantity,
										p.Item_Price,
										i.Currency_ID,
										c.Currency_Symbol
									FROM 
										tbl_inventory_purchase_items p
									LEFT JOIN
										tbl_inventory_purchases i
									ON 
										p.Purchase_ID = i.Purchase_ID
									LEFT JOIN
										tbl_stock_suppliers s
									ON
										i.Supplier_ID = s.Supplier_ID
									LEFT JOIN
										tbl_common_currency c
									ON 
										i.Currency_ID = c.Currency_ID
									WHERE 
										Product_ID = "' . $material_id . '"
                                    AND 
                                       p.Material_Category_ID = 1
				');
        while ($row = $this->db->assoc($query)) {
            $row['Default_Currency_Price'] = CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Item_Price']);
            $row['Default_Currency_Symbol'] = CurrencyFactory::getDefaultCurrencySymbol();
            $data[] = $row;
        }
        return $data;
    }


  


}
