Meta

目录

ColdFusion xmlParse()

<cfscript>
  arData = xmlParse(form.data);
</cfscript>


<table class="table table-bordered table-condensed table-datasubset">
  <thead>
    <tr class="bg-success">
      <cfloop index="i" array="#arData.data.xmlchildren[1].xmlchildren#">
        <th>#i.xmlname#</th>
      </cfloop>
    </tr>
  </thead>
  <tbody>
    <cfloop index="i" array="#arData.data.xmlchildren#">
      <tr>
        <cfloop index="j" array="#i.xmlchildren#">
          <td>#j.xmltext#</td>
        </cfloop>
      </tr>
    </cfloop>
  </tbody>
</table>

首先要求 XML 代码最外层必须被一个 root tag 包围, 否则会报以下错误:

The markup in the document following the root element must be well-formed.

对于下面的 XML

<data>
  <details>
    <location>IS Services (SC) - Denver, CO, USA</location>
    <case_id>150</case_id>
    <case_date>2015-03-14T00:00:00</case_date>
  </details>
  <details>
    <location>IS Services (SC) - Denver, CO, USA</location>
    <case_id>150</case_id>
    <case_date>2015-03-14T00:00:00</case_date>
  </details>
</data>

首先获得 data 下面的 details, 然后循环获取 details 下对应的 tag

注意如果使用 SQL Server 返回的 XML 需要在头尾加一个 <data>标签

Store Procedure

CF 端代码

<cfstoredproc procedure="dbo.PCC_Item_Compliance_Assess" datasource="#request.companyEHS_ODBC#">
  <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.maxItems#" />
  <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.businessID#" />
  <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.entityID#" />
  <cfprocparam cfsqltype="cf_sql_varchar" value="#variables.itemID#" />

  current options are REACH or RoHS --->
  <cfprocparam cfsqltype="cf_sql_varchar" value="REACH,RoHS" />
  <cfprocparam cfsqltype="cf_sql_varchar" value="#1#" />
  <cfprocresult name="qResults">

</cfstoredproc>

最重要的就是 cfprocresult 这东西, 返回之后将其 dump 出来

就可以看到存储过程最后一次 select

createObject

Function syntax

CreateObject(type, component-name) 

Parameters

1. type Type of object to create.
   • com
   • corba
   • java
   • component
   • webservice
2. component-name The CFC name; corresponds to the name of the file that defines the component; for example, use engineComp to specify the component defined in the engineComp.cfc file

Usage

<b>Server's Local Time:</b>
<cfscript> tellTimeCFC=CreateObject("component","appResources.components.tellTime");
  tellTimeCFC.getLocalTime();
</cfscript>
<br>
<b>Calculated UTC Time:</b>
<cfscript>
  tellTimeCFC.getUTCTime();
</cfscript>

Example

注意路径使用点来分割

objChart = CreateObject("component", "developer.emreyucel.area51.library.cfc.chartdirectordirector").init();

这里访问的是根目录下面的

/developer/emreyucel/area51/library/cfc/chartdirectordirector.cfc

ColdFusion: 得到当前服务器目录

Get current directory in server

fullpath = getDirectoryFromPath(getCurrentTemplatePath())

assuming that you are on Windows, and your fullpath looks something like c:\inetpub\site\dir, you can use this:

thisFolder = listlast(fullpath, "\/")

Ternary Expression 三元表达式

<cfscript>
  variables.a = (5>4)?True:False;
</cfscript>
<cfoutput>
  #a#
</cfoutput>

Coldfusion 日期相关

上周最后一天

##DateFormat(DATE - DayOfWeek(DATE) + 1) />#

DateFormat

dateformat(date,"d -mmm -yyyy")
DateFormat("date" [, "mask" ])

• mask

Characters that show how ColdFusion displays a date:

• d: Day of the month as digits; no leading zero for single -digit days.
• dd: Day of the month as digits; leading zero for single -digit days.
• ddd: Day of the week as a three -letter abbreviation.
• dddd: Day of the week as its full name.
• m: Month as digits; no leading zero for single -digit months.
• mm: Month as digits; leading zero for single -digit months.
• mmm: Month as a three -letter abbreviation.
• mmmm: Month as its full name.
• yy: Year as last two digits; leading zero for years less than 10.
• yyyy: Year represented by four digits.
• gg: Period/era string. Ignored. Reserved. The following masks tell how to format the full date and cannot be combined with other masks:
• short: equivalent to m/d/y
• medium: equivalent to mmm d, yyyy
• long: equivalent to mmmm d, yyyy
• full: equivalent to dddd, mmmm d, yyyy

输出日期和时间

日期时间一起:

##DateFormat(now(), 'd-mmm-yy')# #TimeFormat(now(), 'hh:mm tt')

日期差

DateDiff("datepart", "date1", "date2")

显示 date2 - date1 的结果

datepart:

String that specifies the units in which to count; for example yyyy requests a date difference in whole years.

