Jump to content

[RESOLU] | [SQL] : Ajouter un produit en SQL et l'afficher en front/back office

zouhairHajji
 Share

Recommended Posts

zouhairHajji Newbie

zouhairHajji

Posted
Posted (edited)

Bonjour, 
 
 Je me demande si on peut ajouter un produit juste en utilisant SQL, j'ai deja essayé plusieurs script en sql, je peut trouver le produit avec les autres produits au Back/Office, mais je ne peux pas ni le configurer, ni l'afficher sur le Front-office,

Quand je lance le script suivant pour savoir si mon produit existe deja dans la Bd 
 

SELECT DISTINCT
ps_product.id_product AS 'id',
ps_product.active AS 'Actif (0.1)',
ps_product_lang.name AS 'Name*',
'' AS 'Categories (x,y,z,...)',
ps_product.price AS 'Price tax excl.',
ps_product.id_tax_rules_group AS 'Tax rules id',
ps_product.wholesale_price AS 'Wholesale price',
ps_product.on_sale AS 'On sale (0/1)',
ps_specific_price.reduction AS 'Discount amount',
ps_specific_price.reduction AS 'Discount percent',
ps_specific_price.from AS 'Discount from (yyy-mm-dd)',
ps_specific_price.to AS 'Discount to (yyy-mm-dd)',
ps_product.reference AS 'Reference #',
ps_product.supplier_reference AS 'Supplier reference #',
ps_supplier.name AS 'Supplier',
ps_manufacturer.name AS 'Manufacturer',
ps_product.ean13 AS 'EAN13',
ps_product.upc AS 'UPC',
ps_product.ecotax AS 'Ecotax',
ps_product.weight AS 'Weight',
ps_product.quantity AS 'Quantity',
ps_product_lang.description_short AS 'Short description',
ps_product_lang.description AS 'Description',
ps_tag.name AS 'Tags (x,y,z,...)',
ps_product_lang.meta_title AS 'Meta-title',
ps_product_lang.meta_keywords AS 'Meta-keywords',
ps_product_lang.meta_description AS 'Meta-description',
ps_product_lang.link_rewrite AS 'URL rewritten',
ps_product_lang.available_now AS 'Text when in-stock',
ps_product_lang.available_later AS 'Text if back-order allowed',
ps_product.available_for_order AS 'Available for order',
ps_product.date_add AS 'Date add product',
ps_product.show_price AS 'Show price',
'' as 'Image URLs (x,y,z,...)',
ps_product.online_only AS 'Only available online',
0 as 'Delete existing images (0 = no, 1 = yes)',
ps_product.condition AS 'Condition'
FROM ps_product
LEFT JOIN ( ps_category_product, ps_product_lang, ps_manufacturer, ps_specific_price, ps_supplier, ps_product_tag, ps_tag)
ON (		   
ps_product.id_product = ps_category_product.id_product AND
ps_product.id_product = ps_product_lang.id_product AND
ps_product.id_manufacturer = ps_manufacturer.id_manufacturer AND
ps_product.id_product = ps_specific_price.id_product AND
ps_product.id_supplier = ps_supplier.id_supplier AND
ps_product_tag.id_product = ps_product_tag.id_product AND
ps_tag.id_tag = ps_tag.id_tag
)

 J’obtiens ceci 

post-985269-0-38768200-1432892490_thumb.jpg

et le back-office , j'ai ceci : 
 post-985269-0-56820600-1432892516_thumb.jpg

alors que quand je clique sur mon produit pour le paramétrer , ca m'envie ce message 
post-985269-0-39094500-1432892554_thumb.jpg
 
Je me demande si quelqu’un a un script SQL Qui permet d'ajouter un nouveau produit ( exemple produit teste ) 

Merci d'avance

Edited by zouhairHajji (see edit history)
Link to comment
Share on other sites
zouhairHajji Newbie

zouhairHajji

Posted
  • Author
Posted (edited)

Votre post que vous avez postez okom3pom m'a vraiment aidé, merci beaucoup, en effet je n'arrive pas a créer un produit avec son prix, quand je crée un produit prestashop lui attribue comme prix final 00.00, voici la requette que j'ai pu réalier : 

