Документация по запросам и структуре Текдок - версия 1
Таблицы: tof__accessory_lists — таблица не используется tof__acl_criteria — таблица не используется tof__articles — таблица артикулов, данные по позиции детали tof__article_criteria — таблица параметров артикула tof__article_info — таблица не используется tof__article_lists — таблица не используется tof__art_country_specifics — таблица не используется tof__art_lookup — таблица связи артикула с аналогами tof__art_lookup_kind3 — вспомогательная таблица связи артикула с аналогами (тип 3) tof__brands — таблица брендов tof__countries — таблица стран tof__country_designations — таблица текстовых связей для стран tof__criteria — таблица параметров tof__designations — таблица текстовых связей tof__des_texts — тексты tof__doc_types — типы документов tof__engines — таблица не используется tof__eng_country_specifics — таблица не используется tof__eng_lookup — таблица не используется tof__generic_articles — таблица групп товаров tof__graphics — таблица графики и pdf, сами файлы идут отдельно tof__key_values — таблица не используется tof__languages — таблица языков, по ней выбирается язык для текстов tof__la_criteria — таблица параметров для блоков артикулов tof__la_info — таблица не используется tof__link_art — таблица связи артикулов в блоки tof__link_art_ga — таблица связи артикулов и групп tof__link_ga_cri — таблица связи групп и параметров tof__link_ga_str — таблица связи дерева категорий и групп tof__link_gra_art — таблица связи артикулов и графики tof__link_gra_la — таблица связи блоков и графики tof__link_la_eng — таблица не используется tof__link_la_mrk — таблица не используется tof__link_la_typ — таблица связи блоков и типов автомобилей tof__link_la_typ_view — вспомогательная таблица для ускорения обработки tof__link_typ_eng — таблица не используется tof__link_typ_mrk — таблица не используется tof__link_typ_str — вспомогательная таблица для ускорения постройки дерева категорий tof__manufacturers — таблица производителей автомобилей tof__models — таблица моделей автомобилей tof__mod_typ_lookup — таблица не используется tof__parameters — таблица не используется tof__search_tree — дерева категорий tof__str_family_tree — таблица не используется tof__str_lookup — таблица не используется tof__suppliers — таблица производителей деталей tof__supplier_addresses — адреса производителей деталей tof__types — таблица типов автомобилей tof__type_numbers — таблица номеров типов автомобилей tof__typ_country_specifics — таблица описания типов авто для разных стран tof__typ_voltages — таблица не используется tof__typ_wheel_bases — таблица не используется
таблица не используется — таблица появилась в Текдоке недавно и пока в проектах не используется вспомогательная таблица — таблица создана нами для ускорения обработки, в Текдоке отсутствует
SQL-запросы: Настройки для страны и языка: SET @lng_id = 16; (русский) SET @cou_id = 188; (186+2 Россия, для Украины 223+2=225)
Построение дерева категорий:
select str_id as id,str_level,str_sort,0 expand,tex_text as data,str_id_parent
from tof__search_tree join tof__designations on str_des_id=des_id and des_lng_id = @lng_id join tof__des_texts on des_tex_id=tex_id join tof__link_typ_str on lts_typ_id = ".$aData['id_type']." and str_id=lts_str_id where 1 = 1 and str_type = 1 and str_level > 1 order by data
переменная $aData['id_type'] указывает выбранный тип автомобиля для которого строится дерево категорий
Выбор кроссов:
select concat(cat2.pref,'_',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(UPPER(cta.ART_ARTICLE_NR),' ',),'-',),'#',),'.',),'/',),',',),'_',),':',),'[',),']',),'(',),')',)) as item_code_crs, concat(cat1.pref,'_',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(UPPER(ctal.ARL_SEARCH_NUMBER),' ',),'-',),'#',),'.',),'/',),',',),'_',),':',),'[',),']',),'(',),')',)) as item_code
, ctal.ARL_KIND as is_replacement, cta.art_id as art_id
FROM tof__articles as cta
INNER JOIN tof__art_lookup as ctal ON ctal.ARL_ART_ID = cta.ART_ID
INNER JOIN cat as cat1 ON cat1.id_tof = ctal.ARL_BRA_ID
INNER JOIN cat as cat2 ON cat2.id_tof = cta.ART_SUP_ID
where 1=1 and ctal.ARL_KIND in ('3','4')
".$sWhere."
union
select concat(cat2.pref,'_',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(UPPER(ctal.ARL_SEARCH_NUMBER),' ',),'-',),'#',),'.',),'/',),',',),'_',),':',),'[',),']',),'(',),')',)) as item_code_crs, concat(cat2.pref,'_',replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(UPPER(cta.ART_ARTICLE_NR),' ',),'-',),'#',),'.',),'/',),',',),'_',),':',),'[',),']',),'(',),')',)) as item_code
, ctal.ARL_KIND as is_replacement, cta.art_id as art_id
FROM tof__articles as cta
INNER JOIN tof__art_lookup as ctal ON ctal.ARL_ART_ID = cta.ART_ID
INNER JOIN cat as cat2 ON cat2.id_tof = cta.ART_SUP_ID
where 1=1 and ctal.ARL_KIND in ('1','2','5')
".$sWhere2
Таблица cat — рабочая таблица сайта, в ней собраны производители и связь с Текдоком через id_tof if ($aData['sCode']) { $sWhere.=" and ctal.ARL_SEARCH_NUMBER='".$aData['sCode']."' and ctal.ARL_SEARCH_NUMBER<>"; $sWhere1.=" and cc.code='".$aData['sCode']."' "; } else { return "select null "; } $aArtId=(" select cta.art_id as art_id FROM tof__articles as cta INNER JOIN tof__art_lookup as ctal ON ctal.ARL_ART_ID = cta.ART_ID INNER JOIN cat as cat1 ON cat1.id_tof = cta.ART_SUP_ID where 1=1 and ctal.ARL_KIND in ('1','2','5') ".$sWhere );
if ($aArtId) { $inArtId=implode(",",$aArtId); $sWhere2=" and cta.art_id in (".$inArtId.")"; } else { $sWhere2=" and 0=1"; }
Выборка графики:
select lga_art_id,gra_sup_id, gra_id, gra_doc_type, gra_lng_id, gra_type, gra_norm, gra_supplier_nr, gra_tab_nr, gra_grd_id ,
concat( '/tcd/' , case gra_doc_type when 2 then 'pdf' else gra_tab_nr end
, '/', case gra_doc_type when 2 then concat(gra_id, lpad(gra_lng_id, 3, '0')) else gra_grd_id end
, case gra_doc_type when 1 then '.bmp' when 2 then '.pdf' when 3 then '.jpg' when 4 then '.jpg' when 5 then '.png' end
) as img_path , gra_norm as img_width
FROM tof__graphics g JOIN tof__link_gra_art ON gra_id = lga_gra_id where 1=1 ".$sWhere."
UNION
SELECT lgl_la_id,gra_sup_id, gra_id, gra_doc_type, gra_lng_id,gra_type, gra_norm, gra_supplier_nr,gra_tab_nr,
gra_grd_id,
concat( '/tcd/' , case gra_doc_type when 2 then 'pdf' else gra_tab_nr end
, '/', case gra_doc_type when 2 then concat(gra_id, lpad(gra_lng_id, 3, '0')) else gra_grd_id end
, case gra_doc_type when 1 then '.bmp' when 2 then '.pdf' when 3 then '.jpg' when 4 then '.jpg' when 5 then '.png' end
) as img_path , gra_norm as img_width
FROM tof__graphics g JOIN tof__link_gra_la ON gra_id = lgl_gra_id where 1=1 ".$sWhere1;
if(!$aData['aIdGraphic']) $aData['aIdGraphic']=array(); $inIdGraphic = "'".implode("','",$aData['aIdGraphic'])."'";
if ($inIdGraphic) { $sWhere.=" and lga_art_id in(".$inIdGraphic.")"; $sWhere1.=" and lgl_la_id in(".$inIdGraphic.")"; } else { $sWhere.=" and 0=1 "; $sWhere1.=" and 0=1 "; }
Выбор моделей:
select ifnull(lng_tex.tex_text, uni_tex.tex_text) as name,
tof__models.mod_id, tof__models.mod_id as id, tof__models.mod_pc, tof__models.mod_cv,
mod_pcon_start, mod_pcon_end,mod_mfa_id
, substr(mod_pcon_start,5,2) as month_start, substr(mod_pcon_start,1,4) as year_start , substr(mod_pcon_end,5,2) as month_end, substr(mod_pcon_end,1,4) as year_end
from tof__models
inner join cat as c on tof__models.mod_mfa_id=c.id_tof
left outer join tof__country_designations uni_des
on mod_cds_id = uni_des.cds_id
and uni_des.cds_lng_id = @lng_id
and substring(uni_des.cds_ctm, @cou_id,1) = 1
left outer join tof__des_texts uni_tex
on uni_des.cds_tex_id = uni_tex.tex_id
left outer join tof__country_designations lng_des
on mod_cds_id = lng_des.cds_id
and lng_des.cds_lng_id = @lng_id
and substring(lng_des.cds_ctm, @cou_id,1) = 1
left outer join tof__des_texts lng_tex
on lng_des.cds_tex_id = lng_tex.tex_id
".$sJoin."
where 1=1
and ( tof__models.mod_pc = 1 or tof__models.mod_cv = 1 )
and ( substring(mod_pc_ctm,@cou_id,1) = 1 or substring(mod_cv_ctm,@cou_id,1) = 1)
".$sWhere;
if ($aData['id_make']) { $sWhere.=" and c.id = ".$aData['id_make']; } else { $sWhere.=" and 1=0"; }
if ($aData['id_model']) { $sWhere.=" and tof__models.mod_id = ".$aData['id_model']; }
Выбор типов: select ifnull(lng_tex.tex_text, uni_tex.tex_text) name,ifnull(tyc_pcon_start, typ_pcon_start) pcon_start,
ifnull(tyc_pcon_end, typ_pcon_end) pcon_end, ifnull(tyc_kw_from, typ_kw_from) kw_from,
ifnull(tyc_hp_from, typ_hp_from) hp_from, ifnull(tyc_ccm, typ_ccm) ccm,
ifnull(tyc_bod_tex.tex_text, ifnull(bod_tex.tex_text, ifnull(tyc_mod_tex.tex_text, mod_tex.tex_text))) body,
ifnull(tyc_axl_tex.tex_text, axl_tex.tex_text) axis, ifnull(tyc_max_weight, typ_max_weight) max_weight,
ifnull(tyc_kv_body_des_id, ifnull(typ_kv_body_des_id, ifnull(tyc_kv_model_des_id , typ_kv_model_des_id ))) body_des_id,
ifnull(tyc_kv_engine_des_id, typ_kv_engine_des_id) engine_des_id,
ifnull(tyc_kv_axle_des_id, typ_kv_axle_des_id) axis_des_id,
typ_mod_id mod_id, typ_id typ_id,substring(typ_la_ctm,@cou_id,1) flag_id,
mod_pc,mod_cv,mod_mfa_id,typ_kv_fuel_des_id,typ_sort
, substr(ifnull(tyc_pcon_start, typ_pcon_start),5,2) as month_start
, substr(ifnull(tyc_pcon_start, typ_pcon_start),1,4) as year_start
, substr(ifnull(tyc_pcon_end, typ_pcon_end),5,2) as month_end , substr(ifnull(tyc_pcon_end, typ_pcon_end),1,4) as year_end , c.id as id_make, typ_mod_id as id_model, typ_id as id_model_detail
from tof__types
inner join tof__models on typ_mod_id = mod_id
inner join cat as c on mod_mfa_id = c.id_tof
left outer join tof__designations model_des
on tof__types.typ_kv_model_des_id = model_des.des_id
and model_des.des_lng_id = @lng_id
left outer join tof__des_texts mod_tex
on model_des.des_tex_id = mod_tex.tex_id
left outer join tof__designations axle_des
on tof__types.typ_kv_axle_des_id = axle_des.des_id
and axle_des.des_lng_id = @lng_id
left outer join tof__des_texts axl_tex
on axle_des.des_tex_id = axl_tex.tex_id
left outer join tof__designations body_des
on tof__types.typ_kv_body_des_id = body_des.des_id
and body_des.des_lng_id = @lng_id
left outer join tof__des_texts bod_tex
on body_des.des_tex_id = bod_tex.tex_id
left outer join tof__country_designations lng_des
on typ_mmt_cds_id = lng_des.cds_id
and lng_des.cds_lng_id = @lng_id
and substring(lng_des.cds_ctm,@cou_id,1) = 1
left outer join tof__des_texts lng_tex
on lng_des.cds_tex_id = lng_tex.tex_id
left outer join tof__country_designations uni_des
on typ_mmt_cds_id = uni_des.cds_id
and uni_des.cds_lng_id = 255
and substring(uni_des.cds_ctm,@cou_id,1) = 1
left outer join tof__des_texts uni_tex
on uni_des.cds_tex_id = uni_tex.tex_id
left outer join tof__typ_country_specifics
on typ_id = tyc_typ_id
and tyc_cou_id = @cou_id
left outer join tof__designations tyc_model_des
on tyc_kv_model_des_id = tyc_model_des.des_id
and tyc_model_des.des_lng_id = @lng_id
left outer join tof__des_texts tyc_mod_tex
on tyc_model_des.des_tex_id = tyc_mod_tex.tex_id
left outer join tof__designations tyc_axle_des
on tyc_kv_axle_des_id = tyc_axle_des.des_id
and tyc_axle_des.des_lng_id = @lng_id
left outer join tof__des_texts tyc_axl_tex
on tyc_axle_des.des_tex_id = tyc_axl_tex.tex_id
left outer join tof__designations tyc_body_des
on tyc_kv_body_des_id = tyc_body_des.des_id
and tyc_body_des.des_lng_id = @lng_id
left outer join tof__des_texts tyc_bod_tex
on tyc_body_des.des_tex_id = tyc_bod_tex.tex_id
left outer join tof__country_designations short_des
on typ_cds_id = short_des.cds_id and substring(short_des.cds_ctm,@cou_id,1) = 1 and short_des.cds_lng_id = @lng_id left outer join tof__des_texts short_tex on short_tex.tex_id = short_des.cds_tex_id
where 1=1 and substring(typ_ctm,@cou_id,1) = 1 ".$sWhere;
if ($aData['id_model']) { $sWhere.="and typ_mod_id = ".$aData['id_model']; } elseif ($aData["type_number"]) { $sJoin=" inner join tof__type_numbers as ttn on tof__types.typ_id = ttn.tyn_typ_id "; $sWhere.=" and ttn.tyn_kind = 2 AND ttn.tyn_search_text like '".$aData["type_number"]."%' "; } elseif ($aData["code"] && $aData["art_id"]) { $sJoin=" inner join tof__link_la_typ_view on typ_id = lat_typ_id"; $sWhere.=" and art_article_nr = '".$aData["code"]."' and art_id='".$aData["art_id"]."'"; } elseif (!$aData['id_model_detail']) { $sWhere="and 1=0"; }
if ($aData['id_model_detail']) { $sWhere.=" and typ_id = ".$aData['id_model_detail']; }
Информация по модели:
select
coalesce(lng_tex.tex_text, uni_tex.tex_text) type_auto,
concat(substr(coalesce(specifics.tyc_pcon_start, tof__types.typ_pcon_start),5,2),
'.', substr(coalesce(specifics.tyc_pcon_start, tof__types.typ_pcon_start),1,4)) model_year_from,
concat(substr(coalesce(specifics.tyc_pcon_end, tof__types.typ_pcon_end),5,2),
'.', substr(coalesce(specifics.tyc_pcon_end, tof__types.typ_pcon_end),1,4)) model_year_to,
-- coalesce(specifics.tyc_pcon_start, tof__types.typ_pcon_start) model_year_from_,
-- coalesce(specifics.tyc_pcon_end, tof__types.typ_pcon_end) model_year_to,
coalesce(specifics.tyc_kw_from, tof__types.typ_kw_from) pover_output_kw,
coalesce(specifics.tyc_hp_from, tof__types.typ_hp_from) power_output_hp,
coalesce(specifics.tyc_kw_upto, tof__types.typ_kw_upto) power_to_kw,
coalesce(specifics.tyc_hp_upto, tof__types.typ_hp_upto) power_to_hp,
tof__types.typ_ccm as tech_engine_capacity,
des_text_b.tex_text body_type,
des_text_d.tex_text drive_type,
des_text_e.tex_text fuel_type,
coalesce(des_text_fcou.tex_text, des_text_f.tex_text) brake_system,
coalesce(des_text_gcou.tex_text, des_text_g.tex_text) brake,
coalesce(des_text_hcou.tex_text, des_text_h.tex_text) abs_des,
coalesce(des_text_icou.tex_text, des_text_i.tex_text) asr,
coalesce(des_text_jcou.tex_text, des_text_j.tex_text) katart,
des_text_k.tex_text steering_gear,
des_text_l.tex_text lenkart,
coalesce(des_text_mcou.tex_text, des_text_m.tex_text) spannung,
des_text_n.tex_text engine_type,
coalesce(des_text_ocou.tex_text, des_text_o.tex_text) gearbox,
-- tof__types.typ_max_weight,
-- tof__types.typ_kv_body_des_id,
-- tof__types.typ_kv_engine_des_id,
-- tof__types.typ_kv_axle_des_id,
-- tof__types.typ_kv_drive_des_id,
-- tof__types.typ_mod_id,
-- tof__types.typ_id,
coalesce(specifics.tyc_tank, tof__types.typ_tank) tank,
coalesce(specifics.tyc_doors, tof__types.typ_doors) door, tof__types.typ_max_weight,
texts_fuel_supply.tex_text fuel_supply,
coalesce(specifics.tyc_cylinders, tof__types.typ_cylinders) cylinder,
tof__types.typ_valves
from tof__types
left outer join tof__designations designations_b
on tof__types.typ_kv_body_des_id = designations_b.des_id
and designations_b.des_lng_id = @lng_id
left outer join tof__des_texts des_text_b
on designations_b.des_tex_id = des_text_b.tex_id
left outer join tof__designations designations_d
on tof__types.typ_kv_drive_des_id = designations_d.des_id
and designations_d.des_lng_id = @lng_id
left outer join tof__des_texts des_text_d
on designations_d.des_tex_id = des_text_d.tex_id
left outer join tof__designations designations_e
on tof__types.typ_kv_fuel_des_id = designations_e.des_id
and designations_e.des_lng_id = @lng_id
left outer join tof__des_texts des_text_e
on designations_e.des_tex_id = des_text_e.tex_id
left outer join tof__designations designations_f
on tof__types.typ_kv_brake_syst_des_id = designations_f.des_id
and designations_f.des_lng_id = @lng_id
left outer join tof__des_texts des_text_f
on designations_f.des_tex_id = des_text_f.tex_id
left outer join tof__designations designations_g
on tof__types.typ_kv_brake_type_des_id = designations_g.des_id
and designations_g.des_lng_id = @lng_id
left outer join tof__des_texts des_text_g
on designations_g.des_tex_id = des_text_g.tex_id
left outer join tof__designations designations_h
on tof__types.typ_kv_abs_des_id = designations_h.des_id
and designations_h.des_lng_id = @lng_id
left outer join tof__des_texts des_text_h
on designations_h.des_tex_id = des_text_h.tex_id
left outer join tof__designations designations_i
on tof__types.typ_kv_asr_des_id = designations_i.des_id
and designations_i.des_lng_id = @lng_id
left outer join tof__des_texts des_text_i
on designations_i.des_tex_id = des_text_i.tex_id
left outer join tof__designations designations_j
on tof__types.typ_kv_catalyst_des_id = designations_j.des_id
and designations_j.des_lng_id = @lng_id
left outer join tof__des_texts des_text_j
on designations_j.des_tex_id = des_text_j.tex_id
left outer join tof__designations designations_k
on tof__types.typ_kv_steering_des_id = designations_k.des_id
and designations_k.des_lng_id = @lng_id
left outer join tof__des_texts des_text_k
on designations_k.des_tex_id = des_text_k.tex_id
left outer join tof__designations designations_l
on tof__types.typ_kv_steering_side_des_id = designations_l.des_id
and designations_l.des_lng_id = @lng_id
left outer join tof__des_texts des_text_l
on designations_l.des_tex_id = des_text_l.tex_id
left outer join tof__designations designations_m
on tof__types.typ_kv_voltage_des_id = designations_m.des_id
and designations_m.des_lng_id = @lng_id
left outer join tof__des_texts des_text_m
on designations_m.des_tex_id = des_text_m.tex_id
left outer join tof__designations designations_n
on tof__types.typ_kv_engine_des_id = designations_n.des_id
and designations_n.des_lng_id = @lng_id
left outer join tof__des_texts des_text_n
on designations_n.des_tex_id = des_text_n.tex_id
left outer join tof__designations designations_o
on tof__types.typ_kv_trans_des_id = designations_o.des_id
and designations_o.des_lng_id = @lng_id
left outer join tof__des_texts des_text_o
on designations_o.des_tex_id = des_text_o.tex_id
left outer join tof__designations des_fuel_supply
on tof__types.typ_kv_fuel_supply_des_id = des_fuel_supply.des_id
and des_fuel_supply.des_lng_id = @lng_id
left outer join tof__des_texts texts_fuel_supply
on des_fuel_supply.des_tex_id = texts_fuel_supply.tex_id
left outer join tof__typ_country_specifics specifics
on tof__types.typ_id = specifics.tyc_typ_id
and specifics.tyc_cou_id = 185 left outer join tof__designations designations_fcou
on specifics.tyc_kv_brake_syst_des_id = designations_fcou.des_id
and designations_fcou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_fcou
on designations_fcou.des_tex_id = des_text_fcou.tex_id
left outer join tof__designations designations_gcou
on specifics.tyc_kv_brake_type_des_id = designations_gcou.des_id
and designations_gcou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_gcou
on designations_gcou.des_tex_id = des_text_gcou.tex_id
left outer join tof__designations designations_hcou
on specifics.tyc_kv_abs_des_id = designations_hcou.des_id
and designations_hcou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_hcou
on designations_hcou.des_tex_id = des_text_hcou.tex_id
left outer join tof__designations designations_icou
on specifics.tyc_kv_asr_des_id = designations_icou.des_id
and designations_icou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_icou
on designations_icou.des_tex_id = des_text_icou.tex_id
left outer join tof__designations designations_jcou
on specifics.tyc_kv_catalyst_des_id = designations_jcou.des_id
and designations_jcou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_jcou
on designations_jcou.des_tex_id = des_text_jcou.tex_id
left outer join tof__designations designations_mcou
on specifics.tyc_kv_voltage_des_id = designations_mcou.des_id
and designations_mcou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_mcou
on designations_mcou.des_tex_id = des_text_mcou.tex_id
left outer join tof__designations designations_ocou
on specifics.tyc_kv_trans_des_id = designations_ocou.des_id
and designations_ocou.des_lng_id = @lng_id
left outer join tof__des_texts des_text_ocou
on designations_ocou.des_tex_id = des_text_ocou.tex_id
left outer join tof__country_designations lng_des
on typ_mmt_cds_id = lng_des.cds_id
and lng_des.cds_lng_id = @lng_id
left outer join tof__des_texts lng_tex
on lng_des.cds_tex_id = lng_tex.tex_id
left outer join tof__country_designations uni_des
on typ_mmt_cds_id = uni_des.cds_id
and uni_des.cds_lng_id = 255
left outer join tof__des_texts uni_tex
on uni_des.cds_tex_id = uni_tex.tex_id
where 1=1
".$sWhere;
if ($aData['id_type']) { $sWhere.=" and tof__types.typ_id = ".$aData['id_type']; } else { $sWhere=" and 1=0"; }
Параметры артикула:
if(!$aData['aId']) $aData['aId']=array();
$inId = "'".implode("','",$aData['aId'])."'";
if ($inId) { $sWhere.=" and acr_art_id in(".$inId.")"; if ($aData['id_model_detail']) { $sWhere1.=" and la_art_id in(".$inId.") and lat_typ_id=".$aData['id_model_detail']; } else { $sWhere1.=" and 0=1 "; } } else { $sWhere.=" and 0=1 "; $sWhere1.=" and 0=1 "; }
if ($aData['type_']=="all") { $sField.=" distinct krit_name, krit_value"; } elseif ($aData['type_']=="all_edit") { $sField.=" krit_name, krit_value, id_cat_info"; } else { $sField.=" group_concat(' ', krit_name, ' ', krit_value) as criteria "; $sGroup.=" group by acr_art_id";
if ($aData['type_']=="only_la") $sWhere.=" and 0=1 "; }
$sSql=" select ".$sField." from ( select acr_art_id , des_texts.tex_text as krit_name , ifnull(des_texts2.tex_text, acr_value) as krit_value , 2 flag , acr_sort sort , acr_kv_des_id kv_des_id , acr_cri_id cri_id , acr_ga_id ga_id , 0 as id_cat_info
from
tof__article_criteria left join tof__designations as designations2 on designations2.des_id = acr_kv_des_id left join tof__des_texts as des_texts2 on des_texts2.tex_id = designations2.des_tex_id inner join tof__criteria on cri_id = acr_cri_id inner join tof__designations as designations on designations.des_id = cri_des_id inner join tof__des_texts as des_texts on des_texts.tex_id = designations.des_tex_id where 1=1 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)
".$sWhere." union all
select la_art_id , trim(cri_tex.tex_text) krit_name , coalesce(lac_value, lac_tex.tex_text) krit_value , 1 flag , lac_sort sort , lac_kv_des_id kv_des_id , lac_cri_id cri_id , la_ga_id ga_id , 0 as id_cat_info from tof__la_criteria join tof__link_art on la_id = lac_la_id join tof__link_la_typ on lat_la_id =la_id and lat_ga_id=la_ga_id join tof__criteria on cri_id = lac_cri_id join tof__designations cri_des on cri_des.des_id = cri_des_id and cri_des.des_lng_id = @lng_id join tof__des_texts cri_tex on cri_tex.tex_id = cri_des.des_tex_id left join tof__designations lac_des on lac_des.des_id = ifnull(lac_kv_des_id,-1) and lac_des.des_lng_id = @lng_id left join tof__des_texts lac_tex on lac_tex.tex_id = lac_des.des_tex_id where 1=1
".$sWhere1."
) as crt ".$sGroup;
Детали по артикулу:
if ($aData['id_part'] && $aData["id_model_detail"])
{
if(is_array($aData['id_part']))
$sJoin.=" join tof__link_ga_str lgs on lat_ga_id=lgs_ga_id and lgs_str_id in ('".implode ("','", $aData['id_part'])."')";
else
$sJoin.=" join tof__link_ga_str lgs on lat_ga_id=lgs_ga_id and lgs_str_id = '".$aData['id_part']."'";
$sWhere.=" and lat_typ_id = '".$aData['id_model_detail']."'";
}
else
{
$sWhere.=" and 0=1 ";
}
$sSql="select art_id, art_article_nr , concat(cat.pref,'_',replace(replace(replace(replace(replace(art_article_nr,' ',),'-',),'#',),'.',),'/',)) as item_code , tdt.tex_text as name , cat.title as brand , cat.image as image_logo , cat.pref as pref from tof__link_la_typ_view as tlltv
join tof__designations as td on tlltv.ga_des_id = td.des_id and td.des_lng_id = @lng_id
join tof__des_texts as tdt on td.des_tex_id=tdt.tex_id
join cat on lat_sup_id=cat.id_tof ".$sJoin." where 1=1
".$sWhere."
group by art_id";
Информация по артикулу:
if ($aData['item_code']) {
list($aData['pref'],$aData['sCode'])=explode("_",$aData['item_code']);
}
if ($aData['sCode']) { $sWhere.=" and ctal.arl_search_number in ('".$aData['sCode']."') and ctal.arl_search_number<> and ctal.arl_kind=1 "; $sJoin.=" inner join tof__art_lookup as ctal on ctal.arl_art_id = cta.art_id "; } elseif ($aData['art_id']) { $sWhere.=" and cta.art_id in (".$aData['art_id'].")"; } elseif ($aData['id_cat_part']) { $sWhere.=" and 0=1"; } else { return "select null "; }
if ($aData['pref']) { $sWhere.=" and cat2.pref='".$aData['pref']."'"; }
$sSql=" select cta.art_id, cta.art_article_nr as code
, concat(cat2.pref,'_',replace(replace(replace(replace(replace(cta.art_article_nr,' ',),'-',),'#',),'.',),'/',)) as item_code , cat2.pref, cat2.title as brand, dest.tex_text as name
from tof__articles as cta inner join tof__designations as des on cta.art_complete_des_id = des.des_id and des.des_lng_id = @lng_id inner join tof__des_texts dest on des.des_tex_id=dest.tex_id inner join cat as cat2 on cat2.id_tof = cta.art_sup_id ".$sJoin." where 1=1 ".$sWhere ;
Отображение оригиналов для выбранного артикула:
if(!$aData['aIdCatPart']) $aData['aIdCatPart']=array();
$inIdCatPart = "'".implode("','",$aData['aIdCatPart'])."'";
if ($aData['sCode']) { $sWhere.=" and ctal.arl_search_number in (".$aData['sCode'].") and ctal.arl_search_number<> and ctal.arl_kind=1 "; $sJoin.=" inner join tof__art_lookup as ctal on ctal.arl_art_id = cta.art_id ";
} elseif ($aData['art_id'] || ($aData['pref'] && $aData['code'])) { if ($aData['art_id']) { $sWhere.=" and talk.arl_art_id in (".$aData['art_id'].")"; } else { $sWhere.=" and 0=1"; }
} else { $sWhere.=" and 0=1"; }
$sSql="
select talk.arl_art_id as art_id, talk.arl_search_number as number, c.title as name
from tof__art_lookup_kind3 as talk
inner join cat as c on talk.arl_bra_id=c.id_tof
".$sJoin."
where 1=1 "
.$sWhere
;
