Translate Excel formula to PHP calculation meant for HEX conversion to specific decimal value

Question

My USB RFID UHD reader shows the serial number of my Mifare 1K cards as 10 digit hexadecimal number 0025733f4b. This then translates to "11516203" in my current access system.

Android reads the same cards UID as 4b3f7325, which seems to be the reverse of the serial number read by the USB RFID reader.

I would now need to read the 4b3f7325, reverse it to 0025733f4b and then do a calculation which is beyond me and have a result of "11516203".

I would like to do this in PHP.

Idea

I found a HEX to DEC amazing XLS file which has a pretty complicated formula for me.

CELL    -   VALUE

C2      -   0025733f4b
R2      -   =AI2*AW2+AJ2*AX2
T2      -   =AK2*AU2+AL2*AV2+AM2*AW2+AN2*AX2

R2 CALCULATION RESULT = AI2*AW2+AJ2*AX2 = 115
T2 CALCULATION RESULT = AK2*AU2+AL2*AV2+AM2*AW2+AN2*AX2 = 16203

Which gives me "11516203".

---Start of R2 calc---
AI2 = = SUM(16^(ROW(INDIRECT("b1:a"&LEN(Y2)))-1)*(MATCH(LEFT(RIGHT(0&Y2,ROW(INDIRECT("b1:a"&LEN(Y2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs Y2 =MID(C2,5,1)
--------------------
AW2 = 16
--------------------
AJ2 = =SUM(16^(ROW(INDIRECT("b1:a"&LEN(Z2)))-1)*(MATCH(LEFT(RIGHT(0&Z2,ROW(INDIRECT("b1:a"&LEN(Z2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs Z2 =MID(C2,6,1)
--------------------
AX2 = 1
---End of R2 calc---

---Start of T2 calc---
AK2 =SUM(16^(ROW(INDIRECT("b1:a"&LEN(AA2)))-1)*(MATCH(LEFT(RIGHT(0&AA2,ROW(INDIRECT("b1:a"&LEN(AA2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs AA2 =MID(C2,7,1)
--------------------
AU2=4096
--------------------
AL2 =SUM(16^(ROW(INDIRECT("b1:a"&LEN(AB2)))-1)*(MATCH(LEFT(RIGHT(0&AB2,ROW(INDIRECT("b1:a"&LEN(AB2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs AB2 =MID(C2,8,1)
--------------------
AV2=256
--------------------
AM2 =SUM(16^(ROW(INDIRECT("b1:a"&LEN(AC2)))-1)*(MATCH(LEFT(RIGHT(0&AC2,ROW(INDIRECT("b1:a"&LEN(AC2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs AC2 =MID(C2,9,1)
--------------------
AW2=16
--------------------
AN2 =SUM(16^(ROW(INDIRECT("b1:a"&LEN(AD2)))-1)*(MATCH(LEFT(RIGHT(0&AD2,ROW(INDIRECT("b1:a"&LEN(AD2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))
Needs AD2 =MID(C2,10,1)
--------------------
AX2=1

Then I tried to translate the formula from Excel to PHP, and this is where I am now:

<?php
$wrong_hex = "4b3f7325";
echo 'Input HEX from Android = '.$wrong_hex.'<br>';

$done2 = array_reverse(str_split($wrong_hex, 2));
array_unshift($done2, '00');
$true_hex = implode("",$done2);
echo 'True Hex on Mifare1k card (after php reverse): '.$true_hex.'<br>'; // this is where i get the reversed value

echo '<br>Parts of Formula that ive figured out<br>';

$AA2 = $true_hex[6];
$AB2 = $true_hex[7];
$AC2 = $true_hex[8];
$AD2 = $true_hex[9];
$Y2 = $true_hex[4];
$Z2 = $true_hex[5];
$AV2 = 256;
$AW2 = 16;
$AU2 = 4096;
$AX2 = 1;
$AJ2 = '';
$AI2 = '';
$AM2 = '';
$AK2 = '';
$AL2 = '';
$AN2 = '';

echo 'AA2 = '. $AA2 .'<br>';
echo 'AB2 = '. $AB2.'<br>';
echo 'AC2 = '. $AC2.'<br>';
echo 'AD2 = '. $AD2.'<br>';
echo 'Y2 = '. $Y2.'<br>';
echo 'Z2 = '. $Z2.'<br>';
echo 'AV2 = '. $AV2.'<br>';
echo 'AW2 = '. $AW2.'<br>';
echo 'AU2 = '. $AU2.'<br>';
echo 'AX2 = '. $AX2;


// Calculate first part from AI2*AW2+AJ2*AX2, should return 115
// save as $1stpart = $result;
// Then calculate seconds part
// AK2*AU2+AL2*AV2+AM2*AW2+AN2*AX2, should return 16203
// save as $2ndpart = $result;
// combine $endresult = $1stpart . $2ndpart;
// Then i will do PDO Select WHERE idcard = $endresult ?>

Result

Input HEX from Android = 4b3f7325
True Hex on Mifare1k card (after php reverse): 0025733f4b

Parts of Formula that ive figured out
AA2 = 3
AB2 = f
AC2 = 4
AD2 = b
Y2 = 7
Z2 = 3
AV2 = 256
AW2 = 16
AU2 = 4096
AX2 = 1

As I understand, I would need to know how to calculate only 1:**

SUM(16^(ROW(INDIRECT("b1:a"&LEN(Y2)))-1)*(MATCH(LEFT(RIGHT(0&Y2,ROW(INDIRECT("b1:a"&LEN(Y2)))),1),{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"A";"B";"C";"D";"E";"F"},0)-1))

And then I could make some function or something. You can get the XLS here: https://www.i-keys.de/download_free/Hexa.xls To unlock the sheet you'll probably need to use the Execl sheet VBA bypass, I did that way :).

========================== ANSWER =======================================

    <?php
    $wrong_hex = "4b3f7325";
    $done2 = array_reverse(str_split($wrong_hex, 2));
    array_unshift($done2, '00');
    $true_hex = implode("",$done2);
    echo 'From Android : ' . $wrong_hex . '<br>';
    echo 'Reversed TRUE HEX : ' . $true_hex . '<br>'; // this is where i get the reversed value

    $uid_dec = hexdec($true_hex);
    $uid_dec_first = ($uid_dec & 0x0FF0000) >> 16;
    $uid_dec_second = $uid_dec & 0x0FFFF;
    $uid_touse = $uid_dec_first . $uid_dec_second;

    echo 'Your Security ID is '. $uid_touse;
?>

Answer 1

The decimal number that you need for the access control system seems to be the concatenation of the third last byte of the UID (in USB RFID reader output format) represented as 8-bit unsigned decimal integer concatenated with the last two bytes of the UID represented as 16-bit unsigned decimal integer.

Once you have the UID as hexadecimal string $true_hex, you could use

$uid_dec = hexdec($true_hex);

to convert it to a decimal integer. You would then want to take the third lowest byte as the first part:

$uid_dec_first = ($uid_dec & 0x0FF0000) >> 16;

Next, you would take the lower 16 bits of the UID as the second part:

$uid_dec_second = $uid_dec & 0x0FFFF;

Finally, you would concatenate both parts to get the desired output number. This depends on how the Excel sheet concatenates R2 and T2 (which I did not find in your question). Typically, this would be either

$uid_accesscontrol = sprintf("%03d%05d", $uid_dec_first, $uid_dec_second);

or simply

$uid_accesscontrol = $uid_dec_first . $uid_dec_second;

Note that on Android, you could easily do the conversion as:

byte[] uid = tag.getId();
int uid_first = uid[2] & 0x0FF;
int uid_second = ((uid[1] & 0x0FF) << 8) | (uid[0] & 0x0FF);
String serial = String.format("%03d%05d", uid_first, uid_second);