/** * 按条件查询当前库存 * * @param String $condition * @return Array 表架数组 */ public function queryCurrentStocksStructs($condition) { try { $dao = new CurrentStocksDao(); $data = array(); //查询可用的表架 switch (DB_TYPE) { case 'mysql': $sql = "select SUBSTRING(station,1,4) as station\n\t\t\t\t\t\t\t from " . (DB_PREFIX ? DB_PREFIX . "_" : "") . "currentstocks\n\t\t\t\t\t\t group by SUBSTRING(station,1,4)\n\t\t\t\t\t\t order by SUBSTRING(station,1,4) "; break; case 'SQLite': $sql = "select substr(station,1,4) as station\n\t\t\t\t\t\t\t from " . (DB_PREFIX ? DB_PREFIX . "_" : "") . "currentstocks\n\t\t\t\t\t\t group by substr(station,1,4)\n\t\t\t\t\t\t order by substr(station,1,4) "; break; default: return false; } $struct = $dao->query($sql)->toArray(); //按表架查询 for ($i = 0; $i < sizeof($struct); $i++) { $item = array(); $stations = array(); $sql = "select station\n\t\t\t\t from " . (DB_PREFIX ? DB_PREFIX . "_" : "") . "currentstocks\n\t\t\t\t where station like '" . $struct[$i]["station"] . "%'" . " and " . $condition; $result = $dao->query($sql)->toArray(); for ($j = 0; $j < sizeof($result); $j++) { array_push($stations, $result[$j]["station"]); } $item["No"] = $struct[$i]["station"]; $item["value"] = $stations; array_push($data, $item); } return $data; } catch (Executive $e) { $this->setError("按表架方式查询数据出错,errcode:{$condition}"); throw new Exception($e); } }
/** * 库存一览表 * * @param string $condition */ public function queryCurrentStocksGeneral($condition) { try { $storage = new StorageDao(); $sql = "select storageNo,storageName from " . (DB_PREFIX ? DB_PREFIX . "_" : "") . "storage where stype = '库房' or stype='库区' order by storageNo"; $storageList = $storage->query($sql)->toArray(); $stocks = new CurrentStocksDao(); $sql = "select goodsName,spec from " . (DB_PREFIX ? DB_PREFIX . "_" : "") . "currentstocks "; if ($condition != '') { $sql = $sql . " where " . $condition; } $sql .= "group by goodsName,spec"; //echo $sql ; $goodsList = $stocks->query($sql)->toArray(); //数据 $data = array(); for ($i = 0; $i < sizeof($goodsList); $i++) { $item = array(); $item["goodsName"] = $goodsList[$i]["goodsName"]; $item["spec"] = $goodsList[$i]["spec"]; //计算每个仓库的值 foreach ($storageList as $place) { $sql = "goodsName = '" . $goodsList[$i]["goodsName"] . "' and spec ='" . $goodsList[$i]["spec"] . "' and placeno like'" . $place['storageNo'] . "%'"; if ($condition != '') { $sql = $sql . " and " . $condition; } //echo $sql ."<br>"; $sum = $stocks->getSum('qty', $sql); $item[$place['storageNo']] = $sum; } array_push($data, $item); } //计算合计 $sumrecord['goodsName'] = '合计:'; foreach ($storageList as $place) { $sumrecord[$place['storageNo']] = ExFunction::ex_array_sum($data, $place['storageNo']); } array_push($data, $sumrecord); //表的结构 $item = array("goodsName", "spec"); $label = array("goodsName" => "品名", "spec" => "型号"); foreach ($storageList as $place) { array_push($item, $place['storageNo']); $label[$place['storageNo']] = $place['storageName']; } $columnitem = $item; $enabled = array("_type_" => "*"); $visible = array("_type_" => "*"); $align = array(); $width = array("qty" => 50); $inputtype = array(); $struct["item"] = $item; $struct["label"] = $label; $struct["columnitem"] = $columnitem; $struct["enabled"] = $enabled; $struct["visible"] = $visible; $struct["align"] = $align; $struct["width"] = $width; $struct["inputtype"] = $inputtype; //返回值 $rtn["struct"] = $struct; $rtn["data"] = $data; return $rtn; } catch (Exception $e) { throw new Exception($e); } }