----------------------Список счетов c лимитом----------------------------------------------------------
DROP FUNCTION bill.bill_report_json_limit( date, date, uuid[], uuid[], uuid[], uuid[], integer[], uuid[], uuid, character varying, integer, integer);
DROP FUNCTION bill.bill_report_json_limit( date, date, uuid[], uuid[], uuid[], uuid[], integer[], uuid[], uuid[], uuid, character varying, integer, integer);
DROP FUNCTION bill.bill_report_json_limit( date, date, uuid[], uuid[], uuid[], uuid[], integer[], uuid[], uuid[], uuid, character varying, integer, integer, integer, integer);
DROP FUNCTION bill.bill_report_json_limit( date, date, uuid[], uuid[], uuid[], uuid[], integer[], uuid[], uuid[], uuid, character varying, integer, integer, numeric, integer, integer);
CREATE OR REPLACE FUNCTION bill.bill_report_json_limit (
  _from date,
  _to date,
  _waiter_ids uuid [],
  _hall_ids uuid [],
  _table_ids uuid [],
  _terminal_ids uuid [],
  _bill_types integer [],
  _client_ids uuid [],
  _pay_type_ids uuid [],
  _restaurant_id uuid,
  _sort_by character varying,
  _service integer,
  _discount integer,
  _discount_sum numeric,
  _limit integer,
  _offset integer
)
RETURNS TABLE (
  json json
) AS
$body$
declare
  item varchar[];
  where_clause varchar(300) = '';
  waiterFilter character varying = '';
  hallFilter character varying = '';
  tableFilter character varying = '';
  terminalFilter character varying = '';
  bill_typeFilter character varying = '';
  clientFilter character varying = '';
  pay_typeFilter character varying = '';
  pay_typeInFilter character varying = '';
  qFilter character varying = '';
  serviceFilter character varying = '';
  discountFilter character varying = '';
  discount_sumFilter character varying = '';

  waiter_id uuid;
  hall_id uuid;
  table_id uuid;
  terminal_id uuid;
  bill_type integer;
  client_id uuid;
  pay_type_id uuid;
