ecshop添加物料库存管理功能

2016-09-07 22:02 来源:www.chinab4c.com 作者: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:

<?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) //www.zuimoban.com
        {
            $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
    { //www.zuimoban.com
        $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;
}
?>


(责任编辑:chinab4c)