Good afternoon guys from stack overflow.
I have the following situation. I need to calculate how many days a status takes to change. For example, my partner's registration has the status of "Registration Started", then passes to "Waiting for registration approval", these facts occurred on 04/4/2017 and 04/10/2017 respectively. Let's agree that this fact took 6 days to change.
This sql below, works perfectly
$selectDiasEntreStatus = "SELECT TIMESTAMPDIFF(DAY,MAX(prt_partner_historic_status._data_registro) +
INTERVAL TIMESTAMPDIFF(YEAR, prt_partner_historic_status._data_registro, now()) YEAR ,now()) AS dias_entre_status
FROM prt_partner_historic_status WHERE id_partner = :id_partner";
$stmt = DB::prepare($selectDiasEntreStatus);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->execute();
foreach ($stmt->fetchAll() as $keys => $values) {
$this->diasStatus = $values->dias_entre_status;
}
When I comment the code and send the returned value, the value printed is 6 as I put it in the explanation.
When I run the whole process, which is the code down, the value mysteriously goes blank.
$updateDiasStatus = "UPDATE prt_partner_historic_status SET dias_entre_status = :dias_entre_status WHERE id_partner = :id_partner AND id_historic_status = :id_historic_status ";
$stmt = DB::prepare($updateDiasStatus);
$stmt->bindParam(":dias_entre_status",$this->diasStatus,PDO::PARAM_INT);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->bindParam(":id_historic_status", $this->idHistoricStatus,PDO::PARAM_INT);
$stmt->execute();
$stmt->closeCursor();
if (self::getInstance()->commit()) {
return true;
}
There are no apparent errors.
All the piece code
public function updateStatus($id){
$Exc = new ExceptionDatabase();
try{
self::getInstance()->beginTransaction();
self::getInstance()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$updateStatus = "UPDATE prt_partner SET id_status = :id_status WHERE id_partner = :id_partner";
$stmt = DB::prepare($updateStatus);
$stmt->bindParam(":id_status",$this->idStatus,PDO::PARAM_STR);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_STR);
$stmt->execute();
$inserirStatus = "INSERT INTO prt_partner_historic_status(id_historic_status,id_status,id_partner,nota)
VALUES(NULL ,:id_status,:id_partner,:nota);";
$stmt = DB::prepare($inserirStatus);
$stmt->bindParam(":id_status",$this->idStatus, PDO::PARAM_INT);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->bindParam(":nota",$this->nota,PDO::PARAM_STR);
$stmt->execute();
$this->idHistoricStatus = DB::getInstance()->lastInsertId();
if($this->idStatus == "3"){
$inserirHistoricoRegistro = "INSERT INTO prt_partner_historic_register(id_historic_register,id_partner,id_historic_status,uniqueid_portal_papercut,login_portal_papercut,senha_portal_papercut,_ativo)
VALUES(NULL,:id_partner,:id_historic_status,:uniqueid_portal_papercut,:login_portal_papercut,:senha_portal_papercut,:ativo);";
$stmt = DB::prepare($inserirHistoricoRegistro);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->bindParam(":id_historic_status",$this->idHistoricStatus,PDO::PARAM_INT);
$stmt->bindParam(":uniqueid_portal_papercut",$this->uniqueIDPapercut,PDO::PARAM_STR);
$stmt->bindParam(":login_portal_papercut",$this->loginPapercut,PDO::PARAM_STR);
$stmt->bindParam(":senha_portal_papercut",$this->senhaPapercut,PDO::PARAM_STR);
$stmt->bindParam(":ativo",$this->ativo,PDO::PARAM_STR);
$stmt->execute();
}
$selectDiasEntreStatus = "SELECT TIMESTAMPDIFF(DAY,MAX(prt_partner_historic_status._data_registro) +
INTERVAL TIMESTAMPDIFF(YEAR, prt_partner_historic_status._data_registro, now()) YEAR ,now()) AS dias_entre_status
FROM prt_partner_historic_status WHERE id_partner = :id_partner";
$stmt = DB::prepare($selectDiasEntreStatus);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->execute();
foreach ($stmt->fetchAll() as $keys => $values) {
$this->diasStatus = $values->dias_entre_status;
}
$updateDiasStatus = "UPDATE prt_partner_historic_status SET dias_entre_status = :dias_entre_status WHERE id_partner = :id_partner AND id_historic_status = :id_historic_status ";
$stmt = DB::prepare($updateDiasStatus);
$stmt->bindParam(":dias_entre_status",$this->diasStatus,PDO::PARAM_INT);
$stmt->bindParam(":id_partner",$id,PDO::PARAM_INT);
$stmt->bindParam(":id_historic_status", $this->idHistoricStatus,PDO::PARAM_INT);
$stmt->execute();
$stmt->closeCursor();
if (self::getInstance()->commit()) {
return true;
}
}
catch(PDOException $ex){
self::getInstance()->rollBack();
date_default_timezone_set('America/Sao_Paulo');
$dataRegistro = date("Y-m-d H:i:s");
$this->Caminho = explode("/", $_SERVER['SCRIPT_NAME']);
$this->arquivo = $this->Caminho[count($this->Caminho)-1];
$this->arquivoLog = 'log/erros.txt';
$this->erro = $ex->getCode();
$this->mensagem = $ex->getMessage();
$Exc->setTipoLog(enum::Error);
$Exc->setTitleLog($ex->errorInfo[0]);
$Exc->setDescLog($ex->getMessage());
$Exc->setDataRegistro($dataRegistro);
$Exc->setArquivo($this->arquivo);
$Exc->setArquivoLog($this->arquivoLog);
$Exc->setErro($this->erro);
$Exc->setMensagem($this->mensagem);
$Exc->erro();
}
}