Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 349

Přidáno uživatelem Tomáš Dulík před asi 15 roky(ů)

Do importu dodelany statistiky, opraveno par bugu.
Pridana funkce pro generovani/kontrolovani variabilnich symbolu pomoci CRC16 z member_id zatim vyrazena, vypocet CRC16 je vadny.
Opravena funkce pro vypis neidentifikovane platby.
Pridany indexy na sloupce, kde indexy chybely.

Zobrazit rozdíly:

freenetis/trunk/kohana/application/models/member.php
return $query->current();
}
/**
* @author Tomas Dulik, using http://us3.php.net/manual/en/function.crc32.php#86628
* CRC-CCITT-16 algorithm for the polynomial 0x1021
*
* Tomas Dulik note: the algorithm is too nice to be true.
* The values it computes differ from the values returned by other calculators, e.g.
* http://zorc.breitbandkatze.de/crc.html
* @todo: rewrite this function for a proper implementation!!!
*
* This function can be used for generating the payments variable symbol from member id.
* crc16 can check error bursts up to 16bits long, so if the member mistypes such
* a generated variable symbol, almost any possible error should be detected.
* @param $data - 16bit integer for which crc16 should be computed
* @return 16bit int value containing crc16
*/
public function crc16($data) {
$crc = 0xFFFF;
for ($i = 0; $i < 2; $i++) {
$x = (($crc >> 8) ^ $data) & 0xFF;
$data=$data >> 8;
$x ^= $x >> 4;
$crc = (($crc << 8) ^ ($x << 12) ^ ($x << 5) ^ $x) & 0xFFFF;
}
return $crc;
}
/**
* @author Tomas Dulik
* This function can be used for generating variable symbols from member id
* @param $member_id
* @return string containing concatenation of member_id and 5 digits of its crc16
*/
public function make_variable_symbol($member_id) {
return $member_id . sprintf("%5d", crc16($member_id));
}
/**
* @author Tomas Dulik
* This function can be used for checking the validity of payment variable symbols
* and finding the related member.
* If the variable symbol was generated from member id using crc16 function,
* then it is easy to check if the variable symbol is OK and extract the member id from it.
* @param $vs - string containing variable symbol (concatenation of member id and its crc16)
* @return object containing the member
*/
public function get_member_by_crc_id($vs) {
if (($vs_len=strlen($vs))>5) { //
$member_id=(int)substr($vs, 0, $vs_len-5);
$crc=$this->crc16($member_id);
$vs_crc=(int)substr($vs, $vs_len-5, 5);
if ($crc==$vs_crc)
$member=$this->find($member_id);
}
return $this;
}
/**
* Function gets fee information of member. Member is specified by his credit account id.
freenetis/trunk/kohana/application/models/bank_transfer.php
}
/**
* @author Jiri Svitak
* @author Jiri Svitak, Tomas Dulik
* It gets all unidentified bank transfers from db. Unidentified transfer is that with bank info only
* and no other transfer has set previous_transfer_id to this transfer.
* @return unknown_type
* @todo this function is very slow and gets slower as the payments database grows.
* quick fix = make a new boolean column "unasigned" into transfers,
* this query would then serve only for database consistency check purposes
*/
public function get_unidentified_transfers($limit_from = 0, $limit_results = 500, $order_by = 'id', $order_by_direction = 'asc')
{
if ($order_by == 'amount')
$order_by = 'IF( t.destination_id = '.$account_id.', amount, amount*-1 )';
return self::$db->query("SELECT t.id, t.datetime, t.amount, t.text,
bt.variable_symbol,
CONCAT(ba.account_nr, '/', ba.bank_nr) AS account_number
FROM transfers t
JOIN bank_transfers bt ON bt.transfer_id = t.id
LEFT JOIN bank_accounts ba ON ba.id = bt.origin_id
LEFT JOIN accounts a ON a.id = t.origin_id
WHERE t.id NOT IN
(SELECT previous_transfer_id
FROM transfers t
WHERE t.previous_transfer_id IS NOT NULL)
AND a.account_attribute_id = ".Account_attribute_Model::$member_fees."
// srct contains all source transactions, asst contains all transfers assigned to credit accounts
$q="SELECT srct.id, srct.datetime, srct.amount, srct.text, bt.variable_symbol, CONCAT( ba.account_nr, '/', ba.bank_nr ) AS account_number
FROM transfers srct
JOIN accounts na ON na.id = srct.origin_id
AND na.account_attribute_id =".Account_attribute_Model::$member_fees."
JOIN bank_transfers bt ON bt.transfer_id = srct.id
JOIN bank_accounts ba ON ba.id = bt.origin_id
WHERE srct.id NOT
IN (
SELECT previous_transfer_id
FROM transfers asst
JOIN accounts ac ON ac.id = asst.destination_id
AND ac.account_attribute_id =".Account_attribute_Model::$credit."
)
ORDER BY ".$order_by." ".$order_by_direction."
LIMIT ".$limit_from." , ".$limit_results
);
}
LIMIT ".$limit_from." , ".$limit_results;
return self::$db->query($q);
}
/**
* Function gets count of unidentified transfers
* @return unknown_type
freenetis/trunk/kohana/application/controllers/accounts.php
* Používá se v callback funkci store_transfer
*/
protected $parsed_bank_acc = NULL;
protected $time_now;
protected $stats;
/**
* @author Jiri Svitak
* It shows all double-entry accounts. They are shown by selected group in filter.
......
}
}
public $linenr=0;
/**
* @author Tomas Dulik
* Funkce store_transfers_ebanka se používá jako callback funkce pro Parser_Ebanka.
......
/** zde jsou statické objekty, jejichž instance tvořím jen jednou u importu prvního řádku
* výpisu (šetříme paměť...)
* */
static $time_now;
static $acc_model;
static $bank_acc_model;
static $member_model;
......
static $operating;
static $member_fees;
static $first_pass=true;
$this->stats->linenr++;
if ($first_pass) { // dostavame prvni radek vypisu?
$time_now=date("Y-m-d H:i:s");
$this->stats=new stdClass();
$this->time_now=date("Y-m-d H:i:s");
$member_model=new Member_Model(); // vytvorime vsechny instance, ktere potrebujeme i pro dalsi radky
$acc_model=new Account_Model();
$bank_acc_model=new Bank_account_Model();
......
}
// ********************** Tak a jdeme tvořit transakce *********************
if (empty($data->amount))
if (empty($data->amount)) {
// ****** Bankovní poplatky: ebanka má v řádku výpisu pouze poplatek, ale castka==0
// vytvoříme transakci "bankovní poplatek z 221000 (bank. účty) na 549001 (bank. poplatky)
//a bankovní transakci z parsovaného účtu na null
$this->create_transfers($parsed_acc, $bank_fees, abs($data->fee), $time_now,
if (empty($data->comment)) $data->comment=$data->type;
$fee=abs($data->fee);
$this->create_transfers($parsed_acc, $bank_fees, $fee,
$this->parsed_bank_acc, null, $data);
$this->stats->bank_fees+=$fee;
$this->stats->bank_fees_nr++;
}
else // amount je nenulovy
if (!empty($data->amount) && empty($data->fee)) {
// ***** úroky u ebanky: částka!=0 a poplatek==0
// Vytvoříme transakci z 644000 (uroky) na 221000
// a bankovní transakci z null na parsovaný účet
$this->create_transfers($bank_interests, $parsed_acc, $data->amount, $time_now,
$this->create_transfers($bank_interests, $parsed_acc, $data->amount,
null, $this->parsed_bank_acc, $data);
$this->stats->interests += $data->amount;
$this->stats->interests_nr++;
} else {
// ****** castka!=0 && poplatek !=0 - u ebanky nejběžnější případ: ******
// ****** členský příspěvek nebo platba faktury dodavatelum. ******
// Nejdriv zkusím najít majitele bankovního protiúčtu
$member_model->clear();
$member=$member_model;
if ($data->amount>0) {
$vs=trim($data->variable_symbol);
if ($data->amount>0 && !empty($vs)) {
// členský příspěvek: uvedl člen u teto platby jako variabilni symbol (VS) svůj telefon ?
$member=$member_model->find_member_by_phone($data->variable_symbol);
if ($member===FALSE) // find_member_by_phone vrací false, pokud nic nenajde
// ne? zkusime, zda jako VS nezadal svoje member id
$member=$member_model->find($data->variable_symbol);
// pokud se to nepovedlo, pak $member->id nastavíme na null
$member=$member_model->find_member_by_phone($vs);
if ($member===FALSE ) { // find_member_by_phone vrací false, pokud nic nenajde
// ne? zkusime, zda jako VS nezadal svoje member_id + crc16(member_id)
// $member=$member_model->get_member_by_crc_id($vs); // this function has bugs!!! needs rewrite!!!
$member=$member_model;
$this->stats->unasigned++;
}
// pokud se to nepovedlo, pak $member->id nastavíme na null
} // else { // platba přijaté faktury - majitele účtu najdeme dle VS na faktuře, až budeme mít modul přijatých faktur}
// ***Tady si vytvorime instanci účtu clena (nebo dodavatele) z prave nacteneho vypisu:
$bank_acc=$bank_acc_model->find_by_account_nr_and_bank_nr($data->account_nr, $data->account_bank_nr);
......
$bank_acc->account_nr = $data->account_nr;
$bank_acc->bank_nr = $data->account_bank_nr;
$bank_acc->save();
$this->stats->new_bank_accounts++;
// $bank_acc->add_account($member_fees); //tuto vazbu bych tvořil jen pokud bych chtěl evidovat pohyby na bank. účtech členů
}
if ($data->amount<0) {
if (!$member->id && $bank_acc->member_id) // pokud se předtím nepodařilo najít majitele dle VS
$member = $member_model->find($bank_acc->member_id); // zkusím ho vzít odsud
// úhrada faktury - z 221000 (bank. účet) na 321000 (dodavatelé)
$id=$this->create_transfers($parsed_acc, $suppliers, abs($data->amount), $time_now,
$amount=abs($data->amount);
$id=$this->create_transfers($parsed_acc, $suppliers, $amount,
$this->parsed_bank_acc, $bank_acc, $data);
// a ještě vytvoříme transakce (podvojné+bankovní) bank. poplatku
// vytvoříme transakci "bankovní poplatek z 221000 (bank. účty) na 549001 (bank. poplatky)
//a bankovní transakci z parsovaného účtu na null
$this->create_transfers($parsed_acc, $bank_fees, abs($data->fee), $time_now,
$this->parsed_bank_acc, null, $data);
$data->comment = "Bank. poplatek". (!empty($data->comment) ? " ($data->comment)":"");
$fee=abs($data->fee);
$this->create_transfers($parsed_acc, $bank_fees, $fee,
$this->parsed_bank_acc, null, $data, $id);
$this->stats->invoices+=$amount;
$this->stats->invoices_nr++;
$this->stats->bank_fees+=$fee;
$this->stats->bank_fees_nr++;
} else { // $data->amount > 0
// členský příspěvek - vytvoříme:
// - podvojnou transakci z 684000 na 221000
// - bankovní transakci z bank. účtu člena na bank. účet sdružení
$id=$this->create_transfers($member_fees, $parsed_acc, $data->amount, $time_now,
$id=$this->create_transfers($member_fees, $parsed_acc, $data->amount,
$bank_acc, $this->parsed_bank_acc, $data);
// bankovní poplatek - vytvoříme:
// - podvojnou transakci z 221000 (bank. účty) na 549001 (bank. poplatky)
// - bankovní transakci z parsovaného účtu na null
$this->create_transfers($parsed_acc, $bank_fees, abs($data->fee), $time_now,
$this->parsed_bank_acc, null, $data);
$data->comment = "Bank. poplatek". (!empty($data->comment) ? " ($data->comment)":"");
$fee=abs($data->fee);
$this->stats->bank_fees+=$fee;
$this->stats->bank_fees_nr++;
$this->create_transfers($parsed_acc, $bank_fees, $fee,
$this->parsed_bank_acc, null, $data, $id);
// naše správní rada si vymyslela, že poplatek budeme dotovat z operačního účtu
// (pokud máte ve správní radě rozumnější lidi, tak tento řádek zakomentujte :-)
$this->create_transfer($operating, $parsed_acc,
abs($data->fee), $data->date_time, "Bank. poplatek hrazený sdružením", $id);
if ($member->id) { // našli jsme plátce?
// **** převedeme peníze členovi na jeho účet s kreditem
// ten účet ale musíme najít nebo vytvořit:
......
$credit_acc->name = $member->name;
$credit_acc->save();
}
$amount=$data->amount-abs($data->fee);
$this->create_transfer($parsed_acc, $acc_model,
$amount, $time_now, "Přiřazení platby");
$data->amount, $data->date_time, "Přiřazení platby", $id);
// **** teď se podíváme, jestli v té době sdružení účtovalo poplatek za zpracování platby:
$fee=$fee_model->get_by_date_type($data->date_time, 'transfer fee');
if (is_object($fee) && $fee->id) // ano? Pak poplatek strhneme z účtu
$this->create_transfer($credit_acc, $operating,
$fee->fee-$data->fee, $time_now, "Transakční poplatek");
$fee->fee, $data->date_time, "Transakční poplatek", $id);
} // if (is_object($member) && $member->id)
$this->stats->member_fees_nr++;
$this->stats->member_fees+=$data->amount;
} // else { // $data->amount > 0
} // else { // ****** castka!=0 && poplatek !=0
}
private function create_transfer($src, $dst, $amount, $datetime, $text, $prev_id=null) {
static $transfer;
static $transfer; // static variable saves a little bit of memory & time
if (!isset($transfer)) $transfer=new Transfer_Model();
$transfer->clear();
$transfer->amount = $amount;
$transfer->datetime = $datetime;
$transfer->creation_datetime=$this->time_now;
$transfer->origin_id = $src->id;
$transfer->destination_id = $dst->id;
$transfer->text= $text;
......
return $transfer->id;
}
private function create_transfers($src_acc, $dst_acc, $amount, $time_now,
$src_bank_acc, $dst_bank_acc, $data) {
static $transfer;
private function create_transfers($src_acc, $dst_acc, $amount,
$src_bank_acc, $dst_bank_acc, $data, $prev_id=null) {
static $transfer; // static variable saves a little bit of memory & time
static $bank_transfer;
if (!isset($transfer)) {
$transfer=new Transfer_Model();
......
$transfer->clear();
$transfer->amount = $amount;
$transfer->datetime = $data->date_time;
$transfer->creation_datetime=$this->time_now;
$transfer->origin_id = $src_acc->id;
$transfer->destination_id = $dst_acc->id;
$transfer->text= $data->comment;
$transfer->previous_transfer_id=$prev_id;
$transfer->save();
$bank_transfer->clear();
$bank_transfer->datetime=$time_now;
$bank_transfer->transfer_id = $transfer->id;
$bank_transfer->origin_id = isset($src_bank_acc)? $src_bank_acc->id : null;
$bank_transfer->destination_id = isset($dst_bank_acc)? $dst_bank_acc->id : null;
......
return $transfer->id;
}
public function print_row($data) {
print_r($data);
$this->linenr++;
echo "<tr>\n";
foreach ($data as $d)
echo " <td>$d</td>\n";
echo "</tr>\n";
}
/**
* @author Tomas Dulik
* @param $url - URL containing the file to parse
......
$this->parsed_bank_acc=new Bank_account_Model($bank_account_id);
//print_r ($parser);
echo "<table border=1>\n";
// $parser->parse("c:/txt/unart/ucto/vypisy/2007/09.html");
//$parser->parse("http://www.rb.cz/firemni-finance/transparentni-ucty/?root=firemni-finance&item1=transparentni-ucty&tr_acc=vypis&account_number=184932848");
......
}
//$parser->parse("http://localhost/2007.html");
//$parser->parse("c:/txt/unart/ucto/vypisy/2007/2007.html");
echo "<table border=1>\n";
foreach ($this->stats as $key=>$value)
echo "<tr><td>$key</td><td>$value</td></tr>";
echo "</table>\n";
echo "Linenr=$this->linenr<p>";
echo "Memory usage=".(memory_get_usage()-$mem)."<p>\n";
echo"parse - end: ".(microtime(true)-$start)."<p>\n";
//print_r($this);
freenetis/trunk/kohana/application/upgrade_sql/upgrade_sql.php
}
$upgrade_sql[get_SVN_rev()] = array(
"ALTER TABLE `wireless_settings` CHANGE `mode` `wmode` INT( 11 ) NOT NULL ",
"ALTER TABLE `members` ADD `organization_identifier` VARCHAR( 20 ) NULL DEFAULT NULL AFTER `external_type` "
"ALTER TABLE `transfers` ADD INDEX ( `previous_transfer_id` )",
"ALTER TABLE `bank_transfers` ADD INDEX ( `origin_id` )",
"ALTER TABLE `bank_transfers` ADD INDEX ( `destination_id` )",
"ALTER TABLE `bank_transfers` ADD INDEX ( `transfer_id` )",
"ALTER TABLE `bank_accounts` ADD INDEX ( `member_id` )",
"ALTER TABLE `accounts` ADD INDEX ( `account_attribute_id` )"
);
?>
freenetis/trunk/kohana/application/upgrade_sql/upgrade_sql_344.php
<?php
$upgrade_sql[344] = array(
"ALTER TABLE `wireless_settings` CHANGE `mode` `wmode` INT( 11 ) NOT NULL ",
"ALTER TABLE `members` ADD `organization_identifier` VARCHAR( 20 ) NULL DEFAULT NULL AFTER `external_type` "
);
?>
freenetis/trunk/kohana/application/libraries/Parser_Ebanka.php
$field=str_replace(array(" ", " "), "", $field);
return strtr($field, ",", ".");
}
/**
* V posledním sloupci HTML výpisu ebanka dává tyto poplatky: Poplatek, směna, zpráva.
* Poplatky jsou odděleny značkami <br>, u transp. výpisu <br/>
* @param $field
* @param $transparent
* @return součet všech poplatků generovaných pro daný řádek
*/
protected function get_fee($field, $transparent) {
$field=str_replace(array(" ", " "), "", $field);
$field=strtr($field, ",", ".");
if ($transparent) $br_tag="<br/>";
else $br_tag="<br>";
$arr=preg_split("/<br>/si", $field);
$fee=0;
foreach ($arr as $value)
$fee+=$value;
return $fee;
}
protected function get_data_from_transparent() {
$res = $this->result;
......
break;
case 3:
$arr=explode("<br/>", $field); //VS<br/>KS<br/>SS
$res->variable_symbol=$arr[0];
$res->variable_symbol=(int)$arr[0];
$res->constant_symbol=$arr[1];
$res->specific_symbol=$arr[2];
$res->specific_symbol=(int)$arr[2];
break;
case 4:
$res->amount=$this->get_amount($field); // částka
break;
case 5:
$res->fee=$this->get_amount($field); // fee
$res->fee=$this->get_fee($field, TRUE); // fee
if (isset($this->callback)) call_user_func($this->callback, $res);
/**
* ted uz muzeme ulozit ziskane data do databaze:
......
if (!is_numeric($field)) {
ob_flush();
flush();
die("Parser error: ".
trigger_error("Parser error: ".
"očekával jsem číslo výpisu, ale dostal jsem:<br/>\n".$field.
"<br/> \nPoslední správně načtený řádek výpisu má číslo ".$res->cislo.
"<br/> \nCelý vstupní buffer je: <br/>\n". htmlentities($this->buffer)
......
$res->amount=$this->get_amount($field);
break;
case 6: // fee
$res->fee=$this->get_amount($field);
$res->fee=$this->get_fee($field, FALSE);
if (isset($this->callback)) call_user_func($this->callback, $res);
/**
* ted uz muzeme ulozit ziskane data do databaze:
......
$this->get_year();
$found=$this->find_tags_and_trim(array(self::ACCOUNT_STRING, self::START_STRING)); // zkus ještě najít číslo účtu
if ($found==2) // chyba (toto by nikdy nemělo nastat)
die ("Nemohu najít začátek tabulky: '".self::START_STRING."'");
trigger_error ("Nemohu najít začátek tabulky: '".self::START_STRING."'");
else if ($found==1) // našel jsem START_STRING => číslo účtu nemám, konec switch-e
break;
//else goto case 1: protože jsem našel číslo účtu
......
$this->get_cislo_uctu();
$found=$this->find_tag_and_trim(self::START_STRING); // najdi začátek výpisu
if ($found===false) // chyba (toto by nikdy nemělo nastat)
die ("Nemohu najít začátek tabulky: '".self::START_STRING."'");
trigger_error ("Nemohu najít začátek tabulky: '".self::START_STRING."'");
break;
case 2: //nalezen start tabulky s výpisy, což znamená, že
$transparent=true; // období výpisu nenalezeno => transparentní výpis
$this->year=date("Y"); // jako rok doplň aktuální rok
break;
case 3:
die ("Nemohu najít začátek tabulky nebo datum/rok");
trigger_error ("Nemohu najít začátek tabulky nebo datum/rok");
};

Také k dispozici: Unified diff