ecshop功能二次开发之后台管理增加成本价和毛利润统计功能
ecshop功能二次开发之后台管理增加成本价和毛利润统计功能
在做ecshop二次开发的时候增加一个财务统计的功能!
首先在订单中的商品信息【编辑】页面中,修改成本价。也可以在添加商品时添加成本的 如果不做手工修改,则使用默认的商品成本价(在商品信息里编辑的成本
价格)。我用的版本是ecshop2.7.2
第一步:修改数据库中的商品表ecs_goods和订单商品表ecs_order_goods,添加成本价字段cost_price
把SQL语句列出来:
alter table `ecs_goods` add column `cost_price` decimal (10,2) UNSIGNED DEFAULT '0.00' NOT NULL after `promote_price`
alter table `ecs_order_goods` add column `cost_price` decimal (10,2) DEFAULT '0.00' NOT NULL after `market_price`
这里是加在了字段promote_price 和market_price后面
第二步.增加语言包 ,需要修改languages\zh_cn\admin\goods.php
再最后添加 /* 新增-成本价 */
$_LANG['lab_cost_price'] = '成本价:';
$_LANG['notice_cost_price'] = '该商品进货价格(成本价,在商品添加时设置,也可以在商品编辑里面修改 。).';
第三步.修改admin/goods.php 把成本价格插入到数据库
在两个(有两个地方需要修改)
'promote_price' => 0,
后新增一行添加
'cost_price' => 0,
再在
$shop_price = !empty($_POST['shop_price']) ? $_POST['shop_price'] : 0;
后添加
$cost_price = !empty($_POST['cost_price']) ? $_POST['cost_price'] : 0;
将
/* 入库 */
if ($is_insert)
{
。。。。。源代码太多省略(了这个是真实商品和虚拟商品)
}
else
{
/* 如果有上传图片,删除原来的商品图 */
。。。。。源代码太多省略了
}
修改成
/* 入库 */
if ($is_insert)
{
if ($code == '')
{
$sql = "INSERT INTO " . $ecs->table('goods') . " (goods_name, goods_name_style, goods_sn, " .
"cat_id, brand_id, shop_price,cost_price, market_price, is_promote, promote_price, " .
"promote_start_date, promote_end_date, goods_img, goods_thumb, original_img, keywords, goods_brief, " .
"seller_note, goods_weight, goods_number, warn_number, integral, give_integral, is_best, is_new, is_hot, " .
"is_on_sale, is_alone_sale, goods_desc, add_time, last_update, goods_type, rank_integral)" .
"VALUES ('$_POST[goods_name]', '$goods_name_style', '$goods_sn', '$catgory_id', " .
"'$brand_id', '$shop_price','$cost_price', '$market_price', '$is_promote','$promote_price', ".
"'$promote_start_date', '$promote_end_date', '$goods_img', '$goods_thumb', '$original_img', ".
"'$_POST[keywords]', '$_POST[goods_brief]', '$_POST
[seller_note]', '$goods_weight', '$goods_number',".
" '$warn_number', '$_POST[integral]', '$give_integral', '$is_best', '$is_new', '$is_hot', '$is_on_sale', '$is_alone_sale', ".
" '$_POST[goods_desc]', '" . gmtime() . "', '". gmtime() ."', '$goods_type', '$rank_integral')";
}
else
{
$sql = "INSERT INTO " . $ecs->table('goods') . " (goods_name, goods_name_style, goods_sn, " .
"cat_id, brand_id, shop_price,cost_price, market_price, is_promote, promote_price, " .
"promote_start_date, promote_end_date, goods_img, goods_thumb, original_img, keywords, goods_brief, " .
"seller_note, goods_weight, goods_number, warn_number, integral, give_integral, is_best, is_new, is_hot, is_real, " .
"is_on_sale, is_alone_sale, goods_desc, add_time, last_update, goods_type, extension_code, rank_integral)" .
"VALUES ('$_POST[goods_name]', '$goods_name_style', '$goods_sn', '$catgory_id', " .
"'$brand_id', '$shop_price','$cost_price', '$market_price', '$is_promote','$promote_price', ".
"'$promote_start_date', '$promote_end_date', '$goods_img', '$goods_thumb', '$original_img', ".
"'$_POST[keywords]', '$_POST[goods_brief]', '$_POST[seller_note]', '$goods_weight', '$goods_number',".
" '$warn_number', '$_POST[integral]', '$give_integral', '$is_best', '$is_new', '$is_hot', 0, '$is_on_sale', '$is_alone_sale', ".
" '$_POST[goods_desc]', '" . gmtime() . "', '". gmtime() ."', '$goods_type', '$code', '$rank_integral')";
}
}
else
{
/* 如果有上传图片,删除原来的商品图 */
$sql = "SELECT goods_thumb, goods_img, original_img " .
" FROM " . $ecs->table('goods') .
" WHERE goods_id = '$_REQUEST[goods_id]'";
$row = $db->getRow($sql);
if ($proc_thumb && $goods_img && $row['goods_img'] && !goods_parse_url($row['goods_img']))
{
@unlink(ROOT_PATH . $row['goods_img']);
@unlink(ROOT_PATH . $row['original_img']);
}
if ($proc_thumb && $goods_thumb && $row['goods_thumb'] && !goods_parse_url($row['goods_thumb']))
{
@unlink(ROOT_PATH . $row['goods_thumb']);
}
$sql = "UPDATE " . $ecs->table('goods') . " SET " .
"goods_name = '$_POST[goods_name]', " .
"goods_name_style = '$goods_name_style', " .
"goods_sn = '$goods_sn', " .
"cat_id = '$catgory_id', " .
"brand_id = '$brand_id', " .
"shop_price = '$shop_price', " .
"cost_price = '$cost_price', " .
"market_price = '$market_price', " .
"is_promote = '$is_promote', " .
"promote_price = '$promote_price', " .
"promote_start_date = '$promote_start_date', " .
"promote_end_date = '$promote_end_date', ";
/* 如果有上传图片,需要更新数据库 */
if ($goods_img)
{
$sql .= "goods_img = '$goods_img', original_img = '$original_img', ";
}
if ($goods_thumb)
{
$sql .= "goods_thumb = '$goods_thumb', ";
}
if ($code != '')
{
$sql .= "is_real=0, extension_code='$code', ";
}
$sql .= "keywords = '$_POST[keywords]', " .
"goods_brief = '$_POST[goods_brief]', " .
"seller_note = '$_POST[seller_note]', " .
"goods_weight = '$goods_weight'," .
"goods_number = '$goods_number', " .
"warn_number = '$warn_number', " .
"integral = '$_POST[integral]', " .
"give_integral = '$give_integral', " .
"rank_integral = '$rank_integral', " .
"is_best = '$is_best', " .
"is_new = '$is_new', " .
"is_hot = '$is_hot', " .
"is_on_sale = '$is_on_sale', " .
"is_alone_sale = '$is_alone_sale', " .
"goods_desc = '$_POST[goods_desc]', " .
"last_update = '". gmtime() ."', ".
"goods_type = '$goods_type' " .
"WHERE goods_id = '$_REQUEST[goods_id]' LIMIT 1";
}
(第三步 主要是在插入数据库时,把成本价(cost_price)的值插入到数据库)
第四步.下面应该在商品编辑页读取和显示成本价格,需要修改admin/templates/goods_info.dwt
找到代码
n="8" scope="col">
在
('goods')." as g SET og.cost_price =
g.cost_price " .
" WHERE og.goods_id = g.goods_id".
" AND og.order_id = '$order_id' and og.cost_price=0";
$GLOBALS['db']->query($sql_cost);
将/* 取得商品信息 */
$goods_id = $_REQUEST['goods_id'];
$sql = "SELECT goods_id, c.cat_name, goods_sn, goods_name, b.brand_name, " .
"goods_number, market_price, shop_price, promote_price, " .
"promote_start_date, promote_end_date, goods_brief, goods_type, is_promote " .
"FROM " . $ecs->table('goods') . " AS g " .
"LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
"LEFT JOIN " . $ecs->table('category') . " AS c ON g.cat_id = c.cat_id " .
" WHERE goods_id = '$goods_id'";
修改成 /* 取得商品信息 */
$goods_id = $_REQUEST['goods_id'];
$sql = "SELECT goods_id, c.cat_name, goods_sn, goods_name, b.brand_name, " .
"goods_number, market_price, shop_price,cost_price, promote_price, " .
"promote_start_date, promote_end_date, goods_brief, goods_type, is_promote " .
"FROM " . $ecs->table('goods') . " AS g " .
"LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
"LEFT JOIN " . $ecs->table('category') . " AS c ON g.cat_id = c.cat_id " .
" WHERE goods_id = '$goods_id'";
4.修改文件includes\lib_order.php
修改order_goods函数的SQL语句,将 $sql = "SELECT rec_id, goods_id, goods_name, goods_sn, market_price, goods_number, " .
"goods_price, goods_attr, is_real, parent_id, is_gift, " .
"goods_price * goods_number AS subtotal, extension_code " .
"FROM " . $GLOBALS['ecs']->table('order_goods') .
" WHERE order_id = '$order_id'";
修改成$sql = "SELECT o.rec_id, o.goods_id, o.goods_name, o.goods_sn, o.market_price, o.goods_number, " .
"o.goods_price,if(o.cost_price>0,o.cost_price,g.cost_price) AS cost_price, o.goods_attr, o.is_real, o.parent_id, o.is_gift, " .
"o.goods_price * o.goods_number AS subtotal, o.extension_code " .
"FROM " . $GLOBALS['ecs']->table('order_goods') . " AS o ".
"LEFT JOIN " . $GLOBALS['ecs']->table('goods') . " AS g ON o.goods_id = g.goods_id " .
" WHERE order_id = '$order_id'";
5.修改订单商品模板admin\templates\order.dwt
在
后插入
修改成
$sale_total = $cost_total = $gross_profit_total =$gross_profit_rate = 0;
while ($items = $db->fetchRow($res))
{
$sale_total += $items['sales_price'] * $items['goods_num'];
$cost_total += $items['cost_price'] * $items['goods_num'];
$gross_profit_total += $items['gross_profit'];
$items['cost_price'] = price_format($items['cost_price']);
$items['gross_profit'] = price_format($items['gross_profit']);
$items['sales_price'] = price_format($items['sales_price']);
$items['sales_time'] = local_date($_CFG['time_format'], $items['sales_time']);
$goods_sales_list[] = $items;
}
$total_isdisplay = false;
if($sale_total>0)
{
$total_isdisplay = true;
$gross_profit_rate = round($gross_profit_total*100/$sale_total,2).'%'; //毛利/销售收入
$sale_total = price_format($sale_total);
$cost_total = price_format($cost_total);
$gross_profit_total = price_format($gross_profit_total);
}
在$smarty->assign('cfg_lang', $_CFG['lang']);
之后插入
$smarty->assign('sale_total', $sale_total);
$smarty->assign('cost_total', $cost_total);
$smarty->assign('gross_profit_total',$gross_profit_total);
$smarty->assign('gross_profit_rate',$gross_profit_rate);
在echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['sell_price']) . "\t";
之后插入echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['cost_price']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['gross_profit']) . "\t";
将 foreach ($goods_sales_list AS $key => $value)
{
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['goods_name']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '[ ' . $value['order_sn'] . ' ]') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['goods_num']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['sales_price']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['sales_time']) . "\t";
echo "\n";
}
修改成
foreach ($goods_sales_list AS $key => $value)
{
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['goods_name']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '[ ' . $value['order_sn'] . ' ]') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['goods_num']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['sales_price']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['cost_price']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['gross_profit']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $value['sales_time']) . "\t";
echo "\n";
}
if($total_isdisplay)
{
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['sale_total']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $sale_total) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['cost_total']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB23
12', $cost_total) . "\t\n";
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', '') . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['gross_profit_total']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $gross_profit_total) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $_LANG['gross_profit_rate']) . "\t";
echo ecs_iconv(EC_CHARSET, 'GB2312', $gross_profit_rate) . "\t\n";
}
后台增加计算利润的功能已经做好了。