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