public function get_product_shipping_code($product_net_name, $input_user)
{
$user_id = fetch_user_id_by_login_name($input_user);
return $this->get_one('product_net_name', 'shipping_code', array('user_id' => $user_id, 'net_name' => $product_net_name));
}
public function down_order_excel($id)
{
//$this->load->library('PHPExcel');
//$this->load->library('PHPExcel/IOFactory');
require_once APPPATH . 'libraries/PHPExcel' . EXT;
require_once APPPATH . 'libraries/PHPExcel/IOFactory' . EXT;
require_once APPPATH . 'libraries/PHPExcel/Worksheet/Drawing' . EXT;
$CI =& get_instance();
$order = $this->order_model->get_order($id);
//var_dump($order);die();
if ($order) {
$print_date = date('Y.m.d');
$user = fetch_user_name_by_id(fetch_user_id_by_login_name($order->input_user));
$country_cn_name = get_country_name_cn($order->country);
$objPHPExcel = new PHPExcel();
$objPHPExcel->createSheet();
//创建sheet
$objPHPExcel->setActiveSheetIndex(0);
$objActSheet = $objPHPExcel->getActiveSheet();
$objPHPExcel->getProperties()->setCreator("mallerp")->setLastModifiedBy("mallerp")->setTitle("Ebay ShipOrder List")->setSubject("Ebay ShipOrder List")->setDescription("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setKeywords("Mansea, Mallerp, zhaosenlin, 278203374, 7410992")->setCategory("mallerp");
$objActSheet->getColumnDimension('E')->setWidth(20);
$objActSheet->getColumnDimension('A')->setWidth(0);
/*excel表头*/
$objPHPExcel->getActiveSheet()->setCellValue('A1', '');
$objPHPExcel->getActiveSheet()->setCellValue('A2', '');
$objPHPExcel->getActiveSheet()->setCellValue('A3', '');
$objPHPExcel->getActiveSheet()->setCellValue('A4', '');
$objActSheet->getStyle('B1:B4')->getFont()->setSize(12);
$objActSheet->getStyle('B1:B4')->getFont()->setBold(true);
$objActSheet->getStyle('D1:D4')->getFont()->setBold(true);
$objActSheet->getStyle('F1:F4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('H1:M4');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '业务员');
$objPHPExcel->getActiveSheet()->setCellValue('C1', $user);
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'ERP订单号');
$objPHPExcel->getActiveSheet()->setCellValue('E1', $order->item_no);
$objPHPExcel->getActiveSheet()->setCellValue('F1', '日期');
$objPHPExcel->getActiveSheet()->setCellValue('G1', $print_date);
$objPHPExcel->getActiveSheet()->setCellValue('H1', '备注：' . $order->note);
$objPHPExcel->getActiveSheet()->setCellValue('B2', '客户名称');
$objPHPExcel->getActiveSheet()->setCellValue('C2', $order->name);
$objPHPExcel->getActiveSheet()->setCellValue('D2', '国家');
$objPHPExcel->getActiveSheet()->setCellValue('E2', $country_cn_name);
$objPHPExcel->getActiveSheet()->setCellValue('F2', '运输方式');
$objPHPExcel->getActiveSheet()->setCellValue('G2', $order->is_register);
$objPHPExcel->getActiveSheet()->setCellValue('B3', '订单总额$');
$objPHPExcel->getActiveSheet()->setCellValue('C3', $order->gross);
$objPHPExcel->getActiveSheet()->setCellValue('D3', '运费$');
$objPHPExcel->getActiveSheet()->setCellValue('E3', $order->shippingamt);
$objPHPExcel->getActiveSheet()->setCellValue('F3', '付款方式');
$objPHPExcel->getActiveSheet()->setCellValue('G3', $order->payment_type);
$objPHPExcel->getActiveSheet()->setCellValue('B4', '订单总额￥');
$objPHPExcel->getActiveSheet()->setCellValue('C4', $order->gross * $order->ex_rate);
$objPHPExcel->getActiveSheet()->setCellValue('D4', '运费￥');
$objPHPExcel->getActiveSheet()->setCellValue('E4', $order->shippingamt * $order->ex_rate);
$objPHPExcel->getActiveSheet()->setCellValue('F4', '实际运费');
$objPHPExcel->getActiveSheet()->setCellValue('G4', '');
$objPHPExcel->getActiveSheet()->setCellValue('H4', '交易号');
$objPHPExcel->getActiveSheet()->setCellValue('I4', $order->transaction_id);
//$objPHPExcel->getActiveSheet()->mergeCells("F5":"G5":"H5");
$objActSheet->getStyle('B5:M5')->getFont()->setSize(12);
$objActSheet->getStyle('B5:M5')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('F5:H5');
$objPHPExcel->getActiveSheet()->setCellValue('B5', '货架号');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'SKU');
$objPHPExcel->getActiveSheet()->setCellValue('D5', '数量');
$objPHPExcel->getActiveSheet()->setCellValue('E5', '图片');
$objPHPExcel->getActiveSheet()->setCellValue('F5', '中文名称');
$objPHPExcel->getActiveSheet()->setCellValue('I5', '单价$');
$objPHPExcel->getActiveSheet()->setCellValue('J5', '单价￥');
$objPHPExcel->getActiveSheet()->setCellValue('K5', '总价￥');
$objPHPExcel->getActiveSheet()->setCellValue('L5', '成本￥');
$objPHPExcel->getActiveSheet()->setCellValue('M5', '利润￥');
$skus = explode(',', $order->sku_str);
$qties = explode(',', $order->qty_str);
$item_prices = explode(',', $order->item_price_str);
$i = 5;
foreach ($skus as $key => $sku) {
$i++;
$sql1 = 'name_cn,shelf_code,sale_price,image_url,price';
$myproduct = $CI->product_model->fetch_product_by_sku($sku, $sql1);
$image = "";
if ($myproduct->image_url != '' && $myproduct->image_url != NULL && $myproduct->image_url != 'none') {
if (strpos($myproduct->image_url, 'http://') !== false) {
$image = "/var/www/html/mallerp/static/images/404-error.png";
} else {
$image = '/var/www/html/mallerp' . $myproduct->image_url;
}
} else {
$image = "/var/www/html/mallerp/static/images/404-error.png";
}
//die($image);
$objActSheet->getRowDimension($i)->setRowHeight(100);
$objActSheet->getStyle('B' . $i . ':M' . $i)->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->mergeCells('F' . $i . ':H' . $i);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $myproduct->shelf_code);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sku);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $qties[$key]);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('avatar');
$objDrawing->setDescription('avatar');
$objDrawing->setPath($image);
$objDrawing->setHeight(100);
$objDrawing->setWidth(100);
$objDrawing->setCoordinates('E' . $i);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, '图片');
$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $myproduct->name_cn);
$objPHPExcel->getActiveSheet()->setCellValue('I' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0);
$objPHPExcel->getActiveSheet()->setCellValue('J' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate);
$objPHPExcel->getActiveSheet()->setCellValue('K' . $i, isset($item_prices[$key]) ? $item_prices[$key] : 0 * $order->ex_rate * $qties[$key]);
$objPHPExcel->getActiveSheet()->setCellValue('L' . $i, '');
$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, '');
}
}
$filename = date("Y-m-d_H_i_s") . '.xls';
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
