This guide or "tailor's box" for the developers of PHPRunner, are some small things that are not written every day but that are frequent and it is always good to have a place where you can go to remember how they are done.
The topics are obtained from the queries that come to me from different programmers who are normally starting in PHPRunner and who ask me how it can be done ……
To make the examples I have based on this data model that I explain.
The model is not real, but it wants to collect the data of a Person, who depending on his situation, the Administration will provide him with a series of aids and subsidies. All this will depend on the classification of this Person.
(1) .- Master Table of the Person's data and its classification. Only if she is a Woman can she inform if she is pregnant or not.
(2) .- They are the aids / benefits that this Person receives. As the model indicates, the benefits they can receive depends on the Role of the Person.
(3) .- They are the subsidies that the person receives. Only if you are pregnant can you receive a pregnant grant
(4) .- The benefits are related to the Role, but to facilitate the updating and clarity of the data, a multi-value field is defined. The objective is how to act in the lookups with a multivalued field.
(5) .- They are the classifications that Person has. You have to look at the Active field (Active or Low). This field will serve to LOCK (No Add, Edit or Delete) all the records in the History table.
The DEMO of what is scheduled can be seen at: https://fhumanes.com/persona
Requirements, Presentation and Coding.
Although some of the requirements or problems are already raised, I will go on to present the aspect of the application, explain the requirements and the coding that has been done.
(1) .- It is wanted that each person has a State and that according to that state, the text is put in color.
The status is calculated depending on the "Next Review date".
- In term.- When there are more than 15 days to meet the date.
- In renovation.- When there are between 15 and 0 days for the date to expire.
- It is expired.- When the revision date has already passed.
This has been fixed with:
- Changing the SQL of the table "Persona".
SELECT
idpersona,
NombreyApellidos,
persona_genero_idpersona_genero,
persona_embarazo_idpersona_embarazo,
persona_rol_idpersona_rol,
persona_activo_idpersona_activo,
proxima_revision,
DATEDIFF(proxima_revision,now()) dias_diff,
CASE
WHEN DATEDIFF(proxima_revision,now()) > 15 THEN 'En plazo'
WHEN DATEDIFF(proxima_revision,now()) <= 15 and DATEDIFF(proxima_revision,now()) >= 0 THEN 'En renovación'
WHEN DATEDIFF(proxima_revision,now()) < 0 THEN 'Está vencido'
END Estado
FROM persona
- In the "Estado" field it has been defined to be of the "Custom" type and has been coded
// Colorear el estado
if ($data['dias_diff'] <= 15 && $data['dias_diff'] >= 0) {
$value= '<p style="color:orange;">'.$value.'</p>';
}
if ($data['dias_diff'] < 0) {
$value= '<p style="color:red;">'.$value.'</p>';
}
(2) .- It is required that only the "Persona" that is "Activo", can be Add, Edit and Delete.
Several examples have been made for this type of lock, but the most efficient is:
- In the event "after application initialized", I have put this code
if ( $_REQUEST['mastertable'] == 'persona') {
// Consultamos si el registro Master está ACTIVO
$key1 = $_REQUEST['masterkey1'];
$sql="SELECT persona_activo_idpersona_activo activo FROM persona WHERE idpersona = $key1";
$rs=db_query($sql,$conn);
$data=db_fetch_array($rs);
if ($data['activo'] == 2) { // Consulta si "persona está de baja
$_SESSION['historico_add'] = false; // No puede haber Add y DELETE
} else {
$_SESSION['historico_add'] = true; // Sí puede haber Add y DELETE
}
}
What it does is use the session variable "historico_add" to indicate whether the record "Historico" can be A, E, D or not. This is done by checking if the "mastertable" is "Persona" and capturing the "Activo" information from the "Master" record being processed.
- In the event "Get Table Permissions" coding:
if ($_SESSION['historico_add'] == false ) {
$permissions = str_replace("A", "", $permissions); // Quita "A"
$permissions = str_replace("D", "", $permissions); // Quita "D"
$permissions = str_replace("E", "", $permissions); // Quita "E"
}
return $permissions;
(3) .- The requirement is that when doing Add or Edit, in the table of "subvencion", the subsidy for pregnant women only appears for "people" who are "pregnant"
- This is another method of establishing conditions depending on the values of the "master" without having to drag the fields in the presentation of the pages. We include the code in the “Process Record Values” event, both in Add and in Edit:
$idpersona = $values['persona_idpersona']; // Recogemos el Id de la persona
$sql="SELECT persona_embarazo_idpersona_embarazo FROM persona WHERE idpersona = $idpersona";
$rs=db_query($sql,$conn);
$fila=db_fetch_array($rs);
// Pongo el valor de la clave (N,S) (1,2) en variable de "proxy"
$pageObject->setProxyValue("clave_embarazo", $fila['persona_embarazo_idpersona_embarazo']);
What it does is collect the value of "Pregnant" from the "person" record and leave it in the variable "clave_embarazada" and pass it to the javascript environment.
- And in the event of "Javascript OnLoad Event" this encoding is included:
// alert(proxy['clave_embarazo']);
var ClaveEmbarazo = proxy['clave_embarazo']; // Recoger variable de "proxy"
var ctrlPersona_subv_embarazada = Runner.getControl(pageid, 'importe_embarazada'); // Acceder al campo de subv. de embarazada
// Si no está embarazada no se "abre" el campo
if (ClaveEmbarazo != '2') {
ctrlPersona_subv_embarazada.setValue(0);
ctrlPersona_subv_embarazada.makeReadonly();
}
Other requirements
In this case, the requirement is that the dropdown of "Prestaciones" is dependent on the attribute "rol" that is in the "master" "persona" record.
In this case, to maintain the relationship between "Rol" and "Prestación" we have used a multi-value field, to facilitate the reading and maintenance of this relationship:
To be able to use this information in 2 dependent lookups "role" and "provision" you have to normalize the data model and that is done with a view of PHPrunner with the following code:
SELECT
p.`idpersona_prestacion`,
p.`Titulo`,
p.`Sigla`,
r.`idpersona_rol`
FROM ( persona_prestacion p join persona_rol r )
where ( find_in_set(r.`idpersona_rol`, p.`persona_rol_idpersona_rol`) > 0 )
When the “person” record is being added or edited, the “pregnant” field should only be able to be reported if it has been indicated that the sex is “woman”.
This is done by coding in the "Javascript OnLoad Event" event:
// De entrada, el campo de Embarazo se oculta o se pone en solo Lectura
var ctrlGenero = Runner.getControl(pageid, 'persona_genero_idpersona_genero');
var ctrlEmbarazo = Runner.getControl(pageid, 'persona_embarazo_idpersona_embarazo');
// ctrlEmbarazo.hide();
// ctrlEmbarazo.setDisabled();
ctrlEmbarazo.makeReadonly();
// Cuando se introduce el valor de género.
ctrlGenero.on('change', function(e){
if (this.getValue() == '2'){
// ctrlEmbaraso.show();
// ctrlEmbarazo.setEnabled();
ctrlEmbarazo.makeReadWrite();
}
});
This is all I wanted to explain in this guide. I have put it number 1, because my intention is to collect new questions and explain them in this type of guide and example.
In my portal are all the sources that you may need to install the example on your PC’s and as always, for any doubt or need for explanation, contact me through my email [email="fernandohumanes@gmail.com"]fernandohumanes@gmail.com[/email]
fhumanes author