Integration of the TecDoc database with the auto parts online store
- Tutorial
Writing an article was the result of working in a rather interesting project, the result of which should be an online store, with the possibility of linking the item to the TecDoc parts catalog .
The TecDoc product is a kind of database that includes not only the connections of spare parts manufacturers with specific catalog part numbers, but also contains images of goods, as well as a recommended price and, most importantly, the ability to search for analogues.
To link the site with the TecDoc catalog, its database had to be uploaded to MySQL. We will not dwell on this, since in the vastness of the World Wide Web this information can be found without problems.
This online store sells spare parts for cars.
The basis of the online store is CMS "HostCMS" . Since the customer had a license for this CMS, I had to work, so to speak, with what is. Although out of the box, this product can also please with its capabilities.
The main refinement of the online store is the ability to bind goods to the catalog - the TecDoc database, taking into account that the nomenclature can be downloaded both from the price list in * .xls format or manually - through the site control panel.
There are tools for the user to find goods in the store:
Now for more details on solving each of the problems.
The very first task was to download a new item from the generated price list.
Since the price list was in * .xls format, and there is no ready-made tool for working with this format in this CMS, it was decided to use “PHPExcel”
“PHPExcel” - a rather convenient tool that allows not only to read data from a file, but also to generate its files .
In order to make everything look beautiful, we created a module in the site control panel and displayed a link to it in the list of installed modules.
After clicking on the link, the administrator displays a form for downloading a file. If you look at this part from the programmer’s side, everything is standard, that is, a form is created, a file is received, renamed and transferred to the desired directory. It is advisable to add all kinds of checks like file type, protection against "XSS", etc.
In the form, the administrator selects the file that should be successfully uploaded to the server.
So the file is uploaded to the server. Now we have two options:
1. Run the entire file for processing. A good option, but if there are tens of thousands of records, the import process will not be controlled.
2. The second option, we used it.
2.1 After downloading the file, the number of filled lines is determined.
2.2 A script is generated that sends line numbers for processing
2.2.1 The import process is displayed as a percentage
2.2.2 The result of the import is displayed in the form of a table growing downwards during the import.
2.3 From the resulting table, the administrator can see the result and possible actions on each product.
For specifics, I give an example of products from the price list. When the script sends the line numbers to be processed, we will receive an array of properties for each product. That is, each line in the price list corresponds to a separate product, and for this product we draw out properties such as price, quantity, analogues of the TecDoc catalog, etc.
If you need to create a tool in which the price list fields can be changed, then you should write an identification attribute on the first line of each column (for example, 'V.numb', 'Name.manufacturer', etc.), and the second line will be the title for columns. As a result, import will have to start from the third line. And before importing, we need to determine the fields we need based on the identification attributes.
Our next steps are to link the internal number to the catalog products of the store and the TecDoc base.
1. Search in the catalog by internal number
2. If the product is found, we update its characteristics (such as prices and quantity)
3. If the product is not found in the store, do a search for “TecDoc”
3.1 If 1 product is found for this original number and its corresponding analogues, we proceed to “adding a new product”
3.2 If several products are found, display a link to “select one of the products”
3.3 If the product is not found, give a link to “select any product”
3.4 If there are several products or no products were found, then we modify the analogue records (deleting unnecessary characters or dividing into an array when there are several numbers in one field separated by the "/") symbol. If, after carrying out operations with the values, one item is found I - carry out “adding a new product”
4. “Adding a new product” - a new product is created in the catalog based on the fields in the price list, the missing properties of the product receive default values. The entry “Added” appears in the table of results.
5. “Select one of the products” - a dialog box, is the result of the script. The script is activated by clicking on the opposite of the product for which several items are found in "TecDoc". It accepts the found TecDoc item numbers, and allows you to choose the one you need from them:
6. “Select any product” - the dialog box is the result of the script. The script is activated by clicking on the opposite of the product for which no items have been found in “TecDoc” and allows you to select the desired part among all manufacturers:
Appearance of the module:
Import process:
All queries can be found using the search on the Internet.
The given queries were modified for the specialization of a particular task, as well as for optimizations. But much can be understood by looking at the following tasks:
Task 1. Searching for an image address
We add the path to the image catalog, do a check for existence, resize - if necessary, and you can safely display it on the site.
Task 2. Search for analogues:
This request can be used when importing goods, slightly modify it in order to check the conformity of this product and its analogues.
Task 3. Display product information
Do not forget about @LNG_ID - and put a value there, for example equal to 16, for the Russian language.
Catalog
The simplest option is when the visitor enters the catalog, he is presented with a list of all products, divided into pages. Here, I think, no questions arise. The programmer displays all the products in the catalog.
Filtering
As a filter, you can create a category tree, and allow the visitor to navigate in it. For example:
-Cars -
Bodywork
--- Body parts / fender / buffer
--- Glazing / mirrors
--- Covers / hoods / doors / movable roof / folding roof
--- Headlight / accessories
--- Lighting system / alarm
--Engine
--- Gaskets
--- Lubrication system
--- Cylinder head
--- Air supply system
--Sistema release
--- installation details
--- injection of urea
--- silencer assembly
--- Catalyst
--- Lambda Sensor
And now display only those parts catalog when selecting a filter, which are associated with this category, then we add filtering by category, thanks to the binding to the position from the "TecDoc". Updating the list of products in the catalog when choosing a filter can be done both dynamically using Ajax, and with page refresh.
The following is a query that displays a tree of subcategories for the category Cars (10001):
Since the request will be executed literally on every page of the catalog, it is advisable to add its result to the cache. And in general, bottlenecks that give a large load and delay should be studied.
The Caching module in this CMS may come in very handy. Although if you look around, then, probably, any framework will allow you to cache the most loaded places on the site.
Search by internal number
This section will not raise questions, since here we check the article of the product, and if it exists, display the page for this item. You can supplement this section with auto-completion when entering the article number for the user.
Search by original number
To solve this problem, we would seem to have everything. We can easily display all the products of the catalog, taking into account the original number or the number of its analogue, added to the additional properties of the goods.
But ...
The speed of such a request is rather low. Even using all kinds of optimizations for the MySQL server, the result is disappointing. Sphinx (search engine) has been
used more than once in projects , and always this technology only allowed to improve the project. And this time, when using Sphinx, we were surprised:
We adjusted the configuration file for the needs of the project, re-indexed it, and the search speed really increased, compared with the absence of Sphinx.
Search by keywords
Since the speed problem was with the original number, it was decided that the main search should also be done using the Sphinx technology.
In addition, this technology allows you to use:
By the way, at the beginning of the project, the “Your Car" tool was mentioned.
This tool allows you to select the only vehicle modification from the entire TecDoc catalog and view all products that are somehow related only to this car.
The tool is a set of fields that are updated dynamically depending on the choice of the previous field. For example, after choosing the year of production of the car, available brands of cars are selected, then we select the model and type of engine. The result of his work is shown below:
As a result, the main modifications of this car are displayed, among which we select the one we need. After choosing a car, the catalog will open, but your car will be the filter, and the site should remember the user's car and allow you to switch between them and choose a new car.
Despite the large entry threshold, the task was not difficult, but routine. In order to write this module, we can advise you to study the following approaches, technologies and tools:
And most importantly, a similar result can be achieved without the use of ready-made engines, but, for example, on your self-written catalog of cars or on-line store developed using frameworks.
PS As additions, I bring a couple of scripts from the project, which are at least inconclusive, but you can understand the essence of them.
The script responsible for splitting the process into requests is responsible for adding from the list, as well as informing about the import process:
The function of adding goods based on the part number and manufacturer name
PS To receive our new articles earlier than others or simply not to miss new publications - subscribe to us on Facebook , VK , Twitter , LiveJournal and LinkedIn
P.PS Very soon, a course will start in our Digitov business school: I want to become Junior PHP Developer! Subscribe to the course now and you can buy it at a discount.
Article prepared,
Vladimir, Middle PHP Developer, GK « SECL Group is »
Nikita, CEO, CC « SECL Group is »
The TecDoc product is a kind of database that includes not only the connections of spare parts manufacturers with specific catalog part numbers, but also contains images of goods, as well as a recommended price and, most importantly, the ability to search for analogues.
To link the site with the TecDoc catalog, its database had to be uploaded to MySQL. We will not dwell on this, since in the vastness of the World Wide Web this information can be found without problems.
This online store sells spare parts for cars.
The basis of the online store is CMS "HostCMS" . Since the customer had a license for this CMS, I had to work, so to speak, with what is. Although out of the box, this product can also please with its capabilities.
The main refinement of the online store is the ability to bind goods to the catalog - the TecDoc database, taking into account that the nomenclature can be downloaded both from the price list in * .xls format or manually - through the site control panel.
There are tools for the user to find goods in the store:
- By name and company,
- By the original number from "TecDoc",
- By internal number in the catalog of the online store,
- According to the tree of categories of parts,
- And most importantly, using the "Your Car" tool.
Now for more details on solving each of the problems.
Import of goods.
The very first task was to download a new item from the generated price list.
Since the price list was in * .xls format, and there is no ready-made tool for working with this format in this CMS, it was decided to use “PHPExcel”
“PHPExcel” - a rather convenient tool that allows not only to read data from a file, but also to generate its files .
In order to make everything look beautiful, we created a module in the site control panel and displayed a link to it in the list of installed modules.
After clicking on the link, the administrator displays a form for downloading a file. If you look at this part from the programmer’s side, everything is standard, that is, a form is created, a file is received, renamed and transferred to the desired directory. It is advisable to add all kinds of checks like file type, protection against "XSS", etc.
In the form, the administrator selects the file that should be successfully uploaded to the server.
So the file is uploaded to the server. Now we have two options:
1. Run the entire file for processing. A good option, but if there are tens of thousands of records, the import process will not be controlled.
2. The second option, we used it.
2.1 After downloading the file, the number of filled lines is determined.
2.2 A script is generated that sends line numbers for processing
2.2.1 The import process is displayed as a percentage
2.2.2 The result of the import is displayed in the form of a table growing downwards during the import.
2.3 From the resulting table, the administrator can see the result and possible actions on each product.
For specifics, I give an example of products from the price list. When the script sends the line numbers to be processed, we will receive an array of properties for each product. That is, each line in the price list corresponds to a separate product, and for this product we draw out properties such as price, quantity, analogues of the TecDoc catalog, etc.
If you need to create a tool in which the price list fields can be changed, then you should write an identification attribute on the first line of each column (for example, 'V.numb', 'Name.manufacturer', etc.), and the second line will be the title for columns. As a result, import will have to start from the third line. And before importing, we need to determine the fields we need based on the identification attributes.
Our next steps are to link the internal number to the catalog products of the store and the TecDoc base.
1. Search in the catalog by internal number
2. If the product is found, we update its characteristics (such as prices and quantity)
3. If the product is not found in the store, do a search for “TecDoc”
3.1 If 1 product is found for this original number and its corresponding analogues, we proceed to “adding a new product”
3.2 If several products are found, display a link to “select one of the products”
3.3 If the product is not found, give a link to “select any product”
3.4 If there are several products or no products were found, then we modify the analogue records (deleting unnecessary characters or dividing into an array when there are several numbers in one field separated by the "/") symbol. If, after carrying out operations with the values, one item is found I - carry out “adding a new product”
4. “Adding a new product” - a new product is created in the catalog based on the fields in the price list, the missing properties of the product receive default values. The entry “Added” appears in the table of results.
5. “Select one of the products” - a dialog box, is the result of the script. The script is activated by clicking on the opposite of the product for which several items are found in "TecDoc". It accepts the found TecDoc item numbers, and allows you to choose the one you need from them:
6. “Select any product” - the dialog box is the result of the script. The script is activated by clicking on the opposite of the product for which no items have been found in “TecDoc” and allows you to select the desired part among all manufacturers:
Appearance of the module:
Import process:
MySQL
All queries can be found using the search on the Internet.
The given queries were modified for the specialization of a particular task, as well as for optimizations. But much can be understood by looking at the following tasks:
Task 1. Searching for an image address
SELECT
CONCAT(GRA_TAB_NR, '/',GRA_GRD_ID, '.',
IF(LOWER(DOC_EXTENSION)='jp2', 'jpg', LOWER(DOC_EXTENSION))) ASPATHFROM
LINK_GRA_ART
INNERJOIN GRAPHICS ON GRA_ID = LGA_GRA_ID
INNERJOIN DOC_TYPES ON DOC_TYPE = GRA_DOC_TYPE
WHERE
LGA_ART_ID = @ARTICLE AND
(GRA_LNG_ID = 16OR GRA_LNG_ID = 255) AND
GRA_DOC_TYPE <> 2ORDERBY GRA_GRD_ID
We add the path to the image catalog, do a check for existence, resize - if necessary, and you can safely display it on the site.
Task 2. Search for analogues:
SELECT ARL_KIND,
IF (ART_LOOKUP.ARL_KIND = 2, SUPPLIERS.SUP_BRAND, BRANDS.BRA_BRAND) AS BRAND,
ARL_DISPLAY_NR
FROM
ART_LOOKUP
LEFTJOIN BRANDS ON BRA_ID = ARL_BRA_ID
INNERJOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
INNERJOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
WHERE
ARL_ART_ID = @ART_ID AND
ARL_KIND IN (2, 3, 4)
ORDERBY
ARL_KIND,
BRA_BRAND,
ARL_DISPLAY_NR
LIMIT100This request can be used when importing goods, slightly modify it in order to check the conformity of this product and its analogues.
Task 3. Display product information
SELECT
ART_ARTICLE_NR,
SUP_BRAND,
DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT,
DES_TEXTS2.TEX_TEXT AS ART_DES_TEXT,
DES_TEXTS3.TEX_TEXT AS ART_STATUS_TEXT
FROM
ARTICLES
INNERJOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID
AND DESIGNATIONS.DES_LNG_ID = @LNG_ID
INNERJOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFTJOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ART_DES_ID
AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID
LEFTJOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
INNERJOIN SUPPLIERS ON SUP_ID = ART_SUP_ID
INNERJOIN ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID
INNERJOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = ACS_KV_STATUS_DES_ID
AND DESIGNATIONS3.DES_LNG_ID = @LNG_ID
INNERJOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID
WHERE
ART_ID = @ART_ID;
Do not forget about @LNG_ID - and put a value there, for example equal to 16, for the Russian language.
Search for products.
Catalog
The simplest option is when the visitor enters the catalog, he is presented with a list of all products, divided into pages. Here, I think, no questions arise. The programmer displays all the products in the catalog.
Filtering
As a filter, you can create a category tree, and allow the visitor to navigate in it. For example:
-Cars -
Bodywork
--- Body parts / fender / buffer
--- Glazing / mirrors
--- Covers / hoods / doors / movable roof / folding roof
--- Headlight / accessories
--- Lighting system / alarm
--Engine
--- Gaskets
--- Lubrication system
--- Cylinder head
--- Air supply system
--Sistema release
--- installation details
--- injection of urea
--- silencer assembly
--- Catalyst
--- Lambda Sensor
And now display only those parts catalog when selecting a filter, which are associated with this category, then we add filtering by category, thanks to the binding to the position from the "TecDoc". Updating the list of products in the catalog when choosing a filter can be done both dynamically using Ajax, and with page refresh.
The following is a query that displays a tree of subcategories for the category Cars (10001):
SELECT
STR_ID, STR_ID_PARENT,
TEX_TEXT AS STR_DES_TEXT,
IF(EXISTS(
SELECT * FROM SEARCH_TREE AS SEARCH_TREE2
WHERE
SEARCH_TREE2.STR_ID_PARENT <=> SEARCH_TREE.STR_ID LIMIT 1
), 1, 0) AS DESCENDANTS
FROM
SEARCH_TREE
INNER JOIN DESIGNATIONS ON DES_ID = STR_DES_ID
INNER JOIN DES_TEXTS ON TEX_ID = DES_TEX_ID
WHERE
STR_ID_PARENT <=> 10001AND DES_LNG_ID = 16;
Since the request will be executed literally on every page of the catalog, it is advisable to add its result to the cache. And in general, bottlenecks that give a large load and delay should be studied.
The Caching module in this CMS may come in very handy. Although if you look around, then, probably, any framework will allow you to cache the most loaded places on the site.
Search by internal number
This section will not raise questions, since here we check the article of the product, and if it exists, display the page for this item. You can supplement this section with auto-completion when entering the article number for the user.
Search by original number
To solve this problem, we would seem to have everything. We can easily display all the products of the catalog, taking into account the original number or the number of its analogue, added to the additional properties of the goods.
But ...
The speed of such a request is rather low. Even using all kinds of optimizations for the MySQL server, the result is disappointing. Sphinx (search engine) has been
used more than once in projects , and always this technology only allowed to improve the project. And this time, when using Sphinx, we were surprised:
- Easy integration and customization.
- Indexing speed.
- And finally, search speed.
We adjusted the configuration file for the needs of the project, re-indexed it, and the search speed really increased, compared with the absence of Sphinx.
Search by keywords
Since the speed problem was with the original number, it was decided that the main search should also be done using the Sphinx technology.
In addition, this technology allows you to use:
- additional attributes when searching,
- distributed search
- Russian language support,
- creating multiple indexes.
By the way, at the beginning of the project, the “Your Car" tool was mentioned.
This tool allows you to select the only vehicle modification from the entire TecDoc catalog and view all products that are somehow related only to this car.
The tool is a set of fields that are updated dynamically depending on the choice of the previous field. For example, after choosing the year of production of the car, available brands of cars are selected, then we select the model and type of engine. The result of his work is shown below:
As a result, the main modifications of this car are displayed, among which we select the one we need. After choosing a car, the catalog will open, but your car will be the filter, and the site should remember the user's car and allow you to switch between them and choose a new car.
Summarize
Despite the large entry threshold, the task was not difficult, but routine. In order to write this module, we can advise you to study the following approaches, technologies and tools:
- OOP without it, it will be difficult ...
- The basis, so to speak, is the backbone of the site “MySQL” , “PHP” , “HTML” , “JS” , “CSS” .
- Understanding the principle of the CMS "HostCMS"
- Ability to work with the "API" of other developers
- Search for "Sphinx"
- The ability to explore the loaded places of the site and optimize the code
- Preferably jQuery or Prototype
- Well, of course, the truth is debatable, the ability to work with some kind of "Integrated Development Environment".
And most importantly, a similar result can be achieved without the use of ready-made engines, but, for example, on your self-written catalog of cars or on-line store developed using frameworks.
PS As additions, I bring a couple of scripts from the project, which are at least inconclusive, but you can understand the essence of them.
The script responsible for splitting the process into requests is responsible for adding from the list, as well as informing about the import process:
< script>
// knum1,knum2,knum3,knum4,knum5 - Номера аналогов// inum - Внутренний номер// pz,pr - Цены// lim - Лимит// wh1, wh2 - Количество на складе// count - Количество// art_id - ИД артикула//name - Название из прайса//num_fnd - Номера из tecDocvar from = 3;
var to = 3;
var percent = 0;
var chk = 0;
var cond = '';
functionget_list(fromf,tof){
$.post(
"/admin/import_excel_new/get_list.php",
{
from: fromf,
to: tof
},outputt
);
functionoutputt(data){
if(chk == 0)
{
from += 1;
to += 1;
percent = ( from / cond ) * 100;
}
else
{
from = cond;
percent = ( from / cond ) * 100;
}
if ((to - cond) > 0)
{
to = cond;
chk = 1;
get_list(from,to);
to = 0;
}elseif((to - cond) == 0)
{
chk = 1;
get_list(from,to);
to = 0;
}
document.getElementById("perc").innerHTML=percent.toFixed(2)+"%";
$("#progressbar").children(".ui-progressbar-value").css("width", percent+"%");
document.getElementById("tab_cont").innerHTML+=data;
if(chk == 1)
{
$("#progressbar").css("display", "none");
$("#progresspercent").css("display", "none");
}
if ( chk == 0)
{
get_list(from,to);
//setTimeout(get_list, 100, from, to);
}
}
}
alert("Не производите никаких действий, пока индикатор загрузки не дойдет до конца!");
get_list(from,to);
functioncreateWindow(count,art_id,inum,knum1,knum2,name,knum3,knum4,knum5,wh1,
wh2,pz,pr,num_fnd,lim){
$.post(
"/admin/import_excel_new/get_tab.php",
{
id: art_id,
num_fnd: num_fnd
},outputt2
);
functionoutputt2(data){
document.getElementById("inum").innerHTML=inum;
document.getElementById("cntnt").innerHTML=data;
document.getElementById("btn").innerHTML="<input type=\"button\" name=\"add\"
id=\"add_id\"
style=\"float:right; margin-top:5px;\"
value=\"Добавить\" onclick=\"insert_item(\'"+count+"\',
\'"+inum+"\',\'"+knum1+"\',\'"+knum2+"\',\'"+name+"\',
\'"+knum3+"\',\'"+knum4+"\',\'"+knum5+"\',\'"+wh1+"\',
\'"+wh2+"\',\'"+pz+"\',\'"+pr+"\',\'"+num_fnd+"\',
\'"+lim+"\')\" />";
}
document.getElementById("open_block").style.display="block";
}
functionloadNum(){
bnum = document.getElementById("brand_id").value;
$.post(
"/admin/import_excel_new/get_number.php",
{
num: bnum
},outputt11
);
functionoutputt11(data){
document.getElementById("list_num").innerHTML=data;
}
}
functioninsert_item(count,inum,knum1,knum2,name,knum3,knum4,knum5,wh1,wh2,pz,
pr,num_fnd,lim){
if (document.getElementById("opt_id_1").checked === true)
art_id=document.getElementById("art_list_id").value;
elseif (document.getElementById("opt_id_2").checked === true)
art_id=document.getElementById("txtnum_id").value;
if(document.getElementById("txtnum_id").value!="")
{
$.post(
"/admin/import_excel_new/insert_item.php",
{
count: count,
id: art_id,
inum: inum,
knum1: knum1,
knum2: knum2,
name: name,
knum3: knum3,
knum4: knum4,
knum5: knum5,
wh1: wh1,
wh2: wh2,
pz: pz,
pr: pr,
num_fnd: num_fnd,
lim: lim
},outputt3
);
functionoutputt3(data){
document.getElementById("id_"+inum).innerHTML="<span style=\'color:green;\'>Добавлен</span>";
document.getElementById("id_opt_"+inum).innerHTML="<a style=\"cursor:pointer;
text-decoration:underline; color:#0090ff;\" onclick=\"createWindow(\'1\',\'"+data+"\',
\'"+inum+"\',\'"+knum1+"\',\'"+knum2+"\',\'"+name+"\',\'"+knum3+"\',\'"+knum4+"\',\'"+knum5+"\',
\'"+wh1+"\',\'"+wh2+"\',\'"+pz+"\',\'"+pr+"\',\'"+num_fnd+"\',\'"+lim+"\')\">Изменить</a>";
}
}
document.getElementById(\'open_block\').style.display=\'none\';
}
</ script>
The function of adding goods based on the part number and manufacturer name
< ?php
// $DataBase - Инстанс адаптера БД// $knum1,$knum2,$knum3,$knum4,$knum5 - Номера аналогов// $inum - Внутренний номер// $pz,$pr - Цены// $user - Идентификатор пользователя// $date - Дата// $lim - Лимит// $wh1, $wh2 - Количество на складе// $nameProizvoditel, $numberProizvod - Название производителя, и номер деталиfunctionaddToCatalogForNoTecdoc($DataBase,$knum1,$knum2,$knum3,$knum4,
$knum5,$inum,$pr,$user,$date,$pz,$lim,$wh1,$wh2,$nameProizvoditel,$numberProizvod){
$symbols = array("`"," ", "~", "!", "@", "#", "$", "%",
"^", "&", "*", "(", ")", "-", "_", "=", "+", "/", "\\",
"|", "?", ":", ";", "\"", "'", "№", ",", ".");
$numberProizvodVithSymbols = str_replace($symbols, '', $numberProizvod);
$nameProizvoditelUpper=strtoupper($nameProizvoditel);
$query = "
SELECT DISTINCT
IF (ART_LOOKUP.ARL_KIND IN (3, 4), BRANDS.BRA_BRAND, SUPPLIERS.SUP_BRAND) AS brand,
ART_LOOKUP.ARL_SEARCH_NUMBER AS NUMBER,
`ARL_ART_ID` AS item_id,
`ARL_SEARCH_NUMBER` AS num_fnd,
`DES_TEXTS`.`TEX_TEXT` AS item_txt
FROM
ART_LOOKUP
LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID
INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ARTICLES.ART_COMPLETE_DES_ID
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
WHERE
ART_LOOKUP.ARL_SEARCH_NUMBER = '".$numberProizvodVithSymbols."' AND
ART_LOOKUP.ARL_KIND IN (1, 2, 3, 4) AND
DESIGNATIONS.DES_LNG_ID = 16
AND (BRANDS.BRA_BRAND='".$nameProizvoditelUpper."' OR
SUPPLIERS.SUP_BRAND='".$nameProizvoditelUpper."')
GROUP BY
brand,
NUMBER
;";
$result1 = $DataBase->query($query);
$row1 = mysql_fetch_array($result1);
echo'<div class="'.$inum.'" style="display: none;"><b>';
var_dump($query); echo'</b>';
echo'<pre>'; var_dump($row1); echo'</pre></div>';
$art_id=$row1["item_id"];
$txt = $row1['item_txt'];
$brand = $row1['brand'];
$rez=array();
if($row1){
$rez['status']=true;
}else{
$rez['status']=false;
}
if($rez['status']){
//товар
$DataBase->Insert('shop_items_catalog_table', array(
'shop_currency_id' => '4',
'shop_shops_id' => '3',
'shop_items_catalog_name' => $brand.': '.$txt,
'shop_items_catalog_marking' => $art_id,
'shop_vendorcode' => $knum1,
'shop_extension' => $inum,
'shop_items_catalog_price' => $pr,
'users_id' => $user,
'shop_items_catalog_date_time' => $date,
'shop_items_catalog_putoff_date' => $date,
'shop_items_catalog_weight'=>0
));
$last_id = mysql_insert_id();
//номераfunctionaddToMarking($DataBase,$marking_id, $vendor_id){
if(trim($vendor_id)!="" && $vendor_id!=" "){
$DataBase->Insert('marking', array('marking_id'
=> $marking_id, 'vendor_id' => $vendor_id));
}
}
addToMarking($DataBase,$art_id, $numberProizvod);
addToMarking($DataBase,$art_id, $knum1);
addToMarking($DataBase,$art_id, $knum2);
addToMarking($DataBase,$art_id, $knum3);
addToMarking($DataBase,$art_id, $knum4);
addToMarking($DataBase,$art_id, $knum5);
//складыfunctionaddToShop_warehouse_items_table($DataBase,$shop_warehouse_id,
$shop_items_catalog_item_id, $shop_warehouse_items_count, $users_id){
if(trim($vendor_id)!="" && $vendor_id!=" "){
$DataBase->Insert('shop_warehouse_items_table',
array(
'shop_warehouse_id' => $shop_warehouse_id,
'shop_items_catalog_item_id' => $shop_items_catalog_item_id,
'shop_warehouse_items_count' => $shop_warehouse_items_count,
'users_id' => $users_id
));
}
}
$whResult=0;
if(trim($wh1)!=""){
$whResult=$wh1;
}else{
$whResult=0;
}
addToShop_warehouse_items_table($DataBase,4,
$last_id, $whResult, $user);
if(trim($wh2)!=""){
$whResult=$wh2;
}else{
$whResult=0;
}
addToShop_warehouse_items_table($DataBase,3,
$last_id, $whResult, $user);
addToShop_warehouse_items_table($DataBase,5,
$last_id, 0, $user);
functionaddToShop_prices_to_item_table($DataBase,$last_id, $prices, $p){
$DataBase->Insert('shop_prices_to_item_table',
array('shop_items_catalog_item_id' => $last_id,
'shop_list_of_prices_id' => $prices, 'shop_prices_to_item_value' => $p));
}
//цены
addToShop_prices_to_item_table ($DataBase,$last_id, 4, $pz);
addToShop_prices_to_item_table ($DataBase,$last_id, 5, $pr);
//лимит
$DataBase->Insert('shop_properties_items_table',
array('shop_items_catalog_item_id' => $last_id,
'shop_list_of_properties_id' => 155,
'shop_properties_items_value' => $lim));
$param = array();
$param['item_id'] = $last_id;
$param['path'] = 'item_' . $last_id;
$DataBase->Update('shop_items_catalog_table',
array('shop_items_catalog_item_id' => $param['item_id'],
'shop_items_catalog_path' => $param['path'] ),
"
`shop_currency_id` = '4' and
`shop_shops_id` = '3' and
`shop_items_catalog_name` = '".$brand . ': ' . $txt."' and
`shop_items_catalog_marking` = ".$art_id ." and
`shop_vendorcode` = '".$knum1."' and
`shop_extension` = '".$inum."' and
`shop_items_catalog_price` = ".$pr."
"
);
//изобр
$img=getImgForARTid($DataBase,$art_id);
if($img)
{
$DataBase->Insert('shop_properties_items_table',
array('shop_items_catalog_item_id' => $last_id,
'shop_list_of_properties_id' => 164,
'shop_properties_items_value' => $img));
}
$img='';
}
$rez['str_item']=$art_id;
$rez['str_num_find']=$row1["num_fnd"];
return $rez;
}
?>PS To receive our new articles earlier than others or simply not to miss new publications - subscribe to us on Facebook , VK , Twitter , LiveJournal and LinkedIn
P.PS Very soon, a course will start in our Digitov business school: I want to become Junior PHP Developer! Subscribe to the course now and you can buy it at a discount.
Article prepared,
Vladimir, Middle PHP Developer, GK « SECL Group is »
Nikita, CEO, CC « SECL Group is »