ecshop物料库藏管理

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