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 $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
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 {
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 {
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 = '.$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) //不购买，不导出
{