Conexion.---

class Conexion{

public function conectar(){
    $bd="evaluacion";
$host="mysql:host=localhost;dbname=".$bd;
$usuario="root";
$password="";
$link = new PDO($host,$usuario,$password, array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
return $link;
}

}

-----------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------


  public function nombrefuncion($param){
  $mensaje="";
try {
$conexion=Conexion::conectar();
    $sql="INSERT INTO tabla(campo)
    VALUES (:param);";

    $stmt = $conexion->prepare($sql);
$stmt->bindParam(":param", $param);
$stmt->execute();
        $fila=$stmt->rowCount();     
          $mensaje="Guardo, Proyecto";       
       
} catch(PDOException $e) {

    if ($e->errorInfo[1] == 1062) {
$mensaje="registro duplicado";
      // duplicate entry, do something else
   } else {
      // an error other than duplicate entry occurred
    echo $e->errorInfo[1];
   }
 
}
    return $mensaje;
}
---------------------------------------------------------------

public function listaProyecto(){
       
$stmt = Conexion::conectar()->prepare("query");
$stmt->execute();     
#fetchAll(): Obtiene todas las filas de un conjunto de resultados asociado al objeto PDOStatement.
$array=$stmt->fetchAll(PDO::FETCH_ASSOC);
// cerrar conexion;
$stmt=null;
return $array;       
}

---------------------------------------------------------------------------------------------------------



header('Access-Control-Allow-Origin: *');
header("Access-Control-Allow-Headers: X-API-KEY, ,Authorization,Origin, X-Requested-With, Content-Type, Accept, Access-Control-Request-Method");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS, PUT, DELETE");
header("Allow: GET, POST, OPTIONS, PUT, DELETE");
header('Content-Type: application/json;charset=utf-8');
http_response_code('202');

Plantilla Clase Almacenamiento navegador

selector buttonnavigationview

<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
    <item  android:color="@color/boton_seleccion" android:state_checked="true" />
    <item android:color="@color/boton_normal_default" />
</selector>

Notificación Android

public class MainActivity extends AppCompatActivity {
    private static final String CHANNEL_ID = "notificacion";

    @Override    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

       // Intent intent = new Intent(this, AlertActivity.class);  
      //intent para ir a un sitio web//    
    // Intent intent = new Intent(Intent.ACTION_VIEW);
    // intent.setData(Uri.parse("http://usbcartagena.edu.co/"));    
   // intent.setFlags(Intent.FLAG_ACTIVITY_NEW_TASK | Intent.FLAG_ACTIVITY_CLEAR_TASK);     
   //PendingIntent pendingIntent = PendingIntent.getActivity(this, 0,intent, 0);


        NotificationCompat.Builder builder = new NotificationCompat.Builder(this, CHANNEL_ID)
                .setSmallIcon(R.drawable.ic_announcement_black_24dp)
                .setContentTitle("Notificación Sanbuenaventura")
                .setContentText("Clases Virtual por Covid 19")
                .setPriority(NotificationCompat.PRIORITY_DEFAULT)
               
 //           .setContentIntent(pendingIntent)               
               .setAutoCancel(true);

