/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$filterData = array('field' => $this->_field, 'operator' => $this->_operator, 'value' => array($this->_value));
//print_r($filterData);
$oFilter = new Billing_Model_OrderFilter(array(), 'AND');
$oFilter->addFilter($oFilter->createFilter($this->_field, $this->_operator, $this->_value));
/*Filter = $oFilter->toArray();
print_r($aFilter);
*/
$orderIds = Billing_Controller_Order::getInstance()->search($oFilter, null, false, true);
//print_r($orderIds);
$filter = new Billing_Model_ReceiptFilter(array(), 'AND');
if (count($orderIds) > 0) {
$filter->addFilter(new Tinebase_Model_Filter_Id('order_id', 'in', $orderIds));
} else {
$filter->addFilter(new Tinebase_Model_Filter_Id('order_id', 'in', array(0 => null)));
}
$receiptIds = Billing_Controller_Receipt::getInstance()->search($filter, null, false, true);
if (count($receiptIds) > 0) {
$filter1 = new Billing_Model_ArticleSoldFilter(array(), 'AND');
$filter1->addFilter(new Tinebase_Model_Filter_Id('receipt_id', 'in', $receiptIds));
Tinebase_Backend_Sql_Filter_FilterGroup::appendFilters($_select, $filter1, $_backend);
} else {
$filter1 = new Billing_Model_ArticleSoldFilter(array(), 'AND');
$filter1->addFilter(new Tinebase_Model_Filter_Id('receipt_id', 'in', array(0 => null)));
Tinebase_Backend_Sql_Filter_FilterGroup::appendFilters($_select, $filter1, $_backend);
}
// echo $_select->assemble();
}
/**
* inspect create of one record
*
* @param Tinebase_Record_Interface $_record the update record
* @param Tinebase_Record_Interface $_oldRecord the current persistent record
* @return void
*/
protected function _inspectCreate($_record, $_oldRecord)
{
$_record->__set('payment_date', new Zend_Date($_record->__get('payment_date')));
$order = $_record->getForeignRecordBreakNull('order_id', Billing_Controller_Order::getInstance());
if (!$_record->__get('erp_context_id') && $order) {
$_record->__set('erp_context_id', $order->__get('erp_context_id'));
}
$this->handleReturnDebit($_record);
}
protected function _inspectCreate($_record)
{
if (!$_record->__get('booking_nr')) {
$_record->__set('booking_nr', Tinebase_NumberBase_Controller::getInstance()->getNextNumber('booking_nr'));
}
$receipt = $_record->getForeignRecordBreakNull('receipt_id', Billing_Controller_Receipt::getInstance());
$order = $_record->getForeignRecordBreakNull('order_id', Billing_Controller_Order::getInstance());
if ($order) {
$_record->__set('erp_context_id', $order->__get('erp_context_id'));
}
if ($_record->__get('booking_date')) {
if (!$_record->__get('booking_receipt_date')) {
$_record->__set('booking_receipt_date', $_record->__get('booking_date'));
}
}
}
public function toArray($_valueToJson = false)
{
$result = parent::toArray($_valueToJson);
foreach ($result as &$filterData) {
if ($filterData['field'] == 'id' && $_valueToJson == true && !empty($filterData['value'])) {
//if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' value:' . print_r($filterData['value'], true));
try {
$filterData['value'] = Billing_Controller_Order::getInstance()->get($filterData['value'])->toArray();
} catch (Tinebase_Exception_NotFound $nfe) {
if (Tinebase_Core::isLogLevel(Zend_Log::INFO)) {
Tinebase_Core::getLogger()->INFO(__METHOD__ . '::' . __LINE__ . " could not find and resolve timeaccount {$filterData['value']}");
}
}
}
}
return $result;
}
public function exportFibu($filters, $simulation)
{
error_reporting(E_ALL);
ini_set('display_errors', 'on');
Billing_Controller_Order::getInstance()->exportFibu($filters, strtolower(trim($simulation)) == 'false' ? false : true);
}
public static function import($fileName)
{
$result = array('failcount' => 0, 'totalcount' => 0, 'duplicatecount' => 0, 'status' => 'success');
try {
$reader = new Reader();
$statements = $reader->getStatements(file_get_contents($fileName));
foreach ($statements as $statement) {
foreach ($statement->getTransactions() as $transaction) {
//echo $transaction->getAmount() . "\n";
$amount = (double) $transaction->getAmount();
$description = $transaction->getDescription();
$accountNumber = $statement->getAccount()->getNumber();
//echo $accountNumber;
$num = null;
$debitorNr = null;
$memberNr = null;
$receiptNr = null;
$context = 'ERP';
$cdesc = strtolower($description);
$opNr = null;
$openItemId = null;
$debitorId = null;
$opId = null;
$type = null;
$state = 'RED';
$opAmount = 0;
$overpay = 0;
if ($amount < 0) {
$type = 'DEBIT';
} else {
$type = 'CREDIT';
}
if (strpos($cdesc, 'mitg') || strpos($cdesc, 'tgli') || strpos($cdesc, 'beitr')) {
$context = 'MEMBERSHIP';
} elseif (strpos($cdesc, 'spend')) {
$context = 'DONATOR';
}
$isReturnDebit = false;
if (strpos($cdesc, 'rueckbelast')) {
$isReturnDebit = true;
}
$numDesc = $transaction->getDescriptionConcat(1, 7);
$numDescCheck = strtolower($numDesc);
$pos = strpos($numDescCheck, 'mitg');
if (!$pos) {
$pos = strpos($numDescCheck, 'tgli');
}
if (!pos) {
$pos = strpos($numDescCheck, 'beitr');
}
if (!pos) {
$pos = strpos($numDescCheck, 'rech');
}
if ($numDesc && $pos) {
if (preg_match('/([0-9]{5,7})/', substr($numDesc, $pos), $match)) {
$num = $match[1];
}
}
if ($num && !$isReturnDebit) {
if (strpos($cdesc, 'rech')) {
try {
$receiptNr = $num;
$receiptId = Billing_Controller_Receipt::getInstance()->getIdByProperty('invoice_nr', $num);
$receipt = Billing_Controller_Receipt::getInstance()->get($receiptId);
$context = $receipt->getForeignId('erp_context_id');
$order = $receipt->getForeignRecordBreakNull('order_id', Billing_Controller_Order::getInstance());
$debitor = $order->getForeignRecordBreakNull('debitor_id', Billing_Controller_Debitor::getInstance());
if ($debitor) {
$debitorId = $debitor->getId();
$state = 'ORANGE';
$openItems = Billing_Controller_OpenItem::getInstance()->getByDebitor($debitorId);
foreach ($openItems as $op) {
if ($op->__get('state') != 'DONE') {
$openItem = $op;
break;
}
}
//$openItem = $openItems->getFirstRecord();
$openItemId = $openItem->getId();
$opAmount = $openItem->__get('open_sum');
}
if (abs($amount) > abs($opAmount)) {
$overpay = abs($amount) - abs($opAmount);
}
if ($overpay == 0) {
$state = 'GREEN';
}
/*if((float)abs($openItem->__get('total_brutto')) == (float)abs($amount)){
$state = 'GREEN';
}*/
} catch (Exception $e) {
}
} elseif (strpos($cdesc, 'mitg')) {
$memberNr = $num;
try {
$member = Membership_Controller_SoMember::getInstance()->getSoMemberByMemberNr($memberNr);
$contactId = $member->getForeignId('contact_id');
$debitorId = Billing_Controller_Debitor::getInstance()->getIdByContactId($contactId);
$state = 'ORANGE';
$openItems = Billing_Controller_OpenItem::getInstance()->getByDebitor($debitorId);
foreach ($openItems as $op) {
if ($op->__get('state') != 'DONE') {
$openItem = $op;
break;
}
}
//$openItem = $openItems->getFirstRecord();
if ($openItem) {
$openItemId = $openItem->getId();
$opAmount = $openItem->__get('open_sum');
if (abs($amount) > abs($opAmount)) {
$overpay = abs($amount) - abs($opAmount);
}
if ($overpay == 0) {
$state = 'GREEN';
}
}
/*if((float)abs($openItem->__get('total_brutto')) == (float)abs($amount)){
$state = 'GREEN';
}*/
} catch (Exception $e) {
}
} else {
try {
$contactId = (int) trim($num);
$debitorId = Billing_Controller_Debitor::getInstance()->getIdByContactId($contactId);
$state = 'ORANGE';
$openItems = Billing_Controller_OpenItem::getInstance()->getByDebitor($debitorId);
foreach ($openItems as $op) {
if ($op->__get('state') != 'DONE') {
$openItem = $op;
break;
}
}
// changed behviour: was only correct in first period :-/
//$openItem = $openItems->getFirstRecord();
if ($openItem) {
$openItemId = $openItem->getId();
$opAmount = $openItem->__get('open_sum');
}
if (abs($amount) > abs($opAmount)) {
$overpay = abs($amount) - abs($opAmount);
}
if ($overpay == 0) {
$state = 'GREEN';
}
//if((float)abs($openItem->__get('total_brutto')) == (float)abs($amount)){
// $state = 'GREEN';
//}
} catch (Exception $e) {
}
}
}
if ($isReturnDebit) {
$returnDebitFee = (double) Tinebase_Core::getPreference('Billing')->getValue(Billing_Preference::DEBIT_RETURN_FEE);
if ($num) {
$contactId = (int) trim($num);
$debitorId = null;
try {
$debitorId = Billing_Controller_Debitor::getInstance()->getIdByContactId($contactId);
$state = 'ORANGE';
} catch (Exception $e) {
$state = 'RED';
}
try {
$toReturnPayment = Billing_Controller_Payment::getInstance()->getDebitToReturn($debitorId, $amount, $returnDebitFee, 'DEBIT');
$returnPaymentId = $toReturnPayment->getId();
if ($returnPaymentId && $debitorId && $toReturnPayment->__get('is_cancelled') == false) {
$state = 'GREEN';
}
if ($toReturnPayment->__get('is_cancelled') == true) {
$returnPaymentId = null;
}
} catch (Exception $e) {
$returnPaymentId = null;
}
}
}
$id = md5($cdesc);
try {
$payment = Billing_Controller_MT940Payment::getInstance()->get($id);
} catch (Exception $e) {
$payment = Billing_Controller_MT940Payment::getInstance()->getEmptyMT940Payment();
$bankAccount = Billing_Controller_AccountSystem::getInstance()->getDefaultBankAccount();
$fordAccount = Billing_Controller_AccountSystem::getInstance()->getByNumber('14000');
if (!$isReturnDebit) {
if ($type == 'DEBIT') {
$payment->__set('account_system_id', $bankAccount->getId());
$payment->__set('account_system_id_haben', $fordAccount->getId());
} else {
$payment->__set('account_system_id', $fordAccount->getId());
$payment->__set('account_system_id_haben', $bankAccount->getId());
}
} else {
$debitSettleAccount = Tinebase_Core::getPreference('Billing')->getValue(Billing_Preference::FIBU_KTO_DEBITOR);
$payment->__set('account_system_id', $debitSettleAccount);
$payment->__set('account_system_id_haben', $bankAccount->getId());
$payment->__set('is_return_debit', true);
$payment->__set('print_inquiry', true);
$payment->__set('set_accounts_banktransfer', true);
$payment->__set('return_inquiry_fee', $returnDebitFee);
$payment->__set('return_debit_base_payment_id', $returnPaymentId);
}
$payment->__set('id', $id);
$payment->__set('erp_context_id', $context);
if ($opNr) {
$payment->__set('op_nr', $opNr);
}
if ($openItemId) {
$payment->__set('op_id', $openItemId);
$payment->__set('due_date', new Zend_Date($openItem->__get('due_date')));
$payment->__set('op_amount', $openItem->__get('total_brutto'));
}
if ($debitorId) {
$payment->__set('debitor_id', $debitorId);
}
$payment->__set('type', $type);
$payment->__set('payment_date', new Zend_Date($transaction->getBookDate()->format('Y-m-d H:i:s')));
$payment->__set('payment_amount', abs($amount));
$payment->__set('state', $state);
$payment->__set('usage_payment', $description);
$payment->__set('usage', $numDesc);
$payment->__set('overpay_amount', $overpay);
Billing_Controller_MT940Payment::getInstance()->create($payment);
}
}
/*echo $statement->getOpeningBalance()->getAmount() . "\n";
foreach ($statement->getTransactions() as $transaction) {
echo $transaction->getAmount() . "\n";
}
echo $statement->getClosingBalance()->getAmount() . "\n";*/
}
} catch (Exception $e) {
Tinebase_Core::getLogger()->notice(__METHOD__ . '::' . __LINE__ . ' ' . 'TD-Import - Es trat ein Fehler auf. Nicht ausgeführt.' . print_r($e->__toString(), true));
$result['status'] = 'error';
$result['errorInfo'] = $e->getMessage();
$result['errorTrace'] = $e->getTrace();
}
return $result;
}
private function createReceipt()
{
try {
$receipt = $this->_receiptController->get($this->receiptId);
//$receiptPositions = $this->_receiptController->getOrderPositions($this->receiptId)->toArray();
$receiptPositions = $receipt->getPositions()->toArray();
if (!$receipt->isMonition()) {
$receiptSumValues = $this->_receiptController->getReceiptSumValues($this->receiptId);
} else {
$receiptSumValues = array();
}
$orderId = $receipt->__get('order_id');
$order = Billing_Controller_Order::getInstance()->get($orderId);
$contact = $order->__get('debitor_id')->__get('contact_id');
$contactId = $contact['id'];
$contact = Addressbook_Controller_Contact::getInstance()->get($contactId);
$receiptId = $receipt->id;
$this->map[$contactId][] = $receiptId;
$this->sortedMap[] = array($contactId => $receiptId);
$this->count += 1;
$templateId = $this->templateId;
if (!Billing_Custom_Template::isToPrint($receipt, $this->matchReceiptType($receipt->__get('type')), &$templateId)) {
--$this->count;
}
// get data for template from custom template
$replaceTextBlocks = $this->templateController->getTextBlocks($templateId);
$user = $receipt->__get('created_by');
$userContact = Addressbook_Controller_Contact::getInstance()->getContactByUserId($user->getId());
$data = $this->getData($receipt->__get('type'), array('contact' => $contact, 'receipt' => $receipt, 'order' => $order, 'debitor' => $order->__get('debitor_id'), 'positions' => $receiptPositions, 'sums' => $receiptSumValues, 'user' => Tinebase_Core::get(Tinebase_Core::USER), 'currentuser' => Tinebase_Core::getUser(), 'user' => $receipt->__get('created_by'), 'userContact' => $userContact), $replaceTextBlocks);
$tempInFile = $this->tempFilePath . md5(serialize($receipt) . microtime()) . '_in.odt';
$tempOutFile = $this->tempFilePath . md5(serialize($receipt) . microtime()) . '_out.odt';
$this->templateController->renderTemplateToFile($templateId, $data, $tempInFile, $tempOutFile, $replaceTextBlocks);
// move file into storage: cleans up tempfile at once
$this->printJobStorage->moveIn($tempOutFile, "//in/{$contactId}/{$receiptId}/odt/temp");
//$watermarkFile = '/srv/www/vhosts/dev/projects/sopen/web/vdst/customize/data/template/documents/preview.pdf';
if (!$this->preview && !$this->copy) {
$receipt->__set('print_date', new Zend_Date(strftime('%Y-%m-%d')));
$receipt->flatten();
$this->_receiptController->update($receipt);
}
if (Membership_Api_JobManager::getInstance()->hasJob()) {
Membership_Api_JobManager::getInstance()->updateJobFromArray(array('job_name2', 'PRINTING... ' . $count));
}
} catch (Exception $e) {
throw $e;
//
//echo $e->__toString();
//throw new Billing_Exception_Order("Creating receipt document failed. - order: ".$this->receiptId, 0, $e);
}
}
/**
* inspect update of one record
*
* @param Tinebase_Record_Interface $_record the update record
* @param Tinebase_Record_Interface $_oldRecord the current persistent record
* @return void
*/
protected function _inspectUpdate($_record, $_oldRecord)
{
if ($_record->__get('order_id')) {
$order = $_record->getForeignRecord('order_id', Billing_Controller_Order::getInstance());
if ($order->__get('payment_state') != $_record->__get('payment_state')) {
$order->__set('payment_state', $_record->__get('payment_state'));
Billing_Controller_Order::getInstance()->update($order);
}
}
$openItem = null;
if ($_record->__get('payment_state') == 'PAYED') {
try {
$openItem = Billing_Controller_OpenItem::getInstance()->getByReceiptId($_record->getId());
if ($openItem->__get('state') != 'DONE') {
$openItem->__set('state', 'DONE');
$openItem->__set('open_sum', 0);
$openItem->__set('payed_sum', $_record->__get('total_brutto'));
$openItem->__set('payment_method_id', $_record->getForeignId('payment_method_id'));
}
} catch (Exception $e) {
// silent failure, as open item might not exist already
}
}
if ($_record->isCancelled()) {
if (is_null($openItem)) {
$openItem = Billing_Controller_OpenItem::getInstance()->getByReceiptId($_record->getId());
}
$openItem->__set('is_cancelled', true);
$reversionReceiptId = $_record->getForeignId('reversion_record_id');
try {
$reversionOpenItem = Billing_Controller_OpenItem::getInstance()->getByReceiptId($reversionReceiptId);
$openItem->__set('open_sum', $openItem->__get('total_brutto') + $reversionOpenItem->__get('total_brutto'));
$openItem->__set('payed_sum', -1 * $reversionOpenItem->__get('payed_sum'));
$openItem->__set('reversion_record_id', $reversionOpenItem->getId());
} catch (Exception $e) {
//failure OK: open item for reversion record might not already exist
// will be set in on of the next update calls, as soon, the open item exists.
}
}
if (!$_record->getForeignIdBreakNull('payment_method_id')) {
$_record->__set('payment_method_id', 'BANKTRANSFER');
}
if ($openItem) {
if (!$openItem->getForeignIdBreakNull('payment_method_id')) {
$openItem->__set('payment_method_id', 'BANKTRANSFER');
}
Billing_Controller_OpenItem::getInstance()->update($openItem);
}
}
/**
* Returns registry data of addressbook.
* @see Tinebase_Application_Json_Abstract
*
* @return mixed array 'variable name' => 'data'
*/
public function getRegistryData()
{
$filter = new Tinebase_Model_ImportExportDefinitionFilter(array(array('field' => 'plugin', 'operator' => 'equals', 'value' => 'Billing_Import_BankCsv')));
$importDefinitions = Tinebase_ImportExportDefinition::getInstance()->search($filter);
//print_r($importDefinitions);
try {
$defaultDefinitionArray = Tinebase_ImportExportDefinition::getInstance()->getByName('bank_import_csv')->toArray();
} catch (Tinebase_Exception_NotFound $tenf) {
if (count($importDefinitions) > 0) {
$defaultDefinitionArray = $importDefinitions->getFirstRecord()->toArray();
} else {
Tinebase_Core::getLogger()->warn(__METHOD__ . '::' . __LINE__ . ' No import definitions found for Banks');
$defaultDefinitionArray = array();
}
}
$registryData = array('Order' => Billing_Controller_Order::getInstance()->getRegistryData(), 'StockLocation' => Billing_Controller_StockLocation::getInstance()->getRegistryData(), 'PaymentMethods' => $this->getPaymentMethodsAsSimpleArray(), 'Context' => $this->getContextsAsSimpleArray(), 'ArticleGroups' => Billing_Controller_ArticleGroup::getInstance()->getArticleGroupsAsSimpleArray(), 'DebitorGroups' => Billing_Controller_DebitorGroup::getInstance()->getDebitorGroupsAsSimpleArray(), 'ArticleSeriess' => Billing_Controller_ArticleSeries::getInstance()->getArticleSeriessAsSimpleArray(), 'AccountSystems' => Billing_Controller_AccountSystem::getInstance()->getAccountSystemsAsSimpleArray(), 'AccountClasss' => Billing_Controller_AccountClass::getInstance()->getAccountClasssAsSimpleArray(), 'defaultBankImportDefinition' => $defaultDefinitionArray, 'importDefinitions' => array('results' => $importDefinitions->toArray(), 'totalcount' => count($importDefinitions)));
return $registryData;
}
private function createReceipt()
{
$receipt = $this->_receiptController->get($this->receiptId);
$receiptPositions = $this->_receiptController->getOrderPositions($this->receiptId)->toArray();
$receiptSumValues = $this->_receiptController->getReceiptSumValues($this->receiptId);
$receiptId = $receipt->id;
$this->map[] = $receiptId;
$orderId = $receipt->__get('order_id');
$order = Billing_Controller_Order::getInstance()->get($orderId);
$contact = $order->__get('debitor_id')->__get('contact_id');
$contactId = $contact['id'];
$contact = Addressbook_Controller_Contact::getInstance()->get($contactId);
$this->count += 1;
if (!Billing_Custom_Template::isToPrint($receipt, $this->matchReceiptType($receipt->__get('type')), &$templateId)) {
--$this->count;
}
// get data for template from custom template
$replaceTextBlocks = $this->templateController->getTextBlocks($templateId);
$data = $this->getData($receipt->__get('type'), array('contact' => $contact, 'receipt' => $receipt, 'order' => $order, 'debitor' => $order->__get('debitor_id'), 'positions' => $receiptPositions, 'sums' => $receiptSumValues, 'user' => Tinebase_Core::getUser()), $replaceTextBlocks);
$tempInFile = $this->tempFilePath . md5(serialize($receipt) . microtime()) . '_in.odt';
$tempOutFile = $this->tempFilePath . md5(serialize($receipt) . microtime()) . '_out.odt';
$this->templateController->renderTemplateToFile($templateId, $data, $tempInFile, $tempOutFile, $replaceTextBlocks);
// move file into storage: cleans up tempfile at once
$this->printJobStorage->moveIn($tempOutFile, "//in/receipt/{$receiptId}/odt/temp");
//$watermarkFile = '/srv/www/vhosts/dev/projects/sopen/web/vdst/customize/data/template/documents/preview.pdf';
if ($this->printJobStorage->fileExists("//in/receipt/{$receiptId}/odt/temp")) {
$inFile = $this->printJobStorage->resolvePath("//in/receipt/{$receiptId}/odt/temp");
$outFile = $this->printJobStorage->getCreateIfNotExist("//out/result/{$receiptId}/pdf/final");
$this->pdfServer->convertDocumentToPdf($inFile, $outFile);
//$previewOutFile = $this->printJobStorage->getCreateIfNotExist( "//out/preview/$receiptId/pdf/final" );
//$this->pdfServer->watermarkPdf($outFile, $watermarkFile, $previewOutFile);
}
}
public function onBillableReceiptCreated($receipt)
{
$receipt->flatten();
$openItem = new Billing_Model_OpenItem(null, true);
//$order = Billing_Controller_Order::getInstance()->get($receipt->__get('order_id'));
$order = $receipt->getForeignRecord('order_id', Billing_Controller_Order::getInstance());
$debitorId = $order->getForeignId('debitor_id');
$openItem->__set('order_id', $receipt->__get('order_id'));
$openItem->__set('receipt_id', $receipt->getId());
$openItem->__set('debitor_id', $debitorId);
$openItem->__set('payment_method_id', $receipt->getForeignId('payment_method_id'));
$rNr = $receipt->__get('invoice_nr');
$rDate = $receipt->__get('invoice_date');
$openItem->__set('is_cancellation', $receipt->__get('is_cancellation'));
$openItem->__set('is_cancelled', $receipt->__get('is_cancelled'));
if ($receipt->__get('is_cancellation')) {
$openItem->__set('state', 'DONE');
}
if ($receipt->isCredit()) {
$rNr = $receipt->__get('credit_nr');
$rDate = $receipt->__get('credit_date');
$openItem->__set('type', 'CREDIT');
} else {
$openItem->__set('type', 'DEBIT');
}
$openItem->__set('receipt_nr', $rNr);
$openItem->__set('receipt_date', $rDate);
$openItem->__set('total_netto', 0);
$openItem->__set('total_brutto', 0);
// TODO: check this -->
// $openItem->__set('open_sum', $receipt->__get('total_brutto'));
// changed to:
$openItem->__set('open_sum', $receipt->__get('open_sum'));
// <--
$openItem->__set('payed_sum', $receipt->__get('payed_sum'));
$openItem->__set('erp_context_id', $receipt->__get('erp_context_id'));
$openItem->__set('usage', $receipt->__get('usage'));
if ($receipt->isDonation()) {
$openItem->__set('donation_id', $receipt->getForeignId('donation_id'));
}
$this->create($openItem);
}
public function getBookingData($fibuKto)
{
$orderPositions = Billing_Controller_ReceiptPosition::getInstance()->getByReceiptId($this->getId());
$order = $this->getForeignRecord('order_id', Billing_Controller_Order::getInstance());
$debitorId = $order->getForeignId('debitor_id');
$totalBrutto = abs((double) $this->__get('total_brutto'));
$articleKtos = array();
foreach ($orderPositions as $orderPosition) {
$article = $orderPosition->getForeignRecordBreakNull('article_id', Billing_Controller_Article::getInstance());
$kto = $article->getForeignId('rev_account_vat_in');
$value = abs((double) $orderPosition->__get('total_brutto'));
if ($article->createsDonation()) {
$donAmount = abs($orderPosition->__get('amount')) * $article->__get('donation_amount');
$campaignId = $article->getForeignId('donation_campaign_id');
$value -= $donAmount;
$campaign = Donator_Controller_Campaign::getInstance()->get($campaignId);
$dErloesKto = $campaign->getForeignId('erp_proceed_account_id');
$articleKtos[] = array('kto' => $dErloesKto, 'value' => $donAmount, 'debitor' => $debitorId);
}
$articleKtos[] = array('kto' => $kto, 'value' => $value, 'debitor' => $debitorId);
}
if ($this->isInvoice()) {
return array('credits' => $articleKtos, 'debits' => array(array('kto' => $fibuKto, 'value' => $totalBrutto, 'debitor' => $debitorId)));
} else {
return array('debits' => $articleKtos, 'credits' => array(array('kto' => $fibuKto, 'value' => $totalBrutto, 'debitor' => $debitorId)));
}
}
public function onBillableReceiptBooked($receipt, $openItem, $booking)
{
if ($receipt->isCredit() || $receipt->isInvoice()) {
// suchen und neu oder update -> debitor account
$receipt = Billing_Controller_Receipt::getInstance()->get($receipt->getId());
$totalBrutto = $receipt->__get('total_brutto');
try {
$debitorAccount = Billing_Controller_DebitorAccount::getInstance()->getByReceiptId($receipt->getId());
if (!$debitorAccount) {
throw new Exception('');
}
$debitorAccount->__set('s_brutto', $totalBrutto);
Billing_Controller_DebitorAccount::getInstance()->update($debitorAccount);
} catch (Exception $e) {
$debitorAccount = Billing_Controller_DebitorAccount::getInstance()->getEmptyDebitorAccount();
$order = $receipt->getForeignRecord('order_id', Billing_Controller_Order::getInstance());
$debitorAccount->__set('receipt_id', $receipt->getId());
$debitorAccount->__set('debitor_id', $order->getForeignId('debitor_id'));
$debitorAccount->__set('erp_context_id', $receipt->__get('erp_context_id'));
$debitorAccount->__set('usage', $receipt->__get('usage'));
$debitorAccount->__set('booking_id', $booking->getId());
//$debitorAccount->__set('open_item_id', $openItem->getId());
$debitorAccount->__set('is_cancelled', $receipt->__get('is_cancelled'));
$debitorAccount->__set('is_cancellation', $receipt->__get('is_cancellation'));
if ($receipt->isInvoice()) {
$debitorAccount->__set('item_type', 'DEBIT');
$debitorAccount->__set('create_date', new Zend_Date($receipt->__get('invoice_date')));
$debitorAccount->__set('value_date', new Zend_Date($receipt->__get('invoice_date')));
$debitorAccount->__set('s_brutto', $totalBrutto);
}
if ($receipt->isCredit()) {
$debitorAccount->__set('item_type', 'CREDIT');
$debitorAccount->__set('create_date', new Zend_Date($receipt->__get('credit_date')));
$debitorAccount->__set('value_date', new Zend_Date($receipt->__get('credit_date')));
$debitorAccount->__set('h_brutto', $totalBrutto * -1);
}
$this->create($debitorAccount);
}
}
}
/**
*
* 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);
}
protected function appendDependentRecords($record)
{
if ($record->__get('debitor_id')) {
$this->appendForeignRecordToRecord($record, 'debitor_id', 'debitor_id', 'id', new Billing_Backend_Debitor());
$debitor = $record->__get('debitor_id');
try {
if (is_object($debitor)) {
$contactId = $debitor->__get('contact_id');
} else {
$contactId = $debitor->contact_id;
}
$contact = Addressbook_Controller_Contact::getInstance()->get($contactId);
if (is_object($debitor)) {
$debitor->__set('contact_id', $contact->toArray());
} else {
$debitor->contact_id = $contact->toArray();
}
} catch (Exception $e) {
}
$record->__set('debitor_id', $debitor);
}
if ($record->__get('order_id')) {
$this->appendForeignRecordToRecord($record, 'order_id', 'order_id', 'id', new Billing_Backend_Order());
$order = $record->__get('order_id');
try {
if ($order instanceof Billing_Model_Order) {
$debitorId = $order->__get('debitor_id');
} else {
$debitorId = $order->debitor_id;
}
$debitor = Billing_Controller_Debitor::getInstance()->get($debitorId);
if ($order instanceof Billing_Model_Order) {
$order->__set('debitor_id', $debitor->toArray());
} else {
$order->debitor_id = $debitor->toArray();
}
} catch (Exception $e) {
}
$record->__set('order_id', $order);
}
if ($record->__get('receipt_id')) {
$this->appendForeignRecordToRecord($record, 'receipt_id', 'receipt_id', 'id', new Billing_Backend_Receipt());
$receipt = $record->getForeignRecordBreakNull('receipt_id', Billing_Controller_Receipt::getInstance());
if ($receipt) {
try {
if ($receipt instanceof Billing_Model_Order) {
$orderId = $receipt->__get('order_id');
} else {
$orderId = $receipt->order_id;
}
$order = Billing_Controller_Order::getInstance()->get($orderId);
if ($order instanceof Billing_Model_Order) {
$receipt->__set('order_id', $order->toArray());
} else {
$receipt->order_id = $order->toArray();
}
} catch (Exception $e) {
}
$record->__set('receipt_id', $receipt);
}
}
if ($record->__get('account_system_id')) {
$this->appendForeignRecordToRecord($record, 'account_system_id', 'account_system_id', 'id', new Billing_Backend_AccountSystem());
}
if ($record->__get('account_system_id_haben')) {
$this->appendForeignRecordToRecord($record, 'account_system_id_haben', 'account_system_id_haben', 'id', new Billing_Backend_AccountSystem());
}
if ($record->__get('booking_id')) {
$this->appendForeignRecordToRecord($record, 'booking_id', 'booking_id', 'id', new Billing_Backend_Booking());
}
if ($record->__get('account_booking_id')) {
$this->appendForeignRecordToRecord($record, 'account_booking_id', 'account_booking_id', 'id', new Billing_Backend_AccountBooking());
}
if ($record->__get('payment_method_id')) {
$this->appendForeignRecordToRecord($record, 'payment_method_id', 'payment_method_id', 'id', new Billing_Backend_PaymentMethod());
}
if ($record->__get('open_item_id')) {
$this->appendForeignRecordToRecord($record, 'open_item_id', 'open_item_id', 'id', new Billing_Backend_OpenItem());
}
if ($record->__get('return_debit_base_payment_id')) {
$this->appendForeignRecordToRecord($record, 'return_debit_base_payment_id', 'return_debit_base_payment_id', 'id', new Billing_Backend_Payment());
}
/*if($record->__get('batch_job_dta_id')){
$this->appendForeignRecordToRecord($record, 'batch_job_dta_id', 'batch_job_dta_id', 'id', new Billing_Backend_BatchJobDta());
} */
}
