'0', 'message'=>'' ); if(empty($jibao_id)){ $return['message'] = '集报单号为空'; return $return; } $jibao_tmp_condition = array( 'jibao_id'=>$jibao_id ); $count = Service_ApiJibaoGatherDetailsBefore::getByCondition($jibao_tmp_condition,'count(*)'); if($count>0){ $db = Common_Common::getAdapter(); $db->beginTransaction(); try{ Service_JibaoTmp::delete($jibao_id,'jb_code'); $pageSize = 20; $pages = ceil($count/$pageSize); for($page=1;$page<=$pages;$page++){ $lists = Service_ApiJibaoGatherDetailsBefore::getByCondition($jibao_tmp_condition,"*",$pageSize,$page); foreach ($lists as $key=>$value){ //得到api_jibao_gather_details_after数据 $gaterDetailAfterCondition = array( 'GOODS_ID'=>$value['GOODS_ID'], 'jibao_id'=>$jibao_id ); $gatherDetailAfter = Service_ApiJibaoGatherDetailsAfter::getByCondition($gaterDetailAfterCondition,'*'); if(!empty($gatherDetailAfter)){ if(count($gatherDetailAfter)>1){ $return['message'] = "集报单{$jibao_id},料件号{$value['GOODS_ID']}在api_jibao_gather_details_after数据不唯一"; return $return; }else{ $detailAfter = $gatherDetailAfter[0]; if($detailAfter['wlNubber']==''||$detailAfter['wlNubber']<=0){ $return['message'] = "集报单{$jibao_id},料件号{$detailAfter['GOODS_ID']}物理流水号为空"; return $return; } $sb_code = $value['FORM_ID']; $shipBatchXml = Service_ShipBatchXml::getByField($value['FORM_ID'],'sub_sb_code',"*"); if(!empty($shipBatchXml)) $sb_code = $shipBatchXml['sb_code']; $gunit2 = $value['UNIT_2']==""?"":$value['UNIT_2']; //插入到集报tmp表中 $jibaoTmp = array( 'sb_code'=>$sb_code, 'jb_code'=>$value['jibao_id'], 'sorts'=>$detailAfter['wlNubber'], 'hs_code'=>$value['CODE_TS'], 'goods_code'=>$value['GOODS_ID'], 'hs_name'=>$value['G_NAME_CN'], 'hs_model'=>$value['G_MODEL'], 'g_qty'=>$value['G_QTY'], 'g_unit'=>$value['G_UNIT'], 'g_qty1'=>$value['QTY_1'], 'g_unit1'=>$value['UNIT_1'], 'g_qty2'=>$value['QTY_2'], 'g_unit2'=>$gunit2, 'unit_price'=>$value['DECL_PRICE'], 'total_price'=>$value['DECL_TOTAL'], 'currency'=>$value['CURR'], 'country'=>$value['ORIGIN_COUNTRY'] ); if(!Service_JibaoTmp::add($jibaoTmp)){ throw new Exception("{$jibao_id}添加jibao_tmp失败"); } } }else{ /*$return['message'] = "集报单号{$jibao_id},料件号{$value['GOODS_ID']}在jibao_gather_details_before有数据,在jibao_gather_details_after无数据"; $db->rollback();*/ echo "集报单号{$jibao_id},料件号{$value['GOODS_ID']}在jibao_gather_details_before有数据,在jibao_gather_details_after无数据".'
'; //return $return; } } } //$db->rollback(); }catch (Exception $e){ $return['message'] = $e->getMessage(); $db->rollback(); return $return; } $return['message'] = '生成集报数据'; $return['ask'] = '1'; $db->commit(); return $return; }else{ return $count; } } /** * @author william-fan * @todo 将 */ function getAvg($jibao_id,$customer_code){ $return = array( 'ask'=>'0', 'message'=>'', 'data'=>array(), 'datazk'=>array() ); if(empty($jibao_id)){ $return['message'] = '集报单号为空'; return $return; } if(empty($customer_code)){ $return['message'] = '客户为空'; return $return; } $sqlcreate = " create table jibao_tmp_avg as SELECT SUM( op.op_quantity ) AS number, g_qty / SUM( op.op_quantity ) AS avg, jt. * FROM `jibao_tmp` AS jt INNER JOIN `ship_batch_detail` AS sbd ON jt.sb_code = sbd.sb_code INNER JOIN `outbound_bag_detail` AS obd ON sbd.ob_no = obd.ob_no INNER JOIN `order_product` AS op ON op.order_code = obd.order_code INNER JOIN `product` AS p ON op.product_id = p.product_id AND ((p.goods_id_old = jt.goods_code) or (p.goods_id = jt.goods_code)) and p.customer_code='$customer_code' and jb_code = '$jibao_id' GROUP BY ( jt.id )"; $sqlgetjibao = " SELECT jt.sb_code as 出区单,o.reference_no as 交易订单号,o.order_code as 订单号,date(sbd.sbd_add_time) as 出货时间,p.product_sku as sku,sum(op.op_quantity) as 产品数量,jt.avg,sum(op.op_quantity*jt.avg) as 总重量,jt.sorts as 序号,jt.hs_code as 海关编码,jt.goods_code as 料件号,jt.sell_quantity,jt.avg_num,jt.hs_name as 海关品名,jt.hs_model as 申报要素,jt.g_qty as 数量,jt.g_unit as 产品单位,jt.g_qty1 as 法定数量1,jt.g_qty2 as 法定第二数量,jt.g_unit2 as 法定第二单位,jt.unit_price as 申报单价,jt.total_price as 申报总价,jt.currency as 币种,jt.country as 国家 FROM `jibao_tmp_avg` AS jt INNER JOIN `ship_batch_detail` AS sbd ON jt.sb_code = sbd.sb_code INNER JOIN `outbound_bag_detail` AS obd ON sbd.ob_no = obd.ob_no INNER JOIN `order_product` AS op ON op.order_code = obd.order_code INNER JOIN `product` AS p ON op.product_id = p.product_id AND ((p.goods_id_old = jt.goods_code) or (p.goods_id = jt.goods_code)) inner join orders as o on op.order_code=o.order_code where o.customer_code='$customer_code' GROUP BY p.product_sku ORDER BY jt.id"; //echo $sqlgetjibao; $sqlzk = " SELECT jt.sb_code as 出区单,o.reference_no as 交易订单号,o.order_code as 订单号,so.tracking_number as 服务商单号,date(sbd.sbd_add_time) as 出货时间, p.product_sku as sku,p.goods_id as 料件号,p.goods_id_old as 旧料件号,op.op_quantity as 产品数量,jt.sorts as 序号,jt.hs_code as 海关编码,jt.goods_code as 料件号,jt.sell_quantity,jt.avg_num,jt.hs_name as 海关品名,jt.hs_model as 申报要素,jt.g_qty as 数量,jt.g_unit as 产品单位,jt.g_qty1 as 法定数量1,jt.g_qty2 as 法定第二数量,jt.g_unit2 as 法定第二单位,jt.unit_price as 申报单价,jt.total_price as 申报总价,jt.currency as 币种,jt.country as 国家 FROM `jibao_tmp` AS jt INNER JOIN `ship_batch_detail` AS sbd ON jt.sb_code = sbd.sb_code INNER JOIN `outbound_bag_detail` AS obd ON sbd.ob_no = obd.ob_no INNER JOIN `order_product` AS op ON op.order_code = obd.order_code INNER JOIN `product` AS p ON op.product_id = p.product_id inner join orders as o on op.order_code=o.order_code LEFT JOIN `ship_order` as so ON op.order_id = so.order_id where o.customer_code='$customer_code' AND ((p.goods_id_old = jt.goods_code) or (p.goods_id = jt.goods_code)) and jt.jb_code='$jibao_id' ORDER BY jt.id "; //echo $sql; $db = Common_Common::getAdapter(); //$jibaoRows = $db->fetchAll($sql); $dropTmp = 'drop table if EXISTS jibao_tmp_avg'; $db->query($dropTmp); $db->query($sqlcreate); try{ $db->beginTransaction(); $jibaoRows = $db->fetchAll($sqlgetjibao); $jibaoRowszk = $db->fetchAll($sqlzk); if(!empty($jibaoRows)){ $return['ask'] = '1'; $return['data'] = $jibaoRows; $return['datazk'] = $jibaoRowszk; return $return; }else{ $return['message']="集报临时表无数据"; } $db->commit(); }catch(Exception $e){ $db->rollback(); $return['message'] = $e->getMessage(); } return $return; } /** * @todo 处理集报数据 */ public function jibaoResultProcess($jibao_id,$result,$customer){ $return = array( 'ask'=>'0', 'message'=>'生成集报数据错误', ); $db = Common_Common::getAdapter(); try{ $absolutePath = $this->getBasePath()."/{$customer}"; if(!file_exists($absolutePath)) { mkdir($absolutePath, 0777, true); } $jibaoResult = $result['data']; //汇总数据 $jibaoResultzk = $result['datazk']; //展开sku信息 $hzcsv = ""; $hzfilename = $absolutePath.'/'.$jibao_id.'-hz.csv'; echo $hzfilename; //csv 头部 foreach($jibaoResult as $key=>$value){ foreach($value as $key1=>$value1){ if($key1=="国家"){ $hzcsv.=$key1."\r\n"; }else{ $hzcsv.=$key1.","; } } break; } foreach($jibaoResult as $key=>$value){ foreach($value as $key1=>$value1){ //print_r($key1); if($key1=="国家"){ /*$value1=str_replace("\r",'\r\n',$value1); $value1=str_replace("\n",'\r\n',$value1);*/ $value1 = str_replace("\"","\"\"",$value1); $hzcsv.="\"".$value1."\""."\r\n"; }else{ /*$value1=str_replace("\r",'\r\n',$value1); $value1=str_replace("\n",'\r\n',$value1);*/ $value1 = str_replace("\"","\"\"",$value1); $hzcsv.="\"".$value1."\","; } } } $hzcsv=iconv('UTF-8','GBK//IGNORE',$hzcsv); file_put_contents($hzfilename, $hzcsv); $zkcsv = ''; $zkfilename = $absolutePath.'/'.$jibao_id.'-zk.csv'; foreach($jibaoResultzk as $key=>$value){ foreach($value as $key1=>$value1){ if($key1=="国家"){ $zkcsv.=$key1."\r\n"; }else{ $zkcsv.=$key1.","; } } break; } foreach($jibaoResultzk as $key=>$value){ foreach($value as $key1=>$value1){ //print_r($key1); if($key1=="国家"){ /*$value1=str_replace("\r",'\r\n',$value1); $value1=str_replace("\n",'\r\n',$value1);*/ $value1 = str_replace("\"","\"\"",$value1); $zkcsv.="\"".$value1."\""."\r\n"; }else{ /*$value1=str_replace("\r",'\r\n',$value1); $value1=str_replace("\n",'\r\n',$value1);*/ $value1 = str_replace("\"","\"\"",$value1); $zkcsv.="\"".$value1."\","; } } } $zkcsv=iconv('UTF-8','GBK//IGNORE',$zkcsv); file_put_contents($zkfilename, $zkcsv); $jibaoApifromIdRow = array( 'jibao_path_hz'=>$hzfilename, 'jibao_path_zk'=>$zkfilename ); Service_ApiJibaoFormid::update($jibaoApifromIdRow, $jibao_id,'FORM_ID'); $return['ask'] = '1'; $return['message'] = '集报数据生成成功'; $db->commit(); //$db->rollback(); }catch(Exception $e){ $db->rollback(); $return['message'] = $e->getMessage(); } return $return; } }