function getBudgetItemsByBudgetId($budgetId, $isGBK = false)
{
global $mysql;
$res = array();
$arr = $mysql->DBGetAsMap(" select * from `budget_item` where `budgetId` = '?' and `isDeleted` = 'false' ORDER BY LEFT( itemCode, 2 ) ASC , ( SUBSTRING( itemCode, 2 ) ) *1 DESC ", $budgetId);
$count = 0;
$smallCount = array(0, 0, 0, 0, 0, 0);
$directFee = 0;
$isFirstSmallCount = true;
$NOPQRSItems = array();
foreach ($arr as $val) {
$itemCode = $val['itemCode'];
$itemUnit = $val['itemUnit'];
$itemAmount = $val['itemAmount'] == "" || $val['itemAmount'] == null ? 0 : $val['itemAmount'];
$discount = $val['discount'];
$budgetId = $val['budgetId'];
$itemName = $isGBK ? str2GBK($val['itemName']) : $val['itemName'];
$itemUnit = $isGBK ? str2GBK($val['itemUnit']) : $val['itemUnit'];
//这几项需要单独计算
if (in_array($itemCode, array('N', 'O', 'P', 'Q', 'R', 'S'))) {
$NOPQRSItems[$itemCode] = $val;
continue;
}
// itemCode 长度为1时认为是大项
if (strlen($itemCode) == 1) {
if ($isFirstSmallCount) {
//第一个大项出现时不输出小计
$isFirstSmallCount = false;
} else {
//增加一行小计
$res[$count++] = array('budgetItemId' => 'NULL' . $count, 'itemName' => $isGBK ? str2GBK('小计') : '小计', 'budgetId' => $budgetId, 'mainMaterialTotalPrice' => $smallCount[0], 'auxiliaryMaterialTotalPrice' => $smallCount[1], 'manpowerTotalPrice' => $smallCount[2], 'machineryTotalPrice' => $smallCount[3], 'manpowerTotalCost' => $smallCount[4], 'mainMaterialTotalCost' => $smallCount[5]);
$directFee += $smallCount[0];
$directFee += $smallCount[1];
$directFee += $smallCount[2];
$directFee += $smallCount[3];
$smallCount = array(0, 0, 0, 0, 0, 0);
}
//输出大项
$res[$count++] = array('itemName' => $itemName, 'basicItemId' => $val['basicItemId'], 'itemCode' => $val['itemCode'], 'budgetId' => $val['budgetId'], 'budgetItemId' => $val['budgetItemId'], 'isEditable' => true);
continue;
}
//正常输出项
$res[$count] = $val;
$res[$count]['itemName'] = $itemName;
$res[$count]['itemAmount'] = $itemAmount;
$res[$count]['itemUnit'] = $itemUnit;
$res[$count]['mainMaterialPrice'] = $val['mainMaterialPrice'] * $discount / 100;
$res[$count]['auxiliaryMaterialPrice'] = $val['auxiliaryMaterialPrice'] * $discount / 100;
$res[$count]['manpowerPrice'] = $val['manpowerPrice'] * $discount / 100;
$res[$count]['machineryPrice'] = $val['machineryPrice'] * $discount / 100;
$res[$count]['orgMainMaterialPrice'] = "原价:" . $val['mainMaterialPrice'] . " " . ($discount == 100 ? "" : $discount / 10 . "折");
$res[$count]['orgAuxiliaryMaterialPrice'] = "原价:" . $val['auxiliaryMaterialPrice'] . " " . ($discount == 100 ? "" : $discount / 10 . "折");
$res[$count]['orgManpowerPrice'] = "原价:" . $val['manpowerPrice'] . " " . ($discount == 100 ? "" : $discount / 10 . "折");
$res[$count]['orgMachineryPrice'] = "原价:" . $val['machineryPrice'] . " " . ($discount == 100 ? "" : $discount / 10 . "折");
//损耗=（主材单价+辅料单价）*0.05,按折扣后的价格
$loss = ($res[$count]['mainMaterialPrice'] + $res[$count]['auxiliaryMaterialPrice']) * 0.05;
if ($val['lossPercent'] != $loss) {
$val['lossPercent'] = $loss;
editItem($val);
}
$res[$count]['lossPercent'] = $loss;
//主材总价=（主菜单价+损耗）* 数量,按折扣后的价格
$mainMaterialTotalPrice = $itemAmount * ($res[$count]['mainMaterialPrice'] + $loss);
$res[$count]['mainMaterialTotalPrice'] = $mainMaterialTotalPrice;
$res[$count]['auxiliaryMaterialTotalPrice'] = $itemAmount * $res[$count]['auxiliaryMaterialPrice'];
$res[$count]['manpowerTotalPrice'] = $itemAmount * $res[$count]['manpowerPrice'];
$res[$count]['machineryTotalPrice'] = $itemAmount * $res[$count]['machineryPrice'];
$res[$count]['remark'] = $val['remark'] == 'NULL' ? '' : ($isGBK ? str2GBK($val['remark']) : addslashes(nl2br(str_replace("\n", "<br />", $val['remark']))));
$res[$count]['isEditable'] = true;
/**
2.辅材总价=辅材单价*数量
3.人工总价=人工单价*数量
4.机械总价=机械单价*数量
6.小计=各类小项总价之和
7.合计=所有小巷综合
**/
$smallCount[0] += $res[$count]['mainMaterialTotalPrice'];
$smallCount[1] += $res[$count]['auxiliaryMaterialTotalPrice'];
$smallCount[2] += $res[$count]['manpowerTotalPrice'];
$smallCount[3] += $res[$count]['machineryTotalPrice'];
$smallCount[4] += $res[$count]['manpowerCost'];
$smallCount[5] += $res[$count]['mainMaterialCost'];
foreach ($res[$count] as $key => $val) {
if ($val === "" || $val === null) {
//去除空值，减少网络数据量
unset($res[$count][$key]);
}
}
$count++;
}
//如果 isFirstSmallCount 还是初始化的状态true说明没有一行小计,false的时候，说明至少有一个大项输出了。
if (!$isFirstSmallCount) {
//最后一行小计
$res[$count++] = array('budgetItemId' => 'NULL' . $count, 'itemName' => $isGBK ? str2GBK('小计') : '小计', 'budgetId' => $budgetId, 'mainMaterialTotalPrice' => $smallCount[0], 'auxiliaryMaterialTotalPrice' => $smallCount[1], 'manpowerTotalPrice' => $smallCount[2], 'machineryTotalPrice' => $smallCount[3], 'manpowerTotalCost' => $smallCount[4], 'mainMaterialTotalCost' => $smallCount[5], 'isEditable' => false);
$directFee += $smallCount[0];
$directFee += $smallCount[1];
$directFee += $smallCount[2];
$directFee += $smallCount[3];
$smallCount = array(0, 0, 0, 0, 0, 0);
}
//增加一行空行
$res[$count++] = array('budgetItemId' => 'NULL' . $count, 'budgetId' => $budgetId, 'isEditable' => false);
//计算其他项
$totalFee = $directFee;
//N 工程直接费
$itemUnit = '元';
$itemName = '工程直接费';
$itemCode = 'N';
$item = $NOPQRSItems[$itemCode];
$budgetItemId = $item['budgetItemId'];
$fee = $directFee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
if ($fee != $item['mainMaterialPrice']) {
$item['mainMaterialPrice'] = $fee;
$arr = editItem($item);
// update
}
// O 设计费
$itemUnit = '元';
$itemName = '设计费3%';
$itemCode = 'O';
$item = $NOPQRSItems[$itemCode];
$itemAmount = $item['itemAmount'];
$budgetItemId = $item['budgetItemId'];
$fee = $directFee * $itemAmount;
$totalFee += $fee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'itemAmount' => $itemAmount, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
if ($fee != $item['mainMaterialPrice']) {
$item['mainMaterialPrice'] = $fee;
$arr = editItem($item);
// update
}
// P 效果图
$itemUnit = '张';
$itemName = '效果图';
$itemCode = 'P';
$item = $NOPQRSItems[$itemCode];
$itemAmount = $item['itemAmount'];
$budgetItemId = $item['budgetItemId'];
$fee = 500 * $itemAmount;
$totalFee += $fee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'itemAmount' => $itemAmount, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
// Q 5%管理费
$itemUnit = '元';
$itemName = '5%管理费';
$itemCode = 'Q';
$item = $NOPQRSItems[$itemCode];
$itemAmount = $item['itemAmount'];
$budgetItemId = $item['budgetItemId'];
$fee = $directFee * $itemAmount;
$totalFee += $fee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'itemAmount' => $itemAmount, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
if ($fee != $item['mainMaterialPrice']) {
$item['mainMaterialPrice'] = $fee;
$arr = editItem($item);
// update
}
// R 税金
$itemUnit = '元';
$itemName = '税金';
$itemCode = 'R';
$item = $NOPQRSItems[$itemCode];
$itemAmount = $item['itemAmount'];
$budgetItemId = $item['budgetItemId'];
$fee = $directFee * $itemAmount;
$totalFee += $fee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'itemAmount' => $itemAmount, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
if ($fee != $item['mainMaterialPrice']) {
$item['mainMaterialPrice'] = $fee;
$arr = editItem($item);
// update
}
// S 工程总造价
$itemUnit = '元';
$itemName = '工程总造价';
$itemCode = 'S';
$item = $NOPQRSItems[$itemCode];
$budgetItemId = $item['budgetItemId'];
$itemAmount = '';
$fee = $totalFee;
$res[$count++] = array('budgetItemId' => $budgetItemId, 'itemName' => $isGBK ? str2GBK($itemName) : $itemName, 'budgetId' => $budgetId, 'itemCode' => $itemCode, 'itemUnit' => $isGBK ? str2GBK($itemUnit) : $itemUnit, 'itemAmount' => $itemAmount, 'mainMaterialTotalPrice' => $fee, 'isEditable' => false);
if ($fee != $item['mainMaterialPrice']) {
$item['mainMaterialPrice'] = $fee;
$arr = editItem($item);
// update
}
foreach ($res as $count => $bItem) {
//保留小数点后两位,不足补0
foreach ($bItem as $key => $val) {
if (!in_array($key, array('itemAmount', 'mainMaterialTotalPrice', 'auxiliaryMaterialTotalPrice', 'manpowerTotalPrice', 'mainMaterialTotalCost', 'lossPercent', 'manpowerTotalCost', 'machineryTotalPrice', 'mainMaterialPrice', 'auxiliaryMaterialPrice', 'machineryPrice', 'manpowerPrice'))) {
continue;
}
$res[$count][$key] = formatNumber($val);
}
}
return $res;
}
<?php
include_once './Classes/PHPExcel.php';
include_once './Classes/PHPExcel/Writer/Excel5.php';
include_once "../libs/conn.php";
include_once '../libs/budgetDB.php';
$downLoad = false;
if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'download') {
$downLoad = true;
}
$budget = getBudgetsByBudgetId($_REQUEST["budgetId"]);
$budgetItems = getBudgetItemsByBudgetId($_REQUEST["budgetId"], true, false);
$fileName = str2GBK(urldecode($budget[0]["projectName"])) . ".xls";
$cancel_time = date("YmdHis");
// 创建一个处理对象实例
$objExcel = new PHPExcel();
// 创建文件格式写入对象实例, uncomment
//$objWriter = new PHPExcel_Writer_Excel5($objExcel);
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
//设置文档基本属性 encode error ,dont konw why
/*$compay = "author";
$objProps = $objExcel->getProperties();
$objProps->setCreator($compay);
$objProps->setLastModifiedBy(urldecode($compay));
$objProps->setTitle($compay);
$objProps->setTitle(str2GBK(urldecode($budget[0]['projectName'])));
$objProps->setSubject($compay);
$objProps->setDescription($compay);
$objProps->setKeywords($compay));
$objProps->setCategory($compay)); */
//*************************************
//$titleLeft = array(4,25,54.5,65,236);
$GfontSize = 10;
include_once "../libs/conn.php";
include_once '../libs/budgetDB.php';
include_once 'chinese.php';
include_once 'pdf_chinese.php';
global $custName;
global $projectName;
$budget = getBudgetsByBudgetId($_REQUEST["budgetId"]);
if (count($budget) == 0) {
throw new Exception("no budget with id " . $_REQUEST["budgetId"]);
}
$custName = str2GBK(urldecode($budget[0]["custName"]));
$projectName = str2GBK(urldecode($budget[0]["businessAddress"] == "" ? $budget[0]["projectName"] : $budget[0]["businessRegion"] . " " . $budget[0]["businessAddress"]));
$budgetName = str2GBK(urldecode($budget[0]["budgetName"]));
$projectComments = str2GBK(urldecode($budget[0]["comments"]));
$action = isset($_REQUEST["action"]) ? $_REQUEST["action"] : "download";
$budgetItems = getBudgetItemsByBudgetId($_REQUEST["budgetId"], true);
$pdf = new PDF('L', 'mm', 'A4');
//创建新的FPDF对象
$pdf->AddGBFont();
//设置中文字体
$pdf->Open();
//开始创建PDF
$pdf->AddPage();
//增加一页
$pdf->SetFont("GB", '', $GfontSize);
//设置字体样式
$pdf->AliasNbPages("__totalPage__");
//echo $custName.":".$projectName;
foreach ($budgetItems as $bItem) {
