<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Str;
use Illuminate\Http\Request;


use App\Deposit;
use App\DepositDetail;
use App\Delivery;
use App\DeliveryDetail;
use App\Invoice;
use Carbon\Carbon;

class DepositController extends Controller
{
	public function search(Request $request)
	{
		$formdata = [
			'DepositDateFrom' => $request->input('DepositDateFrom', null),
			'DepositDateTo' => $request->input('DepositDateTo', null),
			'DepositNumber' => $request->input('DepositNumber', null),
			'CustomerCode' => $request->input('CustomerCode', null),
			'transactionID' => $request->input('transactionID', null),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		$this->OperationLog($version,['formdata' => $formdata]);
		logger('Deposit', ['formdata' => $formdata]);

		$query = Deposit::query();
		$query->selectRaw("deposits.id");
		$query->selectRaw("deposits.DepositNumber");
		$query->selectRaw("deposits.attributes");

		if ($formdata['DepositDateFrom'])
		{
			$query->whereRaw("'{$formdata['DepositDateFrom']}' <= CAST(deposits.attributes->'$.DepositDate' AS DATE)");
		}
		if ($formdata['DepositDateTo'])
		{
			$query->whereRaw(" CAST(deposits.attributes->'$.DepositDate' AS DATE) <= '{$formdata['DepositDateTo']}'");
		}
		if ($formdata['DepositNumber'])
		{
			$query->where('DepositNumber', 'LIKE', "%{$formdata['DepositNumber']}%");
		}
		if ($formdata['CustomerCode'])
		{
			$query->whereRaw("deposits.attributes->>'$.CustomerCode' LIKE '%{$formdata['CustomerCode']}%')");
		}

		$query->leftJoin('staff', "deposits.attributes->Staff", '=', 'staff.code');
		$query->selectRaw("staff.name as staff_name");

		$query->leftJoin('customers', "deposits.attributes->CustomerCode", '=', 'customers.code');
		$query->selectRaw("customers.name as customer_name");

		logger('Deposit.search', ['sql' => $query->toSql()]);

		$deposits = $query->get();

		foreach($deposits as &$deposit)
		{
			$deposit['attributes'] = json_decode($deposit['attributes'],true);

			$queryDetail = DepositDetail::query();
			$queryDetail->where('deposit_number', $deposit['DepositNumber']);
			$queryDetail->orderBy('id');
			//logger('Deposit.search', ['sql' => $queryDetail->toSql()]);
			$deposit['DepositDetails'] = $queryDetail->get();
			//logger('Deposit', ['deposit_details' => $deposit['DepositDetails']]);
		}
		//logger('Deposit', ['deposits' => $deposits]);

		$resp = [
			'deposits' => $deposits,
			'transactionID' => $formdata['transactionID'],
		];
		return response()->json($resp);
	}

	public function transactionStatus(Request $request)
	{
		$formdata = [
			'DepositNumber' => $request->input('DepositNumber', null),
			'DepositDate' => $request->input('DepositDate', null),
			'CustomerCode' => $request->input('CustomerCode', null),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		$this->OperationLog($version,['formdata' => $formdata]);

		$transactionstatus = [];

		if($formdata['DepositDate'] != null and $formdata['CustomerCode'] != null)
		{
			// 入金日以前の最新の締め日を取得
			/*
			$query = Delivery::query();
			$DeliveryDateColumn = "CAST(deliveries.attributes->'$.\"納品日\"' AS DATE)";
			$query->selectRaw("{$DeliveryDateColumn} AS DeliveryDate");
			$query->selectRaw("deliveries.attributes->'$.Summary.締めフラグ' AS ClosedFlag");
			$query->whereRaw("deliveries.attributes->'$.Summary.締めフラグ' IN (6)");
			$query->whereRaw("{$DeliveryDateColumn} >= DATE_ADD(NOW(), INTERVAL - 2 YEAR)");
			$query->whereRaw("CAST(billing_closing_day AS DATE) < '{$formdata['DepositDate']}'");
			$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
			$query->selectRaw("billing.attributes->'$.締日' as billing_closing_day");
			$query->groupByRaw("DeliveryDate");
			$query->groupByRaw("ClosedFlag");
			$query->groupByRaw("billing_closing_day");
			$query->orderByRaw('billing_closing_day DESC');
			logger('Deposit.transactionStatus', ['sql' => $query->toSql()]);
			$deliveries = $query->get();
			*/

			// 最終締日 を取得
			$closes = [];

			// 売上一覧 から 締め済み(締めフラグ = 6)の日付を取得
			$query = Delivery::query();
			$DeliveryDateColumn = "CAST(deliveries.attributes->'$.\"納品日\"' AS DATE)";
			$query->selectRaw("{$DeliveryDateColumn} AS DeliveryDate");
			$query->selectRaw("deliveries.attributes->'$.Summary.締めフラグ' AS ClosedFlag");
			$query->whereRaw("deliveries.attributes->'$.Summary.締めフラグ' IN (6)");
			$query->whereRaw("{$DeliveryDateColumn} >= DATE_ADD(NOW(), INTERVAL - 2 YEAR)");
			//$query->whereRaw("{$DeliveryDateColumn} < '" . $formdata['DepositDate'] . "'");
			$query->whereRaw('BillingCustomerCode = ' . $formdata['CustomerCode']);
			$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
			$query->selectRaw("billing.attributes->'$.締日' as billing_closing_day");
			$query->groupByRaw("DeliveryDate");
			$query->groupByRaw("ClosedFlag");
			$query->groupByRaw("billing_closing_day");

			logger('Deposit.transactionStatus', ['sql' => $query->toSql()]);
			
			$deliveries = $query->get();
			//logger('Deposit.transactionStatus', ['deliveries' => $deliveries]);
			foreach($deliveries as $delivery)
			{
				// 締日を計算
				if ($delivery['billing_closing_day'] == '99')
				{
					// 月末
					$DeliveryDate = strtotime($delivery['DeliveryDate']);
					$CloseDate = date('Y-m-t', $DeliveryDate);
				}
				else
				{
					$temp = strtotime($delivery['DeliveryDate'] . " - {$delivery['billing_closing_day']} days + 1 month");
					$CloseDate = date('Y-m-', $temp) . $delivery['billing_closing_day'];
				}

				switch($delivery['ClosedFlag'])
				{
					case '6':
						if (!in_array($CloseDate, $closes))
						{
							$closes[] = $CloseDate;
						}
						break;
				}
			}

			// 入金日より以前の締め日を取得
			$closed = null;
			if (!empty($closes) > 0)
			{
				rsort($closes);
				$closed = $closes[0];
				foreach($closes as $closedate) {
					if($closedate < $formdata['DepositDate']) {
						$closed = $closedate;
						break;
					}

				}
			}	

			// 前月繰越額を取得（前回の締め日以前の請求書データから当月請求を取得）
			$query = Invoice::query();
			$query->selectRaw("BillingCustomerCode");
			$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"請求額\"' as 当月請求額");
			//$query->whereRaw("CAST(TRIM(BOTH '\"' FROM invoices.attributes->'$.\"当月請求\".\"請求日\"') AS DATE) < '{$formdata['DepositDate']}'");
			$query->whereRaw("CAST(TRIM(BOTH '\"' FROM invoices.attributes->'$.\"当月請求\".\"請求日\"') AS DATE) <= '{$formdata['DepositDate']}'");
			$query->whereRaw('BillingCustomerCode = ' . $formdata['CustomerCode']);
			$query->orderByRaw("invoices.attributes->'$.\"当月請求\".\"請求日\"' DESC");
			
			logger('Deposit.transactionStatus', ['sql' => $query->toSql()]);
			$invoices = $query->get();

			$transactionstatus = $invoices;

			// 当月入金額を取得
			$query = Deposit::query();
			$query->selectRaw("Sum(deposits.attributes->'$.DepositAmount') AS DepositAmount");
			$query->whereRaw("deposits.attributes->'$.CustomerCode' = '" . $formdata['CustomerCode'] . "'");
			$query->whereRaw("CAST(TRIM(BOTH '\"' FROM deposits.attributes->'$.DepositDate') AS DATE) < '{$formdata['DepositDate']}'");
			if(!is_null($closed)) {
				$query->whereRaw("CAST(TRIM(BOTH '\"' FROM deposits.attributes->'$.DepositDate') AS DATE) > '{$closed}'");
			}
			logger('Deposit.deposits', ['sql' => $query->toSql()]);
			$deposits = $query->get();

			logger('Deposit.deposits', ['deposits' => $deposits]);


			if(!empty($transactionstatus)) {
				if(!empty($deposits)) {
					if(is_nan($deposits[0]['DepositAmount'])) {
						$transactionstatus[0]['今月入金額'] = 0;
					} else {
						$transactionstatus[0]['今月入金額'] = $deposits[0]['DepositAmount'];
					}
				} else {
					$transactionstatus[0]['今月入金額'] = 0;
				}
			}

			// 当月売上、当月値引、消費税を取得
			//$query = Delivery::query();
			//DB::raw('SUM(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$.Summary."消費税額"\'))) as syouhizei')	
			$query = DB::table('deliveries')
			->select(
				DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先".CustomerCode\')) as customer_code'),
				'customers.name as customer_name',
				DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) IN (\'01\', \'02\') THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as uriage'),
				DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) IN (\'03\', \'04\') THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as nebiki'),
				DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) IN (\'01\', \'02\') THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."消費税額"\')) ELSE 0 END) as syouhizei')
			)
			->join('delivery_details as jt', 'jt.delivery_number', '=', 'deliveries.DeliveryNumber')
			->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先".CustomerCode\'))'), '=', 'customers.code')
			->leftJoin('sales_kind', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\'))'), '=', 'sales_kind.code')
			->where(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\'))'), '>', $closed)
			->whereRaw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先".CustomerCode\')) = ' . $formdata['CustomerCode'])
			->groupBy('customer_code', 'customers.name')
			->orderBy('customer_code');

			$deliveries = $query->get();
			logger('Deposit.transactionStatus', ['deliveries' => $deliveries]);

		}

		$resp = [
			'transactionstatus' => $transactionstatus,
			'deliveries' => $deliveries,
		];

		return response()->json($resp);

	}

	public function update(Request $request)
	{
		$resp = 0;
		$formdata = [
			'action' => $request->input('action', null),
			'deposit_id' => $request->input('deposit_id', null),
			'attributes' => $request->input('attributes', null),
			'details'    => $request->input('details', []),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		$this->OperationLog($version,['formdata' => $formdata]);

		logger('Deposit.update', ['formdata' => $formdata]);

		if (empty($formdata['attributes']['DepositNumber'])) {
			// 新規入金伝票は 伝票番号を付番
			$formdata['attributes']['DepositNumber'] = Deposit::GetNewDepositNumber();
		}

		$fields = [
			'attributes' => json_encode($formdata['attributes']),
			'deleted' => null,
		];

		$depositModel = Deposit::query();
		if ($formdata['action'] == 'update') {
			$id = $formdata['deposit_id'];
			$depositModel->updateOrInsert(['id' => $formdata['deposit_id']],$fields);
		} else {
			//logger('Deposit.update', ['__LINE__' => __LINE__]);
			$id = $depositModel->insertGetId($fields);
			//logger('Deposit.update', ['__LINE__' => __LINE__]);
		}

		// 明細 : いったん削除してから入れなおす
		$queryDetail = DepositDetail::query();
		$queryDetail->where('deposit_number', $formdata['attributes']['DepositNumber'])->delete();
		$number = 0;
		foreach($formdata['details'] as $detail)
		{
			$attributes = [
				'Number' => ++$number,
				'DepositType' => $detail['attributes']['DepositType'],
				'Amount' => $detail['attributes']['Amount'],
				'PaymentDate' => $detail['attributes']['PaymentDate'],
				'手形番号' => $detail['attributes']['手形番号'],
				'銀行番号' => $detail['attributes']['銀行番号'],
				'計算黒字' => $detail['attributes']['計算黒字'],
				'計算金額' => $detail['attributes']['計算金額'],
			];
			//logger('Deposit.update', ['$attributes' => $attributes]);
			$queryDetail = new DepositDetail;
			$queryDetail->deposit_number = $formdata['attributes']['DepositNumber'];
			$queryDetail->attributes = $attributes;
			$queryDetail->save();
		}

		$resp = [
			'id' => $id,
		];
		return response()->json($resp);

	}


	public function delete(Request $request)
	{
		$resp = 0;
		$formdata = [
			'deposit_id' => $request->input('deposit_id', null),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		$this->OperationLog($version,['formdata' => $formdata]);

		$depositModel = Deposit::query();
		$deposit = $depositModel->where('id', $formdata['deposit_id'])->first();

		$resp = $depositModel->where('id', $formdata['deposit_id'])
			->update(['deleted' => Carbon::now()]);

		$queryDetail = DepositDetail::query();
		$queryDetail->where('deposit_number', $deposit['DepositNumber'])
			->delete();


		return response()->json($resp);
	}

}