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：
<?php
define('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：
<?php
define('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、導出采購表，截圖如下：
