AlarmeJT Code

Visual alert based on decoded Callsigns with WSJT-X in JT65, JT9, FT8.

Status: Beta
Brought to you by: f5jmh
[r338]: / trunk / udatastats.pas Maximize Restore History
245 lines (220 with data), 11.5 kB

{********************************************************************************
***                               AlarmeJT                                    ***
***    AlarmeJT is designed to work with exellent WSJT -X Joe Taylor, K1JT    ***
***    running on JT65A, JT9 and FT8 modes.                                   ***
***    -------------------------------------------------------------------    ***
***    version : 0.8 UDP beta                                                 ***
***    -------------------------------------------------------------------    ***
***    Copyright 2017 Alain Thébault (F5JMH)                                  ***
***                                                                           ***
***    This file is part of AlarmeJT.                                         ***
***                                                                           ***
***    AlarmeJT is free software: you can redistribute it and/or modify       ***
***    it under the terms of the GNU General Public License as published by   ***
***    the Free Software Foundation, either version 2.0 of the License, or    ***
***    any later version.                                                     ***
***                                                                           ***
***    AlarmeJT is distributed in the hope that it will be useful,            ***
***    but WITHOUT ANY WARRANTY; without even the implied warranty of         ***
***    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          ***
***    GNU General Public License for more details.                           ***
***                                                                           ***
***    You should have received a copy of the GNU General Public License      ***
***    along with AlarmeJT.  If not, see <http://www.gnu.org/licenses/>.      ***
***                                                                           ***
***    -------------------------------------------------------------------    ***
********************************************************************************}

unit udatastats;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, sqldb, sqlite3conn, FileUtil, ufonction, Forms,Dialogs;
type

  { TDMStats }

  TDMStats = class(TDataModule)
    DSStats: TDataSource;
    SQLiteStats: TSQLite3Connection;
    SQLStats: TSQLQuery;
    SQLTStats: TSQLTransaction;
    function ZoneCount(zone, mode, band, qsl:string):Integer;
    function DXCCCount(band, mode, qslr:string):Integer;
    function WASCount(mode, band, qsl:string):Integer;
    function TotalQSO(cfm:string):integer;
    function StatsLoc(mode,bande,qslr:string):integer;
    function ListeDxcc(dxccz,mode,bande,qslr:string):string;
  private
    { private declarations }

  public
    { public declarations }
  end;

var
  DMStats: TDMStats;

implementation

{$R *.lfm}

function TDMStats.ZoneCount(zone, mode, band, qsl:string):Integer;
begin
    SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
    SQLStats.Close;
    try
      if qsl = 'N' then
             begin
               SQLStats.SQL.Text := 'SELECT COUNT(DISTINCT('+zone+')) FROM adif WHERE '+zone+'<>"" AND MODE=:mode AND BAND=:band';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
             end;
          if qsl = 'Y' then
             begin
               SQLStats.SQL.Text := 'SELECT COUNT(DISTINCT('+zone+')) FROM adif WHERE '+zone+'<>"" AND MODE=:mode AND BAND=:band AND QSL_RCVD=:qsl';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
               SQLStats.params.parambyname('qsl').asstring := qsl;
             end;
      if SQLTStats.Active then SQLTStats.RollBack;
      SQLTStats.StartTransaction;
      SQLStats.Open;
      Result := SQLStats.Fields[0].AsInteger
    finally
      SQLStats.Close;
      SQLTStats.RollBack;
    end;
end;

function TDMStats.WASCount(mode, band, qsl:string):Integer;
begin
    SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
    SQLStats.Close;
    try
      if qsl = 'N' then
             begin
               SQLStats.SQL.Text := 'SELECT COUNT(DISTINCT(STATE)) FROM adif WHERE (DXCC="291" OR DXCC="110") AND STATE<>"" AND MODE=:mode AND BAND=:band';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
             end;
          if qsl = 'Y' then
             begin
               SQLStats.SQL.Text := 'SELECT COUNT(DISTINCT(STATE)) FROM adif WHERE (DXCC="291" OR DXCC="110") AND STATE<>"" AND MODE=:mode AND BAND=:band AND QSL_RCVD=:qsl';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
               SQLStats.params.parambyname('qsl').asstring := qsl;
             end;
      if SQLTStats.Active then SQLTStats.RollBack;
      SQLTStats.StartTransaction;
      SQLStats.Open;
      Result := SQLStats.Fields[0].AsInteger
    finally
      SQLStats.Close;
      SQLTStats.RollBack;
    end;
end;

