view_1c_good_calc для взаимодействия с 1С

dnclive commented 11 years ago

Здесь будет вестись лог по изменению view_1c_good_calc

dnclive commented 11 years ago

текущая версия view_1c_good_calc 22.10.2013 - добавлен очередной костыль

SELECT     g.idgood AS id_good, g.idgoodgroup, g.marking, g.extmarking AS marking_id, mc.qustore /
                          (SELECT     factor
                            FROM          dbo.measure
                            WHERE      (idmeasure = g.idmeasure)) AS qu, oi.idorder, mc.idorderitem, md.idmanufactdoc AS idmanufact, md.dtdoc AS manuf_dt, GETDATE() AS dtcre, '' AS store, 

--костыль если материал - это фурнитура рама
--и при этом нет самой рамы у изделия - то считаем - что это монтаж
                      CASE WHEN g.idgoodtype2 IN (37, 38) AND
                          (SELECT top1  idmodelcalc
                            FROM          view_modelcalc
                            WHERE      deleted IS NULL AND good_idgoodtype2 = 39 AND idorderitem = oi.idorderitem) IS NULL 
                      THEN 'Монтаж' 

ELSE (CASE WHEN mc.addstr2 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr2 = 'ПВХ' THEN 'ПВХ' WHEN mc.addstr2 = 'Допы' THEN 'Допы' WHEN mc.addstr2
                       = 'Стеклопакеты' THEN 'Стеклопакеты' WHEN mc.addstr2 = 'Москитная сетка' THEN 'Москитная сетка' WHEN mc.addstr4 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr4
                       = 'ПВХ' THEN 'ПВХ' WHEN mc.addstr4 = 'Допы' THEN 'Допы' WHEN mc.addstr4 = 'Стеклопакеты' THEN 'Стеклопакеты' WHEN mc.addstr4 = 'Москитная сетка' THEN
                       'Москитная сетка' WHEN g.idgoodtype2 IN (5) THEN 'Монтаж' WHEN gt.idgoodtype IN (4, 1, 2, 11, 15, 17, 16) THEN 'ПВХ' WHEN gt.idgoodtype IN (6, 8) 
                      THEN 'Допы' WHEN gt.idgoodtype IN (5) THEN 'Монтаж' WHEN gt.idgoodtype IN (9) THEN 'Стеклопакеты' WHEN gt.idgoodtype IN (10) 
                      THEN 'Москитная сетка' WHEN (g.name LIKE 'ш4х70' OR
                      g.name LIKE 'ДГ%' OR
                      g.name LIKE '06K040' OR
                      g.name LIKE 'ш4.2х16' OR
                      g.name LIKE 'ш4.2х51' OR
                      g.name LIKE 'ш5х90' OR
                      g.name LIKE '5_5x76' OR
                      g.marking = 'нак_под_д' OR
                      g.marking = 'нак_под_м' OR
                      g.marking = 'нак_под_зд' OR
                      g.marking = 'нак_под_б' OR
                      g.marking = 'нак_под_lds' OR
                      g.marking = '8002_б' OR
                      g.marking = '8002_к' OR
                      g.marking = '8004_б' OR
                      g.marking = '8004_к' OR
                      g.marking = '8002_б_м' OR
                      g.marking = '8002_к_м' OR
                      g.marking = 'заг_анк_к' OR
                      g.marking = 'заг_анк_б' OR
                      g.marking = 'нак_лич_б' OR
                      g.marking = 'нак_лич_к' OR
                      g.marking = 'нак_лич_с' OR
                      g.marking = 'FNL0004.07' OR
                      g.marking = 'FNL0005.07' OR
                      g.marking = 'ILL0020' OR
                      g.marking = 'FNL0006.07') THEN 'Монтаж' WHEN (g.name LIKE 'KS-3925_') THEN 'ПВХ' ELSE mc.addstr4 END) END AS unit, o.name AS order_num, 
                      'Изделие ' + CONVERT(varchar(1000), oi.numpos) + ' ' +
                          (SELECT     name
                            FROM          dbo.constructiontype
                            WHERE      (oi.idconstructiontype = idconstructiontype)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system
                            WHERE      (oi.idprofsys = idsystem)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system AS system_1
                            WHERE      (oi.idfurnsys = idsystem)) AS product, oi.qu AS product_num,
                          (SELECT     TOP (1) (CASE WHEN s.name LIKE 'ДилОтд%' THEN 'Дил' WHEN s.name LIKE 'ОбъектОтд%' THEN 'Об' ELSE 'Роз' END) AS Expr1
                            FROM          dbo.seller AS s
                            WHERE      (idseller =
                                                       (SELECT     idseller
                                                         FROM          dbo.orders
                                                         WHERE      (idorder = oi.idorder)))) AS seller_name
