small help with a php script

[Guest]

Hi

 

I have a php script that generates a pages displaying todays total sales grouped by payment method

 

//define constants
define('LINE', '------------------------------------------');
define('BR', "[F");
//include base functions
require('includes/application_top.php'); //
//define queries
$today = date("Y-m-d");
$sql_detail = sprintf("select o.payment_method as pm, sum(ot.value) as amount from orders o 
    inner join orders_total ot on o.orders_id=ot.orders_id 
    where ot.class = 'ot_total' 
    and o.date_purchased like '%s' 
    group by o.payment_method",  $today . '%');
$sql_total = sprintf("select sum(ot.value) as total from orders o 
    inner join orders_total ot on o.orders_id=ot.orders_id 
    where ot.class = 'ot_total' 
    and o.date_purchased like '%s' ",  $today . '%');
//print out results
echo '[b[dTotal for ' . $today . BR;
echo LINE . BR;
$qry_detail = tep_db_query($sql_detail);
while($row = tep_db_fetch_array($qry_detail)){
    echo $row['pm'] . ': ' . round($row['amount'],2) . BR;
}
echo LINE . BR;
$qry_total = tep_db_query($sql_total);
while ($row1 = tep_db_fetch_array($qry_total)) {
    echo 'Total: ' . round($row1['total'],2) . BR;
}
echo LINE . BR . BR;

now i want to tweak it so its  limited by order status id

 

its for a local pizza delivery and they given each driver a status id so they know who delivered what... and to make it simple they want the above page to display what a given driver handles so they know how much cash he should hand over

 

`orders_status_history` is the table and orders_status_id is the field i want to limit on

 

bad at php i ask how i would do this one?

 

[MrPhil]

When you say "limited" by orders_status_id, do you mean "restricted"? In MySQL, "LIMIT" is a specific operation that limits the number of rows returned for a query. It's confusing if that's not what you mean. To restrict the query to certain row(s), you would be adding AND osh.orders_status_id='value' (or something similar) to the WHERE clause. I haven't looked enough in the code to see where orders_status_id is set, but it is not a "normal" (auto-increment) ID, just an integer, so can I assume that many rows could share this value?

 

The big task here is how to JOIN orders_status_history (and possibly orders_status) into the queries. Do you have an idea yet where you're going to get the orders_status_id value from? Will it be manually typed in from a new form, or programmatically go through all orders_status_id's (using GROUP BY and/or ORDER BY clauses)?

 

By the way, you don't need to use sprintf() to create the queries. You can just drop $today right into the string, provided that " quotes are used for the outermost delimiters.

[Guest]

the code above was providede to me... so its now my work

 

yes i know where to get order status id from... in the table `orders_status_history` 

 

 

the current code gives the total daily sales and from what i can see it uses both  the table orders and orders_total.... 

 

only change i want is to restrict or limit the results it uses to a certain order status id

 

 

btw... its does not matter where order status id i set.... i work directly on the database which just happens to be an mysql database for an oscommerce shop

[♥John W]

I played with this a little bit and this will output the way you want like this.  I moved some things around becuase it output funny the way you had it.  There was some extra code in there that added junk to the print.  I'm guessing this is what you wanted.  The code is probably not the best of most efficent but it does what you want.

 

Total for 2015-11-11
Pending
------------------------------------------
Check/Money Order: 63.42
Credit Card (authorizenet_cc_aim; Test): 42.9
------------------------------------------
Processing
------------------------------------------
Check/Money Order: 30.71
------------------------------------------
Shipped
------------------------------------------
------------------------------------------
HOLDING
------------------------------------------
------------------------------------------
Delivered
------------------------------------------
------------------------------------------
Canceled
------------------------------------------
------------------------------------------
Total: 137.03

  define('LINE', '------------------------------------------');
  define('BR', "<br>");
  require('includes/application_top.php');

  $today = date("Y-m-d");
  echo 'Total for ' . $today . BR;
  $orders_status_query_raw = "SELECT orders_status_id, orders_status_name FROM orders_status os WHERE public_flag =1";
  $array = tep_db_query($orders_status_query_raw);
  foreach ($array as $value) {
    echo $value['orders_status_name'] . "<br>";
    $status = $value['orders_status_id'];

//define queries
    $sql_detail = sprintf("select o.payment_method as pm, sum(ot.value) as amount, o.orders_status as os from orders o
    inner join orders_total ot on o.orders_id=ot.orders_id
    inner join orders_status os on o.orders_status = os.orders_status_id
    where ot.class = 'ot_total'
    and o.date_purchased like '%s'
    and o.orders_status = $status
    group by o.payment_method", $today . '%');
    $sql_total = sprintf("select sum(ot.value) as total from orders o
    inner join orders_total ot on o.orders_id=ot.orders_id
    where ot.class = 'ot_total'
    and o.date_purchased like '%s' ", $today . '%');
//print out results

    echo LINE . BR;
    $qry_detail = tep_db_query($sql_detail);
    while ($row = tep_db_fetch_array($qry_detail)) {
      echo $row['pm'] . ': ' . round($row['amount'], 2) . BR;
    }
    echo LINE . BR;
  }

  $qry_total = tep_db_query($sql_total);
  while ($row1 = tep_db_fetch_array($qry_total)) {
    echo 'Total: ' . round($row1['total'], 2) . BR;
  }

[Guest]

will give it a go

 

The reason why i want to sort by order status is that the local pizza delivery have 2 drivers and the owner/staff...

 

each of the drivers have their own order status id so we know who delivered what and what amount of cash they are responsible for

[Guest]

might not make the test today but do i assume right that if i mod this one:

 

$status = $value['orders_status_id'];

 

to

 

$status = 5

 

it will limit everything to that id? so i only get results from that driver?

[♥John W]

There is a foreach loop that will still loop through for each active status.  But, changing the status to a number will only output for that drive, but it will still do it over and over.  So, there's not really a benefit to doing that.  Also, it will echo different names still.

[Guest]

yeah me was just thinking quick and thought i could make one file for each driver

 

so if i did my quick hack it would give me a list of all the order status's i have? but they would be limited to that driver?

 

 

i can see some of the waste as it would not be relavant.... ie to be relavant i would only care about one order status and the payment methods tied to that one...

[♥John W]

To stop it from looping comment out the four lines before $status, then it won't loop.

[Guest]

works, just want some real orders to come in to verify :-D

 

thanks for the help

 

 

 

Q: i have direct mailed an developer that is also a user in here (no need to mention names), i asked him a price on adding some functionality to oscommerce but have not had a reply.... 

 

what time should i allow before passing the task on? dont want to be an arse but also want to get it moving

 

i know the owner of the local pizza delivery might have been changing his mind a lot but i told him that if he want things done it will cost him, and that changing his mind back and forth only makes it more difficult to get help

[♥John W]

If you don't have a reply at all maybe ask again or ask someone else after a few days.  Of course, people go on vacation and such.    

[Guest]

ok, will wait a week in total and see what happens 

 

what are the most suited subforum to ask in? just so that i dont use the wrong one and make a fool of myself

[♥John W]

A week seems reasonable.  Not sure which subforum.  Just pick the best one.  I normally look through "new content" so I see anything new.  Not sure how most do it.  The forum isn't nearly as busy as it used to be.

[Guest]

hmm... i simply could not find a suiting subforum so here it goes:

 

these are items i want to get done.... i want to take one at a time to make the budget a bit more "manageable" 

 

my shop is based on: http://www.clubosc.com/responsive-oscommerce-now-a-reality-using-bootstrap.html

 

so not a straight oscommerce but until now very few contribs have not worked on it

 

 

1. For some of the pizza's there are several sizes to choose from - the owner want a small button on each where customers can pick direct so they dont need to click the pizza first before they can click the option
2. email via SMTP.. sendmail are sometime 1-2 days behind if they get there at all
3. point and rewards scheme - 1 pizza awards 1 point

      when 11 points are scored 1 pizza is free or XX in currence is given as discount if customer opts for it

      have to decide which one of the above - i assume giving a discount is more easy to implement

      XX in currency is for batches of 11 points.. so say 11 points are there we give 61 DKK in discount, when 22 points are there we give twice that

      add checkbox for each product to tell if it awards points (admin side)

 

• Bigger project: make a Point of Sale theme/thingy: the goal is for the store to be able to quickly pick a product
  • i picture the screen can be divided up like this
  • 2 columns 1 small to the left where the the products stack up
  • this column can be split in 3 horizontal... at top customer is selected.. at bottom payment method 
  • for selecting customer phone number should be the search term
  • payment is a bit delicate... for cash is straight forward i just need to figure how we can activate the drawer... 
  • creditcard... they have a terminal there... the cost of getting that integrated is just insane, i think its about 50000 dkk plus dev cost..
    • so we need to have a kind of module that are hidden from internet customers, but availble for the shop, so the order can be marked correct