AUDACON Инструкции, Руководства

Год и месяц начала произвоства автомобиля

SET @TypeID = 90043; /* FORD C-MAX 2 DXA/CB7 1.6 LPG 2012-HB */
SET @COUNTRYID = 51; /* Страна применения - Россия */

SELECT ClassID, COALESCE(BuildText.TextStr, '0') BuildText, COALESCE(TB_TM_FuelTypeID.FuelTypeID, 0) FuelTypeID
FROM TB_TreeRange
INNER JOIN TB_TreeType ON TB_TreeRange.RangeID = TB_TreeType.RangeID AND TB_TreeType.TypeID = @TypeID
LEFT OUTER JOIN TB_TreeAddInfo BuildInfo ON TB_TreeType.TypeID = BuildInfo.TypeID AND BuildInfo.AddInfoKeyID = 3 AND BuildInfo.CountryID = 0
LEFT OUTER JOIN TB_TreeAddInfo BuildInfoNat ON TB_TreeType.TypeID = BuildInfoNat.TypeID AND BuildInfoNat.AddInfoKeyID = 3 AND BuildInfoNat.CountryID = @COUNTRYID
LEFT OUTER JOIN TB_TreeAddInfoText BuildText ON COALESCE(BuildInfoNat.KeyValueTextID, BuildInfo.KeyValueTextID) = BuildText.TextID AND (BuildText.LangCode = 'ru' OR BuildText.LangCode = '0')
LEFT OUTER JOIN TB_TreeAddInfo FuelInfo ON TB_TreeType.TypeID = FuelInfo.TypeID AND FuelInfo.AddInfoKeyID = 10 AND FuelInfo.CountryID = 0
LEFT OUTER JOIN TB_TreeAddInfo FuelInfoNat ON TB_TreeType.TypeID = FuelInfoNat.TypeID AND FuelInfoNat.AddInfoKeyID = 10 AND FuelInfoNat.CountryID = @COUNTRYID
LEFT OUTER JOIN TB_TM_FuelTypeID ON COALESCE(FuelInfoNat.KeyValueTextID, FuelInfo.KeyValueTextID) = TB_TM_FuelTypeID.FuelKeyValueTextID


Разделы сборника инструкций

SET @TypeID = 90043; /* FORD C-MAX 2 DXA/CB7 1.6 LPG 2012-HB */
SET @BUILDFROM = 201202; /* Год начала производства */

SELECT DISTINCT TB_Topic_MainGR.MGID, TB_Topic_MainGR_TXT.TextStr, TB_Image.ImageName, TB_Topic_MainGR.SortNr
FROM TB_Topic_MainGR
INNER JOIN TB_Topic_MainGR_TXT ON TB_Topic_MainGR.MGTextID = TB_Topic_MainGR_TXT.MGTextID AND TB_Topic_MainGR_TXT.LangCode = 'ru'
INNER JOIN TB_Topic_MainGRSubGR ON TB_Topic_MainGR.MGID = TB_Topic_MainGRSubGR.MGID
INNER JOIN TB_Topic_SubGRItem ON TB_Topic_MainGRSubGR.SGRID = TB_Topic_SubGRItem.SGRID
LEFT OUTER JOIN TB_Application_Parameter appSG ON TB_Topic_SubGRItem.SGRID = CAST(appSG.ParameterValue AS INTEGER) AND appSG.Parameter = 'WiringDiagramSubGroup'
INNER JOIN TB_Topic_VisibleForModul ON TB_Topic_MainGR.MGID = TB_Topic_VisibleForModul.MGID AND TB_Topic_VisibleForModul.ModulID = 1
LEFT OUTER JOIN TB_TM_TypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_TypeManuals.ItemMpID AND TB_TM_TypeManuals.Type_ID = @TypeID
LEFT OUTER JOIN TB_TM_AutoTypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_AutoTypeManuals.ItemMpID AND TB_TM_AutoTypeManuals.Class_ID = 1
AND TB_TM_AutoTypeManuals.Build_From <= @BUILDFROM AND (TB_TM_AutoTypeManuals.FuelTypeID = 1 OR TB_TM_AutoTypeManuals.FuelTypeID = 0) LEFT OUTER JOIN TB_Application_Parameter ON TB_Topic_MainGR.MGID = CAST(TB_Application_Parameter.ParameterValue AS INTEGER) AND TB_Application_Parameter.Parameter = 'NotVisibleTMGroup'
LEFT OUTER JOIN TB_Image ON TB_Topic_MainGR.ImageID = TB_Image.ImageID
WHERE (TB_Application_Parameter.ParameterValue IS NULL) AND (appSG.ParameterValue IS NULL) AND (TB_TM_TypeManuals.ItemMpID IS NOT NULL OR TB_TM_AutoTypeManuals.ItemMpID IS NOT NULL)
ORDER BY TB_Topic_MainGR.SortNr


