public function congvandixuatexcel(IncongvandiFormRequest $request)
{
$phongid = User::where('id', Auth::user()->id)->value('phong_id');
$ngaynhanbatdau = $request->input('ngaynhanbatdau');
$ngaynhanketthuc = $request->input('ngaynhanketthuc');
//\DB::connection()->setFetchMode(\PDO::FETCH_ASSOC);
if ($ngaynhanbatdau != '' && $ngaynhanketthuc != '') {
$ngaynhanbatdau = date('Y-m-d', strtotime(str_replace('/', '-', $ngaynhanbatdau)));
//date('Y-m-d', strtotime(str_replace('/', '-', Input::get('ngayphathanh-start'))) ) ;
$ngaynhanketthuc = date('Y-m-d', strtotime(str_replace('/', '-', $ngaynhanketthuc)));
if ($ngaynhanbatdau > $ngaynhanketthuc) {
return \Redirect::route('baocaocongvandi.index')->withErrors('Ngày nhận bắt đầu phải nhỏ hơn ngày nhận kết thúc!')->withInput();
} else {
$congvandis = Congvandi::join('canbos', 'congvandis.nguoiky_id', '=', 'canbos.id')->join('danhmucchucvus', 'canbos.danhmucchucvu_id', '=', 'danhmucchucvus.id')->join('danhmucdomats', 'congvandis.danhmucdomat_id', '=', 'danhmucdomats.id')->where('ngayphathanh', '>=', $ngaynhanbatdau)->where('ngayphathanh', '<=', $ngaynhanketthuc)->where('congvandis.phong_id', $phongid)->select('congvandis.id', 'congvandis.socongvandiso', 'congvandis.socongvandichu', 'congvandis.ngayphathanh', 'congvandis.trichyeucongvandi', 'congvandis.noinhancongvan', 'congvandis.noiluu', 'danhmucdomats.name as domat', 'canbos.name as hoten', 'danhmucchucvus.name as chucvu')->orderBy('congvandis.socongvandiso')->get();
}
}
$tungays = date('d/m/Y', strtotime($ngaynhanbatdau));
//date('Y-m-d', strtotime(str_replace('/', '-', Input::get('ngayphathanh-start'))) ) ;
$denngays = date('d/m/Y', strtotime($ngaynhanketthuc));
$date = date('d/m/Y');
$filename = 'Bao_cao_cong_van_di_tu_ngay_' . str_replace('/', '_', $tungays) . '_den_ngay_' . str_replace('/', '_', $denngays);
\Excel::create($filename, function ($excel) use($congvandis, $tungays, $denngays) {
$excel->sheet('New sheet', function ($sheet) use($congvandis, $tungays, $denngays) {
$endcell = count($congvandis) + 5;
$sheet->loadView('incongvan.resultexcelcongvandi')->with(['congvandis' => $congvandis, 'tungay' => $tungays, 'denngay' => $denngays]);
$sheet->mergeCells('A1:G1');
$sheet->mergeCells('A3:G3');
$sheet->cell('A1:G1', function ($cells) {
$cells->setAlignment('center');
});
$sheet->cell('A3:G3', function ($cells) {
$cells->setAlignment('center');
});
$sheet->cell('A5:G5', function ($cells) {
$cells->setAlignment('center');
});
$sheet->setAutoSize(true);
//dd($endcell);
$sheet->setBorder('A5:G' . $endcell . '');
$sheet->setBorder('A1:G4', 'none');
});
})->export('xls');
}
/**
* Show a list of all the languages posts formatted for Datatables.
*
* @return Datatables JSON
*/
public function data()
{
if (Auth::check() && $this->rolechucnangs && $this->rolechucnangs->Danhsach == 1) {
$user = Auth::user();
if ($user->role->name == 'phong') {
$congvandis = Congvandi::where('nguoitao_id', Auth::user()->id)->orWhereIn('congvandis.id', function ($query) use($user) {
$query->select('congvandi_id')->from('congvandi_danhmucphongs')->whereRaw('congvandi_danhmucphongs.danhmucphong_id = ' . $user->phong_id);
})->whereNull('congvandis.deleted_at')->orWhereIn('congvandis.id', function ($query) use($user) {
$query->select('congvandi_id')->from('congvandi_users')->whereRaw('congvandi_users.user_id = ' . $user->id);
})->whereNull('congvandis.deleted_at')->join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->where('congvandis.phong_id', $user->phong_id)->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
} else {
if ($user->role->name == 'doi') {
$congvandis = Congvandi::where('nguoitao_id', Auth::user()->id)->orWhereIn('congvandis.id', function ($query) use($user) {
$query->select('congvandi_id')->from('congvandi_danhmucdois')->whereRaw('congvandi_danhmucdois.danhmucdoi_id = ' . $user->doi_id);
})->whereNull('congvandis.deleted_at')->orWhereIn('congvandis.id', function ($query) use($user) {
$query->select('congvandi_id')->from('congvandi_users')->whereRaw('congvandi_users.user_id = ' . $user->id);
})->whereNull('congvandis.deleted_at')->join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->where('congvandis.phong_id', $user->phong_id)->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
} else {
if ($user->role->name == 'canbo') {
$congvandis = Congvandi::where('nguoitao_id', Auth::user()->id)->orWhereIn('congvandis.id', function ($query) use($user) {
$query->select('congvandi_id')->from('congvandi_users')->whereRaw('congvandi_users.user_id = ' . $user->id);
})->whereNull('congvandis.deleted_at')->join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->where('congvandis.phong_id', $user->phong_id)->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
} else {
if ($user->role->name == 'root') {
$congvandis = Congvandi::join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
} else {
if ($user->role->name == 'admin') {
$congvandis = Congvandi::join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
} else {
if ($user->role->name == 'vanthu') {
$congvandis = Congvandi::join('danhmucphongs', 'congvandis.noisoanthao_id', '=', 'danhmucphongs.id')->select(\DB::raw('congvandis.id, CONCAT(congvandis.socongvandiso , "/", congvandis.socongvandichu) as socongvandi,
date_format(congvandis.ngayphathanh, "%e/%m/%Y") as ngaygui, danhmucphongs.name as noisoanthao, congvandis.noinhancongvan,
congvandis.trichyeucongvandi'))->where('congvandis.phong_id', $user->phong_id)->orderBy('congvandis.created_at', 'desc')->orderBy('congvandis.socongvandiso', 'desc')->get();
}
}
}
}
}
}
return Datatables::of($congvandis)->add_column('checkbox', '<label class="pos-rel">
<input type="checkbox" name="checkboxvalue{{ $id }}" class="ace check-box-value" value="{{ $id }}" />
<span class="lbl"></span>
</label>')->addColumn('action', '<div class="hidden-sm hidden-xs action-buttons">
@if(Auth::check() && App\\Role::checkXem("congvandi") )
<a class="tooltip-info blue" data-rel="tooltip" title="Xem" href="{{ URL::route("congvandi.show", $id) }}">
<i class="ace-icon fa fa-search-plus bigger-130"></i>
</a>
@endif
@if(Auth::check() && App\\Role::checkSua("congvandi") )
<a class="tooltip-success green" data-rel="tooltip" title="Sửa" href="{{ URL::route("congvandi.edit", $id) }}">
<i class="ace-icon fa fa-pencil bigger-130"></i>
</a>
@endif
@if(Auth::check() && App\\Role::checkXoa("congvandi") )
<a>
{!! Form::open(array("route" => array("congvandi.destroy", $id), "method" => "delete")) !!}
<button class="tooltip-error red btn-delete" data-rel="tooltip" title="Xóa" type="submit" href="{{ URL::route("congvandi.destroy", $id) }}"><i class="ace-icon fa fa-trash-o bigger-120"></i></button>
{!! Form::close() !!}
</a>
@endif
</div>')->remove_column('id')->make(true);
} else {
return \Redirect::to('/')->with('message-error', 'Không có quyền truy cập');
}
}
/**
* check cong van di file is download by user
* @param $filedown
* @param $user
* @return mixed
*/
public static function downloadcongvandibyusername($filedown, $user)
{
$filedown_id = File::getIdByFileName($filedown);
$is_download = Congvandi::join('file_congvandis', 'congvandis.id', '=', 'file_congvandis.congvandi_id')->leftjoin('congvandi_danhmucphongs', 'congvandis.id', '=', 'congvandi_danhmucphongs.congvandi_id')->leftjoin('congvandi_danhmucdois', 'congvandis.id', '=', 'congvandi_danhmucdois.congvandi_id')->leftjoin('congvandi_users', 'congvandis.id', '=', 'congvandi_users.congvandi_id')->where('congvandis.nguoitao_id', $user->id)->orWhere('congvandi_danhmucphongs.danhmucphong_id', $user->phong_id)->orWhere('congvandi_danhmucdois.danhmucdoi_id', $user->doi_id)->orWhere('congvandi_users.user_id', $user->id)->where('file_congvandis.file_id', $filedown_id)->first();
return $is_download;
}