Recipe management software?

[Fangbeast]

*Update*

22/03/2016

Added: Attach a database from some other user of Recipes, search through their recipes, drap and drop recipes you want onto your wanted recipes list from their and then save them to your database.

It wasn't that simple, believe me!! Once again, InfraTec came to my rescue with fixed up SQL queries when I stuffed them up. Check through the code in the main module to see what you can do int he transfer section, I have no time to write documentation. Link is available as usual from the last post that had one in it.

[jack]

thank you
recipe sharing sound interesting.

[Fangbeast]

thank you
recipe sharing sound interesting.

Don't forget to test properly and tell me anything I may have stuffed up or simple things that need adding okay?

I now have to learn how to output in the Food Data Exchange format that is so popular these days.

[jack]

Hi Fangbeast
something happened to your database, I can only get 50 recipes no matter what I do, if I replace the new database with the old one then it works.

[Fangbeast]

Hi Fangbeast
something happened to your database, I can only get 50 recipes no matter what I do, if I replace the new database with the old one then it works.

Nothing happened to the database, I just happened to forget that I had a harcoded limit for query returns if someone didn't choose one.

Save this code as _FindRecipeNow.pbi into Modules\Data and you should be right. Haven't found one anywhere else. Fingers crossed.

Also check SETUP in the menu options and make sure that "Limit the number of records returned during a search to>>>>>" is either unchecked or if it is checked that the spingadget has a number you are comfortable with.

These settings are saved per database.

Code: Select all

;==============================================================================================================================================================================
;Find any records based on fuzzy SQL logic when the button is pressed
;==============================================================================================================================================================================

