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 )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 )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