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:5746
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
