SQL Queries for TecDOC

SqlQueryTemplates.cs

namespace GarantaParts.Engine.Import.TecDoc
{
    public class SqlQueryTemplates
    {
        public static string ArticleSearchTemplate = @"
SELECT DISTINCT
 TOF_ARTICLES.ART_ID,
 TOF_BRANDS.BRA_BRAND,
 TOF_SUPPLIERS.SUP_ID,
 TOF_SUPPLIERS.SUP_BRAND ,
 TOF_ART_LOOKUP.ARL_SEARCH_NUMBER ,
 TOF_ART_LOOKUP.ARL_KIND,
 TOF_ARTICLES.ART_ARTICLE_NR,
 TOF_BRANDS.BRA_MFC_CODE,
 TOF_DES_TEXTS.TEX_TEXT --,  -- VARCHAR(1200)
 -- TOF_ART_COUNTRY_SPECIFICS.ACS_KV_STATUS  -- VARCHAR(9)
 FROM TOF_ART_LOOKUP
 LEFT JOIN TOF_BRANDS ON TOF_BRANDS.BRA_ID = TOF_ART_LOOKUP.ARL_BRA_ID
 INNER JOIN TOF_ARTICLES ON TOF_ARTICLES.ART_ID = TOF_ART_LOOKUP.ARL_ART_ID
 INNER JOIN TOF_SUPPLIERS ON TOF_SUPPLIERS.SUP_ID = TOF_ARTICLES.ART_SUP_ID
 INNER JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = TOF_ARTICLES.ART_COMPLETE_DES_ID AND TOF_DESIGNATIONS.DES_LNG_ID = 16
 INNER JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID
 INNER JOIN TOF_ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID
 WHERE
 (TOF_ART_LOOKUP.ARL_SEARCH_NUMBER ='{1}') AND 
 (TOF_ART_LOOKUP.ARL_CTM subrange(186 cast integer) = 1) AND  
 (TOF_ARTICLES.ART_CTM subrange(186 cast integer) = 1) AND 
 (TOF_ART_LOOKUP.ARL_KIND IN ('{0}')) AND
 (TOF_DESIGNATIONS.DES_LNG_ID = 16) AND 
 ACS_KV_STATUS = '1'
  ORDER BY SUP_BRAND";

        public static string CheckGraphicsForArticle = @"
SELECT TOF_GRAPHICS.GRA_ID, TOF_GRAPHICS.GRA_DOC_TYPE, TOF_GRAPHICS.GRA_GRD_ID, TOF_GRAPHICS.GRA_TAB_NR, TOF_GRAPHICS.GRA_DES_ID 
FROM TOF_LINK_GRA_ART
LEFT JOIN TOF_GRAPHICS ON TOF_GRAPHICS.GRA_ID = TOF_LINK_GRA_ART.LGA_GRA_ID
WHERE
(TOF_LINK_GRA_ART.LGA_CTM SUBRANGE (186 CAST INTEGER) = 1) AND 
LGA_ART_ID = {0} AND TOF_GRAPHICS.GRA_DOC_TYPE <> 2";

        public static string GetGraphicsInfrormation = @"SELECT * FROM TOF_GRA_DATA_{0} WHERE GRD_ID = {1}";

