<?php
class GUsuario{    

  public function lista(){
    return "SELECT u.id_usuario as id, u.id_rol_sistema as idRolSistema, r.no_rol_sistema as noRolSistema, u.id_empresa as idEmpresa, e.no_empresa as noEmpresa, u.id_local as idLocal,
    l.no_local as noLocal, u.id_empleado as idEmpleado,CONCAT(emp.no_empleado, ' ', emp.no_apepat, ' ', emp.no_apemat) as noEmpleado, u.login as usuario, u.password as clave, u.fe_ingreso as feIngreso, u.fe_baje as feBaja, u.il_visible as ilVisible,
    u.STATUS as estado
    FROM tb_usuario u
    LEFT JOIN rol_sistema r on r.id_rol_sistema=u.id_rol_sistema
    LEFT JOIN tb_empresa e on e.id_empresa=u.id_empresa
    LEFT JOIN tb_local l on l.id_local=u.id_local
    LEFT JOIN tb_empleado emp on emp.id_empleado=u.id_empleado
    WHERE u.status = '1'";
  }

  public function listaRolDet(){
    return "SELECT s.id_rol_sistema AS id, s.no_rol_sistema AS noRolSistema, s.id_empresa AS idEmpresa, e.no_empresa AS noEmpresa,
    s.id_local AS idLocal, l.no_local AS noLocal
    FROM rol_sistema s
    LEFT JOIN tb_empresa e ON e.id_empresa=s.id_empresa
    LEFT JOIN tb_local l ON l.id_local=s.id_local
    WHERE s.status='1'";
  }

  public function guardar(){
    return "INSERT INTO tb_usuario(id_rol_sistema,id_empresa,id_local,id_empleado,login,password,fe_ingreso,fe_baje,id_usuario) VALUES (?,?,?,?,?,?,?,?,?)";
  }

  public function modificar(){
    return "UPDATE tb_usuario SET id_rol_sistema = ?, id_empresa = ?, id_local = ?, id_empleado = ?, login = ?, password = ?, fe_ingreso = ?, fe_baje = ? WHERE id_usuario = ?";
  }

  public function guardarRol(){
    return "INSERT INTO rol_sistema(no_rol_sistema,id_empresa,id_local,id_rol_sistema) VALUES (?,?,?,?)";
  }

  public function modificarRol(){
    return "UPDATE rol_sistema SET no_rol_sistema = ?, id_empresa = ?, id_local = ? WHERE id_rol_sistema = ?";
  }

  public function listaRol(){
    return "SELECT id_rol_sistema AS idRolSistema, no_rol_sistema AS noRolSistema FROM rol_sistema WHERE status = '1'";
  }

  public function listaEmpresa(){
    return "SELECT id_empresa AS idEmpresa, no_empresa AS noEmpresa FROM tb_empresa WHERE status = '1'";
  }

  public function listaLocal(){
    return "SELECT id_local AS idLocal, no_local AS noLocal FROM tb_local WHERE status = '1'";
  }

  public function listaEmpleado(){
    return "SELECT id_empleado AS idEmpleado, CONCAT(no_empleado, ' ', no_apepat, ' ', no_apemat) as noEmpleado FROM tb_empleado";
  }

  public function eliminar(){
    return "UPDATE tb_usuario SET status = '0' WHERE id_usuario = ?";
  }

  public function listaOpcionesRol(){
    return "SELECT
            ws.id_win_sistemas AS idWinSistemas,
            ws.no_win_sistemas AS noWinSistemas,
            ws.no_parent AS padre,
            (select '1' from detalle_rol dr WHERE dr.id_win_sistemas= ws.id_win_sistemas and dr.id_rol_sistema = ?) AS estado
            FROM win_sistemas ws";
  }

  public function eliminarRol(){
    return "UPDATE rol_sistema SET status = '0' WHERE id_rol_sistema = ?";
  }

  public function insertaDetalleRol(){
    return "INSERT INTO detalle_rol (id_rol_sistema,id_win_sistemas) VALUES (?,?)";
  }

  public function eliminaDetalleRol(){
    return "DELETE FROM detalle_rol WHERE id_rol_sistema = ? AND id_win_sistemas = ?";
  }

  public function existeDetalleRol(){
    return "SELECT COUNT(1) FROM detalle_rol WHERE id_rol_sistema = ? AND id_win_sistemas=?";
  }

}
?>