Форма генерации счета

Это форма-шаблон для генерации документа счета. 

Идея в том, что шаблон docx и выгрузка не изменяются. Вся кастомизация идет на уровне формы (подстановка нужных параметров и проверка доступа). 

Т.е. разработчик берет готовый механизм генерации счета, добавляет в GetItem формы свои данные (извлечение полей из нужных таблиц) и добавляем механизмы проверки доступа к операции в GetItem и SaveItem. И больше ничего делать не нужно. 

С точки зрения пользователя это выглядит так - кнопка Сформировать счет, в модальной форме (или отдельной странице) появляется форма с заполненными полями. Он может их подредактиировать либо просто нажать Сформировать счет. Далее скачивает по ссылке файл docx сгенерированного счета. 

Механизм форм-шаблонов для генерации типовых документов будет доступен через SQL пакет с кодом genDocForms.

Как устроена форма GenInvoice 

GetItem: 

CREATE PROCEDURE [dbo].[fm_geninvoice_getItem]
    @itemID int,	
	@username nvarchar(256)
AS
BEGIN	
	-- SELECT 1
	select 
      'Индивидуальный предприниматель' type,
      -- 'Иванов И.И.' name,
      'г.Москва, ул. Горбунова, д.7/13, кв.58' address,
      '235689752015' inn,
      '40802810000000668458' sAccount,
      'Иванов И.И.' companyName,
      '044525974' bik,
      'АО "ТИНЬКОФФ БАНК"' bank,
      '30101810145350000123' cAccount,
      '1' number,
      'ООО Телесервис' payer,
      '36780,00' totalAmount,
      -- '36780,00' totalPaid,
      '1' totalQuantity,
      --  'тридцать шесть тысяч семьсот восемьдесят, ноль ноль копеек' amountInWords,
      'Генеральный директор' signer, 
      'Иванов И.И.' signerPost,
      'Установка специализированного ПО' service,
      '1345,78'servicePrice,	
      '2'serviceQuantity,
      -- convert(nvarchar, isnull(getdate(),'1900-01-01'), 23)as date,
      try_convert(date, isnull(getdate(),'01.01.1980'), 104) as date,
      3 nds
	-- SELECT 2
	select '' Title, '' Subtitle, 'h2' HeaderTag, 1 LineLabel
	
END

SaveItem: 

CREATE PROCEDURE [dbo].[fm_geninvoice_saveItem]
   @username nvarchar(256), 
   @itemID int,
   @parameters ExtendedDictionaryParameter readonly
