<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Str;
use Illuminate\Http\Request;
use DateTime;

use App\Payment;
use App\Supply;
use App\SupplierLedger;
use Carbon\Carbon;

class PaymentController extends Controller
{
	public function search(Request $request)
	{
		$formdata = [
			'PaymentDateFrom' => $request->input('PaymentDateFrom', null),
			'PaymentDateTo' => $request->input('PaymentDateTo', null),
			'PaymentNumber' => $request->input('PaymentNumber', null),
			'SupplierCode' => $request->input('SupplierCode', 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('Payment', ['formdata' => $formdata]);

		$query = Payment::query();
		$query->selectRaw("payments.id");
		$query->selectRaw("payments.PaymentNumber");
		$query->selectRaw("payments.attributes");

		if ($formdata['PaymentDateFrom'])
		{
			$query->whereRaw("'{$formdata['PaymentDateFrom']}' <= CAST(payments.attributes->'$.PaymentDate' AS DATE)");
		}
		if ($formdata['PaymentDateTo'])
		{
			$query->whereRaw(" CAST(payments.attributes->'$.PaymentDate' AS DATE) <= '{$formdata['PaymentDateTo']}'");
		}
		if ($formdata['PaymentNumber'])
		{
			$query->whereRaw("(TRIM(BOTH '\"' FROM payments.attributes->'$.PaymentNumber') LIKE '%{$formdata['PaymentNumber']}%')");
		}
		if ($formdata['SupplierCode'])
		{
			$query->whereRaw("(TRIM(BOTH '\"' FROM payments.attributes->'$.SupplierCode') LIKE '%{$formdata['SupplierCode']}%')");
		}

		$query->leftJoin('staff', "payments.attributes->Staff", '=', 'staff.code');
		$query->selectRaw("staff.name as staff_name");
		
		$query->leftJoin('suppliers', "payments.attributes->SupplierCode", '=', 'suppliers.code');
		$query->selectRaw("suppliers.name as supplier_name");

		//logger('Payment.search', ['sql' => $query->toSql()]);


		$payments = $query->get();
		foreach($payments as $payment)
		{
			$payment['attributes'] = json_decode($payment['attributes'],true);
		}
		//logger('Payment', ['sql' => $query->toSql()]);

		$resp = [
			'payments' => $payments,
			'transactionID' => $formdata['transactionID'],
		];
		return response()->json($resp);
	}

	public function transactionStatus(Request $request)
	{
		$formdata = [
			'PaymentNumber' => $request->input('PaymentNumber', null),
			'PaymentDate' => $request->input('PaymentDate', null),
			'SupplierCode' => $request->input('SupplierCode', 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 = [];
		$payments = [];
		$prev = 0;

		if($formdata['SupplierCode'] != null)
		{
			//前月買掛残
			if($formdata['PaymentDate'] != null)
			{
				// DateTimeオブジェクトに変換
				$dateTime = new DateTime($formdata['PaymentDate']);

				// 1ヶ月前の月初日（1日）を取得
				$firstDayOfLastMonth = $dateTime->modify('-1 month')->format('Y-m-01');

				// 1ヶ月前の月末日を取得
				$lastDayOfLastMonth = date('Y-m-t', strtotime($firstDayOfLastMonth));

				// 前月買掛
				$query = Supply::query();
				$query->selectRaw("supplies.attributes->'$.\"仕入先\".SupplierCode' AS SupplierCode");
				$query->selectRaw("SUM(JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, '$.\"Summary\".\"合計金額\"'))) AS 合計金額");
				$query->whereRaw("supplies.attributes->'$.\"仕入先\".SupplierCode' = '" . $formdata['SupplierCode'] . "'");
				//$query->whereRaw("supplies.attributes->'$.Summary.締めフラグ' = 6");
				$query->whereRaw("CAST(supplies.attributes->'$.\"仕入日\"' AS DATE) BETWEEN '" . $firstDayOfLastMonth . "' and '" . $lastDayOfLastMonth . "'");
				$query->groupByRaw("supplies.attributes->'$.\"仕入先\".SupplierCode'");

				logger('Payment.transactionStatus', ['sql' => $query->toSql()]);

				$supplies = $query->get();
				if($supplies->isEmpty()) {
					$zengetukaikake = 0;
				} else {
					$zengetukaikake = $supplies->first()->合計金額;
				}
			
				// 前月支払額
				$query = Payment::query();
				$query->selectRaw("payments.attributes->'$.SupplierCode' AS SupplierCode");
				$query->selectRaw("SUM(JSON_UNQUOTE(JSON_EXTRACT(payments.attributes, '$.PaymentAmount'))) AS PaymentAmount");
				$query->whereRaw("payments.attributes->'$.SupplierCode' = '" . $formdata['SupplierCode'] . "'");
				//$query->whereRaw("payments.attributes->'$.ClosedFlag' = 6");
				$query->whereRaw("CAST(payments.attributes->'$.PaymentDate' AS DATE) BETWEEN '" . $firstDayOfLastMonth . "' and '" . $lastDayOfLastMonth . "'");
				$query->groupByRaw("payments.attributes->'$.SupplierCode'");
	
				logger('Payment.transactionStatus', ['sql' => $query->toSql()]);
				
				$payments = $query->get();
				if($payments->isEmpty()) {
					$zengetusiharai = 0;
				} else {
					$zengetusiharai = $payments->first()->PaymentAmount;
				}

				// 前月買掛残
				$prev = $zengetukaikake - $zengetusiharai;
	
				logger('Payment.transactionStatus', ['prev' => $prev]);


				// 前月の仕入先元帳を取得
				$query = SupplierLedger::query();
				$query->selectRaw("SupplierCode");
				$query->selectRaw("PaymentDate");
				$query->selectRaw("CurrAccountsPayable"); // 当月買掛残
				$query->whereRaw("SupplierCode = '" . $formdata['SupplierCode'] . "'");
				$query->whereRaw("CAST(PaymentDate AS DATE) <= '{$lastDayOfLastMonth}'");
				$query->orderByRaw("CAST(PaymentDate AS DATE) DESC");
				$prerows = $query->get();

				logger('MonthlyPurchase.SupplierLedger', ['sql' => $query->toSql()]);

				if($prerows->isEmpty())
				{
					// 前月データなし
					$prev = 0;
				} else {
					// 前月データあり
					$prev = $prerows[0]['CurrAccountsPayable'];	// 前月の当月買掛残をセット
				}

				

			}
			// DateTimeオブジェクトに変換
			$dateTime = new DateTime($formdata['PaymentDate']);

			// 今月の月初日（1日）を取得
			$firstDayOfThisMonth = $dateTime->format('Y-m-01');

			// 1ヶ月前の月末日を取得
			$lastDayOfThisMonth = date('Y-m-t', strtotime($firstDayOfThisMonth));

			//当月買掛
			$query = Supply::query();
			$query->selectRaw("supplies.attributes->'$.\"仕入先\".SupplierCode' AS SupplierCode");
			$query->selectRaw("SUM(JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, '$.\"Summary\".\"合計金額\"'))) AS 合計金額");
			$query->whereRaw("supplies.attributes->'$.\"仕入先\".SupplierCode' = '" . $formdata['SupplierCode'] . "'");
			//$query->whereRaw("supplies.attributes->'$.Summary.締めフラグ' <= 5");
			$query->whereRaw("CAST(supplies.attributes->'$.\"仕入日\"' AS DATE) BETWEEN '" . $firstDayOfThisMonth . "' and '" . $lastDayOfThisMonth . "'");
			$query->groupByRaw("supplies.attributes->'$.\"仕入先\".SupplierCode'");

			//logger('Payment.transactionStatus', ['sql' => $query->toSql()]);
			
			$supplies = $query->get();

			$transactionstatus = $supplies;
			//logger('Payment.transactionStatus', ['transactionstatus' => $transactionstatus]);
			
			//今月支払額
			$query = Payment::query();
			$query->selectRaw("payments.attributes->'$.SupplierCode' AS SupplierCode");
			$query->selectRaw("SUM(JSON_UNQUOTE(JSON_EXTRACT(payments.attributes, '$.PaymentAmount'))) AS PaymentAmount");
			if(!empty($formdata['PaymentNumber'])) {
				$query->whereRaw("PaymentNumber != " . $formdata['PaymentNumber']);
			}
			$query->whereRaw("payments.attributes->'$.SupplierCode' = '" . $formdata['SupplierCode'] . "'");
			//$query->whereRaw("payments.attributes->'$.ClosedFlag' <= 5");
			$query->whereRaw("CAST(payments.attributes->'$.PaymentDate' AS DATE) BETWEEN '" . $firstDayOfThisMonth . "' and '" . $lastDayOfThisMonth . "'");
			$query->groupByRaw("payments.attributes->'$.SupplierCode'");

			logger('Payment.transactionStatus', ['sql' => $query->toSql()]);
			
			$payments = $query->get();
			//logger('Payment.transactionStatus', ['payments' => $payments]);

		}

		$resp = [
			'prev' => $prev,
			'transactionstatus' => $transactionstatus,
			'payments' => $payments,
		];

		return response()->json($resp);

	}

	public function update(Request $request)
	{
		$resp = 0;
		$formdata = [
			'action' => $request->input('action', null),
			'payment_id' => $request->input('payment_id', null),
			'attributes' => $request->input('attributes', null),
		];
		$this->OperationLog([],['formdata' => $formdata]);

		logger('Payment.update', ['formdata' => $formdata]);

		if (empty($formdata['attributes']['PaymentNumber'])) {
			// 新規支払伝票は 伝票番号を付番
			$formdata['attributes']['PaymentNumber'] = Payment::GetNewPaymentNumber();
		}

		$fields = [
			'attributes' => json_encode($formdata['attributes']),
			'deleted' => null,
		];

		$paymentModel = Payment::query();
		if ($formdata['action'] == 'update') {
			$id = $formdata['payment_id'];
			$paymentModel->updateOrInsert(['id' => $formdata['payment_id']],$fields);
		} else {
			logger('Payment.update', ['__LINE__' => __LINE__]);
			$id = $paymentModel->insertGetId($fields);
			logger('Payment.update', ['__LINE__' => __LINE__]);
		}

		$resp = [
			'id' => $id,
		];
		return response()->json($resp);

	}


	public function delete(Request $request)
	{
		$resp = 0;
		$formdata = [
			'payment_id' => $request->input('payment_id', null),
		];
		$this->OperationLog([],['formdata' => $formdata]);

		$paymentModel = Payment::query();
		$resp = $paymentModel->where('id', $formdata['payment_id'])
			->update(['deleted' => Carbon::now()]);

		return response()->json($resp);
	}

	// 帳票
	public function report()
	{
		logger('Payment.report', [__FILE__ => __LINE__]);
		$formdata = [
			'payment_id' => request('payment_id'),
		];
		$this->OperationLog([],['formdata' => $formdata]);

		$resp = 0;
		logger('Payment.report', ['formdata' => $formdata]);

		$query = Payment::query();
		//$query->where('deleted', null);
		//$query->whereRaw("JSON_CONTAINS_PATH(attributes,'all','$.Outline.OrderNumber','$.Outline.OrderDate') = 1");
		//$query->where("attributes->'$.supplier' != CAST('null' AS JSON)");
		$query->where('id', $formdata['payment_id']);
		logger('Payment.report', ['sql' => $query->toSql()]);
		$payment = $query->first();

		logger('Payment.report', ['payment' => $payment]);

		$payment['attributes'] = json_decode($payment['attributes'],true);

		$viewdata = [
			'payment' => $payment
		];

		logger('Payment.report', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("payment.report01",compact('viewdata'));

    }
}