SHARE
TWEET

Untitled

a guest Jun 14th, 2017 83 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. function postcode2state($postcode) {
  4.   $ranges = array(
  5.     'NSW' => array(
  6.       1000, 1999,
  7.       2000, 2599,
  8.       2619, 2898,
  9.       2921, 2999
  10.     ),
  11.     'ACT' => array(
  12.       200, 299,
  13.       2600, 2618,
  14.       2900, 2920
  15.     ),
  16.     'VIC' => array(
  17.       3000, 3999,
  18.       8000, 8999
  19.     ),
  20.     'QLD' => array(
  21.       4000, 4999,
  22.       9000, 9999
  23.     ),
  24.     'SA' => array(
  25.       5000, 5999
  26.     ),
  27.     'WA' => array(
  28.       6000, 6797,
  29.       6800, 6999
  30.     ),
  31.     'TAS' => array(
  32.       7000, 7999
  33.     ),
  34.     'NT' => array(
  35.       800, 999
  36.     )
  37.   );
  38.   $exceptions = array(
  39.     872 => 'NT',
  40.     2540 => 'NSW',
  41.     2611 => 'ACT',
  42.     2620 => 'NSW',
  43.     3500 => 'VIC',
  44.     3585 => 'VIC',
  45.     3586 => 'VIC',
  46.     3644 => 'VIC',
  47.     3707 => 'VIC',
  48.     2899 => 'NSW',
  49.     6798 => 'WA',
  50.     6799 => 'WA',
  51.     7151 => 'TAS'
  52.   );
  53.  
  54.   $postcode = intval(substr($postcode,0,4));
  55.   if ( array_key_exists($postcode, $exceptions) ) {
  56.     return $exceptions[$postcode];
  57.   }
  58.  
  59.   foreach ($ranges as $state => $range)
  60.   {
  61.     $c = count($range);
  62.     for ($i = 0; $i < $c; $i+=2) {
  63.       $min = $range[$i];
  64.       $max = $range[$i+1];
  65.       if ( $postcode >= $min && $postcode <= $max ) {
  66.         return $state;
  67.       }
  68.     }
  69.   }
  70.  
  71.   return ' ';
  72. }
  73.  
  74. $servername = "127.0.0.1";
  75. $username = "tomorrow";
  76. $password = "tomorrow";
  77. $dbname = "tomorrow";
  78.  
  79. // Create connection
  80. $conn = new mysqli($servername, $username, $password, $dbname);
  81.  
  82. // Check connection
  83. if ($conn->connect_error) {
  84.     die("Connection failed: " . $conn->connect_error);
  85. }
  86.  
  87. // prepare and bind
  88. $sql="SELECT u.abn                                AS 'ABN',
  89.        u.company_name                       AS 'Company',
  90.        IF(u.abr_status, 'Y', 'N'),
  91.        u.abr_type,
  92.        'Y',
  93.        IF(u.employer_company_abn, 'Y', 'N') AS 'ABN Status',
  94.        u.employer_company_abn               AS 'Employer ABN',
  95.        u.employer_company_name              AS 'Legal Name',
  96.        u.employer_company_name              AS 'Trading Name',
  97.        '-',
  98.        Concat(u.company_street, u.company_postcode),
  99.        u.company_suburb,
  100.        u.company_postcode                   AS state,
  101.        u.company_postcode,
  102.        u.affiliate_accreditation_name,
  103.        '-',
  104.        u.affiliate_accreditation_number,
  105.               i.name,
  106.               '-',
  107.        u.affiliate_industry_body_number,
  108.        '-',
  109.        If(ISNULL(u.website), 'Y', 'N'),
  110.        u.website,
  111.  
  112.        IF(u.office_location != 'Home', 'Y', 'N'),
  113.        '-',
  114.        IF(d.current_acl_holder = 1
  115.            OR d.current_credit_representative = 1, 'Y', 'N'),
  116.        '-',
  117.        'Y',
  118.        '-',
  119.        Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
  120.        IF(d.applicant = 1, Concat(d.first_name, ',', d.last_name), ' '),
  121.        IF(d.applicant = 1, d.dob, ' '),
  122.        'Y',
  123.        '-',
  124.        '-',
  125.        Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
  126.        IF(d.applicant = 2, Concat(d.first_name, ',', d.last_name), ' '),
  127.        IF(d.applicant = 2, d.dob, ' '),
  128.        'Y',
  129.        '-',
  130.        '-',
  131.        Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
  132.        IF(d.applicant = 3, Concat(d.first_name, ',', d.last_name), ' '),
  133.        IF(d.applicant = 3, d.dob, ' '),
  134.        'Y',
  135.        '-',
  136.        '-',
  137.        Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
  138.        IF(d.applicant = 4, Concat(d.first_name, ',', d.last_name), ' '),
  139.        IF(d.applicant = 4, d.dob, ' '),
  140.        'Y',
  141.        '-',
  142.        '-',
  143.        Concat(d.first_name, ',', d.middle_name, ',', d.last_name),
  144.        IF(d.applicant = 5, Concat(d.first_name, ',', d.last_name), ' '),
  145.        IF(d.applicant = 5, d.dob, ' '),
  146.        'Y',
  147.        '-',
  148.        '-'
  149. FROM   users u,
  150.        u_lender_accreditations ac,
  151.        u_directors d,
  152.        u_industry_bodies i
  153. WHERE  ac.user_id = u.id
  154.        AND ac.lender_id = 1
  155.        AND d.user_id = u.id
  156.        AND i.id = u.affiliate_industry_body_id and u.status='approved'";
  157. $result = $conn->query($sql);
  158.  
  159. if ($result->num_rows > 0) {
  160.     // output data of each row
  161.     while($row = $result->fetch_row()) {
  162.         foreach($row as $k=>$col) {
  163.  
  164.             echo '"'.($k==13?postcode2state($col):$col).'"'.',';
  165.         }
  166.         echo " ";
  167.         echo "\n";
  168.     }
  169.  
  170. } else {
  171.    
  172. }
  173. $conn->close();
  174. ?>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top