2. PHPRunner General questions
  3. [SOLVED] Custom Auto Sequence Number Issue
This topic is locked
[SOLVED]

Custom Auto Sequence Number Issue

 		9/3/2021 11:14:43 PM
PHPRunner General questions
A
 Abul author

I have follwoing table condition.

  1. Master Table “Workorder” has fields, woid (AI), sampletype(FK), client(FK).
  2. Child Table “Sample” has fields splid (AI), LabId (unique varchar), sampleLabel (varchar), woid (FK)
  3. Independent Table “Client” has fields, clid(AI), clientno(unique Char).

Condition: Client has no login option. Admin alone will enter all requests from clients.

Example: Let say Client id=1, Clientno=AR. SampleTypeid = 1 which is LT as character.

Aspiration: I added a new workorder in Master table “workord” for “client=1” whose “Clientno=AR” successfully. The “clint(FK)” on “workord” add page has selected from Lookup of “Client” table. Then open child sample Inline add form from Master list (workord) to add new samples. Now on sample Add/Inline Add page, I like to enter only “sampleLable” field. After submit app should generate automatic sample sequence number in “LabId” field for each sample with Clientno + sampletype + date function such as,
ARLT20210831001
ARLT20210831002
ARRT20210901001
ARRT20210901002
And so on.
When I have different client with different clientno (SR) and different sampletype (FT) then the sample Labid should be
SRFT20210831001
SRFT20210831002
SRMS20210901001
SRMS20210901002
And so on.

My code: To accomplish this logic I have put following code in sample “Before record added” event.

