source: branches/web/Pages/UFinancePage.pas

unit UFinancePage;

{$mode delphi}{$H+}

interface

uses
  UDatabase, UConfig;

type

  { TBaseObject }

  TBaseObject = class(TDbObject)
  private
    FName: string;
  published
    property Name: string read FName write FName;
  end;

function FinanceOverviewPage: string;
  
implementation

uses 
  USqlDatabase, UCore, SysUtils, UFinance;

function FinanceOverviewPage: string;
begin
  Result := ShowHeader('<strong>ZděchovNET</strong> - komunitní počítačová síť', 'Finance');
  Result := Result + '<table><tr><td valign="top"><table><tr><td>' +
HtmlLink('Výpis zařízení', 'zarizeni.php') + '<br />' +
HtmlLink('Seznam členů', '?p=finance-clenove') + '<br />' +
HtmlLink('Spotřeba energie', 'spotreba.php') + '<br />' +
HtmlLink('Investice v síti', '/aktuality/index.php?category=9') + '<br />' +
HtmlLink('Měsíční přehledy', 'monthly_overall.php') + '<br />' +
HtmlLink('Faktury za internet', 'Faktury') + '<br />' +
HtmlLink('Tarify', 'tarify.php') + '<br />' +
HtmlLink('Výpis skladových zásob', 'inventory.php') + '<br />' +
HtmlLink('Porovnání tarifů s konkurencí', 'srovnani.php') + '<br />' +
'</td><td></td></tr></table><br /><strong>Souhrn:</strong><br />Aktuální měsíční poplatek za společný internet (10240/10240 kbit/s):' +
' <strong>15351 Kč</strong><br />Počet členů (s internetem): <strong>79 (79)</strong><br />Celková cena zařízení sítě: <strong>106335 Kč</strong><br />Celkem peněz na účtech: <strong>' +
'-14537 Kč</strong><br />Stanovená cena jedné kWh: <strong>3 Kč</strong><br />V připojených domácnostech celkem <strong>273</strong>' +
' občanů ze všech <strong>600</strong> (46 %)<br />IČO: <strong>75904535</strong><br />DIČ: <strong>CZ8303255884</strong><br />Bankovní účet: <strong>218098370 / 0300</strong><br /><br /></td><td valign="top">Měsíční vyúčtování:<br />' +
'' +
'<table border="1" cellspacing="0" cellpadding="3">' +
'<tr><th>Položka</th><th>Cena [Kč]</th></tr>' +
'<tr><th colspan="2">Příjmy</th></tr><tr><td>Poplatky účastníků za internet</td><td align="right">27010</td></tr><tr><td><strong>Celkem</strong></td><td align="right"><strong>27010</strong></td></tr><tr><th colspan="2">Výdaje</th></tr>' +
'<tr><td>Internet</td><td align="right">15351</td></tr><tr><td>Správa sítě</td><td align="right">3160</td></tr><tr><td>Spotřeba energie</td><td align="right">525</td></tr><tr><td><strong>Celkem</strong></td><td align="right"><strong>' +
'19036</strong></td></tr><tr><td colspan="2">&nbsp;</td></tr><tr><td><strong>Rozdíl</strong></td><td align="right"><strong>7974</strong></td></tr><tr><td>Převod do dalšího měsíce</td><td align="right">7974</td></tr></table>' +
'<br />Stav účtu sítě: 0 Kč<br /><a href="clenove.php?show=user&amp;id=46">Výpis účtu</a><br /></td></tr></table>';
  Result := Result + ShowFooter;
end;

function FinanceClientList: string;
var
  DbRows: TDbRows;
  I: Integer;
  TotalDevice: Integer;
  TotalMonth: Integer;
  TotalCash: Integer;
  Monthly: string;
  CurrentTariffName: string;
  NextTariffName: string;
  Member: TMember;
  Cash: string;
  Finance: TFinance;
