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

Moderator: amosbatto

User avatar
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
User avatar
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.
By edwinsharp
#822197
There is a lot of bad press surrounding global. Global allow you to change a value from anywhere in your program and that can effect behavior anywhere else in your program, and that is the problem with global. The art of programming is the control of complexity, and one way to control complexity is to write buyassignmentservice functions that take a limited number of parameters, do one thing, and optionally return a value to the caller. We call this "modularity". We strive to write functions that have a minimum, well-defined connection to the "outside world". Global destroy that modularity by connecting behavior to one or more global in a non-obvious way, so they increase complexity of our code.

When you are inside the ProcessMaker interface and[…]

you can update user information with rest api (PUT[…]

To create a message to be sent to an assigned user[…]

The URL is generated in the file workflow/engine[…]