Data Migration from Quickbooks Point of Sale

Comments

56 comments

  • Avatar
    Chris Muench

    I know Qb pos can export a lot of data; but can you share some data from reports that we can look at? Our api or database insert can be one way

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I think I'm going to have to use database insert. I was able to use a third-party ODBC driver to dump the entire database into an Access database which I can actually manipulate the data with. 

    The listing of tables and their columns is here:
    https://doc.qodbc.com/qodbc/pos/TableList.php

    My plan at this point is to extract the data into Excel/map the data to the appropriate PHPpos tables, save as a CSV, and then upload to MySQL. 

    Unless there's a better idea?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    That should work well. All the tables are phppos_sales*. let me know if you have any questions.

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Do you have any reccomendations on what order to import the tables? Does it matter?

    Like do I need to do Customers first, then Sales Receipts, then Deliveries? Or....?

    I feel like as long as I stay out of the actual program while manipulating the database then there shouldn't be any major issues

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It actually might be easier to use the API because it will handle putting things in right tables

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I was thinking that too in reality, and although I've learned a lot about php/mysql over the years, I'm not sure I have the skills to write a script to do this. I'm sure I could figure it out with enough time but I feel like I'd spend more time coding and debugging than I would creating a raw import. 

    I've combed through the DB schema for PHPpos in MySQL fairly thoroughly...maybe I'll try a couple of tables and see what happens

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Okay so after futzing around with transforming the data manually, I'm realizing that the biggest problem is not pulling the exact data needed for the tables but rather that I am developing on a Windows machine and that's not playing nicely with my Linux server. 

    Realizing that doing it this way will kill me if I continue on so I'm going with plan B and using the API instead. 

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    So I've written a couple PHP scripts to pipe the data through the API with some success. Can the "create_date" column in the "phppos_people" table be updated via the API? I may be splitting hairs here, but I don't want to lose that data. "You've been a valued customer since December 2013!", etc. 

    If not the API can it be updated via DB insert?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It is an undocumented feature of our API (as we don't really want people to do this); but you can pass created_at as a parameter and it will overwrite this.

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Do you know what the php "format" is for the Date/Time format required by the API?

    I'm extracting the data alright but passing it in with "created_at" isn't working and I think it might be due to an incorrect date/time number format

     

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    YYYY-MM-DD

    2019-01-02 for example

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I think I figured that out. 

    However now I am running into the problem with API call lockouts while running my migration script. I think you mentioned before that the default limit is 20 calls per minute but this can be overridden in the download version?

    I just need to modify this for the migration and plan to change it back once in production

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Nevermind found it myself :)

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I'm having some trouble with pulling in Items via the API. I've tried passing in both the value of the "id" column and also the person_id column as the supplier_id to the API but no matter what I pick the supplier doesn't populate. Had any other reports of this or should I continue examining my code?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Can you post code sample (make sure you DONT put in api key or url)

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I found the problem I believe and it's a bug report. 

    In /pos/application/controllers/api/v1/Items.php, you are missing any definitions for "supplier" in your private function _create_item($item_request)

    I see it in the function for "Get"/search requests, but not the one for Post requests.

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    As a short-term fix until you can look at this, I added this line to that function and it fixed my problem:

    'supplier_id'=>isset($item_request['supplier_id']) ? $item_request['supplier_id'] : $this->Supplier->get_supplier_id($category_name),

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Thanks, will patch tomorrow and let you know if I use same solution

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    As I am trudging along through my inventory list I found another missing option.

    "is_service" can be passed through the API but similar to above, private function _create_item($item_request) is missing any data to process this :)

    Fixed this for me:
    'is_service'=>isset($item_request['is_service']) && $item_request['is_service'] ? 1 : 0

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    This has been patched.

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Will be released in 1 hour

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I just downloaded the latest hotfix release but it doesn't show any changes....

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    I just downloaded it again and it did change

     

    diff --git a/shared/phppos/htdocs/application/config/constants.php b/shared/phppos/htdocs/application/config/constants.php

    index 7699f43d..70af0d74 100644

    --- a/shared/phppos/htdocs/application/config/constants.php

    +++ b/shared/phppos/htdocs/application/config/constants.php

    @@ -94,7 +94,7 @@ defined('EXIT__AUTO_MAX')      OR define('EXIT__AUTO_MAX', 125); // highest auto

     define('NUMBER_OF_PEOPLE_CUSTOM_FIELDS',10);

     define('APPLICATION_VERSION', '16.3');

     define('ASSET_TIMESTAMP', '1550247487');

    -define('BUILD_TIMESTAMP', '1551191294');

    +define('BUILD_TIMESTAMP', '1551363752');

     

     /*

     |--------------------------------------------------------------------------

    diff --git a/shared/phppos/htdocs/application/controllers/api/v1/Items.php b/shared/phppos/htdocs/application/controllers/api/v1/Items.php

    index 4db0cfc9..8544267e 100644

    --- a/shared/phppos/htdocs/application/controllers/api/v1/Items.php

    +++ b/shared/phppos/htdocs/application/controllers/api/v1/Items.php

    @@ -65,6 +65,7 @@ class Items extends REST_Controller {

                                            'item_id' => (int)$item->item_id,

                                            'name' => $item->name,

                                            'item_number' => $item->item_number,

    +                                       'supplier_id' => $item->supplier_id,

                                            'product_id' => $item->product_id,

                                            'size' => $item->size,

                                            'expire_days' => $item->expire_days,

    @@ -834,6 +835,7 @@ class Items extends REST_Controller {

                                                    'cost_price'=>isset($item_request['cost_price']) ? $item_request['cost_price'] : 0,

                                                    'disable_loyalty'=>isset($item_request['disable_loyalty']) && $item_request['disable_loyalty'] ? 1 : 0,

                                                    'tax_included'=>isset($item_request['tax_included']) && $item_request['tax_included'] ? 1 : 0,

    +                                               'is_service'=>isset($item_request['is_service']) && $item_request['is_service'] ? 1 : 0,

                                                    'change_cost_price'=>isset($item_request['change_cost_price']) && $item_request['change_cost_price'] ? 1 : 0,

                                                    'override_default_tax'=>isset($item_request['override_default_tax']) && $item_request['override_default_tax'] ? 1 : 0,

                                                    'tax_class_id'=>isset($item_request['tax_class_id']) ? $item_request['tax_class_id'] : NULL,

    cmuench@cmuench:PHP-Point-Of-Sale-Installer (master=)$ 
    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Sorry for the delay - I had some problems with the Dev server this afternoon.

    I just gave it a shot with the latest update ensuring that patch above is applied but unfortunately it's still not working. Supplier isn't being imported at all and ALL my items are being set to "is_service" = TRUE

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    You need to create a supplier using the suppliers end Point; you can’t bundle it in with an item request

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Whoops, nevermind about the last part. I was passing the variable wrong

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    And I did. I created the suppliers list FIRST and then ran the API calls to create the items. I can confirm that passing the person_id of the supplier to the API does not work

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Did you pass it is supplier_id of person_id

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I passed it in as "supplier_id": (person_id, as an integer)

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    I have done another release that fixes this

    0
    Comment actions Permalink

Please sign in to leave a comment.