        NotificationManagerCompat notificationManager = NotificationManagerCompat.from(this);

       
  int notificationId=100;
        notificationManager.notify(notificationId, builder.build());

    }

LogoUsb


ARCHIVO PAGINA WEB RESPONSIVO PRACTICA  WEBVIEW

pagina web responsiva

Imagenes Android

Aplicación Javascript responsivo

Proyecto Integrador web

Imagenes Demo

Formulario Demo

Archivo demo

Clase 3 Funciones

Script Ejemplo de Funciones

--Inner Join

select * from dbo.cargo
inner join dbo.empleado
on cargo.codcargo=empleado.codcargo;

--Vista de Join

create view dbo.vistaemp as
select departamento.coddep,nomdep,cargo.codcargo,
nomcargo,salario,codemp,
primnom||' '||primapel as primapel,edad
 from dbo.departamento
inner join (dbo.cargo inner join dbo.empleado
on cargo.codcargo=empleado.codcargo)
on departamento.coddep=cargo.coddep;

select * from dbo.vistaemp;

update dbo.empleado
set codcargo='101-2',
segnom='jose'
where codemp='73121295';

---update

update dbo.departamento
set numcargo=numcargo+1
where coddep='101';

--Rutina Funcion Ejercicio 1

create or replace function
dbo.sumavoid(n integer,m integer)
returns void as $$
declare suma integer;
begin
suma:= n+m;
RAISE NOTICE 'suma=%',suma;
end
$$ language plpgsql;

select dbo.sumavoid(4,5);

Rutina Ejercicio 1 V2

create or replace function
dbo.sumavoidv2(n integer,m integer)
returns void as $$
declare suma integer;
begin
suma:= n+m;
RAISE NOTICE '%+%=%',n,m,suma;
end
$$ language plpgsql;
select dbo.sumavoidv2(4,5);


Rutina Ejericicio 2

create or replace
function dbo.areacirculo(radio double precision)
returns void as $$
declare
area double precision;
PI double precision;
begin
PI=3.1416;
area:= PI*(radio ^ 2);
RAISE NOTICE 'area circulo=%',area;
end
$$ language plpgsql;
select dbo.areacirculo(34.65);

rutina iva

CREATE OR REPLACE FUNCTION dbo.calculoivav1(compra double precision)
  RETURNS void AS $$
declare
iva double precision;
comprasal character(30);
ivasal character(30);
horasal character(30);
fecha date;
hora time;
begin
iva:=compra*0.19;
fecha=now();
hora=now();
comprasal:=to_char(compra,'LFM9,999,999,999,999.00');
ivasal:=to_char(iva,'LFM9,999,999,999,999.00');
horasal=to_char(now()::Time, 'HH12:MI:SS AM');
RAISE NOTICE 'ecompra %',comprasal;
RAISE NOTICE 'iva % ',ivasal;
RAISE NOTICE 'fecha %',fecha;
RAISE NOTICE 'hora %',horasal;
END;
$$ language plpgsql;


select dbo.calculoivav1(45000000);


funcion iva v2

CREATE OR REPLACE FUNCTION dbo.calculoivav2(compra double precision)
  RETURNS void AS $$
declare
iva double precision;
comprasal character(30);
ivasal character(30);
horasal character(30);
total double precision;
totalsal character(30);
fecha date;
hora time;
begin
iva:=compra*0.19;
total:=compra+iva;
fecha=now();
hora=now();
comprasal:=to_char(compra,'LFM9,999,999,999,999.00');
ivasal:=to_char(iva,'LFM9,999,999,999,999.00');
totalsal:=to_char(total,'LFM9,999,999,999,999.00');
horasal=to_char(now()::Time, 'HH12:MI:SS AM');
RAISE NOTICE 'ecompra %',comprasal;
RAISE NOTICE 'iva % ',ivasal;
RAISE NOTICE '--------------';
RAISE NOTICE 'Total:%',totalsal;

RAISE NOTICE 'fecha %',fecha;
RAISE NOTICE 'hora %',horasal;
END;
$$ language plpgsql;



select dbo.calculoivav1(45000000);

--if en funciones

create or replace function dbo.validaredad(edad integer)
returns void as $$
begin
IF(edad>=18) then
RAISE NOTICE 'MAYOR DE EDAD';
ELSE
RAISE NOTICE 'MENOR DE EDAD';
END IF;
END
$$ language plpgsql;


funcion edad v2

create or replace function dbo.validaredadv2(edad integer)
returns character(20) as $$
begin
IF(edad>=18) then
return 'MAYOR DE EDAD';
ELSE
return 'MENOR DE EDAD';
END IF;
END
$$ language plpgsql;

select dbo.validaredadv2(19);

Lados triangulo

create or replace function
dbo.tipotriangulo(l1 double precision,
l2 double precision,l3 double precision)
returns void as $$
begin
IF((l1=l2) and (l2=l3) and (l1=l3)) then
RAISE NOTICE 'Triangulo Equilatero';
ELSEIF ((l1<>l2) and (l2<>l3) and (l1<>l3)) then
RAISE NOTICE 'Triangulo Escaleno';
ELSE
RAISE NOTICE 'Triangulo Isosceles';
END IF;
END;
$$ language plpgsql;

select dbo.tipotriangulo(34.5,34.5,34.5);



funcion mostrar numero de cargos por departamentos

CREATE OR REPLACE FUNCTION dbo.reporte(
    coddepe character,
    nomdepe character)
  RETURNS character AS $$

declare
cont integer;
begin
cont:=(select count(codcargo) from dbo.cargo where coddep=coddepe);
RAISE NOTICE '*****************************';
RAISE NOTICE '****Codigo Departamento %',coddepe;
RAISE NOTICE '****Nombre Departamento %',nomdepe;
RAISE NOTICE 'Numero Cargo: %',cont;
RAISE NOTICE '*****************************';
return 'Codigo Departamento: '||coddepe|| ' Nombre Departamento: '||nomdepe||' Total: '||cont;
end;
 $$  LANGUAGE plpgsql;