        public static string GetAnalogueArticlesIds = @"
select distinct art_id,
ga_id, tof_brands.bra_brand, tof_brands.BRA_MFC_CODE, sup.sup_brand, tof_articles.art_article_nr, tof_des_texts.tex_text,arl_kind
from tof_art_lookup
join tof_articles
on arl_art_id nljoin art_id and
1 nljoin art_ctm subrange(186 cast integer)
join tof_suppliers sup on (sup.sup_id = tof_articles.art_sup_id)
join tof_designations des on (des.des_id = tof_articles.art_complete_des_id)
join tof_des_texts tex on (des.des_tex_id = tex.tex_id)
join tof_art_country_specifics on (acs_art_id = art_id)
left join tof_brands on tof_brands.bra_id = tof_art_lookup.arl_bra_id
 inner join tof_designations on tof_designations.des_id = tof_articles.art_complete_des_id and tof_designations.des_lng_id = 16
 inner join tof_des_texts on tof_des_texts.tex_id = tof_designations.des_tex_id
join tof_link_art_ga
on lag_art_id = art_id
join tof_generic_articles
on ga_id = lag_ga_id and
((ga_universal = 0 and
ga_id = ga_nr) or
ga_universal = 1)
join tof_designations ga_des
on ga_des_id nljoin ga_des.des_id and
ga_des.des_lng_id = 16
join tof_des_texts ga_tex
on ga_des.des_tex_id nljoin ga_tex.tex_id
left outer join tof_designations ga_assembly_des
on ga_des_id_assembly nljoin ga_assembly_des.des_id and
16 nljoin ga_assembly_des.des_lng_id
left outer join tof_des_texts ga_assembly_tex
on ga_assembly_des.des_tex_id nljoin ga_assembly_tex.tex_id
where arl_ctm subrange(186 cast integer) = 1 and
arl_kind in ('1','2','3','4','5') and
((0 = 1 and arl_search_number like '{0}' ) or ( 0 = 0 and arl_search_number = '{0}' ) ) and
(-1 = -1  or  ga_id = -1 ) and acs_kv_status = '1'
union all   
select distinct art_id,
ga_id, tof_brands.bra_brand, tof_brands.BRA_MFC_CODE, sup.sup_brand, tof_articles.art_article_nr, tof_des_texts.tex_text,arl_kind
from tof_tecsel_dealers
join tof_tecsel_prices
on tsd_id nljoin tsp_tsd_id and
tsp_ctm subrange(186 cast integer) = 1 and
( (0 = 1 and tsp_search_number like '{0}' ) or ( 0 = 0 and  tsp_search_number = '{0}' ) )
join tof_articles
on tsp_art_id nljoin art_id and
1 nljoin art_ctm subrange(186 cast integer)
join tof_art_lookup on tof_art_lookup.arl_art_id = tof_articles.art_id
join tof_suppliers sup on (sup.sup_id = tof_articles.art_sup_id)
join tof_designations des on (des.des_id = tof_articles.art_complete_des_id)
join tof_des_texts tex on (des.des_tex_id = tex.tex_id)
join tof_art_country_specifics on (acs_art_id = art_id)
left join tof_brands on tof_brands.bra_id = tof_art_lookup.arl_bra_id
 inner join tof_designations on tof_designations.des_id = tof_articles.art_complete_des_id and tof_designations.des_lng_id = 16
 inner join tof_des_texts on tof_des_texts.tex_id = tof_designations.des_tex_id
join tof_link_art_ga
on lag_art_id = art_id
join tof_generic_articles
on ga_id = lag_ga_id and
((ga_universal = 0 and
ga_id = ga_nr) or
ga_universal = 1)
join tof_designations ga_des
on ga_des_id nljoin ga_des.des_id and
ga_des.des_lng_id = 16
join tof_des_texts ga_tex
on ga_des.des_tex_id nljoin ga_tex.tex_id
left outer join tof_designations ga_assembly_des
on ga_des_id_assembly nljoin ga_assembly_des.des_id and
16 nljoin ga_assembly_des.des_lng_id
left outer join tof_des_texts ga_assembly_tex
on ga_assembly_des.des_tex_id nljoin ga_assembly_tex.tex_id
where tsd_id = -1 and
(-1 = -1  or  ga_id = -1 ) and acs_kv_status = '1'
order by art_id";

        public static string GetTecDocCarModelsQuery = @"
SELECT 
TOF_DES_TEXTS.TEX_TEXT,
TOF_MODELS.MOD_ID,
TOF_MODELS.MOD_PCON_START,
TOF_MODELS.MOD_PCON_END
FROM TOF_MODELS 
INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TOF_MODELS.MOD_CDS_ID 
INNER JOIN TOF_DES_TEXTS ON TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID = TOF_DES_TEXTS.TEX_ID 
WHERE TOF_COUNTRY_DESIGNATIONS.CDS_LNG_ID = 16 
AND TOF_MODELS.MOD_MFA_ID = {0} AND
(MOD_PC_CTM subrange(186 cast integer) = 1)
ORDER BY TOF_DES_TEXTS.TEX_TEXT
";

