hi all
ive written a script to export some data that i want but it seems that alot of the data is missing in the exported cvs file.
please take a look at the script below to see if u can spot any mistakes or whther the sql is incorrect.
<?php
// exportData.php
// use to export specified data into a csv file
include("accesscontrol.php");
// export statement data
function decimalNumber($number) {
$number = number_format($number, 2, '.', '');
return $number;
}
echo "<html>";
echo "<head>";
echo "</head>";
// echo "<body onLoad=history.go(-1)>";
echo "<br>";
echo "<br>";
echo "<br>";
echo "<br>";
echo "<br>";
echo '<br>';
$dateFrom = $dateFromYear."-".$dateFromMonth."-".$dateFromDate;
$dateTo = $dateToYear."-".$dateToMonth."-".$dateToDate;
$yearto = substr($dateToYear, -2, 2);
//$dateFrom = $dateFromDate."/".$dateFromMonth."/".$dateFromYear;
//$dateTo = $dateToDate."/".$dateToMonth."/".$dateToYear;
echo "<center><h3>You may open you file by clicking <a href=".$file.">here</a>. Or go back and append more statement data to the file</h3></center>";
// $sql = "SELECT clients.ClientID, clients.ClientFirstname, clients.ClientSurname, clients.ClientAddress, clients.ClientPostcode, landlord_statement.RentAgreed, landlord_statement.Repairs, landlord_statement.Other, landlord_statement.SetupFee, landlord_statement.Furniture, landlord_statement.BBF, landlord_statement.Total, SUM((landlord_statement.RentAgreed + landlord_statement.BBF)-(landlord_statement.Repairs + landlord_statement.Other + landlord_statement.SetupFee + landlord_statement.Furniture)) as SUM, clients.PropertyAddress, clients.PropertyPostcode FROM landlord_statement, clients WHERE clients.ClientID = landlord_statement.LandlordID AND clients.Status = 0 AND landlord_statement.Can = 0 AND ChequeDateFrom >= '2002-04-01' AND ChequeDateTo <= '2002-04-31' GROUP BY clients.ClientID";
if ($select == "landlord_statement") {
$sql = "SELECT landlord_statement.statementno, landlord_statement.StatementDate, Clients.RentAgreed, landlord_statement.BBF, landlord_statement.Repairs, landlord_statement.Furniture, landlord_statement.SetupFee, landlord_statement.Other, landlord_statement.TotalExpenses, Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.PropertyAddress, Clients.PropertyPostcode, landlord_statement.ChequeDateTo, landlord_statement.ChequeDateFrom, Clients.PaymentMethod, Clients.Status, landlord_statement.Can, Clients.AutoNotes, Clients.ClientType FROM landlord_statement, Clients WHERE landlord_statement.landlordID = Clients.ClientID AND Clients.Status = 0 AND landlord_statement.Can = 0 AND landlord_statement.ChequeDateFrom >= '$dateFrom' AND landlord_statement.ChequeDateFrom <= '$dateTo'";
$message = " landlords to file";
}elseif ($select == "supplier_statement"){
$sql = "SELECT supplier_statement.StatementDate, supplier_statement.StatementNo, Clients.RentAgreed, supplier_statement.BBF, supplier_statement.Repairs, supplier_statement.Furniture, supplier_statement.SetupFee, supplier_statement.Other, supplier_statement.TotalExpenses, Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.PropertyAddress, Clients.PropertyPostcode, supplier_statement.ChequeDateTo, supplier_statement.ChequeDateFrom, Clients.PaymentMethod, Clients.Status, supplier_statement.Can, Clients.AutoNotes, Clients.ClientType FROM supplier_statement, Clients WHERE Clients.ClientID = supplier_statement.SupplierID AND Clients.Status = 0 AND supplier_statement.Can = 0 AND supplier_statement.ChequeDateFrom BETWEEN '$dateFrom' AND '$dateTo'";
// $sql = "SELECT Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.ClientAddress, Clients.ClientPostcode, Clients.PropertyAddress, Clients.PropertyPostcode, SUM((supplier_statement.RentAgreed+supplier_statement.BBF)-(supplier_statement.Repairs + supplier_statement.Furniture + supplier_statement.Other)) as SUM FROM Clients, supplier_statement WHERE Clients.ClientID = supplier_statement.SupplierID AND Clients.Status = 0 AND supplier_statement.Can = 0 AND supplier_statement.ChequeDateFrom BETWEEN '$dateFrom' AND '$dateTo' GROUP BY Clients.ClientID ";
$message = " suppliers to file";
} else {
// else its staff
$sql = "SELECT staff_statement.StatementDate, staff_statement.StatementNo, Clients.RentAgreed, staff_statement.BBF, staff_statement.Repairs, staff_statement.Furniture, staff_statement.SetupFee, staff_statement.Other, staff_statement.TotalExpenses, Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.PropertyAddress, Clients.PropertyPostcode, staff_statement.ChequeDateTo, staff_statement.ChequeDateFrom, Clients.PaymentMethod, Clients.Status, staff_statement.Can, Clients.AutoNotes, Clients.ClientType FROM staff_statement, Clients WHERE Clients.ClientID = staff_statement.StaffID AND Clients.Status = 0 AND staff_statement.Can = 0 AND staff_statement.ChequeDateFrom BETWEEN '$dateFrom' AND '$dateTo'";
// $sql = "SELECT Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.ClientAddress, Clients.ClientPostcode, Clients.PropertyAddress, Clients.PropertyPostcode, SUM((staff_statement.RentAgreed + staff_statement.BBF)-(staff_statement.Repairs + staff_statement.Furniture + staff_statement.Other)) as SUM FROM Clients, staff_statement WHERE Clients.ClientID = staff_statement.StaffID AND Clients.Status = 0 AND staff_statement.Can = 0 AND staff_statement.ChequeDateFrom BETWEEN '$dateFrom' AND '$dateTo' GROUP BY Clients.ClientID ";
$message = " staff to file";
} // end if
// echo "<b>".$sql."</b><br><br><hr>";
$query_result = mysql_query($sql) or die("error :".mysql_error());
$yearFrom = "2002";
$yearTo = "2003";
//echo '<table class="maintable" width=100% border=1 cellpadding="0" cellspacing="0">';
//echo '<tr><th><center><b>No</b></center></th><th><center><b>Landlords Full Name</b></center></th><th><center><b>Landlords Address</b></center></th><th><center><b>Postcode</b></center></th><th><center><b>Amount Paid</b></center></th><th><center><b>Each Let Property Address</b></center></th><th><center><b>Property Postcode</b></center></th><th><center><b>Tax Year</b></center></th><th><center><b>Company Name</b></center></th><th><center><b>Landlord Ref</b></center></th></tr>';
if ($query_result) {
$count = 1;
// $file = "c:\data.csv";
$title = "Statement Number, Cheque Number, Rent Agreed, BBF, Repairs, Furniture, Setup Fee, Other, Total Expenses, Total, ClientID, Client Name, Property Address, DateFrom, DateTo, PaymentMethod, ClientType \r\n";
$handle = fopen ($file, "a+");
if (!fwrite($handle,$title)) {
print "Cannot write to file ($filename)<br>";
exit;
}
// print $count.") Success, wrote ($content) to file ($file)<br><br>";
fclose($handle);
echo $sql."<br>";
while($row = @mysql_fetch_array($query_result)) {
$totalExp = $row['Repairs'] + $row['Furniture'] + $row['Other'] + $row['SetupFee'];
$income = $row['BBF'] + $row['RentAgreed'];
//$net = $income - $totalExp;
// echo $sql."<br>";
$content = $row['StatementNo'].",".$row['StatementDate'].",".$row['RentAgreed'].",".$row['BBF'].",".$row['Repairs'].",".$row['Furniture'].",".$row['SetupFee'].",".$row['Other'].",".$row[Repairs]+$row[Furniture]+$row[Other]+$row[SetupFee].",".($income - $totalExp).",".$row['ClientID'].",".$row['ClientFirstname']." ".$row['ClientSurname'].",".$row['PropertyAddress'].",".$dateFrom.",".$dateTo.",".$row['PaymentMethod'].",".$row['ClientType']."\r\n";
echo "<b>".$content."</b><br><br>";
$handle = fopen ($file, "a+");
if (!fwrite($handle,$content)) {
print "Cannot write to file ($filename)<br>";
exit;
}
// print $count.") Success, wrote ($content) to file ($file)<br><br>";
$count++;
fclose($handle);
} // end while.
} else {
echo 'error again!! : ' . mysql_error();
} // end if.
// echo '</table>';
$count = $count -1;
//print "<b>Finished ".$count." landlords have been written to the file ...... please allocate $file</b>";
echo "<script type=text/javascript>";
//echo 'alert("Finished ".$count." landlords have been written to the file ...... please allocate $file")';
echo 'alert("Finished writing '.$count.$message.' ...... please locate your CVS file at: '.$file.'")';
echo "</script>";
echo "</body>";
echo "</html>";
?>
