//------------------------------------------------------------------------------
//Procedure : DataBaseUpdate
//Author : Richard
//Date : 20/05/2024
//Purpose : To read data from the -.DA.dat file and upload the data
// : to the PvUpload database
// : fDate in format yyyy-mm-dd is used for file operations only
// : sDate, derived from fDate in format yyyy/mm/dd is used for date comparisons
//------------------------------------------------------------------------------
Procedure UpDateDataBase(fDate: String; VAR iDup, iUpd, iZero: Int32; sComplete, sSysName, sModel, sSysDate:String);
Var
i: Integer; //Work var
fPath: String; //For datafile access
fNo: Text;
strLine: String;
arData: TStringArray; //For reading and splitting data line
arDataLen: Int32;
iNewRecs: Int32; //Record counts
iDupRecs: Int32;
iZeroRecs: Int32;
sSystemName: String; //Returned by ReadInFile
sSystemDate: String;
sColNames: String; //returned by ReadUnFile
sUnits: String;
sTimeOn: String; //Returned by ReadSyFile
sTimeOff: String;
sEnergyToday:Single;
sEnergyLife: Single;
arInp: ar19; //An array ofelements used for writing a new Time record
sUnitId: Int32;
sDayId: Int32; //Work var
sDate: String; //Date in format yyyy/mm/dd
iInvId: Int32; //Returned InverterId
Complete: String; //Day's data completed status
sTime: String; //Work var
InverterID: Int32; //InverterID returned by GetInverterId
DayId: Int32; //Work var
UpLoaded: String; //Day uploaded status
sMPPT: String; //Work var
theLine: String; //Debug, holds a line indicator
Begin
Try
//Need to get Model & MPPT now so we can call ImnverterID, required for GetDayID
//So open -DA.dat file, get it and close
theLine:='AA';
GetModelMPPT(fDate); //Gets gSys. Model & MPPT
//Extract relevant information from temp files -In, -Un, -Sy
//First Information file -In
//ReadInFile(fDate, sSystemName, sSystemDate); //Gets gSys. SystemName & SystemDate
ReadInFile(fDate); //Gets gSys. SystemName & SystemDate
//Second Units file -Un
ReadUnFile(fDate, sColNames, sUnits); //Gets gSys ColNames & ColUnits
//Third System file -Sy
//Gets gSys. SysTimeOn & SysTimeOff & EnergyToday & EnergyLife
//ReadSyFile(fDate, sTimeOn, sTimeOff, sEnergyToday, sEnergyLife);
ReadSyFile(fDate);
//But we also do need ColNames & ColUnits for GetInverterID later in code
//sUnitId:=GetUnitId(sColNames, sUnits); //Gets gSys ColNames & ColUnits
sUnitId:=GetUnitId; //Gets gSys ColNames & ColUnits
//And InverterID for call to GetDayId
//Because it comes from reading the -DA.dat file
//InverterID:=GetInverterId(gSys.Model, sSystemName, gSys.MPPT, sColNames, sUnits);
InverterID:=GetInverterId;
//And get DayId for database inserts
theLine:='BB';
sDayId:=GetDayId(fDate, gSys.SysTimeOn, gSys.SysTimeOff, gSys.EnergyToday, gSys.EnergyLife, InverterID);
//sDayId:=GetDayId(fDate, sTimeOn, sTimeOff, sEnergyToday, sEnergyLife, 99); //iInvId not known yet
//Now loop through all data lines in -DA.dat file and process
iDupRecs:=0;
iNewRecs:=0;
iZeroRecs:=0;
//Assign & open data file
fPath:=g.TempPath + '/' + fDate + '-DA.dat';
Assign(fNo, fPath);
Reset(fNo);
While Not Eof(fNo) Do
Begin
ReadLn(fNo, strLine);
//Turn this string into a record of time, need new function
//08:02;2;PVI-3.6-OUTD-UK;D;131.4;0.0;0.0;92.8;0.0;0.0;238.8;0.8;0.0;18.9;17.8;0.0;14.1;0;0;
//Ignore trailing ';' thus 19 elements running 0 to 18 in arData[i]
If RightStr(strLine, 1) = ';' Then strLine:= MidStr(strLine,1, Length(strLine)-1);
arData:=strLine.Split([';']);
arDataLen:=length(arData); //TSTringArray from -DA.dat file
//Create array[1..19] of string for input values
For i:= 0 to arDataLen - 1 Do //Remember length is number of elements, they start at zero
Begin
arInp[i+1]:= arData[i];
End;{For}
//WriteLn(strLine);
If Not IsRecAllZero(arInp) Then
//Ensure that record is not all zero's
Begin
//Use this record
sTime:= arInp[1]; //arInp[1] = time
//Does time exist in table times
If DoesTimeExist( sDayId, sTime) = False Then
Begin
//Ok to insert new record in table Times but first
//We need InverterId for SQL insert plus items returned by GetInverterID
sMPPT:=arInp[4];
sModel:= arInp[3];
DayId:=sDayId; //Is returned by function call DoesTimeExist
UpLoaded:='N'; //As this is a new record, always = 'N'
//InverterID:=GetInverterId(sModel, sSystemName, sMPPT, sColNames, sUnits);
//is this necessary InverterID:=GetInverterId;
dbTrans.Active:=True;
theLine:='CC';
dbQuery.SQL.Text:='INSERT INTO TIMES (DayID, Time, Uploaded, Model, Address, MPPT, ' +
'VDC1, IDC1, PDC1, VDC2, IDC2, PDC2, VAC, ' +
'IAC, PAC, TINV, TINT, Energy, RISO, ILEAK, GenFreq) ' +
'VALUES (' + IntToStr(DayID) + ', ' + //DayId
QuotedStr(arInp[1]) + ', ' + //Time
QuotedStr(Uploaded) + ', ' + //Uploaded
IntToStr(InverterID) + ', ' + //Model
arInp[2] + ', ' + //Address
QuotedStr(arInp[4]) + ', ' + //MPPT
arInp[5] + ', ' + //VCD1
arInp[6] + ', ' + //IDC1
arInp[7] + ', ' + //PDC1
arInp[8] + ', ' + //VDC2
arInp[9] + ', ' + //IDC2
arInp[10] + ', ' + //PDC2
arInp[11] + ', ' + //VAC
arInp[12] + ', ' + //IAC
arInp[13] + ', ' + //PAC
arInp[14] + ', ' + //TINV
arInp[15] + ', ' + //TINT
arInp[16] + ', ' + //Energy
arInp[17] + ', ' + //RISO
arInp[18] + ', ' + //ILEAK
arInp[19] + ');'; //GENFREQ
dbQuery.ExecSQL;
dbTrans.Commit;
dbQuery.Free;
dbTrans.Free;
Inc(iNewRecs);
//WriteLn('Insert ' + IntToStr(iNewRecs));
End
Else
Begin
//Time already exists
Inc(IdupRecs);
//WriteLn('Duplicate time ignore : ' + arInp[1]);
End;{If DoesTimeExist}
End
Else
Begin
//A zero record, ignore
Inc(iZeroRecs);
End;{If IsRecAllZero}
End;{While}
If iNewRecs >= 0 Then iUpd:=iNewRecs;
If iDupRecs >= 0 Then iDup:=iDupRecs;
If iZeroRecs >= 0 Then iZero:=iZeroRecs;
Close(fNo);
Except
On E: EDataBaseError Do
Begin
//Needs Db in Uses statement
WriteLn(theLine + '-- ' + 'Raised Exception: ' + E.ClassName + ' With message: ' + E.Message);
End;
End;{Try}
End;{Procedure UpDateDataBase}
Recent
