Projekt

Obecné

Profil

Stáhnout (22.8 KB) Statistiky
| Větev: | Tag: | Revize:

freenetis-github/application/models/invoice.php @ master

8baed187 Michal Kliment
<?php defined('SYSPATH') or die('No direct script access.');
/*
* This file is part of open source system FreenetIS
* and it is release under GPLv3 licence.
*
* More info about licence can be found:
* http://www.gnu.org/licenses/gpl-3.0.html
*
* More info about project can be found:
* http://www.freenetis.org/
*
*/

/**
* Invoice
*
* @package Model
*
* @property integer $id
c1bdc1c4 Michal Kliment
* @property integer $member_id
* @property Member_Model $partner
* @property string $partner_company
* @property string $partner_name
* @property string $partner_street
* @property string $partner_street_number
* @property string $partner_town
* @property string $partner_zip_code
* @property string $partner_country
* @property string $organization_identifier
* @property string $vat_organization_identifier
* @property string $phone_number
* @property string $email
8baed187 Michal Kliment
* @property double $invoice_nr
c1bdc1c4 Michal Kliment
* @property integer $invoice_type
* @property string $account_nr
8baed187 Michal Kliment
* @property double $var_sym
* @property double $con_sym
* @property date $date_inv
* @property date $date_due
* @property date $date_vat
c1bdc1c4 Michal Kliment
* @property integer $vat
8baed187 Michal Kliment
* @property double $order_nr
* @property string $currency
c1bdc1c4 Michal Kliment
* @property string $note
8baed187 Michal Kliment
* @property ORM_Iterator $invoice_items
*/
class Invoice_Model extends ORM
{
c1bdc1c4 Michal Kliment
/** Type of invoice: issued */
const TYPE_ISSUED = 0;
/** Type of member: received */
const TYPE_RECEIVED = 1;
/**
* Types of invoice
*
* @var array
*/
private static $types = array
(
self::TYPE_ISSUED => 'Issued',
self::TYPE_RECEIVED => 'Received'
);

protected $belongs_to = array('partner' => 'member');
8baed187 Michal Kliment
protected $has_many = array('invoice_items');

c1bdc1c4 Michal Kliment
/**
* Returns type in string from integer
*
* @author Jan Dubina
* @param integer|string $type
* @return string
*/
public static function get_type ($type)
{
if (isset(self::$types[$type]))
return __(self::$types[$type]);
else
return $type;
}
/**
* Return translated invoice type array
*
* @author Jan Dubina
* @return array
*/
public static function types()
{
return array
(
self::TYPE_ISSUED => __('Issued'),
self::TYPE_RECEIVED => __('Received')
);
}
/**
* Returns ORM_Iterator of all invoices
*
* @author Michal Kliment
* @param $limit_from
* @param $limit_results
* @param $order_by
* @param $order_by_direction
* @return Mysql_Result
*/
8baed187 Michal Kliment
public function get_all_invoices(
$limit_from = 0, $limit_results = 50,
c1bdc1c4 Michal Kliment
$order_by = 'id', $order_by_direction = 'ASC',
$filter_sql = '')
{
8baed187 Michal Kliment
// order by direction check
if (strtolower($order_by_direction) != 'desc')
{
$order_by_direction = 'asc';
}
c1bdc1c4 Michal Kliment
$where = '';

if ($filter_sql != '')
$where = "WHERE $filter_sql";
$join_phone = '';
$select_phone = '';
$join_email = '';
$select_email = '';
//HACK FOR IMPROVING PERFORMANCE
if (strpos($filter_sql, '`iv`.`phone` LIKE '))
{
$join_phone = "
LEFT JOIN
(
SELECT member_id, phone
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS phone
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cp ON iv.member_id = cp.member_id
";
$select_phone = ", IF(iv.member_id IS NULL,iv.phone_number, cp.phone) AS phone";
}
if (strpos($filter_sql, '`iv`.`email` LIKE '))
{
$join_email = "
LEFT JOIN
(
SELECT member_id, email
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS email
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cm ON iv.member_id = cm.member_id
";
$select_email = ",IF(iv.member_id IS NULL,iv.email, cm.email) AS email";
}
8baed187 Michal Kliment
// query
c1bdc1c4 Michal Kliment
return $this->db->query("
SELECT *
FROM
(
SELECT iv.id,
IF(iv.member_id IS NULL, partner_company, NULL) AS company,
IF(iv.member_id IS NULL, partner_name, m.name) AS partner,
IF(iv.member_id IS NULL, partner_street, m.street) AS street,
IF(iv.member_id IS NULL, partner_street_number, m.street_number)
AS street_number,
IF(iv.member_id IS NULL, partner_town, m.town) AS town,
IF(iv.member_id IS NULL, partner_zip_code, m.zip_code) AS zip_code,
IF(iv.member_id IS NULL, partner_country, m.country_name) AS country,
IF(iv.member_id IS NULL, iv.organization_identifier, m.organization_identifier)
AS organization_identifier,
IF(iv.member_id IS NULL, iv.vat_organization_identifier, m.vat_organization_identifier)
AS vat_organization_identifier,
IF(iv.member_id IS NULL,iv.account_nr, m.account_nr) AS account_nr,
invoice_nr, invoice_type, var_sym, con_sym, date_inv, date_due,
date_vat, iv.vat, order_nr, currency, note,
COUNT(it.id) AS comments_count,
GROUP_CONCAT(DISTINCT it.name ORDER BY it.name SEPARATOR '\n') AS comments,
SUM(it.price * it.quantity) AS price,
SUM(it.price * it.quantity * (1 + it.vat)) AS price_vat
$select_phone
$select_email
FROM invoices iv
LEFT JOIN
(
SELECT m.id, m.name, m.organization_identifier,
m.vat_organization_identifier,
ap.street, ap.street_number, ap.town,
ap.zip_code, ap.country_name, account_nr
FROM members m
LEFT JOIN
(
SELECT member_id,
IF(account_nr<>'' AND bank_nr<>'', CONCAT(account_nr,'/',bank_nr), '') as account_nr
FROM bank_accounts
GROUP BY member_id
) ba ON ba.member_id = m.id
LEFT JOIN
(
SELECT ap.id, s.street, street_number,
t.town, t.zip_code, c.country_name
FROM address_points ap
LEFT JOIN countries c ON ap.country_id = c.id
LEFT JOIN towns t ON ap.town_id = t.id
LEFT JOIN streets s ON ap.street_id = s.id
) ap ON m.address_point_id = ap.id
) m ON iv.member_id = m.id
LEFT JOIN invoice_items it ON iv.id = it.invoice_id
$join_phone
$join_email
GROUP BY iv.id
) iv $where
8baed187 Michal Kliment
ORDER BY " . $this->db->escape_column($order_by) . " $order_by_direction
c1bdc1c4 Michal Kliment
", array(
Contact_Model::TYPE_EMAIL,
Contact_Model::TYPE_PHONE
));
}
/**
* Returns ORM_Iterator of all invoices
*
* @author Michal Kliment
* @param $limit_from
* @param $limit_results
* @param $order_by
* @param $order_by_direction
* @return Mysql_Result
*/
public function get_all_invoices_export($filter_sql = '')
{
$where = '';

if ($filter_sql != '')
$where = "WHERE $filter_sql";
// query
return $this->db->query("
SELECT *
FROM
(
SELECT iv.id,
IF(iv.member_id IS NULL, partner_company, NULL) AS company,
IF(iv.member_id IS NULL, partner_name, m.name) AS partner,
IF(iv.member_id IS NULL, partner_street, m.street) AS street,
IF(iv.member_id IS NULL, partner_street_number, m.street_number)
AS street_number,
IF(iv.member_id IS NULL, partner_town, m.town) AS town,
IF(iv.member_id IS NULL, partner_zip_code, m.zip_code) AS zip_code,
IF(iv.member_id IS NULL, partner_country, m.country_name) AS country,
IF(iv.member_id IS NULL, iv.organization_identifier, m.organization_identifier)
AS organization_identifier,
IF(iv.member_id IS NULL, iv.vat_organization_identifier, m.vat_organization_identifier)
AS vat_organization_identifier,
IF(iv.member_id IS NULL,iv.account_nr, m.account_nr) AS account_nr,
IF(iv.member_id IS NULL,iv.email, cm.email) AS email,
IF(iv.member_id IS NULL,iv.phone_number, cp.phone) AS phone,
invoice_nr, invoice_type, var_sym, con_sym, date_inv, date_due,
date_vat, iv.vat, order_nr, currency, note,
COUNT(it.id) AS comments_count,
GROUP_CONCAT(DISTINCT it.name ORDER BY it.name SEPARATOR '\n') AS comments,
SUM(it.price * it.quantity) AS price,
SUM(it.price * it.quantity * (1 + it.vat)) AS price_vat
FROM invoices iv
LEFT JOIN
(
SELECT m.id, m.name, m.organization_identifier,
m.vat_organization_identifier,
ap.street, ap.street_number, ap.town,
ap.zip_code, ap.country_name, account_nr
FROM members m
LEFT JOIN
(
SELECT member_id,
IF(account_nr<>'' AND bank_nr<>'', CONCAT(account_nr,'/',bank_nr), '') as account_nr
FROM bank_accounts
GROUP BY member_id
) ba ON ba.member_id = m.id
LEFT JOIN
(
SELECT ap.id, s.street, street_number,
t.town, t.zip_code, c.country_name
FROM address_points ap
LEFT JOIN countries c ON ap.country_id = c.id
LEFT JOIN towns t ON ap.town_id = t.id
LEFT JOIN streets s ON ap.street_id = s.id
) ap ON m.address_point_id = ap.id
) m ON iv.member_id = m.id
LEFT JOIN
(
SELECT member_id, email
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS email
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cm ON iv.member_id = cm.member_id
LEFT JOIN
(
SELECT member_id, phone
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS phone
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cp ON iv.member_id = cp.member_id
LEFT JOIN invoice_items it ON iv.id = it.invoice_id
GROUP BY iv.id
) iv $where
ORDER BY iv.id
", array(
Contact_Model::TYPE_EMAIL,
Contact_Model::TYPE_PHONE
));
}
/**
* Function counts all invoices.
*
* !!!!!! SECURITY WARNING !!!!!!
* Be careful when you using this method, param $filter_sql is unprotected
* for SQL injections, security should be made at controller site using
* Filter_form class.
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*
* @param string $filter_values
* @return integer
*/
public function count_all_invoices($filter_sql = "")
{
// optimalization
if (!empty($filter_sql))
{
$where = "WHERE $filter_sql";
}
else
{
return $this->count_all();
}
$join_phone = '';
$select_phone = '';
$join_email = '';
$select_email = '';
//HACK FOR IMPROVING PERFORMANCE
if (strpos($filter_sql, '`iv`.`phone` LIKE '))
{
$join_phone = "
LEFT JOIN
(
SELECT member_id, phone
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS phone
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cp ON iv.member_id = cp.member_id
";
$select_phone = ", IF(iv.member_id IS NULL,iv.phone_number, cp.phone) AS phone";
}
if (strpos($filter_sql, '`iv`.`email` LIKE '))
{
$join_email = "
LEFT JOIN
(
SELECT member_id, email
FROM users u
RIGHT JOIN
(
SELECT user_id, value AS email
FROM users_contacts uc
LEFT JOIN contacts c ON uc.contact_id = c.id
WHERE c.type = ?
) c ON u.id = c.user_id
GROUP BY member_id
) cm ON iv.member_id = cm.member_id
";
$select_email = ",IF(iv.member_id IS NULL,iv.email, cm.email) AS email";
}
// query
return $this->db->query("
SELECT COUNT(*) AS total
FROM
(
SELECT iv.id,
IF(iv.member_id IS NULL, partner_company, NULL) AS company,
IF(iv.member_id IS NULL, partner_name, m.name) AS partner,
IF(iv.member_id IS NULL, partner_street, m.street) AS street,
IF(iv.member_id IS NULL, partner_street_number, m.street_number)
AS street_number,
IF(iv.member_id IS NULL, partner_town, m.town) AS town,
IF(iv.member_id IS NULL, partner_zip_code, m.zip_code) AS zip_code,
IF(iv.member_id IS NULL, partner_country, m.country_name) AS country,
IF(iv.member_id IS NULL, iv.organization_identifier, m.organization_identifier)
AS organization_identifier,
IF(iv.member_id IS NULL, iv.vat_organization_identifier, m.vat_organization_identifier)
AS vat_organization_identifier,
IF(iv.member_id IS NULL,iv.account_nr, m.account_nr) AS account_nr,
invoice_nr, invoice_type, var_sym, con_sym, date_inv, date_due,
date_vat, iv.vat, order_nr, currency, note,
SUM(it.price * it.quantity) AS price,
SUM(it.price * it.quantity * (1 + it.vat)) AS price_vat
$select_phone
$select_email
FROM invoices iv
LEFT JOIN
(
SELECT m.id, m.name, m.organization_identifier,
m.vat_organization_identifier,
ap.street, ap.street_number, ap.town,
ap.zip_code, ap.country_name, account_nr
FROM members m
LEFT JOIN
(
SELECT member_id,
IF(account_nr<>'' AND bank_nr<>'', CONCAT(account_nr,'/',bank_nr), '') as account_nr
FROM bank_accounts
GROUP BY member_id
) ba ON ba.member_id = m.id
LEFT JOIN
(
SELECT ap.id, s.street, street_number,
t.town, t.zip_code, c.country_name
FROM address_points ap
LEFT JOIN countries c ON ap.country_id = c.id
LEFT JOIN towns t ON ap.town_id = t.id
LEFT JOIN streets s ON ap.street_id = s.id
) ap ON m.address_point_id = ap.id
) m ON iv.member_id = m.id
LEFT JOIN invoice_items it ON iv.id = it.invoice_id
$join_phone
$join_email
GROUP BY iv.id
) iv $where
", array(
Contact_Model::TYPE_EMAIL,
Contact_Model::TYPE_PHONE
))->current()->total;
}
/**
* Returns all partner names by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_names ($like)
{
return $this->db->query("
SELECT DISTINCT partner FROM
(
SELECT IF(i.member_id IS NULL, partner_name, m.name) AS partner
FROM invoices i
LEFT JOIN
(
SELECT id, name
FROM members
) m ON m.id = i.member_id
) i
WHERE partner LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all streets by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_streets ($like)
{
return $this->db->query("
SELECT DISTINCT street FROM
(
SELECT IF(i.member_id IS NULL, partner_street, m.street) AS street
FROM invoices i
LEFT JOIN
(
SELECT m.id, ap.street
FROM members m
LEFT JOIN
(
SELECT ap.id, s.street
FROM address_points ap
LEFT JOIN
(
SELECT id, street
FROM streets
) s ON s.id = ap.street_id
) ap ON ap.id = m.address_point_id
) m ON m.id = i.member_id
) i
WHERE street IS NOT NULL AND street LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all towns by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_towns ($like)
{
return $this->db->query("
SELECT DISTINCT town FROM
(
SELECT IF(i.member_id IS NULL, partner_town, m.town) AS town
FROM invoices i
LEFT JOIN
(
SELECT m.id, ap.town
FROM members m
LEFT JOIN
(
SELECT ap.id, t.town
FROM address_points ap
LEFT JOIN
(
SELECT id, town
FROM towns
) t ON t.id = ap.town_id
) ap ON ap.id = m.address_point_id
) m ON m.id = i.member_id
) i
WHERE town IS NOT NULL AND town LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all zip codes by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_zip_codes ($like)
{
return $this->db->query("
SELECT DISTINCT zip_code FROM
(
SELECT
IF(i.member_id IS NULL, partner_zip_code, m.zip_code) AS zip_code
FROM invoices i
LEFT JOIN
(
SELECT m.id, ap.zip_code
FROM members m
LEFT JOIN
(
SELECT ap.id, t.zip_code
FROM address_points ap
LEFT JOIN
(
SELECT id, zip_code
FROM towns
) t ON t.id = ap.town_id
) ap ON ap.id = m.address_point_id
) m ON m.id = i.member_id
) i
WHERE zip_code IS NOT NULL AND zip_code LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all street numbers by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_street_numbers ($like)
{
return $this->db->query("
SELECT DISTINCT street_number FROM
(
SELECT
IF(i.member_id IS NULL, partner_street_number, m.street_number) AS street_number
FROM invoices i
LEFT JOIN
(
SELECT m.id, ap.street_number
FROM members m
LEFT JOIN
(
SELECT ap.id, ap.street_number
FROM address_points ap
) ap ON ap.id = m.address_point_id
) m ON m.id = i.member_id
) i
WHERE street_number IS NOT NULL AND street_number LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all countries by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_countries ($like)
{
return $this->db->query("
SELECT DISTINCT country FROM
(
SELECT
IF(i.member_id IS NULL, partner_country, m.country_name) AS country
FROM invoices i
LEFT JOIN
(
SELECT m.id, ap.country_name
FROM members m
LEFT JOIN
(
SELECT ap.id, c.country_name
FROM address_points ap
LEFT JOIN
(
SELECT id, country_name
FROM countries
) c ON c.id = ap.town_id
) ap ON ap.id = m.address_point_id
) m ON m.id = i.member_id
) i
WHERE country IS NOT NULL AND country LIKE ".$this->db->escape("%$like%"));
}
/**
* Returns all organization ids by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_organization_ids ($like)
{
return $this->db->query("
SELECT DISTINCT organization_identifier FROM
(
SELECT
IF(i.member_id IS NULL, i.organization_identifier, m.organization_identifier)
AS organization_identifier
FROM invoices i
LEFT JOIN
(
SELECT m.id, m.organization_identifier
FROM members m
) m ON m.id = i.member_id
) i
WHERE organization_identifier IS NOT NULL AND organization_identifier LIKE " .
$this->db->escape("%$like%"));
}
/**
* Returns all VAT organization ids by given like
*
* @author Michal Kliment
* @param string $like
* @return MySQL_Result
*/
public function get_all_vat_organization_ids ($like)
{
return $this->db->query("
SELECT DISTINCT vat_organization_identifier FROM
(
SELECT
IF(i.member_id IS NULL, i.vat_organization_identifier, m.vat_organization_identifier)
AS vat_organization_identifier
FROM invoices i
LEFT JOIN
(
SELECT m.id, m.vat_organization_identifier
FROM members m
) m ON m.id = i.member_id
) i
WHERE vat_organization_identifier IS NOT NULL AND vat_organization_identifier LIKE " .
$this->db->escape("%$like%"));
}
/**
* Returns all account numbers by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_account_nrs ($like)
{
return $this->db->query("
SELECT DISTINCT account_nr FROM
(
SELECT
IF(i.account_nr IS NOT NULL, i.account_nr, m.account_nr)
AS account_nr
FROM invoices i
LEFT JOIN
(
SELECT m.id, ba.account_nr
FROM members m
LEFT JOIN
(
SELECT member_id,
CONCAT(account_nr,'/',bank_nr) as account_nr
FROM bank_accounts
GROUP BY member_id
) ba ON ba.member_id = m.id
) m ON m.id = i.member_id
) i
WHERE account_nr IS NOT NULL AND account_nr LIKE " .
$this->db->escape("%$like%"));
}
/**
* Returns all emails by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_emails ($like)
{
return $this->db->query("
SELECT DISTINCT email FROM
(
SELECT
IF(i.member_id IS NULL, i.email, m.email)
AS email
FROM invoices i
LEFT JOIN
(
SELECT m.id, cm.email
FROM members m
LEFT JOIN
(
SELECT member_id, uc.email
FROM users u
RIGHT JOIN
(
SELECT user_id, c.email
FROM users_contacts uc
RIGHT JOIN
(
SELECT id, value as email
FROM contacts
WHERE type = ?
) c ON uc.contact_id = c.id
) uc ON u.id = uc.user_id
GROUP BY member_id
) cm ON m.id = cm.member_id
) m ON m.id = i.member_id
) i
WHERE email IS NOT NULL AND email LIKE " .
$this->db->escape("%$like%"),
array(Contact_Model::TYPE_EMAIL));
}
/**
* Returns all phone numbers by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_phone_numbers ($like)
{
return $this->db->query("
SELECT DISTINCT phone_number FROM
(
SELECT
IF(i.member_id IS NULL, i.phone_number, m.phone_number)
AS phone_number
FROM invoices i
LEFT JOIN
(
SELECT m.id, cm.phone_number
FROM members m
LEFT JOIN
(
SELECT member_id, uc.phone_number
FROM users u
RIGHT JOIN
(
SELECT user_id, c.phone_number
FROM users_contacts uc
RIGHT JOIN
(
SELECT id, value as phone_number
FROM contacts
WHERE type = ?
) c ON uc.contact_id = c.id
) uc ON u.id = uc.user_id
GROUP BY member_id
) cm ON m.id = cm.member_id
) m ON m.id = i.member_id
) i
WHERE phone_number IS NOT NULL AND phone_number LIKE " .
$this->db->escape("%$like%"),
array(Contact_Model::TYPE_PHONE));
}
/**
* Returns all companies by given like
*
* @author Jan Dubina
* @param string $like
* @return MySQL_Result
*/
public function get_all_companies ($like)
{
return $this->db->query("
SELECT DISTINCT company FROM
(
SELECT partner_company AS company
FROM invoices
WHERE partner_company IS NOT NULL
) i
WHERE company LIKE ".$this->db->escape("%$like%"));
}
8baed187 Michal Kliment
}