        public static string GetTecDocCarTypes = @"
        SELECT 
DISTINCT TOF_TYPES.TYP_ID,    
TOF_DES_TEXTS.TEX_TEXT AS TEX_TEXT, --Наименование модели полное если поле TYP_MMT_CDS_ID и краткое если поле TYP_CDS_ID
TOF_TYPES.TYP_MOD_ID,  --(Модель типа) ключ ссылающийся на таблицу TECDOC_TOF_MODELS=>MOD_ID
TOF_TYPES.TYP_PCON_START,   --первые 4 цифры: год начала выпуска модели, последние две месяц начала выпуска модели.
TOF_TYPES.TYP_PCON_END,   --первые 4 цифры: год окончания выпуска модели, последние две месяц окончания выпуска модели.
TOF_TYPES.TYP_KW_FROM,    --Техническая информация/Мощность двигателя(кВ) (от)
TOF_TYPES.TYP_KW_UPTO,    --Техническая информация/Мощность двигателя(кВ) (до)
TOF_TYPES.TYP_HP_FROM,   --Техническая информация/Мощность двигателя (ЛС) (от)
TOF_TYPES.TYP_HP_UPTO,   --Техническая информация/Мощность двигателя (ЛС) (до)
TOF_TYPES.TYP_LITRES,  -- NUMERIC(6,3)
TOF_TYPES.TYP_CCM,       --Техническая информация/Тех. Объем куб. см.
TYP_CYLINDERS, -- SMALLINT
TOF_DES_TEXTS5.TEX_TEXT AS ENGINE_TYPE, --Вид двигателя
TOF_DES_TEXTS7.TEX_TEXT AS FUEL_TYPE, --вид топлива
TOF_DES_TEXTS8.TEX_TEXT AS CATALYST_TYPE, --вид катализатора
TOF_DES_TEXTS10.TEX_TEXT AS SUPPLY_FUEL_TYPE --запправка горючего
FROM TOF_TYPES 
    INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TOF_TYPES.TYP_MMT_CDS_ID 
    INNER JOIN TOF_DES_TEXTS ON TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID = TOF_DES_TEXTS.TEX_ID

    LEFT JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS5 ON TOF_DESIGNATIONS5.DES_ID = TOF_TYPES.TYP_KV_ENGINE_DES_ID AND TOF_DESIGNATIONS5.DES_LNG_ID = 16   --Вид двигателя
    LEFT JOIN TOF_DES_TEXTS TOF_DES_TEXTS5 ON TOF_DES_TEXTS5.TEX_ID = TOF_DESIGNATIONS5.DES_TEX_ID 

    LEFT JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS7 ON TOF_DESIGNATIONS7.DES_ID = TOF_TYPES.TYP_KV_FUEL_DES_ID AND TOF_DESIGNATIONS7.DES_LNG_ID = 16   --вид топлива
    LEFT JOIN TOF_DES_TEXTS TOF_DES_TEXTS7 ON TOF_DES_TEXTS7.TEX_ID = TOF_DESIGNATIONS7.DES_TEX_ID 

    LEFT JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS8 ON TOF_DESIGNATIONS8.DES_ID = TOF_TYPES.TYP_KV_CATALYST_DES_ID AND TOF_DESIGNATIONS8.DES_LNG_ID = 16   --вид катализатора
    LEFT JOIN TOF_DES_TEXTS TOF_DES_TEXTS8 ON TOF_DES_TEXTS8.TEX_ID = TOF_DESIGNATIONS8.DES_TEX_ID    

    LEFT JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS9 ON TOF_DESIGNATIONS9.DES_ID = TOF_TYPES.TYP_KV_BODY_DES_ID AND TOF_DESIGNATIONS9.DES_LNG_ID = 16   --вид конструкции
    LEFT JOIN TOF_DES_TEXTS TOF_DES_TEXTS9 ON TOF_DES_TEXTS9.TEX_ID = TOF_DESIGNATIONS9.DES_TEX_ID 

    LEFT JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS10 ON TOF_DESIGNATIONS10.DES_ID = TOF_TYPES.TYP_KV_FUEL_SUPPLY_DES_ID AND TOF_DESIGNATIONS10.DES_LNG_ID = 16    --запправка горючего
    LEFT JOIN TOF_DES_TEXTS TOF_DES_TEXTS10 ON TOF_DES_TEXTS10.TEX_ID = TOF_DESIGNATIONS10.DES_TEX_ID   
WHERE 
TOF_TYPES.TYP_MOD_ID = {0} AND
TOF_COUNTRY_DESIGNATIONS.CDS_LNG_ID = 16
AND TOF_TYPES.TYP_CTM subrange(186 cast integer) = 1 
ORDER BY TOF_TYPES.TYP_ID
";

