; ----------------------------------------------------------------------------------------------------
;
; Install:
;    - Run PARADOX 5.0
;    - Create a new Script file
;    - Copy the contents of this file into the new opened script
;    - Run it
;
; This script is delivered as a text file, so anyone who don't have Paradox 5.0 can read it anyway
; and can modify it, if it doesn't work with an older release of Paradox.
;
; ----------------------------------------------------------------------------------------------------
;
; Import ShowCIM and ShowCIM for Windows. Version 1.0
;
; This Paradox 5.0 script can be used to import files created with ShowCIM V1.5 or later using the
; /DB switch or with ShowCIM for Windows with the 'Database export' option checked.
; The import command of Paradox uses the carriage return as an end of record (in a text file), and
; message bodies are full of CR, so I've written the script to get round this problem (Eh Mr Borland,
; why didn't you think of it?).
;
;
; Use: Simply run the script. You will be asked for some files/table locations...
;      When running, press CTRL BREAK to stop the script...
;
;
; It's freeware and you can give it to anybody. Just let me know if you add some features (if you want
; to share it with other users). Thanks for any comment, suggestion...
;
; Here is the structure of the table auto-created for import (all fields are Text-type)
;
;    Field         | Length used | Length suggested (tested over 2500 CompuServe thread files)
;    --------------+-------------+------------------------------------------------------------
;    File          | 130         | *  12 (If only the file name is given, no path with it)
;    Type          | 50          | *  40
;    Forum         | 50          |    50
;    Section       | 50          |    50
;    Message_ID    | 6           |    6
;    Parent_ID     | 6           |    6
;    Subject       | 255         | *  80
;    To            | 50          |    50
;    To_ID         | 50          | *  11 (Be careful, CompuServe addresses are 11 char. long but
;                  |             |        some other adresses can be longer like Internet addresses)
;    From          | 50          |    50 (See above for extra comment)
;    From_ID       | 50          | *  11
;    Creation_Date | 8           |    8
;    Creation_Time | 8           |    8
;    Message_Body  | 25          | *  100 (This field is a Memo field)
;    --------------+-------------+------------------------------------------------------------
;    In database   | 1024 bytes  | 512 bytes
;    --------------+-------------+------------------------------------------------------------
;
; The script does NOT test all possible errors, but tests inserting fields in the table and generates
; an error table containing the file name and the error message. When an error occurs, the process is
; stopped for the current record and goes to the next. Analyzing the last field inserted in the table
; for this file will show you where the error is located.
;
; I'm thinking about building a complete Paradox application... in the future
;
; Gildas Quiniou, Paris/France    CIS-ID:100432,500    December 29, 1994
;
; ----------------------------------------------------------------------------------------------------

Var
   File        TextStream                             ; Text file descriptor
   Fields      Array[] String                          ; The fields for each message
   Line,                                              ; A line read in the text file
   FileName,                                          ; Text file name
   ErrorTable  String                                 ; Error file name
   PosInLine   Number                                 ; Index in the line read
   Threads     TCursor                                ; TCursor opened on the table
   NErrors     SmallInt                               ; Number of errors encountered
EndVar

Const
   QUOTE="\""                                         ; Field separator
   N_FIELDS=14                                        ; Number of fields per record
   MAX_READLINE=1023                                  ; Max size for a string
EndConst

;
; Read a line in the text file.
;
; Paradox limits the size of a string read with ReadLine() to 1023 caracters.
; This procedure can read strings up to the 32000 caracters limit of string variables.
; This is enough for most messages...
;

