<?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\CarryOver;
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 = CarryOver::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);
			if (array_key_exists($deposit['detail']['銀行番号'], $banks))
			{
				$deposit['bank'] = $banks[$deposit['detail']['銀行番号']];
			}
			else
			{
				$deposit['bank'] = [
					'bankcode' => '',
					'name' => '',
				];
			}
		}

		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(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 当月請求額");
		$query->selectRaw("invoices.attributes->'$.\"当月請求\".\"消費税額\"' as 当月消費税額");
		$query->whereNotNull('billing.name');
		//logger('Monthly.receivable01', ['sql' => $query->toSql()]);
		$thisMonthInvoices = $query->get();
		//logger('Monthly.receivable01', ['thisMonthInvoices' => $thisMonthInvoices]);
		
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		// 指定月よりも前の 最新の繰越
		$ym = strtotime($formdata['date_from']);
		$targetMonth = [
			'Year' => date('Y', $ym),
			'Month' => date('m', $ym)
		];
		$latestCarryOverIDs = DB::select(
			"SELECT id FROM carryovers AS latest " .
			"WHERE (latest.year*100+latest.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			"AND NOT EXISTS (SELECT id FROM carryovers AS past WHERE past.customer = latest.customer " .
				"AND (latest.year*100+latest.month) < (past.year*100+past.month) " .
				"AND (past.year*100+past.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			") " .
			"ORDER BY latest.customer, latest.year, latest.month"
		);
		$ids = [];
		foreach($latestCarryOverIDs as $latestCarryOver)
		{
			$ids[] = $latestCarryOver->id;
		}
		$query = CarryOver::query();
		$query->leftJoin('customers AS billing', 'carryovers.customer', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("carryovers.attributes");
		$query->whereIn('carryovers.id', $ids);
		$query->whereNotNull('billing.name');
		$lastMonthCarryOvers = $query->get();		
		//logger('Monthly.receivable01', ['LINE'=>__LINE__,'lastMonthCarryOvers' => $lastMonthCarryOvers]);



		// 指定月の売掛金合計
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.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->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("detail.attributes->>'$.\"金額\"' AS 金額");
		$query->selectRaw("detail.attributes->>'$.\"消費税額\"' AS 消費税額");
		$query->selectRaw("detail.attributes->>'$.\"売上区分No\"' AS SalesKind");
		//$query->selectRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' AS ClosedFlag");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->whereNotNull('billing.name');
		logger('Monthly.receivable01', ['sql' => $query->toSql()]);

		$deliveries = $query->get();
		//logger('Monthly.receivable01', ['deliveries' => $deliveries]);

		// 指定月の入金合計（売掛で入金済の額）
		$query = Deposit::query();
		$query->whereRaw("CAST(deposits.attributes->>'$.DepositDate' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$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 入金金額");
		$query->selectRaw("detail.attributes->>'$.DepositType' AS DepositKind");
		//$query->selectRaw("deposits.attributes->'$.ClosedFlag' AS ClosedFlag");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 0");
		$query->whereNotNull('billing.name');
		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 = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$termBeginDate}' AND '{$formdata['date_to']}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		//$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("MONTH(CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE)) as month");
		$query->selectRaw("billing.code as billing_customer_code");
		$query->selectRaw("billing.name as billing_customer_name");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"合計金額\"') as 売上合計");
		$query->selectRaw("SUM(deliveries.attributes->'$.Summary.\"消費税額\"') as 消費税額合計");
		$query->groupByRaw("billing_customer_code");
		$query->groupByRaw("billing_customer_name");
		$query->groupByRaw("month");
		$query->orderByRaw("billing_customer_code");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->whereNotNull('billing.name');

		logger('Monthly.receivable01', ['sql' => $query->toSql()]);
		$salesPriceTotals = $query->get();
		//logger('Monthly.receivable01', ['salesPriceTotal' => $salesPriceTotal]);

		// 集計
		$customerRowDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'前月請求繰越' => 0,
			'指定月請求額' => 0,
			'指定月消費税額' => 0,
			'指定月売掛金合計' => 0,
			'指定月税込売掛金合計' => 0,
			'指定月入金合計' => 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['当月請求額'];
				$customers[$customer_code]['指定月消費税額'] += $invoice['当月消費税額'];
			}
		}

		foreach($lastMonthCarryOvers as &$lastMonthCarryOver)
		{
			$customer_code = $lastMonthCarryOver['CustomerCode'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $lastMonthCarryOver['CustomerName'],
					]
				);
			}
			$customers[$customer_code]['前月請求繰越'] = $lastMonthCarryOver['attributes']['CarryOver'];
		}		
		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['金額']);
				}
				else
				{
					// 返品・値引き
					$customers[$customer_code]['指定月売掛金合計'] -= ($delivery['金額']);
				}
			}
			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['入金金額'];
		}
		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['売上合計'];
			//$customers[$customer_code]['今期消費税額累計'] += $salesPriceTotal['消費税額合計'];
			$customers[$customer_code]['今期消費税額累計'] += floor($salesPriceTotal['売上合計'] * 0.1);
		}
		foreach($customers as &$customer)
		{
			if ($customer['指定月売掛金合計'] > 0)
			{
				$customer['指定月税込売掛金合計'] = floor($customer['指定月売掛金合計'] * (1 + $system->TaxRate()));
			}
			else
			{
				$abs = - $customer['指定月売掛金合計'];
				$customer['指定月税込売掛金合計'] = - floor($abs * (1 + $system->TaxRate()));
			}
			$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.receivable01', ['LINE'=>__LINE__,'formdata' => $formdata]);
		//logger('Monthly.receivable01', ['LINE'=>__LINE__,'customers' => $customers]);
		foreach($customers as &$customer)
		{
			if (empty($customer['得意先NO']))
			{
				continue;
			}
			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();
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		// 指定月よりも前の 最新の繰越
		$ym = strtotime($formdata['date_from']);
		$targetMonth = [
			'Year' => date('Y', $ym),
			'Month' => date('m', $ym)
		];
		$latestCarryOverIDs = DB::select(
			"SELECT id FROM carryovers AS latest " .
			"WHERE (latest.year*100+latest.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			"AND NOT EXISTS (SELECT id FROM carryovers AS past WHERE past.customer = latest.customer " .
				"AND (latest.year*100+latest.month) < (past.year*100+past.month) " .
				"AND (past.year*100+past.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			") " .
			"ORDER BY latest.customer, latest.year, latest.month"
		);
		$ids = [];
		foreach($latestCarryOverIDs as $latestCarryOver)
		{
			$ids[] = $latestCarryOver->id;
		}
		$query = CarryOver::query();
		$query->leftJoin('customers AS billing', 'carryovers.customer', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("CONCAT(billing.attributes->>'$.address1',billing.attributes->>'$.address2') as CustomerAddress");
		$query->selectRaw("carryovers.attributes");
		$query->whereIn('carryovers.id', $ids);
		$query->whereNotNull('billing.name');
		$lastMonthCarryOvers = $query->get();		
		//logger('Monthly.receivable02', ['LINE'=>__LINE__,'lastMonthCarryOvers' => $lastMonthCarryOvers]);


		// 指定月の売掛金合計
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) 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->selectRaw("CONCAT(billing.attributes->>'$.address1',billing.attributes->>'$.address2') as CustomerAddress");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("detail.attributes->>'$.\"金額\"' AS 金額");
		$query->selectRaw("detail.attributes->>'$.\"消費税額\"' AS 消費税額");
		$query->selectRaw("detail.attributes->>'$.\"売上区分No\"' AS SalesKind");
		//$query->selectRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' AS ClosedFlag");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->whereNotNull('billing.name');
		//logger('Monthly.receivable02', ['sql' => $query->toSql()]);
		$deliveries = $query->get();
		//logger('Monthly.receivable02', ['deliveries' => $deliveries]);

		// 指定月の入金合計（売掛で入金済の額）
		$query = Deposit::query();
		$query->whereRaw("CAST(deposits.attributes->>'$.DepositDate' AS DATE) BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->leftJoin('deposit_details AS detail', 'DepositNumber', '=', 'detail.deposit_number');
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("CONCAT(billing.attributes->>'$.address1',billing.attributes->>'$.address2') as CustomerAddress");
		$query->selectRaw("detail.attributes->>'$.Amount' AS 入金金額");
		$query->selectRaw("detail.attributes->>'$.DepositType' AS DepositKind");
		//$query->selectRaw("deposits.attributes->'$.ClosedFlag' AS ClosedFlag");
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 0");
		$query->whereNotNull('billing.name');
		//logger('Monthly.receivable02', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		// 集計
		$customerRowDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'得意先住所' => '',
			'前月請求繰越' => 0,
			'指定月売掛金合計' => 0,
			'指定月税込売掛金合計' => 0,
			'指定月入金合計' => 0,
			'売掛残高' => 0,
		];
		$customers = [];

		foreach($lastMonthCarryOvers as &$lastMonthCarryOver)
		{
			$customer_code = $lastMonthCarryOver['CustomerCode'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $lastMonthCarryOver['CustomerName'],
						'得意先住所' => str_replace('null','',$lastMonthCarryOver['CustomerAddress']),
					]
				);
			}
			$customers[$customer_code]['前月請求繰越'] = $lastMonthCarryOver['attributes']['CarryOver'];
		}		
		foreach($deliveries as $delivery)
		{
			$customer_code = $delivery['CustomerCode'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $delivery['CustomerName'],
						'得意先住所' => str_replace('null','',$delivery['CustomerAddress']),
					]
				);
			}
			if (array_key_exists($delivery['SalesKind'],$salesKinds))
			{
				if ($salesKinds[$delivery['SalesKind']]['surplus'] > 0)
				{
					// 売掛金合計
					$customers[$customer_code]['指定月売掛金合計'] += ($delivery['金額']);
				}
				else
				{
					// 返品・値引き
					$customers[$customer_code]['指定月売掛金合計'] -= ($delivery['金額']);
				}
			}
		}
		foreach($deposits as $deposit)
		{
			$customer_code = $deposit['CustomerCode'];
			if (!array_key_exists($customer_code, $customers))
			{
				$customers[$customer_code] = array_merge(
					$customerRowDefault,
					[
						'得意先NO' => $customer_code,
						'得意先名' => $deposit['CustomerName'],
						'得意先住所' => str_replace('null','',$deposit['CustomerAddress']),
					]
				);
			}
			// 入金合計
			$customers[$customer_code]['指定月入金合計'] += $deposit['入金金額'];
		}
		foreach($customers as &$customer)
		{
			if ($customer['指定月売掛金合計'] > 0)
			{
				$customer['指定月税込売掛金合計'] = floor($customer['指定月売掛金合計'] * (1 + $system->TaxRate()));
			}
			else
			{
				$abs = - $customer['指定月売掛金合計'];
				$customer['指定月税込売掛金合計'] = - floor($abs * (1 + $system->TaxRate()));
			}
			$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.receivable01', ['LINE'=>__LINE__,'formdata' => $formdata]);
		//logger('Monthly.receivable01', ['LINE'=>__LINE__,'customers' => $customers]);
		$otherTotal = $customerRowDefault;
		$customerRowDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'得意先住所' => '',
			'前月請求繰越' => 0,
			'指定月売掛金合計' => 0,
			'指定月税込売掛金合計' => 0,
			'指定月入金合計' => 0,
			'売掛残高' => 0,
		];
		$othersTotal = array_merge(
			$customerRowDefault,
			[
				'得意先NO' => 'Others',
				'得意先名' => '諸口',
				'得意先住所' => '',
			]
		);

		foreach($customers as &$customer)
		{
			if (empty($customer['得意先NO']))
			{
				continue;
			}
			if ($customer['得意先NO'] >= '00711')
			{
				$othersTotal['売掛残高'] += $customer['売掛残高'];
				continue;
			}
			if ($prev != $customer['売掛残高'])
			{
				$rank = $count;
			}
			$customer['順位'] = $rank;
			$count++;
			$prev = $customer['売掛残高'];
			if ($rank < $formdata['limit'])
			{
				$rankedCustomers[] = $customer;
			}
			else
			{
				$othersTotal['売掛残高'] += $customer['売掛残高'];
			}
		}

		$viewdata = [
			'formdata' => $formdata,
			'header' => $customerRowDefault,
			'customers' => $rankedCustomers,
			'others' => $othersTotal,
		];

		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]);

		// 全得意先（ 00711 までしか使っていないらしい ）
		$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 当月繰越");
		$query->whereRaw("code < '00712'");
		$work = $query->get();
		$customers = [];
		$header = [];
		if (count($work))
		{
			$header = $work[0];
		}
		foreach($work as $customer)
		{
			$customers[$customer['得意先NO']] = $customer;
		}

		// 指定月よりも前の 最新の繰越
		$ym = strtotime($formdata['date_from']);
		$targetMonth = [
			'Year' => date('Y', $ym),
			'Month' => date('m', $ym)
		];
		$latestCarryOverIDs = DB::select(
			"SELECT id FROM carryovers AS latest " .
			"WHERE (latest.year*100+latest.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			"AND NOT EXISTS (SELECT id FROM carryovers AS past WHERE past.customer = latest.customer " .
				"AND (latest.year*100+latest.month) < (past.year*100+past.month) " .
				"AND (past.year*100+past.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			") " .
			"ORDER BY latest.customer, latest.year, latest.month"
		);
		// SELECT * FROM `carryovers` as latest WHERE (year * 100 + month ) < 202409
		// AND NOT EXISTS (SELECT id FROM carryovers WHERE customer = latest.customer AND (year * 100 + month ) < 202409 AND (year*100+month) > (latest.year*100+latest.month))
		// ORDER BY customer , year DESC , month DESC;
		$ids = [];
		foreach($latestCarryOverIDs as $latestCarryOver)
		{
			$ids[] = $latestCarryOver->id;
		}
		//logger('CarryOver.calculate', ['ids' => $ids]);

		$query = CarryOver::query();
		$query->leftJoin('customers AS billing', 'carryovers.customer', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("carryovers.attributes");
		$query->whereIn('carryovers.id', $ids);
		$lastMonthCarryOvers = $query->get();		

		// 指定月売上と税額
		$ym = strtotime($formdata['date_from']);
		$targetMonth = [
			'Year' => date('Y', $ym),
			'Month' => date('m', $ym)
		];
		$query = Delivery::query();
		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$formdata['date_from']}' AND  '{$formdata['date_to']}'");
		$query->selectRaw("BillingCustomerCode AS CustomerCode");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->selectRaw("detail.attributes->>'$.\"金額\"' AS 金額");
		$query->selectRaw("detail.attributes->>'$.\"売上区分No\"' AS SalesKind");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		logger('Monthly.sales01', ['sql' => $query->toSql()]);
		$deliveries = $query->get();

		// 指定月入金
		$KeyColumn = "CAST(deposits.attributes->'$.DepositDate' AS DATE)";
		$query = Deposit::query();
		$query->whereRaw("{$KeyColumn} BETWEEN '{$formdata['date_from']}' AND '{$formdata['date_to']}'");
		$query->selectRaw("deposits.BillingCustomerCode as CustomerCode");
		$query->leftJoin('deposit_details AS details', 'DepositNumber', '=', 'details.deposit_number');
		$query->selectRaw("(details.attributes->>'$.DepositType') AS DepositType");
		$query->selectRaw("(details.attributes->>'$.Amount') AS IncomePrice");
		$query->where('deposits.deleted', null);
		$query->whereRaw("deposits.attributes->>'$.ClosedFlag' > 0");
		logger('Monthly.sales01', ['sql' => $query->toSql()]);
		$deposits = $query->get();

		$system = new System();
		// 入金区分
		$depositKinds = DepositKind::GetKeyValueArray();
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		// 集計
		foreach($lastMonthCarryOvers as $lastMonthCarryOver)
		{
			$customerCode = $lastMonthCarryOver['CustomerCode'];
			if (array_key_exists($customerCode, $customers))
			{
				$work = $customers[$customerCode];
				$work['前月繰越額'] = $lastMonthCarryOver['attributes']['CarryOver'];
				$customers[$customerCode] = $work;
			}
		}

		foreach($deliveries as $delivery)
		{
			$customerCode = $delivery['CustomerCode'];
			$surplus = 1;
			if (array_key_exists($delivery['SalesKind'],$salesKinds))
			{
				if ($salesKinds[$delivery['SalesKind']]['surplus'] > 0)
				{
					$surplus = 1;
				}
				else
				{
					// 返品・値引き
					$surplus = -1;
				}
			}

			if (array_key_exists($customerCode, $customers))
			{
				$work = $customers[$customerCode];
				if ($surplus > 0)
				{
					$work['当月売上'] += $delivery['金額'];
				}
				else
				{
					$work['当月売上'] -= $delivery['金額'];
				}
				$work['消費税'] = floor(($work['当月売上']) * $system->TaxRate());
				$customers[$customerCode] = $work;
			}
		}




		foreach($deposits as $deposit)
		{
			$customerCode = $deposit['CustomerCode'];
			if (empty($deposit['IncomePrice']))
			{
				continue;
			}
			if (array_key_exists($customerCode, $customers))
			{
				$work = $customers[$customerCode];
				switch ($deposit['DepositType'])
				{
					case 1: // 現金
					case 3: // 小切手
					case 4: // 振込
					case 5: // 振込手数料
					case 9: // 振込消費税
						$work['現金・振込'] += $deposit['IncomePrice'];
						break;
					case 2: // 手形・でんさい
						$work['手形入金'] += $deposit['IncomePrice'];
						break;
					case 6: // 相殺
					case 8: // その他
						$work['相殺その他'] += $deposit['IncomePrice'];
						break;
					case 7: // 入金値引き
						$work['入金値引'] += $deposit['IncomePrice'];
						break;
					default:
						break;
				}
				$customers[$customerCode] = $work;
			}
		}

		foreach($customers as &$customer)
		{
			$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]);

		// 指定月売上
		$dateFrom = date('Y-m-01', strtotime($formdata['date_from']));
		$dateTo = date('Y-m-t', strtotime($formdata['date_from']));

		$query = Delivery::query();
		$salesField = "(detail.attributes->>'$.\"金額\"' * sales_kind.surplus)";

		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$dateFrom}' AND  '{$dateTo}'");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->leftJoin('sales_kind', 'detail.SalesKind', '=', 'sales_kind.code');
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("SUM({$salesField}) AS Sales");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->groupBy('CustomerCode');
		$query->groupBy('CustomerName');
		$query->orderBy('Sales', 'DESC');
		$query->orderBy('CustomerCode', 'ASC');
		logger('Monthly.sales02', ['sql' => $query->toSql()]);

		//$deliveries = $query->get();
		$targetMonthSales = $query->get();
		
		$system = new System();

