AUDACON Сервис, Нормочасы

1А - Сервисное обслуживание

ItemMpID
Идентификатор вида обслуживания
KorID
Неизвестно
ItemText
Наименование вида обслуживания
KorText
Вид исполнения обслуживания
SortNr
Сортировка
SET @TypeID = 52179 ; /* Идентификатор автомобиля */

SELECT DISTINCT TB_LT_LT.ItemMpID, TB_LT_LT.KorID, TB_Topic_ItemMp_TXT.TextStr ItemText, TB_LT_Kor_TXT.TextStr KorText, TB_Topic_SubGRItem.SortNr
FROM TB_Topic_SubGRItem
INNER JOIN TB_LT_LT ON TB_LT_LT.TypeID = @TypeID AND (TB_LT_LT.CountryID = 51 OR TB_LT_LT.CountryID = 0) AND TB_Topic_SubGRItem.ItemMpID = TB_LT_LT.ItemMpID AND (TB_LT_LT.BodyQualColID = 0 OR TB_LT_LT.BodyQualColID = 0)
INNER JOIN TB_Application_Parameter ON TB_Topic_SubGRItem.SGRID = CAST(TB_Application_Parameter.ParameterValue AS INTEGER) AND (TB_Application_Parameter.Parameter = 'ServiceSubGroup')
LEFT OUTER JOIN TB_Topic_ItemMp_TXT TB_Topic_ItemMp_TXT ON TB_LT_LT.ItemMpID = TB_Topic_ItemMp_TXT.ItemMpID AND TB_Topic_ItemMp_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_LT_Kor_TXT TB_LT_Kor_TXT ON TB_LT_LT.KorID = TB_LT_Kor_TXT.KorID AND TB_LT_Kor_TXT.LangCode = 'ru' AND TB_LT_Kor_TXT.KorID = 4
LEFT OUTER JOIN TB_LT_LTExcludeCountry ON TB_LT_LT.WorkID = TB_LT_LTExcludeCountry.WorkID AND TB_LT_LTExcludeCountry.ExcludeCountryID = 51
WHERE TB_LT_LTExcludeCountry.WorkID IS NULL
ORDER BY TB_Topic_SubGRItem.SortNr


1Б - Техническое обслуживание

ItemMpID
Идентификатор узла обслуживания
KorID
Неизвестно
ItemText
Наименование узла обслуживания
KorText
Вид исполнения обслуживания
SortNr
Сортировка
QualColText
Примечание
ValueText
Периодичность обслуживания
QuantityText
Необходимое количество
SET @TypeID = 52179 ; /* Идентификатор автомобиля */

SELECT DISTINCT TB_LT_LT.ItemMpID, TB_LT_LT.KorID, TB_Topic_ItemMp_TXT.TextStr ItemText, TB_LT_Kor_TXT.TextStr KorText, TB_Topic_SubGRItem.SortNr, TB_Qual_Qual_TXT.TextStr QualColText, TB_AD_ADValue_TXT.TextStr ValueText, TB_AD_ADQuantity_TXT.TextStr QuantityText
FROM TB_Topic_SubGRItem
INNER JOIN TB_LT_LT ON TB_LT_LT.TypeID = 52179 AND (TB_LT_LT.CountryID = 1 OR TB_LT_LT.CountryID = 0) AND TB_Topic_SubGRItem.ItemMpID = TB_LT_LT.ItemMpID AND (TB_LT_LT.AddMdWork = 1) AND (TB_LT_LT.BodyQualColID = 0 OR TB_LT_LT.BodyQualColID = 0)
INNER JOIN TB_Application_Parameter ON TB_Topic_SubGRItem.SGRID = CAST(TB_Application_Parameter.ParameterValue AS INTEGER) AND (TB_Application_Parameter.Parameter = 'AdditionalSubGroup')
LEFT OUTER JOIN TB_LT_LTExcludeCountry _ex ON TB_LT_LT.WorkID = _ex.WorkID AND _ex.ExcludeCountryID = 1
LEFT OUTER JOIN TB_Topic_ItemLT_ItemAD ON TB_Topic_ItemLT_ItemAD.LtItemMpID = TB_LT_LT.ItemMpID AND TB_Topic_ItemLT_ItemAD.KorID = TB_LT_LT.KorID
LEFT OUTER JOIN TB_AD_AD ON TB_LT_LT.TypeID = TB_AD_AD.TypeID AND TB_AD_AD.ItemMpID = TB_Topic_ItemLT_ItemAD.AdItemMpID
LEFT OUTER JOIN TB_AD_TypeValueCol ON TB_AD_AD.TypeValueID = TB_AD_TypeValueCol.TypeValueID
LEFT OUTER JOIN TB_AD_ADValue ON TB_AD_TypeValueCol.ValueID = TB_AD_ADValue.ValueID
LEFT OUTER JOIN TB_AD_ValueCountryRestriction ON TB_AD_TypeValueCol.TypeValueColID = TB_AD_ValueCountryRestriction.TypeValueColID AND ((TB_AD_ValueCountryRestriction.CountryID = 1 AND TB_AD_ValueCountryRestriction.Exclude = 1) OR (TB_AD_ValueCountryRestriction.Exclude = 0))
LEFT OUTER JOIN TB_AD_ADValue_TXT ON TB_AD_ADValue.ValueID = TB_AD_ADValue_TXT.ValueID AND TB_AD_ADValue_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_AD_ADQuantity_TXT ON TB_AD_ADValue.QuantityID = TB_AD_ADQuantity_TXT.QuantityID AND TB_AD_ADQuantity_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_Topic_ItemMp_TXT TB_Topic_ItemMp_TXT ON TB_LT_LT.ItemMpID = TB_Topic_ItemMp_TXT.ItemMpID AND TB_Topic_ItemMp_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_LT_Kor_TXT TB_LT_Kor_TXT ON TB_LT_LT.KorID = TB_LT_Kor_TXT.KorID AND TB_LT_Kor_TXT.LangCode = 'ru' AND TB_LT_Kor_TXT.KorID = 4
LEFT OUTER JOIN TB_Qual_Qual_TXT TB_Qual_Qual_TXT ON TB_AD_AD.QualColID = TB_Qual_Qual_TXT.QualColID AND TB_Qual_Qual_TXT.LangCode = 'ru' AND TB_Qual_Qual_TXT.QualColID <> 0
WHERE _ex.WorkID IS NULL AND ((TB_AD_ValueCountryRestriction.TypeValueColID IS NULL) OR (TB_AD_ValueCountryRestriction.CountryID = 1 AND TB_AD_ValueCountryRestriction.Exclude = 0))
ORDER BY TB_Topic_SubGRItem.SortNr, TB_LT_LT.ItemMpID, TB_LT_LT.KorID


