between two dates

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

LVL 19
joolsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
BioICommented:
==> 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
 
BioICommented:
(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
 
BioICommented:
damn: first post was very messy (I also forgot to add the $ sign):
if ( $daycount >= $test_startday & $daycount <= $test_endday)

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
BioICommented:
I am suffering after easter-work day: you also need "&&" instead of "&"
if ( $daycount >= $test_startday && $daycount <= $test_endday)
0
 
joolsAuthor Commented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
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
 
joolsAuthor Commented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
joolsAuthor Commented:
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
 
BioICommented:
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
 
joolsAuthor Commented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
[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
 
BioICommented:
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
 
joolsAuthor Commented:
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
 
BioICommented:
no prob!
cu around!

B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.