FROM         (SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, addstr4, deleted
                       FROM          dbo.view_modelcalc
                       UNION ALL
                       SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.view_ordergood
                       UNION ALL
                       SELECT     idgood, idorderitem, qu, qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.orderitem) AS mc LEFT OUTER JOIN
                      dbo.good AS g ON mc.idgood = g.idgood LEFT OUTER JOIN
                      dbo.goodtype AS gt ON g.idgoodtype = gt.idgoodtype LEFT OUTER JOIN
                      dbo.orderitem AS oi ON mc.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.orders AS o ON oi.idorder = o.idorder LEFT OUTER JOIN
                      dbo.manufactdocpos AS md_ps ON md_ps.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.manufactdoc AS md ON md_ps.idmanufactdoc = md.idmanufactdoc
WHERE     (o.deleted IS NULL) AND (md.deleted IS NULL) AND (md_ps.deleted IS NULL) AND (oi.deleted IS NULL) AND (mc.deleted IS NULL)

dnclive commented 11 years ago

потребность материалов view_1c_good_calc


select 
    gc.idorder,
    id_good,
    marking,
    marking_id,
    od.plandate,
    SUM(qu) as qu

 from view_1c_good_calc gc
 left join orderdiraction od on gc.idorder=od.idorder and od.iddiraction=28 and od.deleted is null

where 
 gc.id_good is not null
 --and gc.idorder /*in (95759,95760,95770)*/=99012

 group by gc.idorder, id_good, marking, marking_id, od.plandate

потребность формируемая из modelcalc view_1c_mc_good_needs

select 
    mc.idorder,
    mc.idgood as id_good,
    g.marking as marking,
    g.extmarking as marking_id,
    od.plandate,
    sum(mc.qustore/ms.factor) as qu
from 
    modelcalc mc
    left join good g on mc.idgood=g.idgood
    left join measure ms on ms.idmeasure=g.idmeasure
    left join orderitem oi on oi.idorderitem=mc.idorderitem
    left join orders o on o.idorder=mc.idorder
    left join orderdiraction od on mc.idorder=od.idorder and od.iddiraction=28 and od.deleted is null
where 

    mc.deleted is null
    and oi.deleted is null
    and o.deleted is null

    --and o.idorder=99012

 group by mc.idorder, mc.idgood, g.marking, g.extmarking, od.plandate

потребность уже выгруженная из WD в 1С попадает в tabres эта view возвратит ее в удобном для передачи в 1С виде view_1c_res_good_needs


select
    res.idorder,
    g.idgood as id_good,
    g.marking,
    g.extmarking,
    od.plandate,
    sum(res.qu) as qu
from 
    tabres res
    left join good g on res.idgood=g.idgood
    left join orderdiraction od on res.idorder=od.idorder and od.iddiraction=28 and od.deleted is null
where
    res.deleted is null
    and res.idgood is not null
    and res.name ='good_needs'
group by res.idorder, od.plandate, g.idgood, g.marking, g.extmarking

dnclive commented 11 years ago

view_1c_good


