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
找到代码 <tr>
<td class="label">{$lang.lab_market_price}</td>
<td><input type="text" name="market_price" value="{$goods.market_price}" size="20" />
<input type="button" value="{$lang.integral_market_price}" onclick="integral_market_price()" />
</td>
</tr>
在这段代码之后添加显示成本价格的表格
<!--新增 成本价 begin -->
<tr>
<td class="label">{$lang.lab_cost_price}</td>
<td><input type="text" name="cost_price" value="{$goods.cost_price}" size="20" />
<br />
<span class="notice-span" {if $help_open}style="display:block" {else} style="display:none" {/if} id="minNumber">{$lang.notice_cost_price}</span></td>
</td>
</tr>
<!-- 成本价 end -->
这样就把就完成了在商品添加时增加商品的成本价和在商品列表页编辑商品页显示成本价的功能
ecshop 功能二次开发 关于后台管理增加成本价和毛利润统计功能（二）
1.修改订单的语言文件languages\zh_cn\admin\order.php
最后新增：$_LANG['cost_price'] = '成本价';
2.然后我们来修改订单显示模板文件 admin\templates\order_info.dwt
把<th colspan="7" scope="col">修改成<th colspan="8" scope="col">
在<td scope="col"><div align="center"><strong>{$lang.storage}</strong></div></td>
代码之后插入<td scope="col"><div align="center"><strong>{$lang.cost_price}</strong></div></td>
在<td><div align="right">{$goods.storage}</div></td>
代码之后插入<td><div align="right">{$goods.cost_price}</div></td>
3.下面是如何修改修改admin\order.php
将
$sql = "SELECT o.*, g.goods_number AS storage, o.goods_attr, IFNULL(b.brand_name, '') AS brand_name " .
"FROM " . $ecs->table('order_goods') . " AS o ".
"LEFT JOIN " . $ecs->table('goods') . " AS g ON o.goods_id = g.goods_id " .
"LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
"WHERE o.order_id = '$order[order_id]' ";
修改成
$sql = "SELECT o.*, g.goods_number AS storage,if(o.cost_price>0,o.cost_price,g.cost_price) AS cost_price, o.goods_attr, IFNULL(b.brand_name, '') AS brand_name " .
"FROM " . $ecs->table('order_goods') . " AS o ".
"LEFT JOIN " . $ecs->table('goods') . " AS g ON o.goods_id = g.goods_id " .
"LEFT JOIN " . $ecs->table('brand') . " AS b ON g.brand_id = b.brand_id " .
"WHERE o.order_id = '$order[order_id]' ";
在
$goods_price = floatval($_POST['goods_price'][$key]);
之后插入$cost_price = floatval($_POST['cost_price'][$key]);
将
$sql = "UPDATE " . $ecs->table('order_goods') .
" SET goods_price = '$goods_price', " .
"goods_number = '$goods_number', " .
"goods_attr = '$goods_attr' " .
"WHERE rec_id = '$rec_id' LIMIT 1";
修改成$sql = "UPDATE " . $ecs->table('order_goods') .
" SET goods_price = '$goods_price',cost_price = '$cost_price', " .
"goods_number = '$goods_number', " .
"goods_attr = '$goods_attr' " .
"WHERE rec_id = '$rec_id' LIMIT 1";
在
update_order($order_id, array('order_status' => OS_CONFIRMED, 'confirm_time' => gmtime()));
之后插入
$sql_cost = "UPDATE ". $GLOBALS['ecs']->table('order_goods')." as og,". $GLOBALS['ecs']->table('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);
在
update_order($order_id, array('order_status' => OS_CONFIRMED, 'confirm_time' => gmtime()));
update_order_amount($order_id);
之后插入
$sql_cost = "UPDATE ". $GLOBALS['ecs']->table('order_goods')." as og,". $GLOBALS['ecs']->table('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
在<th scope="col">{$lang.goods_attr}</th>
之后插入<th scope="col">{$lang.cost_price}</th>
在<td><textarea name="goods_attr[]" cols="30" rows="{$goods.rows}">{$goods.goods_attr}</textarea></td>
之后插入<td><input name="cost_price[]" type="text" style="text-align:right" value="{$goods.cost_price}" size="10" /></td>
在 <tr>
<th>{$lang.goods_price}</th>
<td id="add_price"> </td>
</tr>
之后插入
<tr>
<th>{$lang.cost_price}</th>
<td id="cost_price"> </td>
</tr>
在document.getElementByIdx_x_x('add_price').innerHTML = '';
之后插入document.getElementByIdx_x_x('cost_price').innerHTML = '';
在document.getElementByIdx_x_x('add_price').innerHTML = priceHtml;
之后插入document.getElementByIdx_x_x('cost_price').innerHTML = '<input type="text" name="cost_price" value="'+result.cost_price+'" />';
ecshop 功能二次开发 关于后台管理增加成本价和毛利润统计功能（三）
毛利润显示在报表统计的销售明细里 并且增加了成本汇总
1.首先要修改对应的语言文件languages\zh_cn\admin\statistic.php
在最后新增
$_LANG['cost_price'] = '成本价';
$_LANG['gross_profit'] = '毛利润';
$_LANG['sale_total'] = '销售收入：';
$_LANG['cost_total'] = '成本汇总：';
$_LANG['gross_profit_total'] = '毛利润合计：';
$_LANG['gross_profit_rate'] = '成本毛利率：';
2.修改对应的模板文件admin\templates\sale_list.dwt
在 <th>{$lang.sell_price}</th>之后插入 <th>{$lang.cost_price}</th>
<th>{$lang.gross_profit}</th>
在<td align="right">{$list.sales_price}</td>
之后插入 <td align="right">{$list.cost_price}</td>
<td align="right">{$list.gross_profit}</td>
在 {foreachelse}
<tr><td class="no-records" colspan="10">{$lang.no_records}</td></tr>
{/foreach}
之后插入
{if $sale_total}
<tr align="center">
<td align="right" colspan="4">{$lang.sale_total}</td>
<td align="right">{$sale_total}</td>
<td align="right">{$lang.cost_total}</td>
<td align="right">{$cost_total}</td>
</tr>
<tr align="center">
<td align="right" colspan="4">{$lang.gross_profit_total}</td>
<td align="right">{$gross_profit_total}</td>
<td align="right">{$lang.gross_profit_rate}</td>
<td align="right">{$gross_profit_rate}</td>
</tr>
{/if}
3.修改sale_list.php
将 $sql = 'SELECT og.goods_id, og.goods_sn, og.goods_name, og.goods_number AS goods_num, og.goods_price '.
'AS sales_price, oi.add_time AS sales_time, oi.order_id, oi.order_sn '.
"FROM " . $ecs->table('order_goods')." AS og, ".$ecs->table('order_info')." AS oi ".
$where. " ORDER BY sales_time DESC, goods_num DESC";
修改成$sql = 'SELECT og.goods_id, og.goods_sn, og.goods_name, og.goods_number AS goods_num, og.cost_price, og.goods_price '.
'AS sales_price,(og.goods_price-og.cost_price)*og.goods_number as gross_profit, oi.add_time AS sales_time, oi.order_id, oi.order_sn '.
"FROM " . $ecs->table('order_goods')." AS og, ".$ecs->table('order_info')." AS oi ".
$where. " ORDER BY sales_time DESC, goods_num DESC";
将while ($items = $db->fetchRow($res))
{
$items['sales_price'] = price_format($items['sales_price']);
$items['sales_time'] = local_date($_CFG['time_format'], $items['sales_time']);
$goods_sales_list[] = $items;
}
修改成
$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, 'GB2312', $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";
}
后台增加计算利润的功能已经做好了。
上一篇: ECSHOP添加字段并实现商品批量上传