begin
  if ( _service is not null ) then
    serviceFilter = 'and b.service = '||  _service ||'';
  end if;

  if ( _discount is not null ) then
    discountFilter = 'and b.discount = '||  _discount ||'';
  end if;

  if ( _discount_sum is not null ) then
    discountFilter = 'and b.discount_sum = '||  _discount_sum ||'';
  end if;

  if ( _waiter_ids is not null ) then
    waiterFilter = 'and b.waiter_id IN (';
    FOREACH waiter_id in array _waiter_ids loop
      waiterFilter := waiterFilter || '''' ||  waiter_id ||  ''',';
    end loop;
    Select trim(trailing ',' from waiterFilter) into waiterFilter;
    waiterFilter := waiterFilter || ') ';
  end if;

  if ( _hall_ids is not null ) then
    hallFilter = 'and h.id IN (';
    FOREACH hall_id in array _hall_ids loop
      hallFilter := hallFilter || '''' ||  hall_id ||  ''',';
    end loop;
    Select trim(trailing ',' from hallFilter) into hallFilter;
    hallFilter := hallFilter || ') ';
  end if;

  if ( _table_ids is not null ) then
    tableFilter = 'and t.id IN (';
    FOREACH table_id in array _table_ids loop
      tableFilter := tableFilter || '''' ||  table_id ||  ''',';
    end loop;
    Select trim(trailing ',' from tableFilter) into tableFilter;
    tableFilter := tableFilter || ') ';
  end if;

  if ( _terminal_ids is not null ) then
    terminalFilter = 'and b.terminal_id IN (';
    FOREACH terminal_id in array _terminal_ids loop
      terminalFilter := terminalFilter || '''' ||  terminal_id ||  ''',';
    end loop;
    Select trim(trailing ',' from terminalFilter) into terminalFilter;
    terminalFilter := terminalFilter || ') ';
  end if;

  if ( _bill_types is not null ) then
    bill_typeFilter = 'and b.bill_type IN (';
    FOREACH bill_type in array _bill_types loop
      bill_typeFilter := bill_typeFilter || '''' ||  bill_type ||  ''',';
    end loop;
    Select trim(trailing ',' from bill_typeFilter) into bill_typeFilter;
    bill_typeFilter := bill_typeFilter || ') ';
  end if;

  if ( _client_ids is not null ) then
    clientFilter = 'and c.id IN (';
    FOREACH client_id in array _client_ids loop
      clientFilter := clientFilter || '''' ||  client_id ||  ''',';
    end loop;
    Select trim(trailing ',' from clientFilter) into clientFilter;
    clientFilter := clientFilter || ') ';
  end if;

  if ( _pay_type_ids is not null ) then
    pay_typeFilter = 'and b.id IN (Select bill_id from bill.bill_pays Where pay_type_id IN (';
    FOREACH pay_type_id in array _pay_type_ids loop
      pay_typeFilter := pay_typeFilter || '''' ||  pay_type_id ||  ''',';
    end loop;
    Select trim(trailing ',' from pay_typeFilter) into pay_typeFilter;
    pay_typeFilter := pay_typeFilter || ')) ';
  end if;

  if ( _pay_type_ids is not null ) then
    pay_typeInFilter = '';
    FOREACH pay_type_id in array _pay_type_ids loop
      pay_typeInFilter := pay_typeInFilter || '''' ||  pay_type_id ||  ''',';
    end loop;
    Select trim(trailing ',' from pay_typeInFilter) into pay_typeInFilter;
    pay_typeInFilter := pay_typeInFilter || ' ';
  end if;

  qFilter := qFilter || ' and b."work_date" between ''' || _from || ''' and ''' || _to || ''' ';


  if ( _pay_type_ids is not null ) then
    return query execute
        'select array_to_json(array_agg(row_to_json(t)))
          from (
            SELECT
              b.id, b.number, b.waiter_id, u.first_name as waiter_first_name, h.id as hall_id,
              h.title as hall_title, b.table_id, t.number as table_number,
              b.courses_amount, b.entry, b.service,
              b.discount, sum(b.courses_amount / 100 * b.discount) AS sum_discount,
              b.discount_sum,
              b.work_date, b.open_date, b.close_date, b.restaurant_id,
              b.bill_type,
              c.id as client_id, c.first_name as client_first_name,
              cdm.amount as "acc",
              (
                Select sum(bp.sum)
                From bill.bill_pays bp
                Where
                  bp.bill_id = b.id
                  And bp.pay_type_id IN (' || pay_typeInFilter || ')
              ) AS amount
            FROM bill.bills b
            join local.users u on u.id = b.waiter_id
            left join restaurant.tables t on t.id = b.table_id
            left join restaurant.halls h on h.id = t.hall_id
            left join bill.bill_clients bc on bc.bill_id = b.id And bc.is_active = true
            left join clients c on c.id = bc.client_id
            left join restaurant.client_deposit_modify_bills cdmb on cdmb.bill_id = b.id
            left join restaurant.client_deposit_modifies cdm on cdm.id = cdmb.client_deposit_modify_id And cdm.modify_type = 2
            WHERE
              b.restaurant_id = ''' || _restaurant_id || '''
              ' || waiterFilter || '
              ' || hallFilter || '
              ' || tableFilter || '
              ' || terminalFilter || '
              ' || bill_typeFilter || '
              ' || clientFilter || '
              ' || pay_typeFilter || '
              ' || serviceFilter || '
              ' || discountFilter || '
              ' || discount_sumFilter || '
              ' || qFilter || '
              Group by b.id, u.first_name, h.id, t.number, c.id, cdm.amount
              order by ' || _sort_by || '
              limit ' || _limit || '
              offset ' || _offset || '
           ) t';
  else
    return query execute
        'select array_to_json(array_agg(row_to_json(t)))
          from (
            SELECT
              b.id, b.number, b.waiter_id, u.first_name as waiter_first_name, h.id as hall_id,
              h.title as hall_title, b.table_id, t.number as table_number,
              b.courses_amount, b.entry, b.service,
              b.discount, sum(b.courses_amount / 100 * b.discount) AS sum_discount,
              b.amount, b.discount_sum,
              b.work_date, b.open_date, b.close_date, b.restaurant_id, b.bill_type,
              c.id as client_id, c.first_name as client_first_name,
              cdm.amount as acc
            FROM bill.bills b
            join local.users u on u.id = b.waiter_id
            left join restaurant.tables t on t.id = b.table_id
            left join restaurant.halls h on h.id = t.hall_id
            left join bill.bill_clients bc on bc.bill_id = b.id And bc.is_active = true
            left join clients c on c.id = bc.client_id
            left join restaurant.client_deposit_modify_bills cdmb on cdmb.bill_id = b.id
            left join restaurant.client_deposit_modifies cdm on cdm.id = cdmb.client_deposit_modify_id And cdm.modify_type = 2
            WHERE
              b.restaurant_id = ''' || _restaurant_id || '''
              ' || waiterFilter || '
              ' || hallFilter || '
              ' || tableFilter || '
              ' || terminalFilter || '
              ' || bill_typeFilter || '
              ' || clientFilter || '
              ' || serviceFilter || '
              ' || discountFilter || '
              ' || discount_sumFilter || '
              ' || qFilter || '
              Group by b.id, u.first_name, h.id, t.number, c.id, cdm.amount
              order by ' || _sort_by || '
              limit ' || _limit || '
              offset ' || _offset || '
           ) t';
  end if;

end;
$body$
LANGUAGE 'plpgsql' STABLE;
ALTER FUNCTION bill.bill_report_json_limit( date, date, uuid[], uuid[], uuid[], uuid[], integer[], uuid[], uuid[], uuid, character varying, integer, integer, numeric, integer, integer)
  OWNER TO developer;