USE [KupiVIP];
SELECT
       [mi].[No_] AS [kupivip_products_code] --код товара
     , [miv].[Code] AS [kupivip_variants_code] -- размер
     , [miv].[Description] AS [kupivip_products_description]
     , [mi].[Base Unit of Measure] AS [kupivip_products_unit] --единица измерения
     , CASE [VA].[VAT Bus_ Posting Group] -- ндс бизнес группа и определение ставки по ней для учета покупки
           WHEN 'PURCH_RUS'
               THEN [mvpg].[Goods VAT] -- для договоров купли-продажи на ОСНО (российский поставщик)
           WHEN 'PURCH_USN'
               THEN 0 -- для договоров купли-продажи на УСН (российский поставщик)
           WHEN 'PURCH_IMP'
               THEN 0 -- 'ндс учитывается отдельно от покупки' -- для договоров купли-продажи, импорт
           WHEN 'PURCH_COM'
               THEN 0 -- для комиссионных поставщиков на ОСНО и для импортной комиссии (но такого в мамси нет вроде)
           WHEN 'NOT_IN_COM'
               THEN [mvpg].[Goods VAT] -- для комиссионных договоров на УСН
           ELSE [mvpg].[Goods VAT]
       END AS [kupivip_products_VAT]
     , [mvpg].[Goods VAT] AS [kupivip_products_VAT_sale]
     , [mi].[Commission Item] AS [kupivip_products_is_agensy] -- признак комиссионности

     , [mi].[Vendor No_] AS [kupivip_vendor_number] -- номер поставщика MV
     , IIF([mi].[Vendor Item No_] = '', [VA].[1C Employee No_], [mi].[Vendor Item No_]) AS [suppliers_id]  -- suppliers_id из Мамси [1C Employee No_]-- suppliers_id из Мамси
     , ISNULL([va].[AgreementNo], '') AS [kupivip_contracts_number] -- номер договора поставщика в нав мамси
     , ISNULL([va].[External ID], 0) AS [suppliers_contracts_id] -- suppliers_contracts_id из Мамси
     , ISNULL([va].[Agreement Type], 0) AS [suppliers_contracts_type]  -- 1 - купля-продажа , 2 - комиссия, 3 - агентский, 0 - Прочее

     , CASE
           WHEN [mi].[Last date modified] > [miv].[Last date modified]
               THEN [mi].[Last date modified]
           ELSE [miv].[Last date modified]
       END AS [date_modified]
FROM
     [dbo].[Mamsy$Item](NOLOCK) AS [mi]
     JOIN [dbo].[Mamsy$Item Variant](NOLOCK) AS [miv]
         ON [miv].[Item No_] = [mi].[No_]
     JOIN [dbo].[Mamsy$VAT Product Posting Group](NOLOCK) AS [mvpg]
         ON [mvpg].[code] = IIF([miv].[VAT Prod_ Posting Group] = '', [mi].[VAT Prod_ Posting Group], [miv].[VAT Prod_ Posting Group])
     LEFT JOIN
     (
      SELECT
             [t].[No_]
           , [t].[Commission Vendor]
           , [t].[1C Employee No_]
           , [t].[AgreementNo]
           , [mva].[Agreement Date]
           , [mva].[External ID] -- suppliers_contracts_id из Мамси
           , [mva].[Agreement Type]
           , [mva].[VAT Bus_ Posting Group]  -- ндс бизнес группа и определение ставки по ней для учета покупки
      FROM
           (
            SELECT
                   [mv].[No_] -- номер поставщика MV
                 , [mv].[Commission Vendor] -- комиссионность поставщика
                 , [mv].[1C Employee No_] -- suppliers_id из Мамси
                 , MAX([mva].[No_]) AS [AgreementNo] -- номер договора поставщика в нав мамси

            FROM
                 [dbo].[MAMSY$Vendor](NOLOCK) AS [mv]
                 JOIN [dbo].[MAMSY$Vendor Agreement](NOLOCK) AS [mva]
                     ON [mva].[Vendor No_] = [mv].[No_]
            WHERE [mva].[Active] = 1
            --  AND mv.No_ = 'MV00077'
            GROUP BY
                     [mv].[No_] -- номер поставщика MV
                   , [mv].[Commission Vendor] -- комиссионность поставщика
                   , [mv].[1C Employee No_]
           ) AS [t]
           JOIN [dbo].[MAMSY$Vendor Agreement](NOLOCK) AS [mva]
               ON [mva].[No_] = [t].[AgreementNo]
     ) AS [va]
         ON [va].[No_] = [mi].[Vendor No_]
-- WHERE mi.[Last date modified] >= '2020-09-09'  OR miv.[Last date modified] >= '2020-09-09';
WHERE [mi].[No_] IN
                    (
                     SELECT DISTINCT
                            [pil].[No_] AS [kupivip_products_code]
                     -- , [pil].[Variant Code] AS [kupivip_variants_code]

                     FROM
                          [Mamsy$Purch_ Inv_ Line](NOLOCK) AS [pil]
                     UNION
                     SELECT DISTINCT
                            [sil].[No_] AS [kupivip_products_code]
                     FROM
                          [dbo].[Privat Trade$Sales Invoice Line](NOLOCK) AS [sil]
                          JOIN [dbo].[Privat Trade$Sales Invoice Header](NOLOCK) AS [sih]
                              ON [sil].[Document No_] = [sih].[No_]
                     WHERE [sih].[Host Code] = 'MSY'
                    );