/**
* 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 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;
}
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 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 addAvion()
{
if ($this->db->select('id_avion')->from('aviones')->where(array('matricula' => $this->input->post('fmatricula'), 'status' => 'ac'))->get()->num_rows() < 1) {
$id_avion = BDUtil::getId();
$data = array('id_avion' => $id_avion, 'matricula' => $this->input->post('fmatricula'), 'modelo' => $this->input->post('fmodelo'), 'tipo' => $this->input->post('ftipo'), 'fecha_vence_tarjeta' => $this->input->post('dfecha_vence_tarjeta'), 'fecha_vence_seguro' => $this->input->post('dfecha_vence_seguro'));
$this->db->insert('aviones', $data);
return array(true);
}
return array(false);
}
public function addVehiculo()
{
if ($this->db->select('id_vehiculo')->from('vehiculos')->where(array('nombre' => $this->input->post('fnombre'), 'placas' => $this->input->post('fplacas')))->get()->num_rows() < 1) {
$id_vehiculo = BDUtil::getId();
$data = array('id_vehiculo' => $id_vehiculo, 'nombre' => $this->input->post('fnombre'), 'placas' => $this->input->post('fplacas'), 'modelo' => $this->input->post('fmodelo'), 'numero_serie' => $this->input->post('fnumserie'), 'color' => strtolower($this->input->post('fcolor')));
$this->db->insert('vehiculos', $data);
return array(true);
}
return array(false);
}
/**
* Agrega la informacion de una sucursal de una empresa, o la info de una empresa
* sin sucursales
* @param unknown_type $sucu
*/
public function addEmpresa($sucu = false)
{
$path_img = '';
//valida la imagen
$upload_res = UploadFiles::uploadEmpresaLogo();
if (is_array($upload_res)) {
if ($upload_res[0] == false) {
return array(false, $upload_res[1]);
}
$path_img = APPPATH . 'images/empresas/' . $upload_res[1]['file_name'];
}
$id_empresa = BDUtil::getId();
$data = array('id_empresa' => $id_empresa, 'nombre_fiscal' => $this->input->post('dnombre_fiscal'), 'calle' => $this->input->post('dcalle'), 'no_exterior' => $this->input->post('dno_exterior'), 'no_interior' => $this->input->post('dno_interior'), 'colonia' => $this->input->post('dcolonia'), 'localidad' => $this->input->post('dlocalidad'), 'municipio' => $this->input->post('dmunicipio'), 'estado' => $this->input->post('destado'), 'cp' => $this->input->post('dcp'), 'rfc' => $this->input->post('drfc'), 'telefono' => $this->input->post('dtelefono'), 'celular' => $this->input->post('dcelular'), 'email' => $this->input->post('demail'), 'pag_web' => $this->input->post('dpag_web'), 'logo' => $path_img, 'regimen_fiscal' => $this->input->post('dregimen_fiscal'));
$this->db->insert('empresas', $data);
return array(true, '', 3);
}
/**
* Nivelar inventario
*/
public function nivelar()
{
$compras = array();
$ordenest = array();
$total_compra = 0;
$total_ordent = 0;
foreach ($_POST['id_producto'] as $key => $prod) {
if ($_POST['diferie'][$key] != '' && $_POST['diferie'][$key] != 0) {
$cantidad = abs($_POST['diferie'][$key]);
if ($_POST['diferie'][$key] > 0) {
//se agrega una compra
$compras[] = array('id_compra' => '', 'id_producto' => $prod, 'cantidad' => $cantidad, 'precio_unitario' => $_POST['precio_u'][$key], 'importe' => $cantidad * $_POST['precio_u'][$key], 'total' => $cantidad * $_POST['precio_u'][$key]);
$total_compra += $cantidad * $_POST['precio_u'][$key];
} else {
//se agrega una venta
$ordenest[] = array('id_salida' => '', 'id_producto' => $prod, 'cantidad' => $cantidad, 'precio_unitario' => $_POST['precio_u'][$key], 'importe' => $cantidad * $_POST['precio_u'][$key], 'total' => $cantidad * $_POST['precio_u'][$key]);
$total_ordent += $cantidad * $_POST['precio_u'][$key];
}
}
}
//Agregamos una compra para nivelar
if (count($compras) > 0) {
$id_compra = BDUtil::getId();
$compra = array('id_compra' => $id_compra, 'id_proveedor' => '1', 'id_empleado' => $_SESSION['id_empleado'], 'subtotal' => $total_compra, 'total' => $total_compra, 'status' => 'n');
$this->db->insert('compras', $compra);
//productos de la compra
foreach ($compras as $key => $co) {
$compras[$key]['id_compra'] = $id_compra;
}
$this->db->insert_batch('compras_productos', $compras);
//compras_productos_inv
}
//Agregamos una orden t para nivelar
if (count($ordenest) > 0) {
$id_ordent = BDUtil::getId();
$ordent = array('id_salida' => $id_ordent, 'id_usuario' => $_SESSION['id_empleado'], 'folio' => 0, 'status' => 'n');
$this->db->insert('salidas', $ordent);
//productos de la orden t
foreach ($ordenest as $key => $co) {
$ordenest[$key]['id_salida'] = $id_ordent;
}
$this->db->insert_batch('salidas_productos', $ordenest);
}
}
/**
* 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 proveedores
*/
public function getProveedores()
{
$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, nombre_fiscal, rfc, telefono1, email, \n\t\t\t\t\tCONCAT(calle, ' #', no_exterior, ', ', colonia, ', ', municipio, ', ', estado) AS direccion, status\n\t\t\t\tFROM proveedores\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('proveedores' => array(), 'total_rows' => $query['total_rows'], 'items_per_page' => $params['result_items_per_page'], 'result_page' => $params['result_page']);
$response['proveedores'] = $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;
}
public function abonar_piloto($id_piloto = null, $abono = null, $concepto = null)
{
$id_piloto = $id_piloto == null ? $this->input->get('id') : $id_factura;
$abono = $abono == null ? floatval($this->input->get('fabono')) : floatval($abono);
$concepto = $concepto == null ? $this->input->post('fconcepto') : $concepto;
$info_abonos = $this->get_info_abonos();
if ($abono <= $info_abonos->restante) {
$id_abono = BDUtil::getId();
$data = array('id_abono' => $id_abono, 'id_proveedor' => $id_piloto, 'fecha' => $this->input->post('ffecha') . ' ' . date('H:i:s'), 'concepto' => $this->input->post('fconcepto'), 'total' => $this->input->post('fabono'));
$this->db->insert("proveedores_abonos", $data);
return array(TRUE);
} else {
return array(FALSE, 'msg' => 'El Abono que ingreso no puede ser mayor al Saldo');
}
}
/**
* Agrega contactos al proveedor
* @param unknown_type $id_proveedor
*/
public function addContacto($id_proveedor = null)
{
$id_proveedor = $id_proveedor == null ? $this->input->post('id') : $id_proveedor;
$id_conta = BDUtil::getId();
$data = array('id_contacto' => $id_conta, 'id_proveedor' => $id_proveedor, 'nombre' => $this->input->post('dcnombre'), 'domicilio' => $this->input->post('dcdomicilio'), 'municipio' => $this->input->post('dcmunicipio'), 'estado' => $this->input->post('dcestado'), 'telefono' => $this->input->post('dctelefono'), 'celular' => $this->input->post('dccelular'));
$this->db->insert('proveedores_contactos_piloto', $data);
return array(true, 'Se agregó el contacto correctamente.', $id_conta);
}
/**
* Agrega los productos a una compra
* @param unknown_type $id_compra
* @param unknown_type $tipo
*/
public function addProductos($id_salida, $tipo = 'add')
{
if (is_array($_POST['dpid_producto'])) {
$tr_nombre = '';
if ($_POST['dtipo_salida'] == 'tr') {
if ($_POST['dtipo_trabajador'] == 'tr') {
$tr_nombre = $this->db->select("(nombre || ' ' || apellido_paterno) as nombre")->from("empleados")->where("id_empleado", $_POST['did_trabajador'])->get()->row()->nombre;
} elseif ($_POST['dtipo_trabajador'] == 'pi') {
$tr_nombre = $this->db->select("nombre")->from("proveedores")->where("id_proveedor", $_POST['did_trabajador'])->get()->row()->nombre;
}
}
$data_productos = array();
$data_alertas = array();
foreach ($_POST['dpid_producto'] as $key => $producto) {
//Datos de los productos de la salida
$data_productos[] = array('id_salida' => $id_salida, 'id_producto' => $producto, 'taza_iva' => $_POST['dptaza_iva'][$key], 'cantidad' => $_POST['dpcantidad'][$key], 'precio_unitario' => $_POST['dpprecio_unitario'][$key], 'importe' => $_POST['dpimporte'][$key], 'importe_iva' => $_POST['dpimporte_iva'][$key], 'total' => $_POST['dpimporte'][$key] + $_POST['dpimporte_iva'][$key]);
if ($_POST['dtipo_salida'] == 'tr') {
$pr_nombre = $this->db->select("nombre")->from("productos")->where("id_producto", $producto)->get()->row()->nombre;
$id_alerta = BDUtil::getId();
$data_alertas[] = array('id_alerta' => $id_alerta, 'tabla_obj' => 'salidas_productos', 'id_obj1' => $id_salida, 'id_obj2' => $producto, 'descripcion' => "Vencimiento de Prestamo Herramienta - ({$_POST['dpcantidad'][$key]}) {$pr_nombre} a {$tr_nombre}", 'fecha_vencimiento' => $this->input->post('dfecha_entrega'));
}
}
if (count($data_productos) > 0) {
$this->db->insert_batch('salidas_productos', $data_productos);
if (count($data_alertas) > 0) {
$this->db->insert_batch('alertas', $data_alertas);
}
}
return array(true, '');
}
return array(false, '');
}
public function addGastoPiloto()
{
$id_gasto = BDUtil::getId();
$data = array('id_compra' => $id_gasto, 'id_proveedor' => $this->input->post('tpiloto'), 'id_empleado' => $_SESSION['id_empleado'], 'serie' => mb_strtoupper($this->input->post('tserie'), 'utf-8'), 'folio' => $this->input->post('tfolio'), 'fecha' => $this->input->post('tfecha'), 'subtotal' => $this->input->post('subtotal'), 'importe_iva' => $this->input->post('iva'), 'total' => $this->input->post('total'), 'concepto' => $this->input->post('tconcepto'), 'is_gasto' => 't', 'status' => 'pa');
$this->db->insert('compras', $data);
foreach ($_POST as $vuelo) {
if (is_array($vuelo)) {
$data_v = array('id_compra' => $id_gasto, 'id_vuelo' => $vuelo['id_vuelo'], 'cantidad' => String::float($vuelo['cantidad']), 'taza_iva' => String::float($vuelo['taza_iva']), 'precio_unitario' => String::float($vuelo['precio_unitario']), 'importe' => String::float($vuelo['importe']), 'importe_iva' => String::float($vuelo['importe_iva']), 'total' => String::float($vuelo['total']));
$this->db->insert('compras_gastos_vuelos', $data_v);
}
}
return array(true);
}
public function abonar_ticket($liquidar = false, $id_ticket = null, $abono = null, $concepto = null)
{
$id_ticket = $id_ticket == null ? $this->input->get('id') : $id_ticket;
$concepto = $concepto == null ? $this->input->post('fconcepto') : $concepto;
$ticket_info = $this->get_info_abonos($id_ticket);
if ($ticket_info->status == 'p') {
$pagado = false;
if ($liquidar) {
if ($ticket_info->abonado <= $ticket_info->total) {
$total = $ticket_info->restante;
} elseif ($ticket_info->restante == $ticket_info->total) {
$total = $ticket_info->total;
}
$pagado = true;
} else {
if (!is_null($abono)) {
$total = $abono > $ticket_info->restante ? $ticket_info->restante : $abono;
if (floatval($total + $ticket_info->abonado) >= floatval($ticket_info->total)) {
$pagado = true;
}
}
}
$id_abono = BDUtil::getId();
$data = array('id_abono' => $id_abono, 'id_ticket' => $id_ticket, 'fecha' => $this->input->post('ffecha') != '' ? $this->input->post('ffecha') : date("Y-m-d"), 'concepto' => $concepto, 'total' => floatval($total));
$this->db->insert('tickets_abonos', $data);
if ($pagado) {
$this->db->update('tickets', array('status' => 'pa'), array('id_ticket' => $id_ticket));
}
return array(true);
} else {
return array(false, 'msg' => 'No puede realizar mas abonos porque el ticket ya esta totalmente pagado');
}
}
/**
* Agrega una lista de precios
*/
public function addLista()
{
$data = array('id_lista' => BDUtil::getId(), 'nombre' => $this->input->post('dnombre'), 'es_default' => $this->input->post('des_default') === false ? 'f' : 't');
$this->db->insert('productos_listas', $data);
return array(true, '');
}
/**
* Agrega un producto a la bd y los productos q consume
* @return multitype:boolean string
*/
public function addProducto()
{
/*//reajusta a 4 digitos
for($i=strlen($_POST['dcodigo']); $i<4; ++$i)
$_POST['dcodigo'] = '0'.$_POST['dcodigo'];*/
$_POST['dcodigo'] = $this->input->post('codigo_familia') . '-' . $_POST['dcodigo'];
$tipo = is_array($this->input->post('dpcids')) ? 'consumo' : 'base';
$id_producto = BDUtil::getId();
$data = array('id_producto' => $id_producto, 'id_familia' => $this->input->get_post('familia'), 'id_unidad' => $this->input->post('dunidad'), 'codigo' => $this->input->post('dcodigo'), 'nombre' => $this->input->post('dnombre'), 'descripcion' => $this->input->post('ddescripcion'), 'ubicacion' => $this->input->post('dubicacion'), 'stock_min' => $this->input->post('dstock_min') != '' ? $this->input->post('dstock_min') : 0, 'stock_max' => $this->input->post('dstock_max') != '' ? $this->input->post('dstock_max') : 0, 'tipo' => $tipo);
$this->db->insert('productos', $data);
//Productos consumo
// if($tipo == 'consumo'){
// $data_pro = array();
// foreach($this->input->post('dpcids') as $key => $id){
// $data_pro[] = array(
// 'id_producto' => $id_producto,
// 'id_producto_c' => $id,
// 'cantidad' => ($_POST['dpccantidad'][$key]!=''? $_POST['dpccantidad'][$key]: 0)
// );
// }
// if(count($data_pro) > 0)
// $this->db->insert_batch('productos_consumos', $data_pro);
// }
return array(true, '');
}
/**
* Agrega la informacion de un vuelo
* @param unknown_type $sucu
*/
public function addVuelo()
{
$id_vuelo = BDUtil::getId();
$data = array('id_vuelo' => $id_vuelo, 'id_piloto' => $this->input->post('hpiloto'), 'id_avion' => $this->input->post('havion'), 'fecha' => $this->input->post('dfecha') . ':' . date('s'), 'id_producto' => $this->input->post('dproducto'), 'costo_piloto' => $this->input->post('hcosto_piloto'));
$expide_factura = $this->db->select('expide_factura')->from('proveedores')->where('id_proveedor', $this->input->post('hpiloto'))->get()->row()->expide_factura;
if ($expide_factura == 't') {
$data['iva_piloto'] = floatval($data['costo_piloto']) * 0.16;
}
$this->db->insert('vuelos', $data);
$data = array();
foreach ($_POST['hids'] as $cid) {
$data[] = array('id_vuelo' => $id_vuelo, 'id_cliente' => $cid);
}
$this->db->insert_batch('vuelos_clientes', $data);
$msg = 4;
return array(true, '', $msg);
}
/**
* Agrega una serie/folio a la base de datos
*/
public function addSerieFolio()
{
$path_img = '';
//valida la imagen
$upload_res = UploadFiles::uploadImgSerieFolio();
if (is_array($upload_res)) {
if ($upload_res[0] == false) {
return array(false, $upload_res[1]);
}
$path_img = $upload_res[1]['file_name'];
//APPPATH.'images/series_folios/'.$upload_res[1]['file_name'];
}
$id_serie_folio = BDUtil::getId();
$data = array('id_empresa' => $this->input->post('fid_empresa'), 'serie' => strtoupper($this->input->post('fserie')), 'no_aprobacion' => $this->input->post('fno_aprobacion'), 'folio_inicio' => $this->input->post('ffolio_inicio'), 'folio_fin' => $this->input->post('ffolio_fin'), 'ano_aprobacion' => $this->input->post('fano_aprobacion'), 'imagen' => $path_img);
if ($this->input->post('fleyenda') != '') {
$data['leyenda'] = $this->input->post('fleyenda');
}
if ($this->input->post('fleyenda1') != '') {
$data['leyenda1'] = $this->input->post('fleyenda1');
}
if ($this->input->post('fleyenda2') != '') {
$data['leyenda2'] = $this->input->post('fleyenda2');
}
$this->db->insert('facturas_series_folios', $data);
return array(true);
}
public function abonar_nota_venta($liquidar = false, $id_nota_venta = null, $abono = null, $concepto = null)
{
$id_nota_venta = $id_nota_venta == null ? $this->input->get('id') : $id_nota_venta;
$concepto = $concepto == null ? $this->input->post('fconcepto') : $concepto;
$nota_venta_info = $this->get_info_abonos($id_nota_venta);
if ($nota_venta_info->status == 'p') {
$pagado = false;
$total = false;
if ($liquidar) {
if ($nota_venta_info->abonado <= $nota_venta_info->total) {
$total = $nota_venta_info->restante;
} elseif ($nota_venta_info->restante == $nota_venta_info->total) {
$total = $nota_venta_info->total;
}
$pagado = true;
} else {
if (!is_null($abono)) {
$total = $abono;
if (floatval($abono + $nota_venta_info->abonado) >= floatval($nota_venta_info->total)) {
$pagado = true;
}
} else {
$total_abonado_tickets = $this->db->select("SUM(ta.total) as total_abonado_tickets")->from("tickets_abonos AS ta")->join("tickets_notas_venta_tickets AS tnv", "ta.id_ticket=tnv.id_ticket", "inner")->where("tnv.id_nota_venta", $id_nota_venta)->get()->row()->total_abonado_tickets;
if (floatval($total_abonado_tickets) > 0) {
$concepto = 'Pagos y abonos de los tickets agregados a la Nota de Venta';
$total = $total_abonado_tickets;
if (floatval($total_abonado_tickets) >= $nota_venta_info->total) {
$pagado = true;
}
}
}
}
if ($total != false) {
$id_abono = BDUtil::getId();
$data = array('id_abono' => $id_abono, 'id_nota_venta' => $id_nota_venta, 'fecha' => $this->input->post('ffecha') != '' ? $this->input->post('ffecha') : date("Y-m-d"), 'concepto' => $concepto, 'total' => floatval($total));
$this->db->insert('tickets_notas_venta_abonos', $data);
if ($pagado) {
$this->db->update('tickets_notas_venta', array('status' => 'pa'), array('id_nota_venta' => $id_nota_venta));
}
return array(true);
}
return array(false, 'msg' => 'No puede realizar la operación');
} else {
return array(false, 'msg' => 'No puede realizar mas abonos porque la nota de venta ya esta totalmente pagada');
}
}
/**
* Agrega contactos al cliente
* @param unknown_type $id_sucursal
*/
public function addContacto($id_cliente = null)
{
$id_cliente = $id_cliente == null ? $this->input->post('id') : $id_cliente;
$id_conta = BDUtil::getId();
$data = array('id_contacto' => $id_conta, 'id_cliente' => $id_cliente, 'nombre' => $this->input->post('dcnombre'), 'puesto' => $this->input->post('dcpuesto'), 'telefono' => $this->input->post('dctelefono'), 'extension' => $this->input->post('dcextension'), 'celular' => $this->input->post('dccelular'), 'nextel' => $this->input->post('dcnextel'), 'nextel_id' => $this->input->post('dcnextel_id'), 'fax' => $this->input->post('dcfax'));
$this->db->insert('clientes_contacto', $data);
return array(true, 'Se agregó el contacto correctamente.', $id_conta);
}
/**
* Agrega un privilegio a la bd
*/
public function addPrivilegio()
{
$data = array('id_privilegio' => BDUtil::getId(), 'nombre' => $this->input->post('dnombre'), 'id_padre' => $this->input->post('dprivilegios') != '' ? $this->input->post('dprivilegios') : '0', 'mostrar_menu' => $this->input->post('dmostrar_menu') == 'si' ? 't' : 'f', 'url_accion' => $this->input->post('durl_accion'), 'url_icono' => $this->input->post('durl_icono'), 'target_blank' => $this->input->post('dtarget_blank') == 'si' ? 't' : 'f');
$this->db->insert('privilegios', $data);
return array(true, '');
}
/**
* Agrega un producto a la bd y los productos q consume
* @return multitype:boolean string
*/
public function addProducto()
{
/*//reajusta a 4 digitos
for($i=strlen($_POST['dcodigo']); $i<4; ++$i)
$_POST['dcodigo'] = '0'.$_POST['dcodigo'];*/
$_POST['dcodigo'] = $this->input->post('codigo_familia') . '-' . $_POST['dcodigo'];
$id_producto = BDUtil::getId();
$data = array('id_producto' => $id_producto, 'id_familia' => $this->input->get_post('familia'), 'id_unidad' => $this->input->post('dunidad'), 'codigo' => $this->input->post('dcodigo'), 'nombre' => $this->input->post('dnombre'));
$this->db->insert('productos', $data);
return array(true, '');
}
