File: /var/www/vhost/disk-apps/agile-selling-wpb/app/Http/Controllers/Imports/ProductImports.php
<?php
namespace App\Http\Controllers\Imports;
use DB;
use App\Brand;
use App\Product;
use App\Sucursal;
use App\Attribute;
use App\ProductImage;
use App\ProductAttribute;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use App\Http\Controllers\ShopifyController;
use App\Http\Controllers\WoocommerceController;
class ProductImports implements ToCollection
{
public $edit = [
'creados' => 0,
'editados' => 0,
'inactivados' => 0,
];
public $historyBrand = [];
public $request;
public function collection(Collection $rows)
{
// Si el excel cargo correctamente eliminar todas los registros de subcategory_products si no esta activo productos por sucursal
if (!isset($this->request->sucursal_id)) {
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
$product_attributes = ProductAttribute::all();
ProductAttribute::truncate();
DB::rollBack();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}
DB::beginTransaction();
if (!isset($this->request->sucursal_id)) {
// 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,
]);
}
$cnt = 0;
foreach ($rows as $row){
if ($cnt > 0 && $row[0] != '') {
//validaciones
if(!$row[3] || $row[3] == ''){
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag58', ['plu' => $row[0]]));
break;
}
if (!$row[0] || $row[0] == '') {
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag59', ['position' => $cnt]));
break;
}
if (isset($this->request->sucursal_id)) {
$current_product = Product::with('brand')->where([['plu', $row[0]], ['sucursal_id', $this->request->sucursal_id]])->first();
} else {
$current_product = Product::with('brand')->where('plu', $row[0])->first();
}
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);
}
}
if ($row[17] == 'x' || $row[17] == 'X' && $current_product) {
$current_product->active = false;
$current_product->update();
$this->edit['editados'] += 1;
$sho = new ShopifyController();
$data_sho = [
"status" => 'archived',
];
$sho->updateProduct($current_product->reference_shopify_id,$data_sho);
} else { // Crear
//Validación cantidades disponibles
$available_units_attributes = explode('|', $row[22]);
$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[4])){
DB::rollback();
throw new \Exception(trans('messages.screen_products_tag61', ['plu' => $row[0]]));
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[3], $this->historyBrand)) {
$brand_id = trim($this->historyBrand[$row[3]]);
} else {
$id = $this->getBrandId($row[3]);
$this->historyBrand[$row[3]] = $id;
$brand_id = $id;
}
if ($current_product) { // Existe, Actualizar
$current_product->active = 1;
$current_product->name = trim($row[2]);
$current_product->bar_code = trim($row[1]);
$current_product->bin_location = trim($row[33]);
$current_product->available_units = intval($row[4]) == 0 || intval($row[4]) == '' ? null : intval($row[4]);
$current_product->max_units_per_order = intval($row[5]) == 0 || intval($row[5]) == '' ? null : intval($row[5]);
$current_product->price = intval($row[6]);
$current_product->flash_price = intval($row[7]);
$current_product->start_flash_discount = $row[8] == '0000-00-00' || $row[8] == '' ? null : trim($row[8]);
$current_product->limit_flash_discount = $row[9] == '0000-00-00' || $row[9] == '' ? null : trim($row[9]);
$current_product->limit_hour_flash_discount = $row[10] == '00:00:00' || $row[10] == '' ? null : trim($row[10]);
$current_product->percentage_discount = intval($row[11]);
$current_product->start_discount = $row[12] == '0000-00-00' || $row[12] == '' ? null : trim($row[12]);
$current_product->limit_discount = $row[13] == '0000-00-00' || $row[13] == '' ? null : trim($row[13]);
$current_product->limit_hour_discount = $row[14] == '00:00:00' || $row[14] == '' ? null : trim($row[14]);
// 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[3], $this->historyBrand)) {
$current_product->brand_id = trim($this->historyBrand[$row[3]]);
} else {
$id = $this->getBrandId($row[3]);
$this->historyBrand[$row[3]] = $id;
$current_product->brand_id = $id;
}
$current_product->order = intval($row[15]);
$current_product->validate_age = $row[16] == "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();
$data = [
'name' => trim($row[2]),
'price' => intval($row[6]),
];
$woo->updateProduct($current_product->reference_woocommerce_product_id,$data);
$this->assignAttributes($current_product,$row,$current_product->reference_woocommerce_product_id);
$sho = new ShopifyController();
$brand = Brand::where('id', $brand_id)->first();
$data_sho = [
"title" => trim($row[2]),
"vendor" => $brand->name,
"status" => "active",
];
$reference_shopify_id = $sho->updateProduct($current_product->reference_shopify_id,$data_sho);
$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[3], $this->historyBrand)) {
$brand_id = trim($this->historyBrand[$row[3]]);
} else {
$id = $this->getBrandId($row[3]);
$this->historyBrand[$row[3]] = $id;
$brand_id = $id;
}
if (isset($this->request->sucursal_id)) {
$sucursal_id = $this->request->sucursal_id;
} else {
$sucursal_id = null;
}
$woo = new WoocommerceController();
$data = [
'name' => trim($row[2]),
'price' => intval($row[6]),
];
$reference_id = $woo->createProduct($data);
$sho = new ShopifyController();
$brand = Brand::where('id', $brand_id)->first();
$data_sho = [
"title" => trim($row[2]),
"vendor" => $brand->name,
"status" => "active",
];
$reference_shopify_id = $sho->createProduct($data_sho);
$new_product = Product::create([
'active' => 1,
'name' => trim($row[2]),
'plu' => trim($row[0]),
'bar_code' => trim($row[1]),
'bin_location' => trim($row[33]),
'available_units' => intval($row[4]) == 0 || intval($row[4]) == '' ? null : intval($row[4]),
'max_units_per_order' => intval($row[5]) == 0 || intval($row[5]) == '' ? null : intval($row[5]),
'price' => intval($row[6]),
'flash_price' => intval($row[7]),
'start_flash_discount' => $row[8] == '0000-00-00' || $row[8] == '' ? null : trim($row[8]),
'limit_flash_discount' => $row[9] == '0000-00-00' || $row[9] == '' ? null : trim($row[9]),
'limit_hour_flash_discount' => $row[10] == '00:00:00' || $row[10] == '' ? null : trim($row[10]),
'percentage_discount' => intval($row[11]),
'start_discount' => $row[12] == '0000-00-00' || $row[12] == '' ? null : trim($row[12]),
'limit_discount' => $row[13] == '0000-00-00' || $row[13] == '' ? null : trim($row[13]),
'limit_hour_discount' => $row[14] == '00:00:00' || $row[14] == '' ? null : trim($row[14]),
'brand_id' => $brand_id,
'order' => intval($row[15]),
'validate_age' => $row[16] == "X" ? true : false,
'sucursal_id' => $sucursal_id,
'reference_woocommerce_product_id' => $reference_id,
'reference_shopify_id' => $reference_shopify_id,
]);
$new_product->save();
$this->assignAttributes($new_product,$row,$reference_id);
$this->createImage($new_product->id,$new_product->plu);
$this->edit['creados'] += 1;
}
}
}
$cnt++;
}
DB::commit();
}
public function assignAttributes($product,$row,$woo_product_id = null)
{
try {
$attributes = explode('|', $row[18]);
$values = explode('|', $row[19]);
$prices = explode('|', $row[20]);
$skus = explode('|', $row[21]);
$available_units = explode('|', $row[22]);
$pmis = explode('|', $row[23]);
$packagings = explode('|', $row[24]);
$weights = explode('|', $row[25]);
$eans = explode('|', $row[26]);
$widths = explode('|', $row[27]);
$lengths = explode('|', $row[28]);
$highs = explode('|', $row[29]);
$observations = explode('|', $row[30]);
$main_positions = explode('|', $row[31]);
$stowage_patterns = explode('|', $row[32]);
$data_resp = "";
// $data_resp = [$attributes,
// $values,
// $prices,
// $skus,
// $available_units,
// $pmis,
// $packagings,
// $weights,
// $eans,
// $widths,
// $lengths,
// $highs,
// $observations,
// $main_positions,
// $stowage_patterns
// ];
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[0] . json_encode($data_resp)]));
break;
}
}
}
} catch (\Throwable $th) {
DB::rollback();
// throw new \Exception(trans('messages.screen_products_tag60', ['plu' => $row[0] . json_encode($data_resp)]));
throw new \Exception(trans('messages.screen_products_tag60', ['plu' => $row[0] . $th->getMessage()]));
}
}
public function createNewProductAttribute($product,$attribute,$value,$sku,$price,$available_unit,$pmi,$packaging,$weight,$ean,$width,$length,$high,$observation,$main_position,$stowage_pattern){
$attribute_woo = Attribute::where('id', $attribute)->first();
$woo = new WoocommerceController();
$data = [
'name' => $value,
];
$reference_id = $woo->createAttributeTerm($attribute_woo->reference_woocommerce_attribute_id,$data);
$reference_varation_id = null;
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);
}
$sho = new ShopifyController();
$data_sho = [
"option1" => $value,
"price" => $product->price + intval($price),
"sku" => $sku,
"inventory_management" => "shopify",
];
$variant = $sho->createVariant($product->reference_shopify_id,$data_sho);
$reference_shopify_variation_id = null;
$reference_shopify_id = null;
if($variant){
$reference_shopify_variation_id = $variant->getInventoryItemId();
$reference_shopify_id = $variant->getId();
$sho->updateInventoryLevel($reference_shopify_variation_id,$available_unit);
}
ProductAttribute::create([
'sku' => $sku,
'product_id' => $product->id,
'attribute_id' => $attribute,
'value' => $value,
'price_additional' => $price,
'available_units' => $available_unit,
'pmi' => $pmi,
'packaging' => $packaging,
'weight' => $weight,
'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;
}
}