Hi

I want to import the orders from my website to a 3rd party aplication on a semi-authomatic way.

In order to do that I need that every time a new order is recived, a new file in ASCII format has to be created (obviously with the information from that order) and then that file has to be attached to an email. The recipient for that email will be always the same.

Does anyone knows how to do that? Any help will be very apreciated.

In advance, thanks.

This may be what you're looking for:

<%
'::::CREATE THE FILE

Dim oFSO
Dim oHome

Set oFSO = CreateObject("Scripting.FileSystemObject"
Set oHome = oFSO.CreateTextFile("c:\mydir\filename.txt"

oHome.WriteLine "This is a line of text."
oHome.WriteLine "So is this..."
oHome.WriteLine "Hey, even more text"
Set oFSO = Nothing
Set oHome = Nothing

'::::::SEND IT ON ITS MERRY WAY

Dim oCDO
Dim oConf

Set oCDO = Server.CreateObject("CDO.Message"
Set oConf = CreateObject("CDO.Configuration"

With oCDO
Set .Configuration = oConf
.To = " "
.From = " "
.Subject = "Here's your file"
.TextBody = "File is attached."
.AddAttachment "c:\mydir\filename.txt"
.Send
End With
Set oCDO = Nothing

'::::PTIONALLY DELETE FILE AFTER EMAILING IT

'Dim oFSOD
'Dim oHomeD

'Set oFSOD = CreateObject("Scripting.FileSystemObject"
'Set oHomeD = oFSOD.GetFile("c:\mydir\filename.txt"
'oHomeD.Delete

'Set oFSOD = Nothing
'Set oHomeD = Nothing


%>
You can make the path and/or the filename dynamic if you so choose (which sounds like you would) so that a new UNIQUE file is created eacn time the script runs.

You can also change the text after oHome.WriteLine to dynamic strings.

Of course, the user account must have write/change permissions on the directory that the file will be written to.

Hope this helps...

Doc
Rangewalk Digital Studios


Edited by - on 08 Nov 2002 14:57:52

I'll try to put that to work.

Thanks.

Thanks Dave. Everything works great.

I dont want to abuse, but there is something else that I need help with.

The data fields on the ASCII file needs to have a particular format for each of them like:

"0000field1","field2 "," field3.00"

What I mean is that the data not only needs to be delimited (by "" and ,) they also have to be a specific length. In some cases filled with spaces, in others filled with zeros, some are left justify and some others are right justify.

To be honest I dont have any idea about how to pad the dinamic strings that way.

Do you know how to do this? Could you please help me?

In advance, thanks for your time and patience.

Edited by - larrylaffer on 17 Nov 2002 18:40:52

ok, i know you supplied a good bit of info above, but give me one particular example to work with...

it can be done...we can work with lengths no problem...will all the padding be on the same side of the string (right or left) ?


Doc
Rangewalk Digital Studios

Hi Dave (or is it Doc?)

I guess its important to give you more information about the ASCII file too.

This file has to content on its first row information regarding the order itself like order number, client Id, date, total order's amount and tax. It will also content some other values that are constants. This is required in order to be imported correctly into the 3rd party application.

The second row, and subsecuents, includes information from the item ordered, like item id, unit price and quantity ordered.

As well as the first row, the second and subsecuent ones, requiere to include some other constant values too.

Each diferent item needs to go in a diferent row. Hence, aditional to the first row there will be the same number of rows as diferent items ordered. Obviosuly there always will be at least the first and second row.

As I mentioned before, my problem is that fields variable values on the text file (which comes from my database) not only needs to be delimited by commas and double quotes, they also must have a specific length and format.

For example, the total order's amount value stored on my database as "159.80" (which will go on the first row) has to be formated to " 159.80" .- A string aligned to the right with blank spaces to complete 14 characters. (After posting I noticed that for some reason spaces are not displayed but it should look something like "________159.8"

Another one, the quntity ordered for a given item is stored on my database as "4" (this value will go on the second row) has to be formated to " 4.00000" .- A string with the stored value plus a decimal point and 5 ceros, aligned to the left inserting blank spaces to complete a total of 15 characters. (________4.00000)

A last one. the item number stored on my database as "item54" (will go on 2nd row) has to be formated to "item540000000000" .- A 16 characters string aligned to the left inserting a given character (in this case ceros) to complete 16 characters.

This way, an example of the final file needs to look something similar to this:

"1","P"," ","CLIENT"," ","A","00000"," ","DD/MM/YY", AMOUNT.00, TAX.00,"-1"
"2","P"," ","ITEM540000000000", 4.00000, 0.00000, 1.0000," 1","pz","S"
"2","P"," ","ITEM080000000000", 1.00000, 0.00000, 1.0000," 1","pz","S"
"2","P"," ","ITEM100000000000", 15.00000, 0.00000, 1.0000," 1","pz","S"

As shown on this example the data coming from my database for the first row is CLIENT, DD/MM/YY, AMOUNT and TAX. The other values are the constants i previously talked about. For the second and subsecuent rows the data coming from my database will be ITEM..., 4.00000 (qty) and 1.0000 (price)

I hope I explained myself well, otherwise please let me know.

Another problem I have is that I dont know how o make the -for- loop for the ordered items array that inserts and formats the second and subsecuent rows.

In advance, thank you so much.

Edited by - larrylaffer on 20 Nov 2002 09:22:48

OK, I'm going to assume that you are going to tie what I'm about to post in with what's up above, so I won't rehash the code to write your values into a text file...I'll let you glue them together...

Here's the code...I coded it to write dashes where you want spaces just so you can see the ouput...you'll want to change that...also (obviously) I coded the variables to static values which you will want to change to use your dynamic values...


<%
'::::: EXAMPLE ONE
Dim varTAmount

varTAmount = "159.80"
If len(varTAmount) < 14 Then
Do While len(varTAmount) < 14
varTAmount = "-" & varTAmount
Loop
End If

'::::: EXAMPLE TWO
Dim varQTY

varQTY = "4"
varQTY = varQTY & ".00000"

If len(varQTY) < 15 Then
Do While len(varQTY) < 15
varQTY = "-" & varQTY
Loop
End If

'::::: EXAMPLE THREE
Dim varItemNum

varItemNum = "item54"

If len(varItemNum) < 16 Then
Do While len(varItemNum) < 16
varItemNum = varItemNum & "0"
Loop
End If

%>

Hope this helps...

BTW... family calls me Dave, friends call me Doc



Doc
Rangewalk Digital Studios


Edited by - on 20 Nov 2002 15:00:31

Oops...was working from a print out of your email and the tidbit about the array was on the second page...didn't see it...I don't understand what you're asking...

Doc
Rangewalk Digital Studios

Oops...was working from a print out of your email and the tidbit about the array was on the second page...didn't see it...I don't understand what you're asking...

Doc
Rangewalk Digital Studios

Hi Doc,

Certanlly that is a lot of help. Once again, thank you very much.

About the other question, what im trying to ask is how do i get the fields for the second row (and subsecuents) to be formated and inserted into the ASCII file

A little more explanation: After the order is recived and its information is stored into the database, the ASCII file is created and sent.

In order to create the file I need 2 recordsets. The first one will content the information for the order. This data will go on the first row and we know that it will always have one single record.

I dont have any problem getting the information for the first row formated and inserted into the ASCII file because the recordset will always have one single record.

However, the second recordset (rsOrderDetails) will content the information for the items ordered. We know that this recorset will content at least one record but it also can have more than one, as many as items were ordered. The fields from the first record of this recorset will go on the second row of the ASCII file, and IF there are more items ordered, each item will go on a subsecuent row.

In other words, the code I need has to look into rsOrderDetails to see how many records it has. For each record existing, the code has to format each field from this record (each filed has a diferent criteria). Once the fields for this record have the requested format, they are inserted into a new row into the ASCII file. This goes on for each record on this recordset. This is something like:


<%
For record i to N exiting in rsOrderDetails
format field1 this way
format field2 this way
...
format fieldN this way

oHome.WriteLine "Field1,Field2,... fieldN."
Go to next record existing in rsOrderDetails else
Send email with attachment
Delete created file
%>

Needless to say that i dont know how to write this code. I also know that its not as simple as I tried to explain it above.

Edited by - larrylaffer on 20 Nov 2002 22:56:37

Ok, don't want to sound dense, but I want to make sure that I show you exactly what you need...

First, are there multiple data sources (ie, one that contains client info and one that contains order info)...if so, is there a unique identifier that ties orders to clients.

Second, the data format is as follows??

client information
order detail 1
order detail 2
order detail 3
...

Doc
Rangewalk Digital Studios

Thanks for your interest. I know for sure that you ask for giving me the best help possible and I apreciate that very much.

Going back to your question, the orders data is stored in one table (Orders) and the fields for this table are: OrderId, OrderDate, ClientId.

Also, theres another table for orders details (OrderDetails) and it has the followin fields: OrderDetailId, OrderID, ItemId, ItemName, UnitPrice, QtyOrdered.

So, basically there are 2 data sources, and as you can see the OrderID field ties both tables.

By the moment I dont have a Order total amount, Item subtotal (qty ordered*price) and tax fields on my tables because I used to calculate them whenever I needed them on any page. However, since those 3 values have to be inserted into the the ASCII file, may be it will be helpfull to add them to my tables. I guess this would be easier than having to calculate them and then format them and then insert them. Do you think this could simplify things to us or could be of any help?

About the data format for the ascii file it goes like this:

Order information (order id, client id, date, Order's total and some other constants values)
order detail1 (item id, qty, price, tax and some other constant values)
order detail2 (item id, qty, price, tax and some other constant values)
order detail3 (item id, qty, price, tax and some other constant values)

Is this what you were asking me?

Edited by - larrylaffer on 22 Nov 2002 00:31:38

Yup...that's what I needed...

The following is, I believe, what you're looking for...I've commented it pretty well (I'm generally really bad about not commenting my code) so you should be able to figure out what's going on...

<%
Dim rsOrderInfo, rsOrderDetail

'::::: OPEN THE RECORDSETS -- KIND OF UGLY, BUT THIS IS THE WAY DW DOES IT AND I'M FEELING LAZY TONIGHT <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Set rsOrderInfo = Server.CreateObject("ADODB.Recordset"
rsOrderInfo.ActiveConnection = "Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass;"
rsOrderInfo.Source = "SELECT * FROM YourTable WHERE OrderID = '" & WhateverVariable & "'"
rsOrderInfo.CursorType = 0
rsOrderInfo.CursorLocation = 2
rsOrderInfo.LockType = 1
rsOrderInfo.Open()

Set rsOrderDetail = Server.CreateObject("ADODB.Recordset"
rsOrderDetail.ActiveConnection = "Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass;"
rsOrderDetail.Source = "SELECT * FROM Communities WHERE ID = N'" & rsOrderInfo.Fields.Item("OrderId".Value & "'"
'::::: ^^ NOTE THAT IN THE LINE ABOVE YOU VERY WELL MAY NEED TO REWRITE THE SQL STRING TO SUIT YOUR NEEDS ^^
rsOrderDetail.CursorType = 0
rsOrderDetail.CursorLocation = 2
rsOrderDetail.LockType = 1
rsOrderDetail.Open()

'::::: NOTE - I'M NOT SURE WHAT TEXT YOU WANT FORMATTED IN WHAT MANNER SO I'M GOING TO BE A LITTLE
'::::: NOTE - VAGUE HERE AND LET YOU PLUG IN THE APPROPRIATE FORMATTING CODE

'::::: CREATE THE FILE

Dim oFSO, oHome

Set oFSO = CreateObject("Scripting.FileSystemObject"
Set oHome = oFSO.CreateTextFile("c:\mydir\filename.txt"

'::::: WRITE THE ORDER INFO LINE
oHome.WriteLine -Formatted rsOrderInfo Variables and static text go here-

'::::: WRITE THE ORDER DETAIL LINES -
'::::: SET UP A LOOP TO RUN AS LONG AS THE RECORD SET FOR THE ORDER DETAIL IS NOT NULL
'::::: AND IN THAT LOOP WRITE THE ORDER DETAIL LINES INTO THE TEXT FILE

While Not rsOrderDetail.EOF
oHome.WriteLine -Formatted rsOrderDetail variables and static text goes here-
rsOrderDetail.MoveNext
Wend


'::::: CLEAN UP

Set oFSO = Nothing
Set oHome = Nothing

':::::: SEND THE FILE ON ITS MERRY WAY

Dim oCDO, oConf

Set oCDO = Server.CreateObject("CDO.Message"
Set oConf = CreateObject("CDO.Configuration"

With oCDO
Set .Configuration = oConf
.To = " "
.From = " "
.Subject = "Here's your file"
.TextBody = "File is attached."
.AddAttachment "c:\mydir\filename.txt"
.Send
End With

'::::: CLEAN UP

Set oCDO = Nothing

'::::: OPTIONALLY DELETE FILE AFTER EMAILING IT

'Dim oFSOD, oHomeD

'Set oFSOD = CreateObject("Scripting.FileSystemObject"
'Set oHomeD = oFSOD.GetFile("c:\mydir\filename.txt"
'oHomeD.Delete

'::::: CLEAN UP

'Set oFSOD = Nothing
'Set oHomeD = Nothing
%&gt;


Doc
Rangewalk Digital Studios

Wow! Thank you!! Thats exactly what I need.

And you say you were feeling lazy? Practically the whole code is in there, and the comments you included were clear and extent enough to be a great help.

I have put it all toghether with just a few adjustment to match my code requierements (im running this in a linux box) and it works.. perfectly.

The only problem Im having now (sorry for not asking this before) is on how to format a date field. The order's date is stored in my database (MySQL) as a timestamp field (for example 2002112209300) and I need to format this field to a "22/11/02" string (DD/MM/YY) to be included on the ASCII file. I tried using DW's short date format but that way it shows MM/DD/YY.

Could you help me out with this?

Finally I would like to name the ascii file with the coresponding order's Id (like order123.txt) instead of using a contant name (filename.txt). I guess I can figure out how to do this but Im stuck on how to specify the name of the file to be sent and deleted when its not a constant one.

I know that I have already asked too much so I hope this 2 last questions doesn result annoying. I promise they will be the last ones.

Thank you once again

There's probably a shorter way to get the results you want, but i don't have mySQL loaded up on this box so I can't play with the timestamp field...would have to wait til i got back home...

&lt;%
' ::::ECLARE OUR VARIABLES
Dim varDate, varNewDate

' ::::PEN A RECORDSET (MINE IS CALLED rsMyData) -- THE OPEN RECORDSET CODE IS NOT SHOWN HERE

varDate=day(rsMyData.Fields.Item("varDate".Value
varNewDate = Day(varDate) & "/" & Month(varDate) & "/" & Right(Year(varDate),2)

%&gt;

And if you look at line 8 of the last block of code I wrote, you'll see we wrote the SQL statement to use WhateverVariable. Assuming this is Order ID, we'll use this to name the file. Otherwise, you can replace WhateverVariable with whatever holds the Order ID....

(and I'll leave this formatted for the Windows file system instead of switching to *nix formatting simply for continuity's sake)

there are 3 line that contain the path to the file....Create, Mail and Delete....

replace
"c:\mydir\filename.txt"

with

"c:\mydir\" & WhateverVariable & ".txt"

in all three instances

as always, hope this is what you need...let me know if I can help you further...

Doc
Rangewalk Digital Studios

Edited by - on 25 Nov 2002 13:38:41

Yup, thats all I need. Up and running already.

Thank you Doc. You are great!