program logic help needed

multi-array unique check

[slipp]

OK.

I need some help to get the logic together for a small app im trying to write.

I get a CSV file everyday that holds account#; account balance; and owners last name. Some account # are listed three times because they hold different currencies. Also some individuals have multiple accounts.

I want to chop this down to an array whose key would be the individual account owner and the matching value would be the total value of all their accounts listed as one value.

<?PHP

$file_handle = fopen("C:\\Prime\\Import\\work\\balances.csv", "rb");

while (!feof($file_handle) ) {

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);
print $parts[0] . "$parts[2] " . "$parts[3] " . ($parts[5] * $parts[6]) . "<br />";
}

fclose($file_handle);

?>

So far Im exploding each line of the csv file and I'm just printing it to screen right now to see that I am pulling the right data. In the code above ($parts[5] * $parts[6]) is converting the account value into USD. $parts[0] . "$parts[2] " joins the two separate parts of the account number. And "$parts[3] " is the account owners last name. How can I chop this down to an array in which each user corresponds to the total value of all their accounts on the CSV file?

[jatar_k]

my original thought is that listing all acounts as a total would be wrong, you said there were different currencies, how would you convert those?

spot rate, monthly average rate and those would actually be incorrect as the currency hasn't been converted so their total would change.

You should list them all as totals by currency

though all this depends on what you are actually doing.

[slipp]

In the code above ($parts[5] * $parts[6]) is converting the account value into USD.

The CSV file provides the conversion rate. In my output of what I have already everything is in USD.

For what I am trying to do I have to have the total of all accounts for each user.

I was hoping there was a clever way to do this with a multi-array. There probably is but now I'm thinking it would be way easier to just write each line to a SQL table and do th ecalculations there...

[jatar_k]

this file have a variable number of fields then I assume

is there a maximum?
is there a recognizable pattern that you can apply?

how do you identify the currency?

[slipp]

160,BT540,62045,Slipp - F,USD,1,436.34

Here is an example line from the .csv for you. if you explode that on the commas. Then [0].[2] is the account. The first word in [3] is the Owner name. [4] Tells you what the currency is. [5] is the conversion rate to USD. And [6] is the account value. The currency conversion is easy because no matter what currency I get the info in ([5]*[6])= (Acc. Value in USD)

[jatar_k]

so if someone has multiple accounts it is sent as multiple rows?

then you could search the array for the name and add a new amount to their total

are they successive?

if so then you could check the last iteration value and add to the previous total if the name matches

I worry that if all you have is last name and first initial you may have issues

joan smith
john smith

these would be the same person

do they have a client id? what is the number in [1]?

[adwatson]

Perhaps I'm missing something, but wouldn't this work:

Use an array. Index by the person's account number, or perhaps "name-account number", etc. This is the key.

As you loop through the file, the value is the converted value of the currency, plus the current value stored in the array for that person. This basically totals each account as you step through.

Once you're done, you should have the array, indexed by account/name, with each value being the sum of any values encountered for that account.

The only problem I see is if a user has several accounts with different account numbers (which you said some do) - in that case you'll need some other data to combine accounts with.

[slipp]

Here is what I have so far...

<?php

/* Clear out old values*/
$pgsqldb->query("DELETE FROM \"Liq_Val\"") or die("FUBAR");
$pgsqldb->query("SELECT setval('public.\"Liq_Val_Seq\"', 0)") or die("FUBAR");

/* Get the CSV file and chop it down to Account #; Trader Name; and Value in USD */
$file_handle = fopen("C:\\Prime\\Import\\work\\balances.csv", "rb");

while (!feof($file_handle) ) {

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);

$Act=$parts[0].$parts[2];
$ActVal=($parts[5] * $parts[6]);

$TraderName=$parts[3];
$Name = split('[ ]+', $TraderName);
$TraderName = $Name[0];

$pgsqldb->query("INSERT INTO \"Liq_Val\" VALUES('$Act', '$TraderName', '$ActVal')") or die("FUBAR");
}

fclose($file_handle);

?>

I was hoping I could get the data all set up and feed it to my application without moving through the database. But I can't see a good way to do it without some sort of crazy multi-array. I think letting pgsql do the calculations will be easier.

As far as the name on the accounts goes my hands are tied on the format. The only ID I actually get off of there is the last name. And the larger app this data is going to be fed uses first letter of fist name; first and last letter of last name. So when I pull my account value sums I'm going to have to tie it to the user based on the account number. I just need the name field to tell who owns what account to add up the different accounts.