protected function _calculate_profit_rate($order_data)
{
$shipping_code = $order_data['shipping_code'];
$item_ids = explode(',', $order_data['item_id_str']);
$product_cost = $order_data['price'];
$transaction_ids = $order_data['transaction_ids'];
$currency = $order_data['currency'];
$gross = price(calc_currency($currency, $order_data['gross']), 2);
$net = price(calc_currency($currency, $order_data['net']), 2);
$shipping_weight = $order_data['shipping_weight'];
$country = $order_data['country'];
$trade_fee = 0;
$phone = '';
$item_ids = array_unique($item_ids);
$i = 0;
$listing_fee = 0;
// Todo: chinese auction
foreach ($item_ids as $item_id) {
if (is_array($transaction_ids)) {
$transaction_id = $transaction_ids[$i];
} else {
$transaction_id = $transaction_ids;
}
$ebay_order = $this->sale_model->fetch_ebay_order($item_id, $transaction_id);
// it's not correct for merged or splited orders.
if (!empty($ebay_order)) {
$currency = $ebay_order->currency;
$trade_fee += price(calc_currency($currency, $ebay_order->final_value_fee), 2);
$phone = '';
if ($ebay_order->transaction_id == 0) {
$listing = $this->ebay_order_model->fetch_listing_fee($item_id);
if (isset($listing->listing_fee)) {
$listing_fee += price(calc_currency($listing->listing_fee_currency, $listing->listing_fee));
}
}
if ($phone == 'Invalid Request') {
$phone = '';
}
}
$i++;
}
$country_cn = get_country_name_cn($country);
$max_length = 0;
$max_width = 0;
$total_height = 0;
$shipping_rule = _fetch_shipping_cost($shipping_weight, $shipping_code, $country_cn, $max_length, $max_width, $total_height);
$shipping_cost = isset($shipping_rule['price']) ? $shipping_rule['price'] : 0;
$profit = price(to_foreigh_currency('USD', $net - $product_cost - $shipping_cost - $trade_fee - $listing_fee));
$profit_rate = price($profit / $gross, 4);
if ($profit_rate == 0) {
$profit_rate = 0.001;
}
return array('profit' => $profit, 'profit_rate' => $profit_rate, 'trade_fee' => $trade_fee, 'listing_fee' => $listing_fee, 'product_cost' => $product_cost, 'shipping_cost' => $shipping_cost, 'phone' => $phone);
}
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');
}
function order_raw_cost($order_id)
{
$CI =& get_instance();
if (!isset($CI->order_model)) {
$CI->load->model('order_model');
}
if (!isset($CI->product_model)) {
$CI->load->model('product_model');
}
if (!function_exists('_fetch_shipping_cost')) {
$CI->load->helper('shipping');
}
$order = $CI->order_model->get_order($order_id);
if (empty($order)) {
return;
}
$skus = explode(',', $order->sku_str);
$qties = explode(',', $order->qty_str);
$product_cost = 0;
$shipping_weight = 0;
$i = 0;
foreach ($skus as $sku) {
$qty = $qties[$i];
$product = $CI->product_model->fetch_product_by_sku($sku);
$product_cost += $product->price * $qty;
$shipping_weight += $product->total_weight * $qty;
$i++;
}
$country_cn = get_country_name_cn($order->country);
$shipping_rule = _fetch_shipping_cost($shipping_weight, $order->is_register, $country_cn);
$shipping_cost = isset($shipping_rule['price']) ? $shipping_rule['price'] : 0;
return array('product_cost' => $product_cost, 'shipping_cost' => $shipping_cost);
}
$default = isset($product_cost_string[$count]) ? $product_cost_string[$count] : '0.65';
$item_sku_html .= '<br/>' . lang('other_cost_price') . '<input onchange="count_top_cost(' . "{$order->id},{$count}" . ')" id="' . "other_cost_price_{$order->id}" . '" type="text" name="' . "other_cost_price_{$order->id}" . '" value="' . $default . '" /></div>';
$top = $order->product_cost_all == 0 ? $total_product_cost : $order->product_cost_all;
$top_input_html = '<input readonly="true" id="' . "top_cost_{$order->id}" . '" type="text" name="' . "top_cost_{$order->id}" . '" value="' . $top . '" />';
$item_sku_html .= '<br/>' . lang('top_cost') . ' ' . $top_input_html;
$shipping_type = lang('shipping_way') . ': ';
$shipping_type .= $order->is_register;
$product_info = <<<PRODUCT
<div style='padding: 10px;'>
{$item_sku_html}
</div>
PRODUCT;
if (array_key_exists($order->country, $save_countries)) {
$country_name = $save_countries[$order->country];
} else {
$country_name = get_country_name_cn($order->country);
$save_countries[$order->country] = $country_name;
}
$shipping_cost = '';
$is_register = strtoupper(trim($order->is_register));
if (in_array($is_register, $allowed_registers)) {
if ($order->ship_weight <= 2000) {
$shipping_cost = shipping_price($country_name, $order->ship_weight, $order->is_register, $max_length, $max_width, $total_height);
} else {
$shipping_cost = '?';
}
} else {
if (in_array($is_register, $abroad_shipping_codes)) {
$shipping_cost = shipping_price($country_name, $order->ship_weight, $order->is_register, $max_length, $max_width, $total_height);
}
}
private function is_profit_good($transaction_details)
{
$gross = $transaction_details['AMT'];
$fee = isset($transaction_details['FEEAMT']) ? $transaction_details['FEEAMT'] : 0;
$currency = $transaction_details['CURRENCYCODE'];
$gross_rmb = price(calc_currency($currency, $gross));
$fee_rmb = price(calc_currency($currency, $fee));
$skus = $this->item_info['item_codes'];
$qties = $this->item_info['item_qties'];
$item_ids = $this->item_info['item_ids'];
$product_cost = 0;
$shipping_weight = 0;
$other_cost = 0.65;
$paypal_transaction_id = array($transaction_details['TRANSACTIONID']);
$shipping_type = get_register($this->item_info, $this->user, $currency, $paypal_transaction_id);
$country_name_en = $transaction_details['SHIPTOCOUNTRYNAME'];
if (isset($this->country_names[$country_name_en])) {
$country_name_cn = $this->country_names[$country_name_en];
} else {
$country_name_cn = get_country_name_cn($country_name_en);
$this->country_names[$country_name_en] = $country_name_cn;
}
$sale_mode = 'buy_now';
// check sale mode
foreach ($item_ids as $item_id) {
$item = $this->ebay_model->fetch_ebay_item_by_item_id($item_id);
if (isset($item->listing_type)) {
$sale_mode = $item->listing_type;
break;
}
}
if ($sale_mode == 'buy_now') {
$eshop_list_fee_multiply = 1;
$eshop_list_count = 100;
} else {
$bid_rate = 30;
$eshop_list_fee_multiply = 1 + (100 - $bid_rate) / $bid_rate;
$eshop_list_count = 1;
}
$i = 0;
foreach ($skus as $sku) {
$qty = $qties[$i];
$product = $this->product_model->fetch_product_by_sku($sku);
$product_cost += $product->price * $qty;
$shipping_weight += $product->total_weight * $qty;
$i++;
}
$eshop_codes = array('USD' => 'ebay-USA', 'AUD' => 'ebay-AU', 'GBP' => 'ebay-UK', 'EUR' => 'ebay-FR');
$input = array('eshop_code' => $eshop_codes[$currency], 'buyer_shipping_cost' => 0, 'shipping_type' => $shipping_type, 'shipping_country' => $country_name_cn, 'total_weight' => $shipping_weight, 'sale_mode' => $sale_mode, 'eshop_category' => 0, 'suggest_price' => $gross, 'key' => TRUE, 'total_price' => $product_cost, 'balance_profit' => 0.3, 'eshop_list_count' => $eshop_list_count, 'eshop_list_fee_multiply' => $eshop_list_fee_multiply, 'pay_option' => 0, 'pay_discount' => 0, 'other_cost' => $other_cost, 'paypal_cost' => $fee);
$data = price_profit_rate($input);
if (!is_array($data)) {
$this->user_note = lang('not_profit_rate_note');
return FALSE;
}
$profit_rate = price($data['total_profit_rate']);
$this->profit_rate = $profit_rate;
$this->sale_mode = $sale_mode;
$this->user_note = '';
if ($profit_rate < 0) {
if ($sale_mode == 'buy_now' or $profit_rate < -0.3) {
$product_cost = $data['product_cost'];
$shipping_cost = $data['shipping_cost'];
$trade_fee_rmb = price($data['trade_fee']);
$list_fee_rmb = price($data['list_fee']);
$this->user_note = sprintf(lang('wait_for_confirmation_user_note'), $profit_rate, $gross_rmb, $fee_rmb, $trade_fee_rmb, $list_fee_rmb, $product_cost, $shipping_cost, $other_cost);
return FALSE;
}
}
return TRUE;
}