2 - Работа по выполнению обслуживания, нормочасы


На предыдущих шагах выбираем один из видов обслуживания и получаем идентификатор выполняемой работы (их может быть несколько, например в нашем случае замена масла штатная и при повышенном сроке) и сколько времени это занимает

SET @TypeID = 52179 ; /* Идентификатор автомобиля */
SET @COUNTRYID = 51; /* Страна применения - Россия */
SET @ItemMpID=2450; /* Сервис по замене масла по индикатору периодичности */

SELECT WorkID, OnlyMaintenance, TB_LT_LT.ItemMpID, TB_LT_LT.KorID, BodyQualColID, TB_LT_LT.QualColID, WorkTime, CompositeTime, AddMdWork, WorkPos, ItemText.TextStr ItemMpText, KorText.TextStr KorText, QualText.TextStr QualText
FROM (
SELECT _int.WorkID, _int.OnlyMaintenance, _int.CountryID, _int.TypeID, _int.ItemMpID, _int.KorID, _int.BodyQualColID, _int.QualColID, _int.WorkTime, _int.CompositeTime, _int.AddMdWork, TB_LT_WorkPos.WorkPosText WorkPos
FROM TB_TreeType
INNER JOIN TB_LT_LT _int ON TB_TreeType.TypeID = @TypeID AND TB_TreeType.TypeID = _int.TypeID AND (_int.CountryID = @COUNTRYID OR _int.CountryID = 0)
LEFT OUTER
JOIN TB_LT_LT _nat ON _int.TypeID = _nat.TypeID AND (_nat.CountryID = @COUNTRYID OR _nat.CountryID = 0) AND _nat.CountryID = @COUNTRYID AND _int.CountryID <> _nat.CountryID AND _int.ItemMpID = _nat.ItemMpID AND _int.KorID = _nat.KorID
LEFT OUTER
JOIN TB_LT_LTExcludeCountry _ex ON _int.WorkID = _ex.WorkID AND _ex.ExcludeCountryID = @COUNTRYID
LEFT OUTER
JOIN TB_LT_WorkPos ON _int.WorkPosID = TB_LT_WorkPos.WorkPosID
WHERE (_ex.WorkID IS NULL) AND (_nat.WorkID IS NULL)) TB_LT_LT
LEFT OUTER
JOIN TB_Topic_ItemMp_TXT ItemText ON TB_LT_LT.ItemMpID = ItemText.ItemMpID AND ItemText.LangCode = 'ru'
LEFT OUTER
JOIN TB_LT_Kor_TXT KorText ON TB_LT_LT.KorID = KorText.KorID AND KorText.LangCode = 'ru' AND KorText.KorID <> 4
LEFT OUTER
JOIN TB_Qual_Qual_TXT QualText ON TB_LT_LT.QualColID = QualText.QualColID AND QualText.LangCode = 'ru' AND QualText.QualColID <> 0
WHERE TB_LT_LT.ItemMpID=@ItemMpID
ORDER BY TB_LT_LT.ItemMpID, TB_LT_LT.KorID, TB_LT_LT.BodyQualColID, TB_LT_LT.QualColID, TB_LT_LT.WorkTime