INSERT INTO `ps_product` (`id_shop_default`, `id_manufacturer`, `id_supplier`, `reference`, `supplier_reference`, `location`, `width`, `height`, `depth`, `weight`, `quantity_discount`, `ean13`, `upc`, `cache_is_pack`, `cache_has_attachments`, `is_virtual`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`) VALUES ("1", "0", "0", "reference", "", "", "0", "0", "0", "0", "0", "9999999999999", "", "0", "0", "0", "2", "1", "0", "0", "0", "1", "123457", "0", "", "0", "0", "0", "0", "0", "1", "404", "0", "1", "0000-00-00", "new", "1", "0", "both", "0", "0", "2015-06-01 11:15:23", "2015-06-01 11:15:23", "3"); 

INSERT INTO `ps_product_lang` (`id_product`, `meta_description`, `meta_keywords`, `meta_title`, `link_rewrite`, `name`, `description`, `description_short`, `available_now`, `available_later`, `id_lang`, `id_shop`) select max(a.id_product), "", "", "", "name", "name", "
reference

", "
résumé reference

", "", "", "1", "1" from ps_product a; 

INSERT INTO `ps_category_product` (`id_category`, `id_product`, `position`) select "2", max(a.id_product), "7" from ps_product a; 

INSERT INTO `ps_stock_available` (`out_of_stock`, `id_product`, `id_product_attribute`, `id_shop_group`, `id_shop`, `quantity`) select "2", max(a.id_product), "0", "0", "1", "10" from ps_product a; 

INSERT INTO `ps_product_shop` (`id_product`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`, `id_shop`) select max(a.id_product), "2", "1", "", "0", "0", "1", "", "", "", "0", "0", "0", "0", "0", "1", "404", "0", "1", "0000-00-00", "new", "1", "0", "both", "0", "0", "2015-06-01 11:15:23", "2015-06-01 11:15:23", "3", "1" from ps_product a;

le script est opérationnel, vous pouvez le tester sans problème. 

Edited by zouhairHajji (see edit history)
Link to comment
Share on other sites
zouhairHajji Newbie

zouhairHajji

Posted
  • Author
Posted

Merci beaucoup okom3pom .

Voici le script que j'ai réaliser : 

INSERT INTO `ps_product` (`id_shop_default`, `id_manufacturer`, `id_supplier`, `reference`, `supplier_reference`, `location`, `width`, `height`, `depth`, `weight`, `quantity_discount`, `ean13`, `upc`, `cache_is_pack`, `cache_has_attachments`, `is_virtual`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`) VALUES ("1", "0", "0", "reference", "", "", "0", "0", "0", "0", "0", "9999999999999", "", "0", "0", "0", "2", "1", "0", "0", "0", "1", "123457", "0", "", "0", "0", "0", "0", "0", "1", "404", "0", "1", "0000-00-00", "new", "1", "0", "both", "0", "0", "2015-06-01 12:06:27", "2015-06-01 12:06:27", "3"); 

INSERT INTO `ps_product_lang` (`id_product`, `meta_description`, `meta_keywords`, `meta_title`, `link_rewrite`, `name`, `description`, `description_short`, `available_now`, `available_later`, `id_lang`, `id_shop`) select max(a.id_product), "", "", "", "name", "name", "
reference

", "
résumé reference

", "", "", "1", "1" from ps_product a; 

INSERT INTO `ps_category_product` (`id_category`, `id_product`, `position`) select "2", max(a.id_product), "7" from ps_product a; 

INSERT INTO `ps_stock_available` (`out_of_stock`, `id_product`, `id_product_attribute`, `id_shop_group`, `id_shop`, `quantity`) select "2", max(a.id_product), "0", "0", "1", "10" from ps_product a; 

INSERT INTO `ps_product_shop` (`id_product`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`, `id_shop`) select max(a.id_product), "2", "1", "", "0", "0", "1", "", "0", "", "0", "0", "0", "0", "0", "1", "404", "0", "1", "0000-00-00", "new", "1", "0", "both", "0", "0", "2015-06-01 12:06:27", "2015-06-01 12:06:27", "3", "1" from ps_product a; 

insert into ps_specific_price_priority (id_product,priority) select max(a.id_product), "id_shop;id_currency;id_country;id_group" from ps_product a;

UPDATE `ps_product_shop` pps SET `price` = "123457" WHERE pps.`id_product` = (select max(id_product) from ps_product ) AND pps.`id_shop` = (select id_shop from ps_product where id_product = (select max(id_product) from ps_product ) );