SELECT     
    g.idgood AS id, 
    gg.idgoodgroup, 
    gg.parentid, 
    gg.parent_group, 
    gg.group_name, 
    g.marking, 
    g.extmarking AS marking_id, 
    g.name,
      (SELECT     name
        FROM          dbo.color
        WHERE      (idcolor = g.idcolor1)) AS colorin,
      (SELECT     name
        FROM          dbo.color AS color_1
        WHERE      (idcolor = g.idcolor2)) AS colorout, g.packing AS pack_stand,
      (SELECT     name
        FROM          dbo.measure
        WHERE      (g.idmeasure = idmeasure)) AS measure, g.price1 AS price, CAST(g.price1 * dbo.get_valutrate(g.idvalut, GETDATE()) AS numeric(15, 4)) AS price_base,
      (SELECT     name
        FROM          dbo.goodtype
        WHERE      (g.idgoodtype = idgoodtype)) AS good_type,
      (SELECT     name
        FROM          dbo.system
        WHERE      (g.idsystem = idsystem)) AS system, g.waste
FROM         dbo.good AS g LEFT OUTER JOIN
                      dbo.view_1c_good_group AS gg ON g.idgood = gg.id
WHERE     (g.deleted IS NULL) and g.extmarking is not null and g.extmarking not like ''

dnclive commented 11 years ago

view_1c_lamination - используется для экспорта материалов для ламинации из WD в 1C

SELECT     
    gc.id_good, 
    gc.idgoodgroup, 
    gc.marking,
    gc.marking_id AS marking_id, 
    gc.qu AS qu,  
    gc.idorder, 
    gc.idorderitem, 
    gc.idmanufact, 
    gc.manuf_dt,
    gc.dtcre, 
    gc.store, 
    gc.unit, 
    gc.order_num, 
    gc.product, 
    gc.product_num, 
    gc.seller_name
FROM         
    dbo.view_1c_good_calc AS gc 
    LEFT OUTER JOIN dbo.good AS g ON gc.id_good = g.idgood
WHERE     
    (g.idgoodtype = 1 OR g.idgoodtype = 6) 
    AND (g.idcolor1 NOT LIKE '2') 
    AND (g.idcolor1 NOT LIKE '63') 
    AND (g.deleted IS NULL) OR
    (g.idgoodtype = 1 OR g.idgoodtype = 6) 
    AND (g.idcolor1 NOT LIKE '63') 
    AND (g.deleted IS NULL) 
    AND (g.idcolor2 NOT LIKE '2') OR
    (g.idgoodtype = 1 OR  g.idgoodtype = 6) 
    AND (g.idcolor1 NOT LIKE '2') 
    AND (g.deleted IS NULL) 
    AND (g.idcolor2 NOT LIKE '63') OR
   (g.idgoodtype = 1 OR g.idgoodtype = 6) 
   AND (g.deleted IS NULL) 
   AND (g.idcolor2 NOT LIKE '2') 
   AND (g.idcolor2 NOT LIKE '63')

dyominde commented 11 years ago

15.07.2013 17:28 после внезапного улучшения от Вовы

USE [ecad_venta]
GO

