- Fri Feb 10, 2017 2:05 am
#789034
Hello!
I'm trying to make a php file that gets several variables: http://plugins.smart-coast.com/reporte_ ... *-*abc-001
The php file is the next one (for security I avoid the sever connection):
But i get this error:
I'm trying to make a php file that gets several variables: http://plugins.smart-coast.com/reporte_ ... *-*abc-001
The php file is the next one (for security I avoid the sever connection):
Code: Select all
<?php
if(!isset($_GET['q'])){
//echo "no se ha recibido nada";
die();
}
$datos = explode('*-*',$_GET['q']);
$fecha = explode ("-", $datos[0]);
$fecha_inicio = $fecha[2].'/'.$fecha[1].'/'.$fecha[0];
$fecha = explode ("-", $datos[1]);
$fecha_fin = $fecha[2].'/'.$fecha[1].'/'.$fecha[0];
$fecha_inicio1 = $datos[0];
$fecha_fin1 = $datos[1];
$cod_empresa = $datos[2];
$no_contrato = $datos[3];
$contador_AF = 0;
$contador_US = 0;
$contador_AC = 0;
$contador_AP = 0;
$contador_AU = 0;
$contador_AH = 0;
$contador_AM = 0;
$contador_AT = 0;
//Archivo AF____________________________________________________________________________________________________________________________
$query = "Copy (SELECT '524730152501','E.S.E. CENTRO DE SALUD SAN FRANCISCO','NI,900167616-9',a.no_factura,to_char(a.fecha,'dd/mm/yyyy'),'$fecha_inicio','$fecha_fin',b.codigo_entrada,b.nombre,'$no_contrato','','','','','',a.valor_total from hlt_factura a left join hlt_empresa b on a.cod_empresa=b.cod_empresa where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AF524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo US____________________________________________________________________________________________________________________________
/*$query = "Copy (select c.cod_documento,c.no_historia,d.codigo_entrada,d.cod_regimen,c.apel_1,c.apel_2,c.nom_1,c.nom_2,CASE WHEN ((date_part('year',age( c.fecha_nac ))*365)+(date_part('month',age( c.fecha_nac ))*30)+date_part('day',age( c.fecha_nac ))) < 31 THEN date_part('day',age( c.fecha_nac )) WHEN ((date_part('year',age( c.fecha_nac ))*365)+(date_part('month',age( c.fecha_nac ))*30)+date_part('day',age( c.fecha_nac ))) < 365 THEN date_part('month',age( c.fecha_nac )) ELSE date_part('year',age( c.fecha_nac )) END,CASE WHEN ((date_part('year',age( c.fecha_nac ))*365)+(date_part('month',age( c.fecha_nac ))*30)+date_part('day',age( c.fecha_nac ))) < 31 THEN '3' WHEN ((date_part('year',age( c.fecha_nac ))*365)+(date_part('month',age( c.fecha_nac ))*30)+date_part('day',age( c.fecha_nac ))) < 365 THEN '2' ELSE '3' END,c.cod_sexo,e.cod_departamento,e.cod_municipio,'R' from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_empresa d on a.cod_empresa=d.cod_empresa left join hlt_divipola e on c.cod_div_mun_res=e.consecutivo_divipola where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/US524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AC____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,to_char(b.fecha_cita,'dd/mm/yyyy'),'',b.cod_atencion,CASE WHEN b.cod_atencion = '60403' THEN '08' WHEN b.cod_atencion = '39141' THEN '03' WHEN b.cod_atencion = '60302' THEN '04' WHEN b.cod_atencion = '19237' THEN '07' WHEN b.cod_atencion = '19517' THEN '05' WHEN b.cod_atencion = '19304' THEN '06' WHEN b.cod_atencion = '12101' THEN '01' ELSE '10' END,'13',d.dx1,d.dx2,d.dx3,d.dx4,dx_general,'',a.valor_total from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AC524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AP____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,to_char(b.fecha_cita,'dd/mm/yyyy'),'',f.cod_soat_procedimiento,CASE WHEN b.cod_atencion = '89.07.01' THEN '3' ELSE '1' END,e.finalidad,'2',d.dx1,d.dx2,d.complicacion,'',f.valor from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_formula_procedimiento e on a.caso=e.caso left join hlt_procedimiento f on e.consecutivo_procedimiento=f.consecutivo_procedimiento where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AP524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AU____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,to_char(b.fecha_solicitud,'dd/mm/yyyy'),to_char(b.fecha_solicitud,'HH:mm'),'',e.causa_externa,d.dx1,d.dx2,d.dx3,d.dx4,e.destino_salida,e.estado_salida,d.causa_muerte,to_char(e.fecha_salida,'dd/mm/yyyy'),to_char(e.fecha_salida,'HH:mm') from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_consulta_urgencia e on a.caso=e.caso where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AU524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AH____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,'1',to_char(b.fecha_solicitud,'dd/mm/yyyy'),to_char(b.fecha_solicitud,'HH:mm'),'',e.causa_externa,d.dx1,d.dx2,d.dx3,'',d.dx4,d.complicacion,e.estado_salida,d.causa_muerte,to_char(e.fecha_salida,'dd/mm/yyyy'),to_char(e.fecha_salida,'HH:mm') from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_consulta_urgencia e on a.caso=e.caso where a.estado = 'f' AND e.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AH524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AM____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,'',e.cod_medicamento,'1',e.descripcion,e.forma,e.concentracion,'mg',d.cantidad,e.valor,(d.cantidad*e.valor) from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_formula_medica d on a.caso=d.caso left join hlt_medicamento e on d.consecutivo_medicamento=e.consecutivo_medicamento where a.estado = 'f' AND e.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AM524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);
//Archivo AT____________________________________________________________________________________________________________________________
$query = "Copy (select a.no_factura,'524730152501',c.cod_documento,c.no_historia,'','2','999999','Traslado Acuatico','1','1500000','1500000' from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_consulta_urgencia d on a.caso=d.caso where a.estado = 'f' AND d.destino_salida = '3' AND d.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast( as date) AND cast($fecha_fin1 as date)) To '/var/lib/postgresql/AT524730152501.txt' With CSV DELIMITER ',';";
$result = pg_query($db_connection, $query);*/
//Archivo CT
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_empresa b on a.cod_empresa=b.cod_empresa where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AF = $r['cantidad'];
}
/*$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_empresa d on a.cod_empresa=d.cod_empresa left join hlt_divipola e on c.cod_div_mun_res=e.consecutivo_divipola where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_US = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AC = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_formula_procedimiento e on a.caso=e.caso left join hlt_procedimiento f on e.consecutivo_procedimiento=f.consecutivo_procedimiento where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AP = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_consulta_urgencia e on a.caso=e.caso where a.estado = 'f' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AU = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_diagnosticos d on a.caso=d.hlt_citas_caso left join hlt_consulta_urgencia e on a.caso=e.caso where a.estado = 'f' AND e.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AH = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_formula_medica d on a.caso=d.caso left join hlt_medicamento e on d.consecutivo_medicamento=e.consecutivo_medicamento where a.estado = 'f' AND e.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast($fecha_inicio1 as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AM = $r['cantidad'];
}
$query = "SELECT count(*) as cantidad from hlt_factura a left join hlt_citas b on a.caso=b.caso left join hlt_pacientes c on b.no_historia=c.no_historia left join hlt_consulta_urgencia d on a.caso=d.caso where a.estado = 'f' AND d.destino_salida = '3' AND d.destino_salida = '3' AND a.cod_empresa = '$cod_empresa' AND a.cod_facturacion = '2' AND cast(a.fecha as date) BETWEEN cast( as date) AND cast($fecha_fin1 as date);";
$result = pg_query($db_connection, $query);
while($r = pg_fetch_array($result)) {
$contador_AT = $r['cantidad'];
}*/
$fecha_hoy = date("d/m/Y");
$salida = shell_exec('rm CT524730152501.txt');
$archivo = fopen('CT524730152501.txt',"a");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AC524730152501,'.$contador_AC. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AP524730152501,'.$contador_AP. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AH524730152501,'.$contador_AH. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AU524730152501,'.$contador_AU. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AM524730152501,'.$contador_AM. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AT524730152501,'.$contador_AT. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',AF524730152501,'.$contador_AF. "\n");
fwrite($archivo,'524270069601,'.$fecha_hoy.',US524730152501,'.$contador_US. "\n");
fclose($archivo);
//Se cambia los archivos de direccion
$salida = shell_exec('cp /var/lib/postgresql/AC524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
/*$salida = shell_exec('cp /var/lib/postgresql/AP524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/AH524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/AU524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/AM524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/AT524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/AF524730152501.txt /var/www/html/pmplugins/workflow/public_html/');
$salida = shell_exec('cp /var/lib/postgresql/US524730152501.txt /var/www/html/pmplugins/workflow/public_html/');*/
//Se crea el archivo .zip y se adjuntan los archivos de rips
$zip = new ZipArchive;
$zip->open("Rips524730152501.zip",ZipArchive::CREATE);
/*$zip->addFile("AC524730152501.txt");
$zip->addFile("AP524730152501.txt");
$zip->addFile("AH524730152501.txt");
$zip->addFile("AU524730152501.txt");
$zip->addFile("AM524730152501.txt");
$zip->addFile("AT524730152501.txt");*/
$zip->addFile("AF524730152501.txt");
//$zip->addFile("US524730152501.txt");
//Se descarga el archivo
/*header('Content-type: zip');
header('Content-Disposition: attachment; filename="Rips524730152501.zip"');
header('Cache-Control: max-age=0');
readfile("Rips524730152501.zip");*/
?>
But i get this error:
Warning: pg_query(): Query failed: ERROR: cannot cast type integer to date LINE 1: ...ast(to_char(2016-11-01,'yyyy-mm-dd') as date) AND cast(2016-... ^ in /var/www/html/pmplugins/workflow/public_html/reporte_rips.php on line 44