CashMan H.A. Stored Procedures: Complete Domain Reference

All database operations in CashMan H.A. are executed exclusively through the 148 stored procedures defined in ScriptSQL/Stored Procedure/sp_cashmanha.sql. No inline SQL exists in any PHP controller or model file — every read, write, and update goes through a CALL statement. This keeps business logic at the database layer, simplifies auditing, and prevents SQL injection by design.

PHP Call Pattern

Procedures are invoked using mysqli_query with a CALL string. The connection object ($this->conexion or $conectarsistema) is passed as the first argument:

$result = mysqli_query(
    $this->conexion,
    "CALL ConsultarConfiguracionCuentaUsuarios($idUsuario)"
);

Parameters are interpolated directly into the call string from validated PHP session or POST variables. Example from the session init model:

$resultado = mysqli_query(
    $conectarsistema,
    "CALL IniciarSesion('$usuario', '$contrasenia');"
);

Procedures by Domain

Authentication (4 procedures)

Procedure	Parameters	Purpose
`IniciarSesion`	`Usuario VARCHAR(255)`, `Pass VARCHAR(255)`	Validate login credentials and return user record
`RegistrarAccesosUsuarios`	`_dispositivo VARCHAR(255)`, `_sistemaoperativo VARCHAR(255)`, `_idusuarios INT`	Append a login audit row to `accesos`
`CambioContraseniaRecuperacion`	`_contrasenia VARCHAR(255)`, `_correo VARCHAR(255)`	Update password for a given email address
`CambioEstadoToken`	`_correo VARCHAR(255)`, `_token VARCHAR(255)`, `_codigo INT`, `_estado VARCHAR(15)`	Mark a recovery token as `usado`

Password Recovery (3 procedures)

Procedure	Parameters	Purpose
`ConsultarCorreoRecuperacion`	`_correo VARCHAR(255)`	Check if an email address exists in `usuarios`
`VerificarCodigoSeguridad`	`_codigo INT`, `_correo VARCHAR(255)`, `_token VARCHAR(255)`	Validate a one-time recovery code (returns row if unused)
`ReestablecerContrasenias`	`_correo VARCHAR(255)`, `_token VARCHAR(255)`, `_codigo INT`	Reset flow verification (multi-step BEGIN…END body)

User Management (18 procedures)

Procedure	Purpose
`RegistrarNuevosClientesAdministradores`	Insert a new user into `usuarios` with role and registering employee
`RegistrarDetallesUsuarios_Clientes`	Insert the full KYC profile row into `detalleusuarios`
`ConsultarNumeroUsuarios`	Return all rows from `vista_configuracionusuariosgeneral` (admin dashboard counter)
`ConsultaGeneralUsuariosRegistrados`	List active users with complete profile (`completoperfil='si'`, `estado_usuario='activo'`)
`ConsultaGeneralUsuariosInactivos`	List inactive users with complete profile
`ConsultaGeneralUsuariosBloqueados`	List blocked users with complete profile
`ConsultaUsuariosPerfilIncompleto`	List users with incomplete profiles registered by a given employee
`ConsultaGeneralCompletaUsuarios`	Return full user + detail record for a specific `idusuarios`
`ConsultarPerfilUsuarios`	Return profile view for a given user (self-service)
`ModificarConfiguracionCuentaUsuarios_Administradores`	Edit user account fields including role and status (admin-only)
`ModificarDetallesUsuarios_Clientes`	Update `detalleusuarios` including all document photo filenames
`ActualizarDetallesUsuarios`	Self-service update of personal details (no document photos)
`ActualizarConfiguracionCuentasAdministradoresConFoto`	Update admin account including new profile photo
`ActualizarConfiguracionCuentasAdministradoresSinFoto`	Update admin account without changing photo
`DesactivarUsuarios_Clientes`	Set `estado_usuario='inactivo'`
`BloquearUsuarios_Clientes`	Set `estado_usuario='bloqueado'`
`ReactivarUsuarios_Clientes`	Set `estado_usuario='activo'`
`ActualizacionDatosCuenta_InicioSesionUsuariosPrimeraVez`	Update username and password on first login

Roles Management (4 procedures)

Procedure	Parameters	Purpose
`RegistroNuevosRolesDeUsuarios`	`_nombrerol`, `_descripcionrol`	Create a new role
`ModificarRolesUsuarios`	`_idrol`, `_nombrerol`, `_descripcionrol`	Update an existing role
`EliminarRolesUsuarios`	`_idrol INT`	Delete a role by ID
`ConsultaCompletaRolesDeUsuariosRegistrados`	—	List all roles

