[ic] Data tag not returning result
Paul Jordan
paul at gishnetwork.com
Wed May 12 23:24:07 EDT 2004
Tim Good [tim.g at edsd.com] wrote:
>> -----Original Message-----
>> From: interchange-users-bounces at icdevgroup.org
>> [mailto:interchange-users-bounces at icdevgroup.org] On Behalf Of Paul
>> Jordan Sent: Wednesday, May 12, 2004 12:45 PM
>> To: interchange-users at icdevgroup.org
>> Subject: RE: [ic] Data tag not returning result
>>
>>
>> interchange-users-bounces at icdevgroup.org wrote:
>>>> -----Original Message-----
>>>> From: interchange-users-bounces at icdevgroup.org
>>>> [mailto:interchange-users-bounces at icdevgroup.org] On Behalf Of
>>>> Paul Jordan Sent: Wednesday, May 12, 2004 11:16 AM
>>>> To: interchange-users at icdevgroup.org
>>>> Subject: RE: [ic] Data tag not returning result
>>>>
>>>>
>>>> Tim Good [draco at edsd.com] wrote:
>>>>> Hello list,
>>>>>
>>>>> (mysql 3.23.58 on Redhat 8.0 non threaded perl 5.8.2 interchange
>>>>> 5.2 stable) I have a custom table I have added to my database
>>>>> "delivery_schedule". In the order_view page, in admin, I would
>>>>> like to pull data from this table
>>>>>
>>>>> according to the order_number being viewed. I have a field
>>>>> "order_number" in the delivery_schedule table which get the
>>>>> order_number from transactions when the order is placed.
>>>>>
>>>>> I have tried several different approaches to solve this task.
>>>>> However, none are working. What is the best way to do this?
>>>>>
>>>>> Thanks much,
>>>>> Tim
>>>>>
>>>>
>>>>
>>>> Next time include code we can help fix.
>>>>
>>>>
>>>> [query
>>>> ml=100
>>>> type=list
>>>> sp="@@MV_PAGE@@"
>>>> sql=|
>>>> SELECT foo, bar
>>>> FROM delivery_schedule
>>>> WHERE order_number = '[however your getting the
>>>> order number]' |]
>>>> [on-match]
>>>> Delivery Detial:
>>>> [/on-match]
>>>> [list]
>>>> Delivery Schedule #[sql-increment]: [sql-bar]<br>
>>>> [/list] [no-match] Order is not yet in transit
>>>> [/no-match]
>>>> [/query]
>>>>
>>>>
>>>> ----------
>>>>
>>>> [however your getting the order number] is however you can grab the
>>>> order number on that page, maybe a CGI arg, or a value, or
>>>> scratch, i dunno, you'll have to see what it is giving you.
>>>>
>>>> HTH
>>>> Paul
>>>>
>>>>
>>> Paul,
>>>
>>> Thank you for the response. I was using the code below:
>>>
>>> [query sql="select charge from delivery_schedule where order_number
>>> = [loop-data transactions order_number]" type=list]
>>> [if-sql-param charge]
>>> [sql-param charge]
>>> [/if-sql-param]
>>> [/query]
>>>
>>> However, I need to use the results in later areas eg. [total_view
>>> subtotal="[loop-data transactions subtotal]" install="[loop-data
>>> transactions install_chrg]" delivery="[sql-param charge]" ]
>>> Total_view is a custom Usertag I developed to return different
>>> totals depending on view and charges. For instance weither or not
>>> there is a delivery charge. I have tried to wrap the above query in
>>> [seti][/seti] tags then use [scratch foo] to return in
>>> delivery="[scratch foo]". This works once but when I call it later
>>> it dosen't.
>>>
>>> Then I tried:
>>> [perl table='delivery_schedule']
>>> $Tag->data("delivery_schedule", "charge", "[loop-data transactions
>>> order_number]"); [/perl] This returns nothing.
>>>
>>> Also tried to have the query tag ecapsulate the whole area where
>>> [sql-param charge] is needed. However, if there are no results the
>>> "whole area is blank".
>>>
>>> Any ideas?
>>
>>
>> I am not following where and how the UserTag is being used,
>> and how many times, but here is a guess:
>>
>> [tmpn total_charge]0[/tmpn]
>>
>> [query sql="select charge
>> from delivery_schedule
>> where order_number = '[loop-data transactions
>> order_number]'
>> " type=list]
>> [list]
>> [if-sql-param charge]
>> Line item charge: [calc]
>> my $charge =
>> q{[sql-param charge]};
>>
>> $Scratch->{total_charge} += $charge;
>> return $charge;
>> [/calc]
>> [/if-sql-param]
>> [/list]
>> [/query]
>>
>> ....
>>
>> Total Charge:
>> [total_view subtotal="[loop-data transactions subtotal]"
>> install="[loop-data transactions install_chrg]"
>> delivery="[scratch total_charge]" ]
>>
>>
>> Or am I not understanding something?
>>
>> Paul
>>
>>
> Paul,
>
> Sorry for lack of details. Let me try and clarify:
>
> I have a custom Usertag:
> UserTag total_view Order subtotal install damage
> delivery deposit UserTag total_view PosNumber 5
> UserTag total_view addAttr
> UserTag total_view Routine <<EOR
> sub {
> my ($subtotal, $install, $damage, $delivery, $deposit) = @_;
> my $tmp;
>
> $tmp = 0;
> # If subtotal value exists, format it for totaling.
> if ($subtotal && !($subtotal =~ /^\$/)) {
> $tmp += $subtotal;
> }
> elsif ($subtotal && ($subtotal =~ /^\$/)) {
> $tmp += substr($subtotal, 1, length($subtotal)-1);
> } # If install value exists, format it for totaling.
> if ($install && !($install =~ /^\$/)) {
> $tmp += $install;
> }
> elsif ($install && ($install =~ /^\$/)) {
> $tmp += substr($install, 1, length($install)-1); }
> # If damage value exists, format it for totaling.
> if ($damage && !($damage =~ /^\$/)) {
> $tmp += $damage;
> }
> elsif ($damage && ($damage =~ /^\$/)) {
> $tmp += substr($damage, 1, length($damage)-1); }
> # If delivery value exists, format it for totaling.
> if ($delivery && !($delivery =~ /^\$/)) {
> $tmp += $delivery;
> }
> elsif ($delivery && ($delivery =~ /^\$/)) {
> $tmp += substr($delivery, 1, length($delivery)-1);
> } # If deposit value exists, format it for totaling.
> if ($deposit && !($deposit =~ /^\$/)) {
> $tmp -= $deposit;
> }
> elsif ($deposit && ($deposit =~ /^\$/)) {
> $tmp -= substr($deposit, 1, length($deposit)-1); }
> return currency($tmp,,,);
> }
> EOR
>
> I am using this tag in the order_view page in admin UI. Specifically
> at: [/if-sql-param] [if-sql-param color]
> <BR>
> [L]Color[/L]: [sql-param color]
> [/if-sql-param]
>
> [snip]****shortened for viewing****
>
> <td align=right style="padding: 5px">[sql-param
> quantity]</td> <td align=right style="padding:
> 5px">[currency][sql-param price][/currency]</td> <td
> align=right style="padding: 5px">[currency][sql-param
> subtotal][/currency]</td> </tr> [/query]
> <tr>
> <td height=4 colspan=6 class=lightshade><img
> src="bg.gif" width=1 height=4></td> <td
> class=lightshade><img src="bg.gif" width=1 height=4></td>
> </tr> <tr>
> <td height=39 colspan=6 align=right
> class=rsubbold>[L]Rental Sub-Total[/L]:</td>
> <td class=rownorm align=right>[currency][loop-data
> transactions subtotal][/currency]</td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Delivery
> and Pickup[/L]:</td> <td class=rownorm align=right>
> [perl table='delivery_schedule']
> $Tag->data("delivery_schedule", "charge",
> "[loop-data transactions order_number]");
> [/perl]
> </td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Setup and
> Teardown[/L]:</td> <td class=rownorm
> align=right>[loop-data transactions install_chrg]</td>
> </tr> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Total
> without Damage Waiver[/L]:</td> <td class=rownorm
> align=right> [query sql="select charge from
> delivery_schedule where
> order_number = [loop-data transactions order_number]" type=list]
> [total_view subtotal="[loop-data transactions
> subtotal]" install="[loop-data transactions install_chrg]"
> delivery="[sql-param charge]" ]
> [/query]
> </td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Less
> Deposits Received[/L]:</td> <td class=rownorm
> align=right><font color=red>[loop-data
> transactions deposit]</font></td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Balance
> Due[/L]:</td> <td class=rownormbold
> align=right>[total_view
> subtotal="[loop-data transactions subtotal]" install="[loop-data
> transactions install_chrg]" delivery="[sql-param charge]"
> deposit="[loop-data transactions deposit]"]</td>
> </tr>
> <tr>
> <td height=39 colspan=6 align=right
> class=rsubbold></td> <td class=rownorm
> align=right></td> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]10% Damage
> Waiver[L]:</td> <td class=rownorm
> align=right>[loop-data transactions damage_chrg]</td>
> </tr> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Total with
> Damage Waiver[/L]:</td> <td class=rownorm align=right>
> [total_view subtotal="[loop-data transactions
> subtotal]" install="[loop-data transactions install_chrg]"
> delivery="[sql-param charge]" damage="[loop-data transactions
> damage_chrg]"] </td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Less
> Deposits Received[/L]:</td> <td class=rownorm
> align=right><font color=red>[loop-data
> transactions deposit]</font></td>
> </tr>
> <tr>
> <td colspan=6 align=right class=rsubbold>[L]Balance
> Due[/L]:</td> <td class=rownormbold align=right>
> [total_view subtotal="[loop-data transactions
> subtotal]" install="[loop-data transactions install_chrg]"
> delivery="[sql-param charge]" damage="[loop-data transactions
> damage_chrg]" deposit="[loop-data transactions deposit]"]
> </td> </tr>
> </table>
>
> Wherever you see [total_view ... [sql-param charge] ... ] is where I
> need to reference the delivery charge
> from the delivery_schedule table. This area in order_view is where the
> order subtotal, shipping and Grand Total
> would normally be located. I have replaced that section with code
> above. The Usertag total_view returns
> the running total depending on weither you want to include Install
> charges or Delivery charges or Deposits
> received or Damage deposits. Each optional charge is passed as a param
> to total_view and then total_view
> checks formating ($) and calculates the running total and returns it.
> You would end up with something like:
>
> Unit Price Extention
> 1 $5.75 $5.75
>
> Rental Sub-Total: $5.75
> Delivery and Pickup: ***** Problem area ***** should show $20.00
> Setup and Teardown: $0.50 Total without Damage Waiver: $26.25
> Less Deposits Received: $6.00
> Balance Due: $0.25 ***** Problem area ***** should show + $20.00
>
> 10% Damage Waiver: $0.58
> Total with Damage Waiver: $6.83 ***** Problem area ***** should
> show + $20.00 Less Deposits Received: $6.00
> Balance Due: $0.83 ***** Problem area ***** should show + $20.00
>
> I thought I tried the [tmpn foo][/tmpn] tag and it too returned the
> result once but not more than once. However, I will try it again.
>
> Paul thanks again,
>
> Tim
>
Tim, it seems like your saying that [sql-param charge] is not coming through
for you. Your email does not show the [query] that is making [sql-param charge]
availiable. Please include that. Maybe you forgot to include "charge" in the
select statement.
Also, it may be easier, and cleaner to do somehting like:
$Scratch->{charge_1} = 0;
$Scratch->{charge_2} = 0;
$Scratch->{charge_3} = 0;
$tmp = 0;
# If subtotal value exists, format it for totaling.
if ($subtotal && !($subtotal =~ /^\$/)) {
$tmp += $subtotal;
$Scratch->{charge_1} = $tmp;
}
elsif ($subtotal && ($subtotal =~ /^\$/)) {
$tmp += substr($subtotal, 1, length($subtotal)-1);
$Scratch->{charge_1} = $tmp;
}
# If install value exists, format it for totaling.
if ($install && !($install =~ /^\$/)) {
$tmp += $install;
$Scratch->{charge_2} = $tmp;
}
elsif ($install && ($install =~ /^\$/)) {
$tmp += substr($install, 1, length($install)-1);
$Scratch->{charge_2} = $tmp;
}
Then disperse [scratchd charge_N] accordingly. You would only have to call your
tag once.
Paul
More information about the interchange-users
mailing list