3 - Год начала выпуска автомобиля


Получаем год начала выпуска автомобиля, с которого возможно выполение выбранной работы

SET @WorkID = 7630152; /* Сервис по замене масла по индикатору периодичности */
SET @COUNTRYID = 51; /* Страна применения - Россия */

SELECT TB_TreeType.TypeID, ClassID, IFNULL(BuildText.TextStr, '0') BuildFrom, IFNULL(TB_TM_FuelTypeID.FuelTypeID, 0) FuelTypeID
FROM TB_LT_LT
INNER JOIN TB_TreeType ON TB_LT_LT.WorkID = @WorkID AND TB_LT_LT.TypeID = TB_TreeType.TypeID
INNER JOIN TB_TreeRange ON TB_TreeType.RangeID = TB_TreeRange.RangeID
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 IFNULL(FuelInfoNat.KeyValueTextID, FuelInfo.KeyValueTextID) = TB_TM_FuelTypeID.FuelKeyValueTextID


4 - Инструкция к работе


SET @WorkID = 7630152; /* Сервис по замене масла по индикатору периодичности */
SET @Build_From = 200801; /* Год начала производства */

SELECT TB_LT_LT.WorkID, TB_MD_WorkAddOn.ManualID, TB_LT_LT.ItemMpID, ADFill.ItemMpID FillItemMpId, ADSpec.ItemMpID SpecItemMpId, TB_Topic_ItemMp_TXT.TextStr, TB_LT_Kor_TXT.TextStr, COUNT(DISTINCT ADFill.TypeValueID) AdFillExist, COUNT(DISTINCT ADSpec.TypeValueID) AdSpecExist, COUNT(DISTINCT TB_TM_TypeManuals.ManualID) + COUNT(DISTINCT TB_TM_AutoTypeManuals.ManualID) LinkExist, TB_Topic_ItemLT_ItemTM.TmItemMpID, TB_Qual_Qual_TXT.TextStr
FROM TB_LT_LT
LEFT OUTER JOIN TB_MD_WorkAddOn ON TB_LT_LT.WorkID = TB_MD_WorkAddOn.WorkID
LEFT OUTER JOIN TB_Qual_Qual_TXT ON TB_LT_LT.QualColID = TB_Qual_Qual_TXT.QualColID AND TB_Qual_Qual_TXT.QualColID <> 0 AND TB_Qual_Qual_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_Topic_ItemLT_ItemADFill ON TB_LT_LT.ItemMpID = TB_Topic_ItemLT_ItemADFill.LtItemMpID
LEFT OUTER JOIN TB_Topic_ItemLT_ItemADSpec ON TB_LT_LT.ItemMpID = TB_Topic_ItemLT_ItemADSpec.LtItemMpID
INNER JOIN TB_Topic_ItemMp_TXT ON TB_LT_LT.ItemMpID = TB_Topic_ItemMp_TXT.ItemMpID AND TB_Topic_ItemMp_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_LT_Kor_TXT ON TB_LT_LT.KorID = TB_LT_Kor_TXT.KorID AND TB_LT_LT.KorID <> 4 AND TB_LT_Kor_TXT.LangCode = 'ru'
LEFT OUTER JOIN TB_AD_AD ADFill ON TB_LT_LT.TypeID = ADFill.TypeID AND TB_Topic_ItemLT_ItemADFill.AdItemMpID = ADFill.ItemMpID
LEFT OUTER JOIN TB_AD_AD ADSpec ON TB_LT_LT.TypeID = ADSpec.TypeID AND TB_Topic_ItemLT_ItemADSpec.AdItemMpID = ADSpec.ItemMpID
LEFT OUTER JOIN TB_Topic_ItemLT_ItemTM ON TB_LT_LT.ItemMpID = TB_Topic_ItemLT_ItemTM.LtItemMpID AND TB_LT_LT.KorID = TB_Topic_ItemLT_ItemTM.KorID
LEFT OUTER JOIN TB_TM_TypeManuals ON TB_LT_LT.TypeID = TB_TM_TypeManuals.Type_ID AND TB_Topic_ItemLT_ItemTM.TmItemMpID = TB_TM_TypeManuals.ItemMpID
LEFT OUTER JOIN TB_TM_AutoTypeManuals ON TB_Topic_ItemLT_ItemTM.TmItemMpID = TB_TM_AutoTypeManuals.ItemMpID
AND TB_TM_AutoTypeManuals.Build_From <= @Build_From AND TB_TM_AutoTypeManuals.Class_ID = 1 AND (TB_TM_AutoTypeManuals.FuelTypeID = 1 OR TB_TM_AutoTypeManuals.FuelTypeID = 0) WHERE (TB_LT_LT.WorkID IN (@WorkID))
GROUP BY TB_MD_WorkAddOn.ManualID, TB_LT_LT.WorkID, TB_LT_LT.ItemMpID, TB_Qual_Qual_TXT.TextStr, ADFill.ItemMpID, ADSpec.ItemMpID, TB_Topic_ItemMp_TXT.TextStr, TB_LT_Kor_TXT.TextStr, TB_Topic_ItemLT_ItemTM.TmItemMpID