Подразделы сборника инструкций

SET @TypeID = 90043; /* FORD C-MAX 2 DXA/CB7 1.6 LPG 2012-HB */
SET @BUILDFROM = 201202; /* Год начала производства */
SET @MGID = 26; /* Раздел "Кузов снаружи" */

SELECT DISTINCT TB_Topic_MainGRSubGR.SGRID, TB_Topic_SubGR_TXT.TextStr, TB_Image.ImageName, TB_Topic_MainGRSubGR.SortNr
FROM TB_Topic_MainGRSubGR
INNER JOIN TB_Topic_SubGR ON TB_Topic_MainGRSubGR.SGRID = TB_Topic_SubGR.SGRID AND TB_Topic_MainGRSubGR.MGID = @MGID
INNER JOIN TB_Topic_SubGR_TXT ON TB_Topic_SubGR.SGRTextID = TB_Topic_SubGR_TXT.SGRTextID AND TB_Topic_SubGR_TXT.LangCode = 'ru'
INNER JOIN TB_Topic_SubGRItem ON TB_Topic_SubGR.SGRID = TB_Topic_SubGRItem.SGRID
LEFT OUTER JOIN TB_Application_Parameter WdAppParam ON TB_Topic_MainGRSubGR.SGRID = CAST(WdAppParam.ParameterValue AS INTEGER) AND WdAppParam.Parameter = 'WiringDiagramSubGroup'
LEFT OUTER JOIN TB_TM_TypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_TypeManuals.ItemMpID AND TB_TM_TypeManuals.Type_ID = @TypeID
LEFT OUTER JOIN TB_TM_AutoTypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_AutoTypeManuals.ItemMpID AND TB_TM_AutoTypeManuals.Class_ID = 1
AND TB_TM_AutoTypeManuals.Build_From <= @BUILDFROM AND (TB_TM_AutoTypeManuals.FuelTypeID = 1 OR TB_TM_AutoTypeManuals.FuelTypeID = 0)
LEFT OUTER JOIN TB_Image ON TB_Topic_SubGR.ImageID = TB_Image.ImageID
WHERE (TB_TM_TypeManuals.ItemMpID IS NOT NULL OR TB_TM_AutoTypeManuals.ItemMpID IS NOT NULL) AND (WdAppParam.ParameterValue IS NULL)
ORDER BY TB_Topic_MainGRSubGR.SortNr


Перечень инструкций подраздела

SET @TYP_ID = 90043; /* FORD C-MAX 2 DXA/CB7 1.6 LPG 2012-HB */
SET @BUILDFROM = 201202; /* Год начала производства */
SET @SGRID = 129; /* Подраздел "Освещение" */