 select dbo.reporte(coddep,nomdep) from dbo.departamento;



funcion crear empleado

create or replace function dbo.crearempleado(
codemp character(10),
primnom character(20),segnom character(20),
primapel character(20),segapel character(20),
codcargo character(10),edad integer)
returns integer as $$
begin
    insert into dbo.empleado values(codemp,primnom,segnom,
    primapel,segapel,codcargo,edad);
    return 1;
exception
    when unique_violation then
        return 0;
    when foreign_key_violation then
       return -1;   
end;
 $$ language plpgsql;

Clase 2 SQL

Modelo Relacional

--Tabla  departamento

CREATE TABLE dbo.departamento
(
  coddep character(10) NOT NULL,
  nomdep character(50) NOT NULL,
  CONSTRAINT pk_departamento PRIMARY KEY (coddep)
);

-- Tabla Cargo

CREATE TABLE dbo.cargo
(
  codcargo character(10) NOT NULL,
  nomcargo character(100) NOT NULL,
  salario integer,
  coddep character(10),
  CONSTRAINT pk_cargo PRIMARY KEY (codcargo),
  CONSTRAINT fk_cargo FOREIGN KEY (coddep)
      REFERENCES dbo.departamento (coddep) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

--Tabla Empleado

CREATE TABLE dbo.empleado
(
  codemp character(10) NOT NULL,
  primnom character(20),
  segnom character(20),
  primapel character(20),
  segapel character(20),
  codcargo character(10),
  edad integer,
  CONSTRAINT pk_empleado PRIMARY KEY (codemp),
  CONSTRAINT fk_empleado FOREIGN KEY (codcargo)
      REFERENCES dbo.cargo (codcargo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


Practica Script Clase

practica clase

Seleccion de la tabla departamento y cargo

select * from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep;

Cuenta el total de cargos

select count(d.coddep) as total
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep;

Having y Group By
select d.coddep,nomdep,count(d.coddep) as total
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep
group by d.coddep,nomdep
having count(d.coddep)>2;

Having y Group By

select d.coddep,nomdep,min(salario) as promedio
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep
group by d.coddep,nomdep

Hav‌ing y Group BY

select c.codcargo,nomcargo,count(codemp) as total
 from dbo.cargo as c,dbo.empleado as e
where c.codcargo=e.codcargo
group by c.codcargo,nomcargo;

vista

create view dbo.vistaest as
select c.codcargo,nomcargo,count(codemp) as total
 from dbo.cargo as c,dbo.empleado as e
where c.codcargo=e.codcargo
group by c.codcargo,nomcargo;

select nomcargo,total from dbo.vistaest;

Subconsulta

SELECT * from dbo.empleado
WHERE edad >(SELECT avg(edad)
        FROM dbo.empleado);

Any

select * from dbo.cargo
where codcargo=any (select codcargo
 from dbo.empleado where edad>30);

All

select * from dbo.cargo
where codcargo=all (select codcargo
 from dbo.empleado where edad>30);


Tabla Copia Sexo

CREATE TABLE dbo.sexocop
(
  idsexo serial NOT NULL,
  nomsexo character(20),
  CONSTRAINT pk_sexoc PRIMARY KEY (idsexo)
)

insertar filas a traves de select
insert into dbo.sexo2(idsexo,nomsexo)
select idsexo,nomsexo from dbo.sexo;