Hi I am a total newbie, trying to learn coldfusion in my spare time and fix
some errors that are occuring on our website.
Unfortunately our web company went bust and a few queries aren't working so
I could do with some advice.
One of our main queries is to copy a csv with order details to our server
so our program can import order details.The query takes the order info,
asks teh other databse for the correct 'SKU' code returns it then creates a
csv string, it then contacts the server again to make sure the file doesn't
exist.
When a file does exist it means it hasn't yet been processed, so we need
the information to append to the existing csv file. Unfortunately though
the cf file append doesn't seem to be working and it always ends up
overwriting the csv file thus wiping out the previous information.
would any kind soul take a look at the code and see where I am going wrong?
Again I am a total newbie so apologies for any obvious mistakes, I am
trying to learn!
The cfm query is as follows-:
<cfquery name="rsOrder" datasource="#request.dsn#">
SELECT
tbl_list_shipstatus.shipstatus_Name,
tbl_orders.*,
tbl_customers.cst_FirstName,
tbl_customers.cst_LastName,
tbl_orderskus.orderSKU_SKU,
tbl_products.product_Name,
tbl_products.product_ID,
tbl_orderskus.orderSKU_Quantity,
tbl_orderskus.orderSKU_UnitPrice,
tbl_orderskus.orderSKU_SKUTotal,
tbl_shipmethod.shipmeth_Name,
tbl_skus.SKU_ID,
tbl_skus.SKU_MerchSKUID
FROM (
tbl_products
INNER JOIN tbl_skus
ON tbl_products.product_ID = tbl_skus.SKU_ProductID)
INNER JOIN ((tbl_customers
INNER JOIN (tbl_list_shipstatus
INNER JOIN (tbl_shipmethod
RIGHT JOIN tbl_orders ON tbl_shipmethod.shipmeth_ID =
tbl_orders.order_ShipMeth_ID)
ON tbl_list_shipstatus.shipstatus_id = tbl_orders.order_Status)
ON tbl_customers.cst_ID = tbl_orders.order_CustomerID)
INNER JOIN tbl_orderskus
ON tbl_orders.order_ID = tbl_orderskus.orderSKU_OrderID)
ON tbl_skus.SKU_ID = tbl_orderskus.orderSKU_SKU
WHERE tbl_orders.order_ID = '2C7DF553-****-9C00-641******20D4CFED'
ORDER BY
tbl_products.product_Name,
tbl_skus.SKU_Sort
</cfquery>
<!---CHECK DIRECTORY TO SEE IF FILE EXISTS--->
<cfftp
action="existsFile"
server="109.***.***.85"
username="*****"
password="******"
remotefile="/TS000102.POS"
connection = "MyFTPConnection">
<!---IF FILE DOES EXIST - APPEND NEW ORDER --->
<cfif cfftp.returnValue EQ "yes">
<cfoutput query="rsOrder">
<cfquery name="get_new_code" datasource="STOCKADE" username="**"
password="******" maxrows="1">
SELECT PLU, CODE
FROM ProductPLUs, Products
WHERE ProductPLUs.ProductID = Products.ID
AND Products.Code LIKE '#rsOrder.SKU_MerchSKUID#'
</cfquery>
<cfset plu = #get_new_code.PLU#>
<cfset Transaction = #Left(rsOrder.order_TransactionID, 6)#>
<cfset getvat = (rsOrder.orderSKU_UnitPrice * 0.2)>
<cfset getvat = #DecimalFormat(getvat)#>
<cfset fullprice = (rsOrder.orderSKU_UnitPrice + getvat)>
<cfset fullprice = #DecimalFormat(fullprice)#>
<cfif get_new_code.CurrentRow eq 1>
<cffile action="write"
file="#expandpath("/TS000102.POS")#"
output="#chr(34)#DA#chr(34)#,#DateFormat(rsOrder.order_Date,'YYMMDD')#,#TimeFormat(rsOrder.order_Date,'HHMM')#,#Transaction#,#chr(34)##plu##chr(34)#,#DecimalFormat(rsOrder.orderSKU_Quantity)#,#fullprice#,1,#getvat#,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,#chr(34)##chr(34)#,,#chr(34)##chr(34)#,1,,2,#chr(13)##chr(10)##chr(34)#DV#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,1,#getvat#,#DecimalFormat(rsOrder.orderSKU_UnitPrice)#,2,,,3,,,4,,,5,,,6,,,7,,,8,,,9,,,#chr(13)##chr(10)##chr(34)#DX#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,#chr(34)#4#chr(34)#,26,#fullprice#,,,,,,,,,,,,,,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,">
<cfelse>
<cffile action="append"
file="#expandpath("/TS000102.POS")#"
output="#chr(34)#DA#chr(34)#,#DateFormat(rsOrder.order_Date,'YYMMDD')#,#TimeFormat(rsOrder.order_Date,'HHMM')#,#Transaction#,#chr(34)##plu##chr(34)#,#DecimalFormat(rsOrder.orderSKU_Quantity)#,#fullprice#,1,#getvat#,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,#chr(34)##chr(34)#,,#chr(34)##chr(34)#,1,,2,#chr(13)##chr(10)##chr(34)#DV#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,1,#getvat#,#DecimalFormat(rsOrder.orderSKU_UnitPrice)#,2,,,3,,,4,,,5,,,6,,,7,,,8,,,9,,,#chr(13)##chr(10)##chr(34)#DX#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,#chr(34)#4#chr(34)#,26,#fullprice#,,,,,,,,,,,,,,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,">
</cfif>
</cfoutput>
<!---IF FILE DOES NOT EXIST WRITE THE FILE WITH THE ORDER INFO--->
<cfelseif cfftp.returnValue EQ "no">--->
<cfoutput query="rsOrder">
<cfquery name="get_new_code" datasource="STOCKADE" username="sa"
password="P@55w0rd" maxrows="1">
SELECT PLU, CODE
FROM ProductPLUs, Products
WHERE ProductPLUs.ProductID = Products.ID
AND Products.Code LIKE '#rsOrder.SKU_MerchSKUID#'
</cfquery>
<cfset plu = #get_new_code.PLU#>
<cfset Transaction = #Left(rsOrder.order_TransactionID, 6)#>
<cfset getvat = (rsOrder.orderSKU_UnitPrice * 0.2)>
<cfset getvat = #DecimalFormat(getvat)#>
<cfset fullprice = (rsOrder.orderSKU_UnitPrice + getvat)>
<cfset fullprice = #DecimalFormat(fullprice)#>
<cfif rsOrder.CurrentRow eq 1>
<cffile action="write"
file="#expandpath("/TS000102.POS")#"
output="#chr(34)#DA#chr(34)#,#LSDateFormat(rsOrder.order_Date,'YYMMDD')#,#LSTimeFormat(rsOrder.order_Date,'HHMM')#,#Transaction#,#chr(34)##plu##chr(34)#,#DecimalFormat(rsOrder.orderSKU_Quantity)#,#fullprice#,1,#getvat#,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,#chr(34)##chr(34)#,,#chr(34)##chr(34)#,1,,2,#chr(13)##chr(10)##chr(34)#DV#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,1,#getvat#,#DecimalFormat(rsOrder.orderSKU_UnitPrice)#,2,,,3,,,4,,,5,,,6,,,7,,,8,,,9,,,#chr(13)##chr(10)##chr(34)#DX#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,#chr(34)#4#chr(34)#,26,#fullprice#,,,,,,,,,,,,,,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,">
<cfelse>
<cffile action="append"
file="#expandpath("/TS000102.POS")#"
output="#chr(34)#DA#chr(34)#,#LSDateFormat(rsOrder.order_Date,'YYMMDD')#,#LSTimeFormat(rsOrder.order_Date,'HHMM')#,#Transaction#,#chr(34)##plu##chr(34)#,#DecimalFormat(rsOrder.orderSKU_Quantity)#,#fullprice#,1,#getvat#,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,#chr(34)##chr(34)#,,#chr(34)##chr(34)#,1,,2,#chr(13)##chr(10)##chr(34)#DV#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,1,#getvat#,#DecimalFormat(rsOrder.orderSKU_UnitPrice)#,2,,,3,,,4,,,5,,,6,,,7,,,8,,,9,,,#chr(13)##chr(10)##chr(34)#DX#chr(34)#,#DateFormat(Now(),
"YYMMDD")#,#TimeFormat(Now(),
"HHMM")#,#Transaction#,#chr(34)#4#chr(34)#,26,#fullprice#,,,,,,,,,,,,,,,,,#chr(34)##chr(34)#,#chr(34)##chr(34)#,">
</cfif>
</cfoutput>
</cfif>
<!---Send File to remote Server--->
<cfftp
action="putFile"
server="109.***.***.85"
username="*****"
password="*****"
localfile="E:\Domains\********.co.uk\wwwroot\TS000102.POS"
remotefile="/TS000102.POS"
connection = "MyFTPConnection">
<!---Close the connection--->
<cfftp action = "close"
connection = "MyFTPConnection"
stopOnError = "Yes">
<!---Delete File from local server--->
<cffile action="delete"
file="#expandpath("/TS000102.POS")#">
Any help is GREATLY appreciated.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5747
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
