<?php

namespace App\Console\Commands;

use App\Models\BackupLog;
use App\Models\CollectSpend;
use App\Models\Config;
use App\Models\Contract;
use App\Models\ElectricWater;
use App\Models\Hostel;
use App\Models\MoneyInfo;
use App\Models\Renter;
use App\Models\RenterRoom;
use App\Models\Room;
use App\User;
use Carbon\Carbon;
use Illuminate\Console\Command;

class BackupData extends Command {
	/**
	 * The name and signature of the console command.
	 *
	 * @var string
	 */
	protected $signature = 'backup:data-user';

	/**
	 * The console command description.
	 *
	 * @var string
	 */
	protected $description = 'Command description';

	/**
	 * Create a new command instance.
	 *
	 * @return void
	 */
	public function __construct() {
		parent::__construct();
	}

	/**
	 * Execute the console command.
	 *
	 * @return mixed
	 */
	public function handle() {
		//

		// $email = 'phongnn57@gmail.com';
		$owners = User::where( 'type', User::OWNER )
		             // ->where( 'phone', '0868987355' )
		              ->get();
		foreach ( $owners as $owner ) {

			$checkConfig = Config::where( 'owner_id', $owner->id )->first();

			if ( ! $checkConfig ) {
				continue;
			}
			$backupNumberday = $checkConfig->backup_number_day;
			$backupHour      = $checkConfig->backup_hour;

			if ( empty( $backupNumberday ) ) {
				continue;
			}


			$checkBackupLog = BackupLog::where( 'owner_id', $owner->id )
                                        ->orderBy('created_at', 'desc')
			                           ->first();
			if ( $checkBackupLog ) {
				$latestDate = $checkBackupLog->created_at->startOfDay();
				$diffday    = abs( Carbon::now()->startOfDay()->diffInDays( $latestDate ) );

				if ( $diffday < $backupNumberday ) {
					 continue;
				}

			}

			$currentHour = Carbon::now()->hour;

			if ( $currentHour < $backupHour ) {
				continue;
			}

			$email = $owner->email;

			//$email        = 'huynt57@gmail.com';
			$hostelArr    = [];
			$roomArr      = [];
			$contractArr  = [];
			$moneyInfoArr = [];
			$collectArr   = [];
			$spendArr     = [];
			$ewArr        = [];
			$renterArr    = [];


			$hostels     = Hostel::where( 'owner_id', $owner->id )->get();
			$hostelOwner = Hostel::where( 'owner_id', $owner->id )->pluck( 'id' )->toArray();
			foreach ( $hostels as $hostel ) {
				$itemArr                   = [];
				$itemArr['Tên nhà trọ']    = $hostel->name;
				$itemArr['Số phòng']       = $hostel->number_rooms;
				$itemArr['Số phòng trống'] = $hostel->number_empty_rooms;
				$itemArr['Địa chỉ']        = $hostel->address;
				$hostelArr[]               = $itemArr;
			}

			$rooms = Room::whereIn( 'hostel_id', $hostelOwner )
			             ->has( 'hostel' )
			             ->with( [
				             'hostel'
			             ] )
			             ->get();

			foreach ( $rooms as $room ) {
				$itemArr                    = [];
				$itemArr['Nhà trọ']         = $room->hostel->name;
				$itemArr['Tên phòng']       = $room->name;
				$itemArr['Giá']             = $room->price;
				$itemArr['Tầng/khu/dãy']    = $room->block_name;
				$itemArr['Số người tối đa'] = $room->max_renters;
				$itemArr['Diện tích']       = $room->size;
				$itemArr['Đặt cọc']         = $room->deposit;
				$roomArr[]                  = $itemArr;
			}

			$moneyInfos = MoneyInfo::whereIn( 'hostel_id', $hostelOwner )
			                       ->with( [
				                       'contract'
			                       ] )
			                       ->get();

			foreach ( $moneyInfos as $moneyInfo ) {
				$itemArr                           = [];
				$itemArr['Mã hóa đơn']             = $moneyInfo->code;
				$itemArr['Mã hợp đồng']            = ! empty( $moneyInfo->contract ) ? $moneyInfo->contract->code : null;
				$itemArr['Mã hợp đồng tham chiếu'] = ! empty( $moneyInfo->contract ) ? $moneyInfo->contract->reference : null;
				$itemArr['Nội dung']               = $moneyInfo->money_info_name;
				$itemArr['Nhà trọ']                = ! empty( $moneyInfo->hostel ) ? $moneyInfo->hostel->name : null;
				$itemArr['Phòng trọ']              = ! empty( $moneyInfo->room ) ? $moneyInfo->room->name : null;
				$itemArr['Số tiền']                = $moneyInfo->amount;
				$itemArr['Đã thanh toán']          = $moneyInfo->pay;
				$itemArr['Còn lại']                = $moneyInfo->remain;
				$itemArr['Thời gian']              = $moneyInfo->date_action->format( 'd/m/Y' );
				$moneyInfoArr[]                    = $itemArr;
			}


			$contracts = Contract::whereIn( 'hostel_id', $hostelOwner )
			                     ->has( 'hostel' )
			                     ->has( 'room' )
			                     ->with( [
				                     'hostel',
				                     'room'
			                     ] )
			                     ->get();

			foreach ( $contracts as $contract ) {
				$itemArr                  = [];
				$itemArr['Mã tham chiếu'] = $contract->reference;
				$itemArr['Mã hợp đồng']   = $contract->code;
				$itemArr['Ngày HĐ']       = $contract->date_contract->format( 'd/m/Y' );
				$itemArr['Nhà trọ']       = ! empty( $contract->hostel ) ? $contract->hostel->name : null;
				$itemArr['Phòng trọ']     = ! empty( $contract->room ) ? $contract->room->name : null;
				$itemArr['Thời hạn thuê'] = ! empty( $contract->date_end ) ? $contract->date_end->format( 'd/m/Y' ) : null;
				$itemArr['Giá']           = $contract->room_price;
				$itemArr['Tiền cọc']      = $contract->deposit;
				$itemArr['Người ký HĐ']   = $contract->name;
				$itemArr['Trạng thái']    = strip_tags( $contract->status_text );
				$contractArr[]            = $itemArr;
			}

			$collects = CollectSpend::where( function ( $q ) use ( $hostelOwner, $owner ) {
				$q->orWhereIn( 'hostel_id', $hostelOwner );
				$q->orWhere( 'owner_id', $owner->id );
			} )->with( [
				'contract',
				'moneyInfo',
				'hostel',
				'room'
			] )->where( 'type', CollectSpend::COLLECT )
			                        ->get();

			foreach ( $collects as $collect ) {
				$itemArr                           = [];
				$itemArr['Mã phiếu thu']           = $collect->code;
				$itemArr['Mã hóa đơn']             = ! empty( $collect->moneyInfo ) ? $collect->moneyInfo->code : null;
				$itemArr['Mã hợp đồng']            = ! empty( $collect->contract ) ? $collect->contract->code : null;
				$itemArr['Mã hợp đồng tham chiếu'] = ! empty( $collect->contract ) ? $collect->contract->reference : null;
				$itemArr['Nhà trọ']                = ! empty( $collect->hostel ) ? $collect->hostel->name : null;
				$itemArr['Phòng trọ']              = ! empty( $collect->room ) ? $collect->room->name : null;
				$itemArr['Số tiền thu']            = $collect->amount;
				$itemArr['Người nộp tiền']         = $collect->payer;
				$itemArr['Nội dung thu']           = $collect->name;
				$itemArr['Phương thức thanh toán'] = strip_tags( $collect->payment_method_text );
				$itemArr['Ngày thu']               = $collect->date_action->format( 'd/m/Y' );
				$itemArr['Ghi chú']                = $collect->note;
				$collectArr[]                      = $itemArr;
			}


			$spends = CollectSpend::where( function ( $q ) use ( $hostelOwner, $owner ) {
				$q->orWhereIn( 'hostel_id', $hostelOwner );
				$q->orWhere( 'owner_id', $owner->id );
			} )->with( [
				'contract',
				'hostel',
				'room'
			] )->where( 'type', CollectSpend::SPEND )
			                      ->get();

			foreach ( $spends as $spend ) {
				$itemArr                           = [];
				$itemArr['Mã phiếu thu']           = $spend->code;
				$itemArr['Mã hợp đồng']            = ! empty( $spend->contract ) ? $spend->contract->code : null;
				$itemArr['Mã hợp đồng tham chiếu'] = ! empty( $spend->contract ) ? $spend->contract->reference : null;
				$itemArr['Nhà trọ']                = ! empty( $spend->hostel ) ? $spend->hostel->name : null;
				$itemArr['Phòng trọ']              = ! empty( $spend->room ) ? $spend->room->name : null;
				$itemArr['Số tiền chi']            = $spend->amount;
				$itemArr['Người nhận tiền']        = $spend->payer;
				$itemArr['Nội dung chi']           = $spend->name;
				$itemArr['Phương thức thanh toán'] = strip_tags( $spend->payment_method_text );
				$itemArr['Ngày chi']               = $spend->date_action->format( 'd/m/Y' );
				$itemArr['Ghi chú']                = $spend->note;
				$spendArr[]                        = $itemArr;
			}

			$ews = ElectricWater::whereIn( 'hostel_id', $hostelOwner )
			                    ->with( [
				                    'hostel',
				                    'room',
				                    'contract'
			                    ] )
			                    ->has( 'hostel' )
			                    ->has( 'room' )
			                    ->get();

			foreach ( $ews as $ew ) {
				$itemArr = [];

				$itemArr['Nhà trọ']     = ! empty( $ew->hostel ) ? $ew->hostel->name : null;
				$itemArr['Phòng trọ']   = ! empty( $ew->room ) ? $ew->room->name : null;
				$itemArr['Mã hợp đồng'] = ! empty( $ew->contract ) ? $ew->contract->code : null;
				$itemArr['Tháng']       = $ew->date_action->format( 'm/Y' );
				$itemArr['Ngày chốt']   = ! empty( $ew->date_execution ) ? $ew->date_execution->format( 'd/m/Y' ) : null;

				$itemArr['Số điện đầu']     = $ew->start_electric;
				$itemArr['Số điện cuối']    = $ew->end_electric;
				$itemArr['Chênh lệch điện'] = $ew->delta_electric;

				$itemArr['Số nước đầu']     = $ew->start_water;
				$itemArr['Số nước cuối']    = $ew->end_water;
				$itemArr['Chênh lệch nước'] = $ew->delta_water;

				$ewArr[] = $itemArr;
			}

			$items = Renter::whereIn( 'renters.hostel_id', $hostelOwner );
			$items = $items->select( \DB::raw( 'renters.*' ) )
			               ->join( 'renter_rooms', 'renters.user_id', '=', 'renter_rooms.user_id' )
			               ->whereNull( 'renter_rooms.deleted_at' )
			               ->with( 'user' )
			               ->groupBy( \DB::raw( 'renter_rooms.user_id' ) )->get();


			foreach ( $items as $itemE ) {
				$itemArr = [];
				$user    = $itemE->user;
				$message = 'Chưa khai báo';
				if ( $itemE->residence_status == RenterRoom::RESIDENCE_LIMIT ) {
					$message = 'Có thời hạn';
				}
				if ( $itemE->residence_status == RenterRoom::RESIDENCE_NOT_LIMIT ) {
					$message = 'Không thời hạn';
				}

				$itemArr['Tên']              = $itemE->name;
				$itemArr['SDT']              = $itemE->phone;
				$itemArr['Ngày sinh']        = ! empty( $itemE->birthday ) ? $itemE->birthday->format( 'd/m/Y' ) : '';
				$itemArr['Quê quán']         = $itemE->address;
				$itemArr['CMT']              = ! empty( $user ) ? $user->id_number : '';
				$itemArr['Ngày cấp']         = ! empty( $itemE->id_number_date ) ? Carbon::createFromFormat( 'Y-m-d', $itemE->id_number_date )->format( 'd/m/Y' ) : '';
				$itemArr['Nơi cấp']          = $itemE->id_number_location;
				$itemArr['Nhà trọ']          = $itemE->hostel_name;
				$itemArr['Phòng trọ']        = $itemE->room_name;
				$itemArr['Ngày vào']         = ! empty( $itemE->date_joined ) ? $itemE->date_joined->format( 'd/m/Y' ) : '';
				$itemArr['Tạm trú']          = $message;
				$itemArr['Thời hạn tạm trú'] = ! empty( $itemE->údate_end_residence ) ? $itemE->date_end_residence->format( 'd/m/Y' ) : '';
				$renterArr[]                 = $itemArr;
			}

			$name = 'Du-lieu-backup-ngay-' . Carbon::now()->format( 'd-m-Y' ) . '-' . uniqid();

			$styleArray = array(
				'borders' => array(
					'allborders' => array(
						'style' => \PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			\Excel::create( $name, function ( $excel ) use (
				$hostelArr, $roomArr,
				$contractArr, $moneyInfoArr, $collectArr, $spendArr, $ewArr, $renterArr, $styleArray
			) {
				$excel->sheet( 'nha-tro', function ( $sheet ) use ( $hostelArr, $styleArray ) {
					$sheet->fromArray( $hostelArr );
					$sheet->getStyle( 'A1:D' . ( count( $hostelArr ) + 1 ) )->applyFromArray( $styleArray );
				} );


				$excel->sheet( 'phong-tro', function ( $sheet ) use ( $roomArr, $styleArray ) {
					$sheet->fromArray( $roomArr );
					$sheet->getStyle( 'A1:G' . ( count( $roomArr ) + 1 ) )->applyFromArray( $styleArray );
				} );

				$excel->sheet( 'hoa-don', function ( $sheet ) use ( $moneyInfoArr, $styleArray ) {

					$sheet->fromArray( $moneyInfoArr );
					$sheet->getStyle( 'A1:J' . ( count( $moneyInfoArr ) + 1 ) )->applyFromArray( $styleArray );
				} );

				$excel->sheet( 'hơp-dong', function ( $sheet ) use ( $contractArr, $styleArray ) {


					$sheet->fromArray( $contractArr );
					$sheet->getStyle( 'A1:J' . ( count( $contractArr ) + 1 ) )->applyFromArray( $styleArray );

				} );

				$excel->sheet( 'phieu-thu', function ( $sheet ) use ( $collectArr, $styleArray ) {


					$sheet->fromArray( $collectArr );
					$sheet->getStyle( 'A1:L' . ( count( $collectArr ) + 1 ) )->applyFromArray( $styleArray );
				} );

				$excel->sheet( 'phieu-chi', function ( $sheet ) use ( $spendArr, $styleArray ) {


					$sheet->fromArray( $spendArr );
					$sheet->getStyle( 'A1:K' . ( count( $spendArr ) + 1 ) )->applyFromArray( $styleArray );
				} );

				$excel->sheet( 'dien-nuoc', function ( $sheet ) use ( $ewArr, $styleArray ) {


					$sheet->fromArray( $ewArr );
					$sheet->getStyle( 'A1:K' . ( count( $ewArr ) + 1 ) )->applyFromArray( $styleArray );
				} );

				$excel->sheet( 'nguoi-tro', function ( $sheet ) use ( $renterArr, $styleArray ) {


					$sheet->fromArray( $renterArr );
					$sheet->getStyle( 'A1:L' . ( count( $renterArr ) + 1 ) )->applyFromArray( $styleArray );
				} );


			} )->store( 'xlsx', public_path( 'files/' ) );


			\Mail::send( 'frontend3.mail.mail_backup', [
				'owner' => $owner
			], function ( $message ) use ( $email, $name ) {
				// $message->to('huynt57@gmail.com');
				$message->to( $email );
				$message->subject( '[iTro.vn] Dữ liệu backup ngày ' . Carbon::now()->format( 'd/m/Y' ) );
				$message->attach( public_path( 'files/' . $name . '.xlsx' ) );
			} );

            BackupLog::create([
                'owner_id' => $owner->id
            ]);

			$this->line( 'Done backup for owner ' . $owner->id . ' at ' . Carbon::now()->format( 'd/m/Y H:i:s' ) );
			$this->line( '-------' );
		}
	}
}