?
Solved

between two dates

Posted on 2006-04-18
22
Medium Priority
?
202 Views
Last Modified: 2010-03-05
Hi All,

I'm writing a cgi script to pull out system informaction from a database, the information being extracted looks like this;

System  Day  Month   Year   Day   Month   Year    Type    #       Customer        Allocated
HP01       4       3       2006    5       3       2006    home   9       123 Plc            jpar
HP01      10      3       2006   15       3       2006    home   9       123 Plc            jpar
HP01      21      3       2006   25       3       2006    home   9       123 Plc            jpar


The subroutine looks like this;
$daysinmonth is just a number for the number of days, $reference is the System (above) and $frm_startmonth and $frm_year are just a month and year from a web form.


sub print_day($daysinmonth,$reference,$frm_startmonth,$frm_year)
{
   my $daycount = 1;
   my $daysinmonth = $_[0];
   my $reference = $_[1];
   my $frm_startmonth = $_[2];
   my $frm_year = $_[3];
   my $end_day;

   my($test_reference, $test_startday, $test_startmonth, $test_startyear, $test_endday, $test_endmonth, $test_endyear, $test_type, $test_number, $test_customer, $test_analy
st);
   # Loop for each system reference found...
   #
   foreach ($reference)
   {
      # for each day upto the end of the month find out what is in the database
      # for that day
      #
      while ( $daycount <= $daysinmonth)
      {
         my $sth = $dbh->prepare(qq(SELECT reference,
                                           day(start_date),
                                           month(start_date),
                                           year(start_date),
                                           day(end_date),
                                           month(end_date),
                                           year(end_date),
                                           type,
                                           test_number,
                                           customer_name,
                                           analyst_name
                                    FROM test_dates
                                    WHERE reference LIKE '$reference'
                                    AND day(start_date) = '$daycount'
                                    AND month(start_date) = '$frm_startmonth'
                                    AND year(start_date) = '$frm_year'
                                    ORDER BY reference, start_date))
                   or dienice("Could not get headings.");
         $sth->execute;

         $sth->bind_columns(undef,\$test_reference,\$test_startday,\$test_startmonth,\$test_startyear,\$test_endday,\$test_endmonth,\$test_endyear,\$test_type,\$test_number
,\$test_customer,\$test_analyst);
         ($test_reference,$test_startday,$test_startmonth,$test_startyear,$test_endday,$test_endmonth,$test_endyear,$test_type,$test_number,$test_customer,$test_analyst) =
$sth->fetchrow_array();

         if ( "$daycount" >= "test_startday" & "$daycount" <= "$test_endday" )
         {
            print "$daycount<br>($end_day) $test_startday-$test_endday";
            my $testnum = "Test$test_number";
            my $test_tip ="$test_customer<br>$test_startday/$test_startmonth/$test_startyear<br>$test_endday/$test_endmonth/$test_endyear<br>$test_type&nbsp;$test_analyst<b
r>";
#
#  print 1st html stuff here
#

         } else {
#
#  print  2nd html stuff here
#
         }
         $daycount += 1;
      }
   }
}


