public static function get($search = NULL, $order_by = 'accounting_date', $where = NULL)
{
$pdo = Kohana_pdo::instance();
if (is_string($search) || is_array($search)) {
$columns = array_keys($pdo->query('SELECT * FROM transactions LIMIT 1;')->fetch(PDO::FETCH_ASSOC));
}
if (is_string($search)) {
$where = '';
foreach ($columns as $column) {
$where .= '`' . $column . '` LIKE ' . $pdo->quote('%' . $search . '%') . ' OR ';
}
$where = substr($where, 0, strlen($where) - 4);
} elseif (is_array($search)) {
$where = '';
foreach ($search as $column => $string) {
if (in_array($column, $columns)) {
$where .= '`' . $column . '` LIKE ' . $pdo->quote('%' . $string . '%') . ' OR ';
}
}
$where = substr($where, 0, strlen($where) - 4);
} elseif ($where === NULL) {
$where = '1';
}
$sql = '
SELECT
transactions.*,
lastname AS employee_lastname,
firstname AS employee_firstname
FROM transactions
LEFT JOIN employees ON employees.id = transactions.employee_id
WHERE ' . $where . '
ORDER BY ' . $order_by;
// ORDER BY NEEDS TO BE SECURED!!!!!0101=!11111ett
return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
public static function get()
{
$pdo = Kohana_pdo::instance();
return $pdo->query('
SELECT
*,
(SELECT SUM(qty * price * 1.25) FROM bills_items WHERE bills_items.bill_id = bills.id) AS sum,
(SELECT SUM(qty * price * 0.25) FROM bills_items WHERE bills_items.bill_id = bills.id) AS vat
FROM bills order by ID ASC;
')->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Loads the database.
*
* $model = new Foo_Model($db);
*
* @param mixed Database instance object or string
* @return void
*/
public function __construct($instance_name = NULL)
{
if ($instance_name !== NULL) {
// Set the database instance name
$this->pdo = $instance_name;
}
if (is_string($this->pdo)) {
// Load the database
$this->pdo = Kohana_pdo::instance($this->pdo);
}
}
public static function add($customer_data)
{
$pdo = Kohana_pdo::instance();
// Here we should really do a check so those columns actually exists. It might be an SQL-injection exploit!
$sql = 'INSERT INTO customers (' . implode(',', array_keys($customer_data)) . ') VALUES(';
foreach ($customer_data as $data) {
$sql .= $pdo->quote($data) . ',';
}
$sql = substr($sql, 0, strlen($sql) - 1) . ');';
$pdo->exec($sql);
return $pdo->lastInsertId();
}
/**
* Add a bill
*
* @param int $customer_id
* @param num $due_date (UNIX timestamp)
* @param str $contact - Their reference
* @param arr $items - array(
array(
'artnr' => '239D',
'spec' => 'What to bill for',
'price' => 700,
'qty' => 2,
'delivery_date' => '2011-01-01'
),
etc
)
* @param str $comment - Optional
*/
public static function new_bill($customer_id, $due_date, $contact, $items, $comment = '', $template = 'default', $mail_body = '')
{
$pdo = Kohana_pdo::instance();
if (self::$prepared_insert == NULL) {
self::$prepared_insert = $pdo->prepare('INSERT INTO bills (due_date,customer_id,customer_name,customer_orgnr,customer_contact,customer_tel,customer_email,customer_street,customer_zip,customer_city,comment,contact,template,mail_body) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
self::$prepared_item_insert = $pdo->prepare('INSERT INTO bills_items (item_id,bill_id,artnr,spec,qty,price,delivery_date) VALUES(?,?,?,?,?,?,?)');
}
$customer_model = new Customer($customer_id);
self::$prepared_insert->execute(array(date('Y-m-d', $due_date), intval($customer_id), $customer_model->get('name'), $customer_model->get('orgnr'), $customer_model->get('contact'), $customer_model->get('tel'), $customer_model->get('email'), $customer_model->get('street'), $customer_model->get('zip'), $customer_model->get('city'), $comment, $contact, $template, $mail_body));
$bill_id = $pdo->lastInsertId();
foreach ($items as $nr => $item) {
self::$prepared_item_insert->execute(array($nr + 1, $bill_id, $item['artnr'], $item['spec'], $item['qty'], $item['price'], date('Y-m-d', time())));
}
return $bill_id;
}
public static function new_employee($data)
{
$pdo = Kohana_pdo::instance();
$columns = array();
foreach ($pdo->query('DESCRIBE employees')->fetchAll(PDO::FETCH_ASSOC) as $row) {
if ($row['Field'] != 'id') {
$columns[] = $row['Field'];
}
}
foreach ($data as $field => $value) {
if (!in_array($field, $columns)) {
unset($data[$field]);
}
}
$sql = 'INSERT INTO employees (`' . implode('`,`', array_keys($data)) . '`) VALUES(';
foreach ($data as $field => $value) {
$sql .= $pdo->quote($value) . ',';
}
$sql = substr($sql, 0, strlen($sql) - 1) . ')';
$pdo->query($sql);
return $pdo->lastInsertId();
}
public static function get()
{
$pdo = Kohana_pdo::instance();
return $pdo->query('SELECT * FROM employees ORDER BY lastname, firstname;')->fetchAll(PDO::FETCH_ASSOC);
}
public static function get_customers()
{
$pdo = Kohana_pdo::instance();
return $pdo->query('SELECT * FROM customers;')->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Creates a DOMNode or DOMDocument of your array, object or SQL
*
* Examples:
* ===============================================================
* Simple example of the two different return values.
* As DOMDocument:
* <?php
* $doc = xml::to_XML(array('root'=>array('fnupp'=>'dah')));
* $doc->formatOutput = TRUE;
*
* echo $doc->saveXML();
* ?>
*
*
* As DOMNode:
* <?php
* $doc = new DOMDocument();
* $container = $doc->appendChild($doc->createElement('root'));
*
* xml::to_XML(array('fnupp'=>'dah'), $container);
*
* echo $doc->saveXML();
* ?>
* ===============================================================
* If you pass an object it will be converted to an array first and
* then treated just like in the examples above.
* Example of Obejct to Array conversion:
*
* $obj = new stdClass;
* $obj->foo = new stdClass;
* $obj->foo->baz = 'baz';
* $obj->bar = 'bar';
*
* Array
* (
* [foo] => Array
* (
* [baz] => baz
* )
* [bar] => bar
* )
*
*
* ===============================================================
* An SQL-statement, will be grouped like this:
* SQL-table (users):
* ID | name | address
* -------------------------
* 1 | Smith | Nowhere 2
* 2 | Doe | Somestreet 4
*
* $data = 'SELECT * FROM users';
*
* will be transformed to:
*
* $data = array(
* 0 => array(
* 'ID' => '1',
* 'name' => 'Smith',
* 'address' => 'Nowhere 2',
* ),
* 1 => array(
* 'ID' => '2',
* 'name' => 'Doe',
* 'address' => 'Somestreet 4',
* )
* )
* IMPORTANT! This needs Kohana database to be configured
* ===============================================================
* How the $container works:
* xml::to_XML(array('fnupp' => 'dah'))
* will output:
* <fnupp>dah</fnupp>
*
* xml::to_XML(array('fnupp' => 'dah'), 'root')
* will output:
* <root>
* <fnupp>dah</fnupp>
* </root>
*
* The $container can also be a DOMNode, see the examples with return values for more info
* ===============================================================
* How the $group works
* IMPORTANT! $group requires $container
*
* SQL-table (users):
* ID | name | address
* -------------------------
* 1 | Smith | Nowhere 2
* 2 | Doe | Somestreet 4
*
* xml::to_XML('SELECT * FROM users', 'users', 'user');
*
* will output:
*
* <users>
* <user>
* <ID>1</ID>
* <name>Smith</name>
* <address>Nowhere 2</address>
* </user>
* <user>
* <ID>2</ID>
* <name>Doe</name>
* <address>Somestreet 4</address>
* </user>
* </users>
* ===============================================================
* How the $attributes works
* xml::to_XML(array('user'=>array('id'=>2,'name'=>'nisse'),NULL,NULL,array('id'));
*
* will output:
* <user id="2">
* <name>nisse</name>
* </user>
*
* This will work no matter how deep in the structure the attribute is
*
* Alternative to this is to begin the element name with "@", in this case the data would then be:
* array('user'=>array('@id'=>2,'name'=>'nisse')
* ===============================================================
* How $text_values works
* xml::to_XML(array('user'=>array('id'=>2,'name'=>'nisse'),NULL,NULL,array('id'),array('name'));
*
* will output:
* <user id="2">nisse</user>
*
* This will also work no matter the depth of the element
*
* Alternative to this is to begin the element name with "$", in this case the data would then be:
* array('user'=>array('id'=>2,'$name'=>'nisse')
* ===============================================================
* How the $alter_code works
* This is very cool! For each element, you can execute a snippet of code on its data. For example:
* $data = array(
* 'blubb' => 'bla',
* 'strangeness' => 5,
* )
*
* xml::to_XML($data, 'root', NULL, array(), array(), array(), array('strangeness' => '$str = $name . ' is at level ' . $value; return $str;'));
*
* will return:
* <root>
* <blubb>bla</blubb>
* <strangeness>strangeness is at level 5</strangeness>
* </root>
*
* $name and $value is loaded with the element name and element value.
* The code snippet will work exactly as a function, hence the "return" in the example.
*
* To just use an existing function, this is the way to go:
* xml::to_XML($data, 'root', NULL, array(), array(), array('strangeness' => 'return substr($blubb,0,2);'));
* (Will change "bla" to "bl" in the "blubb"-element)
* ===============================================================
* Rule for making several identical elements
*
* $data = array(
* '1blubb' => 233,
* '2blubb' => 993,
* )
*
* xml::to_XML($data, 'root');
*
* will output:
* <root>
* <blubb>233</blubb>
* <blubb>993</blubb>
* </root>
*
* $data = array(
* 1 => 233,
* 2 => 993,
* )
*
* xml::to_XML($data, 'root');
*
* will output:
* <root>233993</root>
*
*
*
* @param str or arr $data - if string, it will be treated as an SQL statement
* @param obj $container
* @param str $group - Container must be provided for this to work
* @param arr $attributes - Array of keys that should always be treated as attributes
* @param arr $text_values - Array of keys that should always have their value as value to the parent, ignoring the key
* @param arr $xml_fragments - Array of keys that should always have their value interpreted as xml fragments
* @param arr $alter_code - keys that should have their values altered by the code given as array value
* @return obj - DOMElement
*/
public static function to_XML($data, $container = NULL, $group = NULL, $attributes = array(), $text_values = array(), $xml_fragments = array(), $alter_code = array())
{
if (is_string($attributes)) {
$attributes = array($attributes);
}
if (is_string($text_values)) {
$text_values = array($text_values);
}
// Make sure the data is always an array
if (is_string($data)) {
// SQL statement - make it an array
$pdo = Kohana_pdo::instance();
$data = $pdo->query($data)->fetchAll(PDO::FETCH_ASSOC);
} elseif (is_object($data)) {
$data = self::object_to_array($data);
} elseif (!is_array($data)) {
// Neither string or object or array. Humbug!
return FALSE;
}
if ($container === NULL) {
$DOM_document = new DOMDocument();
} elseif (is_string($container)) {
$DOM_document = new DOMDocument();
$alt_container = $DOM_document->appendChild($DOM_document->createElement($container));
} else {
$DOM_document = $container->ownerDocument;
}
foreach ($data as $key => $value) {
// Fix the key to a tag
$tag = NULL;
$element_attributes = array();
foreach (explode(' ', $key) as $part) {
if (!$tag) {
$tag = $part;
while (preg_match('/^[0-9]/', $tag)) {
// The first character can not be a numeric char
// So we strip them off
$tag = substr($tag, 1);
}
} else {
// This should be an attribute
$attribute_name = NULL;
$attribute_value = NULL;
list($attribute_name, $attribute_value) = explode('=', $part);
if ($attribute_name && $attribute_value) {
// Both must exist to make a valid attribute
// Set the element attributes, strip " or ' from beginning and end of attribute value
$element_attributes[$attribute_name] = substr($attribute_value, 1, strlen($attribute_value) - 2);
}
}
}
if ($container === NULL && !isset($alt_container)) {
// If we have no container, the tag must be the root element
if ($tag == '') {
// And as such, it must be a valid tag
$tag = 'root';
}
$DOM_element = $DOM_document->createElement($tag);
$DOM_document->appendChild($DOM_element);
if (!is_array($value)) {
if (in_array($key, array_keys($alter_code))) {
$func_name = create_function('$value,$name', $alter_code[$key]);
$value = $func_name($value, $key);
}
$DOM_element->appendChild($DOM_document->createTextNode($value));
} else {
$DOM_element = self::to_XML($value, $DOM_element, NULL, $attributes, $text_values, $xml_fragments, $alter_code);
}
} else {
// Grouping is activated, lets group this up
if (isset($group)) {
if (isset($alt_container)) {
$group_element = $alt_container->appendChild($DOM_document->createElement($group));
} else {
$group_element = $container->appendChild($DOM_document->createElement($group));
}
}
// We have a container, create everything in it
if ($tag != '') {
// This is a tag, parse and create
if (substr($tag, 0, 1) == '@' || in_array($tag, $attributes)) {
// This is an attribute
$tag = str_replace('@', '', $tag);
$attribute = $DOM_document->createAttribute($tag);
if (in_array($tag, array_keys($alter_code))) {
$func_name = create_function('$value,$name', $alter_code[$tag]);
$value = $func_name($value, $tag);
}
$attribute->appendChild($DOM_document->createTextNode($value));
if (isset($group_element)) {
$group_element->appendChild($attribute);
} elseif (isset($alt_container)) {
$alt_container->appendChild($attribute);
} else {
$container->appendChild($attribute);
}
} elseif (substr($tag, 0, 1) == '$' || in_array($tag, $text_values)) {
// This tag should be ignored, and its value should be inline text instead
if (in_array($tag, array_keys($alter_code))) {
$func_name = create_function('$value, $name', $alter_code[$tag]);
$value = $func_name($value, $tag);
}
if (isset($group_element)) {
$group_element->appendChild($DOM_document->createTextNode($value));
} elseif (isset($alt_container)) {
$alt_container->appendChild($DOM_document->createTextNode($value));
} else {
$container->appendChild($DOM_document->createTextNode($value));
}
} elseif (substr($tag, 0, 1) == '?' || in_array($tag, $xml_fragments)) {
// This tag should be interpreted as an XML fragment
$tag = str_replace('?', '', $tag);
$DOM_element = $DOM_document->createElement($tag);
if (in_array($tag, array_keys($alter_code))) {
$func_name = create_function('$value,$name', $alter_code[$tag]);
$value = $func_name($value, $tag);
}
$fragment = $DOM_document->createDocumentFragment();
$fragment->appendXML($value);
$DOM_element->appendChild($fragment);
if (isset($group_element)) {
$group_element->appendChild($DOM_element);
} elseif (isset($alt_container)) {
$alt_container->appendChild($DOM_element);
} else {
$container->appendChild($DOM_element);
}
} else {
// This is just a normal tag
$DOM_element = $DOM_document->createElement($tag);
if (in_array($tag, array_keys($alter_code))) {
$func_name = create_function('$value,$name', $alter_code[$tag]);
$value = $func_name($value, $tag);
}
if (!is_array($value)) {
$DOM_element->appendChild($DOM_document->createTextNode($value));
} else {
$DOM_element = self::to_XML($value, $DOM_element, NULL, $attributes, $text_values, $xml_fragments, $alter_code);
}
if (isset($group_element)) {
$group_element->appendChild($DOM_element);
} elseif (isset($alt_container)) {
$alt_container->appendChild($DOM_element);
} else {
$container->appendChild($DOM_element);
}
}
} else {
/**
* When the tag is an empty string (can also be cuz of the array being non-associative i.e. numbers as keys),
* it should fold down to the above tag as inline text:
* array(
* 'foo' => array('blubb')
* )
* produces:
* <foo>blubb</foo>
*/
if (!is_array($value)) {
// This is a simple string value, just add it
if (isset($group_element)) {
$group_element->appendChild($DOM_document->createTextNode($value));
} elseif (isset($alt_container)) {
$alt_container->appendChild($DOM_document->createTextNode($value));
} else {
$container->appendChild($DOM_document->createTextNode($value));
}
} else {
// This is children-stuff :)
if (isset($group_element)) {
$group_element = self::to_XML($value, $group_element, NULL, $attributes, $text_values, $xml_fragments, $alter_code);
} elseif (isset($alt_container)) {
$alt_container = self::to_XML($value, $alt_container, NULL, $attributes, $text_values, $xml_fragments, $alter_code);
} else {
$container = self::to_XML($value, $container, NULL, $attributes, $text_values, $xml_fragments, $alter_code);
}
}
}
}
// Add the attributes
foreach ($element_attributes as $attribute => $value) {
$attribute = $DOM_element->appendChild($DOM_document->createAttribute($attribute));
$attribute->appendChild($DOM_document->createTextNode($value));
}
}
if (is_object($container)) {
return $container;
} else {
return $DOM_document;
}
}
<?php
defined('SYSPATH') or die('No direct script access.');
if (!is_dir(APPPATH . 'user_content/pdf')) {
mkdir(APPPATH . 'user_content/pdf');
}
if (!is_dir(APPPATH . 'user_content/attachments')) {
mkdir(APPPATH . 'user_content/attachments');
}
$pdo = Kohana_pdo::instance('default');
$db_name = Kohana::$config->load('pdo.default.database_name');
$columns = $pdo->query('
SHOW Tables in
' . $db_name . '
WHERE Tables_in_' . $db_name . '
IN (\'bills_items\',
\'bills\',
\'employees\',
\'transactions\',
\'customers\')')->fetchAll(PDO::FETCH_COLUMN);
if (count($columns) != 5) {
$pdo->query('
--
-- Table structure for table `bills`
--
CREATE TABLE IF NOT EXISTS `bills` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`due_date` timestamp NOT NULL DEFAULT \'0000-00-00 00:00:00\',
`customer_id` int(10) unsigned NOT NULL,
