Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

By Bosatzu
#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):
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
By amosbatto
#789049
If your line numbers are correct, then the error is in the copy command for "Archivo AC". It looks like you are passing the wrong type of information. Does this copy command work when you are inside Postgresql?

It is probably better to ask on a Postgresql forum, since your error looks to be in Postgresql.

Hello, a question: is possible get a dynaform dis[…]

Hello I am a Beginner here so I ask for your pati[…]

Jalali Calendar

I tried use external lib , But this only responds […]

Delete the script task. Instead set the trigger […]