1 (* 2 *************************************************************************** 3 * * 4 * This program is free software; you can redistribute it and/or modify * 5 * it under the terms of the GNU General Public License as published by * 6 * the Free Software Foundation; either version 2 of the License. * 7 * * 8 *************************************************************************** 9 *) 10 11 12 unit fImportProgress; 13 14 {$mode objfpc}{$H+} 15 16 interface 17 18 uses 19 Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs, StdCtrls, 20 ComCtrls,lcltype, synachar, ExtCtrls, httpsend, blcksock, iniFiles, FileUtil, 21 LazFileUtils; 22 23 type 24 TImportProgressType = (imptRegenerateDXCC, imptImportDXCCTables, imptDownloadDXCCData, imptImportLoTWAdif, 25 imptImportQSLMgrs, imptDownloadQSLData, imptInsertQSLManagers, imptImporteQSLAdif, 26 imptRemoveDupes, imptUpdateMembershipFiles, imptDownloadDOKData); 27 28 type 29 30 { TfrmImportProgress } 31 32 TfrmImportProgress = class(TForm) 33 lblCount: TLabel; 34 lblErrors: TLabel; 35 lblComment: TLabel; 36 pBarProg: TProgressBar; 37 tmrImport: TTimer; 38 procedure FormActivate(Sender: TObject); 39 procedure FormCreate(Sender: TObject); 40 procedure FormDestroy(Sender: TObject); 41 procedure FormShow(Sender: TObject); 42 procedure tmrImportTimer(Sender: TObject); 43 private 44 running : Boolean; 45 FileSize : Int64; 46 procedure ImportDXCCTables; 47 procedure RegenerateDXCCStat; 48 procedure DownloadDXCCData; 49 procedure DownloadDOKData; 50 procedure ImportLoTWAdif; 51 procedure ImportQSLMgrs; 52 procedure DownloadQSLData; 53 procedure InsertQSLManagers; 54 procedure ImporteQSLAdif; 55 procedure RemoveDupes; 56 procedure UpdateMembershipFiles; 57 58 procedure SockCallBack (Sender: TObject; Reason: THookSocketReason; const Value: string); 59 60 public 61 ImportType : TImportProgressType; 62 FileName : String; 63 64 Directory : String; 65 CloseAfImport : Boolean; 66 LoTWShowNew : Boolean; 67 LoTWQSOList : TStringList; 68 eQSLShowNew : Boolean; 69 eQSLQSOList : TStringList; 70 71 end; 72 73 var 74 frmImportProgress: TfrmImportProgress; 75 76 implementation 77 {$R *.lfm} 78 { TfrmImportProgress } 79 80 uses dData, dUtils, fImportTest, dDXCC, uMyini, dLogUpload, dMembership, dSatellite; 81 82 procedure TfrmImportProgress.FormActivate(Sender: TObject); 83 begin 84 tmrImport.Enabled := False; 85 if not running then 86 begin 87 running := True; 88 case ImportType of 89 imptRegenerateDXCC : RegenerateDXCCStat; 90 imptImportDXCCTables : ImportDXCCTables; 91 imptDownloadDXCCData : DownloadDXCCData; 92 imptDownloadDOKData : DownloadDOKData; 93 imptImportLoTWAdif : ImportLoTWAdif; 94 imptImportQSLMgrs : ImportQSLMgrs; 95 imptDownloadQSLData : DownloadQSLData; 96 imptInsertQSLManagers : InsertQSLManagers; 97 imptImporteQSLAdif : ImporteQSLAdif; 98 imptRemoveDupes : RemoveDupes; 99 imptUpdateMembershipFiles : UpdateMembershipFiles 100 end // case 101 end 102 end; 103 104 procedure TfrmImportProgress.FormCreate(Sender: TObject); 105 begin 106 CloseAfImport := False; 107 FileSize := 0; 108 LoTWQSOList := TStringList.Create; 109 LoTWQSOList.Clear; 110 eQSLQSOList := TStringList.Create; 111 eQSLQSOList.Clear 112 end; 113 114 procedure TfrmImportProgress.FormDestroy(Sender: TObject); 115 begin 116 LoTWQSOList.Free; 117 eQSLQSOList.Free 118 end; 119 120 procedure TfrmImportProgress.FormShow(Sender: TObject); 121 begin 122 running := False; 123 dmUtils.LoadFontSettings(self); 124 tmrImport.Enabled := True 125 end; 126 127 procedure TfrmImportProgress.tmrImportTimer(Sender: TObject); 128 begin 129 FormActivate(nil) 130 end; 131 132 procedure TfrmImportProgress.ImportDXCCTables; 133 var 134 f : TStringList; 135 i,z,y,c : Integer; 136 Result : TExplodeArray; 137 Prefixes : TExplodeArray; 138 ADIF : Integer; 139 List : TStringList; 140 tmp : String; 141 begin 142 SetLength(Prefixes,0); 143 SetLength(Result,0); 144 f := TStringList.Create; 145 List := TStringList.Create; 146 List.Clear; 147 dmDXCC.qDXCCRef.Close; 148 if dmDXCC.trDXCCRef.Active then 149 dmDXCC.trDXCCRef.Rollback; 150 151 dmDXCC.trDXCCRef.StartTransaction; 152 dmDXCC.qDXCCRef.SQL.Text := 'DELETE FROM cqrlog_common.dxcc_ref'; 153 dmDXCC.qDXCCRef.ExecSQL; 154 dmDXCC.trDXCCRef.Commit; 155 c := 0; 156 try 157 /////////////////////////////////////////////////////////////////////////// country.tab 158 dmDXCC.trDXCCRef.StartTransaction; 159 f.Clear; 160 lblComment.Caption := 'Importing file country.tab ...'; 161 Application.ProcessMessages; 162 f.LoadFromFile(Directory+'Country.tab'); 163 164 for z:=0 to f.Count-1 do 165 begin 166 inc(c); 167 Result := dmUtils.Explode('|',f.Strings[z]); 168 Prefixes := dmUtils.Explode(' ',Result[0]); 169 ADIF := StrToInt(Result[8]); 170 if ADIF > 0 then 171 begin 172 dmDXCC.qDXCCRef.SQL.Text := 'INSERT INTO cqrlog_common.dxcc_ref (pref,name,cont,utc,lat,'+ 173 'longit,itu,waz,adif,deleted) VALUES ('+ 174 QuotedStr(Prefixes[0])+','+ QuotedStr(Result[1])+','+ 175 QuotedStr(Result[2])+','+QuotedStr(Result[3])+','+ 176 QuotedStr(Result[4])+','+QuotedStr(Result[5])+','+ 177 QuotedStr(Result[6])+','+QuotedStr(Result[7])+','+ 178 IntToStr(ADIF)+',0)'; 179 if dmData.DebugLevel >=1 then Writeln(dmDXCC.qDXCCRef.SQL.Text); 180 dmDXCC.qDXCCRef.ExecSQL; 181 end; 182 end; 183 List.AddStrings(f); 184 dmDXCC.trDXCCRef.Commit; 185 ////////////////////////////////////////////////////////////// countrydel.tab 186 dmDXCC.trDXCCRef.StartTransaction; 187 f.Clear; 188 lblComment.Caption := 'Importing file countrydel.tab ...'; 189 Application.ProcessMessages; 190 f.LoadFromFile(Directory+'CountryDel.tab'); 191 for z:=0 to f.Count-1 do 192 begin 193 Result := dmUtils.Explode('|',f.Strings[z]); 194 Prefixes := dmUtils.Explode(' ',Result[0]); 195 ADIF := StrToInt(Result[8]); 196 if ADIF > 0 then 197 begin 198 dmDXCC.qDXCCRef.SQL.Text := 'INSERT INTO cqrlog_common.dxcc_ref (pref,name,cont,utc,lat,'+ 199 'longit,itu,waz,adif,deleted) VALUES ('+ 200 QuotedStr(Prefixes[0]+'*')+','+ QuotedStr(Result[1])+','+ 201 QuotedStr(Result[2])+','+QuotedStr(Result[3])+','+ 202 QuotedStr(Result[4])+','+QuotedStr(Result[5])+','+ 203 QuotedStr(Result[6])+','+QuotedStr(Result[7])+','+ 204 IntToStr(ADIF)+','+'1'+')'; 205 if dmData.DebugLevel >=1 then 206 Writeln(dmDXCC.qDXCCRef.SQL.Text); 207 dmDXCC.qDXCCRef.ExecSQL; 208 end; 209 end; 210 dmDXCC.trDXCCRef.Commit; 211 f.SaveToFile(dmData.HomeDir+'dxcc_data'+PathDelim+'country_del.tab'); 212 213 /////////////////////////////////////////////////////////////////// exceptions.tab 214 CopyFile(Directory+'Exceptions.tab',dmData.HomeDir+'dxcc_data'+PathDelim+'exceptions.tab'); 215 216 ////////////////////////////////////////////////////////////////// callresolution.tbl 217 f.Clear; 218 lblComment.Caption := 'Importing file Callresolution.tbl ...'; 219 Application.ProcessMessages; 220 f.LoadFromFile(Directory+'CallResolution.tbl'); 221 List.AddStrings(f); 222 ////////////////////////////////////////////////////////////////// AreaOK1RR.tab 223 224 f.Clear; 225 f.LoadFromFile(Directory+'AreaOK1RR.tbl'); 226 List.AddStrings(f); 227 228 for y:=0 to List.Count-1 do 229 begin 230 if List.Strings[y][1] = '%' then 231 begin 232 for i:=65 to 90 do 233 list.Add(chr(i)+copy(list.Strings[y],2,Length(list.Strings[y])-1)); 234 end; 235 end; 236 237 List.SaveToFile(dmData.HomeDir+'dxcc_data'+PathDelim+'country.tab'); 238 239 //////////////////////////////////////////////////////////// ambigous.tbl; 240 CopyFile(Directory+'Ambiguous.tbl',dmData.HomeDir+'dxcc_data'+PathDelim+'ambiguous.tab'); 241 242 lblComment.Caption := 'Importing LoTW and eQSL users ...'; 243 Application.ProcessMessages; 244 245 if FileExistsUTF8(Directory+'lotw1.txt') then 246 begin 247 DeleteFileUTF8(dmData.HomeDir+'lotw1.txt'); 248 CopyFile(Directory+'lotw1.txt',dmData.HomeDir+'lotw1.txt'); 249 dmData.LoadLoTWCalls 250 end; 251 252 if FileExistsUTF8(Directory+'eqsl.txt') then 253 begin 254 DeleteFileUTF8(dmData.HomeDir+'eqsl.txt'); 255 CopyFile(Directory+'eqsl.txt',dmData.HomeDir+'eqsl.txt'); 256 dmData.LoadeQSLCalls 257 end; 258 259 lblComment.Caption := 'Loading MASTER.SCP ...'; 260 Application.ProcessMessages; 261 if FileExistsUTF8(Directory+'MASTER.SCP') then 262 begin 263 DeleteFileUTF8(dmData.HomeDir+'MASTER.SCP'); 264 CopyFile(Directory+'MASTER.SCP',dmData.HomeDir+'MASTER.SCP'); 265 dmData.LoadMasterSCP 266 end; 267 268 if FileExistsUTF8(Directory+'us_states.tab') then 269 begin 270 DeleteFileUTF8(dmData.HomeDir+'dxcc_data'+PathDelim+'us_states.tab'); 271 CopyFile(Directory+'us_states.tab',dmData.HomeDir+'dxcc_data'+PathDelim+'us_states.tab') 272 //reloading is in dmDXCC.ReloadDXCCTables 273 end; 274 275 if FileExistsUTF8(Directory + C_SATELLITE_LIST) then 276 begin 277 DeleteFileUTF8(dmData.HomeDir + C_SATELLITE_LIST); 278 CopyFile(Directory + C_SATELLITE_LIST, dmData.HomeDir + C_SATELLITE_LIST); 279 dmSatellite.LoadSatellitesFromFile 280 end; 281 282 if FileExistsUTF8(Directory + C_PROP_MODE_LIST) then 283 begin 284 DeleteFileUTF8(dmData.HomeDir + C_PROP_MODE_LIST); 285 CopyFile(Directory + C_PROP_MODE_LIST, dmData.HomeDir + C_PROP_MODE_LIST); 286 dmSatellite.LoadPropModesFromFile 287 end; 288 289 if FileExistsUTF8(Directory + 'ContestName.tab') then 290 begin 291 DeleteFileUTF8(dmData.HomeDir + 'ContestName.tab'); 292 CopyFile(Directory + 'ContestName.tab', dmData.HomeDir + 'ContestName.tab'); 293 end; 294 295 lblComment.Caption := 'Importing IOTA table ...'; 296 Application.ProcessMessages; 297 dmData.qIOTAList.Close(); 298 dmData.qIOTAList.SQL.Text := 'DELETE FROM cqrlog_common.iota_list'; 299 dmData.trIOTAList.StartTransaction; 300 dmData.qIOTAList.ExecSQL; 301 dmData.trIOTAList.Commit; 302 303 f.Clear; 304 f.LoadFromFile(Directory + 'iota.tbl'); 305 dmData.trIOTAList.StartTransaction; 306 for i:= 0 to f.Count-1 do 307 begin 308 Result := dmUtils.Explode('|',f.Strings[i]); 309 if Length(Result) = 3 then 310 dmData.qIOTAList.SQL.Text := 'INSERT INTO cqrlog_common.iota_list (iota_nr,island_name,dxcc_ref)'+ 311 ' VALUES ('+QuotedStr(Result[0]) + ',' + 312 QuotedStr(Result[1]) + ',' + QuotedStr(Result[2]) + ')' 313 else begin 314 tmp := Result[3]; 315 if pos('/',tmp) > 0 then 316 tmp := Copy(tmp,1,pos('/',tmp)-1)+ '.*' + Copy(tmp,pos('/',tmp),Length(tmp)-pos('/',tmp)+1); 317 dmData.qIOTAList.SQL.Text := 'INSERT INTO cqrlog_common.iota_list (iota_nr,island_name,dxcc_ref,pref)'+ 318 ' VALUES ('+QuotedStr(Result[0]) + ',' + 319 QuotedStr(Result[1]) + ',' + QuotedStr(Result[2]) 320 + ',' + QuotedStr(tmp) + ')'; 321 end; 322 if dmData.DebugLevel>=1 then 323 Writeln(dmData.qIOTAList.SQL.Text); 324 325 if length(Result[1]) > 250 then ShowMessage(Result[0]); 326 if length(Result[2]) > 15 then ShowMessage(Result[0]); 327 if length(Result) > 3 then 328 if length(Result[3]) > 15 then ShowMessage(Result[0]); 329 dmData.qIOTAList.ExecSQL; 330 end; 331 dmData.trIOTAList.Commit; 332 333 finally 334 //dmDXCC.trDXCCRef.StartTransaction; 335 dmDXCC.qDXCCRef.SQL.Text := 'SELECT * FROM cqrlog_common.dxcc_ref ORDER BY adif'; 336 dmDXCC.qDXCCRef.Open; 337 f.Free; 338 List.Free; 339 Close 340 end 341 end; 342 343 procedure TfrmImportProgress.RegenerateDXCCStat; 344 var 345 i : Integer; 346 adif : Word; 347 old_adif : Word; 348 id : Integer; 349 waz : String; 350 itu : String; 351 cont : String; 352 tmp : String; 353 begin 354 lblComment.Caption := 'Rebuilding DXCC statistics ...'; 355 Caption := lblComment.Caption; 356 waz := ''; 357 itu := ''; 358 i := 0; 359 lblCount.Caption := '0'; 360 dmData.qCQRLOG.DisableControls; 361 try try 362 lblComment.Caption := 'Rebuilding DXCC statistics ...'; 363 Repaint; 364 365 if dmData.trQ.Active then dmData.trQ.RollBack; 366 dmData.Q.SQL.Text := 'SELECT COUNT(*) FROM cqrlog_main'; 367 dmData.trQ.StartTransaction; 368 dmData.Q.Open; 369 pBarProg.Max := dmData.Q.Fields[0].AsInteger; 370 dmData.Q.Close; 371 dmData.trQ.Rollback; 372 373 dmData.Q1.Close; 374 if dmData.trQ1.Active then dmData.trQ1.Rollback; 375 dmData.Q1.SQL.Text := 'select id_cqrlog_main,qsodate,callsign,adif,qso_dxcc from cqrlog_main'; 376 dmData.trQ1.StartTransaction; 377 dmData.Q1.Open; 378 dmData.Q1.First; 379 380 dmData.trQ.StartTransaction; 381 while not dmData.Q1.Eof do 382 begin 383 inc(i); 384 if dmData.Q1.Fields[4].AsInteger > 0 then 385 begin 386 dmData.Q1.Next; 387 pBarProg.StepIt; 388 Continue 389 end 390 else begin 391 old_adif := dmData.Q1.Fields[3].AsInteger; 392 id := dmData.qCQRLOG.Fields[0].AsInteger; 393 adif := dmDXCC.id_country(dmData.Q1.Fields[2].AsString, dmUtils.StrToDateFormat( 394 dmData.Q1.Fields[1].AsString), 395 tmp, cont, tmp, waz, tmp, itu, tmp, tmp); 396 if adif<>old_adif then 397 begin 398 cont := copy(cont,1,2); 399 dmUtils.ModifyWAZITU(waz,itu); 400 if adif = 0 then 401 dmData.Q.SQL.Text := 'UPDATE cqrlog_main SET adif=0,waz=null,itu=null,cont=null WHERE id_cqrlog_main='+IntToStr(id) 402 else 403 dmData.Q.SQL.Text := 'UPDATE cqrlog_main SET adif='+IntToStr(adif)+',waz ='+waz+',itu ='+itu+',cont='+QuotedStr(cont)+' WHERE id_cqrlog_main='+IntToStr(id); 404 dmData.Q.ExecSQL 405 end 406 end; 407 dmData.Q1.Next; 408 pBarProg.StepIt; 409 lblCount.Caption := IntToStr(i); 410 if (i mod 100 = 0) then 411 begin 412 Repaint; 413 Application.ProcessMessages 414 end 415 end 416 except 417 on E : Exception do 418 begin 419 Writeln('Exception: ',E.Message); 420 dmData.trQ.RollBack 421 end 422 end; 423 dmData.trQ.Commit 424 finally 425 dmData.Q1.Close; 426 dmData.trQ1.Rollback; 427 dmData.qCQRLOG.Close; 428 dmData.qCQRLOG.Open; 429 dmData.qCQRLOG.EnableControls 430 end; 431 Close 432 end; 433 434 procedure TfrmImportProgress.DownloadDXCCData; 435 var 436 HTTP : THTTPSend; 437 m : TFileStream; 438 begin 439 FileName := dmData.HomeDir+'ctyfiles/cqrlog-cty.tar.gz'; 440 if FileExists(FileName) then 441 DeleteFile(FileName); 442 http := THTTPSend.Create; 443 m := TFileStream.Create(FileName,fmCreate); 444 try 445 HTTP.Sock.OnStatus := @SockCallBack; 446 HTTP.ProxyHost := cqrini.ReadString('Program','Proxy',''); 447 HTTP.ProxyPort := cqrini.ReadString('Program','Port',''); 448 HTTP.UserName := cqrini.ReadString('Program','User',''); 449 HTTP.Password := cqrini.ReadString('Program','Passwd',''); 450 451 if HTTP.HTTPMethod('GET', 'http://www.ok2cqr.com/linux/cqrlog/ctyfiles/cqrlog-cty.tar.gz') then 452 begin 453 http.Document.Seek(0,soBeginning); 454 m.CopyFrom(http.Document,HTTP.Document.Size); 455 if dmUtils.UnTarFiles(FileName,ExtractFilePath(FileName)) then 456 begin 457 Directory := ExtractFilePath(FileName); 458 ImportDXCCTables 459 end; 460 end; 461 finally 462 http.Free; 463 m.Free; 464 end 465 end; 466 467 procedure TfrmImportProgress.DownloadDOKData; 468 var 469 HTTP : THTTPSend; 470 m : TFileStream; 471 begin 472 FileName := dmData.HomeDir+'dok_data/doks.tar.gz'; 473 if FileExists(FileName) then 474 DeleteFile(FileName); 475 http := THTTPSend.Create; 476 m := TFileStream.Create(FileName,fmCreate); 477 try 478 HTTP.Sock.OnStatus := @SockCallBack; 479 HTTP.ProxyHost := cqrini.ReadString('Program','Proxy',''); 480 HTTP.ProxyPort := cqrini.ReadString('Program','Port',''); 481 HTTP.UserName := cqrini.ReadString('Program','User',''); 482 HTTP.Password := cqrini.ReadString('Program','Passwd',''); 483 484 if HTTP.HTTPMethod('GET', 'https://www.df2et.de/cqrlog/doks.tar.gz') then 485 begin 486 http.Document.Seek(0,soBeginning); 487 m.CopyFrom(http.Document,HTTP.Document.Size); 488 if dmUtils.UnTarFiles(FileName,ExtractFilePath(FileName)) then 489 begin 490 Directory := ExtractFilePath(FileName); 491 end; 492 end; 493 finally 494 http.Free; 495 m.Free; 496 Close; 497 end 498 end; 499 500 procedure TfrmImportProgress.DownloadQSLData; 501 var 502 HTTP : THTTPSend; 503 m : TFileStream; 504 begin 505 FileName := dmData.HomeDir+'ctyfiles'+PathDelim+'qslmgr.tar.gz'; 506 if FileExists(FileName) then 507 DeleteFile(FileName); 508 http := THTTPSend.Create; 509 m := TFileStream.Create(FileName,fmCreate); 510 try 511 HTTP.Sock.OnStatus := @SockCallBack; 512 HTTP.ProxyHost := cqrini.ReadString('Program','Proxy',''); 513 HTTP.ProxyPort := cqrini.ReadString('Program','Port',''); 514 HTTP.UserName := cqrini.ReadString('Program','User',''); 515 HTTP.Password := cqrini.ReadString('Program','Passwd',''); 516 if HTTP.HTTPMethod('GET', 'http://www.ok2cqr.com/linux/cqrlog/qslmgr/qslmgr.tar.gz') then 517 begin 518 http.Document.Seek(0,soBeginning); 519 m.CopyFrom(http.Document,HTTP.Document.Size); 520 if dmUtils.UnTarFiles(FileName,ExtractFilePath(FileName)) then 521 begin 522 Directory := ExtractFilePath(FileName); 523 FileName := Directory + 'qslmgr.csv'; 524 ImportQSLMgrs 525 end; 526 end; 527 finally 528 http.Free; 529 m.Free; 530 end 531 end; 532 533 procedure TfrmImportProgress.SockCallBack (Sender: TObject; Reason: THookSocketReason; const Value: string); 534 begin 535 if Reason = HR_ReadCount then 536 begin 537 FileSize := FileSize + StrToInt(Value); 538 lblCount.Caption := IntToStr(FileSize); 539 Repaint; 540 Application.ProcessMessages; 541 end; 542 end; 543 544 545 procedure TfrmImportProgress.ImportLoTWAdif; 546 var 547 num : Word = 1; 548 qsln : Word = 0; 549 size : Word; 550 sSize : String; 551 a : String; 552 orig : String; 553 f : TextFile; 554 PosEOH : Word; 555 PosEOR : Word; 556 call : String; 557 band : String; 558 mode : String; 559 qsodate : String; 560 time_on : String; 561 qslr : String; 562 qslrdate : String; 563 cqz : String; 564 ituz : String; 565 iota : String; 566 grid : String; 567 state : String; 568 county : String; 569 PosCall : Word; 570 PosBand : Word; 571 PosMode : Word; 572 PosQsoDate : Word; 573 PosTime_on : Word; 574 PosQslr : Word; 575 PosQslrDate : Word; 576 PosCqz : Word; 577 PosItuz : Word; 578 PosIota : Word; 579 PosGrid : Word; 580 PosState : Word; 581 PosCounty : Word; 582 583 qso_in_log : Boolean = False; 584 ErrorCount : Word = 0; 585 l : TStringList; 586 t_lotw : TDateTime; 587 t_lotw_min,t_lotw_max : TDateTime; 588 t_log : TDateTime; 589 590 begin 591 if dmData.trQ.Active then 592 dmData.trQ.RollBack; 593 if dmData.trQ1.Active then 594 dmData.trQ1.RollBack; 595 596 597 l := TStringList.Create; 598 AssignFile(f,FileName); 599 try 600 if cqrini.ReadBool('OnlineLog','IgnoreLoTWeQSL',False) and dmLogUpload.LogUploadEnabled then 601 dmLogUpload.DisableOnlineLogSupport; 602 603 dmData.trQ1.StartTransaction; 604 dmData.trQ.StartTransaction; 605 Reset(f); 606 lblComment.Caption := 'Importing LoTW Adif file ...'; 607 pBarProg.Visible := False; 608 Repaint; 609 PosEOH := 0; 610 PosEOR := 0; 611 while (PosEOH = 0) and (not eof(f)) do //Skip header 612 begin 613 Readln(f, a); 614 a := UpperCase(a); 615 PosEOH := Pos('<EOH>', a); 616 end; 617 if PosEOH > 0 then //we have valid lotw adif output 618 begin 619 while not eof(f) do 620 begin 621 call := ''; 622 band := ''; 623 mode := ''; 624 qsodate := ''; 625 time_on := ''; 626 qslr := ''; 627 qslrdate := ''; 628 cqz := ''; 629 ituz := ''; 630 iota := ''; 631 grid := ''; 632 state := ''; 633 county := ''; 634 PosEOR := 0; 635 while not ((PosEOR > 0) or eof(f)) do 636 begin 637 qso_in_log := False; 638 Readln(f, a); 639 a := Trim(a); 640 orig := a; 641 a := UpperCase(a); 642 643 PosCall := Pos('<CALL:',a); 644 PosBand := Pos('<BAND:',a); 645 PosMode := Pos('<MODE:',a); 646 PosQsoDate := Pos('<QSO_DATE:',a); 647 PosTime_on := Pos('<TIME_ON:',a); 648 PosQslr := Pos('<QSL_RCVD:',a); 649 PosQslrDate := Pos('<QSLRDATE:',a); 650 PosCqz := Pos('<CQZ:',a); 651 PosItuz := Pos('<ITUZ:',a); 652 PosIota := Pos('<IOTA:',a); 653 PosGrid := Pos('<GRIDSQUARE:',a); 654 PosState := Pos('<STATE:',a); 655 PosCounty := Pos('<CNTY:',a); 656 PosEOR := Pos('<EOR>',a); 657 658 if PosCall > 0 then 659 begin 660 sSize := ''; 661 PosCall := PosCall + 6; 662 while not (a[PosCall] = '>') do 663 begin 664 sSize := sSize + a[PosCall]; 665 inc(PosCall) 666 end; 667 Size := StrToInt(sSize); 668 call := copy(orig,PosCall+1,Size) 669 end; 670 671 if PosBand > 0 then 672 begin 673 sSize := ''; 674 PosBand := PosBand + 6; 675 while not (a[PosBand] = '>') do 676 begin 677 sSize := sSize + a[PosBand]; 678 inc(PosBand) 679 end; 680 Size := StrToInt(sSize); 681 band := copy(orig,PosBand+1,Size) 682 end; 683 684 if PosMode > 0 then 685 begin 686 sSize := ''; 687 PosMode := PosMode + 6; 688 while not (a[PosMode] = '>') do 689 begin 690 sSize := sSize + a[PosMode]; 691 inc(PosMode) 692 end; 693 Size := StrToInt(sSize); 694 mode := copy(orig,PosMode+1,Size) 695 end; 696 697 if PosQsoDate > 0 then 698 begin 699 sSize := ''; 700 PosQsoDate := PosQsoDate + 10; 701 while not (a[PosQsoDate] = '>') do 702 begin 703 sSize := sSize + a[PosQsoDate]; 704 inc(PosQsoDate) 705 end; 706 Size := StrToInt(sSize); 707 qsodate := copy(orig,PosQsoDate+1,Size) 708 end; 709 710 if PosTime_on > 0 then 711 begin 712 sSize := ''; 713 PosTime_on := PosTime_on + 9; 714 while not (a[PosTime_on] = '>') do 715 begin 716 sSize := sSize + a[PosTime_on]; 717 inc(PosTime_on) 718 end; 719 Size := StrToInt(sSize); 720 time_on := copy(orig,PosTime_on+1,Size) 721 end; 722 723 724 if PosQslr > 0 then 725 begin 726 sSize := ''; 727 PosQslr := PosQslr + 10; 728 while not (a[PosQslr] = '>') do 729 begin 730 sSize := sSize + a[PosQslr]; 731 inc(PosQslr) 732 end; 733 Size := StrToInt(sSize); 734 qslr := copy(orig,PosQslr+1,Size) 735 end; 736 737 if PosQslrDate > 0 then 738 begin 739 sSize := ''; 740 PosQslrDate := PosQslrDate + 10; 741 while not (a[PosQslrDate] = '>') do 742 begin 743 sSize := sSize + a[PosQslrDate]; 744 inc(PosQslrDate) 745 end; 746 Size := StrToInt(sSize); 747 qslrdate := copy(orig,PosQslrDate+1,Size) 748 end; 749 750 if PosCqz > 0 then 751 begin 752 sSize := ''; 753 PosCqz := PosCqz + 5; 754 while not (a[PosCqz] = '>') do 755 begin 756 sSize := sSize + a[PosCqz]; 757 inc(PosCqz) 758 end; 759 Size := StrToInt(sSize); 760 cqz := copy(orig,PosCqz+1,Size) 761 end; 762 763 if PosItuz > 0 then 764 begin 765 sSize := ''; 766 PosItuz := PosItuz + 6; 767 while not (a[PosItuz] = '>') do 768 begin 769 sSize := sSize + a[PosItuz]; 770 inc(PosItuz) 771 end; 772 Size := StrToInt(sSize); 773 ituz := copy(orig,PosItuz+1,Size) 774 end; 775 776 if PosIota > 0 then 777 begin 778 sSize := ''; 779 PosIota := PosIota + 6; 780 while not (a[PosIota] = '>') do 781 begin 782 sSize := sSize + a[PosIota]; 783 inc(PosIota) 784 end; 785 Size := StrToInt(sSize); 786 iota := copy(orig,PosIota+1,Size) 787 end; 788 789 if PosGrid > 0 then 790 begin 791 sSize := ''; 792 PosGrid := PosGrid + 12; 793 while not (a[PosGrid] = '>') do 794 begin 795 sSize := sSize + a[PosGrid]; 796 inc(PosGrid) 797 end; 798 Size := StrToInt(sSize); 799 grid := copy(orig,PosGrid+1,Size) 800 end; 801 802 if PosState > 0 then 803 begin 804 sSize := ''; 805 PosState := PosState + 7; 806 while not (a[PosState] = '>') do 807 begin 808 sSize := sSize + a[PosState]; 809 inc(PosState) 810 end; 811 Size := StrToInt(sSize); 812 state := copy(orig,PosState+1,Size) 813 end; 814 815 if PosCounty > 0 then 816 begin 817 sSize := ''; 818 PosCounty := PosCounty + 6; 819 while not (a[PosCounty] = '>') do 820 begin 821 sSize := sSize + a[PosCounty]; 822 inc(PosCounty) 823 end; 824 Size := StrToInt(sSize); 825 county := copy(orig,PosCounty+1,Size) 826 end; 827 828 if PosEOR > 0 then 829 begin 830 //inc(qsln); 831 if dmData.DebugLevel >= 1 then 832 begin 833 // Writeln('Number: ',IntToStr(qsln)); 834 Writeln('Call: ',call); 835 Writeln('Band: ',band); 836 Writeln('Mode: ',mode); 837 Writeln('QSO_date: ',qsodate); 838 Writeln('Time_on: ',time_on); 839 Writeln('QSLR: ',qslr); 840 Writeln('QSLRDate: ',qslrdate); 841 Writeln('CQZ: ',cqz); 842 Writeln('ITUZ: ',ituz); 843 Writeln('IOTA: ',iota); 844 Writeln('Grid: ',grid); 845 Writeln('State: ',state); 846 Writeln('County: ',county); 847 Writeln('------------------------------------------------') 848 end; 849 band := dmUtils.GetBandFromFreq(dmUtils.FreqFromBand(band,'CW')); 850 qsodate := dmUtils.ADIFDateToDate(qsodate); 851 qslrdate := dmUtils.ADIFDateToDate(qslrdate); 852 853 mode := UpperCase(mode); 854 if mode='JT65' then 855 mode := 'JT65A'; 856 857 dmData.Q.Close; 858 dmData.Q.SQL.Text := 'select time_on,lotw_qslr,waz,itu,iota,loc,state,county,id_cqrlog_main from cqrlog_main ' + 859 'where (qsodate ='+QuotedStr(qsodate)+') '+ 860 'and (band = ' + QuotedStr(band) + ')'+ 861 // 'and (mode = ' + QuotedStr(mode) + ') and (band = ' + QuotedStr(band) + ')'+ 862 'and (callsign = ' + QuotedStr(call) + ')'; 863 if dmData.DebugLevel >=1 then Writeln(dmData.Q.SQL.Text); 864 //if dmData.trQ.Active then dmData.trQ.Rollback; 865 //dmData.trQ.StartTransaction; 866 dmData.Q.Open(); 867 dmData.Q.First; 868 if dmData.Q.Eof then qso_in_log := False; 869 while not dmData.Q.Eof do 870 begin 871 qso_in_log := False; 872 873 t_lotw := EncodeTime(StrToInt(copy(time_on,1,2)), 874 StrToInt(copy(time_on,3,2)),0,0); 875 876 t_log := EncodeTime(StrToInt(copy(dmData.Q.Fields[0].AsString,1,2)), 877 StrToInt(copy(dmData.Q.Fields[0].AsString,4,2)),0,0); 878 879 t_lotw_min := t_lotw-5/1440; 880 t_lotw_max := t_lotw+5/1440; 881 882 if dmData.DebugLevel >=1 then Writeln(call,'|',TimeToStr(t_log),' | ',TimeToStr(t_lotw_min),'|',TimeToStr(t_lotw_max)); 883 884 if (t_log >=t_lotw_min) and (t_log<=t_lotw_max) then 885 begin 886 if (dmData.Q.Fields[1].AsString <> 'L') then 887 begin 888 if LoTWShowNew then //this qso is already confirmed 889 LoTWQSOList.Add(qsodate+ ' ' + call + ' ' + band + ' ' + mode); 890 dmData.Q1.Close; 891 dmData.Q1.SQL.Clear; 892 dmData.Q1.SQL.Add('update cqrlog_main set lotw_qslr = ' + QuotedStr('L')); 893 dmData.Q1.SQL.Add(',lotw_qslrdate = ' + QuotedStr(qslrdate)); 894 if cqz<>'' then 895 dmData.Q1.SQL.Add(',waz = ' + QuotedStr(cqz)); 896 if ituz<>'' then 897 dmData.Q1.SQL.Add(',itu = ' + QuotedStr(ituz)); 898 if iota<>'' then 899 dmData.Q1.SQL.Add(',iota = ' + QuotedStr(iota)); 900 if (grid <> '') and (dmData.Q.Fields[5].AsString='') then 901 dmData.Q1.SQL.Add(',loc = ' + QuotedStr(grid)); 902 if (state<>'') and (dmData.Q.Fields[6].AsString='') then 903 dmData.Q1.SQL.Add(',state = ' + QuotedStr(state)); 904 if (county<>'') and (dmData.Q.Fields[7].AsString='') then 905 dmData.Q1.SQL.Add(',county = ' + QuotedStr(county)); 906 dmData.Q1.SQL.Add(' where id_cqrlog_main = ' + dmData.Q.Fields[8].AsString); 907 inc(qsln); 908 if dmData.DebugLevel>=1 then Writeln(dmData.Q1.SQL.Text+ ' qsl number:'+ IntToStr(qsln)); 909 dmData.Q1.ExecSQL 910 end; 911 qso_in_log := True; 912 Break 913 end; 914 dmData.Q.Next 915 end; 916 if not qso_in_log then 917 begin 918 l.Add('QSO NOT FOUND in log'); 919 l.Add('Call: '+call); 920 l.Add('Band: '+band); 921 l.Add('Mode: '+mode); 922 l.Add('QSO_date: '+qsodate); 923 l.Add('Time_on: '+time_on); 924 l.Add('QSLR: '+qslr); 925 l.Add('QSLRDate: '+qslrdate); 926 l.Add('CQZ: '+cqz); 927 l.Add('ITUZ: '+ituz); 928 l.Add('IOTA: '+iota); 929 l.Add('Grid: '+grid); 930 l.Add('State: '+state); 931 l.Add('County: '+county); 932 l.Add('------------------------------------------------'); 933 l.Add(''); 934 inc(ErrorCount) 935 end 936 end 937 end; 938 inc(num); 939 lblCount.Caption:= IntToStr(num); 940 if num mod 100 = 0 then 941 Repaint 942 end; 943 dmData.trQ1.Commit; 944 if ErrorCount > 0 then 945 begin 946 l.SaveToFile(dmData.HomeDir + 'lotw_error.txt'); 947 if Application.MessageBox(PChar(IntToStr(ErrorCount)+' QSO(s) were not found in your log. '#13' QSO(s) are stored to '+dmData.HomeDir + 'lotw_error.txt'+ 948 LineEnding+LineEnding+'Do you want to show the file?'),'Question ....',mb_YesNo+mb_IconQuestion)=idYes then 949 dmUtils.OpenInApp(dmData.HomeDir + 'lotw_error.txt') 950 //ShowMessage(IntToStr(ErrorCount)+' QSO(s) were not found in your log. '#13' QSO(s) are stored to '+dmData.HomeDir + 'lotw_error.txt') 951 end 952 end 953 else begin 954 if Application.MessageBox('Something is wrong because LoTW server returned invalid adif file header.'+LineEnding+ 955 'Your LoTW username/password could be wrong or LoTW server is down.'+LineEnding+LineEnding+'Do you want to show the file?', 956 'Error ...',mb_YesNo+mb_IconQuestion) = idYes then 957 dmUtils.OpenInApp(FileName) 958 end 959 finally 960 dmData.Q.Close(); 961 if dmData.trQ.Active then 962 dmData.trQ.Rollback; 963 if dmData.trQ1.Active then 964 dmData.trQ1.Rollback; 965 l.Free; 966 CloseFile(f); 967 if cqrini.ReadBool('OnlineLog','IgnoreLoTWeQSL',False) and dmLogUpload.LogUploadEnabled then 968 dmLogUpload.EnableOnlineLogSupport(False) 969 end; 970 Close 971 end; 972 973 procedure TfrmImportProgress.ImportQSLMgrs; 974 const 975 C_INS = 'INSERT INTO cqrlog_common.qslmgr (callsign,qsl_via,fromdate) VALUES (:callsign,:qsl_via, :fromdate)'; 976 var 977 sF : TextFile; 978 a : TExplodeArray; 979 call : String = ''; 980 qsl_via : String = ''; 981 fromDate : String = ''; 982 line : String = ''; 983 num : Word = 1; 984 e : Boolean = False; 985 begin 986 lblComment.Caption := 'Importing QSL managers ...'; 987 AssignFile(sF,FileName); 988 FileMode := 0; 989 {$I-} 990 Reset(sF); 991 {$I+} 992 if IOResult <> 0 then 993 begin 994 Application.MessageBox(PChar('Can not open source file ' + FileName + ' for reading!'),'Error ...',mb_ok+ 995 mb_IconError); 996 exit 997 end; 998 Application.ProcessMessages; 999 Repaint; 1000 try try 1001 dmData.qQSLMgr.Close; 1002 if dmData.trQSLMgr.Active then dmData.trQSLMgr.Rollback; 1003 dmData.trQSLMgr.StartTransaction; 1004 dmData.qQSLMgr.SQL.Text := 'delete from cqrlog_common.qslmgr'; 1005 dmData.qQSLMgr.ExecSQL; 1006 dmData.qQSLMgr.SQL.Text := C_INS; 1007 while not Eof(sF) do 1008 begin 1009 readln(sF,line); 1010 Writeln('Line: ',line); 1011 a := dmUtils.Explode(';',line); 1012 call := a[0]; 1013 qsl_via := a[1]; 1014 fromDate := a[2]+'-01'; 1015 1016 1017 dmData.qQSLMgr.Prepare; 1018 dmData.qQSLMgr.Params[0].AsString := call; 1019 dmData.qQSLMgr.Params[1].AsString := qsl_via; 1020 dmData.qQSLMgr.Params[2].AsString := fromDate; 1021 dmData.qQSLMgr.ExecSQL; 1022 1023 inc(num); 1024 lblCount.Caption := IntToStr(num); 1025 if num mod 100 = 0 then 1026 Repaint 1027 end 1028 except 1029 on Ex : Exception do 1030 begin 1031 dmData.trQSLMgr.Rollback; 1032 e := True; 1033 Writeln(Ex.Message) 1034 end 1035 end 1036 finally 1037 CloseFile(sF); 1038 if not e then 1039 dmData.trQSLMgr.Commit 1040 end; 1041 Close 1042 end; 1043 1044 procedure TfrmImportProgress.InsertQSLManagers; 1045 var 1046 qsl_via : String = ''; 1047 i : Integer = 0; 1048 begin 1049 lblComment.Caption := 'Inserting QSL managers ...'; 1050 pBarProg.Max := dmData.qCQRLOG.RecordCount; 1051 Application.ProcessMessages; 1052 dmData.qCQRLOG.DisableControls; 1053 try 1054 dmData.qCQRLOG.First; 1055 while not dmData.qCQRLOG.Eof do 1056 begin 1057 if (dmData.qCQRLOG.FieldByName('qsl_via').AsString = '') and 1058 dmData.QSLMgrFound(dmData.qCQRLOG.Fields[4].AsString,dmData.qCQRLOG.Fields[1].AsString,qsl_via) then 1059 begin 1060 dmData.trQ.StartTransaction; 1061 dmData.Q.SQL.Text := 'update cqrlog_main set qsl_via = ' + QuotedStr(qsl_via) + 1062 ' where id_cqrlog_main = '+ IntToStr(dmData.qCQRLOG.FieldByName('id_cqrlog_main').AsInteger); 1063 if dmData.DebugLevel>=1 then Writeln(dmData.Q.SQL.Text); 1064 dmData.Q.ExecSQL; 1065 dmData.trQ.Commit 1066 end; 1067 dmData.qCQRLOG.Next; 1068 pBarProg.StepIt; 1069 inc(i); 1070 if i mod 100 = 0 then 1071 Application.ProcessMessages 1072 end 1073 finally 1074 dmData.qCQRLOG.EnableControls 1075 end; 1076 Close 1077 end; 1078 1079 procedure TfrmImportProgress.ImporteQSLAdif; 1080 var 1081 num : Word = 1; 1082 size : Word; 1083 sSize : String; 1084 a : String; 1085 orig : String; 1086 f : TextFile; 1087 PosEOH : Word; 1088 PosEOR : Word; 1089 call : String; 1090 band : String; 1091 mode : String; 1092 submode : String; 1093 qsodate : String; 1094 time_on : String; 1095 qslr : String; 1096 PosCall : Word; 1097 PosBand : Word; 1098 PosMode : Word; 1099 PosSubmode : Word; 1100 PosQsoDate : Word; 1101 PosTime_on : Word; 1102 PosQslr : Word; 1103 1104 qso_in_log : Boolean = False; 1105 ErrorCount : Word = 0; 1106 l : TStringList; 1107 t_eQSL : TDateTime; 1108 t_eQSL_min : TDateTime; 1109 t_eQSL_max : TDateTime; 1110 t_log : TDateTime; 1111 1112 begin 1113 l := TStringList.Create; 1114 if dmData.trQ.Active then 1115 dmData.trQ.RollBack; 1116 if dmData.trQ1.Active then 1117 dmData.trQ1.RollBack; 1118 1119 if cqrini.ReadBool('OnlineLog','IgnoreLoTWeQSL',False) then 1120 dmLogUpload.DisableOnlineLogSupport; 1121 1122 dmData.trQ1.StartTransaction; 1123 dmData.trQ.StartTransaction; 1124 try 1125 AssignFile(f,FileName); 1126 Reset(f); 1127 lblComment.Caption := 'Importing eQSL Adif file ...'; 1128 pBarProg.Visible := False; 1129 Repaint; 1130 PosEOH := 0; 1131 PosEOR := 0; 1132 while not (PosEOH > 0) do //Skip header 1133 begin 1134 Readln(f, a); 1135 a := UpperCase(a); 1136 PosEOH := Pos('<EOH>', a); 1137 end; 1138 while not eof(f) do 1139 begin 1140 call := ''; 1141 band := ''; 1142 mode := ''; 1143 submode := ''; 1144 qsodate := ''; 1145 time_on := ''; 1146 qslr := ''; 1147 PosEOR := 0; 1148 while not ((PosEOR > 0) or eof(f)) do 1149 begin 1150 Readln(f, a); 1151 a := Trim(a); 1152 orig := a; 1153 a := UpperCase(a); 1154 1155 PosCall := Pos('<CALL:',a); 1156 PosBand := Pos('<BAND:',a); 1157 PosMode := Pos('<MODE:',a); 1158 PosSubmode := Pos('<SUBMODE:',a); 1159 PosQsoDate := Pos('<QSO_DATE:8:D',a); 1160 PosTime_on := Pos('<TIME_ON:',a); 1161 PosQslr := Pos('<QSL_RCVD:',a); 1162 PosEOR := Pos('<EOR>',a); 1163 1164 if PosCall > 0 then 1165 begin 1166 sSize := ''; 1167 PosCall := PosCall + 6; 1168 while not (a[PosCall] = '>') do 1169 begin 1170 sSize := sSize + a[PosCall]; 1171 inc(PosCall) 1172 end; 1173 Size := StrToInt(sSize); 1174 call := copy(orig,PosCall+1,Size) 1175 end; 1176 1177 if PosBand > 0 then 1178 begin 1179 sSize := ''; 1180 PosBand := PosBand + 6; 1181 while not (a[PosBand] = '>') do 1182 begin 1183 sSize := sSize + a[PosBand]; 1184 inc(PosBand) 1185 end; 1186 Size := StrToInt(sSize); 1187 band := copy(orig,PosBand+1,Size) 1188 end; 1189 1190 if PosMode > 0 then 1191 begin 1192 sSize := ''; 1193 PosMode := PosMode + 6; 1194 while not (a[PosMode] = '>') do 1195 begin 1196 sSize := sSize + a[PosMode]; 1197 inc(PosMode) 1198 end; 1199 Size := StrToInt(sSize); 1200 mode := copy(orig,PosMode+1,Size) 1201 end; 1202 1203 if PosSubmode > 0 then 1204 begin 1205 sSize := ''; 1206 PosSubmode := PosSubmode + 9; 1207 while not (a[PosSubmode] = '>') do 1208 begin 1209 sSize := sSize + a[PosSubmode]; 1210 inc(PosSubmode) 1211 end; 1212 Size := StrToInt(sSize); 1213 submode := copy(orig,PosSubmode+1,Size) 1214 end; 1215 1216 if PosQsoDate > 0 then 1217 begin 1218 qsodate :=copy(orig,PosQsoDate+14,8); 1219 { 1220 sSize := ''; 1221 PosQsoDate := PosQsoDate + 13; 1222 while not (a[PosQsoDate] = '>') do 1223 begin 1224 sSize := sSize + a[PosQsoDate]; 1225 inc(PosQsoDate) 1226 end; 1227 Size := StrToInt(sSize); 1228 qsodate := copy(orig,PosQsoDate+1,Size) 1229 } 1230 end; 1231 1232 if PosTime_on > 0 then 1233 begin 1234 sSize := ''; 1235 PosTime_on := PosTime_on + 9; 1236 while not (a[PosTime_on] = '>') do 1237 begin 1238 sSize := sSize + a[PosTime_on]; 1239 inc(PosTime_on) 1240 end; 1241 Size := StrToInt(sSize); 1242 time_on := copy(orig,PosTime_on+1,Size) 1243 end; 1244 1245 if PosQslr > 0 then 1246 begin 1247 sSize := ''; 1248 PosQslr := PosQslr + 10; 1249 while not (a[PosQslr] = '>') do 1250 begin 1251 sSize := sSize + a[PosQslr]; 1252 inc(PosQslr) 1253 end; 1254 Size := StrToInt(sSize); 1255 qslr := copy(orig,PosQslr+1,Size) 1256 end; 1257 1258 if PosEOR > 0 then 1259 begin 1260 band := UpperCase(band); 1261 mode := UpperCase(mode); 1262 submode := UpperCase(submode); 1263 qslr := UpperCase(qslr); 1264 call := UpperCase(call); 1265 if dmData.DebugLevel >= 1 then 1266 begin 1267 Writeln('Call: ',call); 1268 Writeln('Band: ',band); 1269 Writeln('Mode: ',mode); 1270 Writeln('Submode: ',submode); 1271 Writeln('QSO_date: ',qsodate); 1272 Writeln('Time_on: ',time_on); 1273 Writeln('QSLR: ',qslr); 1274 Writeln('------------------------------------------------') 1275 end; 1276 qsodate := dmUtils.ADIFDateToDate(qsodate); 1277 mode := UpperCase(mode); 1278 1279 1280 dmData.Q.Close; 1281 1282 if (mode='JT65') then //since implementing submodes below, this can most probably be removed 1283 begin 1284 dmData.Q.SQL.Text := 'select id_cqrlog_main,eqsl_qsl_rcvd,time_on from cqrlog_main ' + 1285 'where (qsodate ='+QuotedStr(qsodate)+') '+ 1286 'and ((mode = ' + QuotedStr('JT65') + ') or (mode='+QuotedStr('JT65A')+') '+ 1287 'or (mode='+QuotedStr('JT65B')+') or (mode='+QuotedStr('JT65C')+')) '+ 1288 'and (band = ' + QuotedStr(band) + ') '+ 1289 'and (callsign = ' + QuotedStr(call) + ')' 1290 end 1291 else begin 1292 dmData.Q.SQL.Text := 'select id_cqrlog_main,eqsl_qsl_rcvd,time_on from cqrlog_main ' + 1293 'where (qsodate ='+QuotedStr(qsodate)+') '+ 1294 'and ((mode = ' + QuotedStr(mode) + ') or (mode = ' + QuotedStr(submode) + ')) '+ 1295 'and (band = ' + QuotedStr(band) + ') '+ 1296 'and (callsign = ' + QuotedStr(call) + ')' 1297 end; 1298 if dmData.DebugLevel >=1 then Writeln(dmData.Q.SQL.Text); 1299 //if dmData.trQ.Active then dmData.trQ.Rollback; 1300 //dmData.trQ.StartTransaction; 1301 dmData.Q.Open(); 1302 while not dmData.Q.Eof do 1303 begin 1304 qso_in_log := False; 1305 1306 t_eQSL := EncodeTime(StrToInt(copy(time_on,1,2)), 1307 StrToInt(copy(time_on,3,2)),0,0); 1308 1309 t_log := EncodeTime(StrToInt(copy(dmData.Q.Fields[2].AsString,1,2)), 1310 StrToInt(copy(dmData.Q.Fields[2].AsString,4,2)),0,0); 1311 1312 t_eQSL_min := t_eQSL-60/1440; 1313 t_eQSL_max := t_eQSL+60/1440; 1314 1315 if dmData.DebugLevel >=1 then Writeln(call,'|',TimeToStr(t_log),' | ',TimeToStr(t_eQSL_min),'|',TimeToStr(t_eQSL_max)); 1316 1317 if (t_log >=t_eQSL_min) and (t_log<=t_eQSL_max) then 1318 begin 1319 if eQSLShowNew and (dmData.Q.Fields[1].AsString <> 'E') then 1320 eQSLQSOList.Add(qsodate+ ' ' + call + ' ' + band + ' ' + mode); 1321 if (dmData.Q.Fields[1].AsString <> 'E') then 1322 begin 1323 dmData.Q1.Close; 1324 dmData.Q1.SQL.Clear; 1325 dmData.Q1.SQL.Add('update cqrlog_main set eqsl_qsl_rcvd = ' + QuotedStr('E')); 1326 dmData.Q1.SQL.Add(',eqsl_qslrdate = ' + QuotedStr(dmUtils.DateInRightFormat(now))); 1327 dmData.Q1.SQL.Add(' where id_cqrlog_main = ' + dmData.Q.Fields[0].AsString); 1328 if dmData.DebugLevel>=1 then Writeln(dmData.Q1.SQL.Text); 1329 dmData.Q1.ExecSQL 1330 end; 1331 qso_in_log := True; 1332 Break //should only be one qso confirmed, if we have several answers we stop looping those if found one match 1333 end; 1334 dmData.Q.Next 1335 end; 1336 if not qso_in_log then 1337 begin 1338 l.Add('QSO NOT FOUND in log'); 1339 l.Add('Call: '+call); 1340 l.Add('Band: '+band); 1341 l.Add('Mode: '+mode); 1342 l.Add('Mode: '+submode); 1343 l.Add('QSO_date: '+qsodate); 1344 l.Add('Time_on: '+time_on); 1345 l.Add('------------------------------------------------'); 1346 l.Add(''); 1347 inc(ErrorCount) 1348 end 1349 end 1350 end; 1351 inc(num); 1352 lblCount.Caption:= IntToStr(num); 1353 if num mod 100 = 0 then 1354 Repaint 1355 end; 1356 dmData.trQ1.Commit; 1357 CloseFile(f); 1358 if ErrorCount > 0 then 1359 begin 1360 l.SaveToFile(dmData.UsrHomeDir + 'eQSL_error.txt'); 1361 ShowMessage('Some QSO(s) were not found in your log. '#13' QSO(s) are stored to '+dmData.UsrHomeDir + 'eQSL_error.txt') 1362 end 1363 finally 1364 l.Free; 1365 if cqrini.ReadBool('OnlineLog','IgnoreLoTWeQSL',False) then 1366 dmLogUpload.EnableOnlineLogSupport(False); 1367 Close 1368 end 1369 end; 1370 1371 procedure TfrmImportProgress.RemoveDupes; 1372 var 1373 err : Boolean = False; 1374 begin 1375 Caption := 'Remove dupes from the log'; 1376 lblComment.Caption := 'Creating temporary table'; 1377 Application.ProcessMessages; 1378 try try 1379 dmData.trQ.StartTransaction; 1380 dmData.Q.SQL.Text := 'create table tempdupes like cqrlog_main'; 1381 if dmData.DebugLevel>=1 then Writeln(dmData.Q.SQL.Text); 1382 dmData.Q.ExecSQL; 1383 dmData.trQ.Commit; 1384 1385 lblComment.Caption := 'Checking for dupe QSOs'; 1386 Application.ProcessMessages; 1387 sleep(200); 1388 1389 dmData.trQ.StartTransaction; 1390 dmData.Q.SQL.Text := 'insert into tempdupes ' + 1391 ' select * from cqrlog_main group by qsodate,time_on,callsign,mode,band'; 1392 if dmData.DebugLevel>=1 then Writeln(dmData.Q.SQL.Text); 1393 dmData.Q.ExecSQL; 1394 1395 dmData.Q.SQL.Text := 'delete from cqrlog_main'; 1396 if dmData.DebugLevel>=1 then Writeln(dmData.Q.SQL.Text); 1397 dmData.Q.ExecSQL; 1398 1399 dmData.Q.SQL.Text := 'insert into cqrlog_main select * from tempdupes'; 1400 if dmData.DebugLevel>=1 then Writeln(dmData.Q.SQL.Text); 1401 dmData.Q.ExecSQL 1402 except 1403 on E : Exception do 1404 begin 1405 Application.MessageBox(PChar('ERROR:'+E.Message+LineEnding),'Error ..',mb_OK+mb_IconError); 1406 err := True 1407 end 1408 end 1409 finally 1410 if err then 1411 dmData.trQ.Rollback 1412 else 1413 dmData.trQ.Commit; 1414 1415 lblComment.Caption := 'Done ...'; 1416 Application.ProcessMessages; 1417 Sleep(500); 1418 1419 dmData.trQ.StartTransaction; 1420 dmData.Q.SQL.Text := 'drop table tempdupes'; 1421 dmData.Q.ExecSQL; 1422 dmData.trQ.Commit; 1423 Close 1424 end 1425 end; 1426 1427 procedure TfrmImportProgress.UpdateMembershipFiles; 1428 1429 procedure SaveMembershipFile(l : TStringList; ClubFileName : String); 1430 begin 1431 if not DirectoryExistsUTF8(dmData.HomeDir + 'members') then 1432 CreateDirUTF8(dmData.HomeDir + 'members'); 1433 l.SaveToFile(dmData.HomeDir + 'members' + DirectorySeparator + ClubFileName) 1434 end; 1435 1436 procedure ImportMembeshipFileToDatabase(l : TStringList; ClubFileName : String); 1437 const 1438 C_INS = 'insert into %s (club_nr,clubcall,fromdate,todate) values (:club_nr, :clubcall, :fromdate, :todate)'; 1439 var 1440 ClubTableName : String; 1441 i : Integer; 1442 y : Integer; 1443 ClubLine : TMembershipLine; 1444 begin 1445 ClubTableName := dmMembership.GetClubTableName(ClubFileName); 1446 pBarProg.Position := 0; 1447 pBarProg.Max := l.Count-1; 1448 1449 dmData.q.Close; 1450 try try 1451 dmData.trQ.StartTransaction; 1452 for i:=0 to l.Count-1 do 1453 begin 1454 //ship file header 1455 if (i < 2) then 1456 Continue; 1457 1458 ClubLine := dmMembership.GetMembershipStructure(l.Strings[i]); 1459 1460 dmData.Q.SQL.Text := Format(C_INS, [ClubTableName]); 1461 dmData.Q.Prepare; 1462 dmData.Q.Params[0].AsString := ClubLine.club_nr; 1463 dmData.Q.Params[1].AsString := ClubLine.club_call; 1464 dmData.Q.Params[2].AsString := ClubLine.fromdate; 1465 dmData.Q.Params[3].AsString := ClubLine.todate; 1466 dmData.Q.ExecSQL; 1467 pBarProg.StepIt; 1468 Application.ProcessMessages 1469 end 1470 except 1471 on E : Exception do 1472 begin 1473 Application.MessageBox(PChar('ERROR:' + LineEnding + LineEnding + E.ToString), 'Error', mb_OK + mb_IconError); 1474 dmData.trQ.Rollback 1475 end 1476 end 1477 finally 1478 dmData.Q.Close; 1479 if dmData.trQ.Active then 1480 dmData.trQ.Commit 1481 end 1482 end; 1483 1484 var 1485 i : Integer; 1486 ClubFileNameWithPath : String; 1487 ClubFileName : String; 1488 data : String; 1489 l : TStringList; 1490 begin 1491 Application.ProcessMessages; 1492 l := TStringList.Create; 1493 try try 1494 for i:=0 to dmMembership.ListOfMembershipFilesForUpdate.Count-1 do 1495 begin 1496 if (dmMembership.ListOfMembershipFilesForUpdate.Strings[i] = '') then 1497 Continue; 1498 1499 l.Clear; 1500 ClubFileNameWithPath := dmMembership.ListOfMembershipFilesForUpdate.Strings[i]; 1501 ClubFileName := ExtractFileName(ClubFileNameWithPath); 1502 1503 lblComment.Caption := 'Downloading ' + ClubFileName; 1504 Application.ProcessMessages; 1505 1506 if dmUtils.GetDataFromHttp(Format(C_MEMBERSHIP_DOWNLOAD_URL,[ClubFileName]), data) then 1507 begin 1508 l.Add(data); 1509 1510 lblComment.Caption := 'Importing ' + ClubFileName; 1511 Application.ProcessMessages; 1512 1513 SaveMembershipFile(l, ClubFileName); 1514 //without loading again whole data was in one line only 1515 l.Clear; 1516 l.LoadFromFile(dmData.HomeDir + 'members' + DirectorySeparator + ClubFileName); 1517 1518 ImportMembeshipFileToDatabase(l, ClubFileName); 1519 1520 dmMembership.SaveLastMembershipUpdateDate(ClubFileName, now()); 1521 end 1522 end 1523 except 1524 on E : Exception do 1525 Application.MessageBox(PChar('ERROR:' + LineEnding + LineEnding + E.ToString), 'Error', mb_OK + mb_IconError) 1526 end 1527 finally 1528 FreeAndNil(l) 1529 end; 1530 Close 1531 end; 1532 1533 end. 1534 1535