<?php
  class Import_debtors
  {
      function __construct($delimiter=';')
      {
          include_once('../Bin/init.php');
          include_once('../Bin/Security.php');
          
          $debtors= fopen('debtors.csv','r');
          foreach(array_flip(fgetcsv($debtors,0,$delimiter)) as $column=>$index)
          {
              $$column=$index;
          }
          
          $currencies=array('EUR'=>1,'GBP'=>2,'USD'=>3);
          
          $query = new Bin_Query();
          while($debtor=fgetcsv($debtors,0,$delimiter))
          {
              if ($debtor[0]!='')
              {
                  $query->updateQuery('INSERT INTO address_details (address_line_one,address_line_two,address_city,address_state,address_country,address_zipcode,address_phone_country,address_phone_code,address_phone,address_fax_country,address_fax_code,address_fax,address_email) 
                               VALUES("'.$debtor[$PADDRESSLINE1].'","'.$debtor[$PADDRESSLINE2].'","'.$debtor[$PCITY].'","'.$debtor[$PCOUNTY].'",'.(int)$debtor[$PCOUNTRY].',"'.$debtor[$PPOSTCODE].'","'.$debtor[$PPHONECOUNTRY].'","'.$debtor[$PPHONECODE].'","'.$debtor[$PPHONE].'","'.$debtor[$PFAXCOUNTRY].'","'.$debtor[$PFAXCODE].'","'.$debtor[$PFAX].'","'.$debtor[$PEMAILADDRESS].'")');
                  
                  $debtor_address_id=$query->insertid;
                  
                  $query->updateQuery('INSERT INTO debtors_master(debtor_master_id,debtor_name,debtor_company_reg_no,debtor_trade_name,debtor_previous_name,debtor_status,debtor_address_id,debtor_revenue,created_date) 
                                VALUES(NULL,"'.$debtor[$DEBTORNAME].'","'.$debtor[$REGNO].'","'.$debtor[$TRADINGAS].'","'.$debtor[$PREVIOUSNAME].'",1,'.$debtor_address_id.','.$debtor[$PREDICTEDANNUALREVENUE].',NOW())');
                  $debtor_master_id=$query->insertid;
                  
                  $query->updateQuery('UPDATE debtors_master 
                                        SET master_reference_id=(CONCAT(DATE_FORMAT(created_date,"%Y%m%d"), "000000") + debtor_master_id) 
                                       WHERE debtor_master_id='.$debtor_master_id);
                  
                  if (empty($debtor[$BADDRESSLINE1]) AND empty($debtor[$BADDRESSLINE2]) AND empty($debtor[$BCITY]))
                  {
                      $query->updateQuery('INSERT INTO address_details (address_line_one,address_line_two,address_city,address_state,address_country,address_zipcode,address_phone_country,address_phone_code,address_phone,address_fax_country,address_fax_code,address_fax,address_email) 
                               VALUES("'.$debtor[$PADDRESSLINE1].'","'.$debtor[$PADDRESSLINE2].'","'.$debtor[$PCITY].'","'.$debtor[$PCOUNTY].'",'.(int)$debtor[$PCOUNTRY].',"'.$debtor[$PPOSTCODE].'","'.$debtor[$PPHONECOUNTRY].'","'.$debtor[$PPHONECODE].'","'.$debtor[$PPHONE].'","'.$debtor[$PFAXCOUNTRY].'","'.$debtor[$PFAXCODE].'","'.$debtor[$PFAX].'","'.$debtor[$PEMAILADDRESS].'")');
                  }
                  else
                  {
                      $query->updateQuery('INSERT INTO address_details (address_line_one,address_line_two,address_city,address_state,address_country,address_zipcode,address_phone_country,address_phone_code,address_phone,address_fax_country,address_fax_code,address_fax,address_email) 
                               VALUES("'.$debtor[$BADDRESSLINE1].'","'.$debtor[$BADDRESSLINE2].'","'.$debtor[$BCITY].'","'.$debtor[$BCOUNTY].'",'.(int)$debtor[$BCOUNTRY].',"'.$debtor[$BPOSTCODE].'","'.$debtor[$BPHONECOUNTRY].'","'.$debtor[$BPHONECODE].'","'.$debtor[$BPHONE].'","'.$debtor[$BFAXCOUNTRY].'","'.$debtor[$BFAXCODE].'","'.$debtor[$BFAX].'","'.$debtor[$BEMAILADDRESS].'")');
                  }
                  
                  
                  $billing_address_id=$query->insertid;
                  
                  $query->updateQuery('INSERT INTO address_details (address_country,address_phone_country,address_phone_code,address_phone,address_email) 
                               VALUES('.$debtor[$PCOUNTRY].',"'.$debtor[$DEBTORCONTACTPHONECOUNTRY].'","'.$debtor[$DEBTORCONTACTPHONECODE].'","'.$debtor[$DEBTORCONTACTPHONE].'","'.$debtor[$DEBTORCONTACTEMAIL].'")');
                  $contact_address_id=$query->insertid;
                  
                  $query->updateQuery('INSERT INTO debtors_detail(debtor_contact_first_name,debtor_contact_last_name,phone1,debtor_company_registration_number,debtor_name,trade_name,previous_name,customer_since,total_debtor_value,average_payment_cycle,no_credit_note_issued,avg_invoice_predicted_annum,avg_revenue_predicted_annum,address_id,billing_address_id,contact_address_id,currency_id,created_by,parent_id,created,interface_added)
                                        VALUES("'.$debtor[$DEBTORCONTACTFIRSTNAME].'","'.$debtor[$DEBTORCONTACTLASTNAME].'","'.$debtor[$DEBTORCONTACTPHONECOUNTRY].'-'.$debtor[$DEBTORCONTACTPHONECODE].'-'.$debtor[$DEBTORCONTACTPHONE].'","'.$debtor[$REGNO].'","'.$debtor[$DEBTORNAME].'","'.$debtor[$TRADINGAS].'","'.$debtor[$PREVIOUSNAME].'",'.$debtor[$CUSTOMERSINCE].','.(int)$debtor[$TOTALHISTORICVALUE].','.$debtor[$AVERAGEPAYMENTCYCLE].',"'.$debtor[$CREDITNOTES].'",'.$debtor[$INVOICEPREDICTEDPERANNUM].','.$debtor[$PREDICTEDANNUALREVENUE].','.$debtor_address_id.','.$billing_address_id.','.$contact_address_id.','.$currencies[$debtor[$CURRENCY]].',0,'.$debtor_master_id.',NOW(),0)');
                  
                  $debtor_id=$query->insertid;
                  
                  $query->updateQuery('UPDATE debtors_detail  
                               SET debtor_reference_id = (CONCAT(DATE_FORMAT(created,"%Y%m%d"), "000000") + debtor_id)  
                               WHERE debtor_id='.$debtor_id);
                  
                  $query->updateQuery('INSERT INTO debtor_bank_details (master_debtor_id,debtor_id,account_no,sort_code,iban_code,swift_code,phone,deb_address_line_one,deb_address_line_two,post_code)
                              VALUES('.$debtor_master_id.','.$debtor_id.',"'.$debtor[$BANKACCOUNTNO].'","'.$debtor[$BANKSORTCODE].'","'.$debtor[$BANKIBAN].'","'.$debtor[$BANKSWIFTCODE].'","'.$debtor[$BANKPHONE].'","'.$debtor[$BANKADDRESSLINE1].'","'.$debtor[$BANKADDRESSLINE2].'","'.$debtor[$BANKPOSTCODE].'")');
                  
                  $query->updateQuery('INSERT INTO risk_credit(debtor_id,provider1_credit_limit_modified_by,provider1_risk_score_modified_by,provider2_credit_limit_modified_by,provider2_risk_score_modified_by) 
                               VALUES('.$debtor_master_id.',1,1,1,1)');
                  
                  $query->executeQuery('SELECT organisation_id,user_id 
                                        FROM  organisation_details
                                        WHERE organisation_reference_id='.$debtor[$ORIGINATORID]);
                  
                  $originator=$query->records[0];
                  
                  $query->updateQuery('INSERT INTO debtor_relation (trader_id,debtor_id,organisation_id,status,notify,revolving_etr,ed_status,created)
                               VALUES('.$originator['user_id'].','.$debtor_id.','.$originator['organisation_id'].',0,0,0,0,NOW())');
              }
          }
      }
  }
  
  $import = new Import_debtors();
?>