File: /var/www/formularioinscripcion.bradford/application/models/Master_model.php
<?php
class Master_model extends CI_Model
{
function __construct()
{
parent::__construct();
}
function get_pos_status()
{
$db->select('max(sales_transactions.create_time) fecha, mbu.name, mbu.id');
$db->from('sales_transactions');
$db->join('master_business_units mbu', 'mbu.id = sales_transactions.master_business_units_id');
$db->group_by('mbu.name, mbu.id');
$db->order_by('mbu.name', 'ASC');
$query = $db->get();
return $query->result();
}
function get_all_dates()
{
$db->select('DATEPART(MM, sales_date) month, DATEPART(YYYY, sales_date) year');
$db->from('DWH_daily_sales');
$db->group_by('DATEPART(MONTH, sales_date), DATEPART(YYYY, sales_date)');
$db->order_by('DATEPART(YYYY, sales_date)', 'DESC');
$db->order_by('DATEPART(MONTH, sales_date)', 'DESC');
$query = $db->get();
return $query->result();
}
function get_sales_by_units($master_business_units_id='')
{
$this->db->select('top 1000 st.code, (st.code +1) code_post, (st.code -1) code_pre');
$this->db->from('sales_transactions st');
$this->db->join('master_business_units mbu', 'mbu.id = st.master_business_units_id');
$this->db->where('st.master_business_units_id', $master_business_units_id);
$this->db->where('st.code !=', 0);
$this->db->group_by('st.code');
$this->db->order_by('st.code', 'ASC');
$query = $this->db->get();
return $query->result();
}
function getTotalMonthSales($year=0, $month=0, $busines_unit_code='')
{
$db->select('SUM(ds.net_total) total_sales');
$db->from('DWH_daily_sales ds');
$db->join('master_business_units mbu', 'mbu.id = ds.master_business_units_id');
if($year > 0)
{
$db->where('datepart(YYYY,sales_date)=', $year);
}
if($month > 0)
{
$db->where('datepart(MM,sales_date)=', $month);
}
if(!empty($busines_unit_code))
{
$db->where('group_name', $busines_unit_code);
}
$query = $db->get();
return $query->row()->total_sales;
}
function getTotalLastMonthSalesSlab($year=0, $month=0, $expiration_date='')
{
$db->select('SUM(st.net_total) total_sales');
$db->from('sales_transactions st');
$db->join('master_business_units mbu', 'mbu.id = st.master_business_units_id');
if($year > 0)
{
$db->where('datepart(YYYY, st.create_time)=', $year);
}
if($month > 0)
{
$db->where('datepart(MM, st.create_time)=', $month);
}
$db->where('group_name', 'LOSAS');
$db->where('st.expiration_date', $expiration_date);
$query = $db->get();
return $query->row()->total_sales;
}
function getMonthlySales($year=0, $month=0, $busines_unit_code='')
{
$db->select('sum(net_total) net_total , datepart(MONTH, sales_date) sales_date');
$db->from('DWH_daily_sales ds');
$db->join('master_business_units mbu', 'mbu.id = ds.master_business_units_id');
if($year > 0)
{
$db->where('datepart(YYYY,sales_date)=', $year);
}
if($month > 0)
{
$db->where('datepart(MM,sales_date)=', $month);
}
if(!empty($busines_unit_code))
{
$db->where('mbu.group_name', $busines_unit_code);
}
$db->group_by("datepart(MONTH, sales_date)");
if(!empty($busines_unit_code))
{
$db->group_by("mbu.group_name");
}
$query = $db->get();
return $query->row()->net_total;
}
function getDailySales($year=0, $month=0, $day=0, $busines_unit_code='')
{
$db->select('sum(net_total) net_total , datepart(DAY, sales_date) sales_date');
$db->from('DWH_daily_sales ds');
$db->join('master_business_units mbu', 'mbu.id = ds.master_business_units_id');
if($year > 0)
{
$db->where('datepart(YYYY,sales_date)=', $year);
}
if($month > 0)
{
$db->where('datepart(MM,sales_date)=', $month);
}
if($day > 0)
{
$db->where('datepart(DAY,sales_date)=', $day);
}
if(!empty($busines_unit_code))
{
$db->where('mbu.group_name', $busines_unit_code);
}
if(!empty($busines_unit_code))
{
$db->group_by("mbu.group_name");
}
$db->group_by("datepart(DAY, sales_date)");
$query = $db->get();
return $query->row()->net_total;
}
function daily_sales_details($date='', $busines_unit_code='')
{
$this->db->select('st.master_id, st.code, st.create_time, st.gross_total, st.taxes, st.net_total, c.first_name, st.id, st.code');
$this->db->from('sales_transactions st');
$this->db->join('customers c', 'c.id = st.customers_id', 'LEFT');
$this->db->join('master_business_units mbu', 'mbu.id = st.master_business_units_id');
$this->db->where("is_done", TRUE);
$this->db->where("is_canceled", FALSE);
$this->db->where("convert(date, st.create_time) =", $date);
if(!empty($busines_unit_code))
{
$this->db->where('mbu.group_name', $busines_unit_code);
}
$this->db->order_by('id', 'DESC');
$query = $this->db->get();
return $query->result();
}
function get_daily_sales_products_details($date, $business_units_group_name)
{
$this->db->select('p.product_categories_id, p.name product, SUM(std.quantity_items) quantity, std.item_price, SUM(std.quantity_items) * std.item_price total_line');
$this->db->from('sales_transactions st');
$this->db->join('master_business_units mbu', 'mbu.id = st.master_business_units_id');
$this->db->join('sales_transactions_details std', 'std.sales_transactions_id = st.id AND std.master_business_units_id = mbu.id');
$this->db->join('products p', 'p.id = std.products_id AND p.master_business_units_id = mbu.id');
//$this->db->join('product_categories pc', 'pc.id = p.product_categories_id AND pc.master_business_units_id = mbu.id');
$this->db->where('convert(date, st.create_time) =', $date);
$this->db->where('is_done', TRUE);
$this->db->where('is_canceled', FALSE);
if(!empty($business_units_group_name))
{
$this->db->where('mbu.group_name', $business_units_group_name);
}
$this->db->group_by('p.product_categories_id, p.name,std.item_price');
$this->db->order_by('p.product_categories_id');
$query = $this->db->get();
return $query->result();
}
function get_sales_details($sales_transactions_id=0, $master_business_id=0)
{
$this->db->select('p.SKU, p.name products_name, p.short_description products_short_description,
std.discount_rate , std.total_price, std.quantity_items');
$this->db->from('sales_transactions_details std');
$this->db->join('products p', 'p.id = std.products_id AND p.master_business_units_id = std.master_business_units_id');
$this->db->where("sales_transactions_id", $sales_transactions_id);
$this->db->where("std.master_business_units_id", $master_business_id);
$query = $this->db->get();
return $query->result();
}
function get_group_years()
{
$this->db->select("datepart(YYYY, sales_date) year");
$this->db->group_by("datepart(YYYY, sales_date)");
$query = $this->db->get('DWH_daily_sales');
return $query->result();
}
function get_business_units_by_group_names($business_group_name='')
{
$this->db->select('id, name');
$this->db->from('master_business_units');
$this->db->where('group_name', $business_group_name);
$query = $this->db->get();
return $query->result();
}
function get_staff($business_group_name='')
{
$this->db->select('rut');
$this->db->from('staff');
$this->db->where('staff_categories_id', 200);
$this->db->group_by('rut');
$query = $this->db->get();
return $query->result();
}
function filter_report_all($master_business_group_name='', $where_filter, $date, $top=1000)
{
$this->db->select(' TOP '.$top.' sum(net_total) net_total , sales_date, c.first_name, transactions_quantity, mbu.name, s.first_name staff_first_name, s.last_name staff_last_name');
$this->db->from('DWH_daily_sales ds');
$this->db->join('master_business_units mbu', 'mbu.id = ds.master_business_units_id');
$this->db->join('customers c', 'c.id = ds.customers_id', LEFT);
$this->db->join('staff s', 's.id = ds.staff_id AND s.master_business_units_id = ds.master_business_units_id', LEFT);
foreach ($where_filter as $key => $value)
{
$this->db->where($key, $value);
}
if(!empty($date['from']))
{
$this->db->where("sales_date >=", $date['from']);
}
if(!empty($date['to']))
{
$this->db->where("sales_date <=", $date['to']);
}
if(!empty($master_business_group_name))
{
$this->db->where("mbu.group_name", $master_business_group_name);
}
$this->db->group_by("sales_date, c.first_name, transactions_quantity, mbu.name, s.first_name, s.last_name");
$this->db->order_by('sales_date', 'DESC');
$query = $this->db->get();
return $query->result();
}
/*
function get_last_sessions()
{
$this->db->select("max(sessions.id) sessions_id, mbu.name master_business_units_name, mbu.id master_business_units_id");
$this->db->from("sessions");
$this->db->join('master_business_units mbu', 'mbu.id = sessions.master_business_units_id');
$query = $this->db->where('is_open', 0);
$this->db->group_by("mbu.name, mbu.id");
$query = $this->db->get();
return $query->result();
}
function get_sessions_details($sessions_id, $master_business_units_id)
{
$this->db->select("sessions.*, pos.name pos_name ");
$this->db->from("sessions");
$this->db->join('master_business_units', 'master_business_units.id = sessions.master_business_units_id');
$this->db->join('pos', 'pos.id = sessions.pos_id AND pos.master_business_units_id = master_business_units.id');
$this->db->where("sessions.id", $sessions_id);
$this->db->where("sessions.master_business_units_id", $master_business_units_id);
//$this->db->where("pos.master_business_units_id", $master_business_units_id);
$query = $this->db->get();
return $query->row();
}
*/
function get_cashiers_by_master_group_names($master_business_units_code)
{
$this->db->select("staff.rut");
$this->db->from("staff");
$this->db->join('master_business_units', 'master_business_units.id = staff.master_business_units_id');
if (!empty($master_business_units_code))
{
$this->db->where("master_business_units.group_name", $master_business_units_code);
}
$this->db->group_by("staff.rut");
$query = $this->db->get();
return $query->result();
}
function get_sessions($post_filter=array())
{
$this->db->select("TOP 1000 sessions.master_id, sessions.id sessions_id, sessions.master_id, sessions.create_time,
sessions.logout_time, sessions.cash_withdrawal, sessions.close_total_sales, sessions.folio_init, sessions.folio_end,
master_business_units.name master_business_units_name,
staff.first_name, staff.last_name, staff.rut,
sessions.treasurer_id,
(select sum(amount) FROM cash_withdrawals where sessions_id = sessions.id AND cash_withdrawals.master_business_units_id = master_business_units.id) total_withdrawals,
(select sum(net_total)
FROM sales_transactions
WHERE sessions_id = sessions.id
AND master_business_units_id = master_business_units.id
AND is_done = 1 AND is_canceled = 0) total_sales");
$this->db->from("sessions");
$this->db->join('master_business_units', 'master_business_units.id = sessions.master_business_units_id');
$this->db->join('staff', 'staff.id = sessions.staff_id and staff.master_business_units_id = master_business_units.id');
//unidades de negocio
if (!empty($post_filter['master_business_units_code']))
{
$this->db->where("master_business_units.group_name", $post_filter['master_business_units_code']);
}
if ($post_filter['business_unit'] != 0)
{
$this->db->where("sessions.master_business_units_id", $post_filter['business_unit']);
}
//fechas
if (!empty($post_filter['from']))
{
$this->db->where("CONVERT(DATE,sessions.create_time) >=", $post_filter['from']);
}
if (!empty($post_filter['to']))
{
$this->db->where("CONVERT(DATE,sessions.create_time) <=", $post_filter['to']);
}
//folios
if (!empty($post_filter['folio_from']))
{
$this->db->where("sessions.folio_init >=", $post_filter['folio_from']);
$this->db->where("sessions.folio_end >=", $post_filter['folio_from']);
}
if (!empty($post_filter['folio_to']))
{
$this->db->where("sessions.folio_init <=", $post_filter['folio_to']);
$this->db->where("sessions.folio_end <=", $post_filter['folio_to']);
}
//cajero
if (!empty($post_filter['cashier']))
{
$this->db->where("staff.rut", $post_filter['cashier']);
}
$this->db->order_by("sessions.id", 'desc');
$this->db->order_by("sessions.master_business_units_id", 'desc');
$query = $this->db->get();
return $query->result();
}
function get_session_payments($master_id_session)
{
$this->db->select('SUM(amount) total, pm.name, p.payment_methods_id');
$this->db->from('payments p');
$this->db->join('sales_transactions st', 'st.id = p.sales_transactions_id');
$this->db->join('payment_methods pm', 'pm.id = p.payment_methods_id');
$this->db->where('st.sessions_id', $session_id);
$this->db->where('st.is_done', TRUE);
$this->db->group_by('p.payment_methods_id, pm.name');
$query = $this->db->get();
return $query->result();
}
function get_sales_sum_session($master_id_session=0)
{
$this->db->select('SUM(amount_due) amount_due, SUM(net_total) net_total');
$this->db->from('sessions s');
$this->db->join('master_business_units', 'master_business_units.id = s.master_business_units_id');
$this->db->join('sales_transactions st', 's.id = st.sessions_id AND st.master_business_units_id = master_business_units.id');
$this->db->where('s.master_id', $master_id_session);
$this->db->where('st.is_done', TRUE);
$query = $this->db->get();
return $query->row();
}
function staff_session($master_id_session=0)
{
$this->db->select('*');
$this->db->from('sessions s');
$this->db->join('master_business_units', 'master_business_units.id = s.master_business_units_id');
$this->db->join('sales_transactions st', 's.id = st.sessions_id AND st.master_business_units_id = master_business_units.id');
$this->db->where('s.master_id', $master_id_session);
$this->db->where('st.is_done', TRUE);
$query = $this->db->get();
return $query->row();
}
/*
***********************************************************
***********************************************************
***********************************************************
***********************************************************
***********************************************************
*/
function getTotalSalesByBusinessUnit($busines_unit_code='', $year=0, $month=0)
{
$this->db->select('SUM(net_total) total_sales');
$this->db->from('DWH_daily_sales ds');
$this->db->join('business_units bu', 'bu.id = ds.business_units_id');
if(!empty($busines_unit_code))
{
$this->db->where('name', $busines_unit_code);
}
if($year > 0)
{
$this->db->where('datepart(YYYY,ds.sales_date)=', $year);
}
if($month > 0)
{
$this->db->where('datepart(MM,ds.sales_date)=', $month);
}
//$this->db->where('is_done', TRUE);
//$this->db->where('is_canceled', FALSE);
$query = $this->db->get();
return $query->row()->total_sales;
}
function getTotalVentasXUnidad($busines_unit_code='', $year=0, $month=0)
{
$query = $this->db->query("select SUM(net_total) as total_sales
from sales_transactions
where business_units_id = ".$busines_unit_code."
and in_year = ".$year."
and in_month = ".$month."
and is_done = TRUE
and is_canceled = FALSE;");
$result = $query->result();
return $result[0]->total_sales;
}
function get_month_budget($group_name='', $year=0, $month=0)
{
$this->db->select('SUM(sales) AS sales');
$this->db->from('budget');
$this->db->where('is_active', 1);
$this->db->where('year', $year);
$this->db->where('month', $month);
$this->db->where('type', 'PPTO');
$this->db->where('group_name', $group_name);
$query = $this->db->get();
return $query->row()->sales;
}
function get_month_old_sales($group_name='', $year=0, $month=0)
{
$this->db->select('SUM(sales) sales');
$this->db->from('budget');
$this->db->where('is_active', TRUE);
$this->db->where('year', $year - 1);
$this->db->where('month', $month);
$this->db->where('type', 'SALES');
$this->db->where('group_name', $group_name);
$query = $this->db->get();
return $query->row()->sales;
}
function get_master_group_names()
{
$this->db->select('group_name');
$this->db->from('master_business_units');
$this->db->group_by('group_name');
$this->db->where('group_name !=', 'MASTER');
$query = $this->db->get();
return $query->result();
}
function get_cost($where, $master_business_unit_id=0)
{
$this->db->select('sum(p.actual_cost) actual_cost');
$this->db->from('sales_transactions st');
$this->db->join('sales_transactions_details std', 'std.sales_transactions_id = st.id');
$this->db->join('products p', 'p.id = std.products_id');
foreach ($where as $key => $value)
{
$this->db->where("$key", $value);
}
$this->db->where("$key", $value);
$query = $this->db->get();
return $query->row()->actual_cost;
}
function get_acumulate($master_business_unit_name='', $year=0, $month=0)
{
$this->db->select('sum(st.net_total) acumulate');
$this->db->from('sales_transactions st');
$this->db->join('master_business_units mbu', 'mbu.id = st.master_business_units_id');
$this->db->where("st.is_done", TRUE);
$this->db->where("st.is_canceled", FALSE);
$this->db->where("mbu.group_name", $master_business_unit_name);
$this->db->where("datepart(MM,st.create_time) <=", $month);
$this->db->where("datepart(YYYY,st.create_time) =", $year);
$query = $this->db->get();
return $query->row();
}
function get_acumulado($business_unit='', $year=0, $month=0)
{
$query = $this->db->query("SELECT sum(st.net_total) acumulate
FROM sales_transactions st
JOIN business_units mbu ON mbu.id = st.business_units_id
WHERE st.is_done = TRUE
AND st.is_canceled = FALSE
AND mbu.id = ".$business_unit."
AND in_month <= ".$month."
AND in_year = ".$year.";");
$result = $query->result();
return $result[0]->acumulate;
}
/// CREATE NADIA CERDA - REPORT BATHROOM
function get_master_group_bathroom()
{
$this->db->select('code');
$this->db->from('master_business_units');
$this->db->where_not_in('code', 'MSTR');
$this->db->where_not_in('code', 'LOS');
$this->db->where_not_in('code', 'CUS');
$query = $this->db->get();
return $query->result();
}
function get_report_bathroom_year($master_business_units_name='', $year=0, $month=0, $category='')
{
$this->db->select('COUNT(*) AS total');
$this->db->from('employees e');
$this->db->join('master_business_units mbu', 'mbu.id = e.master_business_units_id');
$this->db->join('employees_tickets et', 'et.employees_id = e.id AND et.master_business_units_id = mbu.id ');
$this->db->where("DATEPART([MONTH], et.create_time) =", $month);
$this->db->where("DATEPART([YEAR], et.create_time) =", $year);
$this->db->where('type', 'B');
if($master_business_units_name=='BP'){
//$this->db->where("et.burned", 1);
}
$this->db->where("e.category", $category);
if(!empty($master_business_units_name)){
$this->db->like("mbu.code ", $master_business_units_name);
}
$query = $this->db->get();
return $query->row()->total;
}
function get_report_bathroom_day ($master_business_units_name='', $year=0, $month=0, $category='', $day=0)
{
$this->db->select('COUNT(*) AS total');
$this->db->from('employees e');
$this->db->join('master_business_units mbu', 'mbu.id = e.master_business_units_id');
$this->db->join('employees_tickets et', 'et.employees_id = e.id AND et.master_business_units_id = mbu.id ');
$this->db->where("e.category", $category);
$this->db->where("DATEPART([YEAR], et.create_time) =", $year);
$this->db->where("DATEPART([MONTH], et.create_time) =", $month);
$this->db->where("DATEPART([DAY], et.create_time) =", $day);
$this->db->where('type', 'B');
if(!empty($master_business_units_name)){
$this->db->like("mbu.code ", $master_business_units_name);
}
$query = $this->db->get();
return $query->row()->total;
}
function get_report_bathroom_detail_day($master_business_units_name='', $year=0, $month=0, $day=0)
{
/*$this->db->select('e.first_name as nombres, last_name as apellidos, rut, category,
count(et.id) as total');
$this->db->from('employees e');
$this->db->join('master_business_units mbu', 'mbu.id = e.master_business_units_id');
$this->db->join('employees_tickets et', 'et.employees_id = e.id AND et.master_business_units_id = mbu.id ');
$this->db->where("DATEPART(YYYY,et.create_time) =", $year);
$this->db->where("DATEPART(MM, et.create_time) =", $month);
//if(!empty($day))
//{
$this->db->where("DATEPART(DD,et.create_time)", $day);
//}
if(!empty($master_business_units_name)){
$this->db->where("mbu.code ", $master_business_units_name);
}
$this->db->group_by('first_name, last_name, rut, category');
$this->db->order_by('rut', 'desc');
$query = $this->db->get();
return $query->result();*/
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.category,
count(employees_tickets.id) as total,
master_business_units.code,
employees.master_business_units_id');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'B');
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.category, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
function get_report_bathroom_detail_rut($master_business_units_name='', $year=0, $month=0)
{
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.category,
count(employees_tickets.id) as total,
master_business_units.code');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'B');
/*$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);*/
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
if($master_business_units_name=='BP'){
//$this->db->where("employees_tickets.burned", 1);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.category, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
/// REPORT SHOWER
function get_master_group_shower()
{
$this->db->select('code');
$this->db->from('master_business_units');
$this->db->where_not_in('code', 'MSTR');
$this->db->where_not_in('code', 'LOS');
$this->db->where_not_in('code', 'CUS');
$query = $this->db->get();
return $query->result();
}
function get_report_shower_year($master_business_units_name='', $year=0, $month=0, $company='')
{
$this->db->select('COUNT(*) AS total');
$this->db->from('employees e');
$this->db->join('master_business_units mbu', 'mbu.id = e.master_business_units_id');
$this->db->join('employees_tickets et', 'et.employees_id = e.id AND et.master_business_units_id = mbu.id ');
$this->db->where("DATEPART([MONTH], et.create_time) =", $month);
$this->db->where("DATEPART([YEAR], et.create_time) =", $year);
$this->db->where('type', 'D'); // Filtro por tipo D = Ducha
$this->db->where('e.company', $company);
if($master_business_units_name=='BP'){
//$this->db->where("et.burned", 1);
}
//$this->db->where("e.category", $category);
if(!empty($master_business_units_name)){
$this->db->like("mbu.code ", $master_business_units_name);
}
$query = $this->db->get();
return $query->row()->total;
}
function get_report_shower_day ($master_business_units_name='', $year=0, $month=0, $company='', $day=0)
{
$this->db->select('COUNT(*) AS total');
$this->db->from('employees e');
$this->db->join('master_business_units mbu', 'mbu.id = e.master_business_units_id');
$this->db->join('employees_tickets et', 'et.employees_id = e.id AND et.master_business_units_id = mbu.id ');
$this->db->where("DATEPART([YEAR], et.create_time) =", $year);
$this->db->where("DATEPART([MONTH], et.create_time) =", $month);
$this->db->where("DATEPART([DAY], et.create_time) =", $day);
$this->db->where('type', 'D');
$this->db->where('e.company', $company);
if(!empty($master_business_units_name)){
$this->db->like("mbu.code ", $master_business_units_name);
}
$query = $this->db->get();
return $query->row()->total;
}
function get_report_shower_detail_day($master_business_units_name='', $year=0, $month=0, $day=0)
{
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.company,
count(employees_tickets.id) as total,
count(CASE WHEN employees_tickets.paid = 0 THEN 1 END) as planilla,
SUM (CASE WHEN employees_tickets.paid = 0 THEN employees_tickets.ticket_price END) AS tot_planilla,
count(CASE WHEN employees_tickets.paid = 1 THEN 1 END) as efectivo,
SUM (CASE WHEN employees_tickets.paid = 1 THEN employees_tickets.ticket_price END) AS tot_efectivo,
master_business_units.code,
employees.master_business_units_id');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.company, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
function get_report_shower_detail_rut($master_business_units_name='', $year=0, $month=0)
{
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.company,
count(employees_tickets.id) as total,
count(CASE WHEN employees_tickets.paid = 0 THEN 1 END) as planilla,
count(CASE WHEN employees_tickets.paid = 1 THEN 1 END) as efectivo,
SUM (CASE WHEN employees_tickets.paid = 0 THEN employees_tickets.ticket_price END) AS tot_planilla,
SUM (CASE WHEN employees_tickets.paid = 1 THEN employees_tickets.ticket_price END) AS tot_efectivo,
master_business_units.code');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
/*$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);*/
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
if($master_business_units_name=='BP'){
//$this->db->where("employees_tickets.burned", 1);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.company, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
function get_report_shower_detail_rut_xls($master_business_units_name='', $year=0, $month=0)
{
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.company,
count(employees_tickets.id) as total,
count(CASE WHEN employees_tickets.paid = 0 THEN 1 END) as planilla,
SUM (CASE WHEN employees_tickets.paid = 0 THEN employees_tickets.ticket_price END) AS tot_planilla,
master_business_units.code');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
$this->db->where('company', 'PyR');
$this->db->where('employees_tickets.paid <', 1);
/*$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);*/
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
if($master_business_units_name=='BP'){
//$this->db->where("employees_tickets.burned", 1);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.company, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
function get_report_shower_detail_day_xls($master_business_units_name='', $year=0, $month=0, $day=0)
{
$this->db->select('employees.first_name as nombres,
employees.last_name as apellidos,
employees.rut,
employees.company,
count(employees_tickets.id) as total,
count(CASE WHEN employees_tickets.paid = 0 THEN 1 END) as planilla,
SUM (CASE WHEN employees_tickets.paid = 0 THEN employees_tickets.ticket_price END) AS tot_planilla,
master_business_units.code');
$this->db->from('employees');
$this->db->join('employees_tickets', 'employees_tickets.employees_id = employees.id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
$this->db->where('company', 'PyR');
$this->db->where('employees_tickets.paid <', 1);
/*$this->db->where("DATEPART(DAY,employees_tickets.create_time) =", $day);*/
if(!empty($master_business_units_name)){
$this->db->like("master_business_units.code", $master_business_units_name);
}
if($master_business_units_name=='BP'){
//$this->db->where("employees_tickets.burned", 1);
}
$this->db->group_by('employees.first_name, employees.last_name, employees.rut, employees.company, master_business_units.code,employees.master_business_units_id');
$this->db->order_by('employees.rut', 'desc');
$query = $this->db->get();
return $query->result();
}
function detalle_tickets($master_business_units_name='', $year=0, $month=0, $rut='')
{
$this->db->select('employees_tickets.folio_doc');
$this->db->from('employees_tickets');
$this->db->join('employees', 'employees.id = employees_tickets.employees_id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
$this->db->where('company', 'PyR');
$this->db->where('employees_tickets.paid <', 1);
$this->db->where('employees.rut', $rut);
$query = $this->db->get();
return $query->result();
}
function detalle_tickets_day($master_business_units_name='', $year=0, $month=0, $day=0, $rut='')
{
$this->db->select('employees_tickets.folio_doc');
$this->db->from('employees_tickets');
$this->db->join('employees', 'employees.id = employees_tickets.employees_id');
$this->db->join('master_business_units', 'master_business_units.id = employees.master_business_units_id');
$this->db->where("DATEPART(YEAR,employees_tickets.create_time) =", $year);
$this->db->where("DATEPART(MONTH, employees_tickets.create_time) =", $month);
$this->db->where("DATEPART(DAY, employees_tickets.create_time) =", $day);
$this->db->where('employees.is_active',1);
$this->db->where("employees.master_business_units_id = employees_tickets.master_business_units_id");
$this->db->where('type', 'D');
$this->db->where('company', 'PyR');
$this->db->where('employees_tickets.paid <', 1);
$this->db->where('employees.rut', $rut);
$query = $this->db->get();
return $query->result();
}
function get_employees ($id = NULL, $select = NULL) {
$unique = isset($id);
$custom = isset($select);
$this->db
->select(
($custom ?
$select :
'employees.master_id,
employees.first_name,
employees.last_name,
employees.rut,
employees.category,
'.($unique ? 'employees.master_business_units_id,' : 'master_business_units.name as business,').
'employees.is_active')
)
->from('employees');
if ($unique) {
$this->db->where(array('master_id'=> $id));
}
else {
$this->db->join(
'master_business_units',
'employees.master_business_units_id = master_business_units.id'
);
}
$query = $this->db->get();
return $unique ? $query->row() : $query->result();
}
function get_business_units () {
return $this->db
->select('id, name')
->from('master_business_units')
->get()
->result();
}
function get_last_session($business){
$this->db->select('top 15 sessions.master_id');
$this->db->select('sessions.create_time');
$this->db->select('master_business_units.name');
$this->db->select('staff.first_name');
$this->db->select('staff.last_name');
$this->db->select('sessions.close_total_sales');
$this->db->select('master_treasurer.cash_received');
$this->db->join('staff','staff.id = sessions.staff_id','INNER');
$this->db->join('master_business_units','master_business_units.id = staff.master_business_units_id','INNER');
$this->db->join('master_treasurer','master_treasurer.session_id = sessions.master_id','LEFT');
$this->db->where('sessions.master_business_units_id = staff.master_business_units_id');
if(!empty($business))
{
$this->db->where('master_business_units.group_name',$business);
}
$this->db->order_by('sessions.master_id','DESC');
$query = $this->db->get('sessions');
return $query->result();
}
function credit_unidades(){
$this->db->select('id,name');
$this->db->where_not_in(7,9);
$this->db->order_by('name','ASC');
$query = $this->db->get('master_business_units');
return $query->result();
}
function credit_años(){
$this->db->select('in_year');
$this->db->where("in_year != ''");
$this->db->group_by('in_year');
$this->db->order_by('in_year','DESC');
$query = $this->db->get('sales_transactions');
return $query->result();
}
function credit_resul($data){
$queryNames = "(SELECT first_name FROM staff WHERE id = stra.staff_id AND master_business_units_id = ".$data['unidad'].") AS nombres";
$queryLast = "(SELECT last_name FROM staff WHERE id = stra.staff_id AND master_business_units_id = ".$data['unidad'].") AS apellidos";
$this->db->select('stra.master_id');
$this->db->select('stra.code');
$this->db->select('stra.master_business_units_id');
$this->db->select('stra.quantity_items');
$this->db->select('stra.gross_total');
$this->db->select('stra.create_time');
$this->db->select('mbu.name');
$this->db->select('stra.staff_id');
$this->db->select($queryNames);
$this->db->select($queryLast);
$this->db->join('master_business_units mbu','mbu.id = stra.master_business_units_id','INNER');
$this->db->where('stra.in_year',$data['anio']);
$this->db->where('stra.in_month',$data['mes']);
$this->db->where('stra.master_business_units_id',$data['unidad']);
$this->db->where('stra.code',$data['documento']);
$query = $this->db->get('sales_transactions stra');
return $query->result();
}
function get_sessions_unit($master_id){
$this->db->select('mbu.name,mbu.group_name,,ss.close_total_sales');
$this->db->join('master_business_units mbu','mbu.id = ss.master_business_units_id','INNER');
$this->db->where('master_id',$master_id);
$query = $this->db->get('sessions ss');
return $query->result();
}
function get_sessions_unit_group(){
$this->db->select('DISTINCT(group_name)');
$this->db->where_not_in('group_name','MASTER');
$query = $this->db->get('master_business_units');
return $query->result();
}
function get_turn_session_unit($unit,$fecha){
$this->db->select('SUM(mtr.cash_received) as total');
$this->db->select('mtr.treasurer_user_id');
$this->db->select('ss.staff_id');
$this->db->select('ss.master_business_units_id');
$this->db->select('mbu.name');
$this->db->join('sessions ss','ss.master_id = mtr.session_id','INNER');
$this->db->join('master_business_units mbu','mbu.id = ss.master_business_units_id','INNER');
$this->db->where('cash_date',$fecha);
$this->db->where('mtr.cash_round',$unit);
$this->db->group_by('mtr.treasurer_user_id,ss.staff_id,ss.master_business_units_id,mbu.name');
$this->db->order_by('mbu.name','ASC');
$query = $this->db->get('master_treasurer mtr');
return $query->result();
}
function get_turn_session_unit_user($id,$master){
$this->db->select('first_name,last_name');
$this->db->where('id',$id);
$this->db->where('master_business_units_id',$master);
$query = $this->db->get('staff');
return $query->result();
}
}