le code php qui permet de générer ce script : 

 

	public function startSynchronisation(){
			
			$idTaxRule = '1';
			$quantity = '10';
			$name = 'name';
			$description = 'reference';
			$prixDeBase = '123457';
			
			$this->content .= $this->GenerateScript_Produit(
				array('reference' => 'reference',
					  'EAN' => '9999999999999',
					  'prixDeBase' => $prixDeBase,	
					  'idTaxRule' => $idTaxRule)
				);
				
			$this->content .= '<br><br>';
				$this->content .= $this->GenerateScript_product_lang(
					array('description' => $description,
						  'name' => $name)
					);
					
			$this->content .= '<br><br>';
				$this->content .= $this->GenerateScript_category_product();
				
			$this->content .= '<br><br>';	
				$this->content .= $this->GenerateScript_stock_available(
					array('quantity' => $quantity)	
					);
					
			$this->content .= '<br><br>';		
				$this->content .= $this->GenerateScript_product_shop(
					array('prixDeBase' => $prixDeBase,
						  'idTaxRule' => $idTaxRule)	
					);	
					
			$this->content .= '<br><br>';
				$this->content .= $this->GenerateScript_specific_price_priority();
				
			
			$this->content .= '<br><br>';	
				$this->content .= $this->GenerateScript_updatePrice(
					array('beginPrice' => $prixDeBase)	
					);
		}
			
			
		
		
		/**
			args( reference,EAN,prixDeBase,idTaxRule )
		*/
		public function GenerateScript_Produit($args){
			$res = '';
			$res .= 'INSERT INTO `'._DB_PREFIX_.'product` (`id_shop_default`, `id_manufacturer`, `id_supplier`, `reference`, `supplier_reference`, `location`, `width`, `height`, `depth`, `weight`, `quantity_discount`, `ean13`, `upc`, `cache_is_pack`, `cache_has_attachments`, `is_virtual`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`) VALUES 
				("1",
				 "0",
				 "0",
				 "'.$args['reference'].'",
				 "",
				 "",
				 "0",
				 "0",
				 "0",
				 "0",
				 "0",
				 "'.$args['EAN'].'",
				 "",
				 "0",
				 "0",
				 "0",
				 "2",
				 "'.$args['idTaxRule'].'",
				 "0",
				 "0",
				 "0",
				 "1",
				 "'.$args['prixDeBase'].'",
				 "0", 
				 "",
				 "0",
				 "0",
				 "0",
				 "0",
				 "0",
				 "1",
				 "404",
				 "0",
				 "1",
				 "0000-00-00",
				 "new",
				 "1",
				 "0",
				 "both",
				 "0",
				 "0",
				 "'.date("Y-m-d").' '.date("H:i:s").'",
				 "'.date("Y-m-d").' '.date("H:i:s").'",
				 "3");
			';
			return $res;
		}
		/**
			args( description, name, resumé = false )
		*/
		public function GenerateScript_product_lang($args){
			$res = '';
			$res .= 'INSERT INTO `ps_product_lang` (`id_product`, `meta_description`, `meta_keywords`, `meta_title`, `link_rewrite`, `name`, `description`, `description_short`, `available_now`, `available_later`, `id_lang`, `id_shop`)  
						select 
							max(a.id_product),
							"",
							"",
							"",
							"'.$args['name'].'",
							"'.$args['name'].'",
							"<p>'.$args['description'].'</p>",
							"<p>résumé '.$args['description'].'</p>",
							"",
							"",
							"1",
							"1"
						from ps_product a;
					';
			return $res;
		}
		
		public function GenerateScript_category_product(){
			$res = '';
			$res .= 'INSERT INTO `ps_category_product` (`id_category`, `id_product`, `position`) 
						select 
							 "2",
							 max(a.id_product),
							 "7"
						from ps_product a;
					';
			return $res;
		}
		/**
			args( quantity )
		*/
		public function GenerateScript_stock_available($args){
			$res = '';
			$res .= 'INSERT INTO `ps_stock_available` (`out_of_stock`, `id_product`, `id_product_attribute`, `id_shop_group`, `id_shop`, `quantity`) 
						select 
							"2",
						    max(a.id_product),
						    "0",
						    "0",
						    "1",
						    "'.$args['quantity'].'"
						from ps_product a;
					';
			return $res;
		}
		/**
			args( prixDeBase,idTaxRule )
		*/
		public function GenerateScript_product_shop($arrays){
			$res = '';
			$res .= 'INSERT INTO `ps_product_shop` (`id_product`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_product_redirected`, `available_for_order`, `available_date`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`, `id_shop`)  
				select
					max(a.id_product),
					 "2",
					 "1",
					 "'.$args['idTaxRule'].'",
					 "0",
					 "0",
					 "1",
					 "'.$args['prixDeBase'].'",
					 "0",
					 "",
					 "0",
					 "0",
					 "0",
					 "0",
					 "0",
					 "1",
					 "404",
					 "0",
					 "1",
					 "0000-00-00",
					 "new",
					 "1",
					 "0",
					 "both",
					 "0",
					 "0",
					 "'.date("Y-m-d").' '.date("H:i:s").'",
					 "'.date("Y-m-d").' '.date("H:i:s").'",
					 "3",
					 "1"
				from ps_product a;
			';
			return $res;
		}
		
		
		/**
			args( quantity )
		*/
		public function GenerateScript_specific_price_priority(){
			$res = '';
			$res .= 'insert into ps_specific_price_priority  (id_product,priority)
						select 
							max(a.id_product),
							"id_shop;id_currency;id_country;id_group"
						from ps_product a;';
			return $res;
		}
		
		/**
			args( beginPrice )
		*/
		public function GenerateScript_updatePrice($args){
			$res = '';
			$res .= 'UPDATE `ps_product_shop` pps 
						SET `price` = "'.$args['beginPrice'].'"
					WHERE 
						pps.`id_product` = (select max(id_product) from ps_product )  
					AND 
						pps.`id_shop` = (select id_shop from ps_product where id_product = 
											(select max(id_product) from ps_product )
										);';
			return $res;
		}
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...