mz/crm.e5.pl
1
0
Fork 0
Code Issues 12 Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
crm.e5.pl/modules/EcmStockOperations/maksyma.php

1275 lines
61 KiB
PHP
Raw Permalink Normal View History

init
2024-04-27 09:23:34 +02:00
<form action="index.php">
<input type="hidden" name="module" value="EcmStockOperations" /> <input
type="hidden" name="action" value="maksyma" /> Miesiąc:&nbsp;<input
type="text" name="date"
value="<?php
if ($_REQUEST ['date'] != '')
echo $_REQUEST ['date'];
else
echo date ( 'Y-m', strtotime ( "last month" ) );
?>" /> <br> <input type="submit" value="Ok" />
</form>
<br>
<?php
/*
* Magazyny wg księgowości:
* Magazyn towary handlowe: group_ks = 1, stock_id in: c61edc9c-06b0-39d9-94b1-4acaefc892b9', 'c7afd71a-4c3a-bde4-138d-4acaee1644e4','46766156-c896-ead2-0f62-4bc4706696dd
* Magazyn wyroby gotowe: group_ks = 2, stock_id in: c61edc9c-06b0-39d9-94b1-4acaefc892b9', 'c7afd71a-4c3a-bde4-138d-4acaee1644e4','46766156-c896-ead2-0f62-4bc4706696dd
* Magazyn surowce: group_ks = 3, stock_id in: c61edc9c-06b0-39d9-94b1-4acaefc892b9', 'c7afd71a-4c3a-bde4-138d-4acaee1644e4','46766156-c896-ead2-0f62-4bc4706696dd
* Magazyn: Wydane do produkcji-szwalnia, stock_id = 46766ecd-65f1-18f1-1bc7-53569ac9d2d0
* Magazyn: Wydane do produkcji-środki czystości, stock_id = 9cda0d46-f4f4-7487-1144-53569aeb7c2e
* Magazyn: Wydane do produci-etui i inne, stock_id = 49d554d8-f6ee-b791-f863-53b6ac4bda91
*
*/
set_include_path ( 'include/PHPExcel/' );
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/IOFactory.php';
if (! $_REQUEST ['date'] || $_REQUEST ['date'] == '')
return;
$tmp = explode ( "-", $_REQUEST ['date'] );
$mounth = $tmp [1];
$year = $tmp [0];
// /sumary array
$summary = array ();
//createStockInfo ( $year . "-" . $mounth, "", "", "", true, $summary );
//createStockInfo ( $year . "-" . (intval ( $mounth ) - 1), "", "", "", true, $summary );
// prepare dir
mkdir ( 'modules/EcmStockOperations/maksyma/' . $_REQUEST ['date'], 0755 );
// check all stocks
createStockInfo($_REQUEST['date'], " AND group_ks='1'","AND stock_id NOT IN ('49d554d8-f6ee-b791-f863-53b6ac4bda91', '9cda0d46-f4f4-7487-1144-53569aeb7c2e','46766ecd-65f1-18f1-1bc7-53569ac9d2d0')", 'Towary handlowe');
createStockInfo($_REQUEST['date'], " AND group_ks='2'","AND stock_id NOT IN ('49d554d8-f6ee-b791-f863-53b6ac4bda91', '9cda0d46-f4f4-7487-1144-53569aeb7c2e','46766ecd-65f1-18f1-1bc7-53569ac9d2d0')", 'Wyroby gotowe');
createStockInfo($_REQUEST['date'], " AND group_ks='3'","AND stock_id NOT IN ('49d554d8-f6ee-b791-f863-53b6ac4bda91', '9cda0d46-f4f4-7487-1144-53569aeb7c2e','46766ecd-65f1-18f1-1bc7-53569ac9d2d0')", 'Surowce');
createStockInfo($_REQUEST['date'], '',"AND stock_id = '46766ecd-65f1-18f1-1bc7-53569ac9d2d0' ", 'Materiały wydane do produkcji-Szwalnia');
createStockInfo($_REQUEST['date'], '',"AND stock_id = '9cda0d46-f4f4-7487-1144-53569aeb7c2e' ", 'Materiały wydane do produkcji-Środki Czystości');
createStockInfo($_REQUEST['date'], '',"AND stock_id = '49d554d8-f6ee-b791-f863-53b6ac4bda91' ", 'Materiały wydane do produkcji-Etui itd');
createKSinfo($_REQUEST['date']);
createPZinfo($_REQUEST['date']);
createKSinfo($_REQUEST['date']);
createPWinfo($_REQUEST['date']);
createRWinfo($_REQUEST['date']);
createMMinfo($_REQUEST['date']);
createSummary ( $summary, $_REQUEST ['date'] );
addzip ( "modules/EcmStockOperations/maksyma/" . $_REQUEST ['date'], "modules/EcmStockOperations/maksyma/" . $_REQUEST ['date'] . ".zip" );
global $current_user;
echo '<a href="modules/EcmStockOperations/maksyma/' . $_REQUEST ['date'] . '.zip">Paczka</a>';
// helper functions
function createMMinfo($date) {
$db = $GLOBALS ['db'];
$res = $db->query ( "
select concat(mm.stock_out_id,'|',mm.stock_in_id) as stocks, sum(mmi.total) as total, mm.document_no, mm.id, p.group_ks, mm.register_date
from ecmstockdocmoves as mm
inner join ecmstockdocmoveitems as mmi
on mmi.ecmstockdocmove_id = mm.id
inner join ecmproducts as p
on mmi.ecmproduct_id = p.id
where mm.deleted='0' and mmi.deleted='0' and mm.register_date like '$date%'
and stock_in_id in ('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','368479db-22c5-0220-3a14-4bc426b1c709')
group by mm.id, p.group_ks
order by mm.date_entered;
" );
$result = array ();
while ( $row = $db->fetchByAssoc ( $res ) ) {
$result [$row ['stocks']] [$row ['group_ks']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'total' => $row ['total']
);
}
$res = $db->query ( "
select concat(mm.stock_out_id,'|',mm.stock_in_id) as stocks, sum(mmi.total) as total, mm.document_no, mm.id, p.group_ks, mm.register_date
from ecmstockdocmoves as mm
inner join ecmstockdocmoveitems as mmi
on mmi.ecmstockdocmove_id = mm.id
inner join ecmproducts as p
on mmi.ecmproduct_id = p.id
where mm.deleted='0' and mmi.deleted='0' and mm.register_date like '$date%'
and stock_out_id in ('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','368479db-22c5-0220-3a14-4bc426b1c709')
group by mm.id, p.group_ks
order by mm.date_entered;
" );
// create data
while ( $row = $db->fetchByAssoc ( $res ) ) {
$result [$row ['stocks']] [$row ['group_ks']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'total' => $row ['total']
);
}
//echo '<pre>';
// var_dump($result);
//echo '</pre>';
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Zestawienie MM" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A6", "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B6", "Numer dokumentu" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C6", "Data rejestracji" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D6", "Wartość" );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A6:D6" );
$j = 7;
global $app_list_strings;
foreach ( $result as $stock => $types ) {
$bigsum = 0;
$tmp = explode ( '|', $stock );
$s = new EcmStock ();
$s->retrieve ( $tmp [0] );
$stock_name1 = $s->name;
unset ( $s );
$s = new EcmStock ();
$s->retrieve ( $tmp [1] );
$stock_name2 = $s->name;
unset ( $s );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $stock_name1 . ' => ' . $stock_name2 );
// $objPHPExcel->getActiveSheet()->getStyle("B".$j)->getFont()->setBold(true);
$j ++;
foreach ( $types as $type => $doc ) {
$smallsum = 0;
$check = array (
'46766ecd-65f1-18f1-1bc7-53569ac9d2d0',
'9cda0d46-f4f4-7487-1144-53569aeb7c2e',
'368479db-22c5-0220-3a14-4bc426b1c709'
);
if (in_array ( $tmp [1], $check ) == true && in_array ( $tmp [0], $check ) == true) {
$show = '';
} else {
if (in_array ( $tmp [0], $check ) == true && in_array ( $tmp [1], $check ) == false) {
$show = $stock_name1 . " => " . $app_list_strings ['ecmproducts_group_ks_dom'] [$type];
}
if (in_array ( $tmp [0], $check ) == false && in_array ( $tmp [1], $check ) == true) {
$show = $app_list_strings ['ecmproducts_group_ks_dom'] [$type] . ' => ' . $stock_name2;
}
}
$i = 1;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $show );
$objPHPExcel->getActiveSheet ()->getStyle ( "A" . $j )->getFont ()->setBold ( true );
$j ++;
foreach ( $doc as $v ) {
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['document_no'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['register_date'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v ['total'] );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$bigsum += $v ['total'];
$smallsum += $v ['total'];
$i ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $smallsum );
$objPHPExcel->getActiveSheet ()->getStyle ( "D" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, 'Suma dla magazynów' );
$objPHPExcel->getActiveSheet ()->getStyle ( "C" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $bigsum );
$objPHPExcel->getActiveSheet ()->getStyle ( "D" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
$j ++;
}
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/mm.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createPZinfo($date) {
$db = $GLOBALS ['db'];
$res = $db->query ( "
select pz.id, pz.document_no, pz.register_date, p.group_ks, sum(pzi.total) as total, s.name as stock
from ecmstockdocins as pz
inner join ecmstockdocinitems as pzi
on pzi.ecmstockdocin_id = pz.id
inner join ecmproducts as p
on pzi.ecmproduct_id = p.id
inner join ecmstocks as s
on s.id=pz.stock_id
where
pz.deleted='0' and pzi.deleted='0' and pz.register_date like '$date%'
group by pz.id,p.group_ks
order by pz.date_entered;
" );
global $app_list_strings;
$result = array ();
while ( $row = $db->fetchByAssoc ( $res ) ) {
$result [$row ['group_ks']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'stock' => $row ['stock'],
'total' => $row ['total']
);
}
// create data
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Zestawienie PZ" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$j = 6;
foreach ( $result as $type => $doc ) {
$sum = 0;
$i = 1;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $app_list_strings ['ecmproducts_group_ks_dom'] [$type] );
$objPHPExcel->getActiveSheet ()->getStyle ( "A" . $j )->getFont ()->setBold ( true );
$j ++;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, "Numer dokumentu" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, "Data rejestracji" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, "Cecha" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, "Wartość" );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A" . $j . ":E" . $j );
$j ++;
foreach ( $doc as $v ) {
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['document_no'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['register_date'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v ['stock'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $v ['total'] );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$sum += $v ['total'];
$i ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $sum );
$objPHPExcel->getActiveSheet ()->getStyle ( "E" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
}
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/pz.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createRWinfo($date) {
$db = $GLOBALS ['db'];
$total_check = 0;
$res = $db->query ( "
select rw.id as id,rw.document_no, rw.register_date, rw.stock_id, sum(rwi.total) as total, p.group_ks
from ecmstockdocinsideouts as rw
inner join ecmstockdocinsideoutitems as rwi
on rw.id = rwi.ecmstockdocinsideout_id
inner join ecmproducts as p
on p.id = rwi.ecmproduct_id
where
rwi.deleted='0' and rw.deleted='0'
AND rw.stock_id NOT IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and rw.register_date like '$date%' and p.group_ks!=''
group by rw.id, p.group_ks
order by rw.date_entered;
" );
global $app_list_strings;
$result = array ();
while ( $row = $db->fetchByAssoc ( $res ) ) {
$s2 = new EcmStock ();
$s2->retrieve ( $row ['stock_id'] );
$row ['stock'] = $s2->name;
if ($row ['group_ks'] == '')
$row ['group_ks'] = 'inne';
$result [$row ['group_ks']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'stock' => $row ['stock'],
'total' => $row ['total'],
'id' => $row ['id']
);
$total_check += $row ['total'];
}
$res = $db->query ( "
select rw.id as id,rw.document_no, rw.register_date, rw.stock_id, sum(rwi.total) as total
from ecmstockdocinsideouts as rw
inner join ecmstockdocinsideoutitems as rwi
on rw.id = rwi.ecmstockdocinsideout_id
inner join ecmproducts as p
on p.id = rwi.ecmproduct_id
where
rwi.deleted='0' and rw.deleted='0' and p.group_ks!=''
AND rw.stock_id IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and rw.register_date like '$date%'
group by rw.id
order by rw.date_entered;
" );
// create data
while ( $row = $db->fetchByAssoc ( $res ) ) {
$s3 = new EcmStock ();
$s3->retrieve ( $row ['stock_id'] );
$row ['stock'] = $s3->name;
$result [$row ['stock_id']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'stock' => $row ['stock'],
'total' => $row ['total'],
'id' => $row ['id']
);
$total_check += $row ['total'];
}
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'E' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'F' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Zestawienie RW" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$j = 6;
foreach ( $result as $type => $doc ) {
$sum = 0;
$i = 1;
if (strlen ( $type ) > 1) {
$s = new EcmStock ();
$s->retrieve ( $type );
$mag = $s->name;
$cecha = false;
} else {
$mag = $app_list_strings ['ecmproducts_group_ks_dom'] [$type];
if ($type == 'inne')
$mag = 'Inne';
$cecha = true;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $mag );
$objPHPExcel->getActiveSheet ()->getStyle ( "A" . $j )->getFont ()->setBold ( true );
$j ++;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, "Numer dokumentu" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, "Data rejestracji" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, "Cecha" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, "Wartość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . $j, "Powiązany dokument PW" );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A" . $j . ":F" . $j );
$j ++;
foreach ( $doc as $v ) {
// get PW
$pw = $db->fetchByAssoc ( $db->query ( "SELECT pw.document_no FROM ecminsideorders AS io INNER JOIN ecmstockdocinsideins AS pw ON pw.id = io.pw_id WHERE io.rw_id = '" . $v ['id'] . "'" ) );
$pw = $pw ['document_no'];
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['document_no'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['register_date'] );
if (! $cecha)
$v ['stock'] = '';
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v ['stock'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $v ['total'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . $j, $pw );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$sum += $v ['total'];
$i ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $sum );
$objPHPExcel->getActiveSheet ()->getStyle ( "E" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
}
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/rw.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createPWinfo($date) {
$db = $GLOBALS ['db'];
$res = $db->query ( "
select pw.id as id,pw.document_no, pw.register_date, pw.stock_id, sum(pwi.total) as total, p.group_ks
from ecmstockdocinsideins as pw
inner join ecmstockdocinsideinitems as pwi
on pw.id = pwi.ecmstockdocinsidein_id
inner join ecmproducts as p
on p.id = pwi.ecmproduct_id
where
pwi.deleted='0' and pw.deleted='0'
AND pw.stock_id NOT IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and pw.register_date like '$date%'
group by pw.id, p.group_ks
order by pw.date_entered;
" );
global $app_list_strings;
$result = array ();
while ( $row = $db->fetchByAssoc ( $res ) ) {
$s2 = new EcmStock ();
$s2->retrieve ( $row ['stock_id'] );
$row ['stock'] = $s2->name;
if ($row ['group_ks'] == '' || $row['group_ks'] === null) {
$row ['group_ks'] = 'inny';
}
$result [$row ['group_ks']] [$row['id']] = array (
'document_no' => $row ['document_no'],
'register_date' => $row ['register_date'],
'stock' => $row ['stock'],
'total' => $row ['total'],
'id' => $row ['id']
);
}
$res = $db->query ( "
select pw.id as id,pw.document_no, pw.register_date, pw.stock_id, sum(pwi.total) as total
from ecmstockdocinsideins as pw
inner join ecmstockdocinsideinitems as pwi
on pw.id = pwi.ecmstockdocinsidein_id
inner join ecmproducts as p
on p.id = pwi.ecmproduct_id
where
pwi.deleted='0' and pw.deleted='0'
AND pw.stock_id IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and pw.register_date like '$date%'
group by pw.id
order by pw.date_entered;
" );
// create data
while ( $row = $db->fetchByAssoc ( $res ) ) {
$s3 = new EcmStock ();
$s3->retrieve ( $row ['stock_id'] );
$row ['stock'] = $s3->name;
$result [$row ['stock_id']] [$row['id']] = array (
'document_no' => $row ['document_no'].'ttt',
'register_date' => $row ['register_date'],
'stock' => $row ['stock'],
'total' => $row ['total'],
'id' => $row ['id']
);
}
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'E' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'F' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Zestawienie PW" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$j = 6;
foreach ( $result as $type => $doc ) {
$sum = 0;
$i = 1;
if (strlen ( $type ) > 1 && $type!='inny') {
$s = new EcmStock ();
$s->retrieve ( $type );
$mag = $s->name;
$cecha = false;
} else {
$mag = $app_list_strings ['ecmproducts_group_ks_dom'] [$type];
if ($type == 'inny')
$mag = 'Inny';
$cecha = true;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $mag );
$objPHPExcel->getActiveSheet ()->getStyle ( "A" . $j )->getFont ()->setBold ( true );
$j ++;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, "Numer dokumentu" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, "Data rejestracji" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, "Cecha" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, "Wartość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . $j, "Powiązany dokument RW" );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F' . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A" . $j . ":F" . $j );
$j ++;
foreach ( $doc as $v ) {
// get RW
$rw = $db->fetchByAssoc ( $db->query ( "SELECT rw.document_no FROM ecminsideorders AS io INNER JOIN ecmstockdocinsideouts AS rw ON rw.id = io.rw_id WHERE io.pw_id = '" . $v ['id'] . "'" ) );
$rw = $rw ['document_no'];
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['document_no'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['register_date'] );
if (! $cecha)
$v ['stock'] = '';
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v ['stock'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $v ['total'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . $j, $rw );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$sum += $v ['total'];
$i ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $sum );
$objPHPExcel->getActiveSheet ()->getStyle ( "E" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
}
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/pw.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createStockInfo($date, $product_type, $stock_id, $name, $summary = false, &$s = null) {
$dir = $date;
$date .= "-15";
$date = date ( "Y-m-t", strtotime ( $date ) ); // last day
$date .= " 23:59:59";
// echo $date . '<br>';
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 80 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'E' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'F' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'G' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", $name );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Data: " . $date );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C2", "Inwentaryzacja" );
$objPHPExcel->getActiveSheet ()->getStyle ( "C2" )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C2' )->getAlignment ()->setHorizontal ( PHPExcel_Style_Alignment::HORIZONTAL_CENTER );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A6", "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B6", "Kod" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C6", "Nazwa" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D6", "J.m." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E6", "Ilość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F6", "Cena" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "G6", "Wartość" );
// if ($product_type == null)
// $objPHPExcel->getActiveSheet()->SetCellValue("G3","Grupa towarowa");
$db = $GLOBALS ['db'];
$products = $db->query ( "SELECT id, code, name, group_ks, unit_id FROM ecmproducts WHERE deleted='0' $product_type ORDER BY code ASC" );
$sum_qty = 0;
$sum_price = 0;
$i = 1;
global $app_list_strings;
while ( $r = $db->fetchByAssoc ( $products ) ) {
$value = 0;
$qty = 0;
$price = 0;
$arr = array ();
$pp1 = 0;
$q1 = 0;
$ww = $db->query ( "select quantity as qty,id,price from ecmstockoperations where deleted='0' and type='0' and in_id IS NULL and product_id='" . $r ['id'] . "' and deleted='0' and date_entered < '$date' $stock_id" );
while ( $rrr = $db->fetchByAssoc ( $ww ) ) {
$www = $db->query ( "select quantity from ecmstockoperations where in_id='" . $rrr ['id'] . "' and type='1' and in_id IS NOT NULL and product_id='" . $r ['id'] . "' and deleted='0' and date_entered < '$date' $stock_id" );
$rqty = 0;
while ( $rrp = $db->fetchByAssoc ( $www ) ) {
$rqty += $rrp ['quantity'];
}
$qty = $rrr ['qty'] - $rqty;
if ($qty > 0) {
$arr [] = array (
"qty" => $qty,
"id" => $rrr ['id'],
"price" => $rrr ['price']
);
}
}
if (count ( $arr ) > 0) {
foreach ( $arr as $v ) {
$q1 += $v ['qty'];
$pp1 += $v ['qty'] * $v ['price'];
}
}
$qty = $q1;
$value = $pp1;
@$price = $value / $qty;
$r ['qty'] = $qty;
$r ['price'] = $price;
$r ['value'] = $value;
if ($qty == 0)
continue;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . ($i + 6), $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . ($i + 6), $r ['code'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . ($i + 6), htmlspecialchars_decode ( $r ['name'] ) );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . ($i + 6), $app_list_strings ['ecmproducts_unit_dom'] [$r ['unit_id']] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . ($i + 6), round ( $r ['qty'], 4 ) );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . ($i + 6), round ( $r ['price'], 2 ) );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "G" . ($i + 6), round ( $r ['value'], 2 ) );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . ($i + 6) )->getNumberFormat ()->setFormatCode ( '# ##0.0000' );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F' . ($i + 6) )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'G' . ($i + 6) )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$sum_qty += round ( $r ['qty'], 4 );
$sum_price += round ( $r ['value'], 2 );
// summary
if ($summary) {
if (! is_array ( $s [$r ['id']] )) {
$s [$r ['id']] = array ();
$s [$r ['id']] ['code'] = $r ['code'];
$s [$r ['id']] ['name'] = htmlspecialchars_decode ( $r ['name'] );
}
$s [$r ['id']] [$dir . '_qty'] = round ( $r ['qty'], 4 );
$s [$r ['id']] [$dir . '_val'] = round ( $r ['value'], 2 );
}
$i ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . ($i + 6), round ( $sum_qty, 4 ) );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . ($i + 6), round ( $sum_price / $sum_qty, 2 ) );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "G" . ($i + 6), round ( $sum_price, 2 ) );
// beautify :)
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'G6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . ($i + 6) )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F' . ($i + 6) )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'G' . ($i + 6) )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . ($i + 6) )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'F' . ($i + 6) )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$objPHPExcel->getActiveSheet ()->getStyle ( 'G' . ($i + 6) )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
if ($product_type == null)
$tmp = 'G';
else
$tmp = 'G';
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'borders' => array (
'allborders' => array (
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
), "A6:" . $tmp . ($i + 6) );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A6:" . $tmp . "6" );
if ($summary)
return; // don't save
$name = str_replace ( ' ', '_', $name );
$name = removeAccents ( $name );
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$dir/$name.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createKSinfo($date) {
$stock_array = array (
1 => "Towar handlowy",
2 => "Wyrób gotowy",
3 => "Surowiec"
);
$db = $GLOBALS ['db'];
$res = $db->query ( "select pz.id,pz.document_no, pz.register_date, pz.stock_id, sum(pzi.total) as total, p.group_ks
from ecmstockdoccorrects as pz
inner join ecmstockdoccorrectitems as pzi
on pz.id = pzi.ecmstockdoccorrect_id
inner join ecmproducts as p
on p.id = pzi.ecmproduct_id
where
pzi.deleted='0' and pz.deleted='0'
AND pz.stock_id NOT IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and pz.register_date like '$date%'
group by pz.id
order by pz.date_entered " );
$result = array ();
global $app_list_strings;
while ( $row = $db->fetchByAssoc ( $res ) ) {
$c = new EcmStockDocCorrect ();
$c->retrieve ( $row ['id'] );
$s = new EcmStock ();
$s->retrieve ( $c->stock_id );
$result [$row ['group_ks']] [$c->type] [$row['id']] = array (
'document_no' => $c->document_no,
'register_date' => $c->register_date,
'stock' => $s->name,
'total' => $c->total
);
unset ( $s );
unset ( $c );
}
$res = $db->query ( "select pz.id,pz.document_no, pz.register_date, pz.stock_id, sum(pzi.total) as total, p.group_ks
from ecmstockdoccorrects as pz
inner join ecmstockdoccorrectitems as pzi
on pz.id = pzi.ecmstockdoccorrect_id
inner join ecmproducts as p
on p.id = pzi.ecmproduct_id
where
pzi.deleted='0' and pz.deleted='0'
AND pz.stock_id IN
('46766ecd-65f1-18f1-1bc7-53569ac9d2d0','9cda0d46-f4f4-7487-1144-53569aeb7c2e','49d554d8-f6ee-b791-f863-53b6ac4bda91')
and pz.register_date like '$date%'
group by pz.id
order by pz.date_entered;" );
global $app_list_strings;
while ( $row = $db->fetchByAssoc ( $res ) ) {
$c = new EcmStockDocCorrect ();
$c->retrieve ( $row ['id'] );
$result [$row ['stock_id']] [$c->type] [$row['id']] = array (
'document_no' => $c->document_no,
'register_date' => $c->register_date,
'total' => $c->total
);
unset ( $c );
}
// savve data
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 25 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'E' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Zestawienie KS" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A6", "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B6", "Numer dokumentu" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C6", "Data rejestracji" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D6", "Cecha" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E6", "Wartość" );
$i = 1;
$j = 7;
foreach ( $result as $stock => $types ) {
if (strlen ( $stock ) > 1) {
$s = new EcmStock ();
$s->retrieve ( $stock );
$mag = $s->name;
} else {
$mag = $stock_array [$stock];
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $mag );
$objPHPExcel->getActiveSheet ()->getStyle ( "B" . $j )->getFont ()->setBold ( true );
$j ++;
$bigsum = 0;
foreach ( $types as $type => $doc ) {
$smallsum = 0;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $app_list_strings ['ecmstockdoccorrects_type_dom'] [$type] );
$objPHPExcel->getActiveSheet ()->getStyle ( "A" . $j )->getFont ()->setBold ( true );
$j ++;
foreach ( $doc as $v ) {
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['document_no'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['register_date'] );
if (isset ( $v ['stock'] ))
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v ['stock'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $v ['total'] );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$bigsum += $v ['total'];
$smallsum += $v ['total'];
$i ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $smallsum );
$objPHPExcel->getActiveSheet ()->getStyle ( "E" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
$j ++;
}
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, 'Suma magazynu' );
$objPHPExcel->getActiveSheet ()->getStyle ( "D" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $bigsum );
$objPHPExcel->getActiveSheet ()->getStyle ( "E" . $j )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E' . $j )->getNumberFormat ()->setFormatCode ( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );
$j ++;
}
// beautify :)
$objPHPExcel->getActiveSheet ()->getStyle ( 'A1' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A2' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A3' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A4' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'A6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'B6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'C6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'D6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->getStyle ( 'E6' )->getFont ()->setBold ( true );
$objPHPExcel->getActiveSheet ()->duplicateStyleArray ( array (
'fill' => array (
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array (
'rgb' => 'C0C0C0'
)
)
), "A6:E6" );
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/ks.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
function createSummary($s, $date) {
// getStockDocumentsInformations
$db = $GLOBALS ['db'];
foreach ( $s as $id => $v ) {
// wz
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(wi.quantity) as qty, sum(wi.total) as val FROM ecmstockdocoutitems AS wi
INNER JOIN ecmstockdocouts AS w
ON w.id = wi.ecmstockdocout_id
WHERE
wi.deleted = 0 AND
w.deleted = 0 AND
w.register_date LIKE '$date%' AND
wi.ecmproduct_id = '$id'
" ) );
$s [$id] ['wz_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['wz_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// pz
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(pi.quantity) as qty, sum(pi.total) as val FROM ecmstockdocinitems AS pi
INNER JOIN ecmstockdocins AS p
ON p.id = pi.ecmstockdocin_id
WHERE
pi.deleted = 0 AND
p.deleted = 0 AND
p.register_date LIKE '$date%' AND
pi.ecmproduct_id = '$id'
" ) );
$s [$id] ['pz_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['pz_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// rw
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(ri.quantity) as qty, sum(ri.total) as val FROM ecmstockdocinsideoutitems AS ri
INNER JOIN ecmstockdocinsideouts AS r
ON r.id = ri.ecmstockdocinsideout_id
WHERE
ri.deleted = 0 AND
r.deleted = 0 AND
r.register_date LIKE '$date%' AND
ri.ecmproduct_id = '$id'
" ) );
$s [$id] ['rw_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['rw_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// pw
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(pi.quantity) as qty, sum(pi.total) as val FROM ecmstockdocinsideinitems AS pi
INNER JOIN ecmstockdocinsideins AS p
ON p.id = pi.ecmstockdocinsidein_id
WHERE
pi.deleted = 0 AND
p.deleted = 0 AND
p.register_date LIKE '$date%' AND
pi.ecmproduct_id = '$id'
" ) );
$s [$id] ['pw_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['pw_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// ks in plus
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(ki.quantity) as qty, sum(ki.total) as val FROM ecmstockdoccorrectitems AS ki
INNER JOIN ecmstockdoccorrects AS k
ON k.id = ki.ecmstockdoccorrect_id
WHERE
ki.deleted = 0 AND
k.deleted = 0 AND
k.register_date LIKE '$date%' AND
ki.ecmproduct_id = '$id' AND
ki.quantity > 0
" ) );
$s [$id] ['ksp_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['ksp_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// ks in minus
$r = $db->fetchByAssoc ( $db->query ( "
SELECT sum(ki.quantity) as qty, sum(ki.total) as val FROM ecmstockdoccorrectitems AS ki
INNER JOIN ecmstockdoccorrects AS k
ON k.id = ki.ecmstockdoccorrect_id
WHERE
ki.deleted = 0 AND
k.deleted = 0 AND
k.register_date LIKE '$date%' AND
ki.ecmproduct_id = '$id' AND
ki.quantity < 0
" ) );
$s [$id] ['ksm_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['ksm_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
// +
$r = $db->fetchByAssoc($db->query(
"SELECT sum(quantity) as qty, sum(round(price*quantity,2)) as val
FROM ecmstockoperations
WHERE product_id='$id' AND
date_entered LIKE '$date%' AND
deleted='0' AND
type='0'"
));
$s [$id] ['in_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['in_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
//-
$r = $db->fetchByAssoc($db->query(
"SELECT sum(quantity) as qty, sum(round(price*quantity,2)) as val
FROM ecmstockoperations
WHERE product_id='$id' AND
date_entered LIKE '$date%' AND
deleted='0' AND
type='1'"
));
$s [$id] ['out_qty'] = isset ( $r ['qty'] ) ? round ( $r ['qty'], 2 ) : '0.00';
$s [$id] ['out_val'] = isset ( $r ['val'] ) ? round ( $r ['val'], 2 ) : '0.00';
}
$objPHPExcel = new PHPExcel ();
$objPHPExcel->getProperties ()->setCreator ( "E5 Polska sp. z o.o." );
$objPHPExcel->getProperties ()->setLastModifiedBy ( "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setOddFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getHeaderFooter ()->setEvenFooter ( '&R Strona &P z &N' );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'A' )->setWidth ( 5 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'B' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'C' )->setWidth ( 20 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'D' )->setWidth ( 25 );
$objPHPExcel->getActiveSheet ()->getColumnDimension ( 'E' )->setWidth ( 30 );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A1", "E5 Polska sp. z o.o." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A2", "Podsumowanie operacji" );
global $current_user;
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A3", "Sporządził: " . $current_user->full_name );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A4", "Za miesiąc: " . $date );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A6", "Lp." );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B6", "Kod" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C6", "Nazwa" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D6", "Ilośc bm" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E6", "Wartość bm" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F6", "Ilość pm" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "G6", "Wartość pm" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "H6", "Ilość WZ" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "I6", "Wartość WZ" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "J6", "Ilość PZ" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "K6", "Wartość PZ" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "L6", "Ilość PW" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "M6", "Wartość PW" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "N6", "Ilość RW" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "O6", "Wartość RW" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "P6", "KS > 0 Ilość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "Q6", "KS > 0 Wartość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "R6", "KS < 0 Ilość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "S6", "KS < 0 Wartość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "T6", "+ Ilość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "U6", "+ Wartość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "V6", "- Ilość" );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "W6", "- Wartość" );
$j = 7;
$bm = $date;
$tmp = explode ( "-", $date );
$year = $tmp [0];
$mounth = $tmp [1];
$pm = $year . "-" . (intval ( $mounth ) - 1);
foreach ( $s as $v ) {
$i = 1;
// echo $pm.'<br><br>';
// var_dump($v);
// die();
$objPHPExcel->getActiveSheet ()->SetCellValue ( "A" . $j, $i );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "B" . $j, $v ['code'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "C" . $j, $v ['code'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "D" . $j, $v [$bm . '_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "E" . $j, $v [$bm . '_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "F" . $j, $v [$pm . '_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "G" . $j, $v [$pm . '_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "H" . $j, $v ['wz_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "I" . $j, $v ['wz_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "J" . $j, $v ['pz_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "K" . $j, $v ['pz_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "L" . $j, $v ['pw_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "M" . $j, $v ['pw_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "N" . $j, $v ['rw_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "O" . $j, $v ['rw_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "P" . $j, $v ['ksp_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "Q" . $j, $v ['ksp_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "R" . $j, $v ['ksm_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "S" . $j, $v ['ksm_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "T" . $j, $v ['in_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "U" . $j, $v ['in_val'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "V" . $j, $v ['out_qty'] );
$objPHPExcel->getActiveSheet ()->SetCellValue ( "W" . $j, $v ['out_val'] );
$i ++;
$j ++;
}
$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );
$objWriter->save ( "modules/EcmStockOperations/maksyma/$date/summary.xlsx" );
unset ( $objPHPExcel );
unset ( $objWriter );
}
// create zip functions
// compress all files in the source directory to destination directory
function create_zip($files = array(), $dest = '', $overwrite = false) {
if (file_exists ( $dest ) && ! $overwrite) {
return false;
}
if (($files)) {
$zip = new ZipArchive ();
if ($zip->open ( $dest, $overwrite ? ZIPARCHIVE::OVERWRITE : ZIPARCHIVE::CREATE ) !== true) {
return false;
}
foreach ( $files as $file ) {
$zip->addFile ( $file, $file );
}
$zip->close ();
return file_exists ( $dest );
} else {
return false;
}
}
function addzip($source, $destination) {
$files_to_zip = glob ( $source . '/*' );
create_zip ( $files_to_zip, $destination );
}
function removeAccents($str) {
$polishChars = array (
'ą',
'ć',
'ę',
'ł',
'ń',
'ó',
'ś',
'ż',
'ź',
'Ś'
);
$replace = array (
'a',
'c',
'e',
'l',
'n',
'o',
's',
'z',
'z',
'S'
);
return str_replace ( $polishChars, $replace, $str );
}
?>
Reference in New Issue Copy Permalink