Simple TODOs - List 1

Major weaknesses in the module should be handled first:

1- Very heavy server load on product stock sync.
The current mechanism is :

• Magento cron job (10min) checks all products available on its DB.
• Check if they were synced to CheckoutApp(CA) before. If not, it is skipped.
• The confirmed products IDs are used in a SQL query to get all these products current stock in CA.
• Magento spends sometimes over 30sec (the maximum allowed time for the cron job to work) to loop over all the confirmed products to update their stock quantity.

Actually, cron job does not only do this stock sync, some other Magento’s built in functionalities are triggered too, along with any other orders sync in this module....well, cron job is busy.

Solution:

• Use the CA “lastmodified” timestamp value “stock” table ALTER introduced in previous post “Starting Over” to compare the changes done since last sync time.
• Create an API that handles the stock update in case stock quantities changes have been detected.

2- Proper error handling
Catching exceptions in code breaks cronjob (So they were removed). But in manual sync exceptions are caught fine.

3- Proper Tax Sync
Only simple tax rates are implemented currently. No tax groups or locations are implemented in to the sync.

4- Proper Product and Customer info (metavalue and metanumber) Update
Currently, if a certain detail is found different during sync, it is updated. CA actually handles this in a different way. CA inserts the detail as a new record. So when querying a product to show, it queries the last updated detail version.
This issue for customers causes problems. When an order is synced for a certain customer, then later this customer has some details updated, the previous orders will reflect the newest detail and not the detail at the order creation time.

5- Automatic Metatype
Metatype values play the role of mapping the metavalue and metatypes correctly. These values are required in the extension configuration. For convenience, the values input in Magento’s configuration should be done automatically after reading CA’s Metatype table.

Cracking The Code

Checkout’s DB sometimes uses bytea data columns to save some data.
From what I see, some of those are settings data, mostly in the preference table.
Checkout encodes the data before saving it to table, here’s an example:
\\200\\002}q\\001X\\006\\000\\000\\000youngiq\\002J\\315\\215jLs.
The past setting in preference should be holding Enstore’s store name “youngi” and the last sync time.

