assign("MOD", $mod_strings);
//group accounts
if ($_REQUEST['group_accounts'] == "on") {
$group_accounts = true;
$ss->assign("GROUP_ACCOUNTS", "1");
}
//get Stocks
$stocks = "";
if ($_REQUEST['stocks']) $stocks = $_REQUEST['stocks'];
$s_res = $db->query("SELECT id, name FROM ecmstocks WHERE deleted='0' ORDER BY name");
$stocks_tpl = '';
$s_stocks = substr($s_stocks,0,-2);
if (is_array($stocks) && sizeof($stocks) == $s_res->num_rows)
$s_stocks = "Wszystkie";
$ss->assign("STOCKS", $stocks_tpl);
//document types
$types_def = array (
'FV' => 'Faktura',
'FVKOR' => 'Faktura korygująca',
//'REC' => 'Paragon',
//'RECKOR' => 'Korekta paragonu',
);
$types = "";
if ($_REQUEST['types']) $types = $_REQUEST['types'];
$s_types = "";
$types_tpl = '';
$s_types = substr($s_types, 0, -2);
if (is_array($types) && sizeof($types) == sizeof($types_def))
$s_types = "Wszystkie";
$ss->assign("TYPES", $types_tpl);
//document kinds
$kinds_def = array (
'K' => 'Kraj',
'U' => 'Unia',
'E' => 'Eksport',
);
$kinds = "";
if ($_REQUEST['kinds']) $kinds = $_REQUEST['kinds'];
$s_kinds = "";
$kinds_tpl = '';
$s_kinds = substr($s_kinds,0,-2);
if (is_array($kinds) && sizeof($kinds) == sizeof($kinds_def))
$s_kinds = "Wszystkie";
$ss->assign("KINDS", $kinds_tpl);
//document categories
$categories = "";
if ($_REQUEST['categories']) $categories = $_REQUEST['categories'];
$s_categories = "";
global $app_list_strings;
$z=$GLOBALS['db']->query('select id,name from ecminvoicecategories');
$categories_def=array();
while($cat=$GLOBALS['db']->fetchByAssoc($z)){
$categories_def[$cat['id']]=$cat['name'];
}
//$categories_def = $app_list_strings['ecminvoiceouts_category_dom'];
if ($_REQUEST['categories']) $categories = $_REQUEST['categories'];
$categories_tpl = '';
$s_categories = substr($s_categories,0,-2);
if (is_array($categories) && sizeof($categories) == sizeof($categories_def)) {
$s_categories = "Wszystkie";
}
$ss->assign("CATEGORIES", $categories_tpl);
//dates
if ($_REQUEST['date_from'])
$date_from = $_REQUEST['date_from'];
else {
$date_from = new DateTime("first day of last month");
$date_from = $date_from->format('d.m.Y');
}
$date_from_db = implode('-', array_reverse(explode('.', $date_from)));
$ss->assign("DATE_FROM", $date_from);
if ($_REQUEST['date_to'])
$date_to = $_REQUEST['date_to'];
else {
$date_to = new DateTime("last day of last month");
$date_to = $date_to->format('d.m.Y');
}
$date_to_db = implode('-', array_reverse(explode('.', $date_to)));
$ss->assign("DATE_TO", $date_to);
if (!$process == true) {
$ss->display ( 'modules/EcmReports/tpls/ReportSalesVatSales.tpl' );
return;
}
//Do your job!
$main_query = "SELECT id, total_netto, total_brutto, total_vat, vats_summary, purchase_price FROM ecminvoiceouts WHERE deleted='0' AND canceled='0'";
$main_query.= "AND register_date >= '$date_from_db' AND register_date <= '$date_to_db' ";
if (in_array("FV", $types) && !in_array("FVKOR", $types))
$main_query .= "AND type='normal' ";
if (in_array("FVKOR", $types) && ! in_array("FV", $types))
$main_query .= "AND type='correct' ";
if (is_array($stocks))
$main_query .= " AND stock_id IN ('".implode("','", $stocks)."') ";
if($_REQUEST['categories'][0]=='all'){
$s_categories = "Wszystkie";
} else {
if (is_array($categories))
$main_query .= " AND (category IN ('".implode("','", $categories)."') OR category IS NULL)";
}
if (is_array($kinds))
$main_query .= " AND pdf_type IN ('".implode("','", $kinds)."') ";
$main_query .= " ORDER BY register_date, date_entered";
//echo $main_query; die();
//echo $main_query;
$inv_res = $db->query($main_query);
//get vats names
$main_query = str_replace(", total_netto, total_brutto, total_vat, vats_summary, purchase_price", "", $main_query); //leave only id field
$vats_query = "SELECT distinct ii.ecmvat_name, v.value FROM ecminvoiceoutitems AS ii
INNER JOIN ecmvats as v
ON v.id = ii.ecmvat_id
WHERE ecminvoiceout_id IN ($main_query) order by ecmvat_value";
$v_res = $db->query($vats_query);
$vats = array();
$vatsCount = 0;
while ($v_row = $db->fetchByAssoc($v_res)) {
//check if value is 0
if ($v_row['value'] == 0) {
$vats[strtoupper($v_row['ecmvat_name'])] = 1; //var is 0 - we didn't have to draw column
$vatsCount++;
} else {
$vats[strtoupper($v_row['ecmvat_name'])] = 2;
$vatsCount+=2;
}
}
$data = array();
$c = 1; //counter
while ($row = $db->fetchByAssoc($inv_res)) {
$tmp = array();
$tmp['lp'] = $c; $c++;
$inv = new EcmInvoiceOut();
$inv->retrieve($row['id']);
$tmp['document_no'] = $inv->document_no;
if($inv->type=='correct'){
$tmp['document_no'] = 'KF '.$inv->document_no;
} else {
$tmp['document_no'] = 'FV '.$inv->document_no;
}
$tmp['register_date'] = $inv->register_date;
//$pi = $inv->parent_nip.", ".$inv->parent_name.", ".$inv->parent_address_street.", ".$inv->parent_address_postalcode.", ".$inv->parent_address_city;
$pi = $inv->parent_nip;
$a = new Account();
$a->retrieve($inv->parent_id);
$pi.= ', '.$a->ks_account;
if (strlen($pi.", ".$inv->parent_name) <= 80){
$pi.=", ".$inv->parent_name;
} else {
$pos=strpos($inv->parent_name," ");
if($pos !== false){
$inv->parent_name=substr($inv->parent_name, 0,$pos).('...');
}
$pi.=", ".$inv->parent_name;
}
if (strlen($pi.", ".$inv->parent_address_street) <= 80){
$pi.=", ".$inv->parent_address_street;
}
if (strlen($pi.", ".$inv->parent_address_postalcode) <= 80)
$pi.=", ".$inv->parent_address_postalcode;
if (strlen($pi.", ".$inv->parent_address_city) <= 80)
$pi.=", ".$inv->parent_address_city;
$tmp['parent_info'] = $pi;
$a = new Account();
$a->retrieve($inv->parent_id);
$tmp['parent_index'] = $a->index_dbf;
$tmp['parent_id'] = $a->id;
unset($a);
if ($inv->currency_id == 'PLN') $currency = 1; else $currency = $inv->currency_value_nbp;
$tmp['netto'] = round($row['total_netto'] * $currency,2);
$tmp['brutto'] = round($row['total_brutto'] * $currency,2);
$tmp['vat'] = round($row['total_vat'] * $currency,2);
$tmp['type']=$inv->type;
if($inv->type=='correct'){
$tmp['purchase'] = -1*$row['purchase_price'];
} else {
$tmp['purchase'] = $row['purchase_price'];
}
//prepare vats
$vat_tmp = array();
$v_tmp = explode(",", $row['vats_summary']);
$v_tmp = array_slice($v_tmp, 0, -1); //remove last empty element
foreach ($v_tmp as $val) {
$vv_tmp = explode(":", $val);
$vvv_tmp = array();
$vvv_tmp[] = $vv_tmp[1];
$vvv_tmp[] = $vv_tmp[2];
$vat_tmp[strtoupper($vv_tmp[0])] = $vvv_tmp;
unset($vvv_tmp);
unset($vv_tmp);
}
unset($v_tmp);
$inv_vat = array();
foreach ($vats as $k => $v) {
$tmp_vat = array();
if (is_array($vat_tmp[$k])) {
$tmp_vat[] = round($vat_tmp[$k][0]*$currency,2);
$tmp_vat[] = round($vat_tmp[$k][1]*$currency,2);
} else {
$tmp_vat[] = 0;
$tmp_vat[] =0;
}
$inv_vat[$k] = $tmp_vat;
unset($tmp_vat);
}
$tmp['vats'] = $inv_vat;
unset($inv_vat);
unset($inv);
$data[] = $tmp;
unset($tmp);
}
if ($group_accounts == true) {
usort($data, "cmp");
//creaate Accounts summary
$tmp_data = array();
$summary = array();
$sum = array();
$sum_vat = array();
for ($i = 0; $i < sizeof($data); $i++) {
$row = $data[$i];
$row['lp'] = $i+1;
$tmp_data[] = $row;
if (array_key_exists("total_netto",$sum))
$sum['total_netto'] += $row['netto']; else $sum['total_netto'] = $row['netto'];
if (array_key_exists("total_brutto",$sum))
$sum['total_brutto'] += $row['brutto']; else $sum['total_brutto'] = $row['brutto'];
if (array_key_exists("total_vat",$sum))
$sum['total_vat'] += $row['vat']; else $sum['total_vat'] = $row['vat'];
if (array_key_exists("total_purchase",$sum))
$sum['total_purchase'] += $row['purchase']; else $sum['total_purchase'] = $row['purchase'];
foreach ($row['vats'] as $k=>$v) {
if (!array_key_exists($k,$sum_vat)) {
$sum_vat[$k][0] = 0;
$sum_vat[$k][1] = 0;
}
$sum_vat[$k][0] += $v[0];
$sum_vat[$k][1] += $v[1];
}
if ($data[$i]['parent_id'] != $data[$i+1]['parent_id'] || $i == sizeof($data)) {
//insert summary row
$sum['is_summary'] = 1;
$sum['index'] = $row['parent_index'];
$sum['parent_info'] = 'Podsumowanie dla kontrahenta';
$sum['vats'] = $sum_vat;
$tmp_data[] = $sum;
unset($sum); $sum = array();
unset($sum_vat); $sum_vat = array();
}
}
$data = $tmp_data;
}
$datas=$data;
$rop = 28; // rows on page
$nop = ceil(sizeof($data)/$rop); // number of pages
$pages_data = array_chunk($data, $rop);
$pages = array();
unset($data);
$prev_page = array();
$c = 1; //counter
foreach ($pages_data as $data) {
$tmp = array();
$tmp['site_netto'] = 0; $tmp['site_brutto'] = 0; $tmp['site_vat'] = 0; $tmp['site_purchase'] = 0;
$tmp['moved_netto'] = 0; $tmp['moved_brutto'] = 0; $tmp['moved_vat'] = 0; $tmp['moved_purchase'] = 0;
//prepare vats array
$site_vats = array();
$moved_vats = array();
$total_vats = array();
foreach ($vats as $k => $v) {
$site_vats[$k][0] = 0;
$site_vats[$k][1] = 0;
$moved_vats[$k][0] = 0;
$moved_vats[$k][1] = 0;
$total_vats[$k][0] = 0;
$total_vats[$k][1] = 0;
}
foreach ($data as $i) {
$tmp['site_netto'] += $i['netto'];
$tmp['site_brutto'] += $i['brutto'];
$tmp['site_vat'] += $i['vat'];
$tmp['site_purchase'] += $i['purchase'];
foreach ($i['vats'] as $k => $v) {
$site_vats[$k][0] += $v[0];
$site_vats[$k][1] += $v[1];
}
}
if ($c!=1) { //not first page
$tmp['moved_netto'] = $prev_page['total_netto'];
$tmp['moved_brutto'] = $prev_page['total_brutto'];
$tmp['moved_vat'] = $prev_page['total_vat'];
$tmp['moved_purchase'] = $prev_page['total_purchase'];
foreach ($vats as $k=>$v) {
$moved_vats[$k][0] += $prev_page['total_vats'][$k][0];
$moved_vats[$k][1] += $prev_page['total_vats'][$k][1];
}
}
$tmp['total_netto'] = $tmp['site_netto'] + $tmp['moved_netto'];
$tmp['total_brutto'] = $tmp['site_brutto'] + $tmp['moved_brutto'];
$tmp['total_vat'] = $tmp['site_vat'] + $tmp['moved_vat'];
$tmp['total_purchase'] = $tmp['site_purchase'] + $tmp['moved_purchase'];
foreach ($vats as $k=>$v) {
$total_vats[$k][0] += $site_vats[$k][0] + $moved_vats[$k][0];
$total_vats[$k][1] += $site_vats[$k][1] + $moved_vats[$k][1];
}
//insert page data
$tmp['rows'] = $data;
$tmp['page_no'] = $c;
$tmp['site_vats'] = $site_vats;
$tmp['moved_vats'] = $moved_vats;
$tmp['total_vats'] = $total_vats;
$pages[] = $tmp;
$prev_page = $tmp;
$c++;
}
//create document and show it
$ss_pdf = new Sugar_Smarty();
$ss_pdf->assign("PAGES", $pages);
$ss_pdf->assign("VATS", $vats);
$ss_pdf->assign("vatsCount", $vatsCount);
//header stuff
$cur_date = new DateTime("now");
$cur_date = $cur_date->format('d.m.Y H:m:s');
$ss_pdf->assign("CUR_DATE", $cur_date);
$ss_pdf->assign("DATE_FROM", $date_from);
$ss_pdf->assign("DATE_TO", $date_to);
$ss_pdf->assign("S_STOCK", $s_stocks);
$ss_pdf->assign("S_KIND", $s_kinds);
$ss_pdf->assign("S_CATEGORY", $s_categories);
$ss_pdf->assign("S_TYPE", $s_types);
if($_REQUEST['to_xls']==1){
$header=array();
$header[]='nr dok.';
$header[]='data wystawienia';
$header[]='odbiorca - nip,nazwa,adres';
$header[]='sprzedaż brutto';
$header[]='sprzedaż netto';
foreach ($vats as $val){
$header[]='netto '.$val;
$header[]='vat '.$val;
}
$header[]='vat razem';
$header[]='koszt';
$filename='modules/Home/Files/raport_sprzedazy_vat_'.date('d_m-Y').'.csv';
$fp=fopen($filename,'w');
foreach ($header as $k=>$v){
$header[$k]=iconv('UTF-8','windows-1250',$header[$k]);
}
fwrite($fp, implode(";",$header).PHP_EOL);
foreach ($pages[0]['rows'] as $key=>$val){
$line=array();
$line[]='"=""'.preg_replace("/[^A-ZłŁąĄęĘżŻ,:.źŹ%ćĆńŃśŚóÓa-z0-9\-\/]/",' ',preg_replace("/\r\n|\r|\n/",' ',html_entity_decode($val['document_no']))).'"""';
$line[]=$val['register_date'];
$line[]=preg_replace("/[^A-ZłŁąĄęĘżŻ,:.źŹ%ćĆńŃśŚóÓa-z0-9\-\/]/",' ',preg_replace("/\r\n|\r|\n/",' ',html_entity_decode($val['parent_info'])));
$line[]=str_replace(".",",",$val['brutto']);
$line[]=str_replace(".",",",$val['netto']);
foreach ($val['vats'] as $vi){
$line[]=str_replace(".",",",$vi[0]);
$line[]=str_replace(".",",",$vi[1]);
}
$line[]=str_replace(".",",",$val['vat']);
$line[]=str_replace(".",",",$val['purchase']);
foreach ($line as $k=>$v){
$line[$k]=iconv('UTF-8','windows-1250',$line[$k]);;
}
fwrite($fp, implode(";",$line).PHP_EOL);
}
header("Location: ". $filename);
}else {
include_once ("modules/EcmSysInfos/EcmSysInfo.php");
$EcmSysInfo = new EcmSysInfo();
$ss_pdf->assign("COMPANY_NAME", $EcmSysInfo->getName());
$content = $ss_pdf->fetch ( 'modules/EcmReports/tpls/ReportSalesVatSalesPDF.tpl' );
include_once ("include/MPDF57/mpdf.php");
$p = new mPDF ( '', 'A4', null, 'helvetica', 5, 5, 33, 5, 5, 5 );
$p->writeHTML ($content) ;
$p->setTitle ( "Rejestr sprzedaży VAT" );
$p->output("rej.pdf", "I");
}
//helper
function cmp($a, $b) {
if ($a['parent_info'] == $b['parent_info']) {
return ( $a['register_date'] > $b['register_date'] ) ? 1 : -1;
}
return ( $a['parent_info'] > $b['parent_info'] ) ? 1 : -1;
}