Get 30 Individual Days Sales report from mysql - Wordpress

Now days so many eCommerce sites are coming with sophisticated features. Eventhough here i will give an easy way to get sales report or report for individual days. 

Here we are going to use a small php piece of php code with loop to get required individual day report. Its not only for the wordpress code. Also you can use for your own php site. 

Initially i thought to bring it as report data. but if we show it as a chart is ease of understand. so i make it with Simple chart namely  "Chart.js" You can also use it your own js or php chart for representation of data.

First of all we need a mysql table with your desired name. here i created a sample table for easy understanding.  

Note : Here i wrote Wordpress functions , if you want to use it for your php site, just add few lines of code by replacing built in wordpress functions. 

 


$sql_sales = "CREATE TABLE IF NOT EXISTS $kv_sales (
		id mediumint(10) NOT NULL AUTO_INCREMENT,
		date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		pro_id mediumint(9),		
		txn_id varchar(30),
		txn_type varchar(20),
		gateway varchar(30),
		buyer_email varchar(40),
		status varchar(20),
		payer_status varchar(20),		
		pro_price float,
		parent_cur varchar(5),
		convert_cur varchar(5),
		convert_fee float,
		gross_amt float,
		fee_amt float,
		tax float,
		net_amt float,
		profit_amt float,		
      	UNIQUE KEY id (id)
    		)ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;";

dbDelta($sql_sales); // you have to use mysql_query for php site users.

Than, I have insert some data's for using it for our sample report. We need to prepare monthly and weekly, even yearly report with this function. All you have to do is change the $period variable to get your desired period values.

  Next, You have to write SQL query for getting data between certain periods. Here I wrote code 

$sale_table_name = $wpdb->prefix.'sales_report' ;


$period= 30 ;  // Your required period for retrieve data.

$cur_stat= 'USD' ; 



for ( $i = $period; $i>=0; $i-- ) {

	$gross_amt_day = 0;

	$net_amt_day  = 0;

	$profit_amt =0 ; 

	$r_date = 0 ;

	$selected_results = $wpdb->get_results("SELECT gross_amt, net_amt, profit_amt , DATE_FORMAT(`date`,'%e %b')AS date FROM $sale_table_name WHERE DATE(date) = DATE_SUB( CURDATE( ) ,INTERVAL '$i' DAY ) AND parent_cur = '$cur_stat'");

		//echo count($selected_results) . '-';

	foreach($selected_results as $result) {

			$r_date = $result->date ; 	

			$gross_amt_day += $result->gross_amt;

			$net_amt_day += $result->net_amt ; 

			$profit_amt += $result->profit_amt ; 

		}			

		$res_date[] = $r_date ; 

		$gross_amt_ar[] = $gross_amt_day ; 

		$net_amt_ar[] = $net_amt_day ;

		$profit_amt_ar[] = $profit_amt;

         }


} 

 

The following code helps to retrieve the data between two periods. Through the sql code by giving  your necessary time period you will get your desired result.

SELECT gross_amt, net_amt, profit_amt , DATE_FORMAT(`date`,'%e %b')AS date FROM $sale_table_name WHERE DATE(date) = DATE_SUB( CURDATE( ) ,INTERVAL '$i' DAY ) AND parent_cur = '$cur_stat'   

Though i wrote all the code into a file you want it download from Github