Data Migration from Quickbooks Point of Sale

Comments

56 comments

  • Avatar
    Tynan Fox

    Thanks for all your help thus far. I am continuing to write migration scripts to move each type of data over to PHPPos. For some things I'm using the API and others I am using direct database insert. Overall it's going okay. 

    I'm working on migrating my Receivings (QBPOS refers to these as "Vouchers"). The API functionality for "change_cart_date" is broken. I have passed through the Date of the Voucher and I've checked my json etc. Despite being passed in, the "receiving_time" column is not being updated in PHPpos.

    Edit: The following fixed it for me. I added one line to application\controllers\api\v1\Receivings.php:

    Starting on line 221:
    if (isset($receiving_request['change_cart_date']) && $receiving_request['change_cart_date']){
      $this->cart->change_cart_date = date('Y-m-d H:i:s',strtotime($receiving_request['change_cart_date']));
    }

    Change to this:
    if (isset($receiving_request['change_cart_date']) && $receiving_request['change_cart_date']){
      $this->cart->change_date_enable = TRUE;
      $this->cart->change_cart_date = date('Y-m-d H:i:s',strtotime($receiving_request['change_cart_date']));
    }

     

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    On a somewhat related side note, I'm curious about what the benefit of passing item data in the API cart is? It appears that in order to build a cart, the API must be supplied with the item_id. UPC, Custom Fields, Item Name, Product ID, etc are optionally passed into the API but it would appear that doing so does nothing. The Item ID is required by the API and without it it won't create the cart, and the rest of the data seems to be ignored. 

    Unless I am missing something?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    The price fields will make changes to the cart but you shouldn't pass product_id, but some fields such as quantity and unit_price will affect it. This is a documentation error.

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Also I have made your changes for the dates and am working on releasing now

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Thanks for the updates and thanks for taking my bug reports so seriously. :)

    While debugging my attempts to insert my history of Physical Inventory Counts, I've noticed that deleting a Physical Inventory Count while in the actual PHPpos program (the real front-facing one we're supposed to use LOL) deletes the Physical Inventory, but it does not delete or otherwise correct the Inventory Count Adjustment entries in the phppos_inventory table created by the physical inventory. Is that intentional?

    For example, I did a fake Physical Inventory on a couple items to learn how a PI writes data to the database. Then I deleted it. The PI is gone from both the phppos_inventory_counts and phppos_inventory_counts_items tables, but the Count Adjustment entries in phppos_inventory persist. 

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    I don't think I am quite following. The phppos_Inventory table is just a log table. The actual inventory value is stored in phppos_location_items

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Well, that might explain part of it. Lol.

    In any event, do a simple short Physical Inventory. In the item list, quantities are updated. Now delete the PI just created. Go back and look at the item in the items list. The Quantity has not reverted back to it's pre-Inventory level. (All of this done in the acutal program of course).

    Is that intentional?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    The only time the inventory is updated is when you choose close count and update inventory. When you delete it doesn't revert

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Okay thanks - good to know :)

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Okay I have now successfully been able to migrate absolutely all my historical data except the last and biggest hurdle - all 127,000 of my sales receipts. 

    Planning to use the API for this. Is there a way to pass a variable tax amount to the API rather than just a tax category? Over the past 6 years, our sales tax rate has changed 4 times. What would you do? Create 4 different tax categories?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Tax charged is based on the current tax class of an item at the time of sale. The only way would to temporarily have 1 tax group that has all the taxes and then pass the ones you don''t want to charge via excluded taxes

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    How does one pass in the Delivery Fee via the API?

    Thus far, we have used variable delivery fees in QBPOS, charging the customer an amount based on the actual price of the shipping charge, so I don't have one set formula at all for calculating the Delivery Fee. Can this be passed in through the item cart like any other item?

    Additionally, it looks like the item "Delivery Fee" is not created until the very first time the user creates a delivery, when it is built on-the-fly. Is there a predictable pattern to it's generation i.e. is there any way to know ahead of time what the POS will assign to the Delivery Fee for the item_id?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Delivery fee is just another item; I would pre-create it in php pos and use that item_id

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Hey so, that  correction you needed to make for Receivings to set cart_change_date = TRUE; ? 

     

    ....you need to do that to the Sales.php script in the API as well. :)

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It is the latest I see it in there 

     

     

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Ahhh I hadn't updated. Did that and now I see it, thanks. 

    And (hopefully) one of my final questions. I wrote a script to bring in the sales receipts and it's running smoothly, error free after 17,000 transactions. But - understandably - it's moving fairly slowly. 

    What, if anything, can be done to tune the API for performance? It's working great but unfortunately, at the timing/rates I have calculated, the script is going to need to run for about 48 hours to complete the migration - and that's if it doesn't fault or trip up on a random stray broken receipt along the way. 

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    There isn’t a way; just to also note you can only make 20 api requests a minute but it sounds like you aren’t going to meet that

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I actually figured out in the API files where to turn up the limit on requests and have done that. I can see why it's there for a POS that is in active use/production and I'm sure I'll be turning the limit back to the normal level once the migration is done

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    What might cause the API to fail to create an item?

    Most all of my items get created just fine but there are a select few I am submitting via the API which create....a confusing result. 

    A new item is created with a new item_id, but it's completely blank. Every single field is empty. Examining the item in the database shows that all columns for the item row are empty, null, or 0.0000000.

    Unfortunately the API is not returning to me a response of any kind. My script just dies because there's no response at all, so I have no response information to post for debugging, etc. I wish it was returning an error of some sort so I could work on it but for this one? I'm just lost. :(

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It should return an error; if you capture the request being sent that would help replay the request

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Here is the JSON encoded payload I am passing. I have attempted this in both my Dev server and my Production server and both produce the same problem. And as I said, my cURL $response is empty. Not even any errors.

    {"name":"Chrome Ring - 1.75","unit_price":"32.50000","cost_price":"12.20000","category_id":"25","item_number":"0892280013722","locations":{"1":{"quantity":0}},"custom_fields":{"ListID":"-5073570248825863935","ALU":"892280013722","Size":"1.75"}}

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    I think I figured it out. 

    item_number must be UNIQUE. The script I was running was trying to add back in items which I had deleted. In the meantime I had created new items in the system with the same UPC code - and PHPpos is faulting because an item with that item_number already exists. 

    So I need to add an error trap to my script to prevent this from happening - but you may want to look at this from your side as well. When creating an item via the API and the item_number passed already exists, PHPpos spazzes out it would seem. A blank item is created, the auto-increment value of item_id skips a number somehow, and the API returns a blank response. :(

    Edit: I have also just run into this behavior when incorrectly attempting to add a Customer with an account_number that already exists as well, since this field must also be unique. A blank customer record is generated with blank and empty fields in the database, the API returns an empty response, and the auto-increment value is miscalculated (the next correctly generated customer has a person_id that is +2 from the previous one, instead of +1). 

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    As I'm chugging along through my receipts, I've added some data validation functions to make sure the API/PHPpos is calculating everything out correctly to match my current master records/what was fed into it. 

    Unfortunately I'm running into problems still with tax calculation. I had been going with your suggestion of using excluded taxes and it seems to work okay when there is one tax rate for the whole receipt. However, I have some receipts where the whole receipt was taxable in general, however the individual line items on the receipt were changed to non-taxable during the transaction(we have to do this for a variety of regulatory reasons). It would seem in the API, there's no way to be able to override the default tax rate for the cart items on-the-fly, EXCEPT for the Delivery Fee item. 

    Would it be possible to expand the function which overrides the default tax on the Delivery Fee to allow this to be passed in through ANY cart item in the API?

    For a more concrete example, I have a sales receipt with 3 different items on it. All 3 items were programmed as "taxable". However when the cashier rung up the sale, Item #3 was changed to non-taxable on-the-fly. It's still a taxable item generally but the taxes were removed for this sale. When passed in to the API taxes are charged against all 3 items and produces a receipt showing I owe the man a refund for $15.17. :( :( :(

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    I will have a look at this during the week to see what I can come up with

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

     looked into this in a bit more details and there isn't an easy way to do this as the sale API emulates a regular sales transaction and gets the taxes based on current settings. The best bet will be to note these transactions and modify phppos_sales_items, phppos_sales, and phppos_sales_items_taxes for these sales

    0
    Comment actions Permalink
  • Avatar
    Tynan Fox

    Okay so I've been able to set up my script to check for tax miscalculation during validation and modify records to those tables if they don't match. It's going well.

    I've been fine tuning my import script to kill as many bugs as possible and bring in all the receipts correctly. I'm trying to be realistic though that they won't all be perfect so I'm setting it to dump erroneous receipts to a file so I can go back through and fix the problematic ones by hand. There are certain errors in calculations still occuring on a few receipts but it's not worth trying to do an automated fix because they're all different. I've gotten the error rate down to about 0.5% of the receipts though so I will have to manually repair about 500 of them out of 127K. 

    Thanks so much for the support!

    0
    Comment actions Permalink

Please sign in to leave a comment.