USE [CRM0100_SMARTTECH]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_accounting_sales]    Script Date: 11.03.2019 22:35:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[fn_accounting_sales] (  
    @accountMask		varchar(max), 
    @itemcode			varchar(50) = null, 
    @countrycode		varchar(50) = null, 
    @organisationUnit   varchar(50) = null,
    @firmNR             varchar(50) = null,
    @taxRate            varchar(50) = null,
    @invoiceCategory    varchar(50) = null,
    @pageOrder          varchar(50) = null  ,
    @locationCode       varchar(50) = null,
    @invoiceLineID      varchar(50) = null  
)
RETURNS varchar(max)
AS
/*
do pobrania kont ksiegowych
*/
BEGIN
declare @change         varchar(max),
        @itemMainGroup  varchar(100),
        @itemGroupAcounting varchar(100),
        @checkWZ_6          int
--if @accountMask like '%itemcode%'
--@accountmask = replace(@accountmask)'

if @invoiceLineID is not null-- wyjątek opisany #138 KC 2018-05-02
begin
        select @checkWZ_6 = count(*)
        from   document.invoiceLine i
        inner join document.documentLine dl on i.documentLineID = dl.documentLineID
        inner join document.document     d  on d.documentID = dl.documentID
        inner JOIN wms.location          L  on l.warehouseID= d.warehouseID
        inner join firm.firm             f  on f.firmID     = d.firmID
        where i.invoiceLineID = @invoiceLineID
        and   d.documentType = 'WZ'
        and   l.locationCode = '6'
        and   f.firmNR in ('1','2063')
end

/********************************************************************************************************/
            /*******************************WYJĄTKI*****************************************/
            /****************************PODMIANA MASKI*************************************/

 

select 
         @itemMainGroup = accounting
from  wms.item x
INNER join wms.itemClass ic on ic.itemID=x.itemID and ic.mainGroup=1
INNER JOIN common.dictionary d ON d.dictionaryKey= ic.itemSubCategoryID and d.status>-1 and d.dictionaryName='itemsubcategory'
where x.itemID = @itemcode

if @locationCode='6' and @invoiceCategory IN  ('T','KT') and @checkWZ_6 > 0 and @firmNR = '1'-- wyjątek opisany #138 KC 2018-05-02
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '707-01'
                    END
    END 
if @locationCode='6' and @invoiceCategory IN  ('T','KT') and @checkWZ_6 > 0 and @firmNR = '2063'-- wyjątek opisany #138 KC 2018-05-02
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '707-02'
                    END
    END  
if @locationCode='3' and @invoiceCategory IN  ('T','KT')
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '700-itemcode'
                    END
    END 

if @locationCode='3' and @invoiceCategory IN  ('ET','WT','KET','KWT')
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '701-itemcode'
                    END
    END 
if @locationCode='4' and @invoiceCategory IN  ('M','KM')
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '740'
                    END
    END 

if @locationCode='4' and @invoiceCategory IN  ('ET','WT','KET','KWT')
    BEGIN
                    IF @pageOrder = 1 
                        BEGIN
                         SET @accountMask = '741'
                    END
    END 

/*  faktura krajowa np maszyny + indeks towarowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='740'/*towar*/ and @invoiceCategory IN  ('M','KM')      /*typ faktury krajowych*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '740'
                END     
        END

/*  faktura wspólnotowa np maszyny + indeks towarowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='740'/*towar*/  and @invoiceCategory IN  ('WM','KWM')      /*typ faktury krajowych*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '741'
                END     
        END
/*  faktura eksportowa np maszyny + indeks towarowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='740'/*towar*/  and @invoiceCategory IN  ('EM','KEM')      /*typ faktury krajowych*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '741'
                END     
        END
    
/*  faktura krajowa np maszyny + indeks zaliczkowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='034'/*zaliczki*/ and @invoiceCategory IN  ('M','U','FL','T','S','KM','KS','KU','KT','KFL')      /*typ faktury krajowych*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '840-firmNr'
                END     
            ELSE IF  @pageOrder = 2 
                BEGIN
                    SET @accountMask = '222-taxRate'
                END                     
        END

/*  faktura krajowa np maszyny + indeks USŁUGOWY  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup IN ('023','035','032','020','030','031')/*USLUGI*/ and @invoiceCategory IN  ('M','FL','T','S','KM','KS','KT','KFL','Z','KZ')      /*typ faktury krajowych*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '705'
                END      
        END

/*  faktura EKSPORTOWA np maszyny + indeks zaliczkowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='034'/*zaliczki*/ and @invoiceCategory IN  ('EM','ET','EU','KEM')      /*typ faktury ZAGRANICZNYCH*/
        BEGIN
          
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '840-firmNr'
                END  
            ELSE IF  @pageOrder = 2 
                BEGIN
                    SET @accountMask = '202-firmNr'
                END               
            ELSE IF  @pageOrder = 3 
                BEGIN
                    SET @accountMask = 'NULL'
                END                       
        END

