unit SysEditServiceKpi;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, DBCtrls, Db, DBTables;

type
  TSysEditServiceKpiForm = class(TForm)
    lblID: TLabel;
    lblDescription: TLabel;
    lblResponseHours: TLabel;
    lblRestorationHours: TLabel;
    lblAvailability: TLabel;
    lblCoreHours: TLabel;
    lblAfterHours: TLabel;
    lblTravelTime: TLabel;
    lblKpiNumber: TLabel;
    lblFaultDetect: TLabel;
    edtFaultDetect: TEdit;
    edtID: TEdit;
    edtKpiNumber: TEdit;
    edtDescription: TEdit;
    edtResponseHours: TEdit;
    edtRestorationHours: TEdit;
    edtAvailability: TEdit;
    edtCoreHours: TEdit;
    edtAfterHours: TEdit;
    edtTravelTime: TEdit;
    btnOk: TButton;
    btnCancel: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnOkClick(Sender: TObject);
  private
    { Private declarations }
    lAppend : boolean;
    lAudit : boolean;
    AuditActionUpdate : string;
    AuditActionDelete : string;
    AuditActionInsert : string;
    Original_KpiNumber              : string;
    Original_Description            : string;
    Original_ResponseHours          : string;
    Original_RestorationHours       : string;
    Original_Availability           : string;
    Original_CoreHours              : string;
    Original_AfterHours             : string;
    Original_TravelTime             : string;
    Original_FaultDetect            : string;
  protected
    procedure SetAppend(Value: boolean);
    procedure SetID(Value: string);
    procedure SetKpiNumber(const Value: string);
    function GetKpiNumber: string;
    procedure SetDescription(const Value: string);
    function GetDescription: string;
    procedure SetResponseHours(const Value: string);
    function GetResponseHours: string;
    procedure SetRestorationHours(const Value: string);
    function GetRestorationHours: string;
    procedure SetAvailability(const Value: string);
    function GetAvailability: string;
    procedure SetCoreHours(const Value: string);
    function GetCoreHours: string;
    procedure SetAfterHours(const Value: string);
    function GetAfterHours: string;
    procedure SetTravelTime(const Value: string);
    function GetTravelTime: string;
    procedure SetFaultDetect(const Value: string);
    function GetFaultDetect: string;
  published
    property AppendMode: boolean read lAppend write SetAppend;
    property ID: string write SetID;
    property KpiNumber: string read GetKpiNumber write SetKpiNumber;
    property Description: string read GetDescription write SetDescription;
    property ResponseHours: string read GetResponseHours write SetResponseHours;
    property RestorationHours: string read GetRestorationHours write SetRestorationHours;
    property Availability: string read GetAvailability write SetAvailability;
    property CoreHours: string read GetCoreHours write SetCoreHours;
    property AfterHours: string read GetAfterHours write SetAfterHours;
    property TravelTime: string read GetTravelTime write SetTravelTime;
    property FaultDetect: string read GetFaultDetect write SetFaultDetect;
  public
    { Public declarations }
  end;

const
  LEN_COMMON = 50;
  LEN_DESCRIPTION  = 128;

var
  SysEditServiceKpiForm: TSysEditServiceKpiForm;
  sUserName : string;

implementation

uses
  PPSysMain, GenFns;

{$R *.DFM}

procedure TSysEditServiceKpiForm.FormCreate(Sender: TObject);
var
  qFormCreate : TQuery;
  ActionDescription : string;
begin
  qFormCreate := TQuery.Create(SELF);           // SysEditServiceKpiForm
  sUserName := Trim(MainForm.OPERATORNAME);
  qFormCreate := TQuery.Create(SELF);
  qFormCreate.DatabaseName := 'dbPPdata';
  qFormCreate.SQL.Add('SELECT *');
  qFormCreate.SQL.Add('FROM tEvent');
  qFormCreate.SQL.Add('WHERE Description LIKE ''Reference%''');
  qFormCreate.Open;
  while not qFormCreate.Eof do begin
    ActionDescription := UpperCase(qFormCreate.FieldByName('Description').AsString);
    if (Pos('UPDATE', ActionDescription) > 0) then
      AuditActionUpdate := qFormCreate.FieldByName('id').AsString
    else if (Pos('DELETE', ActionDescription) > 0) then
      AuditActionDelete := qFormCreate.FieldByName('id').AsString
    else if (Pos('INSERT', ActionDescription) > 0) then
      AuditActionInsert := qFormCreate.FieldByName('id').AsString;
    qFormCreate.Next;
  end;
  qFormCreate.Close;
  qFormCreate.Free;
  lAudit := TestForAudit('tBatchType');
end;  // FormCreate.

