/**
* Same as StockMvtReason::getStockMvtReasons(), ignoring a specific lists of ids
*
* @since 1.5.0
* @param int $id_lang
* @param array $ids_ignore
* @param int $sign optional
*/
public static function getStockMvtReasonsWithFilter($id_lang, $ids_ignore, $sign = null)
{
$query = new DbQuery();
$query->select('smrl.name, smr.id_stock_mvt_reason, smr.sign');
$query->from('stock_mvt_reason', 'smr');
$query->leftjoin('stock_mvt_reason_lang', 'smrl', 'smr.id_stock_mvt_reason = smrl.id_stock_mvt_reason AND smrl.id_lang=' . (int) $id_lang);
$query->where('smr.deleted = 0');
if ($sign != null) {
$query->where('smr.sign = ' . (int) $sign);
}
if (count($ids_ignore)) {
$ids_ignore = array_map('intval', $ids_ignore);
$query->where('smr.id_stock_mvt_reason NOT IN(' . implode(', ', $ids_ignore) . ')');
}
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
/**
* Retrieves the product entries for the current order
*
* @param int $id_lang Optional Id Lang - Uses Context::language::id by default
* @return array
*/
public function getEntries($id_lang = null)
{
if ($id_lang == null) {
$id_lang = Context::getContext()->language->id;
}
// build query
$query = new DbQuery();
$query->select('
s.*,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(agl.name, \' - \', al.name SEPARATOR \', \')), pl.name) as name_displayed');
$query->from('supply_order_detail', 's');
$query->innerjoin('product_lang', 'pl', 'pl.id_product = s.id_product AND pl.id_lang = ' . $id_lang);
$query->leftjoin('product', 'p', 'p.id_product = s.id_product');
$query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = s.id_product_attribute');
$query->leftjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$query->leftjoin('attribute_lang', 'al', 'al.id_attribute = atr.id_attribute AND al.id_lang = ' . $id_lang);
$query->leftjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang = ' . $id_lang);
$query->where('s.id_supply_order = ' . (int) $this->id);
$query->groupBy('s.id_supply_order_detail');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
/**
* @see StockManagerInterface::getProductRealQuantities()
*/
public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)
{
if (!is_null($ids_warehouse)) {
// in case $ids_warehouse is not an array
if (!is_array($ids_warehouse)) {
$ids_warehouse = array($ids_warehouse);
}
// casts for security reason
$ids_warehouse = array_map('intval', $ids_warehouse);
}
// Gets client_orders_qty
$query = new DbQuery();
$query->select('SUM(od.product_quantity) + SUM(od.product_quantity_refunded)');
$query->from('order_detail', 'od');
$query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
$query->where('od.product_id = ' . (int) $id_product);
if (0 != $id_product_attribute) {
$query->where('od.product_attribute_id = ' . (int) $id_product_attribute);
}
$query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.date_add = o.date_upd');
$query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
$query->where('os.shipped != 1');
$query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
//if (count($ids_warehouse))
//$query->where('od.id_warehouse IN('.implode(', ', $ids_warehouse).')');
$client_orders_qty = (int) Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($query);
// Gets supply_orders_qty
$query = new DbQuery();
$query->select('sod.quantity_expected, sod.quantity_received');
$query->from('supply_order', 'so');
$query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order = so.id_supply_order');
$query->leftjoin('supply_order_state', 'sos', 'sos.id_supply_order_state = so.id_supply_order_state');
$query->where('sos.pending_receipt = 1');
$query->where('sod.id_product = ' . (int) $id_product . ' AND sod.id_product_attribute = ' . (int) $id_product_attribute);
if (!is_null($ids_warehouse) && count($ids_warehouse)) {
$query->where('so.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
}
$supply_orders_qties = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
$supply_orders_qty = 0;
foreach ($supply_orders_qties as $qty) {
if ($qty['quantity_expected'] > $qty['quantity_received']) {
$supply_orders_qty += $qty['quantity_expected'] - $qty['quantity_received'];
}
}
// Gets {physical OR usable}_qty
$qty = $this->getProductPhysicalQuantities($id_product, $id_product_attribute, $ids_warehouse, $usable);
//real qty = actual qty in stock - current client orders + current supply orders
return $qty - $client_orders_qty + $supply_orders_qty;
}
/**
* Gets the list of supply order statuses
*
* @param array $ids Optional Do not include these ids in the result
* @param int $id_lang Optional
* @return array
*/
public static function getStates($ids = null, $id_lang = null)
{
if ($id_lang == null) {
$id_lang = Context::getContext()->language->id;
}
if ($ids && !is_array($ids)) {
$ids = array();
}
$query = new DbQuery();
$query->select('sl.name, s.id_supply_order_state');
$query->from('supply_order_state', 's');
$query->leftjoin('supply_order_state_lang', 'sl', 's.id_supply_order_state = sl.id_supply_order_state AND sl.id_lang=' . (int) $id_lang);
if ($ids) {
$query->where('s.id_supply_order_state NOT IN(' . implode(',', array_map('intval', $ids)) . ')');
}
$query->orderBy('sl.name ASC');
return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
}
public function renderCSV()
{
if (Tools::isSubmit('export_csv')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="suppliers.csv"');
// write headers column
$keys = array('Name', 'Email', 'Company', 'Firstname', 'Lastname', 'Address 1', 'Address 2', 'Post code', 'City', 'Phone', 'GSM', 'Fax', 'Franco amount', 'Discount amount', 'Escompte', 'Delivery time', 'Account number accounting', 'Adding date', 'Updating date', 'activate');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('s.*, erpips.*, a.company, a.firstname, a.lastname, a.address1, a.address2, a.postcode, a.city, a.phone, a.phone_mobile');
$query->from('supplier', 's');
$query->leftjoin('erpip_supplier', 'erpips', 'erpips.id_supplier = s.id_supplier');
$query->leftjoin('address', 'a', 'a.id_supplier = s.id_supplier');
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $supplier) {
$content_csv = array(self::transformText($supplier['name']), $supplier['email'], self::transformText($supplier['company']), self::transformText($supplier['firstname']), self::transformText($supplier['lastname']), self::transformText($supplier['address1']), self::transformText($supplier['address2']), $supplier['postcode'], $supplier['city'], $supplier['phone'], $supplier['phone_mobile'], $supplier['fax'], $supplier['franco_amount'], $supplier['discount_amount'], $supplier['escompte'], $supplier['delivery_time'], $supplier['account_number_accounting'], $supplier['date_add'], $supplier['date_upd'], $supplier['active'], PHP_EOL);
echo implode(';', $content_csv);
}
die;
}
}
/**
* @see StockManagerInterface::getProductRealQuantities()
*/
public function getProductRealQuantities($id_product, $id_product_attribute, $ids_warehouse = null, $usable = false)
{
if (!is_null($ids_warehouse)) {
// in case $ids_warehouse is not an array
if (!is_array($ids_warehouse)) {
$ids_warehouse = array($ids_warehouse);
}
// casts for security reason
$ids_warehouse = array_map('intval', $ids_warehouse);
}
$client_orders_qty = 0;
// check if product is present in a pack
if (!Pack::isPack($id_product) && ($in_pack = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('SELECT id_product_pack, quantity FROM ' . _DB_PREFIX_ . 'pack
WHERE id_product_item = ' . (int) $id_product . '
AND id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0')))) {
foreach ($in_pack as $value) {
if (Validate::isLoadedObject($product = new Product((int) $value['id_product_pack'])) && ($product->pack_stock_type == 1 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && Configuration::get('PS_PACK_STOCK_TYPE') > 0)) {
$query = new DbQuery();
$query->select('od.product_quantity, od.product_quantity_refunded, pk.quantity');
$query->from('order_detail', 'od');
$query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
$query->where('od.product_id = ' . (int) $value['id_product_pack']);
$query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state');
$query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
$query->leftJoin('pack', 'pk', 'pk.id_product_item = ' . (int) $id_product . ' AND pk.id_product_attribute_item = ' . ($id_product_attribute ? (int) $id_product_attribute : '0') . ' AND id_product_pack = od.product_id');
$query->where('os.shipped != 1');
$query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
$query->groupBy('od.id_order_detail');
if (count($ids_warehouse)) {
$query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
}
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
if (count($res)) {
foreach ($res as $row) {
$client_orders_qty += ($row['product_quantity'] - $row['product_quantity_refunded']) * $row['quantity'];
}
}
}
}
}
// skip if product is a pack without
if (!Pack::isPack($id_product) || (Pack::isPack($id_product) && Validate::isLoadedObject($product = new Product((int) $id_product)) && $product->pack_stock_type == 0 || $product->pack_stock_type == 2 || $product->pack_stock_type == 3 && (Configuration::get('PS_PACK_STOCK_TYPE') == 0 || Configuration::get('PS_PACK_STOCK_TYPE') == 2))) {
// Gets client_orders_qty
$query = new DbQuery();
$query->select('od.product_quantity, od.product_quantity_refunded');
$query->from('order_detail', 'od');
$query->leftjoin('orders', 'o', 'o.id_order = od.id_order');
$query->where('od.product_id = ' . (int) $id_product);
if (0 != $id_product_attribute) {
$query->where('od.product_attribute_id = ' . (int) $id_product_attribute);
}
$query->leftJoin('order_history', 'oh', 'oh.id_order = o.id_order AND oh.id_order_state = o.current_state');
$query->leftJoin('order_state', 'os', 'os.id_order_state = oh.id_order_state');
$query->where('os.shipped != 1');
$query->where('o.valid = 1 OR (os.id_order_state != ' . (int) Configuration::get('PS_OS_ERROR') . '
AND os.id_order_state != ' . (int) Configuration::get('PS_OS_CANCELED') . ')');
$query->groupBy('od.id_order_detail');
if (count($ids_warehouse)) {
$query->where('od.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
}
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
if (count($res)) {
foreach ($res as $row) {
$client_orders_qty += $row['product_quantity'] - $row['product_quantity_refunded'];
}
}
}
// Gets supply_orders_qty
$query = new DbQuery();
$query->select('sod.quantity_expected, sod.quantity_received');
$query->from('supply_order', 'so');
$query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order = so.id_supply_order');
$query->leftjoin('supply_order_state', 'sos', 'sos.id_supply_order_state = so.id_supply_order_state');
$query->where('sos.pending_receipt = 1');
$query->where('sod.id_product = ' . (int) $id_product . ' AND sod.id_product_attribute = ' . (int) $id_product_attribute);
if (!is_null($ids_warehouse) && count($ids_warehouse)) {
$query->where('so.id_warehouse IN(' . implode(', ', $ids_warehouse) . ')');
}
$supply_orders_qties = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
$supply_orders_qty = 0;
foreach ($supply_orders_qties as $qty) {
if ($qty['quantity_expected'] > $qty['quantity_received']) {
$supply_orders_qty += $qty['quantity_expected'] - $qty['quantity_received'];
}
}
// Gets {physical OR usable}_qty
$qty = $this->getProductPhysicalQuantities($id_product, $id_product_attribute, $ids_warehouse, $usable);
//real qty = actual qty in stock - current client orders + current supply orders
return $qty - $client_orders_qty + $supply_orders_qty;
}
/**
* Exports CSV
*/
protected function renderCSV()
{
// exports orders
if (Tools::isSubmit('csv_orders')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="supply_orders.csv"');
// write headers column
$keys = array('id_supplier', 'supplier_name', 'id_lang', 'id_warehouse', 'id_supply_order_state', 'id_currency', 'reference', 'date_add', 'date_upd', 'date_delivery_expected', 'total_te', 'total_with_discount_te', 'total_ti', 'total_tax', 'discount_rate', 'discount_value_te', 'is_template', 'escompte', 'invoice_number', 'date_to_invoice', 'global_discount_amount', 'global_discount_type', 'shipping_amount', 'description');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('so.*, ipso.*');
$query->from('supply_order', 'so');
$query->leftjoin('erpip_supply_order', 'ipso', 'ipso.id_supply_order = so.id_supply_order');
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// FILTERS SUPPLIER & WAREHOUSE
$id_warehouse = $this->getCurrentWarehouse();
if ($id_warehouse != -1) {
$query->where('so.id_warehouse = ' . (int) $id_warehouse);
}
$id_supplier = $this->getCurrentSupplier();
if ($id_supplier != -1) {
$query->where('so.id_supplier = ' . (int) $id_supplier);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $order) {
$content_csv = array($order['id_supplier'], self::transformText($order['supplier_name']), $order['id_lang'], $order['id_warehouse'], $order['id_supply_order_state'], $order['id_currency'], $order['reference'], $order['date_add'], $order['date_upd'], $order['date_delivery_expected'], $order['total_te'], $order['total_with_discount_te'], $order['total_ti'], $order['total_tax'], $order['discount_rate'], $order['discount_value_te'], $order['is_template'], $order['escompte'], $order['invoice_number'], $order['date_to_invoice'] == '0000-00-00' ? '' : $order['date_to_invoice'], $order['global_discount_amount'], $order['global_discount_type'], $order['shipping_amount'], self::transformText($order['description']), PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
} else {
if (Tools::isSubmit('csv_orders_details')) {
// header
header('Content-type: text/csv');
header('Content-Type: application/force-download; charset=UTF-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="' . $this->l('supply_orders_details') . '.csv"');
// echoes details
$ids = array();
foreach ($this->_list as $entry) {
$ids[] = $entry['id_supply_order'];
}
if ($this->controller_status == STATUS1) {
$ids = array_splice($ids, 0, ERP_STCKMGTFR);
}
if (count($ids) <= 0) {
return;
}
// for each supply order
$keys = array('id_product', 'id_product_attribute', 'reference', 'supplier_reference', 'ean13', 'upc', 'name', 'unit_price_te', 'quantity_expected', 'quantity_received', 'price_te', 'discount_rate', 'discount_value_te', 'price_with_discount_te', 'tax_rate', 'tax_value', 'price_ti', 'tax_value_with_order_discount', 'price_with_order_discount_te', 'id_supply_order', 'comment');
echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $keys)));
// overrides keys (in order to add FORMAT calls)
$keys = array('sod.id_product', 'sod.id_product_attribute', 'sod.reference', 'sod.supplier_reference', 'sod.ean13', 'sod.upc', 'sod.name', 'FORMAT(sod.unit_price_te, 2)', 'sod.quantity_expected', 'sod.quantity_received', 'FORMAT(sod.price_te, 2)', 'FORMAT(sod.discount_rate, 2)', 'FORMAT(sod.discount_value_te, 2)', 'FORMAT(sod.price_with_discount_te, 2)', 'FORMAT(sod.tax_rate, 2)', 'FORMAT(sod.tax_value, 2)', 'FORMAT(sod.price_ti, 2)', 'FORMAT(sod.tax_value_with_order_discount, 2)', 'FORMAT(sod.price_with_order_discount_te, 2)', 'sod.id_supply_order', 'ipsod.comment');
foreach ($ids as $id) {
$query = new DbQuery();
$query->select(implode(', ', $keys));
$query->from('supply_order_detail', 'sod');
$query->leftJoin('supply_order', 'so', 'so.id_supply_order = sod.id_supply_order');
$query->leftJoin('erpip_supply_order_detail', 'ipsod', 'ipsod.id_supply_order_detail = sod.id_supply_order_detail');
// FILTERS SUPPLIER & WAREHOUSE
$id_warehouse = $this->getCurrentWarehouse();
if ($id_warehouse != -1) {
$query->where('so.id_warehouse = ' . (int) $id_warehouse);
}
$id_supplier = $this->getCurrentSupplier();
if ($id_supplier != -1) {
$query->where('so.id_supplier = ' . (int) $id_supplier);
}
$query->where('sod.id_supply_order = ' . (int) $id);
$query->orderBy('sod.id_supply_order_detail DESC');
$resource = Db::getInstance()->query($query);
// gets details
while ($row = Db::getInstance()->nextRow($resource)) {
$row = array_map(array('CSVCore', 'wrap'), $row);
$row['name'] = self::transformText($row['name']);
$row['reference'] = self::transformText($row['reference']);
$row['supplier_reference'] = self::transformText($row['supplier_reference']);
echo sprintf("%s\n", implode(';', $row));
}
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
} else {
if (Tools::isSubmit('csv_order_details') && Tools::getValue('id_supply_order')) {
$supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
if (Validate::isLoadedObject($supply_order)) {
$details = $supply_order->getEntriesCollection();
$details->getAll();
$csv = new CSV($details, $this->l('supply_order') . '_' . $supply_order->reference . '_details');
$csv->export();
}
} else {
if (Tools::isSubmit('export_csv')) {
// get id lang
$id_lang = Context::getContext()->language->id;
// header
header('Content-type: text/csv');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="Supply order detail.csv"');
// puts hearder of CSV
$keys = array('supplier_reference', 'quantity_expected');
echo sprintf("%s\n", implode(';', $keys));
// gets global order information
$supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
// get supply order detail
$supply_order_detail = $supply_order->getEntries($id_lang);
// puts data
foreach ($supply_order_detail as $product) {
$row_csv = array($product['supplier_reference'], $product['quantity_expected']);
// puts one row
echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv)));
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
} else {
if (Tools::isSubmit('export_history')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="supply_orders_history.csv"');
// write headers column
$keys = array('id_supply_order_history', 'id_supply_order', 'id_employee', 'employee_lastname', 'employee_firstname', 'id_state', 'state', 'unit_price', 'discount_rate', 'is_canceled');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('sorh.*, ipsorh.*, "state" as state');
$query->from('supply_order_receipt_history', 'sorh');
$query->leftjoin('erpip_supply_order_receipt_history', 'ipsorh', 'ipsorh.id_supply_order_receipt_history = sorh.id_supply_order_receipt_history');
$query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order_detail = sorh.id_supply_order_detail');
$query->where('sod.id_supply_order = ' . (int) Tools::getValue('id_supply_order'));
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $history) {
$content_csv = array($history['id_supply_order_history'], $history['id_supply_order'], $history['id_employee'], $history['employee_lastname'], $history['employee_firstname'], $history['id_state'], $history['state'], $history['unit_price'], $history['discount_rate'], $history['is_canceled'], PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
}
die;
}
}
}
}
}
}
public function renderCSV()
{
if (Tools::isSubmit('export_csv')) {
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="areas.csv"');
// write headers column
$keys = array('area_name', 'parent_name', 'warehouse', 'active');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('area.name as area_name, parent.name as parent_name, area.active, w.name as warehouse');
$query->from('erpip_zone', 'area');
$query->leftjoin('erpip_zone', 'parent', 'parent.id_erpip_zone = area.id_parent');
$query->leftjoin('warehouse', 'w', 'w.id_warehouse = area.id_warehouse');
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $area) {
$content_csv = array(self::transformText($area['area_name']), self::transformText($area['parent_name']), $area['warehouse'], $area['active'], PHP_EOL);
echo implode(';', $content_csv);
}
die;
}
}
protected function renderCatalog()
{
$stckmgtfr = ERP_STCKMGTFR;
if (Tools::isSubmit('export_catalog')) {
//OUPUT HEADERS
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private', false);
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=catalog_' . date('Y-m-d_His') . '.csv;');
header('Content-Transfer-Encoding: binary');
$id_warehouse = (int) Tools::getValue('id_warehouse');
$area = (int) Tools::getValue('area');
$subarea = (int) Tools::getValue('subarea');
// GET COMBINATIONS
$combination = new DbQuery();
$select_combination = 'pa.id_product,
pa.id_product_attribute,
pa.reference,
pa.ean13,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.price as price_product,
pa.price as price_attribute,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
CASE pa.wholesale_price WHEN 0.000000 THEN p.wholesale_price ELSE pa.wholesale_price END as wholesale_price,
IFNULL( pa.weight, p.weight) as weight,
pl.description,
pl.description_short ';
// get product and product attribute of selected warehouse
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_combination .= ', wpl.location, z.name as area, sz.name as subarea';
$combination->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' ' . 'AND pa.id_product = wpl.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0)');
$combination->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$combination->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$combination->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$combination->where('z.id_erpip_zone = ' . (int) $area);
$combination->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$combination->select($select_combination);
$combination->from('product_attribute', 'pa');
$combination->innerjoin('product', 'p', 'pa.id_product = p.id_product');
$combination->innerjoin('product_lang', 'pl', 'pa.id_product = pl.id_product');
$combination->innerjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$combination->innerjoin('attribute', 'atr', 'atr.id_attribute = pac.id_attribute');
$combination->innerjoin('attribute_lang', 'al', 'al.id_attribute = pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('attribute_group_lang', 'agl', 'agl.id_attribute_group = atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id);
$combination->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
$combination->groupBy('pa.id_product, pa.id_product_attribute');
if ($this->controller_status == STATUS1) {
$combination->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$combinations = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($combination);
// list of product ids
$ids = array();
foreach ($combinations as $combination) {
$ids[] = (int) $combination['id_product'];
}
$ids = array_unique($ids);
// GET PRODUCT WITHOUT COMBINATIONS
$product = new DbQuery();
// Base query
$select_product = 'p.id_product,
p.reference,
p.ean13,
pl.name as name,
p.weight,
pl.description,
pl.description_short,
p.price as price_product,
p.id_tax_rules_group,
p.id_manufacturer,
cl.name as category,
p.wholesale_price as wholesale_price';
// warehouse query
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$select_product .= ', wpl.location, z.name as area, sz.name as subarea';
$product->innerjoin('warehouse_product_location', 'wpl', 'wpl.id_warehouse = ' . $id_warehouse . ' AND p.id_product = wpl.id_product AND wpl.id_product_attribute = 0');
$product->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$product->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$product->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
// filter on area
if ($area != null && $subarea == null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
$product->where('z.id_erpip_zone = ' . (int) $area);
$product->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$product->select($select_product);
$product->from('product', 'p');
$product->innerjoin('product_lang', 'pl', 'p.id_product = pl.id_product');
$product->innerjoin('category_lang', 'cl', 'cl.id_category = p.id_category_default AND cl.id_lang =' . (int) $this->context->language->id);
// if we have attributes we filter for not having a product already listed with attributes
if (count($ids) > 0) {
$product->where('p.id_product NOT IN (' . pSQL(implode(',', array_map('intval', $ids))) . ') ');
}
$product->groupBy('p.id_product');
if ($this->controller_status == STATUS1) {
$product->limit($stckmgtfr);
$this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $order_free_limit);
}
$products = Db::getInstance()->executeS($product);
// merge product with product attribute
$query = array_merge($products, $combinations);
if ($this->controller_status == STATUS1) {
$query = array_splice($query, 0, $stckmgtfr);
}
$nb_items = count($query);
for ($i = 0; $i < $nb_items; ++$i) {
$item =& $query[$i];
// gets stock manager
$manager = StockManagerFactory::getManager();
// id_product_attribute pour un produit sans déclinaisons
if (!isset($item['id_product_attribute'])) {
$item['id_product_attribute'] = 0;
}
// gets quantities and valuation
$stock = new DbQuery();
$stock->select('SUM(physical_quantity) as physical_quantity');
$stock->select('SUM(usable_quantity) as usable_quantity');
$stock->select('SUM(price_te * physical_quantity) as valuation');
$stock->from('stock');
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute'] . ' AND id_warehouse = ' . (int) $id_warehouse);
} else {
$stock->where('id_product =' . (int) $item['id_product'] . ' AND id_product_attribute = ' . (int) $item['id_product_attribute']);
}
$res_stock = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($stock);
$item['physical_quantity'] = $res_stock['physical_quantity'];
// real quantity
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], $id_warehouse, true);
} else {
$item['real_quantity'] = $manager->getProductRealQuantities($item['id_product'], $item['id_product_attribute'], null, true);
}
// price tax include and tax
$price = new DbQuery();
$price->select('rate');
$price->from('tax', 't');
$price->innerjoin('tax_rule', 'tr', 'tr.id_tax = t.id_tax');
$price->where('t.id_tax = ' . (int) $item['id_tax_rules_group']);
$price->where('tr.id_country = ' . (int) $this->context->country->id);
$res_price = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($price);
$item['rate'] = $res_price['rate'];
// if we are in product
if (!isset($item['price_attribute'])) {
$item['price_ttc'] = $item['price_product'] * ($res_price['rate'] / 100) + $item['price_product'];
} else {
$price = $item['price_product'] + $item['price_attribute'];
$item['price_ttc'] = $price * ($res_price['rate'] / 100) + $price;
}
// get manufacturer
$item['manufacturer'] = Manufacturer::getNameById($item['id_manufacturer']);
// get image product id
$id_image = Product::getCover((int) $item['id_product']);
// there is an image ?
if ($id_image != false) {
$image = new Image($id_image['id_image']);
$item['url_image'] = _PS_BASE_URL_ . _THEME_PROD_DIR_ . $image->getExistingImgPath() . ".jpg";
} else {
$item['url_image'] = $this->l('No image');
}
}
// BASE CSV HEADER
$header = array($this->l('PRODUCT_ID_'), $this->l('PRODUCT_ATTRIBUTE_ID'), $this->l('SKU'), $this->l('EAN13'), $this->l('MANUFACTURER'), $this->l('CATEGORY'), $this->l('PRODUCT_NAME'), $this->l('PRODUCT_WEIGHT'), $this->l('DESCRIPTION'), $this->l('DESCRIPTION_SHORT'), $this->l('URL_IMAGE'), $this->l('PHYSICAL_QTE'), $this->l('REAL_QTY'), $this->l('PURCHASE_PRICE'), $this->l('PRICES_TAX_EXCL'), $this->l('PRICES_TAX_INCL'), $this->l('VAT_RATE'));
// CSV WITH WAREHOUSE LOCATION
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($header, $this->l('AREA'));
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($header, $this->l('AREA'), $this->l('SUBAREA'));
}
array_push($header, $this->l('LOCATION'));
}
// Print header
echo implode(';', $header) . "\r\n";
// generate CSV file
foreach ($query as $product) {
if (!isset($product['price_attribute'])) {
$product['price_attribute'] = 0;
}
$content = array($product['id_product'], $product['id_product_attribute'], $product['reference'], $product['ean13'], $product['manufacturer'], self::transformText($product['category']), self::transformText($product['name']), $product['weight'], self::transformText($product['description']), self::transformText($product['description_short']), self::transformText($product['url_image']), $product['physical_quantity'], $product['real_quantity'], round($product['wholesale_price'], 2), round($product['price_product'] + $product['price_attribute'], 2), round($product['price_ttc'], 2), round($product['rate'], 2));
if (Tools::isSubmit('id_warehouse') && Tools::getValue('id_warehouse') != "-1") {
if ($area != null && $subarea == null) {
array_push($content, $product['area']);
}
// filter on area and sub area
if ($area != null && $subarea != null) {
array_push($content, $product['area'], $product['subarea']);
}
array_push($content, $product['location']);
}
echo implode(';', $content) . "\r\n";
}
echo sprintf($this->l('You are using the free version of 1-Click ERP which limits the export to %d products'), $stckmgtfr);
die;
}
}
protected function renderCSV()
{
if (Tools::isSubmit('export_csv')) {
/* GENERATION CSV */
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="inventory_report.csv"');
// write headers column
$keys = array('warehouse', 'id_product', 'id_product_attribute', 'SKU', 'supplier_reference', 'product_name', 'quantity_before', 'quantity_after', 'movement_reason', 'stock_gap');
echo sprintf("%s\n", implode(';', $keys));
$query = null;
$query = new DbQuery();
$query->select('p.id_product,
IF(pa.id_product_attribute, pa.reference, p.reference) as reference,
IFNULL(pa.id_product_attribute, 0) as id_product_attribute,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.id_product, IFNULL(pa.id_product_attribute, 0) as id_product_attribute, w.name as warehouse, ip.qte_before, ip.qte_after, smrl.name as reason, (qte_after - qte_before) as gap,
(
SELECT ps.product_supplier_reference
FROM ' . _DB_PREFIX_ . 'product_supplier ps
WHERE ps.id_product = ip.id_product
AND ps.id_product_attribute = ip.id_product_attribute
LIMIT 1
)as first_supplier_ref');
$query->from('erpip_inventory_product', 'ip');
$query->leftjoin('product', 'p', 'ip.id_product= p.id_product');
$query->leftjoin('product_attribute', 'pa', 'ip.id_product_attribute= pa.id_product_attribute');
$query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$query->leftjoin('attribute', 'atr', 'atr.id_attribute= pac.id_attribute');
$query->leftjoin('attribute_lang', 'al', '(al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('attribute_group_lang', 'agl', '(agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('product_lang', 'pl', '(p.id_product = pl.id_product AND pl.id_lang =' . (int) $this->context->language->id . ')');
$query->leftjoin('warehouse', 'w', 'w.id_warehouse = ip.id_warehouse');
$query->leftjoin('stock_mvt_reason_lang', 'smrl', '(smrl.id_stock_mvt_reason = ip.id_mvt_reason AND pl.id_lang =' . (int) $this->context->language->id . ')');
$query->where('id_erpip_inventory=' . (int) Tools::getValue('id_container'));
$query->groupBy('ip.id_product_attribute');
if ($this->controller_status == STATUS1) {
$query->limit(ERP_STCKMGTFR);
}
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// write datas
foreach ($res as $product) {
$content_csv = array($product['warehouse'], $product['id_product'], $product['id_product_attribute'], $product['reference'], $product['first_supplier_ref'], self::transformText($product['name']), $product['qte_before'], $product['qte_after'], $product['reason'], $product['gap'], PHP_EOL);
echo implode(';', $content_csv);
}
if ($this->controller_status == STATUS1) {
echo $this->l('You are using the free version of 1-Click ERP, which limits the display to 10 products. In order to remove the limit, switch to a higher version.');
}
die;
}
}
protected function renderCSV()
{
if (Tools::isSubmit('export_csv')) {
/* FILTRES */
// Category filter
$id_category = Tools::isSubmit('id_category') ? intval(Tools::getValue('id_category')) : -1;
$query = new DbQuery();
$query->select('id_product');
$query->from('category_product');
$query->where("id_category = {$id_category}");
$categories = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
$i = 0;
foreach ($categories as $category) {
$categories[$i] = $category['id_product'];
$i++;
}
$categories = implode(',', $categories);
$query = null;
// Provider filter
$id_supplier = Tools::isSubmit('id_supplier') ? Tools::getValue('id_supplier') : -1;
// Brand filter
$id_manufacturer = Tools::isSubmit('id_manufacturer') ? Tools::getValue('id_manufacturer') : -1;
// Location filter
//$area = (Tools::isSubmit('area')) ? Tools::getValue('area') : -1;
//$subarea = (Tools::isSubmit('subarea')) ? Tools::getValue('subarea') : -1;
/* GENERATION CSV */
// header
header('Content-type: text/csv; charset=utf-8');
header('Cache-Control: no-store, no-cache');
header('Content-disposition: attachment; filename="inventory_grid.csv"');
// Get product list
$query = null;
$query = new DbQuery();
$query->select('p.id_product,
IF(pa.id_product_attribute, pa.reference, p.reference) as reference,
p.ean13,
IFNULL(pa.id_product_attribute, 0) as id_product_attribute,
IFNULL(CONCAT(pl.name, \' : \', GROUP_CONCAT(DISTINCT agl.`name`, \' - \', al.name SEPARATOR \', \')),pl.name) as name,
p.id_product, IFNULL(pa.id_product_attribute, 0) as id_product_attribute');
$query->from('product', 'p');
$query->leftjoin('product_attribute', 'pa', 'p.id_product= pa.id_product');
$query->leftjoin('product_attribute_combination', 'pac', 'pac.id_product_attribute = pa.id_product_attribute');
$query->leftjoin('attribute', 'atr', 'atr.id_attribute= pac.id_attribute');
$query->leftjoin('attribute_lang', 'al', '(al.id_attribute= pac.id_attribute AND al.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('attribute_group_lang', 'agl', '(agl.id_attribute_group= atr.id_attribute_group AND agl.id_lang=' . (int) $this->context->language->id . ')');
$query->leftjoin('product_lang', 'pl', '(p.id_product = pl.id_product AND pl.id_lang =' . (int) $this->context->language->id . ')');
// Apply filters
if ($id_category != -1) {
$query->where('p.id_product IN(' . pSQL($categories) . ')');
}
if ($id_supplier != -1) {
$query->where('p.id_supplier = ' . (int) $id_supplier);
}
if ($id_manufacturer != -1) {
$query->where('p.id_manufacturer = ' . (int) $id_manufacturer);
}
$id_warehouse = $this->getCookie('id_warehouse');
// Apply warehouse filter, area, sub area only in active stock manager
if ($this->advanced_stock_management && $id_warehouse != -1) {
$query->select('wpl.location, wpl.id_warehouse, z.name as area, sz.name as subarea');
$query->leftjoin('warehouse_product_location', 'wpl', '(p.id_product = wpl.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0))');
$query->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
$query->leftjoin('erpip_zone', 'z', '(z.id_erpip_zone = ewpl.id_zone_parent)');
$query->leftjoin('erpip_zone', 'sz', '(sz.id_erpip_zone = ewpl.id_zone)');
$area = Tools::getValue('area') == null ? -1 : Tools::getValue('area');
$subarea = Tools::getValue('subarea') == null ? -1 : Tools::getValue('subarea');
// Warehouse filter
$query->where('wpl.id_warehouse = ' . (int) $id_warehouse);
// Area filter
if ($area != -1 && $subarea == -1) {
$query->where('z.id_erpip_zone = ' . (int) $area);
}
// area and sub area filter
if ($area != -1 && $subarea != -1) {
$query->where('z.id_erpip_zone = ' . (int) $area);
$query->where('sz.id_erpip_zone = ' . (int) $subarea);
}
}
$query->groupBy('pa.id_product_attribute, p.id_product');
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
// Get the physical quantity
$nb_items = count($products);
for ($i = 0; $i < $nb_items; ++$i) {
$item =& $products;
if ($this->advanced_stock_management) {
$query = new DbQuery();
$query->select('physical_quantity');
$query->from('stock');
$query->where('id_product = ' . (int) $item[$i]['id_product'] . ' AND id_product_attribute = ' . (int) $item[$i]['id_product_attribute'] . ' AND id_warehouse =' . (int) $item[$i]['id_warehouse']);
// Execute query
$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow($query);
} else {
$res['physical_quantity'] = (int) Product::getQuantity($item[$i]['id_product'], (int) $item[$i]['id_product_attribute']);
}
// add column to array
$item[$i]['physical_quantity'] = $res['physical_quantity'];
}
// write headers column
$keys = array('id_product', 'id_product_attribute', 'reference', 'ean', 'name', 'area', 'subarea', 'location', 'physical_quantity', 'found_quantity');
echo sprintf("%s\n", implode(';', $keys));
// write datas
foreach ($products as $product) {
if ($this->advanced_stock_management) {
// Location
$product['zone'] = $product['area'];
$product['sous_zone'] = $product['subarea'];
$product['location'] = $product['location'];
if (!StockAvailable::dependsOnStock((int) $product['id_product'])) {
continue;
}
} else {
if (StockAvailable::dependsOnStock((int) $product['id_product'])) {
continue;
}
}
// Still not have quantity, set 0
$physical_quantity = $product['physical_quantity'] == '' ? 0 : (int) $product['physical_quantity'];
$content_csv = array($product['id_product'], $product['id_product_attribute'], $product['reference'], $product['ean13'], self::transformText($product['name']));
// define optionnal array to set specific field to the advanced stock manager
$optional = array();
if ($this->advanced_stock_management) {
$optional = array(self::transformText($product['zone']), self::transformText($product['sous_zone']), self::transformText($product['location']));
}
$end = array($physical_quantity . '; ' . PHP_EOL);
// Merge $content_csv with advanced stock manager array
$content_csv = array_merge($content_csv, $optional, $end);
echo implode(';', $content_csv);
}
die;
}
}