数据格式:Array
(
[num] => 1
[project] => 差旅费(国内)
[project_category] =>
[code] => 5101060201
[item_real] => 1.2
[item_budget] => 2.8
[item_current_predict] => 2.5
[item1] =>
[item2] =>
[item3] => 2.5
[item4] => 4.5
[item5] => 4.5
[item6] =>
[tuisuan] => 2.3
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 1
)
Array
(
[num] => 2
[project] => 培训费(国内)
[project_category] =>
[code] => 5101060301
[item_real] => 0.1
[item_budget] => 0.3
[item_current_predict] => 0.3
[item1] =>
[item2] =>
[item3] => 0.3
[item4] => 0.3
[item5] => 0.3
[item6] =>
[tuisuan] => 0.2
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 2
)
Array
(
[num] => 3
[project] => 业务招待费
[project_category] =>
[code] => 51010604
[item_real] => 0.0
[item_budget] => 0.6
[item_current_predict] => 0.2
[item1] =>
[item2] =>
[item3] => 0.2
[item4] => 1.0
[item5] => 1.0
[item6] =>
[tuisuan] => 0.4
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 3
)Array
(
[num] => 4
[project] => 办公费
[project_category] =>
[code] => 5101060601/602/699
[item_real] => 0.3
[item_budget] => 0.3
[item_current_predict] => 0.3
[item1] => 0.4
[item2] => 0.2
[item3] => 0.3
[item4] => 0.3
[item5] => 0.3
[item6] =>
[tuisuan] => 0.3
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 4
)Array
(
[num] => 5
[project] => 消耗品
[project_category] =>
[code] => 51010607
[item_real] => 1.2
[item_budget] => 1.0
[item_current_predict] => 1.0
[item1] =>
[item2] =>
[item3] => 1.0
[item4] => 1.0
[item5] => 1.0
[item6] =>
[tuisuan] => 0.6
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 5
)
Array
(
[num] => 6
[project] => 物流费
[project_category] => 空运费
[code] => 5101040304/6601011602
[item_real] => 24.6
[item_budget] => 25.2
[item_current_predict] => 26.0
[item1] => 26.0
[item2] => 64.8
[item3] => 26.0
[item4] => 26.0
[item5] => 26.0
[item6] =>
[tuisuan] => 33.8
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 6
)
Array
(
[num] => 7
[project] => 物流费
[project_category] => 海运费
[code] => 5101040303/660101160
[item_real] => 18.5
[item_budget] => 21.6
[item_current_predict] => 21.0
[item1] => 15.9
[item2] => 21.0
[item3] => 21.0
[item4] => 21.0
[item5] => 21.0
[item6] =>
[tuisuan] => 20.0
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 7
)
Array
(
[num] => 8
[project] => 物流费
[project_category] => 杂费
[code] => 5101040399
[item_real] => 2.3
[item_budget] => 3.7
[item_current_predict] => 5.0
[item1] => 0.2
[item2] => 0.8
[item3] => 5.0
[item4] => 5.0
[item5] => 5.0
[item6] =>
[tuisuan] => 3.2
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 8
)
Array
(
[num] => 9
[project] => 物流费
[project_category] => 小计
[code] =>
[item_real] => 45.5
[item_budget] => 48.5
[item_current_predict] => 52.0
[item1] => 43.1
[item2] => 86.6
[item3] => 52.0
[item4] => 52.0
[item5] => 52.0
[item6] =>
[tuisuan] => 57.1
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 9
)
Array
(
[num] => 10
[project] => 国内费用小计
[project_category] =>
[code] =>
[item_real] => 48.3
[item_budget] => 53.5
[item_current_predict] => 56.3
[item1] => 43.5
[item2] => 86.8
[item3] => 56.3
[item4] => 59.1
[item5] => 59.1
[item6] =>
[tuisuan] => 61.0
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 10
)
Array
(
[num] => 11
[project] => 出张旅费(海外)
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 2.7
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 15
)
Array
(
[num] => 12
[project] => 培训费(海外)
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 1.9
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 16
)
Array
(
[num] => 13
[project] => 技师派遣
[project_category] =>
[code] =>
[item_real] =>
[item_budget] =>
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 17
)
Array
(
[num] => 14
[project] => 海外费用小计
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 4.6
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 18
)
Array
(
[num] => 15
[project] => 可控费用合计
[project_category] =>
[code] =>
[item_real] => 48.3
[item_budget] => 58.1
[item_current_predict] => 56.3
[item1] => 43.5
[item2] => 86.8
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] => 65.2
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 19
)
关系为:合计费用=国内+国外
国内=各项国内费用相加
国外=各项国内费用相加
小计=大项中的个小项相加数据库描述:CREATE TABLE IF NOT EXISTS `budgetcontrol` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`num` int(4) NOT NULL,
`localcation` tinyint(2) NOT NULL,
`project` varchar(20) NOT NULL,
`project_category` varchar(20) NOT NULL,
`code` varchar(50) DEFAULT NULL,
`item_real` decimal(18,1) DEFAULT NULL,
`item_budget` decimal(18,1) DEFAULT NULL,
`item_current_predict` decimal(18,1) DEFAULT NULL,
`item_current_real` decimal(18,1) DEFAULT NULL,
`item1` decimal(18,1) DEFAULT NULL,
`item2` decimal(18,1) DEFAULT NULL,
`item3` decimal(18,1) DEFAULT NULL,
`item4` decimal(18,1) DEFAULT NULL,
`item5` decimal(18,1) DEFAULT NULL,
`item6` decimal(18,1) DEFAULT NULL,
`department` varchar(20) NOT NULL,
`comment` varchar(50) DEFAULT NULL,
`tuisuan` decimal(18,1) DEFAULT NULL,
`tuisuan_status` tinyint(4) NOT NULL,
`current_status` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
请问数据库设计有无问题?怎么求这些值?图中为num=9,10,14,15这些项。
(
[num] => 1
[project] => 差旅费(国内)
[project_category] =>
[code] => 5101060201
[item_real] => 1.2
[item_budget] => 2.8
[item_current_predict] => 2.5
[item1] =>
[item2] =>
[item3] => 2.5
[item4] => 4.5
[item5] => 4.5
[item6] =>
[tuisuan] => 2.3
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 1
)
Array
(
[num] => 2
[project] => 培训费(国内)
[project_category] =>
[code] => 5101060301
[item_real] => 0.1
[item_budget] => 0.3
[item_current_predict] => 0.3
[item1] =>
[item2] =>
[item3] => 0.3
[item4] => 0.3
[item5] => 0.3
[item6] =>
[tuisuan] => 0.2
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 2
)
Array
(
[num] => 3
[project] => 业务招待费
[project_category] =>
[code] => 51010604
[item_real] => 0.0
[item_budget] => 0.6
[item_current_predict] => 0.2
[item1] =>
[item2] =>
[item3] => 0.2
[item4] => 1.0
[item5] => 1.0
[item6] =>
[tuisuan] => 0.4
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 3
)Array
(
[num] => 4
[project] => 办公费
[project_category] =>
[code] => 5101060601/602/699
[item_real] => 0.3
[item_budget] => 0.3
[item_current_predict] => 0.3
[item1] => 0.4
[item2] => 0.2
[item3] => 0.3
[item4] => 0.3
[item5] => 0.3
[item6] =>
[tuisuan] => 0.3
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 4
)Array
(
[num] => 5
[project] => 消耗品
[project_category] =>
[code] => 51010607
[item_real] => 1.2
[item_budget] => 1.0
[item_current_predict] => 1.0
[item1] =>
[item2] =>
[item3] => 1.0
[item4] => 1.0
[item5] => 1.0
[item6] =>
[tuisuan] => 0.6
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 5
)
Array
(
[num] => 6
[project] => 物流费
[project_category] => 空运费
[code] => 5101040304/6601011602
[item_real] => 24.6
[item_budget] => 25.2
[item_current_predict] => 26.0
[item1] => 26.0
[item2] => 64.8
[item3] => 26.0
[item4] => 26.0
[item5] => 26.0
[item6] =>
[tuisuan] => 33.8
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 6
)
Array
(
[num] => 7
[project] => 物流费
[project_category] => 海运费
[code] => 5101040303/660101160
[item_real] => 18.5
[item_budget] => 21.6
[item_current_predict] => 21.0
[item1] => 15.9
[item2] => 21.0
[item3] => 21.0
[item4] => 21.0
[item5] => 21.0
[item6] =>
[tuisuan] => 20.0
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 7
)
Array
(
[num] => 8
[project] => 物流费
[project_category] => 杂费
[code] => 5101040399
[item_real] => 2.3
[item_budget] => 3.7
[item_current_predict] => 5.0
[item1] => 0.2
[item2] => 0.8
[item3] => 5.0
[item4] => 5.0
[item5] => 5.0
[item6] =>
[tuisuan] => 3.2
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 8
)
Array
(
[num] => 9
[project] => 物流费
[project_category] => 小计
[code] =>
[item_real] => 45.5
[item_budget] => 48.5
[item_current_predict] => 52.0
[item1] => 43.1
[item2] => 86.6
[item3] => 52.0
[item4] => 52.0
[item5] => 52.0
[item6] =>
[tuisuan] => 57.1
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 9
)
Array
(
[num] => 10
[project] => 国内费用小计
[project_category] =>
[code] =>
[item_real] => 48.3
[item_budget] => 53.5
[item_current_predict] => 56.3
[item1] => 43.5
[item2] => 86.8
[item3] => 56.3
[item4] => 59.1
[item5] => 59.1
[item6] =>
[tuisuan] => 61.0
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 10
)
Array
(
[num] => 11
[project] => 出张旅费(海外)
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 2.7
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 15
)
Array
(
[num] => 12
[project] => 培训费(海外)
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 1.9
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 16
)
Array
(
[num] => 13
[project] => 技师派遣
[project_category] =>
[code] =>
[item_real] =>
[item_budget] =>
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 17
)
Array
(
[num] => 14
[project] => 海外费用小计
[project_category] =>
[code] =>
[item_real] =>
[item_budget] => 4.6
[item_current_predict] =>
[item1] =>
[item2] =>
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] =>
[tuisuan_status] => 0
[current_status] => 0
[comment] =>
[id] => 18
)
Array
(
[num] => 15
[project] => 可控费用合计
[project_category] =>
[code] =>
[item_real] => 48.3
[item_budget] => 58.1
[item_current_predict] => 56.3
[item1] => 43.5
[item2] => 86.8
[item3] =>
[item4] =>
[item5] =>
[item6] =>
[tuisuan] => 65.2
[tuisuan_status] => 1
[current_status] => 1
[comment] =>
[id] => 19
)
关系为:合计费用=国内+国外
国内=各项国内费用相加
国外=各项国内费用相加
小计=大项中的个小项相加数据库描述:CREATE TABLE IF NOT EXISTS `budgetcontrol` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`num` int(4) NOT NULL,
`localcation` tinyint(2) NOT NULL,
`project` varchar(20) NOT NULL,
`project_category` varchar(20) NOT NULL,
`code` varchar(50) DEFAULT NULL,
`item_real` decimal(18,1) DEFAULT NULL,
`item_budget` decimal(18,1) DEFAULT NULL,
`item_current_predict` decimal(18,1) DEFAULT NULL,
`item_current_real` decimal(18,1) DEFAULT NULL,
`item1` decimal(18,1) DEFAULT NULL,
`item2` decimal(18,1) DEFAULT NULL,
`item3` decimal(18,1) DEFAULT NULL,
`item4` decimal(18,1) DEFAULT NULL,
`item5` decimal(18,1) DEFAULT NULL,
`item6` decimal(18,1) DEFAULT NULL,
`department` varchar(20) NOT NULL,
`comment` varchar(50) DEFAULT NULL,
`tuisuan` decimal(18,1) DEFAULT NULL,
`tuisuan_status` tinyint(4) NOT NULL,
`current_status` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
请问数据库设计有无问题?怎么求这些值?图中为num=9,10,14,15这些项。
要区分国内、国外,你总得有一个标记吧
要区分大项、中项、小项,你又的有一个标记吧?如果说你的 code 就是这个东西,那么你得搞清楚他的编制方法,如果不能按位区别的话还需要再加工
localcation字段代表国内国外项目
project代表每个项目
project_category代表每个项目的子项
code是业务代码应该没有问题吧?
y=x1+x2从数据结构上完全没法区分哪个是y,哪个是x1/x2
而且还存在x1=x11+x12的深层逻辑关系,更加无法区分
其他都是唯一的无所谓求和
`item5`,`item6`,`tuisuan`,`tuisuan_status`,`current_status`,`comment`,`id`";
$sql .= "FROM `budgetcontrol` ".$str."WHERE `department` = 'ga' order by `num` asc";
$result = mysql_query($sql, $link);// 初始化统计和小计的数组
$total_p = array();
$total_c = array();
// 记录上一个项目的数据
$last_project = array();// 记录显示的列表
$list = array();// 初始化统计和小计需要统计的字段
while($row = mysql_fetch_field($result)) {
// 当然你可以手工写,无所谓
if($row->type == 'real') {
$total_p[$row->name] = null;
$total_c[$row->name] = null;
} else {
$total_p[$row->name] = '';
$total_c[$row->name] = '';
}
// 为了显示方便
$list[0][$row->name] = $row->name;
}$total_p['project'] = '合计';
$total_c['project_category'] = '小计';$curr_total_c = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if(empty($last_project)) {
$last_project = $row;
} else {
if($last_project['project'] == $row['project']) {
// 初次记录
if(!$curr_total_c['project']) {
$curr_total_c['project'] = $row['project'];
$curr_total_c = total_run($curr_total_c, $last_project);
}
// 记录小计
$curr_total_c = total_run($curr_total_c, $row);
} else {
// 添加到数组
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
$curr_total_c = $total_c;
}
// 重置上一记录
$last_project = $row;
}
}
// 总计
$total_p = total_run($total_p, $row);
// 记录内容
$list[] = $row;
}// 最后判断一次是否有小计
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
}// 记录总计
$list[] = $total_p;
echo '<table>';
foreach($list as $line => $row) {
echo '<tr>';
foreach($row as $k => $v) {
if($k == 'num') $v = $v ? $v : $line - 1;
echo '<td>', $v, '</td>';
}
echo '</tr>';
}echo '</table>';function total_run($total, $row) {
foreach($row as $k => $v) {
if($total[$k] === null) {
$total[$k] = $v;
} else if(is_numeric($total[$k])) {
$total[$k] += $v;
}
}
return $total;
}mysql_free_result($result);