Transitioning from Sebenza Order Status

[mikel]

We'd obviously like to ditch this module in favor of the built in option. We have records dating back to 2006 in our current store and if possible would like to get those imported into the built in option. Since there is no off the shelf module for doing this, I took a look at the tables in MySQL and it appears we may be able to simply export select fields from the s02_SS_STATUS_OrderItems table, then import those fields into the corresponding fields in the built in options table (or tables). I see that the build in option has a ship date field that the Sebenza module doesn't have so curious if leaving that out would be a problem and if there are any other challenges to getting this done.


Has anyone done this? How feasible is it?

Mike

[K Series Parts]

Re: Transitioning from Sebenza Order Status

I did it about a month ago, it wasn't too difficult just takes a lot of time because of the import. I created a php script that generates the xml. I had to manually update the cancelled items and orders because there isn't a way to upload that for some reason. Our orders go back to 2007 and it took two of us about 4+ days to get everything cleaned up and accurate. If you don't care too much about the older orders then you don't really have to worry about that step. You can upload the back orders as well but we didn't have too many of those and just manually did it.

the $low and the $high was because I did it in batches of 1000, it didn't really like to upload anything over that without a lot of time. sometimes you get a timeout and just have to go back and import it again until it completes. it skips orders that are already updated so that is how you can work on full batches.

header("Content-Type: application/xml; charset=utf-8");
header('Content-Disposition: attachment; filename="orders-' . $low . '-' . $high . '.xml"');
print '<document>';
$sql = "SELECT * FROM s01_SS_STATUS_Orders WHERE id BETWEEN $low AND $high ORDER BY id ASC";
$result = query_mysql($sql, $link);
if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        
        //variables
        $order_number = $row['id'];
        $order_status = $row['status'];
        $order_tracking_number = $row['tracknum'];
        $order_tracking_type = $row['tracktype'];
        $order_day = date('d',$row['orderdate']);
        $order_month = date('m',$row['orderdate']);
        $order_year = date('Y',$row['orderdate']);
        $order_minute = date('i',$row['orderdate']);
        $order_hour = date('h',$row['orderdate']);


        if ($order_tracking_type == 'FEDEX') {
            $order_tracking_type = 'FedEx';
        } elseif ($order_tracking_type == 'PILOT') {
            $order_tracking_type = 'Pilot';
        }


        if ($order_status == 'Shipped' && $order_tracking_number != null) {
            print '<OrderShipment_Add order_id="' . $order_number . '">';
                print '<Code>' . $order_number . '</Code>';    
            print '</OrderShipment_Add>';


            print '<OrderShipment_SetStatus code="' . $order_number . '">';
                print '<MarkAsShipped>1</MarkAsShipped>';
                print '<TrackingNumber>' . $order_tracking_number . '</TrackingNumber>';
                print '<TrackingType>' . $order_tracking_type . '</TrackingType>';
                print '<ShipDate>';
                    print '<Day>' . $order_day . '</Day>';
                    print '<Month>' . $order_month . '</Month>';
                    print '<Year>' . $order_year . '</Year>';
                    print '<Minute>' . $order_minute . '</Minute>';
                    print '<Hour>' . $order_hour . '</Hour>';
                print '</ShipDate>';
            print '</OrderShipment_SetStatus>';
        } elseif ($order_status == 'Shipped' && $order_tracking_number == null) {
            $item_counter = 1;
            $itemsql = "SELECT * FROM s01_SS_STATUS_OrderItems WHERE order_id = '$order_number'";
            $itemresult = query_mysql($itemsql, $link);
            $num_item_rows = mysql_num_rows($itemresult);


            if ($num_item_rows != 0) {
                while ($itemrow = mysql_fetch_assoc($itemresult)) {


                    //variables
                    $item_order_number = $order_number . '-' . $item_counter;
                    $item_counter++;
                    $item_product_code = $itemrow['code'];
                    $item_quantity = $itemrow['quantity'];
                    $item_status = $itemrow['status'];
                    $item_tracking_number = $itemrow['tracknum'];
                    $item_tracking_type = $itemrow['tracktype'];


                    if ($item_tracking_type == 'FEDEX') {
                        $item_tracking_type = 'FedEx';
                    } elseif ($item_tracking_type == 'PILOT') {
                        $item_tracking_type = 'Pilot';
                    }                    


                    if ($item_status == 'Shipped' && $item_tracking_number != null) {
                        print '<OrderShipment_Add order_id="' . $order_number . '">';
                            print '<ProductList>';
                                print '<Product product_code="' . $item_product_code . '" quantity="' . $item_quantity . '"/>';
                            print '</ProductList>';
                            print '<Code>' . $item_order_number . '</Code>';
                            
                        print '</OrderShipment_Add>';


                        print '<OrderShipment_SetStatus code="' . $item_order_number . '">';
                            print '<MarkAsShipped>1</MarkAsShipped>';
                            print '<TrackingNumber>' . $item_tracking_number . '</TrackingNumber>';
                            print '<TrackingType>' . $item_tracking_type . '</TrackingType>';
                            print '<ShipDate>';
                                print '<Day>' . $order_day . '</Day>';
                                print '<Month>' . $order_month . '</Month>';
                                print '<Year>' . $order_year . '</Year>';
                                print '<Minute>' . $order_minute . '</Minute>';
                                print '<Hour>' . $order_hour . '</Hour>';
                            print '</ShipDate>';
                        print '</OrderShipment_SetStatus>';
                    }
                }
            }
            mysql_free_result($itemresult);
        }
    }
}
mysql_free_result($result);
print '</document>';

[mikel]

Re: Transitioning from Sebenza Order Status

Awesome Chris, that's a huge help.

[K Series Parts]

Re: Transitioning from Sebenza Order Status

let me know if you run into anything and I will try to help where I can.

[oliverands]

Re: Transitioning from Sebenza Order Status

I hadn't been using the order status function until about a year ago. Everything in Miva was in a status of processing because I was using another system for managing shipping and didn't give customers access to shipment info in my store. I decided to start using shipment tracking and wanted to set all past orders to a status of shipped and to bring in shipment information where I had it.

I started by using the CSV import process to mark all past orders as shipped. I generated a CSV file of all order numbers in Miva. I added tracking type and tracking numbers, where available, from data I had in other systems. I imported the CSV file, and it worked without any issues. I don't remember the import taking an exceptionally long period of time. (Just make sure that your shipment shipped email is turned off before you do this. Importing an order number changes the status from processing to shipped and kicks out that email if you have it enabled.)

The Miva import template for shipments has these fields.

* Order (order number)
* Shipment_Code (I'm not sure what this is; I leave this field blank in my daily import process)
* Tracktype (UPS, USPS, FedEx, etc. to match the tracking types you have set up--used to generate the URL for the tracking link)
* Tracknum (the tracking number)
* Cost (your shipping cost)

The only field that you need is Order. Importing the file with the order number in this field will change the order status from processing to shipped. All other fields can be left blank if you don't have the data. You'll note there is no shipment date field in the import file. The store pages for customers to see past order history don't show a shipment date to the customer.

You shouldn't run into any issues if you generate a good CSV file and use the data import process in Miva.