评估ColdFusion变量列表 [英] Evaluate List of ColdFusion Variables
问题描述
我创建了一个表使用coldfusion列表系统，行是月，列是城市，变量是销售，现在我需要添加到每个评估的销售变量，这些销售的公司的名单，到目前为止没有问题，写这个表的sql和销售，但我无法定义 - ＃evaluate＃这些公司属于表中的每个销售的列表，因为它是公司的LIST，而不是一个变量如销售，我希望我自己清楚:)这里是我的代码：
查询一些集合：
< cfset month_list_name =＃message13＃，＃message2＃，＃message3＃，＃message4＃，＃message5＃，＃message6＃，＃message7＃ ，＃message10＃，＃message11＃，＃message12＃>
< cfset type_index ='NETTOTAL'>
< cfquery name =GET_CITYdatasource =＃DSN＃>
SELECT CITY_ID，CITY_NAME FROM SETUP_CITY ORDER BY CITY_NAME
< / cfquery>
< cfquery name =GET_COMPANYdatasource =＃DSN＃>
SELECT COMPANY_ID，FULLNAME FROM COMPANY ORDER BY COMPANY_ID
< / cfquery>
< cfset c_index ='COMPANY_ID'>
< cfset no_cities = 0 />
< cfset cities_list =''>
< cfset cities_list_name =''>
< cfset month_list =''>
< cfoutput query =get_city>< cfset cities_list = listappend（cities_list，city_id）>< cfset no_cities ++ />< / cfoutput&
< cfoutput query =get_city>< cfset cities_list_name = listappend（cities_list，city_name）>< / cfoutput>
< cfloop from =1to =12index =i>< cfset month_list = listappend（month_list，i）>< / cfloop>
< cfquery name =GET_SALES_TOTALdatasource =＃dsn＃>
SELECT
SUM（NETTOTAL）NETTOTAL，
SC.CITY_ID，
DATEPART（MM，INVOICE_DATE）AY，
C.FULLNAME，
C.COMPANY_ID AS COMPANY_ID
FROM
＃DSN2_ALIAS＃.INVOICE I，
SETUP_CITY SC，
COMPANY C
WHERE
MONTH（INVOICE_DATE）> = 1
AND MONTH（INVOICE_DATE）< 12
AND C.COMPANY_ID = I.COMPANY_ID
AND SC.CITY_ID = C.CITY
AND PURCHASE_SALES = 1
GROUP BY
DATEPART（MM，INVOICE_DATE），
SC.CITY_ID，
C.FULLNAME，
C.COMPANY_ID
< / cfquery>
这里是列表的定义：
< cfloop list =＃month_list＃index =month_index>
< cfloop list =＃cities_list＃index =city_index>
< cfoutput query =GET_SALES_TOTAL>
< cfif city_id eq city_index and AY eq month_index>
< cfloop list =＃type_index＃index =tt_index>
< cfset'alan_＃tt_index＃_＃city_index＃_＃month_index＃'= evaluate（tt_index）>
< cfset'alan2_＃city_index＃_＃tt_index＃_＃month_index＃'= evaluate（tt_index）>
< / cfloop>
< cfloop list =＃c_index＃index =cc_index>
< cfif cc_index eq company_id>
< cfset'company_＃cc_index＃_＃city_index＃_＃month_index＃'= evaluate（cc_index）>
< / cfif>
< / cfloop>
< / cfif>
< / cfoutput>
< / cfloop>
< / cfloop>
这里是表格：
< cfloop list =＃month_list＃index =m_index>
< tr class =color-rowheight =20>
< td width =150nowrap>< b>< cfoutput> #left（listgetat（month_list_name，listfind（month_list，m_index）），20）＃< / cfoutput>< / b> < / td>
< cfloop list =＃cities_list＃index =ddd_other>
< cfloop list =＃type_index＃index =ii_index>
< td align =center>
< cfif isdefined（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'）和len（evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'））>
< cfset all_toplam = evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'）+ all_toplam>
< cfset'total_＃ii_index＃_＃m_index＃'= evaluate（'total_＃ii_index＃_＃m_index＃'）+ #evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'） ＃>
< cfset'total2_＃ddd_other＃'= evaluate（'total2_＃ddd_other＃'）+ #evaluate（'alan2_＃ddd_other＃_＃ii_index＃_＃m_index＃'）＃&
< cfif listfindnocase（'NETTOTAL'，ii_index）>
< cfif isdefined（'attributes.money'），attributes.money is 2>
< cfoutput> #TLFormat（（evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'）/ get_money.rate2），2）＃< / cfoutput>
< cfelse>
< cfoutput> #TLFormat（evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'），2）＃< / cfoutput>
< / cfif>
< cfelse>
< cfif isdefined（'attributes.money'），attributes.money is 2>
< cfoutput> #TLFormat（（evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'）/ get_money.rate2），2）＃< / cfoutput>
< cfelse>
< cfoutput> #TLFormat（evaluate（'alan_＃ii_index＃_＃ddd_other＃_＃m_index＃'），2）＃< / cfoutput>
< / cfif>
< / cfif>
< cfelse>
0
< / cfif>
< cfloop list =＃c_index＃index =co_index>
< cfif listfindnocase（'COMPANY_ID'，co_index）>
< cfoutput> #evaluate（'company_＃co_index＃_＃ddd_other＃_＃m_index＃'）＃< / cfoutput>
< / cfif>
< / cfloop>
< / td>
< / cfloop>
< / cfloop>
< cfloop list =＃type_index＃index =kk_ind>
< td align =centerclass =txtbold>
< cfif isdefined（'attributes.money'），attributes.money is 2>
< cfoutput> #TLFormat（（evaluate（'total_＃kk_ind＃_＃m_index＃'）/ get_money.rate2），2）＃< / cfoutput>
< cfelse>
< cfoutput> #TLFormat（evaluate（'total_＃kk_ind＃_＃m_index＃'），2）＃< / cfoutput>
< / cfif>
< / td>
< / cfloop>
< / tr>
< / cfloop>
它说：变量company_COMPANY_ID_1_1未定义。我的错误在哪里？
我不能评估company_idies的LIST以同样的方式我评估的NETTOTAL，因为净总数不是列表，但只有一个值...并感谢大家的帮助和关注！ ;）
这段代码中有很多可以改进的地方。
增加循环中的城市数量是不必要的。您可以直接从SQL（ select count（*）from setup_city ）或从构建您的列表之后的CF中获取计数（ listlen（cities_list ）。
对于这一点，当你可以简单地使用ValueList来让CF为你工作时，不需要手动循环列表：
cities_list = ValueList（get_city.city_id）;
cities_list_name = ValueList（get_city.city_name）;
没有理由建立一个月份列表，如果你要做的是从1到12循环。使用1到12循环。
type_index 不需要是一个列表，如果你只有一个item。使用变量，只有当你实际需要一个列表时，用一个列表替换它;这也将删除需要使用 evaluate ，这使得你的代码很难 cc_index 。
因为范围可以像访问一样访问结构，通常使用 structkeyexists（attributes，money）而不是 isdefined（attributes.money ）。指定 isdefined 的范围确实有帮助，当然... isdefined（money）但可以返回一个完全不同的变量，如果你不小心。
谈到结构，我一定会建议你使用它们，而不是连接变量名。解析你写的内容是非常困难的，以便理解你想要做什么：使用结构体会使它更容易遵循。
最后，对我来说，你在ColdFusion中做了大量工作来做数据库可以更容易做的事情。
< cfquery name =GET_SALES_GRAND_TOTALdatasource = #dsn＃>
SELECT
SUM（NETTOTAL）NETTOTAL，
SC.CITY_ID，
C.FULLNAME，
C.COMPANY_ID AS COMPANY_ID
FROM
＃ DSN2_ALIAS＃.INVOICE I，
SETUP_CITY SC，
COMPANY C
WHERE
MONTH（INVOICE_DATE）> = 1
AND MONTH（INVOICE_DATE） 12
AND C.COMPANY_ID = I.COMPANY_ID
AND SC.CITY_ID = C.CITY
AND PURCHASE_SALES = 1
GROUP BY
SC.CITY_ID，
C.FULLNAME，
C.COMPANY_ID
< / cfquery>
是否需要检查月份是否介于1到12之间？如果INVOICE_DATE存在，那么它必须有一个介于1和12之间的月份。我将从查询中完全删除这些行或用 INVOICE_DATE IS NOT NULL p>
然后，您应该可以得到大致如下所示的结果表：
< cfoutput query =GET_SALES_TOTALgroup =AY>
<！--- months first --->
< tr>
<！--- month-specific stuff goes here --->
< td> month_header< /td>
< cfoutput group =city_id>
<！--- city-specific stuff --->
< td><！--- format NETTOTAL但你想在这里--->< / td>
< / cfoutput>
< / tr>
< / cfoutput>
< tr>
< td> grand_total_header< / td>
< cfoutput query =GET_SALES_GRAND_TOTALgroup =city_id>
< td><！--- format NETTOTAL这里--->< / td>
< / cfoutput>
< / tr>
希望这对你有所帮助。我强烈建议你阅读数组和结构;有人使用评估通常是很难的方式，当有一个更容易的方式来管理事情，有很多例子，在SO和其他地方的CF代码，你应该能够从中学习。
i created a table using coldfusion lists system, rows are months and the columns are cities, and the variable is the sales, now i need to add to every evaluated sale variable, the list of companies that made these sales, so far there is no problem with writting the sql of this table and the sales, but i cant define-#evaluate# the list of these companies that belongs to each sale in the table, since it is the LIST of companies, not one variable like sale, i hope i made myself clear :) here is my code:
The query with some sets:
<cfset month_list_name = "#message13#,#message2#,#message3#,#message4#,#message5#,#message6#,#message7#,#message8#,#message9#,#message10#,#message11#,#message12#">
<cfset type_index = 'NETTOTAL'>
<cfquery name="GET_CITY" datasource="#DSN#">
SELECT CITY_ID,CITY_NAME FROM SETUP_CITY ORDER BY CITY_NAME
</cfquery>
<cfquery name="GET_COMPANY" datasource="#DSN#">
SELECT COMPANY_ID,FULLNAME FROM COMPANY ORDER BY COMPANY_ID
</cfquery>
<cfset c_index = 'COMPANY_ID'>
<cfset no_cities = 0 />
<cfset cities_list = ''>
<cfset cities_list_name = ''>
<cfset month_list = ''>
<cfoutput query="get_city"><cfset cities_list = listappend(cities_list,city_id)><cfset no_cities ++ /></cfoutput>
<cfoutput query="get_city"><cfset cities_list_name = listappend(cities_list,city_name)></cfoutput>
<cfloop from="1" to="12" index="i"><cfset month_list=listappend(month_list,i)></cfloop>
<cfquery name="GET_SALES_TOTAL" datasource="#dsn#">
SELECT
SUM(NETTOTAL) NETTOTAL,
SC.CITY_ID,
DATEPART(MM,INVOICE_DATE) AY,
C.FULLNAME,
C.COMPANY_ID AS COMPANY_ID
FROM
#DSN2_ALIAS#.INVOICE I,
SETUP_CITY SC,
COMPANY C
WHERE
MONTH(INVOICE_DATE) >= 1
AND MONTH(INVOICE_DATE) < 12
AND C.COMPANY_ID=I.COMPANY_ID
AND SC.CITY_ID=C.CITY
AND PURCHASE_SALES=1
GROUP BY
DATEPART(MM,INVOICE_DATE),
SC.CITY_ID,
C.FULLNAME,
C.COMPANY_ID
</cfquery>
here is the definitions for lists:
<cfloop list="#month_list#" index="month_index">
<cfloop list="#cities_list#" index="city_index">
<cfoutput query="GET_SALES_TOTAL">
<cfif city_id eq city_index and AY eq month_index>
<cfloop list="#type_index#" index="tt_index">
<cfset 'alan_#tt_index#_#city_index#_#month_index#' = evaluate(tt_index)>
<cfset 'alan2_#city_index#_#tt_index#_#month_index#' = evaluate(tt_index)>
</cfloop>
<cfloop list="#c_index#" index="cc_index">
<cfif cc_index eq company_id>
<cfset 'company_#cc_index#_#city_index#_#month_index#' = evaluate(cc_index)>
</cfif>
</cfloop>
</cfif>
</cfoutput>
</cfloop>
</cfloop>
and here's the table:
<cfloop list="#month_list#" index="m_index">
<tr class="color-row" height="20">
<td width="150" nowrap><b><cfoutput>#left(listgetat(month_list_name,listfind(month_list,m_index)),20)#</cfoutput></b></td>
<cfloop list="#cities_list#" index="ddd_other">
<cfloop list="#type_index#" index="ii_index">
<td align="center">
<cfif isdefined('alan_#ii_index#_#ddd_other#_#m_index#') and len(evaluate('alan_#ii_index#_#ddd_other#_#m_index#'))>
<cfset all_toplam=evaluate('alan_#ii_index#_#ddd_other#_#m_index#')+all_toplam>
<cfset 'total_#ii_index#_#m_index#'=evaluate('total_#ii_index#_#m_index#') + #evaluate('alan_#ii_index#_#ddd_other#_#m_index#')#>
<cfset 'total2_#ddd_other#'=evaluate('total2_#ddd_other#') + #evaluate('alan2_#ddd_other#_#ii_index#_#m_index#')#>
<cfif listfindnocase('NETTOTAL',ii_index)>
<cfif isdefined('attributes.money') and attributes.money is 2>
<cfoutput>#TLFormat((evaluate('alan_#ii_index#_#ddd_other#_#m_index#')/get_money.rate2),2)#</cfoutput>
<cfelse>
<cfoutput>#TLFormat(evaluate('alan_#ii_index#_#ddd_other#_#m_index#'),2)#</cfoutput>
</cfif>
<cfelse>
<cfif isdefined('attributes.money') and attributes.money is 2>
<cfoutput>#TLFormat((evaluate('alan_#ii_index#_#ddd_other#_#m_index#')/get_money.rate2),2)#</cfoutput>
<cfelse>
<cfoutput>#TLFormat(evaluate('alan_#ii_index#_#ddd_other#_#m_index#'),2)#</cfoutput>
</cfif>
</cfif>
<cfelse>
0
</cfif>
<cfloop list="#c_index#" index="co_index">
<cfif listfindnocase('COMPANY_ID',co_index)>
<cfoutput>#evaluate('company_#co_index#_#ddd_other#_#m_index#')#</cfoutput>
</cfif>
</cfloop>
</td>
</cfloop>
</cfloop>
<cfloop list="#type_index#" index="kk_ind">
<td align="center" class="txtbold">
<cfif isdefined('attributes.money') and attributes.money is 2>
<cfoutput>#TLFormat((evaluate('total_#kk_ind#_#m_index#')/get_money.rate2),2)#</cfoutput>
<cfelse>
<cfoutput>#TLFormat(evaluate('total_#kk_ind#_#m_index#'),2)#</cfoutput>
</cfif>
</td>
</cfloop>
</tr>
</cfloop>
it says: Variable company_COMPANY_ID_1_1 is undefined. where is my mistake? i cant evaluate the LIST of company_idies in the same way i evaluated the NETTOTAL, since net total is not the list, but only one value... And thank you all for the help and attention! ;)
There are a lot of things you can improve in this code.
Incrementing the number of cities inside a loop is unnecessary. You can get the count directly from SQL (select count(*) from setup_city) or from CF after building your list (listlen(cities_list).
For that matter, it's unnecessary to loop through the list manually when you can simply use ValueList to have CF do the work for you:
cities_list = ValueList( get_city.city_id );
cities_list_name = ValueList( get_city.city_name );
There's no reason to build a list of months if all you're ever going to do is loop from 1 to 12. Use the 1-to-12 loop instead.
type_index doesn't need to be a list if you only have one item. Use the variable instead. Replace it with a list only when you actually need a list; this will also remove the need to use evaluate, which is making your code hard to read and difficult for people to follow. Same thing for cc_index.
Because scopes can be accessed as if they were structs, it's generally better (and can be significantly faster) to use structkeyexists( attributes, "money" ) rather than isdefined( "attributes.money" ). Specifying the scope for isdefined does help, of course ... isdefined( "money" ) is not only slow, but could return a completely different variable if you're not careful.
Speaking of structs, I would definitely recommend that you use them rather than concatenated variable names. It's extremely difficult to parse what you have written in order to understand what you are trying to do: using structs would make it easier to follow.
Finally, it looks to me like you're doing a ton of work in ColdFusion to do something that your database could do for you much more easily. If you need grand totals for companies by city, why not just do this?
<cfquery name="GET_SALES_GRAND_TOTAL" datasource="#dsn#">
SELECT
SUM(NETTOTAL) NETTOTAL,
SC.CITY_ID,
C.FULLNAME,
C.COMPANY_ID AS COMPANY_ID
FROM
#DSN2_ALIAS#.INVOICE I,
SETUP_CITY SC,
COMPANY C
WHERE
MONTH(INVOICE_DATE) >= 1
AND MONTH(INVOICE_DATE) < 12
AND C.COMPANY_ID=I.COMPANY_ID
AND SC.CITY_ID=C.CITY
AND PURCHASE_SALES=1
GROUP BY
SC.CITY_ID,
C.FULLNAME,
C.COMPANY_ID
</cfquery>
Is there a need to check that the month is between 1 and 12? If INVOICE_DATE exists, then it has to have a month between 1 and 12. I would either remove those lines from the query entirely or replace them with INVOICE_DATE IS NOT NULL.
You should then be able to get your table of results with something that looks roughly like this:
<cfoutput query="GET_SALES_TOTAL" group="AY">
<!--- months first --->
<tr>
<!--- month-specific stuff goes here --->
<td>month_header</td>
<cfoutput group="city_id">
<!--- city-specific stuff --->
<td><!--- format NETTOTAL however you want here ---></td>
</cfoutput>
</tr>
</cfoutput>
<tr>
<td>grand_total_header</td>
<cfoutput query="GET_SALES_GRAND_TOTAL" group="city_id">
<td><!--- format NETTOTAL here ---></td>
</cfoutput>
</tr>
Hopefully this is of some help to you. I would strongly encourage you to read up on arrays and structs; someone using evaluate is typically doing it the hard way when there is a much easier way of managing things, and there are plenty of examples both on SO and elsewhere of CF code that you should be able to learn from.
这篇关于评估ColdFusion变量列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
