public static function normalizeBankAccounts()
{
$limit = 400;
\org\sopen\dev\DebugLogger::openLogFileAppend(CSopen::instance()->getConfigPath() . '/logs/sepa-integrate.log');
try {
$dFilter = new Addressbook_Model_ContactFilter(array(array('field' => 'query', 'operator' => 'contains', 'value' => '')), 'AND');
/*$dFilter = new Addressbook_Model_ContactFilter(array(array(
'field' => 'contact_id',
'operator' => 'in',
'value' => array('100222', '100223') )), 'AND');
*/
$contactIds = Addressbook_Controller_Contact::getInstance()->search($dFilter, null, false, true);
\org\sopen\dev\DebugLogger::log('Count adress ids: ' . count($contactIds));
$count = 0;
foreach ($contactIds as $contactId) {
/*if($count++ > $limit){
break;
}*/
$contact = Addressbook_Controller_Contact::getInstance()->get($contactId);
self::doSepa($contact);
//$bankAccountCollection = Billing_Controller_Debitor::getInstance()->getBankAccountCollectionForDebitor($debitor);
}
} catch (Exception $e) {
\org\sopen\dev\DebugLogger::log('global error: ' . $e->__toString());
}
}
private function doExport($mandateIds, $data)
{
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$filename = $tempFilePath . DIRECTORY_SEPARATOR . md5(uniqid(rand(), true)) . '.csv';
$filehandle = fopen($filename, 'w');
//self::fputcsv($filehandle, $this->fields['header']);
if (count($mandateIds) < 1) {
self::fputcsv($filehandle, array('Fehler:', 'keine Sepa-Mandate zum angegebenen Filter gefunden.'));
}
/*
* Beraternummer
Mandantennummer
Gläubiger
Gläubiger-ID
Zahlungspflichtiger
IBAN
BIC
Kreditinstitut
Unterschriftsdatum
Mandatsreferenz
Mandatstyp
Ausführungsvariante
Mandatszweck
Vorlauffrist zur Einreichung bei der Bank
Mandat ist gültig ab
Notiz
Pre-Notification-Frist
Historische Mandatsreferenz
Status
*/
foreach ($mandateIds as $id) {
$sepaMandate = $this->controller->get($id);
$sepaCreditor = $sepaMandate->getSepaCreditor();
$bankAccount = $sepaMandate->getBankAccount();
$resultArray = array($data['datevBerater'], $data['datevMandant'], $sepaCreditor->getName(), $sepaCreditor->getSepaCreditorIdent(), $bankAccount->getName(), $bankAccount->getIBAN(), $bankAccount->getBIC(), $bankAccount->getBank(), $sepaMandate->getSignatureDate()->toString('dd.MM.yyyy'), $sepaMandate->getMandateIdent(), $sepaMandate->tellMandateType(), $sepaMandate->tellMandateExecutionType(), '', 6, $sepaMandate->getSignatureDate()->toString('dd.MM.yyyy'), '', 14, '', '');
self::fputcsvEncoding('ISO-8859-1', $filehandle, $resultArray, ';', '', '');
}
fclose($filehandle);
return $filename;
}
private function runTransaction()
{
try {
$config = \Tinebase_Config::getInstance()->getConfig('pdfserver', NULL, TRUE)->value;
$storageConf = \Tinebase_Config::getInstance()->getConfig('printjobs', NULL, TRUE)->value;
$this->tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$this->templateController = DocManager_Controller_Template::getInstance();
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$this->pdfServer = org\sopen\app\api\pdf\server\PdfServer::getInstance($config)->setDocumentsTempPath(CSopen::instance()->getDocumentsTempPath());
$this->printJobStorage = org\sopen\app\api\filesystem\storage\TempFileProcessStorage::createNew('printjobs', $storageConf['storagepath']);
$this->printJobStorage->addProcessLines(array('in', 'convert', 'out'));
$tId = $tm->startTransaction($db);
$this->createResult();
// make db changes final
$tm->commitTransaction($tId);
// output the result
if ($this->count > 0) {
$this->outputResult();
} else {
$this->outputNone();
}
} catch (Exception $e) {
echo $e->__toString();
$tm->rollback($tId);
}
}
private function runTransaction($process)
{
try {
$config = \Tinebase_Config::getInstance()->getConfig('pdfserver', NULL, TRUE)->value;
$storageConf = \Tinebase_Config::getInstance()->getConfig('printjobs', NULL, TRUE)->value;
$this->copyWatermarkFile = $config['copywatermarkfile'];
$this->previewWatermarkFile = $config['previewwatermarkfile'];
$this->tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$this->templateController = DocManager_Controller_Template::getInstance();
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$this->pdfServer = org\sopen\app\api\pdf\server\PdfServer::getInstance($config)->setDocumentsTempPath(CSopen::instance()->getDocumentsTempPath());
$this->printJobStorage = org\sopen\app\api\filesystem\storage\FileProcessStorage::createNew('printjobs', $storageConf['storagepath']);
$this->printJobStorage->addProcessLines(array('in', 'convert', 'out', 'preview', 'copy'));
$tId = $tm->startTransaction($db);
$preview = false;
switch ($process) {
case self::PROCESS_TEST:
break;
case self::PROCESS_RECEIPT:
$this->createReceipt();
break;
case self::PROCESS_RECEIPTS:
$this->createReceipts();
break;
case self::PROCESS_RECEIPT_PREVIEW:
$this->createReceipt();
$preview = true;
break;
case self::PROCESS_SUPPLY_RECEIPT:
$this->createSupplyReceipt();
break;
case self::PROCESS_SUPPLY_RECEIPT_PREVIEW:
$this->createSupplyReceipt();
$preview = true;
break;
}
// create the multipage output from single page input files
if ($this->count > 0) {
$this->createResult();
}
// make db changes final
$tm->commitTransaction($tId);
// output the result
if ($this->output) {
if ($this->count > 0) {
if ($preview) {
if ($this->outputType == self::OUTPUT_TYPE_PAGE) {
$this->outputPreview();
} else {
$this->outputPreviewForDownload();
}
} else {
if ($this->outputType == self::OUTPUT_TYPE_PAGE) {
$this->outputResult();
} else {
$this->outputResultForDownload();
}
}
} else {
$this->outputNone();
}
}
} catch (Exception $e) {
//echo $e->__toString();
$tm->rollback($tId);
throw $e;
}
}
public function prepareMonitionExport(Billing_Model_BatchJob $job)
{
error_reporting(E_ALL);
set_time_limit(0);
\org\sopen\dev\DebugLogger::openLogFileOverwrite(CSopen::instance()->getCustomerPath() . '/conf/logs/mon.log');
//throw new Exception(CSopen::instance()->getCustomerPath().'/conf/logs/mon.log');
try {
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$tId = $tm->startTransaction($db);
$job = Billing_Api_BatchJobManager::getInstance()->loadBatchJob($job->getId());
// Billing_Api_BatchJobManager::getInstance()->startBatchJob();
$data = $job->getData();
$filters = $data['filters'];
$aContexts = $data['contexts'];
if (!is_array($aContexts)) {
$aContexts = Zend_Json::decode($aContexts);
}
$pagination = new Tinebase_Model_Pagination();
//require_once 'Payment/Monition.php';
if (!is_array($filters)) {
$filters = Zend_Json::decode($filters);
}
/*if(!is_array($paymentTypeKeys)){
$paymentTypeKeys = Zend_Json::decode($paymentTypeKeys);
}
$paymentTypeKeys = array(
1 => 'DEBIT',
2 => 'DEBIT_GM'
);*/
$monitionStage1Days = (int) Tinebase_Core::getPreference('Billing')->getValue(Billing_Preference::MONITION_STAGE1);
// get all open items (for debit)
// grouped by debitors
$filters[] = array('field' => 'due_days', 'operator' => 'greater', 'value' => $monitionStage1Days);
/* $filters[] = array(
'field' => 'type',
'operator' => 'notin',
'value' => $paymentTypeKeys
);*/
$filters[] = array('field' => 'state', 'operator' => 'not', 'value' => 'DONE');
$contextFilters = array();
foreach ($aContexts as $context => $flag) {
if ($flag) {
$contextFilters[] = $context;
}
}
$filters[] = array('field' => 'erp_context_id', 'operator' => 'in', 'value' => $contextFilters);
//$rawFilters = $filters;
//$paymentTypeCount = count($paymentTypeKeys);
/*$filters[] = array(
'field' => 'payment_method_id',
'operator' => 'equals',
'value' => $paymentTypeKeys[1]
);*/
//print_r($filters);
$filter = new Billing_Model_OpenItemFilter($filters, 'AND');
// count membership matching filters
$openItemIds = Billing_Controller_OpenItem::getInstance()->search($filter, new Tinebase_Model_Pagination(array('sort' => 'id', 'dir' => 'ASC')), false, true);
//print_r($openItemIds);
$debitorOpenItemIds = array();
foreach ($openItemIds as $openItemId) {
$openItem = Billing_Controller_OpenItem::getInstance()->get($openItemId);
$debitorId = $openItem->getForeignId('debitor_id');
if (!array_key_exists($debitorId, $debitorOpenItemIds)) {
$debitorOpenItemIds[$debitorId] = array();
}
$debitorOpenItemIds[$debitorId][] = $openItemId;
}
foreach ($debitorOpenItemIds as $debitorId => $debitorOpenItems) {
$debitor = Billing_Controller_Debitor::getInstance()->get($debitorId);
$contact = $debitor->getForeignRecord('contact_id', Addressbook_Controller_Contact::getInstance());
$aSaldation = Billing_Controller_DebitorAccount::getInstance()->getSummationByDebitorId($debitorId, $contextFilters);
$saldation = $aSaldation['sum'];
$batchJobMonition = new Billing_Model_BatchJobMonition(null, true);
$batchJobMonition->setFromArray(array('job_id' => $job->getId(), 'contact_id' => $contact->getId(), 'debitor_id' => $debitor->getId(), 'monition_stage' => 1, 'total_sum' => 0, 'open_sum' => 0, 'total_saldation' => 0, 'count_pos' => 0, 'monition_lock' => $debitor->__get('is_monition_locked'), 'skip' => false, 'action_text' => '', 'action_data' => null, 'action_type' => 'DRYRUN', 'action_state' => 'OPEN', 'error_info' => $errorInfo, 'created_datetime' => Zend_Date::now(), 'valid_datetime' => Zend_Date::now(), 'to_process_datetime' => Zend_Date::now(), 'process_datetime' => Zend_Date::now(), 'usage' => ''));
$batchJobMonition = $this->create($batchJobMonition);
$batchJobMonitionId = $batchJobMonition->getId();
$posCount = count($debitorOpenItems);
$totalSum = 0;
$totalBruttoSum = 0;
$usages = array();
$maxMonitionStage = 1;
foreach ($debitorOpenItems as $openItemId) {
$openItem = Billing_Controller_OpenItem::getInstance()->get($openItemId);
$sum = (double) $openItem->__get('open_sum');
$totalBrutto = (double) $openItem->__get('total_brutto');
$batchJobMonitionItem = new Billing_Model_BatchJobMonitionItem(null, true);
$batchJobMonitionItem->setFromArray(array('batch_monition_id' => $batchJobMonitionId, 'open_item_id' => $openItem->getId(), 'erp_context_id' => $openItem->__get('erp_context_id'), 'open_sum' => $sum, 'total_sum' => $totalBrutto, 'monition_stage' => $openItem->getNextMonitionStage(), 'due_days' => $openItem->__get('due_days'), 'skip' => false, 'usage' => $openItem->__get('usage')));
$usages[] = $openItem->__get('usage');
$totalSum += $sum;
$totalBruttoSum += $totalBrutto;
$maxMonitionStage = max($maxMonitionStage, $openItem->getNextMonitionStage());
Billing_Controller_BatchJobMonitionItem::getInstance()->create($batchJobMonitionItem);
}
$usage = implode(' ', $usages);
$batchJobMonition->__set('usage', $usage);
$batchJobMonition->__set('open_sum', $totalSum);
$batchJobMonition->__set('total_sum', $totalBruttoSum);
$batchJobMonition->__set('total_saldation', $saldation);
$batchJobMonition->__set('monition_stage', $maxMonitionStage);
$batchJobMonition->__set('count_pos', $posCount);
$this->update($batchJobMonition);
}
Billing_Api_BatchJobManager::getInstance()->finish();
$tm->commitTransaction($tId);
\org\sopen\dev\DebugLogger::close();
return array('state' => 'success', 'result' => null);
} catch (Exception $e) {
// $e->__toString();
$tm->rollback($tId);
Billing_Api_BatchJobManager::getInstance()->finishError($e->__toString());
\org\sopen\dev\DebugLogger::log($e->__toString);
\org\sopen\dev\DebugLogger::close();
return array('state' => 'failure', 'result' => null, 'errorInfo' => array('message' => $e->getMessage(), 'trace' => $e->getTrace()));
}
}
public function directDebit($paymentTypeKeys, $filters)
{
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
try {
require_once 'Payment/DTA.php';
if (!is_array($filters)) {
$filters = Zend_Json::decode($filters);
}
if (!is_array($paymentTypeKeys)) {
$paymentTypeKeys = Zend_Json::decode($paymentTypeKeys);
}
$filters[] = array('field' => 'banking_exp_date', 'operator' => 'isnull', 'value' => '');
$rawFilters = $filters;
$paymentTypeCount = count($paymentTypeKeys);
$filters[] = array('field' => 'payment_method_id', 'operator' => 'equals', 'value' => $paymentTypeKeys[1]);
$filter1 = new Billing_Model_OpenItemFilter($filters, 'AND');
$filter = new Tinebase_Model_Filter_FilterGroup(array(), 'OR');
$filter->addFilterGroup($filter1);
if ($paymentTypeCount > 1) {
unset($paymentTypeKeys[1]);
foreach ($paymentTypeKeys as $paymentTypeKey) {
$newFilters = $rawFilters;
$newFilters[] = array('field' => 'payment_method_id', 'operator' => 'equals', 'value' => $paymentTypeKey);
$pFilterGroup = new Billing_Model_OpenItemFilter($newFilters, 'AND');
$filter->addFilterGroup($pFilterGroup);
}
}
// start transaction
$tId = $tm->startTransaction($db);
// count membership matching filters
$openItemIds = $this->search($filter, new Tinebase_Model_Pagination(array('sort' => 'due_date', 'dir' => 'ASC')), false, true);
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$mandators = \Tinebase_Config::getInstance()->getConfig('mandators', NULL, TRUE)->value;
$mandator = $mandators[1]['bankdata'];
$hash = md5(serialize($mandator) . microtime());
$dtaFile = new DTA(DTA_DEBIT);
$dtaFile->setAccountFileSender(array("name" => $mandator['account_holder'], "bank_code" => $mandator['bank_code'], "account_number" => $mandator['account_number']));
// create DTA file
foreach ($openItemIds as $openItemId) {
$openItem = $this->get($openItemId);
// value
$val = (double) $openItem->__get('open_sum');
$usage = $openItem->__get('usage');
$u = explode(',', $usage);
$u1 = $u2 = '';
if (count($u) > 0) {
$u1 = $u[0];
$u2 = $u[1];
} else {
$u1 = $usage;
}
if ($val > 0) {
$debitor = $openItem->getForeignRecordBreakNull('debitor_id', Billing_Controller_Debitor::getInstance());
if (!$debitor) {
continue;
}
$contact = $debitor->getForeignRecordBreakNull('contact_id', Addressbook_Controller_Contact::getInstance());
if (!$contact) {
continue;
}
$receipt = $openItem->getForeignRecordBreakNull('receipt_id', Billing_Controller_Receipt::getInstance());
$dtaFile->addExchange(array("name" => $contact->__get('bank_account_name'), "bank_code" => $contact->__get('bank_code'), "account_number" => $contact->__get('bank_account_number')), (string) $val, array($u1, $u2));
}
$openItem->__set('banking_exp_date', new Zend_Date());
$this->update($openItem);
}
$dtaFile->saveFile($tempFilePath . 'DTAUS0' . $hash);
$meta = $dtaFile->getMetaData();
$date = strftime("%d.%m.%y", $meta["date"]);
$execDate = strftime("%d.%m.%y", $meta["exec_date"]);
$count = $meta["count"];
$sumEUR = $meta["sum_amounts"];
$sumKto = $meta["sum_accounts"];
$sumBankCodes = $meta["sum_bankcodes"];
$sender = $mandator['account_holder'];
$senderBank = $mandator['bank'];
$senderBankCode = $mandator['bank_code'];
$senderAccount = $mandator['account_number'];
$handoutContent = "Datenträger-Begleitzettel\n\tErstellungsdatum: {$date} \n\tAusführungsdatum: {$execDate}\n\tAnzahl der Lastschriften: {$count}\n\tSumme der Beträge in EUR: {$sumEUR}\n\tKontrollsumme Kontonummern: {$sumKto}\n\tKontrollsumme Bankleitzahlen: {$sumBankCodes}\n\tAuftraggeber: {$sender}\n\tBeauftragtes Bankinstitut: {$senderBank}\n\tBankleitzahl: {$senderBankCode}\n\tKontonummer: {$senderAccount}";
$zip = new ZipArchive();
$filename = "{$tempFilePath}/DTAUS0-{$ogNr}.zip";
if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
exit("cannot open <{$filename}>\n");
}
$zip->addFromString("begleitzettel.txt", $handoutContent);
$zip->addFile($tempFilePath . 'DTAUS0' . $hash, 'DTAUS0');
$zip->close();
header("Content-type: application/zip;\n");
header("Content-Transfer-Encoding: binary");
$len = filesize($filename);
header("Content-Length: {$len};\n");
$outname = "DTAUS0-{$ogNr}.zip";
header("Content-Disposition: attachment; filename=\"{$outname}\";\n\n");
readfile($filename);
unlink($filename);
$tm->commitTransaction($tId);
} catch (Exception $e) {
$tm->rollback($tId);
}
}
public function exportAsCsv($export, $outputFileName)
{
$labels = $export->getFieldLabels();
$data = $export->toArray();
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$filename = $tempFilePath . DIRECTORY_SEPARATOR . md5(uniqid(rand(), true)) . '.csv';
$filehandle = fopen($filename, 'w');
Tinebase_Export_Csv::fputcsv($filehandle, $labels);
foreach ($data as $line) {
Tinebase_Export_Csv::fputcsv($filehandle, $line);
}
Tinebase_Export_Csv::fputcsv($filehandle, array(null, null, null));
Tinebase_Export_Csv::fputcsv($filehandle, array(null, null, null));
Tinebase_Export_Csv::fputcsv($filehandle, $export->getSumLabels());
Tinebase_Export_Csv::fputcsv($filehandle, array($export->getSumTotalNetto(), $export->getSumTotalBrutto()));
fclose($filehandle);
header("Pragma: public");
header("Cache-Control: max-age=0");
header('Content-Disposition: attachment; filename=' . $outputFileName);
header("Content-Description: csv File");
header("Content-type: {$contentType}");
readfile($filename);
unlink($filename);
}
public function runDebitDtaExport($jobId, $dueDate)
{
set_time_limit(0);
error_reporting(E_ALL);
$job = Billing_Api_BatchJobManager::getInstance()->loadBatchJob($jobId);
Billing_Api_BatchJobManager::getInstance()->startBatchJob();
try {
//$dueDate = '2014-02-17';
$dueDate = new Zend_Date($dueDate);
// init wsf lib
\worg\wsf\util\xml\XmlSerializer::setIgnoreEmptyTags(true);
\worg\wsf\util\objects\Binding::getForXmlFile(CSopen::instance()->getLibPath() . 'wsflib/worg/wsf/sepa/binding/', 'debit-binding.xml');
$sepa = $this->createNewSepa($creditor);
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$tId = $tm->startTransaction($db);
$data = $job->getData();
$filters = $data['filters'];
$pagination = new Tinebase_Model_Pagination();
$contexts = array('EVENTMANAGER' => 'Veranst.', 'MEMBERSHIP' => 'Beitrag', 'DONATOR' => 'Spende', 'ERP' => 'allg.');
// get all open items (for debit)
// grouped by debitors
$filters[] = array('field' => 'job_id', 'operator' => 'equals', 'value' => $job->getId());
$filter = new Billing_Model_BatchJobDtaFilter($filters, 'AND');
// count membership matching filters
$batchJobDtaIds = $this->search($filter, new Tinebase_Model_Pagination(array('sort' => 'n_family', 'dir' => 'ASC')), false, true);
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$hash = md5(microtime());
$protocolBatchJobDtaIds = array();
$sumOfPayment = 0;
$countTransactions = 0;
foreach ($batchJobDtaIds as $batchJobDtaId) {
$batchJobDta = $this->get($batchJobDtaId);
// keep out item which are diffed
if ($batchJobDta->__get('bank_valid') != 'YES' || abs((double) $batchJobDta->__get('total_sum')) == 0) {
continue;
}
//$bankAccount = Billing_Api_BankAccount::getFromBatchJobDta($batchJobDta);
$bankAccount = $batchJobDta->getBankAccount();
if (is_null($bankAccount)) {
continue;
}
$batchJobDtaItems = Billing_Controller_BatchJobDtaItem::getInstance()->getByBatchJobDtaId($batchJobDtaId);
$usage = array();
$contactNr = $batchJobDta->getForeignId('contact_id');
$usage[] = 'Adr.Nr ' . $contactNr;
$payment = Billing_Model_Payment::createDebitFromDta($batchJobDtaId, $batchJobDta->getForeignId('debitor_id'), new Zend_Date($batchJobDta->__get('created_datetime')), $batchJobDta->__get('total_sum'), 'DEBIT', 'ERP');
$this->extractUsageFromDtaItems($batchJobDtaItems, $usage, $contexts, $aReceiptIds);
$paymentAmount = $batchJobDta->getTotalSum();
$sumOfPayment += $paymentAmount;
if (is_array($usage)) {
$usage = join(' ', $usage);
}
$endToEndId = $contactNr . '-' . join('-', $aReceiptIds);
$this->addSepaTransaction($sepa, $batchJobDta, $bankAccount, $creditor, $dueDate, $paymentAmount, $endToEndId, $usage);
if ($this->countTransactions % 1000 == 0) {
$filename = $tempFilePath . "SEPA-DD-" . $job->__get('job_nr') . "-" . ($this->getFileCount() + 1) . ".xml";
$this->finishSepaTransactionBlock($sepa, $filename);
$sepa = $this->createNewSepa($creditor);
}
$protocolBatchJobDtaIds[] = $batchJobDtaId;
$batchJobDta->__set('action_state', 'EXPORTED');
$batchJobDta->__set('process_datetime', Zend_Date::now());
$batchJobDta->__set('processed_by_user', Tinebase_Core::get(Tinebase_Core::USER)->getId());
$this->update($batchJobDta);
}
if ($this->hasOpenSepaContainer()) {
$filename = $tempFilePath . "SEPA-DD-" . $job->__get('job_nr') . "-" . ($this->getFileCount() + 1) . ".xml";
$this->finishSepaTransactionBlock($sepa, $filename);
}
if ($this->getFileCount() > 1) {
$zip = new ZipArchive();
$zipFilename = $tempFilePath . "SEPA-DD-" . $job->__get('job_nr') . ".zip";
if ($zip->open($zipFilename, ZIPARCHIVE::CREATE) !== TRUE) {
throw new SPExcpetion("cannot open <{$filename}> for zip");
}
$lCount = 0;
foreach ($this->sepaFiles as $file) {
$zip->addFile($file, 'SEPA-DD-' . $job->__get('job_nr') . '-' . ++$lCount . ".xml");
}
$zip->close();
$jobFileName = $zipFilename;
$downloadExt = '.zip';
} else {
$downloadExt = '.xml';
$jobFileName = $this->sepaFiles[0];
}
Billing_Api_BatchJobManager::getInstance()->jobAddData('DTA', $jobFileName);
Billing_Api_BatchJobManager::getInstance()->jobAddData('DTADownloadFilename', 'SEPA-DD-' . $job->__get('job_nr') . $downloadExt);
//$tm->rollback($tId);
$tm->commitTransaction($tId);
//Billing_Api_BatchJobManager::getInstance()->finish();
$this->printDtaExportPrepare($jobId, false, null, true, $protocolBatchJobDtaIds);
return array('state' => 'success', 'result' => null);
// $outputFileName = $tempFilePath . md5(serialize($job).'preparePDF'.microtime()).'.pdf';
} catch (Exception $e) {
echo $e->__toString();
$tm->rollback($tId);
Billing_Api_BatchJobManager::getInstance()->finishError($e->__toString());
return array('state' => 'failure', 'result' => null, 'errorInfo' => array('message' => $e->getMessage(), 'trace' => $e->getTrace()));
}
}
/**
*
* Export to DATEV
* @param string $filters Json encoded filters
*/
public function exportFibu($filters)
{
set_time_limit(0);
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$tId = $tm->startTransaction($db);
try {
$filters = Zend_Json::decode($filters);
$checkFilter = new Billing_Model_OpenItemFilter($filters, 'AND');
$pagination = array('sort' => 'op_nr', 'dir' => 'ASC');
$oIController = Billing_Controller_OpenItem::getInstance();
$debController = Billing_Controller_Debitor::getInstance();
$rController = Billing_Controller_Receipt::getInstance();
$oController = Billing_Controller_Order::getInstance();
$oPController = Billing_Controller_OrderPosition::getInstance();
$aController = Billing_Controller_Article::getInstance();
$cController = Addressbook_Controller_Contact::getInstance()->_setRightChecks(false)->_setContainerACLChecks(false);
// if filter contains fibu_exp_date-filter -> force reexport!
if (!$checkFilter->isFilterSet('fibu_exp_date')) {
$fibuFilter = array('field' => 'fibu_exp_date', 'operator' => 'isnull', 'value' => '');
$filters[] = $fibuFilter;
}
$filter = new Billing_Model_OpenItemFilter($filters, 'AND');
$openItemIds = $oIController->search($filter, new Tinebase_Model_Pagination($pagination), false, true);
/*
*
- Rechnungsnummer (redundant für alle Positionen)
- Rechnungsdatum (redundant für alle Positionen)
- Datum Fälligkeit (redundant für alle Positionen)
- Debitorennummer (redundant für alle Positionen)
- Betrag netto
- MWST
- Betrag brutto
- Artikelnummer
- Artikelbezeichnung
- Erlöskonto (aus Artikelstamm gemäß MWST = 0 oder > 0)
*
Umsatz (mit Soll/Haben-Kz) Konto Gegenkonto (ohne BU-Schl�ssel) Belegdatum Belegfeld 1 Buchungstext Kennzeichen D_Opos
299,22 1000200 440000 12.01.2012 101 Rechnung101 Forderungsart "1" D_Opos
*
*/
$csvArray = array('Umsatz (mit Soll/Haben-Kz)', 'Konto', 'Gegenkonto (ohne BU-Schlüssel)', 'Belegdatum', 'Belegfeld', 'Buchungstext', 'Kennzeichen', 'D_Opos');
/*
*
*
- Debitorennummer
- Vorname
- Name
- Anrede
- Titel
- Firma 1
- Firma 2
- Zusatz
- Straße
- PLZ
- Ort
- Land
Kontonummer Unternehmen Anrede Titel Vorname Name1 Adressattyp UST ID Stra�e Postfach Postleitzahl Ort Land Bankleitzahl 1 Bankbez. 1 Bank-Kontonummer 1 Bank-L�nderkennz. 1 IBAN-Nr. 1 SWIFT-Code 1 Bankleitzahl 2 Bankbez. 2 Bank-Kontonummer2 Bank-L�nderkennz. 2 IBAN-Nr. 2 SWIFT-Code 2
5841100 Acuarios Jandia Sotavento Beach Club B.Lehmann Lehmann & Buschmann 2 DE111667148 Wefelen 27 35627 Costa-Calma/Fuerte. Spanien 70050000 Bayern LB M�nchen 2034343 DE DE14700500000002034343 BYLADEMM
5841200 Herr Prof. Dr. Hansi Gustavo 1 DE111667148 Wefelen 29 52134 Herzogenrath 39050000 Sparkasse Aachen 47176987
*/
$debsArray = array('Kontonummer', 'Unternehmen', 'Anrede', 'Titel', 'Vorname', 'Name1', 'Adressattyp', 'UST ID', 'Straße', 'Postfach', 'Postleitzahl', 'Ort', 'Land', 'Bankleitzahl 1', 'Bankbez. 1', 'Bank-Kontonummer 1', 'Bank-Länderkennz. 1', 'IBAN-Nr. 1', 'SWIFT-Code 1', 'Bankbez. 2', 'Bank-Kontonummer 2', 'Bank-Länderkennz. 2', 'IBAN-Nr. 2', 'SWIFT-Code 2');
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$itemsFilename = $tempFilePath . strftime('%Y%m%d%H%M%S') . 'items_temp.csv';
$debsFilename = $tempFilePath . strftime('%Y%m%d%H%M%S') . 'debs_temp.csv';
$zipFilename = $tempFilePath . strftime('%Y%m%d%H%M%S') . 'archive_temp.csv';
if (file_exists($itemsFilename)) {
unlink($itemsFilename);
}
$itemsFileHandle = fopen($itemsFilename, 'w');
//$filePointer, $dataArray, $delimiter=',', $enclosure='"', $escapeEnclosure='"'
Tinebase_Export_Csv::fputcsvEncoding('ISO-8859-1//TRANSLIT', $itemsFileHandle, $csvArray, chr(9), '"', '');
if (file_exists($debsFilename)) {
unlink($debsFilename);
}
$debsFileHandle = fopen($debsFilename, 'w');
Tinebase_Export_Csv::fputcsvEncoding('ISO-8859-1//TRANSLIT', $debsFileHandle, $debsArray, chr(9), '"', '');
$zeroVat = Billing_Controller_Vat::getInstance()->getByName('0');
$creditorZeroAccount = $zeroVat->__get('credit_account');
$fibuExpDate = new Zend_Date(strftime('%Y-%m-%d'));
$adType = 1;
foreach ($openItemIds as $openItemId) {
$oI = Billing_Controller_OpenItem::getInstance()->get($openItemId);
$oI->flatten();
$receiptId = $oI->__get('receipt_id');
$receipt = $rController->get($receiptId);
if (!$receipt->__get('fibu_exp_date')) {
$receipt->__set('fibu_exp_date', $fibuExpDate);
$receipt->flatten();
//print_r($receipt);
$rController->update($receipt);
}
$oI->__set('fibu_exp_date', $fibuExpDate);
$oIController->update($oI);
$positions = $rController->getOrderPositions($receiptId);
//$sums = $rController->getReceiptSumValues($receiptId);
$orderId = $receipt->__get('order_id');
$order = $oController->get($orderId);
$debitor = $order->__get('debitor_id');
$debitorNr = $debitor->__get('debitor_nr');
$contact = $debitor->getForeignRecord('contact_id', $cController);
$debitor->flatten();
$contactId = $debitor->__get('contact_id');
$name = $contact->__get('n_fileas');
$receiptNr = $oI->__get('receipt_nr');
$adType = 1;
if ($contact->__get('org_name') && $contact->getLetterDrawee()->isCompany()) {
$adType = 2;
}
$dueDate = $receipt->__get('due_date');
if (!$debitor->__get('fibu_exp_date')) {
$drawee = $contact->getInvoiceDrawee();
$postal = $drawee->getPostalAddress();
$pf = '';
$street = '';
if (strpos($postal->getStreet(), 'Post')) {
$pf = $postal->getStreet();
} else {
$street = $postal->getStreet();
}
// export debitor
$debsArray = array($debitorNr, $contact->__get('org_name'), $drawee->getSalutationText(), $drawee->getTitle(), $contact->__get('n_given'), $contact->__get('n_family'), $adType, $debitor->__get('ust_id'), $street, $pf, $postal->getPostalCode(), $postal->getLocation(), $postal->getCountryCode('DE'), $contact->__get('bank_code'), $contact->__get('bank_name'), $contact->__get('bank_account_number'), trim($contact->__get('bank_code')) ? $postal->getCountryCode() : '', '', '', '', '', '', '', '', '');
Tinebase_Export_Csv::fputcsvEncoding('ISO-8859-1//TRANSLIT', $debsFileHandle, $debsArray, chr(9), '"', '');
$debitor->__set('fibu_exp_date', new Zend_Date());
$debController->update($debitor);
}
// $values = array();
// $values[] = array(
// 'value' => $sums['total']['sum']['netto'],
// 'credit_account' => $creditorZeroAccount
// );
//
// foreach($sums['vat_sums'] as $vatSum){
// $values[] = array(
// 'value' => $vatSum['sum']['netto'],
// 'credit_account' => $vatSum['sum']['vatcreditaccount']
// );
// }
//Umsatz (mit Soll/Haben-Kz) Konto Gegenkonto (ohne BU-Schl�ssel) Belegdatum Belegfeld 1 Buchungstext Kennzeichen D_Opos
//299,22 1000200 440000 12.01.2012 101 Rechnung101 Forderungsart "1" D_Opos
$receiptText = '';
if ($receipt->__get('type') == Billing_Model_Receipt::TYPE_INVOICE) {
$receiptText = 'Rechnung';
$receiptDate = $receipt->__get('invoice_date');
$receiptNr = $receipt->__get('invoice_nr');
} else {
$receiptText = 'Gutschrift';
$receiptDate = $receipt->__get('credit_date');
$receiptNr = $receipt->__get('credit_nr');
}
$bookingText = $receiptText . $receiptNr;
/*if($oI->__get('usage')){
$bookingText = $oI->__get('usage');
}*/
$paymentMethodId = $receipt->getForeignId('payment_method_id');
$mapPaymentMethod = array('PREPAYMENT' => 1, 'BANKTRANSFER' => 2, 'DEBIT' => 3, 'CASH' => 4, 'CREDITCARD' => 5, 'PAYPAL' => 6, 'IMMEDIATETRANSFER' => 7);
$intPaymentMethod = $mapPaymentMethod[$paymentMethodId];
foreach ($positions as $position) {
$brutto = $position->__get('total_brutto');
//$vatSum = $oPController->getVatSum($position);
//$article = $position->getForeignRecord('article_id', $aController);
//$articleName = $article->__get('name');
//$articleNr = $article->__get('article_nr');
$revAccount = $oPController->getRevenueAccountAccordingToVat($position) . '00';
$csvArray = array(number_format($brutto, 2, ',', '.'), $debitorNr, $revAccount, \org\sopen\app\util\format\Date::format($receiptDate), $receiptNr, $bookingText, 'Forderungsart ' . $intPaymentMethod, 'D_Opos');
Tinebase_Export_Csv::fputcsvEncoding('ISO-8859-1//TRANSLIT', $itemsFileHandle, $csvArray, chr(9), '"', '');
}
}
fclose($itemsFileHandle);
fclose($debsFileHandle);
// -> generate zip archive containing Debitoren.csv and Forderungen.csv
$zip = new ZipArchive();
$zipFile = $zipFilename . ".zip";
if ($zip->open($zipFile, ZIPARCHIVE::CREATE) !== TRUE) {
exit("cannot open <{$zipFile}>\n");
}
$zip->addFile($debsFilename, 'Debitoren.csv');
$zip->addFile($itemsFilename, 'Forderungen.csv');
$zip->close();
$tm->commitTransaction($tId);
} catch (Exception $e) {
$tm->rollback($tId);
echo $e->__toString();
exit;
}
header("Content-type: application/zip;\n");
header("Content-Transfer-Encoding: binary");
$len = filesize($zipFile);
header("Content-Length: {$len};\n");
header("Content-Disposition: attachment; filename=\"DATEV.zip\";\n\n");
readfile($zipFile);
unlink($zipFile);
unlink($debsFilename);
unlink($itemsFilename);
}
private function runTransaction()
{
try {
$config = \Tinebase_Config::getInstance()->getConfig('pdfserver', NULL, TRUE)->value;
$storageConf = \Tinebase_Config::getInstance()->getConfig('printjobs', NULL, TRUE)->value;
$this->tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$this->templateController = DocManager_Controller_Template::getInstance();
$db = Tinebase_Core::getDb();
$tm = Tinebase_TransactionManager::getInstance();
$this->pdfServer = org\sopen\app\api\pdf\server\PdfServer::getInstance($config)->setDocumentsTempPath(CSopen::instance()->getDocumentsTempPath());
$this->printJobStorage = org\sopen\app\api\filesystem\storage\TempFileProcessStorage::createNew('printjobs', $storageConf['storagepath']);
$this->printJobStorage->addProcessLines(array('in', 'convert', 'out'));
$tId = $tm->startTransaction($db);
$this->printResult();
// make db changes final
$tm->commitTransaction($tId);
// output the result
if (!$this->createOnly) {
if ($this->count > 0) {
$this->outputResult();
} else {
$this->outputNone();
}
} else {
if ($this->exportFileName) {
$this->printJobStorage->copyOut("//out/result/merge/pdf/final", $this->exportFileName);
}
return;
}
} catch (Exception $e) {
// echo $e->__toString();
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . $e->__toString());
}
$tm->rollback($tId);
throw $e;
}
exit;
}
private function finalize()
{
$tempFilePath = CSopen::instance()->getCustomerPath() . '/customize/data/documents/temp/';
$files = array();
foreach ($this->excelFileMap as $period => $excelObject) {
$filepart = $period . "-" . strftime("%Y-%m-%d_%H-%M-%S") . ".xlsx";
$filename = $tempFilePath . "/" . $filepart;
$files[] = array('file' => $filename, 'part' => $filepart);
$writer = new PHPExcel_Writer_Excel2007($excelObject);
$writer->save($filename);
}
$zip = new ZipArchive();
$downPart = "Excel-Fibu-Exp-" . strftime("%Y-%m-%d_%H-%M-%S") . ".zip";
$downloadfilename = "{$tempFilePath}/" . $downPart;
if ($zip->open($downloadfilename, ZIPARCHIVE::CREATE) !== TRUE) {
exit("cannot open <{$downloadfilename}>\n");
}
foreach ($files as $fileInfo) {
$zip->addFile($fileInfo['file'], $fileInfo['part']);
}
$zip->close();
header("Pragma: public");
header("Cache-Control: max-age=0");
header('Content-Disposition: attachment; filename=' . $downPart);
header("Content-Description: zip File");
header("Content-type: application/zip");
readfile($downloadfilename);
}