Credit Management (22 procedures)

Procedure	Purpose
`IngresoSolicitudNuevosPrestamosClientes_NuevasAsignaciones`	Submit a new credit application into `creditos`
`ActualizacionDatosNuevasSolicitudesCreditos_PrimeraRevision`	Save Gerencia’s first review decision (state, rates, schedule, notes)
`ActualizacionRevisionFinalPresidencia_SolicitudCreditoClientes`	Save Presidencia’s final approval or denial
`ActualizacionSaldoCreditosClientes_ReestructuracionSolicitudes`	Recalculate balance on restructured credit
`RegistrarCuotasMensualesNuevosCreditosClientes`	Insert one installment row into `cuotas` (called once per month of term)
`RegistrarCuotasMensualesHistoricoCreditosClientes`	Insert installment into `historicocuotascreditos` during archival
`RegistroPagoCuotasCreditosClientes_OrdenPagosCashManHa`	Process an installment payment (INSERT into `transacciones`)
`RegistroCopiaContratoClientesFinal`	Store signed contract filename and set `proceso_finalizado='si'`
`RegistrarSolicitudesCreditosClientesHistorico_Cancelados`	Manually archive a cancelled credit to `historicocreditos`
`EnvioHistoricoSolicitudesCreditos`	Delete credit record, triggering automatic archival via trigger
`EliminarSolicitudesCrediticiasCanceladas_Clientes`	Hard-delete a denied/cancelled credit record
`ConsultaListadoGeneralCreditosAprobados_EnCurso`	List all approved or in-progress credits
`ConsultaGeneralSolicitudesCreditosDenegadasClientes`	List denied credit applications
`ConsultaGeneralListadoCreditosAprobados_EnCurso`	(Alias) List active approved credits
`ConsultaListadoGeneralCuotasClientesMorosos`	List overdue installments sorted by days overdue descending
`ConsultaEspecificaSolicitudesReestructuracionCreditosClientes`	List restructured credits assigned to a specific employee
`ConsultaGeneralReestructuracionCreditosClientes`	List all credits in restructuring state without installments generated
`ConsultarListadoCreditosClientesCancelados`	List fully repaid (cancelled) credits
`ConsultaGestionadorCuotasMensualesContratos_CreditosAprobados`	Fetch approved/cancelled credits for installment generation
`ConsultaSolicitudesCreditosAprobadas`	Credits approved but not yet with installments generated
`ConsultaIdUnicoCreditos_ProductosClientes`	Return credit ID and product info for a specific client
`ConsultarAvanceCreditosClientes_InterfazInicioClientes`	Return payment progress data for the client dashboard

Credit Transactions & Reporting (8 procedures)

Procedure	Purpose
`ConsultaTransacciones_PagosCreditosClientes`	Fetch payment records for a specific user and installment
`ConsultarTransaccionesProcesadasClientes_General`	List all credit payment transactions ordered by date
`ConsultarTransaccionesProcesadasClientes_UltimasTransacciones`	Last 200 transactions across all clients
`ConsultarTransaccionesProcesadasClientes_PortalInicioClientes`	Last 51 transactions for a specific client’s dashboard
`ConsultarMisTransaccionesProcesadasClientes_Especifica`	All transactions for a specific client
`ConsultaDetalleCompletoTransacciones_PagoCuotasCreditosEmpleados`	Last 200 transactions processed by a specific employee
`ContadorTransaccionesProcesadas_EmpleadosAtencionClientes`	Transaction count per employee
`ConsultaTotalIngresosTransaccionesCreditos_EmpleadosAtencion`	Total amount collected by a specific employee

Savings Accounts (12 procedures)

Procedure	Purpose
`RegistroNuevasCuentasAhorroClientesCashmanha`	Open a new savings account (INSERT into `cuentas`)
`RegistroDepositoCuentasAhorrosClientesCashManHa`	Record a deposit in `transaccionescuentasclientes` with type `Entrada`
`RegistroRetiroCuentasAhorrosClientesCashManHa`	Record a withdrawal with type `Salida`
`AnularDepositosTransaccionesCuentasClientes`	Void a deposit by setting `estadotransaccion='AnularDeposito'`
`AnularRetirosTransaccionesCuentasClientes`	Void a withdrawal by setting `estadotransaccion='AnularRetiro'`
`BloquearCuentasAhorroRegistradasClientes`	Set `estadocuenta='bloqueada'`
`CerrarCuentasAhorroRegistradasClientes`	Set `estadocuenta='cerrada'`
`ActivarCuentasAhorroRegistradasClientes`	Set `estadocuenta='activa'`
`ConsultaListadoCuentasAhorrosRegistradas`	List all accounts ordered by client name
`ConsultaEspecificaDatosCuentasAhorroClientesCashmanha`	Fetch account details for a specific user
`ConsultaGeneralTransaccionesCuentasClientesProcesadas`	List all processed account transactions
`ConsultaGeneralTransaccionesCuentasClientesAnuladas`	List all voided account transactions

