Process statistics in database guidence

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Process statistics in database guidence
Posted: Fri Dec 21, 2018 04:21 PM

Hello,

For my personel invoice program, i want to change the logic for processing invoice values.

In early days, I hard coded into the dbf (Will stay with DBF) fields like :

Tot_2017, Tot_2016, Tot_2015, ... Tot_1994
Per_2017, Per_2016, ....
etc..

And in source I had stuff like

replace Tot_2017 with .....
replace Tot_2016 with......

So this year again, I will have to change my source and add structure of the database to fit 2019, 2020

I know this is bad practice...

How do you process detailed data for 1-5 year for statistics on screen (totals) with probably the same fields in the database ?

The most logic will be :

Year1, Year2, ...Year5 and than change headers of Xbrowse according 2019,2018...

At start of a new year I need to process again only the detail from 5 years including 2019 as start.

I hope I make any sense.

I gone update this into my 32 bit version i'm working on.

Marc Venken

Using: FWH 23.08 with Harbour

ukoenig

Posts: 4043

Joined: Wed Dec 19, 2007 06:40 PM

Re: Process statistics in database guidence
Posted: Fri Dec 21, 2018 08:48 PM

Marc,

my solution to check business done.
It might be a little bit more what You need.
Check products of any year against each other with month splitting.
Using many products the browser header will be jan - Dec
and the list are the products.

regards
Uwe

Since 1995 ( the first release of FW 1.9 )

i work with FW.

If you have any questions about special functions, maybe i can help.

reinaldocrespo

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: Process statistics in database guidence
Posted: Mon Dec 24, 2018 04:46 PM

Hello Marc;

If I understand your comments/question; my suggestion would be to add records instead of columns for each year. The structure of the .dbf would look similar this:

Year N 4 0
TotalSales N 9 2
TotalPer N 9 2
...

Adding a new year would simply add another record to the dbf. No need to change table structure.

Hope that helps,

Reinaldo.

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: Process statistics in database guidence
Posted: Fri Dec 28, 2018 11:15 PM

Marc,

You could also get these numbers with an invoice database class. You just subclass from TDatabase and add a method for TotalSales( cYear ). So you would just do this:

Code (fw): Select all Collapse

oInvoices():= TInvoices():New()
oInvoices:TotalSales( year(date()) ) // returns Year-to-date sales for current year
oInvoices:TotalSales( year(date())-1 ) // returns last year's sales
oInvoices:TotalSales( 2015 ) // returns sales for the year 2015

You can do whatever you want with the results, display via MsgInfo(), put in a database (as Reinaldo suggests), or directly into a report or a browse of a temp file. Granted re-reading all the files from past years whenever you generate a report is not the most efficient, but the nice thing is that you always use the same call to get what you need. So, oInvoices:TotalSales( nYear ) could work something like this.

Code (fw): Select all Collapse

Method TotalSales( nYear ) Class TInvoices
   Default nYear:= year(date())
If nYear = Year(date())
   // read all invoices this year and sum
else
   // read sales total from total sales database file (2 fields, year and TotalSales)
endif
Return nTotalSales

This way, you can change the method of finding the data at any later date and any code that is using oInvioces:TotalSales() will not break.

Food for thought.

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Marc Venken

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: Process statistics in database guidence
Posted: Sat Dec 29, 2018 10:50 AM

James,

I surely go for classes !

You have convinced me a while ago with several samples ...

And also for this, classes seems to be the right way.

BTW. The forum samples you posted about classes. (not only mine). By any chance that you have them in a sepperate folder on your system ?
It could be very usefull for samples as a starting point.

Marc Venken

Using: FWH 23.08 with Harbour

FiveTech Support Forums

Continue the discussion