ecshop物料库存管理，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：
![ecshop物料库存管理，ecshop库存管理_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=
newcls_image(
$_CFG['bgcolor'
]);
6
$exc=
newexchange(
$ecs->table("materials"),
$db, 'id', 'name'
);
7
$_REQUEST['act']=!
empty(
$_REQUEST['act']) ?
$_REQUEST['act']:'list'
;
8admin_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
{
51sys_msg('名称、系数、安全天数、目标天数，不能为空或者格式不正确', 1
);
52
}
53
54
$is_only=
$exc->is_only('name',
$material['name'
]);
55
if(!
$is_only
)
56
{
57sys_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'
;
71sys_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
{
90sys_msg('ID不能为空', 1
);
91
}
92
93
if(
empty(
$material['name']) ||
empty(
$material['modulus']) ||
empty(
$material['safe_day']) ||
empty(
$material['intent_day'
]))
94
{
95sys_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'
;
112sys_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'
]);
130ecs_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'
));
147sys_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
{
156make_json_error(
sprintf("格式不正确！",
$val
));
157
}
158
159
$exc->edit("stock_in='
$val'",
$id
);
160make_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
{
169make_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
{
175make_json_error(
sprintf("出库数不能大于现有库存与入库总和！",
$val
));
176
}
177
178
$exc->edit("stock_out='
$val'",
$id
);
179make_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'
));
186sys_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
else
245
{
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
else
272
{
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'
]);
303make_json_result(
$smarty->fetch('material_list.htm'), '',
array('filter' =>
$stock_list['filter'], 'page_count' =>
$stock_list['page_count'
]));
304
}
305
306
functionmaterial_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
;
338set_filter(
$filter,
$sql
);
339
}
340
else
341
{
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
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 {
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) //不购买，不导出
{