5 - Перечень производимых работ для данного сервиса


SET @TypeID = 52179 ; /* Идентификатор автомобиля */
SET @Build_From = 200801; /* Год начала производства */
SET @ManualID = 21919; /* Идентификатор инструкции */

SELECT DISTINCT ContentID, KindOfContentID, KindOfLinkID, LinkID, OrderID, RTRIM(PreText), FormatID, TextStr, TB_TM_TypeManuals.ItemMpID, TB_TM_AutoTypeManuals.ItemMpID
FROM TB_TM_Panels
INNER JOIN TB_TM_BlockContent ON ManualID = @ManualID AND Panel = 1 AND FIELD = 1 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_TM_TypeManuals ON TB_TM_TypeManuals.Type_ID = @TypeID AND TB_TM_TypeManuals.ItemMpID = TB_TM_BlockContent.LinkID
LEFT OUTER JOIN TB_TM_AutoTypeManuals ON TB_TM_BlockContent.LinkID = TB_TM_AutoTypeManuals.ItemMpID AND TB_TM_AutoTypeManuals.Build_From <= @Build_From AND TB_TM_AutoTypeManuals.Class_ID = 1 AND (TB_TM_AutoTypeManuals.FuelTypeID = 1 OR TB_TM_AutoTypeManuals.FuelTypeID = 0)
ORDER BY OrderID


Все возможные работы для автомобиля, их нормочасы


SET @TypeID = 52179 ; /* Идентификатор автомобиля */
SET @COUNTRYID = 51; /* Страна применения - Россия */

SELECT WorkID, OnlyMaintenance, TB_LT_LT.ItemMpID, TB_LT_LT.KorID, BodyQualColID, TB_LT_LT.QualColID, WorkTime, CompositeTime, AddMdWork, WorkPos, ItemText.TextStr ItemMpText, KorText.TextStr KorText, QualText.TextStr QualText
FROM (
SELECT _int.WorkID, _int.OnlyMaintenance, _int.CountryID, _int.TypeID, _int.ItemMpID, _int.KorID, _int.BodyQualColID, _int.QualColID, _int.WorkTime, _int.CompositeTime, _int.AddMdWork, TB_LT_WorkPos.WorkPosText WorkPos
FROM TB_TreeType
INNER JOIN TB_LT_LT _int ON TB_TreeType.TypeID = @TypeID AND TB_TreeType.TypeID = _int.TypeID AND (_int.CountryID = @COUNTRYID OR _int.CountryID = 0)
LEFT OUTER JOIN TB_LT_LT _nat ON _int.TypeID = _nat.TypeID AND (_nat.CountryID = @COUNTRYID OR _nat.CountryID = 0) AND _nat.CountryID = @COUNTRYID AND _int.CountryID <> _nat.CountryID AND _int.ItemMpID = _nat.ItemMpID AND _int.KorID = _nat.KorID
LEFT OUTER JOIN TB_LT_LTExcludeCountry _ex ON _int.WorkID = _ex.WorkID AND _ex.ExcludeCountryID = @COUNTRYID
LEFT OUTER JOIN TB_LT_WorkPos ON _int.WorkPosID = TB_LT_WorkPos.WorkPosID
WHERE (_ex.WorkID IS NULL) AND (_nat.WorkID IS NULL)) TB_LT_LT
LEFT OUTER JOIN TB_Topic_ItemMp_TXT ItemText ON TB_LT_LT.ItemMpID = ItemText.ItemMpID AND ItemText.LangCode = 'ru'
LEFT OUTER JOIN TB_LT_Kor_TXT KorText ON TB_LT_LT.KorID = KorText.KorID AND KorText.LangCode = 'ru' AND KorText.KorID <> 4
LEFT OUTER JOIN TB_Qual_Qual_TXT QualText ON TB_LT_LT.QualColID = QualText.QualColID AND QualText.LangCode = 'ru' AND QualText.QualColID <> 0
ORDER BY TB_LT_LT.ItemMpID, TB_LT_LT.KorID, TB_LT_LT.BodyQualColID, TB_LT_LT.QualColID, TB_LT_LT.WorkTime