public function postListRevenueCustomerYear(Request $request){ DB::enableQueryLog(); $list_order = array(); $year = $request->has('year')?$request->input('year'):0; $begin = date('Y-m-d H:i:s',strtotime('1-1-'.$year)); $end = date('Y-m-d H:i:s',strtotime('1-1-'.($year+1))); $key_order = 1; $arr_company = array(); $list_so = Saleorder::select('saleorders.*','companies.name','companies.id') ->where('date','>=',$begin) ->where('date','<',$end) ->where('status','=',1) ->leftJoin('companies','companies.id','=','saleorders.company_id') ->addSelect(DB::raw('sum(sum_amount) as sum_amount , sum(sum_invest) as sum_invest')) ->groupBy('company_id') ->get()->toArray(); foreach ($list_so as $key => $value) { $list_order[$key_order]['id'] = $value['id']; $list_order[$key_order]['name'] = $value['name']; $list_order[$key_order]['date'] = $value['date']; $list_order[$key_order]['sum_amount'] = $value['sum_amount']; $list_order[$key_order]['sum_invest'] = $value['sum_invest']; $list_order[$key_order]['khoang_giam'] = 0; $list_order[$key_order]['lai'] = $list_order[$key_order]['sum_amount'] - $list_order[$key_order]['sum_invest']; $list_order[$key_order]['loi_nhuan'] = $list_order[$key_order]['lai'] - $list_order[$key_order]['khoang_giam']; $list_order[$key_order]['updated_at'] = $value['updated_at']; $arr_product[$key_order] = $value['id']; $key_order++; } $list_rso = ReturnSaleorder::select('return_saleorders.*','companies.name','companies.id') ->where('date','>=',$begin) ->where('date','<',$end) ->where('status','=',1) ->leftJoin('companies','companies.id','=','return_saleorders.company_id') ->addSelect(DB::raw('sum(sum_amount) as sum_amount , sum(sum_invest) as sum_invest')) ->groupBy('company_id') ->get()->toArray(); foreach ($list_rso as $key => $value) { $check_in_array = array_search($value['id'], $arr_product); if($check_in_array){ $list_order[$check_in_array]['sum_amount'] -= $value['sum_amount']; $list_order[$check_in_array]['sum_invest'] -= $value['sum_invest']; $list_order[$check_in_array]['khoang_giam'] -= (abs($value['sum_amount']) - abs($value['sum_invest'])); $list_order[$check_in_array]['loi_nhuan'] = $list_order[$check_in_array]['lai'] + $list_order[$check_in_array]['khoang_giam']; }else{ $list_order[$key_order]['id'] = $value['id']; $list_order[$key_order]['name'] = $value['name']; $list_order[$key_order]['date'] = $value['date']; $list_order[$key_order]['sum_amount'] = -$value['sum_amount']; $list_order[$key_order]['sum_invest'] = -$value['sum_invest']; $list_order[$key_order]['khoang_giam'] = - (abs($list_order[$key_order]['sum_amount']) - abs($list_order[$key_order]['sum_invest'])); $list_order[$key_order]['lai'] = 0; $list_order[$key_order]['loi_nhuan'] = $list_order[$key_order]['lai'] + $list_order[$key_order]['khoang_giam']; $list_order[$key_order]['updated_at'] = $value['updated_at']; $key_order++; } } $date = array(); foreach ($list_order as $key => $value) { $date[$key] = $value['date']; } array_multisort($date,SORT_ASC,$list_order); $chi_khac = RevenueOther::where('date','>=',$begin) ->where('date','<',$end) ->sum('sum_amount'); $arr_cache = [ 'year'=>$year, 'list_order'=>$list_order, 'chi_khac'=>$chi_khac ]; \Cache::put('list_revenue_customer_year'.\Auth::user()->id, $arr_cache, 30); return view('revenue.list-revenue-customer-year',[ 'list_order' => $list_order, 'chi_khac' => $chi_khac ]); }
public function getListSo(Request $request) { $list_order = array(); $id_product = session('current_product') !== null ? session('current_product') : 0; $type = $request->has('type')? $request->input('type'): 'all'; $company_id = $request->has('company_id')? $request->input('company_id'): 'all'; $month_year = $request->has('month_year')? $request->input('month_year'): 'all'; if($type=='all' || $type=='so'){ $list_so = Saleorder::select('saleorders.date','saleorders.id','m_products.quantity','m_products.specification','oums.name as oum_name','companies.name as company_name','m_products.id as product_id') ->leftJoin('m_products',function($join){ $join->on('m_products.module_id','=','saleorders.id') ->where('module_type','=','App\Saleorder'); }) ->leftJoin('products','m_products.product_id','=','products.id') ->leftJoin('companies','m_products.company_id','=','companies.id') ->leftJoin('oums','m_products.oum_id','=','oums.id') ->where('products.id','=',$id_product) ->where('saleorders.status','=',1) ->orderBy('date','desc'); if($company_id != 'all') $list_so = $list_so->where('companies.id','=',$company_id); if($month_year != 'all'){ $month = explode('-',$month_year); $list_so = $list_so->whereRaw("MONTH(`saleorders`.`date`)=".$month[0]) ->whereRaw("Year(`saleorders`.`date`)=".$month[1]); } $list_so = $list_so->get()->toArray(); foreach ($list_so as $key => $value) { $arr_tmp = array(); $arr_tmp['company_name'] = $value['company_name']; $arr_tmp['oum_name'] = $value['oum_name']; $arr_tmp['specification'] = $value['specification']; $arr_tmp['quantity'] = $value['quantity']; $arr_tmp['id'] = $value['id']; $arr_tmp['date'] = $value['date']; $arr_tmp['type'] = 'so'; $list_order[] = $arr_tmp; } } if($type=='all' || $type=='rpo'){ $list_rpo = ReturnPurchaseorder::select('return_purchaseorders.date','return_purchaseorders.id','m_products.quantity','m_products.specification','oums.name as oum_name','companies.name as company_name','m_products.id as product_id') ->leftJoin('m_products',function($join){ $join->on('m_products.module_id','=','return_purchaseorders.id') ->where('module_type','=','App\ReturnPurchaseorder'); }) ->leftJoin('products','m_products.product_id','=','products.id') ->leftJoin('companies','m_products.company_id','=','companies.id') ->leftJoin('oums','m_products.oum_id','=','oums.id') ->where('products.id','=',$id_product) ->where('return_purchaseorders.status','=',1) ->orderBy('date','desc'); if($company_id != 'all') $list_rpo = $list_rpo->where('companies.id','=',$company_id); if($month_year != 'all'){ $month = explode('-',$month_year); $list_rpo = $list_rpo->whereRaw("MONTH(`return_purchaseorders`.`date`)=".$month[0]) ->whereRaw("Year(`return_purchaseorders`.`date`)=".$month[1]); } $list_rpo = $list_rpo->get()->toArray(); foreach ($list_rpo as $key => $value) { $arr_tmp = array(); $arr_tmp['company_name'] = $value['company_name']; $arr_tmp['oum_name'] = $value['oum_name']; $arr_tmp['specification'] = $value['specification']; $arr_tmp['quantity'] = $value['quantity']; $arr_tmp['id'] = $value['id']; $arr_tmp['date'] = $value['date']; $arr_tmp['type'] = 'rpo'; $list_order[] = $arr_tmp; } } $product = Product::find($id_product)->toArray(); return view('product.list-so',[ 'list_order' => $list_order , 'product' => $product ]); }
public function anyExportExcel(){ $id_template = 5; $arr_print = [ 'arr_list' => [ 'arr_key' => [ 'sku', 'name', 'oum_name', 'specification', 'quantity', 'sell_price', 'amount' ], 'arr_head' => [ ['text'=>'STT','class'=>''], ['text'=>'Mã','class'=>''], ['text'=>'Tên sản phẩm','class'=>''], ['text'=>'Đơn vị','class'=>'center'], ['text'=>'Quy cách','class'=>'center'], ['text'=>'Số lượng','class'=>'center'], ['text'=>'Đơn giá','class'=>'money'], ['text'=>'Thành tiền','class'=>'money'] ], 'arr_body'=>[], 'arr_sum'=>[] ], 'arr_data'=> [ 'id'=>session('current_saleorder') ] ]; if (\Cache::has('list_product_so'.\Auth::user()->id)){ $so = Saleorder::select('saleorders.*','companies.name') ->where('saleorders.id','=',session('current_saleorder')) ->leftJoin('companies','companies.id','=','saleorders.company_id')->get()->first(); $month = intval(date('m',strtotime($so->date))); $year = intval(date('Y',strtotime($so->date))); $begin = date('Y-m-d H:i:s',strtotime('1'.'-'.$month.'-'.$year)); $end = $so->date; $list_order = array(); $key_order = 1; $arr_print['arr_data']['date'] = date('d-m-Y',strtotime($so->date)); $arr_print['arr_data']['company_name'] = $so->name; $arr_print['arr_data']['phone'] = $so->company_phone; $arr_print['arr_data']['address'] = ''; $arr_dress = Address::select('addresses.*','provinces.name as province_name') ->where('addresses.id','=',$so->address_id) ->leftJoin('provinces','provinces.id','=','addresses.province_id') ->get()->first(); $arr_print['arr_data']['address'] .= $arr_dress->address?$arr_dress->address.', ':''; $arr_print['arr_data']['address'] .= $arr_dress->town_city?$arr_dress->town_city.', ':''; $arr_print['arr_data']['address'] .= $arr_dress->province_name?$arr_dress->province_name:''; $receipt_month_prev = ReceiptMonth::where('type_receipt','=','customer') ->where('company_id','=',$so->company_id) ->where(function($query) use ($month,$year){ $query->where(function($query2) use ($month,$year){ $query2->where('month','<',$month) ->where('year','=',$year); })->orWhere(function($query2) use ($month,$year){ $query2->where('year','<',$year); }); }) ->orderBy('year','desc') ->orderBy('month','desc') ->limit(1); $receipt_month_prev = $receipt_month_prev->first(); if($receipt_month_prev){ $arr_print['arr_data']['no_cu'] = $receipt_month_prev->con_lai; }else{ $arr_print['arr_data']['no_cu'] = 0; } $list_po = Saleorder::where('date','>=',$begin) ->where('date','<',$end) ->where('status','=',1) ->where('company_id','=',$so->company_id) ->get()->toArray(); foreach ($list_po as $key => $value) { $list_order[$key_order]['id'] = $value['id']; $list_order[$key_order]['date'] = $value['date']; $list_order[$key_order]['sum_amount'] = $value['sum_amount']; $key_order++; } $list_so = ReturnSaleorder::where('date','>=',$begin) ->where('date','<',$end) ->where('status','=',1) ->where('company_id','=',$so->company_id) ->get()->toArray(); foreach ($list_so as $key => $value) { $list_order[$key_order]['id'] = $value['id']; $list_order[$key_order]['date'] = $value['date']; $list_order[$key_order]['sum_amount'] = -$value['sum_amount']; $key_order++; } $list_paid = Paid::where('date','>=',$begin) ->where('date','<',$end) ->where('company_id','=',$so->company_id) ->where('type_paid','=','customer') ->get()->toArray(); foreach ($list_paid as $key => $value) { $list_order[$key_order]['id'] = $value['id']; $list_order[$key_order]['date'] = $value['date']; $list_order[$key_order]['sum_amount'] = -$value['sum_paid']; $key_order++; } $date = array(); foreach ($list_order as $key => $value) { $date[$key] = $value['date']; } array_multisort($date,SORT_ASC,$list_order); foreach ($list_order as $key => $value) { $arr_print['arr_data']['no_cu']+=$value['sum_amount']; } $arr_cache = \Cache::get('list_product_so'.\Auth::user()->id); $sum_amount = 0; $arr_check_idproduct = array(); $arr_check_mproduct = array(); foreach ($arr_cache as $key => $value) { if(!in_array($value['product_id'],$arr_check_idproduct)){ $arr_check_idproduct[] = $value['product_id']; $arr_check_mproduct[$value['product_id']][]=$value; $arr_cache_tmp[$value['product_id']]=$value; }else{ foreach ($arr_check_mproduct[$value['product_id']] as $key2 => $m_product) { if($value['oum_id']==$m_product['oum_id'] && $value['specification']==$m_product['specification'] && $value['sell_price']==$m_product['sell_price']){ $arr_check_mproduct[$value['product_id']][$key2]['quantity']+= $value['quantity']; $arr_check_mproduct[$value['product_id']][$key2]['amount']+= $value['amount']; }else{ $arr_check_mproduct[$value['product_id']][]=$value; } } } } $arr_cache = array(); foreach ($arr_check_mproduct as $key => $value) { foreach ($value as $key2 => $value2) { $arr_cache[] = $value2; } } $index=2; foreach ($arr_cache as $key => $value) { $sum_amount += $value['sell_price']*$value['quantity']*$value['specification']; $arr_cache[$key]['amount'] = '=E'.$index.'*F'.$index."*G".$index; $index++; } $arr_print['arr_data']['toa_moi'] = $sum_amount; if($so->status==0){ $arr_print['arr_data']['no_cu'] = 0; } $arr_print['arr_data']['tong_cong'] = $arr_print['arr_data']['no_cu'] + $arr_print['arr_data']['toa_moi']; $arr_print['arr_data']['no_cu'] = number_format($arr_print['arr_data']['no_cu']); $arr_print['arr_data']['toa_moi'] = number_format($arr_print['arr_data']['toa_moi']); $arr_print['arr_data']['tong_cong'] = number_format($arr_print['arr_data']['tong_cong']); $arr_print['arr_list']['arr_sum'][] = [ ['value'=>'Tổng cộng:','colspan'=>'6'], ['value'=>$sum_amount] ]; $arr_print['arr_list']['arr_body'] = $arr_cache; $link = ExportsController::getCreatePrintExcel($arr_print,$id_template,'don_hang_so_'.$so->id); Log::create_log(\Auth::user()->id,'App\Saleorder','In đơn hàng số '.session('current_saleorder')); return redirect($link); } die; }