PureBasic Forums - English

Code: Select all

; Author: Fangbeast (MGB Technical Solutions) 2015
; 
; License: Do whatever you want with it except claim that it is yours. You may never, ever sell it.
; 
; Credits; Bernd Krüger-Knauber (InfraTec) for fixing a lot of my blob, import mistakes and just about ever other program that I had issues with.
; People underestimate his knowledge and help, he is a top bloke.
; 

Define EventID, MenuID, GadgetID, WindowID

Enumeration 1
  #Window_BigOvenToSqLite
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_BigOvenToSqLite_Recipeboxes
  #Gadget_BigOvenToSqLite_Numberofrecipeboxes
  #Gadget_BigOvenToSqLite_TItlelist
  #Gadget_BigOvenToSqLite_Numberoftitles
  #Gadget_BigOvenToSqLite_Numberofpictures
  #Gadget_BigOvenToSqLite_Numberofingredients
  #Gadget_BigOvenToSqLite_Convertpath
  #Gadget_BigOvenToSqLite_Databasename
  #Gadget_BigOvenToSqLite_Getdatabase
  #Gadget_BigOvenToSqLite_Getpath
  #Gadget_BigOvenToSqLite_Convert
  #Gadget_BigOvenToSqLite_Exitprogram
  #Gadget_BigOvenToSqLite_Errorlist
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

