public function doSearch(SalesSearchRequest $request) { $productSales = []; $from = date('Y-m-d', strtotime($request->date_from)); $to = date('Y-m-d', strtotime($request->date_to)); $product = $request->product; $doctor = $request->doctor; $allSearchedReport = Report::select('id')->where('date', '>=', $from)->where('date', '<=', $to); if (!empty($MR)) { $allSearchedReport = $allSearchedReport->where('mr_id', \Auth::user()->id); } if (!empty($doctor)) { $allSearchedReport = $allSearchedReport->where('doctor_id', $doctor); } $searchResult = ReportSoldProduct::whereIn('report_id', $allSearchedReport->get()); if (!empty($product)) { $searchResult->where('product_id', $product); } foreach ($searchResult->get() as $singleResult) { if (isset($productSales[$singleResult->product->name])) { $productSales[$singleResult->product->name] += $singleResult->quantity; } else { $productSales[$singleResult->product->name] = $singleResult->quantity; } } $dataView = ['searchResult' => $productSales]; \Session::flash('date_from', $from); \Session::flash('date_to', $to); \Session::flash('productSales', $productSales); return view('mr.search.sales.result', $dataView); }
public function doSearch(SalesSearchRequest $request) { $searchResult = []; $from = $request->date_from; $to = $request->date_to; $products = $request->products; $MRs = $request->mrs; $allSearchedReport = Report::select('sold_products')->where('date', '>=', $from)->where('date', '<=', $to)->whereIn('mr_id', $MRs)->where('sold_products', '<>', 'NULL')->get(); foreach ($allSearchedReport as $singleReport) { foreach (json_decode($singleReport) as $soldProducts) { foreach (json_decode($soldProducts) as $singleProduct => $quantity) { $productName = Product::findOrFail($singleProduct)->name; if (in_array($singleProduct, $products)) { if (isset($searchResult[$productName])) { $searchResult[$productName] += $quantity; } else { $searchResult[$productName] = $quantity; } } } } } $dataView = ['searchResult' => $searchResult]; return view('am.search.sales.result', $dataView); }
public static function test() { $mrId = '3'; $currentMonth = 'Dec-2015'; $totalSoldProductsSales = []; $totalSoldProductsSalesPrice = 0; $reportsId = Report::select('id')->where('month', $currentMonth)->where('mr_id', $mrId)->get()->toArray(); $allSoldProducts = ReportSoldProduct::whereIn('report_id', $reportsId)->get(); foreach ($allSoldProducts as $singleReportProducts) { $productName = $singleReportProducts->product->name; $productPrice = $singleReportProducts->product->unit_price; $quantity = $singleReportProducts->quantity; $totalSoldProductsSalesPrice += $quantity * $productPrice; if (isset($totalSoldProductsSales[$productName])) { $totalSoldProductsSales[$productName] += $quantity; } else { $totalSoldProductsSales[$productName] = $quantity; } } return ['totalSoldProductsSales' => $totalSoldProductsSales, 'totalSoldProductsSalesPrice' => $totalSoldProductsSalesPrice]; print_r($totalSoldProductsSales); print_r($totalSoldProductsSalesPrice); }
public function doMRSearch(ReportSearchRequest $request) { $searchResult = null; $MR = $request->mr; $from = $request->date_from; $to = $request->date_to; $doctor = $request->doctor; $promoted_product = $request->promoted_product; $sample_product = $request->sample_product; $sold_product = $request->sold_product; $gift = $request->gift; $follow_up = $request->follow_up; $feedback = $request->feedback; $allReportsInRange = Report::select('report.id', 'month', 'date', 'doctor_id', 'mr_id', 'total_sold_products_price')->where('date', '>=', $from)->where('date', '<=', $to); if (!empty($MRs)) { $allReportsInRange->where('mr_id', $MR); } if (!empty($doctor)) { $allReportsInRange->where('doctor_id', $doctor); } if (!empty($promoted_product)) { $allReportsInRange->join('report_promoted_product', 'report_promoted_product.report_id', '=', 'report.id')->where('report_promoted_product.product_id', $promoted_product); } if (!empty($sample_product)) { $allReportsInRange->join('report_sample_product', 'report_sample_product.report_id', '=', 'report.id')->where('report_sample_product.product_id', $sample_product); } if (!empty($sold_product)) { $allReportsInRange->join('report_sold_product', 'report_sold_product.report_id', '=', 'report.id')->where('report_sold_product.product_id', $sold_product); } if (!empty($gift)) { $allReportsInRange->join('report_gift', 'report_gift.report_id', '=', 'report.id')->where('report_gift.gift_id', $gift); } if ($follow_up == 1) { $allReportsInRange->where('report.follow_up', '<>', ''); } if ($feedback == 1) { $allReportsInRange->where('report.feedback', '<>', ''); } $searchResult = $allReportsInRange->get(); $dataView = ['searchResult' => $searchResult]; return view('sm.search.reports.mr_result', $dataView); }
public function specialtyCoverageStats($mrId) { $totalVisits = []; $actualVisits = []; $specialtyCoverage = []; $specialty = NULL; $counter = 0; $allSpecialties = Customer::select('specialty')->where('mr_id', $mrId)->get()->toArray(); $allCustomersSpecialties = Customer::whereIn('specialty', $allSpecialties)->get()->toArray(); // Get all medical rep customers specialties foreach ($allCustomersSpecialties as $singleCustomer) { $allSpecialtyClasses[$singleCustomer['specialty']] = Customer::select('class')->where('specialty', $singleCustomer['specialty'])->get()->toArray(); } // Get all customer classes based on specialty foreach ($allSpecialtyClasses as $specialty => $specialtyClasses) { // Calculate total visits based on classes and specialty foreach ($specialtyClasses as $singleSpecialtyClass) { if (isset($totalVisits[$specialty])) { $totalVisits[$specialty] += VisitClass::where('name', $singleSpecialtyClass)->first()->visits_count; } else { $totalVisits[$specialty] = VisitClass::where('name', $singleSpecialtyClass)->first()->visits_count; } } } // Get all doctors visited $doctorsVisited = Report::select('doctor_id')->where('month', date('M-Y'))->where('mr_id', $mrId)->get()->toArray(); foreach ($doctorsVisited as $singleDoctor) { // calculate actual visits $specialty = Customer::select('specialty')->findOrFail($singleDoctor)->first()->specialty; if (isset($actualVisits[$specialty])) { $actualVisits[$specialty] += 1; } else { $actualVisits[$specialty] = 1; } } foreach ($allCustomersSpecialties as $singleCustomerSpecialty) { $specialty = $singleCustomerSpecialty['specialty']; $specialtyCoverage[$specialty] = 0; if (isset($specialtyCoverage[$specialty]) && isset($actualVisits[$specialty]) && isset($totalVisits[$specialty])) { $specialtyCoverage[$specialty] = number_format($actualVisits[$specialty] / $totalVisits[$specialty] * 100, 2); } } foreach ($specialtyCoverage as $specialty => $percentage) { if (isset($specialtyCoverage[$specialty])) { $stats[$counter]['label'] = $specialty; $stats[$counter]['data'] = $percentage; } $counter++; } return $stats; }
public static function monthlyProductsBought($id) { return Report::select('sold_products', 'date')->where('doctor_id', $id)->where('month', date('M-Y'))->get(); }