/**
* add some more values (container id)
*
* @return array
*/
protected function _addData($recordData)
{
$result = array();
$aC = Billing_Controller_ArticleGroup::getInstance();
$aGroup = $aC->getByName('Urkunden');
$pC = Billing_Controller_PriceGroup::getInstance();
$pGDiver = $pC->getByName('Taucher');
$pGMember = $pC->getByName('Mitglied');
$pGMTI = $pC->getByName('MTI');
$pGMTA = $pC->getByName('MTA');
$mwst = floor(($recordData['VK1'] / $recordData['VK1EXKL'] - 1) * 100);
if ($mwst > 8) {
$mwst = 19;
} elseif ($mwst < 7) {
$mwst = 0;
}
$vatId = Billing_Controller_Vat::getInstance()->getByName($mwst);
$articleUnit = Billing_Controller_ArticleUnit::getInstance()->getByName('Stück');
if ($mwst == 19) {
$recordData['VK1EXKL'] = $recordData['VK1'] / 1.19;
$recordData['VK2EXKL'] = $recordData['VK2'] / 1.19;
$recordData['VK3EXKL'] = $recordData['VK3'] / 1.19;
}
$priceGroups['DIVER'] = array('id' => $pGDiver->getId(), 'netto' => $recordData['VK1EXKL'], 'brutto' => $recordData['VK1']);
$priceGroups['MEMBER'] = array('id' => $pGMember->getId(), 'netto' => $recordData['VK2EXKL'], 'brutto' => $recordData['VK2']);
$priceGroups['MTI'] = array('id' => $pGMTI->getId(), 'netto' => $recordData['VK3EXKL'], 'brutto' => $recordData['VK3']);
$priceGroups['MTA'] = array('id' => $pGMTA->getId(), 'netto' => $recordData['VK3EXKL'], 'brutto' => $recordData['VK3']);
$result['article_group_id'] = $aGroup->getId();
$result['prices'] = $priceGroups;
$result['vat_id'] = $vatId;
$result['article_unit_id'] = $articleUnit->getId();
return $result;
}
/**
* add some more values (container id)
*
* @return array
*/
protected function _addData($recordData)
{
$result = array();
$priceGroupId = 1;
$articleGroupId = null;
if ($recordData['article_ext_nr']) {
$isbn = $recordData['article_ext_nr'];
if (strpos($isbn, 'WS')) {
$articleGroupId = 5;
} elseif (strpos($isbn, 'WK')) {
$articleGroupId = 4;
} elseif (strpos($isbn, 'OG')) {
$articleGroupId = 3;
} elseif (strpos($isbn, 'FB')) {
$articleGroupId = 2;
} elseif (strpos($isbn, 'EB')) {
$articleGroupId = 1;
}
}
if (!$articleGroupId) {
$articleGroupId = 1;
}
$result['article_group_id'] = $articleGroupId;
$pC = Billing_Controller_PriceGroup::getInstance();
$pGEmpf = $pC->getByName('Empfohlen');
$pGMitgl = $pC->getByName('Mitglied');
$pGEntn = $pC->getByName('Entnahme');
$vatId = Billing_Controller_Vat::getInstance()->getByName(7);
$articleUnit = Billing_Controller_ArticleUnit::getInstance()->getByName('Stück');
$recordData['mitgl_preis'] = str_replace(',', '.', $recordData['mitgl_preis']);
$recordData['entn_preis'] = str_replace(',', '.', $recordData['entn_preis']);
$recordData['empf_preis'] = str_replace(',', '.', $recordData['empf_preis']);
if (!is_numeric($recordData['mitgl_preis'])) {
$recordData['mitgl_preis'] = 0;
} else {
$recordData['mitgl_preis'] = abs((double) $recordData['mitgl_preis']);
}
if (!is_numeric($recordData['entn_preis'])) {
$recordData['entn_preis'] = 0;
} else {
$recordData['entn_preis'] = abs((double) $recordData['entn_preis']);
}
if (!is_numeric($recordData['empf_preis'])) {
$recordData['empf_preis'] = 0;
} else {
$recordData['empf_preis'] = abs((double) $recordData['empf_preis']);
}
$priceGroups['MITGL'] = array('id' => $pGMitgl->getId(), 'netto' => $recordData['mitgl_preis'], 'brutto' => $recordData['mitgl_preis'] * 1.07);
$priceGroups['ENTN'] = array('id' => $pGEntn->getId(), 'netto' => $recordData['entn_preis'], 'brutto' => $recordData['entn_preis'] * 1.07);
$priceGroups['EMPF'] = array('id' => $pGEmpf->getId(), 'netto' => $recordData['empf_preis'], 'brutto' => $recordData['empf_preis'] * 1.07);
$result['prices'] = $priceGroups;
$result['vat_id'] = $vatId;
$result['article_unit_id'] = $articleUnit->getId();
return $result;
}
/**
* (non-PHPdoc)
* @see Tinebase_Controller_Record_Abstract::_inspectCreate()
*/
protected function _inspectCreate(Tinebase_Record_Interface $_record)
{
// check whether the article nr is set? -> then do not overwrite: maybe from export...
if (!$_record->__get('article_nr')) {
$_record->__set('article_nr', Tinebase_NumberBase_Controller::getInstance()->getNextNumber('article_nr'));
}
if (!$_record->__get('price2_vat_id')) {
$vat0 = Billing_Controller_Vat::getInstance()->getByName(0);
$_record->__set('price2_vat_id', $vat0->getId());
}
}
public function saveVat($recordData)
{
return $this->_save($recordData, Billing_Controller_Vat::getInstance(), 'Vat');
}
/**
*
* Get prices according to price groups
* @param Tinebase_Model_Record $record
*/
public function getPricesForPriceGroups($record)
{
$articleId = $record->__get('id');
$sellPriceBackend = new Billing_Backend_SellPrice();
$prices = $sellPriceBackend->getByArticleId($articleId);
$result = array();
foreach ($prices as $price) {
$result[$price->__get('price_group_id')->__get('id')] = array('name' => $price->__get('price_group_id')->__get('name'), 'price_netto' => $price->__get('price_netto'), 'price_brutto' => $price->__get('price_brutto'), 'price2_netto' => 0, 'price2_brutto' => 0, 'price2_vat_id' => Billing_Controller_Vat::getInstance()->getByName('0'));
}
return $result;
}
/**
*
* 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);
}
public function validate($receiptPosition)
{
$valid = false;
$vat = $receiptPosition->__get('vat_id');
if ($vat instanceof Billing_Model_Vat) {
} elseif (is_numeric($vat)) {
$vat = Billing_Controller_Vat::getInstance()->get($vat);
} elseif (is_array($vat) && array_key_exists('id', $vat)) {
$vat = Billing_Controller_Vat::getInstance()->get($vat['id']);
} else {
throw new Billing_Exception_OrderPosition('Invalid vat');
}
$vatValue = $vat->__get('value');
if (is_numeric($vatValue)) {
$valid = true;
}
$priceNetto = $receiptPosition->__get('price_netto');
if (!is_numeric($priceNetto)) {
$valid = false;
//throw new Billing_Exception_OrderPosition('No netto price');
}
$amount = $receiptPosition->__get('amount');
if (!is_numeric($amount)) {
//throw new Billing_Exception_OrderPosition('Missing amount');
$valid = false;
}
if (!$valid) {
throw new Billing_Exception_OrderPosition('Receipt position is invalid' . print_r($receiptPosition, true));
}
}