Full service web hosting, great prices and support. Starts at $1.99/month!
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: how to print out all sales details ?

  1. #1
    Active Member
    how to print out all sales details ?


    Join Date
    Jan 2009
    Posts
    236
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    jmelson     $ 50.00
    Rep Power
    16


    Default how to print out all sales details ?

    So, I am replacing my very old store with a newly created osCmax. Complete replacement of the whole system. The old store is not compatible with newer OS, so I will not be able to have it available except by bringing the disk up on an old computer. Before shutting the old one down, is there a way to make a list of all the sales to a file? I can imagine a modest sql query could probably do the whole thing.

    What I'd like is name/address, date and items ordered, and the total. Is there a relatively easy way to do this? Then, just print the results to a file.

    Thanks in advance for any tips on how to do this.

    Jon

  2. #2
    osCMax Development Team

    how to print out all sales details ?

    ridexbuilder's Avatar
    Join Date
    Jul 2008
    Location
    Haggisland
    Posts
    4,137
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    ridexbuilder     $ 15.00
    Rep Power
    96


    Lightbulb Re: how to print out all sales details ?

    Quote Originally Posted by jmelson View Post
    .. I can imagine a modest sql query could probably do the whole thing.

    What I'd like is name/address, date and items ordered, and the total. Is there a relatively easy way to do this? Then, just print the results to a file.
    A simplistic view?

    Take a look at the customer sales report query for an idea of what would be involved.
    Would you include cancelled and/or refunded sales?
    What format would the file be output in and would it be manageable?

    Suggestions:
    • Retain the database and use a free mySQL report builder to query the data. You're gonna get intimate with the data structure. Waaay back, MS Access had pretty neat DB reporting.
    • Import the existing customer address, orders and associated tables to your new store. (Some tweaking of fields will be required and the customers' County/Zone/State may well get screwed up.)
    • Import the existing customer, country/zone and product records to a separate osCmax installation, for archival/historical purposes. (Same field tweaking required.) You keep it in maintenance mode but can still see information in Admin.

    Developers resource at bitbucket
    *** *** ***
    oscmax.co.uk / ejsolutions.co.uk
    Hosting plans with installation, configuration, contributions, support and maintenance.
    *** FREE osCmax hosting available ***
    oscmaxtemplates.com

  3. #3
    Active Member
    how to print out all sales details ?


    Join Date
    Jan 2009
    Posts
    236
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    jmelson     $ 50.00
    Rep Power
    16


    Default Re: how to print out all sales details ?

    Quote Originally Posted by ridexbuilder View Post
    A simplistic view?

    Take a look at the customer sales report query for an idea of what would be involved.
    Would you include cancelled and/or refunded sales?
    We really don't have more than 1-2 per year, so that hardly matters.
    What format would the file be output in and would it be manageable?
    something really basic, like CSV would probably work fine. We have some 674 sales in the old store. I just need to keep it in some accessible form for tax purposes.
    Suggestions:
    Retain the database and use a free mySQL report builder to query the data.
    Well, another package to learn, but this makes a lot of sense. Can you recommend a Linux package for this?

    You're gonna get intimate with the data structure. Waaay back, MS Access had pretty neat DB reporting.
    No MS here, gotta be Linux.
    Import the existing customer address, orders and associated tables to your new store. (Some tweaking of fields will be required and the customers' County/Zone/State may well get screwed up.)
    afraid of damaging the store, so I don't think I'd do that. All the internal product IDs are not going to match up, I suspect.

    • Import the existing customer, country/zone and product records to a separate osCmax installation, for archival/historical purposes. (Same field tweaking required.) You keep it in maintenance mode but can still see information in Admin.
    Well, the old store will still be able to be booted up, I will archive the hard drive forever.

    Thanks for the suggestions!

    Jon

  4. #4
    osCMax Development Team

    how to print out all sales details ?

    ridexbuilder's Avatar
    Join Date
    Jul 2008
    Location
    Haggisland
    Posts
    4,137
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    ridexbuilder     $ 15.00
    Rep Power
    96


    Default Re: how to print out all sales details ?

    Quote Originally Posted by jmelson
    I can imagine a modest sql query could probably do the whole thing.
    A bit of this...
    Code:
        $customers_query_raw = "select c.customers_id,
                                      c.customers_lastname,
                                      c.customers_firstname,
                                      c.customers_email_address,
                                      c.customers_newsletter,
                                      c.customers_gender,
                                      c.customers_dob,
                                      c.customers_telephone,
                                      c.customers_fax,
                                      a.entry_company,
                                      a.entry_street_address,
                                      a.entry_postcode,
                                      a.entry_city,
                                      zo.zone_name,
                                      a.entry_suburb,
                                      co.countries_name
                                       from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id
                                       left join " . TABLE_COUNTRIES . " co on co.countries_id = a.entry_country_id 
                                       left join " . TABLE_ZONES . " zo on zo.zone_id = a.entry_zone_id  
                                       order by c.customers_lastname, c.customers_firstname"
    Combined with something like this..
    Code:
    tep_db_query("select orders_status_history_id, orders_status_id, date_added " . TABLE_ORDERS_STATUS_HISTORY . " where orders_id = '" . tep_db_input($oID) . "' order by date_added")
    and the order details... not trivial.

    Quote Originally Posted by jmelson
    Can you recommend a Linux package for this?
    Haven't used a SQL Report generator in about 20 years (commercial products). I Googled for "free mySQL report builder" and noticed a couple. Regardless if the old osCmax never runs again, you can at least export CSV spreadsheets direct from the database tables, using phpmyadmin. Keep the database intact, the data is there but extracting it in a usable form is the challenge.

    Quote Originally Posted by jmelson
    No MS here, gotta be Linux.
    Gotta give ya credit where it's due.

    Developers resource at bitbucket
    *** *** ***
    oscmax.co.uk / ejsolutions.co.uk
    Hosting plans with installation, configuration, contributions, support and maintenance.
    *** FREE osCmax hosting available ***
    oscmaxtemplates.com

  5. #5
    Member
    how to print out all sales details ?


    Join Date
    May 2017
    Posts
    78
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    Markcm     $ 0.00
    Rep Power
    4


    Default Re: how to print out all sales details ?

    Another option is to manually move data from each table over to the new database, not all tables need to be copied over but all your address, customer, product, order related tables are nice to retain.

    A complete compare and import can be done in about an hour or two. I just opened both databases in separate windows, examined the table structure for each table that I wanted to keep and if it was the same, do an sql export to text, copy the text and paste it into the sql window of the new database.

    If the table structure is changed at all, you need to add a step such as pasting to excel first and adding a column, then pasting to the sql window of the new database. If you want to try this approach and have more questions, let me know and I'll try to help. I'm glad I did, I have 10+ years of orders and customers that are still all complete in my admin panel or they can log into from the catalog side.

  6. #6
    osCMax Development Team

    how to print out all sales details ?

    ridexbuilder's Avatar
    Join Date
    Jul 2008
    Location
    Haggisland
    Posts
    4,137
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    ridexbuilder     $ 15.00
    Rep Power
    96


    Default Re: how to print out all sales details ?

    Quote Originally Posted by Markcm View Post
    Another option is to manually move data from each table over to the new database, not all tables need to be copied over but all your address, customer, product, order related tables are nice to retain...
    Sounds familiar
    Import the existing customer address, orders and associated tables to your new store. (Some tweaking of fields will be required and the customers' County/Zone/State may well get screwed up.)

    Developers resource at bitbucket
    *** *** ***
    oscmax.co.uk / ejsolutions.co.uk
    Hosting plans with installation, configuration, contributions, support and maintenance.
    *** FREE osCmax hosting available ***
    oscmaxtemplates.com

  7. #7
    Member
    how to print out all sales details ?


    Join Date
    May 2017
    Posts
    78
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    Markcm     $ 0.00
    Rep Power
    4


    Default Re: how to print out all sales details ?

    Quote Originally Posted by ridexbuilder View Post
    Sounds familiar

    Well I guess I didn't read 100% of this thread before trying to help so change my suggestion to "What he said".

  8. #8
    Active Member
    how to print out all sales details ?


    Join Date
    Jan 2009
    Posts
    236
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    jmelson     $ 50.00
    Rep Power
    16


    Default Re: how to print out all sales details ?

    OK, so the new (2.5.4) store is up and running, so now I can tinker with this issue of getting the sales history back.
    So, I have made a complete copy of the 2.5.4 store but pointed it at the OLD database. Of course, it doesn't work.
    Last week, somewhere on the osCmax site, I found a place that had all the upgrade scripts for the databases. I BELIEVE that there was a continuous
    list of them that would upgrade my old 2.5 RC1 database to the 2.5.4 -- But NOW I CAN'T FIND it! Does anybody know where these sql upgrade scripts are?
    This seems the easiest way to get the old sales data back - just apply the upgrade scripts in order, and hopefully the 2.5.4 store will be able to at least show
    the sales.

    Thanks in advance for any help!

    Jon

  9. #9
    osCMax Development Team

    how to print out all sales details ?

    pgmarshall's Avatar
    Join Date
    Feb 2009
    Location
    London
    Posts
    3,517
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    pgmarshall     $ 35.00
    Rep Power
    77


    Default Re: how to print out all sales details ?

    pgmarshall
    _______________________________

  10. #10
    Member
    how to print out all sales details ?


    Join Date
    May 2017
    Posts
    78
    Contribute If you enjoy reading the
    content here, click the below
    image to support our site.
    Click Here To Contribute To Our Site

    Total Contributions For

    Markcm     $ 0.00
    Rep Power
    4


    Default Re: how to print out all sales details ?

    Quote Originally Posted by jmelson View Post
    OK, so the new (2.5.4) store is up and running, so now I can tinker with this issue of getting the sales history back.
    So, I have made a complete copy of the 2.5.4 store but pointed it at the OLD database. Of course, it doesn't work.
    Last week, somewhere on the osCmax site, I found a place that had all the upgrade scripts for the databases. I BELIEVE that there was a continuous
    list of them that would upgrade my old 2.5 RC1 database to the 2.5.4 -- But NOW I CAN'T FIND it! Does anybody know where these sql upgrade scripts are?
    This seems the easiest way to get the old sales data back - just apply the upgrade scripts in order, and hopefully the 2.5.4 store will be able to at least show
    the sales.

    Thanks in advance for any help!

    Jon
    My recommendation would be using a fresh database and then just manually review/copy/pasting the table(s) data that you want to retain. I didn't have much luck trying to run upgrade scripts even for just one or two revisions and you're quite a bit behind that.

Page 1 of 2 12 LastLast

Similar Threads

  1. Would like to Print - Print catalog
    By billmcelligott in forum Custom Mods and Hacks
    Replies: 0
    Last Post: 06-10-2011, 05:03 PM
  2. Add customer details v1
    By michael_s in forum New osCommerce Contributions
    Replies: 0
    Last Post: 08-04-2009, 12:00 AM
  3. Add customer details v1
    By michael_s in forum New osCommerce Contributions
    Replies: 0
    Last Post: 08-01-2009, 05:00 PM
  4. Add customer details v1
    By michael_s in forum New osCommerce Contributions
    Replies: 0
    Last Post: 04-26-2008, 02:52 PM
  5. Currency details
    By melfalcon15 in forum osCommerce 2.2 Modification Help
    Replies: 2
    Last Post: 03-11-2005, 07:25 AM

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •