ecshop物料庫存管理

1、建立物流庫存表。 sql語句：

如果不存在`emws_materials`則建立表格（
`id`mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL,
`modulus` varchar(60) NOT NULL,` smallint (5) unsigned NOT NULL 預設'0',
`stock_in`smallint(5) unsigned NOT NULL 預設'0',
`stock_out`smallint(5) unsigned NOT NULL 預設'0',
`safe_day`fint 5 ) unsigned NOT NULL 預設'0',
`intent_day`smallint(5) unsigned NOT NULL 預設'0',
`is_buy`tinyint(1) unsigned NOT NULL 預設'1'​​,
`buy_url` char(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 = MyISAM 預設字元集=utf8;

2、php程序，materials.php：

雷雷 查看程式碼

define('IN_ECS', true);
require(dirname(__FILE__) . '/includes/init.php');
include_once(ROOT_PATH . 'includes/cls_image.php'); 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 ('filter',       $stock_list['filter']);
    $smarty->assign('record_count', $stock_list['record_count)] 'page_count',   $stock_list[ 'page_count']);
    
    $smarty->assign('shelf_list',     $shelf_list);
  $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'] == '
}
elseif($_REQUEST['act'] == ' add')
{
    $smarty->assign('ur_here',      "新增補充");
    $smarty->assign('action_link', array('href' =>; 'materials.php?act=list', 'text' => '返回庫存庫存清單'));
    $smarty->assign('form_action', "insert");
    allocate_query_info();
mart display('material_info.htm');
}
elseif ($_REQUEST['act'] == '插入')
{
    $material['is_buy'] '   = isset($_REQUEST['is_buyQUEST[) ? intval($_REQUEST['is_buy']) : 1;
    $material['name']      = isset($_REQUEST['name']) ?修剪($_REQUEST['name']) : ''; ['模數']   = isset($_REQUEST['模數']) ?修剪($_REQUEST['模數']) : '';
    $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']) ?修剪($_REQUEST['desc_info']) : '';material  ['remark']    = isset($_REQUEST['remark']) ?修剪($_REQUEST['remark']) : '';
    $material['update_time']= gmtime();
']   = $_SESSION['admin_id'];
    
    if(empty( $material['name']) || 空($material['modulus']) || 空($material['safe_day']) | | 空白($material['intent_day']))
    {
        sys_msg( '名稱、係數、安全天數、目標天數，不能為空白或格式不正確」 >is_only('名稱', $material['名稱']);
    if (!$is_only)
    {
        sys_msg($material['name'].'，已存在', 1);
/erials... ] = 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'] == '更新資料')
{
    $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']) ?修剪($_REQUEST['name']) : ''; ['模數']   = isset($_REQUEST['模數']) ?修剪($_REQUEST['模數']) : '';
    $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']) ?修剪($_REQUEST['desc_info']) : '';material  ['remark']    = isset($_REQUEST['remark']) ?修剪($_REQUEST['remark']) : '';
    $material['update_time']= gmtime();
']   = $_SESSION['admin_id'];
    
    if(empty( $id))
    {
    
    if(空($material['name' ) ]) || 空($material['modulus']) || 空($material['safe_day']) || 空($material['intent_day']))
    {
       安全天數、目標天數，為空或格式不正確', 1);
    }
     /*處理圖片*/
     if(!empty($_FILES['img']['name']))
    {$up _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]['text'] = '返回清單';
   $link[ 1]  $link[ 1] [ 'href'] = 'materials.php?act=list';
    sys_msg('編輯成功', 0, $link);
}
elseif ($_REQUEST['act'] =='編輯')
{
$sql = "SELECT * FROM " .$ecs->table('材質'). " WHERE";
    $material = $db->GetRow($sql);
    $smarty->assign('ur_here',     "編輯表單對"); ' => 'materials.php?act=list', 'text' => '返回庫存庫存清單'));
    $smarty->assign('material',    $material);
    $ smarty->assign(' form_action', 'updata');
    allocate_query_info();
    $smarty->display('material_info.htm');
}
elsecom = intval($_GET['id']);
    $exc->drop($id);
    $url = 'materials.php?act=query&' . str_replace('act=remove', '', $_SERVER['QUERY_STRING']);
    ecs_header("位置：$urln");
    exit;
}
elsense; ')
{
    $id = isset($_GET['id']) ？ intval($_GET['id']) : 0;
    $sql = "SELECT img FROM " .$ecs->table('materials->table('materials. '）。 " WHERE id = '$id'";
    $img_name = $db->getOne($sql);
    if (!empty($img_name))
    {
, D.D. $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']));🠎. (!is_numeric($val) || $val     {
        make_json_error(sprintf("格式不正確！",  $val =$ $ val' ", $id);
    make_json_result(stripslashes($val));
}
elseif ($_REQUEST['act'] == 'edit_stock_out') //更改出庫
{🜜p) $ 'id']);
    $val = json_str_iconv(trim($_POST['val']));
    /* 檢查格式*/
    if(!is_numeric($val) || $val0)🜠 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);
   '] == '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); 返回物料庫存清單', 'href' => 'materials.php?act=list'));
    sys_msg('成功批次入庫/出庫', 0, $link);
}
elseif ($_REQUEST['act'] == 'export') //匯出購買單
{
    include_once('includes/PHPExcel/PHPExcel.php');
    include_once('corlor.php');
    include_once('corlor.php');
    include_once('corlor.php');
    include_once('corlor.php');
 '物料採購表_'.date("YmdHi",gmtime());
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()-pcel>cr8()-ccel ()->getColumnDimension('A')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
 Pet-$ 。 ')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
    $objPHPExcel->geteet(Dicel setWidth(10);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
    $objPHPExcel->getActiveSheet)-> ;
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
    $objPHPExcel->getActiveSheet()->getStyle-C1'pobjPHPExcel->getActiveSheet()->getStyle-C1'p ::HORIZONTAL_RIGHT);
    $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizo​​ntal(PHPExcel_Style_AlignmentHORIZONTAL_R); tyle('F1 ')->getAlignment()->setHorizo​​​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $objPHPExcel->getActiveSheet()->getStyle('G1')->getAlcimid-Escelzo​​i n​​peakPMec故意_AlignP.S.P.
$objPHPExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setHorizo​​​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
   $PExcel_Style_Alignment::HORIZONTAL_RIGHT);
   get$$ )->setHorizo​​​​ntal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $objPHPExcel->getActiveSheet()->getStyle('J1')->getAlignment()->setHorizo​​​​dP.Pexcel.c.D. $objPHPExcel->getActiveSheet() ->getColumnDimension('K')->setWidth(10);
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', '物料名稱
    ->setCellValue('A1', '物料名稱')🜠圖片'), CValue'Bell'p. )    
    ->setCellValue('C1', '每天用量')
    ->setCellValue('D1', '現有庫存')
F1', '安全庫存')
    ->setCellValue('G1', '目標庫存')
    ->setCellValue('H1', '建議購買')
    ->setCellValue('I1', '單價' ->setCellValue('J1', '實際單價')
    ->setCellValue('K1', '採購連結');
    $i=2;
    $stock 'stock_list'];
    foreach($arr as $v)
    {
        if($v['img'])
 ActiveSheet()->getRowDimension($i)->setRowHeight(50) ;
            $objDrawing = new PHPExcel_Worksheet_Drawing();
            Drawing->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'. ); >setWorksheet($objPHPExcel->getActiveSheet());
        }
        else
     objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$i, '');    
        }
       ->setCellValue('A'.$i, $v['name '])
            ->setCellValue('C'.$i, $v['day_use'])
             ->setCellValue('E '.$i, $v['stock_day'])
            ->setCellValue('F'.$i, $v['stock_safe'])
   '])
            ->setCellValue('H'.$i, $v['proposal_buy'])
             ->setCellValue('J '.$i, '');
        if($v['stock_safe'] >= $v['stock_number'])
        { )  'D'.$i )->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
        }
                $objPHPExcel->setActiveSheetIndex (0)->setCellValue('K'.$i, '採購連結');
            $objPHPExcel->setActiveSheetIndex(0)->getCell('K'.$i)->getHyperlink()->setUrl($v['buy_url']);
       $Sm 'K'.$i)->getHyperlink()->setTooltip('採購連結');
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.$ i)-> ()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
            $objPHPExcel->setActiveSheetIndex(0)->getStyle('K'.$. setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
        }
        else
  Index(0)->setCellValue('K'.$i, ');
        }
        $ objPHPExcel->getActiveSheet()->getStyle ('A'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle( 'B.$ (PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C'.$i)-> $objPHPExcel->getActiveSheet()- >getStyle('D'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);Pexcel); Style('F'. $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G'.$objPHPExcel->getActiveSheet()->getStyle('G'.$i. getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('H'.$i)-PCAL>Alation)(Pcelc);
        $ objPHPExcel->getActiveSheet()->getStyle('I'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
   getp)$Sm )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('K'..$objPHPExcel->getActiveSheet()-PH>getStyle('K'..$A)->Align;
        $i++;
    }
    $file_name    =    $filename.'. Content-Disposition:attachment;filename="'. $file_name.'"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel5'); ://output');
    退出;
}
elseif ($_REQUEST['act'] == '查詢')
{    
    $ stock_list['stock_list']);
    $smarty->assign('filter',       $stock_list['filter']);
    $遠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)
{
    全球 $ecs,$db;
    =$ get_filter(); {
        $filter['sort_by'] = 空($_REQUEST['sort_by']) ? 'id' : 修剪($_REQUEST['sort_by']);
        $filter['sort_order'] = 空白($_REQUEST['sort_order']) ？ select count(t.id) from '.$ecs->table('materials) '）。 ' as t '.$where;
        
        _filter['record_count'One]record>
        
        /* 分頁大小*/
        $filter = page_and_size($filter);
                $ecs->table('materials' ) 中選擇t.*, au.user_name.' as t left join '.
                $ecs->table('admin_user')." as au on t.admin_id=au $filter['sort_by']." " .$filter['sort_order'];
        
        if ($is_pagination)
     $filter['開始'] 。    $start_time = $end_time - 7 * 86400;
        $query =「統計選擇（order_id）總計FROM ".$GLOBALS['ecs']->table('order_info')." WHEREsynch_time = '".$start_time."'";
        $filter['orders'] = round($GLOBALS['db'-]->getOne($query) / 7);//7天平均訂單數    
        $filter['orders'] = $filter['指令'] ？ $filter['Order'] : 1400;
set_filter（ $filter['Order'] : 1400;
set_filter（$filter，$）; else
{
$sql = $result['sql'];
$filter=$filter=$result['filter'];
$row=$row=$globals[''db']->getAll($ sql);
    
    $orders = $filter['orders'];
    foreach($row as $k=>$val)
    ==
 ) // 不購買，不匯出
        {
            unset($row[$k]);