/****** Object:  View [dbo].[view_1c_good_calc]    Script Date: 07/15/2013 17:25:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[view_1c_good_calc]
AS
SELECT     g.idgood AS id_good, g.idgoodgroup, g.marking, g.extmarking AS marking_id, mc.qustore /
                          (SELECT     factor
                            FROM          dbo.measure
                            WHERE      (idmeasure = g.idmeasure)) AS qu, oi.idorder, mc.idorderitem, md.idmanufactdoc AS idmanufact, md.dtdoc AS manuf_dt, GETDATE() AS dtcre, '' AS store, 
                      (CASE WHEN mc.addstr2 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr2 = 'ПВХ' THEN 'ПВХ' WHEN mc.addstr2 = 'Допы' THEN 'Допы' WHEN mc.addstr2 = 'Стеклопакеты'
                       THEN 'Стеклопакеты' WHEN mc.addstr2 = 'Москитная сетка' THEN 'Москитная сетка' WHEN mc.addstr4 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr4 = 'ПВХ' THEN
                       'ПВХ' WHEN mc.addstr4 = 'Допы' THEN 'Допы' WHEN mc.addstr4 = 'Стеклопакеты' THEN 'Стеклопакеты' WHEN mc.addstr4 = 'Москитная сетка' THEN 'Москитная сетка'
                       WHEN gt.idgoodtype IN (4, 1, 2, 11, 15, 17, 16) THEN 'ПВХ' WHEN gt.idgoodtype IN (6, 8) THEN 'Допы' WHEN gt.idgoodtype IN (5) 
                      THEN 'Монтаж' WHEN g.idgoodtype2 IN (5) THEN 'Монтаж' WHEN gt.idgoodtype IN (9) THEN 'Стеклопакеты' WHEN gt.idgoodtype IN (10) 
                      THEN 'Москитная сетка' WHEN (g.name LIKE 'ш4х70' OR
                      g.name LIKE 'ДГ%' OR
                      g.name LIKE '06K040' OR
                      g.name LIKE 'ш4.2х16' OR
                      g.name LIKE 'ш4.2х51' OR
                      g.name LIKE 'ш5х90' OR
                      g.name LIKE '5_5x76' OR
                      g.marking = 'нак_под_д' OR
                      g.marking = 'нак_под_м' OR
                      g.marking = 'нак_под_зд' OR
                      g.marking = 'нак_под_б' OR
                      g.marking = 'нак_под_lds' OR
                      g.marking = '8002_б' OR
                      g.marking = '8002_к' OR
                      g.marking = '8004_б' OR
                      g.marking = '8004_к' OR
                      g.marking = '8002_б_м' OR
                      g.marking = '8002_к_м' OR
                      g.marking = 'заг_анк_к' OR
                      g.marking = 'заг_анк_б' OR
                      g.marking = 'нак_лич_б' OR
                      g.marking = 'нак_лич_к' OR
                      g.marking = 'нак_лич_с' OR
                      g.marking = 'FNL0004.07' OR
                      g.marking = 'FNL0005.07' OR
                      g.marking = 'ILL0020' OR
                      g.marking = 'FNL0006.07') THEN 'Монтаж' WHEN (g.name LIKE 'KS-3925_') THEN 'ПВХ' ELSE mc.addstr4 END) AS unit, o.name AS order_num, 
                      'Изделие ' + CONVERT(varchar(1000), oi.numpos) + ' ' +
                          (SELECT     name
                            FROM          dbo.constructiontype
                            WHERE      (oi.idconstructiontype = idconstructiontype)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system
                            WHERE      (oi.idprofsys = idsystem)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system AS system_1
                            WHERE      (oi.idfurnsys = idsystem)) AS product, oi.qu AS product_num,
                          (SELECT     TOP (1) (CASE WHEN s.name LIKE 'ДилОтд%' THEN 'Дил' WHEN s.name LIKE 'ОбъектОтд%' THEN 'Об' ELSE 'Роз' END) AS Expr1
                            FROM          dbo.seller AS s
                            WHERE      (idseller =
                                                       (SELECT     idseller
                                                         FROM          dbo.orders
                                                         WHERE      (idorder = oi.idorder)))) AS seller_name
FROM         (SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, addstr4, deleted
                       FROM          dbo.view_modelcalc
                       UNION ALL
                       SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.view_ordergood
                       UNION ALL
                       SELECT     idgood, idorderitem, qu, qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.orderitem) AS mc LEFT OUTER JOIN
                      dbo.good AS g ON mc.idgood = g.idgood LEFT OUTER JOIN
                      dbo.goodtype AS gt ON g.idgoodtype = gt.idgoodtype LEFT OUTER JOIN
                      dbo.orderitem AS oi ON mc.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.orders AS o ON oi.idorder = o.idorder LEFT OUTER JOIN
                      dbo.manufactdocpos AS md_ps ON md_ps.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.manufactdoc AS md ON md_ps.idmanufactdoc = md.idmanufactdoc
WHERE     (o.deleted IS NULL) AND (md.deleted IS NULL) AND (md_ps.deleted IS NULL) AND (oi.deleted IS NULL) AND (mc.deleted IS NULL)

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[26] 4[4] 2[54] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "g"
            Begin Extent = 
               Top = 6
               Left = 236
               Bottom = 114
               Right = 399
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "gt"
            Begin Extent = 
               Top = 6
               Left = 437
               Bottom = 114
               Right = 597
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "oi"
            Begin Extent = 
               Top = 6
               Left = 635
               Bottom = 114
               Right = 804
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "o"
            Begin Extent = 
               Top = 6
               Left = 842
               Bottom = 114
               Right = 1012
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "md_ps"
            Begin Extent = 
               Top = 114
               Left = 38
               Bottom = 222
               Right = 205
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "md"
            Begin Extent = 
               Top = 145
               Left = 279
               Bottom = 253
               Right = 457
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "mc"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         En' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_1c_good_calc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'd
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 15
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_1c_good_calc'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_1c_good_calc'
GO

dyominde commented 11 years ago

Оптимизированная вьюха

SELECT     g.idgood AS id_good,
           g.idgoodgroup,
           g.marking,
           g.extmarking AS marking_id,
           mc.qustore /(SELECT factor FROM dbo.measure WHERE (idmeasure = g.idmeasure)) AS qu,
           oi.idorder,
           mc.idorderitem,
           md.idmanufactdoc AS idmanufact,
           md.dtdoc AS manuf_dt,
           GETDATE() AS dtcre,
           '' AS store,
           (CASE WHEN g.idgoodtype2 IN (5) THEN 'Монтаж' 
                 WHEN gt.idgoodtype IN (4, 1, 2, 11, 15, 17, 16) THEN 'ПВХ' 
                 WHEN gt.idgoodtype IN (6, 8) THEN 'Допы' 
                 WHEN gt.idgoodtype IN (5) THEN 'Монтаж'                  
                 WHEN gt.idgoodtype IN (9) THEN 'Стеклопакеты' 
                 WHEN gt.idgoodtype IN (10) THEN 'Москитная сетка' 
                 WHEN mc.addstr2 = 'Монтаж' THEN 'Монтаж' 
                 WHEN mc.addstr2 = 'ПВХ' THEN 'ПВХ' 
                 WHEN mc.addstr2 = 'Допы' THEN 'Допы' 
                 WHEN mc.addstr2 = 'Стеклопакеты' THEN 'Стеклопакеты' 
                 WHEN mc.addstr2 = 'Москитная сетка' THEN 'Москитная сетка'
                 WHEN mc.addstr4 = 'Монтаж' THEN 'Монтаж' 
                 WHEN mc.addstr4 = 'ПВХ' THEN 'ПВХ' 
                 WHEN mc.addstr4 = 'Допы' THEN 'Допы' 
                 WHEN mc.addstr4 = 'Стеклопакеты' THEN 'Стеклопакеты' 
                 WHEN mc.addstr4 = 'Москитная сетка' THEN 'Москитная сетка'                 
                 WHEN (g.name LIKE 'ш4х70' OR
                      g.name LIKE 'ДГ%' OR
                      g.name LIKE '06K040' OR
                      g.name LIKE 'ш4.2х16' OR
                      g.name LIKE 'ш4.2х51' OR
                      g.name LIKE 'ш5х90' OR
                      g.name LIKE '5_5x76' OR
                      g.marking = 'нак_под_д' OR
                      g.marking = 'нак_под_м' OR
                      g.marking = 'нак_под_зд' OR
                      g.marking = 'нак_под_б' OR
                      g.marking = 'нак_под_lds' OR
                      g.marking = '8002_б' OR
                      g.marking = '8002_к' OR
                      g.marking = '8004_б' OR
                      g.marking = '8004_к' OR
                      g.marking = '8002_б_м' OR
                      g.marking = '8002_к_м' OR
                      g.marking = 'заг_анк_к' OR
                      g.marking = 'заг_анк_б' OR
                      g.marking = 'нак_лич_б' OR
                      g.marking = 'нак_лич_к' OR
                      g.marking = 'нак_лич_с' OR
                      g.marking = 'FNL0004.07' OR
                      g.marking = 'FNL0005.07' OR
                      g.marking = 'ILL0020' OR
                      g.marking = 'FNL0006.07') THEN 'Монтаж' 
                 WHEN (g.name LIKE 'KS-3925_') THEN 'ПВХ' 
                 ELSE mc.addstr4 END) AS unit, 
                 o.name AS order_num, 
                 'Изделие ' + CONVERT(varchar(1000), oi.numpos) + ' ' +
                  (SELECT name FROM dbo.constructiontype WHERE (oi.idconstructiontype = idconstructiontype)) + ' ' +
                  (SELECT name FROM dbo.system WHERE (oi.idprofsys = idsystem)) + ' ' +
                  (SELECT name FROM dbo.system AS system_1 WHERE (oi.idfurnsys = idsystem)) AS product, 
                 oi.qu AS product_num,
                  (SELECT TOP (1) (CASE WHEN s.name LIKE 'ДилОтд%' THEN 'Дил' 
                                        WHEN s.name LIKE 'ОбъектОтд%' THEN 'Об' 
                                        ELSE 'Роз' END) AS Expr1
                   FROM dbo.seller AS s
                   WHERE (idseller = (SELECT idseller FROM dbo.orders WHERE (idorder = oi.idorder)))) AS seller_name
FROM (SELECT idgood, idorderitem, qu*orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, addstr4, deleted
      FROM dbo.view_modelcalc
      UNION ALL
      SELECT idgood, idorderitem, qu*orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, '' AS addstr4, deleted
      FROM dbo.view_ordergood
      UNION ALL
      SELECT idgood, idorderitem, qu, qustore, addstr2, '' AS addstr4, deleted
      FROM dbo.orderitem
      ) AS mc 
 LEFT OUTER JOIN dbo.good AS g ON mc.idgood = g.idgood 
 LEFT OUTER JOIN dbo.goodtype AS gt ON g.idgoodtype = gt.idgoodtype 
 LEFT OUTER JOIN dbo.orderitem AS oi ON mc.idorderitem = oi.idorderitem 
 LEFT OUTER JOIN dbo.orders AS o ON oi.idorder = o.idorder 
 LEFT OUTER JOIN dbo.manufactdocpos AS md_ps ON md_ps.idorderitem = oi.idorderitem 
 LEFT OUTER JOIN dbo.manufactdoc AS md ON md_ps.idmanufactdoc = md.idmanufactdoc

WHERE  (o.deleted IS NULL) 
        AND (md.deleted IS NULL) 
        AND (md_ps.deleted IS NULL)
        AND (oi.deleted IS NULL) 
        AND (mc.deleted IS NULL)

dnclive commented 11 years ago

необходимо брать сумарное количество задействованного материала из оптимизации для материалов попадающих в оптимизацию как qu хлыстов * длину хлыста вместо qustore

dnclive commented 11 years ago

вариант от 19_09_2013


USE [ecad_venta]
GO

/****** Object:  View [dbo].[view_1c_good_calc]    Script Date: 09/19/2013 12:20:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[view_1c_good_calc]
AS
SELECT     g.idgood AS id_good, g.idgoodgroup, g.marking, g.extmarking AS marking_id, mc.qustore /
                          (SELECT     factor
                            FROM          dbo.measure
                            WHERE      (idmeasure = g.idmeasure)) AS qu, oi.idorder, mc.idorderitem, md.idmanufactdoc AS idmanufact, md.dtdoc AS manuf_dt, GETDATE() AS dtcre, '' AS store, 
                      (CASE WHEN mc.addstr2 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr2 = 'ПВХ' THEN 'ПВХ' WHEN mc.addstr2 = 'Допы' THEN 'Допы' WHEN mc.addstr2 = 'Стеклопакеты'
                       THEN 'Стеклопакеты' WHEN mc.addstr2 = 'Москитная сетка' THEN 'Москитная сетка' WHEN mc.addstr4 = 'Монтаж' THEN 'Монтаж' WHEN mc.addstr4 = 'ПВХ' THEN
                       'ПВХ' WHEN mc.addstr4 = 'Допы' THEN 'Допы' WHEN mc.addstr4 = 'Стеклопакеты' THEN 'Стеклопакеты' WHEN mc.addstr4 = 'Москитная сетка' THEN 'Москитная сетка'
                       WHEN g.idgoodtype2 IN (5) THEN 'Монтаж' WHEN gt.idgoodtype IN (4, 1, 2, 11, 15, 17, 16) THEN 'ПВХ' WHEN gt.idgoodtype IN (6, 8) 
                      THEN 'Допы' WHEN gt.idgoodtype IN (5) THEN 'Монтаж' WHEN gt.idgoodtype IN (9) THEN 'Стеклопакеты' WHEN gt.idgoodtype IN (10) 
                      THEN 'Москитная сетка' WHEN (g.name LIKE 'ш4х70' OR
                      g.name LIKE 'ДГ%' OR
                      g.name LIKE '06K040' OR
                      g.name LIKE 'ш4.2х16' OR
                      g.name LIKE 'ш4.2х51' OR
                      g.name LIKE 'ш5х90' OR
                      g.name LIKE '5_5x76' OR
                      g.marking = 'нак_под_д' OR
                      g.marking = 'нак_под_м' OR
                      g.marking = 'нак_под_зд' OR
                      g.marking = 'нак_под_б' OR
                      g.marking = 'нак_под_lds' OR
                      g.marking = '8002_б' OR
                      g.marking = '8002_к' OR
                      g.marking = '8004_б' OR
                      g.marking = '8004_к' OR
                      g.marking = '8002_б_м' OR
                      g.marking = '8002_к_м' OR
                      g.marking = 'заг_анк_к' OR
                      g.marking = 'заг_анк_б' OR
                      g.marking = 'нак_лич_б' OR
                      g.marking = 'нак_лич_к' OR
                      g.marking = 'нак_лич_с' OR
                      g.marking = 'FNL0004.07' OR
                      g.marking = 'FNL0005.07' OR
                      g.marking = 'ILL0020' OR
                      g.marking = 'FNL0006.07') THEN 'Монтаж' WHEN (g.name LIKE 'KS-3925_') THEN 'ПВХ' ELSE mc.addstr4 END) AS unit, o.name AS order_num, 
                      'Изделие ' + CONVERT(varchar(1000), oi.numpos) + ' ' +
                          (SELECT     name
                            FROM          dbo.constructiontype
                            WHERE      (oi.idconstructiontype = idconstructiontype)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system
                            WHERE      (oi.idprofsys = idsystem)) + ' ' +
                          (SELECT     name
                            FROM          dbo.system AS system_1
                            WHERE      (oi.idfurnsys = idsystem)) AS product, oi.qu AS product_num,
                          (SELECT     TOP (1) (CASE WHEN s.name LIKE 'ДилОтд%' THEN 'Дил' WHEN s.name LIKE 'ОбъектОтд%' THEN 'Об' ELSE 'Роз' END) AS Expr1
                            FROM          dbo.seller AS s
                            WHERE      (idseller =
                                                       (SELECT     idseller
                                                         FROM          dbo.orders
                                                         WHERE      (idorder = oi.idorder)))) AS seller_name
FROM         (SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, addstr4, deleted
                       FROM          dbo.view_modelcalc
                       UNION ALL
                       SELECT     idgood, idorderitem, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.view_ordergood
                       UNION ALL
                       SELECT     idgood, idorderitem, qu, qustore, addstr2, '' AS addstr4, deleted
                       FROM         dbo.orderitem) AS mc LEFT OUTER JOIN
                      dbo.good AS g ON mc.idgood = g.idgood LEFT OUTER JOIN
                      dbo.goodtype AS gt ON g.idgoodtype = gt.idgoodtype LEFT OUTER JOIN
                      dbo.orderitem AS oi ON mc.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.orders AS o ON oi.idorder = o.idorder LEFT OUTER JOIN
                      dbo.manufactdocpos AS md_ps ON md_ps.idorderitem = oi.idorderitem LEFT OUTER JOIN
                      dbo.manufactdoc AS md ON md_ps.idmanufactdoc = md.idmanufactdoc
WHERE     (o.deleted IS NULL) AND (md.deleted IS NULL) AND (md_ps.deleted IS NULL) AND (oi.deleted IS NULL) AND (mc.deleted IS NULL)

GO

venta-holding / windraw_2013

view_1c_good_calc для взаимодействия с 1С #11