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();
}
