<?php
define('cn_InvoiceImportImport', 'InvoiceImport');
require_once ('class/DB/DBObj.php');
require_once ('class/DB/ImageDB.php');

class InvoiceImport extends DBObj {

	public $tableName = 'InvoiceImport';
    protected $invoiceId = null;

	function InvoiceImport($id=null)
	{
		parent::DBObj($id);
	}

	public function loadData($fileName)
	{
		$this->db->query('CREATE TEMPORARY TABLE `tempInvoiceImport` (
`customer_number` VARCHAR( 255 ) NOT NULL ,
`customer_name` VARCHAR( 255 ) NOT NULL ,
`item_number` VARCHAR( 255 ) NOT NULL ,
`item_description` VARCHAR( 255 ) NOT NULL ,
`quantity` VARCHAR( 255 ) NOT NULL ,
`unit_price` VARCHAR( 255 ) NOT NULL ,
`address` VARCHAR( 255 ) NOT NULL ,
`address2` VARCHAR( 255 ) NOT NULL ,
`city` VARCHAR( 255 ) NOT NULL ,
`state` VARCHAR( 255 ) NOT NULL ,
`zip` VARCHAR( 255 ) NOT NULL ,
`contact_person` VARCHAR( 255 ) NOT NULL ,
`invoice_date` VARCHAR( 255 ) NOT NULL ,
`batch_number` VARCHAR( 255 ) NOT NULL ,
`sop_number` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM');

		 $this->db->query("LOAD DATA LOCAL INFILE '{$fileName}' INTO TABLE `tempInvoiceImport`
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES");
	}

	public function check($source)
	{
		$this->db->query('DELETE FROM tempInvoiceImport WHERE CAST(sop_number AS SIGNED INTEGER) = 0');
        $invoiceNoExists = $this->db->queryOne("SELECT COUNT(*)
            FROM tempInvoiceImport
            WHERE CAST(sop_number AS SIGNED INTEGER) IN (SELECT number FROM {$this->db->tblInvoice})");
        $this->db->query("DELETE FROM tempInvoiceImport
            WHERE CAST(sop_number AS SIGNED INTEGER) IN (SELECT number FROM {$this->db->tblInvoice})");
		if ($source == 'exchange') {
        	$sourceTable = $this->db->tblLocation;
        } else {
        	$sourceTable = $this->db->tblMember;
        }
        $incorrectNrdsId = $this->db->queryOne("SELECT COUNT(*)
            FROM tempInvoiceImport ii
                LEFT JOIN $sourceTable st ON CAST(ii.customer_number AS SIGNED INTEGER) = st.nrds_id
            WHERE st.nrds_id IS NULL");
        //vdie($incorrectNrdsId, $this->db);
        $correct = $this->db->queryOne("SELECT COUNT(*)
            FROM tempInvoiceImport ii
                INNER JOIN $sourceTable st ON CAST(ii.customer_number AS SIGNED INTEGER) = st.nrds_id");
        return array(
            'correct' => $correct,
            'invoiceNoExists' => $invoiceNoExists,
            'noInvoiceNo' => $noInvoiceNo,
            'incorrectNrdsId' => $incorrectNrdsId
        );
	}

	public function import($source)
	{
		$this->db->query("DELETE FROM tempInvoiceImport WHERE CAST(sop_number AS SIGNED INTEGER) = 0 OR
            CAST(sop_number AS SIGNED INTEGER) IN (SELECT number FROM {$this->db->tblInvoice})");
        if ($source == 'exchange') {
            $sourceTable = $this->db->tblLocation;
        } else {
            $sourceTable = $this->db->tblMember;
        }
        $this->db->query("DELETE FROM tempInvoiceImport WHERE CAST(customer_number AS SIGNED INTEGER) NOT IN (
            SELECT nrds_id FROM {$sourceTable}) OR CAST(customer_number AS SIGNED INTEGER) = 0");
        $this->db->query("INSERT INTO {$this->db->tblInvoiceImport}
            SELECT st.id, '{$source}', customer_name, item_number, item_description, CAST(quantity AS SIGNED INTEGER),
                SUBSTRING(unit_price, 2), ii.address, address2, ii.city, ii.state, ii.zip, contact_person,
                STR_TO_DATE(invoice_date, '%m/%d/%Y'), batch_number, CAST(sop_number AS SIGNED INTEGER)
            FROM tempInvoiceImport ii
            INNER JOIN $sourceTable st ON CAST(ii.customer_number AS SIGNED INTEGER) = st.nrds_id");
        /*vdie($this->db->queryAll("SELECT st.id, '{$source}', customer_name, item_number, item_description, CAST(quantity AS SIGNED INTEGER),
                SUBSTRING(unit_price, 2), ii.address, address2, ii.city, ii.state, ii.zip, contact_person,
                STR_TO_DATE(invoice_date, '%m/%d/%Y'), batch_number, CAST(sop_number AS SIGNED INTEGER)
            FROM tempInvoiceImport ii
            INNER JOIN $sourceTable st ON CAST(ii.customer_number AS SIGNED INTEGER) = st.nrds_id"));*/
	}

	public function isGenerateOrderOpen()
	{
		return $this->db->queryOne("SELECT COUNT(customer_id) FROM {$this->db->tblInvoiceImport}");
	}

	public function getReadyInvoicesCount()
	{
	    $total = $this->db->queryOne("SELECT COUNT(id) FROM {$this->db->tblInvoice} WHERE senderror='n' AND sent='n'");
	    $noPay = $this->db->queryOne("SELECT COUNT(id) FROM {$this->db->tblInvoice} WHERE senderror='n' AND sent='n'
	    	AND id_location IN (SELECT l.id
	    		FROM {$this->db->tblLocation} l
	    		LEFT JOIN {$this->db->tblMember} m ON l.id_pay_member = m.id
	    		WHERE m.id IS NULL)");
	    return array('total' => $total, 'noPay' => $noPay);
	}

	public function getSendQueue()
	{
	    $invoices = $this->db->queryAll("SELECT i.number, IFNULL(m.nrds_id, l.nrds_id) AS nrds_id,
	    	IFNULL(CONCAT(first_name, ' ', middle_initial, IF(middle_initial, '. ', ''), last_name), l.name) AS name, amount
	    	FROM {$this->db->tblInvoice} i
	    		LEFT JOIN {$this->db->tblMember} m ON m.id = i.id_member
	    		LEFT JOIN {$this->db->tblLocation} l ON l.id = i.id_location
	    	WHERE senderror='n' AND sent='n'");
	    return $invoices;
	}

	public function getGenerateQueue()
	{
	    $invoices = $this->db->queryAll("SELECT ii.sop_number AS number, IFNULL(m.nrds_id, l.nrds_id) AS nrds_id,
	    	IFNULL(CONCAT(first_name, ' ', middle_initial, IF(middle_initial, '. ', ''), last_name), l.name) AS name,
	    	SUM(quantity * unit_price) AS amount
	    	FROM {$this->db->tblInvoiceImport} ii
	    		LEFT JOIN {$this->db->tblMember} m ON m.id = ii.customer_id AND ii.type= 'mncar'
	    		LEFT JOIN {$this->db->tblLocation} l ON l.id = ii.customer_id AND ii.type= 'exchange'
	    	GROUP BY sop_number");
	    return $invoices;
	}

}
?>