<?php


class Mbe_Shipping_Helper_Package
{
    protected $_cvs_package_table_name  = 'mbe_shipping_standard_packages';
    protected $_cvs_package_product_table_name  = 'mbe_shipping_standard_package_product';

    public function installCsvPackageTable()
    {
        $sql = "CREATE TABLE IF NOT EXISTS `" . _DB_PREFIX_ . $this->_cvs_package_table_name . "`(
                `id_mbeshippingpackage` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `max_weight` decimal(12,4) default 0 not null,
                `length` decimal(12,4) default 0 not null,
                `width` decimal(12,4) default 0 not null,
                `height` decimal(12,4) default 0 not null,
                `package_label` varchar(255) not null,
                `package_code` varchar(55) not null,                
                 UNIQUE KEY MBE_PKG_PROD_UNIQUE (package_code))";
        $result = Db::getInstance()->execute($sql);
        return $result;
    }

    public function uninstallCsvPackageTable()
    {
        $sql = "DROP TABLE IF EXISTS `" . _DB_PREFIX_ . $this->_cvs_package_table_name . "`";
        $result = Db::getInstance()->execute($sql);
        return $result;
    }

    public function truncate()
    {
        $truncateSql = " TRUNCATE `" . _DB_PREFIX_ . $this->_cvs_package_table_name . "` ";
        $truncateResult = Db::getInstance()->execute($truncateSql);
        return $truncateResult;
    }

    public function insertCsvPackage($max_weight, $length, $width, $height, $package_label, $package_code)
    {
        $sql = "
                INSERT INTO `" . _DB_PREFIX_ . $this->_cvs_package_table_name . "` (
                    `max_weight`,`length`,`width`,`height`,`package_label`,`package_code`
                ) 
                VALUES (
                    " . $max_weight . ",
                    " . $length . ",
                    " . $width . ",
                    " . $height . ",
                    '" . $package_label . "',
                    '" . $package_code . "'                    
                );";


        $insertResult = Db::getInstance()->execute($sql);
        return $insertResult;
    }

	public function getPackageInfobyProduct( $productSku ) {
		$main_table      = _DB_PREFIX_ . $this->_cvs_package_table_name;
		$packagesProduct = _DB_PREFIX_ . $this->_cvs_package_product_table_name;

		$sql = "SELECT " . $main_table . ".*, " .
			$packagesProduct . ".id_mbeshippingpackageproduct as id_product, " . $packagesProduct .".single_parcel, " . $packagesProduct . ".custom_package " .
			" FROM " . $main_table .
			" LEFT JOIN " . $packagesProduct . " ON " .
			$main_table . ".package_code = " . $packagesProduct . ".package_code " .
			" WHERE " . $packagesProduct . ".product_sku = '" . $productSku . "'";

		return Db::getInstance()->executeS($sql);

	}

	public function getStandardPackages() {
        $main_table = _DB_PREFIX_ . $this->_cvs_package_table_name;
		$join_table = _DB_PREFIX_ . $this->_cvs_package_product_table_name;

		$sql = "SELECT " . $main_table . ".package_label, " . $main_table . ".id  FROM " . $main_table .
			" LEFT JOIN " . $join_table . " ON " .
			$main_table . ".package_code = " . $join_table . ".package_code " .
			" WHERE " . $join_table . ".custom_package <> true OR ". $join_table . ".custom_package is null";

        return Db::getInstance()->executeS($sql);
	}

	public function getCsvPackages() {
		$main_table = _DB_PREFIX_ . $this->_cvs_package_table_name;

        return Db::getInstance()->executeS("SELECT *  FROM ". $main_table);
	}

	public function getPackageInfobyId( $packageId ) {
        $main_table = _DB_PREFIX_ . $this->_cvs_package_table_name;

        $sql = "SELECT *, 0 as single_parcel, 0 as custom_package FROM " . $main_table  . " WHERE id = ".  $packageId;

		return Db::getInstance()->executeS($sql);
	}
}