procedure TSysEditServiceKpiForm.SetAppend(Value: boolean);
begin
  lAppend := Value;
  if lAppend then begin
    Caption := Caption + ' (ADD)';
    edtID.Text := '';
  end  // if lAppend then begin.
  else begin
    Caption := Caption + ' (EDIT)';
  end;  // if lAppend then begin..else.
end;  // SetAppend.

procedure TSysEditServiceKpiForm.SetID(Value: string);
begin
  if not lAppend then edtID.Text := Value;
end;  // SetID.

procedure TSysEditServiceKpiForm.SetKpiNumber(const Value: string);
begin
  Original_KpiNumber := Trim(Value);
  edtKpiNumber.Text := Original_KpiNumber;
end;  // SetKpiNumber.

function TSysEditServiceKpiForm.GetKpiNumber: string;
begin
  Result := Trim(edtKpiNumber.Text);
end;  // GetKpiNumber.

procedure TSysEditServiceKpiForm.SetDescription(const Value: string);
begin
  Original_Description := Trim(Value);
  edtDescription.Text := Original_Description;
end;  // SetDescription.

function TSysEditServiceKpiForm.GetDescription: string;
begin
  Result := Trim(edtDescription.Text);
end;  // GetDescription.

procedure TSysEditServiceKpiForm.SetResponseHours(const Value: string);
begin
  Original_ResponseHours := Trim(Value);
  edtResponseHours.Text := Original_ResponseHours;
end;  // SetResponseHours.

function TSysEditServiceKpiForm.GetResponseHours: string;
begin
  Result := Trim(edtResponseHours.Text);
end;  // GetResponseHours.

procedure TSysEditServiceKpiForm.SetRestorationHours(const Value: string);
begin
  Original_RestorationHours := Trim(Value);
  edtRestorationHours.Text := Original_RestorationHours;
end;  // SetRestorationHours.

function TSysEditServiceKpiForm.GetRestorationHours: string;
begin
  Result := Trim(edtRestorationHours.Text);
end;  // GetRestorationHours.

procedure TSysEditServiceKpiForm.SetAvailability(const Value: string);
begin
  Original_Availability := Trim(Value);
  edtAvailability.Text := Original_Availability;
end;  // SetAvailability.

function TSysEditServiceKpiForm.GetAvailability: string;
begin
  Result := Trim(edtAvailability.Text);
end;  // GetAvailability.

procedure TSysEditServiceKpiForm.SetCoreHours(const Value: string);
begin
  Original_CoreHours := Trim(Value);
  edtCoreHours.Text := Original_CoreHours;
end;  // SetCoreHours.

function TSysEditServiceKpiForm.GetCoreHours: string;
begin
  Result := Trim(edtCoreHours.Text);
end;  // GetCoreHours.

procedure TSysEditServiceKpiForm.SetAfterHours(const Value: string);
begin
  Original_AfterHours := Trim(Value);
  edtAfterHours.Text := Original_AfterHours;
end;  // SetAfterHours.

function TSysEditServiceKpiForm.GetAfterHours: string;
begin
  Result := Trim(edtAfterHours.Text);
end;  // GetAfterHours.

procedure TSysEditServiceKpiForm.SetTravelTime(const Value: string);
begin
  Original_TravelTime := Trim(Value);
  edtTravelTime.Text := Original_TravelTime;
end;  // SetTravelTime.

function TSysEditServiceKpiForm.GetTravelTime: string;
begin
  Result := Trim(edtTravelTime.Text);
end;  // GetTravelTime.

procedure TSysEditServiceKpiForm.SetFaultDetect(const Value: string);
begin
  Original_FaultDetect := Trim(Value);
  edtFaultDetect.Text := Original_FaultDetect;
end;  // SetFaultDetect.

function TSysEditServiceKpiForm.GetFaultDetect: string;
begin
  Result := Trim(edtFaultDetect.Text);
end;  // GetFaultDetect.

procedure TSysEditServiceKpiForm.btnOkClick(Sender: TObject);
var
  qKpi : TQuery;
  AmendString : TStringList;
  AuditString : TStringlist;
  sField : string;
  sValue : string;
  sTempValue : string;
  sAuditInsert : string;
  sAuditField : string;
  sAuditValue : string;
  AuditAction : string;
  sOperatorName: string;
  slFieldList : TStringList;
  slValueList : TStringList;
  nCount : integer;
  nMaxCount : integer;
  sStarted : string;
  lContinue : boolean;
