I have a strange problem
I'm using a postgresql database, so i've created a function with that return a query with the following data.
When i want retrieve the data from a programing language how php or python, some rows and cells were altered.
The following picture is a 'var_dump' statement writed in php, and you can see that the first data is not equals with the result of the dbms
'var_dump' statement in php of the result set
Now the following picture is a 'print' statement written in python, i've calling the same function with the same parameters and occurs the same
'print' statement in python of the result set
For example, i have a row with the follow data This is an example of data retrieved by the dbms DBeaver 7.3.3 and AquaDataStudio 18.0.18. Check the values wrapped with *
Ac Diseño Y Maquinados S.A. De C.V. ADM120725CE8 **20105.00** **170684.94**
Now when I obtain the values from some programming language, the result is the following
Ac Diseño Y Maquinados S.A. De C.V. ADM120725CE8 **297488.33** **171968.47**
Here is the code
Code of the function that return the result set
Returns this Resultset of DBMS
create or replace function fn_reporte_cfdis(p_fecha_desde varchar,p_fecha_hasta varchar)
returns table (
razon_social varchar(254)
, rfc varchar(254)
, suma_emitidos NUMERIC
, suma_recibidos NUMERIC
, utilidad_fiscal NUMERIC
, isr NUMERIC
, ptu NUMERIC
, dividendos NUMERIC
) language plpgsql
as $$
begin
return query
SELECT
CI.SNOMBRE RAZON_SOCIAL,
CI.SRFC RFC,
coalesce(TO_NUMBER(TO_CHAR(EMITIDOS.TOTAL,'99G999G999D99'),'99G999G999D99'),0) SUMA_EMITIDOS,
coalesce(TO_NUMBER(TO_CHAR(RECIBIDOS.TOTAL,'99G999G999D99'),'99G999G999D99'),0)SUMA_RECIBIDOS,
coalesce(TO_NUMBER(TO_CHAR((EMITIDOS.TOTAL - RECIBIDOS.TOTAL),'99G999G999D99'),'99G999G999D99'),0) UTILIDAD_FISCAL,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.30),'99G999G999D99'),'99G999G999D99'),0) ISR,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.10),'99G999G999D99'),'99G999G999D99'),0) PTU,
coalesce(TO_NUMBER(TO_CHAR(((EMITIDOS.TOTAL - RECIBIDOS.TOTAL)*0.10),'99G999G999D99'),'99G999G999D99'),0) DIVIDENDOS
FROM
CLIENTES_IR CI
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) AS TOTAL
FROM
CLIENTES_IR CI
LEFT JOIN
CFDIS_SAT CS
ON
CS.RFCEMISOR = CI.SRFC
WHERE
CS.TIPO = 'I' AND
CS.RFCEMISOR = CI.SRFC AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC ) EMITIDOS ON CI.SRFC = EMITIDOS.SRFC
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) + COALESCE(EMITIDOS_NOMINA.SUBTOTAL,0) AS TOTAL
FROM
CFDIS_SAT CS
INNER JOIN
CLIENTES_IR CI ON CS.RFCRECEPTOR = CI.SRFC
LEFT JOIN
(SELECT
CI.SRFC,
SUM(COALESCE(CS.SUBTOTAL,0)) AS SUBTOTAL
FROM
CFDIS_SAT CS
INNER JOIN CLIENTES_IR CI ON CS.RFCEMISOR = CI.SRFC
WHERE
CS.TIPO = 'N' AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC ) EMITIDOS_NOMINA ON CI.SRFC = EMITIDOS_NOMINA.SRFC
WHERE
CS.TIPO ='I' AND
(CS.USOCFDI = 'G01' OR CS.USOCFDI = 'G03') AND
CS.FHEMISION BETWEEN to_date(p_fecha_desde,'DD-MM-YYYY') AND to_date(p_fecha_hasta,'DD-MM-YYYY')
GROUP BY CI.SRFC , EMITIDOS_NOMINA.SUBTOTAL) RECIBIDOS ON RECIBIDOS.SRFC = CI.SRFC;
end;$$
The code of the model class in php that connect to database and call the function
class ReporteCfdisModel
{
private $connection;
private $queryData;
public function __construct()
{
$this->connection = Connection::get()->connection(); //Get the connection of a class, is a tipical connection with pg_connect()
$this->queryData = IniUtils::getUtils("application", "QUERY"); // get data of .ini file (Not relevant for this case)
}
public function getAll()
{
try {
$stmt = pg_prepare($this->connection, "get_all", "select * from fn_reporte_cfdis($1,$2)"); // Prepare the query
$stmt = pg_execute($this->connection, "get_all", array("01-02-2021", "28-02-2021")); // execute the query and pass parameters
var_dump(pg_fetch_all($stmt)); // The line that Shows in the second image.
// return pg_fetch_all($stmt);
} catch (\Exception $ex) {
echo json_encode(new Response(500, $ex->getMessage(), ""), JSON_PRETTY_PRINT);
} finally {
$stmt = null;
$this->connection = null;
}
}
}
Here is the code of database connection
class Connection
{
private $configData;
private $host;
private $dbName;
private $user;
private $password;
private $port;
private static $instance = NULL;
public $connectionData;
public function __construct()
{
$this->configData = IniUtils::getUtils("application", "DB_PGSQL");
if ($this->configData !== null || count($this->configData) > 0) {
$this->host = $this->configData["db_host"];
$this->dbName = $this->configData["db_name"];
$this->user = $this->configData["db_user"];
$this->password = $this->configData["db_password"];
$this->port = $this->configData["db_port"];
}
}
public static function get()
{
if (self::$instance === NULL) {
self::$instance = new Connection();
}
return self::$instance;
}
public function connection()
{
try {
// $this->connectionData = new PDO('pgsql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbName, $this->user, $this->password);
$strHost = 'host=' . $this->host . ' port=' . $this->port . ' dbname=' . $this->dbName . " user=" . $this->user . " password=" . $this->password;
$this->connectionData = pg_connect($strHost);
} catch (\Exception $ex) {
echo $ex->getMessage();
}
return $this->connectionData;
}
}
It's all important code that make the logic.
Whats Happened? I don't know what happens when I call the query or function with the dbms and why this data does not match the result set calling in some programming language.
Help me please!