Proc MyReadLine(Var Buffer String) Logical
   Var
      TmpBuf   String                                 ; Temporary string
      TmpFile  TextStream                             ; File descriptor to open temporarily the file twice
      Position LongInt                                ; The offset in the text file
   EndVar

   Buffer=""                                          ; Nothing has been read
   While TRUE                                          ; I use QuitLoop to break the process
      Position=File.Position()
      If Not File.ReadLine(TmpBuf) Then               ; Try to read a line
         Return FALSE                               ; Nothing else to read
      EndIf
      Buffer=Buffer+TmpBuf                            ; Merge the string read with the previous part read
      If TmpBuf.Size()=MAX_READLINE Then             ; If the string read has a size of 1023 :
                                                      ;   - Either the string is really 1023 char. long (it's very rare)
                                                      ;   - Or the string is longer but Paradox hasn't read more, and
                                                      ;     we need to do the job for it...
         If Not TmpFile.Open(FileName,"r") Then       ; So, open the text file temporarily
            ErrorShow()
            Return FALSE
         EndIf
         TmpFile.SetPosition(Position+MAX_READLINE)   ; Seek for the next character to be read
         If TmpFile.Eof() Then
            TmpFile.Close()
            Return TRUE
         EndIf
         TmpFile.ReadChars(TmpBuf,1)                  ; Read this character
         If TmpBuf="\r" Or TmpBuf="\n" Then            ; If it's an carriage return, this means the whole line has been read
            TmpFile.Close()
            Return TRUE                                ; MyReadLine() is OK
         EndIf
         TmpFile.Close()
         File.SetPosition(Position+MAX_READLINE)      ; To be sure, Paradox is going to read the good things
      Else
         Return TRUE                                   ; The length of the line read is less than 1023, it's OK
      EndIf
   EndWhile                                           ; Another turn...
EndProc

;
; Because quotes are doubled in fields, we need to convert double quotes into single ones
;

Proc StripDoubleQuotes(Var Field String)
   Var
      MyString String
      LoopVar,
      Len      Number
   EndVar

   MyString=""
   LoopVar=2
   Len=Field.Size()
   While LoopVar<Len
      MyString=MyString+Field.Substr(LoopVar)
      If Field.Substr(LoopVar)=QUOTE Then
         LoopVar=LoopVar+1
      EndIf
      LoopVar=LoopVar+1
   EndWhile
   Field=MyString.ToANSI()
EndProc

;
; Look for the end of field begining at the 'Begin' position.
; The end of field is reached when a single quote is found.
; Returns the end position if found ou -1 if not (field continues on the next line)
;

Proc SearchEndOfField(Begin Number) Number
   Var
      LoopVar,
      Len      Number
   EndVar

   Len=Line.Size()
   If Len=0 Then
      Return 0
   EndIf
   If Len=1 Then
      If Line.Substr(1)=QUOTE Then
         Return 1
      Else
         Return 0
      EndIf
   EndIf
   If Line.Substr(1)=QUOTE Then
      If Line.Substr(2)=QUOTE Then
         LoopVar=Begin+2
      Else
         LoopVar=Begin+1
      EndIf
   Else
      LoopVar=Begin+1
   EndIf
   While LoopVar<=Len
      If Line.Substr(LoopVar)=QUOTE Then
         If (LoopVar=Len) Then
            Return Len
         EndIf
         If Line.Substr(LoopVar+1)=QUOTE Then
            LoopVar=LoopVar+2
            Loop
         EndIf
         Return LoopVar
      EndIf
      LoopVar=LoopVar+1
   EndWhile
   Return 0
EndProc

;
; Retrieve the next field.
;

Proc ReadField(Var FieldRead String) Logical
   Var
      Len   Number
   EndVar

   FieldRead=""
   If PosInLine=0 Then
      If Not MyReadLine(Line) Then
         Return FALSE
      EndIf
      Len=Line.Size()
      PosInLine=1
   Else
      Len=Line.Size()
   EndIf
   While TRUE
      PosFin=SearchEndOfField(PosInLine)
      If PosFin=0 Then
         If (Len-PosInLine+1)>0 Then
            FieldRead=FieldRead+Line.Substr(PosInLine,Len-PosInLine+1)+"\n"
         EndIf
         If Not MyReadLine(Line) Then
            Return FALSE
         EndIf
         Len=Line.Size()
         PosInLine=1
         Loop
      Else
         FieldRead=FieldRead+Line.Substr(PosInLine,PosFin-PosInLine+1)
         If (PosFin=Len) Or (PosFin=Len+1) Then
            PosInLine=0
         Else
            PosInLine=PosFin+2
         EndIf
         Return TRUE
      EndIf
   EndWhile
EndProc

;
; Write a record in the table field by field. If an error occurs, the error message is
; written in an error table and it beeps. The process aborts for this record and continues
; with the next one
;

Proc WriteFields() Logical
   Var
      LoopVar  SmallInt
      Errors   TCursor
      TheError String
   EndVar

   Try
      Threads.InsertRecord()
      For LoopVar From 1 To N_FIELDS
         StripDoubleQuotes(Fields[LoopVar])
         If Not Threads.SetFieldValue(LoopVar,Fields[LoopVar]) Then
            TheError=ErrorMessage()
;           ErrorShow()
            Errors.Open(ErrorTable)
            Errors.Edit()
            Errors.InsertRecord()
            Errors.SetFieldValue(1,FileName)
            Errors.SetFieldValue(2,TheError)
            Errors.Close()
            Beep()
            NErrors=NErrors+1
            QuitLoop
         EndIf
      EndFor
      Fields.Empty()
      Return TRUE
   OnFail
      ErrorShow()
      Return FALSE
   EndTry
EndProc

;
; Retrieve all fields in a record. Then it verifies the number of fields read.
; If it's OK it writes the record in the table, otherwise it does nothing and
; go to the next record.
;
; NB : What shall I do if the number of fields is not good ?...
;

Proc RetrieveFields(FileName String) Logical
   Var
      FieldRead   String
   EndVar

   MyReadLine(Line)
   Fields.Empty()
   PosInLine=0
   While TRUE
      If Not ReadField(FieldRead) Then
         Return TRUE
      EndIf
      Fields.AddLast(FieldRead)
      If Fields.Size()=N_FIELDS Then
         If Not WriteFields() Then
            Return FALSE
         EndIf
      EndIf
   EndWhile
EndProc

;
; Opens a thread on the table and compute each file found in the 'SourcePath' path.
; An info message is printed at the bottom of the window:
;    Name of file / File number / Number of files / Number of errors
;

method run(var eventInfo Event)
   Var
      List        FileSystem
      Files       Array[] String
      NFiles      SmallInt
      BeginTime   LongInt
      TableName,
      SourcePath  String
      FBI         FileBrowserInfo
      NewTable    Table
   EndVar

   If MsgQuestion("Create Table","You will choose the path where files to import are located (wildcard '*.*' will be used)\n\nOK to proceed?")="No" Then
      Return
   EndIf
   FBI.AllowableTypes=fbFiles
   FBI.PathOnly=TRUE
   If Not FileBrowser(SourcePath,FBI) Then
      Return
   EndIf
   If MsgQuestion("Create Table","You will be asking for the name and location of the import table.\nGive a name up to 8 characters...\n\nOK to proceed?")="No" Then
      Return
   EndIf
   While TRUE
      If Not FileBrowser(TableName) Then
         Return
      EndIf
      If Not IsTable(TableName) Then
         QuitLoop
      EndIf
      If MsgQuestion("","The table '"+TableName+"' exists... Do you want to append?")="Yes" Then
         QuitLoop
      EndIf
   EndWhile
   If Not IsTable(TableName) Then
      NewTable=Create TableName With        ; I suggest you the values below to save more space.
                                             ; Tested over 2500 CompuServe thread files with no broblem
         "File"            : "A130",       ; A12 (If only the file name is given, no path with it)
         "Type"            : "A50",           ; A40
         "Forum"           : "A50",
         "Section"         : "A50",
         "Message_ID"      : "A6",
         "Parent_ID"       : "A6",
         "Subject"         : "A255",       ; A80
         "To"              : "A50",
         "To_ID"           : "A50",           ; A11 (Be careful, CompuServe addresses are 11 char. long but
                                             ; some other adresses can be longer like Internet addresses)
         "From"            : "A50",
         "From_ID"         : "A50",           ; A11 (see above for extra comment)
         "Creation_Date"   : "A8",
         "Creation_Time"   : "A8",
         "Message_Body"    : "M25"            ; M100 (You can make this field as short as you want because datas
                                             ; are stored in a separated .MB file)

                                             ; Each record uses 1024 bytes with the initial mode
                                             ;              and  512 bytes with the suggested one
      EndCreate
   EndIf
   If MsgQuestion("Create Table","You will be asking for the name and location of the error table.\nGive a name up to 8 characters...\n\nOK to proceed?")="No" Then
      Return
   EndIf
   While TRUE
      If Not FileBrowser(ErrorTable) Then
         Return
      EndIf
      If Not IsTable(ErrorTable) Then
         QuitLoop
      EndIf
      If MsgQuestion("","The table '"+TableName+"' exists... Do you want to append?")="Yes" Then
         QuitLoop
      EndIf
   EndWhile
   If Not IsTable(ErrorTable) Then
      NewTable=Create ErrorTable With
         "File"         : "A130",
         "Error"        : "A150"
      EndCreate
   EndIf
   BeginTime=CpuClockTime()
   Try
      Threads.Open(TableName)
      Threads.Edit()
      If SourcePath.Substr(SourcePath.Size())<>"\\" Then
         SourcePath=SourcePath+"\\"
      EndIf
      List.EnumFileList(SourcePath+"*.*",Files)
      Files.RemoveItem(".")
      Files.RemoveItem("..")
      NFiles=Files.Size()
      If MsgQuestion("","The directory '"+SourcePath+"' contains "+String(NFiles)+" file(s)\n\nOK to proceed?")="No" Then
         Return
      EndIf
      PosDansFichier=0
      NErrors=0
      For LoopVar From 1 To NFiles
         FileName=SourcePath+Files[LoopVar]
         Message("File: "+Files[LoopVar]+"   |   "+String(LoopVar)+" / "+String(NFiles)+"   |   Errors: "+String(NErrors))
         If Not File.Open(FileName,"r") Then
            ErrorShow()
            Return
         EndIf
         If Not RetrieveFields(FileName) Then
            QuitLoop
         EndIf
         File.Close()
      EndFor
      Threads.Close()
   OnFail
      ErrorShow()
   EndTry
   MsgInfo("Process status","The process is finished.\nTime: "+String((CpuClockTime()-BeginTime)/1000)+" sec.\nErrors: "+String(NErrors))
endmethod
