my sample database is an excel file, though one can choose any database one wants, however the field mappings and the tables are documented in the file,
; the database can be anything, however the tables have to have the following format:
; table name : Inventory ; fields: ItemCode Description UnitPrice Units
; table name : Sales-Record-January ; Fields: Receipt Date Year Month Time ItemCode Description SalePrice
; table name : Sales-Record-February; Fields: Receipt Date Year Month Time ItemCode Description SalePrice
; likewise till december...
; table name : Monthly ; Fields : Month TotalSaleAmount
; table name : Yearly ; Fields: TotalSaleAmount
; each year will have to have a seperate database.
Code: Select all
; PureBasic Visual Designer v3.95 build 1485 (PB4Code)
;- Window Constants
;
If UseODBCDatabase() = 0
MessageRequester("Error", "Can't initialize Database (ODBC v3 or better) environment", 0)
End
EndIf
;OpenConsole()
Dim DatabaseType.s(4)
DatabaseType(0) = "Unknown"
DatabaseType(1) = "Numeric"
DatabaseType(2) = "String"
DatabaseType(3) = "Float"
; First, let's see which drivers are attached to the system..
;
If ExamineDatabaseDrivers()
While NextDatabaseDriver()
; PrintN(DatabaseDriverName()+" - "+DatabaseDriverDescription())
Wend
EndIf
; the database can be anything, however the tables have to have the following format:
; table name : Inventory ; fields: ItemCode Description UnitPrice Units
; table name : Sales-Record-January ; Fields: Receipt Date Year Month Time ItemCode Description SalePrice
; table name : Sales-Record-February; Fields: Receipt Date Year Month Time ItemCode Description SalePrice
; likewise till december...
; table name : Monthly ; Fields : Month TotalSaleAmount
; table name : Yearly ; Fields: TotalSaleAmount
; each year will have to have a seperate database.
Global TotalItems=0; items in the inventory table
Global price$ ;used to extract price from the billed items
Global Dim ItemCode(TotalItems) ; item code array from inventory table
Global Dim Description.s(TotalItems) ;description array from inventory table
Global Dim UnitPrice(TotalItems) ; unit price array from inventory table
Global Dim Units(TotalItems) ;units array from inventory table
Global index ; marks the item index in the inventory table
Global selection$
Global Total_price=0 ; sum total price of items purchased
Global Items_purchased=0 ; number of items purchased
;Global printer_online=0 ; printer toggle button status
Global Dim Month_of_year.s(12)
Month_of_year(0)="January"
Month_of_year(1)="February"
Month_of_year(2)="March"
Month_of_year(3)="April"
Month_of_year(4)="May"
Month_of_year(5)="June"
Month_of_year(6)="July"
Month_of_year(7)="August"
Month_of_year(8)="September"
Month_of_year(9)="October"
Month_of_year(10)="November"
Month_of_year(11)="December"
Enumeration
#Window_0
#Window_1
EndEnumeration
;- Gadget Constants
;
Enumeration
#ListIcon_0
#Total
#Combo_item_code
#Combo_description
#Combo_heading1
#Combo_heading2
#AddItem_Gadget
#CommitandPrint_Gadget
#DELETE_Gadget
#Printer_toggle
EndEnumeration
;total.l=0
Procedure GetItemnumber_FromItemCode(selection.s)
Protected result=-1
For i=0 To TotalItems
If selection=Str(ItemCode(i))
result=i
Break
EndIf
Next i
ProcedureReturn result
EndProcedure
If Not(OpenDatabaseRequester(0))
MessageRequester("Info", "Operation canceled", 0)
End
Else
;we create the gagdets now
OpenWindow(#Window_0, 251, 71, 670, 600, "Small-shop", #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_TitleBar )
;OpenWindow(#Window_1, 10, 10, 60, 60, "Small-shop", #PB_Window_SystemMenu | #PB_Window_SizeGadget | #PB_Window_TitleBar )
CreateGadgetList(WindowID(#Window_0))
ListIconGadget(#ListIcon_0, 20, 40, 460, 400, "Item Code", 90,#PB_ListIcon_GridLines|#PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection)
AddGadgetColumn(#ListIcon_0, 1, "Description", 300)
AddGadgetColumn(#ListIcon_0, 2, "Price", 66)
LoadFont(0, "Arial", 10,#PB_Font_Bold)
LoadFont(1, "Arial", 8,#PB_Font_Bold)
TextGadget(#Total, 380, 440, 250, 20, "Total: "+Str(Total_price))
SetGadgetFont(#Total,FontID(0))
TextGadget(#Combo_heading1, 30, 480, 100, 20, "Item Code")
TextGadget(#Combo_heading2, 230, 480, 130, 20, "Description")
SetGadgetFont(#Combo_heading1,FontID(1))
SetGadgetFont(#Combo_heading2,FontID(1))
ComboBoxGadget(#Combo_item_code, 20, 500, 110, 100,#PB_ComboBox_Editable)
ComboBoxGadget(#Combo_description, 200, 500, 275, 100)
ButtonGadget(#AddItem_Gadget, 550, 495, 70, 30, "Add Item")
SetGadgetFont(#AddItem_Gadget,FontID(1))
ButtonGadget(#CommitandPrint_Gadget, 550, 350, 70, 50, "Commit",#PB_Button_MultiLine)
SetGadgetFont(#CommitandPrint_Gadget,FontID(1))
ButtonGadget(#Delete_Gadget, 550, 200, 70, 50, "Delete Selection",#PB_Button_MultiLine)
SetGadgetFont(#Delete_Gadget,FontID(1))
;ButtonGadget(#Printer_toggle, 550,80,70, 50, "Printer Off", #PB_Button_Toggle|#PB_Button_MultiLine)
;SetGadgetFont(#Printer_toggle,FontID(1))
;now we populate the combo box with entries from the database
;OpenConsole()
Command$="select [ItemCode],[Description],[UnitPrice],[Units] from [Inventory$]"
DatabaseQuery(0, Command$)
NbColumns = DatabaseColumns(0)
While NextDatabaseRow(0)
;If Val(GetDatabaseString(0, 3))>0 ; if there are units of this item in stock
ReDim ItemCode(TotalItems)
ReDim Description.s(TotalItems)
ReDim UnitPrice(TotalItems)
ReDim Units(TotalItems)
ItemCode(TotalItems)=Val(GetDatabaseString(0, 0))
Description(TotalItems)=LCase(GetDatabaseString(0, 1))
UnitPrice(TotalItems)=Val(GetDatabaseString(0, 2))
Units(TotalItems)=Val(GetDatabaseString(0, 3))
; Debug ( Str(ItemCode(TotalItems))+" "+Description(TotalItems)+" "+Str(UnitPrice(TotalItems))+" "+Str(Units(TotalItems)))
AddGadgetItem(#Combo_item_code,TotalItems, Str(ItemCode(TotalItems)))
AddGadgetItem(#Combo_description,TotalItems,Description(TotalItems))
TotalItems=TotalItems+1
; EndIf
Wend
TotalItems=TotalItems-1
AddKeyboardShortcut(#Window_0, #PB_Shortcut_Return,1); enter key causes a menu event
; this event maps to the same case as AddItem_Gadget click
Repeat
EventID = WaitWindowEvent()
WindowNumber = EventWindow()
Select WindowNumber
Case #Window_0
Select EventID
Case #PB_Event_Gadget
Select EventGadget()
Case #Combo_item_code
;Debug "Combo Item Code Event"
; Debug Str(GetGadgetState(#Combo_item_code))
selection$=GetGadgetText(#Combo_item_code)
SetGadgetState( #Combo_description,(GetItemnumber_FromItemCode(selection$)))
Case #Combo_description
;Debug "Combo Description Event"
SetGadgetState(#Combo_item_code,GetGadgetState(#Combo_description))
Case #AddItem_Gadget
;Debug "Add Item Clicked"
index=GetGadgetState(#Combo_item_code)
If Not(index=-1)
;selection$=GetGadgetText(#Combo_item_code)
If Units(index)>0 ;we have to have atleast 1 unit in stock
AddGadgetItem(#ListIcon_0,-1, Str(ItemCode(index))+Chr(10)+Description(index)+Chr(10)+Str(UnitPrice(Index)))
Total_price=Total_price+UnitPrice(index)
SetGadgetText(#Total,"Total: "+Str(Total_price))
Items_purchased=Items_purchased+1
;decrement the units available from the in memory array
Units(index)=Units(index)-1
;Debug Str(Units(index))
EndIf
EndIf
Case #Delete_Gadget
index=GetGadgetState(#ListIcon_0)
If Not(index=-1)
price$=GetGadgetItemText(#ListIcon_0,index,2)
selection$=GetGadgetItemText(ListIcon_0,index,0)
Units(GetItemNumber_FromItemCode(selection$))=Units(GetItemNumber_FromItemCode(selection$))+1
;Debug Str(Units(GetItemNumber_FromItemCode(selection$)))
;Debug price$
RemoveGadgetItem(#ListIcon_0,index)
Total_price=Total_price-Val(price$)
SetGadgetText(#Total,"Total: "+Str(Total_price))
Items_purchased=Items_purchased-1 ; reduce the items purchased
EndIf
Case #CommitandPrint_Gadget
If Not(Items_purchased=0)
date$=FormatDate("%dd", Date())
year$=FormatDate("%yyyy",Date())
month$=FormatDate("%mm",Date())
time$=FormatDate("%hh-%ii-%ss",Date())
receiptnumber$=date$+"-"+year$+"-"+month$+"-"+time$
tablename$="Sales-Record-"+Month_of_year(Val(month$)-1)
For i = 0 To Items_purchased-1
itemcode$=GetGadgetItemText(#ListIcon_0,i,0)
description$=GetGadgetItemText(#ListIcon_0,i,1)
price$=GetGadgetItemText(#ListIcon_0,i,2)
;Debug receiptnumber$
;Debug itemcode$
;Debug description$
;Debug price$
;Debug tablename$
;insert transaction into sales table for month
Command$="INSERT INTO ["+tablename$+"$] VALUES ('"+receiptnumber$+"','"+date$+"','"+year$+"','"+month$+"','"+time$+"','"+itemcode$+"','"+description$+"','"+price$+"')"
If Not(DatabaseUpdate(0, Command$))
MessageRequester("Error", "Can't execute the Insert into "+ tablename$+": "+DatabaseError())
EndIf
;Debug Itemcode$
;Debug Str(GetItemNumber_FromItemCode(Itemcode$))
units$=Str(Units(GetItemNumber_FromItemCode(itemcode$)))
;Debug units$
;update the inventory table to deduct units sold
Command$="UPDATE [Inventory$] SET Units='"+units$+"' WHERE [ItemCode]="+itemcode$
;Debug Command$
If Not(DatabaseUpdate(0, Command$))
MessageRequester("Error", "Can't execute the Update in Inventory: "+DatabaseError())
EndIf
;update the total sales per month in the monthly table
Command$="SELECT [TotalSaleAmount] from [Monthly$] WHERE [Month]='"+Month_of_year(Val(month$)-1)+"'"
;Debug Command$
If Not(DatabaseQuery(0, Command$))
MessageRequester("Error", "Can't execute the select from monthly sheet: "+DatabaseError())
EndIf
While NextDatabaseRow(0)
total_sale_amount$=Str(Val(GetDatabaseString(0, 0))+Val(price$))
Command$="UPDATE [Monthly$] SET TotalSaleAmount='"+total_sale_amount$+"' WHERE [Month]='"+Month_of_year(Val(month$)-1)+"'"
;Debug total_sale_amount$
;Debug Command$
If Not(DatabaseUpdate(0, Command$))
MessageRequester("Error", "Can't execute the Monthly sheet Update: "+DatabaseError())
EndIf
Wend
;update the total yearlysales
Command$="SELECT [TotalSaleAmount] from [Yearly$]"
;Debug Command$
If Not(DatabaseQuery(0, Command$))
MessageRequester("Error", "Can't execute the select from yearly sheet: "+DatabaseError())
EndIf
While NextDatabaseRow(0)
total_sale_amount$=Str(Val(GetDatabaseString(0, 0))+Val(price$))
Command$="UPDATE [Yearly$] SET TotalSaleAmount='"+total_sale_amount$+"'"
;Debug total_sale_amount$
;Debug Command$
If Not(DatabaseUpdate(0, Command$))
MessageRequester("Error", "Can't execute the Yearly sheet Update: "+DatabaseError())
EndIf
Wend
;Debug "================"
Next i
EndIf
ClearGadgetItemList(#ListIcon_0)
Items_Purchased=0
Total_price=0
SetGadgetText(#Total,"Total: "+Str(Total_price))
EndSelect;gadgetevents end here
Case #PB_Event_Menu; we get here if the enter key is pressed
Select EventMenu()
Case 1
;Debug "Add Item Clicked"
index=GetGadgetState(#Combo_item_code)
If Not(index=-1)
;selection$=GetGadgetText(#Combo_item_code)
If Units(index)>0
AddGadgetItem(#ListIcon_0,-1, Str(ItemCode(index))+Chr(10)+Description(index)+Chr(10)+Str(UnitPrice(Index)))
Total_price=Total_price+UnitPrice(index)
SetGadgetText(#Total,"Total: "+Str(Total_price))
Items_purchased=Items_purchased+1
;decrement the units available from the in memory array
Units(index)=Units(index)-1
EndIf
EndIf
EndSelect;menu events end here
EndSelect ;eventIds end here
EndSelect ;end windownumber ends here
If (EventID = #PB_Event_CloseWindow And EventWindow()=#Window_0); If the user has pressed on the close button
Quit = 1
If IsWindow(#Window_1)
CloseWindow (#Window_1)
EndIf
EndIf
Until Quit = 1
EndIf
End