|
M
mumtaz author
Hello all,
how to create a function to be able to automatically generate id with the following criteria:
[Bbbcccyynnnn]
BBB = number of branches in the three-digit
ccc = number of districts in the three-digit
yy = two digit year from the registration date
nnnn = serial number
table structure as well as examples of expected results as below
|#cus---------regdate--------branch----------name-------districts
001002090001--05-06-2009-----1---------------fulan-------2
002016100001--06-06-2010-----2---------------filin-------16
001003100001--23-06-2010-----1---------------fulun-------3
001003100002--15-07-2010-----1---------------felen-------3
002017100001--14-08-2010-----2---------------folon-------17
002016100002--16-08-2010-----2---------------fulanah-----16
Please enlightenment from the masters as well. Thank previously
this my trial before record added event
$sql="select max(substr(`#CUS`,9)) as mx,
lpad(`branch`,3,0) as branch,
lpad(`dist`,3,0) as dist,
right(year(`reg`),2) as year
from customer
where substr(#cus,1,3)=lpad(`branch`,3,0) and
substr(#cus,4,3)= lpad(`dist`,3,0) and
substr(#cus,7,2)=right(year(reg),2)
order by mx";
$rs=CustomQuery($sql);
$data=db_fetch_array($rs);
$str=$data["branch"];
$str1=$data["dist"];
$str2=$data["year"];
$str3=($data["mx"]+1);
$values["#cus"]="$str$str1$str2".str_pad($str3, 4, 0, STR_PAD_LEFT);
Its running. But branc & district id is not change.
I think there are constraints in the WHERE clause in SQL.
How to make a clause whrere it could take from the data we just entered?
And maybe with these tricks I can perfect the customer code
[color="#0000FF"][size="4"]solved code[/size]
$sql="select LPAD(`branch`,3,0) as branch, LPAD(`dist`,3,0) as dist,RIGHT(YEAR(`reg`),2) as year,max(substr(`#CUS`,9)) as mx
from customer
where substr(`#CUS`,1,3)=LPAD('$values[branch]',3,0) and
substr(`#CUS`,4,3)=LPAD('$values[dist]',3,0) and
substr(`#CUS`,7,2)=RIGHT(YEAR('$values[reg]'),2)
order by mx";
$rs=CustomQuery($sql);
$data=db_fetch_array($rs);
$str= sprintf('%03s',$values['branc']);
$str1=sprintf('%03s',$values['dist']);
$str2=substr($values['reg'],2,2);
$str3=($data["mx"]+1);
$values["#CUS"]="$str$str1$str2".str_pad($str3, 4, 0, STR_PAD_LEFT);
|
|