public function get_usuarios($paginados = true)
{
$sql = '';
//paginacion
if ($paginados) {
$this->load->library('pagination');
$params = array('result_items_per_page' => '40', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = "WHERE ( lower(u.nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\t\t\t\t\tlower(u.usuario) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\t\t\t\t\tlower(u.email) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%')";
}
if ($this->input->get('fstatus') != '' && $this->input->get('fstatus') != 'todos') {
$sql .= ($sql == '' ? 'WHERE' : ' AND') . " u.status='" . $this->input->get('fstatus') . "'";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT u.id AS id_usuario, u.nombre, u.usuario, u.email, u.tipo, u.status\n\t\t\t\tFROM usuarios u\n\t\t\t\t" . $sql . "\n\t\t\t\tORDER BY u.usuario ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('usuarios' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['usuarios'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado Paginado de
*/
public function getNominaPilotos($per_page = '9999')
{
$sql = '';
$sql_date_v = '';
$sql_date_a = '';
//paginacion
$params = array('result_items_per_page' => $per_page, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
$sql_date_v = "AND DATE(v.fecha)<='" . date("Y-m-d") . "'";
$sql_date_a = "AND DATE(pa.fecha)<='" . date("Y-m-d") . "'";
//Filtros para buscar
if ($this->input->get('ffecha1') != '' && $this->input->get('ffecha2') != '') {
if (strtotime($this->input->get('ffecha1')) <= strtotime($this->input->get('ffecha2'))) {
$sql_date_v = "AND DATE(v.fecha)<='{$this->input->get('ffecha2')}'";
$sql_date_a = "AND DATE(pa.fecha)<='{$this->input->get('ffecha2')}'";
} else {
$sql_date_v = "AND DATE(v.fecha)<='{$this->input->get('ffecha1')}'";
$sql_date_a = "AND DATE(pa.fecha)<='{$this->input->get('ffecha1')}'";
}
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT p.id_proveedor, p.nombre, \n\t\t\t\t\t\tSUM(v.costo_piloto) as total_costo_piloto, \n\t\t\t\t\t\tSUM(v.iva_piloto) as total_iva_piloto,\n\t\t\t\t\t\tSUM(v.costo_piloto)+SUM(v.iva_piloto) as total_vuelos, \n\t\t\t\t\t\tCOALESCE(ta.total_abonos,0) as total_abonos,\n\t\t\t\t\t\t(SUM(v.costo_piloto)+SUM(v.iva_piloto))-COALESCE(ta.total_abonos,0) as total_saldo\n\t\t\t\tFROM proveedores as p\n\t\t\t\tINNER JOIN vuelos as v ON p.id_proveedor=v.id_piloto \n\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\tSELECT COALESCE(SUM(pa.total),0) as total_abonos, pa.id_proveedor \n\t\t\t\t\t\tFROM proveedores_abonos as pa WHERE pa.tipo='ab' {$sql_date_a} \n\t\t\t\t\t\tGROUP BY pa.id_proveedor\n\t\t\t\t\t) as ta ON ta.id_proveedor=p.id_proveedor\n\t\t\t\tWHERE p.tipo='pi' {$sql_date_v}\n\t\t\t\t" . $sql . "\n\t\t\t\tGROUP BY p.id_proveedor, p.nombre, total_abonos\n\t\t\t\tORDER BY p.nombre ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('pilotos' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['pilotos'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de proveedores y el saldo que se les debe
*/
public function getCuentasXCobrarData($per_pag = '9999')
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => $per_pag, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
$_GET['ffecha1'] = $this->input->get('ffecha1') == '' ? date("Y-m-") . '01' : $this->input->get('ffecha1');
$_GET['ffecha2'] = $this->input->get('ffecha2') == '' ? date("Y-m-d") : $this->input->get('ffecha2');
$fecha = $_GET['ffecha1'] > $_GET['ffecha2'] ? $_GET['ffecha1'] : $_GET['ffecha2'];
$_GET['ftipo'] = isset($_GET['ftipo']) ? $_GET['ftipo'] : 'pp';
$sql = $this->input->get('ftipo') == 'pv' ? " AND (Date('" . $fecha . "'::timestamp with time zone)-Date(f.fecha)) > f.plazo_credito" : '';
$sqlt = $this->input->get('ftipo') == 'pv' ? " AND (Date('" . $fecha . "'::timestamp with time zone)-Date(t.fecha)) > t.dias_credito" : '';
$query = BDUtil::pagination("\n\t\t\tSELECT \n\t\t\t\tid_cliente,\n\t\t\t\tnombre_fiscal as nombre,\n\t\t\t\tSUM(total) as total,\n\t\t\t\tSUM(iva) as iva, \n\t\t\t\tSUM(saldo) as saldo\n\t\t\tFROM \n\t\t\t\t(\n\t\t\t\t\tSELECT \n\t\t\t\t\t\tc.id_cliente,\n\t\t\t\t\t\tc.nombre_fiscal,\n\t\t\t\t\t\tSum(f.total) AS total,\n\t\t\t\t\t\tSum(f.importe_iva) AS iva, \n\t\t\t\t\t\tCOALESCE(Sum(f.total) - COALESCE(faa.abonos,0), 0) AS saldo\n\t\t\t\t\tFROM\n\t\t\t\t\t\tclientes AS c\n\t\t\t\t\t\tINNER JOIN facturacion AS f ON c.id_cliente = f.id_cliente\n\t\t\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\t\tSELECT \n\t\t\t\t\t\t\t\tf.id_cliente,\n\t\t\t\t\t\t\t\tSum(fa.total) AS abonos\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tfacturacion AS f INNER JOIN facturacion_abonos AS fa ON f.id_factura = fa.id_factura\n\t\t\t\t\t\t\tWHERE f.status <> 'ca'\n\t\t\t\t\t\t\t\tAND fa.tipo <> 'ca' AND Date(fa.fecha) <= '" . $fecha . "'" . $sql . "\n\t\t\t\t\t\t\tGROUP BY f.id_cliente\n\t\t\t\t\t\t) AS faa ON c.id_cliente = faa.id_cliente\n\t\t\t\t\tWHERE f.status <> 'ca' AND Date(f.fecha) <= '" . $fecha . "'" . $sql . "\n\t\t\t\t\tGROUP BY c.id_cliente, c.nombre_fiscal, faa.abonos\n\n\t\t\t\t\tUNION ALL\n\n\t\t\t\t\tSELECT \n\t\t\t\t\t\tc.id_cliente,\n\t\t\t\t\t\tc.nombre_fiscal,\n\t\t\t\t\t\tSum(t.total) AS total,\n\t\t\t\t\t\t0 AS iva,\n\t\t\t\t\t\tCOALESCE(Sum(t.total) - COALESCE(taa.abonos,0), 0) AS saldo\n\t\t\t\t\tFROM \n\t\t\t\t\t\tclientes AS c\n\t\t\t\t\t\tINNER JOIN tickets AS t ON c.id_cliente = t.id_cliente\n\t\t\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\t\tSELECT \n\t\t\t\t\t\t\t\tt.id_cliente,\n\t\t\t\t\t\t\t\tSum(ta.total) AS abonos\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\ttickets AS t INNER JOIN tickets_abonos AS ta ON t.id_ticket = ta.id_ticket\n\t\t\t\t\t\t\tWHERE valida_ticket_fac(t.id_ticket)='t' AND t.status <> 'ca'\n\t\t\t\t\t\t\t\tAND ta.tipo <> 'ca' AND Date(ta.fecha) <='" . $fecha . "'" . $sqlt . "\n\t\t\t\t\t\t\tGROUP BY t.id_cliente\n\t\t\t\t\t\t) AS taa ON c.id_cliente = taa.id_cliente\n\t\t\t\t\tWHERE valida_ticket_fac(t.id_ticket)='t' AND t.status <> 'ca' AND Date(t.fecha) <= '" . $fecha . "'" . $sqlt . "\n\t\t\t\t\tGROUP BY c.id_cliente, c.nombre_fiscal, taa.abonos\n\n\t\t\t\t) AS sal\n\n\t\t\tGROUP BY id_cliente, nombre_fiscal\n\t\t\tORDER BY nombre_fiscal ASC\n\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('cuentas' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page'], 'ttotal' => 0);
if ($res->num_rows() > 0) {
$response['cuentas'] = $res->result();
}
foreach ($query['resultset']->result() as $cliente) {
$response['ttotal'] += $cliente->saldo;
}
return $response;
}
/**
* Obtiene el listado de empleados
*/
public function getEmpleados($paginados = true)
{
$sql = '';
//paginacion
if ($paginados) {
$params = array('result_items_per_page' => '40', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = "WHERE lower((apellido_paterno || ' ' || apellido_materno || ' ' || nombre))\n\t\t\t\tLIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
if ($this->input->get('fstatus') != 'todos') {
$_GET['fstatus'] = $this->input->get('fstatus') == '' ? 'contratado' : $this->input->get('fstatus');
$sql .= ($sql == '' ? 'WHERE' : ' AND') . " lower(status) LIKE '" . mb_strtolower($this->input->get('fstatus'), 'UTF-8') . "'";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT id_empleado, (apellido_paterno || ' ' || apellido_materno || ' ' || nombre) AS e_nombre, telefono, tipo_usuario, status\n\t\t\t\tFROM empleados\n\t\t\t\t" . $sql . "\n\t\t\t\tORDER BY e_nombre ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('empleados' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['empleados'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de proveedores
*/
public function getClientes()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '20', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = " AND lower(nombre_fiscal) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
if ($this->input->get('fmunicipio') != '') {
$sql = " AND lower(municipio) LIKE '%" . mb_strtolower($this->input->get('fmunicipio'), 'UTF-8') . "%'";
}
if ($this->input->get('festado') != '') {
$sql = " AND lower(estado) LIKE '%" . mb_strtolower($this->input->get('festado'), 'UTF-8') . "%'";
}
if ($this->input->get('fcalle') != '') {
$sql = " AND lower(calle) LIKE '%" . mb_strtolower($this->input->get('fcalle'), 'UTF-8') . "%'";
}
// if($this->input->get('fstatus') != 'todos'){
// $_GET['fstatus'] = $this->input->get('fstatus')==''? 'ac': $this->input->get('fstatus');
// $sql .= ($sql==''? 'WHERE': ' AND')." lower(status) LIKE '".mb_strtolower($this->input->get('fstatus'), 'UTF-8')."'";
// }
$query = BDUtil::pagination("\r\n\t\t\t\tSELECT id_cliente, nombre_fiscal, telefono, email, dias_pago, recepcion_facturas\r\n\t\t\t\tFROM clientes\r\n\t\t\t\tWHERE status = 'ac'\r\n\t\t\t\t" . $sql . "\r\n\t\t\t\tORDER BY nombre_fiscal ASC\r\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('clientes' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['clientes'] = $res->result();
return $response;
}
/**
* Obtiene el listado de todos los productos base paginados
*/
public function obtenProductosBase($per_pag = '40', $sqlp = '')
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => $per_pag, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = "WHERE ( lower(pb.nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(pb.stock_min) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\tlower(pb.descripcion) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' )";
}
$fstatus = $this->input->get('fstatus') === false ? '1' : $this->input->get('fstatus');
if ($fstatus != '' && $fstatus != 'todos') {
$sql .= ($sql == '' ? 'WHERE' : ' AND') . " pb.status = '" . $fstatus . "'";
}
$sql .= $sqlp != '' ? ($sql == '' ? 'WHERE' : ' AND') . $sqlp : '';
$query = BDUtil::pagination("\n\t\t\tSELECT pb.id, pb.nombre,pb.tipo, pb.stock_min, pb.status, pe.existencia, pe.precio_compra\n\t\t\tFROM productos_base AS pb INNER JOIN productos_base_existencias AS pe ON pe.id = pb.id\n\t\t\t" . $sql . "\n\t\t\tORDER BY pb.nombre ASC\n\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('productos' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['productos'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de compras
*/
public function getCompras()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('ffecha') != '') {
$sql = " AND Date(c.fecha) = '" . $this->input->get('ffecha') . "'";
}
if ($this->input->get('fserie') != '') {
$sql .= " AND c.serie = '" . mb_strtoupper($this->input->get('fserie')) . "'";
}
if ($this->input->get('ffolio') != '') {
$sql .= " AND c.folio = '" . $this->input->get('ffolio') . "'";
}
if ($this->input->get('fid_proveedor') != '') {
$sql .= " AND p.id_proveedor = '" . $this->input->get('fid_proveedor') . "'";
}
if ($this->input->get('ftipo') != '') {
$status = $this->input->get('ftipo') == 'co' ? 'f' : 't';
$sql .= " AND is_gasto = '" . $status . "'";
}
$query = BDUtil::pagination("\r\n\t\t\t\tSELECT c.id_compra, Date(c.fecha) AS fecha, c.serie, c.folio, p.nombre, c.is_gasto, c.status, p.tipo\r\n\t\t\t\tFROM compras AS c INNER JOIN proveedores AS p ON p.id_proveedor = c.id_proveedor\r\n\t\t\t\tWHERE c.status <> 'ca' AND c.status <> 'n'" . $sql . "\r\n\t\t\t\tORDER BY (Date(c.fecha), c.serie, c.folio) DESC\r\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('compras' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['compras'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de las herramientas prestadas
*/
public function getHerramientas()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('ffecha') != '') {
$sql = " AND Date(s.fecha) = '" . $this->input->get('ffecha') . "'";
}
if ($this->input->get('fmostrar') != '') {
if ($this->input->get('fmostrar') == 'pv') {
$sql .= " AND DATE(fecha_vencimiento)-DATE(now())<=3 AND DATE(fecha_vencimiento)-DATE(now())>=1";
} elseif ($this->input->get('fmostrar') == 've') {
$sql .= " AND DATE(now())>=DATE(fecha_vencimiento)";
}
}
if ($this->input->get('ftipo') != '') {
$sql .= " AND s.status = '" . $this->input->get('ftipo') . "'";
}
$query = BDUtil::pagination("\r\n\t\t\t\tSELECT id_alerta, tabla_obj, id_obj1, id_obj2, descripcion, DATE(fecha_vencimiento) as fecha_vencimiento, DATE(fecha_vencimiento)-DATE(now()) as dias_restantes\r\n\t\t\t\tFROM alertas\r\n\t\t\t\tWHERE tabla_obj = 'salidas_productos' {$sql}\r\n\t\t\t\tORDER BY (Date(fecha_vencimiento)) DESC\r\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('herramientas' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['herramientas'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de todos los productos base paginados
*/
public function obtenFamilias()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '40', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
//buscar en descripcion de productos base
$filt_sql = '';
$palabras = explode(' ', mb_strtolower($this->input->get('fnombre'), 'UTF-8'));
foreach ($palabras as $key => $value) {
$filt_sql .= "AND lower(pb.descripcion) LIKE '%" . $value . "%' ";
}
$filt_sql = substr($filt_sql, 3);
$sql = " AND ( lower(pf.nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\tlower(pf.precio_venta) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\tlower(pf.codigo_barra) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\t(" . $filt_sql . ") )";
}
$fstatus = $this->input->get('fstatus') === false ? '1' : $this->input->get('fstatus');
if ($fstatus != '' && $fstatus != 'todos') {
$sql .= " AND pf.status = '" . $fstatus . "'";
}
$query = BDUtil::pagination("\n\t\t\tSELECT pf.id, pf.id_padre, pf.nombre, pf.precio_venta, pf.codigo_barra, pf.imagen, pf.color1, pf.color2, pf.ultimo_nodo, pf.status\n\t\t\tFROM productos_familias AS pf\n\t\t\t\tLEFT JOIN productos_base_familia AS pbf ON pf.id = pbf.familia_id\n\t\t\t\tLEFT JOIN productos_base AS pb ON pb.id = pbf.base_id\n\t\t\tWHERE pf.id <> 1 " . $sql . "\n\t\t\tGROUP BY pf.id\n\t\t\tORDER BY pf.nombre ASC\n\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('familias' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['familias'] = $res->result();
}
return $response;
}
public function getTickets()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
switch ($this->input->get('fstatus')) {
case 'todos':
$sql = "t.status<>'ca'";
break;
case 'pendientes':
$sql = "t.status='p'";
break;
case 'pagados':
$sql = "t.status='pa'";
break;
}
if ($this->input->get('fstatus') == '') {
$sql = "t.status<>'ca'";
}
if ($this->input->get('ffecha_ini') != '') {
$sql .= $this->input->get('ffecha_fin') != '' ? " AND DATE(t.fecha)>='" . $this->input->get('ffecha_ini') . "'" : " AND DATE(t.fecha)='" . $this->input->get('ffecha_ini') . "'";
}
if ($this->input->get('ffecha_fin') != '') {
$sql .= $this->input->get('ffecha_ini') != '' ? " AND DATE(t.fecha)<='" . $this->input->get('ffecha_fin') . "'" : " AND DATE(t.fecha)='" . $this->input->get('ffecha_fin') . "'";
}
if ($this->input->get('ffecha_ini') == '' && $this->input->get('ffecha_fin') == '') {
$sql .= " AND DATE(t.fecha)=DATE(now())";
}
if ($this->input->get('fid_empresa') != '') {
$sql .= " AND e.id_empresa = '" . $this->input->get('fid_empresa') . "'";
}
if ($this->input->get('fidcliente') != '') {
$sql .= " AND c.id_cliente = '" . $this->input->get('fidcliente') . "'";
}
$query = BDUtil::pagination("\r\n\t\t\t\tSELECT t.id_ticket, t.folio, t.fecha, t.tipo_pago, c.nombre_fiscal as cliente, t.status, valida_ticket(t.id_ticket) as disponible,\r\n\t\t\t\t\te.id_empresa, e.nombre_fiscal\r\n\t\t\t\tFROM tickets as t\r\n\t\t\t\t\tINNER JOIN clientes as c ON t.id_cliente=c.id_cliente\r\n\t\t\t\t\tINNER JOIN empresas as e ON t.id_empresa=e.id_empresa\r\n\t\t\t\tWHERE " . $sql . "\r\n\t\t\t\tORDER BY (folio,DATE(t.fecha)) DESC\r\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('tickets' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['tickets'] = $res->result();
return $response;
}
/**
* Obtiene el listado de proveedores
*/
public function getEmpresas()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = " AND lower(nombre_fiscal) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT id_empresa, nombre_fiscal, rfc, telefono\n\t\t\t\tFROM empresas\n\t\t\t\tWHERE status = 'ac'\n\t\t\t\t" . $sql . "\n\t\t\t\tORDER BY nombre_fiscal ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('empresas' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['empresas'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de todos los privilegios paginados
*/
public function obtenPrivilegios()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '40', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = "WHERE ( lower(nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n\t\t\t\tlower(url_accion) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' )";
}
$query = BDUtil::pagination("\n\t\t\tSELECT id AS id_privilegio, id_padre, nombre, mostrar_menu, url_accion\n\t\t\tFROM privilegios\n\t\t\t" . $sql . "\n\t\t\tORDER BY url_accion ASC\n\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('privilegios' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['privilegios'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de proveedores y el saldo que se les debe
*/
public function getCuentasXPagarData($per_pag = '9999')
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => $per_pag, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
$_GET['ffecha1'] = $this->input->get('ffecha1') == '' ? date("Y-m-") . '01' : $this->input->get('ffecha1');
$_GET['ffecha2'] = $this->input->get('ffecha2') == '' ? date("Y-m-d") : $this->input->get('ffecha2');
$fecha = $_GET['ffecha1'] > $_GET['ffecha2'] ? $_GET['ffecha1'] : $_GET['ffecha2'];
$sql = $this->input->get('ftipo') == 'pv' ? " AND (Date('" . $fecha . "'::timestamp with time zone)-Date(c.fecha)) > c.plazo_credito" : '';
$query = BDUtil::pagination("\n\t\t\tSELECT \n\t\t\t\tid_proveedor,\n\t\t\t\tnombre,\n\t\t\t\ttotal,\n\t\t\t\tiva, \n\t\t\t\tsaldo\n\t\t\tFROM \n\t\t\t\t(\n\t\t\t\t\tSELECT \n\t\t\t\t\t\tp.id_proveedor,\n\t\t\t\t\t\tp.nombre,\n\t\t\t\t\t\tSum(c.total) AS total,\n\t\t\t\t\t\tSum(c.importe_iva) AS iva, \n\t\t\t\t\t\tCOALESCE(Sum(c.total) - COALESCE(caa.abonos,0), 0) AS saldo\n\t\t\t\t\tFROM\n\t\t\t\t\t\tproveedores AS p \n\t\t\t\t\t\tINNER JOIN compras AS c ON p.id_proveedor = c.id_proveedor\n\t\t\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\t\tSELECT \n\t\t\t\t\t\t\t\tc.id_proveedor,\n\t\t\t\t\t\t\t\tSum(ca.total) AS abonos\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tcompras AS c INNER JOIN compras_abonos AS ca ON c.id_compra = ca.id_compra\n\t\t\t\t\t\t\tWHERE c.status <> 'ca' AND c.status <> 'n' \n\t\t\t\t\t\t\t\tAND ca.tipo <> 'ca' AND Date(ca.fecha) <= '" . $fecha . "'" . $sql . "\n\t\t\t\t\t\t\tGROUP BY c.id_proveedor\n\t\t\t\t\t\t) AS caa ON p.id_proveedor = caa.id_proveedor\n\t\t\t\t\tWHERE p.tipo <> 'pi' AND c.status <> 'ca' AND c.status <> 'n' AND Date(c.fecha) <= '" . $fecha . "'" . $sql . "\n\t\t\t\t\tGROUP BY p.id_proveedor, p.nombre, caa.abonos\n\t\t\t\t) AS sal\n\t\t\tORDER BY nombre ASC\n\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('cuentas' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['cuentas'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de clientes
*/
public function getClientes()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '20', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = " WHERE ( \n\t\t\t\tlower(nombre_fiscal) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(rfc) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(email) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(calle) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(colonia) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(municipio) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR \n\t\t\t\tlower(estado) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'\n\t\t\t\t)";
}
$fstatus = $this->input->get('fstatus') === false ? '1' : $this->input->get('fstatus');
if ($fstatus != '' && $fstatus != 'todos') {
$sql .= ($sql == '' ? ' WHERE ' : ' AND ') . " status = '" . $fstatus . "'";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT id AS id_cliente, nombre_fiscal, rfc, telefono, email, status\n\t\t\t\tFROM clientes\n\t\t\t\t" . $sql . "\n\t\t\t\tORDER BY nombre_fiscal ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('clientes' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['clientes'] = $res->result();
return $response;
}
public function getAviones($id_avion = false, $order = 'matricula ASC')
{
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
$order = !empty($order) ? $order : 'matricula ASC';
$id_avion ? $this->db->where('id_avion', $id_avion) : null;
$this->db->where('status', 'ac');
$this->db->like('lower(matricula)', mb_strtolower($this->input->get('fmatricula'), 'UTF-8'));
$this->db->order_by($order);
$this->db->get('aviones');
$sql = $this->db->last_query();
$query = BDUtil::pagination($sql, $params, true);
$res = $this->db->query($query['query']);
$data = array('aviones' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$data['aviones'] = $res->result();
}
return $data;
}
/**
* Obtiene el listado de precios para los productos de una familia y una lista de precios
* @param unknown_type $id_familia
* @param unknown_type $id_lista
* @param unknown_type $orderby
*/
public function getPreciosProductosLista($id_familia = null, $id_lista = null, $orderby = 'ORDER BY p.codigo ASC', $paginar = true)
{
$sql = $sql1 = '';
//paginacion
if ($paginar) {
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
}
//filtro de familia
if ($id_familia != null) {
$sql = "WHERE id_familia = '" . $id_familia . "'";
$sql1 = " AND id_familia = '" . $id_familia . "'";
}
//filtro de lista
if ($id_lista != null) {
$sql .= ($sql == '' ? 'WHERE ' : ' AND ') . "id_lista = '" . $id_lista . "'";
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql1 .= " AND lower(nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
$query = "\n\t\t\t\tSELECT p.id_producto, p.id_familia, lp.id_lista, p.codigo, p.nombre, lp.precio\n\t\t\t\tFROM (\n\t\t\t\t\t\tSELECT id_producto, id_familia, codigo, nombre \n\t\t\t\t\t\tFROM productos\n\t\t\t\t\t\tWHERE status = 'ac' " . $sql1 . "\n\t\t\t\t\t) AS p \n\t\t\t\t\tLEFT JOIN \n\t\t\t\t\t(\n\t\t\t\t\t\tSELECT * \n\t\t\t\t\t\tFROM listas_precio \n\t\t\t\t\t\t" . $sql . "\n\t\t\t\t\t) AS lp ON p.id_producto = lp.id_producto\n\t\t\t\t" . $orderby . "\n\t\t\t\t";
$response = array('productos' => array());
if ($paginar) {
$query = BDUtil::pagination($query, $params, true);
$res = $this->db->query($query['query']);
$response['total_rows'] = $query['total_rows'];
$response['items_per_page'] = $params['result_items_per_page'];
$response['result_page'] = $params['result_page'];
} else {
$res = $this->db->query($query);
}
if ($res->num_rows() > 0) {
$response['productos'] = $res->result();
}
return $response;
}
public function getNotasVenta()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
switch ($this->input->get('fstatus')) {
case 'todos':
$sql = "tnv.status<>'ca'";
break;
case 'pendientes':
$sql = "tnv.status='p'";
break;
case 'pagados':
$sql = "tnv.status='pa'";
break;
}
if ($this->input->get('fstatus') == '') {
$sql = "tnv.status<>'ca'";
}
if ($this->input->get('ffecha_ini') != '') {
$sql .= $this->input->get('ffecha_fin') != '' ? " AND DATE(tnv.fecha)>='" . $this->input->get('ffecha_ini') . "'" : " AND DATE(tnv.fecha)='" . $this->input->get('ffecha_ini') . "'";
}
if ($this->input->get('ffecha_fin') != '') {
$sql .= $this->input->get('ffecha_ini') != '' ? " AND DATE(tnv.fecha)<='" . $this->input->get('ffecha_fin') . "'" : " AND DATE(tnv.fecha)='" . $this->input->get('ffecha_fin') . "'";
}
// if($this->input->get('ffecha_ini') == '' && $this->input->get('ffecha_fin') == '')
// $sql .= " AND DATE(tnv.fecha)=DATE(now())";
$query = BDUtil::pagination("\n\t\t\t\tSELECT tnv.id_nota_venta, tnv.folio, tnv.fecha, tnv.tipo_pago, c.nombre_fiscal as cliente, tnv.status\n\t\t\t\tFROM tickets_notas_venta as tnv\n\t\t\t\tINNER JOIN clientes as c ON tnv.id_cliente=c.id_cliente\n\t\t\t\tWHERE {$sql}\n\t\t\t\tORDER BY DATE(tnv.fecha) DESC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('notas' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['notas'] = $res->result();
return $response;
}
/**
* Obtiene el listado de todos los tickets
*/
public function obten_tickets()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '40', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = " ( lower(u.usuario) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n lower(t.total) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%' OR\n lower(t.folio) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%')";
}
$fstatus = $this->input->get('fstatus') === false ? '1' : $this->input->get('fstatus');
if ($fstatus != '' && $fstatus != 'todos') {
$sql .= ($sql !== '' ? ' AND ' : '') . " t.status = '" . $fstatus . "'";
}
$query = BDUtil::pagination("\n SELECT t.id, t.folio, t.fecha, t.total, t.status, u.usuario\n FROM tickets AS t\n INNER JOIN usuarios AS u ON u.id = t.usuario_id\n " . ($sql !== '' ? 'WHERE ' . $sql : '') . "\n ORDER BY t.fecha DESC\n ", $params, true);
$res = $this->db->query($query['query']);
$response = array('tickets' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['tickets'] = $res->result();
}
return $response;
}
/**
* Obtiene los vuelos de un cliente
*
* @param id_cliente
*/
public function getVuelosPiloto($id_piloto = '')
{
$sql = $id_piloto != '' ? "WHERE id_piloto = '{$id_piloto}'" : "";
//paginacion
$params = array('result_items_per_page' => '15', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('ffecha_ini') != '') {
$sql .= $this->input->get('ffecha_fin') != '' ? " AND DATE(fecha)>='" . $this->input->get('ffecha_ini') . "'" : " AND DATE(fecha)='" . $this->input->get('ffecha_ini') . "'";
}
if ($this->input->get('ffecha_fin') != '') {
$sql .= $this->input->get('ffecha_ini') != '' ? " AND DATE(fecha)<='" . $this->input->get('ffecha_fin') . "'" : " AND DATE(fecha)='" . $this->input->get('ffecha_fin') . "'";
}
if ($sql == '') {
$sql = " AND DATE(v.fecha)=DATE(now())";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT id_vuelo, clientes, piloto, matricula, fecha, id_piloto, id_avion, total_vuelos\n\t\t\t\tFROM get_vuelos_piloto_pendientes {$sql}\n\t\t\t\tORDER BY (fecha,piloto,clientes) DESC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('vuelos' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['vuelos'] = $res->result();
return $response;
}
/**
* Obtiene el listado de proveedores
*/
public function getPilotos()
{
$sql = '';
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql = " AND lower(nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
if ($this->input->get('fstatus') != 'todos') {
$_GET['fstatus'] = $this->input->get('fstatus') == '' ? 'ac' : $this->input->get('fstatus');
$sql .= " AND lower(status) LIKE '" . mb_strtolower($this->input->get('fstatus'), 'UTF-8') . "'";
}
$query = BDUtil::pagination("\n\t\t\t\tSELECT id_proveedor, nombre, telefono, email, recepcion_facturas, dias_pago\n\t\t\t\tFROM proveedores\n\t\t\t\tWHERE tipo='pi'\n\t\t\t\t" . $sql . "\n\t\t\t\tORDER BY nombre ASC\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('pilotos' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['pilotos'] = $res->result();
}
return $response;
}
/**
* Obtiene el listado de productos, si el parametro $_GET[id] existe,
* el metodo regresa los productos que pertenecen a la familia,
* o el filtro por nombre con "fnombre"
* @param unknown_type $per_page
* @param unknown_type $compara_id
* @param unknown_type $sql
*/
public function getProductosFamilia($per_page = '30', $compara_id = true, $sql = '', $orderby = 'codigo ASC')
{
//paginacion
$params = array('result_items_per_page' => $per_page, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
//filtro de familia
if (isset($_GET['id'][0]) && $compara_id) {
$sql .= " AND id_familia = '" . $_GET['id'] . "'";
}
//Filtros para buscar
if ($this->input->get('fnombre') != '') {
$sql .= " AND lower(nombre) LIKE '%" . mb_strtolower($this->input->get('fnombre'), 'UTF-8') . "%'";
}
$query = BDUtil::pagination("\r\n\t\t\t\tSELECT id_producto, codigo, nombre\r\n\t\t\t\tFROM productos\r\n\t\t\t\tWHERE status = 'ac' " . $sql . "\r\n\t\t\t\tORDER BY " . $orderby . "\r\n\t\t\t\t", $params, true);
$res = $this->db->query($query['query']);
$response = array('productos' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$response['productos'] = $res->result();
}
return $response;
}
/**
* SERIES Y FOLIOS
* ***************************************************
* Obtiene el listado de series y folios para administrarlos
*/
public function getSeriesFolios($per_pag = '30')
{
//paginacion
$params = array('result_items_per_page' => $per_pag, 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
$sql = '';
// if($this->input->get('fserie')!='')
// $this->db->where('serie',$this->input->get('fserie'));
$query = BDUtil::pagination("SELECT fsf.id_serie_folio, fsf.id_empresa, fsf.serie, fsf.no_aprobacion, fsf.folio_inicio,\r\n\t\t\t\t\tfsf.folio_fin, fsf.imagen, fsf.leyenda, fsf.leyenda1, fsf.leyenda2, fsf.ano_aprobacion, e.nombre_fiscal AS empresa\r\n\t\t\t\tFROM facturas_series_folios AS fsf\r\n\t\t\t\t\tINNER JOIN empresas AS e ON e.id_empresa = fsf.id_empresa\r\n\t\t\t\tWHERE lower(serie) LIKE '" . mb_strtolower($this->input->get('fserie'), 'UTF-8') . "' " . $sql . "\r\n\t\t\t\tORDER BY fsf.serie", $params, true);
$res = $this->db->query($query['query']);
$data = array('series' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$data['series'] = $res->result();
}
return $data;
}
public function getSeriesFolios()
{
//paginacion
$params = array('result_items_per_page' => '30', 'result_page' => isset($_GET['pag']) ? $_GET['pag'] : 0);
if ($params['result_page'] % $params['result_items_per_page'] == 0) {
$params['result_page'] = $params['result_page'] / $params['result_items_per_page'];
}
// if($this->input->get('fserie')!='')
// $this->db->where('serie',$this->input->get('fserie'));
$this->db->like('lower(serie)', mb_strtolower($this->input->get('fserie'), 'UTF-8'));
$this->db->order_by('serie');
$this->db->get('facturacion_series_folios');
$sql = $this->db->last_query();
$query = BDUtil::pagination($sql, $params, true);
$res = $this->db->query($query['query']);
$data = array('series' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
if ($res->num_rows() > 0) {
$data['series'] = $res->result();
}
return $data;
}
