1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
| Procedure TCadaDataModule.FillTableProprio(CadaRepert:String);
Var
Nom, AdresseProprio, CodePostal, Localite, DroitProprio:String;
Daa, CodeNext, DivCode, LenStr, DelimPos, NoField : Integer;
Fichier : TextFile ;
Chaine, Champs : string;
NoRec, NbrRec :LongInt;
InitTime, InstTime, RemainTime, TotTime : TTime;
Begin
if TableExist('TEMPTABLE') Then SuppressTable('TEMPTABLE');
with Zquery.Sql do // Création de la table temporaire des biens
begin // Add(Name; DataType: TFieldType; Size; Required);
Clear; // Description des champs
Add('Create Table TempTable (');
Add('DIVCODE INTEGER,'); // Field8
Add('DAA INTEGER,'); // Field8
Add('NOM VARCHAR(180),'); // Field1
Add('ADRESSEPROPRIO VARCHAR(44),'); // Field4
Add('CODEPOSTAL CHAR(4),'); // Field5
Add('LOCALITE VARCHAR(38),'); // Field7
Add('CODENEXT SMALLINT,'); // Field9
Add('DROITPROPRIO VARCHAR(45)'); // Field10
Add(') ;');
end;
Zquery.ExecSQL;
AssignFile(Fichier,CadaRepert+'Pe.txt');
Reset(Fichier);
NbrRec := Ceil(FileSize(Fichier)*1.21);
FormProgress.LabelTask.Caption := MainForm.StrList[181];
FormProgress.StepLabel.Caption := MainForm.StrList[182];
FormProgress.StepLabel.Color := $0080FF80;
FormProgress.OperLabel.Caption := MainForm.StrList[183];
FormProgress.OperLabel.Color :=$0080FF80;
FormProgress.ProgressBar.Step := 1;
FormProgress.ProgressBar.Max := NbrRec;
NoRec:=0;
InitTime := Now;
Zquery.Sql.Clear;
ZQuery.SQL.Add('INSERT INTO TempTable (');
ZQuery.SQL.Add('DivCode,Daa,Nom,AdresseProprio,CodePostal,Localite,CodeNext,DroitProprio) ');
ZQuery.SQL.Add('Values (');
ZQuery.SQL.Add(':DivCode,:Daa,:Nom,:AdresseProprio,:CodePostal,:Localite,:CodeNext,:DroitProprio) ');
While not System.eof(Fichier) Do // Lecture des enregistrements
Begin
Inc(NoRec);
Nom:='';AdresseProprio:='';CodePostal:='';Localite:='';Daa:=0;
DivCode:=0;CodeNext:=0;DroitProprio:='';
NoField:=1;
ReadLn(Fichier,Chaine);
DelimPos := Pos(';',Chaine);
LenStr:=1 ;
While (NoField < 9) and (DelimPos > 0 ) do // Lecture des champs
Begin
DelimPos := Pos(';',Chaine);
if DelimPos = 0 Then DelimPos := LenStr;
Champs := Copy(Chaine,0,DelimPos -1) ;
LenStr:= Length(Champs);
Case NoField of // Assignation des champs
1: Nom := Champs;
3: begin
DivCode:= StrToInt(Copy(Champs,1,5));
Daa:=StrToInt(Copy(Champs,6,5));
end;
4: CodeNext:= StrToInt(Champs);
5: Begin
CodePostal := Copy(Champs,1,5);
Localite := Copy(Champs,6,LenStr-5);
end;
6: AdresseProprio := Champs;
8: DroitProprio := Champs;
end;
LenStr := Length(Chaine);
Chaine := Copy(Chaine,DelimPos+1,LenStr-DelimPos);
Inc(NoField);
end; //Fin Lecture des champs
Zquery.Params[0].AsInteger:=DivCode;
Zquery.Params[1].AsInteger:=Daa;
Zquery.Params[2].AsString:=Nom;
Zquery.Params[3].AsString:=AdresseProprio;
Zquery.Params[4].AsString:=CodePostal;
Zquery.Params[5].AsString:=Localite;
Zquery.Params[6].AsSmallInt:=CodeNext;
Zquery.Params[7].AsString:=DroitProprio;
Zquery.ExecSQL;
FormProgress.ProgressBar.StepIt;
InstTime := Now-InitTime ;
TotTime := EndStepTime + InstTime;
RemainTime := InstTime/NoRec*(NbrRec-NoRec) ;
FormProgress.LabelElapsTimeDigit.Caption := TimeToStr(InstTime);
FormProgress.LabelRemainTimeDigit.Caption:=TimeToStr(RemainTime);
FormProgress.LabelTotalElapsTimeDigit.Caption := TimeToStr(TotTime);
end; // Fin Lecture des enregistrements
CloseFile(Fichier);
ZQuery.SQL.Clear;
ZQuery.SQL.Add('INSERT INTO "TableProprio" ('); // Remplissage de la Table "TableProprio"
ZQuery.SQL.Add('Nom, AdresseProprio, CodePostal, Localite, DroitProprio, Article) ');
ZQuery.SQL.Add('SELECT ');
ZQuery.SQL.Add('TempTable.Nom as Nom, TempTable.AdresseProprio as AdresseProprio, ');
ZQuery.SQL.Add('TempTable.CodePostal as CodePostal, TempTable.Localite as Localite, ');
ZQuery.SQL.Add('TempTable.DroitProprio as DroitProprio, ');
ZQuery.SQL.Add('((TableDiv.Div*100000)+ TempTable.Daa) as Article ');
ZQuery.SQL.Add('FROM TempTable INNER JOIN TableDiv ON ');
ZQuery.SQL.Add('TempTable.DivCode = TableDiv.DivCode');
Zquery.ExecSQL ;
Zquery.SQL.Clear;
TableProprio.Active := True;
end; |
Partager