USE [CRM0100_SMARTTECH]
GO

/****** Object:  StoredProcedure [accounting].[sales_exportToFinancialSystem]    Script Date: 11.03.2019 21:02:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [accounting].[sales_exportToFinancialSystem]
	-- Add the parameters for the stored procedure here
	@invoiceID uniqueidentifier, 
	@username varchar(50),
	@setStatus bit = 0,
	@test int =0 
AS
BEGIN
--update document.invoice set status = 1 
--[accounting].[sales_exportToFinancialSystem] @invoiceid= '0e68dae0-6fff-40cc-8cdd-e88f84dda660' ,@username='m.kotynia' ,@setStatus=0
	SET NOCOUNT ON;

    declare @currentFinancialSystemExportDate datetime
	declare @currentStatus datetime,
            @canBeExported int,
            @USERNR_TETA int,
            @invoiceFullNumber nvarchar(300),
            @currencyCode varchar(50),
            @currencyExchangeRate decimal(18,4),
            @currencyExchangeDate smalldatetime,
            @currencyTABLE varchar(400),
            @Exists_payment_SH int,
            @request_xml  xml,
            @request_xml_ans xml,
            @WN_page_value  decimal(18,2),
            @USER_REW       NVARCHAR(50)
     
      
                SELECT @USER_REW =
                                        CASE WHEN @username ='k.musialowska'   then 'KM'
                                             WHEN @username ='m.dzierzanowska' then 'M'
                                        ELSE 'B'
                                        END
/*czy mamy do czynienia z korektą*/
    declare @it_is_correct int=0
    select @it_is_correct=
            case when correctInvoiceID is not null then 1
            else 0 end
    from document.invoice
    where invoiceID=@invoiceID
    and correctInvoiceID is not null

	select @currentFinancialSystemExportDate = financialSystemExport, @currentStatus = [status],
           @invoiceFullNumber=invoiceFullNR
	from document.invoice where invoiceID = @invoiceID

    
    select @Exists_payment_SH=COUNT(*)
    from common.paymentSchedule
    where remoteID=@invoiceID
    and status>-1
            
    SELECT @currencyCode=currency,
           @currencyExchangeRate=currencyExchangeRate,
           @currencyExchangeDate=currencyExchangeDate 
    FROM   document.invoice WHERE 
    invoiceID= @invoiceID

    select @currencyTABLE = substring(tableNumber,1,3)
    from(
    SELECT TOP 1 tableNumber
    from common.currencyExchange 
    where currencyCode=@currencyCode 
    and avargeRate=@currencyExchangeRate
    AND publicationDate=@currencyExchangeDate
    ORDER BY publicationDate
    )x

    if isnumeric(@currencyTABLE)=1
        BEGIN
                set @currencyTABLE=cast(@currencyTABLE as int)
        end
        
        select   @canBeExported= 
                COUNT(*)
        from 
                    (
		SELECT 
					COALESCE(
							  /* konta wpisane ręcznie */
							  CASE WHEN dc.WN_MA = 'MA' and dc.pageOrder=1  then cA.ma_account_mask
								   WHEN dc.WN_MA = 'MA' and dc.pageOrder<>1 then cA.vat_account_mask
								   WHEN dc.WN_MA = 'WN'                     then cA.wn_account_mask
							  END,
							  /* konta generowane automatycznie */
								isnull(cast([dbo].[fn_accounting_sales](
									dc.accountmask,		--szablon konta ksiegowego
									x.itemid,			--itemcode (przekazujemy itemid)
									f.countrycode,		--countrycode
									null, 	            --@organisationUnit	 (tutaj mozna by przekazc od razu jaki kod ksiegowy ale chodzi o spojnosc),
									f.firmNR,           --id firmy (podmieniany numer dla spójności)
									x.itemTaxRate,      --podatek 
									i.invoiceCategory , -- kategoria faktury
									DC.PAGEORDER  ,       -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
                                    l.locationCode ,      -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
                                    x.invoiceLineID
									) as varchar(max)),'!-')
							) account
				FROM document.invoiceline x

				inner join document.invoice i on x.invoiceID=i.invoiceID             left join wms.location l on l.locationid = i.warehouseID
				LEFT JOIN common.dictionary dd ON dd.dictionaryKey = x.itemTaxRate and dd.dictionaryName ='system.taxRate' 
				left join  firm.firm f on I.payerID = f.firmid
				LEFT join document.documentType dt ON dt.documentType = i.invoiceCategory and dt.componentName = 'saleinvoice'
				left  join wms.itemClass ic on x.itemid = ic.itemid and ic.mainGroup = 1
				left  join common.dictionary itemgroup on ic.itemgroupid = itemgroup.dictionaryKey and itemgroup.dictionaryName = 'itemgroup' and itemgroup.[status] >= 0
				left  join common.dictionary itemsubcategory on itemgroup.dictionaryMasterKey = itemsubcategory.dictionaryKey and itemsubcategory.dictionaryName = 'itemsubcategory' and itemsubcategory.[status] >= 0
	            
                
                left join document.invoiceLine corected_il on corected_il.invoiceLineID = x.sourceInvoiceLineID  

		/*polaczenie z szablonem ksiegowania*/
				left join accounting.salesConfiguration dc    on  dc.documentType = I.invoiceCategory
			
		/*polaczenie z szablonem konta wpisanym ręcznie!!!*/
				left join accounting.customeAccounting cA ON cA.remoteID = x.invoiceLineID and cA.remoteSource = 'invoiceline'
            
				where x.status>-1
				and  i.status>=1
				and i.invoiceID = @invoiceID
				AND /*DODATKOWY MECHANIZM DLA USUNIĘĆIA Z DEKRETOW FAKTUR VAT VATU DLA INDEKSOW ZALICZKOWYCH*/
					[dbo].[fn_accounting_sales](dc.accountmask,x.itemid,f.countrycode,	null,f.firmNR,x.itemTaxRate,i.invoiceCategory , DC.PAGEORDER ,       -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
                                l.locationCode,x.invoiceLineID)<>'NULL'

        )x
         where x.account  like '%!%'         

        declare @mess nvarchar(3000)='#error.accounting.wrongAccountMask '+@invoiceFullNumber
            if @canBeExported>0 
                BEGIN
			        RAISERROR(@mess, 16, 1)
			        return;
                END


 --oznaczenie tylko ze jest do wyslania
 if (@setStatus =1 )
 Begin	
	update document.invoice
	set financialSystemExport = GETUTCDATE()
	where invoiceID = @invoiceID 
 End

 --Eksportujemy tylko jesli nei bylo juz wczesniej exportowane
 --Status 2 oznacza wyeksportowane do systemu FK
 if ((@setStatus =0 and @canBeExported=0) or @test=1)
 Begin   
        SELECT @WN_page_value = iw.sysGrossValue
        from document.invoice iW 
        LEFT join document.documentType dtA ON dtA.documentType = iW.invoiceCategory and dtA.componentName = 'saleinvoice'
        WHERE   iw.invoiceID = @invoiceID 

    select @request_xml =
