/******************************************************************************/
/***                               Procedures                               ***/
/******************************************************************************/

SET TERM ^ ;

CREATE PROCEDURE DATE_GEN(
    DFROM DATE NOT NULL,
    DTO DATE NOT NULL)
RETURNS (
    CDATE DATE)
AS
BEGIN
  SUSPEND;
END^


SET TERM ; ^



/******************************************************************************/
/***                            Tables and Views                            ***/
/******************************************************************************/

CREATE TABLE ALFA (
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
    ID_NEW_NAME INTEGER NOT NULL,
    DAT DATE NOT NULL,
    POSIT INTEGER NOT NULL,
    SUMMA INTEGER NOT NULL);


CREATE TABLE ROKEROVKA (
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
    OLD_VAL VARCHAR(20) CHARACTER SET WIN1251 NOT NULL,
    NEW_VAL VARCHAR(20) CHARACTER SET WIN1251 NOT NULL,
    VID VARCHAR(20) CHARACTER SET WIN1251 NOT NULL,
    CATEGORY VARCHAR(20) CHARACTER SET WIN1251 NOT NULL);




/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/


ALTER TABLE ALFA ADD CONSTRAINT PK_ALFA PRIMARY KEY (ID);
ALTER TABLE ROKEROVKA ADD CONSTRAINT PK_ROKEROVKA PRIMARY KEY (ID);

/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/



CREATE INDEX A_IDX1 ON ALFA (DAT);

/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/

SET TERM ^ ;

SET TERM ; ^

/******************************************************************************/
/***                               Procedures                               ***/
/******************************************************************************/

SET TERM ^ ;

ALTER PROCEDURE DATE_GEN(
    DFROM DATE NOT NULL,
    DTO DATE NOT NULL)
RETURNS (
    CDATE DATE)
AS
begin
  cdate = dfrom;
  while (cdate <= dto) do
  begin
    suspend;
    cdate = cdate + 1;
  end
end^


SET TERM ; ^

execute ibeblock
as
begin
  for i = 1 to 100 do
  begin
    v_OLD_VAL = ibec_RandomString(7, 10, 65, 90);
    v_NEW_VAL = ibec_RandomString(7, 10, 65, 90);
    v_VID = ibec_RandomString(4, 8, 65, 90);
    v_CATEGORY = ibec_RandomString(10, 20, 97, 122);

    INSERT INTO ROKEROVKA (OLD_VAL, NEW_VAL, VID, CATEGORY) VALUES (:v_OLD_VAL, :v_NEW_VAL, :v_VID, :v_CATEGORY);

    if (ibec_Mod(i, 1000) = 0) then
      Commit;
  end;
  Commit;

  lst_ID_NEW_NAME = null;
  iIndex = 0;
  for select ID
      from ROKEROVKA
      into :vValue
  do
  begin
    lst_ID_NEW_NAME[iIndex] = :vValue;
    iIndex = iIndex + 1;
    if (iIndex >= 100) then
      Break;
  end;

  for i = 1 to 100 do
  begin
    iIndex = ibec_Random2(0, ibec_High(lst_ID_NEW_NAME));
    v_ID_NEW_NAME = lst_ID_NEW_NAME[iIndex];
    v_DAT = ibec_Random2(43983, 44033);
    v_POSIT = ibec_Random2(1, 15);
    v_SUMMA = ibec_Random2(400, 8600);

    INSERT INTO ALFA (ID_NEW_NAME, DAT, POSIT, SUMMA) VALUES (:v_ID_NEW_NAME, :v_DAT, :v_POSIT, :v_SUMMA);

    if (ibec_Mod(i, 1000) = 0) then
      Commit;
  end;
  Commit;

end

select g.cdate, r.category, r.vid, sum( a.posit ) sum_posit, sum( a.summa ) sum_summa
  from rokerovka r right join date_gen(:d1, :d2) g on true
  left join alfa a on a.id_new_name = r.id and a.dat = g.cdate
  group by g.cdate, r.category, r.vid
  order by g.cdate