begin
  qKpi := TQuery.Create(SELF);
  qKpi.DatabaseName := 'dbPPdata';
  if lAppend then AuditAction := AuditActionInsert
  else            AuditAction := AuditActionUpdate;
  sOperatorName := MainForm.OPERATORNAME;
  AmendString := TStringList.Create;
  AuditString := TStringList.Create;
  if lAppend then begin
    slFieldList := TStringList.Create;
    slValueList := TStringList.Create;
    sStarted := Trim(Copy(KpiNumber,1,LEN_COMMON));
    lContinue := not (Length(sStarted) = 0);
    if lContinue then begin
      with qKpi.SQL do begin
        Add('SELECT KpiNumber');
        Add('FROM tServiceKpi');
        Add('WHERE KpiNumber LIKE '''+sStarted+'''');
      end;
      qKpi.Open;
      lContinue := qKpi.Eof;
      qKpi.Close;
    end;  // lContinue.
    if not lContinue then begin
      MessageDlg('The KPInumber must be unique and Not empty.',mtWarning,[mbOk],0);
    end
      else begin
      if lAudit then begin
        SetAuditStart(sUserName, AuditAction, 'tServiceKpi',
          sAuditInsert, sAuditField, sAuditValue);
      end;  // lAudit.
      AmendString.Add('INSERT INTO tServiceKpi');
      sField := '(KpiNumber, Active, Description, ResponseHours, RestorationHours,';
      if (Length(sStarted) = 0) then sValue := 'NULL, 1,'
      else begin
        sValue := ''''+sStarted+''',1,';
        if lAudit then begin
          SetAuditData('KPINUMBER', '', sStarted, AuditString);
          SetAuditData('ACTIVE', '', '1', AuditString);
        end;
      end;
      sStarted := Copy(Description,1,LEN_DESCRIPTION);
      if (Length(sStarted) = 0) then sValue := sValue + 'NULL,'
      else begin
        sValue := sValue +''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('DESCRIPTION', '', sStarted, AuditString);
        end;
      end;
      sStarted := Copy(ResponseHours,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue + 'NULL,'
      else begin
        sValue := sValue +''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('RESONSEHOURS', '', sStarted, AuditString);
        end;
      end;
      sStarted := Copy(RestorationHours,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue + 'NULL,'
      else begin
        sValue := sValue +''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('RESTORATIONHOURS', '', sStarted, AuditString);
        end;
      end;
      slFieldList.Add(sField);
      slValueList.Add('VALUES ('+ sValue);
      sField := '';
      sValue := '';

      sField := 'Availability, CoreHours, AfterHours, TravelTime, FaultDetect)';
      sStarted := Copy(Availability,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue +'NULL,'
      else begin
        sValue := sValue + ''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('AVAILABILITY', '', sStarted, AuditString);
        end;
      end;
      sStarted := Copy(CoreHours,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue +'NULL,'
      else begin
        sValue := sValue + ''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('COREHOURS', '', sStarted, AuditString);
        end;
      end;
      sStarted := Copy(AfterHours,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue +'NULL,'
      else begin
        sValue := sValue + ''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('AFTERHOURS', '', sStarted, AuditString);
        end;
      end;
      sStarted := Copy(TravelTime,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue +'NULL,'
      else begin
        sValue := sValue + ''''+sStarted+''',';
        if lAudit then begin
          SetAuditData('TRAVELTIME', '', sStarted, AuditString);
        end;
      end;  // TravelTime.
      sStarted := Copy(FaultDetect,1,LEN_COMMON);
      if (Length(sStarted) = 0) then sValue := sValue +'NULL)'
      else begin
        sValue := sValue + ''''+sStarted+''')';
        if lAudit then begin
          SetAuditData('FAULTDETECT', '', sStarted, AuditString);
        end;
      end;  // FaultDetect.
      slFieldList.Add(sField);
      slValueList.Add(sValue);

      // Now compose the full SQL statement.
      nMaxCount := slFieldList.Count - 1;
      for nCount := 0 to nMaxCount do AmendString.Add(slFieldList[nCount]);
      for nCount := 0 to nMaxCount do AmendString.Add(slValueList[nCount]);
    end;  // lContinue.
  end  // if lAppend then begin.
  // *****************************************************************
  // ********************** UPDATE ***********************************
  // *****************************************************************
  else begin
    sStarted := Trim(Copy(KpiNumber,1,LEN_COMMON));
    lContinue := not (Length(sStarted) = 0);
    if lContinue then begin
      with qKpi.SQL do begin
        Add('SELECT KpiNumber');
        Add('FROM tServiceKpi');
        Add('WHERE KpiNumber LIKE '''+sStarted+'''');
        Add('AND ID <> '+edtID.Text);
      end;
      qKpi.Open;
      lContinue := qKpi.Eof;
      qKpi.Close;
    end;  // lContinue.
    if not lContinue then begin
      MessageDlg('The KPInumber must be unique and Not empty.',mtWarning,[mbOk],0);
    end
    else begin
      AmendString.Add('UPDATE tServiceKpi');
      sTempValue := Copy(KpiNumber,1,LEN_COMMON);
      if lAudit then begin
        SetAuditStart(sUserName, AuditAction, 'tServiceKpi',
          sAuditInsert, sAuditField, sAuditValue);
        SetAuditID(Trim(edtID.Text), sAuditValue);
        if (sTempValue <> Original_KpiNumber) then begin
          SetAuditData('KPINUMBER', Original_KpiNumber, sTempValue, AuditString);
        end;
      end;  // lAudit.
      if (Length(sTempValue)=0) then sTempValue := 'NULL,'
      else                           sTempValue := ''''+sTempValue+''',';
      sValue := 'SET KpiNumber = '+ sTempValue;
      sTempValue := Copy(Description,1,LEN_DESCRIPTION);
      if (sTempValue <> Original_Description) then begin
        if lAudit then begin
          SetAuditData('DESCRIPTION', Original_Description, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' Description = '+ sTempValue;
      end;
      sTempValue := Copy(ResponseHours,1,LEN_COMMON);
      if (sTempValue <> Original_ResponseHours) then begin
        if lAudit then begin
          SetAuditData('RESPONSEHOURS', Original_ResponseHours, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' ResponseHours = '+ sTempValue;
      end;
      AmendString.Add(sValue);
      sValue := '';

      sTempValue := Copy(RestorationHours,1,LEN_COMMON);
      if (sTempValue <> Original_RestorationHours) then begin
        if lAudit then begin
          SetAuditData('RESTORATIONHOURS', Original_RestorationHours, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' RestorationHours = '+ sTempValue;
      end;
      sTempValue := Copy(Availability,1,LEN_COMMON);
      if (sTempValue <> Original_Availability) then begin
        if lAudit then begin
          SetAuditData('AVAILABILITY', Original_Availability, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' Availability = '+ sTempValue;
      end;
      sTempValue := Copy(CoreHours,1,LEN_COMMON);
      if (sTempValue <> Original_CoreHours) then begin
        if lAudit then begin
          SetAuditData('COREHOURS', Original_CoreHours, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' CoreHours = '+ sTempValue;
      end;
      if Length(sValue) > 0 then begin
        AmendString.Add(sValue);
        sValue := '';
      end;

      sTempValue := Copy(AfterHours,1,LEN_COMMON);
      if (sTempValue <> Original_AfterHours) then begin
        if lAudit then begin
          SetAuditData('AFTERHOURS', Original_AfterHours, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' AfterHours = '+ sTempValue;
      end;
      sTempValue := Copy(TravelTime,1,LEN_COMMON);
      if (sTempValue <> Original_TravelTime) then begin
        if lAudit then begin
          SetAuditData('TRAVELTIME', Original_TravelTime, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' TravelTime = '+ sTempValue;
      end;
      sTempValue := Copy(FaultDetect,1,LEN_COMMON);
      if (sTempValue <> Original_FaultDetect) then begin
        if lAudit then begin
          SetAuditData('FAULTDETECT', Original_FaultDetect, sTempValue, AuditString);
        end;
        if (Length(sTempValue)=0) then sTempValue := 'NULL,'
        else                           sTempValue := ''''+sTempValue+''',';
        sValue := sValue + ' FaultDetect = '+ sTempValue;
      end;
      sTempValue := Copy(ResponseHours,1,LEN_COMMON);
      // Note: Active is set elsewhere in DeleteRecord().
      if Length(sValue) > 0 then begin
        AmendString.Add(sValue);
        sValue := '';
      end;

      // Done - Now remove the last trailing comma.
      with AmendString do begin
        sStarted := Strings[Count-1];
        Strings[Count-1] := Copy(sStarted,1,Length(sStarted)-1);
      end;
      // Complete the SQL statement.
      AmendString.Add('WHERE id = '+ Trim(edtID.Text));
    end;  // lContinue.
  end;  // if lAppend then begin..else.
  if lContinue then begin
    qKpi.Close;
    qKpi.SQL.Text := AmendString.Text;
    qKpi.ExecSQL;
    if lAudit then begin
      if lAppend then begin
        // Get and store the new ID for this table.
        with qKpi do begin
          SQL.Clear;
          SQL.Add('SELECT ID');
          SQL.ADD('FROM tServiceKpi');
          SQL.Add('WHERE KpiNumber LIKE '''+ KpiNumber +'''');
          SQL.Add('AND Description LIKE '''+ Description +'''');
          SQL.Add('AND Active = 1');
          Open;
          SetAuditID(FieldByName('ID').AsString, sAuditValue);
          Close;
        end;  // qKpi.
      end;  // lAppend.
      SaveAudit(sAuditInsert, sAuditField, sAuditValue, AuditString);
    end;  // lAudit.
  end;  // lContinue.
  AmendString.Free;
  AuditString.Free;
  qKpi.Free;
  if lContinue then begin
    ModalResult := mrOk;
  end;
end;  // btnOkClick.

end.