File: /var/www/vhost/disk-apps/qas.sports-crowd.com/app/Http/Controllers/Imports/ProductsImport.php
<?php
namespace App\Http\Controllers\Imports;
use DB;
use App\Tag;
use App\Brand;
use App\Product;
use App\Sucursal;
use App\Attribute;
use App\Parameter;
use App\ProductImage;
use App\ProductAttribute;
use Illuminate\Support\Collection;
use App\Http\Controllers\ShopifyController;
use Maatwebsite\Excel\Concerns\ToCollection;
use App\Http\Controllers\ProductTagController;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use App\Http\Controllers\WoocommerceController;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
class ProductsImport implements ToCollection, WithHeadingRow, WithCalculatedFormulas
{
public $edit = [
'creados' => 0,
'editados' => 0,
'inactivados' => 0,
];
public $historyBrand = [];
public $request;
public $cnt;
public $messages = [];
public function collection(Collection $rows)
{
$this->cnt = 1;
DB::beginTransaction();
if (!isset($this->request->sucursal_id)) {
DB::table('product_attributes')->delete();
// Inactivar todos los productos, desactiva las fechas establecidas previamente para relampago y descuento, deja en 0 los descuentos de los productos.
DB::table('products')
->update([
'active' => false,
'flash_price' => null,
'start_flash_discount' => null,
'limit_flash_discount' => null,
'limit_hour_flash_discount' => null,
'percentage_discount' => null,
'start_discount' => null,
'limit_discount' => null,
'limit_hour_discount' => null,
]);
}
foreach ($rows as $row) {
//validaciones
if (!$row['marca'] || $row['marca'] == '') {
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag58', ['plu' => $row['plu']]));
break;
}
if (!$row['plu'] || $row['plu'] == '') {
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag59', ['position' => $this->cnt]));
break;
}
if (isset($this->request->sucursal_id)) {
$current_product = Product::with('brand')->where([['plu', $row['plu']], ['sucursal_id', $this->request->sucursal_id]])->first();
} else {
$current_product = Product::with('brand')->where('plu', $row['plu'])->first();
}
$parameters = Parameter::select('sync_shopify', 'sucursal_products')->find(1);
if ($parameters && $parameters->sync_shopify) {
if (isset($product_attributes) && count($product_attributes) && $current_product) {
$list = $product_attributes->where('product_id', $current_product->id);
foreach ($list as $attribute) {
$sho = new ShopifyController();
$sho->deleteVariant(
$attribute->reference_shopify_id,
$current_product->reference_shopify_id,
'COP'
);
}
}
}
$active = true;
if ($row['inactivar'] == 'x' || $row['inactivar'] == 'X' && $current_product) {
$active = false;
}
//Validación cantidades disponibles
$available_units_attributes = explode('|', $row['unidades_disponibles_separados_por_comas']);
foreach ($available_units_attributes as $key => $value) {
if (empty($value)) {
unset($available_units_attributes[$key]);
}
}
if (!empty($available_units_attributes)) {
$total = 0;
foreach ($available_units_attributes as $item) {
if ($item && $item != "") {
$units = explode(',', $item);
foreach ($units as $quantity) {
$total = $total + intval($quantity);
}
}
}
if ($total != intval($row['unidades_disponibles'])) {
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag61', ['plu' => $row['plu']]));
break;
}
}
// busca en el array si ya se consulto el id de la marca para no volver a consultar la bd
if (array_key_exists($row['marca'], $this->historyBrand)) {
$brand_id = trim($this->historyBrand[$row['marca']]);
} else {
$id = $this->getBrandId($row['marca']);
$this->historyBrand[$row['marca']] = $id;
$brand_id = $id;
}
if ($current_product) { // Existe, Actualizar
$current_product->active = $active;
$current_product->name = trim($row['nombre']);
$current_product->bar_code = trim($row['codigo_de_barras']);
$current_product->available_units = intval($row['unidades_disponibles']) == 0 || intval($row['unidades_disponibles']) == '' ? null : intval($row['unidades_disponibles']);
$current_product->max_units_per_order = intval($row['maximo_unidades_por_talla']) == 0 || intval($row['maximo_unidades_por_talla']) == '' ? null : intval($row['maximo_unidades_por_talla']);
$current_product->max_units_per_product = intval($row['maximo_unidades_por_producto']) == 0 || intval($row['maximo_unidades_por_producto']) == '' ? null : intval($row['maximo_unidades_por_producto']);
$current_product->price = floatval($row['precio']);
$current_product->flash_price = floatval($row['precio_relampago']);
$current_product->start_flash_discount = $row['inicio_precio_relampago'] == '0000-00-00' || $row['inicio_precio_relampago'] == '' ? null : trim($row['inicio_precio_relampago']);
$current_product->limit_flash_discount = $row['vigencia_precio_relampago'] == '0000-00-00' || $row['vigencia_precio_relampago'] == '' ? null : trim($row['vigencia_precio_relampago']);
$current_product->limit_hour_flash_discount = $row['hora_limite_relampago'] == '00:00:00' || $row['hora_limite_relampago'] == '' ? null : trim($row['hora_limite_relampago']);
$current_product->percentage_discount = intval($row['descuento']);
$current_product->start_discount = $row['inicio_descuento'] == '0000-00-00' || $row['inicio_descuento'] == '' ? null : trim($row['inicio_descuento']);
$current_product->limit_discount = $row['vigencia_descuento'] == '0000-00-00' || $row['vigencia_descuento'] == '' ? null : trim($row['vigencia_descuento']);
$current_product->limit_hour_discount = $row['hora_limite_descuento'] == '00:00:00' || $row['hora_limite_descuento'] == '' ? null : trim($row['hora_limite_descuento']);
$current_product->product_link = trim($row['enlace_del_producto']);
// busca en el array si ya se consulto el id de la marca para no volver a consultar la bd
if (array_key_exists($row['marca'], $this->historyBrand)) {
$current_product->brand_id = trim($this->historyBrand[$row['marca']]);
} else {
$id = $this->getBrandId($row['marca']);
$this->historyBrand[$row['marca']] = $id;
$current_product->brand_id = $id;
}
$current_product->order = intval($row['prioridad']);
$current_product->validate_age = $row['validacion_edad'] == "X" ? true : false;
if (isset($this->request->sucursal_id)) {
$current_product->sucursal_id = $this->request->sucursal_id;
} else {
$current_product->sucursal_id = null;
}
$current_product->update();
$woo = new WoocommerceController();
$attributeList = [];
$stockQuantity = [];
$skuList = [];
$attributeList = explode(',', $row['valores_separados_por_comas']);
$stockQuantity = explode(',', $row['unidades_disponibles_separados_por_comas']);
$skuList = explode(',', $row['sku_separados_por_comas']);
$reference_woo_attribute_id = 0;
$attribute_woo_id = Attribute::where('id', $row['atributos_separados_por'])->first();
$data = [
'name' => $attributeList,
];
if ($attribute_woo_id) {
$reference_woo_attribute_id = $attribute_woo_id->reference_woocommerce_attribute_id;
}
if (!$reference_woo_attribute_id) {
$reference_woo_attribute_id = 15;
}
$data = [
'name' => trim($row['nombre']),
'price' => floatval($row['precio']),
'type' => 'variable',
'manage_stock' => false,
'attributes' => [
[
'id' => $reference_woo_attribute_id,
'variation' => true,
'visible' => true,
'options' => $attributeList,
],
],
];
$woo->updateProduct($current_product->reference_woocommerce_product_id, $data);
$this->assignAttributes($current_product, $row, $current_product->reference_woocommerce_product_id);
$parameters = Parameter::select('sync_shopify', 'sucursal_products')->find(1);
if ($parameters && $parameters->sync_shopify) {
$sho = new ShopifyController();
$brand = Brand::where('id', $brand_id)->first();
$data_sho = [
"title" => trim($row['nombre']),
"vendor" => $brand->name,
"status" => "active",
];
$reference_shopify_id = $sho->updateProduct($current_product->reference_shopify_id, $data_sho, 'COP');
}
$this->assignProductTags($current_product->id, trim($row['segmentacion']), floatval($row['precio_segmentacion']));
$this->edit['editados'] += 1;
} else { // Crear
// busca en el array si ya se consulto el id de la marca para no volver a consultar la bd
if (array_key_exists($row['marca'], $this->historyBrand)) {
$brand_id = trim($this->historyBrand[$row['marca']]);
} else {
$id = $this->getBrandId($row['marca']);
$this->historyBrand[$row['marca']] = $id;
$brand_id = $id;
}
if (isset($this->request->sucursal_id)) {
$sucursal_id = $this->request->sucursal_id;
} else {
$sucursal_id = null;
}
$attributeList = [];
$stockQuantity = [];
$skuList = [];
$attributeList = explode(',', $row['valores_separados_por_comas']);
$stockQuantity = explode(',', $row['unidades_disponibles_separados_por_comas']);
$skuList = explode(',', $row['sku_separados_por_comas']);
$cont = 0;
$reference_woo_attribute_id = 0;
$attribute_woo_id = Attribute::where('id', $row['atributos_separados_por'])->first();
$data = [
'name' => $attributeList,
];
$woo = new WoocommerceController();
if ($attribute_woo_id) {
$reference_woo_attribute_id = $attribute_woo_id->reference_woocommerce_attribute_id;
}
if (!$reference_woo_attribute_id) {
$reference_woo_attribute_id = 15;
}
$data = [
'name' => trim($row['nombre']),
'type' => 'variable',
'manage_stock' => false,
'attributes' => [
[
'id' => intval($reference_woo_attribute_id),
'variation' => true,
'visible' => true,
'options' => $attributeList,
],
],
];
$reference_id = $woo->createProduct($data);
if ($reference_id) {
foreach ($attributeList as $index => $attribute) {
$skuWoo = $skuList[$index];
if ($attribute) {
$cont++;
$variation_data = [
'regular_price' => trim($row['precio']),
'manage_stock' => true,
'sku' => trim($skuWoo),
'attributes' => [
[
'id' => $reference_woo_attribute_id,
'option' => $attribute,
],
],
];
$variation_id = $woo->createProductVariation($reference_id, $variation_data);
}
$quantity = $stockQuantity[$index];
if (is_string($quantity)) {
$numQuantity = intval($quantity);
$inventory_management_woo = [
'stock_quantity' => $numQuantity,
];
}
$woo->updateProductVariation($reference_id, $variation_id, $inventory_management_woo);
}
}
$parameters = Parameter::select('sync_shopify', 'sucursal_products')->find(1);
if ($parameters && $parameters->sync_shopify) {
$sho = new ShopifyController();
$brand = Brand::where('id', $brand_id)->first();
$data_sho = [
"title" => trim($row['nombre']),
"vendor" => $brand->name,
"status" => "active",
];
$reference_shopify_id = $sho->createProduct($data_sho, 'COP');
}
$new_product = Product::create([
'active' => 1,
'name' => trim($row['nombre']),
'plu' => trim($row['plu']),
'bar_code' => trim($row['codigo_de_barras']),
'available_units' => intval($row['unidades_disponibles']) == 0 || intval($row['unidades_disponibles']) == '' ? null : intval($row['unidades_disponibles']),
'max_units_per_order' => intval($row['maximo_unidades_por_talla']) == 0 || intval($row['maximo_unidades_por_talla']) == '' ? null : intval($row['maximo_unidades_por_talla']),
'price' => floatval($row['precio']),
'flash_price' => floatval($row['precio_relampago']),
'start_flash_discount' => $row['inicio_precio_relampago'] == '0000-00-00' || $row['inicio_precio_relampago'] == '' ? null : trim($row['inicio_precio_relampago']),
'limit_flash_discount' => $row['vigencia_precio_relampago'] == '0000-00-00' || $row['vigencia_precio_relampago'] == '' ? null : trim($row['vigencia_precio_relampago']),
'limit_hour_flash_discount' => $row['hora_limite_relampago'] == '00:00:00' || $row['hora_limite_relampago'] == '' ? null : trim($row['hora_limite_relampago']),
'percentage_discount' => intval($row['descuento']),
'start_discount' => $row['inicio_descuento'] == '0000-00-00' || $row['inicio_descuento'] == '' ? null : trim($row['inicio_descuento']),
'limit_discount' => $row['vigencia_descuento'] == '0000-00-00' || $row['vigencia_descuento'] == '' ? null : trim($row['vigencia_descuento']),
'limit_hour_discount' => $row['hora_limite_descuento'] == '00:00:00' || $row['hora_limite_descuento'] == '' ? null : trim($row['hora_limite_descuento']),
'brand_id' => $brand_id,
'order' => intval($row['prioridad']),
'validate_age' => $row['validacion_edad'] == "X" ? true : false,
'sucursal_id' => $sucursal_id,
'reference_woocommerce_product_id' => $reference_id,
'reference_shopify_id' => $reference_shopify_id ?? null,
'product_link' => trim($row['enlace_del_producto']),
'store_type' => $this->request->storeType ?? null,
]);
$new_product->save();
$this->assignAttributes($new_product, $row, $reference_id);
$this->createImage($new_product->id, $new_product->plu);
$this->assignProductTags($new_product->id, trim($row['segmentacion']), floatval($row['precio_segmentacion']));
$this->edit['creados'] += 1;
}
$this->cnt++;
}
DB::commit();
}
public function assignAttributes($product, $row, $woo_product_id = null)
{
try {
$attributes = explode('|', $row['atributos_separados_por']);
$values = explode('|', $row['valores_separados_por_comas']);
$prices = explode('|', $row['precio_adicional_separados_por_comas']);
$skus = explode('|', $row['sku_separados_por_comas']);
$available_units = explode('|', $row['unidades_disponibles_separados_por_comas']);
$pmis = explode('|', $row['codigo_interno_pmi_separados_por_comas']);
$packagings = explode('|', $row['embalaje_separados_por_comas']);
$weights = explode('|', $row['peso_separados_por_comas']);
$eans = explode('|', $row['ean_separados_por_comas']);
$widths = explode('|', $row['ancho_separados_por_comas']);
$lengths = explode('|', $row['largo_separados_por_comas']);
$highs = explode('|', $row['alto_separados_por_comas']);
$observations = explode('|', $row['observacion_separados_por_comas']);
$main_positions = explode('|', $row['posicion_principal_separados_por_comas']);
$stowage_patterns = explode('|', $row['patron_estiba_separados_por_comas']);
$data_resp = "";
foreach ($attributes as $key => $attribute) {
if ($attribute && $attribute != "") {
if (
isset($values[$key]) && $values[$key] != "" &&
isset($prices[$key]) && $prices[$key] != "" &&
isset($skus[$key]) && $skus[$key] != "" &&
isset($available_units[$key]) && $available_units[$key] != "" &&
isset($pmis[$key]) && $pmis[$key] != "" &&
isset($packagings[$key]) && $packagings[$key] != "" &&
isset($weights[$key]) && $weights[$key] != "" &&
isset($eans[$key]) && $eans[$key] != "" &&
isset($widths[$key]) && $widths[$key] != "" &&
isset($lengths[$key]) && $lengths[$key] != "" &&
isset($highs[$key]) && $highs[$key] != "" &&
isset($observations[$key]) && $observations[$key] != "" &&
isset($main_positions[$key]) && $main_positions[$key] != "" &&
isset($stowage_patterns[$key]) && $stowage_patterns[$key] != ""
) {
$value = explode(',', $values[$key]);
$price = explode(',', $prices[$key]);
$sku = explode(',', $skus[$key]);
$available_unit = explode(',', $available_units[$key]);
$pmi = explode(',', $pmis[$key]);
$packaging = explode(',', $packagings[$key]);
$weight = explode(',', $weights[$key]);
$ean = explode(',', $eans[$key]);
$width = explode(',', $widths[$key]);
$length = explode(',', $lengths[$key]);
$high = explode(',', $highs[$key]);
$observation = explode(',', $observations[$key]);
$main_position = explode(',', $main_positions[$key]);
$stowage_pattern = explode(',', $stowage_patterns[$key]);
foreach ($value as $key1 => $val) {
if (isset($this->request->sucursal_id)) {
// Actualiza o crea el ProductAttribute cuando hay sucursal
$current_product_attribute = ProductAttribute::where([['product_id', $product->id], ['attribute_id', $attribute], ['value', $val]])->first();
if ($current_product_attribute) {
// Si el ProductAttribute ya esta, procede a actualizarlo
$current_product_attribute->sku = $sku[$key1];
$current_product_attribute->product_id = $product->id;
$current_product_attribute->attribute_id = $attribute;
$current_product_attribute->value = $val;
$current_product_attribute->price_additional = $price[$key1];
$current_product_attribute->available_units = $available_unit[$key1];
$current_product_attribute->pmi = $pmi[$key1];
$current_product_attribute->packaging = $packaging[$key1];
$current_product_attribute->weight = $weight[$key1];
$current_product_attribute->ean = $ean[$key1];
$current_product_attribute->width = $width[$key1];
$current_product_attribute->length = $length[$key1];
$current_product_attribute->high = $high[$key1];
$current_product_attribute->observation = $observation[$key1];
$current_product_attribute->main_position = $main_position[$key1];
$current_product_attribute->stowage_pattern = $stowage_pattern[$key1];
$current_product_attribute->update();
} else {
// Si el ProductAttrubute no se encuentra, procede a crearlo
$this->createNewProductAttribute($product, $attribute, $val, $sku[$key1], $price[$key1], $available_unit[$key1], $pmi[$key1], $packaging[$key1], $weight[$key1], $ean[$key1], $width[$key1], $length[$key1], $high[$key1], $observation[$key1], $main_position[$key1], $stowage_pattern[$key1]);
}
} else {
// Crea el ProductAttribute cuando no hay sucursal
$this->createNewProductAttribute($product, $attribute, $val, $sku[$key1], $price[$key1], $available_unit[$key1], $pmi[$key1], $packaging[$key1], $weight[$key1], $ean[$key1], $width[$key1], $length[$key1], $high[$key1], $observation[$key1], $main_position[$key1], $stowage_pattern[$key1]);
}
}
} else {
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag60', ['plu' => $row['plu'] . json_encode($data_resp)]));
break;
}
}
}
} catch (\Throwable $th) {
DB::rollback();
// throw new \Exception(trans('messages.screen_products_tag60', ['plu' => $row['plu'] . json_encode($data_resp)]));
throw new \Exception(trans('messages.screen_products_tag60', ['plu' => $row['plu'] . $th->getMessage()]));
}
}
public function createNewProductAttribute($product, $attribute, $value, $sku, $price, $available_unit, $pmi, $packaging, $weight, $ean, $width, $length, $high, $observation, $main_position, $stowage_pattern)
{
$allAttributeTerms = [];
$attributeTermsId = '';
$reference_id = '';
$reference_varation_id = null;
$attribute_woo = Attribute::where('id', $attribute)->first();
$woo = new WoocommerceController();
if ($attribute_woo) {
$allAttributeTerms = $woo->getAllAttributeTerm(intval($attribute_woo->reference_woocommerce_attribute_id));
if ($allAttributeTerms) {
foreach ($allAttributeTerms as $key => $allAttributeTerm) {
if ($allAttributeTerm->name === $value) {
$attributeTermsId = $allAttributeTerm->id;
}
}
}
if (!$attributeTermsId) {
$data = [
'name' => $value,
];
$reference_id = $woo->createAttributeTerm($attribute_woo->reference_woocommerce_attribute_id, $data);
$woo_product_id = $product->reference_woocommerce_product_id;
if ($reference_id) {
$data_pro = [
'attributes' => [
[
'id' => $attribute_woo->reference_woocommerce_attribute_id,
'option' => $value
]
]
];
$reference_varation_id = $woo->createProductVariation($woo_product_id, $data_pro);
if ($reference_varation_id) {
if (is_string($available_unit)) {
$numQuantity = intval($available_unit);
$inventory_management_woo = [
'stock_quantity' => $numQuantity,
];
$woo->updateProductVariation($product->reference_woocommerce_product_id, $reference_varation_id, $inventory_management_woo);
}
}
}
}
}
$finalPrice = $product->price + floatval($price);
$reference_shopify_variation_id = null;
$reference_shopify_id = null;
$parameters = Parameter::select('sync_shopify', 'sucursal_products')->find(1);
if ($parameters && $parameters->sync_shopify) {
$sho = new ShopifyController();
$data_sho = [
"option1" => $value,
"price" => $finalPrice,
"sku" => $sku,
"inventory_management" => "shopify",
];
$variant = $sho->createVariant($product->reference_shopify_id, $data_sho, 'COP');
if ($variant) {
$reference_shopify_variation_id = $variant->getInventoryItemId();
$reference_shopify_id = $variant->getId();
$sho->updateInventoryLevel($reference_shopify_variation_id, $available_unit, 'TI108', 'COP');
}
}
ProductAttribute::create([
'id' => $this->cnt,
'sku' => $sku,
'product_id' => $product->id,
'attribute_id' => $attribute,
'value' => $value,
'price_additional' => $price,
'available_units' => $available_unit,
'pmi' => $pmi,
'packaging' => $packaging,
'weight' => $weight,
'pum' => ($weight != '0' ? ROUND($finalPrice / ($weight ?? 1), 2) : null),
'ean' => $ean,
'width' => $width,
'length' => $length,
'high' => $high,
'observation' => $observation,
'main_position' => $main_position,
'stowage_pattern' => $stowage_pattern,
'reference_woocommerce_id' => $reference_id,
'reference_woocommerce_variation_id' => $reference_varation_id,
'reference_shopify_variation_id' => $reference_shopify_variation_id,
'reference_shopify_id' => $reference_shopify_id,
]);
}
public function createImage($product_id, $plu)
{
ProductImage::create([
"name" => $plu . ".jpg",
"product_id" => $product_id,
]);
}
public function getBrandId($name)
{
if ($brand = Brand::select('id')->where('name', '=', trim(ucfirst($name)))->first()) {
return $brand->id;
} elseif ($name != '') {
$brand = Brand::create([
'name' => trim(ucfirst($name)),
'active' => 1,
]);
return $brand->id;
}
return null;
}
public function getSucursalId($name)
{
if ($sucursal = Sucursal::select('id')->where('name', '=', trim($name))->first()) {
return $sucursal->id;
} elseif ($name != '') {
$sucursal = Sucursal::create([
'code' => trim($name),
'name' => trim($name),
'mail' => trim($name),
'phone' => trim($name),
'active' => 1,
'address_id' => 1,
'city_id' => 1,
'coverage_id' => 1,
]);
return $sucursal->id;
}
return null;
}
public function assignProductTags($productId, $segmentations, $specialPrice)
{
$items = array_map('trim', explode(',', $segmentations));
$tags = Tag::whereIn('name', $items)->pluck('id')->toArray();
$controller = new ProductTagController();
$controller->assignProductTags($productId, $tags, $specialPrice);
}
}