Difference between revisions of "Script requete"

Latest revision as of 15:16, 21 June 2006

 
 <?php
 
 function add_book($login, $ID, $start_date, $end_date, $aircraft_num, $member_num, $slot_type, $inst_num, $free_seats, $comments)
 {
   $now = date("Y-m-d G:i:s");
   $query = 'insert into journal values (\''.$login.'\', null, \''.$now.'\', \'book_Alone\', \'record_book\')';
   mysql_query($query);
 
   $num_log = mysql_insert_id();
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'ID\', \''.$ID.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'START_DATE\', \''.$start_date.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'END_DATE\', \''.$end_date.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'AIRCRAFT_NUM\', \''.$aircraft_num.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'MEMBER_NUM\', \''.$member_num.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'SLOT_TYPE\', \''.$slot_type.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'INST_NUM\', \''.$inst_num.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'FREE_SEATS\', \''.$free_seats.'\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'booking\', \'comments\', \''.$comments.'\')';
   mysql_query($query);
 }
 
 function delete_book($login, $ID)
 {
   $now = date("Y-m-d G:i:s");
   $query = 'insert into journal values (\''.$login.'\', null, \''.$now.'\', \'book_Alone\', \'delete_book\')';
   mysql_query($query);
 
   $num_log = mysql_insert_id();
 
   $query = 'insert into log values ('.$num_log.', \'delete\', \'booking\', \'ID\', \''.$ID.'\')';
   mysql_query($query);
 }
 
 function clean_log()
 {
   $query = 'delete from log';
   mysql_query($query);
 
   $query = 'delete from journal';
   mysql_query($query);
 }
 
 function create_aircraft($login)
 {
   $now = date("Y-m-d G:i:s");
   $query = 'insert into journal values (\''.$login.'\', null, \''.$now.'\', \'book_Alone\', \'add_modify_aircraft\')';
   mysql_query($query);
   $num_log = mysql_insert_id();
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'aircraft\', \'NUM\', \'1\')';
   mysql_query($query);
 
   $query = 'insert into log values ('.$num_log.', \'insert\', \'aircraft\', \'CALLSIGN\', \'B-52\')';
   mysql_query($query);
 }
 
 function requete()
 {
 $query = 'select
 L_add_modify_aircraft_CALLSIGN.field_value,
 L_record_book_START_DATE.field_value,
 L_record_book_END_DATE.field_value,
 J_delete_book.date_log
 
 from
 journal J_delete_book,
 journal J_add_modify_aircraft,
 journal J_record_book,
 log L_delete_book,
 log L_add_modify_aircraft_NUM,
 log L_add_modify_aircraft_CALLSIGN,
 log L_record_book_ID,
 log L_record_book_START_DATE,
 log L_record_book_END_DATE,
 log L_record_book_AIRCRAFT_NUM
 
 where 
 J_delete_book.action = \'delete_book\'
 and J_add_modify_aircraft.action = \'add_modify_aircraft\'
 and J_record_book.action = \'record_book\'
 
 and J_delete_book.login = \'mezza\'
 and J_record_book.login = \'mezza\'
 
 and J_delete_book.num_log = L_delete_book.num_log
 and J_add_modify_aircraft.num_log = L_add_modify_aircraft_NUM.num_log
 and J_add_modify_aircraft.num_log = L_add_modify_aircraft_CALLSIGN.num_log
 and J_record_book.num_log = L_record_book_ID.num_log
 and J_record_book.num_log = L_record_book_START_DATE.num_log
 and J_record_book.num_log = L_record_book_END_DATE.num_log
 and J_record_book.num_log = L_record_book_AIRCRAFT_NUM.num_log
 
 and L_delete_book.table_name = \'booking\'
 and L_delete_book.field_name = \'ID\'
 and L_record_book_ID.table_name = \'booking\'
 and L_record_book_ID.field_name = \'ID\'
 and L_delete_book.field_value = L_record_book_ID.field_value
 
 and L_record_book_AIRCRAFT_NUM.table_name = \'booking\'
 and L_record_book_AIRCRAFT_NUM.field_name = \'AIRCRAFT_NUM\'
 and L_add_modify_aircraft_NUM.table_name = \'aircraft\'
 and L_add_modify_aircraft_NUM.field_name = \'NUM\'
 and L_record_book_AIRCRAFT_NUM.field_value = L_add_modify_aircraft_NUM.field_value
 
 and L_add_modify_aircraft_CALLSIGN.table_name = \'aircraft\'
 and L_add_modify_aircraft_CALLSIGN.field_name = \'CALLSIGN\'
 
 and L_record_book_START_DATE.table_name = \'booking\'
 and L_record_book_START_DATE.field_name = \'START_DATE\'
 
 and L_record_book_END_DATE.table_name = \'booking\'
 and L_record_book_END_DATE.field_name = \'END_DATE\'
 
 and L_record_book_START_DATE.field_value > J_delete_book.date_log
 and date_sub(L_record_book_START_DATE.field_value, interval 1 day) < J_delete_book.date_log';
 
  $result = mysql_query($query);
  if($result)
    {
      echo 'aircraft -- start_date -- end_date -- delete_date<br>';
      while($name_row = mysql_fetch_row($result))
        {
 	 echo $name_row[0].' -- '.$name_row[1].' -- '.$name_row[2].' -- '.$name_row[3].'<br>';
        }
    }
  else
    {
      echo 'La requete a échoué.<br>';
    }
 }
 
 /********
  * main *
  ********/
 
 mysql_connect('localhost', 'mezza', '');
 mysql_select_db('openflyers');
 
 clean_log();
 
 create_aircraft('mezza');
 add_book('mezza', 1, '2006-06-01 00:00:00', '2006-06-02 00:00:00', 1, 1, 1, 1, 1, 'no comment');
 add_book('mezza', 2, '2006-06-03 00:00:00', '2006-06-05 00:00:00', 1, 1, 1, 1, 1, 'no comment');
 add_book('mezza', 3, '2006-06-20 20:00:00', '2006-06-22 21:00:00', 1, 1, 1, 1, 1, 'no comment');
 add_book('mezza', 4, '2006-06-25 00:00:00', '2006-06-25 10:00:00', 1, 1, 1, 1, 1, 'no comment');
 add_book('mezza', 5, '2006-06-19 22:00:00', '2006-06-21 14:00:00', 1, 1, 1, 1, 1, 'no comment');
 delete_book('mezza', 1);
 delete_book('mezza', 2);
 delete_book('mezza', 3);
 delete_book('mezza', 4);
 delete_book('mezza', 5);
 
 requete();
 ?>