数据分类 本帖最后由 lazygc520 于 2011-05-12 09:44:13 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 数据表结构:---- 表的结构 `budgetcontrol`--CREATE TABLE IF NOT EXISTS `budgetcontrol` ( `id` int(10) NOT NULL AUTO_INCREMENT, `num` int(4) NOT NULL, `localcation` varchar(20) NOT NULL, `project` varchar(20) NOT NULL, `project_category` varchar(20) NOT NULL, `code` varchar(50) CHARACTER SET utf8 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) CHARACTER SET utf8 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=gb2312 AUTO_INCREMENT=232 ;---- 转存表中的数据 `budgetcontrol`--INSERT INTO `budgetcontrol` (`id`, `num`, `localcation`, `project`, `project_category`, `code`, `item_real`, `item_budget`, `item_current_predict`, `item_current_real`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `department`, `comment`, `tuisuan`, `tuisuan_status`, `current_status`) VALUES(1, 1, '国内', '差旅费(国内)', '', '5101060201', 1.2, 2.8, 2.5, NULL, NULL, NULL, 2.5, 4.5, 4.5, NULL, 'pu', NULL, 2.3, 0, 0),(2, 2, '国内', '培训费(国内)', '', '5101060301', 0.1, 0.3, 0.3, NULL, NULL, NULL, 0.3, 0.3, 0.3, NULL, 'pu', NULL, 0.2, 0, 0),(6, 6, '国内', '物流费', '空运费', '5101040304/6601011602', 24.6, 25.2, 26.0, 64.8, 26.0, 64.8, 26.0, 26.0, 26.0, NULL, 'pu', '郑州日产增产空运费(29K),瑞意泰克紧急空运费(29.7K)年末海关查验采取空运对应费(6K)', 33.8, 0, 0),(7, 7, '国内', '物流费', '海运费', '5101040303/660101160', 18.5, 21.6, 21.0, 21.0, 15.9, 21.0, 21.0, 21.0, 21.0, NULL, 'pu', NULL, 20.0, 0, 0),(8, 8, '国内', '物流费', '杂费', '5101040399', 2.3, 3.7, 5.0, 0.8, 0.2, 0.8, 5.0, 5.0, 5.0, NULL, 'pu', NULL, 3.2, 0, 0),(3, 3, '国内', '业务招待费', '', '51010604', 0.0, 0.6, 0.2, NULL, NULL, NULL, 0.2, 1.0, 1.0, NULL, 'pu', NULL, 0.4, 0, 0),(4, 4, '国内', '办公费', '', '5101060601/602/699', 0.3, 0.3, 0.3, 0.2, 0.4, 0.2, 0.3, 0.3, 0.3, 0.0, 'pu', '', 0.3, 0, 0),(5, 5, '国内', '消耗品', '', '51010607', 1.2, 1.0, 1.0, NULL, NULL, NULL, 1.0, 1.0, 1.0, NULL, 'pu', '', 1.0, 0, 0),(15, 11, '海外', '出张旅费(海外)', '', NULL, NULL, 2.7, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0),(16, 12, '海外', '培训费(海外)', '', NULL, NULL, 1.9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0),(17, 13, '海外', '技师派遣', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0); 请问分类信息加在哪里?localcation字段代表国内,海外项目标识。 <?php error_reporting(E_ALL & ~E_NOTICE);$db_server = "localhost";$db_login = "root";$db_password = "123456";$db_name = "q";$link = mysql_connect($db_server, $db_login, $db_password);if (!$link) { die('Could not connect: ' . mysql_error());}mysql_select_db($db_name, $link);mysql_query("set names 'gb2312'");$sql = "SELECT `num`,`localcation`,`project`,`project_category`,`code`,`item_real`,`item_budget`,`item_current_predict`,`item_current_real`,`item1`,`item2`,`item3`,`item4`, `item5`,`item6`,`tuisuan`,`tuisuan_status`,`current_status`,`comment`,`id`";$sql .= "FROM `budgetcontrol` ".$str."WHERE `department` = 'pu' order by `num` asc";$result = mysql_query($sql, $link);// 初始化统计和小计的数组$total_p = array();$total_c = array();$total_m = array();// 记录上一个项目的数据$last_project = array();$last_localcation =array();// 记录显示的列表$list = array();// 初始化统计和小计需要统计的字段while($row = mysql_fetch_field($result)) { // 当然你可以手工写,无所谓 if($row->type == 'real') { $total_p[$row->name] = null; $total_c[$row->name] = null; $total_m[$row->name] = null; } else { $total_p[$row->name] = ''; $total_c[$row->name] = ''; $total_m[$row->name] = ''; } // 为了显示方便 $list[0][$row->name] = $row->name;}$total_p['localcation'] = "可控费用合计";$total_m['project'] = "费用小计";$total_c['project_category'] = "小计";// 初始化当前统计内容数组$curr_total_c = $total_c;$curr_total_m = $total_m;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_category'] = $total_c['project_category']; $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($curr_total_c, $total_c)) > 0) { $curr_total_c['num'] = count($list); $list[] = $curr_total_c; $curr_total_c = $total_c; } // 重置上一记录 $last_project = $row; } } if(empty($last_localcation)) { $last_localcation = $row; } else { if($last_localcation['localcation'] == $row['localcation']) { // 初次记录 if(!$curr_total_m['localcation']) { $curr_total_m['localcation'] = $row['localcation']; $curr_total_m['project'] = $row['localcation'].$total_m['project']; $curr_total_m = total_run($curr_total_m, $last_localcation); } // 记录小计 $curr_total_m = total_run($curr_total_m, $row); } else { // 添加到数组 if(count(array_diff_assoc($curr_total_m, $total_m)) > 0) { $curr_total_m['num'] = count($list); $list[] = $curr_total_m; $curr_total_m = $total_m; } // 重置上一记录 $last_localcation = $row; } } // 总计 $total_p = total_run($total_p, $row); // 记录内容 $list[] = $row; }// 最后判断一次是否有小计if(count(array_diff_assoc($curr_total_c, $total_c)) > 0) { $list[] = $curr_total_c;}if(count(array_diff_assoc($curr_total_m, $total_m)) > 0) { $list[] = $curr_total_m;}// 记录总计$list[] = $total_p;//var_dump($list);exit;echo '<table>';foreach($list as $line => $row) { $row = format_data($row); echo '<tr class="cells" onmouseover="this.className=\'cells2\'" onmouseout="this.className=\'cells\'">'; foreach($row as $k => $v) { if($k == 'num') $v = $v ? $v : $line; echo '<td><center><font size=2>', $v, '</font></center></td>'; } echo '</tr>';}echo '</table>';// 修正数据状态function format_data($row) { $row['tuisuan_status'] = !$row['tuisuan'] || $row['tuisuan'] <= $row['item_budget'] ? 'D' : 'F'; $row['current_status'] = !$row['item_current_real'] || $row['item_current_real'] <= $row['item_current_predict'] ? 'D' : 'F'; return $row;}// 进行数据统计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); PHP 5.4.11 and PHP 5.3.21 released! php python which is better? php如何实现文件夹监听 怎么取出 <33> 中间的这个 33值? 急...读取时只想读取文字不想读取出图片 连接策略与搜索引擎优化 我下载的一个程序 数据库版本: 4.0.22,我的是5.0.27 为什么读取出来的数据是乱码呀?? PHP 文件解密问题 特菜的问题:怎么用php编译程序 怎样获得上一页的URL? 咨询个问题,记得有种图片,汇集了各种语言或者技术的技巧 如何截取文件名?
-- 表的结构 `budgetcontrol`
--CREATE TABLE IF NOT EXISTS `budgetcontrol` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`num` int(4) NOT NULL,
`localcation` varchar(20) NOT NULL,
`project` varchar(20) NOT NULL,
`project_category` varchar(20) NOT NULL,
`code` varchar(50) CHARACTER SET utf8 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) CHARACTER SET utf8 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=gb2312 AUTO_INCREMENT=232 ;--
-- 转存表中的数据 `budgetcontrol`
--INSERT INTO `budgetcontrol` (`id`, `num`, `localcation`, `project`, `project_category`, `code`, `item_real`, `item_budget`, `item_current_predict`, `item_current_real`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `department`, `comment`, `tuisuan`, `tuisuan_status`, `current_status`) VALUES
(1, 1, '国内', '差旅费(国内)', '', '5101060201', 1.2, 2.8, 2.5, NULL, NULL, NULL, 2.5, 4.5, 4.5, NULL, 'pu', NULL, 2.3, 0, 0),
(2, 2, '国内', '培训费(国内)', '', '5101060301', 0.1, 0.3, 0.3, NULL, NULL, NULL, 0.3, 0.3, 0.3, NULL, 'pu', NULL, 0.2, 0, 0),
(6, 6, '国内', '物流费', '空运费', '5101040304/6601011602', 24.6, 25.2, 26.0, 64.8, 26.0, 64.8, 26.0, 26.0, 26.0, NULL, 'pu', '郑州日产增产空运费(29K),瑞意泰克紧急空运费(29.7K)年末海关查验采取空运对应费(6K)', 33.8, 0, 0),
(7, 7, '国内', '物流费', '海运费', '5101040303/660101160', 18.5, 21.6, 21.0, 21.0, 15.9, 21.0, 21.0, 21.0, 21.0, NULL, 'pu', NULL, 20.0, 0, 0),
(8, 8, '国内', '物流费', '杂费', '5101040399', 2.3, 3.7, 5.0, 0.8, 0.2, 0.8, 5.0, 5.0, 5.0, NULL, 'pu', NULL, 3.2, 0, 0),
(3, 3, '国内', '业务招待费', '', '51010604', 0.0, 0.6, 0.2, NULL, NULL, NULL, 0.2, 1.0, 1.0, NULL, 'pu', NULL, 0.4, 0, 0),
(4, 4, '国内', '办公费', '', '5101060601/602/699', 0.3, 0.3, 0.3, 0.2, 0.4, 0.2, 0.3, 0.3, 0.3, 0.0, 'pu', '', 0.3, 0, 0),
(5, 5, '国内', '消耗品', '', '51010607', 1.2, 1.0, 1.0, NULL, NULL, NULL, 1.0, 1.0, 1.0, NULL, 'pu', '', 1.0, 0, 0),
(15, 11, '海外', '出张旅费(海外)', '', NULL, NULL, 2.7, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0),
(16, 12, '海外', '培训费(海外)', '', NULL, NULL, 1.9, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0),
(17, 13, '海外', '技师派遣', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'pu', NULL, NULL, 0, 0);
$db_server = "localhost";
$db_login = "root";
$db_password = "123456";
$db_name = "q";
$link = mysql_connect($db_server, $db_login, $db_password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db($db_name, $link);
mysql_query("set names 'gb2312'");$sql = "SELECT `num`,`localcation`,`project`,`project_category`,`code`,`item_real`,`item_budget`,`item_current_predict`,`item_current_real`,`item1`,`item2`,`item3`,`item4`,
`item5`,`item6`,`tuisuan`,`tuisuan_status`,`current_status`,`comment`,`id`";
$sql .= "FROM `budgetcontrol` ".$str."WHERE `department` = 'pu' order by `num` asc";
$result = mysql_query($sql, $link);
// 初始化统计和小计的数组
$total_p = array();
$total_c = array();
$total_m = array();// 记录上一个项目的数据
$last_project = array();
$last_localcation =array();// 记录显示的列表
$list = array();// 初始化统计和小计需要统计的字段
while($row = mysql_fetch_field($result)) {
// 当然你可以手工写,无所谓
if($row->type == 'real') {
$total_p[$row->name] = null;
$total_c[$row->name] = null;
$total_m[$row->name] = null; } else {
$total_p[$row->name] = '';
$total_c[$row->name] = '';
$total_m[$row->name] = ''; }
// 为了显示方便
$list[0][$row->name] = $row->name;
}$total_p['localcation'] = "可控费用合计";$total_m['project'] = "费用小计";$total_c['project_category'] = "小计";// 初始化当前统计内容数组
$curr_total_c = $total_c;
$curr_total_m = $total_m;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_category'] = $total_c['project_category'];
$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($curr_total_c, $total_c)) > 0) {
$curr_total_c['num'] = count($list);
$list[] = $curr_total_c;
$curr_total_c = $total_c;
}
// 重置上一记录
$last_project = $row;
}
}
if(empty($last_localcation)) {
$last_localcation = $row;
} else {
if($last_localcation['localcation'] == $row['localcation']) {
// 初次记录
if(!$curr_total_m['localcation']) {
$curr_total_m['localcation'] = $row['localcation'];
$curr_total_m['project'] = $row['localcation'].$total_m['project'];
$curr_total_m = total_run($curr_total_m, $last_localcation);
}
// 记录小计
$curr_total_m = total_run($curr_total_m, $row);
} else {
// 添加到数组
if(count(array_diff_assoc($curr_total_m, $total_m)) > 0) {
$curr_total_m['num'] = count($list);
$list[] = $curr_total_m;
$curr_total_m = $total_m;
}
// 重置上一记录
$last_localcation = $row;
}
}
// 总计
$total_p = total_run($total_p, $row);
// 记录内容
$list[] = $row;
}// 最后判断一次是否有小计
if(count(array_diff_assoc($curr_total_c, $total_c)) > 0) {
$list[] = $curr_total_c;
}
if(count(array_diff_assoc($curr_total_m, $total_m)) > 0) {
$list[] = $curr_total_m;
}// 记录总计
$list[] = $total_p;//var_dump($list);exit;echo '<table>';
foreach($list as $line => $row) {
$row = format_data($row);
echo '<tr class="cells" onmouseover="this.className=\'cells2\'" onmouseout="this.className=\'cells\'">';
foreach($row as $k => $v) {
if($k == 'num') $v = $v ? $v : $line;
echo '<td><center><font size=2>', $v, '</font></center></td>';
}
echo '</tr>';
}echo '</table>';// 修正数据状态
function format_data($row) {
$row['tuisuan_status'] = !$row['tuisuan'] || $row['tuisuan'] <= $row['item_budget'] ? 'D' : 'F';
$row['current_status'] = !$row['item_current_real'] || $row['item_current_real'] <= $row['item_current_predict'] ? 'D' : 'F';
return $row;
}// 进行数据统计
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);