Выгрузка материалов в 1С с учетом оптимизации

функция возвращает расход учетом оптимизации а также количество использованное из остатков

USE [ecad_venta]
GO

/****** Object:  UserDefinedFunction [dbo].[f_work_flow_by_mf]    Script Date: 09/21/2013 16:35:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter FUNCTION [dbo].[f_1c_mc_calc_optim] (
    @idmanufactdoc int
)   
RETURNS  table

as 

return 

select 
    odp.id_good, 
    odp.idgoodgroup, 
    odp.marking, 
    odp.marking_id,
    odp.idmanufact,
    odp.manuf_dt,
    odp.dtcre,
    odp.mc_qu,
    odp.odp_qu,
    odpic.whiplen as store_whiplen,
    odpic.wh_s as store_wh_s,
    odpic_ost.whiplen as ost_whiplen,
    odpic_ost.wh_s as ost_wh_s
from 

(

select 
    mc.id_good, 
    mc.idgoodgroup, 
    mc.marking, 
    mc.marking_id,
    mc.idmanufact,
    mc.manuf_dt,
    mc.dtcre,
    SUM (mc.qu) as mc_qu,
    SUM(odp.qu*thick/mc.good_measure_factor) as odp_qu
from 
    f_1c_mc_calc (@idmanufactdoc) mc
    left join optimdocpos odp on 
        mc.idmodelcalc=odp.idmodelcalc 
        and odp.deleted is null

group by
    mc.id_good, 
    mc.idgoodgroup, 
    mc.marking, 
    mc.marking_id,
    mc.idmanufact,
    mc.manuf_dt,
    mc.dtcre

) odp
left join 
(
    select
        odp.idgood,
        SUM(whiplen/ms.factor) as whiplen,
        SUM(odp.width*odp.height/ms.factor) as wh_s
    from
        optimdocpic odp
        left join good g on odp.idgood=g.idgood
        left join measure ms on g.idmeasure=ms.idmeasure
    where
        odp.deleted is null
        and idoptimdocgoodin is null
        and idoptimdoc in (select idoptimdoc from optimdoc where idmanufactdoc=@idmanufactdoc and deleted is null)
    group by
        odp.idgood
) odpic on odp.id_good=odpic.idgood

left join 
(
    select
        odp.idgood,
        SUM(odpgin.thick/ms.factor) as whiplen,
        SUM(odpgin.width*odpgin.height/ms.factor) as wh_s
    from
        optimdocpic odp
        left join optimdocgoodin odpgin on odp.idoptimdocgoodin=odpgin.idoptimdocgoodin
        left join good g on odp.idgood=g.idgood
        left join measure ms on g.idmeasure=ms.idmeasure
    where
        odp.deleted is null
        and odp.idoptimdocgoodin is not null
        and odp.idoptimdoc in (select idoptimdoc from optimdoc where idmanufactdoc=@idmanufactdoc and deleted is null)
    group by
        odp.idgood
) odpic_ost on odp.id_good=odpic_ost.idgood

where odpic.idgood is not null

GO

функция возвращает расход по modelcalc, orderitem, ordergood в форме для передачи в 1С

USE [ecad_venta]
GO

/****** Object:  UserDefinedFunction [dbo].[f_1c_mc_calc]    Script Date: 10/15/2013 12:52:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[f_1c_mc_calc] (
    @idmanufactdoc int
)   
RETURNS  table

as 

return 

SELECT 
    g.idgood AS id_good, 
    g.idgoodgroup,
    g.marking, 
    g.extmarking AS marking_id, 
    g.idmeasure as good_idmeasure,
    (
        SELECT     factor
        FROM          dbo.measure
        WHERE      (idmeasure = g.idmeasure)
    ) as good_measure_factor,
    g.idgoodoptim as good_idgoodoptim,
    mc.qustore/
    (
        SELECT     factor
        FROM          dbo.measure
        WHERE      (idmeasure = g.idmeasure)
    ) AS qu, 
    oi.idorder, 
    mc.idorderitem, 
    mc.idmodelcalc,
    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) 

        from seller s
        where s.idseller=(select idseller from orders where idorder=oi.idorder)
     ) as seller_name
FROM         
    (
        select idgood, idorderitem, idmodelcalc, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2, addstr4, deleted from view_modelcalc
        union all
        select idgood, idorderitem, null, qu * orderitem_qu AS qu, qustore * orderitem_qu AS qustore, addstr2,'' as addstr4, deleted from view_ordergood
        union all
        select idgood, idorderitem, null, qu, qustore, addstr2,'' as addstr4, deleted from 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 mc.deleted is null  
    AND (md.deleted IS NULL) 
    AND (md_ps.deleted IS NULL) 
    AND (oi.deleted IS NULL)
    and md.idmanufactdoc=@idmanufactdoc

GO

venta-holding / windraw_2013

Выгрузка материалов в 1С с учетом оптимизации #16