yyyy: Years
q: Quarters
m: Months
y: Days of year (same as d)
d: Days
w: Weekdays (same as ww)
ww: Weeks
h: Hours
n: Minutes
s: Seconds

Date: Quarter related && first and last day of quarter

First day of quarter:

FirstDayOfQuarter = CreateDate(year, (quarter-1)*3 + 1, 1)

Last day of quarter:

LastDayOfQuarter = DateAdd("d", -1, DateAdd("m", 3, FirstDayOfQuarter))

Rereplace()

这里替换了不合法的文件名字符

<cfset variables['badFileNameCharset']='[\\~##%&*{}/:<>?|\"-]' />
<cfset variables['filename']=rereplace(qChemcialListName.listnameDIsplayed, variables['badFileNameCharset'],' ' , ' all') />

4 个参数:

1. string
2. regex
3. substring
4. scope: ‘one’ 或者 ‘all’

Query Loop in cfscript

< cfscript >
  // Loop over the records in the query.
  for (intRow = 1; intRow LTE qPerson.RecordCount; intRow = (intRow + 1)) {

    // Output the name some values. When doing so, access the
    // query as if it were a structure of arrays and we want
    // only get values from this row index.
    WriteOutput(
      qPerson["name"][intRow] & " is a " &
      qPerson["sex"][intRow] & " that prefers doing " &
      qPerson["preferred_category"][intRow] & " movies." &
      "<br />"
    );
  } <
  /cfscript >

Personal Example:

