En ARTICLES.sql
/* Detailed information display for a given non-original product (ART_ID) */
SET @ART_ID = 1806202; /* 202-134 [METZGER] - Brake master cylinder */ SET @LNG_ID = 16; /* 1 - German; 16 - Russian */
/* General information on the product */
/* ART_ARTICLE_NR - Article number */
/* SUP_BRAND - Product supplier name */
/* ART_COMPLETE_DES_TEXT - Product name */
/* ART_DES_TEXT - Product description */
/* ART_STATUS_TEXT - Product status (Normal, Not available...) */
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 INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNG_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ART_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID INNER JOIN ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID INNER JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = ACS_KV_STATUS_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNG_ID INNER JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID WHERE ART_ID = @ART_ID;
/* Criteria (Specification) */
/* CRITERIA_DES_TEXT - Criterion description */
/* CRITERIA_VALUE_TEXT - Criterion value */
SELECT DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT, IFNULL(DES_TEXTS2.TEX_TEXT, ACR_VALUE) AS CRITERIA_VALUE_TEXT FROM ARTICLE_CRITERIA LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACR_KV_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID LEFT JOIN CRITERIA ON CRI_ID = ACR_CRI_ID LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE ACR_ART_ID = @ART_ID AND (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = @LNG_ID) AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = @LNG_ID);
/* Additional information on the product*/
SELECT TMT_TEXT AS AIN_TMO_TEXT FROM ARTICLE_INFO INNER JOIN TEXT_MODULES ON TMO_ID = AIN_TMO_ID INNER JOIN TEXT_MODULE_TEXTS ON TMT_ID = TMO_TMT_ID WHERE AIN_ART_ID = @ART_ID AND TMO_LNG_ID = @LNG_ID ORDER BY AIN_TMO_TEXT ;
/* Analogs */
/* ARL_KIND - Analog kind : 2 - Commercial, 3 - Original, 4 - Non-original */
/* BRAND - Analog supplier name */
/* ARL_DISPLAY_NR - Analog number */
SELECT ARL_KIND, IF (ART_LOOKUP.ARL_KIND = 2, SUPPLIERS.SUP_BRAND, BRANDS.BRA_BRAND) AS BRAND, ARL_DISPLAY_NR FROM ART_LOOKUP LEFT JOIN BRANDS ON BRA_ID = 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 WHERE ARL_ART_ID = @ART_ID AND ARL_KIND IN (2, 3, 4) ORDER BY ARL_KIND, BRA_BRAND, ARL_DISPLAY_NR LIMIT 100;
/* Paths to picture files */
/* PATH - folder name/file name.extension */
SELECT CONCAT( 'images/', GRA_TAB_NR, '/', GRA_GRD_ID, '.', IF(LOWER(DOC_EXTENSION)='jp2', 'jpg', LOWER(DOC_EXTENSION)) ) AS PATH FROM LINK_GRA_ART INNER JOIN GRAPHICS ON GRA_ID = LGA_GRA_ID INNER JOIN DOC_TYPES ON DOC_TYPE = GRA_DOC_TYPE WHERE LGA_ART_ID = @ART_ID AND (GRA_LNG_ID = @LNG_ID OR GRA_LNG_ID = 255) AND GRA_DOC_TYPE <> 2 ORDER BY GRA_GRD_ID;
/* Paths to logo files of non-original product supplier */
SELECT CONCAT('images/logos/', SLO_ID, '.png') AS PATH
FROM ARTICLES
INNER JOIN SUPPLIER_LOGOS ON SLO_SUP_ID = ART_SUP_ID
WHERE ART_ID = @ART_ID;
/* Paths to PDF-files */
/* PATH - folder name/file name.extension */
SELECT CONCAT( 'pdf/', GRA_ID, LPAD(GRA_LNG_ID, 3, '0'), '.pdf' ) AS PATH FROM LINK_GRA_ART INNER JOIN GRAPHICS ON GRA_ID = LGA_GRA_ID WHERE LGA_ART_ID = @ART_ID AND (GRA_LNG_ID = @LNG_ID OR GRA_LNG_ID = 255) AND GRA_DOC_TYPE = 2 ORDER BY LGA_ART_ID, GRA_ID, GRA_LNG_ID;
/* Relevance to cars */
/* MFA_BRAND - Car brand */
/* MOD_CDS_TEXT - Car model */
/* TYP_CDS_TEXT - Car type name */
/* TYP_PCON_START - YYYYMM: Release Year/month of the type */
/* TYP_PCON_END - YYYYMM: Production termination Year/month of the type (NULL - unlimited) */
/* TYP_CCM - Engine capacity (cc) */
/* TYP_CCM_TAX - Engine capacity (cc) ("effective capacty"?) */
/* TYP_KW_FROM - Engine power (kW): FROM */
/* TYP_KW_UPTO - Engine power (kW): TO (NULL - unlimited) */
/* TYP_HP_FROM - Engine power (hp): FROM */
/* TYP_HP_UPTO - Engine power (hp): TO (NULL - unlimited) */
/* TYP_CYLINDERS - Number of cylinders */
/* ENG_CODE - Engine code */
/* TYP_ENGINE_DES_TEXT - Engine type */
/* TYP_FUEL_DES_TEXT - Fuel type */
/* TYP_BODY_DES_TEXT - Assembly type */
/* TYP_AXLE_DES_TEXT - Axle design (for trucks) */
/* TYP_MAX_WEIGHT - Tonnage (for trucks)*/
SELECT TYP_ID, MFA_BRAND, DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT, DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT, TYP_PCON_START, TYP_PCON_END, TYP_CCM, TYP_KW_FROM, TYP_KW_UPTO, TYP_HP_FROM, TYP_HP_UPTO, TYP_CYLINDERS, ENGINES.ENG_CODE, DES_TEXTS2.TEX_TEXT AS TYP_ENGINE_DES_TEXT, DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT, IFNULL(DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS TYP_BODY_DES_TEXT, DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT, TYP_MAX_WEIGHT FROM LINK_ART INNER JOIN LINK_LA_TYP ON LAT_LA_ID = LA_ID INNER JOIN TYPES ON TYP_ID = LAT_TYP_ID INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = TYP_KV_FUEL_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID LEFT JOIN ENGINES ON ENG_ID = LTE_ENG_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = TYP_KV_BODY_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = TYP_KV_MODEL_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = TYP_KV_AXLE_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID WHERE LA_ART_ID = @ART_ID AND COUNTRY_DESIGNATIONS.CDS_LNG_ID = @LNG_ID AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = @LNG_ID AND (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = @LNG_ID) AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = @LNG_ID) AND (DESIGNATIONS3.DES_LNG_ID IS NULL OR DESIGNATIONS3.DES_LNG_ID = @LNG_ID) AND (DESIGNATIONS4.DES_LNG_ID IS NULL OR DESIGNATIONS4.DES_LNG_ID = @LNG_ID) AND (DESIGNATIONS5.DES_LNG_ID IS NULL OR DESIGNATIONS5.DES_LNG_ID = @LNG_ID) ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT, TYP_PCON_START, TYP_CCM LIMIT 100;
/* Recommended prices */
/* PRI_PRICE - Price */
/* PRICE_UNIT_DES_TEXT - Packing unit (1 piece, 100 pieces...) */
/* QUANTITY_UNIT_DES_TEXT - Product unit of measurement (pieces, meters...)*/
/* PRI_CURRENCY_CODE - Currency */
SELECT PRI_PRICE, DES_TEXTS.TEX_TEXT AS PRICE_UNIT_DES_TEXT, DES_TEXTS2.TEX_TEXT AS QUANTITY_UNIT_DES_TEXT, PRI_CURRENCY_CODE FROM PRICES INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = PRI_KV_PRICE_UNIT_DES_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID INNER JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = PRI_KV_QUANTITY_UNIT_DES_ID INNER JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID WHERE PRI_ART_ID = @ART_ID AND DESIGNATIONS.DES_LNG_ID = @LNG_ID AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID;
- Back to index page: Tecdoc_queries_and_structure_manual_-_version_2
- Prices and packages for Irbis software based on tecdoc from Mstarproject company: http://www.mstarproject.com/en/?action=tecdoc_mysql_site