AS
BEGIN	  
    declare @type nvarchar(max)= (select Value2 from @parameters where [Key]='type')
    declare @address nvarchar(max)= (select Value2 from @parameters where [Key]='address')
    declare @inn nvarchar(max)= (select Value2 from @parameters where [Key]='inn')
    declare @sAccount nvarchar(max)= (select Value2 from @parameters where [Key]='sAccount')
    declare @companyName nvarchar(max)= (select Value2 from @parameters where [Key]='companyName')
    declare @bik nvarchar(max)= (select Value2 from @parameters where [Key]='bik')
    declare @bank nvarchar(max)= (select Value2 from @parameters where [Key]='bank')
    declare @cAccount nvarchar(max)= (select Value2 from @parameters where [Key]='cAccount')
    declare @number nvarchar(max)= (select Value2 from @parameters where [Key]='number')
    declare @payer nvarchar(max)= (select Value2 from @parameters where [Key]='payer')
    declare @totalAmount decimal(17,2)= (select replace(Value2,',','.') from @parameters where [Key]='totalAmount')
   
	declare @service nvarchar(max)= (select Value2 from @parameters where [Key]='service')
    declare @servicePrice decimal(17,2)= (select replace(Value2,',','.') from @parameters where [Key]='servicePrice')
    declare @serviceQuantity int= (select Value2 from @parameters where [Key]='serviceQuantity')
    declare @serviceSum decimal(17,2) = @servicePrice*@serviceQuantity
                            
     declare @nds int= (select Value2 from @parameters where [Key]='nds')
     declare @nds1 decimal(10,2)= (select 
                              case
                               when @nds =1 then 0.0
                               when @nds =2 then 0.1
                               when @nds =3 then 0.2
                              end as t)
    declare @totalPaid decimal(17,2)= @totalAmount+(@totalAmount*@nds1)
    declare @nds0 decimal(17,2)= @totalAmount*@nds1
   
    declare @totalQuantity nvarchar(max)= (select Value2 from @parameters where [Key]='totalQuantity')
    declare @amountInWords nvarchar(max)= (dbo.as_numPropis(CAST(CAST(replace(@totalPaid,',','.') as NUMERIC) as BIGINT),1)) 
    	+ ' руб. '+dbo.str_splitPart(@totalPaid,'.',2) + ' коп.'
    declare @date date= (select try_convert(date, isnull(value2,'01.01.1980'), 104) from @parameters where [Key]='date')
	
    declare @signer nvarchar(max) = (select value2 from @parameters where [key]='signer')
	declare @signerPost nvarchar(max) = (select value2 from @parameters where [key]='signerPost')
	
    
    -- SELECT 1 
    select 1 Result, ' ' Msg, '' SuccessUrl, 0 HideFormAfterSubmit, '' RefreshContainer

    -- SELECT 2 Вызов внешнего действия
    select 'generatedoc' type, 'genInvoice' code, cast(@itemID as nvarchar) itemID, '{
            "type" : "'+replace(isnull(@type, ''), '"', '')+'",
            "name" : "'+replace(isnull(@companyName, ''), '"', '')+'",
            "address" : "'+replace(isnull(@address, ''), '"', '')+'", 
            "inn" : "'+cast(isnull(@inn, '') as nvarchar)+'",
            "sAccount" : "'+cast(isnull(@sAccount, '') as nvarchar)+'",
            "companyName" : "'+replace(isnull(@companyName, ''), '"', '')+'",
            "bik" : "'+cast(isnull(@bik, '') as nvarchar)+'",
            "bank" : "'+replace(replace(isnull(@bank, ''), '"', ''),'"','' )+'",
            "cAccount" : "'+cast(isnull(@cAccount, '') as nvarchar)+'",
            "number" : "'+replace(isnull(@number, ''), '"', '')+'",
             "payer" : "'+replace(replace(isnull(@payer, ''), '"', ''),'"','' )+'",        
            "totalAmount" : "'+cast(isnull(@totalAmount, '') as nvarchar)+'",
            "totalPaid" : "'+cast(isnull(@totalPaid, '') as nvarchar)+'",
            "totalQuantity" : "'+replace(isnull(@totalQuantity, ''), '"', '')+'",
            "amountInWords" : "'+replace(isnull(@amountInWords, ''), '"', '')+'",
            "date" : "'+isnull(convert(nvarchar, isnull(@date,'01.01.1900'), 104), '')+'",
            "nds" : "'+isnull(iif(try_cast(@nds0 as nvarchar)='0.00','Без НДС',try_cast(@nds0 as nvarchar) ), '')+'",   
            "signer" : "'+isnull(@signer, '')+'",
            "signerPost" : "'+isnull(@signerPost, '')+'",
            "service" : "'+isnull(@service, '')+'",
            "servicePrice" : "'+isnull(try_cast(@servicePrice as nvarchar), '')+'",
            "serviceQuantity" : "'+isnull(try_cast(@serviceQuantity as nvarchar), '')+'",
            "serviceSum" : "'+isnull(try_cast(@serviceSum as nvarchar), '')+'"
        }' parameters
END

Выгрузка документа в /doctemplates

Шаблон документа docx

SQL процедура GetInvoice:

CREATE PROCEDURE [dbo].[doc_genInvoice_getData]
	@username nvarchar(64),
	@itemID int,
	@urlParameters CRUDFilterParameter READONLY	,
    @parameters ExtendedDictionaryParameter READONLY	
