PHP - MySQL Weekly Sales Report Query Issue

Question

I am preparing weekly sales report for particular seller so he can see after login at any time.

Current week info is collected using below code.

<?php 
$dt_week_start_date = date('Y-m-d 20:00:01',strtotime("last Saturday"));
$dt_week_end_date = date('Y-m-d 20:00:00',strtotime("next Saturday"));
?>

Below query is used to get sold item data for specific seller from table for current week. This query works fine if I simply display sold item listing on page.

<?php
$str_query_select = "SELECT * FROM t_product_purchase ";
$str_query_select .= " WHERE purchasedatetime BETWEEN '".$dt_week_start_date ."' AND '".$dt_week_end_date."'";
$str_query_select .= " AND sellerpkid=1";
$str_query_select .= " ORDER BY purchasedatetime DESC ";
?>

But I need to show sold items order wise like below. I tried to do GROUP BY on purchasedatetime field and tried few other things but it's not working at all. Please help me to solve this isse.

Purchase Date-Time       Product      Price
2014-08-10 14.20.00      Item 010      $50
2014-08-10 14.20.00      Item 016      $20
                         ------------------
                         Total :       $70
                         + Shipping :  $10
                         - Promo Code :$ 5
                         ------------------
                         Sub Total :   $75


2014-08-13 09.08.10      Item 056      $20
2014-08-13 09.08.10      Item 056      $65
2014-08-13 09.08.10      Item 056      $ 5
                         ------------------
                         Total :       $90
                         + Shipping :  $15
                         - Promo Code :$ 5
                         ------------------
                         Sub Total :   $100

Total For week 2014-08-09 to 2014-08-16 : $175

Data are stored in database table in following way. All data are related to single seller only.

Buyer 01 buys 2 items together from site where shipping charge is $10 & promo code discount is $5 (so 2 records for 2 items are added in t_product_purchase table along with shipping charge and promo code discount)
Buyer 02 buys 3 items together from site where shipping charge is $15 & promo code discount is $5 (so 3 records for 3 items are added in t_product_purchase table along with shipping charge and promo code discount)

Here is database table structure:

