<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING CASHING DOCS ////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningCashingSQL', 0);
$repCashingData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$repCashingData->setMainTable($DBTRd_clientInvoiceCashings,	'doc');
$repCashingData->addJoinTable($DBTRd_clientInvoices,		'inv',			'doc.idInvoice=inv.id',			'INNER');
$repCashingData->addJoinTable($DBTRd_corporates,			'invCorp',		'inv.idCorporate=invCorp.id',	'LEFT');
$repCashingData->addJoinTable($DBTRd_customers, 			'invCust',		'inv.idCustomer=invCust.id',	'LEFT');
$repCashingData->addJoinTable($DBTRd_importedDocs,			'impDoc',		'doc.idImportedDoc=impDoc.id',	'LEFT');

$cols = [
	'doc_id'					=> 'doc.id',
	'doc_sourceCode'			=> '"cashing"',
	'doc_typeCode'				=> 'doc.docType',
	'doc_seriesId'				=> 'doc.idCashingSeries',
	'doc_number'				=> 'doc.docNumber',
	'doc_date'					=> 'doc.docDate',
	'doc_bankAccountId'			=> 'doc.idBankAccount',
	'doc_explanations'			=> 'doc.explanations',
	'doc_comments'				=> 'doc.comments',

	'doc_value'					=> 'doc.value',
	'doc_currency'				=> 'doc.currency',
	'doc_currencyRate'			=> 'doc.currencyRate',
	'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

	'doc_respUserId'			=> 'doc.idUser',
	'doc_respOfficeId'			=> 'doc.idOffice',
	'doc_addUserId'				=> 'doc.idAddUser',
	'doc_issueUserId'			=> 'doc.idIssueUser',
	'doc_blockUserId'			=> 'doc.idBlockUser',

	'doc_addTime'				=> 'doc.addTime',
	'doc_issueTime'				=> 'doc.issueTime',
	'doc_blockTime'				=> 'doc.blockTime',

	'impDoc_id'					=> 'doc.idImportedDoc',
	'impDoc_value'				=> 'impDoc.docValue',
	'impDoc_currency'			=> 'impDoc.docCurrency',
	'impDoc_externalSystemId'	=> 'impDoc.idExternalSystem',
	'impDoc_systemStatusCode'	=> 'impDoc.systemStatus',
	'impDoc_processStatusCode'	=> 'impDoc.status',

	'inv_id'					=> 'inv.id',
	'inv_number'				=> 'inv.invoiceNumber',
	'inv_seriesId'				=> 'inv.idInvoiceSeries',
	'inv_date'					=> 'inv.invoiceDate',
	'inv_dueDate'				=> 'inv.dueDate',
	'inv_value'					=> 'inv.invoiceValue',
	'inv_vat' 					=> 'inv.invoiceVat',
	'inv_total' 				=> 'inv.invoiceTotal',
	'inv_currency'				=> 'inv.currency',
];

$repCashingData->setStructureColumns($cols);
$repCashingData->addStructureColumn("doc_categoryCode",     	"IF(doc.value>0, 'cashing', 'payment')");
$repCashingData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.docDate, '%Y-%m-01')");
$repCashingData->addStructureColumn("doc_dateY", 	            "DATE_FORMAT(doc.docDate, '%Y-01-01')");
$repCashingData->addStructureColumn("doc_valueDefCurr",     	"doc.value*doc.currencyRate");
$repCashingData->addStructureColumn("doc_valueWorkCurr",     	"doc.value*doc.currencyRate/doc.workCurrencyRate");
$repCashingData->addStructureColumn("doc_partnerTypeCode",		"IF(inv.idCorporate, 'corporate', 'customer')", "inv");

$repCashingData->addStructureColumn("inv_idCorporate",			"IF(invCorp.idRealCorporate, invCorp.idRealCorporate, inv.idCorporate)", "invCorp");
$repCashingData->addStructureColumn("inv_idCustomer",			"IF(invCust.idRealCustomer, invCust.idRealCustomer, inv.idCustomer)", "invCust");

$cashIncludedTypesArr = [];
foreach ($CFG['cashBook']['cashBookView']['clientAcceptedDocTypes'] as $type=>$settings) {
	if ($settings) {
		$cashIncludedTypesArr[] = "'".$type."'";
	}
}
$repCashingData->addStructureColumn("doc_isCash",				"IF(doc.docType IN (".implode(', ', $cashIncludedTypesArr)."), 1, 0)");

$repCashingData->setActiveColumns($availableTableColumns);
// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////

$repCashingData->addFilter('doc.voidTime', 'IS NULL');

if ($reportFilters['documentDate']['startDate']) {
	$repCashingData->addFilter('doc.docDate', '>=', $reportFilters['documentDate']['startDate']);
}
if ($reportFilters['documentDate']['endDate']) {
	$repCashingData->addFilter('doc.docDate', '<=', $reportFilters['documentDate']['endDate']);
}

