0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
} else {
WindykacjaView::zadluzenia($user);
return;
}
} else {
echo''."Bledne dane.".'
';
}
}
else if ($task == 'zestawienie_vectra_zadluzenia') {
WindykacjaView::zestawienie_vectra_zadluzenia();
exit;
}
else if ($task == 'zestawienie_vectra_nadplaty') {
WindykacjaView::zestawienie_vectra_nadplaty();
exit;
}
else if ($task == 'wezwanie') {
$user_id = V::get('user_id', 0, $_REQUEST, 'int');
if ($user_id > 0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
} else {
WindykacjaView::wezwanie($user, $user->PAY_TERM);
return;
}
} else {
echo''."Bledne dane.".'
';
}
}
else if ($task == 'wezwanie_ostateczne') {
$user_id = V::get('user_id', 0, $_REQUEST, 'int');
if ($user_id > 0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
} else {
WindykacjaView::wezwanie_ostateczne($user, $user->PAY_TERM);
return;
}
} else {
echo''."Bledne dane.".'
';
}
}
else if ($task == 'bok_rozwiazanie_umowy') {
$user_id = V::get('user_id', 0, $_REQUEST, 'int');
if ($user_id > 0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
} else {
WindykacjaView::task_bok_rozwiazanie_umowy($user);
return;
}
} else {
echo''."Bledne dane.".'
';
}
}
else if ($task == 'bok_rozwiazanie_umowy_print') {
$user_id = V::get('user_id', 0, $_REQUEST, 'int');
if ($user_id > 0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
} else {
WindykacjaView::task_bok_rozwiazanie_umowy_print($user, V::get('id_koresp', 0, $_REQUEST, 'int'));
return;
}
} else {
echo''."Bledne dane.".'
';
}
}
else if ($task == 'stats_update') {
WindykacjaView::task_update_stats();
return;
}
else if ($task == 'export_csv') {
$items = array();
$page_nav = new stdClass();
$page_nav->total = 0;
$page_nav->page_total = 0;
$page_nav->base_link_arr = array('MENU_INIT'=>"USERS2_WINDYKACJA_STATUS", 'q'=>$q);
$page_nav->page_limit = 100000;
$page_nav->page_nr = 0;
$page_nav->base_link_arr['_f'] = V::get('_f', '', $_GET);
if ($page_nav->page_nr > 0) {
$page_nav->base_link_arr['_p'] = $page_nav->page_nr;
}
// sortowanie, filtr wyszukiwania
$page_nav->base_link_arr['_oby'] = 'ID';
$page_nav->base_link_arr['_odir'] = 'DESC';
if (($order_by = V::get('_oby', '', $_GET)) != '') {
$page_nav->base_link_arr['_oby'] = $order_by;
}
if (($order_dir = V::get('_odir', '', $_GET)) != '') {
$page_nav->base_link_arr['_odir'] = $order_dir;
}
if (!empty($q)) {
$page_nav->total = WindykacjaStatsModel::get_users_total($q);
$page_nav->last_page_nr = floor($page_nav->total / $page_nav->page_limit);
$items = WindykacjaStatsModel::get_users($q, $page_nav->page_limit, $page_nav->page_nr * $page_nav->page_limit, $page_nav->base_link_arr['_oby'], $page_nav->base_link_arr['_odir']);
$page_nav->page_total = count($items);
}
else {
$page_nav->total = WindykacjaStatsModel::get_users_total($q);
$page_nav->last_page_nr = floor($page_nav->total / $page_nav->page_limit);
$items = WindykacjaStatsModel::get_users($q, $page_nav->page_limit, $page_nav->page_nr * $page_nav->page_limit, $page_nav->base_link_arr['_oby'], $page_nav->base_link_arr['_odir']);
$page_nav->page_total = count($items);
}
if ($page_nav->total > 0) {
WindykacjaView::export_csv($q, $page_nav, $items);
} else {
echo'' . "Brak danych" . '
';
}
exit;
}
}
WindykacjaView::css();
if ($user_id > 0) {
$user = WindykacjaStatsModel::get_user_by_id($user_id);
if (!$user) {
echo''."Użytkownik nie istnieje lub brak uprawnień.".'
';
return;
}
WindykacjaView::user($user);
WindykacjaView::user_umowy($user);
WindykacjaView::user_historia_platnosci($user);
}
else {
// aktualiacja danych
WindykacjaStatsHelper::update_users_table();
$items = array();
$page_nav = new stdClass();
$page_nav->total = 0;
$page_nav->page_total = 0;
$page_nav->base_link_arr = array('MENU_INIT'=>"USERS2_WINDYKACJA_STATUS", 'q'=>$q);
$page_nav->page_limit = 50;
$page_nav->page_nr = V::get('_p', 0, $_REQUEST, 'int');
$page_nav->base_link_arr['_f'] = V::get('_f', '', $_GET);
if ($page_nav->page_nr > 0) {
$page_nav->base_link_arr['_p'] = $page_nav->page_nr;
}
// sortowanie, filtr wyszukiwania
$page_nav->base_link_arr['_oby'] = 'ID';
$page_nav->base_link_arr['_odir'] = 'DESC';
if (($order_by = V::get('_oby', '', $_GET)) != '') {
$page_nav->base_link_arr['_oby'] = $order_by;
}
if (($order_dir = V::get('_odir', '', $_GET)) != '') {
$page_nav->base_link_arr['_odir'] = $order_dir;
}
if (!empty($q)) {
$page_nav->total = WindykacjaStatsModel::get_users_total($q);
$page_nav->last_page_nr = floor($page_nav->total / $page_nav->page_limit);
$items = WindykacjaStatsModel::get_users($q, $page_nav->page_limit, $page_nav->page_nr * $page_nav->page_limit, $page_nav->base_link_arr['_oby'], $page_nav->base_link_arr['_odir']);
$page_nav->page_total = count($items);
}
else {
$page_nav->total = WindykacjaStatsModel::get_users_total($q);
$page_nav->last_page_nr = floor($page_nav->total / $page_nav->page_limit);
$items = WindykacjaStatsModel::get_users($q, $page_nav->page_limit, $page_nav->page_nr * $page_nav->page_limit, $page_nav->base_link_arr['_oby'], $page_nav->base_link_arr['_odir']);
$page_nav->page_total = count($items);
}
WindykacjaView::form($q);
$to_update_total = WindykacjaStatsHelper::get_to_update_total();
if ($to_update_total > 0) {
echo'';
echo'' . "Aktualizuj statusy (" . $to_update_total . ")" . '';
echo'
';
}
WindykacjaView::items_filters();
if ($page_nav->total > 0) {
WindykacjaView::items($q, $page_nav, $items);
} else {
echo'' . "Brak danych" . '
';
}
}
}
class WindykacjaView {
public static function user(&$user) {
$bad_address_task = V::get('bad_address', '', $_POST);
$bad_address_msg = array();
if ($bad_address_task) {
$id_koresp = V::get('ID_KORESP', 0, $_POST, 'int');
if ($id_koresp <= 0) {
$bad_address_msg['error_id_koresp'] = true;
} else {
if ($bad_address_task == 'save') {
if (!WindykacjaStatsHelper::bad_address_save($user, $id_koresp)) {
$bad_address_msg['error'] = true;
} else {
$bad_address_msg['ok'] = true;
}
}
else if ($bad_address_task == 'confirm') {
if (!WindykacjaStatsHelper::bad_address_confirm($user, $id_koresp)) {
$bad_address_msg['error'] = true;
} else {
$bad_address_msg['ok'] = true;
}
}
}
}
$change_status_task = V::get('change_status', '', $_POST);
$change_status_msg = array();
if ($change_status_task) {
if ($change_status_task == 'change') {
$new_status = V::get('new_status', '', $_POST);
$fltr_arr = WindykacjaStatsHelper::get_filters();
if (!array_key_exists($new_status, $fltr_arr)) {
$change_status_msg['error'] = true;
} else {
if (!WindykacjaStatsHelper::change_status_save($user, $new_status)) {
$change_status_msg['error'] = true;
} else {
$change_status_msg['ok'] = true;
}
}
}
}
$set_nr_sprawy_krd_task = V::get('set_nr_sprawy_krd', '', $_POST);
$set_nr_sprawy_krd_msg = array();
if ($set_nr_sprawy_krd_task) {
if ($set_nr_sprawy_krd_task == 'change') {
$nr_sprawy_krd = V::get('nr_sprawy_krd', '', $_POST);
if (!WindykacjaStatsHelper::nr_sprawy_krd_save($user, $nr_sprawy_krd)) {
$set_nr_sprawy_krd_msg['error'] = true;
} else {
$set_nr_sprawy_krd_msg['ok'] = true;
}
}
}
$set_nr_sprawy_sad_task = V::get('set_nr_sprawy_sad', '', $_POST);
$set_nr_sprawy_sad_msg = array();
if ($set_nr_sprawy_sad_task) {
if ($set_nr_sprawy_sad_task == 'change') {
$nr_sprawy_sad = V::get('nr_sprawy_sad', '', $_POST);
if (!WindykacjaStatsHelper::nr_sprawy_sad_save($user, $nr_sprawy_sad)) {
$set_nr_sprawy_sad_msg['error'] = true;
} else {
$set_nr_sprawy_sad_msg['ok'] = true;
}
}
}
$set_nr_sprawy_komornik_task = V::get('set_nr_sprawy_komornik', '', $_POST);
$set_nr_sprawy_komornik_msg = array();
if ($set_nr_sprawy_komornik_task) {
if ($set_nr_sprawy_komornik_task == 'change') {
$nr_sprawy_komornik = V::get('nr_sprawy_komornik', '', $_POST);
if (!WindykacjaStatsHelper::nr_sprawy_komornik_save($user, $nr_sprawy_komornik)) {
$set_nr_sprawy_komornik_msg['error'] = true;
} else {
$set_nr_sprawy_komornik_msg['ok'] = true;
}
}
}
$set_ustalenia_info_task = V::get('set_ustalenia_info', '', $_POST);
$set_ustalenia_info_msg = array();
if ($set_ustalenia_info_task) {
if ($set_ustalenia_info_task == 'change') {
$ustalenia_date = V::get('ustalenia_date', '', $_POST);
$ustalenia_info = V::get('ustalenia_info', '', $_POST);
if (!WindykacjaStatsHelper::ustalenia_info_save($user, $ustalenia_date, $ustalenia_info)) {
$set_ustalenia_info_msg['error'] = true;
} else {
$set_ustalenia_info_msg['ok'] = true;
}
}
}
echo'';
echo' '."Klienci".'';
echo ' » '."[".$user->id_users."]".'';
echo ' '.$user->P_NAME.' '.$user->P_NAME_SECOND.'';
echo'
';
echo '';
echo '';
echo '';
echo "Dane " . (($user->is_firma)? "firmy" : "klienta") . " ";
if ($user->BAD_ADDRESS) {
echo '';
} else {
echo '';
}
if (!empty($bad_address_msg)) {
if ('id_koresp' == V::get('error', '', $bad_address_msg)) {
echo ''."Podaj poprawny ID Koresp.".' ';
} else if (true == V::get('error', false, $bad_address_msg)) {
echo ''."Error!".' ';
} else if (true == V::get('ok', false, $bad_address_msg)) {
echo ''."Zapisano pomyślnie.".' ';
}
}
echo '';
echo'';
echo'| ' . "Adres:" . ' | ';
echo'BAD_ADDRESS)? ' style="background-color:#FF7878;"' : '').'>';
echo "ul. ".$user->P_ADDRESS_STREET." ".$user->P_ADDRESS_HOUSE."/".$user->P_ADDRESS_HOME.", ".$user->P_ADDRESS_POST_CODE." ".$user->P_ADDRESS_CITY;
echo'';
echo' ';
echo'';
echo'| ' . "Pesel:" . ' | ';
echo'';
if ($user->P_PESEL) {
echo $user->P_PESEL;
} else {
echo ''."Brak numeru PESEL!".'';
}
echo' | ';
echo' ';
echo'';
echo'| ' . "Nr telefonu:" . ' | ';
echo'';
if ($user->P_PHONE) {
echo $user->P_PHONE;
} else {
echo ''."Brak!".'';
}
echo' | ';
echo' ';
echo'';
echo'| ' . "Nr dowodu:" . ' | ';
echo'';
if ($user->P_OTHER_DOC) {
echo $user->P_OTHER_DOC;
} else {
echo ''."Brak!".'';
}
echo' | ';
echo' ';
echo'';
echo'| ' . "e-mail:" . ' | ';
echo'';
if ($user->user_mail_contact) {
echo ''.$user->user_mail_contact.'';
} else {
echo ''."Brak!".'';
}
echo' | ';
echo' ';
echo'';
echo'| ' . "Klient firmy:" . ' | ';
echo'';
if ($user->BILLING_OWNER == 1) {
echo 'BN - BIALL-NET';
} else if ($user->BILLING_OWNER == 2) {
echo 'ND - NETDAY';
} else {
echo 'nieznany';
}
echo' | ';
echo' ';
echo'';
echo'| ' . "Blokada:" . ' | ';
echo'';
if ($user->STATUS == 9) {
echo '' . "ZABLOKOWANY" . '';
} else {
echo 'brak blokady';
}
echo' | ';
echo' ';
echo ' ';
echo ' | ' . " " . ' | ';
echo '';
echo '';
echo "Status windykacji ";
echo '' . "(" . $user->WINDYKACJA_ID . ")" . '';
if ($user->A_STATUS_UPDATE_DATE < date("Y-m-d")) {
echo " " . '' . "Aktualizuj status" . '';
}
// TODO: zmien status klienta - przypisanie do odpowiedniej grupy
echo '';
echo '';
echo '| ' . "status:" . ' | ';
echo '';
echo $user->A_STATUS;
if (V::get('DBG', '', $_GET, 'int') > 0) {// TODO: TEST
if ($user->A_STATUS == 'waiting-krd') {
echo '
' . "wezwanie2_DATE: {$user->wezwanie2_DATE}";
$wzw2 = new stdClass();
$wzw2->Y = intval(substr($user->wezwanie2_DATE, 0, 4));
$wzw2->m = intval(substr($user->wezwanie2_DATE, 5, 2));
$wzw2->d = intval(substr($user->wezwanie2_DATE, 8, 2));
echo '
' . "wzw2: {$wzw2->Y}-{$wzw2->m}-{$wzw2->d}";
$wzw2->plus_60 = date("Y-m-d", mktime(0,0,0, $wzw2->m, $wzw2->d + 60, $wzw2->Y));
echo '
' . "wezwanie2_DATE + 60 dni: {$wzw2->plus_60}";
if ($wzw2->plus_60 < date("Y-m-d")) {
echo '
' . "TODO: przenieść do krd";
}
}
}// TODO: TEST
echo ' ';
echo ' | ';
echo ' ';
echo '';
echo '| ' . "Nr sprawy w krd" . ' | ';
echo '';
echo ' ';
echo ' | ';
echo ' ';
echo '';
echo '| ' . "Nr sprawy w sądzie" . ' | ';
echo '';
echo ' ';
echo ' | ';
echo ' ';
echo '';
echo '| ' . "Nr sprawy u komornika" . ' | ';
echo '';
echo ' ';
echo ' | ';
echo ' ';
echo '';
echo '| ' . "Aktualne ustalenia z klientem" . ' | ';
echo '';
echo ' ';
echo ' | ';
echo ' ';
echo '';
echo '| ' . "data ostatniej wpłaty:" . ' | ';
echo '';
if ($user->LAST_PAY_DATE && $user->LAST_PAY_DATE != '0000-00-00') {
echo $user->LAST_PAY_DATE;
} else {
echo '---';
}
echo ' | ';
echo ' ';
echo '';
echo '| ' . "data wymagalności ostatniej faktury:" . ' | ';
echo '';
if ($user->PAY_DATE != '0000-00-00') {
echo $user->PAY_DATE;
}
echo ' | ';
echo ' ';
echo '';
echo '| ' . "data wymagalności pierwszej faktury:" . ' | ';
echo '';
if ($user->PAY_DATE_FIRST_FVAT != '0000-00-00') {
echo $user->PAY_DATE_FIRST_FVAT;
}
echo ' | ';
echo ' ';
echo '';
echo '| ' . "termin płatności:" . ' | ';
echo '';
if ($user->PAY_TERM != '0000-00-00') {
echo $user->PAY_TERM;
}
echo ' | ';
echo ' ';
echo ' ';
echo ' |
';
// TODO: windykacja_status_info - grupa filtrow do ktorej nalezy klient
Lib::loadClass('CompanyHelper');
$db = DB::getDB();
$comHelper = new CompanyHelper($db);
$usr = $comHelper->getById($user->ID);
$dealsActive = $comHelper->getDealsFull($user->ID, false);
Lib::loadClass('PokazOfertyView');
$pokaz_oferty_view = new PokazOfertyView();
echo $pokaz_oferty_view->print_css();
$pokaz_oferty_view->printServices($usr, $dealsActive);
// aktualny status klienta
echo'';
if ($user->A_STATUS_UPDATE_DATE < date("Y-m-d")) {
}
else {
switch ($user->A_STATUS) {
case 'WAITING': {
echo'
' . "Status OK" . '
';
}
break;
case 'wezwanie1': {
$default_PAY_TERM = date("Y-m-d", mktime(0,0,0,date('m'), date('d') + 14, date('Y')));
$frm_errors = array();
$frm_msgs = array();
$frm = array();
$frm['wezwanie1_DATE'] = date("Y-m-d");
$frm['PAY_TERM'] = V::get('PAY_TERM', $default_PAY_TERM, $_POST);
$frm['ID_PROJ'] = V::get('ID_PROJ', 0, $_POST, 'int');
if ('set_wezwanie' == V::get('user_task', '', $_POST)) {
if ($frm['ID_PROJ'] == 0) {
$frm_errors[] = "Brak numeru ID Projketu";
}
if (empty($frm_errors)) {
$ret = WindykacjaStatsHelper::update_user($user, $frm);
if ($ret) {
$frm_msgs[] = "Status klienta został zaktualizowany";
} else {
$frm_errors[] = "Status klienta nie został zaktualizowany";
}
}
}
echo '
';
if ($user->PAY_FVAT > 0 && $user->PAY_SALDO < 0) {
echo "Saldo klienta: " . '
' . "{$user->PAY_SALDO} zł" . '' . " ({$user->PAY_FVAT} niezapłaconych faktur)" . '
';
echo'
';
}
echo '
'."Wysłać wezwanie do zapłaty".''.'
';
if (!empty($frm_errors)) {
echo'
' . implode('
', $frm_errors) . '
';
}
if (!empty($frm_msgs)) {
echo'
' . implode('
', $frm_msgs) . '
';
}
echo'
';
if ($user->wezwanie1_DATE != '0000-00-00') {
echo'
';
$ico_print = '
';
echo'
'.$ico_print." wezwanie do zapłaty".'';
}
echo '
';// .box-blue
}
break;
case 'waiting-wezwanie2': {// TODO: only auto update ?
echo '
';
if ($user->PAY_FVAT > 0 && $user->PAY_SALDO < 0) {
echo "Saldo klienta: " . '
' . $user->PAY_SALDO . " zł" . '' . " (" . $user->PAY_FVAT ." niezapłaconych faktur)" . '
';
echo'
';
}
echo '
'."Oczekiwanie na uzbieranie kwoty 300zł (firmy 500zł)".''.'
';
if (!empty($frm_errors)) {
echo'
';
echo implode('
', $frm_errors);
echo'
';
}
echo'
';
if ($user->wezwanie1_DATE != '0000-00-00') {
echo'
';
$ico_print = '
';
echo'
' . $ico_print . " wezwanie do zapłaty".''.'
'." (termin płatności: " . $user->PAY_TERM . ")".'';
}
echo '
';// .box-blue
}
break;
case 'wezwanie2': {
$default_PAY_TERM = date("Y-m-d", mktime(0,0,0,date('m'), date('d') + 14, date('Y')));
$frm_msgs = array();
$frm_errors = array();
$frm = array();
$frm['wezwanie2_DATE'] = date("Y-m-d");
$frm['PAY_TERM'] = V::get('PAY_TERM', $default_PAY_TERM, $_POST);
$frm['ID_PROJ'] = V::get('ID_PROJ', 0, $_POST, 'int');
if ('set_wezwanie2' == V::get('user_task', '', $_POST)) {
if ($frm['ID_PROJ'] == 0) {
$frm_errors[] = "Brak numeru ID korespondencji";
}
if (empty($frm_errors)) {
$ret = WindykacjaStatsHelper::update_user($user, $frm);
if ($ret) {
$frm_msgs[] = "Status klienta został zaktualizowany";
} else {
$frm_errors[] = "Status klienta nie został zaktualizowany";
}
}
}
echo '
';
if ($user->PAY_FVAT > 0 && $user->PAY_SALDO < 0) {
echo "Saldo klienta: " . '
' . "{$user->PAY_SALDO} zł" . '' . " ({$user->PAY_FVAT} niezapłaconych faktur)" . '
';
echo'
';
}
echo '
'."Wysłać ostateczne wezwanie do zapłaty".''.'
';
if (!empty($frm_errors)) {
echo'
' . implode('
', $frm_errors) . '
';
}
if (!empty($frm_msgs)) {
echo'
' . implode('
', $frm_msgs) . '
';
}
echo'
';
if ($user->wezwanie2_DATE != '0000-00-00') {
echo'
';
$ico_print = '
';
echo'
' . $ico_print . " ostateczne wezwanie do zapłaty".'';
}
echo '
';// .box-blue
}
break;
case 'waiting-krd': {
if ($user->wezwanie2_DATE != '0000-00-00') {
echo'
';
$ico_print = '
';
echo'
' . $ico_print . " ostateczne wezwanie do zapłaty".'';
}
}
break;
default:
echo'
' . "TODO: (".$user->A_STATUS.")" . '
';
}
}
//if ($user->BA_TIMESTAMP) {
// echo "Stan wzajemnych rozrachunków:";
// echo " WINIEN:
".$user->BA_WINIEN." - MA:
".$user->BA_MA." SALDO(DO ZAPŁATY):
".($user->BA_WINIEN - $user->BA_MA)."";
// echo", i jest to stan na dzień ".date("Y-m-d",$user->BA_TIMESTAMP)." ";
//}
// phone contact
$phone_status_info = WindykacjaStatsHelper::get_phone_status_info($user);
$sms_status_info = WindykacjaStatsHelper::get_sms_status_info($user);
$frm_errors = array();
$frm_msgs = array();
if (1 == V::get('phone_contact_save', 0, $_POST, 'int')) {
if ('' == ($phone_status = V::get('phone_status', '', $_POST))) {
$frm_errors[] = "Nic nie zaznaczono!";
} else {
if (!array_key_exists($phone_status, $phone_status_info)) {
$frm_errors[] = "Nieprawidłowa wartość!";
} else {
$ret = WindykacjaStatsHelper::update_phone_status($user, $phone_status);
if ($ret) {
$frm_msgs[] = "Dane zapisano pomyślnie";
} else {
$frm_errors[] = "Wystąpił błąd podczas zapisu danych";
}
}
}
}
if (1 == V::get('sms_contact_save', 0, $_POST, 'int')) {
if ('' == ($sms_status = V::get('sms_status', '', $_POST))) {
$frm_errors[] = "Nic nie zaznaczono!";
} else {
if (!array_key_exists($sms_status, $sms_status_info)) {
$frm_errors[] = "Nieprawidłowa wartość!";
} else {
$ret = WindykacjaStatsHelper::update_sms_status($user, $sms_status);
if ($ret) {
$frm_msgs[] = "Dane zapisano pomyślnie";
} else {
$frm_errors[] = "Wystąpił błąd podczas zapisu danych";
}
}
}
}
// add old id_koresp to hist (events)
if (1 == V::get('contact_add_id_koresp_save', 0, $_POST, 'int')) {
if (($id_koresp = V::get('id_koresp', '', $_POST, 'int')) <= 0) {
$frm_errors[] = "Nie podano numeru korespondencji!";
} else {
$koresp_type = V::get('koresp_type', '', $_POST);
$ret = WindykacjaStatsHelper::update_old_id_koresp($user, $id_koresp, $koresp_type);
if ($ret) {
$frm_msgs[] = "Dane zapisano pomyślnie";
} else {
$frm_errors[] = "Wystąpił błąd podczas zapisu danych";
}
}
}
echo'';
echo'
';
if (!empty($frm_errors)) {
echo'
' . implode('
', $frm_errors) . '
';
}
if (!empty($frm_msgs)) {
echo'
' . implode('
', $frm_msgs) . '
';
}
echo '
';
echo '';
echo' ';
echo " kontakt z klientem ";
echo ' | ';
echo '';
echo " Co ustalono: ";
echo ' | ';
echo '';
echo'';
echo ' | ';
echo '';
echo ' | ';
echo '';
echo'';
echo ' | ';
echo '
';
echo'
';// .box
echo'
';
echo'';
echo'
';// .box
echo'
';// .box
echo'
';
if (!empty($filter_selected)) {
echo'' . ' ' . '';
echo ' | ';
}
echo'';
echo'';
echo'';
foreach ($filter_arr as $k => $v_arr) {
$cls = ($filter_selected == $k)? ' class="selected"' : '';
echo'';
if ($desc = V::get('desc', '', $v_arr)) {
echo '' . reset($desc) . '';
}
echo'';
}
if (!empty($zewn_filters_arr)) {
echo '| ' . "Windykacja zewnętrzna" . ' | ';
}
echo' ';
echo'';
foreach ($filter_arr as $k => $v_arr) {
$cls = ($filter_selected == $k)? ' class="selected"' : '';
echo'';
echo '' . reset($v_arr) . '';
//$stat_cnt = V::get($k, 0, $status_cnt, 'int');
//echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_cnt = 0;
if (array_key_exists($k, $status_cnt)) {
$stat_cnt = $status_cnt[$k]->cnt;
echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_saldo = number_format($status_cnt[$k]->suma_zaleglosci, 2, ',', ' ');
echo '
' . '' . $stat_saldo . '';
} else {
echo ' ' . "(" . $stat_cnt . ")" . '';
}
echo'';
}
if (!empty($zewn_filters_arr)) {
foreach ($zewn_filters_arr as $k => $v_arr) {
$cls = ($filter_selected == $k)? ' class="selected"' : '';
echo'';
echo '' . reset($v_arr) . '';
//$stat_cnt = V::get($k, 0, $status_cnt, 'int');
//echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_cnt = 0;
if (array_key_exists($k, $status_cnt)) {
$stat_cnt = $status_cnt[$k]->cnt;
echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_saldo = number_format($status_cnt[$k]->suma_zaleglosci, 2, ',', ' ');
echo '
' . '' . $stat_saldo . '';
} else {
echo ' ' . "(" . $stat_cnt . ")" . '';
}
echo'';
}
}
echo' ';
echo'';
echo' ';
if (!empty($sub_filters_arr)) {
echo '';
echo '';
foreach ($sub_filters_arr as $k => $v_arr) {
$cls = ($filter_selected == $k)? ' class="selected"' : '';
echo'';
echo '' . reset($v_arr) . '';
//$stat_cnt = V::get($k, 0, $status_cnt, 'int');
//echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_cnt = 0;
if (array_key_exists($k, $status_cnt)) {
$stat_cnt = $status_cnt[$k]->cnt;
echo ' ' . "(" . $stat_cnt . ")" . '';
$stat_saldo = number_format($status_cnt[$k]->suma_zaleglosci, 2, ',', ' ');
echo '
' . '' . $stat_saldo . '';
} else {
echo ' ' . "(" . $stat_cnt . ")" . '';
}
echo'';
}
echo ' ';
echo ' ';
}
echo ' |
';
echo'
';
}
public static function items($q, &$page_nav, &$items) {
$page_nav->links = array();
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_p'] = 0;
$out_link = V::make_link('', $out_link_arr);
$page_nav->links[] = ''."|<<".'';
if ($page_nav->page_nr > 0) {
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_p'] = $page_nav->page_nr - 1;
$out_link = V::make_link('', $out_link_arr);
$page_nav->links[] = ''."<< -{$page_nav->page_limit}".'';
} else {
$page_nav->links[] = " << ";
}
$page_nav->links[] = ''.($page_nav->page_nr * $page_nav->page_limit).'';
$page_nav->links[] = 'total." rekordów".'">'."({$page_nav->total}) ".'';
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_p'] = $page_nav->page_nr + 1;
$out_link = V::make_link('', $out_link_arr);
$page_nav->links[] = ''."+{$page_nav->page_limit} >>".'';
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_p'] = $page_nav->last_page_nr;
$out_link = V::make_link('', $out_link_arr);
$page_nav->links[] = ''.">>|".'';
$page_nav_out = '' . implode(" ", $page_nav->links);
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['task'] = 'export_csv';
$out_link = V::make_link('', $out_link_arr);
$page_nav_out .= '
export csv';
$page_nav_out .= '
'."Brak danych".'
';
return;
}
echo $page_nav_out;
$cols_order_by = WindykacjaStatsModel::get_order_by_fields();
$cols = array();
$cols['ID_BILLING_USERS'] = array('label'=>'Nr klienta', 'title'=>'ID_BILLING_USERS');
$cols['is_firma'] = array('label'=>'F', 'title'=>'is_firma: Firma 1 - tak, 0 nie');
$cols['BILLING_OWNER'] = array('label'=>'COM', 'title'=>'BILLING_OWNER: Klient firmy 1 - BIALL-NET, 2 - NETDAY');
$cols['BLOKADA'] = array('label'=>'B', 'title'=>'BLOKADA: Informacja o blokadzie');
$cols['P_NAME'] = array('label'=>'imie i nazwisko', 'title'=>'P_NAME');
$cols['P_ADDRESS_STREET'] = array('label'=>'adres', 'title'=>'P_ADDRESS_STREET');
$cols['A_STATUS'] = array('label'=>'status', 'title'=>'A_STATUS');
$cols['A_STATUS_UPDATE_DATE'] = array('label'=>'data aktualizacji', 'title'=>'A_STATUS_UPDATE_DATE');
$cols['PAY_SALDO'] = array('label'=>'saldo', 'title'=>'PAY_SALDO');
$cols['PAY_FVAT'] = array('label'=>'zaległe faktury', 'title'=>'PAY_FVAT');
$cols['PAY_DATE_FIRST_FVAT'] = array('label'=>'data wymagalności
pierwszej faktury', 'title'=>'PAY_DATE_FIRST_FVAT');
$cols['PAY_DATE'] = array('label'=>'data wymagalności
ostatniej faktury', 'title'=>'PAY_DATE');
$cols['LAST_PAY_DATE'] = array('label'=>'data ostatniej wpłaty', 'title'=>'LAST_PAY_DATE');
$cols['PAY_TERM'] = array('label'=>'termin płatności', 'title'=>'PAY_TERM');
echo'';
echo'';
echo'';
foreach ($cols as $k_field => $v_params) {
echo'';
echo $v_params['label'];
if (in_array($k_field, $cols_order_by)) {
$sel_oby = (isset($page_nav->base_link_arr['_oby']))? $page_nav->base_link_arr['_oby'] : '';
$sel_odir = 'DESC';
if ($k_field == $sel_oby) {
$sel_odir = (isset($page_nav->base_link_arr['_odir']))? $page_nav->base_link_arr['_odir'] : '';
$sel_odir = ($sel_odir == 'ASC')? 'ASC' : 'DESC';
}
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_p'] = 0;
$out_link_arr['_oby'] = $k_field;
$out_link_arr['_odir'] = ($sel_odir == 'DESC')? 'ASC' : 'DESC';
$out_link = V::make_link('', $out_link_arr);
echo ' ' . (($sel_odir == 'DESC')? 'v' : '^') . '';
}
echo '';
}
echo'
';
echo'';
echo'';
$t = 0;
foreach ($items as $k_id => $user) {
echo'';
echo'';
echo'';
$out_link_arr = V::copy($page_nav->base_link_arr);
$out_link_arr['_user_id'] = $k_id;
$out_link = V::make_link('', $out_link_arr);
echo ''.' '.'';
echo " ";
echo ''.$k_id.'';
echo'';
echo' | ';
echo '';
echo $user->is_firma;
echo ' | ';
echo '';
if ($user->BILLING_OWNER == 1) {
echo '' . 'BN' . '';
} else if ($user->BILLING_OWNER == 2) {
echo '' . 'ND' . '';
} else {
echo '0';
}
echo ' | ';
echo '';
if ($user->STATUS == 9) {
echo '' . "Z" . '';
} else {
echo '' . "0" . '';
}
echo ' | ';
echo'';
$cell_out = $user->P_NAME.' '.$user->P_NAME_SECOND;
echo '' . ((strlen($cell_out) > 30)? substr($cell_out, 0, 30) . ' ...' : $cell_out) . '';
echo' | ';
echo'BAD_ADDRESS)? ' style="background-color:#FF7878;"' : '').'>';
$cell_out = "ul. " . $user->P_ADDRESS_STREET . " " . $user->P_ADDRESS_HOUSE . "/" . $user->P_ADDRESS_HOME . ", " . $user->P_ADDRESS_POST_CODE . " " . $user->P_ADDRESS_CITY;
echo '' . ((strlen($cell_out) > 30)? mb_substr($cell_out, 0, 30, "UTF-8") . ' ...' : $cell_out) . '';
echo'';
echo'';
echo '';
switch ($user->A_STATUS) {
case 'WAITING':
echo '' . $user->A_STATUS . '';
break;
default:
echo $user->A_STATUS;
}
echo '';
echo' | ';
echo'';
if ($user->A_STATUS_UPDATE_DATE != '0000-00-00') {
echo $user->A_STATUS_UPDATE_DATE;
} else {
echo '' . $user->A_STATUS_UPDATE_DATE . '';
}
echo' | ';
echo'';
echo '';
if ($user->PAY_SALDO != 0) {
$col = ($user->PAY_SALDO > 0)? '#333' : '#f00';
echo '' . "{$user->PAY_SALDO} zł" . '';
} else {
echo'' . "0" . '';
}
echo '';
echo' | ';
echo'';
if ($user->PAY_FVAT > 0) {
$st = ($user->PAY_FVAT > 1)? ' style="color:#f00"' : '';
echo' PAY_FVAT} nieopłaconych faktur" . '"'.$st.'>' . $user->PAY_FVAT . '';
} else {
echo $user->PAY_FVAT;
}
echo' | ';
echo'';
if ($user->PAY_DATE_FIRST_FVAT != '0000-00-00') {
echo $user->PAY_DATE_FIRST_FVAT;
}
echo' | ';
echo'';
if ($user->PAY_DATE != '0000-00-00') {
echo $user->PAY_DATE;
}
echo' | ';
echo'';
if ($user->LAST_PAY_DATE && $user->LAST_PAY_DATE != '0000-00-00') {
echo $user->LAST_PAY_DATE;
} else {
echo '---';
}
echo' | ';
echo'';
if ($user->PAY_TERM != '0000-00-00') {
echo $user->PAY_TERM;
}
echo' | ';
//, w.`USER_PAY_TERM_ADD`
//, w.`L_APPOITMENT_DATE`
//, w.`L_APPOITMENT_USER`
echo'
';
}
echo'';
echo'
';
echo $page_nav_out;
}
public static function export_csv($q, &$page_nav, &$items) {
if (empty($items)) {
echo "Brak danych";
return;
}
$cols_order_by = WindykacjaStatsModel::get_order_by_fields();
$cols = array();
$cols['ID_BILLING_USERS'] = array('label'=>'Nr klienta', 'title'=>'ID_BILLING_USERS');
$cols['is_firma'] = array('label'=>'F', 'title'=>'is_firma: Firma 1 - tak, 0 nie');
$cols['BILLING_OWNER'] = array('label'=>'COM', 'title'=>'BILLING_OWNER: Klient firmy 1 - BIALL-NET, 2 - NETDAY');
$cols['BLOKADA'] = array('label'=>'B', 'title'=>'BLOKADA: Informacja o blokadzie');
$cols['P_NAME'] = array('label'=>'imie i nazwisko', 'title'=>'P_NAME');
$cols['P_ADDRESS_STREET'] = array('label'=>'adres', 'title'=>'P_ADDRESS_STREET');
$cols['A_STATUS'] = array('label'=>'status', 'title'=>'A_STATUS');
$cols['A_STATUS_UPDATE_DATE'] = array('label'=>'data aktualizacji', 'title'=>'A_STATUS_UPDATE_DATE');
$cols['PAY_SALDO'] = array('label'=>'saldo', 'title'=>'PAY_SALDO');
$cols['PAY_FVAT'] = array('label'=>'zaległe faktury', 'title'=>'PAY_FVAT');
$cols['PAY_DATE_FIRST_FVAT'] = array('label'=>'data wymagalności pierwszej faktury', 'title'=>'PAY_DATE_FIRST_FVAT');
$cols['PAY_DATE'] = array('label'=>'data wymagalności ostatniej faktury', 'title'=>'PAY_DATE');
$cols['LAST_PAY_DATE'] = array('label'=>'data ostatniej wpłaty', 'title'=>'LAST_PAY_DATE');
$cols['PAY_TERM'] = array('label'=>'termin płatności', 'title'=>'PAY_TERM');
$out_cols = array();
foreach ($cols as $k_field => $v_params) {
$out_cols[] = '"' . $v_params['label'] . '"';
}
echo implode(";", $out_cols) . "
\n";
foreach ($items as $k_id => $user) {
$out_cols = array();
$out_cols[] = '"' . $k_id . '"';
$out_cols[] = '"' . $user->is_firma . '"';
if ($user->BILLING_OWNER == 1) {
$out_cols[] = '"' . 'BN' . '"';
} else if ($user->BILLING_OWNER == 2) {
$out_cols[] = '"' . 'ND' . '"';
} else {
$out_cols[] = '"' . '0' . '"';
}
$out_cols[] = '"' . (($user->STATUS == 9)? "Z" : "0") . '"';
$out_cols[] = '"' . $user->P_NAME.' '.$user->P_NAME_SECOND . '"';
$out_cols[] = '"' . "ul. {$user->P_ADDRESS_STREET} {$user->P_ADDRESS_HOUSE}/{$user->P_ADDRESS_HOME}, {$user->P_ADDRESS_POST_CODE} {$user->P_ADDRESS_CITY}" . '"';
$out_cols[] = '"' . $user->A_STATUS . '"';
$out_cols[] = '"' . $user->A_STATUS_UPDATE_DATE . '"';
$out_cols[] = '"' . (($user->PAY_SALDO != 0)? "{$user->PAY_SALDO} zł" : "0") . '"';
$out_cols[] = '"' . $user->PAY_FVAT . '"';
$out_cols[] = '"' . (($user->PAY_DATE_FIRST_FVAT != '0000-00-00')? $user->PAY_DATE_FIRST_FVAT : '') . '"';
$out_cols[] = '"' . (($user->PAY_DATE != '0000-00-00')? $user->PAY_DATE : '') . '"';
$out_cols[] = '"' . (($user->LAST_PAY_DATE && $user->LAST_PAY_DATE != '0000-00-00')? $user->LAST_PAY_DATE : '---') . '"';
$out_cols[] = '"' . (($user->PAY_TERM != '0000-00-00')? $user->PAY_TERM : '') . '"';
echo implode(";", $out_cols) . "
\n";
}
}
public static function form($q = '') {
SEF("MENU");
MENU();
echo'';// .box-blue
}
public static function css() {
echo'';
}
public static function user_umowy(&$user) {
Lib::loadClass('Tree');
Lib::loadClass('TreeHelper');
// build tree by P_ID - array or 0
function Tree_Helper__create_tree_rec($p_id, &$items) {
static $rec_ind;
$rec_ind++;
$ret = array();
if (!array_key_exists($p_id, $items)) {
return false;
} else {
$r = $items[$p_id];
if (empty($r->sub)) {
return false;
}
foreach ($r->sub as $k_id => $v) {
$ret[$k_id] = Tree_Helper__create_tree_rec($k_id, $items);
}
return $ret;
}
}
function tree_callback__show_item_from_SERVICES(&$r, &$tree) {
$cls = array();
$cls[] = 'HANGUP_STATUS-' . $r->HANGUP_STATUS;
$cls = (!empty($cls))? ' class="'.implode(' ', $cls).'"' : '';
echo'';
echo'';
$out_id = ($r->ID < 10)? ' '.$r->ID.'' : $r->ID;
echo''.$out_id.'';
//echo'ID.'" class="btn" title="'."Edit".'">'."E".'';
$st_status_bg = '';
if ($r->A_STATUS == 'NORMAL') {
$st_status_bg = '#8F8;';
} else if ($r->A_STATUS == 'WAITING') {
$st_status_bg = '#F8F;';
} else if ($r->A_STATUS == 'DELETED') {
$st_status_bg = '#ccc;';
} else if ($r->A_STATUS == 'OFF_SOFT') {
$st_status_bg = '#F99;';
} else if ($r->A_STATUS == 'OFF_HARD') {
$st_status_bg = '#eee;';
}
$st_status_bg = ($st_status_bg)? 'background-color:'.$st_status_bg : '';
echo'';
echo''.$r->A_STATUS.'';
echo' '.$r->description.'';
if ($r->ID_OFFERS) {
echo' ' . "(Nr oferty: {$r->ID_OFFERS})" . '';
} else {
echo' '."Brak numeru oferty!".'';
}
$st_status_bg = '';
if ($r->HANGUP_STATUS == 'NORMAL') {
$st_status_bg = 'background-color:#8F8;';
} else if ($r->HANGUP_STATUS == 'WAITING') {
$st_status_bg = 'background-color:#F8F;';
} else if ($r->HANGUP_STATUS == 'DELETED') {
$st_status_bg = 'background-color:#ccc;';
} else if ($r->HANGUP_STATUS == 'OFF_SOFT') {
$st_status_bg = 'background-color:#F99;';
} else if ($r->HANGUP_STATUS == 'OFF_HARD') {
$st_status_bg = 'background-color:#f00;color:#fff;';
}
echo ' ' . "{$r->HANGUP_STATUS} ({$r->HANGUP_FROM} - {$r->HANGUP_TILL}) - {$r->A_STATUS_DESC}" .'';
echo'';
echo'';
echo''."\n";
}
$uslugi_l2 = WindykacjaStatsModel::get_uslugi_from_l2($user);
$umowy_l2 = WindykacjaStatsModel::get_umowy_from_l2($user);
$uslugi_l2_nieprzypisane = array();
foreach ($uslugi_l2 as $k_id => $h) {
$uslugi_l2_nieprzypisane[$k_id] = true;
}
if (is_array($umowy_l2) && !empty($umowy_l2)) {
if (is_array($uslugi_l2) && !empty($uslugi_l2)) {
foreach ($umowy_l2 as $k_umowa_ind => $h_umowa) {
$umowy_l2[$k_umowa_ind]['uslugi'] = array();
foreach ($uslugi_l2 as $k_id => $h_usluga) {
if ($h_usluga['ID_DEALS_TABLE'] == $h_umowa['ID']) {
$umowy_l2[$k_umowa_ind]['uslugi'][$k_id] = $h_usluga;
if (isset($uslugi_l2_nieprzypisane[$k_id])) unset($uslugi_l2_nieprzypisane[$k_id]);
}
}
}
}
}
echo'';
$ARGS['ZAPISZ'] = true;
Lay_Html_Toggle::start("Aktualne umowy i usługi ", array('open'=>(($ARGS['ZAPISZ'])? false : true)));
//echo'umowy_l2: ';print_r($umowy_l2);echo'
';
echo'';
//echo''."Aktualne umowy".''." (l2)".''.'';
if ($umowy_l2 == -1) {
echo'| ';
echo''."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'';
echo' |
';
} else if (empty($umowy_l2)) {
echo'| '."Brak umów.".' |
';
}
else {
$tbl_umowy_cols = 5;
echo'';
echo'';
echo'| '."Nr umowy".' | ';
echo''."Prefix".' | ';
echo''."Data umowy".' | ';
echo''."Termin".' | ';
echo''."".' | ';
echo'
';
echo'';
$t = 0;
foreach ($umowy_l2 as $h) {
echo'';
echo'| ' . $h['P_DEALNUMBER'] . ' | ';
echo'' . $h['P_DEALPREFIX'] . ' | ';
echo'' . $h['P_DEALDATE'] . ' | ';
echo'' . $h['P_DEALDATE_TERM'] . ' | ';
echo'';
if ($h['Wcześniejszy Nr umowy']) echo "Wcześniejszy Nr umowy: ".$h['Wcześniejszy Nr umowy'].'
';// TODO: Undefined index
if ($h['DEALDESC']) echo "Notatki: ".$h['DEALDESC'].'
';
echo "Koniecznosc podpisania aneksu lub innego dokumentu: ".$h['ANEX_NEEDED'].'
';
echo "Data potwierdzenia podpisania aneksu/dokumentu: ".$h['ANEX_CONFIRM_DATE'].'
';
echo' | ';
echo'
';
if (!empty($h['uslugi'])) {
// TODO: make TREE by P_ID_SERVICES
$items = array();
foreach ($h['uslugi'] as $k_id_usluga => $h_suluga) {
$h_suluga['sub'] = array();
$items[$k_id_usluga] = (object)$h_suluga;
}
$uslugi_tree = array();// tree root
foreach ($items as $k_id => $r) {
if ($r->P_ID == 0) {
$uslugi_tree[$r->ID] = array();
if (array_key_exists($r->P_ID, $items)) {
$items[$r->P_ID]->sub[$r->ID] = true;
}
} else {// array
if (array_key_exists($r->P_ID, $items)) {
$items[$r->P_ID]->sub[$r->ID] = true;
}
}
}
ksort($uslugi_tree);
// set up has_childrens
foreach ($items as $k_id => $r) {
$items[$k_id]->has_childrens = !empty($r->sub);
}
foreach ($uslugi_tree as $k_id => $v_childrens) {
if (array_key_exists($k_id, $items)) {
$uslugi_tree[$k_id] = Tree_Helper__create_tree_rec($k_id, $items);
}
}
echo'';
echo'| '." ".' | ';
echo'';
$tree = new Tree('SERVICES');
$tree->set_parent_id_col('P_ID');
$tree->set_param('show_item_callback', "tree_callback__show_item_from_SERVICES");
$tree->set_param('ajax', "NIE");
$tree->set_param('rozwin', true);
$tree->show_css();
$tree->show_js();
$_COOKIE[$tree->cookie_name] = implode(' ', array_keys($items));
echo "\n".' '."\n";
$tree->show_rec_by_tree($uslugi_tree, $items);
echo ' '."\n";
echo' | ';
echo'
';
}
}
}
echo'
';
if (!empty($uslugi_l2_nieprzypisane)) {
echo'';
echo''."Nieprzypisane usługi".''." (l2)".''.'';
echo'';
echo'';
echo'| '."ID".' | ';
echo''."usługa".' | ';
echo''."status".' | ';
echo''."nr umowy".' | ';
echo''."id oferty".' | ';
echo'
';
echo'';
foreach ($uslugi_l2_nieprzypisane as $k_id => $v_bool) {
$h_usluga = $uslugi_l2[$k_id];
echo'';
echo'| ' . $h_usluga['ID'] . ' | ';
echo'' . $h_usluga['description'] . ' | ';
echo'' . $h_usluga['A_STATUS_DESC'] . ' | ';
echo'' . '';
if (array_key_exists($h['ID_DEALS_TABLE'], $umowy_l2)) {
echo $umowy_l2[$h['ID_DEALS_TABLE']]['P_DEALNUMBER'];
} else {
//TODO: get P_DEALNUMBER from DEALS_TABLE where ID = $h['ID_DEALS_TABLE']
}
echo'' . ' | ';
echo'';
if ($h_usluga['ID_OFFERS']) {
echo'' . $h_usluga['ID_OFFERS'] . '';
} else {
echo''."Brak!".'';
}
echo' | ';
echo'
';
}
echo'
';
}
Lay_Html_Toggle::end();
$umowy_l3 = WindykacjaStatsModel::get_umowy_from_l3($user);
$ARGS['ZAPISZ'] = true;
Lay_Html_Toggle::start("Aktualne usługi".''." (l3)".'', array('open'=>(($ARGS['ZAPISZ'])? false : true)));
//echo'umowy_l3: ';print_r($umowy_l3);echo'
';
echo'';
if (empty($umowy_l3)) {
echo'| '."Brak usług.".' |
';
}
else {
echo'';
echo'';
echo'| '."ID".' | ';
echo''."status".' | ';
echo''."data dok".' | ';
echo''."adres".' | ';
echo''."usługa".' | ';
echo''."S_OTHER_INFO".' | ';
echo''. "nr klienta" .' | ';
echo''."nr usługi".' | ';
echo''."nr oferty".' | ';
echo''."data umowy".' | ';
echo''."nr umowy".' | ';
echo''."termin umowy".' | ';
echo''."abonament".' | ';
echo''."INICJALY HANDLOWCA".' | ';
echo''."RODZAJ DZIALANIA HANDLOWEGO".' | ';
echo'
';
echo'';
echo'| '."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo''."OLD".' | ';
echo''."NEW".' | ';
echo'
';
echo'';
foreach ($umowy_l3 as $h) {
echo'';
echo'| ' . $h['ID'] . ' | ';
echo'' . $h['A_STATUS'] . ' | ';
echo'' . '' . $h['DATA_DOK'] . '' . ' | ';
echo'' . $h['S_ADDRESS_STREET'] . ' | ';
echo'' . '' . $h['P_SERVICE'] . '' . ' | ';
echo'' . $h['S_OTHER_INFO'] . ' | ';
echo'' . $h['ID_BILLING_USERS_OLD'] . ' | ';
echo'' . $h['ID_BILLING_USERS_NEW'] . ' | ';
echo'' . $h['ID_SERVICES_OLD'] . ' | ';
echo'' . $h['ID_SERVICES_NEW'] . ' | ';
echo'' . $h['P_OFFER_ID_OLD'] . ' | ';
echo'' . $h['P_OFFER_ID_NEW'] . ' | ';
echo'' . '' . $h['P_DEAL_DATE_OLD'] . '' . ' | ';
echo'' . '' . $h['P_DEAL_DATE_NEW'] . '' . ' | ';
echo'' . $h['P_DEALNUMBER_OLD'] . ' | ';
echo'' . $h['P_DEALNUMBER_NEW'] . ' | ';
echo'' . '' . $h['P_DEALTERM_OLD'] . '' . ' | ';
echo'' . '' . $h['P_DEALTERM_NEW'] . '' . ' | ';
echo'' . $h['MONTH_COST_OLD'] . ' | ';
echo'' . $h['MONTH_COST_NEW'] . ' | ';
echo'' . $h['INICJALY_HANDLOWCA'] . ' | ';
echo'' . $h['RODZAJ_DZIALANIA_HANDLOWEGO'] . ' | ';
echo'
';
}
}
echo'
';
Lay_Html_Toggle::end();
}
public static function user_historia_platnosci(&$user) {
echo'';
Lay_Html_Toggle::show_js();
$billing_docs = WindykacjaStatsModel::get_bill_dosc_by_date($user);
// add events
$umowy_l2 = WindykacjaStatsModel::get_umowy_from_l2($user);
//echo'billing_docs ';print_r($billing_docs);echo'
';
//echo'billing_docs ';print_r(reset($billing_docs));echo'
';
//echo'umowy_l2 ';print_r($umowy_l2);echo'
';
if (!empty($umowy_l2)) {
foreach ($umowy_l2 as $h) {
$billing_docs->add_event($h['P_DEALDATE'], 'UMOWA', $h);
if ($h['P_DEALDATE_TERM'] != '0000-00-00') {// < date('Y-m-d')) {
$billing_docs->add_event($h['P_DEALDATE_TERM'], 'KONIEC UMOWY', $h);
}
}
}
$user_hist_events = WindykacjaStatsHelper::get_user_hist_status_events($user);
if (!empty($user_hist_events)) {
foreach ($user_hist_events as $v_hist) {
$bill_doc = (array)$v_hist;
$billing_docs->add_event($v_hist->A_STATUS_UPDATE_DATE, 'HIST_STATUS', $bill_doc);
}
}
$user_hist_events = WindykacjaStatsHelper::get_user_hist_phone_events($user);
if (!empty($user_hist_events)) {
foreach ($user_hist_events as $v_hist) {
$bill_doc = (array)$v_hist;
$billing_docs->add_event(substr($v_hist->A_RECORD_UPDATE_DATE, 0, 10), 'HIST_PHONE', $bill_doc);
}
}
$user_hist_events = WindykacjaStatsHelper::get_user_bad_address_events($user);
if (!empty($user_hist_events)) {
foreach ($user_hist_events as $v_hist) {
$bill_doc = (array)$v_hist;
$billing_docs->add_event(substr($v_hist->A_RECORD_UPDATE_DATE, 0, 10), 'HIST_BAD_ADDRESS', $bill_doc);
}
}
// add today
$bill_doc = array();
$bill_doc['saldo'] = $billing_docs->get_saldo();
$billing_docs->add_event(date('Y-m-d'), 'TODAY', $bill_doc);
$billing_docs->sort_docs();
//$billing_docs = array_reverse($billing_docs, true);
//echo'billing_docs: ';print_r($billing_docs);echo'
';
$billing_docs_arr = $billing_docs->get_docs();
$types_finanse = array();
$types_finanse[] = 'KW';
$types_finanse[] = 'KP';
$types_finanse[] = 'WB_MASS';
$types_finanse[] = 'WB';
$types_finanse[] = 'KORV';
$types_finanse[] = 'FVAT';
$saldo_all = 0;
$out_tbl = array();
foreach ($billing_docs_arr as $k_data => $v_docs_arr) {
foreach ($v_docs_arr as $k_ind => $v_doc) {
$out_tr = array();
$out_tr['data'] = $k_data;
if ($k_data == date("Y-m-d")) $out_tr['class'] = 'today';
$out_tr['ID'] = '';
$out_tr['nr'] = '';
$out_tr['winien'] = '';
$out_tr['ma'] = '';
$out_tr['saldo'] = '';
$out_tr['saldo_all'] = '';
if ($v_doc->get_class() == 'BILLING') {
$saldo_all += $v_doc->get_saldo();
$out_tr['ID'] = $v_doc->get_type();
$type_desc = WindykacjaStatsModel::get_billing_type_desc($v_doc->get_type());
if ($type_desc) {
if ($v_doc->get_type() == 'FVAT') $type_desc .= "\n Wystawiona: ".$v_doc->get('BILL_DATE')."\n Termin płatności: ".$v_doc->get('PAYMENT_TERM');
$out_tr['ID'] = ''.$out_tr['ID'].'';
// TODO: podglad faktury
//if ($h['type'] == 'FVAT') $out_tr['ID'] .= ' ID."&task=view_faktura&id=".$h['ID'].'" target="_blank">'.'
'.'';
}
$docNr = $v_doc->get('nr');
if ('FVAT' == $v_doc->get_type()) {
$out_tr['ID'] .= ' '."({$docNr})".'';
}
$out_tr['nr'] = $docNr;
$out_tr['winien'] = number_format($v_doc->get('WINIEN'), 2, ',', ' ');
$out_tr['ma'] = number_format($v_doc->get('MA'), 2, ',', ' ');
$out_tr['saldo'] = number_format($v_doc->get_saldo(), 2, ',', ' ');
$out_tr['saldo_all'] = number_format($saldo_all, 2, ',', ' ');
// style
if ($out_tr['winien'] == '0,00') $out_tr['winien'] = '' . $out_tr['winien'] . '';
if ($out_tr['ma'] == '0,00') $out_tr['ma'] = '' . $out_tr['ma'] . '';
}
else if ($v_doc->get_class() == 'EVENT') {
if ($v_doc->get_type() == 'TODAY') {
$out_tr['class'] = 'today';
$vDocSaldoData = $v_doc->get_data();
$v_doc_saldo = V::get('saldo', '', $vDocSaldoData);
$out_tr['saldo_all'] = number_format($v_doc_saldo, 2, ',', ' ');
}
else if ($v_doc->get_type() == 'HIST_STATUS') {
// search for koresp files
// TODO: mv to ::get_koresp_file($id_koresp);
$id_koresp = intval($v_doc->get('ID_KORESP'));
$found_files = array();
{
$CNF = new stdClass();
$CNF->koresp_dir = '/home/samba/BIALL-NET/PISMA/OUT';
$CNF->koresp_www_path = '/se-dev/PISMA/OUT';
if ($id_koresp > 0) {
$pattern = $CNF->koresp_dir . '/' . '*.' . $id_koresp . '.pdf';
$file_tmp_list = glob($pattern);//, GLOB_NOSORT);
if (!empty($file_tmp_list)) {
foreach ($file_tmp_list as $v_file) {
$test_file = explode('/', $v_file);
$test_file = end($test_file);
$found_files[] = $CNF->koresp_www_path . '/' . $test_file;
}
}
}
// return $found_files;
}
switch ($v_doc->get('A_STATUS')) {
case 'WAITING':
$out_tr['add'] = "powrót do oczekujących";
break;
case 'waiting-wezwanie2':
$out_tr['add'] = "wezwanie do zapłaty - ID_KORESP: ".$v_doc->get('ID_KORESP')." (termin: ".$v_doc->get('PAY_TERM').") ";
break;
case 'waiting-krd':
$out_tr['add'] = "wezwanie do zapłaty ost. - ID_KORESP: ".$v_doc->get('ID_KORESP')." (termin: ".$v_doc->get('PAY_TERM').") ";
break;
case 'N/S;':
$out_tr['add'] = "ID_KORESP: ".$v_doc->get('ID_KORESP')." (" . $v_doc->get('K_ZAWARTOS') . ")";// TODO: get opis from koresp
//echo '('.$v_doc->get('ID_KORESP').', '.$v_doc->get('K_ZAWARTOS').'='.substr($v_doc->get('K_ZAWARTOS'), 0, strlen('rozwiazanie umowy')).')';
if ($v_doc->get('ID_KORESP') > 0 && substr($v_doc->get('params'), 0, strlen('rozwiazanie umowy')) == 'rozwiazanie umowy') {
$out_tr['add'] .= " " . 'ID}&id_koresp=".$v_doc->get('ID_KORESP').'" target="_blank" title="' . "Generuj druk rozwiązania umowy" . '">' . "druk" . '';
}
break;
default:
$out_tr['add'] = "zmiana statusu na: ".$v_doc->get('A_STATUS')." ";
}
if ($id_koresp > 0) {
if (!empty($out_tr['add']) && !empty($found_files)) {
$add_link_koresp = reset($found_files);// first file from koresp
$out_tr['add'] .= " " . '' . "P" . '';
}
//$koresp_edit_link = "?MENU_INIT=IN7_DZIENNIK_KORESP_EDIT&ARG1=EDIT&ARG1_VAL=" . $id_koresp;
Lib::loadClass('ProcesHelper');
$zasobKorespID = ProcesHelper::getZasobTableID('IN7_DZIENNIK_KORESP');
$koresp_edit_link = "index.php?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID={$zasobKorespID}#EDIT/{$id_koresp}";
$out_tr['add'] .= " " . '' . "KOR" . '';
}
}
else if ($v_doc->get_type() == 'HIST_PHONE') {
$date = $v_doc->get('PAY_TERM');
if ($date == 'N/S;') $date = '';
$lastPhoneStatus = $v_doc->get('LAST_PHONE_STATUS');
if ($lastPhoneStatus == 'N/S;') $lastPhoneStatus = '';
$lastSmsStatus = $v_doc->get('LAST_SMS_STATUS');
if ($lastSmsStatus == 'N/S;') $lastSmsStatus = '';
$lastUpdateDate = $v_doc->get('A_RECORD_UPDATE_DATE');
if (!empty($lastPhoneStatus)) {
switch ($lastPhoneStatus) {
case 'nie_zaplaci':
$out_tr['add'] = "nie zapłaci";
break;
case 'zaplaci_w_terminie':
$out_tr['add'] = "zapłaci w terminie {$date} " . '' . "(ustalono {$lastUpdateDate})" . '';
break;
case 'zaplaci_za_1mc':
$out_tr['add'] = "zapłaci miesiąc później {$date} " . '' . "(ustalono {$lastUpdateDate})" . '';
break;
case 'zaplaci_za_2mc':
$out_tr['add'] = "zapłaci 2 miesiące później {$date} " . '' . "(ustalono {$lastUpdateDate})" . '';
break;
case 'zaplaci_za_3mc':
$out_tr['add'] = "zapłaci 3 miesiące później {$date} " . '' . "(ustalono {$lastUpdateDate})" . '';
break;
default:
$out_tr['add'] = "kontakt z klientem: {$lastPhoneStatus} " . '' . "(ustalono {$lastUpdateDate})" . '';
}
} else if (!empty($lastSmsStatus)) {
$lastSmsId = $v_doc->get('LAST_SMS_MSG_ID');
$out_tr['add'] = "SMS: {$lastSmsStatus} " . '' . "(wysłano {$lastUpdateDate})" . '';
}
} else if ($v_doc->get_type() == 'HIST_BAD_ADDRESS') {
if ($v_doc->get('BAD_ADDRESS')) {
$out_tr['add'] = "błędny adres zameldowania ";
} else {
$out_tr['add'] = "poprawa adresu zameldowania ";
}
if ($v_doc->get('ID_KORESP') > 0) {
$out_tr['add'] .= " - ID Koresp. (" . 'get('ID_KORESP') . '" target="_blank">' . $v_doc->get('ID_KORESP') . '' . ")";
}
} else {
$out_tr['ID'] = $v_doc->get_type().' '."(".$v_doc->get('ID').")".'';// TODO: dla KP,KW: ID_BILLING_NUMBERS zamiast ID
if ($v_doc->get_type() == 'UMOWA') {
$out_tr['add'] = "umowa nr ".$v_doc->get('P_DEALNUMBER')." (termin ".$v_doc->get('P_DEALDATE_TERM').")";
} else if ($v_doc->get_type() == 'KONIEC UMOWY') {
$out_tr['add'] = "zakończenie umowy nr ".$v_doc->get('P_DEALNUMBER')." (z dnia ".$v_doc->get('P_DEALDATE').")";
}
}
}
//$out_tbl[] = $out_tr;// add to the end
array_unshift($out_tbl, $out_tr);// add to the begining
}
}
echo'';
$last_year = '';
$cols = array();
$cols['data'] = "data";
$cols['ID'] = "ID";
$cols['nr'] = "nr";
$cols['winien'] = "winien";
$cols['ma'] = "ma";
//$cols['saldo'] = "saldo";
$cols['saldo_all'] = "saldo";
$cols['add'] = "uwagi";
echo''."Historia płatności".''." (l1)".''.'
';
echo'';
echo'';
echo'';
echo'| '."rok".' | ';
foreach ($cols as $col_name => $col_label) {
echo'' . $col_label . ' | ';
}
echo'
';
echo'';
foreach ($out_tbl as $out_tr) {
$change_year = false;
$k_data = $out_tr['data'];
$rok = substr($k_data, 0, 4);
if ($rok != $last_year) {
$change_year = true;
}
$cls = array();
if ($change_year) $cls[] = 'change-year';
if ($add_cls = V::get('class', '', $out_tr)) $cls[] = $add_cls;
$cls = (!empty($cls))? ' class="'.implode(' ', $cls).'"' : '';
echo'';
echo'';
echo ($change_year)? $rok : " ";
echo' | ';
$last_year = $rok;
foreach ($cols as $col_name => $col_label) {
//foreach ($out_tr as $k_col_name => $v_val) {
if (array_key_exists($col_name, $out_tr)) {
echo'' . $out_tr[$col_name] . ' | ';
} else {
echo''." ".' | ';
}
}
echo'';
}
echo'
';
echo'
';
echo'
';
WindykacjaStatsHelper::update_stats($user, $billing_docs);
}
public static function view_faktura($faktura_id) {
// https://l1.webone.pl/modules/make_billing/edit.php?mod=make_billing&form=make_faktura&adm=edit&uid=11381&fpos=&dz=
// https://l1.webone.pl/modules/make_billing/edit.php?mod=make_billing&form=html_faktura&adm=edit&uid=11381&fpos=&dz=&close=0&doc=483930&doctype=1
echo'{'.$faktura_id.'}';
}
public static function zadluzenia(&$user) {
$THIS_DATA_DO = null;// TODO: pobierac z PAY_TERM z tabeli USERS2_WINDYKACJA_STATUS
$post_sent = (!empty($_POST['sbmt']));
$post_DATA_DO = (isset($_POST['DATA_DO']))? $_POST['DATA_DO'] : date("Y-m-d", mktime(0,0,0,date('m'), date('d') + 14, date('Y')));
if (!$post_sent) {
echo'';
return;
} else {
$THIS_DATA_DO = $post_DATA_DO;
}
self::wezwanie($user, $THIS_DATA_DO);
}
public static function wezwanie_ostateczne(&$user, $wezwanie_termin) {
self::wezwanie($user, $wezwanie_termin, true);
}
public static function zestawienie_vectra_nadplaty() {
$usersList = array();
$db = DB::getDB();
$sql_select = "
c.`ID` as ID
, c.`ID` as id_users
, c.`P_NAME`
, c.`P_NAME_SECOND`
, c.`P_ADDRESS_STREET`
, c.`P_ADDRESS_HOME`
, c.`P_ADDRESS_HOUSE`
, c.`P_ADDRESS_CITY`
, c.`P_ADDRESS_POST_CODE`
, c.`P_ADDRESS_REGION`
, c.`P_PESEL`
, c.`P_PHONE`
, c.`P_OTHER_DOC`
, c.`user_mail_contact`
, c.`is_firma`
, w.`ID` as WINDYKACJA_ID
, w.`ID_BILLING_USERS`
, w.`A_STATUS`
, w.`A_STATUS_UPDATE_DATE`
, w.`USER_PAY_TERM_ADD`
, w.`L_APPOITMENT_DATE`
, w.`L_APPOITMENT_USER`
, w.`L_APPOITMENT_INFO`
, w.`PAY_DATE`
, w.`PAY_DATE_FIRST_FVAT`
, w.`PAY_TERM`
, w.`PAY_SALDO`
, w.`PAY_FVAT`
, w.`wezwanie1_DATE`
, w.`wezwanie2_DATE`
, w.`ID_KORESP`
, w.`LAST_PAY_DATE`
, w.`BAD_ADDRESS`
, c.`STATUS`
, c.`BILLING_OWNER`
, w.`NR_SPRAWY_KRD`
, w.`NR_SPRAWY_SAD`
, w.`NR_SPRAWY_KOMORNIK`
, w.`LAST_FVAT_PAY_TERM`
";
$sqlWhereAdd = "
and (w.`LAST_FVAT_PAY_TERM`>='2014-12-01'
or ( w.`LAST_FVAT_PAY_TERM`='0000-00-00'
and w.`LAST_FVAT_SELL_DATE`>='2014-12-01'
)
)
and c.`ID_BILLING_PREFIXES` in('0','1')
and c.`BILLING_OWNER` in('1')
";
$sql = "select {$sql_select}
from `USERS2_WINDYKACJA_STATUS` as w
join `COMPANIES` as c on(c.`ID`=w.`ID_BILLING_USERS`)
where c.`A_ADM_COMPANY`='19994_PODMIOT_Vectra_wlasciciel'
{$sqlWhereAdd}
";
if (V::get('DBG_SQL', '', $_GET)) {
header('Content-Type: text/plain; charset=utf-8');
echo $sql;
exit;
}
$res = $db->query($sql);
if ($db->has_errors()) {
print_r($db->get_errors());
die('die L.' . __LINE__);
}
while ($r = $db->fetch($res)) {
$usersList[$r->ID_BILLING_USERS] = $r;
}
if (V::get('DBG_ILE', '', $_GET)) {
echo 'ilosc: ' . count($usersList) . '
' . '';
print_r($usersList);
echo '
';
exit;
}
if (V::get('DBG_IDS', '', $_GET)) {
header('Content-Type: text/plain; charset=utf-8');
echo 'ilosc: ' . count($usersList) . "\n";
echo implode("\n", array_keys($usersList));
exit;
}
$limit = V::get('DBG_LOOP_LIMIT', 100000, $_GET, 'int');
if(V::get('DBG_LOOP_LIMIT', 0, $_GET, 'int')){echo "Loimit: {$limit}\n";}
$date_limit = V::get('DATE_LIMIT', date("Y-m-d"), $_GET);
$date_limit_from = '2014-12-01';
$csvFileName = "Zestawienie-nadplaty-do-{$date_limit}";
header('Content-Type: text/csv; charset=utf-8');
header("Content-Disposition: attachment; filename={$csvFileName}.csv");
$showCsvHeader = true;
foreach ($usersList as $userId => $user) {
$billing_docs = WindykacjaStatsModel::get_bill_dosc_by_date($user, $date_limit);
$billing_docs->sort_docs();
$saldo = $billing_docs->get_saldo();
if ($saldo <= 0) {
continue;
}
if(V::get('DBG_LOOP', '', $_GET)){echo "{$userId}:{$saldo}\n";}
$incomeDocs = $billing_docs->getLastIncomeDocsForSaldo($saldo);
if(V::get('DBG_LOOP', '', $_GET)){print_r($incomeDocs);echo "\n";}
//WindykacjaStatsModel::update_doc_number($incomeDocs);
$out_tbl = array();
foreach ($incomeDocs as $v_fvat) {
$out_tr = array();
$out_tr['ID'] = $v_fvat['ID'];
$out_tr['nr'] = $v_fvat['NUMBER'] . '/' . $v_fvat['ID_BILLING_PREFIXES'];
$out_tr['wplata'] = number_format($v_fvat['MA'], 2, '.', '');
$out_tr['data'] = $v_fvat['BILL_DATE'];
$out_tr['pozostalo'] = number_format($v_fvat['MA_POZOSTALO'], 2, '.', '');
array_push($out_tbl, $out_tr);// add to the begining
}
{// csv
$csvLine = array();
$csvLine[] = '"id klienta"';
$csvLine[] = '"nr dokumentu"';
$csvLine[] = '"kwota brutto"';
$csvLine[] = '"data wpłaty"';
$csvLine[] = '"pozostało"';
if ($showCsvHeader) {
echo implode(';', $csvLine) . ";\n";
$showCsvHeader = false;
}
foreach ($out_tbl as $tr) {
$csvLine = array();
$csvLine[] = '"' . $userId . '"';
$csvLine[] = '"' . $tr['nr'] . '"';
$csvLine[] = '"' . $tr['wplata'] . '"';
$csvLine[] = '"' . $tr['data'] . '"';
$csvLine[] = '"' . $tr['pozostalo'] . '"';
echo implode(';', $csvLine) . ";\n";
}
}
//echo 'incomeDocs: ';print_r($incomeDocs);echo'
';
//echo 'ilosc('.count($out_tbl).') ' . "client({$userId}): ";print_r($out_tbl);echo'';
if (--$limit < 0) {
echo '
BREAK';
break;
}
}
exit;
}
public static function zestawienie_vectra_zadluzenia() {
$usersList = array();
$db = DB::getDB();
$sql_select = "
c.`ID` as ID
, c.`ID` as id_users
, c.`P_NAME`
, c.`P_NAME_SECOND`
, c.`P_ADDRESS_STREET`
, c.`P_ADDRESS_HOME`
, c.`P_ADDRESS_HOUSE`
, c.`P_ADDRESS_CITY`
, c.`P_ADDRESS_POST_CODE`
, c.`P_ADDRESS_REGION`
, c.`P_PESEL`
, c.`P_PHONE`
, c.`P_OTHER_DOC`
, c.`user_mail_contact`
, c.`is_firma`
, w.`ID` as WINDYKACJA_ID
, w.`ID_BILLING_USERS`
, w.`A_STATUS`
, w.`A_STATUS_UPDATE_DATE`
, w.`USER_PAY_TERM_ADD`
, w.`L_APPOITMENT_DATE`
, w.`L_APPOITMENT_USER`
, w.`L_APPOITMENT_INFO`
, w.`PAY_DATE`
, w.`PAY_DATE_FIRST_FVAT`
, w.`PAY_TERM`
, w.`PAY_SALDO`
, w.`PAY_FVAT`
, w.`wezwanie1_DATE`
, w.`wezwanie2_DATE`
, w.`ID_KORESP`
, w.`LAST_PAY_DATE`
, w.`BAD_ADDRESS`
, c.`STATUS`
, c.`BILLING_OWNER`
, w.`NR_SPRAWY_KRD`
, w.`NR_SPRAWY_SAD`
, w.`NR_SPRAWY_KOMORNIK`
, w.`LAST_FVAT_PAY_TERM`
";
$sqlWhereAdd = "
and (w.`LAST_FVAT_PAY_TERM`>='2014-12-01'
or ( w.`LAST_FVAT_PAY_TERM`='0000-00-00'
and w.`LAST_FVAT_SELL_DATE`>='2014-12-01'
)
)
and c.`ID_BILLING_PREFIXES` in('0','1')
and c.`BILLING_OWNER` in('1')
";
if (V::get('DBG_NUM', '', $_GET)) {// number of clients with fvat after 2014-12-01
$sqlWhereAdd = "
and (w.`LAST_FVAT_PAY_TERM`>='2014-12-01'
or ( w.`LAST_FVAT_PAY_TERM`='0000-00-00'
and w.`LAST_FVAT_SELL_DATE`>='2014-12-01'
)
)
and c.`ID_BILLING_PREFIXES` in('0','1')
and c.`BILLING_OWNER` in('1')
";
}
$sql = "select {$sql_select}
from `USERS2_WINDYKACJA_STATUS` as w
join `COMPANIES` as c on(c.`ID`=w.`ID_BILLING_USERS`)
where c.`A_ADM_COMPANY`='19994_PODMIOT_Vectra_wlasciciel'
{$sqlWhereAdd}
";
if (V::get('DBG_SQL', '', $_GET)) {
header('Content-Type: text/plain; charset=utf-8');
echo $sql;
exit;
}
$res = $db->query($sql);
if ($db->has_errors()) {
print_r($db->get_errors());
die();
}
while ($r = $db->fetch($res)) {
$usersList[$r->ID_BILLING_USERS] = $r;
}
if (V::get('DBG_ILE', '', $_GET)) {
echo 'ilosc: ' . count($usersList) . '
' . '';
print_r($usersList);
echo '
';
exit;
}
if (V::get('DBG_IDS', '', $_GET)) {
header('Content-Type: text/plain; charset=utf-8');
echo 'ilosc: ' . count($usersList) . "\n";
echo implode("\n", array_keys($usersList));
exit;
}
$limit = 10000;
$date_limit = V::get('DATE_LIMIT', date("Y-m-d"), $_GET);
$date_limit_from = '2014-12-01';
$csvFileName = "Zestawienie-zadluzenia-do-{$date_limit}";
header('Content-Type: text/csv; charset=utf-8');
header("Content-Disposition: attachment; filename={$csvFileName}.csv");
$showCsvHeader = true;
foreach ($usersList as $userId => $user) {
$billing_docs = WindykacjaStatsModel::get_bill_dosc_by_date($user, $date_limit);
$billing_docs->sort_docs();
$fvat_arr = $billing_docs->get_unpaid_fvat();
WindykacjaStatsModel::update_doc_number($fvat_arr);
if (V::get('DBG_LAST_FVAT_TERM', '', $_GET)) {
$lastFvatDoc = $billing_docs->get_last_fvat_doc();
if ($lastFvatDoc) {
echo 'lastFvatDoc:
';
print_r($lastFvatDoc);
echo '
';
exit;
}
}
$user->hasActiveDeal = 0;
$sql = "select sum(t.`DEALS_ACTIVE`) as activeDeals
from `temp_DEALS_STATUS` as t
where t.`DEALS_ACTIVE`=1
and t.`ID_BILLING_USERS`='{$userId}'
";
$res = $db->query($sql);
if ($db->num_rows($res) > 0) {
$user->hasActiveDeal = 1;
}
$out_tbl = array();
foreach ($fvat_arr as $v_fvat) {
if ($v_fvat['BILL_DATE'] < $date_limit_from || $v_fvat['BILL_DATE'] > $date_limit) {
//echo 'pomin fvat:'."\n";print_r($v_fvat);
continue;
}
$out_tr = array();
$out_tr['ID'] = $v_fvat['ID'];
$out_tr['nr'] = $v_fvat['NUMBER'] . '/' . $v_fvat['ID_BILLING_PREFIXES'];
$out_tr['winien'] = number_format($v_fvat['WINIEN'], 2, '.', '');
$out_tr['data'] = $v_fvat['BILL_DATE'];
$out_tr['termin'] = $v_fvat['PAYMENT_TERM'];
$out_tr['pozostalo'] = number_format($v_fvat['WINIEN_POZOSTALO'], 2, '.', '');
array_unshift($out_tbl, $out_tr);// add to the begining
}
{// csv
$csvLine = array();
$csvLine[] = '"id klienta"';
$csvLine[] = '"nr dokumentu"';
$csvLine[] = '"kwota brutto"';
$csvLine[] = '"data faktury"';
$csvLine[] = '"termin płatności"';
$csvLine[] = '"pozostało do zapłaty"';
$csvLine[] = '"status klienta (9=blokada)"';
$csvLine[] = '"aktywne uslugi"';
if ($showCsvHeader) {
echo implode(';', $csvLine) . ";\n";
$showCsvHeader = false;
}
foreach ($out_tbl as $tr) {
$csvLine = array();
$csvLine[] = '"' . $userId . '"';
$csvLine[] = '"' . $tr['nr'] . '"';
$csvLine[] = '"' . $tr['winien'] . '"';
$csvLine[] = '"' . $tr['data'] . '"';
$csvLine[] = '"' . $tr['termin'] . '"';
$csvLine[] = '"' . $tr['pozostalo'] . '"';
$csvLine[] = '"' . $user->STATUS . '"';
$csvLine[] = '"' . $user->hasActiveDeal . '"';
echo implode(';', $csvLine) . ";\n";
}
}
//echo 'fvat_arr: ';print_r($fvat_arr);echo'
';
//echo 'ilosc('.count($out_tbl).') ' . "client({$userId}): ";print_r($out_tbl);echo'';
if (--$limit < 0) {
echo '
BREAK';
break;
}
}
exit;
}
public static function wezwanie(&$user, $wezwanie_termin, $wezwanie_ostateczne = false) {
$date_limit = date("Y-m-d");
$billing_docs = WindykacjaStatsModel::get_bill_dosc_by_date($user, $date_limit);
$billing_docs->sort_docs();
// sprawdz zadluzenie
if ($billing_docs->get_saldo() >= 0) {
echo'' . "Brak zadluzenia" . '
';
return;
}
else {
$fvat_arr = $billing_docs->get_unpaid_fvat();
//echo'saldo('.$billing_docs->get_saldo().') fvat_arr: ';print_r($fvat_arr);echo'';
//foreach ($fvat_arr as $key => $v) {
// echo'
'.number_format($v['WINIEN_POZOSTALO'], 4, ',', ' ');
//}
if (empty($fvat_arr)) {
echo''."Brak nieoplaconych faktur".'
';
return;
}
WindykacjaStatsModel::update_doc_number($fvat_arr);
$cols = array();
$cols['ID'] = "ID";
$cols['nr'] = "nr";
$cols['winien'] = "winien";
$cols['ma'] = "ma";
//$cols['saldo'] = "saldo";
$cols['saldo_all'] = "saldo";
$cols['add'] = " ";
$saldo_all = 0;
foreach ($fvat_arr as $f) {
$saldo_all += round($f['WINIEN_POZOSTALO'], 2);
}
$out_tbl = array();
foreach ($fvat_arr as $v_fvat) {
$out_tr = array();
$out_tr['ID'] = ' '."(".$v_fvat['ID'].")".'';// TODO: dla KP,KW: ID_BILLING_NUMBERS zamiast ID
$out_tr['nr'] = $v_fvat['NUMBER'] . '/' . $v_fvat['ID_BILLING_PREFIXES'];
$out_tr['winien'] = number_format($v_fvat['WINIEN'], 2, ',', ' ');
$out_tr['data'] = $v_fvat['BILL_DATE'];
$out_tr['termin'] = $v_fvat['PAYMENT_TERM'];
$out_tr['pozostalo'] = number_format($v_fvat['WINIEN_POZOSTALO'], 2, ',', ' ');
array_unshift($out_tbl, $out_tr);// add to the begining
}
$razem = $saldo_all;
$company = WindykacjaStatsModel::get_company($user);
$nr_konta = FunkcjeL1::bankowy_formatuj_nrach(FunkcjeL1::bankowy_make_nrach($company->NR_RACH_MASS_PAY, $user->ID_BILLING_USERS, 0));
echo'';
echo'';
if ($user->ID_KORESP) {
$koresp = DB::get_by_id('IN7_DZIENNIK_KORESP', $user->ID_KORESP);
if ($koresp) {
echo '
';
echo $koresp->ID_PROJECT . '-' .$koresp->ID . "/" . substr(date("Y"), 2);
echo '
';
}
}
echo '
';
echo'';
echo'';
//echo "„BIALL-NET” Sp. z o.o.
Otomin, ul. Słoneczna 43
80-174 Gdańsk";
echo $company->name1 . '
' . "ul. " . $company->ulica . " " . $company->numer_dom . '
' . $company->kod . " " . $company->miasto;
echo' | ';
echo'';
echo "Gdańsk, dnia ".date("Y-m-d");
echo' | ';
echo'
';
echo'';
echo'| '." ".' | ';
echo'';
echo' ';
echo "Numer klienta: ".$user->ID;
echo '
'."".$user->P_NAME." ".$user->P_NAME_SECOND;
// TODO: P_ADRESS_KORESP_1282
echo '
'."ul. ".$user->P_ADDRESS_STREET." ".$user->P_ADDRESS_HOUSE;
if ($user->P_ADDRESS_HOME) echo "/".$user->P_ADDRESS_HOME;
echo '
' . $user->P_ADDRESS_POST_CODE . " " . $user->P_ADDRESS_CITY;
echo' ';
echo' | ';
echo'
';
echo'
';
echo'
';
if ($wezwanie_ostateczne) {
echo "OSTATECZNE WEZWANIE DO ZAPŁATY";
} else {
echo "WEZWANIE DO ZAPŁATY";
}
echo'
';
echo'
'." Na podstawie art. 476 Kodeksu cywilnego (Dz. U. z 1964 r. nr 16, poz. 93, z późn. zmianami) wzywamy do natychmiastowego uregulowania należnej sumy, zgodnie z poniższym zestawieniem. ".'
';
echo '
';
echo '';
echo '';
echo '| '."Podstawa zobowiązania*".' | ';
echo ''."Nr dokumentu".' | ';
//echo ''."Kwota netto".' | ';
echo ''."Kwota brutto".' | ';
echo ''."Data faktury".' | ';
echo ''."Termin płatności".' | ';
echo ''."Pozostało do zapłaty".' | ';
echo '
';
echo '';
echo '';
echo'';
echo' | ';
echo''."Razem:".' | ';
echo''.FunkcjeL1::formatuj_grosze($razem).' | ';
echo'
';
echo '';
echo "\n";
foreach ($out_tbl as $out_tr) {
$k_data = $out_tr['data'];
$rok = substr($k_data, 0, 4);
$cls = array();
if ($add_cls = V::get('class', '', $out_tr)) $cls[] = $add_cls;
$cls = (!empty($cls))? ' class="'.implode(' ', $cls).'"' : '';
echo'';
echo'';
echo "Faktura Vat";
echo' | ';
//$file_total['PODSTAWA_NUMBER'] = $r['NUMBER']."/".$r['ID_BILLING_PREFIXES'];
//$file_total['PODSTAWA_DATA'] = date("Y-m-d",$r['BAF_TIMESTAMP']);
//$file_total['PAYMENT_TERM'] = date("Y-m-d",$r['TERMIN_PL']);
echo'';
echo $out_tr['nr'];
echo' | ';
echo'';
echo $out_tr['winien'];
echo' | ';
echo'';
echo '' . $out_tr['data'] . '';
echo' | ';
echo'';
echo '' . $out_tr['termin'] . '';
echo' | ';
echo'';
echo $out_tr['pozostalo'];
echo' | ';
echo'';
echo "\n";
}
echo '
';
echo'
'."Słownie do zapłaty: ".''." ".''.FunkcjeL1::slownie($razem).''." ".''.'
';
echo'
'."Wymienioną sumę prosimy przekazać na nasz rachunek bankowy
".$nr_konta." w terminie do dnia ".''." ".''.$wezwanie_termin.''." ".''.'
';
echo'
';
echo'- '."Od nieterminowych płatności mogą zostać naliczone odsetki ustawowe (art.481 § 1 KC).".'
';
echo'- '."W przypadku nieterminowej zapłaty nabywca zostanie obciążony wszelkimi kosztami windykacji oraz postępowania sądowego, które zostaną poniesione przez sprzedawcę w celu odzyskania należności.".'
';
echo '- ' . "W przypadku uregulowania należności przed otrzymaniem niniejszego wezwania, proszę potraktować je za bezprzedmiotowe." . '
';
echo'
';
echo'
';
echo'';
echo'| ';
echo' '."W razie problemów prosimy o kontakt z Biurem Obsługi Klienta tel. (58) 741-84-10 wew. 2,
e-mail: bok@biall.net.pl lub z Windykatorem: tel. 513-156-732, (58) 741-84-78".' ';
echo' | ';
echo'
';
echo'
';
echo'
';
echo'';
echo'| '." ".' | ';
echo''."
".' | ';
echo'
';
echo'';
echo'| '." ".' | ';
echo''."(pieczęć i podpis wierzyciela)".' | ';
echo'
';
echo'';
echo'| ';
echo' '."* Podstawa zobowiązania: Faktura VAT, Rachunek, Umowa, Nota odsetkowa, Nota księgowa, Weksel, Uznanie długu, Ugoda, Kara umowna, Faktura zbiorowa, Wyrok sądowy, Nakaz zapłaty, Pożyczka, inne.".' ';
echo' | ';
echo'
';
echo'';
echo'| ';
echo' '."W przypadku nieuregulowania płatności we wskazanym terminie, zgodnie z „Ustawą o udostępnianiu informacji gospodarczych z dnia 14 lutego 2003 r. (Dz. U. Nr 50)” , informacje o zadłużeniu zostaną przekazane do:".' ';
echo''."Krajowego Rejestru Długów
Biura Informacji Gospodarczej S.A.
ul. Armii Ludowej 21, 51-214 Wrocław
www.krd.pl
Informacja o zadłużeniu upubliczniona będzie w systemie KRD do dnia zapłaty lub 10 lat od daty dokonania wpisu.".' ';
echo' | ';
echo'
';
echo'
';
echo'
' . '';
$to_update_total = WindykacjaStatsHelper::get_to_update_total();
if ($to_update_total <= 0) {
echo'' . "Wszystkie rekordy zaktualizowane" . '
';
return;
}
if ($to_update_total > $to_update_step) {
echo'
';
echo'' . "Aktualizuj statusy (" . ($to_update_total - $to_update_step) . ")" . '';
echo'
';
}
$to_update_list = WindykacjaStatsHelper::get_to_update_list($to_update_step);
if (empty($to_update_list)) {
echo'' . "Wszystkie rekordy zaktualizowane" . '
';
return;
}
//echo'to_update_list: ';print_r($to_update_list);echo'
';
foreach ($to_update_list as $w) {
//echo'';
echo "Nr. klienta: ".$w->ID_BILLING_USERS . " ...";
//echo $w->A_STATUS . ' (' . $w->A_STATUS_UPDATE_DATE . ')';
$user = WindykacjaStatsModel::get_user_by_id($w->ID_BILLING_USERS);
if ($user) {
$billing_docs = WindykacjaStatsModel::get_bill_dosc_by_date($user);
WindykacjaStatsHelper::update_stats($user, $billing_docs);
}
//echo'
';// .box-blue
echo'
';
}
if ($to_update_total > $to_update_step) {
echo'
';
echo'' . "Aktualizuj statusy (" . ($to_update_total - $to_update_step) . ")" . '';
}
}
public static function getPowodyRezygnacji() {
// [895].[3888] - DEALS_TABLE.RODZAJ_DZIALANIA_HANDLOWEGO
$powody = array();
// $powody['3226'] = "REZYGNACJA FINANSE";
// $powody['3227'] = "REZYGNACJA SLABA OFERTA";
// $powody['3121'] = "REZYGNACJA WYPROWADZKA";
// $powody['3228'] = "REZYGNACJA PROBLEMY TECHNICZNE";
// $powody['3230'] = "REZYGNACJA WYJAZD";
// $powody['3229'] = "REZYGNACJA ZLA OBSLUGA BOK";
// $powody['3123'] = "REZYGNACJA CESJA";
// $powody['3119'] = "BRAK-BOK (Inne)";
// $powody['4188'] = "UMOWA_BEZPOSREDNIA";
// $powody['4189'] = "UMOWA_POSREDNIA";
// $powody['4190'] = "TELEMARKETING";
$powody['4192'] = "REZYGNACJA ZMIANA OPERATORA";
$powody['4193'] = "REZYGNACJA WYPROWADZKA";
$powody['4194'] = "REZYGNACJA NIEZADOWOLENIE";
$powody['4195'] = "REZYGNACJA CESJA";
//$powody['4196'] = "REZYGNACJA FINANSE";
$powody['4197'] = "REZYGNACJA SLABA OFERTA";
$powody['4198'] = "REZYGNACJA PROBLEMY TECHNICZNE";
$powody['4199'] = "REZYGNACJA ZLA OBSLUGA BOK";
$powody['4200'] = "WYPOWIEDZENIE PRZEZ OPERATORA ZA DLUGI";
// $powody['4201'] = "UMOWA W BOK ZWYCZAJNA";
// $powody['4202'] = "REZYGNACJA WYJAZD";
// $powody['4191'] = "BRAK-BOK (Inne)";
$powody['19803'] = "REZYGNACJA_Z_PRZEJSCIEM_DO_VECTRA";
$powody['4213'] = "REZYGNACJA INNE";
return $powody;
}
/**
* @params $user
* @params $id_koresp
*/
public static function task_bok_rozwiazanie_umowy_print($user, $id_koresp) {
$user_hist_events = WindykacjaStatsHelper::get_user_hist_status_events($user);
if (empty($user_hist_events)) {
echo '' . "Brak danych HIST" . '
';
return;
}
$v_doc = null;
foreach ($user_hist_events as $v_hist) {
if ($v_hist->ID_KORESP == $id_koresp) {
$v_doc = $v_hist;
break;
}
}
if (!$v_doc) {
echo '' . "Brak danych HIST dla kodresp(".$v_doc->ID_KORESP.")" . '
';
return;
}
// params(255): rozwiazanie umowy,9872,3121
$params = explode(',', $v_doc->params);
if (count($params) < 3 && $params[0] != 'rozwiazanie umowy') {
echo '' . "Bad params" . '
';
return;
}
$koresp = DB::get_by_id('IN7_DZIENNIK_KORESP', $id_koresp);
if (!$koresp) {
echo '' . "Koresp not exists!" . '
';
return;
}
$nr_umowy = $params[1];
$powod = $params[2];
$deal_id = '..........................................................';
$termin_odlaczenia = '.....................';
if (count($params) > 4) {
// $data_arr["params"] = "rozwiazanie umowy,{$nr_umowy},{$powod},{$termin_odlaczenia},{$new_id_deals}";
$termin_odlaczenia = $params[3];
$deal_id = $params[4];
}
$arg_values = array();
$arg_values['powod'] = self::getPowodyRezygnacji();
$com = new stdClass();
if ($user->BILLING_OWNER == 2) {
$com->name = 'NET-DAY';
$com->fullName = 'NET-DAY s.c.';
$com->address_street = 'ul. Kopeckiego 9/24';
$com->address_city = '80-809 Gdańsk';
} else {
$com->name = 'BIALL-NET';
$com->fullName = 'BIALL-NET Sp. z o.o.';
$com->address_street = 'Otomin ul. Słoneczna 43';
$com->address_city = '80-174 Gdańsk';
}
?>
| [1612][v3] |
Nr dziennika przyjęcia pisma fullName; ?> ID_PROJECT; ?>-ID; ?>/
Gdańsk, dnia: .......................
|
Dane Abonenta
Imię i Nazwisko / Nazwa firmy: P_NAME} {$user->P_NAME_SECOND}";?>
Pesel / nr dowodu osoby reprezentującej firmę: P_PESEL; ?>
Adres zameldowania: ul. P_ADDRESS_STREET} {$user->P_ADDRESS_HOUSE}/{$user->P_ADDRESS_HOME}, {$user->P_ADDRESS_POST_CODE} {$user->P_ADDRESS_CITY}"; ?>
Nr ID klienta: ID; ?>
Nr ID Umowy:
ID_SERVICES_OLD: ..........................................................
?>
| |
fullName; ?>
address_street; ?>
address_city; ?>
|
ROZWIĄZANIE UMOWY ABONENCKIEJ O ŚWIADCZENIE USŁUG TELEKOMUNIKACYJNYCH NR [794]
Proszę o rozwiązanie umowy abonenckiej o świadczeniu Usług Telekomunikacyjnych i odłączenie sygnału fullName; ?>. Jednocześnie jestem świadomy/a tego, że jeżeli okres trwania wypowiadanej umowy jeszcze nie uległ zakończeniu to firma name; ?> może naliczyć karę za wcześniejsze zerwanie umowy zgodnie z obowiązującym regulaminem i cennikiem.
Termin odłączenia:
(poinformowano o miesięcznym terminie wypowiedzenia, jak również przedstawiono nowe propozycje oferty)
[1061]Powody rezygnacji z Usług (wybierz tylko 1 najważniejszy powód)
| |
Data i podpis pracownika BOK
|
|
Data i czytelny podpis Klienta
|
|
'."
function frm_bok_rozwiazanie_umowy(frm){
if (frm.id_proj.value=='') {
alert('Nie podano numeru projektu!');
} else if (frm.nr_umowy.value=='') {
alert('Nie podano numeru umowy!');
} else if (frm.temin_odlaczenia.value=='') {
alert('Nie podano daty odłączenia!');
// TODO: powod - radio
} else {
return true;
}
return false;
}
".'';
$umowy_l2 = WindykacjaStatsModel::get_umowy_from_l2($user);
if (empty($umowy_l2)) {
echo '' . "Brak aktualnych umów z klientem" . '
';
return;
}
$arg_errors = array();
$arg_values = array();
$args = array();
$args['id_proj'] = V::get('id_proj', '', $_REQUEST, 'int');
$args['nr_umowy'] = V::get('nr_umowy', '', $_REQUEST, 'int');
$args['temin_odlaczenia'] = V::get('temin_odlaczenia', '', $_REQUEST);
$args['powod'] = V::get('powod', '', $_REQUEST, 'int');
$arg_values['nr_umowy'] = array();
foreach ($umowy_l2 as $k_id => $v_deal) {
$arg_values['nr_umowy'][$v_deal['ID']] = $v_deal['ID'] . ' (Nr umowy: ' . $v_deal['P_DEALNUMBER'] . ', ' . $v_deal['P_DEALDATE'] . ' - ' . $v_deal['P_DEALDATE_TERM'] . ')';
}
$arg_values['powod'] = self::getPowodyRezygnacji();
// validate
if ($args['temin_odlaczenia'] == '') {
$args['temin_odlaczenia'] = date("Y-m-d", mktime(0, 0, 0, date('m') + 1, date('d'), date('Y')));
} else {// cehck date format
if (strlen($args['temin_odlaczenia']) != 10) {
$arg_errors['temin_odlaczenia'] = "Błędny format daty";
} else {
$check_date = explode('-', $args['temin_odlaczenia']);
if (count($check_date) != 3 || strlen($check_date[0]) != 4 || strlen($check_date[1]) != 2 || strlen($check_date[2]) != 2) {
$arg_errors['temin_odlaczenia'] = "Błędny format daty";
} else {
// ok
}
}
}
if ($args['id_proj'] <= 0) {
$arg_errors['id_proj'] = "Nie podano numeru projektu!";
}
if ($args['nr_umowy'] <= 0) {
$arg_errors['nr_umowy'] = "Nie wybrano numeru umowy!";
}
if ($args['powod'] <= 0) {
$arg_errors['powod'] = "Nie wybrano powodu rezygnacji!";
}
// add rezygnacja z umowy
if (1 == V::get('contact_bok_rozwiazanie_umowy_save', 0, $_POST, 'int')) {
if (!empty($arg_errors)) {
echo '' . "W formularzy wystąpiły błędy:";
foreach ($arg_errors as $k_field => $v_err) {
echo '
' . $v_err;
}
echo '
';
} else {
$msg_log = array();
$ret = WindykacjaStatsHelper::update_bok_rozwiazanie_umowy($user, $args['id_proj'], $args['nr_umowy'], $args['powod'], $arg_values['powod'][$args['powod']], $args['temin_odlaczenia'], $msg_log);
if ($ret) {
echo '' . "Dane zapisano pomyślnie" . '
';
if (!empty($msg_log)) {
echo '' . implode('
', $msg_log) . '
';
}
$ico_print = '';
echo '' . $ico_print . " rozwiązanie umowy".'';
return;
} else {
echo '' . "Wystąpił błąd podczas zapisu danych" . '
';
if (!empty($msg_log)) {
echo '' . implode('
', $msg_log) . '
';
}
}
}
}
echo'';
}
}
class BillingDocs {
var $_docs;
var $_saldo;
public function __construct() {
$this->_docs = array();
$this->_saldo = 0;
}
public function add_bill_doc($data, $type, $h) {
$bill_doc = new BillingDoc($type, $h);
if ('FVAT' == $type && '0000-00-00' == $data) {
$data = $h['BILL_DATE'];
}
$this->_docs[$data][] = $bill_doc;
if ($data <= date("Y-m-d")) {
$this->_saldo += $bill_doc->get_saldo();
}
}
public function add_event($data, $type, $h) {
$event = new EventDoc($type, $h);
$this->_docs[$data][] = $event;
}
public function get_saldo() {
if (round($this->_saldo, 2) === 0.0) {// float point bug round('-6.821210263297E-13', 2) = -0
$this->_saldo = 0.0;
}
return round($this->_saldo, 2);
}
public function get_docs() {
return $this->_docs;
}
public function sort_docs() {
ksort($this->_docs);
}
public function get_unpaid_fvat() {
$today_date = date("Y-m-d");
$fvat_arr = array();
$saldo_curr = $this->get_saldo();
if ($saldo_curr >= 0) {
return $fvat_arr;
}
$break = false;
$saldo_dates_keys = array_keys($this->_docs);
$saldo_dates_keys = array_reverse($saldo_dates_keys);
foreach ($saldo_dates_keys as $k_data) {
if ($k_data > $today_date) continue;
$v_saldo_arr = $this->_docs[$k_data];
foreach ($v_saldo_arr as $k_ind => $v_doc) {
if ($v_doc->get_type() == 'FVAT') {
$h = $v_doc->get_data();
$h['WINIEN_POZOSTALO'] = round($h['WINIEN'], 2);
$saldo_curr += round($h['WINIEN'], 2);
if ($saldo_curr >= 0) {
$h['WINIEN_POZOSTALO'] -= $saldo_curr;
$break = true;
}
$fvat_arr[] = $h;
}
if ($break) break;
}
if ($break) break;
}
return $fvat_arr;
}
public function getLastIncomeDocsForSaldo($saldoLimit) {
$today_date = date("Y-m-d");
$incomeDocs = array();
$break = false;
$saldo_dates_keys = array_keys($this->_docs);
$saldo_dates_keys = array_reverse($saldo_dates_keys);
$saldo_curr = 0;
foreach ($saldo_dates_keys as $k_data) {
if ($k_data > $today_date) continue;
$v_saldo_arr = $this->_docs[$k_data];
foreach ($v_saldo_arr as $k_ind => $v_doc) {
if (in_array($v_doc->get_type(), array('WB_MASS','KP'))) {
$h = $v_doc->get_data();
$h['MA_POZOSTALO'] = round($h['MA'], 2);
$saldo_curr += round($h['MA'], 2);
if ($saldo_curr >= $saldoLimit) {
$h['MA_POZOSTALO'] -= $saldo_curr - $saldoLimit;
$break = true;
}
$incomeDocs[] = $h;
}
if ($break) break;
}
if ($break) break;
}
return $incomeDocs;
}
public function get_last_pay_doc() {
$last_pay_doc = null;
$today_date = date("Y-m-d");
$break = false;
foreach ($this->_docs as $k_data => $v_saldo_arr) {
if ($k_data > $today_date) continue;
foreach ($v_saldo_arr as $k_ind => $v_doc) {
if (in_array($v_doc->get_type(), array('WB_MASS','KP'))) {
$last_pay_doc = $v_doc;
break;
}
}
if ($break) break;
}
return $last_pay_doc;
}
public function get_last_fvat_doc() {
$last_fvat_doc = null;
$today_date = date("Y-m-d");
$break = false;
foreach ($this->_docs as $k_data => $v_saldo_arr) {
if ($k_data > $today_date) continue;
foreach ($v_saldo_arr as $k_ind => $v_doc) {
if (in_array($v_doc->get_type(), array('FVAT'))) {
$last_fvat_doc = $v_doc;
break;
}
}
if ($break) break;
}
return $last_fvat_doc;
}
}
class TimelineDoc {
var $_type;
var $_data;
var $_class;
public function get_type() { return $this->_type; }
public function get_data() { return $this->_data; }
public function get_class() { return $this->_class; }
public function get($key) {
return $this->_data[$key];
}
}
class EventDoc extends TimelineDoc {
public function __construct($type, &$data) {
$this->_data = $data;
$this->_class = 'EVENT';
$this->_type = $type;
}
}
class BillingDoc extends TimelineDoc {
public function __construct($type, &$data) {
$this->_data = $data;
$this->_class = 'BILLING';
$this->_convert_type($type);
$this->_count_saldo();
}
public function get_saldo() {
return $this->_saldo;
}
public function get($key) {
return $this->_data[$key];
}
public function _convert_type($type) {
$this->_type = $type;
switch ($this->_type) {
case 'FVAT':
$this->_data['nr'] = $this->_data['ID_BILLING_NUMBERS'];
$this->_data['WINIEN'] = $this->_data['WARTOSC'];
$this->_data['MA'] = 0;
break;
case 'KP':
case 'KW':
case 'WB':
case 'WB_MASS':
case 'KORV':
$this->_data['nr'] = $this->_data['NUMBER'];
break;
default:
}
}
public function _count_saldo() {
$this->_saldo = 0;
switch ($this->_type) {
case 'FVAT':
$this->_saldo -= round($this->_data['WARTOSC'], 2);
break;
case 'KORV':
$this->_saldo -= round($this->_data['MA'], 2);
break;
case 'KP':
case 'KW':
case 'WB':
case 'WB_MASS':
$this->_saldo += round($this->_data['MA'], 2);
$this->_saldo -= round($this->_data['WINIEN'], 2);
break;
default:
}
}
}
class WindykacjaStatsHelper {
// TODO: `A_RECORD_UPDATE_DATE` aktualizowane tylko wraz ze statusem
// TODO: statusy:
// 1. Automatyczne wysłanie powiadomień do Abonentów o niezapłaconych fakturach drogą e-mailową
// 5 dni roboczych po 10-go na okolo 5 dni przed blokadami
// - blokada: ??? dni po ostatniej dacie platnosci - odczytac czy zablokowany z l2?
// - 1 list - wezwanie - jesli min. 2 faktury - czyli 2 m-ce od 1 daty platnosci
// - 2 list - wezwanie ostateczne - min. 300 zł
public static function get_filter_selected() {
$filter_selected = V::get('_f', '', $_GET);
$filter_arr = self::get_filters();
$filter_selected = (array_key_exists($filter_selected, $filter_arr))? $filter_selected : '';
return $filter_selected;
}
public static function get_filters() {
$filter_arr['stan_zero'] = array("stan zerowy");
$filter_arr['10-ego'] = array("termin płatności faktur, saldo na -", 'desc'=>array("10-ego", "10-ego każdego miesiąca - termin płatnosci faktur oraz saldo na minusie"));
$filter_arr['WAITING'] = array("termin płatności faktur", 'desc'=>array("10-ego", "10-ego każdego miesiąca - termin płatnosci faktur"));// z regulaminu
$filter_arr['15'] = array("mail ponaglenie", 'desc'=>array("10-ego + 5", "10-ego + 5 dni roboczych"));
$filter_arr['blokada'] = array("blokada", 'desc'=>array("pn, wt, śr około 25", "najbliższy pn, wt, śr do 25"));// najbliższy pn, wt, śr do 25-ego danego miesiąca
$filter_arr['tel1'] = array("kontakt tel.", 'desc'=>array("", "Ostatni kontakt tel. ponad 3 m-ce temu"));
$filter_arr['bad_address'] = array("błędny adres");
$filter_arr['wezwanie1'] = array("wezwanie do zapłaty", 'desc'=>array("2 x FVat", "2 faktury"));
$filter_arr['waiting-wezwanie2'] = array("oczekiwanie 7 dni od terminu płatności");
$filter_arr['wezwanie2'] = array("ostateczne wezwanie do zapłaty, rozwiązanie umowy", 'desc'=>array("wezwanie + 14", "wezwanie + 14 dni"));
$filter_arr['waiting-krd'] = array("min. 30 dni od ostatecznego wezwania");
$filter_arr['krd'] = array("do przekazania do KRD", 'desc'=>array("wezwanie ost. + 60", "wezwanie ost. + 60 dni"));
$filter_arr['waiting-sad'] = array("przekazano do KRD");
$filter_arr['docs-in-sad'] = array("przekazać sprawę do sądu");// waiting-sad = 30 dni
$filter_arr['sad'] = array("przekazano sprawę do sądu");
$filter_arr['3 m-ce przed'] = array("3 m-ce przed przedawnieniem");// 3 m-ce przed przedawnieniem
$filter_arr['po-terminie'] = array("po terminie");// 3 lata po dacie platnosci faktur
$filter_arr['has_nr_sad'] = array("nr sprawy sąd");
$filter_arr['has_nr_komornik'] = array("nr sprawy komornik");
$filter_arr['has_ustalenia'] = array("ustalenia z klientem");
$filter_arr['sad_and_komornik'] = array("sąd z komornik");
$filter_arr['sad_bez_komornik'] = array("sąd bez komornik");
return $filter_arr;
}
public static function get_by_user(&$user) {
$db = DB::getDB();
$sql = "select * from `USERS2_WINDYKACJA_STATUS` as w where w.`ID_BILLING_USERS`='{$user->ID}' ";
$res = $db->query($sql);
$ret = $db->fetch($res);
return $ret;
}
/**
* Auto update user stats.
*
* @returns boolean - status changed or not
* @param $user - object return from function WindykacjaStatsModel::get_user_by_id
* @param $billing_docs - user billing docs
*
* @used in task_update_stats - for all users
* @used in WindykacjaView::user_historia_platnosci after view billing docs
*
* save HIST at status change to WAITING
*/
public static function update_stats(&$user, &$billing_docs) {
$data_arr = array();
if (!V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)) {
if ($user->A_STATUS_UPDATE_DATE >= date("Y-m-d")) {
return;
}
}
$saldo = $billing_docs->get_saldo();
$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = "stat-update";
// set default values
//$data_arr["A_STATUS"] = "WAITING";
//$data_arr["PAY_FVAT"] = 0;
//$data_arr["PAY_DATE"] = "0000-00-00";
// update values
{// LAST_PAY_DATE
$last_pay_doc = $billing_docs->get_last_pay_doc();
if ($last_pay_doc) {
$last_pay_doc = $last_pay_doc->get_data();
$data_arr["LAST_PAY_DATE"] = $last_pay_doc['BILL_DATE'];
}
}
$data_arr["PAY_SALDO"] = $saldo;
$data_arr["PAY_FVAT"] = 0;
$data_arr["PAY_DATE_FIRST_FVAT"] = '0000-00-00';
$lastFvatDoc = $billing_docs->get_last_fvat_doc();
if(V::get('DBG_LAST_FVAT_PAY_TERM', '', $_GET)){echo'lastFvatDoc: ';print_r($lastFvatDoc);echo'
';}
if ($lastFvatDoc) {
$data_arr["LAST_FVAT_PAY_TERM"] = $lastFvatDoc->get('PAYMENT_TERM');
$data_arr["LAST_FVAT_SELL_DATE"] = $lastFvatDoc->get('SELL_DATE');
}
if ($saldo <= 0) {
$fvat_arr = $billing_docs->get_unpaid_fvat();
$data_arr["PAY_FVAT"] = count($fvat_arr);
if (count($fvat_arr) > 0) {
foreach ($fvat_arr as $v_fvat) {
if (!isset($data_arr["PAY_DATE"]) || $v_fvat['PAYMENT_TERM'] > $data_arr["PAY_DATE"]) {
$data_arr["PAY_DATE"] = $v_fvat['PAYMENT_TERM'];
}
if ('0000-00-00' == $data_arr["PAY_DATE_FIRST_FVAT"] || $v_fvat['PAYMENT_TERM'] < $data_arr["PAY_DATE_FIRST_FVAT"]) {
$data_arr["PAY_DATE_FIRST_FVAT"] = $v_fvat['PAYMENT_TERM'];
}
}
}
}
// zmiana statusu na kolejny wg. salda
switch ($user->A_STATUS) {
case 'WAITING':// auto
if ($user->wezwanie1_DATE == '0000-00-00') {
if ($saldo < 0) {
$fvat_arr = $billing_docs->get_unpaid_fvat();
if (count($fvat_arr) > 1) {
$data_arr["A_STATUS"] = "wezwanie1";
}
}
}
else if ($user->wezwanie2_DATE == '0000-00-00') {
$data_arr["A_STATUS"] = "waiting-wezwanie2";
}
else {
$data_arr["A_STATUS"] = "wezwanie2";
}
break;
case 'wezwanie1':// wymaga `PAY_TERM`, `wezwanie1_DATE`, `ID_KORESP`
break;
case 'waiting-wezwanie2':// auto -> wezwanie2
if ($user->wezwanie2_DATE == '0000-00-00') {
$payTermArr = explode('-', $user->PAY_TERM);// Y-m-d
$payTermPlus7dni = date("Y-m-d", mktime(0,0,0, intval($payTermArr[1]), intval($payTermArr[2]) + 7, intval($payTermArr[0])));
// TODO: data +7 dni od terminu płatności (PAY_TERM) / mktime ($user->PAY_TERM + 7 dni)
if ($user->PAY_TERM != '0000-00-00' && $payTermPlus7dni < date('Y-m-d')) {
$data_arr["A_STATUS"] = "wezwanie2";
}
}
break;
case 'wezwanie2':// wymaga `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP`
break;
case 'waiting-krd':// auto -> krd jesli minelo 30 dni od wystawienia wezwanie2
if ($user->wezwanie2_DATE != '0000-00-00') {
$wzw2 = new stdClass();
$wzw2->Y = intval(substr($user->wezwanie2_DATE, 0, 4));
$wzw2->m = intval(substr($user->wezwanie2_DATE, 5, 2));
$wzw2->d = intval(substr($user->wezwanie2_DATE, 8, 2));
$wzw2->plus_30 = date("Y-m-d", mktime(0,0,0, $wzw2->m, $wzw2->d + 30, $wzw2->Y));
if ($wzw2->plus_30 < date("Y-m-d")) {
$data_arr["A_STATUS"] = "krd";
}
}
break;
case 'waiting-sad':// auto -> sad jesli minelo 30 dni od wpisania do krd (wpis_w_krd_DATE)
if ($user->wpis_w_krd_DATE != '0000-00-00') {
$krdDate = new stdClass();
$krdDate->Y = intval(substr($user->wpis_w_krd_DATE, 0, 4));
$krdDate->m = intval(substr($user->wpis_w_krd_DATE, 5, 2));
$krdDate->d = intval(substr($user->wpis_w_krd_DATE, 8, 2));
$krdDate->plus_30 = date("Y-m-d", mktime(0,0,0, $krdDate->m, $krdDate->d + 30, $krdDate->Y));
if ($krdDate->plus_30 < date("Y-m-d")) {
$data_arr["A_STATUS"] = "docs-in-sad";
}
}
break;
default:
}
// zmiana statusu na czysty - WAITING
if ($saldo >= 0 || $data_arr["PAY_FVAT"] < 2) {
if (!in_array($user->A_STATUS, array('waiting-sad', 'docs-in-sad', 'sad'))) {
$data_arr["A_STATUS"] = "WAITING";
$data_arr["wezwanie2_DATE"] = '0000-00-00';
$data_arr["wezwanie1_DATE"] = '0000-00-00';
//$data_arr["PAY_DATE"] = '0000-00-00';// TODO: ?
}
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$db = DB::getDB();
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ; ";
$db->query($sql);
// update HIST - tylko zmiana na WAITING - powrót do oczekujących
if (isset($data_arr["A_STATUS"]) && $data_arr["A_STATUS"] == "WAITING" && $user->A_STATUS != "WAITING") {
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
$db->query($sql);
}
}
public static function &get_phone_status_array(&$user) {
$ret = array();
$status_info = self::get_phone_status_info($user);
foreach ($status_info as $k => $v) {
$ret[$k] = $v['label'];
}
return $ret;
}
/**
* @param $user
*
* @returns array of 'label', 'date' -> nowy PAY_TERM ustalony wg. aktualnego stanu
*/
public static function get_phone_status_info($user) {
$ret = array();
$ret['nie_zaplaci'] = array('label'=>"nie zapłaci", 'date'=>'');
$today = date("Y-m-d");
$date = date("Y-m-d");
{// PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
if ($user->PAY_TERM != '0000-00-00') {
if ($user->PAY_TERM > $today) {
// jest już ustalona data platnosci na przyszłość
$date = $user->PAY_TERM;
}
}
}
if ($date > $today) {// data platnosci w przyszlosci -> ok
{// zapłaci w terminie today +14 dni lub PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
$ret['zaplaci_w_terminie'] = array('label'=>"zapłaci w terminie", 'date'=>$date);
}
}
else {// data platnosci w przeszlosci - przeterminowana -> ustalic nowa (+14,+1mc,+2mc,+3mc)
{// zapłaci w terminie today +14 dni lub PAY_TERM jesli jest ustalony np. z wewaznie1 lub wezwanie2
$date = date("Y-m-d", mktime(0,0,0,date('m'), date('d') + 14, date('Y')));
$ret['zaplaci_w_terminie'] = array('label'=>"zapłaci w terminie", 'date'=>$date);
}
{// zaplaci za 1-mc pozniej
$date = date("Y-m-d", mktime(0,0,0,date('m') + 1, date('d'), date('Y')));
$ret['zaplaci_za_1mc'] = array('label'=>"zapłaci miesiąc później", 'date'=>$date);
}
{// zaplaci za 2-mce pozniej
$date = date("Y-m-d", mktime(0,0,0,date('m') + 2, date('d'), date('Y')));
$ret['zaplaci_za_2mc'] = array('label'=>"zapłaci 2 miesiące później", 'date'=>$date);
}
{// zaplaci za 3-mce pozniej
$date = date("Y-m-d", mktime(0,0,0,date('m') + 3, date('d'), date('Y')));
$ret['zaplaci_za_3mc'] = array('label'=>"zapłaci 3 miesiące później", 'date'=>$date);
}
}
$ret['nie_odbiera'] = array('label'=>"nie obiera lub wyłączony telefon", 'date'=>'');
$ret['zly_numer'] = array('label'=>"zły numer telefonu", 'date'=>'');
return $ret;
}
public static function get_sms_status_info($user) {
$ret = array();
$today = date("Y-m-d");
$due_date = $user->PAY_TERM;// ustalowny pay term
$zaleglosc = number_format($user->PAY_SALDO, 2, ',', '');
if ($user->PAY_SALDO < 0) {
$msg = "Twoje saldo na dzien {$today} wynosi {$zaleglosc} zl.\n";
$msg .= "Prosimy o niezwloczne uregulowanie zaleglosci.";
if ($due_date > $today) {
// $msg .= "Prosimy o uregulowanie zaleglosci do dnia {$due_date} r.";
} else {
}
$ret['Powiadomienie SMS o zaleglosciach'] = array('label'=>"Windykacja: Powiadomienie SMS o zaleglosciach", 'msg'=>$msg);
}
return $ret;
}
/**
* Update user phone status.
*
* @returns boolean - status changed or not
* @param $user - object return from function WindykacjaStatsModel::get_user_by_id
* @param $phone_status - phone status
*
*/
public static function update_phone_status($user, $phone_status) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["LAST_PHONE_STATUS"] = $phone_status;
$data_arr["LAST_PHONE_STATUS_DATE"] = date("Y-m-d");
$status_info = self::get_phone_status_info($user);
if (array_key_exists($phone_status, $status_info)) {
$date = V::get('date', '', $status_info[$phone_status]);
if ($date != '' && $date != '0000-00-00') {
$data_arr["PAY_TERM"] = $date;
}
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ; ";
//echo'';print_r($sql);echo'
';
DB::query($sql);
// update HIST
//if (DB::affected()) {
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
DB::query($sql);
//}
return true;
}
public static function update_sms_status($user, $sms_status) {
$data_arr = array();
$status_info = self::get_sms_status_info($user);
//echo'$sms_status ';print_r($sms_status);echo'
';
//echo'$status_info ';print_r($status_info);echo'
';
if (!array_key_exists($sms_status, $status_info)) {
echo 'Nieznany status!
';
return false;
}
$db_webone = DB::getDB('931');
if (!$db_webone) {
echo 'Brak połączenia do bazy billing!
';
return false;
}
$sqlArr = array();
$sqlArr["`ID_BILLING_USERS`"] = $user->ID;
$sqlArr["`SUBJECT`"] = "'{$status_info[$sms_status]['label']}'";
$sqlArr["`BODY_HTML`"] = "'{$status_info[$sms_status]['msg']}'";
$sqlArr["`REQUEST_STATUS_SMS`"] = "'SENT_SMS'";
$sql = "insert into `HIST_CONTACTS` (" . implode(",", array_keys($sqlArr)) . ")
values (" . implode(",", array_values($sqlArr)) . ");";
//echo'sql ';print_r($sql);echo'
';
$db_webone->query($sql);
$smsId = $db_webone->insert_id();
echo'$smsId ';print_r($smsId);echo'
';
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["LAST_SMS_MSG_ID"] = $smsId;
$data_arr["LAST_SMS_STATUS"] = $sms_status;
$data_arr["LAST_PHONE_STATUS_DATE"] = date("Y-m-d");
if (array_key_exists($sms_status, $status_info)) {
$date = V::get('date', '', $status_info[$sms_status]);
if ($date != '' && $date != '0000-00-00') {
$data_arr["PAY_TERM"] = $date;
}
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ; ";
//echo'';print_r($sql);echo'
';
DB::query($sql);
// update HIST
//if (DB::affected()) {
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
DB::query($sql);
//}
return true;
}
public static function update_old_id_koresp(&$user, $id_koresp, $koresp_type) {
$db = DB::getDB();
$koresp = DB::get_by_id('IN7_DZIENNIK_KORESP', $id_koresp);
if (!$koresp) {
return false;
}
// check if ID koresp already exists in HIST table!
$sql = "select wh.`ID`,wh.``,wh.`` from `USERS2_WINDYKACJA_STATUS_HIST` as wh where ";
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["ID_USERS2"] = $user->WINDYKACJA_ID;
$data_arr["ID_KORESP"] = $id_koresp;
//$data_arr["A_STATUS_UPDATE_DATE"] = $koresp->K_DATA_OTRZYMANEJ_KORESP;// data utworzenia
$data_arr["A_STATUS_UPDATE_DATE"] = $koresp->K_DATA_OTRZYM_KORESP;// data wyslania
// $koresp_type ?
// update HIST
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
$db->query($sql);
return true;
}
/**
* @returns int - ID Koresp or null if error
*/
public static function update_bok_rozwiazanie_umowy(&$user, $id_proj, $nr_umowy, $powod, $powod_desc, $termin_odlaczenia, &$msg_log) {
$data_arr = array();
if ($id_proj > 0 && $nr_umowy > 0 && $powod != '') {
$new_id_deals = WindykacjaStatsModel::create_deals_rozwiazanie($user->ID, $nr_umowy, $termin_odlaczenia, $powod_desc);
if (!$new_id_deals) {
$msg_log[] = "Nie udało się utworzyć rekordu w DEALS_TABLE";
return null;
}
$msg_log[] = "Utworzonno rekord w DEALS_TABLE ID={$new_id_deals}";
$new_id_koresp = WindykacjaStatsModel::create_koresp($user, 'rozwiazanie umowy', $id_proj, array('nr_umowy'=>$nr_umowy, 'powod'=>$powod, 'powod_desc'=>$powod_desc));
if (!$new_id_koresp) {
$msg_log[] = "Nie udało się utworzyć rekordu KORESP";
return null;
}
$msg_log[] = "Utworzonno rekord KORESP ID={$new_id_koresp}";
$data_arr["ID_KORESP"] = $new_id_koresp;
} else {
return null;
}
$data_arr["params"] = "rozwiazanie umowy,{$nr_umowy},{$powod},{$termin_odlaczenia},{$new_id_deals}";
$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");// zawsze podana jesli jest zmiana A_STATUS
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ";
DB::query($sql);
//if (DB::affected()) {
// update $user data
foreach ($data_arr as $k => $v) {
$user->$k = $v;
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
DB::query($sql);
//}
return $new_id_koresp;
}
public static function update_users_table() {
if (!isset($_SESSION['USERS2_WINDYKACJA_PANEL'])) $_SESSION['USERS2_WINDYKACJA_PANEL'] = array();
$_SESSION['USERS2_WINDYKACJA_PANEL']['_initialized'] = 0;// always actualize
if (0 == V::get('_initialized', 0, $_SESSION['USERS2_WINDYKACJA_PANEL'], 'int')) {
$sql = "insert ignore into `USERS2_WINDYKACJA_STATUS` (`ID_BILLING_USERS`, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
select `id_users`, '" . date("Y-m-d-H:i") . "', 'sync-users' from `BILLING_USERS_ADD`
";
$res = DB::query($sql);
$_SESSION['USERS2_WINDYKACJA_PANEL']['_initialized'] = 1;
}
}
public static function _to_update_where() {
$sql_where = "";
$sql_where_and_arr = array();
//$sql_where_and_arr[] = "w.`A_STATUS`='WAITING'";
$sql_where_and_arr[] = "w.`A_STATUS_UPDATE_DATE`query($sql);
while ($r = $db->fetch($res)) {
$ret = $r->cnt;
}
return $ret;
}
public static function get_to_update_list($limit) {
$ret = array();
$db = DB::getDB();
$sql_where = self::_to_update_where();
$sql_limit = ($limit > 0)? "limit {$limit}" : "";
$sql = "select w.*
from `USERS2_WINDYKACJA_STATUS` as w
where {$sql_where}
order by ID desc
{$sql_limit}
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[] = $r;
}
return $ret;
}
/**
* @returns boolean - status changed or not
* @param $user - object return from function WindykacjaStatsModel::get_user_by_id
* @param $data - array of new user data
* 1. wysłanie 1 wezwania do zapłaty ($user->A_STATUS='wezwanie1'; `PAY_TERM`, `wezwanie1_DATE`, `ID_PROJ`)
* 2. wysłanie 2 wezwania do zapłaty ($user->A_STATUS='wezwanie2'; `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP`)
* 3. klient spłacił wszystkie zobowiązania (`PAY_SALDO`>=0)
* TODO: 4. bledny adres klienta - ponowne wyslanie wezwania - niezaleznie od statusu?
*
* if $user->A_STATUS == 'wezwanie1' @param $data:
* $data['wezwanie1_DATE'] - user data
* $data['PAY_TERM'] - user data
* $data['ID_PROJ'] - do WindykacjaStatsModel::create_koresp
*/
public static function update_user($user, $data) {
if (empty($data)) {
return $user;
}
$data_arr = array();
// set up status by current status and $data
switch ($user->A_STATUS) {
case 'WAITING':// auto
break;
case 'wezwanie1':// wymaga `PAY_TERM`, `wezwanie1_DATE`, `ID_KORESP` - wysłanie wezwanie1 (z podaniem ID_KORESP, i PAY_TERM=NOW()+14dni)
// TODO: ID_KORESP z utworzonego rekordu KORESP (wymaga ID_PROJ)
if ("" != V::get('wezwanie1_DATE' ,'', $data) && "" != V::get('PAY_TERM' ,'', $data) && V::get('ID_PROJ' ,'', $data, 'int') > 0) {
$new_id_koresp = WindykacjaStatsModel::create_koresp($user, 'wezwanie1', V::get('ID_PROJ' ,'', $data, 'int'));
if (!$new_id_koresp) {
// TODO: revert changes - DB error
return $user;
}
$data_arr["ID_KORESP"] = $new_id_koresp;
$data_arr["A_STATUS"] = "waiting-wezwanie2";
$data_arr["wezwanie1_DATE"] = V::get('wezwanie1_DATE' ,'', $data);
$data_arr["PAY_TERM"] = V::get('PAY_TERM' ,'', $data);
unset($data['ID_PROJ']);// TODO: RM after mv $data to $data_arr
}
break;
case 'waiting-wezwanie2':// auto
break;
case 'wezwanie2':// wymaga `PAY_TERM`, `wezwanie2_DATE`, `ID_KORESP` - wysłanie wezwanie2 (z podaniem ID_KORESP, i PAY_TERM=NOW()+14dni)
if ("" != V::get('wezwanie2_DATE' ,'', $data) && "" != V::get('PAY_TERM' ,'', $data) && V::get('ID_PROJ' ,'', $data, 'int') > 0) {
$new_id_koresp = WindykacjaStatsModel::create_koresp($user, 'wezwanie2', V::get('ID_PROJ' ,'', $data, 'int'));
if (!$new_id_koresp) {
// TODO: revert changes - DB error
return $user;
}
$data_arr["ID_KORESP"] = $new_id_koresp;
$data_arr["A_STATUS"] = "waiting-krd";
$data_arr["wezwanie2_DATE"] = V::get('wezwanie2_DATE' ,'', $data);
$data_arr["PAY_TERM"] = V::get('PAY_TERM' ,'', $data);
}
break;
default:
}
// if status not set, check id user saldo is ok
if (!isset($data_arr["A_STATUS"])) {
if (isset($data['PAY_SALDO']) && $data['PAY_SALDO'] >= 0) {
$data_arr["A_STATUS"] = "WAITING";
}
}
// error jesli nie ustalono statusu
if (!isset($data_arr["A_STATUS"])) {
return false;
}
$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");// zawsze podana jesli jest zmiana A_STATUS
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ";
DB::query($sql);
//if (DB::affected()) {
// update $user data
foreach ($data_arr as $k => $v) {
$user->$k = $v;
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
DB::query($sql);
//}
return true;
}
public static function bad_address_save($user, $id_koresp) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["BAD_ADDRESS"] = 1;
$data_arr["ID_KORESP"] = $id_koresp;
return self::_sql_update($user, $data_arr);
}
public static function bad_address_confirm($user, $id_koresp) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["BAD_ADDRESS"] = 0;
$data_arr["ID_KORESP"] = $id_koresp;
return self::_sql_update($user, $data_arr);
}
public static function _sql_update($user, $data_arr) {
// update values
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr[] = "`{$k}`='{$v}'";
}
$sql = "update `USERS2_WINDYKACJA_STATUS` set " . implode(",", $sql_arr) . " where `ID`='{$user->WINDYKACJA_ID}' limit 1 ";
DB::query($sql);
// update $user data
foreach ($data_arr as $k => $v) {
$user->$k = $v;
}
$sql_arr = array();
foreach ($data_arr as $k => $v) {
$sql_arr["`{$k}`"] = "'{$v}'";
}
$sql_arr["`ID_USERS2`"] = "'{$user->WINDYKACJA_ID}'";
$sql = "insert into `USERS2_WINDYKACJA_STATUS_HIST`(" . implode(",", array_keys($sql_arr)) . ") values(" . implode(",", array_values($sql_arr)) . ");";
DB::query($sql);
return true;
}
public static function change_status_save($user, $new_status) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["A_STATUS_UPDATE_DATE"] = date("Y-m-d");
$data_arr["A_STATUS"] = $new_status;
if ($user->A_STATUS == 'krd' && $new_status == 'waiting-sad') {
$data_arr["wpis_w_krd_DATE"] = date('Y-m-d');
}
return self::_sql_update($user, $data_arr);
}
public static function nr_sprawy_krd_save($user, $nr_sprawy_krd) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["NR_SPRAWY_KRD"] = $nr_sprawy_krd;
return self::_sql_update($user, $data_arr);
}
public static function nr_sprawy_sad_save($user, $nr_sprawy_sad) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["NR_SPRAWY_SAD"] = $nr_sprawy_sad;
return self::_sql_update($user, $data_arr);
}
public static function nr_sprawy_komornik_save($user, $nr_sprawy_komornik) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["NR_SPRAWY_KOMORNIK"] = $nr_sprawy_komornik;
return self::_sql_update($user, $data_arr);
}
public static function ustalenia_info_save($user, $ustalenia_date, $ustalenia_info) {
$data_arr = array();
$data_arr["A_RECORD_UPDATE_DATE"] = date("Y-m-d-H:i");
$data_arr["A_RECORD_UPDATE_AUTHOR"] = $_SESSION['ADM_ACCOUNT'];
$data_arr["L_APPOITMENT_DATE"] = $ustalenia_date;
$data_arr["L_APPOITMENT_INFO"] = $ustalenia_info;
$data_arr["L_APPOITMENT_USER"] = $_SESSION['ADM_ACCOUNT'];
if (empty($ustalenia_info) && (empty($ustalenia_date) || $ustalenia_date == '0000-00-00')) {
$data_arr["L_APPOITMENT_USER"] = '';
}
return self::_sql_update($user, $data_arr);
}
public static function get_user_hist_events(&$user) {
$db = DB::getDB();
$ret = array();
$sql = "select wh.*
from `USERS2_WINDYKACJA_STATUS_HIST` as wh
where wh.`ID_USERS2`='{$user->WINDYKACJA_ID}'
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[] = $r;
}
return $ret;
}
public static function get_user_hist_status_events(&$user) {
$db = DB::getDB();
$ret = array();
$sql = "select wh.*, kor.`K_ZAWARTOS`
from `USERS2_WINDYKACJA_STATUS_HIST` as wh
left join `IN7_DZIENNIK_KORESP` as kor on(wh.`ID_KORESP`!='N/S;' and kor.`ID`=wh.`ID_KORESP`)
where wh.`ID_USERS2`='{$user->WINDYKACJA_ID}'
and wh.`A_STATUS_UPDATE_DATE`!='N/S;'
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[] = $r;
}
return $ret;
}
public static function get_user_hist_phone_events(&$user) {
$ret = array();
$db = DB::getDB();
$sql = "select wh.*
from `USERS2_WINDYKACJA_STATUS_HIST` as wh
where wh.`ID_USERS2`='{$user->WINDYKACJA_ID}'
and wh.`LAST_PHONE_STATUS_DATE`!='N/S;'
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[] = $r;
}
return $ret;
}
public static function get_user_bad_address_events(&$user) {
$ret = array();
$db = DB::getDB();
$sql = "select wh.*
from `USERS2_WINDYKACJA_STATUS_HIST` as wh
where wh.`ID_USERS2`='{$user->WINDYKACJA_ID}'
and wh.`BAD_ADDRESS`!='N/S;'
order by wh.`ID` ASC
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[] = $r;
}
return $ret;
}
public static function get_status_count() {
$sql_where_and_arr = array();
$usrAclGroups = User::getLdapGroupsNames();
$usrAclGroups[] = '';
$sqlUsrAclGroups = "'" . implode("','", $usrAclGroups) . "'";
$sql_where_and_arr[] = "a.`A_ADM_COMPANY` in({$sqlUsrAclGroups})";
$sql_where_and_arr[] = "a.`A_CLASSIFIED` in({$sqlUsrAclGroups})";
$sqlWhereAdd = " and " . implode(" and ", $sql_where_and_arr);
$ret = array();
$db = DB::getDB();
$sql = " select w.`A_STATUS`, count(1) as cnt
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where 1=1 {$sqlWhereAdd}
group by w.`A_STATUS`
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[$r->A_STATUS] = $r;
}
// filter: 'po-terminie'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`PAY_SALDO`<0
and w.`PAY_DATE`!='0000-00-00'
and w.`PAY_DATE`query($sql);
if ($r = $db->fetch($res)) {
$ret['po-terminie'] = $r;
}
// filter: '3 m-ce przed'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`PAY_SALDO`<0
and w.`PAY_DATE`!='0000-00-00'
and w.`PAY_DATE`DATE_SUB(NOW(), INTERVAL 36 MONTH)
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$ret['3 m-ce przed'] = $r;
}
// filter: 'tel1'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`PAY_SALDO`<0
and w.`A_STATUS` in('wezwanie1', 'waiting-wezwanie2', 'wezwanie2', 'waiting-krd')
and ( w.`LAST_PHONE_STATUS_DATE`='0000-00-00'
or w.`LAST_PHONE_STATUS_DATE`query($sql);
if ($r = $db->fetch($res)) {
$ret['tel1'] = $r;
}
// filter: 'bad_address'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`PAY_SALDO`<0
-- and w.`A_STATUS` in('wezwanie1', 'waiting-wezwanie2', 'wezwanie2', 'waiting-krd')
and w.`BAD_ADDRESS`>0
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$ret['bad_address'] = $r;
}
// filter: 'stan_zero', '10-ego'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`PAY_SALDO`>=0, 1, 0)) as cnt_stan_zero
, sum(IF(w.`PAY_SALDO`<0, 1, 0)) as cnt_stan_minus
, sum(IF(w.`PAY_SALDO`<0, w.`PAY_SALDO`, 0)) as suma_zaleglosci
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`A_STATUS`='WAITING'
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$ret['stan_zero'] = (object)array('A_STATUS'=>'stan_zero', 'cnt'=>$r->cnt_stan_zero, 'suma_zaleglosci'=>0);
$ret['10-ego'] = (object)array('A_STATUS'=>'10-ego', 'cnt'=>$r->cnt_stan_minus, 'suma_zaleglosci'=>$r->suma_zaleglosci);
}
// filter: 'has_nr_sad', 'has_nr_komornik', 'has_ustalenia'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`NR_SPRAWY_SAD`!='', 1, 0)) as cnt_sad
, sum(IF(w.`NR_SPRAWY_KOMORNIK`!='', 1, 0)) as cnt_komornik
, sum(IF(w.`L_APPOITMENT_INFO`!='', 1, 0)) as cnt_ustalenia
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
(w.`NR_SPRAWY_SAD`!=''
or w.`NR_SPRAWY_KOMORNIK`!=''
or w.`L_APPOITMENT_INFO`!=''
)
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$ret['has_nr_sad'] = (object)array('A_STATUS'=>'has_nr_sad', 'cnt'=>$r->cnt_sad, 'suma_zaleglosci'=>null);
$ret['has_nr_komornik'] = (object)array('A_STATUS'=>'has_nr_komornik', 'cnt'=>$r->cnt_komornik, 'suma_zaleglosci'=>null);
$ret['has_ustalenia'] = (object)array('A_STATUS'=>'has_ustalenia', 'cnt'=>$r->cnt_ustalenia, 'suma_zaleglosci'=>null);
}
// filter: 'sad_and_komornik', 'sad_bez_komornik'
$sql = "select w.`A_STATUS`
, count(1) as cnt
, sum(IF(w.`NR_SPRAWY_KOMORNIK`!='', 1, 0)) as cnt_komornik
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where
w.`A_STATUS`='sad'
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$ret['sad_and_komornik'] = (object)array('A_STATUS'=>'sad_and_komornik', 'cnt'=>$r->cnt_komornik, 'suma_zaleglosci'=>null);
$ret['sad_bez_komornik'] = (object)array('A_STATUS'=>'sad_bez_komornik', 'cnt'=>($r->cnt - $r->cnt_komornik), 'suma_zaleglosci'=>null);
}
return $ret;
}
}
class WindykacjaStatsModel {
public static function _parse_query($q) {
$sql_where = '';
$sql_where_and_arr = array();
$usrAclGroups = User::getLdapGroupsNames();
$usrAclGroups[] = '';
$sqlUsrAclGroups = "'" . implode("','", $usrAclGroups) . "'";
$sql_where_and_arr[] = "a.`A_ADM_COMPANY` in({$sqlUsrAclGroups})";
$sql_where_and_arr[] = "a.`A_CLASSIFIED` in({$sqlUsrAclGroups})";
$filter_selected = WindykacjaStatsHelper::get_filter_selected();
if ($filter_selected) {
if ($filter_selected == 'po-terminie') {
$sql_where_and_arr[] = "w.`PAY_SALDO`<0";
$sql_where_and_arr[] = "w.`PAY_DATE`!='0000-00-00'";
$sql_where_and_arr[] = "w.`PAY_DATE` 2) {// min. 3 znaki
$q_arr_values[] = strtolower($v_q);
}
}
foreach ($q_arr_values as $q_value) {
$sql_filter_q = array();
$q_arr = array();// uniq array
$q_arr_pl = array();// loop array
$pl_letters = array('ą', 'ć', 'ę', 'ł', 'ń', 'ó', 'ś', 'ź', 'ż');
$en_letters = array('a', 'c', 'e', 'l', 'n', 'o', 's', 'z', 'z');
$sqlQValue = DB::_($q_value);
$sql_filter_q[] = "a.`P_NAME` like '{$sqlQValue}%'";
$sql_filter_q[] = "a.`P_NAME_SECOND` like '{$sqlQValue}%'";
$q_value_clean_en = str_replace($pl_letters, $en_letters, $q_value);
$q_value_clean_pl = str_replace($en_letters, $pl_letters, $q_value_clean_en);
$q_value_clean_pr = str_replace($en_letters, '_', $q_value_clean_en);
if ($q_value_clean_en != $q_value) $q_arr[$q_value_clean_en] = true;
if ($q_value_clean_pl != $q_value) $q_arr[$q_value_clean_pl] = true;
if ($q_value_clean_pr != $q_value) $q_arr[$q_value_clean_pr] = true;
foreach ($q_arr as $v_q => $v_val) {
$sqlQValue = DB::_($v_q);
$sql_filter_q[] = "a.`P_NAME` like '{$sqlQValue}'";
$sql_filter_q[] = "a.`P_NAME_SECOND` like '{$sqlQValue}'";
// $sql_filter_q[] = "a.`P_ADDRESS_STREET` like '{$sqlQValue}'";
}
$sql_field_1 = "a.`P_NAME`";
$sql_field_2 = "a.`P_NAME_SECOND`";
foreach ($pl_letters as $k_ind => $v_char_pl) {
$sql_field_1 = "REPLACE({$sql_field_1}, '{$v_char_pl}', '{$en_letters[$k_ind]}')";
$sql_field_2 = "REPLACE({$sql_field_2}, '{$v_char_pl}', '{$en_letters[$k_ind]}')";
}
$q_value_clean_en = DB::_($q_value_clean_en);
$sql_filter_q[] = $sql_field_1 . " like '%{$q_value_clean_en}%'";
$sql_filter_q[] = $sql_field_2 . " like '%{$q_value_clean_en}%'";
$sql_where_and_arr[] = "(" . implode(" or ", $sql_filter_q) . ")";
}
}
}
if (!empty($sql_where_and_arr)) {
$sql_where = implode(" and ", $sql_where_and_arr);
} else {
$sql_where = "1=1";
}
return $sql_where;
}
public static function get_order_by_fields() {
$order_by_fields = array();
$order_by_fields[] = 'ID';
$order_by_fields[] = 'is_firma';
$order_by_fields[] = 'BILLING_OWNER';
$order_by_fields[] = 'STATUS';
$order_by_fields[] = 'BLOKADA';
$order_by_fields[] = 'ID_BILLING_USERS';
$order_by_fields[] = 'P_NAME';
$order_by_fields[] = 'PAY_SALDO';
$order_by_fields[] = 'PAY_FVAT';
$order_by_fields[] = 'PAY_DATE';
$order_by_fields[] = 'PAY_TERM';
return $order_by_fields;
}
public static function get_users_total($q = '') {
$total = 0;
$sql_where = WindykacjaStatsModel::_parse_query($q);
$db = DB::getDB();
$sql = "select count(1) as cnt
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where {$sql_where}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$total = $r->cnt;
}
return $total;
}
public static function get_sql_users_select() {
$sql_select = "
a.`id_users` as ID
, a.`id_users`
, a.`P_NAME`
, a.`P_NAME_SECOND`
, a.`P_ADDRESS_STREET`
, a.`P_ADDRESS_HOME`
, a.`P_ADDRESS_HOUSE`
, a.`P_ADDRESS_CITY`
, a.`P_ADDRESS_POST_CODE`
, a.`P_ADDRESS_REGION`
, a.`P_PESEL`
, a.`P_PHONE`
, a.`P_OTHER_DOC`
, a.`user_mail_contact`
, a.`is_firma`
, w.`ID` as WINDYKACJA_ID
, w.`ID_BILLING_USERS`
, w.`A_STATUS`
, w.`A_STATUS_UPDATE_DATE`
, w.`USER_PAY_TERM_ADD`
, w.`L_APPOITMENT_DATE`
, w.`L_APPOITMENT_USER`
, w.`L_APPOITMENT_INFO`
, w.`PAY_DATE`
, w.`PAY_DATE_FIRST_FVAT`
, w.`PAY_TERM`
, w.`PAY_SALDO`
, w.`PAY_FVAT`
, w.`wezwanie1_DATE`
, w.`wezwanie2_DATE`
, w.`wpis_w_krd_DATE`
, w.`ID_KORESP`
, w.`LAST_PAY_DATE`
, w.`BAD_ADDRESS`
-- , (select bu.`BILLING_OWNER` from `BILLING_USERS` as bu where bu.`ID`=w.`ID_BILLING_USERS` limit 1) as BILLING_OWNER
-- , (select bu.`STATUS` from `BILLING_USERS` as bu where bu.`ID`=w.`ID_BILLING_USERS` limit 1) as STATUS
, bu.`STATUS`
, bu.`BILLING_OWNER`
, w.`NR_SPRAWY_KRD`
, w.`NR_SPRAWY_SAD`
, w.`NR_SPRAWY_KOMORNIK`
";
return $sql_select;
}
public static function &get_users($q = '', $limit = 10, $limit_start = 0, $order_by = '', $order_dir = '') {
$ret = array();
$sql_select = self::get_sql_users_select();
$sql_where = WindykacjaStatsModel::_parse_query($q);
$sql_limit = "limit " . (($limit > 0)? $limit : "10");
if ($limit_start > 0) $sql_limit .= " offset {$limit_start}";
$sql_order_by = "";
$order_by_allowed = self::get_order_by_fields();
if (in_array($order_by, $order_by_allowed)) {
if (in_array($order_by, array('P_NAME','is_firma'))) {
$order_by = "a.{$order_by}";
} else if (in_array($order_by, array('BILLING_OWNER','STATUS'))) {
$order_by = "bu.{$order_by}";
} else if ($order_by == 'BLOKADA') {
$order_by = "bu.`STATUS`";
} else {
$order_by = "w.{$order_by}";
}
$sql_order_by .= " order by {$order_by}";
if (in_array($order_dir, array('DESC','ASC'))) {
$sql_order_by .= " {$order_dir}";
}
}
$db = DB::getDB();
$sql = "select {$sql_select}
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS` as bu on(bu.`ID`=w.`ID_BILLING_USERS`)
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where {$sql_where}
{$sql_order_by}
{$sql_limit}
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$ret[$r->id_users] = $r;
}
return $ret;
}
public static function get_user_by_id($id) {
$ret = null;
if ($id <= 0) return $ret;
$sql_where_and_arr = array();
$usrAclGroups = User::getLdapGroupsNames();
$usrAclGroups[] = '';
$sqlUsrAclGroups = "'" . implode("','", $usrAclGroups) . "'";
$sql_where_and_arr[] = "a.`A_ADM_COMPANY` in({$sqlUsrAclGroups})";
$sql_where_and_arr[] = "a.`A_CLASSIFIED` in({$sqlUsrAclGroups})";
$sqlWhereAdd = " and " . implode(" and ", $sql_where_and_arr);
$sql_select = self::get_sql_users_select();
$sql_where = "a.`id_users`='{$id}'";
$db = DB::getDB();
$sql = "select {$sql_select}
from `USERS2_WINDYKACJA_STATUS` as w
left join `BILLING_USERS` as bu on(bu.`ID`=w.`ID_BILLING_USERS`)
left join `BILLING_USERS_ADD` as a on(a.`id_users`=w.`ID_BILLING_USERS`)
where {$sql_where}
{$sqlWhereAdd}
";
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
if(0){
$r->BA_WINIEN = "";
$r->BA_MA = "";
$r->BA_TIMESTAMP = "";
$sql = "select BA.`WINIEN` as BA_WINIEN
, BA.`MA` as BA_MA
, unix_timestamp(BA.TIMESTAMP) as BA_TIMESTAMP
from `BILLING_ACCOUNTS` as BA
where BA.`ID_BILLING_USERS`='" . $r->ID . "'
";
$db_webone = DB::getDB('931');
if ($db_webone) {
$res_fin = $db_webone->query($sql);
if ($r_fin = $db_webone->fetch($res_fin)) {
$r->BA_WINIEN = $r_fin->BA_WINIEN;
$r->BA_MA = $r_fin->BA_MA;
$r->BA_TIMESTAMP = $r_fin->BA_TIMESTAMP;
}
}
}
$ret = $r;
}
return $ret;
}
public static function get_company(&$user) {
if (!isset($user->_company)) {
$db = DB::getDB();
/*
id 1 / 2
name1 "BIALL-NET" Sp. z o.o. / "NET-DAY" s.c.
name2 --- / Adrian i Ewa Wieczorkowscy
kod 80-174 / 80-809
miasto Gdańsk, Otomin / Gdańsk
ulica Słoneczna / Cieszyńskiego
numer_dom 43 / 38
numer_pos NULL / NULL
uwagi --- / ---
tel 0-58 320-72-92 / 0-58 741 84 54
fax 0-58 320-72-96 / 0-58 741 84 56
nip 593-22-68-672 / 583-27-54-031
regon 192120212 / 192578721
bank Bank Zachodni WBK SA I O/Gdansk / ---
nr_rach 46 1090 1098 0000 0001 0253 7156 / 84 1500 1171 1211 7002 9997 0000
NR_RACH_MASS_PAY 109000049887 / 109000049669
BILLING_OWNER_EMAIL biall-net@biall.net.pl / netday@netday.pl
*/
$sql = "select
bo.`id`
, bo.`name1`
, bo.`name2`
, bo.`kod`
, bo.`miasto`
, bo.`ulica`
, bo.`numer_dom`
, bo.`tel`
, bo.`fax`
, bo.`nip`
, bo.`regon`
, bo.`bank`
, bo.`nr_rach`
, bo.`NR_RACH_MASS_PAY`
, bo.`BILLING_OWNER_EMAIL`
from `BILLING_USERS` as bu
left join `BILLING_OWNER` as bo on (bo.`ID`=bu.`BILLING_OWNER`)
where
bu.`ID`='{$user->ID_BILLING_USERS}'
";
//$db_webone = DB::getDB('931');
//if ($db_webone) {
//$res = $db_webone->query($sql);
//if ($r = $db_webone->fetch($res)) {
// $user->_company = $r;
//}
//}
$res = $db->query($sql);
if ($r = $db->fetch($res)) {
$user->_company = $r;
}
}
return $user->_company;
}
public static function get_billing_type($type_id) {
$ret = null;
$types = WindykacjaStatsModel::get_billing_types();
if (array_key_exists($type_id, $types)) {
$ret = $types[$type_id];
}
return $ret;
}
public static function get_billing_type_desc($type) {
$ret = '';
if ($type == 'FVAT') {
return "Faktura Vat";
}
$types = WindykacjaStatsModel::get_billing_types();
foreach ($types as $h) {
if ($h['TYPE'] == $type) {
return $h['DESC'];
}
}
return $ret;
}
public static function get_billing_types($ids = array()) {
static $_cache;
if (empty($_cache)) $_cache = array();
$cache_key = implode(".", $ids);
if (!array_key_exists($cache_key, $_cache)) {
$billing_types = array();
$db_webone = DB::getDB('931');
if ($db_webone) {
$sql_where = "1=1";
if (!empty($ids)) {
$sql_where = "`ID` in ('".implode("','", $ids)."')";
}
$sql = "select *
from `BILLING_NUMBERS_TYPE`
where
{$sql_where}
";
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
$billing_types[$h['ID']] = $h;
}
}
$_cache[$cache_key] = $billing_types;
}
return $_cache[$cache_key];
}
public static function get_umowy_from_l2(&$user) {
$ret = array();
$db_webone = DB::getDB();
if (!$db_webone) {
return -1;
}
$sql = "select
d.`ID`, d.`P_DEALPREFIX`, d.`P_DEALNUMBER`, d.`P_DEALNUMBER_OLD`, d.`P_DEALDATE`, d.`P_DEALDATE_TERM`, d.`DEALDESC`, d.`ANEX_NEEDED`, d.`ANEX_CONFIRM_DATE`
from `DEALS_TABLE` as d
where d.`ID_BILLING_USERS`='{$user->ID}'
order by d.`ID` DESC
";
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
$ret[$h['ID']] = $h;
}
return $ret;
}
public static function get_uslugi_from_l2(&$user) {
// l2: www/modules/webone/form/edit_user_status.php
// Aktualne: Usługa[Status] => list_services2($user->ID);
$uID = $user->ID;
$ret = array();
$db_webone = DB::getDB();
if (!$db_webone) {
return -1;
}
if(0){// SERVICES table struct example:
//[ID_BILLING_USERS] => 11363
//[active] => 1
//[TAB_UPDATE_STAT] => 1
//[TAB_UPDATE_DATE] => 2012-02-03 17:43:44
//[TAB_UPDATE_INFO] => OK-PREUPDATE-DELETED---UPDATED-OK-:195.117.2.79
//[SERVICE_TYPE] => ABONAMENT
//[ID_BILLING_NUMBERS_TYPE] => 1
//[ABONAMENT_PERIOD] => 1
//[ABONAMENT_VALUE] => 26.01
//[VAT] => 23
//[ID_CURRENCY] => 0
//[ABONAMENT_PAYMENT] => UP
//[ABONAMENT_START] => 1
//[MINIMUM_BILLING_VALUE] => 0.00
//[BILLED_TILL] => 2012-06-01 00:00:00
//[HANGUP_FROM] =>
//[HANGUP_TILL] =>
//[HANGUP_STATUS] =>
//[HANGUP_RATIO] => 1.00
//[TIMESTAMP] => 20120504125053
//[P_ID_SERVICES] => 0
//[uwagi] =>
//[ID_DEALS_TABLE] => 9373
//[VAT_NAME] => 23
//[id_list_sww] => 1
//[SERV_ID_BILLING_PREFIXES] => 0
//[ID_OFFERS] => 833
//[A_RECORD_CREATE_DATE] =>
//[A_RECORD_CREATE_AUTHOR] =>
//[A_RECORD_UPDATE_DATE] =>
//[A_RECORD_UPDATE_AUTHOR] =>
}
$sql = "select
s.`ID`, s.`NAME_LIST_SERVICES`, s.`A_STATUS`
, s.`ID_OFFERS`
, s.`ID_DEALS_TABLE`
, s.`P_ID_SERVICES`
, s.`P_ID_SERVICES` as P_ID
, s.`HANGUP_STATUS`
, s.`HANGUP_FROM`
, s.`HANGUP_TILL`
, ls.`description` as description
, lst.`name` as A_STATUS_DESC
from `SERVICES` as s
left join `LIST_SERVICES` as ls on(ls.`name`=s.`NAME_LIST_SERVICES`)
left join `LIST_status` as lst on(lst.`ses_users2`=s.`HANGUP_STATUS` and lst.`ID`>2)
where `ID_BILLING_USERS`='{$user->ID}'
";
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
$ret[$h['ID']] = $h;
}
return $ret;
}
public static function get_umowy_from_l3(&$user) {
$ret = array();
$db = DB::getDB();
$sql = "select d.*
from `USERS2_DEALS` as d
where
(d.`ID_BILLING_USERS_OLD`='{$user->ID}' or d.`ID_BILLING_USERS_NEW`='{$user->ID}')
order by d.`ID` DESC
";
$res = $db->query($sql);
while ($h = $db->fetch_assoc($res)) {
$ret[] = $h;
}
return $ret;
}
public static function get_bill_dosc_by_date(&$user, $date_limit = null) {
// fetch finanse data from remote DB
$billing_docs = new BillingDocs();
$db_webone = DB::getDB('931');
if (!$db_webone) {
echo''."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'
';
return;
}
$sql_where = " BILLS_FVAT.OPEN='N' and BILLS_FVAT.ID_BILLING_USERS ='{$user->ID}' ";
// blad w zaokragleniach
// , sum((BILLS_FVAT_POS.AMMOUNT * BILLS_FVAT_POS.PRICE)*(1 + BILLS_FVAT_POS.VAT/100)) as WARTOSC
if ($date_limit) {
$sql_where .= " and BILLS_FVAT.PAYMENT_TERM <= '{$date_limit}' ";
}
$sql = "select
BILLS_FVAT.*
, count(BILLS_FVAT_POS.ID) as ILOSC_POZYCJI
, BILLS_FVAT_POS.ID_BILLS_FVAT
, round(sum((BILLS_FVAT_POS.AMMOUNT * BILLS_FVAT_POS.PRICE)*(1 + BILLS_FVAT_POS.VAT/100)), 2) as WARTOSC
-- , count(BILLS_FVAT_POS.PRICE) as ILOSC_POZYCJI
from `BILLS_FVAT`
left join `BILLS_FVAT_POS` on BILLS_FVAT.ID=BILLS_FVAT_POS.ID_BILLS_FVAT
where {$sql_where}
group by BILLS_FVAT.ID
order by BILLS_FVAT.ID_BILLING_NUMBERS ASC
";
//echo'';print_r($sql);echo'
';
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
if (0){// ? dla kazdej faktury ?
$sql = "select t1.ID_BILLING_NUMBERS AS NR_DOK
, t1.ID_BILLING_USERS as PLATNIK
, t1.WINIEN,t1.MA
, t1.ID_FIN_WINIEN as ID_F_W
, t1.ID_FIN_WINIEN_VAL as F_WINIEN_V
, t1.ID_FIN_MA as ID_F_M
, t1.ID_FIN_MA_VAL as F_MA_V
, max(t2.ID)
, sum(t2.ID_FIN_WINIEN_VAL) as ROZ_WINIEN
, max(t3.ID)
, sum(t3.ID_FIN_MA_VAL) as ROZ_MA
, t1.WINIEN - sum(t2.ID_FIN_WINIEN_VAL) - sum(t3.ID_FIN_MA_VAL) as POZ_WINIEN
, t1.MA - sum(t3.ID_FIN_MA_VAL) - sum(t2.ID_FIN_WINIEN_VAL) as POZ_MA
from `BILLING_ACCOUNTS_FILES` as t1
left join `BILLING_ACCOUNTS_FILES` as t2 on (t1.ID_BILLING_NUMBERS=t2.ID_FIN_WINIEN)
left join `BILLING_ACCOUNTS_FILES` as t3 on (t1.ID_BILLING_NUMBERS=t3.ID_FIN_MA)
where
t1.ID_FIN_WINIEN is NULL and t1.ID_FIN_MA is NULL and t1.MA = 0 and t1.WINIEN > 0
and t1.ID_BILLING_NUMBERS={$user->ID}
group by t1.ID_BILLING_NUMBERS
limit 0,1
";
}
//$h['type'] = 'FVAT';
//$h['nr'] = $h['ID_BILLING_NUMBERS'];
//$h['WINIEN'] = $h['WARTOSC'];
//$h['MA'] = 0;
$billing_docs->add_bill_doc($h['PAYMENT_TERM'], 'FVAT', $h);
}
$faktury_cols = array();
//$faktury_cols['ID_BILLING_USERS'] = "id klienta";//
$faktury_cols['ID_BILLING_NUMBERS'] = "numer";// numer faktury
$faktury_cols['BILL_DATE'] = "";// data('Y-m-d')
$faktury_cols['SELL_DATE'] = "";// data('Y-m-d')
$faktury_cols['PAYMENT_TERM'] = "";// data('Y-m-d')
//$billing_types = WindykacjaStatsModel::get_billing_types(array(3, 4, 5, 7, 8));
$billing_types = WindykacjaStatsModel::get_billing_types();
foreach ($billing_types as $bill_type) {
if ($bill_type['CLASS'] == 'FINANCE') {
$sql_where = ($date_limit)? " and {$bill_type['TABLE']}.BILL_DATE <= '{$date_limit}' " : "";
$sql = "select {$bill_type['TABLE']}.ID
, {$bill_type['TABLE']}.ID_BILLING_NUMBERS
, {$bill_type['TABLE']}.BILL_DATE
, round({$bill_type['TABLE']}.WINIEN, 2) as WINIEN
, round({$bill_type['TABLE']}.MA, 2) as MA
, {$bill_type['TABLE']}.ID_FK_DEKRET
, {$bill_type['TABLE']}.OPEN
, {$bill_type['TABLE']}.FK_ZAKSIEG
, {$bill_type['TABLE']}.do_dokumentu
, {$bill_type['BILLING_NUMBERS_TABLE']}.NUMBER
, {$bill_type['BILLING_NUMBERS_TABLE']}.ID_BILLING_PREFIXES
, {$bill_type['BILLING_NUMBERS_TABLE']}.ID_BILLING_NUMBERS_TYPE
from {$bill_type['TABLE']}
left join {$bill_type['BILLING_NUMBERS_TABLE']} on ({$bill_type['BILLING_NUMBERS_TABLE']}.ID={$bill_type['TABLE']}.ID_BILLING_NUMBERS)
where {$bill_type['TABLE']}.ID_BILLING_USERS='{$user->ID}'
{$sql_where}
";
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
$billing_docs->add_bill_doc($h['BILL_DATE'], $bill_type['TYPE'], $h);
}
}
else if ($bill_type['CLASS'] == 'SELL_MA') {
$sql_where = ($date_limit)? " and {$bill_type['TABLE']}.BILL_DATE <= '{$date_limit}' " : "";
$sql = "select {$bill_type['TABLE']}.ID
, count({$bill_type['TABLE']}_POS.ID) as cnt
, {$bill_type['TABLE']}_POS.ID_BILLS_FVAT
, {$bill_type['TABLE']}.ID_CURRENCY
, round(sum(({$bill_type['TABLE']}_POS.N_AMMOUNT * {$bill_type['TABLE']}_POS.N_PRICE) * ( 1 + {$bill_type['TABLE']}_POS.N_VAT / 100)),2) as WINIEN
, round(sum(
(({$bill_type['TABLE']}_POS.AMMOUNT * {$bill_type['TABLE']}_POS.PRICE) * ( 1 + {$bill_type['TABLE']}_POS.VAT / 100))
- ({$bill_type['TABLE']}_POS.N_AMMOUNT * {$bill_type['TABLE']}_POS.N_PRICE * {$bill_type['TABLE']}_POS.N_VAT / 100)
+ ({$bill_type['TABLE']}_POS.N_AMMOUNT * {$bill_type['TABLE']}_POS.N_PRICE * {$bill_type['TABLE']}_POS.VAT / 100)
),2) as MA
, {$bill_type['TABLE']}.ID_BILLING_NUMBERS , {$bill_type['TABLE']}.OPEN , {$bill_type['TABLE']}.FK_ZAKSIEG
, {$bill_type['TABLE']}.IF_KORV , {$bill_type['TABLE']}.BILL_DATE , {$bill_type['BILLING_NUMBERS_TABLE']}.NUMBER
from {$bill_type['TABLE']}
left join {$bill_type['BILLING_NUMBERS_TABLE']} on {$bill_type['BILLING_NUMBERS_TABLE']}.ID={$bill_type['TABLE']}.ID_BILLING_NUMBERS
left join {$bill_type['TABLE']}_POS on {$bill_type['TABLE']}.ID={$bill_type['TABLE']}_POS.ID_BILLS_FVAT
where {$bill_type['TABLE']}.ID_BILLING_USERS='{$user->ID}'
{$sql_where}
group by {$bill_type['TABLE']}.ID
order by {$bill_type['TABLE']}.ID
";
$res = $db_webone->query($sql);
while ($h = $db_webone->fetch_assoc($res)) {
$billing_docs->add_bill_doc($h['BILL_DATE'], $bill_type['TYPE'], $h);
}
}
}
return $billing_docs;
}
/**
* @used in FunkcjeL1::billing_billwiev_przeterminowania
*/
public static function get_billing_account_files($user_id) {
$baf = array();
$ECHO_PARAM = '';
$ID_BILLING_USERS_PARAM = $user_id;
$db_webone = DB::getDB('931');
if (!$db_webone) {
echo''."Blad polaczenia do bazy dancyh webone_billing - Zasob [931]".'
';
return $baf;
}
$WHERE = "";
if ($ID_BILLING_USERS_PARAM) $WHERE .= " and BAF.ID_BILLING_USERS='{$ID_BILLING_USERS_PARAM}' ";
//UZGODNIENIE_SALD
if (strstr($ECHO_PARAM, 'saldo_rok')) {
global $base_path ;
include_once($base_path."/modules/billing/bill/modules/billing_financefunc.inc");
if ($ID_BILLING_USERS_PARAM) {
if ($vdb->sql_numrows($vdb->sql_query("select ID from BILLING_ACCOUNTS_TILL where ID_BILLING_USERS='{$ID_BILLING_USERS_PARAM}' and TILL=YEAR(NOW())-1 ;")) != 1) {
UAKTUALNIJ_KONTA($ID_BILLING_USERS_PARAM);
}
}
$WHERE .= " AND YEAR(BN.TIMESTAMP)query("drop TEMPORARY table TEMP_BILLING_ACCOUNTS_FILES");
$result_create = $db_webone->query($sql);
$result_alter = $db_webone->query($sql_alter);
$result_create2 = $db_webone->query($sql2);
echo'db_webone: ';print_r($db_webone);echo'
';
$sql = "select * from TEMP_BILLING_ACCOUNTS_FILES order by ID_BILLING_USERS,ID ";
$sql_count = "select count(1) as cnt from TEMP_BILLING_ACCOUNTS_FILES ";
$count = 0;
$res = $db_webone->query($sql_count);
if ($r_count = $db_webone->fetch($res)) {
$count = $r_count->cnt;
}
echo'count: ';print_r($count);echo'
';
if ($count > 0 && $ID_BILLING_USERS_PARAM > 0) {
$result = $db_webone->query($sql);
} else {
$sql_none = "insert into TEMP_BILLING_ACCOUNTS_FILES (ID,ID_BILLING_USERS,BAT_TILL,BAT_WINIEN,BAT_MA)
select '0',ID_BILLING_USERS,TILL ,WINIEN ,MA from BILLING_ACCOUNTS_TILL where ID_BILLING_USERS='{$ID_BILLING_USERS_PARAM}' and TILL=YEAR(NOW())-1
";
$db_webone->query($sql_none);
$result = $db_webone->query($sql);
}
while ($h_baf = $db_webone->fetch_row($result)) {
$baf[] = $h_baf;
}
return $baf;
}
public static function update_doc_number(&$fvat_arr) {
$fetch_ids = array();
foreach ($fvat_arr as $k => $fvat) {
$fetch_ids[] = $fvat['ID_BILLING_NUMBERS'];
}
if (!empty($fetch_ids)) {
$db_webone = DB::getDB('931');
if (!$db_webone) {
return -1;
}
$sql = "select `ID`, `NUMBER`, `ID_BILLING_PREFIXES`
from `BILLING_NUMBERS`
where
`ID` in(".implode(",", $fetch_ids).")
";
$res = $db_webone->query($sql);
$bnum = array();
while ($h = $db_webone->fetch_assoc($res)) {
$bnum[$h['ID']] = $h;
}
foreach ($bnum as $k_id => $bn) {
foreach ($fvat_arr as $k_fvat => $fvat) {
if ($bn['ID'] == $fvat['ID_BILLING_NUMBERS']) {
$fvat_arr[$k_fvat]['NUMBER'] = $bn['NUMBER'];
$fvat_arr[$k_fvat]['ID_BILLING_PREFIXES'] = $bn['ID_BILLING_PREFIXES'];
}
}
}
}
}
/**
* @param $user - Windykacja status user
* @param $taks - wezwanie1, wezwanie2
* @param $id_proj - ID Projektu
* @returns int - ID created record
*/
public static function create_koresp($user, $task, $id_proj, $params = array()) {
$sql_data = array();
$sql_zawartosc = '';
if ($task == 'wezwanie1') {
$sql_zawartosc = 'wezwanie do zaplaty';
$sql_data['K_TYP_KORESP'] = 'OUT';
$sql_data['K_TYP_RODZAJ'] = 'POLECONY';
$sql_data['K_LOKALIZACJA'] = 'PROJEKT';
$sql_data['K_LOKALIZACJA_NEW'] = 'PROJEKT';
} else if ($task == 'wezwanie2') {
$sql_zawartosc = 'wezwanie do zaplaty - ostateczne';
$sql_data['K_TYP_KORESP'] = 'OUT';
$sql_data['K_TYP_RODZAJ'] = 'POLECONY';
$sql_data['K_LOKALIZACJA'] = 'PROJEKT';
$sql_data['K_LOKALIZACJA_NEW'] = 'PROJEKT';
} else if ($task == 'rozwiazanie umowy') {
$sql_zawartosc = 'rozwiazanie umowy ID ' . $params['nr_umowy'] . ' - ' . $params['powod_desc'];
$sql_data['K_TYP_KORESP'] = 'IN';
}
if (!$sql_zawartosc) {
return false;
}
/**
* przykładowe wezwania:
* SELECT * FROM `IN7_DZIENNIK_KORESP` WHERE `K_ZAWARTOS` LIKE '%wezwanie%' ORDER BY `IN7_DZIENNIK_KORESP`.`ID` DESC;
* przykład:
* SELECT * FROM `IN7_DZIENNIK_KORESP` WHERE `ID`=25951;
*/
$db = DB::getDB();
$sql_obj = new stdClass();
$sql_obj->A_STATUS = 'WAITING';
foreach ($sql_data as $k_field => $v_value) {
$sql_obj->$k_field = $v_value;
}
$sql_obj->ID_BILLING_USERS = $user->ID;
$sql_obj->K_DATA_OTRZYM_KORESP = '';// data ?
$sql_obj->K_DATA_OTRZYMANEJ_KORESP = '';// data ?
$sql_obj->K_OD_KOGO = "{$user->P_NAME} {$user->P_NAME_SECOND}";
$sql_obj->OD_KOGO_ADRES = "ul. {$user->P_ADDRESS_STREET} {$user->P_ADDRESS_HOUSE}/{$user->P_ADDRESS_HOME}, {$user->P_ADDRESS_POST_CODE} {$user->P_ADDRESS_CITY}";
$sql_obj->K_ZAWARTOS = $sql_zawartosc;
$sql_obj->K_ZNAK_REFERENTA = User::getFullName() . ' (' . User::getInicjaly() . ')';
$sql_obj->ID_PROJECT = $id_proj;
$ret_id = $db->ADD_NEW_OBJ('IN7_DZIENNIK_KORESP', $sql_obj);
return $ret_id;
}
/**
* @param int $user_id - IS klienta
* @param int $id_deals_old - ID umowy do wyłączenia
* @param date 'Y-m-d' $termin_odlaczenia - Termin odłączenia
* @returns int - ID created record
*/
public static function create_deals_rozwiazanie($user_id, $id_deals_old, $termin_odlaczenia, $powod_desc = '') {
$db = DB::getDB();
$old_deal = $db->get_by_id('DEALS_TABLE', $id_deals_old);
if (!$old_deal) {
return false;
}
$sql_obj = new stdClass();
$sql_obj->DEALDESC = "Rezygnacja z dniem {$termin_odlaczenia}";
$sql_obj->A_STATUS = 'WAITING';
$sql_obj->ID_BILLING_USERS = $user_id;
$sql_obj->P_DEALNUMBER_OLD = $old_deal->ID;
$sql_obj->S_ADDRESS_STREET = $old_deal->S_ADDRESS_STREET;
$sql_obj->P_DEALDATE = $old_deal->P_DEALDATE;
$sql_obj->P_DEALDATE_TERM = $old_deal->P_DEALDATE_TERM;
$sql_obj->L2_HANGUP_FROM = $termin_odlaczenia;
$sql_obj->RODZAJ_DZIALANIA_HANDLOWEGO = $powod_desc;
// TODO: $sql_obj->ID_OFFERS_OFF = get_active_services_id($old_deal->ID);
/*
if(
SV.HANGUP_STATUS=SV.A_STATUS
, coalesce(SV.A_STATUS)
, if(
( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
and ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
)
, coalesce(SV.HANGUP_STATUS)
, if(
( unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_FROM)
and (SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
)
, coalesce(SV.A_STATUS)
, if(
( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
and unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_TILL)
and ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
)
, coalesce(SV.HANGUP_STATUS)
, if(
( unix_timestamp(NOW()) < unix_timestamp(SV.HANGUP_FROM) and unix_timestamp(NOW())< unix_timestamp(SV.HANGUP_TILL)
and ( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null ))
, coalesce(SV.A_STATUS)
, if(
( ( SV.HANGUP_TILL='0000-00-00' or SV.HANGUP_TILL is NULL )
and ( SV.HANGUP_FROM='0000-00-00' or SV.HANGUP_FROM is null ))
, coalesce(SV.A_STATUS)
, if( unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
and unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_TILL)
and (
( SV.HANGUP_TILL<>'0000-00-00' and SV.HANGUP_TILL is not NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )
)
, coalesce(SV.A_STATUS)
, if(
unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_FROM)
and unix_timestamp(NOW()) > unix_timestamp(SV.HANGUP_TILL)
and SV.HANGUP_FROM'0000-00-00' and SV.HANGUP_TILL is not NULL )
and ( SV.HANGUP_FROM<>'0000-00-00' and SV.HANGUP_FROM is not null )) , 'ERROR1',
'ERROR2'
)
)
)
)
)
)
)
)
*/
/*
$id_offers_off = array();
$sql = "select
s.``
, s.`ID_OFFERS`
from `SERVICES` as s
where
s.`ID_DEALS_TABLE`='{$old_deal->ID}'
and s.`ID_BILLING_USERS`='{$user_id}'
";
$res = $db->query($sql);
while ($r = $db->fetch($res)) {
$id_offers_off[] = $r;
}
*/
$ret_id = $db->ADD_NEW_OBJ('DEALS_TABLE', $sql_obj);
return $ret_id;
}
}
class Lay_Html_Toggle {
public static function start($title, $attrs = array()) {
$cls = array();
$cls[] = 'may-hide';
if (array_key_exists('open', $attrs) && $attrs['open'] == false) $cls[] = 'hide';
echo'';
$st = array();
$st[] = 'margin-bottom:0';
if (!empty($attrs['color'])) $st[] = 'color:'.$attrs['color'];
echo'
'.$title;
echo''."-".'';
echo''."+".'';
echo'
';
echo'
';
}
public static function end() {
echo'
';// .to-hide
echo'
AASDADS f. bankowy_make_nrach($NR_RACH_MASS_PAY,$ID_BILLING_USERS,$ID_BILLING_NUMBERS)
";
$ID_BILLING_USERS=str_pad($ID_BILLING_USERS,10,0,STR_PAD_LEFT) ;
$ID_BILLING_NUMBERS=str_pad($ID_BILLING_NUMBERS,2,0,STR_PAD_LEFT) ;
$ID_BILLING_NUMBERS=substr($ID_BILLING_NUMBERS,-2) ;
$NN = self::bankowy_make($NR_RACH_MASS_PAY.$ID_BILLING_NUMBERS.$ID_BILLING_USERS) ;
if($NN) return $NN.$NR_RACH_MASS_PAY.$ID_BILLING_NUMBERS.$ID_BILLING_USERS ;
}
public static function bankowy_make($nrb) {
$nrb=preg_replace("/[^0-9]/","",$nrb);
if (strlen($nrb)<>24) return 0;
$nrb .="252100";
$modulo=self::my_bcmod($nrb,97) ;
$modulo= 98 - $modulo ;
if(strlen($modulo)==1) $modulo="0".$modulo ;
return $modulo ;
if ($nrb[0]==$liczba[0] and $nrb[1]==$liczba[1]){
return 1;
}
return 0;
}
public static function my_bcmod($x, $y) {
// how many numbers to take at once? carefull not to exceed (int)
$take = 5;
$mod = '';
do {
$a = (int)$mod . substr($x, 0, $take);
$x = substr($x, $take);
$mod = $a % $y;
} while (strlen($x));
return (int)$mod;
}
public static function bankowy_formatuj_nrach($NR_RACH_MASS_PAY) {
$NR_RACH_MASS_PAY_FORMATTED = '';
if ($NR_RACH_MASS_PAY) {
for ($i=4; $i<(strlen($NR_RACH_MASS_PAY)); $i=$i+4) {
$NR_RACH_MASS_PAY_FORMATTED = " ".substr($NR_RACH_MASS_PAY,-$i,4).$NR_RACH_MASS_PAY_FORMATTED;
}
$NR_RACH_MASS_PAY_FORMATTED = substr($NR_RACH_MASS_PAY,0,$i-strlen($NR_RACH_MASS_PAY))." ".$NR_RACH_MASS_PAY_FORMATTED;
}
return $NR_RACH_MASS_PAY_FORMATTED ;
}
public static function policz($l,$t1,$t2,$t3) {
$j = array("", "jeden ", "dwa ", "trzy ", "cztery ", "pięć ", "sześć ",
"siedem ", "osiem ", "dziewięć ", "dziesięć ", "jedenaście ",
"dwanaście ", "trzynaście ", "czternaście ", "piętnaście ",
"szesnaście ", "siedemnaście ", "osiemnaście ", "dziewiętnaście ");
$d = array("", "", "dwadzieścia ", "trzydzieści ", "czterdzieści ",
"pięćdziesiąt ", "sześćdziesiąt ", "siedemdziesiąt ",
"osiemdziesiąt ", "dziewięćdziesiąt ");
$s = array("","sto ", "dwieście ", "trzysta ", "czterysta ", "pięćset ",
"sześćset ", "siedemset ", "osiemset ", "dziewięćset ");
$txt = $s[0+substr($l,0,1)];
if (substr($l,1,2)<20) {
$txt .= $j[0+substr($l,1,2)];
}
else {
$txt .= $d[0+substr($l, 1,1)].$j[0+substr($l, 2,1)];
}
if ($l<>0) {
if ($l==1) {
$txt .= "$t1 ";
} else {
if ((substr($l,2,1)==2 or substr($l,2,1)==3 or substr($l,2,1)==4) and (substr($l,1,2)>20 or substr($l,1,2)<10)) {
$txt .= "$t2 ";
} else {
$txt .= "$t3 ";
}
}
}
return $txt;
}
public static function slownie($liczba) {
$txt = '';
$kwota = explode(".", $liczba);
if (strlen($kwota[1])==1) $kwota[1] = $kwota[1]."0";
$kwotazl = sprintf("%012d",$kwota[0]);
$kwotagr = sprintf("%03d",$kwota[1]);
$txt .= self::policz(substr($kwotazl, 0,3),"miliard","miliardy","miliardów");
$txt .= self::policz(substr($kwotazl, 3,3),"milion","miliony","milionów");
$txt .= self::policz(substr($kwotazl, 6,3),"tysiąc","tysiące","tysięcy");
$txt .= self::policz(substr($kwotazl, 9,3),"złoty","złote","złotych");
if ($kwotazl==0) $txt .= "zero złotych ";
$txt .= " i ";
$txt .= self::policz($kwotagr,"grosz","grosze","groszy");
if ($kwotagr==0) $txt .= "zero groszy";
return $txt;
}
public static function formatuj_grosze($kwota) {
$kwota = round($kwota, 2);
$liczba = explode(".", $kwota);
if (!$liczba[1]) {
$liczba = explode(",", $kwota);
if (!$liczba[1]) $liczba[1] = "00";
}
if (strlen($liczba[1]) == 1) $liczba[1] = $liczba[1] . "0";
$liczba_out = $liczba[0] . "." . "$liczba[1]";
return $liczba_out;
}
public static function billing_billwiev_przeterminowania($ID_BILLING_USERS_PARAM, $IF_PRINT_PARAM, $ECHO_PARAM) {
global $vdb
, $STOPA_ODSETEK // from billing-l1/www/config.php
, $form_mail_sender // billing-l1/www/config.php:24:$form_mail_sender="/usr/bin/mutt";
, $form_send_from_param // billing-l1/www/config.php:19:$form_send_from_param="biall-net@biall.net.pl";
;
//NAGLOWKI DO HTML MAILA
$naglowki = "From: ".$form_send_from_param."\r\n";
$naglowki .= "MIME-Version: 1.0\r\n";
$naglowki .= "Content-type: text/html; charset=iso-8859-1\r\n";
//PODRECZNE_ZAPYTANIE_SLOWNIKOWE_DO_TYPOW_DOKUMENTOW
$BNT = array();
$bnt_types = WindykacjaStatsModel::get_billing_types();
foreach ($bnt_types as $k_type_id => $h_type) {
$BNT[$h_type['ID']] = $h_type['TYPE'];
}
echo'bnt_types: ';print_r($bnt_types);echo'
';
echo'BNT: ';print_r($BNT);echo'
';
if ($_GET['ID_BILLING_USERS_PARAM']) $ID_BILLING_USERS_PARAM = $_GET['ID_BILLING_USERS_PARAM'];
if (!$IF_PRINT_PARAM && !$ECHO_PARAM) {
echo'';
echo"";
echo"![\"Zadłużenia\"](\"$xpath/ico/kw.gif\")
";
echo"Druki wezwania do zaplaty";
echo"";
echo'
';
}
$user_id = $ID_BILLING_USERS_PARAM;
$billing_account_files = WindykacjaStatsModel::get_billing_account_files($user_id);
echo'billing_account_files: ';print_r($billing_account_files);echo'
';
die('F.' . __FUNCTION__ . ' ...');
$NAGLOWEK = "| Płatnik | WINIEN | MA | DOK | Numer | (T)ermin pł./(D)ata ksiegowania | Dni przeterminowania | Kwota przeterminowana | Odsetki |
";
//while ($BAF = $vdb->sql_fetchrow($result)) {
foreach ($billing_account_files as $BAF) {
//RESETUJEMY FORM - jak nie chcemy pojedynczego odczytu:
if (!$ID_BILLING_USERS_PARAM) unset($FORM);
if (!$LAST_PLATNIK) $LAST_PLATNIK = $BAF[1];
if (!$LPD) $LPD = $BAF;
if (($BAF[0] <> $LAST_ID)) {// ID
//DRUKUJEMY_STARE_PODSUMOWANIE (DLA_FAKTURY POJEDYNCZEJ)
if ($LAST_ID) {
$FORM_2.=" | ".FunkcjeL1::formatuj_grosze($LTOTAL[WINIEN])." | ".FunkcjeL1::formatuj_grosze($LTOTAL[MA])." | | ";
$FORM_S.= "
| ".FunkcjeL1::formatuj_grosze($LTOTAL[WINIEN])." | ".FunkcjeL1::formatuj_grosze($LTOTAL[MA])." | | ";
$LDNI = floor((date("U") - $DOC_TERM) / 86400) ;
if(($LDNI>0) && ($DOC_TERM) && ($LTOTAL['WINIEN_MA'] == "WINIEN")) {
$LODSETKI= FunkcjeL1::formatuj_grosze($LTOTAL[$LTOTAL[WINIEN_MA]] * $STOPA_ODSETEK * $LDNI / 365);
$POD_ODSETEK=$LTOTAL[$LTOTAL['WINIEN_MA']];
$PLATNIK_DATA['PRZETER']['ODSETKI'] += $LODSETKI;
$PLATNIK_DATA['PRZETER']['WINIEN'] += $LTOTAL[$LTOTAL['WINIEN_MA']];
} else {
unset($LODSETKI);
unset($LDNI);
unset($POD_ODSETEK);
}
$FORM_2.= "$LDNI | $POD_ODSETEK | ";
$FORM_2.= "$LODSETKI | ";
$FORM_S.= "--- | --- | --- | ";
// if(!$PLATNIK_DATA[TOTAL][WINIEN])
$PLATNIK_DATA[TOTAL][WINIEN]+=FunkcjeL1::formatuj_grosze($LTOTAL[WINIEN]) ;
// if(!$PLATNIK_DATA[TOTAL][MA])
$PLATNIK_DATA[TOTAL][MA]+=FunkcjeL1::formatuj_grosze($LTOTAL[MA]) ;
$FORM_2.= "
";
$FORM_2.= "
|
";
$FORM_S.="
|
";
$TOTAL[WINIEN]+=$LTOTAL[WINIEN] ; $TOTAL[MA]+=$LTOTAL[MA] ;
if ($LAST_PLATNIK <> $BAF[1]) {//PODSUMOWANIE_DLA_PLATNIKA
$TOTAL[PRZETER]+=$PLATNIK_DATA[PRZETER][WINIEN];
$TOTAL[ODSETKI]+=$PLATNIK_DATA[PRZETER][ODSETKI];
$FORM_2.= "Podsumowanie salda niezbilansowanych zobowiązań dla platnika $LAST_PLATNIK
$LPD[P_NAME] $LPD[P_NAME_SECOND] $LPD[P_ADDRESS_CITY] $LPD[P_ADDRESS_STREET] $LPD[P_ADDRESS_HOUSE] $LPD[P_ADDRESS_HOME]
WINIEN: ".$PLATNIK_DATA[TOTAL][WINIEN]." - MA: ".$PLATNIK_DATA[TOTAL][MA]." DO ZAPŁATY: ".FunkcjeL1::formatuj_grosze($PLATNIK_DATA[TOTAL][WINIEN]-$PLATNIK_DATA[TOTAL][MA])." " ;
if($PLATNIK_DATA[PRZETER][WINIEN]) $FORM_2.=", z czego przeterminowane ".FunkcjeL1::formatuj_grosze($PLATNIK_DATA[PRZETER][WINIEN])." ";
if($PLATNIK_DATA[PRZETER][ODSETKI]) $FORM_2.= " ( aktualnie nalezne odsetki to ".$PLATNIK_DATA[PRZETER][ODSETKI]." PLN , które mogą zostać naliczone ) " ;
if($LPD[BA_TIMESTAMP]) $FORM_2.="
Stan wzajemnych rozrachunków: WINIEN: $LPD[BA_WINIEN] - MA: $LPD[BA_MA] SALDO(DO ZAPŁATY): ".FunkcjeL1::formatuj_grosze($LPD[BA_WINIEN]-$LPD[BA_MA])." , i jest to stan na dzień ".date("Y-m-d",$LPD['BA_TIMESTAMP'])." ";
$RETURN_ARR="";
$FORM_S.=" |
|
";
$FORM_2.="
W razie problemów prosimy o kontakt z biurem obsługi.
webone billing system ©2004 by Arkadiusz Binder & www.grafnet.pl | finance module by A.Binder ";
$FORM_2.= " ";
$FORM_2 = "{$NAGLOWEK} \n {$FORM_2}";
$FORM_S= "{$NAGLOWEK} \n {$FORM_S}";
if ($IF_PRINT_PARAM) {
$delivery_subject="Stan rozrachunków dla {$LPD[P_NAME]} {$LPD[P_NAME_SECOND]}" ;
$sql2="insert into HIST_CONTACTS (ID_BILLING_USERS,ID_BILLING_NUMBERS,SUBJECT,BODY_HTML,REQUEST_STATUS_MAIL) values
('".$LAST_PLATNIK."','','{$delivery_subject}','{$FORM_2}','CONFIRM_SENT_MAIL') ";
$res = $vdb->sql_query($sql2) ;
echo "
$sql2
****";
} else if (!$ECHO_PARAM) {
echo $FORM_2;
}
$LAST_PLATNIK=$BAF[1];
unset($PLATNIK_DATA);
$FORM.=$FORM_2 ;
unset($FORM_2);
$FORM_SS.=$FORM_S ;
unset($FORM_S);
} else {
}
//$PLATNIK_DATA[TOTAL][WINIEN]+=$BAF[2];
//$PLATNIK_DATA[TOTAL][MA]+=$BAF[3];
$LPD = $BAF;
$LAST_PLATNIK = $BAF[1];
}
else {
}
//DODAJEMY_NOWY_REKORD_OD_SAMEJ_PLATNOSCI
unset($LTOTAL);
$FORM_2.= "| $BAF[1] | $BAF[2] | $BAF[3] | ".$BNT[$BAF[4]]." | $BAF[5]/$BAF[6] | ";
if ($BAF[19]) $FORM_2.="T: ".date("Y-m-d",$BAF[19])." "; else $FORM_2.="D: ".date("Y-m-d",$BAF['BAF_TIMESTAMP'])." ";
$FORM_2.=" |
";
$FORM_S.= "| $BAF[1] | $BAF[2] | $BAF[3] | ".$BNT[$BAF[4]]." | $BAF[5]/$BAF[6] | ";
if($BAF[19])$FORM_S.="T: ".date("Y-m-d",$BAF[19])." "; else $FORM_2.="D: ".date("Y-m-d",$BAF['BAF_TIMESTAMP'])." ";
$FORM_S.=" |
";
$DOC_TERM = $BAF[19];
if ($BAF[2] > 0) $LTOTAL['WINIEN_MA'] = "WINIEN"; else $LTOTAL['WINIEN_MA'] = "MA";
$LTOTAL[$LTOTAL['WINIEN_MA']] = $BAF[2] + $BAF[3];
} else {
//DRUKUJEMY_PODSTUMOWANIE_STAREGO i ZAPODAJEMY NOWY_REKORD
}
if ($BAF[12]) {// ID_FIN_WINIEN_VAL
$FORM_2.= " | ";
$FORM_S.= "
| ";
if($LTOTAL['WINIEN_MA']=="MA") {
$FORM_2.= " $BAF[12] | ";
$FORM_2.= " | ";
$FORM_S.=" $BAF[12] | | ";
} else {
$FORM_2.= " | ";
$FORM_2.= " $BAF[12] | ";
$FORM_S.=" | $BAF[12] | ";
}
$FORM_2.= "".$BNT[$BAF[8]]." | ";
$FORM_2.= " $BAF[9]/$BAF[10] | D: ".date("Y-m-d",$BAF[11])." | ";
$FORM_S.= "".$BNT[$BAF[8]]." | ";
$FORM_S.= " $BAF[9]/$BAF[10] | D: ".date("Y-m-d",$BAF[11])." | ";
$LDNI = floor(($BAF[11] - $DOC_TERM) / 86400);
if (($LDNI > 0) && ($DOC_TERM) && ($LTOTAL['WINIEN_MA'] == "WINIEN")) {
$DOC_TERM = $BAF[11];
$LODSETKI = FunkcjeL1::formatuj_grosze($LTOTAL[$LTOTAL['WINIEN_MA']] * $STOPA_ODSETEK * $LDNI / 365);
$POD_ODSETEK = $LTOTAL[$LTOTAL['WINIEN_MA']];
$PLATNIK_DATA['PRZETER']['ODSETKI'] += $LODSETKI;
} else {
unset($LODSETKI);
unset($LDNI);
unset($POD_ODSETEK);
}
$FORM_2.= "$LDNI | $POD_ODSETEK | ";
$FORM_2.= "$LODSETKI | ";
$FORM_2.= "
";
$FORM_S.="--- | --- | --- | ";
$LTOTAL[$LTOTAL['WINIEN_MA']] -= $BAF[12];
}
if ($BAF[18]) {// ID_FIN_MA_VAL
$FORM_2.= " | ";
$FORM_S.= "
| ";
if ($LTOTAL['WINIEN_MA'] == "MA") {
$FORM_2.= " $BAF[18] | ";
$FORM_2.= " | ";
$FORM_S.= " $BAF[18] | | ";
} else {
$FORM_2.= " | ";
$FORM_2.= " $BAF[18] | ";
$FORM_S.=" | $BAF[18] | ";
}
$FORM_2.= "".$BNT[$BAF[14]]." | ";
$FORM_2.= " $BAF[15]/$BAF[16] | D: ".date("Y-m-d",$BAF[17])." | ";
$FORM_S.="".$BNT[$BAF[14]]." | $BAF[15]/$BAF[16] | D: ".date("Y-m-d",$BAF[17])." | ";
$LDNI=floor(($BAF[17] - $DOC_TERM) / 86400) ;
// $LDNI=10;
if (($LDNI > 0) && ($DOC_TERM) && ($LTOTAL['WINIEN_MA'] == "WINIEN")) {
$LODSETKI = FunkcjeL1::formatuj_grosze($LTOTAL[$LTOTAL[WINIEN_MA]] * $STOPA_ODSETEK * $LDNI / 365);
$POD_ODSETEK = $LTOTAL[$LTOTAL['WINIEN_MA']];
$PLATNIK_DATA['PRZETER']['ODSETKI'] += $LODSETKI;
} else {
unset($LODSETKI);
unset($LDNI);
unset($POD_ODSETEK);
}
$FORM_2.= "$LDNI | $POD_ODSETEK | ";
$FORM_S.= "$LDNI | $POD_ODSETEK | ";
$FORM_2.= "$LODSETKI | ";
$FORM_S.= "$LODSETKI | ";
$FORM_2.= "
";
$FORM_S.= "";
$LTOTAL[$LTOTAL['WINIEN_MA']]-=$BAF[18] ;
}
$LAST_ID = $BAF[0];
$L_BAF = $BAF;
}//eof SQL while($BAF=$vdb->sql_fetchrow($result)) {
//DODANIE_ZESTAWIENIA KOPIA_Z_GORY
//DRUKUJEMY_STARE_PODSUMOWANIE (DLA_FAKTURY POJEDYNCZEJ)
if ($LAST_ID) {
$FORM_2.=" | ".FunkcjeL1::formatuj_grosze($LTOTAL['WINIEN'])." | ".FunkcjeL1::formatuj_grosze($LTOTAL['MA'])." | | ";
$FORM_S.="
| ".FunkcjeL1::formatuj_grosze($LTOTAL['WINIEN'])." | ".FunkcjeL1::formatuj_grosze($LTOTAL['MA'])." | | ";
$LDNI = round((date("U") - $DOC_TERM) / 86400, 0);
if (($LDNI > 0) && ($DOC_TERM)) {
$LODSETKI = FunkcjeL1::formatuj_grosze($LTOTAL[$LTOTAL['WINIEN_MA']] * $STOPA_ODSETEK * $LDNI / 365);
$POD_ODSETEK = $LTOTAL[$LTOTAL['WINIEN_MA']];
$PLATNIK_DATA['PRZETER']['ODSETKI'] += $LODSETKI ;
$PLATNIK_DATA['PRZETER']['WINIEN'] += $LTOTAL[$LTOTAL['WINIEN_MA']];
} else {
unset($LODSETKI);
unset($LDNI);
unset($POD_ODSETEK);
}
$FORM_2.= "$LDNI | ".FunkcjeL1::formatuj_grosze($POD_ODSETEK)." | ";
$FORM_2.= "$LODSETKI | ";
$FORM_S.="--- | --- | --- | ";
// if(!$PLATNIK_DATA[TOTAL]['WINIEN'])
$PLATNIK_DATA['TOTAL']['WINIEN']+=FunkcjeL1::formatuj_grosze($LTOTAL['WINIEN']) ;
// if(!$PLATNIK_DATA[TOTAL][MA])
$PLATNIK_DATA['TOTAL']['MA']+=FunkcjeL1::formatuj_grosze($LTOTAL['MA']) ;
$FORM_2.= "
";
$FORM_2.= "
|
";
$FORM_S.="
|
";
$TOTAL['WINIEN']+=$LTOTAL['WINIEN'] ; $TOTAL['MA']+=$LTOTAL['MA'] ;
if ($LAST_PLATNIK <> $BAF[1]) {//PODSUMOWANIE_DLA_PLATNIKA
$TOTAL['PRZETER'] += $PLATNIK_DATA['PRZETER']['WINIEN'];
$TOTAL['ODSETKI'] += $PLATNIK_DATA['PRZETER']['ODSETKI'];
if (!$LPD) $LPD = $L_BAF ;
$FORM_2.= "Podsumowanie salda niezbilansowanych zobowiązań dla platnika $LAST_PLATNIK
$LPD[P_NAME] $LPD[P_NAME_SECOND] $LPD[P_ADDRESS_CITY] $LPD[P_ADDRESS_STREET] $LPD[P_ADDRESS_HOUSE] $LPD[P_ADDRESS_HOME]
WINIEN: ".$PLATNIK_DATA['TOTAL']['WINIEN']." - MA: ".$PLATNIK_DATA['TOTAL']['MA']."
DO ZAPŁATY: ".FunkcjeL1::formatuj_grosze($PLATNIK_DATA['TOTAL']['WINIEN']-$PLATNIK_DATA['TOTAL']['MA'])." " ;
if ($PLATNIK_DATA['PRZETER']['WINIEN']) $FORM_2.=", z czego przeterminowane ".FunkcjeL1::formatuj_grosze($PLATNIK_DATA['PRZETER']['WINIEN'])." ";
if ($PLATNIK_DATA['PRZETER']['ODSETKI']) $FORM_2.= " ( aktualnie nalezne odsetki to ".$PLATNIK_DATA['PRZETER']['ODSETKI']." PLN , które mogą zostać naliczone ) " ;
if ($LPD['BA_TIMESTAMP']) $FORM_2.="
Stan wzajemnych rozrachunków: WINIEN: ".$LPD['BA_WINIEN']." - MA: ".$LPD['BA_MA']." SALDO(DO ZAPŁATY): ".FunkcjeL1::formatuj_grosze($LPD['BA_WINIEN']-$LPD['BA_MA'])." , i jest to stan na dzień ".date("Y-m-d",$LPD['BA_TIMESTAMP'])." ";
$RETURN_ARR="";
$FORM_S.=" |
|
";
$FORM_2.="
W razie problemów prosimy o kontakt z biurem obsługi.
webone billing system ©2004 by Arkadiusz Binder & www.grafnet.pl | finance module by A.Binder ";
$FORM_2.= " ";
$FORM_2 = "{$NAGLOWEK} \n {$FORM_2}";
$FORM_S = "{$NAGLOWEK}{$FORM_S}";
//DRUKOWANIE ZESTAWIENIA
if ($IF_PRINT_PARAM) {
$delivery_subject="Stan rozrachunków dla {$LPD['P_NAME']} {$LPD['P_NAME_SECOND']}" ;
// exec("echo \"Pliki zostały dołączone do załącznika\" | $form_mail_sender -n -a \"$filename\" $delivery_mail -s ".$delivery_subject."" );
$sql2="insert into HIST_CONTACTS (ID_BILLING_USERS,ID_BILLING_NUMBERS,SUBJECT,BODY_HTML,REQUEST_STATUS_MAIL) values
('".$LAST_PLATNIK."','','{$delivery_subject}','{$FORM_2}','CONFIRM_SENT_MAIL') ";
$res = $vdb->sql_query($sql2);
echo "
{$sql2}
***";
} else if (!$ECHO_PARAM) {
echo $FORM_2;
}
//EOF DRUKOWANIE ZESTAWIENIA itp
$LAST_PLATNIK = $BAF[1];
$ECHO_DATA=$PLATNIK_DATA;
unset($PLATNIK_DATA);
$FORM .= "{$FORM_2}";
$FORM_SS .= "{$FORM_S}";
unset($FORM_2);
unset($FORM_S);
}
}
$FORM_2.= $NAGLOWEK;
$FORM_2.= "| TOT | {$TOTAL['WINIEN']} | {$TOTAL['MA']} | |
{$TOTAL['PRZETER']} | {$TOTAL['ODSETKI']} |
";
$FORM_2 .= "";
if (!$ECHO_PARAM) echo "{$FORM_2}";
$FORM_S.= " ";
if ($ECHO_PARAM == "zadluzenie") return FunkcjeL1::formatuj_grosze($ECHO_DATA['PRZETER']['WINIEN']);
if ($ECHO_PARAM == "saldo") return FunkcjeL1::formatuj_grosze($LPD['BA_WINIEN']-$LPD['BA_MA']);
if ($ECHO_PARAM == "zadluzenie_lista") return $FORM;
if ($ECHO_PARAM == "saldo_rok") {
$L_BAF['FORM'] = $FORM;
$L_BAF['FORM_SALDO'] = $FORM_SS;
return $L_BAF;
}
}
}