public function testQueryDatabase() { $objDB = class_carrier::getInstance()->getObjDB(); $arrFields = array(); $arrFields["temp_systemid"] = array("char40", true); $arrFields["temp_name"] = array("char254", true); $this->assertTrue($objDB->createTable("temp_propertyintest", $arrFields, array("temp_systemid")), "testDataBase createTable"); echo "\tcreating 50 records...\n"; $arrValues = array(); for ($intI = 1; $intI <= 50; $intI++) { $arrValues[] = array(generateSystemid(), "text " . $intI); } $this->assertTrue($objDB->multiInsert("temp_propertyintest", array("temp_systemid", "temp_name"), $arrValues)); $arrParams = array(); for ($intI = 1; $intI <= class_orm_objectlist_in_restriction::MAX_IN_VALUES + 10; $intI++) { $arrParams[] = generateSystemid(); } $arrParams[] = $arrValues[0][0]; $objRestriction = new class_orm_objectlist_in_restriction("temp_systemid", $arrParams); $arrResult = $objDB->getPArray("SELECT * FROM " . _dbprefix_ . "temp_propertyintest WHERE 1=1 " . $objRestriction->getStrWhere(), $objRestriction->getArrParams()); $this->assertEquals("text 1", $arrResult[0]["temp_name"]); }
/** * Returns the latest new_value in the date range per systemid * * @param $strClass * @param $strProperty * @param class_date $objDateFrom * @param class_date $objDateTo * @param array $arrAllowedSystemIds * @return array */ public static function getNewValuesForDateRange($strClass, $strProperty, class_date $objDateFrom = null, class_date $objDateTo = null, array $arrAllowedSystemIds) { $arrParams = array($strClass, $strProperty); //system id filter $objRestriction = new class_orm_objectlist_in_restriction("log.change_systemid", $arrAllowedSystemIds); $strQueryCondition = $objRestriction->getStrWhere(); $arrParams = array_merge($arrParams, $objRestriction->getArrParams()); //filter by create date from if ($objDateFrom != null) { $objRestriction = new class_orm_objectlist_restriction("AND ( log.change_date >= ? )", array($objDateFrom->getLongTimestamp())); $strQueryCondition .= $objRestriction->getStrWhere() . " "; $arrParams[] = $objDateFrom->getLongTimestamp(); } //filter by create end to if ($objDateTo != null) { $objRestriction = new class_orm_objectlist_restriction("AND ( log.change_date <= ? )", array($objDateTo->getLongTimestamp())); $strQueryCondition .= $objRestriction->getStrWhere() . " "; $arrParams[] = $objDateTo->getLongTimestamp(); } $strQuery = " SELECT change_systemid,\n change_newvalue\n FROM " . _dbprefix_ . self::getTableForClass($strClass) . " log\n WHERE log.change_class = ?\n AND log.change_property = ?\n {$strQueryCondition}\n ORDER BY log.change_systemid ASC, log.change_date DESC"; $arrResult = class_carrier::getInstance()->getObjDB()->getPArray($strQuery, $arrParams); $arrData = array(); $strLastId = ""; foreach ($arrResult as $arrRow) { if ($strLastId != $arrRow["change_systemid"]) { $arrData[] = array("change_systemid" => $arrRow["change_systemid"], "change_newvalue" => $arrRow["change_newvalue"]); } $strLastId = $arrRow["change_systemid"]; } return $arrData; }