1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
<?php
ignore_user_abort(true);
$formid = "Order";
require_once "inc/configure.php";
require_once "inc/phpexcel_1.7.7/PHPExcel.php";
if(havePermission("DPr")==false){
myerror("Invalid Permission");
}
/*--------------------------------END OF FUNCTIONS--------------------------------------*/
$refid = (int)setDefaultReqVar("refid", '');
$customGoldRate = (float)setDefaultReqVar("customGoldRate", false);
if($refid && $customer = getDB("master_customer", $refid) ){
} else {
myerror("Invalid Request");
}
$header = $sth->fetch();
//vdump($header); exit;
@set_time_limit(0);
//ini_set('max_execution_time', '999');
ini_set('post_max_size', '20M');
ini_set('memory_limit','1024M');
$objPHPexcel = PHPExcel_IOFactory::load('templates/cust_pricelist.xls');
$objWorksheet = $objPHPexcel->getSheetByName('Pricelist');
/*
title: A1
issue date: B2
today gold rate: B3
*/
$todaygoldrate = getMatPrice('gold');
$objWorksheet->getCell('A1')->setValue($customer['custname_en'].' Price List');
$objWorksheet->getCell('B2')->setValue(date("Y-m-d"));
$objWorksheet->getCell('B3')->setValue(numf($todaygoldrate));
$objWorksheet->getCell('D3')->setValue(numf(weightConv($todaygoldrate, 'gr', 'oz')));
if($customGoldRate){
$objWorksheet->getCell('T3')->setValue('Gold rate calculation:');
$objWorksheet->getCell('U3')->setValue(numf($customGoldRate));
$objWorksheet->getCell('V3')->setValue('/oz');
}
//vdump($todaygoldrate, weightConv($todaygoldrate, 'gr', 'oz')); exit;
$excel_row=6;
//products:
$sql = "SELECT
dbo.inv_product.*
FROM
dbo.inv_product
WHERE
dbo.inv_product.custcode = :custcode
ORDER BY
dbo.inv_product.itemno ASC
";
$sth = $dbh->prepare($sql);
$sth->execute( array(':custcode'=>$customer['custcode']) );
//echo $sth->getSQL( array(':custcode'=>$customer['custcode']) );
while($dtl = $sth->fetch() ){
$productrow = $excel_row;
$productPriceHist = getProductPriceHistory($dtl['refid']);
$productPriceHist['ttlcost'] = $productPriceHist['labourcost']+$productPriceHist['materialcost'];
$productInvoiceHist = getInvoicedHistory($dtl['refid']);
//vdump($productInvoiceHist); exit;
if($customGoldRate){//use input gold rate
$dtl['goldprice'] = weightConv($customGoldRate, "gr", "oz"); // =matprice/(oz->gr)
$dtl['materialcost'] = getProductBOMCost($dtl['refid'], $customGoldRate);
$dtl['labourcost'] = getProductRouteCost($dtl['refid']);
$dtl['ttlcost'] = $dtl['materialcost'] + $dtl['labourcost'];
}
/*
elseif($productPriceHist['metalprice']){
$dtl['goldprice'] = $productPriceHist['metalprice'];
$dtl['materialcost'] = getProductBOMCost($dtl['refid']);
$dtl['labourcost'] = getProductRouteCost($dtl['refid']);
$dtl['ttlcost'] = $dtl['materialcost'] + $dtl['labourcost'];
}
*/
else{
$dtl['goldprice'] = weightConv(getMatPrice('gold'), "gr", "oz");
$dtl['materialcost'] = getProductBOMCost($dtl['refid']);
$dtl['labourcost'] = getProductRouteCost($dtl['refid']);
$dtl['ttlcost'] = $dtl['materialcost'] + $dtl['labourcost'];
}
//vdump(//$customGoldRate, $productPriceHist, getMatPrice('gold'), $dtl['goldprice']); exit;
//vdump($productPriceHist['createdate'], getMatPrice('gold', $productPriceHist['createdate']) );
//echo $dtl['refid'].' '.prod_itemdesc($dtl['refid']).HTML_EOL;
$objWorksheet->insertNewRowBefore($excel_row+1, 1);
$objWorksheet->setCellValueByColumnAndRow(0, $productrow, $dtl['refid'] );
$objWorksheet->setCellValueByColumnAndRow(1, $productrow, $dtl['itemno'] );
$objWorksheet->setCellValueByColumnAndRow(2, $productrow, resolveCode_master_type_code('PRODTYPE', $dtl['producttype'], false, 'en') );
$objWorksheet->setCellValueByColumnAndRow(3, $productrow, evl(prod_itemdesc($dtl['refid']), '') );
//4 invoice history data
$col=4;
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productInvoiceHist['metalprice']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, '/gr' );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, datef($productInvoiceHist['invoiced_time'],10) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productInvoiceHist['unitcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productInvoiceHist['unitprice']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf( calcMarginNew($productInvoiceHist['unitprice'],$productInvoiceHist['unitcost']) ) );
//10 price history
$col=10;
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, datef($productPriceHist['createdate'],10) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productPriceHist['metalprice']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, '/gr' );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productPriceHist['materialcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productPriceHist['labourcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($productPriceHist['ttlcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf( calcMarginNew($productPriceHist['sellingprice'],$productPriceHist['ttlcost']) ) );
//17 actucal or chosen gold rate calculation
$col=17;
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($dtl['goldprice']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, '/gr' );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($dtl['materialcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($dtl['labourcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($dtl['ttlcost']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf($dtl['sellingprice']) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, numf( calcMarginNew($productPriceHist['sellingprice'],$dtl['ttlcost']) ) );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, '' );
$objWorksheet->setCellValueByColumnAndRow($col++, $productrow, 'N' );
/*
$styleArray = array(
'borders' => array(
'allborders' => array(
'color' => array('argb' => 'FFFFFFFF'),
),
'horizontal' => array(
'color' => array('argb' => '00000000'),
),
),
);
$objWorksheet->getStyle("D".$productrow)->applyFromArray($styleArray);
*/
//$objWorksheet->getStyle("A$excel_row:O$excel_row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
if(getProductPath($dtl['refid'], true)){
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName($dtl['itemno']);
$objDrawing->setDescription($dtl['itemno']);
$objDrawing->setPath(getProductPath($dtl['refid'], true));
$objDrawing->setHeight(40);//$rowheight-4);
// Insert picture
$objDrawing->setCoordinates('A'.$productrow);
$objDrawing->setOffsetX(5);
$objDrawing->setOffsetY(3);
$objDrawing->setWorksheet($objWorksheet);
//echo "EOL IMG----";
}
$excel_row++;
}
//exit;
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Pricelist_'.$customer['custcode'].'.xls"');
header('Cache-Control: max-age=0');
$objPHPexcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
$objWriter->save('php://output');
//$objWriter = new PHPExcel_Writer_PDF($objPHPexcel);
//$objWriter->save("test.pdf");
flush();
?>
|