I found a bytea.py file in checkout’s root which contains few python functions about this encoding and decoding, but I am trying to translate it to PHP in order to use it. (it is a http://python.projects.postgresql.org 2005 project that I can’t find online now)

here’s the content of the file :

'PostgreSQL bytea encodings'

def bchr(ord):
        if not (32 < ord < 126):
                return "\\" + oct(ord).lstrip('0').rjust(3, '0')
        elif ord == 92:
                return r'\\'
        else:
                return chr(ord)

def encode(data):
        return ''.join([bchr(ord(x)) for x in data])

def decode(data):
        diter = iter(data)
        output = []
        next = diter.next
        for x in diter:
                if x == "\\":
                        try:
                                y = next()
                        except StopIteration:
                                raise ValueError, "incomplete backslash sequence"
                        if y == "\\":
                                pass
                        elif y.isdigit():
                                try:
                                        os = ''.join((y, next(), next()))
                                except StopIteration:
                                        raise ValueError, "incomplete backslash sequence"
                                try:
                                        x = chr(int(os, base = 8))
                                except ValueError:
                                        raise ValueError, "invalid bytea octal sequence '%s'" %(os,)
                        else:
                                raise ValueError, "invalid backslash follow '%s'" %(y,)
                output.append(x)
        return ''.join(output)

I tried hard to translate it to PHP, got close but still no success. What I guess is that the input is JSON, so when decoding I expect a JSON output, or at least some nodes and strings.

If anyone has any clue, share it please, coz this is quite important to decode some of the orders info in the DB.

my trial is like follows:

public function bchr($ord)
        {
                if (!((32<$ord)&&($ord<126)))
                {
                        $result = "\\\\". str_pad(ltrim(decoct($ord),0),3,0, STR_PAD_LEFT);
                        Mage::log('1='.$result);
                        return $result;
                }
                elseif ($ord==92)
                {
                        $result = '\\\\';
                        Mage::log('2='.$result);
                        return $result;
                }
                else
                {
                        $result = chr($ord);
                        Mage::log('3='.$result);
                        return $result;
                }
        }
        
        public function encode($data)
        {
                $joined = '';
                $pregData = preg_split('//', $data, -1, PREG_SPLIT_NO_EMPTY);
                foreach ($pregData as $x)
                {
                        $ord = ord($x);
                        Mage::log('x='.$x);
                        Mage::log('ord='.$ord);
                        $joined .= $this->bchr($ord);
                }
                
                return $joined;
        }
        
        public function decode($data)
        {
                $pregData = preg_split('//', $data, -1, PREG_SPLIT_NO_EMPTY);
                $output = '';
                foreach ($pregData as $x)
                {
                        if ($x == '\\\\')
                        {
                                $y = next($pregData);
                                //Mage::log($y);
                                if ($y == '\\\\')
                                {
                                        continue;
                                }
                                elseif (is_int($y))
                                {
                                        $os = $y.next($pregData).next($pregData);
                                        $x = chr(intval($os, 8));
                                        Mage::log($x);
                                }
                                else
                                {
                                        //Mage::throwException('Invalid Backslash Follow $y');
                                }
                        }
                        $output .= $x;
                }
                
                return $output;
        }

Old Vs New Concept

The major drawback in my current module is that all the processing is done from Magento's side. Having this server somewhere and Checkout server in your store will cause major delays. Connections are done from Magento to Checkout's database, processes take place and the final instruction is done.

My thought is to have an API layer on Checkout's server and let it handle all DB connections and give results to Magento.

Since I am aware of Magento's APIs and framework construction, I think I will make the new module actually 2 extensions.

1- Dummy Magento Installation and have a (server-side) extension to handle Checkout DB connection and provide secure API externally.

2- The real Magento Installation to have (worldwide-side) extension.

I think this construction will save me lots of time, coz I will eventually have to kind of (split) the current extension to have a server side, and a worldwide side. So alot of work is already done.

Previously I had in plan to rewrite the Tax sync codes for proper syncing during initial setup, but never had the time to do that. I think this is the main reason people do not succeed getting my extension to work.

Starting Over

It's been a while we had our store working with the magento-checkout extension I wrote. But obviously, it's buggy. I figure out how to avoid all bugs coz I wrote the code, but I want to share everything and so everyone can benefit from my work.

So, I am rewriting the whole thing. I will revamp the whole extension.
I have been testing with Checkout's integrated ecommerce (EnStore). First it will not work in my country, nor with my currency. So to test, I put location the US, and USDollar currency.
Also Enstore is trying to lease the hosting, which makes it inconvenient of using a "username.enstore.com" address instead of your own address (like "blabla.com").

From what I see, they are making Enstore work as a interface to Checkout's DB, they will provide APIs for integration. Also it is very under construction and far from being done, this is why it is for free while in beta. Magento has lots of payment and shipping methods too, and very integratable, and since we have access to Checkout's DB then we can work on a clean Sync solution.

I will put in mind to be able to Sync in a Checkout->Magento way. My previous module was a Magento->Checkout solution, maybe I will use some features in the end to enable sync both ways.

First thing I noticed about integrating Enstore with Checkout. Preferences table holds the webstore identification and last sync time.
The sync was meant to be MANUAL, I will put in mind to make possible syncing to Magento Automatically.

Products info (like name, price,barcode...etc) all are saved in metavalue and metanumber tables along with a timestamp to figure out if this was changed after last sync was done... (that is very helpful).

BUT, STOCK....stock does not have a timestamp in stock table. Obviously in Enstore, there are no quantities being added during sync. This is not convenient as what if the product is out of stock, you shouldn't have to manually disable the product from being sold, this should be done automatically.

I figured out how to add a timestamp column to the stock table that auto updates on creation or on modification.(thanks to POINTBEING.NET). So I will modify the stock table, which I think will make no problem upgrading later to newer versions of Checkout...but hey let's see over time.

The code for adding this column is:

ALTER TABLE stock
ADD lastmodified TIMESTAMP;

ALTER TABLE stock
ALTER COLUMN lastmodified
SET DEFAULT CURRENT_TIMESTAMP;

UPDATE stock
SET lastmodified=CURRENT_TIMESTAMP;

CREATE OR REPLACE FUNCTION update_lastmodified_column()
RETURNS TRIGGER AS '
BEGIN
NEW.lastmodified = NOW();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER update_lastmodified_modtime BEFORE UPDATE
ON stock FOR EACH ROW EXECUTE PROCEDURE
update_lastmodified_column();

Keep checking for news

Some Basics

I had few emails asking some basic questions, and to make usefulness to everyone I'm posting my replies here along with the latest versions.

Well first of all, The HOST STATIC IP in the server configuration should not be "localhost".
Your magento installation is at your remote server(most probably) while your checkout installation is on your computer, what you're telling magento is to try to connect to the postgresql installed on the same machine as magento which is incorrect.

I highly suggest you try everything on your local machine.
A few tips on getting this host locally:

Make a backup of your magento store from System->Tools->Backups.
download it from your var/backups on your magento server.
Then compress your installation files to a zip file (you can skip the media folder coz it must be big and full of images), you can skip var/backups because it will be full of your database backups, and you can skip the downloader folder too.
Then host this magento installation on your computer (use MAMP, it's great).
use "Sequel Pro" to connect to magento's mysql database and make sure that you change core_config_data table the "base url" and "base secure url" to "localhost". if you don't do this, magento will not display properly on your browser because it will not be able to find its required files (css and images)

This way you can test further and be sure you will not break your real shop.

One thing to clear out. At the end, when you want to make the true connection with your live installation and your checkout installation, you must make sure the machine where checkout is installed does have a STATIC IP. This is a must so that your remote server will be able to find it and connect to it each time it needs. Or else you will not be able to work live.
Probably if you do not have STATIC IP, your ISP will charge your for it a little but not much.

The postgresql database you should be connecting to should be Checkout App's. And if everything's set to default when you first created Checkout App's DB then the port should be 5505. Also if Checkout is installed on your computer and your magento is installed on the same one then the host should be "localhost" or "127.0.0.1" depending on if connecting through socket or not.

Going to Administrate stores was meant to show you Checkout App's DB name so you put it in the configuration of the extension, but I was not in focus that time that I wrote the code to use the DB name set in the configuration...so to bypass this issue, put in the configuration DB name: postgres.

After you get the Administrate stores connecting to your server and retrieving your Checkout DB name, put it back in the config.
Administrate stores will also help you set your store order number.

If you go through this and you face other issues, send me back, i'll do my best to help you with your project.

Version 1.3.2Beta
http://www.megafileupload.com/en/file/240535/CheckoutAppSync-1-3-2-tgz.html

Beta 1.0.0 Release

Just done packaging newest Release.
Release submitted to Magento Connect and awaiting approval.

Fixed issues with installation and database tables creation.

Features:
-Customers sync/detect changes and update
-Tax rates sync
-Sync orders
-Sku, name, prices(and special price), cost, tax assigned for simple products
-Sync stock to checkoutapp (auto create a purchase order like checkoutapp's own products import from file feature)
-Sync variations to checkoutapp (product attributes in magento)
-Sync stock back to Magento
-A Sync Schedule for auto order push and auto import stock to update magento's
-Cron Jobs for automation(depends on the sync schedule)
-Update Order Info (print packingslip, shipping rate calculation)
-Aramex Shipping AirWayBill PDF creation

Download link: http://www.megafileupload.com/en/file/221551/MageCheckoutAppSync-1-0-0-tgz.html

Progress

Here's the progress so far:

-Customers sync/detect changes and update (done)
-Tax rates sync (done)
-Sync orders (done)
-Sku, name, prices, cost, tax assigned for simple products(done)
-Sync stock to checkoutapp (done...by auto create a purchase order like checkoutapp's own products import from file feature)
-Sync variations to checkoutapp (product attributes in magento) (done)
-Sync stock back to Magento (done)
-Automate these actions somehow, like with cron jobs or trigger after event (done...only implemented for auto order push and auto import stock to update magento's)
-Create a kind of log of each process/sync done for revising and error handling/fixing situations (done...only for the automated features, logs errors and success)

I'm planning to release a beta version asap, and a demo video.