Procedure FindRecipeNow()

  ;------------------------------------------------------------------------------------------------
  ; Clear the existing display if it contains items
  ;------------------------------------------------------------------------------------------------
  
  If CountGadgetItems(#Gadget_Recipes_Recipes) <> #NoItems
    
    ClearGadgetItems(#Gadget_Recipes_Recipes)
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  SearchType.s                        = GetGadgetText(#Gadget_Find_FindType)
  
  ;------------------------------------------------------------------------------------------------
  ; Get the type of SQL Find to perform
  ;------------------------------------------------------------------------------------------------
  
  Option\SearchWindowSearchType       = GetGadgetState(#Gadget_Find_FindType)
  
  ;------------------------------------------------------------------------------------------------
  ; Get the type of SQL field to search on
  ;------------------------------------------------------------------------------------------------
  
  Option\SearchWindowSearchField      = GetGadgetState(#Gadget_Find_FindField)
  
  ;------------------------------------------------------------------------------------------------
  ; Get the Find text for recipes to return
  ;------------------------------------------------------------------------------------------------
  
  Option\SearchWindowSearchText       = Trim(RepQuote(GetGadgetText(#Gadget_Find_FindText)))
  
  ;------------------------------------------------------------------------------------------------
  ; Get the field to perform the sort on
  ;------------------------------------------------------------------------------------------------
  
  Option\SearchWindowSortField        = GetGadgetState(#Gadget_Find_SortField)
  
  ;------------------------------------------------------------------------------------------------
  ; Get the Find text for recipes to return
  ;------------------------------------------------------------------------------------------------
  
  SearchIngredients.s                 = Trim(RepQuote(GetGadgetText(#Gadget_Find_FindIngredientText)))
  
  ;------------------------------------------------------------------------------------------------
  ; Alias the search field numbers to database field names
  ;------------------------------------------------------------------------------------------------
  
  Select Option\SearchWindowSearchField
      
    Case    0  : FieldText.s = "All fields"
      
    Case    1  : FieldText.s = "Recipetitle"                                                                               ; 
      
    Case    2  : FieldText.s = "Numberofservings"                                                                          ; 
      
    Case    3  : FieldText.s = "Recipeauthor"                                                                              ; 
      
    Case    4  : FieldText.s = "Categories"                                                                                ; 
      
    Case    5  : FieldText.s = "Subcategories"                                                                             ; 
      
    Case    6  : FieldText.s = "Preparationtime"                                                                           ; 
      
    Case    7  : FieldText.s = "Cookingtime"                                                                               ; 
      
    Case    8  : FieldText.s = "Difficulty"                                                                                ; 
      
    Case    9  : FieldText.s = "Recipeversion"                                                                             ; 
      
    Case   10  : FieldText.s = "Recipesource"                                                                              ; 
      
    Case   11  : FieldText.s = "Copyright"                                                                                 ; 
      
    Case   12  : FieldText.s = "Cuisine"                                                                                   ; 
      
    Case   13  : FieldText.s = "Reciperating"                                                                              ; 
      
    Case   14  : FieldText.s = "Importedfrom"                                                                              ; 
      
    Case   15  : FieldText.s = "Authorcomments"                                                                            ; 
      
    Case   16  : FieldText.s = "Instructions"                                                                              ; 
      
    Case   17  : FieldText.s = "Nutritionaldata"                                                                           ; 
      
    Case   18  : FieldText.s = "Othercomments"                                                                             ; 
      
    Case   19  : FieldText.s = "Homepage"                                                                                  ; 
      
    Case   20  : FieldText.s = "Deleted"                                                                                   ; 
      
    Case   21  : FieldText.s = "Updated"                                                                                   ; 
      
    Case   22  : FieldText.s = "Favourite"                                                                                 ; 
      
    Case   23  : FieldText.s = "Locked"                                                                                    ; 
      
    Case   24  : FieldText.s = "Selected"                                                                                  ; 
      
    Case   25  : FieldText.s = "Recordid"                                                                                  ;       
      
  EndSelect
  
  ;------------------------------------------------------------------------------------------------
  ; Alias the Find type text to SQLite operators
  ;------------------------------------------------------------------------------------------------
  
  SearchCommand.s = SetSearchQuery(SearchType.s, Option\SearchWindowSearchText)
  
  ;------------------------------------------------------------------------------------------------
  ; Search all fields
  ;------------------------------------------------------------------------------------------------
  
  Select FieldText.s

    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------

    Case "All fields"
      
      ;--------------------------------------------------------------------------------------------
      ; Make the compound query
      ;--------------------------------------------------------------------------------------------
      
      DatabaseQuery.s = "SELECT * FROM Recipes "
      
      DatabaseQuery.s + "WHERE "      
      
      DatabaseQuery.s + "   Recipetitle"       + SearchCommand.s      
      
      DatabaseQuery.s + "OR Numberofservings"  + SearchCommand.s      
      
      DatabaseQuery.s + "OR Categories"        + SearchCommand.s      
      
      DatabaseQuery.s + "OR Subcategories"     + SearchCommand.s      
      
      DatabaseQuery.s + "OR Preparationtime"   + SearchCommand.s      
      
      DatabaseQuery.s + "OR Cookingtime"       + SearchCommand.s      
      
      DatabaseQuery.s + "OR Difficulty"        + SearchCommand.s      
      
      DatabaseQuery.s + "OR Recipeversion"     + SearchCommand.s      
      
      DatabaseQuery.s + "OR Recipesource"      + SearchCommand.s      
      
      DatabaseQuery.s + "OR Copyright"         + SearchCommand.s      
      
      DatabaseQuery.s + "OR Cuisine"           + SearchCommand.s      
      
      DatabaseQuery.s + "OR Reciperating"      + SearchCommand.s      
      
      DatabaseQuery.s + "OR Importedfrom"      + SearchCommand.s      
      
      DatabaseQuery.s + "OR Authorcomments"    + SearchCommand.s      
      
      DatabaseQuery.s + "OR Instructions"      + SearchCommand.s      
      
      DatabaseQuery.s + "OR Nutritionaldata"   + SearchCommand.s      
      
      DatabaseQuery.s + "OR Othercomments"     + SearchCommand.s      
      
      DatabaseQuery.s + "OR Homepage"          + SearchCommand.s      
      
      DatabaseQuery.s + "OR Deleted"           + SearchCommand.s      
      
      DatabaseQuery.s + "OR Updated"           + SearchCommand.s      
      
      DatabaseQuery.s + "OR Favourite"         + SearchCommand.s      
      
      DatabaseQuery.s + "OR Locked"            + SearchCommand.s      
      
      DatabaseQuery.s + "OR Selected"          + SearchCommand.s      
      
      DatabaseQuery.s + "OR Recordid"          + SearchCommand.s      
      
    Default
      
      DatabaseQuery.s = "SELECT * FROM Recipes "
      
      DatabaseQuery.s + "WHERE "
      
      DatabaseQuery.s + FieldText.s + " "      + SearchCommand.s      
      
  EndSelect
  
  ;------------------------------------------------------------------------------------------------
  ; Get the SQL sort direction
  ;------------------------------------------------------------------------------------------------
  
  If GetGadgetState(#Gadget_Find_oSortAcending) = #True
    
    SortDirection.s = " ORDER BY "  + GetGadgetText(#Gadget_Find_SortField) + " ASC "
    
    Option\SearchWindowSortDirection  = #True
    
  ElseIf GetGadgetState(#Gadget_Find_oSortDescending) = #True
    
    SortDirection.s = " ORDER BY "  + GetGadgetText(#Gadget_Find_SortField) + " DESC "
    
    Option\SearchWindowSortDirection  = #False
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; Get the limit for the number of recipes to return
  ;------------------------------------------------------------------------------------------------
  
  If GetGadgetState(#Gadget_Find_cbQueryReturnLimit) = #True
    
    SortLimit.s = " LIMIT " + GetGadgetText(#Gadget_Find_sQueryReturnLimit)
    
    Option\SearchWindowQueryReturnLimit = #True
    
    Option\SearchWindowQueryReturnSize  = GetGadgetState(#Gadget_Find_sQueryReturnLimit)
    
  ElseIf GetGadgetState(#Gadget_Find_cbQueryReturnLimit) = #False
    
    SortLimit.s = #EmptyString
    
    Option\SearchWindowQueryReturnLimit = #False
    
    Option\SearchWindowQueryReturnSize  = 0
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  If GetGadgetState(#Gadget_Find_oCheckOr) = #True
    
    FindMode.s = " OR "
    
    Option\SearchWindowSearchMode   = #True
    
  ElseIf  GetGadgetState (#Gadget_Find_oCheckAnd) = #True
    
    FindMode.s = " AND "
    
    Option\SearchWindowSearchMode   = #False
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  Option\SearchWindowCloseMode  = GetGadgetState(#Gadget_Find_cbCheckEnter)

  ;------------------------------------------------------------------------------------------------
  ; Set the initial total item counter
  ;------------------------------------------------------------------------------------------------
  
  ItemCounter.i   = 0
  
  ;------------------------------------------------------------------------------------------------
  ; Find all fields or just a specific field
  ;------------------------------------------------------------------------------------------------
  
  If SearchIngredients.s = #EmptyString
    
    DatabaseQuery.s   + SortDirection.s
    
    DatabaseQuery.s   + SortLimit.s
    
  ElseIf SearchIngredients.s <> #EmptyString
    
    NumberOfItems.i = CountString(SearchIngredients.s, ",")
    
    If NumberOfItems.i
      
      For GetItems.i = 1 To NumberOfItems.i + 1
        
        GetItemString.s + FindMode.s  + " Recordid IN (SELECT Recordid FROM Ingredients WHERE Ingredient LIKE '%"
        
        GetItemString.s + StringField(SearchIngredients.s, GetItems.i, ",")
        
        GetItemString.s + "%') "
        
      Next GetItems.i
      
    EndIf
    
    DatabaseQuery.s + GetItemString.s    
    
    DatabaseQuery.s + SortDirection.s
    
    DatabaseQuery.s + SortLimit.s
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
 ;Debug DatabaseQuery.s
  
  ;------------------------------------------------------------------------------------------------
  ; Run the query with the chosen details
  ;------------------------------------------------------------------------------------------------
  
 ;DatabaseUpdate(Program\DatabaseHandle, "Begin Transaction")   
  
  ;------------------------------------------------------------------------------------------------
  ; Was the compiled binary SQLite statement generation successfull?
  ;------------------------------------------------------------------------------------------------
  
  If DatabaseQuery(Program\DatabaseHandle, DatabaseQuery.s) <> #DatabaseQueryFail
    
    ;----------------------------------------------------------------------------------------------
    ; Lock the list form to prevent it greying out or flickering during the update
    ;----------------------------------------------------------------------------------------------
    
   ;LockWindowUpdate_(WindowID(#Window_Recipes))
    
   ;SendMessage_(GadgetID(#Gadget_Recipes_Recipes), #WM_SETREDRAW,  #False, 0)
    
    ;----------------------------------------------------------------------------------------------
    ; Step through all available returned records
    ;----------------------------------------------------------------------------------------------
    
    While NextDatabaseRow(Program\DatabaseHandle)  <> #NoItems
      
      ;--------------------------------------------------------------------------------------------
      ; Retrieve all returned lines of data
      ;--------------------------------------------------------------------------------------------
      
      Recipe\Recipetitle       = KillQuote(GetDatabaseString(Program\DatabaseHandle,   0))
      
      Recipe\Numberofservings  = KillQuote(GetDatabaseString(Program\DatabaseHandle,   1))
      
      Recipe\Recipeauthor      = KillQuote(GetDatabaseString(Program\DatabaseHandle,   2))
      
      Recipe\Categories        = KillQuote(GetDatabaseString(Program\DatabaseHandle,   3))
      
      Recipe\Subcategories     = KillQuote(GetDatabaseString(Program\DatabaseHandle,   4))
      
      Recipe\Preparationtime   = KillQuote(GetDatabaseString(Program\DatabaseHandle,   5))
      
      Recipe\Cookingtime       = KillQuote(GetDatabaseString(Program\DatabaseHandle,   6))
      
      Recipe\Difficulty        = KillQuote(GetDatabaseString(Program\DatabaseHandle,   7))
      
      Recipe\Recipeversion     = KillQuote(GetDatabaseString(Program\DatabaseHandle,   8))
      
      Recipe\Recipesource      = KillQuote(GetDatabaseString(Program\DatabaseHandle,   9))
      
      Recipe\Copyright         = KillQuote(GetDatabaseString(Program\DatabaseHandle,  10))
      
      Recipe\Cuisine           = KillQuote(GetDatabaseString(Program\DatabaseHandle,  11))
      
      Recipe\Reciperating      =           GetDatabaseString(Program\DatabaseHandle,  12) 
      
      Recipe\Importedfrom      = KillQuote(GetDatabaseString(Program\DatabaseHandle,  13))
      
      Recipe\Authorcomments    = KillQuote(GetDatabaseString(Program\DatabaseHandle,  14))
      
      Recipe\Instructions      = KillQuote(GetDatabaseString(Program\DatabaseHandle,  15))
      
      Recipe\Nutritionaldata   = KillQuote(GetDatabaseString(Program\DatabaseHandle,  16))
      
      Recipe\Othercomments     = KillQuote(GetDatabaseString(Program\DatabaseHandle,  17))
      
      Recipe\Homepage          = KillQuote(GetDatabaseString(Program\DatabaseHandle,  18))
      
      Recipe\Deleted           =           GetDatabaseString(Program\DatabaseHandle,  19) 
      
      Recipe\Updated           =           GetDatabaseString(Program\DatabaseHandle,  20) 
      
      Recipe\Favourite         =           GetDatabaseString(Program\DatabaseHandle,  21) 
      
      Recipe\Locked            =           GetDatabaseString(Program\DatabaseHandle,  22) 
      
      Recipe\Selected          =           GetDatabaseString(Program\DatabaseHandle,  23) 
      
      Recipe\Shopping          =           GetDatabaseString(Program\DatabaseHandle,  24) 
      
      Recipe\Recordid          =           GetDatabaseString(Program\DatabaseHandle,  25) 
      
      ;--------------------------------------------------------------------------------------------
      ; Send data to the re-useable string display as if in add mode
      ;--------------------------------------------------------------------------------------------
      
      DisplayRecipeString(ItemCounter.i, "AddMode")
      
      SetGadgetText(#Gadget_Find_RecipesFound, Str(ItemCounter.i) + " recipes")
      
      ;--------------------------------------------------------------------------------------------
      ; Stop the form greying out during long operations by cleaning out other events
      ;--------------------------------------------------------------------------------------------
      
      ;While WindowEvent() : Wend
      
      ;--------------------------------------------------------------------------------------------
      ; Increment the line counter
      ;--------------------------------------------------------------------------------------------
      
      ItemCounter.i + 1
      
      ;--------------------------------------------------------------------------------------------
      ; 
      ;--------------------------------------------------------------------------------------------
      
    Wend
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
    FinishDatabaseQuery(Program\DatabaseHandle)
    
    ;----------------------------------------------------------------------------------------------
    ; Lock the list form to prevent it greying out or flickering during the update
    ;----------------------------------------------------------------------------------------------
    
   ;LockWindowUpdate_(0)
    
   ;SendMessage_(GadgetID(#Gadget_Recipes_Recipes), #WM_SETREDRAW, #True, 0)
    
   ;RedrawWindow_(GadgetID(#Gadget_Recipes_Recipes), #Null, #Null, #RDW_ERASE | #RDW_FRAME | #RDW_INVALIDATE | #RDW_ALLCHILDREN)
    
    ;----------------------------------------------------------------------------------------------
    ; Force a change event in the titles list for the first record in the list
    ;----------------------------------------------------------------------------------------------
    
    If ItemCounter.i <> #NoItems
      
      SetActiveGadget(#Gadget_Recipes_Recipes)
      
      SetGadgetState(#Gadget_Recipes_Recipes, #FirstItem)
      
      SetGadgetItemState(#Gadget_Recipes_Recipes, #FirstItem, #PB_ListIcon_Selected)
      
      PostEvent(#PB_Event_Gadget, #Window_Recipes, #Gadget_Recipes_Recipes, #PB_EventType_Change)
      
      SetInfoBarArea("Recipes", #EmptyString, Str(ItemCounter.i), "FindRecipeNow")
      
    Else
      
      ClearMainForm()
      
    EndIf
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
  Else
    
    SetInfoBarArea("Headings", "Error", "The database query failed to be executed " + DatabaseError(), "FindRecipeNow")
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  DatabaseUpdate(Program\DatabaseHandle, "Commit")
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  If Option\SearchWindowCloseMode = #True
    
    CloseMyWindow(#Window_Find)
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------

EndProcedure

[jack]

ok, thank you

[Fangbeast]

01/04/2016

Added: Create a new database with your own name, anywhere you like. This is used as the currently open database. This is similar to many cooking programs 'CookBook' feature (I hope)

Added: Open any other database 'CookBook', at any location.

Added: Formatting toolbar for the instructions editor. All the usual suspects.

Added: Show current cookbook name above the recipe titles. So you know what database is open.

Added: Program will now open the last database used or the default system database if not found.

Changed: Program toggles and options are saved to local program directory instead of each database. This was necessary as local variables should not be written to every opened database.

Changed: Database name no longer contains version numbers, too confusing.

Changed: Error log name no longer contains version numbers, too confusing.

Changed: Preference file name no longer contains version numbers, too confusing.

Changed. The product version is changed to 1.00b as it can now be considered a reasonably stable beta.

**Notes** There are now some bonus help files located in the Help directory for you to go through, they are a work in progress. CHM, DOCX, EPUB and PDF formats. There are regrettably full of HelpnDoc's advertising as I cannot afford the commercial version.

The change log doesn't really begin to show you how much work was done under the hood to get them done.

**HELP needed** The formatting toolbar icons for the Instructions editor form frankly suck but it was the best that I could come up with. If someone could come up with some better ones, please do so!

https://www.dropbox.com/sh/qjrsw33plpcl ... czDDa?dl=0

[Fangbeast]

03/04/2016

Added: Importing of MasterCook compressed files with pictures. (.MZ2 filetype)

Fixed: 'Database not initialised' error.

***NOTES***

Recipes manager can also export its own database to XML which it can also import but, the export to Recipes xml format seems to take forever and I can't seem to import it it yet either. Far too tired to figure it out as yet.

I am going to need help with this please!!!.

Doing the latest MasterCook format import took all day due to my tiredness and a bug in their xml output.

Will also be uploading another 100 meg or so of recipes to MegaSync where I uploaded the lot last month.

[Fangbeast]

***UPDATE***

05/04/2016

Added: Can now search for recipes by themselves without ingredients, recipes that include certain ingredients or search for recipes by ingredients only without a recipe titles. This one took a while and I am at the absolute limit of my skill with this and still not certain I got it right.

Bernd tries to help me but as I get older, I understand less and less.! (and panic)

Added: There are search cues for both recipe text and ingredient text in the advanced search window.

Added: CHM, DOCX, EPUB and PDF help files in the Help\ directory.

ToDo

1. Print help pages.
2. Explore threaded model somehow.
3. List of keyboard shortcuts. (Maybe editable?)
4. Print help pages.
5. Print shopping list.
6. Print keyboard shortcuts.
7. Do something with the "Save shopping" button as it isn't really used here.
8. Keyboard shortcuts list.
9. Funny issue with some form buttons stuck in their pressed state???
10. Need some decent icons for the instruction window formatting bar. Mine are bad and boring.
11. Search cues for transfer, shopping and export menus. Unless I already did them?
12. The export menu is far too slow and appears to hang. Must fix that.

P.S I am really struggling to make this faster folks but unless I can get some help, I will leave this project as it is. It does the job I want but I don't have the smarts to do it any better.

[jack]

Hi Fangbeast
very nice job, the only thing I changed in your recipe manager are the colours on the recipe list, see what you think
file _DisplayRecipeString.pbi, lines 69 thru 89

Code: Select all

  If Option\ColourBand = #True
    If Program\ColourToggle = #True
      Program\ColourToggle = #False
      SetGadgetItemColor(#Gadget_Recipes_Recipes, LineNumber.i, #PB_Gadget_BackColor, $FAE6E6) ; $D4D4D4)
     SetGadgetItemColor(#Gadget_Recipes_Recipes, LineNumber.i, #PB_Gadget_FrontColor, $D1091D) ; Dark IBM blue font
    ElseIf Program\ColourToggle = #False
      Program\ColourToggle = #True
      SetGadgetItemColor(#Gadget_Recipes_Recipes, LineNumber.i, #PB_Gadget_BackColor, $E1E4FF);  $C1C1C1)
     SetGadgetItemColor(#Gadget_Recipes_Recipes, LineNumber.i, #PB_Gadget_FrontColor, $006400);  $566A19) ; Dark green font
    EndIf
  EndIf

[jack]

I just realized that when you double-click on a recipe you get a nice page with all the info needed, I had wished for that feature.

[Fangbeast]

I just realized that when you double-click on a recipe you get a nice page with all the info needed, I had wished for that feature.

?????? When you double left click on a recipe, you are in EDIT mode. Says so at the top of the edit window title. Didn't I remember to put that into the help file? The standalone CHM/PDF/EPUB/DOCX that I left in the help directory?

When you double right click on the recipe title list, you are adding a new recipe.

[jack]

I thought it was some kind of preview, with my large screen it would be nice to view a full page.

[Fangbeast]

I thought it was some kind of preview, with my large screen it would be nice to view a full page.

How big though? None of my forms have proportional resize and I haven't seen full screen preview of any recipe in any program yet. Only seen three programs, ChickenPing, Living CookBook and Big Oven and none of them had full screen preview, that's what they print routine was for.

And weren't you going to test my software and give me a report??? (suspicious look). No-one else is!!

[jack]

hi Fangbeast
I think your recipe manager is feature complete at this point and has an intuitive interface, I was able to run it without reading the manual, as I quite often do.
as to how big?, I know not everyone has a huge monitor, but the size of that window when you double-click on a recipe would be great to have, to view everything at a glance without scrolling.
thank you