<?php

function postcode2state($postcode) {
  $ranges = array(
    'NSW' => array(
      1000, 1999,
      2000, 2599,
      2619, 2898,
      2921, 2999
    ),
    'ACT' => array(
      200, 299,
      2600, 2618,
      2900, 2920
    ),
    'VIC' => array(
      3000, 3999,
      8000, 8999
    ),
    'QLD' => array(
      4000, 4999,
      9000, 9999
    ),
    'SA' => array(
      5000, 5999
    ),
    'WA' => array(
      6000, 6797,
      6800, 6999
    ),
    'TAS' => array(
      7000, 7999
    ),
    'NT' => array(
      800, 999
    )
  );
  $exceptions = array(
    872 => 'NT',
    2540 => 'NSW',
    2611 => 'ACT',
    2620 => 'NSW',
    3500 => 'VIC',
    3585 => 'VIC',
    3586 => 'VIC',
    3644 => 'VIC',
    3707 => 'VIC',
    2899 => 'NSW',
    6798 => 'WA',
    6799 => 'WA',
    7151 => 'TAS'
  );

  $postcode = intval(substr($postcode,0,4));
  if ( array_key_exists($postcode, $exceptions) ) {
    return $exceptions[$postcode];
  }

  foreach ($ranges as $state => $range)
  {
    $c = count($range);
    for ($i = 0; $i < $c; $i+=2) {
      $min = $range[$i];
      $max = $range[$i+1];
      if ( $postcode >= $min && $postcode <= $max ) {
        return $state;
      }
    }
  }

  return ' ';
}

$servername = "127.0.0.1";
$username = "tomorrow";
$password = "tomorrow";
$dbname = "tomorrow";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$sql="SELECT u.abn                                AS 'ABN',
       u.company_name                       AS 'Company',
       IF(u.abr_status, 'Y', 'N'),
       u.abr_type,
       'Y',
       IF(u.employer_company_abn, 'Y', 'N') AS 'ABN Status',
       u.employer_company_abn               AS 'Employer ABN',
       u.employer_company_name              AS 'Legal Name',
       u.employer_company_name              AS 'Trading Name',
       '-',
       Concat(u.company_street, u.company_postcode),
       u.company_suburb,
       u.company_postcode                   AS state,
       u.company_postcode,
       u.affiliate_accreditation_name,
       '-',
       u.affiliate_accreditation_number,
              i.name,
              '-',
       u.affiliate_industry_body_number,
       '-',
       If(ISNULL(u.website), 'Y', 'N'),
       u.website,

       IF(u.office_location != 'Home', 'Y', 'N'),
       '-',
       IF(d.current_acl_holder = 1
           OR d.current_credit_representative = 1, 'Y', 'N'),
       '-',
       'Y',
       '-',
       Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
       IF(d.applicant = 1, Concat(d.first_name, ',', d.last_name), ' '),
       IF(d.applicant = 1, d.dob, ' '),
       'Y',
       '-',
       '-',
       Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
       IF(d.applicant = 2, Concat(d.first_name, ',', d.last_name), ' '),
       IF(d.applicant = 2, d.dob, ' '),
       'Y',
       '-',
       '-',
       Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
       IF(d.applicant = 3, Concat(d.first_name, ',', d.last_name), ' '),
       IF(d.applicant = 3, d.dob, ' '),
       'Y',
       '-',
       '-',
       Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
       IF(d.applicant = 4, Concat(d.first_name, ',', d.last_name), ' '),
       IF(d.applicant = 4, d.dob, ' '),
       'Y',
       '-',
       '-',
       Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
       IF(d.applicant = 5, Concat(d.first_name, ',', d.last_name), ' '),
       IF(d.applicant = 5, d.dob, ' '),
       'Y',
       '-',
       '-'
FROM   users u,
       u_lender_accreditations ac,
       u_directors d,
       u_industry_bodies i
WHERE  ac.user_id = u.id
       AND ac.lender_id = 1
       AND d.user_id = u.id
       AND i.id = u.affiliate_industry_body_id and u.status='approved'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_row()) {
        foreach($row as $k=>$col) {

        	echo '"'.($k==13?postcode2state($col):$col).'"'.',';
        }
        echo " ";
        echo "\n";
    }

} else {

}
$conn->close();
?>