AP6-AA2-EV2-CONSTRUCCIÓN DE ELEMENTOS DE AP6-AA2-EV2-CONSTRUCCIÓN PROGRAMACIÓN PARA EL PROYECTO DE FORMACIÓN
POR FÉLIX MERIÑO MENDOZA FREDDY MÁRQUEZ
Servicio Nacional de Aprendizaje SENA Análisis y Desarrollo de Sistemas de Información 2018
Esquema Entidad Relación de la Base de datos
PROCEDIMIENTOS:
1. Procedimiento almacenado almacenado para la inserción de datos en la tabla clientes: clientes: CREATE PROCEDURE `ingreso_cliente` `ingreso_cliente`( (IN `nombre_cliente` VARCHAR VARCHAR( (255 255), ), IN `telefono_cliente` VA RCHAR( RCHAR (45 45), ), IN `correo_cliente` VARCHAR VARCHAR( (60 60), ), IN `direccion_cliente` VARCHAR VARCHAR( (255 255), ), IN IN` `No_expedi ente` VARCHAR VARCHAR( (255 255)) )) NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER INSERT INTO INTO clientes clientes (Nombr e_Cliente, Telefono_Cliente, Correo_Cliente, Direccion_Cliente, No_expediente) VALUE VALUE(nombre_c (nombre_c liente, telefono_cliente, correo_cliente, direccion_cliente, No_expediente);
call ingreso_cliente('Alberto 23','9099456490');
Marin','62354123','
[email protected]','av
cl
3
#
12-
2. Procedimiento almacenado almacenado para la consulta de datos de la tabla clientes: CREATE PROCEDURE `mostrar_clientes` `mostrar_clientes`() () NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER SELECT * FROM clientes; FROM clientes;
3.
Procedimiento almacenado para la inserción de datos en la tabla perfil: CREATE PROCEDURE `agregar_perfil` `agregar_perfil`( (IN `nombre_bufet` VARCHAR VARCHAR( (255 255), ), IN `telefono` VARCHAR VARCHAR( (60 60), ), IN `resolucion` VARCHAR VARCHAR( (255 255), ), IN `ciudad` VARCHAR VARCHAR( (100 100), ), IN `impuesto_iva` INT INT( (2),IN `nit` VAR CHAR( CHAR (30 30), ), IN `prefijo` VARCHAR VARCHAR( (255 255), ), IN `comentarios` TEXT TEXT) ) NOT DETERMINISTIC NO SQL SQL SECU RITY DEFINER RITY DEFINER INSERT INTO `perfil` `perfil` ( (`Nombre_bufet` `Nombre_bufet`, ,`Telefono` `Telefono`, , `Resolucion` `Resolucion`, , `Ciudad` `Ciudad`, , `Impues to_iva`, to_iva` , `Nit` `Nit`, , `Prefijo` `Prefijo`, , `Comentarios` `Comentarios`) ) VALUES (nombre_bufet, telefono, resolucion, ciudad, impuesto_iva, nit, prefijo,comentarios);
Call agregar_perfil('Meriño y asociados', '3107028929', '00000000004560', 'soledad', 19, '8006752256', 'ccc03', 'Cuenta corriente 111907795643');
4. Procedimiento almacenado almacenado para la consulta de datos de la tabla perfil: CREATE PROCEDURE `mostrar_perfil` `mostrar_perfil`() () NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER SELECT * FROM FROM perfil; perfil;
5. Procedimiento almacenado almacenado para la inserción inserción de datos en la tabla tabla abogados: CREATE PROCEDURE `ingreso_abogado` `ingreso_abogado`( (IN Nombre VARCHAR VARCHAR( (255 255), ), IN Apellido VARCHAR VARCHAR( (255 255), ), IN perfi l INT INT, , IN clave VARCHAR( VARCHAR (45 45) ),IN correo VARCHAR VARCHAR( (255 255)) )) NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER INSERT INTO INTO abogados abogados (Nombre_Usuario, Apel lido_Usuario, Perfil_Usuario, Usuario_Clave, Usuario_Correo) VALUE VALUE(Nombre, (Nombre, Apellido, per fil, clave, correo);
Call ingreso_abogado('Juan Manuel','Santos',1,'123456','
[email protected]');
6. Procedimiento almacenado almacenado para la consulta consulta de datos de la tabla abogados: CREATE PROCEDURE `mostrar_abogados` `mostrar_abogados`() () NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER SELECT * FROM abogados; FROM abogados;
7. Procedimiento almacenado almacenado para la inserción inserción de datos en la tabla tabla asesorías: CREATE PROCEDURE `ingreso_asesoria` `ingreso_asesoria`( (IN idCliente INT INT, , IN INT, INT , IN fecha DATE DATE, , IN condiciones VARCHAR VARCHAR( (255 255) ),IN honorarios FLOAT, LONGTEXT, IN referencia VARCHAR(50) )
IN
idAbogado observaciones
NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER INSERT INTO INTO asesorias asesorias (idCliente, idAbogado, Fe cha, Condiciones, Total_Honorari Total_Honorarios, os, observaciones, ref_asesoria) VALUE VALUE(idCliente, (idCliente, idAbogado, fecha, condiciones, honorarios, observaciones, referencia); Call ingreso_asesoria( ingreso_asesoria(1 1,1,'2018-10-06' '2018-10-06', ,'Resolución de Detección', Detección',14250000 14250000, ,'Los terminos del servicio son claros para el cliente', cliente' ,'CASE 012456' ); 012456' );
8. Procedimiento almacenado almacenado para la consulta consulta de datos de la tabla asesorías: CREATE PROCEDURE `mostrar_asesorias` () NOT DETERMINISTIC NO SQL SQL SECURITY SECURITY DEFINER DEFINER SEL ECT * FROM FROM asesorias; asesorias;
9. Procedimiento almacenado almacenado para la consulta que permita conocer conocer los datos del del cliente y de la asesoría recibida dado el código de la asesoría: CREATE PROCEDURE `asesoria_clientes` (IN `idAsesoria` INT INT) ) NOT DETERMINISTIC NO SQL SQL S ECURITY DEFINER ECURITY DEFINER SELECT SELECT c. c.`idCliente` `idCliente`, , c.`Nombre_Cliente` c.`Nombre_Cliente`, , c.`Telefono_Cliente` c.`Telefono_Cliente`,c. ,c.`Correo `Correo _Cliente`, _Cliente` , c.`Direccion_Cliente` c.`Direccion_Cliente` , c.`No_expediente` c.`No_expediente`, , a.`Fecha` a.`Fecha`, , a.`Condiciones` a.`Condiciones`, , a.`Tota a.`Tota l_Honorarios`, l_Honorarios` , a.`observaciones` a.`observaciones` FROM `clientes` `clientes`c c INNER JOIN JOIN asesorias asesorias a on ona a.idCliente .idCliente = = c.idCliente c .idCliente WHERE WHERE a a.idAsesoria .idAsesoria = = idAsesoria;
10. Procedimiento almacenado para la consulta que permita conocer los datos del abogado y el perfil del mismo dado su código: CREATE PROCEDURE `perfil_abogado` `perfil_abogado`( (IN `idAbogado` INT INT) ) NOT DETERMINISTIC NO SQL SQL SECUR ITY DEFINER ITY DEFINER SELECT a. a.`idUsuarios` `idUsuarios`, , a.`Nombre_Usuario` a.`Nombre_Usuario`, , a.`Apellido_Usuario` a.`Apellido_Usuario`,a. ,a.`Usuario_C `Usuario_C orreo`, orreo` , p.`Nombre_bufet` p.`Nombre_bufet`, , p.`Telefono` p.`Telefono`, , p.`Resolucion` p.`Resolucion`, , p.`Ciudad` p.`Ciudad` FROM `abogados` `abogados` a a INN ER JOIN JOIN perfil perfil p ON ON p p.idPerfil .idPerfil = = a.Perfil_Usuario a.Perfil_Usuario WHERE WHERE a a.idUsuarios .idUsuarios =idAbogado; =idAbogado;
FUNCIONES
1. Función que permite obtener obtener los datos concatenados concatenados del nombre del abogado según el código ingresado como parámetro DELIMITER $$ CREATE FUNCTION nombreabogado (id INT INT) ) RETURNS VARCHAR VARCHAR( (150 150) ) BEGIN DECLARE nombrecompleto VARCHAR( VARCHAR (150 150); ); SET nombrecompleto = (SELECT CONCAT( CONCAT(`Nombre_Usuario` `Nombre_Usuario`, ," ",`Apellido_Usuario` `Apellido_Usuario`) )FROM FROM abogados abogados WHERE `idUsuarios` `idUsuarios` = = id); RETURN RETURN nombrecompleto; nombrecompleto; END $$ DELIMITER $$
2. Función que permite obtener el valor total de todos los honorarios generados por concepto de asesorías: DELIMITER $$ CREATE FUNCTION total_honorarios () RETURNS FLOAT BEGIN DECLARE total FLOAT FLOAT; ; SET total = (SELECT SUM SUM( (`Total_Honorarios` `Total_Honorarios`) ) FROM FROM asesorias); asesorias); RETURN RETURN total; total; END $$ DELIMITER $$
3. Función que permite permite obtener el valor valor máximo de todos los honorarios cobrados por asesorías: DELIMITER $$ CREATE FUNCTION honorarios_max () RETURNS FLOAT BEGIN DECLARE total FLOAT FLOAT; ; SET tot al = (SELECT MAX MAX( (`Total_Honorarios` `Total_Honorarios`) ) FROM FROM asesorias); asesorias); RETURN RETURN total; total; END $$ DELIMITER $$
4. Función que permite obtener los días que han trascurrido hasta la fecha actual actual de una asesoría según su id: DELIMITER $$ CREATE FUNCTION dias_transcurridos (idIn INT INT) ) RETURNS INT BEGIN DECLARE dias INT INT; ; SET dias = (SELECT DATEDIFF(CURDATE(),fecha) AS "Dias Transcurridos" FROM FROM asesorias asesorias WHERE WHEREidAsesoria idAsesoria = idIn); RETURN RETURN dias; dias; END $$ DELIMITER $$
5. Función que permite permite obtener el valor valor promedio de los honorarios honorarios cobrados por asesorías: DELIMITER $$ CREATE FUNCTION honorarios_promedio () RETURNS FLOAT BEGIN DECLARE total FLOA T; SET total = (SELECT AVG AVG( (`Total_Honorarios` `Total_Honorarios`) ) FROM FROM asesorias); asesorias); RETURN RETURN total; total; END $$ DELIMITER $$
DESENCADENADORES – TRIGGERS 1. TRIGGER QUE REGISTRA EN LA TABLA HISTORIAL EL INGRESO DE UN NUEVO REGISTRO EN LA TABLA ABOGADOS DELIMITER $$ CREATE TRIGGER `abogados` `abogados`. .`accionesAbogados` AFTER INSERT ON `abogados` `abogados`. .`abogados` FOR EAC H ROW BEGIN INSERT historial( historial(`usuario` `usuario`, ,`accion` `accion`, ,`tabla` `tabla`, ,`idRegistro` `idRegistro`) )VALUES VALUES( ("root" "root", ,"INSERT ","abogados" "abogados",NEW ,NEW.idUsuarios .idUsuarios); ); END $$ DELIMITER $$
2. TRIGGER QUE REGISTRA EN LA TABLA HISTORIAL LA MODIFICACION DE UN REGISTRO EN LA TABLA ABOGADOS DELIMITER $$ CREATE TRIGGER `abogados` `abogados`. .`modificarAbogados` AFTER UPDATE ON `abogados` `abogados`. .`abogados` FOR EA CH ROW BEGIN INSERT historial( historial(`usuario` `usuario`, ,`accion` `accion`, ,`tabla` `tabla`, ,`idRegistro` `idRegistro`) )VALUES VALUES( ("root" "root", ,"UPDAT E", E" ,"abogados" "abogados",OLD ,OLD.idUsuarios .idUsuarios); ); END $$ DELIMITER $$
3. TRIGGER QUE REGISTRA EN LA TABLA HISTORIAL LA ELIMINACION DE UN REGISTRO EN LA TABLA ABOGADOS DELIMITER $$ CREATE TRIGGER `abogados` `abogados`. .`eliminarAbogado` AFTER DELETE ON `abogados` `abogados`. .`abogados` FOR EACH ROW BEGIN INSERT INTO historial( historial(`usuario` `usuario`, ,`accion` `accion`, ,`tabla` `tabla`, ,`idRegistro` `idRegistro`) ) VALUES( VALUES ("root" "root", ,"DELETE" "DELETE", ,"abogados" "abogados",OLD ,OLD.idUsuarios .idUsuarios); ); END $$ DELIMITER $$
4. TRIGGER QUE REGISTRA EN LA TABLA HISTORIAL EL INGRESO DE UN NUEVO REGISTRO EN LA TABLA CLIENTES DELIMITER $$ CREATE TRIGGER `abogados` `abogados`. .`ingresoCliente` AFTER INSERT ON `abogados` `abogados`. .`clientes` FOR EACH ROW BEGIN INSERT INTO historial( historial(`usuario` `usuario`, ,`accion` `accion`, ,`tabla` `tabla`, ,`idRegistro` `idRegistro`) ) VALUES( VALUES ("root" "root", ,"INSERT" "INSERT", ,"clientes" "clientes",NEW ,NEW.idCliente .idCliente); ); END $$ DELIMITER $$ INSERT INTO `clientes` `clientes`( (`Nombre_Cliente` `Nombre_Cliente`, , `Telefono_Cliente` `Telefono_Cliente`, , `Correo_Cliente` `Correo_Cliente`, , `Direccion_ Cliente`, Cliente` , `No_expediente` `No_expediente`) ) VALUES ("Pedro Luis Mendoza", Mendoza" ,"3011234567" "3011234567", ,"
[email protected]" ,"Barrio Arriba", Arriba","12488" "12488") )
11
5. TRIGGER QUE REGISTRA EN LA TABLA HISTORIAL LA ELIMINACION DE REGISTROS EN LA TABLA CLIENTES DELIMITER $$ CREATE TRIGGER `abogados` `abogados`. .`eliminarCliente` AFTER DELETE ON `abogados` `abogados`. .`clientes` FOR EACH ROW BEGIN INSERT INTO INTO historial( historial(`usuario` `usuario`, ,`accion` `accion`, ,`tabla` `tabla`, ,`idRegistro` `idRegistro`) ) VALUES( VALUES ("root" "root", ,"DELETE" "DELETE", ,"clientes" "clientes",OLD ,OLD.idCliente .idCliente); ); END $$ DELIMITER $$ DELETE FROM `clientes` WHERE `idCliente` = 5;
12
