How do I do this?
1. Im assuming I would use a stored procedure to run from the report (instead of linking it to an stored SQL) I would link the report to a stored procedure such as:
Procedure: GetCustSSN
Code: Select all
]$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '#RPT_sdate#' AND '#RPT_edate#';
";
nuRunQuery("CREATE TABLE #TABLE_ID# $s");2. As part of the stored procedure, how then can I run my un-encrypt function against the temporary table? and update/change the cust_SSN field? I can put my un-encrypt function in another stored procedure or load it from a file. The function is:
unencryptSSN($encryptedSSN) I currenlty use this to un-encrypt the SSN when I retrieve the records in a search form and display it on the edit form. The field is already in memory at that point. I'm not sure how to do it for reports?
3. Im assuming that my code would do something like this:
1. for each record in my temporary table, get the cust_SSN, run the function $unecryptedSSN=unencryptSSN(cust_SSN), extract the last4 of the $unecryptedSSN using substr(), then update the temporary table with the $last4SSN, Im assuming that I should change my SQL statement above to create a new field (last4ssn) like this:
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN , FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '#RPT_sdate#' AND '#RPT_edate#';
How would I :
1. Write the for loop or while loop to get each record from the temporary table and assign the encrypted cust_SSN field to a variable?
2. Call my unencryptSSN(cust_SSN) function? to unecrypt the SSN and return it to a variable?
3. Write the UPDATE statement to put it back in the temporary table in the last4SSN field?
4. delete this temporary table AFTER the report has run?