if ($reportFilters['responsibleUser']['idUser']) {
	$repCashingData->addFilter('doc.idUser' , '=', $reportFilters['responsibleUser']['idUser']);
}
elseif ($reportFilters['responsibleUser']['idOffice']) {
	$repCashingData->addFilter('doc.idOffice' , '=', $reportFilters['responsibleUser']['idOffice']);
}

$repCashingData->populate();

///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING PAYMENT DOCS ////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningPaymentSQL', 0);
$repPaymentData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$repPaymentData->setMainTable($DBTRd_supplierInvoicePayments,	'doc');
$repPaymentData->addJoinTable($DBTRd_supplierInvoices,			'inv',		'doc.idInvoice=inv.id',			'INNER');
$repPaymentData->addJoinTable($DBTRd_suppliers,					'supp',		'inv.idSupplier=supp.id',		'INNER');
$repPaymentData->addJoinTable($DBTRd_importedDocs,				'impDoc',	'doc.idImportedDoc=impDoc.id',	'LEFT');

$cols = [
	'doc_id'					=> 'doc.id',
	'doc_sourceCode'			=> '"payment"',
	'doc_typeCode'				=> 'doc.docType',
	'doc_seriesId'				=> 'doc.idPaymentSeries',
	'doc_number'				=> 'doc.docNumber',
	'doc_date'					=> 'doc.docDate',
	'doc_bankAccountId'			=> 'doc.idBankAccount',
	'doc_explanations'			=> 'doc.explanations',
	'doc_comments'				=> 'doc.comments',

	'doc_currency'				=> 'doc.currency',
	'doc_currencyRate'			=> 'doc.currencyRate',
	'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

	'doc_partnerTypeCode'		=> '"supplier"',

	'doc_respUserId'			=> 'doc.idUser',
	'doc_respOfficeId'			=> 'doc.idOffice',
	'doc_addUserId'				=> 'doc.idAddUser',
	'doc_issueUserId'			=> 'doc.idIssueUser',
	'doc_blockUserId'			=> 'doc.idBlockUser',

	'doc_addTime'				=> 'doc.addTime',
	'doc_issueTime'				=> 'doc.issueTime',
	'doc_blockTime'				=> 'doc.blockTime',

	'impDoc_id'					=> 'doc.idImportedDoc',
	'impDoc_value'				=> 'impDoc.docValue',
	'impDoc_currency'			=> 'impDoc.docCurrency',
	'impDoc_externalSystemId'	=> 'impDoc.idExternalSystem',
	'impDoc_systemStatusCode'	=> 'impDoc.systemStatus',
	'impDoc_processStatusCode'	=> 'impDoc.status',

	'inv_id'					=> 'inv.id',
	'inv_number'				=> 'inv.invoiceNumber',
	'inv_series'				=> 'inv.invoiceSeries',
	'inv_date'					=> 'inv.invoiceDate',
	'inv_dueDate'				=> 'inv.dueDate',
	'inv_value'					=> 'inv.invoiceValue',
	'inv_vat' 					=> 'inv.invoiceVat',
	'inv_total' 				=> 'inv.invoiceTotal',
	'inv_currency'				=> 'inv.currency',
];

$repPaymentData->setStructureColumns($cols);
$repPaymentData->addStructureColumn("doc_value",     			"-1*doc.value");
$repPaymentData->addStructureColumn("doc_categoryCode",     	"IF(doc.value<0, 'cashing', 'payment')");
$repPaymentData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.docDate, '%Y-%m-01')");
$repPaymentData->addStructureColumn("doc_dateY", 	            "DATE_FORMAT(doc.docDate, '%Y-01-01')");
$repPaymentData->addStructureColumn("doc_valueDefCurr",     	"-1*doc.value*doc.currencyRate");
$repPaymentData->addStructureColumn("doc_valueWorkCurr",     	"-1*doc.value*doc.currencyRate/doc.workCurrencyRate");

$repPaymentData->addStructureColumn("inv_idSupplier",			"IF(supp.idRealSupplier, supp.idRealSupplier, inv.idSupplier)", "supp");

$payIncludedTypesArr = [];
foreach ($CFG['cashBook']['cashBookView']['supplierAcceptedDocTypes'] as $type=>$settings) {
	if ($settings) {
		$payIncludedTypesArr[] = "'".$type."'";
	}
}
$repCashingData->addStructureColumn("doc_isCash",				"IF(doc.docType IN (".implode(', ', $payIncludedTypesArr)."), 1, 0)");

$repPaymentData->setActiveColumns($availableTableColumns);
// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////

$repPaymentData->addFilter('doc.deleted', '=', 0);

