<?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\Customer;
use App\Deposit;
use App\DepositDetail;
use App\DepositKind;
use App\Bank;
use App\Delivery;
use App\DeliveryDetail;
use App\SalesKind;
use App\System;
use App\Invoice;
use App\Monthly;
use App\Unit;

class MonthlyController extends Controller
{

	public function summary(Request $request)
	{
		$formdata = [
			'Action' => $request->input('Action', null),
			'Year' => $request->input('Year', null), // 月次更新（年） : Action='Update'のときのみ有効
			'Month' => $request->input('Month', null),// 月次更新（月） : Action='Update'のときのみ有効
		];
		$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]);

		if ($formdata['Action'] == 'Update')
		{
			// 月次更新

			$this->updateSummary($formdata['Year'], $formdata['Month']);
		}
		elseif ($formdata['Action'] == 'Cancel')
		{
			// 月次更新 取消
			//		月次更新した記録を消すだけ

			$query = Monthly::query();
			$query->where("year" , $formdata['Year']);
			$query->where("month" , $formdata['Month']);
			$query->delete();
		}
		

		// 最終 月次更新月
		$monthly = null;
	
		$query = Monthly::query();
		$query->selectRaw("year");
		$query->selectRaw("month");
		$query->orderBy("year", 'DESC');
		$query->orderBy("month", 'DESC');
		
		$monthly = $query->first();
		logger('Monthly.summary', ['monthly' => $monthly]);

		$resp = [
			'monthly' => $monthly,
		];
		return response()->json($resp);
	}

	public function bill00(Request $request)
	{
		//logger('Monthly.bill00', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);

		$query = Deposit::query();
		$query->selectRaw("DepositNumber");
		$query->selectRaw("deposits.attributes");
		$query->where('deposits.deleted', null);
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("details.attributes as detail");
		$query->leftJoin('customers AS billing', "deposits.BillingCustomerCode", '=', 'billing.code');
		$query->selectRaw("billing.name as CustomerName");

		$query->whereRaw("details.attributes->>'$.DepositType' = 2"); // 2:手形
		
		$KeyColumn = "CAST(deposits.attributes->'$.DepositDate' AS DATE)";

		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->orderByRaw($KeyColumn);
		$query->orderBy('billing.code');
			
		logger('Monthly.bill00', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		$query = Bank::query();
		$query->selectRaw("code");
		$query->selectRaw("bankcode");
		$query->selectRaw("name");
		$query->orderBy("code");
		$query->distinct();
		$rows = $query->get();
		$banks = [];
		foreach($rows as $row) {
			$banks[$row['code']] = [
				'bankcode' => $row['bankcode'],
				'name' => $row['name'],
			];
		}

		foreach($deposits as &$deposit)
		{
			$deposit['attributes'] = json_decode($deposit['attributes'],true);
			$deposit['detail'] = json_decode($deposit['detail'],true);
			$deposit['bank'] = $banks[$deposit['detail']['銀行番号']];
		}

		logger('Monthly.bill00', ['deposits' => $deposits]);

		
		$viewdata = [
			'formdata' => $formdata,
			'deposits' => $deposits,
		];

		logger('Monthly.bill00', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.bill00" ,compact('viewdata'));
	}

	public function bill01(Request $request)
	{
		//logger('Monthly.bill01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);


		// 翌月落ちる手形の一覧を取得
		$nextMonthFirstDay = date('Y-m-01', strtotime($formdata['date_from'] . ' + 1 month' ) );
		$nextMonthLastDay  = date('Y-m-t' , strtotime($formdata['date_from'] . ' + 1 month' ) );

		$query = Deposit::query();
		$query->selectRaw("DepositNumber");
		$query->selectRaw("deposits.attributes");
		$query->where('deposits.deleted', null);
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("details.attributes as detail");
		$query->leftJoin('customers AS billing', "deposits.BillingCustomerCode", '=', 'billing.code');
		$query->selectRaw("billing.name as CustomerName");

		$query->whereRaw("details.attributes->>'$.DepositType' = 2"); // 2:手形
		$KeyColumn = "CAST(details.attributes->'$.PaymentDate' AS DATE)";
		$query->whereRaw("{$KeyColumn} BETWEEN '{$nextMonthFirstDay}' AND '{$nextMonthLastDay}'");
		$query->orderByRaw($KeyColumn);
		$query->orderBy('billing.code');
			
		logger('Monthly.bill01', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		$query = Bank::query();
		$query->selectRaw("code");
		$query->selectRaw("bankcode");
		$query->selectRaw("name");
		$query->orderBy("code");
		$query->distinct();
		$rows = $query->get();
		$banks = [];
		foreach($rows as $row) {
			$banks[$row['code']] = [
				'bankcode' => $row['bankcode'],
				'name' => $row['name'],
			];
		}

		foreach($deposits as &$deposit)
		{
			$deposit['attributes'] = json_decode($deposit['attributes'],true);
			$deposit['detail'] = json_decode($deposit['detail'],true);
			$deposit['bank'] = $banks[$deposit['detail']['銀行番号']];
		}

		logger('Monthly.bill01', ['deposits' => $deposits]);

		
		$viewdata = [
			'formdata' => $formdata,
			'deposits' => $deposits,
		];

		logger('Monthly.bill01', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.bill01" ,compact('viewdata'));
	}


	public function bill02(Request $request)
	{
		//logger('Monthly.bill02', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);

		$query = Deposit::query();
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("DATE_FORMAT(CAST(details.attributes->'$.PaymentDate' AS DATE),'%Y-%m') AS YM");
		$query->selectRaw("SUM(details.attributes->'$.Amount') AS Price");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->leftJoin('customers AS billing', "deposits.BillingCustomerCode", '=', 'billing.code');
		$query->where('deposits.deleted', null);
		$query->whereRaw("details.attributes->>'$.DepositType' = 2"); // 2:手形
		$KeyColumn = "CAST(details.attributes->'$.PaymentDate' AS DATE)";
		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");

		$query->orderBy("CustomerCode");
		$query->orderBy("CustomerName");
		$query->orderBy("YM");

		$query->groupBy("CustomerCode");
		$query->groupBy("CustomerName");
		$query->groupBy("YM");

		logger('Monthly.bill02', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		// 5か月分 と 6か月以降 の容れ物を作る
		$customerRowDefault = [
			'CustomerCode' => '',
			'CustomerName' => '',
			'TotalPrice' => 0,
			'Prices' => [],
		];
		for($i = 0; $i < 5; ++$i)
		{
			$YM = date('Y-m', strtotime($formdata['date_from'] . " {$i} month" ));
			$customerRowDefault['Prices'][$YM] = 0;
		}
		$customerRowDefault['Prices']['After'] = 0;

		logger('Monthly.bill02', ['customerRowDefault' => $customerRowDefault]);

		$customers = [];
		foreach($deposits as $deposit)
		{
			$customerCode = $deposit['CustomerCode'];
			if (!array_key_exists($customerCode, $customers))
			{
				$customers[$customerCode] = array_merge(
					$customerRowDefault,
					[
						'CustomerCode' => $customerCode,
						'CustomerName' => $deposit['CustomerName'],
					]
				);
			}

			if (array_key_exists($deposit['YM'], $customers[$customerCode]['Prices']))
			{
				$customers[$customerCode]['Prices'][$deposit['YM']] += $deposit['Price'];
			}
			else
			{
				$customers[$customerCode]['Prices']['After'] += $deposit['Price'];
			}
			$customers[$customerCode]['TotalPrice'] += $deposit['Price'];
		}

		//logger('Monthly.bill02', ['deposits' => $deposits]);
		logger('Monthly.bill02', ['customers' => $customers]);

		
		$viewdata = [
			'formdata' => $formdata,
			'header' => $customerRowDefault,
			'customers' => $customers,
		];

		logger('Monthly.bill02', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.bill02" ,compact('viewdata'));
	}


	// 得意先別売掛残高順位表
	//    指定月の請求額
	//    指定月の売掛金合計
	//    指定月以降の入金合計
	//    期首からの請求額合計
	public function receivable01(Request $request)
	{
		//logger('Monthly.receivable01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
			'limit'=> request('limit'),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		if (empty($formdata['limit']))
		{
			// 最低 上位50位まで出力
			$formdata['limit'] = 50;
		}
		$this->OperationLog($version,['formdata' => $formdata]);

		$system = new System();

		// 指定月の請求額
		$query = Invoice::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(TRIM(BOTH '\"' FROM invoices.attributes->'$.\"当月請求\".\"請求日\"') AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"請求額\"' as 当月請求額");
		//logger('Monthly.receivable01', ['sql' => $query->toSql()]);
		$thisMonthInvoices = $query->get();
		//logger('Monthly.receivable01', ['thisMonthInvoices' => $thisMonthInvoices]);
		
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		// 指定月の売掛金合計
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("detail.attributes->>'$.Amount' AS Price");
		$query->selectRaw("detail.attributes->>'$.\"売上区分No\"' AS SalesKind");
		//$query->selectRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' AS ClosedFlag");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' = 6");
		//logger('Monthly.receivable01', ['sql' => $query->toSql()]);

		$deliveries = $query->get();
		//logger('Monthly.receivable01', ['deliveries' => $deliveries]);

		// 指定期間以降の入金合計（売掛で入金済の額）
		$query = Deposit::query();
		$query->whereRaw("'{$formdata['date_to']}' < CAST(deposits.attributes->>'$.DepositDate' AS DATE)");
		$query->leftJoin('deposit_details AS detail', 'DepositNumber', '=', 'detail.deposit_number');
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("detail.attributes->>'$.Amount' AS Price");
		$query->selectRaw("detail.attributes->>'$.DepositType' AS DepositKind");
		//$query->selectRaw("deposits.attributes->'$.ClosedFlag' AS ClosedFlag");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' = 6");
		//logger('Monthly.receivable01', ['sql' => $query->toSql()]);

		$deposits = $query->get();
		//logger('Monthly.receivable01', ['deposits' => $deposits]);

		// 期首からの請求額合計
		$year = intval(date('Y', strtotime($formdata['date_from'])));
		$month = intval(date('m', strtotime($formdata['date_from'])));
		if ($month < $system->TermBeginingMonth())
		{
			$year--;
		}
		$termBeginDate = "{$year}-{$system->TermBeginingMonth()}-01";
		logger('Monthly.receivable01', ['termBeginDate' => $termBeginDate]);

		$query = Invoice::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(TRIM(BOTH '\"' FROM invoices.attributes->'$.\"当月請求\".\"請求日\"') AS DATE) BETWEEN '{$termBeginDate}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("SUM(invoices.attributes->'$.\"当月請求\".\"買上額\"') as 売上合計");
		$query->groupByRaw("billing_customer_code");
		$query->groupByRaw("billing_customer_name");
		$query->orderByRaw("billing_customer_code");
		//logger('Monthly.receivable01', ['sql' => $query->toSql()]);
		$salesPriceTotals = $query->get();
		//logger('Monthly.receivable01', ['salesPriceTotal' => $salesPriceTotal]);

		// 集計
		$customerRowDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'指定月請求額' => 0,
			'指定月売掛金合計' => 0,
			'指定月以降入金合計' => 0,
			'今期請求額累計' => 0,
		];
		$customers = [];
		foreach($thisMonthInvoices as $invoice)
		{
			$customer_code = $invoice['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $invoice['billing_customer_name'],
					]
				);
				$customers[$customer_code]['指定月請求額'] += $invoice['当月請求額'];
			}
		}
		foreach($deliveries as $delivery)
		{
			$customer_code = $delivery['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $delivery['billing_customer_name'],
					]
				);
			}
			if (array_key_exists($delivery['SalesKind'],$salesKinds))
			{
				if ($salesKinds[$delivery['SalesKind']]['surplus'] > 0)
				{
					// 売掛金合計
					$customers[$customer_code]['指定月売掛金合計'] += ($delivery['Price'] * (1 + $system->TaxRate()) );
				}
				else
				{
					// 返品・値引き
					$customers[$customer_code]['指定月売掛金合計'] -= ($delivery['Price'] * (1 + $system->TaxRate()) );
				}
			}
			else
			{
				logger('Monthly.receivable01', ['LINE'=>__LINE__,'delivery' => $delivery]);
			}
		}
		foreach($deposits as $deposit)
		{
			$customer_code = $deposit['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $deposit['billing_customer_name'],
					]
				);
			}
			// 入金合計
			$customers[$customer_code]['指定月以降入金合計'] += $deposit['Price'];
		}
		foreach($salesPriceTotals as $salesPriceTotal)
		{
			$customer_code = $salesPriceTotal['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $salesPriceTotal['billing_customer_name'],
					]
				);
			}
			// 入金合計
			$customers[$customer_code]['今期請求額累計'] += $salesPriceTotal['売上合計'];
		}
		foreach($customers as &$customer)
		{
			if ($customer['指定月売掛金合計'] > $customer['指定月以降入金合計'])
			{
				$customer['売掛残高'] = $customer['指定月売掛金合計'] - $customer['指定月以降入金合計'];
			}
			else
			{
				$customer['売掛残高'] = 0;
			}
		}

		// 売掛残高でソートして順位付け
		usort($customers, function ($left, $right)
		{
			if ($left['売掛残高'] != $right['売掛残高']) return ($left['売掛残高'] < $right['売掛残高']) ? 1 : -1;
			if ($left['得意先NO'] != $right['得意先NO']) return ($left['得意先NO'] - $right['得意先NO']);
			return 0;
		});
		// 順位
		$rankedCustomers = [];
		$rank = 1;
		$count = 1;
		$prev = 0;
		logger('Monthly.receivable01', ['LINE'=>__LINE__,'formdata' => $formdata]);
		foreach($customers as &$customer)
		{
			if ($prev != $customer['売掛残高'])
			{
				$rank = $count;
			}
			$customer['順位'] = $rank;
			$count++;
			$prev = $customer['売掛残高'];
			if ($rank <= $formdata['limit'])
			{
				$rankedCustomers[] = $customer;
			}
		}



		$viewdata = [
			'formdata' => $formdata,
			'header' => $customerRowDefault,
			'customers' => $rankedCustomers,
		];

		//logger('Monthly.bill02', ['viewdata' => $viewdata]);
		return view("monthly.receivable01" ,compact('viewdata'));
	}


	// 売掛高順位表（決算）
	//    期末までの納品額合計
	//    期末までの入金合計
	public function receivable02(Request $request)
	{
		//logger('Monthly.receivable02', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
			'limit'=> request('limit'),
		];
		$version = [
			'client_version' => $request->input('version', null),
			'page_created' => $request->input('server_clock', null),
			'page_loaded_' => $request->input('client_clock', null),
		];
		if (empty($formdata['limit']))
		{
			// 最低 上位25位まで出力
			$formdata['limit'] = 25;
		}
		$this->OperationLog($version,['formdata' => $formdata]);

		$system = new System();

		// 指定月までの納品額
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) <= '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("CONCAT(billing.attributes->>'$.address1',billing.attributes->>'$.address2') as billing_customer_address");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"合計金額\"' + deliveries.attributes->>'$.Summary.\"消費税額\"') AS Price");
		//$query->selectRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' AS ClosedFlag");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 1");
		$query->groupBy('billing_customer_code');
		$query->groupBy('billing_customer_name');
		$query->groupBy('billing_customer_address');
		$query->orderBy('billing_customer_code');
		//logger('Monthly.receivable02', ['sql' => $query->toSql()]);

		$deliveries = $query->get();
		
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		// 指定月までの入金額
		$query = Deposit::query();
		$query->whereRaw("CAST(deposits.attributes->>'$.DepositDate' AS DATE) <= '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("CONCAT(billing.attributes->>'$.address1',billing.attributes->>'$.address2') as billing_customer_address");
		$query->selectRaw("SUM(deposits.attributes->>'$.DepositAmount') AS Price");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 1");
		$query->groupBy('billing_customer_code');
		$query->groupBy('billing_customer_name');
		$query->groupBy('billing_customer_address');
		$query->orderBy('billing_customer_code');
		//logger('Monthly.receivable02', ['sql' => $query->toSql()]);
		$deposits = $query->get();
		//logger('Monthly.receivable02', ['deposits' => $deposits]);

		// 集計
		$customerRowDefault = [
			'順位' => 0,
			'得意先NO' => '',
			'得意先名' => '',
			'得意先住所' => '',
			'請求額' => 0,
			'入金額' => 0,
			'売掛残' => 0,
		];

		$customers = [];
		foreach($deliveries as $delivery)
		{
			$customer_code = $delivery['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $delivery['billing_customer_name'],
						'得意先住所' => str_replace("null", "", $delivery['billing_customer_address']),
					]
				);
				$customers[$customer_code]['請求額'] += $delivery['Price'];
			}
		}
		foreach($deposits as $deposit)
		{
			$customer_code = $deposit['billing_customer_code'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $deposit['billing_customer_name'],
						'得意先住所' => str_replace("null", "", $delivery['billing_customer_address']),
					]
				);
			}
			// 入金合計
			$customers[$customer_code]['入金額'] += $deposit['Price'];
		}
		foreach($customers as &$customer)
		{
			$customer['売掛残'] = $customer['請求額'] - $customer['入金額'];
		}

		// 売掛残高でソートして順位付け
		usort($customers, function ($left, $right)
		{
			if ($left['売掛残'] != $right['売掛残']) return ($left['売掛残'] < $right['売掛残']) ? 1 : -1;
			if ($left['得意先NO'] != $right['得意先NO']) return ($left['得意先NO'] - $right['得意先NO']);
			return 0;
		});
		// 順位
		$rankedCustomers = [];
		$rank = 1;
		$count = 1;
		$prev = 0;
		logger('Monthly.receivable02', ['LINE'=>__LINE__,'formdata' => $formdata]);
		foreach($customers as &$customer)
		{
			if ($prev != $customer['売掛残'])
			{
				$rank = $count;
			}
			$customer['順位'] = $rank;
			$count++;
			$prev = $customer['売掛残'];
			if ($rank <= $formdata['limit'])
			{
				$rankedCustomers[] = $customer;
			}
		}



		$viewdata = [
			'formdata' => $formdata,
			'header' => $customerRowDefault,
			'customers' => $rankedCustomers,
		];

		//logger('Monthly.receivable02', ['viewdata' => $viewdata]);
		return view("monthly.receivable02" ,compact('viewdata'));
	}


	// 得意先別売上管理表
	public function sales01(Request $request)
	{
		//logger('Monthly.sales01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);

		// 全得意先
		$query = Customer::query();
		$query->selectRaw("code as 得意先NO");
		$query->selectRaw("name as 得意先名");
		$query->selectRaw("0 as 前月繰越額");
		$query->selectRaw("0 as 現金・振込");
		$query->selectRaw("0 as 手形入金");
		$query->selectRaw("0 as 相殺その他");
		$query->selectRaw("0 as 入金合計");
		$query->selectRaw("0 as 入金値引");
		$query->selectRaw("0 as 差引繰越");
		$query->selectRaw("0 as 当月売上");
		$query->selectRaw("0 as 消費税");
		$query->selectRaw("0 as 当月繰越");
		$work = $query->get();
		$customers = [];
		$header = [];
		if (count($work))
		{
			$header = $work[0];
		}
		foreach($work as $customer)
		{
			$customers[$customer['得意先NO']] = $customer;
		}

		// 指定月の請求額
		$query = Invoice::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(invoices.attributes->>'$.\"当月請求\".\"請求日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->selectRaw("BillingCustomerCode as billing_customer_code");
		$query->selectRaw("invoices.attributes->'$.\"前回請求\".\"残額\"' as 前回残額");
		$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"買上額\"' as 当月買上額");
		$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"返品値引額\"' as 当月返品値引額");
		$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"消費税額\"' as 消費税額");
		$query->selectRaw("invoices.details as details");
		//logger('Monthly.sales01', ['sql' => $query->toSql()]);
		$invoices = $query->get();
		//logger('Monthly.sales01', ['invoices' => $invoices]);
		
		// 入金区分
		$depositKinds = DepositKind::GetKeyValueArray();

		// 集計
		$system = new System();
		foreach($invoices as $invoice)
		{
			$billing_customer_code = $invoice['billing_customer_code'];

			foreach($invoice['details'] as $detail)
			{
				if (empty($detail['入金額']))
				{
					continue;
				}
				switch ($detail['入金区分'])
				{
					case 1: // 現金
					case 4: // 振込
					case 5: // 振込手数料
					case 9: // 振込消費税
							$customers[$billing_customer_code]['現金・振込'] += $detail['入金額'];
						break;
					case 2: // 手形・でんさい
					case 3: // 小切手
						$customers[$billing_customer_code]['手形入金'] += $detail['入金額'];
						break;
					case 6: // 相殺
					case 8: // その他
						$customers[$billing_customer_code]['相殺その他'] += $detail['入金額'];
						break;
					case 7: // 入金値引き
						$customers[$billing_customer_code]['入金値引'] += $detail['入金額'];
						break;
					default:
						break;
				}
			}

			$customers[$billing_customer_code]['前月繰越額'] += $invoice['前回残額'];
			$customers[$billing_customer_code]['当月売上'] += ($invoice['当月買上額'] - $invoice['当月返品値引額']);
			$customers[$billing_customer_code]['消費税'] += $invoice['消費税額'];
		}
		foreach($customers as &$customer)
		{
			$customer['入金合計'] = $customer['現金・振込'] + $customer['手形入金'] + $customer['相殺その他'];
			$customer['差引繰越'] = $customer['前月繰越額'] - $customer['入金合計'];
			$customer['当月繰越'] = $customer['差引繰越'] + $customer['当月売上'] + $customer['消費税'];
		}

		ksort($customers);

		//logger('Monthly.sales01', ['customers' => $customers]);
		
		$viewdata = [
			'formdata' => $formdata,
			'header' => $header,
			'customers' => $customers,
		];

		logger('Monthly.sales01', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.sales01" ,compact('viewdata'));
	}

	// 得意先別売上順位表
	public function sales02(Request $request)
	{
		//logger('Monthly.sales02', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);


		// 指定月の請求額
		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"合計金額\"') as 当月売上");
		$query->groupBy('billing_customer_code');
		$query->groupBy('billing_customer_name');
		$query->orderBy('当月売上', 'DESC');
		logger('Monthly.sales02', ['sql' => $query->toSql()]);
		$targetMonthInvoices = $query->get();
		logger('Monthly.sales02', ['targetMonthInvoices' => $targetMonthInvoices]);

		// 指定前月の請求額
		$prevMonthFirstDate = date('Y-m-01', strtotime($formdata['date_from'] . ' - 1 month'));
		$prevMonthLastDate  = date('Y-m-t' , strtotime($formdata['date_from'] . ' - 1 month'));
		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$prevMonthFirstDate}' AND '{$prevMonthLastDate}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"合計金額\"') as 前月売上");
		$query->groupBy('billing_customer_code');
		$query->groupBy('billing_customer_name');
		$query->orderBy('前月売上', 'DESC');
		$query->orderBy('billing_customer_code');
		//logger('Monthly.sales02', ['sql' => $query->toSql()]);
		$lastMonthInvoices = $query->get();
		//logger('Monthly.sales02', ['lastMonthInvoices' => $lastMonthInvoices]);

		// 指定前年同月の請求額
		$sameMonthprevYearFirstDate = date('Y-m-01', strtotime($formdata['date_from'] . ' - 1 year'));
		$sameMonthprevYearLastDate  = date('Y-m-t' , strtotime($formdata['date_from'] . ' - 1 year'));
		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$sameMonthprevYearFirstDate}' AND '{$sameMonthprevYearLastDate}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"合計金額\"') as 前年同月売上");
		$query->groupBy('billing_customer_code');
		$query->groupBy('billing_customer_name');
		$query->orderBy('前年同月売上', 'DESC');
		//logger('Monthly.sales02', ['sql' => $query->toSql()]);
		$sameMonthLastYearInvoices = $query->get();
		//logger('Monthly.sales02', ['sameMonthLastYearInvoices' => $sameMonthLastYearInvoices]);
		

		$customerDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'当月売上' => 0,
			'順位' => 0,
			'前月売上' => 0,
			'前年同月売上' => 0,
		];


		// 集計
		$customers = [];
		$count = 0; $rank = 0; $prevValue = PHP_INT_MAX;
		foreach($targetMonthInvoices as $invoice)
		{
			$billing_customer_code = $invoice['billing_customer_code'];
			if (!array_key_exists($billing_customer_code, $customers))
			{
				if (($invoice['当月売上']))
				{
					++$count;
					if ($invoice['当月売上'] < $prevValue)
					{
						$rank = $count;
						$prevValue = $invoice['当月売上'];
					}
					$customers[$billing_customer_code] = array_merge(
						$customerDefault,
						[
							'得意先NO' => $billing_customer_code,
							'得意先名' => $invoice['billing_customer_name'],
						]
					);
					$customers[$billing_customer_code]['当月売上'] = $invoice['当月売上'];
					$customers[$billing_customer_code]['順位'] = $rank;
				}
			}

		}
		foreach($lastMonthInvoices as $invoice)
		{
			$billing_customer_code = $invoice['billing_customer_code'];
			if (array_key_exists($billing_customer_code, $customers))
			{
				$customers[$billing_customer_code]['前月売上'] = $invoice['前月売上'];
			}
		}
		foreach($sameMonthLastYearInvoices as $invoice)
		{
			$billing_customer_code = $invoice['billing_customer_code'];
			if (array_key_exists($billing_customer_code, $customers))
			{
				$customers[$billing_customer_code]['前年同月売上'] = $invoice['前年同月売上'];
			}

		}
		foreach($customers as &$customer)
		{
		}

		//ksort($customers);

		//logger('Monthly.sales02', ['customers' => $customers]);
		
		$viewdata = [
			'formdata' => $formdata,
			'header' => $customerDefault,
			'customers' => $customers,
		];

		//logger('Monthly.sales02', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.sales02" ,compact('viewdata'));
	}


	// 得意先別月間推移表
	public function sales03(Request $request)
	{
		//logger('Monthly.sales03', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);


		// 指定月の請求額
		$MonthField = "MONTH(CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE))";
		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as 得意先NO");
		$query->selectRaw("billing.name as 得意先名");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 11 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計11月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 12 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計12月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 01 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計01月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 02 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計02月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 03 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計03月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 04 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計04月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 05 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計05月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 06 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計06月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 07 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計07月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 08 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計08月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 09 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計09月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 10 THEN deliveries.attributes->'$.Summary.\"合計金額\"' ELSE 0 END) as 計10月");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"合計金額\"') as 年間売上");
		$query->groupBy('得意先NO');
		$query->groupBy('得意先名');
		$query->orderBy('得意先NO', 'ASC');
		logger('Monthly.sales03', ['sql' => $query->toSql()]);
		$customers = $query->get();
		logger('Monthly.sales03', ['customers' => $customers]);

		//ksort($customers);

		//logger('Monthly.sales03', ['customers' => $customers]);
		
		$viewdata = [
			'formdata' => $formdata,
			'customers' => $customers,
		];

		//logger('Monthly.sales03', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.sales03" ,compact('viewdata'));
	}


	// 入金一覧月報
	public function deposit01(Request $request)
	{
		//logger('Monthly.deposit01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);


		// 指定月の入金額
		$KeyColumn = "CAST(deposits.attributes->'$.DepositDate' AS DATE)";
		$query = Deposit::query();
		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->selectRaw("{$KeyColumn} AS DepositDate"); // 入金日
		$query->selectRaw("DepositNumber"); // 入金番号
		$query->selectRaw("deposits.attributes->>'$.Remark' AS Remark"); // 備考
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("(details.attributes->>'$.DepositType') AS DepositType");
		$query->selectRaw("(details.attributes->>'$.Amount') AS Price");
		$query->selectRaw("(CAST(details.attributes->'$.PaymentDate' AS DATE)) AS 手形支払日");//手形の支払日
		$query->selectRaw("(details.attributes->>'$.\"手形番号\"') AS 手形番号");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->leftJoin('customers AS billing', "deposits.BillingCustomerCode", '=', 'billing.code');
		$query->where('deposits.deleted', null);
		$query->orderBy("DepositDate");
		$query->orderBy("DepositNumber");
		$query->orderBy("details.attributes->'$.Number'");
		logger('Monthly.deposit01', ['sql' => $query->toSql()]);
		$deposits = $query->get();
		//logger('Monthly.deposit01', ['deposits' => $deposits]);

		// 入金区分
		$depositKinds = DepositKind::GetKeyValueArray();


		//logger('Monthly.deposit01', ['customers' => $customers]);
		
		$viewdata = [
			'formdata' => $formdata,
			'depositKinds' => $depositKinds,
			'deposits' => $deposits,
		];

		//logger('Monthly.deposit01', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.deposit01" ,compact('viewdata'));
	}

	// 得意先元帳
	public function customer01(Request $request)
	{
		//logger('Monthly.customer01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);

		// 指定月の前までの納品合計
		$KeyColumn = "CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE)";
		$query = Delivery::query();
		$query->whereRaw("{$KeyColumn} < '{$formdata['date_from']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"合計金額\"') AS CarriedSalesPrice");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"消費税額\"') AS CarriedTaxPrice");
		$query->selectRaw("CONCAT(billing.code, ':0000-00-00') AS SortKey");
		//$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' = 6");
		$query->orderBy("SortKey");
		$query->groupBy("CustomerCode");
		$query->groupBy("CustomerName");
		$query->groupBy("SortKey");
		//logger('Monthly.customer01', ['sql' => $query->toSql()]);
		$sumDeliveries = $query->get();
		
		
		// 指定月の納品一覧
		$query = Delivery::query();
		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("{$KeyColumn} AS TheDate");
		$query->selectRaw("detail.attributes->>'$.\"商品名\"' AS ProductName");
		$query->selectRaw("detail.attributes->>'$.Remark' AS Remark");
		$query->selectRaw("detail.attributes->>'$.Amount' AS Amount");
		$query->selectRaw("detail.attributes->>'$.\"商品単位\"' AS UnitCode");
		$query->selectRaw("detail.attributes->>'$.\"単価\"' AS UnitPrice");
		$query->selectRaw("detail.attributes->>'$.\"金額\"' AS SalesPrice");
		$query->selectRaw("detail.attributes->>'$.\"消費税額\"' AS TaxPrice");
		$query->selectRaw("0 AS DepositType");
		$query->selectRaw("0 AS IncomePrice");
		$query->selectRaw("'' AS BillNumber");
		$query->selectRaw("CONCAT(billing.code, ':', {$KeyColumn}, ':0', DeliveryNumber, ':', detail.attributes->>'$.Number') AS SortKey");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' IN ( 6 )");
		$query->orderBy("SortKey");

		logger('Monthly.customer01', ['sql' => $query->toSql()]);
		$deliveries = $query->get();


		// 指定月の前までの入金合計
		$KeyColumn = "CAST(deposits.attributes->'$.DepositDate' AS DATE)";
		$query = Deposit::query();
		$query->whereRaw("{$KeyColumn} < '{$formdata['date_from']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("SUM(deposits.attributes->>'$.DepositAmount') AS CarriedIncomePrice");
		$query->selectRaw("CONCAT(billing.code, ':0000-00-00') AS SortKey");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' = 6");
		$query->orderBy("SortKey");
		$query->groupBy("CustomerCode");
		$query->groupBy("CustomerName");
		$query->groupBy("SortKey");
		//logger('Monthly.customer01', ['sql' => $query->toSql()]);
		$sumDeposits = $query->get();

		// 指定月の入金一覧
		$query = Deposit::query();
		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', "deposits.BillingCustomerCode", '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("{$KeyColumn} AS TheDate"); // 入金日
		$query->selectRaw("'' AS ProductName"); // あとで 「入金区分 + 支払期日」に書き換える
		$query->selectRaw("(details.attributes->>'$.DepositType') AS DepositType");
		$query->selectRaw("(details.attributes->>'$.PaymentDate') AS PaymentDate");
		$query->selectRaw("(details.attributes->>'$.\"手形番号\"') AS BillNumber");
		$query->selectRaw("'' AS Remark");
		$query->selectRaw("0 AS Amount");
		$query->selectRaw("'' AS UnitCode");
		$query->selectRaw("0 AS UnitPrice");
		$query->selectRaw("0 AS SalesPrice");
		$query->selectRaw("(details.attributes->'$.Amount') AS IncomePrice");
		$query->selectRaw("CONCAT(billing.code, ':', {$KeyColumn}, ':1;', details.attributes->'$.Number') AS SortKey");
		$query->where('deposits.deleted', null);
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' = 6");
		$query->orderBy("SortKey");
		//logger('Monthly.customer01', ['sql' => $query->toSql()]);
		$deposits = $query->get();
		//logger('Monthly.customer01', ['deposits' => $deposits]);

		// 入金区分
		$depositKinds = DepositKind::GetKeyValueArray();
		// 単位
		$units = Unit::GetKeyValueArray();
		//logger('Monthly.customer01', ['units' => $units]);


		$summaries = [];
		foreach($sumDeliveries as $sumDelivery)
		{
			$summary = [
				'CustomerCode' => $sumDelivery['CustomerCode'],
				'CustomerName' => $sumDelivery['CustomerName'],
				'CarriedSalesPrice' => $sumDelivery['CarriedSalesPrice'],
				'CarriedTaxPrice' => $sumDelivery['CarriedTaxPrice'],
				'CarriedIncomePrice' => 0,
			];

			$summaries[$sumDelivery['CustomerCode']] = $summary;
		}
		foreach($sumDeposits as $sumDeposit)
		{
			$summary = [
				'CustomerCode' => $sumDeposit['CustomerCode'],
				'CustomerName' => $sumDeposit['CustomerName'],
				'CarriedIncomePrice' => $sumDeposit['CarriedIncomePrice'],
			];
			if (array_key_exists($sumDeposit['CustomerCode'], $summaries))
			{
				$summaries[$sumDeposit['CustomerCode']] = 
					array_merge($summaries[$sumDeposit['CustomerCode']],$summary);
			}
			else
			{
				$summaries[$sumDeposit['CustomerCode']] = $summary;
			}
		}
		//logger('Monthly.customer01', ['summaries' => $summaries]);

		$records = [];
		foreach($deliveries as $delivery)
		{
			$unitName = '';
			if (array_key_exists($delivery['UnitCode'], $units))
			{
				$unitName = $units[$delivery['UnitCode']]['name'];
			}
			$delivery['UnitName'] = $unitName;
			
			$records[$delivery['SortKey']] = $delivery;
		}
		foreach($deposits as $deposit)
		{
			$productName = '';
			if (array_key_exists($deposit['DepositType'], $depositKinds))
			{
				$productName = '☆' . $depositKinds[$deposit['DepositType']]['name'];
				if (strtotime($deposit['PaymentDate']))
				{
					$productName .= ' ' . $deposit['PaymentDate'];
				}
			}
			$deposit['ProductName'] = $productName;
			$records[$deposit['SortKey']] = $deposit;
		}

		ksort($records);



		//logger('Monthly.customer01', ['customers' => $customers]);
		
		$viewdata = [
			'formdata' => $formdata,
			'depositKinds' => $depositKinds,
			'summaries' => $summaries,
			'records' => $records,
		];

		//logger('Monthly.customer01', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.customer01" ,compact('viewdata'));
	}


	// 機械稼働状況表
	public function equipment01(Request $request)
	{
		//logger('Monthly.equipment01', [__FILE__ => __LINE__]);
		$formdata = [
			'date_from' => request('date_from'),
			'date_to' => request('date_to'),
		];
		$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]);


		// 指定月の請求額
		$MonthField = "MONTH(CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE))";
		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("detail.attributes->>'$.\"機械No\"' AS 機械NO");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 11 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計11月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 12 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計12月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 01 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計01月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 02 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計02月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 03 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計03月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 04 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計04月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 05 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計05月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 06 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計06月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 07 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計07月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 08 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計08月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 09 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計09月");
		$query->selectRaw("SUM(CASE WHEN {$MonthField} = 10 THEN detail.attributes->'$.\"金額\"' ELSE 0 END) as 計10月");
		$query->selectRaw("SUM(detail.attributes->'$.\"金額\"') as 年間売上");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' IN ( 6 )");
		$query->groupBy('機械NO');
		//$query->whereRaw("LENGTH(detail.attributes->>'$.\"機械No\"') > 0");
		$query->orderBy('機械NO', 'ASC');
		//logger('Monthly.equipment01', ['sql' => $query->toSql()]);
		$equipments = $query->get();
		logger('Monthly.equipment01', ['equipments' => $equipments]);

		//ksort($equipments);

		//logger('Monthly.equipment01', ['equipments' => $equipments]);
		
		$viewdata = [
			'formdata' => $formdata,
			'equipments' => $equipments,
		];

		//logger('Monthly.equipment01', ['viewdata' => $viewdata]);
		//return response()->json($formdata);
		return view("monthly.equipment01" ,compact('viewdata'));
	}


	// 月次集計
	//    指定月の 売上 税額 入金 割引 繰越 を計算
	private function updateSummary(int $year, int $month)
	{
		logger('Monthly.updateSummary', ['year' => $year,'month' => $month,]);

		$targetMonthFirstDay = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
		$targetMonthLastDay  = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
	
		// 前月までの売上
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) < '{$targetMonthFirstDay}'");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 1");
		$query->selectRaw("BillingCustomerCode AS CustomerCode");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"合計金額\"') AS 売上");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"消費税額\"') AS 税額");
		$query->groupBy('CustomerCode');
		$query->orderBy('CustomerCode');
		logger('Monthly.updateSummary', ['sql' => $query->toSql()]);
		$deliveries = $query->get();

		// 前月までの入金
		$query = Deposit::query();
		$query->whereRaw("CAST(deposits.attributes->>'$.DepositDate' AS DATE) < '{$targetMonthFirstDay}'");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 1");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("BillingCustomerCode AS CustomerCode");
		$query->selectRaw("SUM(CASE WHEN details.attributes->>'$.DepositType'= 7 THEN 0 ELSE details.attributes->>'$.Amount' END) as 入金");
		$query->selectRaw("SUM(CASE WHEN details.attributes->>'$.DepositType'= 7 THEN details.attributes->>'$.Amount' ELSE 0 END) as 値引");
		$query->groupBy('CustomerCode');
		$query->orderBy('CustomerCode');
		logger('Monthly.updateSummary', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		// 集計
		$pastRowDefault = [
			'CustomerCode' => '',
			'売上' => 0,
			'税額' => 0,
			'入金' => 0,
			'値引' => 0,
			'繰越' => 0,
		];

		$pastMonthly = [];
		foreach($deliveries as $delivery)
		{
			$customer_code = $delivery['CustomerCode'];
			if (!array_key_exists($customer_code, $pastMonthly))
			{
				$pastMonthly[$customer_code] = array_merge(
					$pastRowDefault,
					[
						'CustomerCode' => $customer_code,
					]
				);
			}
			$pastMonthly[$customer_code]['売上'] += $delivery['売上'];
			$pastMonthly[$customer_code]['税額'] += $delivery['税額'];
			$pastMonthly[$customer_code]['繰越'] += ($delivery['売上'] + $delivery['税額']);
		}
		foreach($deposits as $deposit)
		{
			$customer_code = $deposit['CustomerCode'];
			if (!array_key_exists($customer_code, $pastMonthly))
			{
				$pastMonthly[$customer_code] = array_merge(
					$pastRowDefault,
					[
						'CustomerCode' => $customer_code,
					]
				);
			}
			$pastMonthly[$customer_code]['入金'] += $deposit['入金'];
			$pastMonthly[$customer_code]['値引'] += $deposit['値引'];
			$pastMonthly[$customer_code]['繰越'] -= ($deposit['入金'] + $deposit['値引']);
		}


		// 指定月の納品額
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE) BETWEEN '{$targetMonthFirstDay}' AND '{$targetMonthLastDay}'");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 1");
		$query->selectRaw("BillingCustomerCode AS CustomerCode");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"合計金額\"') AS 売上");
		$query->selectRaw("SUM(deliveries.attributes->>'$.Summary.\"消費税額\"') AS 税額");
		$query->groupBy('CustomerCode');
		$query->orderBy('CustomerCode');
		logger('Monthly.updateSummary', ['sql' => $query->toSql()]);
		$deliveries = $query->get();

		// 指定月の入金額
		$query = Deposit::query();
		$query->whereRaw("CAST(deposits.attributes->>'$.DepositDate' AS DATE) BETWEEN '{$targetMonthFirstDay}' AND '{$targetMonthLastDay}'");
		$query->selectRaw("BillingCustomerCode AS CustomerCode");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 1");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("SUM(CASE WHEN details.attributes->>'$.DepositType'= 7 THEN 0 ELSE details.attributes->>'$.Amount' END) as 入金");
		$query->selectRaw("SUM(CASE WHEN details.attributes->>'$.DepositType'= 7 THEN details.attributes->>'$.Amount' ELSE 0 END) as 値引");
		$query->groupBy('CustomerCode');
		$query->orderBy('CustomerCode');
		logger('Monthly.updateSummary', ['sql' => $query->toSql()]);
		$deposits = $query->get();
		//logger('Monthly.updateSummary', ['deposits' => $deposits]);

		$targetMonthlySummary = [];
		foreach($pastMonthly as $past)
		{
			$customer_code = $past['CustomerCode'];
			if (!array_key_exists($customer_code, $targetMonthlySummary))
			{
				$targetMonthlySummary[$customer_code] = array_merge(
					$pastRowDefault,
					[
						'CustomerCode' => $customer_code,
					]
				);
				$targetMonthlySummary[$customer_code]['繰越'] = $past['繰越'];
			}
		}
		foreach($deliveries as $delivery)
		{
			$customer_code = $delivery['CustomerCode'];
			if (!array_key_exists($customer_code, $targetMonthlySummary))
			{
				$targetMonthlySummary[$customer_code] = array_merge(
					$pastRowDefault,
					[
						'CustomerCode' => $customer_code,
					]
				);
			}
			$targetMonthlySummary[$customer_code]['売上'] = $delivery['売上'];
			$targetMonthlySummary[$customer_code]['税額'] = $delivery['税額'];
			$targetMonthlySummary[$customer_code]['繰越'] += ($delivery['売上'] + $delivery['税額']);
		}
		foreach($deposits as $deposit)
		{
			$customer_code = $deposit['CustomerCode'];
			if (!array_key_exists($customer_code, $targetMonthlySummary))
			{
				$targetMonthlySummary[$customer_code] = array_merge(
					$pastRowDefault,
					[
						'CustomerCode' => $customer_code,
					]
				);
			}
			$targetMonthlySummary[$customer_code]['入金'] += $deposit['入金'];
			$targetMonthlySummary[$customer_code]['値引'] += $deposit['値引'];
			$targetMonthlySummary[$customer_code]['繰越'] -= ($deposit['入金'] + $deposit['値引']);
		}

		// まず同月のデータがあれば、削除
		$query = Monthly::query();
		$query->where("year" , $year);
		$query->where("month" , $month);
		$query->delete();

		// 集計結果を登録
		foreach($targetMonthlySummary as $customerSummary)
		{
			$monthly = new Monthly;
			$monthly->year = $year;
			$monthly->month = $month;
			$monthly->customer = $customerSummary['CustomerCode'];
			$monthly->attributes = [
				'売上' => $customerSummary['売上'],
				'税額' => $customerSummary['税額'],
				'入金' => $customerSummary['入金'],
				'値引' => $customerSummary['値引'],
				'繰越' => $customerSummary['繰越'],
			];
			$monthly->updated = date('Y-m-d H:i:s');
			$monthly->save();
		}

		return;
	}


}