Apart from the messy inefficient code (I'm still learning Perl) I cannot seem to get the routing to print the 1st html output for anything but the first matching date, this is probably because once the program fetches the next row the start and end days are different

At the moment I can't think of a way round it and my current programming experience means that trying to get past this will take me several weeks work.

Can anyone suggest a solution and give some examples so I can try them out.

I'm after a quick answer so allocating 500 points for anyone willing to help and save me several days of messing about myself! :-)

If you need more information then please ask.

Cheers

Jools

0
Comment
Question by:jools
  • 12
  • 10
22 Comments
 
LVL 3

Expert Comment

by:BioI
ID: 16476674
==> you forget the $ sign in one of your mysql-queries (and I don't know why you use " " signs in your if-statement??):
 if ( "$daycount" >= "test_startday" & "$daycount" <= "$test_endday" )
changes into:
 if ( $daycount >= test_startday & $daycount <= $test_endday)

0
 
LVL 3

Expert Comment

by:BioI
ID: 16476690
(sorry for previous post: it is not in your mysql-query, but in your if-statement)

==> I use another approach for reading in variables into a subroutine (I don't know whether your method would work, I guess not? The syntax you use is correct for PHP but not for perl I think):

change:
sub print_day($daysinmonth,$reference,$frm_startmonth,$frm_year) {
    #.... your code
}

into:

sub print_day {
  my ($daysinmonth,$reference,$frm_startmonth,$frm_year) = @_;
  #.... your code
}
0
 
LVL 3

Expert Comment

by:BioI
ID: 16476707
damn: first post was very messy (I also forgot to add the $ sign):
if ( $daycount >= $test_startday & $daycount <= $test_endday)

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:BioI
ID: 16476902
I am suffering after easter-work day: you also need "&&" instead of "&"
if ( $daycount >= $test_startday && $daycount <= $test_endday)
0
 
LVL 19

Author Comment

by:jools
ID: 16477063
Thanks for the tips, like the mod using @_.
The forgotton $ was a cut/paste thing fortunately...

I've no idea why I used the quotes thing, all got rid of now, must have been one of my desperate attempts to try to get it all working :-)

Going back to the main question, I need a way of saving the endday so when the loop goes onto the next day where there will be no database entry, the script still knows to print the 1st html block.

I can't paste here what it looks like but a simple representation would be

HP01  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
                     B  B                 B   B   B  B   B   B                         B   B   B  B   B

The "B" here would mean that the entry is booked. at the moment I only get the 1st day of each booking shown, not all the days.

If you think of the view as a spreadsheet where the reference is on each row and each of the B's is assigned a colour, this is the # column in the database.


Cheers

J

0
 
LVL 19

Author Comment

by:jools
ID: 16477084
Hi again,

I had already tried this, it made no difference, using the single & was another one of my wild stabs in the dark.

==> if ( $daycount >= $test_startday && $daycount <= $test_endday)

I may need to look at how I do the whole routine.

J
0
 
LVL 3

Expert Comment

by:BioI
ID: 16478174
I don't see what you mean? I mean: I don't see the relation between the first table you describe and the problem with booked entries you show? Because apparently, you have already all information stored in your table? Or do you still have to "translate" the textfile described above into a table?

I got this output:
123 Plc<br>4/3/2006<br>5/3/2006<br>home&nbsp;jpar<br>
123 Plc<br>10/3/2006<br>15/3/2006<br>home&nbsp;jpar<br>
123 Plc<br>21/3/2006<br>25/3/2006<br>home&nbsp;jpar<br>

using this script:
(I had to change in the mysql-statement "day" into "dayofmonth" but this is probably due to my mysql-version...)

#!/usr/bin/perl -w

use DBI;

my $dbh = DBI->connect("DBI:mysql:test:localhost", 'user', 'pasw');
&print_day(30, 'HP01', 3, 2006);
 

sub print_day {
    my ($daysinmonth,$reference,$frm_startmonth,$frm_year) = @_;
    my $daycount = 1;
    my $end_day ="";
   
    my($test_reference, $test_startday, $test_startmonth, $test_startyear, $test_endday, $test_endmonth, $test_endyear, $test_type, $test_number, $test_customer, $test_analyst);
    # Loop for each system reference found...
    #
    foreach ($reference)
    {
      # for each day upto the end of the month find out what is in the database
      # for that day
      #
      
      while ( $daycount <= $daysinmonth)
      {
          my $sth = $dbh->prepare(qq(SELECT reference,
                               dayofmonth(start_date),
                               month(start_date),
                               year(start_date),
                               dayofmonth(end_date),
                               month(end_date),
                               year(end_date),
                               type,
                               test_number,
                               customer_name,
                               analyst_name
                               FROM jools
                               WHERE reference LIKE '$reference'
                               AND dayofmonth(start_date) = '$daycount'
                               AND month(start_date) = '$frm_startmonth'
                               AND year(start_date) = '$frm_year'
                               ORDER BY reference, start_date))
            or dienice("Could not get headings.");
          $sth->execute;
          
          $sth->bind_columns(undef,\$test_reference,\$test_startday,\$test_startmonth,\$test_startyear,\$test_endday,\$test_endmonth,\$test_endyear,\$test_type,\$test_number,\$test_customer,\$test_analyst);
          ($test_reference,$test_startday,$test_startmonth,$test_startyear,$test_endday,$test_endmonth,$test_endyear,$test_type,$test_number,$test_customer,$test_analyst) = $sth->fetchrow_array();
          if (! defined($test_startday) || !defined($test_endday)) {
            $daycount++;
            next;
          }
          if ( $daycount >= $test_startday && $daycount <= $test_endday ) {
            #print "$daycount<br>($end_day) $test_startday-$test_endday\n";
            my $testnum = "Test$test_number";
            my $test_tip ="$test_customer<br>$test_startday/$test_startmonth/$test_startyear<br>$test_endday/$test_endmonth/$test_endyear<br>$test_type&nbsp;$test_analyst<br>\n";
            print $test_tip;
#
#  print 1st html stuff here
#
            
          } else {
#
#  print  2nd html stuff here
#
          }
          $daycount += 1;
      }
    }
}
0
 
LVL 19

Author Comment

by:jools
ID: 16478405
I guess I should have explained that there are two tables.

The first table just lists all the systems available and also has some specification of the servers, the only information I use at the moment is the system reference.
Whereas the first table will list ALL the available systems, the second tables will only have entries for those system that are used.

For each system reference I want to get the dates from the test_dates table and then loop for each day in the month (this is where I can't get it right), the output looks like a spreadsheet as described before, unused systems are still displayed but have no entries associated with them so there a re no colours shown.

If I use the simplified example above, I see the following;

HP01  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
                     B                     B                                                B    

I think it's because as the loop gets the information for the next day it wont find anything in the database because I only store date ranges which I thought would be more efficient than populating a database with loads of duplicate entries.

There may be a better way of doing this but my knowledge of perl is only a couple of week old and I'm basically referring to books even for doing the simple loops and if structures.
I can post the script I have so far if you find it helps but it will have to be edited slightly to protect some company data.

J
0
 
LVL 3

Expert Comment

by:BioI
ID: 16485941
I would work with hashes (do you know the syntax?) (i.e. a associative array)
You can use one mysql-statement, store all the information in the hashes and print the output at the end. I mean, not looping trough different days but just loop trough your mysql-query where you select all bookings of a certain month with a mysql-query, and loop trough the mysql-query using a fetchrow_array and store everything in a hash.
subroutine below gave me the right output...


sub print_day {
    my ($daysinmonth,$reference,$frm_startmonth,$frm_year) = @_;
    my $daycount = 1;
    my $end_day ="";
   
    my($test_reference, $test_startday, $test_startmonth, $test_startyear, $test_endday, $test_endmonth, $test_endyear, $test_type, $test_number, $test_customer, $test_analyst);
   
    my $sth = $dbh->prepare(qq(SELECT reference,
                         dayofmonth(start_date),
                         month(start_date),
                         year(start_date),
                         dayofmonth(end_date),
                         month(end_date),
                         year(end_date),
                         type,
                         test_number,
                         customer_name,
                         analyst_name
                         FROM jools
                         WHERE reference LIKE '$reference'
                         AND month(start_date) = '$frm_startmonth'
                         AND year(start_date) = '$frm_year'
                         ORDER BY reference, start_date))
      or dienice("Could not get headings.");
    $sth->execute;
   
    $sth->bind_columns(undef,\$test_reference,\$test_startday,\$test_startmonth,\$test_startyear,\$test_endday,\$test_endmonth,\$test_endyear,\$test_type,\$test_number,\$test_customer,\$test_analyst);
   
    my @booking;
    for my $i (1 .. $daysinmonth) {
      $booking[$i] = 0;
    }
    while (($test_reference,$test_startday,$test_startmonth,$test_startyear,$test_endday,$test_endmonth,$test_endyear,$test_type,$test_number,$test_customer,$test_analyst) = $sth->fetchrow_array()) {
      for my $j ($test_startday .. $test_endday) {
          $booking[$j] = 1;
      }
    }
   
    print "<table>";
    print "<tr>";
    print "<td>$reference</td>";
    for my $i (1 .. $daysinmonth) {
      if ($booking[$i]) {
          print "<td><font color=\"red\">$i</font><td>";
      }else{
          print "<td>$i</td>";
      }
    }
    print "<table>";

}
0
 
LVL 19

Author Comment

by:jools
ID: 16492904
Hmmm...

I tried the changes but the script seems to be looping, I guess I screwed it up somewhere.

I'd like to post a link to a website so you can see what I'm trying to do but I don't want it available for all to see. I can't see a way of editing old posts, do you have an address I can send a quick email to and give you access for a while or perhaps send it over IM (just to give out the address, nothing else), if not perhaps I could get a moderator to remove it once you have checked it out.

Perhaps at this point posting the complete script would also help? Any thoughts?

Thanks for the assistance thus far...

J
0
 
LVL 3

Expert Comment

by:BioI
ID: 16495760
I am affraid it's not allowed to exchange email-adressed on this website :-S Maybe the temporary URL is a solution if you ask the admin?
Indeed, maybe is posting the complete script an option.
What do you mean if your script "seems to be looping": an endless while-loop?

0
 
LVL 19

Author Comment

by:jools
ID: 16496019
OK, I kinda guess it would be against the rules, I've posted to Community Support, I guess I'll get an answer soon.

I'll check out posting the complete script, or may chuck it on the website page because it'll take some time to remove the site references which I'm not suposed to give out for some reason.

I think I must have entered the updates to the script wrong because as soon as I click Search the browser takes ages to load (still going after 10 mins which I cancelled). Gotta be a typo on my part!

I'll post again soon once I have a response from CS.
0
 
LVL 19

Author Comment

by:jools
ID: 16497995
OK, I guess they are busy at the moment, I found a shorter link service that expires in 90 days, Try this out and click search so you get an idea what it's doing, this is the original version, not the recently modified one.

If you click on the "+" you'll get the full cgi script, I'll remove this after today.

http://www.bristol.ac.uk/slink/6pp6

Any tips greatly recieved...

J
0
 
LVL 3

Accepted Solution

by:
BioI earned 2000 total points
ID: 16498407
Ok, check this out...
substitute the whole "print_day" subroutine with the subroutine I pasted below...
let me know if it works, or if you are still getting error messages...
B.



sub print_day {
    my ($daysinmonth,$reference,$frm_startmonth,$frm_year) = @_;
    my $daycount = 1;
    my $end_day ="";
   
    my($test_reference, $test_startday, $test_startmonth, $test_startyear, $test_endday, $test_endmonth, $test_endyear, $test_type, $test_number, $test_customer, $test_analyst);
   
    my $sth = $dbh->prepare(qq(SELECT reference,
                         dayofmonth(start_date),
                         month(start_date),
                         year(start_date),
                         dayofmonth(end_date),
                         month(end_date),
                         year(end_date),
                         type,
                         test_number,
                         customer_name,
                         analyst_name
                         FROM test_dates
                         WHERE reference LIKE '$reference'
                         AND month(start_date) = '$frm_startmonth'
                         AND year(start_date) = '$frm_year'
                         ORDER BY reference, start_date))
      or dienice("Could not get headings.");
    $sth->execute;
   
    $sth->bind_columns(undef,\$test_reference,\$test_startday,\$test_startmonth,\$test_startyear,\$test_endday,\$test_endmonth,\$test_endyear,\$test_type,\$test_number,\$test_customer,\$test_analyst);
   
    my @info = ();
    for my $i (1 .. $daysinmonth) {
      $info[$i]{'filled'} = 0;
    }
    while (($test_reference,$test_startday,$test_startmonth,$test_startyear,$test_endday,$test_endmonth,$test_endyear,$test_type,$test_number,$test_customer,$test_analyst) = $sth->fetchrow_array()) {
      for my $i ($test_startday .. $test_endday) {
          #print "test start day => ", $test_startday, " || start month ", $test_startmonth, " || start year ", $test_startyear, "\n\n\n";
          $info[$i]{'filled'} = 1;
          $info[$i]{'testnumber'} = $test_number;
          $info[$i]{'customer'} = $test_customer;
          $info[$i]{'startday'} = $test_startday;
          $info[$i]{'startmonth'} = $test_startmonth;
          $info[$i]{'startyear'} = $test_startyear;
          $info[$i]{'endday'} = $test_endday;
          $info[$i]{'endmonth'} = $test_endmonth;
          $info[$i]{'endyear'} = $test_endyear;
          $info[$i]{'type'} = $test_type;
          $info[$i]{'analyst'} = $test_analyst;
      }
    }
   
    for my $i (1 .. $daysinmonth) {
      if ($info[$i]{'filled'} == 1) {
          my $testnum = "Test".$info[$i]{'testnumber'};
            my $test_tip =$info[$i]{'customer'}."<br>".$info[$i]{'startday'}."/".$info[$i]{'startmonth'}."/".$info[$i]{'startyear'}."<br>".$info[$i]{'endday'}."/".$info[$i]{'endmonth'}."/".$info[$i]{'endyear'}."<br>".$info[$i]{'type'}."&nbsp;".$info[$i]{'analyst'}."<br>";
          print "<td align='center' class='$testnum'>";
            print "   <div style='overflow:hidden; width:30px; height:18px;' ";
            print "        class='VAFS' style='cursor:hand;' ";
            print "        tty='3' bid='0' ";
            print "        sm='55640160' ";
            print "        em='55640700' ";
            print "        rdt='-18' rid='840' tid='-1' cid='-1' ";
            print "        tip='$test_tip'>$daycount";
            print "        &nbsp;&nbsp;";
            print "   </div>";
          print "</td>";
      }else{
          print "<td align='center' class='Test0'>";
            print "   <div style='overflow:hidden; width:30px; height:18px;' ";
            print "        class='VAFS' style='cursor:hand;' ";
            print "        tty='3' bid='0' ";
            print "        sm='55640160' ";
            print "        em='55640700' ";
            print "        rdt='-18' rid='840' tid='-1' cid='-1' ";
            print "        tip='Free Slot'>$i";
            print "        &nbsp;&nbsp;";
            print "   </div>";
            print "</td>";
          
      }
    }
}
0
 
LVL 19

Author Comment

by:jools
ID: 16498467
Thanks,

I hope by checking the site the problem is a little clearer now, I've removed the link to the cgi script BTW.
I'll get editing and post back here. Thanks for sticking with it. It's a bit more complicated than I first thought.

Cheers

J
0
 
LVL 3

Expert Comment

by:BioI
ID: 16498546
sorry, small bug found
you need to change $daycount to $i in the "if-else" loop (if-part, at "tip":

for my $i (1 .. $daysinmonth) {
      if ($info[$i]{'filled'} == 1) {
          my $testnum = "Test".$info[$i]{'testnumber'};
            my $test_tip =$info[$i]{'customer'}."<br>".$info[$i]{'startday'}."/".$info[$i]{'startmonth'}."/".$info[$i]{'startyear'}."<br>".$info[$i]{'endday'}."/".$info[$i]{'endmonth'}."/".$info[$i]{'endyear'}."<br>".$info[$i]{'type'}."&nbsp;".$info[$i]{'analyst'}."<br>";
          print "<td align='center' class='$testnum'>";
            print "   <div style='overflow:hidden; width:30px; height:18px;' ";
            print "        class='VAFS' style='cursor:hand;' ";
            print "        tty='3' bid='0' ";
            print "        sm='55640160' ";
            print "        em='55640700' ";
            print "        rdt='-18' rid='840' tid='-1' cid='-1' ";
            print "        tip='$test_tip'>$i";
            print "        &nbsp;&nbsp;";
            print "   </div>";
          print "</td>";
      }else{
          print "<td align='center' class='Test0'>";
            print "   <div style='overflow:hidden; width:30px; height:18px;' ";
            print "        class='VAFS' style='cursor:hand;' ";
            print "        tty='3' bid='0' ";
            print "        sm='55640160' ";
            print "        em='55640700' ";
            print "        rdt='-18' rid='840' tid='-1' cid='-1' ";
            print "        tip='Free Slot'>$i";
            print "        &nbsp;&nbsp;";
            print "   </div>";
            print "</td>";
          
      }
    }
0
 
LVL 19

Author Comment

by:jools
ID: 16498562
Crikey! That certainly made a difference! have a look!

Is there anyway to continue the "date" for the filled entries? I'd still need them to have the number in as opposed to using "1".

Can you also give me a quick rundown of each bit, I know this is going over and above but I don't think I'll find easy explanations in books!

Thanks

0
 
LVL 19

Author Comment

by:jools
ID: 16498597
Nice, posting must have crossed in the ether!!! :-)

Now all I lack is the understanding, can you do a quick rundown for me please (muchly appreciated).
Where did I go wrong exactly?

Once this is done I can go and get stuck on the bits to add to the database :-) that'll be another question.

J
0
 
LVL 3

Expert Comment

by:BioI
ID: 16498643
[for the remaining bug: check my previous post]

What I do is not the nicest solution, but it works ;-)
First, I create an array where each element in an array corresponds to 1 day in the month e.g. 10 = 10 march. For each element in the array, you can store "filled" which indicates whether this day is booked or not, and set it to 0 by default. Also other values can be stored for each element, like start day, testnumber, .... This is the following syntax:
$info[10]{'filled'} = 1: march 10th is booked
$info[10]{'analyst'} = 'xxx': analyst xxx is assigned to this job
...
So I loop trough the different rows in your table, and for each entry in your table, I store the information in an array as mentioned above.
Afterwards, I loop trough the array and write everything to a HTML output (copy-past from your code...)

enjoy ;-)
0
 
LVL 3

Expert Comment

by:BioI
ID: 16498713
I think the error is that you should put this fetchrow_array-command: ($test_reference,$test_startday,$test_startmonth,$test_startyear,$test_endday,$test_endmonth,$test_endyear,$test_type,$test_number,$test_customer,$test_analyst) = $sth->fetchrow_array();
into a while-loop:
for daycount (0 .. 31) {
     while (... = $sth->fetchrow_arrar()) {
               ## your code ###
     }
}

In the initial script, you were only looking at the first entry that matched your mysql-query
0
 
LVL 19

Author Comment

by:jools
ID: 16498732
You make it sound easy, I'd never have know to do it that way.

It certinly looks much better then I had before and it would have taken me weeks to do this on my own.

Thank you for your tireless efforts and don't stray too far from the group, I may well be back for more :-).

Points very well deserved.

Thanks

Jools
0
 
LVL 3

Expert Comment

by:BioI
ID: 16498801
no prob!
cu around!

B.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question