Beispiel #1
0
 public function exportcsvAction()
 {
     set_time_limit(0);
     $fa = new Product_Model_Fa();
     $bomPrice = new Product_Model_BomPrice();
     $son = new Product_Model_Son();
     $catalog = new Product_Model_Catalog();
     $materiel = new Product_Model_Materiel();
     $request = $this->getRequest()->getParams();
     $faName = $fa->getName();
     $priceName = $bomPrice->getName();
     $mName = $materiel->getName();
     $catalogName = $catalog->getName();
     $db = $fa->getAdapter();
     $whereSearch = "{$faName}.state != 'Obsolete'";
     foreach ($request as $k => $v) {
         if ($v) {
             if ($k == 'search_key') {
                 $whereSearch .= " and (ifnull({$faName}.remark,'') like '%{$v}%' or ifnull({$mName}.name,'') like '%{$v}%' or ifnull({$mName}.description,'') like '%{$v}%' or ifnull({$catalogName}.model_internal, '') like '%{$v}%')";
             } else {
                 if ("search_fa" == $k && $v) {
                     $whereSearch .= " and {$faName}.code like '%{$v}%'";
                 } else {
                     if ("search_archive_date_from" == $k && $v) {
                         $whereSearch .= " and {$faName}.bom_upd_time >= '" . str_replace('T', ' ', $v) . "'";
                     } else {
                         if ("search_archive_date_to" == $k && $v) {
                             $whereSearch .= " and v.bom_upd_time <= '" . str_replace('T00:00:00', ' 23:59:59', $v) . "'";
                         } else {
                             if ("search_son" == $k && $v) {
                                 $recordkey = "";
                                 $sonData = $db->query("select group_concat(recordkey) as recordkey from oa_product_bom_son where code like '%{$v}%'")->fetchObject();
                                 if ($sonData && $sonData->recordkey) {
                                     $recordkey = $sonData->recordkey;
                                 }
                                 if (!$recordkey) {
                                     $recordkey = "0";
                                 }
                                 $whereSearch .= " and {$faName}.recordkey in ({$recordkey})";
                             } else {
                                 if ("search_recordkey" == $k && $v) {
                                     $whereSearch .= " and {$faName}.recordkey = '{$v}'";
                                 } else {
                                     if ("explanded" == $k && $v) {
                                         $explanded = json_decode($v);
                                     } else {
                                         $col = str_replace('search_', '', $k);
                                         if ($col != $k) {
                                             // 查询条件
                                             $whereSearch .= " and ifnull({$faName}." . $col . ",'') like '%" . $v . "%'";
                                         }
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
         }
     }
     $rate = new Erp_Model_Setting_Currencyrate();
     $date = date('Y-m-d');
     $rateCny = $rate->getRateByCode('CNY', $date);
     $rateUsd = $rate->getRateByCode('USD', $date);
     $user_session = new Zend_Session_Namespace('user');
     $user = $user_session->user_info['employee_id'];
     $sort = $this->getRequest()->getParam('sort');
     if ($sort) {
         $sort = json_decode($sort);
         $sort = $sort[0];
         $property = $sort->property;
         $direction = $sort->direction;
         if (in_array($property, array('code', 'ver', 'state', 'remark'))) {
             $order = array($faName . '.' . $property . ' ' . $direction);
         } else {
             if (in_array($property, array('low_cny', 'low_usd', 'high_cny', 'high_usd', 'average_cny', 'average_usd', 'update_time'))) {
                 $order = array($bomPrice->getName() . '.' . $property . ' ' . $direction);
             } else {
                 if (in_array($property, array('description', 'name'))) {
                     $order = array($mName . '.' . $property . ' ' . $direction);
                 }
             }
         }
     }
     if (isset($order)) {
         $order[] = $faName . '.code';
     } else {
         $order = array($faName . '.code');
     }
     // 获取物料数据
     $cjoin = array(array('type' => LEFTJONIN, 'table' => $catalog->getName(), 'condition' => $catalog->getName() . '.id = ' . $fa->getName() . '.project_no'), array('type' => INNERJOIN, 'table' => $bomPrice->getName(), 'condition' => $bomPrice->getName() . '.recordkey = ' . $fa->getName() . '.recordkey'), array('type' => INNERJOIN, 'table' => $materiel->getName(), 'condition' => $materiel->getName() . '.id = ' . $fa->getName() . '.id'));
     $join = array(array('type' => LEFTJONIN, 'table' => $catalog->getName(), 'condition' => $catalog->getName() . '.id = ' . $fa->getName() . '.project_no', 'cols' => array('project_no_name' => 'model_internal')), array('type' => INNERJOIN, 'table' => $bomPrice->getName(), 'condition' => $bomPrice->getName() . '.recordkey = ' . $fa->getName() . '.recordkey', 'cols' => array("low_cny", "low_usd", 'high_cny', 'high_usd', 'average_cny', 'average_usd', 'update_time')), array('type' => INNERJOIN, 'table' => $materiel->getName(), 'condition' => $materiel->getName() . '.id = ' . $fa->getName() . '.id', 'cols' => array("name", "description")));
     $data = $fa->getJoinList($whereSearch, $join, null, $order);
     $allData = array();
     for ($i = 0; $i < count($data); $i++) {
         // 对于每个BOM,递归计算每个下级物料或BOM的价格
         if (!$data[$i]['low_cny']) {
             $price = $bomPrice->calcBomPrice($data[$i]['recordkey'], 'CNY');
             $data[$i]['low_cny'] = $price['low'];
             $data[$i]['high_cny'] = $price['high'];
             $data[$i]['average_cny'] = $price['average'];
             $data[$i]['low_usd'] = round($data[$i]['low_cny'] * ($rateCny / $rateUsd), 4);
             $data[$i]['high_usd'] = round($data[$i]['high_cny'] * ($rateCny / $rateUsd), 4);
             $data[$i]['average_usd'] = round($data[$i]['average_cny'] * ($rateCny / $rateUsd), 4);
         }
         $allData[] = $data[$i];
     }
     print chr(0xef) . chr(0xbb) . chr(0xbf);
     // 获取物料数据
     $data_csv = array();
     $title = array('cnt' => '#', 'code' => 'BOM号', 'ver' => '版本', 'state' => '状态', 'low_cny' => '最低价格-人民币', 'high_cny' => '最高价格-人民币', 'average_cny' => '平均价格-人民币', 'low_usd' => '最低价格-美元', 'high_usd' => '最高价格-美元', 'average_usd' => '平均价格-美元', 'update_time' => '更新时间', 'name' => '物料名称', 'description' => '物料描述', 'project_no_name' => '产品型号', 'bom_upd_time' => '归档时间', 'remark' => '备注');
     $title = $this->object_array($title);
     $date = date('YmdHsi');
     $filename = 'bomprice-' . $date;
     $path = "../temp/" . $filename . ".csv";
     $file = fopen($path, "w");
     fputcsv($file, $title);
     array_push($data_csv, $title);
     $typeids = array();
     $typenames = array();
     $k = 0;
     for ($i = 0; $i < count($data); $i++) {
         $d = $data[$i];
         $k++;
         $info = array('cnt' => $k, 'code' => Helper::ifNull($d, 'code'), 'ver' => "V" . $d['ver'], 'state' => Helper::ifNull($d, 'state'), 'low_cny' => Helper::ifNull($d, 'low_cny'), 'high_cny' => Helper::ifNull($d, 'high_cny'), 'average_cny' => Helper::ifNull($d, 'average_cny'), 'low_usd' => Helper::ifNull($d, 'low_usd'), 'high_usd' => Helper::ifNull($d, 'high_usd'), 'average_usd' => Helper::ifNull($d, 'average_usd'), 'update_time' => $d['update_time'], 'name' => Helper::ifNull($d, 'name'), 'description' => Helper::ifNull($d, 'description'), 'project_no_name' => Helper::ifNull($d, 'project_no_name'), 'bom_upd_time' => $d['bom_upd_time'], 'remark' => Helper::ifNull($d, 'remark'));
         $bomd = $this->object_array($info);
         fputcsv($file, $bomd);
     }
     fclose($file);
     $this->operate("BOM导出");
     echo $filename;
     exit;
 }
Beispiel #2
0
 /**
  * bom dosage
  * @param $id
  * @param $name
  * @return string
  */
 public function getmaterieldosageAction()
 {
     $code = $this->getRequest()->getParam('code');
     $data = array();
     if ($code) {
         $fa = new Product_Model_Fa();
         $son = new Product_Model_Son();
         $materiel = new Product_Model_Materiel();
         $catalog = new Product_Model_Catalog();
         // first step: find recordkey from bom_son
         $recordkeyData = $son->getJoinList("code = '{$code}'", array(), array('recordkey', 'code', 'qty', 'partposition', 'replace'));
         $recordkeys = $codes = $qtys = $partpositions = $replaces = array();
         foreach ($recordkeyData as $r) {
             if (!in_array($r['recordkey'], $recordkeys)) {
                 $recordkey = $r['recordkey'];
                 $recordkeys[] = $recordkey;
                 $codes[$recordkey] = $r['code'];
                 $qtys[$recordkey] = $r['qty'];
                 $partpositions[$recordkey] = $r['partposition'];
                 $replaces[$recordkey] = $r['replace'];
             }
         }
         // second step: query bom code from bom_son with recordkey
         if (count($recordkeys) > 0) {
             $join = array(array('type' => INNERJOIN, 'table' => $materiel->getName(), 'condition' => $materiel->getName() . '.code = ' . $fa->getName() . '.code', 'cols' => array('name', 'description')), array('type' => LEFTJONIN, 'table' => $catalog->getName(), 'condition' => $catalog->getName() . '.id = ' . $fa->getName() . '.project_no', 'cols' => array('project_no_name' => 'model_internal')));
             $where = $fa->getName() . ".recordkey in (" . implode(',', $recordkeys) . ") and " . $fa->getName() . ".state != 'Obsolete'";
             $data = $fa->getJoinList($where, $join, array('sid', 'recordkey', 'code', 'state', 'ver', 'bom_upd_time'));
             for ($i = 0; $i < count($data); $i++) {
                 $recordkey = $data[$i]['recordkey'];
                 $data[$i]['archive_time'] = strtotime($data[$i]['bom_upd_time']);
                 $data[$i]['qty'] = $qtys[$recordkey];
                 $data[$i]['partposition'] = $partpositions[$recordkey];
                 $data[$i]['replace'] = $replaces[$recordkey];
             }
         }
     }
     echo Zend_Json::encode($data);
     exit;
 }