if ($reportFilters['documentDate']['startDate']) {
	$repPaymentData->addFilter('doc.docDate', '>=', $reportFilters['documentDate']['startDate']);
}
if ($reportFilters['documentDate']['endDate']) {
	$repPaymentData->addFilter('doc.docDate', '<=',$reportFilters['documentDate']['endDate']);
}

if ($reportFilters['responsibleUser']['idUser']) {
	$repPaymentData->addFilter('doc.idUser' , '=', $reportFilters['responsibleUser']['idUser']);
}
elseif ($reportFilters['responsibleUser']['idOffice']) {
	$repPaymentData->addFilter('doc.idOffice' , '=', $reportFilters['responsibleUser']['idOffice']);
}

$repPaymentData->populate();

///////////////////////////////////////////////////////////////////////////////////////////////////
// CREATE SQL FOR COPYING CASHBOOK DOCS ///////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
// refresh report status
refreshReportStatus('extractMainDataFromDb', 'runningCashbookSQL', 0);
$repCashbookData = new \App\Reports\Db\ManageRecords\Insert($reportTable);
$repCashbookData->setMainTable($DBTRd_cashbookDocuments,	'doc');

$cols = [
	'doc_id'					=> 'doc.id',
	'doc_sourceCode'			=> '"cashbook"',
	'doc_categoryCode'			=> 'doc.category',
	'doc_typeCode'				=> 'doc.type',
	'doc_number'				=> 'doc.number',
	'doc_date'					=> 'doc.date',
	'doc_bankAccountId'			=> 'doc.idBankAccount',
	'doc_explanations'			=> 'doc.explanations',
	'doc_comments'				=> 'doc.comments',
	'doc_partnerName'			=> 'doc.partner',

	'doc_value'					=> 'doc.value',
	'doc_currency'				=> 'doc.currency',
	'doc_currencyRate'			=> 'doc.currencyRate',
	'doc_workCurrencyRate'		=> 'doc.workCurrencyRate',

	'doc_respUserId'			=> 'doc.idUser',
	'doc_respOfficeId'			=> 'doc.idOffice',
	'doc_addUserId'				=> 'doc.idAddUser',
	'doc_issueUserId'			=> 'doc.idIssueUser',
	'doc_blockUserId'			=> 'doc.idBlockUser',

	'doc_addTime'				=> 'doc.addTime',
	'doc_issueTime'				=> 'doc.issueTime',
	'doc_blockTime'				=> 'doc.blockTime',
];

$repCashbookData->setStructureColumns($cols);
$repCashbookData->addStructureColumn("doc_dateYM", 			    "DATE_FORMAT(doc.date, '%Y-%m-01')");
$repCashbookData->addStructureColumn("doc_dateY", 	            "DATE_FORMAT(doc.date, '%Y-01-01')");
$repCashbookData->addStructureColumn("doc_valueDefCurr",     	"doc.value*doc.currencyRate");
$repCashbookData->addStructureColumn("doc_valueWorkCurr",     	"doc.value*doc.currencyRate/doc.workCurrencyRate");


$cashIncludedInCashbook = [];
foreach ($CFG['cashBook']['documentTypes']['cashing'] as $type=>$settings) {
	if ($settings['includeInCashbook']) {
		$cashIncludedInCashbook[] = "'".$type."'";
	}
}

$payIncludedInCashbook = [];
foreach ($CFG['cashBook']['documentTypes']['payment'] as $type=>$settings) {
	if ($settings['includeInCashbook']) {
		$payIncludedInCashbook[] = "'".$type."'";
	}
}

$repCashbookData->addStructureColumn("doc_isCash",				"IF(doc.category='cashing' AND doc.type IN (".implode(', ', $cashIncludedInCashbook)."), 1, IF(doc.category='payment' AND doc.type IN (".implode(', ', $payIncludedInCashbook)."), 1, 0))");

$repCashbookData->setActiveColumns($availableTableColumns);
// ADD FILTERS ////////////////////////////////////////////////////////////////////////////////////

$repCashbookData->addFilter('doc.deleted', '=', 0);

if ($reportFilters['documentDate']['startDate']) {
	$repCashbookData->addFilter('doc.date', '>=', $reportFilters['documentDate']['startDate']);
}
if ($reportFilters['documentDate']['endDate']) {
	$repCashbookData->addFilter('doc.date', '<=',$reportFilters['documentDate']['endDate']);
}

if ($reportFilters['responsibleUser']['idUser']) {
	$repCashbookData->addFilter('doc.idUser' , '=', $reportFilters['responsibleUser']['idUser']);
}
elseif ($reportFilters['responsibleUser']['idOffice']) {
	$repCashbookData->addFilter('doc.idOffice' , '=', $reportFilters['responsibleUser']['idOffice']);
}

$repCashbookData->populate();
