Support the ongoing development of Laravel.io →
Database Eloquent Views
Last updated 1 year ago.
0
  1. Am I going about this in the most wrongest way possible? Is there an easier, or even much better way to display year over year revenues in a view?

I think it could be reworked to merge the three getSumArray for totalPrice, total_profit, emp_total_cost in to one sql call since they are all using the same years.

  1. MORE IMPORTANTLY: Any ideas how I would go about displaying this data where the Months (Jan-Dec) are displayed top to bottom as rows, and the past three years are displayed as columns?

If I understand correctly what your wanting. I don't see you retrieving the data by month I see per year. You would need to rework the code to pull each month's total, store that in a year->month->total array then I think you could make the display your talking about.

It all depends on the data and how the dates are stored.

If you want to share some example data I might be able to help more.

0

thanks TerrePorter.

My services table does not have a year, month or week column - just serviceDate, so I extract the year and month from that. I don't know a SQL call that can do all three sums at once. My other concern was in the way in which I was creating the arrays. Should I be building one array to pass to the view?

About the other piece, I haven't yet begun work on it - so you won't see any efforts to retrieve the months. I'm interested in learning best practices, and I'll settle for better practices. :)

I'm thinking of building a 3 multi-dimensional arrays for year and month to store revenue, profit and costs. I just can't help but think that there's a single magical object I can create that would contain it all.

The other thing I'm considering is creating a table that would store all this information immediately after a service is created. When a service is updated, so is the reporting table. This scares me in that perhaps there's a chance a service would update but not the reporting table.

Thanks

0

schema looks like this,

Schema::create('services', function($table){
			$table->bigIncrements('id');
			$table->date('serviceDate');
			$table->string('serviceName');
			$table->integer('serviceTypeID');
			$table->bigInteger('clientID');
			$table->integer('petQty');
			$table->float('ratePrice');
			$table->float('rateAddPrice')->nullable();
			$table->float('totalPrice');
			$table->string('serviceStatus');
			$table->integer('empID')->nullable();
			$table->boolean('billable');
			$table->bigInteger('seriesID')->nullable();
			$table->bigInteger('invoiceID')->nullable();
			$table->softDeletes();
			$table->timestamps();
		});
0

I just hope this helps some .

My services table does not have a year, month or week column - just serviceDate, so I extract the year and month from that. I don't know a SQL call that can do all three sums at once.

Something like this might work,

$var = $this->reportingServices->getSumArray($theYears, ['totalPrice', 'total_profit', 'emp_total_cost']);

// getSumArray query code

$sum = Service::where('serviceDate', '>', $firstDay)
	->where('serviceDate', '<', $lastDay)
	->where('billable', true)
	->where('serviceStatus', '<>', 'SCHEDULED')
	->where('serviceStatus', '<>', 'CANCELLED');

// add in each sum
foreach($columns as $column ) {
	$sum ->sum( $column ) ;
}

My other concern was in the way in which I was creating the arrays. Should I be building one array to pass to the view?

I would be more concerned about the number of queries you will end up with over time. I have 9 years of day that I am generating reports from, it would always timeout.

My data is for a index of articles, who wrote what where and when - summary table is month,year,section,byline,count

I had to make summary_table and then pull a report from that table.

About the other piece, I haven't yet begun work on it - so you won't see any efforts to retrieve the months. I'm interested in learning best practices, and I'll settle for better practices. :)

The db queries start to add up quickly. If your using MySQL you could update the query to with a raw tag and convert the serviceDate column to a Month or a Day then filter according to get a sum on each.

...where( DB::raw('MONTH(serviceDate)'), '=', # ) // # is 1 to 12 for the months ...where( DB::raw('DAY(serviceDate)'), '=', # ) // # is 1 to 30 for the months

Depending on the way you go, that could be 30+ queries.

I'm thinking of building a 3 multi-dimensional arrays for year and month to store revenue, profit and costs. I just can't help but think that there's a single magical object I can create that would contain it all.

Hum... I don't think it is impossible but the over all volume of queries to get all the data could be problematic.

The other thing I'm considering is creating a table that would store all this information immediately after a service is created. When a service is updated, so is the reporting table. This scares me in that perhaps there's a chance a service would update but not the reporting table.

As I mentioned, I had to make a summary_table to hold the totals for each day as my queries became way to many to run for all the years each time. I ended up making the search indexer update the summary_table when it indexes the files when a file is uploaded and a cronjob that checks for changes for all records nightly to ensure the data is consistent.

My summary_table has 115,780 records in it ATM, I got curious and had to look.

0

Thanks again.

So if my function goes through all three columns (or as many as I pass), what would the value of $sum be that I pass back? Would I be passing back an array of sums?

As for the db queries, would it be advisable to simple pull all service records, and do the filtering at the collection level?

Sounds like the summary table may be the way to go.

Thanks!

0

So if my function goes through all three columns (or as many as I pass), what would the value of $sum be that I pass back? Would I be passing back an array of sums?

I think you will get an array back. You will likely need to rework a few of the functions to get everything to work again.

As for the db queries, would it be advisable to simple pull all service records, and do the filtering at the collection level?

Depends on how many records your talking about, and how many you estimate it will grow to.

The more records the longer the processing time and more likely for a timeout.

Sounds like the summary table may be the way to go.

It was the best way I was able to handle the quantity of record I had to parse.

Thanks!

Your welcome :)

0

Sign in to participate in this thread!

Eventy

Your banner here too?

unohuim unohuim Joined 1 Sep 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.