unit sem1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, DBTables, ComCtrls, ExtCtrls, StdCtrls, Buttons,
  Mask, DBCtrls;

type
  TFormUcty = class(TForm)
    Database: TDatabase;
    DataSource: TDataSource;
    PageControl: TPageControl;
    TabSheet1: TTabSheet;
    DBGridUcet: TDBGrid;
    btnUpdate: TBitBtn;
    QueryKlient: TQuery;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    Panel2: TPanel;
    BitBtn3: TBitBtn;
    Button1: TButton;
    QueryName: TQuery;
    DataSourceName: TDataSource;
    DBGrid1: TDBGrid;
    Button2: TButton;
    TableZmeny: TTable;
    DataSourceNewUcet: TDataSource;
    NUQuery: TQuery;
    GroupBox5: TGroupBox;
    Label20: TLabel;
    Button6: TButton;
    GroupBoxKarta: TGroupBox;
    Label21: TLabel;
    ButtonCKarty: TButton;
    Karta_cislo: TEdit;
    CheckKarta: TCheckBox;
    GroupBox7: TGroupBox;
    klient_rc: TLabel;
    klient_adresa: TLabel;
    klient_prijmeni: TLabel;
    klient_jmeno: TLabel;
    klient_id: TLabel;
    Label27: TLabel;
    Label28: TLabel;
    Label29: TLabel;
    Label30: TLabel;
    Label31: TLabel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Ucet_cislo: TEdit;
    Label1: TLabel;
    karta_platnost: TEdit;
    GroupBox1: TGroupBox;
    DBGrid2: TDBGrid;
    QueryTransakce: TQuery;
    DataSourceTransakce: TDataSource;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Labelkj: TLabel;
    Labelkp: TLabel;
    Labelzust: TLabel;
    QueryTransakce2: TQuery;
    TypTransakce: TRadioGroup;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    LabelInfo: TLabel;
    LabelTyp: TLabel;
    Editcislo: TEdit;
    ButtonFind: TButton;
    GroupBoxUctem: TGroupBox;
    GroupBoxKartou: TGroupBox;
    ComboBoxAkce: TComboBox;
    Label5: TLabel;
    EditHodnota: TEdit;
    ComboBoxAdresa: TComboBox;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    ComboBoxKTyp: TComboBox;
    ComboBoxKAdresa: TComboBox;
    EditKHodnota: TEdit;
    Label6: TLabel;
    BitBtn6: TBitBtn;
    BitBtn7: TBitBtn;
    dotaz: TEdit;
    RadioGroup1: TRadioGroup;
    RadioButtonKlient: TRadioButton;
    Panel1: TPanel;
    Label7: TLabel;
    Label8: TLabel;
    EditSrchJmeno: TEdit;
    EditSrchPrijmeni: TEdit;
    RadioButtonBank: TRadioButton;
    RadioButtonPrep: TRadioButton;
    procedure btnUpdateClick(Sender: TObject);
    procedure NovyUcetClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Gen_cuctuClick(Sender: TObject);

    procedure ButtonUcetClick(Sender: TObject);
    procedure CheckKartaClick(Sender: TObject);
    procedure RadioButton1Click(Sender: TObject);
    procedure RadioButton2Click(Sender: TObject);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure ButtonFindClick(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure ButtonCKartyClick(Sender: TObject);
  private
    { Private declarations }
    procedure updateTerminal(ucet_cislo:string; karta_cislo:string);
    procedure updateucet(id, hodnota:integer);
    function datumtransakce:string;
  public
    { Public declarations }
  end;

var
  FormUcty: TFormUcty;

implementation

uses newklient;

{$R *.DFM}

procedure TFormUcty.btnUpdateClick(Sender: TObject);
var mysql, jmeno, prijmeni:string;
begin
  mysql:=''; jmeno:=''; prijmeni:='';
  if RadioButtonKlient.checked then begin
    if ((editsrchjmeno.text='') or (editsrchjmeno.text='')) then
      messagedlg('Nebylo zadano jmeno klienta',mtError, [mbAbort],0)
    else begin
      jmeno:=editsrchjmeno.text; prijmeni:=editsrchprijmeni.text;
      mysql:='select datum, nazev, adresa, zmena from klient k, ucet u, terminal t '+
             'where jmeno like '''+jmeno+''' and prijmeni like '''+prijmeni+''' and '+
             't.ucet_id=u.ucet_id and k.klient_id=u.klient_id';
    end;
  end else
  if RadioButtonBank.checked then begin
    mysql:='select prijmeni, jmeno, datum,zmena, nazev, adresa from terminal t, ucet u, klient k'+
           ' where t.ucet_id=u.ucet_id and k.klient_id=u.klient_id and t.nazev=''Vyber z Karty''';
  end else
  if RadioButtonPrep.checked then begin
      mysql:='select prijmeni, jmeno, datum, nazev, adresa from terminal t, ucet u, klient k'+
           ' where t.ucet_id=u.ucet_id and k.klient_id=u.klient_id and t.nazev like ''%prepazce%''';
  end else
  if (dotaz.text<>'') then mysql:=dotaz.text;

  if mysql<>'' then
  with queryklient do begin
{    messagedlg(mysql,mtError, [mbAbort],0);}
{
  If Dotaz.text='' then
    QueryKlient.SQL.Add('SELECT * FROM ucet u, klient k, kkarta ka WHERE '+
                        'u.klient_id=k.klient_id and u.karta_id=ka.karta_id')
  else QueryKlient.SQL.Add(Dotaz.text);}
  SQL.clear;
  sql.add(mysql);
  Close;

  try
    Open;
  except
  on E: Exception do
    if not (E is ENoResultSet) then
      raise;
  end;
  end;
end;

procedure TFormUcty.NovyUcetClick(Sender: TObject);
begin
  TabSheet2.Enabled:=true;
  TabSheet2.caption:='vytvoreni uctu';
end;

procedure TFormUcty.BitBtn1Click(Sender: TObject);
var NumUcet,NumKarta:integer;
begin
  NumUcet:=0; NumKarta:=0;
{ tady se bude neco vkladat do tabulek...}
  if Klient_id.caption='' then begin
    MessageDlg('Musite vybrat klienta!', mtError, [mbAbort], 0)
  end else if (ucet_cislo.text='') then
     MessageDlg('Musite zadat cislo uctu!', mtError, [mbAbort], 0)
  else begin
    with NUQuery do begin
      If Active then Close;
      SQL.clear;
      SQL.add('SELECT max(ucet_id) as id FROM ucet');

      Open;
      NumUcet:=NUQuery.fieldbyname('id').asinteger;
      inc(NumUcet);
    end;

    if (CheckKarta.checked) then begin
     if (karta_cislo.text='') then
      MessageDlg('Musite zadat cislo karty!', mtError, [mbAbort], 0)
     else begin
      with NUQuery do begin
        if Active then Close;
        SQL.clear;
        SQL.add('SELECT max(karta_id) as id FROM kkarta');

        Open;
        NumKarta:=NUQuery.fieldbyname('id').asinteger;
        inc(NumKarta);
      end;
        with TableZmeny do begin
          Close;
          tablename:='kkarta';
          Active:=true;
          Append;
          FieldbyName('karta_id').AsInteger:=NumKarta;
          FieldbyName('cislo').AsString:=karta_cislo.text;
          FieldbyName('platnost').AsString:=karta_platnost.text;
          Post;
        end;
       end;
    end;
    with TableZmeny do begin
      Close;
      tablename:='ucet';
      Active:=true;
      Append;
      FieldbyName('ucet_id').AsInteger:=NumUcet;
      FieldbyName('karta_id').AsInteger:=NumKarta;
      FieldByName('klient_id').AsInteger:=strtoint(klient_id.caption);
      FieldByName('zustatek').AsInteger:=0;
      FieldByName('cislo_uctu').AsString:=Ucet_cislo.text;
      Post;
    end;
  end;
end;

procedure TFormUcty.BitBtn2Click(Sender: TObject);
begin
  { vycisteni policek ...}
  ucet_cislo.text:='';
  karta_cislo.text:='';
  karta_platnost.text:='';
end;

procedure TFormUcty.BitBtn3Click(Sender: TObject);
begin
  form_newklient.showmodal;
end;

procedure TFormUcty.Button1Click(Sender: TObject);
begin
  QueryName.Close;
  QueryName.ExecSQL;
  try
    QueryName.Open;
  except
  on E: Exception do
    if not (E is ENoResultSet) then
      raise;
  end;
end;

procedure TFormUcty.Button2Click(Sender: TObject);
var i,sel:integer;
begin
  klient_id.caption:=QueryName.FieldByName('klient_id').AsString;
  klient_jmeno.caption:=QueryName.FieldByName('prijmeni').AsString;
  klient_prijmeni.caption:=QueryName.FieldByName('jmeno').AsString;
  klient_adresa.caption:=QueryName.FieldByName('ulice').AsString+', '
  +QueryName.FieldByName('psc').AsString+' '+QueryName.FieldByName('mesto').AsString;
  klient_rc.caption:=QueryName.FieldByName('rc').AsString;

{  if DBGrid1.SelectedRows.Count>0 then
    with DBGrid1.DataSource.DataSet do begin
    for i:=0 to DBGrid1.SelectedRows.Count-1 do
      if DBGrid1.SelectedRows.CurrentRowSelected then
        sel:=i;


    GotoBookmark(pointer(DBGrid1.SelectedRows.Items[sel]));
    with DBGrid1.DataSource.DataSet do
      begin
          label1.caption:=Fields[0].AsString;
          label2.caption:=Fields[1].AsString;
          label3.caption:=Fields[2].AsString;
        end;
      end;
}
end;

function generate:string;
begin
  randomize;
  generate:=inttostr(round(random*1000000000));
end;


procedure TFormUcty.Gen_cuctuClick(Sender: TObject);
begin
  if Klient_id.caption='' then begin
    MessageDlg('Musite vybrat klienta!', mtError, [mbAbort], 0);
  end else
    Ucet_cislo.text:=generate;
end;


procedure TFormUcty.ButtonUcetClick(Sender: TObject);
begin
  with QueryTransakce do begin
    close;
    sql.clear;
    sql.add('select cislo_uctu as cislo from ucet');
    execsql;
    open;
  end;
    updateTerminal(querytransakce.fieldbyname('cislo').asstring,'');
end;

procedure TFormUcty.CheckKartaClick(Sender: TObject);
begin
  if (GroupBoxKarta.enabled) and (CheckKarta.checked=false) then
    GroupBoxKarta.enabled:=false
  else GroupBoxKarta.enabled:=true;
end;

procedure  TFormUcty.updateTerminal(ucet_cislo:string; karta_cislo:string);
begin
  with querytransakce2 do begin
    if karta_cislo='' then begin
      close;
      sql.clear;
      sql.add('select count(*) as pocet from ucet u, klient k where cislo_uctu='+ucet_cislo+' and u.klient_id=k.klient_id');

      open;
      if fieldbyname('pocet').asinteger=0 then labelinfo.caption:='Nevyhovuje zadny klient' else
     begin
      close;
      sql.clear;
      sql.add('select jmeno, prijmeni, zustatek from ucet u, klient k where cislo_uctu='+ucet_cislo+' and u.klient_id=k.klient_id');

      open;
      if (fieldbyname('zustatek').asinteger<=0) then labelinfo.caption:='Nelze provadet vyber, pouze vklad!'
      else labelinfo.caption:='';
      labelkj.caption:=fieldbyname('jmeno').asstring;
      labelkp.caption:=fieldbyname('prijmeni').asstring;
      labelzust.caption:=fieldbyname('zustatek').asstring;
      groupboxuctem.caption:=ucet_cislo;
     end;
    end else begin
      close;
      sql.Clear;
      sql.add('select count(*) as pocet from ucet u, kkarta ka, klient k where ka.cislo='+karta_cislo+' and u.karta_id=ka.karta_id and u.klient_id=k.klient_id');

      open;
      if fieldbyname('pocet').asinteger=0 then labelinfo.caption:='Nevyhovuje zadny klient' else
     begin
      close;
      sql.clear;
      sql.add('select jmeno, prijmeni, zustatek from ucet u, kkarta ka, klient k where ka.cislo='+karta_cislo+' and u.karta_id=ka.karta_id and u.klient_id=k.klient_id');

      open;
      if (fieldbyname('zustatek').asinteger<=0) then labelinfo.caption:='Nelze provadet vyber!'
      else labelinfo.caption:='';
      labelkj.caption:=fieldbyname('jmeno').asstring;
      labelkp.caption:=fieldbyname('prijmeni').asstring;
      labelzust.caption:=fieldbyname('zustatek').asstring;
      groupboxkartou.caption:=karta_cislo;
     end;
    end;
  end;
end;

procedure TFormUcty.RadioButton1Click(Sender: TObject);
begin
    labeltyp.caption:='Cislo uctu';
    with QueryTransakce do begin
    close;
    sql.clear;
    sql.add('select cislo_uctu as cislo from ucet');

    open;
  end;
end;

procedure TFormUcty.RadioButton2Click(Sender: TObject);
begin
   labeltyp.caption:='Cislo karty';
   with QueryTransakce do begin
    close;
    sql.clear;
    sql.add('select cislo from kkarta');

    open;
  end;
end;

procedure TFormUcty.DBGrid2CellClick(Column: TColumn);
begin
  if RadioButton1.checked then
    updateTerminal(querytransakce.fieldbyname('cislo').asstring,'')
  else
    updateTerminal('', querytransakce.fieldbyname('cislo').asstring)
end;

procedure TFormUcty.ButtonFindClick(Sender: TObject);
begin
  if RadioButton1.checked then
    updateTerminal(editcislo.text,'')
  else
    updateTerminal('', editcislo.text)
end;

procedure TFormUcty.BitBtn4Click(Sender: TObject);
var maxterm,ucet:integer;
begin
  if ((strtoint(edithodnota.text)>strtoint(labelzust.caption)) and
    (comboboxakce.Items[comboboxakce.itemindex]='Vyber na prepazce')) then
    messagedlg(concat('Nedostatecny zustatek na uctu!'#13#13'Pozadovany vyber: ',edithodnota.text,#13'Hotovost: ',labelzust.Caption),  mtError	, [mbAbort],0)
  else
  with querytransakce2 do begin
    close;
    sql.clear;
    sql.add('select max(terminal_id) as ti from terminal');

    open;
    maxterm:=fieldbyname('ti').asinteger;
    inc(maxterm);
    close;
    sql.clear;
    sql.add('select ucet_id as id from ucet where ucet.cislo_uctu='''+groupboxuctem.caption+'''');

    open;
    ucet:=fieldbyname('id').asinteger;
     with tablezmeny do begin
      Close;
      tablename:='terminal';
      Active:=true;
      Append;
      FieldbyName('terminal_id').AsInteger:=maxterm;
      FieldbyName('ucet_id').AsInteger:=ucet;
      FieldByName('nazev').Asstring:=comboboxakce.items[comboboxakce.itemindex];
      FieldByName('adresa').Asstring:=comboboxadresa.items[comboboxadresa.itemindex];
      fieldbyname('zmena').asinteger:=strtoint(edithodnota.text);
      fieldbyname('datum').asstring:=datumtransakce;
      Post;
      Open;
     end;

      UpdateUcet(ucet, strtoint(edithodnota.text));
     end;
end;

procedure TFormUcty.BitBtn6Click(Sender: TObject);
var maxterm, ucet:integer;
begin
  if ((strtoint(editkhodnota.text)>(strtoint(labelzust.caption)))) then
    messagedlg(concat('Nedostatecny zustatek na uctu!'#13#13'Pozadovany vyber: ',edithodnota.text,#13'Hotovost: ',labelzust.Caption),  mtError	, [mbAbort],0)
  else

  with querytransakce2 do begin
    close;
    sql.clear;
    sql.add('select max(terminal_id) as ti from terminal');

    open;
    maxterm:=fieldbyname('ti').asinteger;
    inc(maxterm);
    close;
    sql.clear;
    sql.add('select ucet_id as id from ucet u, kkarta ka where u.karta_id=ka.karta_id and ka.cislo='+groupboxkartou.caption);

    open;
    ucet:=fieldbyname('id').asinteger;
    with tablezmeny do begin
      tablename:='terminal';
      Active:=true;
      Append;
      FieldbyName('terminal_id').AsInteger:=maxterm;
      FieldbyName('ucet_id').AsInteger:=ucet;
      FieldByName('nazev').Asstring:=comboboxktyp.items[comboboxktyp.itemindex];
      FieldByName('adresa').Asstring:=comboboxkadresa.items[comboboxkadresa.itemindex];
      fieldbyname('zmena').asinteger:=strtoint(editkhodnota.text)*-1;
      fieldbyname('datum').asstring:=datumtransakce;
      Post;
      Open;
      updateucet(ucet,-1*strtoint(editkhodnota.text));
   end;
   end;

end;

procedure tformucty.updateucet(id, hodnota:integer);
var zmena,zustatek:integer; qstr:string;
begin
  with querytransakce2 do begin
    close;
    sql.clear;
    sql.add('select zustatek as zustatek from ucet where ucet.ucet_id='+inttostr(id));
    open;

    zustatek:=fieldbyname('zustatek').asinteger;
    zustatek:=zustatek+hodnota;

    close;
    sql.clear;
    qstr:=concat('update ucet set zustatek=',inttostr(zustatek),' where ucet_id=',inttostr(id));
    MessageDlg(qstr, mtConfirmation, [mbYes, mbNo], 0);
    sql.add(qstr);

   try
    open;
   except
   on E: Exception do
     messagedlg('Vse v poradku',mtConfirmation,[mbOk],0);
   end;
  end;
end;

function tformucty.datumtransakce:string;
var  Present: TDateTime;
  Year, Month, Day, Hour, Min, Sec, MSec: Word;
begin
  Present:=now;
  DecodeDate(Present, Year, Month, Day);
  DecodeTime(Present, Hour, Min, Sec, MSec);
  datumtransakce := IntToStr(Day) + '.'
    + IntToStr(Month) + '.' + IntToStr(Year)+' '
    + IntToStr(Hour) + ':' + IntToStr(Min);
end;

procedure TFormUcty.ButtonCKartyClick(Sender: TObject);
var  Present: TDateTime;
  Year, Month, Day, Hour, Min, Sec, MSec: Word;

begin
    if Klient_id.caption='' then begin
    MessageDlg('Musite vybrat klienta!', mtError, [mbAbort], 0);
  end else
    Karta_cislo.text:=generate;
    Present:= now;
    DecodeDate(Present, Year, Month, Day);
    karta_Platnost.text:=inttostr(Month)+'/'+inttostr(year+2);
end;

end.