clog_list_of_students->clog_student->account_number01))) { // debug //echo "is empty"; } else { /***** TODO AFTER EACH UPDATE 1) download account statement manually from on-line bank История операций > Выписки по продуктам > Счет > За период > inc_payments/account_statements/account_statement_*csv 2) remove deprecated locally after each download: cd tmp_files/ rm payment_* rm attendance_history_* 3) remove deprecated on-line lftp tmp_files/ mrm payment_* mrm attendance_history_ 4) upload latest (still using lftp) cd ../inc_payments/account_statements/ mrm account_statement_01.01.20-* lcd ../inc_payments/account_statements/ mput account_statement_01.01.20-* ****/ /***** get list of account statements *****/ $account_statements = "tmp_files/list_of_account_statements.txt"; $fh_acc_list = fopen($account_statements, 'w') or die("can't open file $account_statements"); $pattern_file_name ="inc_payments/account_statements/account_statement_*csv"; $list_of_account_statements = shell_exec("ls $pattern_file_name | grep -v 'utf8' | sed 's/inc_payments\/account_statements\///'"); fwrite($fh_acc_list, $list_of_account_statements) or die ("can't run fwrite list of account statements"); fclose($fh_acc_list) or die("can't fclose fh_acc_list"); /***** convert Russian account statement to utf8 *****/ if (file_exists($account_statements)) { // todo // foreach account_statement in $list_of_account_statement convert and cat shell_exec("iconv -c --from-code=WINDOWS-1251 --to-code=UTF-8 -o inc_payments/account_statements/account_statement_utf8.csv inc_payments/account_statements/$list_of_account_statements"); $account_statement_utf8 = file_get_contents('inc_payments/account_statements/account_statement_utf8.csv'); //echo "
".$account_statement_utf8."
\n"; } // if file_exists else { echo "$ file account_statements does not exist"; } /***** FIND PAYMENT(S) BY MEMBER IN ACCOUNT STATEMENT(S) ****/ /*** fetch account details ***/ // account 01 if (trim(!empty($clog_course_details->clog_list_of_students->clog_student->account_number01))) { $account_number01 = $clog_course_details->clog_list_of_students->clog_student->account_number01; // echo "account number 01: ".$account_number01."
\n"; $reference_pattern = '/(^.*'.$account_number01.'.*$)/m'; } else { echo "No account number available\n"; } // account 02 if (trim(!empty($clog_course_details->clog_list_of_students->clog_student->account_number02))) { $account_number02 = $clog_course_details->clog_list_of_students->clog_student->account_number02; //echo "account number 02: ".$account_number02."
\n"; $reference_pattern = '/(^.*'.$account_number01.'.*|^.*'.$account_number02.'.*)/m'; } else { echo "\n"; } // account 03 if (trim(!empty($clog_course_details->clog_list_of_students->clog_student->account_number03))) { $account_number03 = $clog_course_details->clog_list_of_students->clog_student->account_number03; //echo "account number 03: ".$account_number03."
\n"; $reference_pattern = '/(^.*'.$account_number01.'.*|^.*'.$account_number02.'.*|^.*'.$account_number03.'.*\$)/m'; } else { echo "\n"; } /**** create payment history cv ****/ $file_payment_history = "tmp_files/payment_history_$member.csv"; $fh_file_payment_history = fopen($file_payment_history, 'w') or die("can't open file $file_payment_history"); /*** find account(s) ***/ if (preg_match_all($reference_pattern,$account_statement_utf8,$matches)) { foreach ($matches[0] as $matched_account_number) { // define columns in account statement $columns_payment_history= preg_split ("/;/",$matched_account_number); //echo $columns_payment_history[0]."
"; // date //echo $columns_payment_history[1]."
"; // account holder & number //echo $columns_payment_history[2]."
"; // RUB //echo $columns_payment_history[3]."
"; // sum //echo $columns_payment_history[4]."
"; // RUB //echo $columns_payment_history[5]."
"; // sum // format date in column date $columns_payment_history[0] = str_replace(' 00:00','',$columns_payment_history[0]); $date = preg_split("/\./",$columns_payment_history[0]); //echo $date[2].$date[1].$date[0]."
"; // format account holder & number $columns_payment_history[1] = str_replace('рублевый перевод,','bank transfer ',$columns_payment_history[1]); $columns_payment_history[1] = str_replace('. НДС не облагается,номер счета отправителя-',' ',$columns_payment_history[1]); $columns_payment_history[1] = str_replace('НДС не облагается',' ',$columns_payment_history[1]); $columns_payment_history[1] = str_replace(',номер счета отправителя-',' ',$columns_payment_history[1]); //echo $columns_payment_history[1]."
"; // account holder & number // format sum $columns_payment_history[3] = str_replace(' ','',$columns_payment_history[3]); $columns_payment_history[3] = str_replace('.00','',$columns_payment_history[3]); //echo $columns_payment_history[3]."
"; // sum // create new columns for csv $payment_history_6_columns = $date[2].$date[1].$date[0].";".$columns_payment_history[1].";;;;".$columns_payment_history[3].";\n"; // DEBUG //echo "
".$payment_history_6_columns."
"; // finalise payment history fwrite($fh_file_payment_history,$payment_history_6_columns) or die ('failed to write $ fh_file_payment_history, $ payment_history_6_columns'); } } // if (preg_match_all($reference_pattern,$account_statement_utf8,$matches)) /**** CALCULATE ATTENDANCE COSTS ****/ /*** create attendance history ***/ $file_attendance_history = "tmp_files/attendance_history_$member.csv"; $fh_file_attendance_history = fopen($file_attendance_history, 'a') or die("can't open file $file_attendance_history"); // need to load the whole course log and not only the latest session // start loop if (preg_match_all("#^$(.*?)^<\/clog_session>$#sim",$sessions_log,$matches)) { foreach ($matches[0] as $clog_xml_attendance_costs) { // needed to rename to clog_xml_attendance_costs to avoid collision w/ clog_xml in sbook_session_from_xml.inc $clog_xml_attendance_costs=simplexml_load_string($clog_xml_attendance_costs); if (trim($clog_xml_attendance_costs->clog_session_status =='lc')) { // todo //$session_date_for_attendance_history = $clog_xml_attendance_costs->clog_session_date." (late cancellation)"; $session_date_for_attendance_history = $clog_xml_attendance_costs->clog_session_date; // late cancellation → lesson charged //echo "session date for attendance history: ".$session_date_for_attendance_history; //fwrite($fh_file_attendance_history,$session_date_for_attendance_history); } if ((trim($clog_xml_attendance_costs->clog_session_status !='future')) && (trim($clog_xml_attendance_costs->clog_session_status !='lc')) && (!empty($clog_xml_attendance_costs->clog_session_date_cancelled) && !empty($clog_xml_attendance_costs->clog_session_date_rescheduled))) { // lesson not charged //$session_date_for_attendance_history = $clog_xml_attendance_costs->clog_session_date; //echo "session date for attendance costs: ".$session_date_for_attendance_history; //fwrite($fh_file_attendance_history,$session_date_for_attendance_history); } if ((trim($clog_xml_attendance_costs->clog_session_status !='future')) && (trim($clog_xml_attendance_costs->clog_session_status !='lc')) && (trim(empty($clog_xml_attendance_costs->clog_session_date_cancelled))) && (!empty($clog_xml_attendance_costs->clog_session_date))) { $session_date_for_attendance_history = $clog_xml_attendance_costs->clog_session_date; //echo "session date for attendance: ".$session_date_for_attendance_history; //fwrite($fh_file_attendance_history,$session_date_for_attendance_history); } if (!empty($clog_xml_attendance_costs->clog_session_credit)) { //$session_credit_for_attendance_costs = $clog_xml_attendance_costs->clog_session_credit; } if (!empty($clog_xml_attendance_costs->clog_session_ach)) { $session_ach_for_attendance_history = $clog_xml_attendance_costs->clog_session_ach; //echo "session ach for attendance: ".$session_ach_for_attendance_history; //fwrite($fh_file_attendance_history,$session_ach_for_attendance_history); } if (!empty($clog_xml_attendance_costs->clog_session_rate)) { $session_rate_for_attendance_history = $clog_xml_attendance_costs->clog_session_rate; //echo "session rate for attendance: ".$session_rate_for_attendance_history; //fwrite($fh_file_attendance_history,$session_rate_for_attendance_history); } $cost = $session_rate_for_attendance_history * $session_ach_for_attendance_history; /********** todo if time available current balance *************/ //$previous_balance = "1450"; // not always! //$previous_balance = 0; // depends on previous lesson... //$current_balance = $cost - $previous_balance; //$attendance_data = $session_date_for_attendance_history.";;".$session_ach_for_attendance_history.";".$session_rate_for_attendance_history.";-".$cost.";;;;".$current_balance."\n"; $attendance_data = $session_date_for_attendance_history.";lesson;".$session_ach_for_attendance_history.";".$session_rate_for_attendance_history.";-".$cost.";;;;\n"; //echo "
(3) attendance data from course log: ".$attendance_data."
\n"; fwrite($fh_file_attendance_history,$attendance_data); // end loop } // foreach ($matches[0] as $clog_xml_attendance_costs) } // if (preg_match_all("#^$(.*?)^<\/clog_session>$#sim",$sessions_log,$matches)) /**** FINALISE PAYMENT AND ATTENDANCE COSTS ******/ // create final file $file_payment_and_attendance_costs = "tmp_files/payment_and_attendance_costs_$member.csv"; $fh_file_payment_and_attendance_costs = fopen($file_payment_and_attendance_costs , "w") or die("could not open file for fh_file_payment_and_attendance_costs"); // make attendance costs unique and copy contents into new file $file_attendance_history=file($file_attendance_history); //echo $file_attendance_history; // array $unique = array_unique($file_attendance_history); foreach($unique as $line1) { // echo "
".$line1."
\n"; fwrite($fh_file_payment_and_attendance_costs, $line1) or die("could not write file for fh_file_payment_and_attendance_costs"); } // copy contents into new file $file_payment_history = "tmp_files/payment_history_$member.csv"; $file_payment_history = file($file_payment_history); // doesn't actually need unique but just in case - NO! bc some payments could be made on the same day... // $unique2 = array_unique($file_payment_history); //$unique2 = array($file_payment_history); $unique2 = $file_payment_history; foreach($unique2 as $line2) { // echo "
".$line2."
\n"; fwrite($fh_file_payment_and_attendance_costs, $line2); } // create new file for rsort $file_payment_and_attendance_costs2 = "tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"; $fh_file_payment_and_attendance_costs2 = fopen($file_payment_and_attendance_costs2 , "w") or die("could not open file for fh_file_payment_and_attendance_costs2"); // shell_exec("sort -r $file_payment_and_attendance_costs | cat > $file_payment_and_attendance_costs2 "); // sort -u reverse $file_payment_and_attendance_costs2 = file($file_payment_and_attendance_costs); $file_payment_and_attendance_costs2 = array_unique($file_payment_and_attendance_costs2); rsort($file_payment_and_attendance_costs2); // sort in revert order foreach($file_payment_and_attendance_costs2 as $line2) { // debug //echo "
".$line2."
\n"; fwrite($fh_file_payment_and_attendance_costs2, $line2) or die("could not write file for fh_file_payment_and_attendance_costs2"); } fclose($fh_file_payment_and_attendance_costs); fclose($fh_file_payment_and_attendance_costs2); fclose($fh_file_attendance_history) or die("can't fclose fh_file_attendance_history"); fclose($fh_file_payment_history) or die("can't fclose fh_file_payment_history"); } // else if (trim(empty($clog_course_details->clog_list_of_students->clog_student->account_number01))) /**** SUM PAYMENTS ****/ // end import if ($member == NULL) { echo "Your session has expired!
Please log in to view your payment history."; } else { $file_payment_and_attendance_costs2 = "tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"; if (file_exists($file_payment_and_attendance_costs2 )) { $file_payment_and_attendance_costs2 = fopen($file_payment_and_attendance_costs2, "r") or die ('cant open $ file_payment_and_attendance_costs2'); // empty array before each loop or will add up $csv = array(); while(!feof($file_payment_and_attendance_costs2)) { // [] mandatory $csv[] = fgetcsv($file_payment_and_attendance_costs2,1024,";"); // var_dump ($csv[0][4]); } // todo // $last_update = $csv[0][6]; // this is today's date only // but should be giving last date from bank statement $total_ach = array_sum(array_column($csv,'2')); $total_cost = array_sum(array_column($csv,'4')); $total_paid = array_sum(array_column($csv,'5')); $final_balance = $total_cost + $total_paid; $session_rate_for_attendance_history = (-$total_cost) / $total_ach; // approximate bc rate actually depends if f2f or on-line $final_balance_ach = $final_balance / $session_rate_for_attendance_history; $final_balance = number_format($final_balance, 0); $final_balance_ach = number_format($final_balance_ach, 2); echo "
"; echo "
"; //echo "last update: ".$last_update."
"; echo "total ach: ".$total_ach."
"; //echo "total cost: ".$total_cost."
"; //echo "total paid: ".$total_paid."
"; echo "lessons left (approx ach): ".$final_balance_ach."
"; echo "balance: ".$final_balance."\n"; echo "
\n"; echo "\"payment \"logo\" payment history"; echo "
"; fclose($file_payment_and_attendance_costs2); // add total sums // v2 $table_heading1 = ";ATTENDANCE AND COSTS: ".$member.";;;;last update: ;".`date +%Y%m%d`.";\n"; shell_exec("sed -i '1i ".$table_heading1."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer5 = ";;;;;;\n"; shell_exec("sed -i '2i ".$table_footer5."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer1 = "SUMMARY;total ach;".$total_ach.";;;\n"; shell_exec("sed -i '3i ".$table_footer1."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer2 = ";total due;;;".$total_cost.";\n"; shell_exec("sed -i '4i ".$table_footer2."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer3 = ";total paid;;;;".$total_paid."\n"; shell_exec("sed -i '5i ".$table_footer3."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer4 = ";BALANCE;;;;;".$final_balance."\n"; shell_exec("sed -i '6i ".$table_footer4."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_footer5 = ";;;;;;\n"; shell_exec("sed -i '7i ".$table_footer5."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); $table_heading4 = "date;description;ach;rate;due;paid;\n"; shell_exec("sed -i '8i ".$table_heading4."' tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv"); //shell_exec("cp tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv tmp_files/payment_history_and_attendance_costs_sbook_$member.csv"); //shell_exec("mv tmp_files/tmp_payment_history_and_attendance_costs_sbook_$member.csv tmp_files/payment_history_and_attendance_costs_sbook_$member.csv"); }// if (file_exists($file_payment_and_attendance_costs2 )) } //if ($member == NULL) ?>