Správa reportů

query byId($id:String) {
  findUser(by: { id: $id }) {
    firstName
    lastName
    id
    subordinates {
      firstName
      lastName
      id
    }
  }
}

function main(summaryVariables) {
  // ziskame id aktualniho uzivatele
  const currentUserId = CurrentUser.User.GetValue('Id');

  // zavolame GQL dotaz pro ziskani seznamu podrizenych
  const queryVariables = { ...summaryVariables, id: currentUserId };
  const queryResult = GraphQL.Execute('byId', queryVariables);

  // JSON odpovedi
  const toJsonOptions = JSONConvert.CreateToJsonOptions();
  toJsonOptions.DetectRepeAction = false;
  toJsonOptions.WithJsonTypes = false;
  toJsonOptions.EmitRootObject = false;
  toJsonOptions.EmitArrayObject = false;
  const queryData = queryResult.EvaluatePathAsJson(null, toJsonOptions);

  // vytahneme podrizene
  const { data: { findUser: { subordinates } } } = queryData;
  // a sestavime podminku
  const subordinatesWhere = {
    inString: {
      expr: {
        // POZOR! zde musi byt nazev sloupce z hlavniho dotazu
        col: 'USER_ID'
      },
      values: [currentUserId, ...subordinates.map(su => su.id)]
    }
  };

  // sestavime novy where spojenim pripadneho puvodniho + podrizeni
  let where;
  let { where: originalWhere } = summaryVariables;
  if (originalWhere !== null && typeof originalWhere === 'object') {
    where = {
      and: [ originalWhere, subordinatesWhere ]
    };
  } else {
    where = subordinatesWhere
  }

  // nakonec rozsirime puvodni promenne
  const outVariables = {
    ...summaryVariables,
    where
  };

  Log.DbgLog(JSON.stringify(outVariables));
  return outVariables;
}

--DROP TABLE suppliers;

create table suppliers (
[id] [int] IDENTITY(1,1) NOT NULL,
[company_name] [nvarchar](max) NOT NULL,
[company_address] [nvarchar](max) NOT NULL,
[company_identification] [nvarchar](max) NOT NULL
PRIMARY KEY CLUSTERED (
        [id] ASC
) ON [PRIMARY])

insert into suppliers values ('Firma 1', 'Hornokrcska', '123456');
insert into suppliers values ('Firma 2', 'Dolnokrcska', '1234567');
insert into suppliers values ('Firma 3', 'Pravokrcska', '12345678');
insert into suppliers values ('Firma 4', 'Levokrcska', '123456789');

--DROP TABLE [invoices]
--GO

CREATE TABLE [invoices](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [text] [nvarchar](max) NOT NULL,
    [note] [nvarchar](max) NULL,
    [amount] [int] NOT NULL,
    [amount_decimal] [decimal](18, 2) NOT NULL,
    [date_received] [datetime] NOT NULL,
    [paid] [bit] NOT NULL,
    [supplier_id] [int] NULL,
 	 [user_id] [nvarchar](max) NOT NULL,
	 [user_name] [nvarchar](max) NOT NULL
     CONSTRAINT [pk_invoices] PRIMARY KEY CLUSTERED
    (
        [id] ASC
    ) ON [PRIMARY])

ALTER TABLE [invoices]  WITH CHECK ADD  CONSTRAINT [fk_invoice_supplier] FOREIGN KEY([supplier_id])
REFERENCES [suppliers] ([id])
ALTER TABLE [invoices] CHECK CONSTRAINT [fk_invoice_supplier]

--DROP TABLE [invoice_attachments]
--GO

CREATE TABLE [invoice_attachments](
	    [id] [int] IDENTITY(1,1) NOT NULL,
	    [attachment] [nvarchar](max) NOT NULL,
	    [invoice_id] [int] NOT NULL
     CONSTRAINT [pk_invoice_attachments] PRIMARY KEY CLUSTERED
    (
	    [id] ASC
    ) ON [PRIMARY])

ALTER TABLE [invoice_attachments]  WITH CHECK ADD CONSTRAINT [fk_attachment_invoice] FOREIGN KEY([invoice_id])
REFERENCES [invoices] ([id])
ALTER TABLE [invoice_attachments] CHECK CONSTRAINT [fk_attachment_invoice]