Transfers (5 procedures)

Procedure	Purpose
`RegistrarTransferenciasEnviadasClientes`	Execute a transfer (INSERT into `transferencias`, triggers handle balance updates)
`RegistroCodigoSeguridadTransferenciasCuentasClientes`	Store a new OTP security code before a transfer is authorised
`Verificar_ValidacionCodigoSeguridadTransferencias`	Validate that a submitted OTP is still `Valido`
`ConsultarExistenciaCuentaAhorros_SistemaTransferenciasClientes`	Confirm that a destination account number exists
`ConsultaDatosClientes_TransferenciasCuentasAhorros`	Fetch destination account and owner info by account number

Messaging & Notifications (7 procedures)

Procedure	Purpose
`RegistroNuevosMensajesUsuarios_MensajeriaCashManHa`	Send a new internal message (INSERT into `mensajeria`)
`ConsultaMensajesBandejaEntradaUsuariosCashmanHa`	Fetch inbox messages for a given recipient (visible only) ordered by date
`ConsultarDetallesMensajesBandejaEntradaUsuariosCashmanHa`	Fetch a single message by ID
`OcultarMensajesRecibidos_MensajeriaInternaUsuariosCashManHa`	Archive a message by setting `ocultarmensaje='si'`
`ConsultarNotificacionesRecibidasUsuarios`	Fetch all unread notifications for a user
`ConsultaNotificacionesRecortada_BarraHerramientasPlataforma`	Fetch last 25 notifications for the toolbar bell icon
`OcultarNotificacionesRecibidasUsuarios`	Dismiss a notification by setting `ocultarnotificacion='si'`

Products Management (7 procedures)

Procedure	Purpose
`RegistrarNuevosProductosCashManHa`	Create a new product record
`ModificarProductosRegistradosCashManHa`	Update all product fields
`ActivarProductosCashManHa`	Set product `estado='activo'`
`DesactivarProductosCashmanHa`	Set product `estado='inactivo'`
`ExpirarProductosCashmanHa`	Set product `estado='expirado'`
`ConsultarProductosCashManHARegistrados_Activos`	List active products
`ConsultarProductosDisponibles_NuevosCreditos`	List products available for new credit applications

Platform Reports & Help Desk (3 procedures)

Procedure	Purpose
`RegistroReportesFallosPlataforma`	Submit a bug report ticket
`ActualizacionTicketsReportesFallosPlataforma`	Update ticket status, add staff comment
`ConsultaCompleta_ReportesFallosPlataforma`	List all tickets ordered by report date

Procedure	Purpose
`ReporteCompletoIniciosdeSesionesUsuarios`	Full login history for a specific user ordered by date
`ConsultaDatosGenerales_InicioPlataformaAdministradores`	Dashboard summary statistics for admin home screen

Naming Convention

Procedure names follow a consistent three-part pattern that makes the operation immediately clear:

{Verb}{Domain}{Scope/Qualifier}

Examples:
  Registrar  + NuevosMensajesUsuarios + _MensajeriaCashManHa
  Consultar  + ListadoCreditosClientes + Cancelados
  Actualizar + ConfiguracionCuentas    + AdministradoresConFoto

Verbs used across the codebase:

Verb	Operation
`Registrar` / `Ingreso`	INSERT
`Consultar` / `Consulta`	SELECT
`Actualizar` / `Actualizacion` / `Modificar`	UPDATE
`Eliminar` / `Envio` (to history)	DELETE
`Bloquear` / `Desactivar` / `Cerrar` / `Activar` / `Reactivar`	Status UPDATE
`Anular`	Void/reverse UPDATE

When navigating the 148-procedure codebase, filter by verb prefix first (grep "^CREATE PROCEDURE Registrar") to find all write procedures, then narrow by domain keyword.

Database

Controllers & Routing

Credit Products

CashMan H.A. Stored Procedures: Complete Domain Reference

PHP Call Pattern

Procedures by Domain

Naming Convention

Build docs developers (and LLMs) love

Database

Controllers & Routing

Credit Products

Documentation Index

​PHP Call Pattern

​Procedures by Domain

​Naming Convention

Build docs developers (and LLMs) love

PHP Call Pattern

Procedures by Domain

Naming Convention