CREATE TABLE IF NOT EXISTS `t_product_purchase` (
`purchasepkid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sellerpkid` bigint(20) unsigned NOT NULL DEFAULT '0',
`productpkid` bigint(20) unsigned NOT NULL DEFAULT '0',
`prodcatpkid` bigint(20) unsigned NOT NULL DEFAULT '0',
`extendedprice` double(10,2) NOT NULL DEFAULT '0.00',
`shippingvalue` double(10,2) NOT NULL DEFAULT '0.00',
`discountpercentage` float(6,2) NOT NULL DEFAULT '0.00',
`discountamount` float(6,2) NOT NULL DEFAULT '0.00',
`finaldiscountedamount` float(6,2) NOT NULL DEFAULT '0.00',
`quantity` smallint(6) NOT NULL DEFAULT '0',
`color` varchar(255) DEFAULT NULL,
`size` varchar(255) DEFAULT NULL,
`emailid` varchar(255) NOT NULL DEFAULT '',
`firstname` varchar(100) DEFAULT NULL,
`lastname` varchar(100) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`zipcode` varchar(10) DEFAULT NULL,
`shippingaddress` text,
`specialnote` text,
`ipaddress` varchar(50) DEFAULT NULL,
`purchasedatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`purchaseday` int(11) DEFAULT '0',
`purchasemonth` int(11) DEFAULT '0',
`purchaseyear` int(11) DEFAULT '0',
PRIMARY KEY (`purchasepkid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Ok, I just added database table structure in above description. — K Ahir, Aug 15 '14 at 06:46
@Kamal Ahir I will like to know if this was helpful or if you need further assistance — Paullo, Aug 15 '14 at 07:34

score 0 · Accepted Answer · answered Aug 15 '14 at 06:56

0

You can modify your query to get the Gross price while you do the other additions like the shipment and deductions like the Promo Code from within PHP.

Here is what I mean:

<?php
$str_query_select = "SELECT *, SUM(price_column) as gross_price FROM t_product_purchase ";
$str_query_select .= " WHERE purchasedatetime BETWEEN '".$dt_week_start_date ."' AND '".$dt_week_end_date."'";
$str_query_select .= " AND sellerpkid=1";
$str_query_select .= " GROUP BY purchasedatetime ORDER BY purchasedatetime DESC ";
?>

You can now do your additions and deductions: PLEASE NOTE: You must use the column that is unique for a particular transaction for your grouping if purchasedatetime is not.

Purchase Date-Time                     Price
2014-08-10 14.20.00      Gross Total : $70
                         + Shipping :  $10
                         - Promo Code :$ 5
                         ------------------
                         Sub Total :   $75


2014-08-13 09.08.10      Gross Total : $90
                         + Shipping :  $15
                         - Promo Code :$ 5
                         ------------------
                         Sub Total :   $100

Total For week 2014-08-09 to 2014-08-16 : $175

answered Aug 15 '14 at 06:56

Paullo

2,038
4
25
50

purchasedatetime field will be ubnique for each order. So if there are 3 items in order then all will have same value in purchasedatetime field. Also in your query, only single item is displaying for each order no matter how many items are in that order. Please give further guidance. – K Ahir Aug 15 '14 at 08:41
@Kamal you are seeing only one item because we asked MySql group all the items that has the same purchasedatetime. If you get the individual components that make up each group then you might need a different query to get that. Otherwise you can do GROUP_CONCAT() to return the required fields as a comma separated string. – Paullo Aug 15 '14 at 09:28
1

I am getting result like you described above in your answer. Really helpful and for that thank you so much. Should I use query inside query to show all items in single order and still it will display like you described above. Please let me know – K Ahir Aug 15 '14 at 09:36
Depending on the fields you will like to get you can use the same query above but include GROUP_CONCAT: Seee example below: – Paullo Aug 15 '14 at 09:45
1

Above code will give me comma separated result as you mentioned and as I can see here http://sqlfiddle.com/#!2/e51b7/11. I am really sorry that your above code is giving syntax error. I tried to compare it with other codes but no luck. Again thank you, – K Ahir Aug 15 '14 at 10:03
Look at this fiddle http://sqlfiddle.com/#!2/73381d/3. I have a working example their – Paullo Aug 15 '14 at 10:19
1

Yes, it helped me to understand it properly. Can We add any type of captions with each field in GROUP_CONCAT? – K Ahir Aug 15 '14 at 10:41
Sure! You can Concatenate any string as you wish to but notice the difference between Group_Concat and Concat. While Concat is use for concatenating two or more strings Group_Concat will target your table column. You can also look at MySQL documentation on it http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat or W3Rosource http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php. – Paullo Aug 15 '14 at 11:00
it's working very well. One small thing i want to know if I can remove or change default comma separator between each items. Currently I have changed your code like this: GROUP_CONCAT(CONCAT('title: ' , producttitle , ' | price: ' , price , ' | qty: ', quantity , '
' )) as item_details – K Ahir Aug 15 '14 at 11:42
Sure! But I don't it is be wise to include html tag
in the concat(). You can have your GROUP_CONCAT like this GROUP_CONCAT(CONCAT(producttitle , '|' , price , ' |', quantity ) SEPARATOR '::') as item_details. This will return data in this format: "Item 056|20 |20::Item 056|65 |65::Item 056|5 |5". While :: delimits each product | delimits the details for each product. Finally you can PHP explode function like this $array_of_prods=explode("::", $item_details);. For the details: $prod_1_detail=explode("|", $array_of_prods). Access the individual detail like echo $prod_1_details[0]; item 010 – Paullo Aug 15 '14 at 12:23
Also there is strange problem. I need to show weekly report from Every last Saturday 20.00.01 to next Saturday 20.00.00 ... $dt_week_start_date = date('Y-m-d 20:00:01',strtotime("last Saturday")); ... this line gives me last Saturday (09-AUG-2014) ... $dt_week_end_date = date('Y-m-d 20:00:00',strtotime("next Saturday")); ... this line gives me next Saturday (23-AUG-2014) ... right now it is 16-AUG-2014 04:47:20 Saturday so should not it give me 16-AUG-2014 ? Please guide me. – K Ahir Aug 16 '14 at 04:56
Sorry @Kamal for my late responses, I traveled hence I have been on the road all the while. Please can you give more details on what you intended to achieve, if possible with illustrations. I don't think that I understand you. – Paullo Aug 17 '14 at 06:17
Its okay. I am happy to hear that. Cheers – Paullo Aug 18 '14 at 07:55
Hello, can you please look at this and help me to resolve this issue? http://stackoverflow.com/questions/25346695/mysql-convert-tz-funciton-related-issue – K Ahir Aug 19 '14 at 04:14

PHP - MySQL Weekly Sales Report Query Issue

1 Answers1