Counter field restart at midnight

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

Counter field restart at midnight

Postby MASTERAL » Sun Jan 14, 2018 8:54 am

Regards

I have a project that generates an automatic file number in the numexp field.

The table has the following fields:

idgen (autoincremental)
numexp (int)
datefld (date)
year (int)

How can I make the numexp field automatically at night (at midnight) the counter will restart and start again at 0.

Any ideas? Thank you very much.
MASTERAL
 
Posts: 3
Joined: Mon Jan 01, 2018 10:50 am

Re: Counter field restart at midnight

Postby kirondedshem » Sun Jan 14, 2018 3:35 pm

You need to create afunction in global code, which returns the number for any file to be inserted. In that fucntion you need to query for the latest number for a given day and increment it by one. SOmething like

NOTE:Assuming datfld, is the date and time when the document is saved, if its not then you need to add one.
NOTE:change my_table to your table name
so paste this in global code and call it later.

function GenerateNewNumExp()
{

//get latest number today(a day starts at midnight)
//so we et all documents added today and get latest number

$latest_num_today = ew_ExecuteScalar("select numexp from my_table where DATE(datefld) = DATE(now()) order by numexp DESC LIMIT 1");

//if there was alreadya document entered today ten get net number
if($latest_num_today)
{
//next num will be previous plus one
$next_num = $latest_num_today + 1;
return $next_num;
}
//if there was no document inserted today yet then start at zero
else
{
return 0;
}

}
kirondedshem
 
Posts: 308
Joined: Sat Jan 28, 2017 7:13 pm

Re: Counter field restart at midnight

Postby MASTERAL » Sun Jan 14, 2018 10:56 pm

Thanks, I will try and inform you.
MASTERAL
 
Posts: 3
Joined: Mon Jan 01, 2018 10:50 am

Re: Counter field restart at midnight

Postby MASTERAL » Mon Jan 15, 2018 5:26 am

Greetings.

Using the idea of your code, apply it as follows and it !! worked !!!.

// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {

// Enter your code here
// To cancel, set return value to FALSE

$AUTO = ew_ExecuteScalar("select turnos from contaturno where fecha = DATE(now()) order by turnos DESC LIMIT 1");
//if there was alreadya document entered today then get next number
if($AUTO)
{
//next num will be previous plus one
$Nnext=$AUTO + 1;
$Result = ew_Execute("UPDATE contaturno SET turnos=$Nnext WHERE idcontaturno= 1");
$format = $AUTO;
$ids = sprintf($format, $Nnext);
$rsnew["TURNO"] = $ids;
}
else
{
$Result = ew_Execute("UPDATE contaturno SET turnos = 1, fecha = DATE(now()) WHERE idcontaturno= 1");
$AUTO = ew_ExecuteScalar("select turnos from contaturno where fecha = DATE(now()) order by turnos DESC LIMIT 1");
$Nnext=$AUTO + 1;
$Result = ew_Execute("UPDATE contaturno SET turnos=$Nnext WHERE idcontaturno= 1");
$format = $AUTO;
$ids = sprintf($format, $Nnext);
$rsnew["TURNO"] = $ids;
}
return TRUE;
}

Thank you very much for your help
MASTERAL
 
Posts: 3
Joined: Mon Jan 01, 2018 10:50 am

Re: Counter field restart at midnight

Postby kirondedshem » Mon Jan 15, 2018 1:41 pm

Also ensure you run your code ONLY and ONLY when you are sure the record is going to be inserted, coz i see you are doing alot of updates before the record even insert, SO suppose you have some validations that fail or some error that ocuur then you will have run the previous updates for nothing.SO Its good design to do one of the following.

1. Either move all this code to row_inserted where you are sure the record has inserted so you run all those updates.
2. IMPORTANT: for cases where you have a bunch of queries running and you would like them to all run successfully. Then ensure that you close them in an sql transaction(google sql transactions), but basically this ensures all queries run or non at all, that way you atleast close that gap eg.

*************start a transaction before any of the queries run in row_inserting****************
// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
// Enter your code here
// To cancel, set return value to FALSE
//start sql transaction
ew_Execute("START TRANSACTION");

//run a bunch of queries before returning true

//since transaction starts before returning true, even inserting the main record is also part of it
return TRUE;
}

*************start a transaction before any of the queries run in row_inserting****************



*************commit the transaction only after you are sure everthing should have run by now****************
// Row Inserted event
function Row_Inserted($rsold, &$rsnew) {
//echo "Row Inserted"

//save everything at this pointat this point
ew_Execute("COMMIT");

}

*************commit the transaction only after you are sure everthing should have run by now****************



NOTE: If you are using mysql, ensure that the tables are of innoDB which supports transaction, so if they are not then change that immediately as MyIsam does not support transactions of force key constraints
kirondedshem
 
Posts: 308
Joined: Sat Jan 28, 2017 7:13 pm


Return to General Discussion (PHPMaker)