1、创建物流库存表。sql语句:
CREATE TABLE IF NOT EXISTS `emws_materials` (`id` mediumint(8) unsigned NOT NULL auto_increment,`name` varchar(60) NOT NULL,`modulus` varchar(60) NOT NULL,`stock_number` smallint(5) unsigned NOT NULL default ‘0’,`stock_in` smallint(5) unsigned NOT NULL default ‘0’,`stock_out` smallint(5) unsigned NOT NULL default ‘0’,`safe_day` smallint(5) unsigned NOT NULL default ‘0’,`intent_day` smallint(5) unsigned NOT NULL default ‘0’,`is_buy` tinyint(1) unsigned NOT NULL default ‘1’,`buy_url` varchar(60) NOT NULL,`price` decimal(10,2) NOT NULL,`weight` smallint(5) unsigned NOT NULL default ‘0’,`img` varchar(60) NOT NULL,`desc_info` varchar(60) NOT NULL,`remark` varchar(60) NOT NULL,`admin_id` smallint(5) unsigned NOT NULL,`update_time` int(10) unsigned NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
2、php程序,materials.php:
1 <?php 2 define('IN_ECS', true); 3 require(dirname(__FILE__) . '/includes/init.php'); 4 include_once(ROOT_PATH . 'includes/cls_image.php'); 5 $image = new cls_image($_CFG['bgcolor']); 6 $exc = new exchange($ecs->table("materials"), $db, 'id', 'name'); 7 $_REQUEST['act']=!empty($_REQUEST['act']) ? $_REQUEST['act']:'list'; 8 admin_priv('stock_alert');//权限:库存数量修改 9 if($_REQUEST['act'] == 'list') 10 { 11 $stock_list=material_list(); 12 $smarty->assign('ur_here', '物料库存列表'); 13 $smarty->assign('stock_list', $stock_list['stock_list']); 14 $smarty->assign('filter', $stock_list['filter']); 15 $smarty->assign('record_count', $stock_list['record_count']); 16 $smarty->assign('page_count', $stock_list['page_count']); 17 18 $smarty->assign('shelf_list', $shelf_list); 19 $smarty->assign('full_page', 1); 20 $smarty->assign('action_link', array('href' => 'goods_stock.php?act=list', 'text' => '商品库存列表')); 21 $smarty->assign('action_link2', array('href' => 'materials.php?act=export', 'text' => '导出采购单')); 22 $smarty->assign('action_link3', array('href' => 'materials.php?act=add', 'text' => '添加物料')); 23 24 $smarty->display('material_list.htm'); 25 } 26 elseif($_REQUEST['act'] == 'add') 27 { 28 $smarty->assign('ur_here', "添加物料"); 29 $smarty->assign('action_link', array('href' => 'materials.php?act=list', 'text' => '返回物料库存列表')); 30 $smarty->assign('form_action', "insert"); 31 32 assign_query_info(); 33 $smarty->display('material_info.htm'); 34 } 35 elseif($_REQUEST['act'] == 'insert') 36 { 37 $material['is_buy'] = isset($_REQUEST['is_buy']) ? intval($_REQUEST['is_buy']) : 1; 38 $material['name'] = isset($_REQUEST['name']) ? trim($_REQUEST['name']) : ''; 39 $material['modulus'] = isset($_REQUEST['modulus']) ? trim($_REQUEST['modulus']) : ''; 40 $material['safe_day'] = isset($_REQUEST['safe_day']) ? intval($_REQUEST['safe_day']) : 0; 41 $material['intent_day']= isset($_REQUEST['intent_day']) ? intval($_REQUEST['intent_day']) : 0; 42 $material['price'] = isset($_REQUEST['price']) ? floatval($_REQUEST['price']) : '0.00'; 43 $material['weight'] = isset($_REQUEST['weight']) ? intval($_REQUEST['weight']) : 0; 44 $material['desc_info'] = isset($_REQUEST['desc_info']) ? trim($_REQUEST['desc_info']) : ''; 45 $material['remark'] = isset($_REQUEST['remark']) ? trim($_REQUEST['remark']) : ''; 46 $material['update_time']= gmtime(); 47 $material['admin_id'] = $_SESSION['admin_id']; 48 49 if(empty($material['name']) || empty($material['modulus']) || empty($material['safe_day']) || empty($material['intent_day'])) 50 { 51 sys_msg('名称、系数、安全天数、目标天数,不能为空或者格式不正确', 1); 52 } 53 54 $is_only = $exc->is_only('name', $material['name']); 55 if (!$is_only) 56 { 57 sys_msg($material['name'].',已存在', 1); 58 } 59 60 /*处理图片*/ 61 $material['img'] = basename($image->upload_image($_FILES['img'],'material')); 62 /*处理URL*/ 63 $material['buy_url'] = sanitize_url($_POST['buy_url']); 64 /*插入数据*/ 65 $db->autoExecute($ecs->table('materials'), $material, 'INSERT', '', 'SILENT'); 66 67 $link[0]['text'] = '继续添加'; 68 $link[0]['href'] = 'materials.php?act=add'; 69 $link[1]['text'] = '返回列表'; 70 $link[1]['href'] = 'materials.php?act=list'; 71 sys_msg('添加成功', 0, $link); 72 } 73 elseif($_REQUEST['act'] == 'updata') 74 { 75 $id = isset($_REQUEST['id']) ? intval($_REQUEST['id']) : 0; 76 $material['is_buy'] = isset($_REQUEST['is_buy']) ? intval($_REQUEST['is_buy']) : 1; 77 $material['name'] = isset($_REQUEST['name']) ? trim($_REQUEST['name']) : ''; 78 $material['modulus'] = isset($_REQUEST['modulus']) ? trim($_REQUEST['modulus']) : ''; 79 $material['safe_day'] = isset($_REQUEST['safe_day']) ? intval($_REQUEST['safe_day']) : 0; 80 $material['intent_day']= isset($_REQUEST['intent_day']) ? intval($_REQUEST['intent_day']) : 0; 81 $material['price'] = isset($_REQUEST['price']) ? floatval($_REQUEST['price']) : '0.00'; 82 $material['weight'] = isset($_REQUEST['weight']) ? intval($_REQUEST['weight']) : 0; 83 $material['desc_info'] = isset($_REQUEST['desc_info']) ? trim($_REQUEST['desc_info']) : ''; 84 $material['remark'] = isset($_REQUEST['remark']) ? trim($_REQUEST['remark']) : ''; 85 $material['update_time']= gmtime(); 86 $material['admin_id'] = $_SESSION['admin_id']; 87 88 if(empty($id)) 89 { 90 sys_msg('ID不能为空', 1); 91 } 92 93 if(empty($material['name']) || empty($material['modulus']) || empty($material['safe_day']) || empty($material['intent_day'])) 94 { 95 sys_msg('名称、系数、安全天数、目标天数,不能为空或者格式不正确', 1); 96 } 97 98 /*处理图片*/ 99 if(!empty($_FILES['img']['name']))100 {101 $material['img'] = basename($image->upload_image($_FILES['img'],'material'));102 }103 /*处理URL*/104 $material['buy_url'] = sanitize_url($_POST['buy_url']);105 /*插入数据*/106 $db->autoExecute($ecs->table('materials'), $material, 'UPDATE', "id = '$id'");107 108 $link[0]['text'] = '继续编辑';109 $link[0]['href'] = 'materials.php?act=edit&id='.$id;110 $link[1]['text'] = '返回列表';111 $link[1]['href'] = 'materials.php?act=list';112 sys_msg('编辑成功', 0, $link);113 }114 elseif ($_REQUEST['act'] =='edit')115 {116 $sql = "SELECT * FROM " .$ecs->table('materials'). " WHERE id='$_REQUEST[id]'";117 $material = $db->GetRow($sql);118 $smarty->assign('ur_here', "编辑物料");119 $smarty->assign('action_link', array('href' => 'materials.php?act=list', 'text' => '返回物料库存列表'));120 $smarty->assign('material', $material);121 $smarty->assign('form_action', 'updata');122 assign_query_info();123 $smarty->display('material_info.htm');124 }125 elseif ($_REQUEST['act'] == 'remove')126 {127 $id = intval($_GET['id']);128 $exc->drop($id);129 $url = 'materials.php?act=query&' . str_replace('act=remove', '', $_SERVER['QUERY_STRING']);130 ecs_header("Location: $url\n");131 exit;132 }133 elseif ($_REQUEST['act'] == 'drop_img')134 {135 $id = isset($_GET['id']) ? intval($_GET['id']) : 0;136 137 $sql = "SELECT img FROM " .$ecs->table('materials'). " WHERE id = '$id'";138 $img_name = $db->getOne($sql);139 140 if (!empty($img_name))141 {142 @unlink(ROOT_PATH . DATA_DIR . '/material/' .$img_name);143 $sql = "UPDATE " .$ecs->table('materials'). " SET img = '' WHERE id = '$id'";144 $db->query($sql);145 }146 $link= array(array('text' => '继续编辑', 'href' => 'materials.php?act=edit&id=' . $id), array('text' => '返回物料库存列表', 'href' => 'materials.php?act=list'));147 sys_msg('图片删除成功', 0, $link);148 } 149 elseif ($_REQUEST['act'] == 'edit_stock_in') //更改入库150 {151 $id = intval($_POST['id']);152 $val = json_str_iconv(trim($_POST['val']));153 /* 检查格式 */154 if(!is_numeric($val) || $val < 0)155 {156 make_json_error(sprintf("格式不正确!", $val));157 }158 159 $exc->edit("stock_in='$val'", $id);160 make_json_result(stripslashes($val));161 }162 elseif ($_REQUEST['act'] == 'edit_stock_out') //更改出库163 {164 $id = intval($_POST['id']);165 $val = json_str_iconv(trim($_POST['val']));166 /* 检查格式 */167 if(!is_numeric($val) || $val < 0)168 {169 make_json_error(sprintf("格式不正确!", $val));170 }171 $sql="SELECT * FROM ".$GLOBALS['ecs']->table('materials')." where id = '".$id."'";172 $material = $GLOBALS['db']->getRow($sql); 173 if($val > $material['stock_in'] + $material['stock_number'])174 {175 make_json_error(sprintf("出库数不能大于现有库存与入库总和!", $val));176 }177 178 $exc->edit("stock_out='$val'", $id);179 make_json_result(stripslashes($val));180 }181 elseif ($_REQUEST['act'] == 'operate') //批量入库/出库182 {183 $sql = "UPDATE " .$ecs->table('materials'). " SET stock_number = stock_number + stock_in - stock_out,stock_out = 0,stock_in = 0,admin_id=$_SESSION[admin_id],update_time = ".gmtime();184 $db->query($sql);185 $link= array(array('text' => '返回物料库存列表', 'href' => 'materials.php?act=list'));186 sys_msg('成功批量入库/出库', 0, $link);187 }188 elseif ($_REQUEST['act'] == 'export') //导出采购单189 {190 include_once('includes/PHPExcel/PHPExcel.php');191 include_once('corlor.php');192 $objPHPExcel = new PHPExcel();193 194 $filename = '物料采购表_'.date("YmdHi",gmtime());195 $objPHPExcel->setActiveSheetIndex(0);196 $objPHPExcel->getActiveSheet()->setTitle($filename);197 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);198 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);199 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);200 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);201 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);202 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);203 $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);204 $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);205 $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);206 $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);207 $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);208 $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);209 $objPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);210 $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);211 $objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);212 $objPHPExcel->getActiveSheet()->getStyle('I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);213 $objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);214 $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);215 $objPHPExcel->setActiveSheetIndex(0)216 ->setCellValue('A1', '物料名称')217 ->setCellValue('B1', '图片') 218 ->setCellValue('C1', '每天用量')219 ->setCellValue('D1', '现有库存')220 ->setCellValue('E1', '周转天数')221 ->setCellValue('F1', '安全库存')222 ->setCellValue('G1', '目标库存')223 ->setCellValue('H1', '建议购买')224 ->setCellValue('I1', '单价')225 ->setCellValue('J1', '实际单价')226 ->setCellValue('K1', '采购链接');227 $i=2;228 $stock_list = material_list(false);229 $arr = $stock_list['stock_list'];230 foreach($arr as $v)231 {232 if($v['img'])233 {234 $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(50);235 $objDrawing = new PHPExcel_Worksheet_Drawing();236 $objDrawing->setName('goods thumb');237 $objDrawing->setDescription('Pgoods thumb');238 $img_path = file_exists('../data/material/'.$v['img']) ? '../data/material/'.$v['img'] : '../images/no_img.jpg';239 $objDrawing->setPath($img_path);240 $objDrawing->setWidth(100);241 $objDrawing->setCoordinates('B'.$i);242 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());243 }244 else245 {246 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, ''); 247 }248 249 $objPHPExcel->setActiveSheetIndex(0)250 ->setCellValue('A'.$i, $v['name'])251 ->setCellValue('C'.$i, $v['day_use'])252 ->setCellValue('D'.$i, $v['stock_number'])253 ->setCellValue('E'.$i, $v['stock_day'])254 ->setCellValue('F'.$i, $v['stock_safe'])255 ->setCellValue('G'.$i, $v['stock_intent'])256 ->setCellValue('H'.$i, $v['proposal_buy'])257 ->setCellValue('I'.$i, $v['price'])258 ->setCellValue('J'.$i, '');259 if($v['stock_safe'] >= $v['stock_number'])260 {261 $objPHPExcel->setActiveSheetIndex(0)->getStyle('D'.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);262 }263 if($v['buy_url'] != 'http://')264 {265 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, '采购链接');266 $objPHPExcel->setActiveSheetIndex(0)->getCell('K'.$i)->getHyperlink()->setUrl($v['buy_url']);267 $objPHPExcel->setActiveSheetIndex(0)->getCell('K'.$i)->getHyperlink()->setTooltip('采购链接');268 $objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);269 $objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.$i)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);270 }271 else272 {273 $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$i, '');274 }275 $objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);276 $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);277 $objPHPExcel->getActiveSheet()->getStyle('C'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);278 $objPHPExcel->getActiveSheet()->getStyle('D'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);279 $objPHPExcel->getActiveSheet()->getStyle('E'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);280 $objPHPExcel->getActiveSheet()->getStyle('F'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);281 $objPHPExcel->getActiveSheet()->getStyle('G'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);282 $objPHPExcel->getActiveSheet()->getStyle('H'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);283 $objPHPExcel->getActiveSheet()->getStyle('I'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);284 $objPHPExcel->getActiveSheet()->getStyle('J'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);285 $objPHPExcel->getActiveSheet()->getStyle('K'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);286 $i++;287 }288 $file_name = $filename.'.xls';289 header('Content-Type: application/vnd.ms-excel');290 header('Content-Disposition: attachment;filename="'.$file_name.'"');291 header('Cache-Control: max-age=0');292 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');293 $objWriter->save('php://output');294 exit;295 }296 elseif ($_REQUEST['act'] == 'query')297 { 298 $stock_list = material_list();299 $smarty->assign('stock_list', $stock_list['stock_list']);300 $smarty->assign('filter', $stock_list['filter']);301 $smarty->assign('record_count', $stock_list['record_count']);302 $smarty->assign('page_count', $stock_list['page_count']);303 make_json_result($smarty->fetch('material_list.htm'), '', array('filter' => $stock_list['filter'], 'page_count' => $stock_list['page_count']));304 }305 306 function material_list($is_pagination = true)307 {308 GLOBAL $ecs,$db;309 $result = get_filter();310 if ($result === false)311 {312 $filter['sort_by'] = empty($_REQUEST['sort_by']) ? 'id' : trim($_REQUEST['sort_by']);313 $filter['sort_order'] = empty($_REQUEST['sort_order']) ? 'desc' : trim($_REQUEST['sort_order']);314 $where = " WHERE 1 = 1 ";315 316 $sql = 'select count(t.id) from '.$ecs->table('materials'). ' as t '.$where;317 318 $filter['record_count'] = $db->getOne($sql);319 320 /* 分页大小 */321 $filter = page_and_size($filter);322 323 $sql = 'select t.*, au.user_name from '.324 $ecs->table('materials').' as t left join '.325 $ecs->table('admin_user')." as au on t.admin_id=au.user_id ".$where.326 ' order by '.$filter['sort_by']." ".$filter['sort_order'];327 328 if ($is_pagination)329 {330 $sql .= " LIMIT " . $filter['start'] . ', ' . $filter['page_size'];331 }332 333 $end_time = strtotime(date("Y-m-d",gmtime()));334 $start_time = $end_time - 7 * 86400;335 $query = "SELECT count(order_id) as total FROM ".$GLOBALS['ecs']->table('order_info')." WHERE synch_time < '".$end_time."' and synch_time >= '".$start_time."'";336 $filter['orders'] = round($GLOBALS['db']->getOne($query) / 7);//7天平均订单数 337 $filter['orders'] = $filter['orders'] ? $filter['orders'] : 1400; 338 set_filter($filter, $sql);339 }340 else341 {342 $sql = $result['sql'];343 $filter = $result['filter'];344 }345 $row = $GLOBALS['db']->getAll($sql);346 347 $orders = $filter['orders'];348 foreach($row as $k=>$val)349 {350 if ($is_pagination == false && $val['is_buy'] == 0) //不购买,不导出351 {352 unset($row[$k]);353 continue;354 }355 $row[$k]['update_time'] = local_date('Y-m-d H:i',$val['update_time']);356 $row[$k]['day_use'] = $day_use = round($orders * $val['modulus'],1);//每日用量357 $row[$k]['stock_day'] = $day_use ? round($val['stock_number'] / $day_use,1) : 0;//周转天数358 $row[$k]['stock_safe'] = round($val['safe_day'] * $day_use,1);//安全库存359 $row[$k]['stock_intent']= $stock_intent = round($val['intent_day'] * $day_use,1);//目标库存360 $row[$k]['proposal_buy']= round($stock_intent - $val['stock_number'],1);//建议购买361 }362 363 $stock_list = array('stock_list' => $row, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count']);364 return $stock_list;365 }366 ?>
View Code
<?phpdefine(‘IN_ECS’, true);require(dirname(__FILE__) . ‘/includes/init.php’);include_once(ROOT_PATH . ‘includes/cls_image.php’);$image = new cls_image($_CFG[‘bgcolor’]);$exc = new exchange($ecs->table(“materials”), $db, ‘id’, ‘name’);$_REQUEST[‘act’]=!empty($_REQUEST[‘act’]) ? $_REQUEST[‘act’]:’list’;admin_priv(‘stock_alert’);//权限:库存数量修改if($_REQUEST[‘act’] == ‘list’){ $stock_list=material_list(); $smarty->assign(‘ur_here’, ‘物料库存列表’); $smarty->assign(‘stock_list’, $stock_list[‘stock_list’]); $smarty->assign(‘filter’, $stock_list[‘filter’]); $smarty->assign(‘record_count’, $stock_list[‘record_count’]); $smarty->assign(‘page_count’, $stock_list[‘page_count’]); $smarty->assign(‘shelf_list’, $shelf_list); $smarty->assign(‘full_page’, 1); $smarty->assign(‘action_link’, array(‘href’ => ‘goods_stock.php?act=list’, ‘text’ => ‘商品库存列表’)); $smarty->assign(‘action_link2’, array(‘href’ => ‘materials.php?act=export’, ‘text’ => ‘导出采购单’)); $smarty->assign(‘action_link3’, array(‘href’ => ‘materials.php?act=add’, ‘text’ => ‘添加物料’)); $smarty->display(‘material_list.htm’);}elseif($_REQUEST[‘act’] == ‘add’){ $smarty->assign(‘ur_here’, “添加物料”); $smarty->assign(‘action_link’, array(‘href’ => ‘materials.php?act=list’, ‘text’ => ‘返回物料库存列表’)); $smarty->assign(‘form_action’, “insert”); assign_query_info(); $smarty->display(‘material_info.htm’);}elseif($_REQUEST[‘act’] == ‘insert’){ $material[‘is_buy’] = isset($_REQUEST[‘is_buy’]) ? intval($_REQUEST[‘is_buy’]) : 1; $material[‘name’] = isset($_REQUEST[‘name’]) ? trim($_REQUEST[‘name’]) : ”; $material[‘modulus’] = isset($_REQUEST[‘modulus’]) ? trim($_REQUEST[‘modulus’]) : ”; $material[‘safe_day’] = isset($_REQUEST[‘safe_day’]) ? intval($_REQUEST[‘safe_day’]) : 0; $material[‘intent_day’]= isset($_REQUEST[‘intent_day’]) ? intval($_REQUEST[‘intent_day’]) : 0; $material[‘price’] = isset($_REQUEST[‘price’]) ? floatval($_REQUEST[‘price’]) : ‘0.00’; $material[‘weight’] = isset($_REQUEST[‘weight’]) ? intval($_REQUEST[‘weight’]) : 0; $material[‘desc_info’] = isset($_REQUEST[‘desc_info’]) ? trim($_REQUEST[‘desc_info’]) : ”; $material[‘remark’] = isset($_REQUEST[‘remark’]) ? trim($_REQUEST[‘remark’]) : ”; $material[‘update_time’]= gmtime(); $material[‘admin_id’] = $_SESSION[‘admin_id’]; if(empty($material[‘name’]) || empty($material[‘modulus’]) || empty($material[‘safe_day’]) || empty($material[‘intent_day’])) { sys_msg(‘名称、系数、安全天数、目标天数,不能为空或者格式不正确’, 1); } $is_only = $exc->is_only(‘name’, $material[‘name’]); if (!$is_only) { sys_msg($material[‘name’].’,已存在’, 1); } /*处理图片*/ $material[‘img’] = basename($image->upload_image($_FILES[‘img’],’material’)); /*处理URL*/ $material[‘buy_url’] = sanitize_url($_POST[‘buy_url’]); /*插入数据*/ $db->autoExecute($ecs->table(‘materials’), $material, ‘INSERT’, ”, ‘SILENT’); $link[0][‘text’] = ‘继续添加’; $link[0][‘href’] = ‘materials.php?act=add’; $link[1][‘text’] = ‘返回列表’; $link[1][‘href’] = ‘materials.php?act=list’; sys_msg(‘添加成功’, 0, $link);}elseif($_REQUEST[‘act’] == ‘updata’){ $id = isset($_REQUEST[‘id’]) ? intval($_REQUEST[‘id’]) : 0; $material[‘is_buy’] = isset($_REQUEST[‘is_buy’]) ? intval($_REQUEST[‘is_buy’]) : 1; $material[‘name’] = isset($_REQUEST[‘name’]) ? trim($_REQUEST[‘name’]) : ”; $material[‘modulus’] = isset($_REQUEST[‘modulus’]) ? trim($_REQUEST[‘modulus’]) : ”; $material[‘safe_day’] = isset($_REQUEST[‘safe_day’]) ? intval($_REQUEST[‘safe_day’]) : 0; $material[‘intent_day’]= isset($_REQUEST[‘intent_day’]) ? intval($_REQUEST[‘intent_day’]) : 0; $material[‘price’] = isset($_REQUEST[‘price’]) ? floatval($_REQUEST[‘price’]) : ‘0.00’; $material[‘weight’] = isset($_REQUEST[‘weight’]) ? intval($_REQUEST[‘weight’]) : 0; $material[‘desc_info’] = isset($_REQUEST[‘desc_info’]) ? trim($_REQUEST[‘desc_info’]) : ”; $material[‘remark’] = isset($_REQUEST[‘remark’]) ? trim($_REQUEST[‘remark’]) : ”; $material[‘update_time’]= gmtime(); $material[‘admin_id’] = $_SESSION[‘admin_id’]; if(empty($id)) { sys_msg(‘ID不能为空’, 1); } if(empty($material[‘name’]) || empty($material[‘modulus’]) || empty($material[‘safe_day’]) || empty($material[‘intent_day’])) { sys_msg(‘名称、系数、安全天数、目标天数,不能为空或者格式不正确’, 1); } /*处理图片*/ if(!empty($_FILES[‘img’][‘name’])) { $material[‘img’] = basename($image->upload_image($_FILES[‘img’],’material’)); } /*处理URL*/ $material[‘buy_url’] = sanitize_url($_POST[‘buy_url’]); /*插入数据*/ $db->autoExecute($ecs->table(‘materials’), $material, ‘UPDATE’, “id = ‘$id'”); $link[0][‘text’] = ‘继续编辑’; $link[0][‘href’] = ‘materials.php?act=edit&id=’.$id; $link[1][‘text’] = ‘返回列表’; $link[1][‘href’] = ‘materials.php?act=list’; sys_msg(‘编辑成功’, 0, $link);}elseif ($_REQUEST[‘act’] ==’edit’){ $sql = “SELECT * FROM ” .$ecs->table(‘materials’). ” WHERE id=’$_REQUEST[id]'”; $material = $db->GetRow($sql); $smarty->assign(‘ur_here’, “编辑物料”); $smarty->assign(‘action_link’, array(‘href’ => ‘materials.php?act=list’, ‘text’ => ‘返回物料库存列表’)); $smarty->assign(‘material’, $material); $smarty->assign(‘form_action’, ‘updata’); assign_query_info(); $smarty->display(‘material_info.htm’);}elseif ($_REQUEST[‘act’] == ‘remove’){ $id = intval($_GET[‘id’]); $exc->drop($id); $url = ‘materials.php?act=query&’ . str_replace(‘act=remove’, ”, $_SERVER[‘QUERY_STRING’]); ecs_header(“Location: $url\n”); exit;}elseif ($_REQUEST[‘act’] == ‘drop_img’){ $id = isset($_GET[‘id’]) ? intval($_GET[‘id’]) : 0; $sql = “SELECT img FROM ” .$ecs->table(‘materials’). ” WHERE id = ‘$id'”; $img_name = $db->getOne($sql); if (!empty($img_name)) { @unlink(ROOT_PATH . DATA_DIR . ‘/material/’ .$img_name); $sql = “UPDATE ” .$ecs->table(‘materials’). ” SET img = ” WHERE id = ‘$id'”; $db->query($sql); } $link= array(array(‘text’ => ‘继续编辑’, ‘href’ => ‘materials.php?act=edit&id=’ . $id), array(‘text’ => ‘返回物料库存列表’, ‘href’ => ‘materials.php?act=list’)); sys_msg(‘图片删除成功’, 0, $link);} elseif ($_REQUEST[‘act’] == ‘edit_stock_in’) //更改入库{ $id = intval($_POST[‘id’]); $val = json_str_iconv(trim($_POST[‘val’])); /* 检查格式 */ if(!is_numeric($val) || $val < 0) { make_json_error(sprintf(“格式不正确!”, $val)); } $exc->edit(“stock_in=’$val'”, $id); make_json_result(stripslashes($val));}elseif ($_REQUEST[‘act’] == ‘edit_stock_out’) //更改出库{ $id = intval($_POST[‘id’]); $val = json_str_iconv(trim($_POST[‘val’])); /* 检查格式 */ if(!is_numeric($val) || $val < 0) { make_json_error(sprintf(“格式不正确!”, $val)); } $sql=”SELECT * FROM “.$GLOBALS[‘ecs’]->table(‘materials’).” where id = ‘”.$id.”‘”; $material = $GLOBALS[‘db’]->getRow($sql); if($val > $material[‘stock_in’] + $material[‘stock_number’]) { make_json_error(sprintf(“出库数不能大于现有库存与入库总和!”, $val)); } $exc->edit(“stock_out=’$val'”, $id); make_json_result(stripslashes($val));}elseif ($_REQUEST[‘act’] == ‘operate’) //批量入库/出库{ $sql = “UPDATE ” .$ecs->table(‘materials’). ” SET stock_number = stock_number + stock_in – stock_out,stock_out = 0,stock_in = 0,admin_id=$_SESSION[admin_id],update_time = “.gmtime(); $db->query($sql); $link= array(array(‘text’ => ‘返回物料库存列表’, ‘href’ => ‘materials.php?act=list’)); sys_msg(‘成功批量入库/出库’, 0, $link);}elseif ($_REQUEST[‘act’] == ‘export’) //导出采购单{ include_once(‘includes/PHPExcel/PHPExcel.php’); include_once(‘corlor.php’); $objPHPExcel = new PHPExcel(); $filename = ‘物料采购表_’.date(“YmdHi”,gmtime()); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle($filename); $objPHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘G’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘H’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘I’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘J’)->setWidth(10); $objPHPExcel->getActiveSheet()->getStyle(‘C1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘D1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘F1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘G1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘H1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘I1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘J1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getColumnDimension(‘K’)->setWidth(10); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue(‘A1’, ‘物料名称’) ->setCellValue(‘B1’, ‘图片’) ->setCellValue(‘C1’, ‘每天用量’) ->setCellValue(‘D1’, ‘现有库存’) ->setCellValue(‘E1’, ‘周转天数’) ->setCellValue(‘F1’, ‘安全库存’) ->setCellValue(‘G1’, ‘目标库存’) ->setCellValue(‘H1’, ‘建议购买’) ->setCellValue(‘I1’, ‘单价’) ->setCellValue(‘J1’, ‘实际单价’) ->setCellValue(‘K1’, ‘采购链接’); $i=2; $stock_list = material_list(false); $arr = $stock_list[‘stock_list’]; foreach($arr as $v) { if($v[‘img’]) { $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(50); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName(‘goods thumb’); $objDrawing->setDescription(‘Pgoods thumb’); $img_path = file_exists(‘../data/material/’.$v[‘img’]) ? ‘../data/material/’.$v[‘img’] : ‘../images/no_img.jpg’; $objDrawing->setPath($img_path); $objDrawing->setWidth(100); $objDrawing->setCoordinates(‘B’.$i); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘B’.$i, ”); } $objPHPExcel->setActiveSheetIndex(0) ->setCellValue(‘A’.$i, $v[‘name’]) ->setCellValue(‘C’.$i, $v[‘day_use’]) ->setCellValue(‘D’.$i, $v[‘stock_number’]) ->setCellValue(‘E’.$i, $v[‘stock_day’]) ->setCellValue(‘F’.$i, $v[‘stock_safe’]) ->setCellValue(‘G’.$i, $v[‘stock_intent’]) ->setCellValue(‘H’.$i, $v[‘proposal_buy’]) ->setCellValue(‘I’.$i, $v[‘price’]) ->setCellValue(‘J’.$i, ”); if($v[‘stock_safe’] >= $v[‘stock_number’]) { $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘D’.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); } if($v[‘buy_url’] != ‘http://’) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘K’.$i, ‘采购链接’); $objPHPExcel->setActiveSheetIndex(0)->getCell(‘K’.$i)->getHyperlink()->setUrl($v[‘buy_url’]); $objPHPExcel->setActiveSheetIndex(0)->getCell(‘K’.$i)->getHyperlink()->setTooltip(‘采购链接’); $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘K’.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘K’.$i)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘K’.$i, ”); } $objPHPExcel->getActiveSheet()->getStyle(‘A’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘B’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘C’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘D’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘E’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘F’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘G’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘H’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘I’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘J’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘K’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $i++; } $file_name = $filename.’.xls’; header(‘Content-Type: application/vnd.ms-excel’); header(‘Content-Disposition: attachment;filename=”‘.$file_name.'”‘); header(‘Cache-Control: max-age=0’); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’); $objWriter->save(‘php://output’); exit;}elseif ($_REQUEST[‘act’] == ‘query’){ $stock_list = material_list(); $smarty->assign(‘stock_list’, $stock_list[‘stock_list’]); $smarty->assign(‘filter’, $stock_list[‘filter’]); $smarty->assign(‘record_count’, $stock_list[‘record_count’]); $smarty->assign(‘page_count’, $stock_list[‘page_count’]); make_json_result($smarty->fetch(‘material_list.htm’), ”, array(‘filter’ => $stock_list[‘filter’], ‘page_count’ => $stock_list[‘page_count’]));}function material_list($is_pagination = true){ GLOBAL $ecs,$db; $result = get_filter(); if ($result === false) { $filter[‘sort_by’] = empty($_REQUEST[‘sort_by’]) ? ‘id’ : trim($_REQUEST[‘sort_by’]); $filter[‘sort_order’] = empty($_REQUEST[‘sort_order’]) ? ‘desc’ : trim($_REQUEST[‘sort_order’]); $where = ” WHERE 1 = 1 “; $sql = ‘select count(t.id) from ‘.$ecs->table(‘materials’). ‘ as t ‘.$where; $filter[‘record_count’] = $db->getOne($sql); /* 分页大小 */ $filter = page_and_size($filter); $sql = ‘select t.*, au.user_name from ‘. $ecs->table(‘materials’).’ as t left join ‘. $ecs->table(‘admin_user’).” as au on t.admin_id=au.user_id “.$where. ‘ order by ‘.$filter[‘sort_by’].” “.$filter[‘sort_order’]; if ($is_pagination) { $sql .= ” LIMIT ” . $filter[‘start’] . ‘, ‘ . $filter[‘page_size’]; } $end_time = strtotime(date(“Y-m-d”,gmtime())); $start_time = $end_time – 7 * 86400; $query = “SELECT count(order_id) as total FROM “.$GLOBALS[‘ecs’]->table(‘order_info’).” WHERE synch_time < ‘”.$end_time.”‘ and synch_time >= ‘”.$start_time.”‘”; $filter[‘orders’] = round($GLOBALS[‘db’]->getOne($query) / 7);//7天平均订单数 $filter[‘orders’] = $filter[‘orders’] ? $filter[‘orders’] : 1400; set_filter($filter, $sql); } else { $sql = $result[‘sql’]; $filter = $result[‘filter’]; } $row = $GLOBALS[‘db’]->getAll($sql); $orders = $filter[‘orders’]; foreach($row as $k=>$val) { if ($is_pagination == false && $val[‘is_buy’] == 0) //不购买,不导出 { unset($row[$k]); continue; } $row[$k][‘update_time’] = local_date(‘Y-m-d H:i’,$val[‘update_time’]); $row[$k][‘day_use’] = $day_use = round($orders * $val[‘modulus’],1);//每日用量 $row[$k][‘stock_day’] = $day_use ? round($val[‘stock_number’] / $day_use,1) : 0;//周转天数 $row[$k][‘stock_safe’] = round($val[‘safe_day’] * $day_use,1);//安全库存 $row[$k][‘stock_intent’]= $stock_intent = round($val[‘intent_day’] * $day_use,1);//目标库存 $row[$k][‘proposal_buy’]= round($stock_intent – $val[‘stock_number’],1);//建议购买 } $stock_list = array(‘stock_list’ => $row, ‘filter’ => $filter, ‘page_count’ => $filter[‘page_count’], ‘record_count’ => $filter[‘record_count’]); return $stock_list;}?>
3、列表页模板文件,material_list.htm:
<?phpdefine(‘IN_ECS’, true);require(dirname(__FILE__) . ‘/includes/init.php’);include_once(ROOT_PATH . ‘includes/cls_image.php’);$image = new cls_image($_CFG[‘bgcolor’]);$exc = new exchange($ecs->table(“materials”), $db, ‘id’, ‘name’);$_REQUEST[‘act’]=!empty($_REQUEST[‘act’]) ? $_REQUEST[‘act’]:’list’;admin_priv(‘stock_alert’);//权限:库存数量修改if($_REQUEST[‘act’] == ‘list’){ $stock_list=material_list(); $smarty->assign(‘ur_here’, ‘物料库存列表’); $smarty->assign(‘stock_list’, $stock_list[‘stock_list’]); $smarty->assign(‘filter’, $stock_list[‘filter’]); $smarty->assign(‘record_count’, $stock_list[‘record_count’]); $smarty->assign(‘page_count’, $stock_list[‘page_count’]); $smarty->assign(‘shelf_list’, $shelf_list); $smarty->assign(‘full_page’, 1); $smarty->assign(‘action_link’, array(‘href’ => ‘goods_stock.php?act=list’, ‘text’ => ‘商品库存列表’)); $smarty->assign(‘action_link2’, array(‘href’ => ‘materials.php?act=export’, ‘text’ => ‘导出采购单’)); $smarty->assign(‘action_link3’, array(‘href’ => ‘materials.php?act=add’, ‘text’ => ‘添加物料’)); $smarty->display(‘material_list.htm’);}elseif($_REQUEST[‘act’] == ‘add’){ $smarty->assign(‘ur_here’, “添加物料”); $smarty->assign(‘action_link’, array(‘href’ => ‘materials.php?act=list’, ‘text’ => ‘返回物料库存列表’)); $smarty->assign(‘form_action’, “insert”); assign_query_info(); $smarty->display(‘material_info.htm’);}elseif($_REQUEST[‘act’] == ‘insert’){ $material[‘is_buy’] = isset($_REQUEST[‘is_buy’]) ? intval($_REQUEST[‘is_buy’]) : 1; $material[‘name’] = isset($_REQUEST[‘name’]) ? trim($_REQUEST[‘name’]) : ”; $material[‘modulus’] = isset($_REQUEST[‘modulus’]) ? trim($_REQUEST[‘modulus’]) : ”; $material[‘safe_day’] = isset($_REQUEST[‘safe_day’]) ? intval($_REQUEST[‘safe_day’]) : 0; $material[‘intent_day’]= isset($_REQUEST[‘intent_day’]) ? intval($_REQUEST[‘intent_day’]) : 0; $material[‘price’] = isset($_REQUEST[‘price’]) ? floatval($_REQUEST[‘price’]) : ‘0.00’; $material[‘weight’] = isset($_REQUEST[‘weight’]) ? intval($_REQUEST[‘weight’]) : 0; $material[‘desc_info’] = isset($_REQUEST[‘desc_info’]) ? trim($_REQUEST[‘desc_info’]) : ”; $material[‘remark’] = isset($_REQUEST[‘remark’]) ? trim($_REQUEST[‘remark’]) : ”; $material[‘update_time’]= gmtime(); $material[‘admin_id’] = $_SESSION[‘admin_id’]; if(empty($material[‘name’]) || empty($material[‘modulus’]) || empty($material[‘safe_day’]) || empty($material[‘intent_day’])) { sys_msg(‘名称、系数、安全天数、目标天数,不能为空或者格式不正确’, 1); } $is_only = $exc->is_only(‘name’, $material[‘name’]); if (!$is_only) { sys_msg($material[‘name’].’,已存在’, 1); } /*处理图片*/ $material[‘img’] = basename($image->upload_image($_FILES[‘img’],’material’)); /*处理URL*/ $material[‘buy_url’] = sanitize_url($_POST[‘buy_url’]); /*插入数据*/ $db->autoExecute($ecs->table(‘materials’), $material, ‘INSERT’, ”, ‘SILENT’); $link[0][‘text’] = ‘继续添加’; $link[0][‘href’] = ‘materials.php?act=add’; $link[1][‘text’] = ‘返回列表’; $link[1][‘href’] = ‘materials.php?act=list’; sys_msg(‘添加成功’, 0, $link);}elseif($_REQUEST[‘act’] == ‘updata’){ $id = isset($_REQUEST[‘id’]) ? intval($_REQUEST[‘id’]) : 0; $material[‘is_buy’] = isset($_REQUEST[‘is_buy’]) ? intval($_REQUEST[‘is_buy’]) : 1; $material[‘name’] = isset($_REQUEST[‘name’]) ? trim($_REQUEST[‘name’]) : ”; $material[‘modulus’] = isset($_REQUEST[‘modulus’]) ? trim($_REQUEST[‘modulus’]) : ”; $material[‘safe_day’] = isset($_REQUEST[‘safe_day’]) ? intval($_REQUEST[‘safe_day’]) : 0; $material[‘intent_day’]= isset($_REQUEST[‘intent_day’]) ? intval($_REQUEST[‘intent_day’]) : 0; $material[‘price’] = isset($_REQUEST[‘price’]) ? floatval($_REQUEST[‘price’]) : ‘0.00’; $material[‘weight’] = isset($_REQUEST[‘weight’]) ? intval($_REQUEST[‘weight’]) : 0; $material[‘desc_info’] = isset($_REQUEST[‘desc_info’]) ? trim($_REQUEST[‘desc_info’]) : ”; $material[‘remark’] = isset($_REQUEST[‘remark’]) ? trim($_REQUEST[‘remark’]) : ”; $material[‘update_time’]= gmtime(); $material[‘admin_id’] = $_SESSION[‘admin_id’]; if(empty($id)) { sys_msg(‘ID不能为空’, 1); } if(empty($material[‘name’]) || empty($material[‘modulus’]) || empty($material[‘safe_day’]) || empty($material[‘intent_day’])) { sys_msg(‘名称、系数、安全天数、目标天数,不能为空或者格式不正确’, 1); } /*处理图片*/ if(!empty($_FILES[‘img’][‘name’])) { $material[‘img’] = basename($image->upload_image($_FILES[‘img’],’material’)); } /*处理URL*/ $material[‘buy_url’] = sanitize_url($_POST[‘buy_url’]); /*插入数据*/ $db->autoExecute($ecs->table(‘materials’), $material, ‘UPDATE’, “id = ‘$id'”); $link[0][‘text’] = ‘继续编辑’; $link[0][‘href’] = ‘materials.php?act=edit&id=’.$id; $link[1][‘text’] = ‘返回列表’; $link[1][‘href’] = ‘materials.php?act=list’; sys_msg(‘编辑成功’, 0, $link);}elseif ($_REQUEST[‘act’] ==’edit’){ $sql = “SELECT * FROM ” .$ecs->table(‘materials’). ” WHERE id=’$_REQUEST[id]'”; $material = $db->GetRow($sql); $smarty->assign(‘ur_here’, “编辑物料”); $smarty->assign(‘action_link’, array(‘href’ => ‘materials.php?act=list’, ‘text’ => ‘返回物料库存列表’)); $smarty->assign(‘material’, $material); $smarty->assign(‘form_action’, ‘updata’); assign_query_info(); $smarty->display(‘material_info.htm’);}elseif ($_REQUEST[‘act’] == ‘remove’){ $id = intval($_GET[‘id’]); $exc->drop($id); $url = ‘materials.php?act=query&’ . str_replace(‘act=remove’, ”, $_SERVER[‘QUERY_STRING’]); ecs_header(“Location: $url\n”); exit;}elseif ($_REQUEST[‘act’] == ‘drop_img’){ $id = isset($_GET[‘id’]) ? intval($_GET[‘id’]) : 0; $sql = “SELECT img FROM ” .$ecs->table(‘materials’). ” WHERE id = ‘$id'”; $img_name = $db->getOne($sql); if (!empty($img_name)) { @unlink(ROOT_PATH . DATA_DIR . ‘/material/’ .$img_name); $sql = “UPDATE ” .$ecs->table(‘materials’). ” SET img = ” WHERE id = ‘$id'”; $db->query($sql); } $link= array(array(‘text’ => ‘继续编辑’, ‘href’ => ‘materials.php?act=edit&id=’ . $id), array(‘text’ => ‘返回物料库存列表’, ‘href’ => ‘materials.php?act=list’)); sys_msg(‘图片删除成功’, 0, $link);} elseif ($_REQUEST[‘act’] == ‘edit_stock_in’) //更改入库{ $id = intval($_POST[‘id’]); $val = json_str_iconv(trim($_POST[‘val’])); /* 检查格式 */ if(!is_numeric($val) || $val < 0) { make_json_error(sprintf(“格式不正确!”, $val)); } $exc->edit(“stock_in=’$val'”, $id); make_json_result(stripslashes($val));}elseif ($_REQUEST[‘act’] == ‘edit_stock_out’) //更改出库{ $id = intval($_POST[‘id’]); $val = json_str_iconv(trim($_POST[‘val’])); /* 检查格式 */ if(!is_numeric($val) || $val < 0) { make_json_error(sprintf(“格式不正确!”, $val)); } $sql=”SELECT * FROM “.$GLOBALS[‘ecs’]->table(‘materials’).” where id = ‘”.$id.”‘”; $material = $GLOBALS[‘db’]->getRow($sql); if($val > $material[‘stock_in’] + $material[‘stock_number’]) { make_json_error(sprintf(“出库数不能大于现有库存与入库总和!”, $val)); } $exc->edit(“stock_out=’$val'”, $id); make_json_result(stripslashes($val));}elseif ($_REQUEST[‘act’] == ‘operate’) //批量入库/出库{ $sql = “UPDATE ” .$ecs->table(‘materials’). ” SET stock_number = stock_number + stock_in – stock_out,stock_out = 0,stock_in = 0,admin_id=$_SESSION[admin_id],update_time = “.gmtime(); $db->query($sql); $link= array(array(‘text’ => ‘返回物料库存列表’, ‘href’ => ‘materials.php?act=list’)); sys_msg(‘成功批量入库/出库’, 0, $link);}elseif ($_REQUEST[‘act’] == ‘export’) //导出采购单{ include_once(‘includes/PHPExcel/PHPExcel.php’); include_once(‘corlor.php’); $objPHPExcel = new PHPExcel(); $filename = ‘物料采购表_’.date(“YmdHi”,gmtime()); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle($filename); $objPHPExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘G’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘H’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘I’)->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension(‘J’)->setWidth(10); $objPHPExcel->getActiveSheet()->getStyle(‘C1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘D1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘F1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘G1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘H1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘I1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle(‘J1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getColumnDimension(‘K’)->setWidth(10); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue(‘A1’, ‘物料名称’) ->setCellValue(‘B1’, ‘图片’) ->setCellValue(‘C1’, ‘每天用量’) ->setCellValue(‘D1’, ‘现有库存’) ->setCellValue(‘E1’, ‘周转天数’) ->setCellValue(‘F1’, ‘安全库存’) ->setCellValue(‘G1’, ‘目标库存’) ->setCellValue(‘H1’, ‘建议购买’) ->setCellValue(‘I1’, ‘单价’) ->setCellValue(‘J1’, ‘实际单价’) ->setCellValue(‘K1’, ‘采购链接’); $i=2; $stock_list = material_list(false); $arr = $stock_list[‘stock_list’]; foreach($arr as $v) { if($v[‘img’]) { $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(50); $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName(‘goods thumb’); $objDrawing->setDescription(‘Pgoods thumb’); $img_path = file_exists(‘../data/material/’.$v[‘img’]) ? ‘../data/material/’.$v[‘img’] : ‘../images/no_img.jpg’; $objDrawing->setPath($img_path); $objDrawing->setWidth(100); $objDrawing->setCoordinates(‘B’.$i); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘B’.$i, ”); } $objPHPExcel->setActiveSheetIndex(0) ->setCellValue(‘A’.$i, $v[‘name’]) ->setCellValue(‘C’.$i, $v[‘day_use’]) ->setCellValue(‘D’.$i, $v[‘stock_number’]) ->setCellValue(‘E’.$i, $v[‘stock_day’]) ->setCellValue(‘F’.$i, $v[‘stock_safe’]) ->setCellValue(‘G’.$i, $v[‘stock_intent’]) ->setCellValue(‘H’.$i, $v[‘proposal_buy’]) ->setCellValue(‘I’.$i, $v[‘price’]) ->setCellValue(‘J’.$i, ”); if($v[‘stock_safe’] >= $v[‘stock_number’]) { $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘D’.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); } if($v[‘buy_url’] != ‘http://’) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘K’.$i, ‘采购链接’); $objPHPExcel->setActiveSheetIndex(0)->getCell(‘K’.$i)->getHyperlink()->setUrl($v[‘buy_url’]); $objPHPExcel->setActiveSheetIndex(0)->getCell(‘K’.$i)->getHyperlink()->setTooltip(‘采购链接’); $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘K’.$i)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); $objPHPExcel->setActiveSheetIndex(0)->getStyle(‘K’.$i)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); } else { $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘K’.$i, ”); } $objPHPExcel->getActiveSheet()->getStyle(‘A’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘B’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘C’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘D’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘E’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘F’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘G’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘H’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘I’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘J’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle(‘K’.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $i++; } $file_name = $filename.’.xls’; header(‘Content-Type: application/vnd.ms-excel’); header(‘Content-Disposition: attachment;filename=”‘.$file_name.'”‘); header(‘Cache-Control: max-age=0’); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’); $objWriter->save(‘php://output’); exit;}elseif ($_REQUEST[‘act’] == ‘query’){ $stock_list = material_list(); $smarty->assign(‘stock_list’, $stock_list[‘stock_list’]); $smarty->assign(‘filter’, $stock_list[‘filter’]); $smarty->assign(‘record_count’, $stock_list[‘record_count’]); $smarty->assign(‘page_count’, $stock_list[‘page_count’]); make_json_result($smarty->fetch(‘material_list.htm’), ”, array(‘filter’ => $stock_list[‘filter’], ‘page_count’ => $stock_list[‘page_count’]));}function material_list($is_pagination = true){ GLOBAL $ecs,$db; $result = get_filter(); if ($result === false) { $filter[‘sort_by’] = empty($_REQUEST[‘sort_by’]) ? ‘id’ : trim($_REQUEST[‘sort_by’]); $filter[‘sort_order’] = empty($_REQUEST[‘sort_order’]) ? ‘desc’ : trim($_REQUEST[‘sort_order’]); $where = ” WHERE 1 = 1 “; $sql = ‘select count(t.id) from ‘.$ecs->table(‘materials’). ‘ as t ‘.$where; $filter[‘record_count’] = $db->getOne($sql); /* 分页大小 */ $filter = page_and_size($filter); $sql = ‘select t.*, au.user_name from ‘. $ecs->table(‘materials’).’ as t left join ‘. $ecs->table(‘admin_user’).” as au on t.admin_id=au.user_id “.$where. ‘ order by ‘.$filter[‘sort_by’].” “.$filter[‘sort_order’]; if ($is_pagination) { $sql .= ” LIMIT ” . $filter[‘start’] . ‘, ‘ . $filter[‘page_size’]; } $end_time = strtotime(date(“Y-m-d”,gmtime())); $start_time = $end_time – 7 * 86400; $query = “SELECT count(order_id) as total FROM “.$GLOBALS[‘ecs’]->table(‘order_info’).” WHERE synch_time < ‘”.$end_time.”‘ and synch_time >= ‘”.$start_time.”‘”; $filter[‘orders’] = round($GLOBALS[‘db’]->getOne($query) / 7);//7天平均订单数 $filter[‘orders’] = $filter[‘orders’] ? $filter[‘orders’] : 1400; set_filter($filter, $sql); } else { $sql = $result[‘sql’]; $filter = $result[‘filter’]; } $row = $GLOBALS[‘db’]->getAll($sql); $orders = $filter[‘orders’]; foreach($row as $k=>$val) { if ($is_pagination == false && $val[‘is_buy’] == 0) //不购买,不导出 { unset($row[$k]); continue; } $row[$k][‘update_time’] = local_date(‘Y-m-d H:i’,$val[‘update_time’]); $row[$k][‘day_use’] = $day_use = round($orders * $val[‘modulus’],1);//每日用量 $row[$k][‘stock_day’] = $day_use ? round($val[‘stock_number’] / $day_use,1) : 0;//周转天数 $row[$k][‘stock_safe’] = round($val[‘safe_day’] * $day_use,1);//安全库存 $row[$k][‘stock_intent’]= $stock_intent = round($val[‘intent_day’] * $day_use,1);//目标库存 $row[$k][‘proposal_buy’]= round($stock_intent – $val[‘stock_number’],1);//建议购买 } $stock_list = array(‘stock_list’ => $row, ‘filter’ => $filter, ‘page_count’ => $filter[‘page_count’], ‘record_count’ => $filter[‘record_count’]); return $stock_list;}?>
4、添加物料模板页,material_info.htm:
{include file=”pageheader.htm”}{insert_scripts files=”../js/utils.js,listtable.js,validator.js”}<script type=”text/javascript” src=”../js/calendar.php?lang={$cfg_lang}”></script><link href=”../js/calendar/calendar.css” rel=”stylesheet” type=”text/css” /><div class=”main-div”><form method=”post” action=”materials.php” name=”theForm” enctype=”multipart/form-data” onsubmit=”return check()”><table cellspacing=”1″ cellpadding=”3″ width=”100%”> <tr> <td class=”label”>物料名称:</td> <td><input type=”text” name=”name” value=”{$material.name}” size=”40″ />{$lang.require_field}</td> </tr> <tr> <td class=”label”>系数:</td> <td><input type=”text” name=”modulus” value=”{$material.modulus}” size=”20″ />{$lang.require_field}</td> </tr> <tr> <td class=”label”>单价:</td> <td><input type=”text” name=”price” value=”{$material.price}” size=”20″ /></td> </tr> <tr> <td class=”label”>安全库存天数:</td> <td><input type=”text” name=”safe_day” value=”{$material.safe_day}” size=”20″ />{$lang.require_field}</td> </tr> <tr> <td class=”label”>目标库存天数:</td> <td><input type=”text” name=”intent_day” value=”{$material.intent_day}” size=”20″ />{$lang.require_field}</td> </tr> <tr> <td class=”label”>是否参与购买:</td> <td><select name=”is_buy” id=”is_buy”> <option value=”1″ {if $material.is_buy == 1}selected=”selected”{/if}>是</option> <option value=”0″ {if $material.is_buy == 0}selected=”selected”{/if}>否</option> </select><br /><span class=”notice-span”>否表示不参与导出采购单</span></td> </tr> <tr> <td class=”label”>购买链接:</td> <td><textarea name=”buy_url” rows=”4″ cols=”80″>{$material.buy_url}</textarea></td> </tr> <tr> <td class=”label”>图片:</td> <td><input type=”file” name=”img” id=”img” size=”45″> {if $material.img neq “”}<input type=”button” value=”删除图片” onclick=”if (confirm(‘确定删除图片?’))location.href=’materials.php?act=drop_img&id={$material.id}'”> <br /><span class=”notice-span”>你已经上传过图片。再次上传时将覆盖原图片!</span> <br /><img src=”../data/material/{$material.img}” height=”50″ />{/if}</td> </tr> <tr> <td class=”label”>重量:</td> <td><input type=”text” name=”weight” value=”{$material.weight}” size=”20″ /><span class=”notice-span”>单位:克</span></td> </tr> <tr> <td class=”label”>物料描述:</td> <td><textarea name=”desc_info” rows=”8″ cols=”80″>{$material.desc_info}</textarea></td> </tr> <tr> <td class=”label”>备注:</td> <td><input type=”text” name=”remark” value=”{$material.remark}” size=”40″ /></td> </tr> <tr> <td class=”label”></td> <td> <input type=”submit” value=”{$lang.button_submit}” class=”button” /> <input type=”reset” value=”{$lang.button_reset}” class=”button” /> <input type=”hidden” name=”act” value=”{$form_action}” /> <input type=”hidden” name=”id” value=”{$material.id}” /> </td> </tr></table></form></div><script type=”text/javascript” language=”javascript”>document.forms[‘theForm’].elements[‘name’].focus();function check(){ validator = new Validator(“theForm”); validator.required(“name”, ‘物料名称 不能为空’); validator.isNumber(“modulus”, ‘系数 不能为空或者格式不正确’, true); validator.isNumber(“safe_day”, ‘安全库存天数 不能为空或者格式不正确’, true); validator.isNumber(“intent_day”, ‘目标库存天数 不能为空或者格式不正确’, true); validator.isNumber(“price”, ‘价格 格式不正确’, false); validator.isNumber(“weight”, ‘重量 格式不正确’, false); return validator.passed();}</script>{include file=”pagefooter.htm”}
5、导出采购表,截图如下:
冬天已经到来,春天还会远吗?