示例#1
0
    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);
    }
示例#2
0
    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);
     }
 }
示例#4
0
 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();
 }
示例#5
0
 /**
 * 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;
 }
示例#6
0
 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();
 }
示例#7
0
 public static function get()
 {
     $pdo = Kohana_pdo::instance();
     return $pdo->query('SELECT * FROM employees ORDER BY lastname, firstname;')->fetchAll(PDO::FETCH_ASSOC);
 }
示例#8
0
 public static function get_customers()
 {
     $pdo = Kohana_pdo::instance();
     return $pdo->query('SELECT * FROM customers;')->fetchAll(PDO::FETCH_ASSOC);
 }
示例#9
0
 /**
  * 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;
     }
 }
示例#10
0
<?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,