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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
|
<?php
session_start();
?>
<?php
$date1=$_SESSION['date1'];
$date2=$_SESSION['date2'];
$date11=$_SESSION['date11'];
$date22=$_SESSION['date22'];
$id_operator=$_SESSION["id_operator"];
require_once('Connections/connex1.php');
mysql_select_db($database_connex1,$connex1);
$insertSQL1 = sprintf("select currency,amount from currency where currency = '".$_SESSION["currency"]."'");
$query1 = mysql_query($insertSQL1,$connex1) or die(mysql_error()) ;
$row1 = mysql_fetch_row($query1);
$query3="$bol1 (Yup_number='$room_number')";
$timestamp = strtotime($date2);
$timestamp2 = strtotime($date1);
$day5=$timestamp+(3600 * 24 * 1);
$day6=$timestamp2+(3600 * 24 * 1);
$date_fin1 = date("Y-m-d ",$day5);
$date_deb1 = date("Y-m-d ",$day6);
$insertSQL1 = sprintf("select ID_hotel_invoice,ID_hotel,Yup_number, room,First_ID_call,Last_ID_call,Amount, Amount_taxe_1, Amount_taxe_2, Global_amount,Payed ,payement_method,Last_payement_date,Customer_first_name, Customer_last_name, customer_adress, Customer_city,Customer_postal_code, Customer_country,telephone, mail,ID_global_invoice, buy_amount from hotel_invoice where ID_hotel='".$_SESSION['id_hotel']."' and Last_payement_date between '$date11 ' and '$date22 ' ");
if ($bol1=="and") $insertSQL1=$insertSQL1." ".$query3;
if ($bol1=="or") $insertSQL1=$insertSQL1." ".$query3;
//echo $insertSQL1;
$query2 = mysql_query($insertSQL1,$connex1) or die(mysql_error());
$row1= mysql_fetch_row($query2);
//$row8 = mysql_fetch_array($query);
$id_glob=$row1[21];
$amount_glob=0;
$amount_cost=0;
$amount_taxe1=0;
$amount_taxe2=0;
$insertSQL11 = sprintf("select currency,amount from currency where currency = '".$_SESSION["currency"]."'");
$query11 = mysql_query($insertSQL11,$connex1) or die(mysql_error()) ;
$row11 = mysql_fetch_row($query11);
if ($row11[0] =='Chinese Yuan CN'){
$curr =$row11[1] ;
$currency ='Yuan';
$real_curr ='Chinese Yuan CN';
}
else{
$curr =1 ;
$currency ='US $';
$real_curr ='US $';
}
//echo $curr;
while($row1){
$amount_glob=round(($amount_glob+$row1[6]*$curr),2);
$amount_cost=round(($amount_cost+$row1[22]*$curr),2);
$amount_taxe1=round(($amount_taxe1+$row1[7]*$curr),2);
$amount_taxe2=round(($amount_taxe2+$row1[8]*$curr),2);
$row1= mysql_fetch_row($query2);
}
$amount_glob_cur = "".$amount_glob." ".$currency."";
$amount_cost_cur = "".$amount_cost." ".$currency."";
$amount_taxe1_cur = "".$amount_taxe1." ".$currency."";
$amount_taxe2_cur = "".$amount_taxe2." ".$currency."";
$benefice=$amount_glob-$amount_cost;
$benefice_cur = "".$benefice." ".$currency."";
?>
<?php
define("FILENAME" ,"periode_bill_".$date11."_to_".$date22.".xls");
//define("FILENAME","C:/tests.xls");
if(file_exists(FILENAME))
{
unlink(FILENAME);
}
$excel=new COM("Excel.application");//Instanciation de l'objet COM
$excel->sheetsinnewworkbook=1;//Une seule feuille dans le document
$excel->Workbooks->Add();//Ajout d'un classeur
$book=$excel->Workbooks(1);//$book contient le classeur actif
$sheet=$book->Worksheets(1);//$sheet contient la feuille active
$sheet->Name="Graphique";//Attribution d'un nom à la feuille
$cell=$sheet->Range('A1');
$cell->value='Number'; //Titre de mes colonnes
$cell=$sheet->Range('B1');
$cell->value='Room Number'; //Nombre de questions qu'on a posées
$cell=$sheet->Range('C1');
$cell->value='YUP Number'; //Nombre de questions qu'on a posées
$cell=$sheet->Range('D1');
$cell->value='Periodically Invoice Amount('.$currency.')'; //Nombre de questions qu'on a posées
$cell=$sheet->Range('E1');
$cell->value='Periodically Un-invoice Amount('.$currency.')'; //Nombre de questions qu'on a posées
$cell=$sheet->Range('F1');
$cell->value='Uninvoice amount('.$currency.')';
$j=1;
$c_amount=0;
$c_amount_call=0;
$c_amount_total_call=0;
while($row){
require_once('Connections/connex1.php');
mysql_select_db($database_connex1,$connex1);
$insertSQL2 = sprintf("select Yup_number,ID_hotel_invoice,ID_hotel, room,First_ID_call,Last_ID_call,Amount, Amount_taxe_1, Amount_taxe_2, Global_amount,Payed ,payement_method,Last_payement_date,Customer_first_name, Customer_last_name, customer_adress, Customer_city,Customer_postal_code, Customer_country,telephone, mail,ID_global_invoice,buy_amount from hotel_invoice where ID_hotel='".$_SESSION['id_hotel']."' and Yup_number='$row[0]' and Last_payement_date between '$date11' and '$date22' ");
$query2 = mysql_query($insertSQL2,$connex1) or die(mysql_error());
$row2 = mysql_fetch_row($query2);
$amount = 0;
while($row2){
$amount=round(($row2[6]*$curr),2);
$amount_tot=round(($row2[22]*$curr),2);
$taxe1=round(($row2[7]*$curr),2);
$taxe2=round(($row2[7]*$curr),2);
$glob_amount1 = round(($row2[9]*$curr),2);
$amount_curr = "".$amount." ".$currency."";
$amount_tot_curr = "".$amount_tot." ".$currency."";
$taxe1_curr = "".$taxe1." ".$currency."";
$taxe2_curr = "".$taxe2." ".$currency."";
$glob_amount1_curr = "".$glob_amount1." ".$currency."";
$row2= mysql_fetch_row($query2);
}
$c_amount =$c_amount+$amount;
require_once('Connections/connex4.php');
mysql_select_db($database_connex4,$connex4);
$insertSQL4 = sprintf("select ID,username,sessionbill,buycost from cc_call where username='$row[0]' and id >$row[1] and starttime between '$date11' and '$date22' ;");
$query4 = mysql_query($insertSQL4,$connex4) or die(mysql_error()) ;
$row4 = mysql_fetch_row($query4);
$amount_call = 0;
$buy_amount_call=0;
while($row4){
$amount_call = round(($amount_call+$row4[2]*$curr),2);
$buy_amount_call= round(($buy_amount_call+$row4[3]*$curr),2);
$row4 = mysql_fetch_row($query4);
}
$c_amount_call =$c_amount_call+$amount_call;
$insertSQL14 = sprintf("select ID,username,sessionbill,buycost from cc_call where username='$row[0]' and id >$row[1] and starttime between '2000-01-01 0:00' and '$date22' ;");
$query14 = mysql_query($insertSQL14,$connex4) or die(mysql_error()) ;
$row14 = mysql_fetch_row($query14);
$amount_total_call = 0;
while($row14){
$amount_total_call = round(($amount_total_call+$row14[2]*$curr),2);
//$buy_amount_call= round(($buy_amount_call+$row4[3]*$curr),2);
$row14 = mysql_fetch_row($query14);
}
$c_amount_total_call =$c_amount_total_call+$amount_total_call;
$cell=$sheet->Range('A'.$j+1);
$cell->value=$j; // Zéros2 jusquà Zéros6
$cell=$sheet->Range('B'.$j+1);
$cell->value=$row[2]; // de 4 à 12 questions
$cell=$sheet->Range('C'.$j+1);
$cell->value=$row[0];
$cell=$sheet->Range('D'.$j+1);
$cell->value=$amount;
$cell=$sheet->Range('E'.$j+1);
$cell->value=$amount_call;
$cell=$sheet->Range('F'.$j+1);
$cell->value=$amount_total_call;
$cell=$sheet->Range('F'.$j+1);
$cell->value=$amount_total;
//$fichier .= "".$j.";".$row[2].";".$row[0].";".$amount.";".$amount_call.";".$amount_total_call.";".$amount_total."\n";
/*printf("<tr><td><input type=text readonly name=room value=\"$j\" class=style11 size=10></td>");
printf("<td><input type=text readonly name=room value=\"$row[2]\" class=style11 size=10></td>");
printf("<td><input type=text readonly name=yup_number value=\"$row[0]\" class=style11 size=10></td>");
printf("<td><input type=text readonly name=amount value=\"$amount\" class=style11 size=12></td>");
printf("<td><input type=text readonly name=amount value=\"$amount_call\" class=style11 size=12></td>");
printf("<td><input type=text readonly name=amount value=\"$amount_total_call\" class=style11 size=12></td></tr>");
// printf("<td><input type=text readonly name=amount value=\"$amount_total\" class=style11 size=12></td>");*/
$row=mysql_fetch_row($query);
$j++;
}
/*for ($i=2;$i<7;$i++)
{
$cell=$sheet->Range('A'.$i);
$cell->value='Zéro'.($i-1); // Zéros2 jusquà Zéros6
$cell=$sheet->Range('B'.$i);
$cell->value=2*$i; // de 4 à 12 questions
}*/
$book->open(FILENAME);//Enregistrement du document
unset($sheet);//Libération de $sheet
unset($book);//Libération de $book
$excel->Workbooks->Close();//Fermeture du classeur
$excel->Quit();//On quitte Excel
unset($excel);//Libération de l'objet $excel
?> |
Partager