[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