125 lines
5.1 KiB
PHP
Executable File
125 lines
5.1 KiB
PHP
Executable File
<?php
|
|
$date_from=$_GET['date_from'];
|
|
$date_to=$_GET['date_to'];
|
|
if(!$date_from)$date_from=date("Y-m-d");
|
|
if(!$date_to)$date_to=date("Y-m-d");
|
|
set_include_path('include/PHPExcel/');
|
|
|
|
include 'PHPExcel.php';
|
|
include 'PHPExcel/Writer/Excel2007.php';
|
|
include 'PHPExcel/IOFactory.php';
|
|
|
|
$objPHPExcel = new PHPExcel();
|
|
$objPHPExcel->getProperties()->setCreator("E5 CRM");
|
|
$objPHPExcel->getProperties()->setLastModifiedBy("E5 CRM");
|
|
$objPHPExcel->getProperties()->setTitle("Office 2007 SALED PRODUCTS");
|
|
$objPHPExcel->getProperties()->setSubject("Office 2007 SALED PRODUCTS");
|
|
$objPHPExcel->getProperties()->setDescription("SALED PRODUCTS");
|
|
|
|
$alf="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
|
|
|
|
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
|
|
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
|
|
|
|
|
|
$objPHPExcel->getActiveSheet()->setTitle('Simple');
|
|
$objPHPExcel->setActiveSheetIndex(0);
|
|
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("A1","Lp.");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("B1","Index");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("C1","Name");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("D1","Invoice");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("E1","Quantity");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("F1","Price");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("G1","Total netto");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("H1","Total Vat");
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("I1","Total brutto");
|
|
$i=2;
|
|
$z="select ecmreceiptitems.*,ecmreceipts.document_no as dno,ecmreceipts.id as sid,ecmreceipts.type as type from ecminvoiceoutitems inner join ecminvoiceouts on ecminvoiceoutitems.ecminvoiceout_id=ecminvoiceouts.id where ecminvoiceouts.deleted='0' and ecminvoiceouts.register_date>='".$date_from."' and ecminvoiceouts.register_date<='".$date_to."' and ecminvoiceoutitems.deleted='0'";
|
|
echo $z;
|
|
$w=$GLOBALS[db]->query($z);
|
|
echo mysql_error();
|
|
while($r=$GLOBALS[db]->fetchByAssoc($w)){
|
|
|
|
$total_netto=$r['price']*$r['quantity'];
|
|
$total_vat=$r['price']*$r['quantity']*($r['ecmvat_value']/100);
|
|
$total_brutto=$r['price']*$r['quantity']*(1+$r['ecmvat_value']/100);
|
|
|
|
if($r['type']=="correct"){
|
|
$rr=$GLOBALS[db]->fetchByAssoc($GLOBALS[db]->query("select price,quantity,ecmvat_value from ecmreceiptitems where id='".$r['ecmreceiptitem_id']."'"));
|
|
$total_netto-=$rr['price']*$rr['quantity'];
|
|
$total_vat-=$rr['price']*$rr['quantity']*($rr['ecmvat_value']/100);
|
|
$total_brutto-=$rr['price']*$rr['quantity']*(1+$rr['ecmvat_value']/100);
|
|
$r['price']=$rr['price'];
|
|
$r['quantity']-=$rr['quantity'];
|
|
}
|
|
if($r['quantity']==0)continue;
|
|
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("A".$i,($i-1));
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("B".$i,$r['code']);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("C".$i,$r['name']);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("D".$i,$r['dno']);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("E".$i,$r['quantity']);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("F".$i,$r['price']);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("G".$i,($total_netto));
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("H".$i,($total_vat));
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("I".$i,($total_brutto));
|
|
|
|
$sum_total_netto+=$total_netto;
|
|
$sum_total_vat+=$total_vat;
|
|
$sum_total_brutto+=$total_brutto;
|
|
$sum_qty+=$r['quantity'];
|
|
$i++;
|
|
}
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("E".$i,$sum_qty);
|
|
@$objPHPExcel->getActiveSheet()->SetCellValue("F".$i,($sum_total/$sum_qty));
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("G".$i,$sum_total_netto);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("H".$i,$sum_total_vat);
|
|
$objPHPExcel->getActiveSheet()->SetCellValue("I".$i,$sum_total_brutto);
|
|
|
|
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
|
|
array(
|
|
'fill' => array(
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
|
'color' => array('argb' => 'F0F0F0')
|
|
),
|
|
'borders' => array(
|
|
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
|
|
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
|
|
)
|
|
),
|
|
"A1:I1"
|
|
);
|
|
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
|
|
array(
|
|
'fill' => array(
|
|
'type' => PHPExcel_Style_Fill::FILL_SOLID,
|
|
'color' => array('argb' => 'F0F0F0')
|
|
),
|
|
'borders' => array(
|
|
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
|
|
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
|
|
)
|
|
),
|
|
"A".($i).":I".($i)
|
|
);
|
|
|
|
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
|
|
chmod("cache/upload",0777);
|
|
$microtime=str_replace(".","",str_replace(" ","",microtime()));
|
|
$name="cache/upload/SaledProducts".$microtime.".xlsx";
|
|
$objWriter->save($name);
|
|
chmod($name,0777);
|
|
|
|
header("Location: ".$name);
|
|
?>
|