//		uasort($targetMonthSales, function($a, $b){
//			return
//				($a['Sales'] != $b['Sales']) ? ($a['Sales'] < $b['Sales']) :
//				strcmp($a['CustomerCode'],$b['CustomerCode']);
//		});

		// 前月売上
		$dateFrom = date('Y-m-01', strtotime($formdata['date_from'] . ' -1 month'));
		$dateTo = date('Y-m-t', strtotime($formdata['date_from'] . ' -1 month'));

		$query = Delivery::query();
		$salesField = "(detail.attributes->>'$.\"金額\"' * sales_kind.surplus)";

		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$dateFrom}' AND  '{$dateTo}'");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->leftJoin('sales_kind', 'detail.SalesKind', '=', 'sales_kind.code');
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("SUM({$salesField}) AS Sales");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->groupBy('CustomerCode');
		$query->groupBy('CustomerName');
		logger('Monthly.sales02', ['sql' => $query->toSql()]);
		$lastMonthSales = $query->get();

		// 前年売上
		$dateFrom = date('Y-m-01', strtotime($formdata['date_from'] . ' -1 year'));
		$dateTo = date('Y-m-t', strtotime($formdata['date_from'] . ' -1 year'));

		$query = Delivery::query();
		$salesField = "(detail.attributes->>'$.\"金額\"' * sales_kind.surplus)";

		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$dateFrom}' AND  '{$dateTo}'");
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->leftJoin('sales_kind', 'detail.SalesKind', '=', 'sales_kind.code');
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("SUM({$salesField}) AS Sales");
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->groupBy('CustomerCode');
		$query->groupBy('CustomerName');
		logger('Monthly.sales02', ['sql' => $query->toSql()]);
		$lastYearSales = $query->get();

		$customerDefault = [
			'得意先NO' => '',
			'得意先名' => '',
			'当月売上' => 0,
			'順位' => 0,
			'前月売上' => 0,
			'前年同月売上' => 0,
		];

		// 集計
		$customers = [];
		$count = 0; $rank = 0; $prevValue = PHP_INT_MAX;
		foreach($targetMonthSales as $sale)
		{
			$customerCode = $sale['CustomerCode'];
			if (!array_key_exists($customerCode, $customers))
			{
				if ($sale['Sales'])
				{
					++$count;
					if ($sale['Sales'] < $prevValue)
					{
						$rank = $count;
						$prevValue = $sale['Sales'];
					}
				}
				$work = array_merge(
					$customerDefault,
					[
						'得意先NO' => $sale['CustomerCode'],
						'得意先名' => $sale['CustomerName'],
						'当月売上' => $sale['Sales'],
						'順位' => $rank,
					]
				);
				if ($rank <= 50) // 50位まで印字
				{
					$customers[$customerCode] = $work;
				}
			}			
		}
		foreach($lastMonthSales as $sale)
		{
			$customerCode = $sale['CustomerCode'];
			if (array_key_exists($customerCode, $customers))
			{
				$customers[$customerCode]['前月売上'] = $sale['Sales'];
			}
		}
		foreach($lastYearSales as $sale)
		{
			$customerCode = $sale['CustomerCode'];
			if (array_key_exists($customerCode, $customers))
			{
				$customers[$customerCode]['前年同月売上'] = $sale['Sales'];
			}
		}
		
		$viewdata = [
			'formdata' => $formdata,
			'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]);


		// 指定月の請求額（ 00711 までしか使っていないらしい ）
		$MonthField = "MONTH(CAST(deliveries.attributes->>'$.Summary.\"請求日\"' AS DATE))";
		$dateFrom = date('Y-m-01', strtotime($formdata['date_from']));
		$dateTo = date('Y-m-t', strtotime($formdata['date_to']));

		// 指定された期間の月数
		$months = abs((date('Y', strtotime($formdata['date_to'])) * 12 + date('m', strtotime($formdata['date_to']))) -
		 (date('Y', strtotime($formdata['date_from'])) * 12 + date('m', strtotime($formdata['date_from'])))) + 1;
		//logger('Monthly.sales03', ['months' => $months]);

		$query = Delivery::query();
		//$query->selectRaw("invoices.attributes");
		$salesField = "(detail.attributes->>'$.\"金額\"' * sales_kind.surplus)";
		$monthField = "MONTH(CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE))";
		$query->whereRaw("CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE) BETWEEN '{$dateFrom}' AND '{$dateTo}'");
		$query->leftJoin('customers AS billing', 'BillingCustomerCode', '=', 'billing.code');
		$query->leftJoin('delivery_details AS detail', 'DeliveryNumber', '=', 'detail.delivery_number');
		$query->leftJoin('sales_kind', 'detail.SalesKind', '=', 'sales_kind.code');
		$query->selectRaw("billing.code as 得意先NO");
		$query->selectRaw("billing.name as 得意先名");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 11 THEN {$salesField} ELSE 0 END) as 計11月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 12 THEN {$salesField} ELSE 0 END) as 計12月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 01 THEN {$salesField} ELSE 0 END) as 計01月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 02 THEN {$salesField} ELSE 0 END) as 計02月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 03 THEN {$salesField} ELSE 0 END) as 計03月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 04 THEN {$salesField} ELSE 0 END) as 計04月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 05 THEN {$salesField} ELSE 0 END) as 計05月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 06 THEN {$salesField} ELSE 0 END) as 計06月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 07 THEN {$salesField} ELSE 0 END) as 計07月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 08 THEN {$salesField} ELSE 0 END) as 計08月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 09 THEN {$salesField} ELSE 0 END) as 計09月");
		$query->selectRaw("SUM(CASE WHEN {$monthField} = 10 THEN {$salesField} ELSE 0 END) as 計10月");
		$query->selectRaw("SUM({$salesField}) as 年間売上");
		$query->whereRaw("billing.code < '00712'");
		$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,
			'months' => $months,
		];

		//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->selectRaw("(deposits.attributes->>'$.Application1') AS 適用1");
		$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]);


		// 前月繰越
		$tm = strtotime($formdata['date_from']);
		$targetMonth = [
			'Year' => date('Y', $tm),
			'Month' => date('m', $tm)
		];
		$latestCarryOverIDs = DB::select(
			"SELECT id FROM carryovers AS latest " .
			"WHERE (latest.year*100+latest.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			"AND NOT EXISTS (SELECT id FROM carryovers AS past WHERE past.customer = latest.customer " .
				"AND (latest.year*100+latest.month) < (past.year*100+past.month) " .
				"AND (past.year*100+past.month) < {$targetMonth['Year']}*100+{$targetMonth['Month']} " .
			") " .
			"ORDER BY latest.customer, latest.year, latest.month"
		);
		$ids = [];
		foreach($latestCarryOverIDs as $latestCarryOver)
		{
			$ids[] = $latestCarryOver->id;
		}
		$query = CarryOver::query();
		$query->leftJoin('customers AS billing', 'carryovers.customer', '=', 'billing.code');
		$query->selectRaw("billing.code as CustomerCode");
		$query->selectRaw("billing.name as CustomerName");
		$query->selectRaw("carryovers.attributes");
		$query->whereIn('carryovers.id', $ids);
		$query->whereNotNull('billing.name');
		logger('Monthly.customer01', ['targetMonth' => $targetMonth]);
		$lastMonthCarryOvers = $query->get();
		//logger('Monthly.customer01', ['lastMonthCarryOvers' => $lastMonthCarryOvers]);

		// 指定月の納品一覧
		$KeyColumn = "CAST(deliveries.attributes->>'$.\"納品日\"' AS DATE)";
		$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->selectRaw("(deliveries.attributes->>'$.\"請求先\".\"名称\"') as OtherDeliveryName");
		$query->selectRaw("'' as OtherDepositName");
		$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->>'$.\"売上区分No\"' AS SalesKind");
		$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', detail.id) AS SortKey");
		$query->selectRaw("0 AS Count");//得意先ごとの件数:headerにだけ値を入れる
		$query->whereRaw("deliveries.attributes->'$.Summary.\"締めフラグ\"' > 0");
		$query->whereRaw("detail.attributes->>'$.\"商品名\"' NOT IN ('null')");
		$query->whereRaw("detail.attributes->>'$.\"金額\"' <> 0");
		$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} 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->selectRaw("'' as OtherDeliveryName");
		$query->selectRaw("deposits.attributes->>'$.Application1' as OtherDepositName");
		$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("0 AS SalesKind");
		$query->selectRaw("(details.attributes->>'$.Amount') AS IncomePrice");
		$query->selectRaw("CONCAT(billing.code, ':', {$KeyColumn}, ':1;', details.attributes->'$.Number', details.id) AS SortKey");
		$query->selectRaw("0 AS Count");//得意先ごとの件数:headerにだけ値を入れる
		$query->where('deposits.deleted', null);
		$query->whereRaw("deposits.attributes->'$.ClosedFlag' > 0");
		$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]);


		// 全顧客
		$customers = Customer::GetKeyValueArray();
		$summaries = [];
		foreach($customers as $customer)
		{
			$customerCode = $customer['code'];
			$summary = [
				'CustomerCode' => $customer['code'],
				'CustomerName' => $customer['name'],
				'CarriedPrice' => 0,
				'Count' => 0,
			];
			$summaries[$customerCode] = $summary;
		}		

		$records = [];
		foreach($lastMonthCarryOvers as &$lastMonthCarryOver)
		{
			$customerCode = $lastMonthCarryOver['CustomerCode'];
			$summary = [
				'CustomerCode' => $lastMonthCarryOver['CustomerCode'],
				'CustomerName' => $lastMonthCarryOver['CustomerName'],
				'CarriedPrice' => $lastMonthCarryOver['attributes']['CarryOver'],
				'Count' => 0,
			];

			$summaries[$customerCode] = $summary;

			//if ($lastMonthCarryOver['attributes']['CarryOver'] > 0)
			{
				$sortKey = "{$customerCode}:0000:header";
				$records[$sortKey] = [
					'CustomerCode' => $customerCode,
					'CustomerName' => $lastMonthCarryOver['CustomerName'],
					'OtherDeliveryName' => '',
					'OtherDepositName' => '',
					'TheDate' => '',
					'ProductName' => 'header',
					'Amount' => 0,
					'UnitCode' => 0,
					'UnitPrice' => 0,
					'SalesPrice' => 0,
					'SalesKind' => 0,
					'TaxPrice' => 0,
					'DepositType' => 0,
					'IncomePrice' => 0,
					'BillNumber' => 0,
					'Remark' => $sortKey,
					'Count' => 0,
				];
			}
		}
		// 売上区分
		$salesKinds = SalesKind::GetKeyValueArray();

		foreach($deliveries as $delivery)
		{
			$unitName = '';
			if (array_key_exists($delivery['SalesKind'],$salesKinds))
			{
				if ($salesKinds[$delivery['SalesKind']]['surplus'] > 0)
				{
				}
				else
				{
					// 返品・値引き
					$delivery['SalesPrice'] *= -1;
				}
			}			
			if (array_key_exists($delivery['UnitCode'], $units))
			{
				$unitName = $units[$delivery['UnitCode']]['name'];
			}
			$delivery['UnitName'] = $unitName;
			
			$records[$delivery['SortKey']] = $delivery;
			$headerSortKey = "{$delivery['CustomerCode']}:0000:header";
			$records[$headerSortKey]['Count']++;
		}
		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;
			$headerSortKey = "{$deposit['CustomerCode']}:0000:header";
			$records[$headerSortKey]['Count']++;
		}

		ksort($records);

		//logger('Monthly.customer01', ['customers' => $customers]);
		//logger('Monthly.customer01', ['records' => $records]);
		
		$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("CASE detail.attributes->>'$.\"機械No\"' WHEN ''THEN '0000' WHEN 'null' THEN '0000' WHEN null THEN '0000' ELSE detail.attributes->>'$.\"機械No\"' END 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.\"締めフラグ\"' > 0");
		$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.\"締めフラグ\"' > 0");
		$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' > 0");
		$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.\"締めフラグ\"' > 0");
		$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' > 0");
		$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;
	}


}