/*  faktura EKSPORTOWA np maszyny + indeks USŁUGOWY  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup IN ('023','035','032','020','030','031')/*USLUGI*/ and @invoiceCategory IN   ('EM','ET','KWM')      /*typ faktury ZAGRANICZNYCH*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '706'
                END                  
        END 
        
/*  faktura WSPÓLNOTOWA np maszyny + indeks zaliczkowy   [034] - indeksy zaliczkowe  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup='034'/*zaliczki*/ and @invoiceCategory IN  ('WM','WT','WU')      /*typ faktury WSPÓLNOTOWA*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '840-firmNr'
                END    
            ELSE IF  @pageOrder = 2 
                BEGIN
                    SET @accountMask = '202-firmNr'
                END               
            ELSE IF  @pageOrder = 3 
                BEGIN
                    SET @accountMask = 'NULL'
                END                              
           
        END

/*  faktura WSPÓLNOTOWA np maszyny + indeks USŁUGOWY  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup IN ('023','035','032','020','030','031')/*USLUGI*/ and @invoiceCategory IN   ('WM','WT','KWT')      /*typ faktury WSPÓLNOTOWA*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '706'
                END      
        END         



/*  faktura WSPÓLNOTOWA/eksportowa np maszyny + indeks USŁUGOWY  DZIAŁA NA ZASADZIE PODMIANY MASKI */
    if @itemMainGroup NOT IN ('023','035','032','020','030','031')/*USLUGI*/ and @invoiceCategory IN   ('WU','EU','KWU')      /*typ faktury WSPÓLNOTOWA/eksportowa*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '706'
                END      
        END                        

    if @itemMainGroup NOT IN ('023','035','032','020','030','031')/*USLUGI*/ and @invoiceCategory IN   ('KEU')      /*typ faktury WSPÓLNOTOWA/eksportowa*/
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '706'
                END      
        END        

--/*  faktura WSPÓLNOTOWA/eksportowa np maszyny + indeks USŁUGOWY  DZIAŁA NA ZASADZIE PODMIANY MASKI */
--    if @taxRate= '-2'
--        BEGIN
            
--            IF @pageOrder = 1 
--                BEGIN
--                    SET @accountMask = '841-firmNr'
--                END    
--            ELSE IF  @pageOrder = 2 
--                BEGIN
--                    SET @accountMask = 'NULL'
--                END                  
--        END   

/*  ODWROTNE OBICIĄŻĘNIE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
    if @taxRate= '-2' --''--stawka "DTPN" odwrotne obciążęnie
        BEGIN
            
            IF @pageOrder = 1 
                BEGIN
                    SET @accountMask = '733-countrycode'
                END    
            ELSE IF  @pageOrder = 2 
                BEGIN
                    SET @accountMask = 'NULL'
                END                  
        END   


if @accountMask like '%firmNr%'
Begin
 
           select @firmNR=right((REPLICATE('0',5)+@firmNR),5)
 
	select 
		    @change  = @firmNR
	set    @accountMask = replace(@accountMask,'firmNr', isnull(@change,'firmNR[' + isnull(@firmNR,'')+ ']') )
End

if @accountMask like '%countrycode%'
Begin
	
	select top 1 
		@change  =  isnull(cd.accounting,'!'+ cd.dictionaryvalue)
    from  common.dictionary cd 
	where cd.dictionaryname ='countrycode'  and dictionarykey = @countrycode
	
    if @accountMask='840-countryCode' 
    BEGIN
            if len(@change)=1
                BEGIN
                    set @change='000'+@change
                end
            else if len(@change)=2
                BEGIN
                    set @change='00'+@change
                end
            else if len(@change)=3
                BEGIN
                    set @change='0'+@change
                end	                	
    end
    else
    begin
            if len(@change)=1
                BEGIN
                    set @change='00'+@change
                end
            else if len(@change)=2
                BEGIN
                    set @change='0'+@change
                end	
    end
	set   @accountMask = replace(@accountMask,'countrycode', isnull(@change,'countrycode[' + isnull(@countrycode,'')+ ']' ))
End

if @accountMask like '%itemcode%'   --exec	 accounting.warehouse 
Begin
	select top 1  
		@change  =  cd.accounting
    from 
    wms.item i inner join wms.itemClass ic on i.itemid = ic.itemid and ic.maingroup=1
	left join common.dictionary cd on ic.itemSubCategoryID = cd.dictionaryKeyGuid --zmiana MK 2013-01-20
    --left JOIN common.dictionary d ON d.dictionaryUrl=i.itemCodeTemplate and d.dictionaryName='itemgroup' and d.status>-1
    --left JOIN common.dictionary d2 on d2.dictionaryKey=d.dictionaryMasterKey and d2.dictionaryName='itemsubcategory' and d2.status>-1
	where  i.itemID = @itemcode
	
	set   @accountMask = replace(@accountMask,'itemcode', isnull(@change,'itemcode[' + N' Błędna grupa asortymentowa !'+ ']')  )	

End


if @accountMask like '%organisationUnitCode%'
Begin
	
	select 
		@change  = @organisationUnit
	set   @accountMask = replace(@accountMask,'organisationUnitCode', isnull(@change,'organisationUnit[' + isnull(@organisationUnit,'')+']')  )
End

if @accountMask like '%taxRate%'
Begin
	
select  top 1
		  @change  =  isnull(cd.accounting,'!'+ cd.dictionaryvalue)
    from  common.dictionary cd 
	where cd.dictionaryname ='system.taxrate'  and dictionarykey = @taxRate
    and cd.status>-1
	set   @accountMask = replace(@accountMask,'taxRate', isnull(@change,'taxRate[' + isnull(@taxRate,'')+']')  )
End

return @accountmask


end
GO


