代码通用性比较差，文字又不知道说啥，因为更多是代码，所以只放一些关键方法来说明，基类会通过附件的方式放文章里
- 查询结果数据
- 格式化文件标题头、格式化数据
- 调用基类【AmazonReceivablesExcel】的exportExcel2007方法生成导出文件
建议优先参考 PurchaseDiscountAmountReportService.php、AmazonReceivablesExcel.php、xlsxwriter.class.php 示例，因为代码封装的层次较浅、易懂
/**
* 执行导出
* @param array $chooseFields
* @param array $file
* @param array $params
* @param int $isQueue
* @return array
* @throws Exception
* @throws DbException
*/
public function dayExport(array $chooseFields, array $file, array $params, int $isQueue = 0): array
{
$this->requestFields = array_flip((new ListService())->getApiFields());
$daysReportSummary = [];
$pageSize = 50000; // 每次最多执行5000条数据
$count = (new ListService())->getCount($params);
$num = ceil($count / $pageSize);
$header = array_values($this->daysExportField($chooseFields));
// $generator = $this->exportBuilder($num, $params, $pageSize);
for ($i = 1; $i <= $num; $i++) {
$offset = ($i - 1) * $pageSize;
// 这几步是共用列表封装的方法，实际情况可根据自己的业务直接查sql得出数据即可
$accountIds = $this->getIds($params, $offset, $pageSize);
$this->requestParams = $params;
$this->queryDataByIds($accountIds);
$generator = $this->restructData();
// 这几步是共用列表封装的方法，实际情况可根据自己的业务直接查sql得出数据即可
/*$generator = $this->getQuery($params)
->group('far.account_shop_id,far.type')
->page($i, $pageSize)
->select();*/
$accountIds = array_column($generator, 'account_shop_id');
foreach ($accountIds as &$item) {
$item = $item % OrderType::ChannelVirtual;
}
/**
* 拆分sub_data 为一维数组
*/
$temp = [];
$singleTemp = [];
foreach ($generator as $key => $singleAccount) {
$singleTemp = $singleAccount;
// 共用的代码不方便获取id字段&&id仅用作合并单元格区分之用暂用此方式.
$singleTemp['id'] = rand(1, 10000000000000000);
foreach ($singleAccount['sub_data'] as $warehouse) {
$singleTemp['pay_fee'] = $warehouse['pay_fee'];
$singleTemp['channel_order_amount'] = floatval($warehouse['channel_order_amount']);
$singleTemp['channel_commission'] = $warehouse['channel_commission'];
$singleTemp['fba_shipping_fee'] = $warehouse['fba_shipping_fee'];
$singleTemp['erp_shipped_refund_amount'] = $warehouse['erp_shipped_refund_amount'];
$singleTemp['erp_not_ship_refund_amount'] = $warehouse['erp_not_ship_refund_amount'];
$singleTemp['refund_commission'] = $warehouse['refund_commission'];
$singleTemp['erp_shipped_amount'] = $warehouse['erp_shipped_amount'];
$singleTemp['erp_first_fee_and_tariff_cny'] = $warehouse['erp_first_fee_and_tariff_cny'];
$singleTemp['internet_sales_tax_diff'] = $warehouse['internet_sales_tax_diff'];
$singleTemp['type_text'] = $warehouse['type_text'];
$singleTemp['currency_code'] = $warehouse['currency_code'];
$temp[] = $singleTemp;
}
}
$generator = $temp;
$lists = [];
$list = $this->formatDaysData($generator, $daysReportSummary);
$lists = array_merge($lists, $list);
return AmazonReceivablesExcel::exportExcel2007($header, $lists, $file, $params, $isQueue);
}
}
/**
* 日报表导出字段
* @param $chooseField
* @return array
*/
public function daysExportField(array $chooseField = []): array
{
/**
* type 所支持的类型参考：\XLSXWriter::numberFormatStandardized
*/
$fields = [
'account_code' => ['title' => '账号简称', 'key' => 'account_code', 'width' => 20, 'need_merge' => 1],
'site_code' => ['title' => '站点', 'key' => 'site_code', 'width' => 20, 'need_merge' => 1],
'seller_name' => ['title' => '销售员', 'key' => 'seller_name', 'width' => 20, 'need_merge' => 1],
'account_status_text' => ['title' => '账号状态', 'key' => 'account_status_text', 'width' => 20, 'need_merge' => 1],
'frozen_time' => ['title' => '冻结时间', 'key' => 'frozen_time', 'width' => 20, 'need_merge' => 1],
'recover_time' => ['title' => '解冻时间', 'key' => 'recover_time', 'width' => 20, 'need_merge' => 1],
'currency_code' => ['title' => '币种', 'key' => 'currency_code', 'width' => 20, 'need_merge' => 1],
'type_text' => ['title' => '仓库类型', 'key' => 'type_text', 'width' => 20, 'need_merge' => 0],
'pay_fee' => ['title' => '订单支付金额', 'key' => 'pay_fee', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'channel_order_amount' => ['title' => '平台订单金额', 'key' => 'channel_order_amount', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'channel_commission' => ['title' => '平台销售佣金', 'key' => 'channel_commission', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'fba_shipping_fee' => ['title' => 'FBA运费', 'key' => 'fba_shipping_fee', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'erp_shipped_refund_amount' => ['title' => '平台退款金额-已发货', 'key' => 'erp_shipped_refund_amount', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'erp_not_ship_refund_amount' => ['title' => '平台退款金额-未发货', 'key' => 'erp_not_ship_refund_amount', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'refund_commission' => ['title' => '退款返佣金', 'key' => 'refund_commission', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'erp_shipped_amount' => ['title' => '本期ERP发货订单金额', 'key' => 'erp_shipped_amount', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'internet_sales_tax_diff' => ['title' => '互联网销售税差异', 'key' => 'internet_sales_tax_diff', 'width' => 20, 'need_merge' => 0, 'type' => 'price'],
'zhichi_channel_order_amount' => ['title' => '智持订单平台订单金额', 'key' => 'zhichi_channel_order_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'zhichi_channel_order_amount_commission' => ['title' => '智持订单平台销售佣金', 'key' => 'zhichi_channel_order_amount_commission', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'advertising_cost' => ['title' => '广告费用', 'key' => 'advertising_cost', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'credit_card_invest_amount' => ['title' => '信用卡充值金额', 'key' => 'credit_card_invest_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'shop_charges' => ['title' => '店铺费用', 'key' => 'shop_charges', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'unknown_fee' => ['title' => '调整费用', 'key' => 'unknown_fee', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'transfer_diff' => ['title' => '转账补差异', 'key' => 'transfer_diff', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'settled_transfer_amount' => ['title' => '已结算转账金额', 'key' => 'settled_transfer_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'transfer_failed' => ['title' => '转账失败金额原币', 'key' => 'transfer_failed', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'opening_advance_balance' => ['title' => '期初预收账款余额', 'key' => 'opening_advance_balance', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'half_year_unshipped_order_price' => ['title' => '半年未发货订单售价', 'key' => 'half_year_unshipped_order_price', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_advance_balance' => ['title' => '期末预收账款余额', 'key' => 'final_advance_balance', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_advance_balance_check' => ['title' => '期末预收账款余额（校验）', 'key' => 'final_advance_balance_check', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'opening_receivables_balance' => ['title' => '期初应收账款余额', 'key' => 'opening_receivables_balance', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_receivables_balance' => ['title' => '期末应收账款余额', 'key' => 'final_receivables_balance', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
// 'multi_pay_amount' => ['title' => '已匹配原单的多渠道发货订单金额'],
'final_reserve_amount' => ['title' => '期末预留金额', 'key' => 'final_reserve_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'not_settlement_amount' => ['title' => '未结算金额', 'key' => 'not_settlement_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_receivables_balance_check' => ['title' => '期末应收账款余额（校验）', 'key' => 'final_receivables_balance_check', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'opening_receivables_in_transit' => ['title' => '期初应收在途', 'key' => 'opening_receivables_in_transit', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'third_party_amount' => ['title' => '第三方入账金额', 'key' => 'third_party_amount', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_receivables_in_transit' => ['title' => '期末应收在途', 'key' => 'final_receivables_in_transit', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'final_receivables_in_transit_check' => ['title' => '期末应收在途（校验）', 'key' => 'final_receivables_in_transit_check', 'width' => 20, 'need_merge' => 1, 'type' => 'price'],
'sales_company_name' => ['title' => '运营公司', 'key' => 'sales_company_name', 'width' => 20, 'need_merge' => 1],
];
$result = [];
if (empty($chooseField) && count($chooseField) == 0) {
return $fields;
}
// foreach ($chooseField as $key => $field) {
// if (isset($fields[$field['field_key']])) {
// $result[$field['field_key']] = $fields[$field['field_key']];
// if ($title = param($field, 'field_name')) {
// $result[$field['field_key']]['title'] = $title;
// }
// }
// }
foreach ($chooseField as $key => $field) {
if (isset($fields[$field])) {
$result[$field] = $fields[$field];
// if ($title = param($field, 'field_name')) {
// $result[$field['field_key']]['title'] = $title;
// }
}
}
return $result;
}
2 AmazonReceivablesExcel.php