begin
  Finance := TFinance.Create;
  Member := TMember.Create;
  Result := ShowHeader('<strong>ZděchovNET</strong> - komunitní počítačová síť', 'Finance');
  Result := Result + 'Seznam účastníků:<br />' +
    '<table style="font-size: smaller;" border="1" cellspacing="0" cellpadding="3">' +
    '<tr><th>Jméno</th>';
  Result := Result + '<th>Měsíční poplatek [Kč]</th><th>Stav účtu [Kč]</th><th>Běžící tarif</th><th>Příští tarif</th><th>Poznámky</th></tr>';
  TotalDevice := 0;
  TotalMonth := 0;
  TotalCash := 0;
  DbRows := Database.Query('SELECT *, CONCAT(users.second_name, " ", users.first_name) as Fullname, ((SELECT COALESCE(SUM(Value), 0) FROM FinanceCashFlow WHERE Subject=users.id) + ' +
    '(SELECT COALESCE(SUM(-Value), 0) FROM FinanceClaimsLiabilities WHERE Subject=users.id)) as Cash FROM users WHERE role=2 ORDER BY fullname');
  for I := 0 to DbRows.Count - 1 do begin
     Member.LoadFromDbRecord(DbRows[I]);
     Monthly := IntToStr(Member.Monthly - Member.Plus); 
     if not Member.Hardware then Monthly := '(trvale nepřipojen) ' + IntToStr(-Member.Plus)
      else begin
        if not Member.Overheads then Monthly := '(dočasně nepřipojen) ' + Monthly
        else begin
          if not Member.Inet then Monthly := '(bez internetu) ' + Monthly
          else Monthly := Monthly;
        end;
      end;
      TotalCash := TotalCash + Member.Cash;
      if Member.Cash < 0 then Cash := '<span style="color: red;">' + IntToStr(Member.Cash) + '</span>'
            else Cash := IntToStr(Member.Cash);
      CurrentTariffName := TTariff(Finance.TariffList[StrToInt(DbRows[I].Values['inet_tarif_now'])]).Name;
      NextTariffName := TTariff(Finance.TariffList[StrToInt(DbRows[I].Values['inet_tarif_next'])]).Name;
      Result := Result + '<tr><td><a href="?show=user&amp;id=' + IntToStr(Member.Id) + '">' + Member.FullName + '</a></td>';
      Result := Result + '<td align="right">' + Monthly + '</td><td align="right">' + Cash + '</td><td align="center">' + CurrentTariffName + '</td><td align="center">' + NextTariffName + '</td><td>' + Member.Notice + '&nbsp;</td></tr>';
      TotalDevice := TotalDevice + Member.NetworkDevice;
      TotalMonth := TotalMonth + (Member.Monthly - Member.Plus);
    end;
    Result := Result + '<tr><td><strong>Celkem</strong></td>';
    Result := Result + '<td align="right"><strong>' + IntToStr(TotalMonth) + '</strong></td><td align="right"><strong>' + IntToStr(TotalCash) + '</strong></td><td align="right">&nbsp;</td><td align="right">&nbsp;</td><td align="right">&nbsp;</td></tr>' +
    '</table>';
  Result := Result + ShowFooter;
  Member.Free;
  Finance.Free;
end;

function Test: string;
var
  Base: TBaseObject;
begin
  Result := ShowHeader('<strong>ZděchovNET</strong> - komunitní počítačová síť', 'Finance');
  Base := TBaseObject.Create(nil);
  Base.Database := TDatabase.Create;
  Base.Database.Hostname := DatabaseHostname;
  Base.Database.Database := 'centrala';
  Base.Database.UserName := DatabaseUserName;
  Base.Database.Password := DatabasePassword;
  Base.Database.Connect;
  //Base.Store;

  Result := Result + ShowFooter;
end;

{ TBaseObject }

begin
  RegisterPage('finance', @FinanceOverviewPage);    
  RegisterPage('finance-clenove', @FinanceClientList);
  RegisterPage('test', @Test);
end.