unit SummaryManager;

  interface
    uses
      AdsData   (* used by AdsFunc *),
      AdsFunc   (* for AdsTable *),
      AdsTable  (* tAdsQuery *),
      AdvGrid   (* glCustom *),
      AdvObj    (* TMS *),
      AdvUtil   (* TMS *),
      BaseGrid  (* TMS *),
      Classes,
      ComCtrls  (* tDateTimePicker *),
      Controls  (* crHourglass, tCursor, tDate, tTime *),
      DB        (* tBooleanField, tDataSet, tDataSource, tDateField, tFloatField,
                    tIntegerField, tStringField, tTimeField *),
      DBAdvGrid (* tDBAdvGrid *),
      DBCtrls   (* tDBNavigator *),
      ExtCtrls  (* mystery need *),
      Forms     (* tForm, Screen *),
      Graphics  (* TMS *),
      Grids     (* TMS *),
      StdCtrls  (* tButton, tCheckBox, tGroupBox *),
      Windows   (* tRect *);

    type
      tfmSummaryManager = class(tForm)
          qGenericQuery:    tAdsQuery;
          qMostRecentEntry: tAdsQuery;
          qPhotocell:       tAdsQuery;
          qSummary:         tAdsQuery;
            qSummarySunriseBad: tBooleanField;
            qSummarySunsetBad:  tBooleanField;
            qSummaryTheDate: tDateField;
            qSummaryAvgTempF:     tFloatField;
            qSummaryMaxTempF:     tFloatField;
            qSummaryMinTempF:     tFloatField;
            qSummaryPM25:         tFloatField;
            qSummaryPondDepth:    tFloatField;
            qSummaryPondTempF:    tFloatField;
            qSummaryRainmm:       tFloatField;
            qSummaryXAvgTempC:    tFloatField;
            qSummaryACPercent:      tIntegerField;
            qSummaryAvgWindMPH:     tIntegerField;
            qSummaryFurnacePercent: tIntegerField;
            qSummaryMaxRH:          tIntegerField;
            qSummaryMaxWindMPH:     tIntegerField;
            qSummaryMinRH:          tIntegerField;
            qSummaryXACPercent:     tStringField;
            qSummaryXFurnacePercent: tStringField;
            qSummaryXRainMM:         tStringField;
            qSummaryXRHRange:        tStringField;
            qSummaryXSunrise:        tStringField;
            qSummaryXSunset:         tStringField;
            qSummaryXTempRangeF:     tStringField;
            qSummarySunrise:      tTimeField;
            qSummarySunset:       tTimeField;
            qSummaryXPhotoPeriod: tTimeField;
              dataSummary: tDataSource;
                dbadvgridSummary: tDBAdvGrid;
                navSummary: tDBNavigator;
          qTRH:                    tAdsQuery;
          qUpdateSunriseSunset:    tAdsQuery;
          qUpdateSunriseSunsetBad: tAdsQuery;
          tbSummary: tAdsTable;
          btnDumpSummary:   tButton;
          btnUpdateSummary: tButton;
          grpDeleteRecent: tGroupBox;
            qDeleteAfter: tAdsQuery;
            btnDeleteAfter: tButton;
            dtpDeleteAfter: tDateTimePicker;
          grpSunrise: tGroupBox;
            btnSmoothSunrise: tButton;
            ckbxSunriseBad: tCheckBox;
          grpSunset: tGroupBox;
            btnSmoothSunset: tButton;
            ckbxSunsetBad: tCheckBox;

          procedure qSummaryAfterScroll(DataSet: tDataSet);
          procedure qSummaryCalcFields (DataSet: tDataSet);

          procedure btnDeleteAfterClick  (Sender: tObject);
          procedure btnDumpSummaryClick  (Sender: tObject);
          procedure btnUpdateSummaryClick(Sender: tObject);
          procedure ckbxSunriseBadClick  (Sender: tObject);
          procedure ckbxSunsetBadClick   (Sender: tObject);
          procedure FormDblClick         (Sender: tObject);
          procedure FormShow             (Sender: tObject);
          procedure SmoothPhotoPeriod    (Sender: tObject);
        private
          mmRainTrace: real;
          procedure UpdateBad(const FieldName: string; const Badness: boolean);
        public
          procedure UpdateIfNeeded;
        end (* tfmSummaryManager *);

    var
      fmSummaryManager: tfmSummaryManager;

  implementation
    uses
      DataSetToTabDelimitedFile (* DataSetToTDFile *),
      DateUtils                 (* DateOf, HourSpan, IncDay, Today, Yesterday *),
      DBAdvGridUtils            (* SetUpDBAdvGridReadOnly *),
      Dialogs                   (* ShowMessageFmt *),
      HC3DM                     (* dbmSummary, Global *),
      HC4Main                   (* fmHC4Main *),
      HC3Utilities              (* ChangeDebuggingMode, FtoC, INILoadFloat *),
      MyConsts                  (* BLANK, CR, LPAR, nullstring, RPAR,
                                     UsualFormColor *),
      ObservedAndWM             (* obAC, obFurnace, obImplicit, obLeftPower,
                                     tObserved, obOutside, obRightPower *),
      SysUtils                  (* Abort, Date, EConvertError, EncodeDate,
                                     EZeroDivide, Format, FormatDateTime, Now,
                                     StringReplace,  StrToFloat, StrToIntDef,
                                     Trim, UpperCase *);
      {$R *.dfm}

    const
      DateTest = '  convert(WhenRecorded, SQL_DATE) between :GapBegin and :GapEnd ';

    procedure tfmSummaryManager.btnDeleteAfterClick(Sender: tObject);
      (* get rid of incomplete data *)
    
      begin (* tfmSummaryManager.btnDeleteAfterClick *)
        qDeleteAfter.ParamByName('DeleteAfter').AsDate := dtpDeleteAfter.Date;
        qDeleteAfter.ExecSQL;
        qSummary.Close;
        qSummary.Open;
        qSummary.Last
      end   (* tfmSummaryManager.btnDeleteAfterClick *);

    procedure tfmSummaryManager.btnDumpSummaryClick(Sender: tObject);
      (* dump to file *)
    
      begin (* tfmSummaryManager.btnDumpSummaryClick *)
        DataSetToTDFile(qSummary, 'TheDate')
      end   (* tfmSummaryManager.btnDumpSummaryClick *);

    procedure tfmSummaryManager.btnUpdateSummaryClick(Sender: tObject);
      (* Go through NewPowerLog, RainCumulative, and SensorLog, looking for
      days before today for which data are not yet in the summary, and move the
      data in.  Because Advantage SQL won't allow aggregates to be used in
      UPDATE ... FROM constructions, the process is tedious:
           GapBegin is succ(last dat in summary)
           GapEnd is Yesterday
           if GapEnd >= GapBegin
             open queries that collect the aggregates
             for each day in gap
               MineQuery(TThisDate, heQuery, Sink1, Sink2, Sink3)
                 where fields[0 .. 3(?)} will be copied into fthe named fields of Summary
      *)

      var
        OldCursor: tCursor;
        GapBegin, GapEnd: tDate;

      procedure FillInDates;
        (* prime records of tbSummary with TheDate *)

        var
          ThisDate: tDate;

        begin (* FillInDates *)
          ThisDate := GapBegin;
          with tbSummary do
            while ThisDate <= GapEnd do
              begin (* one more date *)
                Insert;
                  FieldByName('TheDate').AsDateTime := ThisDate;
                Post;
                ThisDate := IncDay(ThisDate)
              end   (* one more date *);
        end   (* FillInDates *);

      procedure GetAverageAndRange(const FieldName: string);
        (* for TempF & RH *)

        var
          WantAvg: boolean;
          WhenRecorded: tDate;
          TheAvg, TheMin, TheMax: real;
          GetAvgLine: string;

        begin (* GetAverageAndRange *)
          TheAvg := 0; // avoid spurious warning
          WantAvg := (FieldName = 'TempF');
          if WantAvg then
            GetAvgLine := 'avg(TempF) as TheAvg, '
          else
            GetAvgLine := nullstring;

          with qTRH do
            begin (* qTRH *)
              Close;
              SQL.Text := ' select                                        ' +
                          '   convert(WhenRecorded, SQL_DATE) as TheDate, ' +
                              GetAvgLine                                    +
                              Format('min(%s) as TheMin, ', [FieldName])    +
                              Format('max(%s) as TheMax ',  [FieldName])    +
                          ' from                                          ' +
                          '   TRHP                                        ' +
                          ' where                                         ' +
                              Format('%s and (%s is not null) and (ObservedUID = %d)',
                                     [DateTest, FieldName, ord(obOutside)])      +
                          ' group by                                      ' +
                          '   TheDate                                     ';
              ParamByName('GapBegin').AsDate := GapBegin;
              ParamByName('GapEnd'  ).AsDate := GapEnd;
                          
              Open;
              while not EOF do
                begin (* one record *)
                  WhenRecorded := FieldByName('TheDate').AsDateTime;
                  if WantAvg then
                    TheAvg     := FieldByName('TheAvg' ).AsFloat;
                  TheMin       := FieldByName('TheMin' ).AsFloat;
                  TheMax       := FieldByName('TheMax' ).AsFloat;

                  with tbSummary do
                    if Locate('TheDate', DateOf(WhenRecorded), []) then
                      begin (* update this field *)
                        Edit;
                        if WantAvg then
                          FieldByName('Avg' + FieldName).AsFloat := TheAvg;
                        FieldByName  ('Min' + FieldName).AsFloat := TheMin;
                        FieldByName  ('Max' + FieldName).AsFloat := TheMax;
                        Post
                      end   (* update this field *);
                  Next
                end   (* one record *);

              Close
            end   (* qTRH *);
        end   (* GetAverageAndRange *);

      procedure GetPhotocellData;
        (* special case *)

        var
          WhenRecorded: tDate;
          Sunrise, Sunset: tTime;

        begin (* GetPhotocellData *)
          with qPhotocell do
            begin (* qPhotocell *)
              Close;
              ParamByName('GapBegin' ).AsDate    := GapBegin;
              ParamByName('GapEnd'   ).AsDate    := GapEnd;
              ParamByName('obOutside').AsInteger := ord(obOutside);

              Open;
              while not EOF do
                begin (* one record *)
                  WhenRecorded := FieldByName('TheDate').AsDateTime;
                  Sunrise      := FieldByName('Sunrise').AsDateTime;
                  Sunset       := FieldByName('Sunset' ).AsDateTime;

                  with tbSummary do
                    if Locate('TheDate', DateOf(WhenRecorded), []) then
                      begin (* update this field *)
                        Edit;
                        FieldByName('Sunrise').AsDateTime := Sunrise;
                        FieldByName('Sunset' ).AsDateTime := Sunset;
                        Post
                      end   (* update this field *);
                  Next
                end   (* one record *);

              Close
            end   (* qPhotocell *)
        end   (* GetPhotocellData *);

      procedure GetSummaryField(const SummaryFN, TableName, TableFN: string;
                                ObservedUID: tObserved = obImplicit);
        (* common code *)

        var
          WhenRecorded: tDate;
          Thing: real;
          ObservedTest: string;

        begin (* GetSummaryField *)
          if ObservedUID <> obImplicit then
            ObservedTest := Format(' and ObservedUID = %d ', [ord(ObservedUID)])
          else
            ObservedTest := nullstring;

          with qGenericQuery do
            begin (* qGenericQuery *)
              Close;
              SQL.Text := ' select                                        ' +
                          '   convert(WhenRecorded, SQL_DATE) as TheDate, ' +
                              TableFN                                       +
                          ' from                                          ' +
                              TableName                                     +
                          ' where                                         ' +
                              DateTest + ObservedTest                       +
                          ' group by                                      ' +
                          '   convert(WhenRecorded, SQL_DATE)             ';
              ParamByName('GapBegin').AsDate := GapBegin;
              ParamByName('GapEnd'  ).AsDate := GapEnd;

              Open;
              while not EOF do
                begin (* one record *)
                  WhenRecorded := Fields[0].AsDateTime;
                  Thing        := Fields[1].AsFloat;
                  with tbSummary do
                    if Locate('TheDate', DateOf(WhenRecorded), []) then
                      begin (* update this field *)
                        Edit;
                        FieldByName(SummaryFN).AsFloat := Thing;
                        Post
                      end   (* update this field *);
                  Next
                end   (* one record *);

              Close
            end   (* qGenericQuery *)
        end   (* GetSummaryField *);

      begin (* tfmSummaryManager.btnUpdateSummaryClick *)
        OldCursor := Screen.Cursor;
        Screen.Cursor := crHourglass;
        tbSummary.Open;
        try     (* must restore cursor *)
          with qMostRecentEntry do
            begin (* qMostRecentEntry *)
              Open;
              GapBegin := IncDay(DateOf(Fields[0].AsDateTime));
              Close
            end   (* qMostRecentEntry *);
          GapEnd := Yesterday;

          FillInDates;   // SummaryFN      TableName       TableFN                    ObservedUID
          GetPhotocellData;             //   Light
          GetSummaryField('ACPercent',      'OnOff', 'round(100*(max(HoursOn)/24),0)', obAC        );
          GetSummaryField('FurnacePercent', 'OnOff', 'round(100*(max(HoursOn)/24),0)', obFurnace   );
          GetSummaryField('PondTempF',      'Pond',  'avg(TempF)'                                  );
          GetSummaryField('Rainmm',         'Rain',  'max(Rainmm)'                                 );
          GetSummaryField('PM25',           'SPS30', 'avg(Mass02p5)',                  obOutside   );
          GetAverageAndRange('TempF');  //   TRHP
          GetAverageAndRange('RH');     //   TRHP
          GetSummaryField('AvgWindMPH',     'Wind',  'avg(WindMPHRaw)'                             );
          GetSummaryField('MaxWindMPH',     'Wind',  'max(WindMPHMax)'                             );
          qSummary.Close;
          qSummary.Open;
          qSummary.Last;
        finally (* must restore cursor *)
          tbSummary.Close;
          Screen.Cursor := OldCursor
        end     (* must restore cursor *)
      end   (* tfmSummaryManager.btnUpdateSummaryClick *);

    procedure tfmSummaryManager.ckbxSunriseBadClick(Sender: tObject);
      (* update table from checkbox *)

      begin (* tfmSummaryManager.ckbxSunriseBadClick *)
        UpdateBad('SunriseBad', ckbxSunriseBad.Checked)
      end   (* tfmSummaryManager.ckbxSunriseBadClick *);

    procedure tfmSummaryManager.ckbxSunsetBadClick(Sender: tObject);
      (* update table from checkbox *)

      begin (* tfmSummaryManager.ckbxSunsetBadClick *)
        UpdateBad('SunsetBad', ckbxSunsetBad.Checked)
      end   (* tfmSummaryManager.ckbxSunsetBadClick *);

    procedure tfmSummaryManager.FormDblClick(Sender: tObject);
      (* show master form *)

      begin (* tfmSummaryManager.FormDblClick *)
        fmHC4Main.Show
      end   (* tfmSummaryManager.FormDblClick *);

    procedure tfmSummaryManager.FormShow(Sender: tObject);
      (* initialize *)

      begin (* tfmSummaryManager.FormShow *)
        self.Color := UsualFormColor;
        mmRainTrace := INILoadFloat('Rain', 'mmTrace');
        dtpDeleteAfter.Date := Today;
        with qSummary do
          begin (* qSummary *)
            AfterScroll := nil;
            Open;
            SetUpDBAdvGridReadOnly(dbadvgridSummary);
            Last;
            btnUpdateSummary.Enabled := (Global.Status = gsConnected);
            AfterScroll := qSummaryAfterScroll
          end   (* qSummary *)
      end   (* tfmSummaryManager.FormShow *);

    procedure tfmSummaryManager.qSummaryAfterScroll(DataSet: tDataSet);
      (* check box is not data-aware.
         Setting <checkbox>.Checked triggers OnClick event *)

      begin (* tfmSummaryManager.qSummaryAfterScroll *)
        ckbxSunriseBad.OnClick := nil;
        ckbxSunsetBad.OnClick := nil;

        with DataSet do
          begin (* DataSet *)
            ckbxSunriseBad.Checked := FieldByName('SunriseBad').AsBoolean;
            ckbxSunsetBad.Checked  := FieldByName('SunsetBad' ).AsBoolean
          end   (* DataSet *);

        ckbxSunriseBad.OnClick := ckbxSunriseBadClick;
        ckbxSunsetBad.OnClick  := ckbxSunsetBadClick
      end   (* tfmSummaryManager.qSummaryAfterScroll *);

    procedure tfmSummaryManager.qSummaryCalcFields(DataSet: tDataSet);
      (* lots of prettyprinting *)

      procedure LightRelated;
        (* sunrise, sunset, photoperiod *)

        var
          Sunrise, Sunset: tTime;

        procedure SunriseSunset(const TheTime: tTime;
                                const BadFieldName, ResultFieldName: string);
          (* flag funny results *)

          var
            BadTime: boolean;
            TimeString: string;

          begin (* SunriseSunset *)
            with DataSet do
              begin (* DataSet *)
                BadTime := FieldByName(BadFieldName).AsBoolean;

                TimeString := FormatDateTime('hh:nn', TheTime);
                if TheTime = 0 then
                  TimeString := nullstring
                else if BadTime then
                  TimeString := LPAR + TimeString + RPAR;

                FieldByName(ResultFieldName).AsString := TimeString
              end   (* DataSet *)
          end   (* SunriseSunset *);

        begin (* LightRelated *)
          Sunrise := 0;
          Sunset := 0;

          with DataSet do
            begin (* DataSet *)
              with FieldByName('Sunrise') do
                if not IsNull then
                  SunRise  := AsDateTime;
              SunriseSunset(Sunrise, 'SunriseBad', 'XSunrise');
              with FieldByName('Sunset') do
                if not IsNull then
                  Sunset  := AsDateTime;
              SunriseSunset(Sunset,  'SunsetBad',  'XSunset' );

              if Sunrise <> 0 then
                if Sunset <> 0 then
                  with FieldByName('XPhotoPeriod') do
                    AsDateTime := HourSpan(SunSet, Sunrise) / 24.0
            end   (* DataSet *)
        end   (* LightRelated *);

      procedure Percents;
        (* shorten procedure *)

        var
          InOutage: boolean;
          BeginOutage, EndOutage, TheDate: tDate;
          ACPercent, FurnacePercent: integer;

        begin (* Percents *)
          BeginOutage := EncodeDate(2020, 2, 9);
          EndOutage   := EncodeDate(2020, 7, 17);
          with DataSet do
            begin (* DataSet *)
              ACPercent      := FieldByName('ACPercent'     ).AsInteger;
              FurnacePercent := FieldByName('FurnacePercent').AsInteger;
              TheDate        := FieldByName('TheDate'       ).AsDateTime;

              InOutage := (TheDate >= BeginOutage) and (TheDate <= EndOutage);

              with FieldByName('XACPercent') do
                if InOutage then
                  AsString := 'N/A'
                else if ACPercent = 0 then
                  AsString := nullstring
                else
                  AsString := Format('%d%%', [ACPercent]);

              with FieldByName('XFurnacePercent') do
                if InOutage then
                  AsString := 'N/A'
                else if FurnacePercent = 0 then
                  AsString := nullstring
                else
                  AsString := Format('%d%%', [FurnacePercent]);
            end   (* DataSet *)
        end   (* Percents *);

      procedure Rain;
        (* shorten CalcFields *)

        var
          TotalRainfallMM: real;

        begin (* Rain *)
          with DataSet do
            begin (* DataSet *)
              TotalRainfallMM := FieldByName('Rainmm').AsFloat;
              with FieldByName('XRainMM') do
                if TotalRainfallMM = 0 then
                  AsString := nullstring
                else if TotalRainfallMM < mmRainTrace then
                  AsString := 'trace'
                else
                  AsString := IntToStr(round(TotalRainfallMM))
            end   (* DataSet *)
        end   (* Rain *);

      procedure TempAndRH;
        (* set XX.X (xx - XX) *)

        var
          AvgTemp, MaxRH, MaxTemp, MinRH, MinTemp: real;

        begin (* TempAndRH *)
          with DataSet do
            begin (* DataSet *)
              with FieldByName('MaxRH') do
                if not IsNull then
                  begin (* max OK *)
                    MaxRH := AsFloat;
                    with FieldByName('MinRH') do
                      if not IsNull then
                        begin (* range OK *)
                          MinRH := AsFloat;
                          with FieldByName('XRHRange') do
                            if abs(MaxRH - MinRH) < 0.5  then
                              AsString := Format('%.0f', [MinRH])
                            else
                              AsString := Format('%.0f - %.0f', [MinRH, MaxRH])
                        end   (* range OK *)
                  end   (* max OK *);

              with FieldByName('AvgTempF') do
                if not IsNull then
                  begin (* Avg OK *)
                    AvgTemp := AsFloat;
                    with FieldByName('MaxTempF') do
                      if not IsNull then
                        begin (* Max OK *)
                          MaxTemp := AsFloat;
                          with FieldByName('MinTempF') do
                            if not IsNull then
                              begin (* Min OK *)
                                MinTemp := AsFloat;
                                with FieldByName('XTempRangeF') do
                                  AsString := Format('%.1f (%.0f - %.0f)',
                                                     [AvgTemp, MinTemp, MaxTemp])
                              end   (* Min OK *)
                        end   (* Max OK *)
                  end   (* Avg OK *)
            end   (* DataSet *)
        end   (* TempAndRH *);

      begin (* tfmSummaryManager.qSummaryCalcFields *)
        with DataSet do
          begin (* DataSet *)
            Percents;
            LightRelated;

            TempAndRH;
            Rain
          end   (* DataSet *)
      end   (* tfmSummaryManager.qSummaryCalcFields *);

    procedure tfmSummaryManager.SmoothPhotoPeriod(Sender: tObject);
      (* set the field named by the button caption to be the average of its
      value on the two surrounding days *)

      var
        OldDate: tDate;
        Fieldname: string;
        NewTime, NextTime, PriorTime: tTime;

      begin (* tfmSummaryManager.SmoothPhotoPeriod *)
        FieldName := ((Sender as tButton).Parent as tGroupBox).Caption;

        with qSummary do
          begin (* qSummary *)
            OldDate := FieldByName('TheDate').AsDateTime;
            Prior;
            PriorTime := FieldByName(FieldName).AsDateTime;
            Next;
            Next;
            NextTime := FieldByName(FieldName).AsDateTime;
            Close;

            NewTime := (PriorTime + NextTime) / 2;

            with qUpdateSunriseSunset do
              begin (* qUpdateSunriseSunset *)
                SQL.Text := 'update                    ' +
                            '  Summary3                ' +
                            'set                       ' +
                              Fieldname + ' = :NewTime ' +
                            'where                     ' +
                            '  TheDate = :OldDate      ';
                ParamByName('NewTime').AsTime := NewTime;
                ParamByName('OldDate').AsDate := OldDate;
                ExecSQL
              end   (* qUpdateSunriseSunset *);

            Open;
            if not Locate('TheDate', OldDate, []) then
              Last
          end   (* qSummary *)
      end   (* tfmSummaryManager.SmoothPhotoPeriod *);

    procedure tfmSummaryManager.UpdateIfNeeded;
      (* could call on starting Main *)
    
      begin (* tfmSummaryManager.UpdateIfNeeded *)
        with qSummary do
          begin (* qSummary *)
            Close;
            Open;
            Last;
            if DateOf(FieldByName('TheDate').AsDateTime) < DateOf(Today) then
              btnUpdateSummaryClick(nil)
          end   (* qSummary *)
      end   (* tfmSummaryManager.UpdateIfNeeded *);

    procedure tfmSummaryManager.UpdateBad(const FieldName: string;
                                          const Badness: boolean);
      (* service for checkboxes *)

      var
        TheDate: tDate;

      begin (* tfmSummaryManager.UpdateBad *)
        with qSummary do
          begin (* qSummary *)
            TheDate := FieldByName('TheDate').AsDateTime;

            with qUpdateSunriseSunsetBad do
              begin (* qUpdateSunriseSunsetBad *)
                SQL.Text := 'update                     ' +
                            '  Summary3                 ' +
                            'set                        ' +
                               FieldName + ' = :Badness ' +
                            'where                      ' +
                            '  TheDate = :TheDate       ';
                ParamByName('Badness').AsBoolean := Badness;
                ParamByName('TheDate').AsDate    := TheDate;
                ExecSQL
              end   (* qUpdateSunriseSunsetBad *);

            Close;
            Open;
            if not Locate('TheDate', TheDate, []) then
              (* ignore it *)
          end   (* qSummary *)
      end   (* tfmSummaryManager.UpdateBad *);



end.
