unit kortberegning;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, mySQLDbTables, DB, DBClient;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    ClientDataSet1: TClientDataSet;
    mySQLDatabase1: TmySQLDatabase;
    mySQLQuery1: TmySQLQuery;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Button1: TButton;
    DataSource2: TDataSource;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses DateUtils;

var
  FormatSettings: TFormatSettings;

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);

var
  Timer, I, J, Min: Integer;
  Startdato, CurrDato: TDateTime;
  Funn: Boolean;

begin
  with FormatSettings do
  begin
    DateSeparator := '-';
    TimeSeparator := ':';
    ShortDateFormat := 'yyyy-mm-dd';
    LongDateFormat := 'yyyy-mm-dd';
    ShortTimeFormat := 'hh:nn:ss';
    LongTimeFormat := 'hh:nn:ss';
  end;

  mySQLQuery1.SQL.Text := 'SELECT S.idStangunntak, S.Stangantall, V.idVald, V.Vald, S.Startdato, S.Sluttdato, S.Sluttdato-S.Startdato AS Intervall '+
'FROM Vald V, Stangunntak S, Salgsstedgodkjenning G, Utsalgssted U '+
'WHERE V.idVald=S.idVald AND (("2007-08-12" BETWEEN S.Startdato AND S.Sluttdato) OR ("2007-08-15" BETWEEN S.Startdato AND S.Sluttdato)) AND V.idVald=G.idVald AND G.idUtsalgssted=U.idUtsalgssted AND G.Aktiv=True AND U.idUtsalgssted=1 '+
'ORDER BY idVald, Sluttdato-Startdato';
  mySQLQuery1.Open;
  
  with ClientDataSet1.FieldDefs do
  begin
    with AddFieldDef do
    begin
      Name := 'Tid';
      DataType := ftDateTime;
    end;
    mySQLQuery1.First;
    while not mySQLQuery1.EOF do
    begin
      if IndexOf('Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString) = -1 then
      begin
        with AddFieldDef do
        begin
          Name := 'Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString;
          DataType := ftInteger;
        end;
        with AddFieldDef do
        begin
          Name := 'Brukt'+mySQLQuery1.FieldByName('idVald').AsString;
          DataType := ftInteger;
        end;
        with AddFieldDef do
        begin
          Name := 'Ledig'+mySQLQuery1.FieldByName('idVald').AsString;
          DataType := ftInteger;
        end;
      end;
      mySQLQuery1.Next;
    end;
  end;

  ClientDataSet1.CreateDataSet;
  ClientDataSet1.Open;

  Startdato := StrToDateTime('2007-08-12 00:00:00', FormatSettings);
  CurrDato := Startdato;
  Timer := HoursBetween(StrToDateTime('2007-08-15 00:00:00', FormatSettings), StrToDateTime('2007-08-12 00:00:00', FormatSettings));

  for I := 0 to Timer-1 do
  begin
    with ClientDataSet1 do
    begin
      Append;
      FieldByName('Tid').AsDateTime := CurrDato;

      // Finn det laveste intervallet for dette tidspunktet
      mySQLQuery1.First;
      J := mySQLQuery1.FieldByName('idVald').AsInteger;
      while not mySQLQuery1.EOF do
      begin
        Funn := False;
        while (mySQLQuery1.FieldByName('idVald').AsInteger = J) and (not Funn) and (not mySQLQuery1.EOF) do
        begin
          if (CurrDato >= mySQLQuery1.FieldByName('Startdato').AsFloat) and (CurrDato <= mySQLQuery1.FieldByName('Sluttdato').AsFloat) then
          begin
            FieldByName('Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger := mySQLQuery1.FieldByName('Stangantall').AsInteger;
            Funn := True;
          end
          else
            FieldByName('Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger := 0;
          mySQLQuery1.Next;
        end;
        while (J = mySQLQuery1.FieldByName('idVald').AsInteger) and (not mySQLQuery1.EOF) do
          mySQLQuery1.Next;
        J := mySQLQuery1.FieldByName('idVald').AsInteger;
      end;
      Post;
    end;
    CurrDato := IncHour(CurrDato);
  end;

  mySQLQuery1.SQL.Text := 'SELECT V.idVald, F.idFiskekort, F.Starttid, F.Sluttid, K.Stangbruk '+
'FROM Fiskekort F, Korttype K, Vald V '+
'WHERE F.idKorttype=K.idKorttype AND K.idVald=V.idVald AND K.Stangbruk>0 AND ((F.Starttid >= "2007-08-12 00:00:00" AND F.Starttid < "2007-08-15 00:00:00") OR (F.Sluttid > "2007-08-12 00:00:00" AND F.Sluttid <= "2007-08-15 00:00:00"))';
  mySQLQuery1.Open;

  Startdato := StrToDateTime('2007-08-12 00:00:00', FormatSettings);
  mySQLQuery1.First;
  while not mySQLQuery1.EOF do
  begin
    ClientDataSet1.First;
    if mySQLQuery1.FieldByName('Starttid').AsDateTime > Startdato then
      ClientDataSet1.RecNo := HoursBetween(mySQLQuery1.FieldByName('Starttid').AsDateTime, Startdato)+1;
    I := HoursBetween(mySQLQuery1.FieldByName('Sluttid').AsDateTime, mySQLQuery1.FieldByName('Starttid').AsDateTime);
    for J := 0 to I-1 do
    begin
      ClientDataSet1.Edit;
      ClientDataSet1.FieldByName('Brukt'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger := ClientDataSet1.FieldByName('Brukt'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger+1;
      ClientDataSet1.Post;
      ClientDataSet1.Next;
    end;

    mySQLQuery1.Next;
  end;

  mySQLQuery1.SQL.Text := 'SELECT V.idVald, V.Vald FROM Vald V, Salgsstedgodkjenning S, Utsalgssted U WHERE V.idVald=S.idVald AND S.idUtsalgssted=U.idUtsalgssted AND S.Aktiv=True AND U.idUtsalgssted = 1 ORDER BY Vald';
  mySQLQuery1.Open;
  mySQLQuery1.First;
  while not mySQLQuery1.EOF do
  begin
    Min := 1000;
    with ClientDataSet1 do
    begin
      First;
      while not EOF do
      begin
        Edit;
        FieldByName('Ledig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger := FieldByName('Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger - FieldByName('Brukt'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger;
        Post;
        if FieldByName('Ledig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger < Min then
          Min := FieldByName('Ledig'+mySQLQuery1.FieldByName('idVald').AsString).AsInteger;
        Next;
      end;
    end;
    ListBox1.Items.Add(mySQLQuery1.FieldByName('Vald').AsString+' ('+IntToStr(Min)+')');
    mySQLQuery1.Next;
  end;
end;

end.