I work on company report. I'm a beginner in PERL/CGI.
I need to make a report with result by month and it increase on the next hits/page with next month and so on. This work also from month December to January, as a next year.
For information, I use date format mm/dd/yyyy
Can someone help ? I've search for posts, but did not find what I'm looking for.

Would you want to do a search on the "date" field and then sort results accordingly, or would you want to do a search on some other field and then sort by date?
kellner

It has no relation with other field, I need to summaries what record available in current month in one page, last month in next page and so on.
So the record displayed depending on $db_max_hits.
Thank you.

Ok. Next question: What format do you use for dates? It's necessary to know that for sorting. Is your date format "01-01-2001" or "01-Jan-2001"?
kellner

Date format is 01/01/2001

Lembono

OK, this is just a possible beginning; I got stuck in the middle and currently have no time to do some more, but perhaps someone else could jump in.

One question: Will all records have dates, or will there by records where the date fields are empty?

Basically, I thought to bypass search routines, directly open the db file - as we want to get *all* records anyway and sort them by date -, and read in date fields plus id fields (= database key fields). I don't think it's a good idea to do this through db_max_hits. You'd have to run through an entire search routine, searching for all records, and you'd *then* have to go through a whole new routine sorting them by month - apart from the fact that I really wouldn't know how to do the sorting and how to create the appropriate links on result pages.

sub sort_all_by_month {
my %date_id_hash;
my $date_field_number = $db_def{'date'}[0]; # replace "date" with your database date field name
my $id_field_number = $db_def{'ID'}[0];# replace "ID" with your database key field name
open (DB, "<$db_file_name") || &cgierr("can't open $db_file_name: $!");
while (<DB>) {
chomp;
my @linearray = split /\|/, $_;
$linearray[$date_field_number] =~ s/(\d{2})(\/)(\d{2})(\/)(\d{4})/$5$4$3$2$1/;# reverse date into YYYY/MM/DD for easy sorting
$date_id_hash{$linearray[$id_field_number]} = $linearray[$date_field_number];
}

# now we have all dates in the db stored as values of %date_id_hash,
# and their id numbers = database keys as keys
my @sorted_array = sort values %date_id_hash;
...
} #end of sub

OK, now I have all dates in the format YYYY/MM/DD sorted ascending in @sorted_array.

This would remain to be done:
1) get the record for each ID based on the date (thought of doing a reverse array, but the keys won't be unique, as there presumably might be more than one date instance in the database. What to do?)
2) structure output: Break down every element of @sorted_array into year, month and day. If its year and month are the same as that of the next element, print this element and the next element. If they are not, print this element and a link to the next element.
3) think about how to code the link: perhaps just pass on month & year of the element to be linked (= next month's page) to the script, have the sub check whether there's such form input and if so, splice @sorted_array in the end to return only those elements where month and year are larger than form input.
4) would you also want a link back to entries of the previous month?

This perhaps sounds rather confusing, but that's as far as I got. If anyone else has a better idea, I'd be delighted :-)


kellner

I've try this, but still I can't display records for each month in one page.

Lembono

of course you can't, because, as I wrote, it's only the beginning, it's not a complete solution. I was hoping someone more experienced with perl would jump in to fill in the blanks.
kellner

Have you by any chance looked in the FAQ noted below under "Viewing" for a thread called "Creating A Reports Routine"
I'm sure you could get some ideas from that thread.

Depending on how you have your date field setup you may have to change this so that the day, month and year as in separate fields for the search to provide the output you need.

Let me know if that thread helps


Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/

Yes, I have. But there's no specific mod I'm looking for.
But thanks everyone, now this problem is solved after search the net and modify db.cgi and html.pl.


Lembono

could you post the solution you found, for future reference?
kellner

Where to post, it's a long line.

Lembono


I will put it somewhere in GeoCities today.

I have post it to:
http://www.geocities.com/glembono/

I hope this useful.

Lembono

Hi,

may be that i look at the problem to simple. I've set up an event database. I've createted static links to show all events of a month using standard query functionality of dbman.

Using the short/long mod you can customize the output to your needs.

To get an idea look at:
http://www.sport-page.com/...;view_records=Suchen

A typical month view is generated by a link like that:
http://www.sport-page.com/...records=View+Records

This is a live database, please do not mess up.

Regards,

Henning

I agree for anyone which has not yet starting any database by using Henning solution, sure you must have two fields that contain date information. Using the > or < is smart solution.

For those already started the database and not wish to add a second date field, then using mod I wrote, may be a good idea.

Lembono

Hi,

the query string might look as referencing two date fields. But its just one! The dbman standard query (i dont think i installed a mod for this) is parsing
Datum-gt as greater then Datum xyz
Datum-lt as lower then Datum xyz

So just one date field (in this case "Datum") is enough.

Since i used just static links per month, you don't need to customize the search form.

Regards,

Henning

Hi everyone!

I've been using DBMan for a about a year now and I did several small hacks. Nothing major but enough to know how the program works and where everything is located. What I am doing now is creating a remote sales reporting system for an internet company. I have the main report running which is the ability to view orders based on a last name, email address or phone #. That part is working fine. Right now I am working on a report similar to the one that was discussed in this thread. But here is my situation and I need some clarification on the best way to do this - although I have not actually tried to implement this yet.

This new report, an order summary report is a seperate setup of DBMan in a seperate directory but running off the same database as the order view report and just extracting less fields. One of the main queries for this report is based on a date. Such as to view a summary (a list of order #'s and total's) for a specified date range. eg May 15 - May 19. The first field of each row is a date field which looks like this:

Monday, May 19, 2003 at 15:14:03 | etc.....

What I would need to do is be able to specify a date range and display a summary of orders placed during the specified period using ascending sorting. I would like to provide two drop downs which will include various date variables such as:

From Date Dropdown:
April 2003 (form value would be April+2003 effectively displaying all orders with an April 2003 date in ascending order)
May 1, 2003 (form value would be May+1+2003)
May 2, 2003
May 3, 2003
(etc continuing for every day in the month and I'd update the month name manually at the beginning of the month)

To Date Dropdown:
Blank unselected field (to run reports from the first dropdown only such as a whole month)
May 1, 2003 (form value would be May+1+2003)
May 2, 2003
May 3, 2003
(etc continuing for every day in the month and I'd update the month name manually at the beginning of the month)

So basically my question is: what is the simplest way to accomplish this from the methods that were mentioned above? I will be sitting and experimenting, but I figured I'd ask anyway to see what all the buffs here think

Thanks!

Anyone?

I'll think about it.

But it looks a a glance that the second option (Henning) is what you want.