Procedure.i Window_BigOvenToSqLite()
  If OpenWindow(#Window_BigOvenToSqLite, 114, 68, 785, 715, "BigOven recipes to MGBRecipes", #PB_Window_SystemMenu|#PB_Window_ScreenCentered|#PB_Window_Invisible)
    ListIconGadget(#Gadget_BigOvenToSqLite_Recipeboxes, 5, 5, 160, 555, "Boxes", 156, #PB_ListIcon_FullRowSelect|#LVS_NOCOLUMNHEADER)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Recipeboxes, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Recipeboxes, LoadFont(#Gadget_BigOvenToSqLite_Recipeboxes, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, 5, 565, 160, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, LoadFont(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Comic Sans MS", 10, 0))
    ListIconGadget(#Gadget_BigOvenToSqLite_TItlelist, 170, 5, 610, 555, "Titles", 596, #PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection|#LVS_NOCOLUMNHEADER)
      SetGadgetColor(#Gadget_BigOvenToSqLite_TItlelist, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_TItlelist, LoadFont(#Gadget_BigOvenToSqLite_TItlelist, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberoftitles, 170, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberoftitles, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberoftitles, LoadFont(#Gadget_BigOvenToSqLite_Numberoftitles, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofpictures, 375, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofpictures, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofpictures, LoadFont(#Gadget_BigOvenToSqLite_Numberofpictures, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofingredients, 580, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofingredients, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofingredients, LoadFont(#Gadget_BigOvenToSqLite_Numberofingredients, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Convertpath, 5, 595, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Convertpath, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Convertpath, LoadFont(#Gadget_BigOvenToSqLite_Convertpath, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Databasename, 5, 625, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Databasename, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Databasename, LoadFont(#Gadget_BigOvenToSqLite_Databasename, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Getdatabase, 5, 655, 190, 25, "Open database")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Getdatabase, LoadFont(#Gadget_BigOvenToSqLite_Getdatabase, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Getpath, 200, 655, 190, 25, "Get path")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Getpath, LoadFont(#Gadget_BigOvenToSqLite_Getpath, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Convert, 395, 655, 190, 25, "Convert now")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Convert, LoadFont(#Gadget_BigOvenToSqLite_Convert, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Exitprogram, 590, 655, 190, 25, "Exit program")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Exitprogram, LoadFont(#Gadget_BigOvenToSqLite_Exitprogram, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Errorlist, 5, 685, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Errorlist, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Errorlist, LoadFont(#Gadget_BigOvenToSqLite_Errorlist, "Comic Sans MS", 10, 0))
    HideWindow(#Window_BigOvenToSqLite, 0)
    ProcedureReturn WindowID(#Window_BigOvenToSqLite)
  EndIf
EndProcedure

; My generic includes

Declare.s CountRecords(QueryString.s)
Declare.s DatabaseLastInsertRowId()
Declare.s KillQuote(Instring.s)
Declare   LastLine(Gadget.i, LineNumber.i)
Declare.s MakeSureDirectoryPathExists(Directory.s)
Declare   OpenOtherDatabase()
Declare   OpenSystemDatabase()
Declare.s RepQuote(Instring.s)
Declare.s TextToXML(TextString.s)
Declare.s XMLToText(XMLString.s)

; Custom includes

Declare   ConvertBigOvenToSqLite(CurrentFile.s)
Declare   GetConvertPath()
Declare   SearchEngine(SearchDirectory.s)

; Need a unicode aware version of the API directory creator

Import "shell32.lib"                                                                                ; 
  SHCreateDirectory(*hwnd, pszPath.p-unicode)                                                        ; 
EndImport                                                                                           ; 

; 

UseJPEG2000ImageDecoder()                                                                           ; PureBasic native image decoder
UseJPEGImageDecoder()                                                                               ; PureBasic native image decoder
UsePNGImageDecoder()                                                                                ; PureBasic native image decoder
UseTGAImageDecoder()                                                                                ; PureBasic native image decoder
UseTIFFImageDecoder()                                                                               ; PureBasic native image decoder

; 

UseODBCDatabase()                                                                                   ; 
UseSQLiteDatabase()                                                                                 ; 

; 

#ODBC_ADD_DSN                       =   1 ; Add Data source
#ODBC_ADD_SYS_DSN                   =   4 ; Add SYSTEM Data source
#ODBC_CONFIG_DSN                    =   2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN                    =   3 ; Remove Data source
#ODBC_REMOVE_SYS_DSN                =   6 ; Remove SYSTEM Data source

#SQL_SUCCESS                        =   0
#SQL_SUCCESS_WITH_INFO              =   1
#SQL_ERROR                          =  -1
#SQL_INVALID_HANDLE                 =  -2
#SQL_NO_DATA                        = 100
#SQL_MAX_MESSAGE_LENGTH             = 512
#SQL_NTS                            =  -3
#SQL_HANDLE_ENV                     =   1;?
#SQL_HANDLE_DBC                     =   2;?
#SQL_HANDLE_STMT                    =   3
#SQL_HANDLE_DESC                    =   4;?
#SQL_C_CHAR                         =   1

#ODBC_ERROR_GENERAL_ERR             =   1
#ODBC_ERROR_INVALID_BUFF_LEN        =   2
#ODBC_ERROR_INVALID_HWND            =   3
#ODBC_ERROR_INVALID_STR             =   4
#ODBC_ERROR_INVALID_REQUEST_TYPE    =   5
#ODBC_ERROR_COMPONENT_NOT_FOUND     =   6
#ODBC_ERROR_INVALID_NAME            =   7
#ODBC_ERROR_INVALID_KEYWORD_VALUE   =   8
#ODBC_ERROR_INVALID_DSN             =   9
#ODBC_ERROR_INVALID_INF             =  10
#ODBC_ERROR_REQUEST_FAILED          =  11
#ODBC_ERROR_INVALID_PATH            =  12
#ODBC_ERROR_LOAD_LIB_FAILED         =  13
#ODBC_ERROR_INVALID_PARAM_SEQUENCE  =  14
#ODBC_ERROR_INVALID_LOG_FILE        =  15
#ODBC_ERROR_USER_CANCELED           =  16
#ODBC_ERROR_USAGE_UPDATE_FAILED     =  17
#ODBC_ERROR_CREATE_DSN_FAILED       =  18
#ODBC_ERROR_WRITING_SYSINFO_FAILED  =  19
#ODBC_ERROR_REMOVE_DSN_FAILED       =  20
#ODBC_ERROR_OUT_OF_MEM              =  21
#ODBC_ERROR_OUTPUT_STRING_TRUNCATED =  22

; My personal constants - Program details etc

#Author                         = "Miklos G Bolvary"                                                ; Author's name
#CopyRight                      = "MGB Technical Services 2015 "                                    ; Copyright holder
#Basename                       = "OvenConvert "                                                    ; Base name for ini and dataabse
#Version                        = "v0.00 "                                                          ; Program version
#Program                        = #Basename + #Version                                              ; Copyright string
#Eol                            = Chr(13) + Chr(10)                                                 ; End of line marker
#Fish                           = "<°)))o><²³  "                                                    ; My favourite fish!!
#Traynote                       = #Basename + #Version                                              ; Copyright string

; My personal constants - Program constants that don't need to change

#AtTheEndOfTheList              = -1                                                                ; 

#NoPictureHandle                = 0                                                                 ; 
#NoFileHandle                   = 0                                                                 ; 
#NoFileFound                    = -1                                                                ; 
#NoXMLNodeHandle                = 0                                                                 ; 

#NoBlobFound                    = 0                                                                 ; 
#NoDataFound                    = 0                                                                 ; 
#NoBufferDataFound              = 0                                                                 ; 

#NothingEncoded                 = 0                                                                 ; 
#NothingDecoded                 = 0                                                                 ; 

#NothingSelected                = -1                                                                ; 
#NothingFound                   = 0                                                                 ; 

#NoItems                        = 0                                                                 ; 
#FirstItem                      = 0                                                                 ; 

#EmptyString                    = ""                                                                ; 

#DatabaseQueryFail              = 0                                                                 ; 
#DatabaseUpdateFail             = 0                                                                 ; 
#DatabaseOpenFail               = 0                                                                 ; 

#NotEndOfFile                   = 0                                                                 ; 

; 

Structure ProgramData
  ; Program flags
  QuitValue.i                                                                                       ; 
  ConvertPath.s                                                                                     ; 
  
  ; BigOven specific
  Numberofrecipeboxes.i                                                                             ; 
  Numberoftitles.i                                                                                  ; 
  Numberofpictures.i                                                                                ; 
  Numberofingredients.i                                                                             ; 
  
  ; Database specific
  DatabaseHandle.i                                                                                  ; Handle to the current database
  DatabaseDirectory.s                                                                               ; Directory for the database
  DatabaseName.s                                                                                    ; Current path and name of the system database
  
  ; Directory directives
  CurrentDirectory.s                                                                                ; The current program startup directory
  TemporaryDirectory.s                                                                              ; Temporary files directory
  
  CrbPassword.s                                                                                     ; 
  
  Configfile.s                                                                                      ; 
  LastDatabase.s                                                                                    ; 
  LastConvertPath.s                                                                                 ; 
EndStructure

; 

Global Program.ProgramData                                                                         ; 
Global NewList FoundDirs.s()                                                                      ; List of recursed import directories

; 

Program\CrbPassword         = "mealpoint123"                                                        ; 

; Get working directories variables and create the physical dirs that go with them

Program\CurrentDirectory    = GetCurrentDirectory()                                                 ; Get the current directory name

; Setup the various system directory names and paths

Program\DatabaseDirectory   = Program\CurrentDirectory  + "Database\"                               ; Database directory

; Setup the various system file names and where they go.

Program\DatabaseName        = Program\DatabaseDirectory   + ReplaceString(#Basename, " ", "_") + #Version  +  ".Sqlite" ; Database name and path

; Setup the program config text file

Program\Configfile          = Program\CurrentDirectory + "Configfile.ini"                           ; 

; Create the directories if you can

MakeSureDirectoryPathExists(Program\DatabaseDirectory)                                              ; 

; Count the number of records in an SQLite database

Procedure.s CountRecords(QueryString.s)
  ; 
  If DatabaseQuery(Program\DatabaseHandle, QueryString.s)
    While NextDatabaseRow(Program\DatabaseHandle)
      Records.s = GetDatabaseString(Program\DatabaseHandle, 0)
    Wend
    ProcedureReturn Records.s
  EndIf
  ; 
EndProcedure

; Get the ID of the last inserted record

Procedure.s DatabaseLastInsertRowId()
  ; 
  If DatabaseQuery(Program\DatabaseHandle, "SELECT last_insert_rowid()")
    While NextDatabaseRow(Program\DatabaseHandle)
      RecordId.s = GetDatabaseString(Program\DatabaseHandle, 0)
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  EndIf
  ; 
  ProcedureReturn RecordId.s
  ; 
EndProcedure

; Kill double quotes in strings for display purposes

Procedure.s KillQuote(Instring.s)
  ; 
  ProcedureReturn ReplaceString(Instring.s, "''", "'", 1, 1)
  ; 
EndProcedure

; Go to the last line of a ListIconGadget

Procedure LastLine(Gadget.i, LineNumber.i)
  ; Make sure the current line is visible
  SendMessage_(GadgetID(Gadget.i), #LVM_ENSUREVISIBLE, LineNumber.i, 0)
  ; 
EndProcedure

; Need a unicode aware version of the API directory creator

Procedure.s MakeSureDirectoryPathExists(Directory.s)
  ; 
  ErrorCode.i = SHCreateDirectory(#Null, Directory.s)
  ; 
  Select ErrorCode.i
    Case #ERROR_SUCCESS               : Message.s ="Okay"                                           ; ResultCode = 0
    Case #ERROR_BAD_PATHNAME          : Message.s ="Bad directory path"                             ; ResultCode = 161
    Case #ERROR_FILENAME_EXCED_RANGE  : Message.s ="Directory path too long"                        ; ResultCode = 206
    Case #ERROR_FILE_EXISTS           : Message.s ="Directory already exists"                       ; ResultCode = 80
    Case #ERROR_ALREADY_EXISTS        : Message.s ="Directory already exists"                       ; ResultCode = 183
;   Case #ERROR_CANCELLED             : Message.s ="The user canceled the operation."                ; ResultCode = ??. Not defined in compiler residents
  EndSelect
  ; 
  ProcedureReturn Message.s
  ; 
  ; Debug MakeSureDirectoryPathExists("c:\1\2\3\4\5\6")
  ; 
EndProcedure

; Try to open the system database and create missing tables

Procedure OpenOtherDatabase()
  ; 
  DatabaseName.s = OpenFileRequester("Open database", Program\DatabaseName, "SQLite (*.sqlite)|*.sqlite", 0)
  ; 
  If DatabaseName.s <> #EmptyString
    ; 
    If Program\DatabaseHandle
      CloseDatabase(Program\DatabaseHandle)
    EndIf
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString, #PB_Database_SQLite)
    ; 
    If Program\DatabaseHandle <> #DatabaseOpenFail
      ; 
      Program\LastDatabase = DatabaseName.s
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "There was a problem attempting to open " + DatabaseName.s + ", could be corrupt or open by some other process.")
    EndIf 
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "User cancelled the database chooser, nothing to do.")
  EndIf
  ; 
EndProcedure

; Try to open the system database and create missing tables

Procedure OpenSystemDatabase()
  ; 
  FileHandle.i = OpenFile(#PB_Any, Program\DatabaseName)
  ; 
  If FileHandle.i
    ; 
    CloseFile(FileHandle.i)
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString, #PB_Database_SQLite)
    ; 
    If Program\DatabaseHandle <> #DatabaseOpenFail
      ; Turn on auto database vacuum
      If Not DatabaseUpdate(Program\DatabaseHandle, "PRAGMA auto_vacuum = on")                    ; 
        ;Debug "Could not setup auto database vacuum: " + DatabaseError()
      EndIf                                                                                       ; 
      ; Create the Recipes table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Recipes("                                    ; 
      DatabaseUpdate.s + "Recipetitle TEXT, "                                                     ; Title
      DatabaseUpdate.s + "Numberofservings TEXT, "                                                ; YieldText (YieldNumber not used)
      DatabaseUpdate.s + "Recipeauthor TEXT, "                                                    ; 
      DatabaseUpdate.s + "Categories TEXT, "                                                      ; PrimaryIngredient
      DatabaseUpdate.s + "Subcategories TEXT, "                                                   ; AllCategoriesText
      DatabaseUpdate.s + "Preparationtime TEXT, "                                                 ; 
      DatabaseUpdate.s + "Cookingtime TEXT, "                                                     ; 
      DatabaseUpdate.s + "Difficulty TEXT, "                                                      ; 
      DatabaseUpdate.s + "Recipeversion TEXT, "                                                   ; 
      DatabaseUpdate.s + "Recipesource TEXT, "                                                    ; Source
      DatabaseUpdate.s + "Copyright TEXT, "                                                       ; 
      DatabaseUpdate.s + "Cuisine TEXT, "                                                         ; Cuisine
      DatabaseUpdate.s + "Reciperating TEXT, "                                                    ; 
      DatabaseUpdate.s + "Importedfrom TEXT, "                                                    ; 
      DatabaseUpdate.s + "Authorcomments TEXT, "                                                  ; 
      DatabaseUpdate.s + "Instructions TEXT, "                                                    ; Instructions
      DatabaseUpdate.s + "Nutritionaldata TEXT, "                                                 ; 
      DatabaseUpdate.s + "Othercomments TEXT, "                                                   ; Notes
      DatabaseUpdate.s + "Deleted TEXT, "                                                         ; 
      DatabaseUpdate.s + "Updated TEXT, "                                                         ; 
      DatabaseUpdate.s + "Favourite TEXT, "                                                       ; 
      DatabaseUpdate.s + "Locked TEXT, "                                                          ; 
      DatabaseUpdate.s + "Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "                           ; RecipeID
      DatabaseUpdate.s + "UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)"                   ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create recipe table in database: " + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; Create the index table
      DatabaseUpdate.s = "CREATE INDEX IF NOT EXISTS RecipeIndex ON Recipes(Recipetitle)"         ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create index table in database: " + DatabaseError() ); 
      EndIf                                                                                       ; 
      ; Create the picture table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Pictures("                                   ; 
      DatabaseUpdate.s + "Pictureid INTEGER PRIMARY KEY AUTOINCREMENT, "                          ; PictureID
      DatabaseUpdate.s + "Picture BLOB, "                                                         ; 
      DatabaseUpdate.s + "Description TEXT, "                                                     ; 
      DatabaseUpdate.s + "Filename TEXT, "                                                        ; 
      DatabaseUpdate.s + "Recordid INTEGER, "                                                     ; 
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"  ; 
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                             ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, DatabaseUpdate.s  + "  --  "  + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; Create the ingredients table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Ingredients("                                ; 
      DatabaseUpdate.s + "Ingredientid INTEGER PRIMARY KEY AUTOINCREMENT, "                       ; IngredientLinesID
      DatabaseUpdate.s + "Unit TEXT, "                                                            ; TextQty
      DatabaseUpdate.s + "Measure TEXT, "                                                         ; Measure
      DatabaseUpdate.s + "Ingredient TEXT, "                                                      ; Name
      DatabaseUpdate.s + "Preparation TEXT, "                                                     ; PrepNotes
      DatabaseUpdate.s + "Lineorder TEXT, "                                                       ; LineOrder
      DatabaseUpdate.s + "Recordid INTEGER, "                                                     ; RecipeID
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"  ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create ingredients table: "  + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "There was a serious problem attempting to connect to " + #BaseName + #Version + " system database. Could be corrupt or open by some other process.")
    EndIf 
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not open or create raw database file.")
  EndIf
  ; 
EndProcedure

; Berikco's routine to properly replace single quotes with double for SQL passing

Procedure.s RepQuote(Instring.s)
  ;
  Protected.s TemporaryString.s
  ;
  For i = 1 To Len(Instring.s)
    If Mid(Instring.s, i, 1) = "'"
      TemporaryString.s = TemporaryString.s + "''"
    Else
      TemporaryString.s = TemporaryString.s + Mid(Instring.s, i, 1)
    EndIf
  Next i
  ;
  ProcedureReturn TemporaryString.s
  ; 
EndProcedure

; Replace bad characters with XML compatible ones. Thorsten Hoeppner

Procedure.s TextToXML(TextString.s)
  ; 
  XMLString.s = ReplaceString(TextString.s,     "&", "&")
  XMLString.s = ReplaceString(XMLString.s,       "<", "<")
  XMLString.s = ReplaceString(XMLString.s,       ">", ">")
  XMLString.s = ReplaceString(XMLString.s,     "'", "&apos;")
  XMLString.s = ReplaceString(XMLString.s,      "€", "&#128")
  XMLString.s = ReplaceString(XMLString.s, #DQUOTE$, """)
  XMLString.s = RemoveString(XMLString.s,  #CR$)
  XMLString.s = ReplaceString(XMLString.s, #LF$, Chr(182))
  ; 
  ProcedureReturn XMLString.s
  ; 
EndProcedure

; Strip illegal characters from text strings

Procedure.s XMLToText(XMLString.s)
  ;
  TextString.s = RemoveString(XMLString.s, #CR$)
  ;
  If Left(TextString.s, 1) = #LF$
    TextString.s = Mid(TextString.s, 2)
  EndIf
  ;
  TextString.s = ReplaceString(TextString.s, "&",          "&")
  TextString.s = ReplaceString(TextString.s, "<",           "<")
  TextString.s = ReplaceString(TextString.s, ">",           ">")
  TextString.s = ReplaceString(TextString.s, "&apos;",         "'")
  TextString.s = ReplaceString(TextString.s, """,    #DQUOTE$)
  TextString.s = ReplaceString(TextString.s, "&#128",          "€")
  TextString.s = ReplaceString(TextString.s, "'",          "'")
  TextString.s = ReplaceString(TextString.s, #LF$,    #EmptyString)
  ;TextString.s = ReplaceString(TextString.s, Chr(182), #LF$)
  TextString.s = ReplaceString(TextString.s, #CR$,    #EmptyString)
  ;
  ProcedureReturn Trim(TextString.s)
  ;
EndProcedure

; Universal, recursive search engine used by many functions

Procedure ConvertBigOvenToSqLite(CurrentFile.s)
  ; Try to configure the MDB data source via ODBC
  If SQLConfigDataSource_(0, #ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)","Server=127.0.0.1; Description=MyDescription ;DSN=ConnectMDB;DBQ=" + CurrentFile.s + ";UID=;PWD=" + Program\CrbPassword + ";")
    ; Now try to open the current MDB database
    MDBDatabaseHandle.i = OpenDatabase(#PB_Any, "ConnectMDB", #EmptyString, #EmptyString, #PB_Database_ODBC)
    ; Check to see if the database failed to open
    If MDBDatabaseHandle.i  <> #DatabaseOpenFail
      ; Create a new list to hold the recipe ID's in
      NewList RecipeId.s()
      ; Create the ID retrieval query
      DatabaseQuery.s = "SELECT RecipeID FROM Recipes"
      ; Get all the current recipe ID's from the current MDB file
      If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
        While NextDatabaseRow(MDBDatabaseHandle.i)
          AddElement(RecipeId.s())
          RecipeId.s()  = GetDatabaseString(MDBDatabaseHandle.i, 0)
        Wend
        FinishDatabaseQuery(MDBDatabaseHandle.i)
      Else 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "The database query failed: " + DatabaseError())
      EndIf
      ; Process all the current recipe ID's from the current MDB file
      If ListSize(RecipeId.s())
        ; 
        DatabaseUpdate(Program\DatabaseHandle, "Begin Transaction")
        ; Iterate each RecipeID from the list and process the wanted recipe elements
        ForEach RecipeId.s()
          DatabaseQuery.s = "SELECT Title, YieldText, Instructions, Source, "
          DatabaseQuery.s + "YieldNumber, PrimaryIngredient, Cuisine, "
          DatabaseQuery.s + "PictureFile1, Notes, AllCategoriesText "
          DatabaseQuery.s + "FROM Recipes WHERE RecipeID = " + RecipeId.s()  + ""
          If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
            While NextDatabaseRow(MDBDatabaseHandle.i) <> 0
              Title.s             = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 0))
              YieldText.s         = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 1))
              Instructions.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 2))
              Source.s            = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 3))
              YieldNumber.s       = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 4))
              PrimaryIngredient.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 5))
              Cuisine.s           = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 6))
              PictureFile1.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 7))
              Notes.s             = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 8))
              AllCategoriesText.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 9))
              AllCategoriesText.s = ReplaceString(AllCategoriesText.s, "|", ",", #PB_String_NoCase, 1)
              AllCategoriesText.s = Trim(AllCategoriesText.s, ",")
              Updated.s           = FormatDate("%yyyy-%mm-%dd", Date()) ; Standard ISO date format
              ; Save the recipe item into the SQLite database
              DatabaseUpdate.s  = "INSERT INTO Recipes("
              DatabaseUpdate.s  + "Recipetitle, Numberofservings, Recipeauthor, Categories, "
              DatabaseUpdate.s  + "Subcategories, Preparationtime, Cookingtime, Difficulty, "
              DatabaseUpdate.s  + "Recipeversion, Recipesource, Copyright, Cuisine, "
              DatabaseUpdate.s  + "Reciperating, Importedfrom, Authorcomments, Instructions, "
              DatabaseUpdate.s  + "Nutritionaldata, Othercomments, Deleted, Updated, "
              DatabaseUpdate.s  + "Favourite, Locked) "
              DatabaseUpdate.s  + "VALUES("
              DatabaseUpdate.s  + "'"  + RepQuote(Title.s)              + "', " ; Recipetitle
              DatabaseUpdate.s  + "'"  + RepQuote(YieldText.s)          + "', " ; Numberofservings
              DatabaseUpdate.s  + "'"  +                                  "', " ; Recipeauthor
              DatabaseUpdate.s  + "'"  + RepQuote(PrimaryIngredient.s)  + "', " ; Categories
              DatabaseUpdate.s  + "'"  + RepQuote(AllCategoriesText.s)  + "', " ; SubCategoriesText
              DatabaseUpdate.s  + "'"  +                                  "', " ; Preparationtime
              DatabaseUpdate.s  + "'"  +                                  "', " ; Cookingtime
              DatabaseUpdate.s  + "'"  +                                  "', " ; Difficulty
              DatabaseUpdate.s  + "'"  +                                  "', " ; Recipeversion
              DatabaseUpdate.s  + "'"  + RepQuote(Source.s)             + "', " ; Recipesource
              DatabaseUpdate.s  + "'"  + RepQuote(Copyright.s)          + "', " ; Copyright
              DatabaseUpdate.s  + "'"  + RepQuote(Cuisine.s)            + "', " ; Cuisine
              DatabaseUpdate.s  + "'"  +                                  "', " ; Reciperating
              DatabaseUpdate.s  + "'"  +          "BigOven"             + "', " ; Importedfrom
              DatabaseUpdate.s  + "'"  +                                  "', " ; Authorcomments
              DatabaseUpdate.s  + "'"  + RepQuote(Instructions.s)       + "', " ; Instructions
              DatabaseUpdate.s  + "'"  +                                  "', " ; Nutritionaldata
              DatabaseUpdate.s  + "'"  + RepQuote(Notes.s)              + "', " ; Othercomments
              DatabaseUpdate.s  + "'"  + "0"                            + "', " ; Deleted
              DatabaseUpdate.s  + "'"  +          Updated.s             + "', " ; Updated
              DatabaseUpdate.s  + "'"  + "0"                            + "', " ; Favourite
              DatabaseUpdate.s  + "'"  + "0"                            + "')"  ; Locked
              If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                LastRecordNumberInserted.s = DatabaseLastInsertRowId()
                If LastRecordNumberInserted.s
                  Program\Numberoftitles  + 1
                  AddGadgetItem(#Gadget_BigOvenToSqLite_TItlelist, #AtTheEndOfTheList, Title.s)
                  LastLine(#Gadget_BigOvenToSqLite_TItlelist, Program\Numberoftitles - 1)
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberoftitles, "Recipes found: "  + Str(Program\Numberoftitles))                  
                  While WindowEvent() : Wend
                  ; Process the picture into its own table
                  If PictureFile1.s <> #EmptyString
                    Program\Numberofpictures  + 1
                    SetGadgetText(#Gadget_BigOvenToSqLite_Numberofpictures, "Pictures found: "  + Str(Program\Numberofpictures))                  
                    PictureName.s = GetPathPart(CurrentFile.s)  + "Pictures\" + PictureFile1.s
                    PictureFileIn.i = ReadFile(#PB_Any, PictureName.s)
                    If PictureFileIn.i
                      FileInSize.i = Lof(PictureFileIn.i)
                      *Buffer = AllocateMemory(FileInSize.i)
                      If *Buffer
                        If ReadData(PictureFileIn.i, *Buffer, FileInSize.i) = FileInSize.i
                          SetDatabaseBlob(Program\DatabaseHandle, 0, *Buffer, FileInSize.i)
                          DatabaseUpdate.s  = "INSERT INTO Pictures("                 ; 
                         ;DatabaseUpdate.s + "Pictureid, "                            ; Pictureid
                          DatabaseUpdate.s + "Picture, "                              ; Picture
                          DatabaseUpdate.s + "Description, "                          ; Description
                          DatabaseUpdate.s + "Filename, "                             ; Filename
                          DatabaseUpdate.s + "Recordid) "                             ; Recordid
                          DatabaseUpdate.s + "VALUES("                                ; 
                         ;DatabaseUpdate.s + "Pictureid, "                            ; Pictureid
                          DatabaseUpdate.s + "?, "                                    ; Picture
                          DatabaseUpdate.s + "'', "                                   ; Description
                          DatabaseUpdate.s + "'" + RepQuote(PictureFile1.s)  + "', "  ; Filename
                          DatabaseUpdate.s + "'" + LastRecordNumberInserted.s + "')"  ; Recordid
                          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                            NewPictureRecord.s = DatabaseLastInsertRowId()
                            If NewPictureRecord.s <> #EmptyString
                              ; Tell the user something here
                            Else
                              SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not get a new record number for picture data")
                            EndIf
                          Else
                            SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not save the picture data into the database")
                          EndIf
                        Else
                          SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not read the picture data into the buffer")
                        EndIf
                        FreeMemory(*Buffer)
                      Else
                        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not allocate a buffer for the picture data")
                      EndIf
                      CloseFile(PictureFileIn.i)
                    Else
                      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not open the designated file to read from")
                    EndIf
                  Else
                    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No picture for this record")
                  EndIf
                Else
                  SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Failed to get a new database record ID")
                EndIf
              Else
                SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "The database update failed: "  + DatabaseError())
              EndIf
              ; 
            Wend
            FinishDatabaseQuery(MDBDatabaseHandle.i)
            ; Process the ingredients for this recipe
            If LastRecordNumberInserted.s
              DatabaseQuery.s = "SELECT TextQty, Measure, Name, PrepNotes, LineOrder "
              DatabaseQuery.s + "FROM IngredientLines "
              DatabaseQuery.s + "WHERE RecipeID = "    + RecipeId.s()  + ""
              If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
                While NextDatabaseRow(MDBDatabaseHandle.i)
                  Program\Numberofingredients  + 1
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberofingredients, "Ingredients found: "  + Str(Program\Numberofingredients))                  
                  TextQty.s   = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 0))
                  Measure.s   = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 1))
                  Name.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 2))
                  PrepNotes.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 3))
                  LineOrder.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 4))
                  ; 
                  DatabaseUpdate.s = "INSERT INTO Ingredients("                       ; 
                 ;DatabaseUpdate.s + "Ingredientid, "                                 ; IngredientLinesID
                  DatabaseUpdate.s + "Unit, "                                         ; TextQty
                  DatabaseUpdate.s + "Measure, "                                      ; Measure
                  DatabaseUpdate.s + "Ingredient, "                                   ; Name
                  DatabaseUpdate.s + "Preparation, "                                  ; PrepNotes
                  DatabaseUpdate.s + "Lineorder, "                                    ; LineOrder
                  DatabaseUpdate.s + "Recordid) "                                     ; RecipeID
                  DatabaseUpdate.s + "VALUES("                                        ; 
                  DatabaseUpdate.s + "'" + RepQuote(TextQty.s)        + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(Measure.s)        + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(Name.s)           + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(PrepNotes.s)      + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(LineOrder.s)      + "', "         ; 
                  DatabaseUpdate.s + "'" + LastRecordNumberInserted.s + "')"          ; 
                  If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                  Else
                    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not insert the current ingredient into the database")
                  EndIf
                  ; 
                Wend
                FinishDatabaseQuery(MDBDatabaseHandle.i)
              EndIf
            Else
              SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No last record number created")
            EndIf
            ; 
          Else 
            SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "The database query failed: " + DatabaseError())
          EndIf
          ; 
        Next ;RecipeID()  
        ; 
        DatabaseUpdate(Program\DatabaseHandle, "Commit")
        ; 
      Else
        ; The list is empty, nothing to process
      EndIf
      ; 
      CloseDatabase(MDBDatabaseHandle.i)
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No MDB database handle: " + DatabaseError())
    EndIf
    ; 
    SQLConfigDataSource_(0, #ODBC_REMOVE_DSN, "Microsoft Access Driver (*.mdb)","Server=127.0.0.1; Description=MyDescription ;DSN=ConnectMDB;DBQ=" + CurrentFile.s + ";UID=;PWD=" + Program\CrbPassword + ";")
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not configure MDB data source: " + DatabaseError())
  EndIf
  ; 
EndProcedure

; 

Procedure GetConvertPath()
  ; 
  ConvertPath.s = PathRequester("CRB convert path", Program\CurrentDirectory)
  ; 
  If ConvertPath.s <> #EmptyString
    SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath, ConvertPath.s)
    Program\LastConvertPath = ConvertPath.s
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "[ No BigOven CRB path set yet? ]")
  EndIf
  ; 
EndProcedure

; Universal, recursive search engine used by many functions

Procedure SearchEngine(SearchDirectory.s)
  ; 
  If SearchDirectory.s  <> "[ No BigOven CRB path set yet? ]"
    ; 
    ClearGadgetItems(#Gadget_BigOvenToSqLite_Recipeboxes)
    Program\Numberofrecipeboxes = 0
    SetGadgetText(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Boxes found: "  + Str(Program\Numberofrecipeboxes))
    ; 
    ClearGadgetItems(#Gadget_BigOvenToSqLite_TItlelist)
    Program\Numberoftitles = 0
    SetGadgetText(#Gadget_BigOvenToSqLite_Numberoftitles, "Recipes found: "  + Str(Program\Numberofrecipeboxes))
    ; 
    NewList FoundDirectories.s()
    ; 
    If SearchDirectory.s <> #EmptyString
      If Right(SearchDirectory.s, 1) = "\"
        SearchDirectory.s = Left(SearchDirectory.s, Len(SearchDirectory.s) - 1)
      EndIf
      AddElement(FoundDirectories.s())
      FoundDirectories.s() = SearchDirectory.s
      IndexLevel.i = 0
      Repeat
        SelectElement(FoundDirectories.s(), IndexLevel.i)
        If ExamineDirectory(0, FoundDirectories.s(), "*.*")
          Path.s = FoundDirectories.s() + "\"
          While NextDirectoryEntry(0)
            Filename.s = DirectoryEntryName(0)
            Select DirectoryEntryType(0)
              Case 1
                ; 
                If LCase(GetExtensionPart(Path.s + Filename.s)) = "crb"
                  Program\Numberofrecipeboxes + 1
                  AddGadgetItem(#Gadget_BigOvenToSqLite_Recipeboxes, #AtTheEndOfTheList,  Filename.s)
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Boxes found: "  + Str(Program\Numberofrecipeboxes))
                  While WindowEvent() : Wend
                  ConvertBigOvenToSqLite(Path.s + Filename.s)
                EndIf
                ; 
              Case 2
                Filename.s = DirectoryEntryName(0)
                If Filename.s <> ".." And Filename.s <> "."
                  AddElement(FoundDirectories())
                  FoundDirectories() = Path + Filename.s
                EndIf
            EndSelect
          Wend
        EndIf
        IndexLevel.i + 1
      Until IndexLevel.i > ListSize(FoundDirectories()) -1
    EndIf
    ; 
  EndIf
  ; 
EndProcedure

; Main program body

If Window_BigOvenToSqLite()
  ; 
  If FileSize(Program\Configfile) <> #NoFileFound                           ; Config file was found
    ConfigFile.i = ReadFile(#PB_Any, Program\Configfile)
    If ConfigFile.i <> #NoFileHandle
      Program\LastDatabase    = ReadString(ConfigFile.i,  #PB_Ascii)
      SetGadgetText(#Gadget_BigOvenToSqLite_Databasename, Program\LastDatabase)
      Program\LastConvertPath = ReadString(ConfigFile.i,  #PB_Ascii)
      SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath,  Program\LastConvertPath)
      CloseFile(ConfigFile.i)
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Config file read, BigOven converter ready to work...")
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Couldn't open up the config file")
    EndIf
  ElseIf FileSize(Program\Configfile) = #NoFileFound
    OpenSystemDatabase()
    SetGadgetText(#Gadget_BigOvenToSqLite_Databasename, Program\DatabaseName)
    Program\LastDatabase    = Program\DatabaseName
    SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath,  "[ No BigOven CRB path set yet? ]")
    Program\LastConvertPath = "[ No BigOven CRB path set yet? ]"
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,    "No config file, default database opened, ready to work")
  EndIf
  ; 
  Program\QuitValue = #False
  ; 
  Repeat
    EventID  = WaitWindowEvent()
    MenuID   = EventMenu()
    GadgetID = EventGadget()
    WindowID = EventWindow()
    Select EventID
      Case #PB_Event_CloseWindow
        Select WindowID
        Case  #Window_BigOvenToSqLite                 : Program\QuitValue = #True
        EndSelect
      Case #PB_Event_Gadget
        Select GadgetID
          Case  #Gadget_BigOvenToSqLite_Getdatabase   : OpenOtherDatabase()
          Case  #Gadget_BigOvenToSqLite_Getpath       : GetConvertPath()
          Case  #Gadget_BigOvenToSqLite_Convert       : SearchEngine(Program\ConvertPath)
          Case  #Gadget_BigOvenToSqLite_Exitprogram   : Program\QuitValue = #True
        EndSelect
    EndSelect
  Until Program\QuitValue = #True
  ; 
  CloseWindow(#Window_BigOvenToSqLite)
  ; 
  ConfigFile.i = CreateFile(#PB_Any, Program\Configfile)
  If ConfigFile.i <> #NoFileHandle
    WriteStringN(ConfigFile.i, Program\LastDatabase,    #PB_Ascii)
    WriteStringN(ConfigFile.i, Program\LastConvertPath, #PB_Ascii)
    CloseFile(ConfigFile.i)
  Else
    Debug "Couldn't open up the config file"
  EndIf
  ; 
EndIf
; 
End

Code: Select all

; Copyrights, thank yous etc
; 
; ***Program***   
; ***License***   
; ***Credits***   
;                 
; Recipe structures in OpenSystemDatabase and their equivalent in DATA.XML from the FDXZ file
; 
; My recipes table	    RecipeML XML table
; 
; 
; Recipetitle		        Recipe Name
; Numberofservings	    Servings
; Recipeauthor
; Categories		        RecipeTypes
; Subcategories
; Preparationtime	      PreparationTime
; Cookingtime		        CookingTime
; Difficulty
; Recipeversion
; Recipesource		      Source
; Copyright		          Copyright
; Reciperating
; Importedfrom		      WebPage
; Authorcomments		    Comments
; Instructions		      ProcedureText		  ;  Multiple lines of procedure text from source xml
; Nutritionaldata	      RecipeNutrition		;  Multiple lines. All items to be extracted with headings, one per line
; Othercomments		      RecipeTips
; Deleted		            0
; Updated		            CreateDate
; Favourite		          0
; Locked			          0
; Recordid
; 
; 
; My ingredients table	recipe filename.XML table
; 
; 
; Ingredientid
; Unit			            MeasureQuantity
; Measure		            Measure
; Ingredient		        Ingredient
; Preparation		        IngredientName
; Lineorder
; Recordid
; 
; 
; My pictures table	    XML recipe file
; 
; 
; Pictureid
; Picture		            FileName    ; Base64Encoded inside uncompressed XML file
; Recordid
;
; 

EnableExplicit                                                                                       ; 

; Set the system to use SQLite database handling

UseSQLiteDatabase()                                                                                    ; Use the SQLite database backend

; 

; Main modules

Declare.s RecipeCRLFSpaceTrim(RecipeString.s)
Declare   OpenSystemDatabase()
Declare.s KillQuote(Instring.s)
Declare.s RepQuote(Instring.s)
Declare.s XMLToText(XMLString.s)
Declare.s TextToXML(TextString.s)

; Import modules

Declare   RequestRecipeML()
Declare   ImportRecipeMLFile(ImportFileName.s)
Declare   ImportRecipeMLFile_XML(ImportFileName.s, *CurrentNode, CurrentSublevel.i)
Declare   SaveRecipe(ImportFileName.s)

; Program constants that don't need to change. Wordier to allow easier error tracking

#ListIconHeadingArea            = -1                                                                  ; 

#AtTheEndOfTheList              = -1                                                                  ; 

#NoItems                        = 0                                                                   ; 
#FirstItem                      = 0                                                                   ; 

#NothingFound                   = 0                                                                   ; 
#NothingSelected                = -1                                                                  ; 

#NoDataFound                    = 0                                                                   ; 
#NoBlobFound                    = 0                                                                   ; 
#NoPictureHandle                = 0                                                                   ; 
#NoPictureFound                 = -1                                                                  ; 
#NoPictureLoaded                = 0                                                                   ; 
#NoPictureResized               = 0                                                                   ; 

#NoColumnIcon                   = 0                                                                   ; 
#NoData                         = 0                                                                   ; 
#NoCurrentLine                  = -1                                                                  ; Make sure no line can be selected accidentally
#NoRecordNumber                 = ""                                                                  ; 
#EmptyString                    = ""                                                                  ; 

#RecipeDeleted                  = 1                                                                   ; 
#RecipeLocked                   = 1                                                                   ; 
#RecipeFavourite                = 1                                                                   ; 

#DatabaseNotOpen                = 0                                                                   ; 
#DatabaseQueryFail              = 0                                                                   ; 
#DatabaseUpdateFail             = 0                                                                   ; 

#NoFileHandle                   = 0                                                                   ; 
#NotEndOfFile                   = 0                                                                   ; 
#FileIsEmpty                    = 0                                                                   ; 
#FileNotDecompressed            = -1                                                                  ; 
#FileIsDirectory                = -2                                                                  ; 

#DirectoryNotCreated            = 0                                                                   ; 

#NoPrintFileHandle              = 0                                                                   ; 
#NoImportFileHandle             = 0                                                                   ; 
#NoExportFileHandle             = 0                                                                   ; 

#NoMenuCreated                  = 0                                                                   ; 

#TitleBarClockTimerEvent        = 624                                                                 ; Title bar clock timer event

#Fish                           = "<°)))o><²³  "                                                      ; Beware the fish!!

#PictureBufferSize        = 10000000

; Program data variables

Structure ProgramData
  CurrentDirectory.s
  TemporaryDirectory.s
  DatabaseHandle.i
  Databasename.s
EndStructure

; Picture data structure

Structure PictureData
  FileType.s
  FileName.s
  OriginalFileName.s
  Description.s
  *PictureBuffer
  PictureBufferPtr.i
EndStructure

; Ingredient table data structure

Structure IngredientData
; Ingredientid.s  Auto increment field during recipe save
  Unit.s
  Measure.s
  Ingredient.s
  Preparation.s
  LineOrder.s
  RecipeId.s
EndStructure

; Recipe table data structure

Structure RecipeData
  Recipetitle.s
  Numberofservings.s
  Recipeauthor.s
  Categories.s
  Subcategories.s
  Preparationtime.s
  Cookingtime.s
  Difficulty.s
  Recipeversion.s
  Recipesource.s
  Copyright.s
  Reciperating.s
  Importedfrom.s
  Authorcomments.s
  Instructions.s
  Nutritionaldata.s
  Othercomments.s
  Deleted.s
  Updated.s
  Favourite.s
  Locked.s
  RecordId.s
  List IngredientList.IngredientData()
  List PictureList.PictureData()
EndStructure

; Global declarations

Global Program.ProgramData
Global Recipe.RecipeData

; Current dirrectory reference for file use

Program\CurrentDirectory    = GetCurrentDirectory()
Program\TemporaryDirectory  = GetTemporaryDirectory()

; Create the database name that I am going to use

Program\DatabaseName        = Program\CurrentDirectory + "RecipeML recipe file test.sqlite"

; Clear the recipe structure in between recipe reads

Procedure ClearRecipeStructure()
  Recipe\Recipetitle.s      = #EmptyString
  Recipe\Numberofservings.s = #EmptyString
  Recipe\Recipeauthor.s     = #EmptyString
  Recipe\Categories.s       = #EmptyString
  Recipe\Subcategories.s    = #EmptyString
  Recipe\Preparationtime.s  = #EmptyString
  Recipe\Cookingtime.s      = #EmptyString
  Recipe\Difficulty.s       = #EmptyString
  Recipe\Recipeversion.s    = #EmptyString
  Recipe\Recipesource.s     = #EmptyString
  Recipe\Copyright.s        = #EmptyString
  Recipe\Reciperating.s     = #EmptyString
 ;Recipe\Importedfrom.s     = #EmptyString  ; This field needs to persis for the current file session
  Recipe\Authorcomments.s   = #EmptyString
  Recipe\Instructions.s     = #EmptyString
  Recipe\Nutritionaldata.s  = #EmptyString
  Recipe\Othercomments.s    = #EmptyString
  Recipe\Deleted.s          = #EmptyString
  Recipe\Updated.s          = #EmptyString
  Recipe\Favourite.s        = #EmptyString
  Recipe\Locked.s           = #EmptyString
  Recipe\RecordId.s         = #EmptyString
  ClearList(Recipe\IngredientList())
  ForEach Recipe\PictureList()
    FreeMemory(Recipe\PictureList()\PictureBuffer)
  Next
  ClearList(Recipe\PictureList())
EndProcedure

; Trim additional CR LF and spaces in front of input lines, InfraTec/Bernd

Procedure.s RecipeCRLFSpaceTrim(RecipeString.s)
  ; 
  RecipeString.s = Trim(RecipeString.s)           ; Trim spaces at the front and the back
  RecipeString.s = Trim(RecipeString.s, #CR$)     ; Trim extra carriage returns at the front and the back
  RecipeString.s = Trim(RecipeString.s, #LF$)     ; Trim extra line feeds at the front and the back
  RecipeString.s = Trim(RecipeString.s, #CR$)     ; Trim Extra carriage returns at the front and the back
  RecipeString.s = Trim(RecipeString.s)           ; Trim spaces at the front and the back
  ; 
  ProcedureReturn RecipeString.s
  ; 
EndProcedure 

; 

Procedure.s KillQuote(Instring.s)
  ProcedureReturn ReplaceString(Instring.s, "''", "'", 1, 1)
EndProcedure

; Berikco's routine to properly replace single quotes with double for SQL passing

Procedure.s RepQuote(Instring.s)
  Protected i.i, tmp.s
  For i = 1 To Len(Instring.s)
    If Mid(Instring.s, i, 1) = "'"
      tmp.s = tmp.s + "''"
    Else
      tmp.s = tmp.s + Mid(Instring.s, i, 1)
    EndIf
  Next i
  ;
  ProcedureReturn tmp.s
  ; 
EndProcedure

; Replace bad characters with XML compatible ones. Thorsten Hoeppner

Procedure.s TextToXML(TextString.s)
  ; 
  Protected XMLString.s 
  ; 
  XMLString.s = ReplaceString(TextString.s,      "&", "&")
  XMLString.s = ReplaceString(XMLString.s,        "<", "<")
  XMLString.s = ReplaceString(XMLString.s,        ">", ">")
  XMLString.s = ReplaceString(XMLString.s,      "'", "&apos;")
  XMLString.s = ReplaceString(XMLString.s,       "€", "&#128")
  XMLString.s = ReplaceString(XMLString.s, #DQUOTE$, """)
  XMLString.s = RemoveString(XMLString.s,  #CR$)
  XMLString.s = ReplaceString(XMLString.s, #LF$,     Chr(182))
  ; 
  ProcedureReturn XMLString.s
  ; 
EndProcedure

; Strip illegal characters from text strings

Procedure.s XMLToText(XMLString.s)
  ; 
  Protected TextString.s 
  ;
  TextString.s = RemoveString(XMLString.s, #CR$)
  ;
  If Left(TextString.s, 1) = #LF$
    TextString.s = Mid(TextString.s, 2)
  EndIf
  ;
  TextString.s = ReplaceString(TextString.s, "&",       "&")
  TextString.s = ReplaceString(TextString.s, "<",        "<")
  TextString.s = ReplaceString(TextString.s, ">",        ">")
  TextString.s = ReplaceString(TextString.s, "&apos;",      "'")
  TextString.s = ReplaceString(TextString.s, """,  #DQUOTE$)
  TextString.s = ReplaceString(TextString.s, "&#128",       "€")
  TextString.s = ReplaceString(TextString.s, "'",       "'")
  TextString.s = ReplaceString(TextString.s, #LF$, #EmptyString)
 ;TextString.s = ReplaceString(TextString.s, Chr(182),    #LF$))
  TextString.s = ReplaceString(TextString.s, #CR$, #EmptyString)
  ;
  ProcedureReturn Trim(TextString.s)
  ;
EndProcedure

; 

Procedure OpenSystemDatabase()
  Protected FileHandle.i, DatabaseUpdate.s
  ; Create the database if it doesn't already exist.
  FileHandle.i = OpenFile(#PB_Any, Program\DatabaseName)
  ; 
  If FileHandle.i
    ; 
    CloseFile(FileHandle.i)
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString)
    ; 
    If Program\DatabaseHandle
      ; Turn on auto database vacuum
      If Not DatabaseUpdate(Program\DatabaseHandle, "PRAGMA auto_vacuum = on")
      EndIf
      ; Write the Recipes table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Recipes("
      DatabaseUpdate.s + "Recipetitle TEXT, "
      DatabaseUpdate.s + "Numberofservings TEXT, "
      DatabaseUpdate.s + "Recipeauthor TEXT, "
      DatabaseUpdate.s + "Categories TEXT, "
      DatabaseUpdate.s + "Subcategories TEXT, "
      DatabaseUpdate.s + "Preparationtime TEXT, "
      DatabaseUpdate.s + "Cookingtime TEXT, "
      DatabaseUpdate.s + "Difficulty TEXT, "
      DatabaseUpdate.s + "Recipeversion TEXT, "
      DatabaseUpdate.s + "Recipesource TEXT, "
      DatabaseUpdate.s + "Copyright TEXT, "
      DatabaseUpdate.s + "Reciperating TEXT, "
      DatabaseUpdate.s + "Importedfrom TEXT, "
      DatabaseUpdate.s + "Authorcomments TEXT, "
      DatabaseUpdate.s + "Instructions TEXT, "
      DatabaseUpdate.s + "Nutritionaldata TEXT, "
      DatabaseUpdate.s + "Othercomments TEXT, "
      DatabaseUpdate.s + "Deleted TEXT, "
      DatabaseUpdate.s + "Updated TEXT, "
      DatabaseUpdate.s + "Favourite TEXT, "
      DatabaseUpdate.s + "Locked TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)"
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Create the index on Recipes table
      DatabaseUpdate.s = "CREATE INDEX IF NOT EXISTS Recipesindex ON Recipes("
      DatabaseUpdate.s + "Recipetitle, "
      DatabaseUpdate.s + "Instructions)"
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Write the Pictures table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Pictures("
      DatabaseUpdate.s + "Pictureid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "Picture BLOB, "
      DatabaseUpdate.s + "Description TEXT, "
      DatabaseUpdate.s + "FileName TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER, "
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"     
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Write the ingredients table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Ingredients("
      DatabaseUpdate.s + "Ingredientid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "Unit TEXT, "
      DatabaseUpdate.s + "Measure TEXT, "
      DatabaseUpdate.s + "Ingredient TEXT, "
      DatabaseUpdate.s + "Preparation TEXT, "
      DatabaseUpdate.s + "Lineorder TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER, "
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"     
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; 
    Else
      MessageRequester("Database open error", "Could not open database file in database mode.", #PB_MessageRequester_Ok)
    EndIf
    ; 
  Else
    ; 
    MessageRequester("Database open error", "Could not open or create raw database file.", #PB_MessageRequester_Ok)
    ; 
  EndIf
  ; 
EndProcedure

; Multiple file import

Procedure.i RequestRecipeML()
  ; 
  Protected ResultFlag.i, RecipeName.s, PackHandle.i
  ; Ask the user for a standard Recipeml XML recipe file
  RecipeName.s = OpenFileRequester("Select recipe file", GetCurrentDirectory(), "Recipeml (*.Recipeml)|*.Recipeml", 0, #PB_Requester_MultiSelection)
  ; If the user didn't cancel, do the code below
  If RecipeName.s <> #EmptyString
    ; 
    DatabaseUpdate(Program\DatabaseHandle, "BEGIN TRANSACTION")
    ; 
    While RecipeName.s 
      ; Check to see if the recipe is valid and undamaged
      If FileSize(RecipeName.s) <> #FileIsEmpty
        ; 
        ImportRecipeMLFile(RecipeName.s)
        ;         If DeleteFile(RecipeName.s, #PB_FileSystem_Force) <> 0
        ;         Else
        ;           Debug "Could not delete the processed recipe"
        ;         EndIf
      Else
        ; Debug "The selected file in the requester was not found on disk?"
      EndIf
      ; 
      RecipeName.s = NextSelectedFileName() 
      ; 
    Wend
    ; 
    DatabaseUpdate(Program\DatabaseHandle, "COMMIT")
    ; 
  Else
    ; Debug "Nothing to do as the user cancelled the operation"
  EndIf
  ; 
  ProcedureReturn ResultFlag
  ; 
EndProcedure

; Import a RecipeML XML file format

Procedure ImportRecipeMLFile(ImportFileName.s)
  ; 
  Protected.i XML.i, RecipeMLFile.i
  Protected.q RecipeMLSize
  Protected   *Buffer, *Node
  ; 
  RecipeMLFile.i = ReadFile(#PB_Any, ImportFilename.s)
  ; 
  If RecipeMLFile.i
    ;
    RecipeMLSize.q = Lof(RecipeMLFile.i)
    *Buffer = AllocateMemory(RecipeMLSize.q)
    If *Buffer
      If ReadData(RecipeMLFile.i, *Buffer, RecipeMLSize.q) = RecipeMLSize.q
        XML = CatchXML(#PB_Any, *Buffer, RecipeMLSize.q)
        If XML
          *Node = MainXMLNode(XML)
          If *Node
            ImportRecipeMLFile_XML(ImportFileName.s, *Node, 0)
          EndIf
          FreeXML(XML)
          Recipe\Categories   = Trim(Recipe\Categories,  ",")
          SaveRecipe(ImportFileName.s)
          ; 
          ClearRecipeStructure()
          ; 
        EndIf
      EndIf
      ; 
      FreeMemory(*Buffer)
      ; 
    EndIf
    ; 
    CloseFile(RecipeMLFile.i)
    ;
  Else
    ; Debug "File wasn't opened, no there is file handle."
  EndIf
  ; 
EndProcedure

; 

Procedure.i ImportRecipeMLFile_XML(ImportFileName.s, *CurrentNode, CurrentSublevel.i)
  ; 
  Protected NodeName.s, *ChildNode, AttributeName.s, AttributeValue.s
  ; 
  If XMLNodeType(*CurrentNode) = #PB_XML_Normal
    ; 
    NodeName.s = GetXMLNodeName(*CurrentNode)
    ; 
    ; Debug NodeName.s
    ; 
    If NodeName.s               = "title"
      ; 
      Recipe\Recipetitle.s      = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))
      Recipe\Recipetitle.s      = ReplaceString(Recipe\Recipetitle.s, "'", "''")                            ;: Debug "Title seems okay: "  + Recipe\Recipetitle
      ; 
    ElseIf NodeName.s           = "cat"
      ; 
      Recipe\Categories.s       + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + ","        ;: Debug Recipe\Categories
      Recipe\Categories.s       = ReplaceString(Recipe\Categories.s, "'", "''")
      ; 
    ElseIf NodeName.s           = "yield"
      ; 
      Recipe\Numberofservings.s + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))              ;: Debug Recipe\Numberofservings
      ; 
      ElseIf NodeName.s         = "qty"
        AddElement(Recipe\IngredientList())
        Recipe\IngredientList()\Unit        = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Unit
      ElseIf NodeName.s         = "unit"
        Recipe\IngredientList()\Measure     = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Measure
      ElseIf NodeName.s         = "item"
        Recipe\IngredientList()\Ingredient  = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Ingredient
        Recipe\IngredientList()\Ingredient  = ReplaceString(Recipe\IngredientList()\Ingredient, "'", "''")
        ; 
    ElseIf NodeName.s           = "step"
      ; 
      Recipe\Instructions.s     + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + #LF$         ;: Debug Recipe\Instructions
      Recipe\Instructions.s     = ReplaceString(Recipe\Instructions.s, "'", "''")
      ; 
    EndIf
    ; 
    *ChildNode                  = ChildXMLNode(*CurrentNode)
    ; 
    While *ChildNode <> 0
      ImportRecipeMLFile_XML(ImportFileName.s, *ChildNode, CurrentSublevel + 1)
      *ChildNode = NextXMLNode(*ChildNode)
    Wend   
    ; To make sure to save the last record and free the picture memory if any
    If *ChildNode = 0 And CurrentSublevel = 0
      If Len(Recipe\Recipetitle)  <> 0
        SaveRecipe(ImportFileName.s)  ; Save the filled fields of the recipe structure
        ClearRecipeStructure()
      EndIf
    EndIf    
    ; 
  EndIf
  ; 
EndProcedure

; 

Procedure SaveRecipe(ImportFileName.s)
  Protected DatabaseUpdate.s, FileIn.i, FileInSize, *Buffer
  ; Only proceed if the title field at least has something in it
  If Len(Recipe\Recipetitle) <> 0
    Recipe\Deleted  = "0"
    Recipe\Locked   = "0"
    DatabaseUpdate.s = "INSERT INTO Recipes("
    DatabaseUpdate.s + "Recipetitle, "
    DatabaseUpdate.s + "Numberofservings, "
    DatabaseUpdate.s + "Recipeauthor, "
    DatabaseUpdate.s + "Categories, "
    DatabaseUpdate.s + "Subcategories, "
    DatabaseUpdate.s + "Preparationtime, "
    DatabaseUpdate.s + "Cookingtime, "
    DatabaseUpdate.s + "Difficulty, "
    DatabaseUpdate.s + "Recipeversion, "
    DatabaseUpdate.s + "Recipesource, "
    DatabaseUpdate.s + "Copyright, "
    DatabaseUpdate.s + "Reciperating, "
    DatabaseUpdate.s + "Importedfrom, "
    DatabaseUpdate.s + "Authorcomments, "
    DatabaseUpdate.s + "Instructions, "
    DatabaseUpdate.s + "Nutritionaldata, "
    DatabaseUpdate.s + "Othercomments, "
    DatabaseUpdate.s + "Deleted, "
    DatabaseUpdate.s + "Updated, "
    DatabaseUpdate.s + "Favourite, "
    DatabaseUpdate.s + "Locked)"
    DatabaseUpdate.s + "VALUES("
    DatabaseUpdate.s + "'" +  Recipe\Recipetitle      + "', "
    DatabaseUpdate.s + "'" +  Recipe\Numberofservings + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipeauthor     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Categories       + "', "
    DatabaseUpdate.s + "'" +  Recipe\Subcategories    + "', "
    DatabaseUpdate.s + "'" +  Recipe\Preparationtime  + "', "
    DatabaseUpdate.s + "'" +  Recipe\Cookingtime      + "', "
    DatabaseUpdate.s + "'" +  Recipe\Difficulty       + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipeversion    + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipesource     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Copyright        + "', "
    DatabaseUpdate.s + "'" +  Recipe\Reciperating     + "', "
   ;DatabaseUpdate.s + "'" +  Recipe\Importedfrom     + "', "
    DatabaseUpdate.s + "'" +  "RecipeML"              + "', "
    DatabaseUpdate.s + "'" +  Recipe\Authorcomments   + "', "
    DatabaseUpdate.s + "'" +  Recipe\Instructions     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Nutritionaldata  + "', "
    DatabaseUpdate.s + "'" +  Recipe\Othercomments    + "', "
    DatabaseUpdate.s + ""  +  Recipe\Deleted          + "', "
    DatabaseUpdate.s + "'" +  Recipe\Updated          + "', "
    DatabaseUpdate.s + "'" +  Recipe\Favourite        + "', "
    DatabaseUpdate.s + ""  +  Recipe\Locked           + ")"
    ; Now update the database with the new record
    If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
      ; Get the new record number if any
      If DatabaseQuery(Program\DatabaseHandle, "SELECT last_insert_rowid()")
        If NextDatabaseRow(Program\DatabaseHandle)
          Recipe\Recordid = GetDatabaseString(Program\DatabaseHandle, 0)
        EndIf
      EndIf
      ; Proceed if we got a new record number
      If Len(Recipe\Recordid)
        ForEach Recipe\IngredientList()
          DatabaseUpdate.s  = "INSERT INTO Ingredients("
          DatabaseUpdate.s  + "Unit, "
          DatabaseUpdate.s  + "Measure, "
          DatabaseUpdate.s  + "Ingredient, "
          DatabaseUpdate.s  + "Preparation, "
          DatabaseUpdate.s  + "Lineorder, "
          DatabaseUpdate.s  + "Recordid) "
          DatabaseUpdate.s  + "VALUES("
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Unit            + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Measure         + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Ingredient      + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Preparation     + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Lineorder       + "', "
          DatabaseUpdate.s  + ""  + Recipe\Recordid + ")"
          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <>  #DatabaseUpdateFail
            ; Debug "New ingredient saved for recipe"
          Else
            ; Debug "New igredient not saved for recipe" + DatabaseError()
          EndIf
        Next 
        ; ClearList(Ingredients())
        ForEach Recipe\PictureList()
          SetDatabaseBlob(Program\DatabaseHandle, 0, Recipe\PictureList()\PictureBuffer, Recipe\PictureList()\PictureBufferPtr)
          DatabaseUpdate.s = "INSERT INTO Pictures("
          DatabaseUpdate.s + "Picture, "
          DatabaseUpdate.s + "FileName, "
          DatabaseUpdate.s + "Description, "
          DatabaseUpdate.s + "Recordid) "
          DatabaseUpdate.s + "VALUES("
          DatabaseUpdate.s + "?, "
          DatabaseUpdate.s + "'"  + Recipe\PictureList()\OriginalFileName + "', "
          DatabaseUpdate.s + "'"  + Recipe\PictureList()\Description      + "', "
          DatabaseUpdate.s + ""   + Recipe\Recordid                       + ")"
          ; 
          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)  <> #DatabaseUpdateFail
            ;MessageRequester("Error", DatabaseError())
          EndIf
        Next
          Debug "Saved: " + KillQuote(Recipe\Recipetitle.s) + " Program: "  + Recipe\Importedfrom.s + " File: "  + ImportFileName.s
      Else
        ; Debug "Database failed to be updated so we didn't get a new record number."
      EndIf
      ; 
    Else
      Debug DatabaseUpdate.s
      Debug "---------------------------------------------------------------------------"
      Debug "The database update failed. " + DatabaseError()
    EndIf
    ; Saving an edited record
  Else
    ;Debug ImportFileName.s  + " failed: "  + Recipe\Recipetitle  ; Might already be saved
  EndIf
  ; 
EndProcedure

; Open the system database file

OpenSystemDatabase()                                                                                   ; 

; Get an RecipeML file from the user and then parse it

RequestRecipeML()                                                                                      ; 

; 

End                                                                                                    ;

; 

Code: Select all

Recipe\Instructions.s + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + #LF$
      
Recipe\Instructions.s = ReplaceString(Recipe\Instructions.s, "'", "''")

Code: Select all

Recipe\Instructions.s + ReplaceString(RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))), "'", "''") + #LF$

Code: Select all

;=======================================================================================================
; TI-994A's routine for vector printing
;=======================================================================================================

Procedure TI_PrintRecipe(recipeName.s, image.i, ingredients.s, method.s)
 
  recipeTitleFont   = LoadFont(#PB_Any, "Arial",            24, #PB_Font_Bold)
  recipeBodyFonts   = LoadFont(#PB_Any, "Times New Roman",  14)
  recipeHeaderFonts = LoadFont(#PB_Any, "Times New Roman",  22, #PB_Font_Bold | #PB_Font_Italic)
  
  If PrintRequester()
    If StartPrinting("MGB Recipes: " + recipeName)
      If StartVectorDrawing(PrinterVectorOutput(#PB_Unit_Millimeter))
        
        frameWidth  = 1
        singleSpace = 5
        doubleSpace = 10
        pageMargin  = 10
        pageWidth   = VectorOutputWidth()
        pageHeight  = VectorOutputHeight()
        
        MovePathCursor(pageMargin, pageMargin)
        AddPathLine(pageWidth - (pageMargin * 2), 0,      #PB_Path_Relative)
        AddPathLine(0, pageHeight - (pageMargin * 2),     #PB_Path_Relative)
        AddPathLine(- (pageWidth - (pageMargin  * 2)), 0, #PB_Path_Relative)
        AddPathLine(0, -(pageHeight - (pageMargin * 2)),  #PB_Path_Relative)
        StrokePath(frameWidth, #PB_Path_SquareEnd)
       
        VectorFont(FontID(recipeTitleFont))
        VectorSourceColor(RGBA(0, 0, 155, 255))
        
        titleFontSize = 24 / 2.83
        titleXMargin = (pageWidth - VectorTextWidth(recipeName)) / 2
        While titleXMargin < pageMargin
          If titleFontSize > 1
            titleFontSize - 1
            VectorFont(FontID(recipeTitleFont), titleFontSize)
            titleXMargin = (pageWidth - VectorTextWidth(recipeName)) / 2
          Else
            Break
          EndIf
        Wend
        titleYMargin = pageMargin + singleSpace
        
        MovePathCursor(titleXMargin, titleYMargin)
        DrawVectorText(recipeName)
       
        startX = pageMargin + singleSpace
        startY = PathCursorY() + (singleSpace * 3)
        MovePathCursor(startX, startY)
        
        If IsImage(image)
          If ImageWidth(image) <= ImageHeight(image)
            imgAspect.d = ImageWidth(image) / ImageHeight(image)
            imgHeight.d  = (pageWidth - (pageMargin * 2)) / 2
            imgWidth.d = imgHeight * imgAspect
          Else
            imgAspect.d = ImageHeight(image) / ImageWidth(image)
            imgWidth.d  = (pageWidth - (pageMargin * 2)) / 2
            imgHeight.d = imgWidth * imgAspect
          EndIf
          
          DrawVectorImage(ImageID(image), 255, imgWidth, imgHeight)
          startX = PathCursorX() + singleSpace
        EndIf
        
        imageBase = PathCursorY()
        
        VectorFont(FontID(recipeHeaderFonts))
        VectorSourceColor(RGBA(0, 0, 0, 255))
        MovePathCursor(startX, startY)
        DrawVectorText("Ingredients")
        
        startY = PathCursorY() + doubleSpace
        VectorFont(FontID(recipeBodyFonts))
        MovePathCursor(startX, startY)
        paragraphWidth = pageWidth - (startX + pageMargin + singleSpace)
        paragraphHeight = pageHeight - (startY + pageMargin)
        DrawVectorParagraph(ingredients, paragraphWidth, paragraphHeight)
        
        If IsImage(image) 
          If PathCursorY() > imageBase
            indentY = PathCursorY() + doubleSpace
            indentWidth = (pageWidth - ((pageMargin * 2) + (singleSpace * 3))) - paragraphWidth
          EndIf
          startY = imageBase
        Else
          startY = PathCursorY()
        EndIf
                
        startY + doubleSpace
        startX = pageMargin + singleSpace
        VectorFont(FontID(recipeHeaderFonts))
        VectorSourceColor(RGBA(0, 0, 0, 255))
        MovePathCursor(startX, startY)
        DrawVectorText("Method")
        
        startY = PathCursorY() + doubleSpace
        VectorFont(FontID(recipeBodyFonts))
        MovePathCursor(startX, startY)
        
        If indentY
          paragraphWidth = indentWidth
          paragraphHeight = indentY - (startY - doubleSpace)
          requiredHeight = 0
          
          While requiredHeight < paragraphHeight
            fragmentLen + 1
            If fragmentLen > Len(method)
              Break
            EndIf            
            partialParagraph$ = Mid(method, 1, fragmentLen)
            requiredHeight = VectorParagraphHeight(partialParagraph$, paragraphWidth, paragraphHeight)
          Wend
          
          If fragmentLen < Len(method)
            While Mid(partialParagraph$, fragmentLen, 1) <> " "
              fragmentLen - 1
              If fragmentLen < 1
                Break
              EndIf
            Wend  
          EndIf
          
          partialParagraph$ = Mid(partialParagraph$, 1, fragmentLen)
          DrawVectorParagraph(partialParagraph$, paragraphWidth, paragraphHeight)
          
          method = Mid(method, fragmentLen + 1)
          startY = PathCursorY()
        EndIf
        
        If Trim(method) <> ""
          While Trim(method) <> ""
            
            paragraphWidth = pageWidth - ((pageMargin * 2) + doubleSpace)
            paragraphHeight = pageHeight - (startY + pageMargin)
            requiredHeight = 0
            fragmentLen = 0
            
            While requiredHeight < paragraphHeight
              fragmentLen + 1
              If fragmentLen > Len(method)
                Break
              EndIf
              partialParagraph$ = Mid(method, 1, fragmentLen)
              requiredHeight = VectorParagraphHeight(partialParagraph$, paragraphWidth, paragraphHeight)
            Wend
            
            If fragmentLen < Len(method)
              While Mid(partialParagraph$, fragmentLen, 1) <> " "
                fragmentLen - 1
                If fragmentLen < 1
                  Break
                EndIf
              Wend  
            EndIf
            
            partialParagraph$ = Mid(partialParagraph$, 1, fragmentLen)
            DrawVectorParagraph(partialParagraph$, paragraphWidth, paragraphHeight)
            
            method = Mid(method, fragmentLen + 1)
            
            If Trim(method) = ""
              Break
            EndIf
          
            NewVectorPage()
            
            MovePathCursor(pageMargin, pageMargin)
            AddPathLine(pageWidth - (pageMargin * 2), 0,      #PB_Path_Relative)
            AddPathLine(0, pageHeight - (pageMargin * 2),     #PB_Path_Relative)
            AddPathLine(- (pageWidth - (pageMargin  * 2)), 0, #PB_Path_Relative)
            AddPathLine(0, -(pageHeight - (pageMargin * 2)),  #PB_Path_Relative)
            StrokePath(frameWidth, #PB_Path_SquareEnd)
            
            VectorFont(FontID(recipeTitleFont), titleFontSize)
            VectorSourceColor(RGBA(0, 0, 155, 255))
            
            MovePathCursor(titleXMargin, titleYMargin)
            DrawVectorText(recipeName)            
                        
            startY = PathCursorY() + (singleSpace * 3)   
            VectorFont(FontID(recipeBodyFonts))
            VectorSourceColor(RGBA(0, 0, 0, 255))
            MovePathCursor(startX, startY)
            
          Wend    
        EndIf
        
        StopVectorDrawing()
        
      EndIf
    EndIf
    StopPrinting()
  EndIf
 
EndProcedure