OK I'll explain here with code what I am trying to accomplish, please let me know if you understand, I have comments on the code explaining as well, I would really appreciate any feed back on this!
# Start first sql...
my $dbh = DBI->connect...
my $sql = "
SELECT
acc_num,
date,
user1,
user2,
email,
user4,
user5,
user6,
country,
FROM users_info
WHERE date='2009-04-11'
";
my $sth = $dbh->prepare($sql, { RaiseError => 1 });
$sth->execute() or die "$!\n";
my $array_ref = $sth->fetchall_arrayref();
my (@ar_acc_num, $new_array_ref);
foreach my $vals (@$array_ref){
my ($acc_num, $date, $user1,$user2,$email,$user4,$user5,$user6,$countr
+y) =@$vals;
#Now edit data here before saving it:::
$data=~s/2000/2009/g;
$user=~s/joe/Joe/g;
#get edited data to the new array_ref::: Is this possible, is it how
+it is done?
push @{$new_array_ref}, $date, $user1,$user2,$user3,$user4,$user5,$use
+r6,$user7;
push @ar_acc_num ,$acc_num;
# I will need to push $date into an array as well, but one problem at
+a time.
}
# here I have part of the data I need, print for test
print Dumper ($new_array_ref);
#@ar_acc_num -> here I have all the acc numbers I need to go after on
+the second table:::
# Start second sql...
my $dbh_b = DBI->connect...
my $values =
join(","
,map { $dbh_sec->quote($_) } @ar_acc_num
);
my $sql_b = "
SELECT add,
acc,
date,
user3,
phone,
city,
state,
user7,
reg
FROM all_info
WHERE date='2009' /* # it should be the value of $date pu
+shed previously but for now I am using static year value of 2009 */
AND acc IN ($values)
";
my $sth_b = $dbh_b->prepare($sql_b, { RaiseError => 1 });
$sth_b->execute() or die "$!\n";
my $array_ref_b = $sth_b->fetchall_arrayref();
my $new_array_ref_b;
foreach my $vals_b (@$array_ref_b){
my ($add, $acc, $date,$user3,$phone,$city,$state,$user7,$reg) =@$vals_
+b;
#Now edit data here before saving it:::
$city=~s/new york/New York/g;
$reg=~s/usa/USA/g;
#get edited data to the new array_ref::: Is this possible, or how can
+ this be done?
push @{$new_array_ref_b}, $add, $acc, $date,$user3,$phone,$city,$state
+,$user7,$reg;
}
# print for test
print Dumper ($new_array_ref_b);
my $header = [
"Acc. Num",
"Date",
"User 1",
"User 2",
"User 3",
"User 4",
"User 5",
"User 6",
"User 7",
"Add",
"Acc",
"Email",
"Phone",
"City",
"State",
"Country",
"Regular",
];
# Here is another question and problem I am having, as you can see I n
+eed to rearrange the columns to mach the order on $header, and
# $email, $country are coming from the first query and $user3 and $use
+r7 fro mthe second, I need to reorganize the data to have some consis
+tency
# when processing the rest of the code to be printed on this report. H
+ow can this be done?
# Here I need to add @{ $new_array_ref } to @{ $new_array_ref_b } to
+contain all the data I need to print a report and
# make sure that if data from the first table query has more data than
+ the results from the second table dont generate any error, if it is
+it will
# insert a '' to the value and it will print nice on the report
for my $i (0..$#$new_array_ref) {
push @{$new_array_ref->[$i]}, @{ ref($new_array_ref_b->[$i]) eq 'A
+RRAY' ?
$new_array_ref_b->[$i] : [(' ') x 3] };
}
# This will add some data for the table header when printing this repo
+rt that will be done in pdf later
unshift @{ $new_array_ref }, $header;
##Now if all its done OK go here:
main_table($pdf, \@{$new_array_ref});
$pdf->saveas();
sub main_table {
my $pdf = shift;
my $data = shift;
do more stuff......
}
Thanks! |