(
  select 
         NEWID()               'requestId',
        'CommercialDocument'   'method',
         (
          select   ISNULL(@USER_REW ,'B')               'username',
                   i.invoiceID              'foreignId',
                   '2033'                   'type',
                   0                        'typeAbbreviation'  ,
                   122                      'documentSequentialNumber',
                   'WP'                     'documentSeries',
                   I.invoiceFullNR2         'supplierDocumentNumber',
                   Year(I.invoiceIssueDate) 'documentYear',
                   I.invoiceFullNR          'documentNumber',
                   c_i.invoiceFullNR        'correctedNumber',
                   c_i.invoiceIssueDate     'correctedDate',
                   I.invoiceIssueDate       'datee',
                   f.firmNR                 'contractorId'   ,
 ------------------------------------------------------------------------------------------------------------------- 
 -------------------------------------------------------------------------------------------------------------------
 ------------------------------------------------------------------------------------------------------------------- 
                   f.firmNR                 'contractor/codeContractor',
                   f.taxCode                'contractor/taxId',
                   f.firmName               'contractor/name',
                   f.firmNameShort          'contractor/shortName',
                   I.invoiceIssueDate       'contractor/dateCreate',
                   16                       'contractor/type',
                   upper(f.countryCode)     'contractor/address/country',
                   f.postCode               'contractor/address/postCode',
                   f.city                   'contractor/address/city',
                   f.countryState           'contractor/address/region',
                   f.flatNumber             'contractor/address/premise',
                   f.houseNumber            'contractor/address/building',
                   f.streetName             'contractor/address/street',
                   1  'contractor/accountingEntries/accountingEntrie/@skmId',
                   case when isnull(upper(f.countryCode),'PL') ='PL' then '200' else '211' end  'contractor/accountingEntries/accountingEntrie',
 ------------------------------------------------------------------------------------------------------------------- 
 -------------------------------------------------------------------------------------------------------------------
 -------------------------------------------------------------------------------------------------------------------               
                   i.sysGrossValue                                           'gross',
                   i.grossValue                                              'grossInCurrency',
                   isnull(i.currency,'PLN')                                  'currency',
                   isnull(i.currencyExchangeRate,1)                          'exchangeRate',
                   case when isnull(i.currencyExchangeRate,1)<>1 then 2 end  'exchangeRateType',
                   case when isnull(i.currencyExchangeDate ,1)<>1 then 2 end 'date',
                   i.invoiceIssueDate                                        'operationDate',
                   i.invoiceIssueDate                                        'periodDate',
                   i.invoiceIssueDate                                        'incomeDate',
                   i.invoiceIssueDate                                        'registrationDate',
                   i.invoiceMemo                                             'description',
                   N'SPRZEDAŻ'                                               'accountingRule',
                   'RKS'                                                     'vatRegister',
                    isnull(res.countryCode,'PL')                             'sendingCountry',
                    1                                                        'documentPayments/documentPayment/order',
                    i.invoiceID                                              'documentPayments/documentPayment/foreignId',
                    i.invoiceFullNR                                          'documentPayments/documentPayment/documentNumber',
                    i.sysGrossValue                                          'documentPayments/documentPayment/totalAmount',
                    i.grossValue                                             'documentPayments/documentPayment/totalAmountInCurrency',
                    0                                                        'documentPayments/documentPayment/amount',
                    0                                                        'documentPayments/documentPayment/amountInCurrencyInCurrency',
                    isnull(i.currency,'PLN')                                 'documentPayments/documentPayment/currency',
                   case when isnull(i.currencyExchangeRate,1)<>1 then 2 end  'documentPayments/documentPayment/exchangeRateType',
                    d_p.dictionaryValue                                      'documentPayments/documentPayment/paymentForm',
                   20                                                        'documentPayments/documentPayment/paymentFormNo',
                   null                                                      'documentPayments/documentPayment/contractorPayment' ,
                   isnull(i.paymentDate,i.invoiceIssueDate)                  'documentPayments/documentPayment/paymentDate',
                   1                                                         'documentPayments/documentPayment/settlements/settlement/order',
                   i.sysGrossValue                                           'documentPayments/documentPayment/settlements/settlement/amount',
                   i.paymentDate                                             'documentPayments/documentPayment/settlements/settlement/date',
                        (
                            select DENSE_RANK() over (PARTITION by x.itemTaxRate order by sum(x.sysvatValue)) 'order',
                                   x.itemTaxRate            'symbol',
                                   1                        'periodType',
                                   i.invoiceIssueDate       'periodDate',
                                   x.itemTaxRate            'vatRate',
                                   sum(x.sysnetValue)       'net',
                                   sum(x.sysvatValue)       'vat',
                                   sum(x.sysGrossValue)     'gross'                                  
                            from   [document].[invoiceVatTable] X
                            where  x.invoiceID = i.invoiceID                          
                            group by x.itemTaxRate 
                            for XML PATH('vatEntry'),TYPE ,ELEMENTS
                        ) 'vatEntries',
(
    SELECT 
           ROW_NUMBER()OVER (ORDER BY ACCOUNT)                                          'order',
           case when WN_MA = 'WN' THEN value ELSE 0.00 end                              'grossDebet',
		   case when WN_MA = 'WN' THEN value_Base ELSE 0.00 end                         'grossInCurrencyDebet',
           case when WN_MA = 'WN' and @WN_page_value=0.00  then '840-'+'[dostawca]'      
                when WN_MA = 'WN' THEN replace(account,right( REPLICATE('0',5)+firmNR,5) ,'[dostawca]') ELSE '' end  'syntheticAccountDebet',
           ''                                                                           'analyticAccountDebet',
           case when WN_MA = 'MA' THEN value ELSE 0.00 end                              'grossCredit',
           case when WN_MA = 'MA' THEN value_Base ELSE 0.00 END                         'grossInCurrencyCredit',
           case when WN_MA = 'MA' THEN account ELSE '' end                              'syntheticAccountCredit',       
           ''                                                                           'analyticAccountCredit',
           upper(currency)                                                              'currency',
           ISNULL(currencyExchangeRate,1)                                               'exchangeRate',
           case when isnull(i.currencyExchangeRate,1)<>1 then 2 end                     'exchangeRateType',
           invoiceFullNR                                                                'description',
           case when account like '222%' then itemTaxRate end                           'itemTaxRate',
           paymentDate              'paymentDate'
 	 FROM   
	(    


        SELECT 
/*POBRANIE Z TABELI VAT WARTOSCI SUM BRUTTO I VAT  @WN_page_value  - TO ZNACZNIK FAKTURY ROZLICZAJCEJ  pageOrder=2 TO STRONA MA DLA VAT */
                case  when   WN_MA='WN' and @WN_page_value<>0  then (
                                                                        select sum(v.sysGrossValue) 
                                                                        from  document.invoiceVatTable v 
                                                                        where v.invoiceID =@invoiceID
                                                                    ) 
                      when x.pageOrder=2 and WN_MA<>'WN'then (
                                                                select v.sysVatValue 
                                                                from document.invoiceVatTable v 
                                                                where v.invoiceID =@invoiceID 
                                                                and   v.itemTaxRate = x.itemTaxRate
                                                              )
                 else  SUM(x.value) end  value,
                 case when   WN_MA='WN' and @WN_page_value<>0 then (
                                                                    select sum(v.GrossValue) 
                                                                    from document.invoiceVatTable v 
                                                                    where v.invoiceID =@invoiceID
                                                                   ) 
                     when x.pageOrder=2 and WN_MA<>'WN' then (
                                                                select v.VatValue 
                                                                from document.invoiceVatTable v 
                                                                where v.invoiceID =@invoiceID 
                                                                and v.itemTaxRate = x.itemTaxRate
                                                              )
                 else  SUM(x.value_Base) end  value_Base,
                 currency,
                 currencyExchangeRate,
                 WN_MA,
                 account,
                 itemTaxRate,
                 firmNR,
                 invoiceFullNR,
                  paymentDate
         from 
        (

		SELECT      i.invoiceFullNR,
                    i.paymentDate,
 					i.currency,
					i.currencyExchangeRate,
                    case when dc.WN_MA ='wn'  then null else x.itemTaxRate end  itemTaxRate,
                    CASE WHEN itemsubcategory.dictionaryValue='ZALICZKI' 
                          and @WN_page_value=0.00 THEN itemsubcategory.dictionaryValue ELSE '' END dictionaryValue,
                     D_PAYMENT.accounting D_PAYMENT,
                    CASE when dc.valueType = 'brutto' and @it_is_correct=0  THEN x.sysgrossValue 
                         when dc.valueType = 'netto' and  @it_is_correct=0  THEN x.sysnetValue   
                         when dc.valueType = 'vat'    and @it_is_correct=0  THEN x.sysvatValue   
                         when dc.valueType = 'brutto' and @it_is_correct=1  THEN isnull(corected_il.c_sysGrossValue,0)   
                         when dc.valueType = 'netto'  and @it_is_correct=1  THEN isnull(corected_il.c_sysnetValue,0)   
                         when dc.valueType = 'vat'    and @it_is_correct=1  THEN isnull(corected_il.c_sysvatValue,0)                              
					end  value,
                    CASE when dc.valueType = 'brutto' and @it_is_correct=0  THEN x.grossValue 
                         when dc.valueType = 'netto' and  @it_is_correct=0  THEN x.netValue 
                         when dc.valueType = 'vat'    and @it_is_correct=0  THEN x.vatValue  
                         when dc.valueType = 'brutto' and @it_is_correct=1  THEN isnull(corected_il.c_GrossValue,0)   
                         when dc.valueType = 'netto'  and @it_is_correct=1  THEN isnull(corected_il.c_netValue,0)   
                         when dc.valueType = 'vat'    and @it_is_correct=1  THEN isnull(corected_il.c_vatValue,0)                              
					end  				 value_Base,
					dc.WN_MA,
                    f.firmNR,
 					COALESCE(
							  /* konta wpisane ręcznie */
							  CASE WHEN dc.WN_MA = 'MA' and dc.pageOrder=1  then cA.ma_account_mask
								   WHEN dc.WN_MA = 'MA' and dc.pageOrder<>1 then cA.vat_account_mask
								   WHEN dc.WN_MA = 'WN'                     then cA.wn_account_mask
							  END,
							  /* konta generowane automatycznie */
								isnull(cast([dbo].[fn_accounting_sales](
									dc.accountmask,		--szablon konta ksiegowego
									x.itemid,			--itemcode (przekazujemy itemid)
									f.countrycode,		--countrycode
									null, 	            --@organisationUnit	 (tutaj mozna by przekazc od razu jaki kod ksiegowy ale chodzi o spojnosc),
									f.firmNR,           --id firmy (podmieniany numer dla spójności)
									x.itemTaxRate,      --podatek 
									i.invoiceCategory , -- kategoria faktury
									DC.PAGEORDER    ,       -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
                                    l.locationCode ,    -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
									x.invoiceLineID) as varchar(max)),'-')
							) account,
                            pageOrder
				FROM document.invoiceline x
				inner join document.invoice i on x.invoiceID=i.invoiceID             left join wms.location l on l.locationid = i.warehouseID
				LEFT JOIN common.dictionary dd ON dd.dictionaryKey = x.itemTaxRate and dd.dictionaryName ='system.taxRate' 
				left join  firm.firm f on I.payerID = f.firmid
				LEFT join document.documentType dt ON dt.documentType = i.invoiceCategory and dt.componentName = 'saleinvoice'
				left  join wms.itemClass ic on x.itemid = ic.itemid and ic.mainGroup = 1
				left  join common.dictionary itemgroup on ic.itemgroupid = itemgroup.dictionaryKey and itemgroup.dictionaryName = 'itemgroup' and itemgroup.[status] >= 0
				left  join common.dictionary itemsubcategory on itemgroup.dictionaryMasterKey = itemsubcategory.dictionaryKey and itemsubcategory.dictionaryName = 'itemsubcategory' and itemsubcategory.[status] >= 0
	            LEFT JOIN common.dictionary D_PAYMENT ON D_PAYMENT.dictionaryKey = I.paymentTypeID AND D_PAYMENT.dictionaryName='paymenttype'
                
                --left join document.invoiceLine corected_il on corected_il.invoiceLineID = x.sourceInvoiceLineID  and x.isCorrectiveLine=1
                --left join document.invoiceLine corected_il_2 on x.sourceInvoiceLineID = corected_il_2.sourceInvoiceLineID    and corected_il_2.invoiceID<>x.invoiceID and corected_il_2.isCorrectiveLine=1                 
        /*polaczenie z funkcją obliczającą wartości korekt dla każdej pozycji kolejnej faktury korygującej */
                 LEFT JOIN  accounting.invoice_correct_value(@invoiceId,0) corected_il on corected_il.invoiceLineID = x.invoiceLineID 

		/*polaczenie z szablonem ksiegowania*/
				left join accounting.salesConfiguration dc    on  dc.documentType = I.invoiceCategory
			
		/*polaczenie z szablonem konta wpisanym ręcznie!!!*/
				left join accounting.customeAccounting cA ON cA.remoteID = x.invoiceLineID and cA.remoteSource = 'invoiceline'
            
				where x.status>-1
				and  i.status>=1
			
				and i.invoiceID = @invoiceID
				AND /*DODATKOWY MECHANIZM DLA USUNIĘĆIA Z DEKRETOW FAKTUR VAT VATU DLA INDEKSOW ZALICZKOWYCH*/
					[dbo].[fn_accounting_sales](dc.accountmask,x.itemid,f.countrycode,	null,f.firmNR,x.itemTaxRate,i.invoiceCategory , DC.PAGEORDER ,       -- NUMER STRONY DO OBSŁUGI WYJĄTKÓW TAKICH JAK ZALICZKI/USŁUGI
                                l.locationCode,x.invoiceLineID)<>'NULL'
                AND 
                    CASE WHEN dt.isForeignDocument = 1 AND   dc.valueType = 'vat' AND x.sysvatValue=0  THEN x.sysvatValue ELSE 1 END
                    <> 0
	    )x
		where x.value !=0
    GROUP by 
                 currency,
                 currencyExchangeRate,
                 WN_MA,
                 account,
                 D_PAYMENT,
                 itemTaxRate,
                 firmNR,
                 invoiceFullNR,
                 pageOrder,
                 paymentDate
     )y
        for XML PATH('entry'),TYPE ,ELEMENTS
) entries
           from document.invoice I
          left JOIN document.invoice c_i on c_i.invoiceID = i.correctInvoiceID
          left join firm.firm f on f.firmID = i.payerID
          left join firm.firm res on res.firmID = i.recipientID
          left join common.dictionary d_p on d_p.dictionaryKey = i.paymentTypeID and d_p.dictionaryName='paymentType'
          where i.invoiceID= @invoiceID
          for XML PATH(''),type,ELEMENTS
         )document
  for XML PATH('request'),TYPE , ELEMENTS
)    
  