AS
begin
    declare @type nvarchar(max)= (select Value2 from @parameters where [Key]='type')
    declare @name nvarchar(max)= (select Value2 from @parameters where [Key]='name')
    declare @address nvarchar(max)= (select Value2 from @parameters where [Key]='address')
    declare @inn nvarchar(max)= (select Value2 from @parameters where [Key]='inn')
    declare @sAccount nvarchar(max)= (select Value2 from @parameters where [Key]='sAccount')
    declare @companyName nvarchar(max)= (select Value2 from @parameters where [Key]='companyName')
    declare @bik nvarchar(max)= (select Value2 from @parameters where [Key]='bik')
    declare @bank nvarchar(max)= (select Value2 from @parameters where [Key]='bank')
    declare @cAccount nvarchar(max)= (select Value2 from @parameters where [Key]='cAccount')
    declare @number nvarchar(max)= (select Value2 from @parameters where [Key]='number')
    declare @payer nvarchar(max)= (select Value2 from @parameters where [Key]='payer')
    declare @totalAmount nvarchar(max)= (select Value2 from @parameters where [Key]='totalAmount')
    declare @totalPaid nvarchar(max)= (select Value2 from @parameters where [Key]='totalPaid')
    declare @totalQuantity nvarchar(max)= (select Value2 from @parameters where [Key]='totalQuantity')
    declare @amountInWords nvarchar(max)= (select Value2 from @parameters where [Key]='amountInWords')
    declare @date nvarchar(max)= (select Value2 from @parameters where [Key]='date')
    declare @nds nvarchar(max)= (select Value2 from @parameters where [Key]='nds')
   
    declare @signer nvarchar(max)= (select Value2 from @parameters where [Key]='signer')
    declare @signerPost nvarchar(max)= (select Value2 from @parameters where [Key]='signerPost')
    
    declare @service nvarchar(max)= (select Value2 from @parameters where [Key]='service')
    declare @servicePrice nvarchar(max)= (select Value2 from @parameters where [Key]='servicePrice')
    declare @serviceQuantity nvarchar(max)= (select Value2 from @parameters where [Key]='serviceQuantity')
    declare @serviceSum nvarchar(max)= (select Value2 from @parameters where [Key]='serviceSum')

    --SELECT 1 Мета данные 
	select 1 Result, '' msg, '9400' TableWidth, 
    	'800,4000,1000,1200,1200,1200' ColWidths, 'f5f5f5' TableHeaderBackColor,
    	[dbo].[str_processURL] ('invoice '+ format(getdate(),'ddMMMyy') + ' {g}') FileName   
   
    -- SELECT 2
	select       	
         isnull(@type, '') p1,
         isnull(@name, '') p2,
         isnull(@address, '') p3,
         isnull(@inn, '') p4,
         isnull(@sAccount, '') p5,
         isnull(replace(@companyName,'"',''), '') p6,
         isnull(@bik, '') p7,
         isnull(replace(@bank,'"',''), '') p8,
         isnull(@cAccount, '') p9,
         isnull(@number, '') p10,
         isnull(@payer, '') p11,
         isnull(@totalAmount, '') p12,
         isnull(@totalPaid, '') p13,
         isnull(@totalQuantity, '') p14,
         isnull(@amountInWords, '') p15,
         isnull(@signer, '') p16,
         isnull(@signerPost, '') p17,
         isnull(@date, '') p18,
         isnull(@nds, '') p19
        
   -- SELECT 3 СМЕТА
	select '1' '№', 
    	isnull(@service, '') 'Наименование услуги', 
        'услуга' 'Ед. изм.',
        @serviceQuantity 'Кол-во',
		cast(@servicePrice as nvarchar) 'Цена, руб.', 
        cast((@serviceSum) as nvarchar) 'Сумма, руб.'   	

end