File: /var/www/formularioinscripcion.bradford/application/models/Sell_model.php
<?php
class sell_model extends CI_Model
{
function __construct()
{
parent::__construct();
}
function get_products_by_category($category_id)
{
$this->db->select($this->_get_tables_field_alias('products').$this->_get_tables_field_alias('inventory_products').$this->_get_tables_field_alias('product_categories'));
$this->db->from('products');
$this->db->join('product_categories', 'product_categories.id = products.product_categories_id');
$this->db->join('inventory_products', 'inventory_products.products_id = products.id');
//$this->db->join('business_units bu', 'bu.id = inventory_products.business_units_id');
$this->db->where('products.product_categories_id', $category_id);
$this->db->where('products.is_active', TRUE);
$this->db->where('inventory_products.business_units_id', BUSINESS_UNIT_ID);
$query = $this->db->get();
return array(2, $query->result());
}
function get_top_category($category_id)
{
$this->db->select("*");
$this->db->from('product_categories');
$this->db->where('parent', $category_id);
$this->db->where('is_active', TRUE);
$query = $this->db->get();
$result1 = $query->result();
if (count($result1) == 0) {
return array(2, $this->get_products_by_category($category_id));
} else {
return array(1, $query->result());
}
}
function get_total_update_sales_transactions($sales_transactions_id)
{
$this->db->select("SUM(quantity_items) sales_transacion_quantity, sum(total_price) sales_transacion_subtotal_item");
$this->db->from('sales_transactions_details');
$this->db->where('sales_transactions_id', $sales_transactions_id);
$query = $this->db->get();
return $query->row();
}
function get_item_by_name_or_sku_or_id($product="")
{
$query = "SELECT p.id, p.name
FROM products p
INNER JOIN inventory_products il ON il.products_id = p.id
WHERE il.business_units_id = ".BUSINESS_UNIT_ID."
AND name LIKE '".$product."'
OR SKU LIKE '".$product."'
AND p.is_active = true";
// EVITAR EXCEPCION POR INT FUERA DE RANGO
if (strlen($product) <= 8) {
if (is_numeric($product)) {
$query .= " OR p.id = '".$product."'";
}
}
$query .= " GROUP BY p.id";
$query = $this->db->query($query);
return $query->row();
}
function get_items_by_sku($sku)
{
$sql = "SELECT p.id, p.name, p.sku, p.retail_price, il.stock_on_hand
FROM products p
INNER JOIN inventory_products il ON il.products_id = p.id
WHERE il.business_units_id = ".BUSINESS_UNIT_ID."
AND SKU LIKE '".$sku."'
AND p.is_active = true
GROUP BY p.id, p.name, p.sku, p.retail_price, il.stock_on_hand";
$query = $this->db->query($sql);
return $query->result();
}
protected function _get_table_field($table, $alias)
{
$fields='';
if($table!='')
{
$field_data = $this->db->field_data($table);
foreach ($field_data as $key => $value)
{
if (empty($alias)) {
$fields .= $value->name.', ';
} else {
$fields .= $alias.'.'.$value->name.', ';
}
}
}
$fields = trim($fields, ',');
return $fields;
}
protected function _get_tables_field_alias($table)
{
$fields='';
if(strlen($table) > 0)
{
$CI =& get_instance();
$field_data = $CI->db->field_data($table);
foreach ($field_data as $key => $value)
{
$fields .= $table.'.'.$value->name.' AS '.$table.'_'.$value->name.' , ';
}
}
$fields = trim($fields, ',');
return $fields;
}
function DuplicateRecord($table, $primary_key_field, $primary_key_val)
{
/* generate the select query */
$this->db->where($primary_key_field, $primary_key_val);
$query = $this->db->get($table);
foreach ($query->result() as $row){
foreach($row as $key=>$val){
if($key != $primary_key_field){
/* $this->db->set can be used instead of passing a data array directly to the insert or update functions */
$this->db->set($key, $val);
}//endif
}//endforeach
}//endforeach
/* insert the new record into table*/
$this->db->insert($table);
return $this->db->insert_id();
}
function customers_for_billing ($search) {
$this->db->select(
"customers.id, concat(customers.first_name, ' ', customers.last_name) as first_name, customers.rut")
->from('customers')
->where(array('customers.is_active' => TRUE, 'customer_types_id' => 3));
if (!empty($search)) {
$this->db->like("concat(customers.first_name, ' ', customers.last_name)", $search);
$this->db->or_like('customers.rut', $search);
}
$query = $this->db->order_by('customers.first_name', 'asc')
->limit(20)
->get();
return $query->result();
}
function get_sale_for_print_39 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"localeDate\",
concat(staff.first_name, ' ', staff.last_name) AS \"seller\",
sales_transactions.folio_doc as \"folio\",
(select sum(total_price) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"total\",
sales_transactions.discounts as \"discount\",
(CASE WHEN (redondeo IS NOT NULL AND redondeo <> 0) THEN (net_total + redondeo) ELSE 0 END) as \"redondeo\",
regexp_replace(cast(redondeo as varchar), '[^0-9]+', '', 'g') as \"redondeoValue\",
(CASE WHEN (redondeo IS NULL OR redondeo = 0) THEN NULL ELSE (CASE WHEN redondeo > 0 THEN '+' ELSE '-' END) END) as \"signRedondeo\",
sales_transactions.net_total as \"totalSale\",
sales_transactions.amount_received as \"totalPay\",
sales_transactions.amount_due as \"due\",
NULL as \"signature\"
from sales_transactions
inner join staff on sales_transactions.staff_id = staff.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$query = $this->db->query("select name, quantity_items as quantity, item_price as price, total_price as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
function get_sale_for_print_41 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"localeDate\",
concat(staff.first_name, ' ', staff.last_name) AS \"seller\",
sales_transactions.folio_doc as \"folio\",
(select sum(total_price) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"total\",
sales_transactions.discounts as \"discount\",
(CASE WHEN (redondeo IS NOT NULL AND redondeo <> 0) THEN (net_total + redondeo) ELSE 0 END) as \"redondeo\",
regexp_replace(cast(redondeo as varchar), '[^0-9]+', '', 'g') as \"redondeoValue\",
(CASE WHEN (redondeo IS NULL OR redondeo = 0) THEN NULL ELSE (CASE WHEN redondeo > 0 THEN '+' ELSE '-' END) END) as \"signRedondeo\",
sales_transactions.net_total as \"totalSale\",
sales_transactions.amount_received as \"totalPay\",
sales_transactions.amount_due as \"due\",
NULL as \"signature\"
from sales_transactions
inner join staff on sales_transactions.staff_id = staff.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$query = $this->db->query("select name, quantity_items as quantity, item_price as price, total_price as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
function get_sale_for_print_33 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"release\",
sales_transactions.folio_doc as \"folio\",
customers.rut as \"customerRut\",
concat(customers.first_name, ' ', customers.last_name) as \"customerName\",
customers.address as \"customerAddress\",
customers.commune as \"customerCommune\",
customers.city as \"customerCity\",
customers.turn as \"customerTurn\",
(select sum(total_price / 1.19) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"netAmount\",
(sales_transactions.discounts / 1.19) as \"discountAmount\",
0 as \"ivamount\",
sales_transactions.net_total as \"total\",
NULL as \"signature\"
from sales_transactions
inner join customers on sales_transactions.customers_id = customers.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$sale->ivaAmount = ($sale->netAmount - $sale->discountAmount) * 0.19;
$query = $this->db->query("select name, quantity_items as quantity, (item_price / 1.19) as price, (total_price / 1.19) as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
function get_sale_for_print_34 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"release\",
sales_transactions.folio_doc as \"folio\",
customers.rut as \"customerRut\",
concat(customers.first_name, ' ', customers.last_name) as \"customerName\",
customers.address as \"customerAddress\",
customers.commune as \"customerCommune\",
customers.city as \"customerCity\",
customers.turn as \"customerTurn\",
(select sum(total_price) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"subTotal\",
(sales_transactions.discounts) as \"discountAmount\",
sales_transactions.net_total as \"total\",
NULL as \"signature\"
from sales_transactions
inner join customers on sales_transactions.customers_id = customers.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$query = $this->db->query("select name, quantity_items as quantity, item_price as price, total_price as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
function get_sale_for_print_52 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"release\",
sales_transactions.folio_doc as \"folio\",
customers.rut as \"customerRut\",
concat(customers.first_name, ' ', customers.last_name) as \"customerName\",
customers.address as \"customerAddress\",
customers.commune as \"customerCommune\",
customers.city as \"customerCity\",
customers.turn as \"customerTurn\",
(select sum(total_price / 1.19) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"netAmount\",
(sales_transactions.discounts / 1.19) as \"discountAmount\",
0 as \"ivamount\",
sales_transactions.net_total as \"total\",
NULL as \"signature\"
from sales_transactions
inner join customers on sales_transactions.customers_id = customers.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$sale->ivaAmount = ($sale->netAmount - $sale->discountAmount) * 0.19;
$query = $this->db->query("select name, quantity_items as quantity, (item_price / 1.19) as price, (total_price / 1.19) as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
function get_sale_for_print_61 ($id) {
$query = $this->db->query(
"select to_char(credit_notes.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"release\",
credit_notes.folio_doc as \"folio\",
customers.rut as \"customerRut\",
concat(customers.first_name, ' ', customers.last_name) as \"customerName\",
customers.address as \"customerAddress\",
customers.commune as \"customerCommune\",
customers.city as \"customerCity\",
customers.turn as \"customerTurn\",
(select sum((item_price * refund_quantity) / 1.19) from sales_transactions_details where sales_transactions_id = sales_transactions.id and refund = 1) as \"netAmount\",
(select sum(item_price * refund_quantity) from sales_transactions_details where sales_transactions_id = sales_transactions.id and refund = 1) as \"subTotal\",
(sales_transactions.discount_rate * 100) as \"discountRate\",
0 as \"ivamount\",
0 as \"total\",
NULL as \"signature\",
(case WHEN sales_transactions.document_types_id = 34 or sales_transactions.document_types_id = 41 THEN FALSE ELSE TRUE END) as \"isAffect\"
from credit_notes
inner join sales_transactions on credit_notes.sales_transactions_id = sales_transactions.id
inner join customers on credit_notes.customers_id = customers.id
where credit_notes.id = " . $id);
$sale = $query->row();
if ($sale->discountRate > 0) {
if ($sale->isAffect) {
$sale->discountAmount = $sale->netAmount * ($sale->discountRate / 100);
}
else {
$sale->discountAmount = $sale->subTotal * ($sale->discountRate / 100);
}
}
if ($sale->isAffect) {
$sale->ivaAmount = ($sale->netAmount - $sale->discountAmount) * 0.19;
}
$sale->total = ($sale->isAffect ? $sale->netAmount : $sale->subTotal) +
($sale->isAffect ? $sale->ivaAmount : 0);
$query = $this->db->query("select name, refund_quantity as quantity, item_price as price, 0 as total from sales_transactions_details where refund = 1 and sales_transactions_id = (select credit_notes.sales_transactions_id from credit_notes where credit_notes.id = " . $id . ")");
$sale->detail = $query->result();
foreach ($sale->detail as $key => &$item) {
$item->price = ($sale->isAffect ? $item->price / 1.19 : $item->price);
$item->total = $item->price * $item->quantity;
}
return $sale;
}
function get_sale_for_print_0 ($id) {
$query = $this->db->query(
"select to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as \"localeDate\",
concat(staff.first_name, ' ', staff.last_name) AS \"seller\",
(select sum(total_price) from sales_transactions_details where sales_transactions_id = sales_transactions.id) as \"total\",
sales_transactions.discounts as \"discount\",
(CASE WHEN (redondeo IS NOT NULL AND redondeo <> 0) THEN (net_total + redondeo) ELSE 0 END) as \"redondeo\",
regexp_replace(cast(redondeo as varchar), '[^0-9]+', '', 'g') as \"redondeoValue\",
(CASE WHEN (redondeo IS NULL OR redondeo = 0) THEN NULL ELSE (CASE WHEN redondeo > 0 THEN '+' ELSE '-' END) END) as \"signRedondeo\",
sales_transactions.net_total as \"totalSale\",
sales_transactions.amount_received as \"totalPay\",
sales_transactions.include_fonasa as \"fonasa\"
from sales_transactions
inner join staff on sales_transactions.staff_id = staff.id
where sales_transactions.id = " . $id);
$sale = $query->row();
$query = $this->db->query("select name, quantity_items as quantity, item_price as price, total_price as total from sales_transactions_details where sales_transactions_id = " . $id);
$sale->detail = $query->result();
return $sale;
}
}