if (@test <>1)
Begin
    EXEC [SERVICE].[dbo].[fractusImportDocumentCommercial] @request_xml,@request_xml_ans output
    select @request_xml_ans
    declare 
            @errorCode int,
            @errorMess varchar(max)
    select
			@errorCode =  (con.query('codeErr').value('.', 'int')),
			@errorMess = (con.query('message').value('.', 'varchar(4000)'))
	FROM  @request_xml_ans.nodes('/response/document/status') AS C ( con )


    if @errorCode <> 1
        BEGIN
			        RAISERROR(@errorMess, 16, 1)
			        return;
        END
    ELSE
        BEGIN
                update document.invoice
	            set financialSystemExport = GETUTCDATE()
		            , [status] = 2--status FK
	            where invoiceID = @invoiceID
	
	            update document.invoiceLine set [status] = 2 /*Status FK*/ where invoiceID = @invoiceID

				--Uzupełnienie loga o zmianę statusu
				exec log.wall_insert
					@username=@username, 
					@operation='update',
					@remoteid=@invoiceID,
					@remotesource='invoice',
					@navigateremoteid=@invoiceID,
					@navigateremotesource='invoice',
					@summary=@invoiceFullNumber,
					@newStatus='2',
					@previousStatus=@currentStatus,
					@statusDictionaryName='system.invoiceStatus';
        END
End
Else
Begin --TEST
	select  @request_xml
End
    
End



END
 
GO