DECLARE @i int = 0
DECLARE @zaznamy int
set @zaznamy = (select COUNT(*) from [invoices])

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    DECLARE @supp_id int
    SELECT @supp_id = Cast(RAND()*(4-1)+1 as int)

    DECLARE @amount int
    SELECT @amount = Cast(RAND()*(10001-1)+1 as int)

    DECLARE @amount_decimal decimal(18, 2)
    SELECT @amount_decimal = Cast((@amount + RAND())  as decimal(18, 2))

    DECLARE @paid bit
    SELECT @paid = Cast(ROUND(RAND(), 0) as bit)

    DECLARE @day int = Cast(RAND()*(29-1)+1 as int)
    DECLARE @month int = Cast(RAND()*(13-1)+1 as int)
    -- 2015 - 2020
    DECLARE @year int = Cast(RAND()*(2021-2015)+2015 as int)

    DECLARE @date_received datetime
    SELECT @date_received = CONCAT(@year, '-', @month, '-', @day, ' 00:00:00.000')

    -- zmenit ID uzivatelu a jmena uzivatelu, pro kazdou firmu unikatni! Je nutne zachovat spravne poradi ID a jmen a upravit hodnotu n pro pocet uzivatelu. Pro 5 uzivatelu by tedy bylo DECLARE @n int = Cast(RAND()*(6-1)+1 as int)
    DECLARE @n int = Cast(RAND()*(10-1)+1 as int)
    DECLARE @user_id nvarchar(max) = choose(@n, '98688029-fc7c-4e08-9ac2-ffe2e7c1b79b','1ee5d82a-5cc5-4a18-a058-2fb2f1121917','61cda2dd-11bf-4a3b-a0fa-e81a3b85cf37','42387e6f-8e96-4fe0-aa55-84ceda92ae3c','75d65294-4e0f-47ab-a252-cfce3d47f686','11092dbf-f678-4287-ac3d-a7290ac99b9c','932d019c-6546-4b58-849c-bd7ac9ef6f84','2411b897-2f59-448c-89a0-b9579265c44e','d3eb53f6-65ed-40a7-a5cc-bdcd7ac881bc')
    DECLARE @user_name nvarchar(max) = choose(@n, 'Eva Účetní','Marie Účetní','Petr Administrátor','Tomáš Ředitel','Jan Žadatel','Jana Účetní','Josef Jednorožec','Lukáš Jednorožec','Miroslav Jednorožec')

    INSERT INTO [invoices]
       ([text]
       ,[note]
       ,[supplier_id]
       ,[amount]
       ,[amount_decimal]
       ,[paid]
       ,[date_received]
       ,[user_id]
	   ,[user_name])
     VALUES
       (CONCAT('Faktura', @i)
       ,CONCAT('Moje faktura #', @i)
       ,@supp_id
       ,@amount
       ,@amount_decimal
       ,@paid
       ,@date_received
       ,@user_id
	   ,@user_name)
END
GO

DECLARE @i int = 0
DECLARE @zaznamy int
set @zaznamy = (select COUNT(*) from [invoices])

WHILE @i < @zaznamy
BEGIN
    SET @i = @i + 1
-- zde musi byt detachidenty z dane firmy pro vlozene prilohy, lze zjistit z dat formulare kde je prilohove pole, hledejte detachident
	DECLARE @n int = Cast(RAND()*(5-1)+1 as int)
	DECLARE @priloha nvarchar(max) = choose(@n, 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzaWQiOiJkZWZhdWx0IiwiZmlkIjoiZTZmMmYwYmYtMzlkOS00YmYwLWE1MmQtMDAwMWZkZjY0MzQ2IiwiaXNzIjoiaHR0cHM6Ly9kZXZzb2ZhLjYwMi5jei8ifQ.aGOag0DQwnDKbnJP5JymXJbDUub0TIcj7OqWd_OpekQ','eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzaWQiOiJkZWZhdWx0IiwiZmlkIjoiMDkzMGE5NGItNjc5NS00ZTdmLWI2NzQtNWM0OTIzZjk0OWU5IiwiaXNzIjoiaHR0cHM6Ly9kZXZzb2ZhLjYwMi5jei8ifQ.-vNJVTmDRoE-e2_i9n8FIHPNh2CTJKfQAVvWxYyNq-k','eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzaWQiOiJkZWZhdWx0IiwiZmlkIjoiNjFlZWY2ZjMtYmZjMS00NDg0LTgwMjgtMGFkYzNiNDg3MzBkIiwiaXNzIjoiaHR0cHM6Ly9kZXZzb2ZhLjYwMi5jei8ifQ.8lJPOrIl21LVhYZQgqgfaa-mC8rjJk_m_Ys2Q6lq0wc','eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzaWQiOiJkZWZhdWx0IiwiZmlkIjoiNmNhMjQxZjgtZDY2Mi00ZjNmLThiZmYtMWI0ZDU0YmI5N2ZjIiwiaXNzIjoiaHR0cHM6Ly9kZXZzb2ZhLjYwMi5jei8ifQ.xKrFVnX7phEgxSvDTN6BIVVv-vdy3ldkgdMl-Xh7qPI')

	DECLARE @inv_id int
    SELECT @inv_id = Cast(RAND()*(@zaznamy-1)+1 as int)

	INSERT INTO [invoice_attachments]
           ([attachment]
           ,[invoice_id])
     VALUES
           (@priloha,
		   @inv_id)

END
GO

DECLARE @i int = 0
DECLARE @zaznamy int
set @zaznamy = (select COUNT(*) from [invoices])

WHILE @i < @zaznamy
BEGIN
    SET @i = @i + 1

    DECLARE @amount int
    SELECT @amount = Cast(RAND()*(10001-1)+1 as int)

    DECLARE @amount_decimal decimal(18, 2)
    SELECT @amount_decimal = Cast((@amount + RAND())  as decimal(18, 2))

	INSERT INTO [invoice_items]
           ([item_name]
           ,[item_value]
           ,[invoice_id])
    VALUES
           (NEWID(),
           @amount_decimal,
           @i)
END

SET @i = 0
WHILE @i < @zaznamy
BEGIN
    SET @i = @i + 1

	DECLARE @inv_id int
    SELECT @inv_id = Cast(RAND()*(@zaznamy-1)+1 as int)

    SELECT @amount = Cast(RAND()*(10001-1)+1 as int)

    SELECT @amount_decimal = Cast((@amount + RAND())  as decimal(18, 2))

	INSERT INTO [invoice_items]
           ([item_name]
           ,[item_value]
           ,[invoice_id])
    VALUES
           (NEWID(),
           @amount_decimal,
           @inv_id)
END

SET @i = 0
WHILE @i < 200
BEGIN
    SET @i = @i + 1

    SELECT @amount = Cast(RAND()*(10001-1)+1 as int)

    SELECT @amount_decimal = Cast((@amount + RAND())  as decimal(18, 2))

	INSERT INTO [invoice_items]
           ([item_name]
           ,[item_value]
           ,[invoice_id])
    VALUES
           (NEWID(),
           @amount_decimal,
           '1')
END
GO