function BeforeAdd(&$values, &$message, $inline, &$pageObject)
{
$data = $_SESSION["sample_masterkey1"]; // to get masterkey (workorder) in session
$rs=DB::Query("SELECT CONCAT(client.clientno,sampletype.sampletype) AS a
FROM
sample
INNER JOIN workord ON sample.woid = workord.workordid
INNER JOIN client ON workord.client = client.clientid
INNER JOIN sampletype ON workord.sampletype = sampletype.sampletypeid
WHERE sample.woid = '".$data."'"); // to get clientno (AR) + sampletype (LT) fields and save in array
$data1 = db_fetch_array($rs);
$sql="select max(substr(labid,13,5)) as mx from sample where substr(labid,9,2)=month(now()) order by mx";
$rs1=CustomQuery($sql);
$data2=db_fetch_array($rs1);
if(!$data1)
{
$str= 0;
}
else
{
$str=$data1['a']; // I think I have logic issue at this spot but I cannot resolve.
$str2=date("Ymd");
$str3.=($data2["mx"]+1);
$values["labid"]="$str$str2".str_pad($str3, 3, 0, STR_PAD_LEFT);
}
return true;
;
}

Its working very fantastic but issue is that if the records are totally empty (!data1) for a client then the “LabID” field for the very first sample entry left blank. However, from the second rows works just like a rocket going to mars. No duplication value errors are thrown. I understand my code is totally correct to accomplish my goal, but I think there is a logic issue causing left blank LabID field for only first entry. I think since, the SESSION just started, and it is not get the Client and sampletype value yet until entered into the table.
Any idea to resolve this? Thanks, and appreciate my dear gigs.

A
 		acpan 9/4/2021

Not sure what you are trying to say, but i see your DB Query syntax mixing and there is some error in it and likely no relation with timing of session value.

Some errors in your statement:

  1. $str3 will throw error because it started with dot = ( .= )
  2. CustomQuery is deprecated. Use DB::Query.
  3. May not be able to get the data without while loop. Use while( $data = $rs->fetchAssoc() ) instead of db_fetch_array($rs); It is better to follow the manual QB Query. So when there is error, you can tell by comparing with the manual.

Also make sure both your queries return only 1 record (use ORDER BY and LIMIT 0,1), else it may give you unpredictable result.

You can try to use this:

// To get masterkey (workorder) in session
$master_key = $_SESSION["sample_masterkey1"];

$sql1 = "SELECT CONCAT(client.clientno,sampletype.sampletype) AS a
FROM sample INNER JOIN workord ON sample.woid = workord.workordid
INNER JOIN client ON workord.client = client.clientid
INNER JOIN sampletype ON workord.sampletype = sampletype.sampletypeid
WHERE sample.woid = '$master_key' ";

// echo the sql1 to verify if session value is captured, if empty session value
// means you did not assign the session value properly before you reach this page:
// echo $sql1;
// exit;

// To get clientno (AR) + sampletype (LT) fields and save in array
$str=0;
$rs1 = DB::Query($sql1);
while( $data1 = $rs1->fetchAssoc() )
{
$str=$data1['a'];
}

if ($str != 0 )
{
$str2=date("Ymd");
$str3 = "";
$sql2 = "select max(substr(labid,13,5)) as mx from sample where substr(labid,9,2)=month(now()) order by mx";
$rs2 = DB::Query($sql2);
while( $data2 = $rs2->fetchAssoc() )
{
$str3 =($data2["mx"]+1);
}
$values["labid"]= "$str$str2".str_pad($str3, 3, 0, STR_PAD_LEFT);
}

return true;
A
 		Abul author 9/4/2021

Thank you so much acpan for taking time in helping me. However, the code you gave me did not work. Rather now the "labid' field on sample add page left blank for all entry. It looks like at the begining of the entry the $master_key = $_SESSION["sample_masterkey1"] value can not be captured as I have checked on my database query. Because sample table has not yet records. I will appreciate your continuous helping please.
A
 		acpan 9/4/2021
  1. So below i asked you to test shows empty session value:
    // echo the sql1 to verify if session value is captured, if empty session value
    // means you did not assign the session value properly before you reach this page:

Is above correct? Can you show where is the first $_SESSION["sample_masterkey1" ] you put, show how and where you assign or get the session var.

  1. Aso explain what you mean by "left blank for all entry" ? left side of all the rows are blank? Or there are rows returned but all the rows have blank data? Or no record shows, they have different meaning. If there is no record shows, just say there is no record shows.


  2. Is your $sql1 trying to select a record that will be in database after you add the record successfully? meaning you are trying to select a record that is not yet added to the database?
A
 		Abul author 9/4/2021

Hi acpan,

Thank you so much for your quick respond. By this time I have done so many tricks on my db. And finally it seems working for me. I found that my sql query in earlier code was not able to pull Clientno and sampletype based on workordid when sample table remain totally empty. This was because my earlier query included sample table.
So I have eliminated the sample table from my earlier query. Instead I have created a view table in database using workorder, client and sampletype tables to capture workordid, clientno and sampletype where workordid = $SESSION[“samplemasterkey1”]. That can be able to get clientno & sampletype in the array what I can use for the next following codes to insert new automatic sequence number (LabID field) for each new sample entry.
Thank you so much for your help. Still I am not sure whether this will works in Production with out any flaws. I will let you know. Here is my revised code for your kind information. Please look into the following codes and let me know any gap. Thanks.

$data = $_SESSION["sample_masterkey1"]; //get masterkey in session

$rs=DB::Query("SELECT
CONCAT(clientno,sampletype) AS a
FROM
vclientsampletype
WHERE
workordid = '".$data."'"); //get client field query

$data1 = db_fetch_array($rs);

$sql="select max(substr(labid,13,5)) as mx from sample where substr(labid,11,2)=day(now()) order by mx";

$rs1=DB::Query($sql);

$data2=db_fetch_array($rs1);

if(!$data1)
{
$str= 0;
}
else
{
$str=$data1['a'];

$str2=date("Ymd");

$str3=($data2["mx"]+1);

$values["labid"]="$str$str2".str_pad($str3, 3, 0, STR_PAD_LEFT);
}
A
 		acpan 9/4/2021

i see that you revert back to the old style. if it works for you, then it is ok. Good luck!
A
 		acpan 9/4/2021

Not sure why you revert to using db_fetch_array($rs) instead of my recommendation while( $data = $rs->fetchAssoc() ) . This is one of the 3 points i asked you to correct.

db_fetch_array($rs) is already deprecated

A
 		Abul author 9/4/2021

Hi acpan,
Thank yoiu so much for your inquery. I tried your code but that did not give me any result rather left the target field "LabID" blank. Please see the screen shot as well as my revise code with yours one.

here is your code I used:

$data = $_SESSION["sample_masterkey1"]; //get masterkey in session

$rs=DB::Query("SELECT
CONCAT(clientno,sampletype) AS a
FROM
vclientsampletype
WHERE
workordid = '".$data."'"); //get client field query

while ($data1 = $rs->fetchAssoc() )
{
$str=$data1['a'];
}
if ($str !=0)
{
$str2=date("Ymd");
$str3="";
$sql="select max(substr(labid,13,5)) as mx from sample where substr(labid,11,2)=day(now()) order by mx";

$rs1=DB::Query($sql);
while($data2 = $rs1->fetchAssoc() )
{
$str3=($data2["mx"]+1);
}

$values["labid"]="$str$str2".str_pad($str3, 3, 0, STR_PAD_LEFT);
}

// Place event code here.
// Use "Add Action" button to add code snippets.

return true;
;
} // function BeforeAdd

and I got this wrong result with target field "LabID" left blank.

img alt

However, I dont get any error but got the right result as per my goal with my revised code as mentioned in my last reply above. Please see the screen shot below.

img alt

A
 		acpan 9/5/2021

This is clearer with picture.

  1. The sample i showed you is not much different, except it isolates the 2 queries. The problem with your version is if query 1 is empty, you still execute the next query - totally unecessary and bad design flow and made the final result dependant on the 2 queries.

My version test the first query, and if empty, exit immediately; -- this is what you want.

  1. The problem why my version does not work is: You have omitted 1 important variable in my previous example =>

    // To get clientno (AR) + sampletype (LT) fields and save in array
    $str=0;

With this omitted, the final part WILL NOT be executed, this is why you see LabID is empty.

  1. You should NOT use the command that is deprecated. Period. Your goal will becomes ghost later.


  2. There is also a reason why I changed this:
    $data = $_SESSION["sample_masterkey1"]; =====> $master_key = $_SESSION["sample_masterkey1"];



$data is used as ARRAY in many places. You should avoid naming your variable as $data, to prevent overwrite of PHPRunner's arrays in some events codes if you are not careful, as a good practice.
A
 		acpan 9/5/2021

It is better to assign $str this way to prevent ambiguity, change this:

$str=0; === Change to ==> $str="";

and

if ($str != 0 ) === change to ==> if ($str != "" )
A
 		Abul author 9/5/2021

Hi acpan,
Thank you so much for your nice clarification. My very early code where 1st query actually did not get the Clientno + Sampletype info as I am interested due to query design error I found. That's why I have changed the target query from view table rather than actual tables.

I have revised my code as per your suggession and yes now it is working as per my goal. Thank you so much. Here is my final revised code and the result screen shot.

$master_key = $_SESSION["sample_masterkey1"];

$sql1 = "SELECT CONCAT(clientno,sampletype) AS a
FROM
vclientsampletype
WHERE
workordid = '$master_key' ";

// echo the sql1 to verify if session value is captured, if empty session value
// means you did not assign the session value properly before you reach this page:
// echo $sql1;
// exit;

// To get clientno (AR) + sampletype (LT) fields and save in array
$str="";
$rs1 = DB::Query($sql1);
while( $data1 = $rs1->fetchAssoc() )
{
$str=$data1['a'];
}

if ($str != "" )
{
$str2=date("Ymd");
$str3 = "";
$sql2 = "select max(substr(labid,13,5)) as mx from sample where substr(labid,11,2)=day(now()) order by mx";
$rs2 = DB::Query($sql2);
while( $data2 = $rs2->fetchAssoc() )
{
$str3 =($data2["mx"]+1);
}
$values["labid"]= "$str$str2".str_pad($str3, 3, 0, STR_PAD_LEFT);
}

img alt

A
 		acpan 9/5/2021

Ok, great! I removed this since it is not necessary anymore.
A
 		acpan 9/5/2021

Okay, it works now for you as above, happy coding!

A
 		Abul author 9/5/2021

Hi acpan,
Yes both are working good. Thank you so much. Happy coding!
A
 		acpan 9/5/2021

Just a disclaimer: I am randomly assisting on debugging that caught my interest at the right time, whenever i am free to drop by.

For urgent or more complicated cases, especially cases where you think you might need hand-holding, Xlinesoft's professional service team is definitely a go-to contact for such dedicated support and in-depth debugging :)

happy coding!