Custom Order Form

We use an order form that is created in excel. This form is changed once a year as new products are created. What I have been able to do is place a second worksheet in the file with product order on it. The order form worksheet uses VLOOKUP to pull the information from the second worksheet.



I would like to be able to export selected orders to the second worksheet of the excel file and email to us (each order emailed separately). Then we would print the order form sheet from the excel file.



So I would need to be able to provide the excel sheet in the admin side so if when we need to change it we can.



Other thoughts: Maybe as each order is placed the above could be email to us.



It needs to be a real excel file not a csv with. xls extension as we would lose all formatting and functions.

We would need to be able to define a subject line that would include the USERNAME. This way or people will be able to sort orders from other email.



Finally this is for our B2B (version 2.1.4) site so I would like this as an addon so I can use this on our retail site (2.1.1) also.



After doing a bit of reading, maybe it could be done like this:

Using an XML files. From the admin side I could upload the first worksheet of the xml file. Then as orders are place the addon would grab the order info and create the second worksheet of XML file and combine with the first and email it.

I have figured out the queries just not how to do the php part. Have not tested my queries on the 2.1.1 version yet.

Does this sound reasonable? Would like to find someone that could do this at a reasonable cost.



Thanks,

David DeWitt

Is this not possible, or has not body ever tried it.



T

Its hard to understand :)



Maybe upload some examples of your sheets so we can get our heads around it.

I have included the excel file (2011 Order Form.xls) in zip file and the other method of an xml file (2011 Order Form.xml) The first sheet “ORDER FORM” is our standard order form. I have use the vlook fuction to pull data from the second sheet “NEW ORDER”. Note: I have not completed putting the vlookup in cells. Some how I would like to use admin or I could ftp to a location on server the first sheet “ORDER FORM”. As orders are entered in the website I would like the data to be enter into second sheet “NEW ORDER” and then the complete excel file email to the standard orders email.



The other method would to be to do the same thing but with an XML file which then can be open with excel once it is emailed. I find that it would be easy to export the first sheet “ORDER FORM” from excel in XML format. I would have to edit out the last line of this file . I would place on server as above. Then as orders are entered on website it would some create the XML file of the data with a as last line. Merge the two file then email.

Note: the supplied XML file (2011 Order Form.xml). The first sheet is lines 1 - 4374. The second sheet(created by the website) starts at line 4375



I have created 2 SQL queries that I believe will pull the corret data. included in file (order query.txt)

I hope this can be an addon used in both version 2.1.4 and 2.1.1





I hope that I have been able to clarify what I hoping to acomplish.

Thanks for your help,

David DeWitt

order query.txt

2011 Order Form.xml

2011 Order Form.zip

Well I have been taking a stab at extracting and creating an XML file. Not with the correct format yet. Just trying to get any kind of result. But not having very good luck. My output to the browser is:



Could not complete database query



I have edit this post a bit. Found one stupid mistake. Now I get the above error from mysql_query. I think it hase to be in my query.



Here is my phpcode, hopefully some one can spot my error. The query should return 4 records. It works in phpmyadmin.


```php

/*
got this from http://www.techrepublic.com/article/create-xml-with-mysql-and-php/5035149
*/

require_once ('./mysqli-connect.php'); // Connect to the db. This does work for other script I wrote


// Create query for the database...

$query = "SELECT product_code, price, amount FROM cscart_order_details WHERE order_id=10 ";
// Make the query:
$result = @mysql_query ($dbc, $query) or die("Could not complete database query"); // Run the query.
if($result) {echo "ok 1234
";} //TESTING*********check if for results
echo mysql_num_rows($result); // TESTING**** try to show number of rows
$num = mysql_num_rows($result);


if ($num != 0) {
$file = fopen("results.xml", "w");
$_xml ="\r\n";

$_xml .=“\r\n”;

while ($row = mysql_fetch_array($result)) {

if ($row[“pageTitle”]) {

$_xml .=“\t\r\n”;

$_xml .=“\t\t” . $row[“pageLink”] . “\r\n”;$_xml .=“\t\r\n”; } else {

$_xml .=“\t\r\n”;$_xml .=“\t\tnone\r\n”;

$_xml .=“\t\r\n”; } }

$_xml .=“”;

fwrite($file, $_xml);

fclose($file);

echo “XML has been written. View the XML.”;

} else {

echo “No Records found”;

}



mysqli_close($dbc); // Close the database connection.

?>

```



Thanks for looking at it,

David DeWitt

Well I got a major breakthrough (at least for me) I got the above code to finally give me some real output. Now to slowly massage it to give me output formated the way I want. I will still need help intergrading this into cs-cart, but I'll get this thread updated.



Dave

mysql_query takes the SQL as the first argument and the connection string as the second. You have it reversed.



You are looking for $row['pageTitle'] but it's not in the field list you provided to the SQL and all your item rows are dependent upon that being in the query.



Suggest you also add

error_reporting(E_ALL);

ini_set('display_errors', true);



to the start of your script so you can see that you're referencing array indexes that don't exist (like 'pageTitle').

Thnaks tbirnseth,

did not catch the reversal. I'll have to check my other script I wrote a wihile back, thought I just copied that part.



as with the second half I was using that I dea and had not made changes to meat my fields.



I now have made much headway and hope to post my code soon for inspection from all the talented cs-cart users.



Dave

Would help if you'd post info up to the point where you encounter problems (your first chunk) and not post stuff you haven't even looked at yet.

[quote name='tbirnseth' timestamp='1308888810' post='115656']

and not post stuff you haven't even looked at yet.

[/quote]

Sorry about that, I had worked on that part, it got to be a mess so started over and recopied that section in. Did not think about that before my earlier post.

Dave