public function insertarBD()
{
$sqlA = 'INSERT INTO [usuarios] ([nombreUsuario], [password], [nombre], [apellidoPaterno],
[apellidoMaterno], [correo], [idCatTipoUsuario]';
$sqlB = 'VALUES (\'' . trim($this->nombreUsuario) . '\', \'' . md5(trim($this->password)) . '\', \'' . $this->nombre . '\', \'' . $this->apellidoPaterno . '\', \'' . $this->apellidoMaterno . '\', \'' . $this->correo . '\', \'' . $this->idCatTipoUsuario . '\' ';
if ($this->idCatEstado != '' && !is_null($this->idCatEstado)) {
$sqlA .= ', [idCatEstado]';
$sqlB .= ', \'' . $this->idCatEstado . '\'';
}
if ($this->idCatJurisdiccion != '' && !is_null($this->idCatJurisdiccion)) {
$sqlA .= ', [idCatJurisdiccion]';
$sqlB .= ', \'' . $this->idCatJurisdiccion . '\'';
}
if ($this->habilitado != '' && !is_null($this->habilitado)) {
$sqlA .= ', [habilitado]';
$sqlB .= ', \'' . $this->habilitado . '\'';
}
$sqlA .= ') ' . $sqlB . '); SELECT @@Identity AS nuevoId;';
$consulta = ejecutaQueryClases($sqlA);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . ' SQL:' . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idUsuario = $tabla['nuevoId'];
}
}
public function query($sql)
{
LogMaster::log($sql);
if ($this->start_from) {
$res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
} else {
$res = sqlsrv_query($this->connection, $sql);
}
if ($res === false) {
$errors = sqlsrv_errors();
$message = array();
foreach ($errors as $error) {
$message[] = $error["SQLSTATE"] . $error["code"] . $error["message"];
}
throw new Exception("SQLSrv operation failed\n" . implode("\n\n", $message));
}
if ($this->insert_operation) {
sqlsrv_next_result($res);
$last = sqlsrv_fetch_array($res);
$this->last_id = $last["dhx_id"];
sqlsrv_free_stmt($res);
}
if ($this->start_from) {
$data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
}
return $res;
}
public function insertarBD()
{
$sqlA = "INSERT INTO [control] ([idDiagnostico], [fecha]";
$sqlB = "VALUES (" . $this->idDiagnostico . " , '" . formatFechaObj($this->fecha, 'Y-m-d') . "'";
if ($this->reingreso != '' && !is_null($this->reingreso)) {
$sqlA .= ", [reingreso]";
$sqlB .= ", " . $this->reingreso;
}
if ($this->idCatEstadoPaciente != '' && !is_null($this->idCatEstadoPaciente)) {
$sqlA .= ", [idCatEstadoPaciente]";
$sqlB .= ", " . $this->idCatEstadoPaciente;
}
if ($this->idCatTratamientoPreescrito != '' && !is_null($this->idCatTratamientoPreescrito)) {
$sqlA .= ", [idCatTratamientoPreescrito]";
$sqlB .= ", " . $this->idCatTratamientoPreescrito;
}
if ($this->vigilanciaPostratamiento != '' && !is_null($this->vigilanciaPostratamiento)) {
$sqlA .= ", [vigilanciaPostratamiento]";
$sqlB .= ", " . $this->vigilanciaPostratamiento;
}
if ($this->observaciones != '' && !is_null($this->observaciones)) {
$sqlA .= ", [observaciones]";
$sqlB .= ", '" . $this->observaciones . "'";
}
if ($this->idCatEvolucionClinica != '' && !is_null($this->idCatEvolucionClinica)) {
$sqlA .= ", [idCatEvolucionClinica]";
$sqlB .= ", '" . $this->idCatEvolucionClinica . "'";
}
if ($this->idCatBaja != '' && !is_null($this->idCatBaja)) {
$sqlA .= ", [idCatBaja]";
$sqlB .= ", '" . $this->idCatBaja . "'";
}
if ($this->seed != '' && !is_null($this->seed)) {
$sqlA .= ", [seed]";
$sqlB .= ", '" . $this->seed . "'";
}
$sqlA .= ") " . $sqlB . "); SELECT @@Identity AS nuevoId;";
$consulta = ejecutaQueryClases($sqlA);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idControl = $tabla["nuevoId"];
}
// Revisar, actualiza el Estado del paciente (Diagnostico) al reguistrar un nuevo control
$sql = "";
if ($this->idCatEstadoPaciente != '' && !is_null($this->idCatEstadoPaciente)) {
$sql .= "UPDATE diagnostico SET idCatEstadoPaciente = " . $this->idCatEstadoPaciente . " WHERE idDiagnostico = " . $this->idDiagnostico . ";";
}
if ($this->idCatTratamientoPreescrito != '' && !is_null($this->idCatTratamientoPreescrito)) {
$sql .= "UPDATE diagnostico SET idCatTratamiento = " . $this->idCatTratamientoPreescrito . " WHERE idDiagnostico = " . $this->idDiagnostico . ";";
}
$consulta = ejecutaQueryClases($sql);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " No se pudo actualizar el estado del paciente en la tabla Diagnostico SQL:" . $sqlA;
}
}
/**
* Erstellt ein Bild in der Datenbank
* @param int $idLocation
* @return mixed
*/
public function createImage(int $idLocation)
{
$query = 'INSERT INTO image (fk_location) VALUES(?);SELECT SCOPE_IDENTITY() as ID';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($idLocation));
if (sqlsrv_errors()) {
http_response_code(500);
}
sqlsrv_next_result($stmt);
$stmt = sqlsrv_fetch_array($stmt);
return $stmt['ID'];
}
/**
* Erstellt einen Event und gib die ID dessen zurück
* @param int $idcreator
* @param string $name
* @param string $description
* @param int $location
* @return mixed
*/
public function createEvent(int $idcreator, string $name, string $description, int $location)
{
$query = 'INSERT INTO event(fk_person_creator,fk_location,name,description) VALUES (?,?,?,?);SELECT SCOPE_IDENTITY() as ID';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($idcreator, $location, $name, $description));
if (sqlsrv_errors()) {
http_response_code(500);
}
//Select next Result (SCOPE_IDENTITY)
sqlsrv_next_result($stmt);
$stmt = sqlsrv_fetch_array($stmt);
return $stmt['ID'];
}
public function insertarBD()
{
// OJO, ponendo estado como NUEVA (dada la inicializacion)
$sql = "INSERT INTO [incidencia] ([idUsuario], [idCatEstadoIncidencia], [contenido]) VALUES (" . $_SESSION[ID_USR_SESSION] . ", " . (int) $_SESSION[EDO_USR_SESSION] . " , '" . $this->contenido . "'); SELECT @@identity AS nuevoId;";
$consulta = ejecutaQueryClases($sql);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sql;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->fechaCaptura = date("Y-m-d H:i:s");
$this->idIncidencia = $tabla["nuevoId"];
}
}
public function insertarBD()
{
$sql = "INSERT INTO [diagramaDermatologico] ([idDiagnostico], [idCatTipoLesion], [x], [y], [w], [h], [idPaciente]) VALUES (";
$sql .= (int) $this->idDiagnostico . " ," . $this->idCatTipoLesion . " ," . $this->x . " ," . $this->y . " ," . $this->w . " ," . $this->h . "," . (int) $this->idPaciente . ");";
$sql .= "SELECT @@Identity AS nuevoId";
$consulta = ejecutaQueryClases($sql);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idLesion = $tabla["nuevoId"];
}
}
public function insertarBD()
{
$sql = "INSERT INTO [casosRelacionados] ([idDiagnostico], [Nombre], [idCatParentesco], [idCatSituacionCasoRelacionado], \n [tiempoConvivenciaMeses], [tiempoConvivenciaAnos]) ";
$sql .= "VALUES (" . $this->idDiagnostico . ", '" . $this->nombre . "' ," . $this->idCatParentesco . " ," . $this->idCatSituacionCasoRelacionado . " ," . (int) $this->tiempoConvivenciaMeses . " ," . (int) $this->tiempoConvivenciaAnos . ");";
$sql .= "SELECT @@Identity AS nuevoId";
$consulta = ejecutaQueryClases($sql);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
return false;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idCasoRelacionado = $tabla["nuevoId"];
}
return true;
}
function callStoredProcedure($conn, $spName, $spParams)
{
// Count total array parameters
$ttlParams = count($spParams);
// define string
$spData = '';
// Build string Component
for ($i = 0; $i <= $ttlParams - 1; $i++) {
// if string value is not null, add a placeholder as [?]
if ($spParams[$i][0] != 'null') {
$spData[$i] = '?';
} else {
if ($spParams[$i][0] == 'null') {
// insert blank array space holder
$spData[$i] = '';
// delete the array entry from the spParams
unset($spParams[$i]);
}
}
}
// Turn array into comma delimited string
$spComponent = implode(', ', $spData);
// Build Stored Procedure Call
$sp = "{ call {$spName}({$spComponent}) }";
// Call the Stored Procedure
$result = sqlsrv_query($conn, $sp, $spParams);
// if there is a result, Makes the next result of the specified statement active
// @TODO Probably should move this to a different function as this is specialized for WYTOBACCO
if ($next_result = sqlsrv_next_result($result)) {
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
// Get the ID and Report Name.
$result = array("success" => TRUE, "report_name" => $row['report_name'], "report_id" => $row['report_id']);
return $result;
}
} elseif (is_null($next_result)) {
// no result return to script
return;
} else {
// Return whatever sql serv error there was
$result = array("success" => FALSE, "errors" => sqlsrv_errors());
return $result;
}
}
public function query($sql)
{
LogMaster::log($sql);
if ($this->start_from) {
$res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
} else {
$res = sqlsrv_query($this->connection, $sql);
}
if ($this->insert_operation) {
sqlsrv_next_result($res);
$last = sqlsrv_fetch_array($res);
$this->last_id = $last["dhx_id"];
sqlsrv_free_stmt($res);
}
if ($this->start_from) {
$data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
}
return $res;
}
public static function query($queryStr = '', $objectStr = '')
{
$queryDB = sqlsrv_query(self::$dbConnect, $queryStr);
if (preg_match('/insert into/i', $queryDB)) {
sqlsrv_next_result($queryDB);
sqlsrv_fetch($queryDB);
self::$insertID = sqlsrv_get_field($queryDB, 0);
}
if ($queryDB) {
if (is_object($objectStr)) {
$objectStr($queryDB);
}
// sqlsrv_free_stmt($queryDB);
return $queryDB;
} else {
self::$error = sqlsrv_errors();
return false;
}
}
public function insertarBD()
{
$sqlA = "INSERT INTO [contactos] ([idDiagnostico], [nombre], [sexo], [edad], [idCatParentesco]";
$sqlB = "VALUES (" . $this->idDiagnostico . ", '" . $this->nombre . "', '" . $this->sexo . "', " . (int) $this->edad . ", " . $this->idCatParentesco;
if ($this->tiempoConvivenciaAnos != '' && !is_null($this->tiempoConvivenciaAnos)) {
$sqlA .= ", [tiempoConvivenciaAnos]";
$sqlB .= ", " . (int) $this->tiempoConvivenciaAnos;
}
if ($this->tiempoConvivenciaMeses != '' && !is_null($this->tiempoConvivenciaMeses)) {
$sqlA .= ", [tiempoConvivenciaMeses]";
$sqlB .= ", " . (int) $this->tiempoConvivenciaMeses;
}
$sqlA .= ") " . $sqlB . "); SELECT @@Identity AS nuevoId;";
$consulta = ejecutaQueryClases($sqlA);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idContacto = $tabla["nuevoId"];
}
}
/**
* 执行存储过程，多结果集
* @param $sql
* @param $params
* @return array
*/
function sp_execute_multi($sql, $params)
{
$conn = get_sqlsrv_conn();
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
die(print_r(sqlsrv_errors(), true));
}
//
$rss = array();
do {
$rs = array();
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
array_push($rs, $row);
}
array_push($rss, $rs);
} while (sqlsrv_next_result($stmt));
sqlsrv_free_stmt($stmt);
free_sqlsrv_conn($conn);
return $rss;
}
/**
* Fügt einen neuen User der Person hinzu.
* @param string $username
* @param string $password
* @param string $surname
* @param string $name
* @param string $mail
* @return array|false|null
*/
public function insert(string $username, string $password, string $surname, string $name, string $mail)
{
$loginModel = new LoginModel();
$user = $loginModel->load($username);
//User already exists
if ($user != null) {
return false;
}
$connection = Database::getConnection();
$hashedPassword = password_hash($password, PASSWORD_BCRYPT);
$query = "INSERT INTO person(username, password, surname, name, mail) VALUES(?, ?, ?, ?, ?); SELECT SCOPE_IDENTITY() as ID;";
//Execute Query
$stmt = sqlsrv_query($connection, $query, array($username, $hashedPassword, $surname, $name, $mail));
if (sqlsrv_errors()) {
http_response_code(500);
}
//Select next Result (SCOPE_IDENTITY)
sqlsrv_next_result($stmt);
$res = sqlsrv_fetch_array($stmt);
//Load inserted Row
$query = 'SELECT * FROM person WHERE id_person = ' . $res['ID'];
$stmt = sqlsrv_query($connection, $query);
return sqlsrv_fetch_array($stmt);
}
/**
* Move the internal result pointer to the next available result
*
* @return true on success, false if there is no more result set or an error object on failure
* @access public
*/
function nextResult()
{
if (false === $this->result) {
return $this->db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null, 'resultset has already been freed', __FUNCTION__);
}
if (null === $this->result) {
return false;
}
$ret = sqlsrv_next_result($this->result);
if ($ret) {
$this->cursor = 0;
$this->rows = array();
$this->numFields = sqlsrv_num_fields($this->result);
$this->fieldMeta = sqlsrv_field_metadata($this->result);
$this->numRowsAffected = sqlsrv_rows_affected($this->result);
while ($row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC)) {
if ($row !== null) {
if ($this->offset && $this->offset_count < $this->offset) {
$this->offset_count++;
continue;
}
foreach ($row as $k => $v) {
if (is_object($v) && method_exists($v, 'format')) {
//DateTime Object
//$v->setTimezone(new DateTimeZone('GMT'));//TS_ISO_8601 with a trailing 'Z' is GMT
$row[$k] = $v->format("Y-m-d H:i:s");
}
}
$this->rows[] = $row;
//read results into memory, cursors are not supported
}
}
$this->rowcnt = count($this->rows);
}
return $ret;
}
/**
* Advances to the next rowset in a multi-rowset statement handle
*
* Some database servers support stored procedures that return more than one rowset
* (also known as a result set). The nextRowset() method enables you to access the second
* and subsequent rowsets associated with a PDOStatement object. Each rowset can have a
* different set of columns from the preceding rowset.
*
* @return boolean Returns TRUE on success or FALSE on failure.
*/
public function nextRowset()
{
if (sqlsrv_next_result($this->statement) === false) {
$this->handleError();
}
//else - moved to next (or there are no more rows)
}
protected function insert_id($db, $result)
{
sqlsrv_next_result($result);
sqlsrv_fetch($result);
return (int) sqlsrv_get_field($result, 0);
}
/**
* Retrieves the next rowset (result set) for a SQL statement that has
* multiple result sets. An example is a stored procedure that returns
* the results of multiple queries.
*
* @return bool
* @throws \Zend\Db\Statement\Exception
*/
public function nextRowset()
{
if (sqlsrv_next_result($this->_stmt) === false) {
throw new SqlsrvException(sqlsrv_errors());
}
// reset column keys
$this->_keys = null;
return true;
}
public function insertarBD()
{
$sqlA = "INSERT INTO [estudiosHis] ([idDiagnostico], [fechaSolicitud]";
$sqlB = "VALUES (" . $this->idDiagnostico . ", '" . formatFechaObj($this->fechaSolicitud, 'Y-m-d') . "'";
if ($this->idContacto != '' && !is_null($this->idContacto)) {
$sqlA .= ", [idContacto]";
$sqlB .= ", " . $this->idContacto;
}
if ($this->idPaciente != '' && !is_null($this->idPaciente)) {
$sqlA .= ", [idPaciente]";
$sqlB .= ", " . $this->idPaciente;
}
if ($this->fechaRecepcion != '' && !is_null($this->fechaRecepcion)) {
$sqlA .= ", [fechaRecepcion]";
$sqlB .= ", '" . formatFechaObj($this->fechaRecepcion, 'Y-m-d') . "'";
}
if ($this->folioLaboratorio != '' && !is_null($this->folioLaboratorio)) {
$sqlA .= ", [folioLaboratorio]";
$sqlB .= ", '" . $this->folioLaboratorio . "'";
}
if ($this->idCatSolicitante != '' && !is_null($this->idCatSolicitante)) {
$sqlA .= ", [idCatSolicitante]";
$sqlB .= ", '" . $this->idCatSolicitante . "'";
}
if ($this->idCatTipoEstudio != '' && !is_null($this->idCatTipoEstudio)) {
$sqlA .= ", [idCatTipoEstudio]";
$sqlB .= ", " . $this->idCatTipoEstudio;
}
if ($this->lesionTomoMuestra != '' && !is_null($this->lesionTomoMuestra)) {
$sqlA .= ", [lesionTomoMuestra]";
$sqlB .= ", '" . $this->lesionTomoMuestra . "'";
}
if ($this->regionTomoMuestra != '' && !is_null($this->regionTomoMuestra)) {
$sqlA .= ", [regionTomoMuestra]";
$sqlB .= ", '" . $this->regionTomoMuestra . "'";
}
if ($this->fechaTomaMuestra != '' && !is_null($this->fechaTomaMuestra)) {
$sqlA .= ", [fechaTomaMuestra]";
$sqlB .= ", '" . formatFechaObj($this->fechaTomaMuestra, 'Y-m-d') . "'";
}
if ($this->personaTomaMuestra != '' && !is_null($this->personaTomaMuestra)) {
$sqlA .= ", [personaTomaMuestra]";
$sqlB .= ", '" . $this->personaTomaMuestra . "'";
}
if ($this->fechaSolicitudEstudio != '' && !is_null($this->fechaSolicitudEstudio)) {
$sqlA .= ", [fechaSolicitudEstudio]";
$sqlB .= ", '" . formatFechaObj($this->fechaSolicitudEstudio, 'Y-m-d') . "'";
}
if ($this->personaSolicitudEstudio != '' && !is_null($this->personaSolicitudEstudio)) {
$sqlA .= ", [personaSolicitudEstudio]";
$sqlB .= ", '" . $this->personaSolicitudEstudio . "'";
}
if ($this->muestraRechazada != '' && !is_null($this->muestraRechazada)) {
$sqlA .= ", [muestraRechazada]";
$sqlB .= ", " . $this->muestraRechazada;
}
if ($this->idCatMotivoRechazo != '' && !is_null($this->idCatMotivoRechazo)) {
$sqlA .= ", [idCatMotivoRechazo]";
$sqlB .= ", " . $this->idCatMotivoRechazo;
}
if ($this->otroMotivoRechazo != '' && !is_null($this->otroMotivoRechazo)) {
$sqlA .= ", [otroMotivoRechazo]";
$sqlB .= ", '" . $this->otroMotivoRechazo . "'";
}
if ($this->fechaResultado != '' && !is_null($this->fechaResultado)) {
$sqlA .= ", [fechaResultado]";
$sqlB .= ", '" . formatFechaObj($this->fechaResultado, 'Y-m-d') . "'";
}
if ($this->hisDescMacro != '' && !is_null($this->hisDescMacro)) {
$sqlA .= ", [hisDescMacro]";
$sqlB .= ", '" . $this->hisDescMacro . "'";
}
if ($this->hisDescMicro != '' && !is_null($this->hisDescMicro)) {
$sqlA .= ", [hisDescMicro]";
$sqlB .= ", '" . $this->hisDescMicro . "'";
}
if ($this->hisResultado != '' && !is_null($this->hisResultado)) {
$sqlA .= ", [hisResultado]";
$sqlB .= ", '" . $this->hisResultado . "'";
}
if ($this->idCatHisto != '' && !is_null($this->idCatHisto)) {
$sqlA .= ", [idCatHisto]";
$sqlB .= ", " . $this->idCatHisto;
}
if ($this->idCatEstadoLaboratorio != '' && !is_null($this->idCatEstadoLaboratorio)) {
$sqlA .= ", [idCatEstadoLaboratorio]";
$sqlB .= ", " . $this->idCatEstadoLaboratorio;
}
if ($this->idCatJurisdiccionLaboratorio != '' && !is_null($this->idCatJurisdiccionLaboratorio)) {
$sqlA .= ", [idCatJurisdiccionLaboratorio]";
$sqlB .= ", " . $this->idCatJurisdiccionLaboratorio;
}
if ($this->idCatAnalistaLab != '' && !is_null($this->idCatAnalistaLab)) {
$sqlA .= ", [idCatAnalistaLab]";
$sqlB .= ", " . $this->idCatAnalistaLab;
}
if ($this->idCatSupervisorLab != '' && !is_null($this->idCatSupervisorLab)) {
$sqlA .= ", [idCatSupervisorLab]";
$sqlB .= ", " . $this->idCatSupervisorLab;
}
if ($this->idCatEstadoTratante != '' && !is_null($this->idCatEstadoTratante)) {
$sqlA .= ", [idCatEstadoTratante]";
$sqlB .= ", " . $this->idCatEstadoTratante;
}
if ($this->IdCatJurisdiccionTratante != '' && !is_null($this->IdCatJurisdiccionTratante)) {
$sqlA .= ", [IdCatJurisdiccionTratante]";
$sqlB .= ", " . $this->IdCatJurisdiccionTratante;
}
$sqlA .= ") " . $sqlB . "); SELECT @@Identity AS nuevoId;";
$consulta = ejecutaQueryClases($sqlA);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idEstudioHis = $tabla["nuevoId"];
}
}
INPUT parameter. The second parameter is specified as an INOUT
parameter. To ensure data type integrity, output parameters should be
initialized before calling the stored procedure, or the desired
PHPTYPE should be specified in the $params array.*/
$Message = 0;
$contId = $SESSION[0];
//this is set at login
$params = array(array($contId, SQLSRV_PARAM_IN), array($fName, SQLSRV_PARAM_IN), array($lName, SQLSRV_PARAM_IN), array($Address, SQLSRV_PARAM_IN), array($City, SQLSRV_PARAM_IN), array($State, SQLSRV_PARAM_IN), array($Zip, SQLSRV_PARAM_IN), array($eMail, SQLSRV_PARAM_IN), array($Phone, SQLSRV_PARAM_IN), array($Password, SQLSRV_PARAM_IN), array($Message, SQLSRV_PARAM_OUT));
/* Execute the query. */
$stmt3 = sqlsrv_query($conn, $tsql_callSP, $params);
if ($stmt3 === false) {
echo "Error in executing statement 3.\n";
die(print_r(sqlsrv_errors(), true));
}
/* Display the value of the output parameter $Message. */
sqlsrv_next_result($stmt3);
if ($Message == 1) {
echo "Your changes have been saved";
}
/*Free the statement and connection resources. */
sqlsrv_free_stmt($stmt3);
sqlsrv_close($conn);
?>
<!--this can be removed or not-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
function s()
{
// Connect to DB. You can't pass $this->db, cause it's an object and the connection info
// needs and Connection resource.
$parameter1 = 1;
$parameter2 = 1;
$serverName = "HP,1433";
$connectionInfo = array("Database" => "ad", "UID" => "sa", "PWD" => "mynameisanu");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
/* Define the Transact-SQL query. Use question marks in place of
the parameters to be passed to the stored procedure */
$tsql_callSP = "{call assign_duty(?, ?, ?)}";
/*
* Define the parameter array. Put all parameter in the order they appear in the SP.
* The second argument is needed to say if the parameter is an INPUT or an OUTPUT
*/
// This must be set as Integer, cause the initial type for the OUTPUT
$output_parameter = 'out_parameter123456789';
$params = array(array($parameter1, SQLSRV_PARAM_IN), array($parameter2, SQLSRV_PARAM_IN), array($output_parameter, SQLSRV_PARAM_OUT));
/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql_callSP, $params);
if ($stmt === false) {
echo "Error in executing statement.\n";
die(print_r(sqlsrv_errors(), true));
}
sqlsrv_next_result($stmt);
echo $output_parameter;
/*Free the statement and connection resources. */
sqlsrv_free_stmt($stmt);
return $output_parameter;
}
function isInserted($stmt)
{
sqlsrv_next_result($stmt);
sqlsrv_fetch($stmt);
return sqlsrv_get_field($stmt, 0);
}
public function insertarBD()
{
// Se calcula el folio de registro manualmente contando todos los registros actuales en la base de datos
// revisar si no entra en conflicto con la concurrencia
$resultFolio = ejecutaQueryClases('SELECT ( COUNT([idPaciente])+1 ) AS folio FROM [pacientes]');
$folio = devuelveRowAssoc($resultFolio);
$resultEdoFolio = ejecutaQueryClases("SELECT [idCatEstado] FROM [catUnidad] WHERE [idCatUnidad] = '" . $this->idCatUnidadTratante . "'");
$edoFolio = devuelveRowAssoc($resultEdoFolio);
$this->folioRegistro = 'LEP' . str_pad($edoFolio['idCatEstado'], 2, '0', STR_PAD_LEFT) . str_pad($folio['folio'], 5, '0', STR_PAD_LEFT);
$sqlA = "INSERT INTO [pacientes] ([nombre] ,[apellidoPaterno] ,[apellidoMaterno] ,[sexo] ,[fechaNacimiento] ,\n \t\t[cveExpediente] ,[idCatTipoPaciente] ,[idCatMunicipioNacimiento] ,[idCatEstadoNacimiento] ,\n \t\t[idCatLocalidad] ,[idCatMunicipio] ,[idCatEstado] ,[idCatUnidadNotificante] ,[idCatFormaDeteccion] ,\n \t\t[fechaInicioPadecimiento] ,[fechaDiagnostico],[celularContacto]";
$sqlB = "VALUES ('" . $this->nombre . "', '" . $this->apellidoPaterno . "', '" . $this->apellidoMaterno . "', '" . $this->sexo . "', '" . formatFechaObj($this->fechaNacimiento, 'Y-m-d') . "', '" . $this->cveExpediente . "', " . $this->idCatTipoPaciente . ", '" . $this->idCatMunicipioNacimiento . "', " . $this->idCatEstadoNacimiento . ", " . $this->idCatLocalidad . ", " . $this->idCatMunicipio . ", " . $this->idCatEstado . ", '" . $this->idCatUnidadNotificante . "', " . $this->idCatFormaDeteccion . ", '" . formatFechaObj($this->fechaInicioPadecimiento, 'Y-m-d') . "', '" . formatFechaObj($this->fechaDiagnostico, 'Y-m-d') . "', '" . $this->celularContacto . "'";
if ($this->fechaNotificacion != '' && !is_null($this->fechaNotificacion)) {
$sqlA .= ", [fechaNotificacion]";
$sqlB .= ", '" . formatFechaObj($this->fechaNotificacion, 'Y-m-d') . "'";
}
if ($this->semanaEpidemiologica != '' && !is_null($this->semanaEpidemiologica)) {
$sqlA .= ", [semanaEpidemiologica]";
$sqlB .= ", " . $this->semanaEpidemiologica;
}
if ($this->ocupacion != '' && !is_null($this->ocupacion)) {
$sqlA .= ", [ocupacion]";
$sqlB .= ", '" . $this->ocupacion . "'";
}
if ($this->calle != '' && !is_null($this->calle)) {
$sqlA .= ", [calle]";
$sqlB .= ", '" . $this->calle . "'";
}
if ($this->noExterior != '' && !is_null($this->noExterior)) {
$sqlA .= ", [noExterior]";
$sqlB .= ", '" . $this->noExterior . "'";
}
if ($this->noInterior != '' && !is_null($this->noInterior)) {
$sqlA .= ", [noInterior]";
$sqlB .= ", '" . $this->noInterior . "'";
}
//if($this->celularContacto != '' && !is_null($this->celularContacto)) { $sqlA .= ", [celularContacto]"; $sqlB .= ", '" . $this->celularContacto. "'"; }
if ($this->colonia != '' && !is_null($this->colonia)) {
$sqlA .= ", [colonia]";
$sqlB .= ", '" . $this->colonia . "'";
}
if ($this->telefono != '' && !is_null($this->telefono)) {
$sqlA .= ", [telefono]";
$sqlB .= ", '" . $this->telefono . "'";
}
if ($this->anosRadicando != '' && !is_null($this->anosRadicando)) {
$sqlA .= ", [anosRadicando]";
$sqlB .= ", " . $this->anosRadicando;
}
if ($this->mesesRadicando != '' && !is_null($this->mesesRadicando)) {
$sqlA .= ", [mesesRadicando]";
$sqlB .= ", " . $this->mesesRadicando;
}
if ($this->idCatInstitucionUnidadNotificante != '' && !is_null($this->idCatInstitucionUnidadNotificante)) {
$sqlA .= ", [idCatInstitucionUnidadNotificante]";
$sqlB .= ", " . $this->idCatInstitucionUnidadNotificante;
}
if ($this->otraInstitucionUnidadNotificante != '' && !is_null($this->otraInstitucionUnidadNotificante)) {
$sqlA .= ", [otraInstitucionUnidadNotificante]";
$sqlB .= ", '" . $this->otraInstitucionUnidadNotificante . "'";
}
if ($this->idCatInstitucionDerechohabiencia != '' && !is_null($this->idCatInstitucionDerechohabiencia)) {
$sqlA .= ", [idCatInstitucionDerechohabiencia]";
$sqlB .= ", " . $this->idCatInstitucionDerechohabiencia;
}
if ($this->otraDerechohabiencia != '' && !is_null($this->otraDerechohabiencia)) {
$sqlA .= ", [otraDerechohabiencia]";
$sqlB .= ", '" . $this->otraDerechohabiencia . "'";
}
if ($this->fechaInicioPQT != '' && !is_null($this->fechaInicioPQT)) {
$sqlA .= ", [fechaInicioPQT]";
$sqlB .= ", '" . formatFechaObj($this->fechaInicioPQT, 'Y-m-d') . "'";
}
if ($this->idCatUnidadReferido != '' && !is_null($this->idCatUnidadReferido)) {
$sqlA .= ", [idCatUnidadReferido]";
$sqlB .= ", '" . $this->idCatUnidadReferido . "'";
}
if ($this->idCatUnidadTratante != '' && !is_null($this->idCatUnidadTratante)) {
$sqlA .= ", [idCatUnidadTratante]";
$sqlB .= ", '" . $this->idCatUnidadTratante . "'";
}
if ($this->idCatInstitucionTratante != '' && !is_null($this->idCatInstitucionTratante)) {
$sqlA .= ", [idCatInstitucionTratante]";
$sqlB .= ", " . $this->idCatInstitucionTratante . " ";
}
if ($this->campoExtrangero != '' && !is_null($this->campoExtrangero)) {
$sqlA .= ", [campoExtrangero]";
$sqlB .= ", '" . $this->campoExtrangero . "'";
}
if ($this->otraInstitucionTratante != '' && !is_null($this->otraInstitucionTratante)) {
$sqlA .= ", [otraInstitucionTratante]";
$sqlB .= ", '" . $this->otraInstitucionTratante . "'";
}
if ($this->idCatEstadoReferido != '' && !is_null($this->idCatEstadoReferido)) {
$sqlA .= ", [idCatEstadoReferido]";
$sqlB .= ", '" . $this->idCatEstadoReferido . "'";
}
if ($this->folioRegistro != '' && !is_null($this->folioRegistro)) {
$sqlA .= ", [folioRegistro]";
$sqlB .= ", '" . $this->folioRegistro . "'";
}
if ($this->medicoElaboro != '' && !is_null($this->medicoElaboro)) {
$sqlA .= ", [medicoElaboro]";
$sqlB .= ", '" . $this->medicoElaboro . "'";
}
if ($this->medicoValido != '' && !is_null($this->medicoValido)) {
$sqlA .= ", [medicoValido]";
$sqlB .= ", '" . $this->medicoValido . "'";
}
$sqlA .= ") " . $sqlB . "); SELECT @@Identity AS nuevoId;";
$consulta = ejecutaQueryClases($sqlA);
if (is_string($consulta)) {
$this->error = true;
$this->msgError = $consulta . " SQL:" . $sqlA;
} else {
sqlsrv_next_result($consulta);
$tabla = devuelveRowAssoc($consulta);
$this->idPaciente = $tabla["nuevoId"];
}
}
public function execute($params = null)
{
if ($params) {
$hasZeroIndex = array_key_exists(0, $params);
foreach ($params as $key => $val) {
$key = $hasZeroIndex && is_numeric($key) ? $key + 1 : $key;
$this->bindValue($key, $val);
}
}
$this->stmt = sqlsrv_query($this->conn, $this->sql, $this->params);
if (!$this->stmt) {
throw SQLSrvException::fromSqlSrvErrors();
}
if ($this->lastInsertId) {
sqlsrv_next_result($this->stmt);
sqlsrv_fetch($this->stmt);
$this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0));
}
}
public function insert_records($tablename, $datas)
{
//Before any Query, first get the table description
if (!$this->describe_table($tablename)) {
return false;
}
if (!(gettype($datas) == 'array')) {
trigger_error("Unsupported datatype for datas. Array expected, " . gettype($datas) . " passed", E_USER_ERROR);
return false;
}
if (sizeof($datas) < 1) {
trigger_error("No values passed to insert query", E_USER_ERROR);
return false;
}
$sql = "INSERT INTO " . $tablename . " ";
//$tempdata = array_values($datas);
$insertclause = $this->insert_clause_builder($datas[0]);
$placeholder = array();
$i = sizeof($datas);
while ($i > 0) {
$placeholder[] = $insertclause[1];
$i--;
}
$insertclause[1] = implode(",", $placeholder);
$sql .= $insertclause[0] . " VALUES " . $insertclause[1] . "; SELECT SCOPE_IDENTITY();";
$tempArray = array();
foreach ($data as $key => $value) {
$tempArray[] =& $value;
}
$stmt = sqlsrv_query($this->connection, $sql, $tempArray);
if (!$stmt) {
return false;
}
sqlsrv_next_result($stmt);
sqlsrv_fetch($stmt);
$newId = sqlsrv_get_field($stmt, 0);
if ($newId > 0) {
return $newId;
} else {
return true;
}
}
/**
* Retrieves the next rowset (result set) for a SQL statement that has
* multiple result sets. An example is a stored procedure that returns
* the results of multiple queries.
*
* @return bool
* @throws Zend_Db_Statement_Exception
*/
public function nextRowset()
{
if (sqlsrv_next_result($this->_stmt) === false) {
require_once 'include/Zend/Db/Statement/Sqlsrv/Exception.php';
throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
}
// reset column keys
$this->_keys = null;
return true;
}
public function sp($sql, array &$params, $options = array())
{
if (!isset($this->db)) {
if (error_reporting() !== 0) {
log::error('Error running database query. Database connection not initialized.');
}
return false;
}
if (isset($options) && !is_array($options)) {
$options = array();
}
$result = sqlsrv_query($this->db, $sql, $params, $options);
if ($result === false && error_reporting() !== 0) {
log::$errorDetails = $this->last_error();
log::error('Error running query on database: ' . $this->connectDetails['db']);
}
if ($result !== false) {
sqlsrv_next_result($result);
}
return $result;
}
function NextRecordSet()
{
if (!sqlsrv_next_result($this->_queryID)) {
return false;
}
$this->_inited = false;
$this->bind = false;
$this->_currentRow = -1;
$this->Init();
return true;
}
/**
* Retrieves the next rowset (result set) for a SQL statement that has
* multiple result sets. An example is a stored procedure that returns
* the results of multiple queries.
*
* @return bool
* @throws \Zend\DB\Statement\Exception
*/
public function nextRowset()
{
if (sqlsrv_next_result($this->_stmt) === false) {
throw new Exception(sqlsrv_errors());
}
//else - moved to next (or there are no more rows)
}
public function execute()
{
if ($this->usedPrepare) {
if (!sqlsrv_execute($this->stmt)) {
throw new SqlException('Failed to execute prepared statement', sqlsrv_errors(), $this->query, $this->params);
}
}
$next = null;
$selectedRows = false;
$this->affected = 0;
// get affected row count from each result (triggers could cause multiple inserts)
do {
$affectedRows = sqlsrv_rows_affected($this->stmt);
if ($affectedRows === false) {
throw new SqlException('Failed to get affected row count', sqlsrv_errors(), $this->query, $this->params);
} elseif ($affectedRows === -1) {
$selectedRows = true;
// reached SELECT result
break;
// so that getIterator will be able to select the rows
} else {
$this->affected += $affectedRows;
}
} while ($next = sqlsrv_next_result($this->stmt));
if ($next === false) {
throw new SqlException('Failed to get next result', sqlsrv_errors(), $this->query, $this->params);
}
if ($selectedRows === false && !$this->usedPrepare) {
$this->close();
// no results, so statement can be closed
}
}