<cfquery name="q" datasource="#cc_odbc#">
  declare @tbl as table (#meta.getTableDeclaration(rqColumns)#);

  #meta.getDatasSQL(rqDataIDS , ck.ckpttableid, '@tbl' , rqColumns.cname.toArray())#

  select
  d.[#rvColumns#]
  ,s.orgname, s.location
  from @tbl d
  join meta_row r on d.did = r.did
  join meta_scope s on r.scopeid = s.scopeid
</cfquery>

<cfscript>
  if(form.casecount EQ 0) {
  return writeoutput("there is not enough information to complete this request");
  };

  index = 1;
  for(i=1; i LTE q.RecordCount; i++){

  if(!toString(q[rvColumns][i]) == '')
  continue;

  writeoutput(toString(q[rvColumns][i]));

  inputString = ('<data>' & toString(q[rvColumns][i]) & '</data>');

  if(!isXML(inputString))
  return writeoutput("there is not enough information to complete this request");

  arData[index++] = xmlParse(inputString);
  }
</cfscript>

注意获取 recordcount 的时候直接使用 queryname 之后再 loop 之中获取数据的时候是使用

queryName[columnName][RowName]

这样的形式

Prevent Output

两个方法

• cfsilent 围起来

标签内的东西都不会输出(可以防止意外输出导致占用内存)

• cfcontent 并且设置 reset=“yes”

清空在 cfcontent 标签之前的缓存

String

字符串连接(类 StringConcat 效果)

ColdFusion’s concatenation operator is &

 <cfset teacherName = Firstname &" "& Lastname>

在 cfscript 中也是同样用法

<cfscript>
  arData = xmlParse('<data>' & toString(form.data) & '</data>');
</cfscript>

另一种比较奇葩的方法

<cfset myStruct.concatendatedSring="#myStruct.string1##myStruct.string2#">

单引号字符串传到 cfquery 里面变为双引号

解决方法 中间加一个 Replace

<cfquery name="string_with_single_quote" datasource="schema_user" result="my_result"> #REReplace(string_variable,"''","'","ALL")#
</cfquery>

replace

Argument Reference

| Name | Required | Default | Description | | ------------ | ------------ |------------ | | String | Yes | | A string (or variable that contains one) within which to replace substring | | substring1 | Yes ||| | substring2 | Yes ||| | scope | No || * one: Replace the first occurrence (default)*
all: Replace all occurrences | |

Usage in cfscript

getVal = replaceNoCase("ColdFUsion", "u", "a");
writeDump(getVal);

<cfset getVal=replaceNoCase("ColdFusiOn", "O" , "a" , "ALL" )>
  <cfdump var="#getVal#">

String Split

<cfset a=TestString.Split("<br />")>

---

CF-Try/Catch

简单用法

嵌套用法, 注意内部捕捉之后不会被外部再次捕捉

<cfscript>
    try {
        try {
            a = b;
        }
        catch(any variable) {
            // do something 1
        } finally {
            
            // do something 2
        }
    }
    catch(any variable) {
            // do something 3
    } finally {
            // do something 4

    }
</cfscript>

<cftry>
  <cfset a=URL.asd />
  <cfcatch type="any">
    <cfdump var="#cfcatch.tagcontext#" abort="true" expand="true">
  </cfcatch>
</cftry>

---

NumberFormat

Syntax

NumberFormat(num, mask);
NumberFormat(123456789.99, ",")

Excel

cfspreadsheet

使用 CF 来 export excel 的时候使用到了这个 tag

Syntax

The tag syntax depends on the action attribute value:

注意 Read 和 Write 的参数有些不同, Write 的时候不需要 src 这样的参数

Read
<cfspreadsheet action="read" src="filepath" columns="range" columnnames="comma-delimited list" excludeHeaderRow="true | false" format="CSV|HTML" headerrow="row number" name="text" query="query name" rows="range" sheet="number" sheetname="text">

Update
<cfspreadsheet action="update" filename="filepath" format="csv" name="text" password="password" query="query name" sheetname="text">

Write
<cfspreadsheet action="write" filename="filepath" format="csv" name="text" overwrite="true | false" password="password" query="queryname" sheetname="text">

Example

新建一个 Excel 并写入数据

<cfset decSheetObj=SpreadsheetNew()>
  <cfscript>
    //Build Declaration Sheet
    spreadsheetSetCellValue(variables.decSheetObj, 123, 56, 4);
  </cfscript>
  <cfspreadsheet action="write" filename="report.xls" sheetname="Declaration" name="decSheetObj" overwrite="true" />

SpreadsheetSetCellValue

Syntax

SpreadsheetSetCellValue(spreadsheetObj, value, row, column)

Example: Excel 输出

纯文本输出见附件

<cfscript>
  decSheetObj = SpreadsheetNew();

  [SomeCode……]

  for(col=1; col<=arrayLen(rhColumns); col++) { spreadsheetSetCellValue(variables.decSheetObj, "123" , 1, 2); } 
</cfscript> <!--- write and download--->
    <cfspreadsheet action="write" filename="/report.xls" sheetname="Declaration" name="decSheetObj" overwrite="true" />
    <cfheader name="Content-Disposition" value="attachment; filename=Report_#dateFormat(now(), 'yyyymmdd ')##timeFormat(now(), 'HHmmss')#.xls" />
    <cfcontent file="/report.xls" type="application/vnd.ms-excel" deletefile="true" />

注意 cfcontent 的路径必须和上面 cfspreadsheet 写入的路径相同 不然会报找不到文件的错误

• 上方代码测试有效
• 运行正常的情况下会自动下载 Excel 并不会报错
• 打开新窗口的情况下会弹出下载窗口

关于 Cell 风格的设置

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6747.html

Array Functions

Arrayfind

<cfscript>
  writeDump(ArrayFind(["STRING","string"], "string"));
  writeDump(ArrayFind(["STRING","string"], function(s) {if(compare(s, "string")==0) return true; return false;}));
</cfscript>

ArrayMap

这个比普通的循环方便多了

arrayMap([Array], function(item) {
  ……
  return item.title;
})

---

Dump Message within condition

 if(structKeyExists(url, "test")){
  writeDump(variables.oPortalTranslator.translate('CAS:'));
  abort;
 }

---

Escape singlequote and doublequote

<!--- Here I have put two single quotes between Hari and s --->
<cfset variables.event = 'Hari''s first pen.' />

It will print as: Hari』s first pen.

<!--- Here I have put two single quotes around amnesia --->
<cfset variables.event = 'what you told the meaning of ''amnesia'' word?'/>

It will print as: what you told the meaning of 『amnesia』 word?

Escaping a double quote with in double quotes

---

HTML Encode/Decode

几种情况:

1. Coldfusion 字符串, 用于 HTML 属性的值, 使用 encodeForHTML()
2. Coldfusion 字符串, 用于 Excel 内容, 使用 decodeForHTML()

---

Query of Query

QoQ JOIN

首先 ColdFusion 不支持 JOIN

但是可以用取巧的方法:

SELECT *
FROM qTask, qScopeData

不过如果是 Left Join, Right Join 那样的需求就没办法了

报错

Error Executing Database Query. Query Of Queries syntax error. Encountered ”. Incorrect GROUP BY column reference []. Only simple column reference, and alias name are allowed. Example: You can use alias to refer to a complex expression: SELECT (a+b)/2 as x, count(*) FROM T GROUP BY x The error occurred on line 69.

一般 QoQ 报错很有可能就是 SQL 格式出错, 找一下是否存在错误的用法改掉即可

---

listToArray

<h1>Demo for listToArray</h1>
<cfquery name="query" datasource="CC_TEST">
  select top(3) * from ltbContact;
</cfquery>
<cfdump var="#query#" expand="false">
  <cfset xAxisLabelsdata=listToArray(ValueList(query.CONTACT_LASTNAME,'|'),'|') />
  <cfdump var="#xAxisLabelsdata#" expand="false">

Loop in Array

<h1>Demo For Loop in Array</h1>
<cfoutput>
  <cfloop index="i" from="1" to="#ArrayLen(xAxisLabelsdata)#">
    <!--- #i# - #xAxisLabelsdata[i]# <br>             --->
    <div width="30%" align="center">#i# - #xAxisLabelsdata[i]#</div>
  </cfloop>
</cfoutput>