<?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\Delivery;
use App\DeliveryDetail;
use App\Deposit;
use App\Supply;
use App\Payment;
use App\DailyLock;

class DailyPrintController extends Controller
{

	public function search(Request $request)
	{
		$formdata = [
			'Action' => $request->input('Action', null),
			'LockKey' => $request->input('LockKey', null),
			'ClosingDate' => $request->input('ClosingDate', null), // 伝票ロック日
			'ClosedFlag' => $request->input('ClosedFlag', 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('DailyPrint.search', ['formdata' => $formdata]);

		if ($formdata['Action'] == 'Update')
		{
			// 伝票ロック

			$dailylock = new DailyLock;
			$dailylock->LockKey = $formdata['LockKey'];
			$dailylock->LockDate = $formdata['ClosingDate'];
			$dailylock->LockTime = date('H:i:s');
			$dailylock->ClosingFlag = $formdata['ClosedFlag'];
			$dailylock->save();

			$closedFlg = 1;
			$closingDate = $formdata['ClosingDate'];

			switch ($formdata['LockKey']) {
				case 'orders':
					// 受注伝票ロック
					// 納品伝票ロック
					DB::update(
						"UPDATE deliveries 
						 SET attributes = JSON_SET(attributes, 
							 '$.Summary.締めフラグ', ?, 
							 '$.Summary.締め日時', ?) 
						 WHERE 
							 JSON_EXTRACT(attributes, '$.Summary.\"締めフラグ\"') = 0 
							 AND JSON_EXTRACT(attributes, '$.\"納品日\"') <= ?",
						[$closedFlg, $closingDate, $closingDate]
					);
					break;

				case 'deposits':
					// 入金伝票ロック
					break;
			
				case 'payments':
					// 支払伝票ロック
					break;
			
				case 'supplies':
					// 仕入伝票ロック
					break;
			
				default:
					// その他または無効な場合
					break;
			}

		}
		elseif ($formdata['Action'] == 'Cancel')
		{
			// 伝票ロック 取消

			$dailylock = DailyLock::query();
			$dailylock->where("LockKey" , $formdata['LockKey']);
			$dailylock->where("LockDate" , $formdata['ClosingDate']);
			$dailylock->delete();

			$closedFlg = 0;
			$closingDate = $formdata['ClosingDate'];
		
			switch ($formdata['LockKey']) {
				case 'orders':
					// 受注伝票アンロック
					// 納品伝票アンロック
					DB::update(
						"UPDATE deliveries 
							SET attributes = JSON_SET(attributes, 
								'$.Summary.締めフラグ', ?, 
								'$.Summary.締め日時', NULL) 
							WHERE 
								JSON_EXTRACT(attributes, '$.Summary.\"締めフラグ\"') = 1 
								AND JSON_EXTRACT(attributes, '$.Summary.\"締め日時\"') = ?",
						[$closedFlg, $closingDate, $closingDate]
					);
					break;

				case 'deposits':
					// 入金伝票ロック
					break;
			
				case 'payments':
					// 支払伝票ロック
					break;
			
				case 'supplies':
					// 仕入伝票ロック
					break;
			
				default:
					// その他または無効な場合
					break;
			}
		}

		// 伝票ロック
		$uri_zendate1 = "";
		$uri_zendate3 = "";
		$shi_zendate1 = "";
		$shi_zendate2 = "";

		// 売上ロック最終日付
		$query = DailyLock::query();
		$query->selectRaw("daily_lock.id");
		$query->selectRaw("daily_lock.LockKey");
		$query->selectRaw("daily_lock.LockDate");
		$LockKey = 'orders';
		$query->whereRaw("daily_lock.LockKey = '$LockKey'");
		$query->orderBy("daily_lock.LockDate", "desc");

		logger('DailyPrint.search', ['sql' => $query->toSql()]);

		$dailylock = $query->first();
		if ($dailylock) {
			$uri_zendate1 = $dailylock->LockDate;
		} else {
			$uri_zendate1 = null; // デフォルト値を設定するか、エラーハンドリングを追加します
		}

		// 入金ロック最終日付
		$query = DailyLock::query();
		$query->selectRaw("daily_lock.id");
		$query->selectRaw("daily_lock.LockKey");
		$query->selectRaw("daily_lock.LockDate");
		$LockKey = 'deposits';
		$query->whereRaw("daily_lock.LockKey = '$LockKey'");
		$query->orderBy("daily_lock.LockDate", "desc");

		logger('DailyPrint.search', ['sql' => $query->toSql()]);

		$dailylock = $query->first();
		if ($dailylock) {
			$uri_zendate3 = $dailylock->LockDate;
		} else {
			$uri_zendate3 = null; // デフォルト値を設定するか、エラーハンドリングを追加します
		}

		// 仕入ロック最終日付
		$query = DailyLock::query();
		$query->selectRaw("daily_lock.id");
		$query->selectRaw("daily_lock.LockKey");
		$query->selectRaw("daily_lock.LockDate");
		$LockKey = 'supplies';
		$query->whereRaw("daily_lock.LockKey = '$LockKey'");
		$query->orderBy("daily_lock.LockDate", "desc");

		logger('DailyPrint.search', ['sql' => $query->toSql()]);

		$dailylock = $query->first();
		if ($dailylock) {
			$shi_zendate1 = $dailylock->LockDate;
		} else {
			$shi_zendate1 = null; // デフォルト値を設定するか、エラーハンドリングを追加します
		}

		// 支払ロック最終日付
		$query = DailyLock::query();
		$query->selectRaw("daily_lock.id");
		$query->selectRaw("daily_lock.LockKey");
		$query->selectRaw("daily_lock.LockDate");
		$LockKey = 'payments';
		$query->whereRaw("daily_lock.LockKey = '$LockKey'");
		$query->orderBy("daily_lock.LockDate", "desc");

		logger('DailyPrint.search', ['sql' => $query->toSql()]);

		$dailylock = $query->first();
		if ($dailylock) {
			$shi_zendate2 = $dailylock->LockDate;
		} else {
			$shi_zendate2 = null; // デフォルト値を設定するか、エラーハンドリングを追加します
		}

	
		$resp = [
			'uri_zendate1' => $uri_zendate1,
			'uri_zendate3' => $uri_zendate3,
			'shi_zendate1' => $shi_zendate1,
			'shi_zendate2' => $shi_zendate2,
		];
		return response()->json($resp);
	}

	// 一覧 帳票出力
	// view
	//   report01 : 売上日次明細一覧表
	public function report01(Request $request)
	{		
		$formdata = [
			'closing_date' => request('closing_date'),
			'view' => request('view','report01'),
		];
		$version = [
		];
		//$this->OperationLog($version,['formdata' => $formdata]);
		logger('Dailyuri.report01', [
			'formdata' => $formdata,
		]);

		$query = Delivery::query();
		$query->selectRaw("deliveries.id");
		$query->selectRaw("deliveries.attributes");
		$query->selectRaw("deliveries.DeliveryNumber");

		// 納品日
		$DeliveryDateColumn = "CAST(deliveries.attributes->'$.\"納品日\"' AS DATE)";
		if ($formdata['closing_date'])
		{
			$query->whereRaw("'{$formdata['closing_date']}' = {$DeliveryDateColumn}");
		}
		//$query->leftJoin('customers', "JSON_UNQUOTE(deliveries.attributes->'$.\"注文先\".CustomerCode')", '=', 'customers.code');
		$query->leftJoin('customers', "deliveries.attributes->注文先->CustomerCode", '=', 'customers.code');
		$query->selectRaw("customers.name as customer_name");
		$query->selectRaw("customers.code as customer_code");
		// 納品番号の照準
		$query->orderBy("DeliveryNumber");

		logger('Dailyuri.report01', ['sql' => $query->toSql()]);

		$dailyuris = $query->get();
		foreach($dailyuris as &$dailyuri)
		{
			$dailyuri['attributes'] = json_decode($dailyuri['attributes'],true);
			//$dailyuri['orders'] = json_decode($dailyuri['orders_del'],true);
			$orders = [];
			$query = DeliveryDetail::query();
			$query->selectRaw('delivery_number');
			$query->selectRaw('attributes');
			$query->selectRaw('sales_kind.name as saleskind_name');
			$query->selectRaw('sales_kind.surplus as saleskind_surplus');
			$query->leftjoin('sales_kind', 'delivery_details.attributes->売上区分No', '=', 'sales_kind.code');
			$query->whereRaw('delivery_number = ' . $dailyuri['DeliveryNumber']);
			$deliverydetail = $query->get();
			foreach($deliverydetail as $delivery)
			{
				/*
				$attributes = $delivery->attributes;  // attributesプロパティを取得
				$delivery->attributes['saleskind_name'] = $delivery->saleskind_name;
				$attributes['saleskind_surplus'] = $delivery->saleskind_surplus;

				$orders[] = (array)$delivery['attributes'];
				*/

				// モデルを配列に変換
				$attributes = $delivery->toArray();

				// 必要な属性を追加
				$attributes['attributes']['saleskind_name'] = $delivery->saleskind_name;
				$attributes['attributes']['saleskind_surplus'] = $delivery->saleskind_surplus;

				// 配列に追加
				$orders[] = $attributes['attributes'];
			}
			logger('Dailyuri.report01 ordres', ['orders' => $orders]);
			$dailyuri['orders'] = $orders;
		}

		//$dailyuris = [];
		$viewdata = [
			'dailyuris' => $dailyuris,
			'closingdate' => $formdata['closing_date']
		];
		//logger('Dailyuri.report01', ['viewdata' => $viewdata]);

		return view("dailyuri." . $formdata['view'], compact('viewdata'));

	}

	// 一覧 帳票出力
	// view
	//   report02 : 売上・入金日報
	public function report02(Request $request)
	{		
		$formdata = [
			'closing_date' => request('closing_date'),
			'view' => request('view','report02'),
		];
		$version = [
		];
		//$this->OperationLog($version,['formdata' => $formdata]);
		logger('Dailyuri.report02', [
			'formdata' => $formdata,
		]);

		//$query = Delivery::query();
		//$query->selectRaw('deliveries.attributes->"$.\\"請求先\\".CustomerCode" as customer_code, SUM(jt.money) as total_money');
		//$query->join(DB::raw('JSON_TABLE(deliveries.orders_del, "$[*]" COLUMNS(money INT PATH "$.金額")) as jt'), function($join) {
		//	// 条件が必要であればここに追加することができます
		//});

		// 日付形式を変換
        $formdata['closing_date'] = str_replace('/', '-', $formdata['closing_date']);

		//$query->whereRaw('CAST(deliveries.attributes->\'$."納品日"\' AS DATE) = ?', [$formdata['closing_date']]);
		//$query->groupBy(DB::raw('deliveries.attributes->"$.\\"請求先\\".CustomerCode"'));
		//$query->orderBy(DB::raw('deliveries.attributes->"$.\\"請求先\\".CustomerCode"'));
	
		// 納品日
		//$DeliveryDateColumn = "CAST(deliveries.attributes->'$.\"納品日\"' AS DATE)";
		//if ($formdata['closing_date'])
		//{
		//	$query->whereRaw("'{$formdata['closing_date']}' = {$DeliveryDateColumn}");
		//}
		//$query->leftJoin('customers', "JSON_UNQUOTE(deliveries.attributes->'$.\"注文先\".CustomerCode')", '=', 'customers.code');
		//$query->leftJoin('customers', "deliveries.attributes->注文先->CustomerCode", '=', 'customers.code');
		//$query->selectRaw("customers.name as customer_name");


		/*
		$query = Delivery::query();
		$query->selectRaw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先"."CustomerCode"\')) AS customer_code, JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) as urino, SUM(CASE WHEN urino = \'01\' THEN jt.money ELSE 0 END) AS total_money1, SUM(CASE WHEN urino = \'02\' THEN jt.money ELSE 0 END) AS total_money2, SUM(CASE WHEN jt.urino = \'03\' THEN jt.money ELSE 0 END) AS total_money3, SUM(CASE WHEN jt.urino = \'04\' THEN jt.money ELSE 0 END) AS total_money4, SUM(CASE WHEN jt.urino = \'07\' THEN jt.money ELSE 0 END) AS total_money7, SUM(CASE WHEN jt.urino = \'02\' THEN jt.money ELSE 0 END) AS total_money2, SUM(CASE WHEN jt.urino IN (\'05\', \'06\', \'08\') THEN jt.money * sales_kind.surplus ELSE 0 END) AS total_money99, COUNT(jt.money) AS detail_count, customers.name AS customer_name')
    	->join(DB::raw('JSON_TABLE(deliveries.orders_del, \'$[*]\' COLUMNS(money INT PATH \'$.金額\', urino VARCHAR(255) PATH \'$."売上区分No"\')) AS jt'), function ($join) {
        	// join句に条件が必要であればここに追加
    	})
		-join('delivery_details as jt', 'deliveries.DeliveryNumber', "=", 'delivery_details.delivery_number')
    	->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."注文先"."CustomerCode"\'))'), '=', 'customers.code')
		->leftJoin('sales_kind', 'jt.urino', '=', 'sales_kind.code')
    	->whereRaw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE) = ?', [$formdata['closing_date']])
    	->groupBy(DB::raw('customer_code, customers.name'))
    	->orderBy(DB::raw('customer_code'));
		*/

		$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"\')) = \'01\' THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as total_money1'),
			DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) = \'02\' THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as total_money2'),
			DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) = \'03\' THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as total_money3'),
			DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) = \'04\' THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as total_money4'),
			DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) = \'07\' THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) ELSE 0 END) as total_money7'),
			DB::raw('SUM(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\')) IN (\'05\', \'06\', \'08\') THEN JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) * sales_kind.surplus ELSE 0 END) as total_money99'),
			DB::raw('COUNT(JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\'))) as detail_count')
		)
		->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, \'$."納品日"\'))'), '=', $formdata['closing_date'])
		->groupBy('customer_code', 'customers.name')
		->orderBy('customer_code');

		//logger('Dailyuri.report02', ['sql' => $query->toSql()]);

		$startDate = date("Y-m-01", strtotime($formdata['closing_date']));
		$endDate = $formdata['closing_date'];
		//$startDate = '2022-11-01';
		//$endDate = '2022-11-29';

		$results = $query->get();
		// 配列に変換
		$dailyuris = [];
		foreach($results as $result)
		{
			$dailyuris[] = (array)$result;
		}

		foreach($dailyuris as &$dailyuri)
		{
			$customerCode = $dailyuri['customer_code'];
			//$customerCode = '00001';
			$dailyuri['total_money'] = $dailyuri['total_money1'] + $dailyuri['total_money2'] - $dailyuri['total_money3'] - $dailyuri['total_money4'] + $dailyuri['total_money7'] + $dailyuri['total_money99'];
			
			//$query2 = Delivery::query();
			//$query2->select(
			//	DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先"."CustomerCode"\')) AS customer_code'),
			//	'customers.name AS customer_name',
			//	DB::raw('jt.urino'),
			//	DB::raw('SUM(jt.money) AS total_money')
			//)
			//->join(DB::raw('JSON_TABLE(deliveries.orders_del, \'$[*]\' 
			//	COLUMNS(
			//		money INT PATH \'$.金額\',
			//		urino VARCHAR(255) PATH \'$."売上区分No"\'
			//	)
			//) AS jt'), 'deliveries.id', '=', 'deliveries.id')
			//->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."注文先"."CustomerCode"\'))'), '=', 'customers.code')
			//->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE)'), [$startDate, $endDate])
			//->where(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."注文先"."CustomerCode"\'))'), '=', $customerCode)
			//->groupBy('customer_code', 'customers.name', 'jt.urino')
			//->orderBy('customer_code');
			/*
			$query2->select(
				DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先"."CustomerCode"\')) AS customer_code'),
				'customers.name AS customer_name',
				DB::raw('SUM(jt.money) AS total_money')
			)
			->join(DB::raw('JSON_TABLE(deliveries.orders_del, \'$[*]\' 
				COLUMNS(
					money INT PATH \'$.金額\'
				)
			) AS jt'), 'deliveries.id', '=', 'deliveries.id')
			->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."注文先"."CustomerCode"\'))'), '=', 'customers.code')
			->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE)'), [$startDate, $endDate])
			->where(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."注文先"."CustomerCode"\'))'), '=', $customerCode)
			->groupBy('customer_code', 'customers.name')
			->orderBy('customer_code');
			*/

			// 得意先毎月計
			$query2 = DB::table('deliveries')
			->select(
				DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先".CustomerCode\')) AS customer_code'),
				'customers.name AS customer_name',
				DB::raw('SUM(JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) * sales_kind.surplus) AS total_money')
			)
			->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')
			->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE)'), [$startDate, $endDate])
			->where(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."請求先"."CustomerCode"\'))'), '=', $customerCode)
			->groupBy('customer_code', 'customers.name')
			->orderBy('customer_code');
	
			//logger('Dailyuri.report02 query2', ['sql' => $query2->toSql()]);

			$results = $query2->get();
			// 配列に変換
			$customer_sums = [];
			foreach($results as $result)
			{
				$customer_sums[] = (array)$result;
			}

			$dailyuri['customer_total'] = 0;
			foreach($customer_sums as $customer_sum)
			{
				$dailyuri['customer_total'] = $customer_sum['total_money'];
			}
			
		}

		/*
		$query3 = Delivery::query();
		$query3->select(
			DB::raw('SUM(jt.money) AS total_money')
		)
		->join(DB::raw('JSON_TABLE(deliveries.orders_del, \'$[*]\' 
			COLUMNS(
				money INT PATH \'$.金額\'
			)
		) AS jt'), 'deliveries.id', '=', 'deliveries.id')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE)'), [$startDate, $endDate]);
		*/

		// 全得意先月計
		$query3 = DB::table('deliveries')
		->select(
			DB::raw('SUM(JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$.金額\')) * sales_kind.surplus) AS total_money')
		)
		->join('delivery_details as jt', 'jt.delivery_number', '=', 'deliveries.DeliveryNumber')
		->leftJoin('sales_kind', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes, \'$."売上区分No"\'))'), '=', 'sales_kind.code')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deliveries.attributes, \'$."納品日"\')) AS DATE)'), [$startDate, $endDate]);

		logger('Dailyuri.report02 query3', ['sql' => $query3->toSql()]);

		$results = $query3->get();

		logger('Dailyuri.report02 query3-results', ['results' => $results]);

		// 配列に変換
		$monthtotals = [];
		foreach($results as $result)
		{
			$monthtotals = (array)$result;
		}

		logger('Dailyuri.report02 query3-results', ['monthtotals' => $monthtotals]);

		//$query4 = Deposit::query();
		//$query4->select(
		//	DB::raw('jt.deposit_type'),
		//	DB::raw('SUM(jt.money) AS total_money')
		//)
		//->join(DB::raw('JSON_TABLE(deposits.attributes, \'$.DepositDetailTable[*]\' 
		//	COLUMNS(
		//		money INT PATH \'$.Amount\',
		//		deposit_type PATH \'$.DepositType\'
		//	)
		//) AS jt'), function ($join) {
        	// join句に条件が必要であればここに追加
    	//})
		//->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$."DepositDate"\')) AS DATE)'), [$startDate, $endDate])
		//->groupBy('deposit_type')
		//->orderBy('deposit_type');
		/*
		$query4->select(
			'jt.deposit_type',
			DB::raw('SUM(jt.money) AS total_money')
		)
		->join(DB::raw('JSON_TABLE(deposits.attributes, \'$.DepositDetailTable[*]\' 
			COLUMNS(
				money INT PATH \'$.Amount\',
				deposit_type INT PATH \'$.DepositType\'
			)
		) AS jt'), function ($join) {
			$join->on('deposits.id', '=', 'deposits.id'); // Adjust the join condition as needed
		})
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS DATE)'), [$startDate, $endDate])
		->groupBy('jt.deposit_type')
		->orderBy('jt.deposit_type', 'asc');
		*/

		$query4 = Deposit::query();
		$query4->select(
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.DepositType\')) AS deposit_type'),
			DB::raw('SUM(JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.Amount\'))) AS total_money')
		)
		->join('deposit_details AS jt', 'jt.deposit_number', "=", 'deposits.DepositNumber')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS DATE)'), [$startDate, $endDate])
		->groupBy('deposit_type')
		->orderBy('deposit_type', 'asc');

		logger('Dailyuri.report02', ['sql' => $query4->toSql()]);

		$deposits = $query4->get();


		$viewdata = [
			'dailyuris' => $dailyuris,
			'deposits' => $deposits,
			'closingdate' => $formdata['closing_date'],
			'monthtotal' => $monthtotals['total_money']	
			//'closingdate' => date("Y-m-01", strtotime($formdata['closing_date']))
		];
		//logger('Dailyuri.report01', ['viewdata' => $viewdata]);

		return view("dailyuri." . $formdata['view'], compact('viewdata'));

	}

	// 一覧 帳票出力
	// view
	//   report03 : 入金一覧日報
	public function report03(Request $request)
	{		
		$formdata = [
			'closing_date' => request('closing_date'),
			'view' => request('view','report03'),
		];
		$version = [
		];
		//$this->OperationLog($version,['formdata' => $formdata]);
		logger('Dailyuri.report03', [
			'formdata' => $formdata,
		]);

		$startDate = date("Y-m-01", strtotime($formdata['closing_date']));
		$endDate = $formdata['closing_date'];

		$query = DailyLock::query();
		$query->select('LockDate');
		$query->whereRaw("LockKey = 'deposits'");
		$query->whereRaw("LockDate < '" . $endDate . "'");
		$query->orderBy("LockDate", 'DESC');
		$dailylock = $query->first();
		$startDate = $dailylock['LockDate'];

		/*
		$query = Deposit::query()		
		->select(
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS BillingDate'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositNumber\')) AS DepositNumber'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.CustomerCode\')) AS CustomerCode'),
			'customers.name AS customer_name',
			'jt.deposit_type',
			'deposit_kind.name AS deposit_kind_name',
			'jt.money',
			'jt.payment_date',
			'jt.tegata',
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.Remark\')) AS Remark')
		)
		->join(DB::raw('JSON_TABLE(deposits.attributes, \'$.DepositDetailTable[*]\' 
			COLUMNS(
				money INT PATH \'$.Amount\',
				deposit_type INT PATH \'$.DepositType\',
				payment_date CHAR(255) PATH \'$.PaymentDate\',
				tegata CHAR(255) PATH \'$.手形番号\'
			)
		) AS jt'), function ($join) {
			$join->on('deposits.id', '=', 'deposits.id'); // Adjust the join condition as needed
		})
		->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.CustomerCode\'))'), '=', 'customers.code')
		->leftJoin('deposit_kind', 'jt.deposit_type', '=', 'deposit_kind.code')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS DATE)'), [$startDate, $endDate])
		->orderBy('BillingDate')
		->orderBy('DepositNumber')
		->orderBy('jt.deposit_type');
		*/

		$query = Deposit::query()
		->select(
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS BillingDate'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositNumber\')) AS DepositNumber'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.CustomerCode\')) AS CustomerCode'),
			'customers.name AS customer_name',
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.DepositType\')) AS deposit_type'),
			'deposit_kind.name AS deposit_kind_name',
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.Amount\')) AS money'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.PaymentDate\')) AS payment_date'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.手形番号\')) AS tegata')
		)
		->join('deposit_details AS jt', 'jt.deposit_number', "=", 'deposits.DepositNumber')
		->leftJoin('customers', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.CustomerCode\'))'), '=', 'customers.code')
		->leftJoin('deposit_kind', DB::raw('JSON_UNQUOTE(JSON_EXTRACT(jt.attributes,\'$.DepositType\'))'), '=', 'deposit_kind.code')
		->whereRaw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS DATE) > \'' . $startDate . "'")
		->whereRaw('CAST(JSON_UNQUOTE(JSON_EXTRACT(deposits.attributes, \'$.DepositDate\')) AS DATE) <= \'' . $endDate . "'")
		->orderBy('BillingDate')
		->orderBy('DepositNumber')
		->orderBy('deposit_type');
	

		logger('Dailyuri.report03', ['sql' => $query->toSql()]);

		$deposits = $query->get();

		//$dailyuris = $query->get();
		//foreach($dailyuris as $dailyuri)
		//{
		//	$dailyuri['attributes'] = json_decode($dailyuri['attributes'],true);
		//	$dailyuri['orders'] = json_decode($dailyuri['orders_del'],true);
		//}

		//$dailyuris = [];
		$viewdata = [
			'deposits' => $deposits,
			'closingdate' => $formdata['closing_date']
		];
		//logger('Dailyuri.report01', ['viewdata' => $viewdata]);

		return view("dailyuri." . $formdata['view'], compact('viewdata'));

	}	

	// 一覧 帳票出力
	// view
	//   report04 : 仕入日時明細
	public function report04(Request $request)
	{		
		$formdata = [
			'closing_date' => request('closing_date'),
			'view' => request('view','report04'),
		];
		$version = [
		];
		//$this->OperationLog($version,['formdata' => $formdata]);
		logger('Dailyuri.report04', [
			'formdata' => $formdata,
		]);

		$startDate = date("Y-m-01", strtotime($formdata['closing_date']));
		$endDate = $formdata['closing_date'];
	

		$query = Supply::query()
		->select(
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."SupplyNumber"\')) AS SupplyNumber'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."仕入日"\')) AS 仕入日'),
			DB::raw('JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."仕入先"."名称"\')) AS 仕入先名'),
			'supply_kind.name AS 仕入区分名',
			'jt.商品No',
			'jt.商品名',
			'jt.寸法1',
			'jt.寸法2',
			'jt.総量',
			'jt.仕入数量',
			'jt.単位名',
			'jt.単価',
			'jt.仕入金額'
		)
		->join(DB::raw('JSON_TABLE(supplies.details, \'$[*]\' 
			COLUMNS(
				商品No VARCHAR(255) PATH \'$."商品No"\',
				仕入区分No INT PATH \'$."仕入区分No"\',
				商品名 VARCHAR(255) PATH \'$."商品名"\',
				寸法1 INT PATH \'$."寸法1"\',
				寸法2 INT PATH \'$."寸法2"\',
				総量 INT PATH \'$."総量"\',
				仕入数量 INT PATH \'$."仕入数量"\',
				単位名 VARCHAR(255) PATH \'$."単位名"\',
				単価 INT PATH \'$."単価"\',
				仕入金額 INT PATH \'$."仕入金額"\'
			)
		) AS jt'), function ($join) {
			$join->on(DB::raw('1'), DB::raw('1')); // ダミーの条件。必要に応じて修正してください。
		})
		->leftJoin('supply_kind', 'jt.仕入区分No', '=', 'supply_kind.code')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."仕入日"\')) AS DATE)'), [$startDate, $endDate])
		->orderBy(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."仕入日"\'))'))
		->orderBy('SupplyNumber')
		->orderBy('jt.仕入区分No');



		logger('Dailyuri.report04', ['sql' => $query->toSql()]);

		$supply = $query->get();

		//$dailyuris = $query->get();
		//foreach($dailyuris as $dailyuri)
		//{
		//	$dailyuri['attributes'] = json_decode($dailyuri['attributes'],true);
		//	$dailyuri['orders'] = json_decode($dailyuri['orders_del'],true);
		//}

		//$dailyuris = [];
		$viewdata = [
			'dailyshis' => $supply,
			'closingdate' => $formdata['closing_date']
		];
		//logger('Dailyuri.report01', ['viewdata' => $viewdata]);

		return view("dailyuri." . $formdata['view'], compact('viewdata'));

	}	

	// 一覧 帳票出力
	// view
	//   report05 : 仕入・支払日報
	public function report05(Request $request)
	{		
		$formdata = [
			'closing_date' => request('closing_date'),
			'view' => request('view','report05'),
		];
		$version = [
		];
		//$this->OperationLog($version,['formdata' => $formdata]);
		logger('Dailyuri.report05', [
			'formdata' => $formdata,
		]);

		$startDate = date("Y-m-01", strtotime($formdata['closing_date']));
		$endDate = $formdata['closing_date'];
	

		$query = Supply::query()
		->select(
			'products.product_type AS 商品分類No',
			'product_types.name AS 商品分類名',
			DB::raw('SUM(jt.仕入金額) AS 仕入金額合計'),
			DB::raw('SUM(jt.仕入数量) AS 仕入数量合計')
		)
		->join(DB::raw('JSON_TABLE(supplies.details, \'$[*]\' 
			COLUMNS(
				商品No VARCHAR(255) PATH \'$."商品No"\',
				仕入数量 INT PATH \'$."仕入数量"\',
				仕入金額 INT PATH \'$."仕入金額"\'
			)
		) AS jt'), function ($join) {
			$join->on(DB::raw('1'), DB::raw('1')); // ダミーの条件。必要に応じて修正してください。
		})
		->leftJoin('products', 'jt.商品No', '=', 'products.code')
		->leftJoin('product_types', 'products.product_type', '=', 'product_types.code')
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(supplies.attributes, \'$."仕入日"\')) AS DATE)'), [$startDate, $endDate])
		->groupBy('products.product_type', 'product_types.name')
		->orderBy('products.product_type');
	
		logger('Dailyuri.report05', ['sql' => $query->toSql()]);

		$supply = $query->get();


		$query = Payment::query()
		->select(
			'jt.payment_type',
			DB::raw('SUM(jt.money) AS total_money')
		)
		->join(DB::raw('JSON_TABLE(payments.attributes, \'$.PaymentDetailTable[*]\' 
			COLUMNS(
				money INT PATH \'$.Amount\',
				payment_type INT PATH \'$.PaymentType\'
			)
		) AS jt'), function ($join) {
			$join->on(DB::raw('1'), DB::raw('1')); // Adjust the join condition as needed
		})
		->whereBetween(DB::raw('CAST(JSON_UNQUOTE(JSON_EXTRACT(payments.attributes, \'$."PaymentDate"\')) AS DATE)'), [$startDate, $endDate])
		->groupBy('jt.payment_type')
		->orderBy('jt.payment_type', 'asc');
	
	    $payments = $query->get();

		//$dailyuris = $query->get();
		//foreach($dailyuris as $dailyuri)
		//{
		//	$dailyuri['attributes'] = json_decode($dailyuri['attributes'],true);
		//	$dailyuri['orders'] = json_decode($dailyuri['orders_del'],true);
		//}

		//$dailyuris = [];
		$viewdata = [
			'supply' => $supply,
			'payments' => $payments,
			'closingdate' => $formdata['closing_date']
		];
		//logger('Dailyuri.report01', ['viewdata' => $viewdata]);

		return view("dailyuri." . $formdata['view'], compact('viewdata'));

	}	



}