Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Location
    Pittsburgh, PA
    Posts
    237
    Plugin Contributions
    1

    Default Vendor Invoice History Table Add-on Dev (COGS by invoice)

    so can anyone help me out w/ an sql statement?

    I've added products_cost and products_invoice to the products table. I want to figure out the cost of the sold products grouped by invoice name. (products_invoice would be something like "023014Distributor003" and have 30 or so products on it; not very atomized I know)

    This statement seems to run forever in phpMyAdmin

    Code:
    SELECT p.products_id, p.products_invoice, sum(p.products_cost)
    FROM products p, orders_products op
    WHERE op.products_quantity>0
    GROUP BY p.products_invoice
    seriously stuck on this one... thanks.

  2. #2
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    12,399
    Plugin Contributions
    87

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    Quote Originally Posted by wolfderby View Post
    so can anyone help me out w/ an sql statement?

    I've added products_cost and products_invoice to the products table. I want to figure out the cost of the sold products grouped by invoice name. (products_invoice would be something like "023014Distributor003" and have 30 or so products on it; not very atomized I know)

    This statement seems to run forever in phpMyAdmin

    Code:
    SELECT p.products_id, p.products_invoice, sum(p.products_cost)
    FROM products p, orders_products op
    WHERE op.products_quantity>0
    GROUP BY p.products_invoice
    seriously stuck on this one... thanks.
    How about "connecting" the products and orders_products tables:
    Code:
    SELECT p.products_id, p.products_invoice, sum(p.products_cost)
    FROM products p, orders_products op
    WHERE op.products_id = p.products_id
    AND op.products_quantity>0
    GROUP BY p.products_invoice

  3. #3
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    2,668
    Plugin Contributions
    11

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    wolf,
    i rarely get into people's business operations. i'm making an exception here, just to make some observations.

    your change of the products table is very limiting and could destroy a potential audit trail. you are limiting yourself to 1 cost/product as well as 1 invoice/purchase per product.

    costing is much more complex than i will get into here. but if you purchase 100 units of product A, it becomes a hot seller, and you want to repurchase it, what happens then? and then lets say the cost has gone up by 12%? how would you keep track of that? your only solution would be to create a new product with the new invoice. but what happens if you then want to find out how many of product A you sold? you have 2 product numbers that you need to keep track of as they are the same product.

    perhaps this situation does not apply to you, but maintaining separate tables for vendor purchases is a much better way to handle these things. and then add the product cost to the order_products table. just my 2 cents.

    the problem with the sql statement the way it is currently written is two-fold: products_id has no significance, and there is no accounting for the quantity sold on each order. if i wanted to just find out the cost for each invoice of products sold from that invoice, i would use:

    Code:
    SELECT p.products_invoice, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
    FROM  orders_products op
    left join products p on op.products_id = p.products_id
    where op.products_quantity>0  
    GROUP BY p.products_invoice
    i have included a couple of other numbers to show the potential problems if run as you had constructed. the key number is the sum(p.products_cost *op.products_quantity).

    if i wanted to see the cost of each item that has a sale for every invoice, i would modify the sql as such:

    Code:
    SELECT p.products_invoice, p.products_id, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
    FROM  orders_products op
    left join products p on op.products_id = p.products_id
    where op.products_quantity>0  
    GROUP BY p.products_invoice, p.products_id
    best.
    author of square Webpay.
    mxWorks has premium plugins. donations: venmo or paypal accepted.
    premium consistent excellent support. available for hire.

  4. #4
    Join Date
    Dec 2008
    Location
    Pittsburgh, PA
    Posts
    237
    Plugin Contributions
    1

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    Quote Originally Posted by carlwhat View Post
    wolf,
    i rarely get into people's business operations. i'm making an exception here, just to make some observations.

    your change of the products table is very limiting and could destroy a potential audit trail. you are limiting yourself to 1 cost/product as well as 1 invoice/purchase per product.

    costing is much more complex than i will get into here. but if you purchase 100 units of product A, it becomes a hot seller, and you want to repurchase it, what happens then? and then lets say the cost has gone up by 12%? how would you keep track of that? your only solution would be to create a new product with the new invoice. but what happens if you then want to find out how many of product A you sold? you have 2 product numbers that you need to keep track of as they are the same product.

    perhaps this situation does not apply to you, but maintaining separate tables for vendor purchases is a much better way to handle these things. and then add the product cost to the order_products table. just my 2 cents.

    the problem with the sql statement the way it is currently written is two-fold: products_id has no significance, and there is no accounting for the quantity sold on each order. if i wanted to just find out the cost for each invoice of products sold from that invoice, i would use:

    Code:
    SELECT p.products_invoice, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
    FROM  orders_products op
    left join products p on op.products_id = p.products_id
    where op.products_quantity>0  
    GROUP BY p.products_invoice
    i have included a couple of other numbers to show the potential problems if run as you had constructed. the key number is the sum(p.products_cost *op.products_quantity).

    if i wanted to see the cost of each item that has a sale for every invoice, i would modify the sql as such:

    Code:
    SELECT p.products_invoice, p.products_id, sum(p.products_cost *op.products_quantity), sum(p.products_cost), count(op.products_quantity), sum(op.products_quantity)
    FROM  orders_products op
    left join products p on op.products_id = p.products_id
    where op.products_quantity>0  
    GROUP BY p.products_invoice, p.products_id
    best.
    Thanks, yeah I should have mentioned I created a "re-order id" aka reoid # that is a 3 digit integer for each time I repurchase a product, I try to increment it around wholesaler_sku and distributor_sku fields w/ this productmachine script that outputs easypopulate csv's from 4 or 5 column basic invoices. (quan, desc, cost, price, (sku-optional)

    The productmachine database has sizedata tables, category tables, etc. I think it'd be too customized to share atm, and it usually only gets me half way.

    So TESTPRODUCT-001 becomes TESTPRODUCT-002

    Generally I'm trying to make it more of a goal to contribute to this community (some/more) if I can learn how. :)
    Last edited by wolfderby; 14 Feb 2018 at 08:08 PM.

  5. #5
    Join Date
    Dec 2008
    Location
    Pittsburgh, PA
    Posts
    237
    Plugin Contributions
    1

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    so I'm trying to build an $invoice_totals as an associative array

    and then add the invoice_cogs into an associative array field, but after the second query, within the while loop if I try to add it back in like...

    Code:
    'newsoldsum' => $invoices_totals[$invoicename]['cogs_sum'];
    it's not matching the value back up w/ the array $invoices_totals[$invoicename] part.

    perhaps I should have tried making a class/object?

  6. #6
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    2,668
    Plugin Contributions
    11

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    what exactly are you trying to accomplish?
    author of square Webpay.
    mxWorks has premium plugins. donations: venmo or paypal accepted.
    premium consistent excellent support. available for hire.

  7. #7
    Join Date
    Dec 2008
    Location
    Pittsburgh, PA
    Posts
    237
    Plugin Contributions
    1

    Default Re: Vendor Invoice History Table Add-on Dev (COGS by invoice)

    So I ended up figuring it out somewhat using array_merge after 2 queries with while loops building 2 arrays then merging them under the invoice name.

    Usually before I import/easypopulate my orders into my website, I incorporate shipping costs based on a weighted-by-cost method.

    Basically I'm working on a table of invoices.

    Invoice Date | Invoice Name | Cost of Products w/ Quan > 0 (in products table) | Invoice COGS | Invoice Total

 

 

Similar Threads

  1. Missing order status history in invoice and packing slip
    By flix in forum Managing Customers and Orders
    Replies: 1
    Last Post: 12 Feb 2011, 12:19 PM
  2. Looking to add print invoice and shipp invoice from admin---> orders
    By r4fdud in forum Upgrading from 1.3.x to 1.3.9
    Replies: 0
    Last Post: 15 Mar 2009, 11:21 PM
  3. Order History and Invoice Ignores USPS Display Options?
    By fancypants in forum Templates, Stylesheets, Page Layout
    Replies: 2
    Last Post: 16 Nov 2007, 02:46 AM
  4. printable invoice in customer history view
    By harlyman in forum General Questions
    Replies: 4
    Last Post: 8 Dec 2006, 12:38 AM

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
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR