How to make a counter field restart at midnight?

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
MASTERAL
User
Posts: 15

How to make a counter field restart at midnight?

Post by MASTERAL »

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.


kirondedshem
User
Posts: 642

Post by kirondedshem »

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;
}

}


MASTERAL
User
Posts: 15

Post by MASTERAL »

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


kirondedshem
User
Posts: 642

Post by kirondedshem »

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


MASTERAL
User
Posts: 15

Post by MASTERAL »

Regards

Ok thank you very much for your suggestions I will take them into account, I will analyze your code to apply your observations.

Thank you very much.


sangnandar
User
Posts: 980

Post by sangnandar »

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

IMHO, this line will be susceptible against concurrency problem, that is when 2 or more records are inserted at the same time their numexp value will ended up the same (which is unexpected).

Another approach would be to use id (autoincrement) value as the basis, that is:
$yesterday = ew_ExecuteScalar("select max(id) from my_table where date=yesterday"); // a fixed value.
$today = $yesterday - $rsnew["id"]; // add -1 if you want to start from 0 .

Autoincrement id is not available before insert, so you need to update numexp in Row_Inserted().

This way you can avoid concurrency problem since autoincrement value will never be the same even if they arrive exactly at the same time.


MASTERAL
User
Posts: 15

Post by MASTERAL »

Thanks

I'm try the solutions.

:)


Post Reply