        public static string GetTecDocLinkedTable = @"SELECT STR_ID, LAT_TYP_ID, ART_ID, ART_ARTICLE_NR, ARL_SEARCH_NUMBER, ARL_DISPLAY_NR, TOF_DES_TEXTS.TEX_TEXT, DES_TEXT2.TEX_TEXT DES2
FROM          TOF_LINK_GA_STR
 JOIN TOF_LINK_LA_TYP ON LAT_GA_ID = LGS_GA_ID AND LAT_TYP_ID = {0}
 JOIN TOF_LINK_ART ON LA_ID = LAT_LA_ID
 JOIN TOF_SEARCH_TREE ON STR_ID = LGS_STR_ID
 JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = STR_DES_ID AND TOF_DESIGNATIONS.DES_LNG_ID = 16
 JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID
 JOIN TOF_ARTICLES ON ART_ID = LA_ART_ID
 JOIN TOF_ART_LOOKUP ON ARL_ART_ID = ART_ID
 JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS2 ON TOF_DESIGNATIONS2.DES_ID = TOF_ARTICLES.ART_COMPLETE_DES_ID AND TOF_DESIGNATIONS.DES_LNG_ID = 16
 JOIN TOF_DES_TEXTS DES_TEXT2 ON DES_TEXT2.TEX_ID = TOF_DESIGNATIONS2.DES_TEX_ID
 JOIN TOF_ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID
 WHERE TOF_LINK_LA_TYP.LAT_CTM subrange(186 cast integer) = 1 AND 
TOF_LINK_ART.LA_CTM  subrange(186 cast integer) = 1 AND 
 (TOF_ART_LOOKUP.ARL_CTM subrange(186 cast integer) = 1) AND  
 (TOF_ARTICLES.ART_CTM subrange(186 cast integer) = 1) AND TOF_DESIGNATIONS2.DES_LNG_ID = 16 AND ACS_KV_STATUS = '1'";

        public static string GetTecDocLinkedTableBySearchNumber = @"
    SELECT DISTINCT
 STR_ID, LAT_TYP_ID, ART_ID, ART_ARTICLE_NR, ARL_SEARCH_NUMBER, ARL_DISPLAY_NR, TOF_DES_TEXTS.TEX_TEXT, DES_TEXT2.TEX_TEXT DES2
 FROM TOF_ART_LOOKUP
 JOIN TOF_ARTICLES ON TOF_ARTICLES.ART_ID = TOF_ART_LOOKUP.ARL_ART_ID
 JOIN TOF_ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID
 JOIN TOF_LINK_ART ON LA_ART_ID = ART_ID
 JOIN TOF_LINK_LA_TYP ON LAT_LA_ID = LA_ID
 JOIN TOF_LINK_GA_STR ON LAT_GA_ID = LGS_GA_ID 
 JOIN TOF_SEARCH_TREE ON STR_ID = LGS_STR_ID
 JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = STR_DES_ID AND TOF_DESIGNATIONS.DES_LNG_ID = 16
 JOIN TOF_DES_TEXTS ON TOF_DES_TEXTS.TEX_ID = TOF_DESIGNATIONS.DES_TEX_ID
 JOIN TOF_DESIGNATIONS TOF_DESIGNATIONS2 ON TOF_DESIGNATIONS2.DES_ID = TOF_ARTICLES.ART_COMPLETE_DES_ID AND TOF_DESIGNATIONS2.DES_LNG_ID = 16
 JOIN TOF_DES_TEXTS DES_TEXT2 ON DES_TEXT2.TEX_ID = TOF_DESIGNATIONS2.DES_TEX_ID
 WHERE
 (TOF_ART_LOOKUP.ARL_SEARCH_NUMBER ='0') AND 
 (TOF_ART_LOOKUP.ARL_CTM subrange(186 cast integer) = 1) AND  
 (TOF_ARTICLES.ART_CTM subrange(186 cast integer) = 1) AND 
 (TOF_DESIGNATIONS.DES_LNG_ID = 16) AND 
 ACS_KV_STATUS = '1'
  ORDER BY ART_ID, LAT_TYP_ID
";

