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;
    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
      try
        Find()
        Find('Tilgjengelig'+mySQLQuery1.FieldByName('idVald').AsString);
      except
        on EDatabaseError do
        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;
      end;
      mySQLQuery1.Next;
    end;
  end;

  ClientDataSet1.CreateDataSet;
  ClientDataSet1.Open;

  ShowMessage('Fortsett');

  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('idVald').AsInteger := 1;
      FieldByName('Tid').AsDateTime := CurrDato;

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

  mySQLQuery1.SQL.Text := 'SELECT F.idFiskekort, F.Starttid, F.Sluttid, K.Stangbruk '+
'FROM Fiskekort F, Korttype K '+
'WHERE F.idKorttype=K.idKorttype 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').AsInteger := ClientDataSet1.FieldByName('Brukt').AsInteger+1;
      ClientDataSet1.Post;
      ClientDataSet1.Next;
    end;

    mySQLQuery1.Next;
  end;

  Min := 1000;
  with ClientDataSet1 do
  begin
    First;
    while not EOF do
    begin
      Edit;
      FieldByName('Ledige').AsInteger := FieldByName('Tilgjengelig').AsInteger - FieldByName('Brukt').AsInteger;
      Post;
      if FieldByName('Ledige').AsInteger < Min then
        Min := FieldByName('Ledige').AsInteger;
      Next;
    end;
  end;
  Caption := 'Minst '+IntToStr(Min)+' ledige kort';
end;

end.