function TDMStats.DXCCCount(band, mode, qslr:string):Integer;
begin
    SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
    SQLStats.Close;
    try
      if qslr = 'N' then
             begin
               SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE BAND=:band AND MODE=:mode';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
             end;
          if qslr = 'Y' then
             begin
               SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE BAND=:band AND MODE=:mode AND QSL_RCVD=:qslr';
               SQLStats.params.parambyname('mode').asstring := mode;
               SQLStats.params.parambyname('Band').asstring := band;
               SQLStats.params.parambyname('qslr').asstring := qslr;
             end;
      if SQLTStats.Active then SQLTStats.RollBack;
      SQLTStats.StartTransaction;
      SQLStats.Open;
      Result := SQLStats.Fields[0].AsInteger
    finally
      SQLStats.Close;
      SQLTStats.RollBack;
    end;
end;

function TDMStats.TotalQSO(cfm:string):integer;                     //******************************** Toute la function
begin
   SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
   SQLStats.Close;
       try
          case cfm of
             'T':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif';
             'C65':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT65"';
             'C9':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT9"';
             'C8':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE QSL_RCVD="Y" AND MODE="FT8"';

             'W65':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="JT65"';
             'W9':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="JT9"';
             'W8':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="FT8"';

             'DxccT':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif';
             'DxccC65':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT65"';
             'DxccC9':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT9"';
             'DxccC8':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE QSL_RCVD="Y" AND MODE="FT8"';

             'DxccW65':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE MODE="JT65"';
             'DxccW9':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE MODE="JT9"';
             'DxccW8':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(DXCC)) FROM adif WHERE MODE="FT8"';

             'JT65':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="JT65"';
             'JT9':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="JT9"';
             'FT8':SQLStats.SQL.Text:= 'SELECT COUNT(id) FROM adif WHERE MODE="FT8"';

             'LocW65':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE MODE="JT65" AND length(GRIDSQUARE) > 3';
             'LocW9':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE MODE="JT9" AND length(GRIDSQUARE) > 3';
             'LocW8':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE MODE="FT8" AND length(GRIDSQUARE) > 3';

             'LocC65':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT65" AND length(GRIDSQUARE) > 3';
             'LocC9':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE QSL_RCVD="Y" AND MODE="JT9" AND length(GRIDSQUARE) > 3';
             'LocC8':SQLStats.SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) FROM adif WHERE QSL_RCVD="Y" AND MODE="FT8" AND length(GRIDSQUARE) > 3';
          end;
          if SQLTStats.Active then SQLTStats.RollBack;
             SQLTStats.StartTransaction;
             SQLStats.Open;
             Result := SQLStats.Fields[0].AsInteger
       finally
         SQLStats.Close;
         SQLTStats.RollBack;
       end;
end;

function TDMStats.StatsLoc(mode,bande,qslr:string):integer;
begin
     SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
     SQLiteStats.Connected:=True;
     with SQLStats do begin
          close;
          Unprepare;
          if qslr = 'N' then
             begin
               SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) AS grid FROM adif WHERE MODE=:mode AND BAND=:Bande AND length(GRIDSQUARE) > 3';
               params.parambyname('mode').asstring := mode;
               params.parambyname('Bande').asstring := bande;
             end;
          if qslr = 'Y' then
             begin
               SQL.Text:= 'SELECT COUNT(DISTINCT(SUBSTR(GRIDSQUARE,1,4))) AS grid FROM adif WHERE MODE=:mode AND BAND=:Bande AND QSL_RCVD=:qslr AND length(GRIDSQUARE) > 3';
               params.parambyname('mode').asstring := mode;
               params.parambyname('Bande').asstring := bande;
               params.parambyname('qslr').asstring := qslr;
             end;
          Prepare;
          Open;
          result:=Fields[0].AsInteger;
          close;
        end;
     SQLiteStats.Connected:=False;
end;

function TDMStats.ListeDxcc(dxccz,mode,bande,qslr:string):string;
begin
     SQLiteStats.DatabaseName:=ExtractFilePath(Application.ExeName)+CARNET_TRAFFIC;
     SQLiteStats.Connected:=True;
       with SQLStats do begin
          close;
          Unprepare;
          SQL.Text:= 'SELECT COUNT(DXCC) AS ndxcc FROM adif WHERE DXCC=:dxcc AND MODE=:mode AND BAND=:bande AND QSL_RCVD=:qslr'; //ORDER BY CAST(DXCC AS INTEGER)';
          params.parambyname('mode').asstring := mode;
          params.parambyname('Bande').asstring := bande;
          params.parambyname('qslr').asstring := qslr;
          params.parambyname('dxcc').asstring := dxccz;
          Prepare;
          Open;
          result:=Fields[0].AsString;
          close;
        end;
     SQLiteStats.Connected:=False;
 end;

end.