数据分类 本帖最后由 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); 谷歌浏览器下的JS解析XML代码,谁能给一个 PHP里有没有像htmlspecialchars()那样的把要用在LIKE中的字符串中的通配符转换成转义字符的函数? 用phpmailer怎么样发送整个页面的内容呢? MYAQL数据库的远程连接 想找份PHP的工作,在深圳 csdn树菜单php+mysql版(下载地址) 毕设紧急求救,一个翻页的问题 php新手请教计算总和问题! GB转BIG5的程序,不过出了点问题,提示php出错,大家帮忙看看 数组的检索 咨询个问题,记得有种图片,汇集了各种语言或者技术的技巧 如何截取文件名?
-- 表的结构 `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);