SELECT DISTINCT TB_Topic_SubGRItem.SGRID, TB_Topic_SubGRItem.SortNr, TB_TM_Manual.ManualID, TB_Topic_ItemMp_TXT.TextStr ItemMpText, TB_Qual_Qual_TXT.TextStr QualText, TB_Qual_Qual_TXT.QualColID
FROM TB_Topic_MainGRSubGR
INNER JOIN TB_Topic_SubGR ON TB_Topic_MainGRSubGR.SGRID = TB_Topic_SubGR.SGRID
LEFT OUTER JOIN TB_Application_Parameter wd ON TB_Topic_MainGRSubGR.SGRID = CAST(wd.ParameterValue AS INTEGER) AND wd.Parameter = 'WiringDiagramSubGroup'
INNER JOIN TB_Topic_SubGRItem ON TB_Topic_SubGR.SGRID = TB_Topic_SubGRItem.SGRID AND TB_Topic_SubGRItem.SGRID = @SGRID
INNER JOIN TB_Topic_VisibleForModul ON TB_Topic_MainGRSubGR.MGID = TB_Topic_VisibleForModul.MGID AND TB_Topic_VisibleForModul.ModulID = 1
LEFT OUTER JOIN TB_Application_Parameter ON TB_Topic_MainGRSubGR.MGID = CAST(TB_Application_Parameter.ParameterValue AS INTEGER) AND TB_Application_Parameter.Parameter = 'NotVisibleTMGroup'
INNER JOIN TB_Topic_ItemMp_TXT ON TB_Topic_SubGRItem.ItemMpID = TB_Topic_ItemMp_TXT.ItemMpID AND TB_Topic_ItemMp_TXT.LangCode = 'ru'
INNER JOIN TB_TM_TypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_TypeManuals.ItemMpID AND TB_TM_TypeManuals.Type_ID = @TYP_ID
INNER JOIN TB_Qual_Qual_TXT ON TB_TM_TypeManuals.QualColID = TB_Qual_Qual_TXT.QualColID AND TB_Qual_Qual_TXT.LangCode = 'ru'
INNER JOIN TB_TM_Manual ON TB_TM_TypeManuals.ManualID = TB_TM_Manual.ManualID
WHERE (TB_Application_Parameter.ParameterValue IS NULL) AND (wd.ParameterValue IS NULL)
UNION ALL
SELECT DISTINCT TB_Topic_SubGRItem.SGRID, TB_Topic_SubGRItem.SortNr, TB_TM_Manual.ManualID, TB_Topic_ItemMp_TXT.TextStr ItemMpText, TB_Qual_Qual_TXT.TextStr QualText, TB_Qual_Qual_TXT.QualColID
FROM TB_Topic_MainGRSubGR
INNER JOIN TB_Topic_SubGR ON TB_Topic_MainGRSubGR.SGRID = TB_Topic_SubGR.SGRID
LEFT OUTER JOIN TB_Application_Parameter wd ON TB_Topic_MainGRSubGR.SGRID = CAST(wd.ParameterValue AS INTEGER) AND wd.Parameter = 'WiringDiagramSubGroup'
INNER JOIN TB_Topic_SubGRItem ON TB_Topic_SubGR.SGRID = TB_Topic_SubGRItem.SGRID AND TB_Topic_SubGRItem.SGRID = @SGRID
INNER JOIN TB_Topic_VisibleForModul ON TB_Topic_MainGRSubGR.MGID = TB_Topic_VisibleForModul.MGID AND TB_Topic_VisibleForModul.ModulID = 1
LEFT OUTER JOIN TB_Application_Parameter ON TB_Topic_MainGRSubGR.MGID = CAST(TB_Application_Parameter.ParameterValue AS INTEGER) AND TB_Application_Parameter.Parameter = 'NotVisibleTMGroup'
INNER JOIN TB_Topic_ItemMp_TXT ON TB_Topic_SubGRItem.ItemMpID = TB_Topic_ItemMp_TXT.ItemMpID AND TB_Topic_ItemMp_TXT.LangCode = 'ru'
INNER JOIN TB_TM_AutoTypeManuals ON TB_Topic_SubGRItem.ItemMpID = TB_TM_AutoTypeManuals.ItemMpID
AND TB_TM_AutoTypeManuals.Class_ID = 1 AND TB_TM_AutoTypeManuals.Build_From <= @BUILDFROM AND (TB_TM_AutoTypeManuals.FuelTypeID = 1 OR TB_TM_AutoTypeManuals.FuelTypeID = 0)
INNER JOIN TB_Qual_Qual_TXT ON TB_TM_AutoTypeManuals.QualColID =TB_Qual_Qual_TXT.QualColID AND TB_Qual_Qual_TXT.LangCode = 'ru'
INNER JOIN TB_TM_Manual ON TB_TM_AutoTypeManuals.ManualID = TB_TM_Manual.ManualID
LEFT OUTER JOIN TB_TM_TypeManuals ON TB_TM_TypeManuals.Type_ID = @TYP_ID AND TB_TM_AutoTypeManuals.QualColID = TB_TM_TypeManuals.QualColID AND TB_TM_AutoTypeManuals.ItemMpID = TB_TM_TypeManuals.ItemMpID
WHERE (TB_Application_Parameter.ParameterValue IS NULL) AND (TB_TM_TypeManuals.ManualID IS NULL) AND (wd.ParameterValue IS NULL)
ORDER BY SGRID, SortNr, QualColID