        public static string GetEnginesByModuleId = @"SELECT 
DISTINCT TOF_ENGINES.ENG_ID, 
TOF_MODELS.MOD_ID,
TOF_ENGINES.ENG_CODE,
TOF_ENGINES.ENG_MFA_ID,
TOF_ENGINES.ENG_PCON_START,
TOF_ENGINES.ENG_PCON_END,
TOF_ENGINES.ENG_KW_FROM,
TOF_ENGINES.ENG_KW_UPTO,
TOF_ENGINES.ENG_HP_FROM,
TOF_ENGINES.ENG_HP_UPTO,
TOF_ENGINES.ENG_VALVES,
TOF_ENGINES.ENG_CYLINDERS,
TOF_ENGINES.ENG_CCM_FROM,
TOF_ENGINES.ENG_CCM_UPTO,
KV_DESIGN_TEXTS.TEX_TEXT KV_DESIGN_DES,
KV_FUEL_TYPE_TEXTS.TEX_TEXT KV_FUEL_TYPE_DES,
KV_FUEL_SUPPLY_TEXTS.TEX_TEXT KV_FUEL_SUPPLY_DES,
TOF_ENGINES.ENG_DESCRIPTION,  -- VARCHAR(90)
KV_ENGINE_TEXTS.TEX_TEXT KV_ENGINE_TEXTS_DES,
TOF_ENGINES.ENG_KW_RPM_FROM, -- rpm
TOF_ENGINES.ENG_KW_RPM_UPTO,
TOF_ENGINES.ENG_COMPRESSION_FROM,  -- NUMERIC(6,3)
TOF_ENGINES.ENG_COMPRESSION_UPTO,  -- NUMERIC(6,3)
TOF_ENGINES.ENG_DRILLING,  -- NUMERIC(6,3)
TOF_ENGINES.ENG_EXTENSION,  -- NUMERIC(6,3)
TOF_ENGINES.ENG_CRANKSHAFT,  -- SMALLINT
KV_CHARGE_TEXTS.TEX_TEXT KV_CHARGE_DES,
KV_CYLINDERS_TEXTS.TEX_TEXT KV_CYLINDERS_DES,
KV_CONTROL_TEXTS.TEX_TEXT KV_CONTROL_DES,
KV_VALVE_CONTROL_TEXTS.TEX_TEXT KV_VALVE_CONTROL_DES,
KV_COOLING_TEXTS.TEX_TEXT KV_COOLING_DES,
KV_USE_TEXTS.TEX_TEXT KV_USE_DES,
---
TOF_ENGINES.ENG_LITRES_FROM,  -- NUMERIC(6,3)
TOF_ENGINES.ENG_LITRES_UPTO  -- NUMERIC(6,3)
FROM TOF_ENGINES 
-- INNER JOIN TOF_MANUFACTURERS ON TOF_MANUFACTURERS.MFA_ID = TOF_ENGINES.ENG_MFA_ID
INNER JOIN TOF_MODELS ON TOF_MODELS.MOD_MFA_ID = TOF_ENGINES.ENG_MFA_ID
-- INNER JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID = TOF_ENGINES.ENG_KV_DESIGN_DES_ID
-- INNER JOIN TOF_DES_TEXTS ON TOF_DESIGNATIONS.DES_TEX_ID = TOF_DES_TEXTS.TEX_ID 
-- DESCRIPTIONS:
LEFT JOIN TOF_DESIGNATIONS KV_DESIGN ON KV_DESIGN.DES_ID = TOF_ENGINES.ENG_KV_DESIGN_DES_ID AND KV_DESIGN.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_DESIGN_TEXTS ON KV_DESIGN.DES_TEX_ID = KV_DESIGN_TEXTS.TEX_ID

LEFT JOIN TOF_DESIGNATIONS KV_FUEL_TYPE ON KV_FUEL_TYPE.DES_ID = TOF_ENGINES.ENG_KV_FUEL_TYPE_DES_ID AND KV_FUEL_TYPE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_FUEL_TYPE_TEXTS ON KV_FUEL_TYPE.DES_TEX_ID = KV_FUEL_TYPE_TEXTS.TEX_ID

LEFT JOIN TOF_DESIGNATIONS KV_FUEL_SUPPLY ON KV_FUEL_SUPPLY.DES_ID = TOF_ENGINES.ENG_KV_FUEL_SUPPLY_DES_ID AND KV_FUEL_SUPPLY.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_FUEL_SUPPLY_TEXTS ON KV_FUEL_SUPPLY.DES_TEX_ID = KV_FUEL_SUPPLY_TEXTS.TEX_ID

LEFT JOIN TOF_DESIGNATIONS KV_ENGINE ON KV_ENGINE.DES_ID = TOF_ENGINES.ENG_KV_ENGINE_DES_ID AND KV_ENGINE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_ENGINE_TEXTS ON KV_ENGINE.DES_TEX_ID = KV_ENGINE_TEXTS.TEX_ID

--TOF_ENGINES.ENG_KV_CHARGE_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CHARGE ON KV_CHARGE.DES_ID = TOF_ENGINES.ENG_KV_CHARGE_DES_ID AND KV_CHARGE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CHARGE_TEXTS ON KV_CHARGE.DES_TEX_ID = KV_CHARGE_TEXTS.TEX_ID

-- TOF_ENGINES.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CYLINDERS ON KV_CYLINDERS.DES_ID = TOF_ENGINES.ENG_KV_CYLINDERS_DES_ID AND KV_CYLINDERS.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CYLINDERS_TEXTS ON KV_CYLINDERS.DES_TEX_ID = KV_CYLINDERS_TEXTS.TEX_ID

-- TOF_ENGINES.ENG_KV_CONTROL_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CONTROL ON KV_CONTROL.DES_ID = TOF_ENGINES.ENG_KV_CONTROL_DES_ID AND KV_CONTROL.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CONTROL_TEXTS ON KV_CONTROL.DES_TEX_ID = KV_CONTROL_TEXTS.TEX_ID

-- TOF_ENGINES.ENG_KV_VALVE_CONTROL_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_VALVE_CONTROL ON KV_VALVE_CONTROL.DES_ID = TOF_ENGINES.ENG_KV_VALVE_CONTROL_DES_ID AND KV_VALVE_CONTROL.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_VALVE_CONTROL_TEXTS ON KV_VALVE_CONTROL.DES_TEX_ID = KV_VALVE_CONTROL_TEXTS.TEX_ID

-- TOF_ENGINES.ENG_KV_COOLING_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_COOLING ON KV_COOLING.DES_ID = TOF_ENGINES.ENG_KV_COOLING_DES_ID AND KV_COOLING.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_COOLING_TEXTS ON KV_COOLING.DES_TEX_ID = KV_COOLING_TEXTS.TEX_ID

-- TOF_ENGINES.ENG_KV_USE_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_USE ON KV_USE.DES_ID = TOF_ENGINES.ENG_KV_USE_DES_ID AND KV_USE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_USE_TEXTS ON KV_USE.DES_TEX_ID = KV_USE_TEXTS.TEX_ID

WHERE 
(ENG_CTM SUBRANGE (16 CAST INTEGER) = 1 OR ENG_LA_CTM SUBRANGE (16 CAST INTEGER) = 1) AND MOD_ID = {0}";

        public static string GetManufacturesQuery = @"SELECT 
MFA_ID, 
MFA_BRAND,
MFA_MFC_CODE,
MFA_PC_MFC,
MFA_CV_MFC,
MFA_ENG_MFC,
MFA_ENG_TYP,
MFA_AXL_MFC,
MFA_MF_NR FROM TOF_MANUFACTURERS 
WHERE (TOF_MANUFACTURERS.MFA_PC_CTM subrange(186 cast integer) = 1)
ORDER BY MFA_BRAND";

        public static string GetTdSearchTree = @"SELECT STR_ID, STR_ID_PARENT, STR_TYPE, STR_LEVEL, TOF_DES_TEXTS.TEX_TEXT
FROM TOF_SEARCH_TREE
INNER JOIN TOF_DESIGNATIONS ON DES_ID = STR_DES_ID 
INNER JOIN TOF_DES_TEXTS ON TEX_ID = DES_TEX_ID 
WHERE DES_LNG_ID = 16";
    }
}