Инструкция по выбранной работе (текст+изображения)

SET @TypeID = 90043; /* FORD C-MAX 2 DXA/CB7 1.6 LPG 2012-HB */
SET @BUILDFROM = 201202; /* Год начала производства */
SET @ManualID = 73592; /* Инструкция по работам "Фара" */

SELECT ContentID, KindOfContentID, KindOfLinkID, LinkID, OrderID, RTRIM(PreText) PreText, FormatID, TextStr, COUNT(DISTINCT TB_TM_TypeManuals.ItemMpID) TmLinkEx, COUNT(DISTINCT TB_TM_AutoTypeManuals.ItemMpID) AtmLinkEx, TB_Image.ImageName, TB_TM_Texts_TXT.TextID, TB_TM_Panels.Panel, TB_TM_Panels.Field, MaxField.MaxFieldAmt, TB_TM_BlockContent.BlockID
FROM TB_TM_Panels
INNER JOIN ( SELECT TB_TM_Panels.Panel, MAX(TB_TM_Panels.Field) MaxFieldAmt
FROM TB_TM_Panels
WHERE (ManualID = @ManualID)
GROUP BY TB_TM_Panels.Panel) MaxField ON TB_TM_Panels.Panel = MaxField.Panel
INNER JOIN TB_TM_BlockContent ON ManualID = @ManualID AND TB_TM_Panels.BlockID = TB_TM_BlockContent.BlockID
LEFT OUTER JOIN TB_TM_Texts ON TB_TM_BlockContent.ContentID = TB_TM_Texts.TmTextID AND TB_TM_BlockContent.KindOfContentID = 2
LEFT OUTER JOIN TB_TM_Texts_TXT ON TB_TM_Texts.TextID = TB_TM_Texts_TXT.TextID AND TB_TM_Texts_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_Image ON TB_TM_BlockContent.ContentID = TB_Image.ImageID AND TB_TM_BlockContent.KindOfContentID IN (4, 5)
LEFT OUTER JOIN TB_TM_TypeManuals ON TB_TM_TypeManuals.Type_ID = @TypeID AND TB_TM_BlockContent.LinkID = TB_TM_TypeManuals.ItemMpID
LEFT OUTER JOIN TB_TM_AutoTypeManuals ON TB_TM_BlockContent.LinkID = TB_TM_AutoTypeManuals.ItemMpID
AND TB_TM_AutoTypeManuals.Build_From <= @BUILDFROM AND TB_TM_AutoTypeManuals.Class_ID = 1 AND TB_TM_AutoTypeManuals.FuelTypeID IN (1, 0)
GROUP BY ContentID, KindOfContentID, KindOfLinkID, LinkID, OrderID, PreText, FormatID, TextStr, TB_Image.ImageName, TB_TM_Texts_TXT.TextID, TB_TM_Panels.Panel, TB_TM_Panels.Field, MaxField.MaxFieldAmt, TB_TM_BlockContent.BlockID
ORDER BY TB_TM_Panels.Panel, TB_TM_Panels.Field, OrderID