📄 mydb.php

📁 FP2 CRM code+Mysql DB
💻 PHP
字号:
<?php

require_once 'DB/mysql.php';

class MyDb extends DB_mysql
{
	var $pageBase;
	function MyDb($pageBase)
	{
		$this->pageBase = $pageBase;
		$dsn = 'mysql://'.APP_DB_USER.':'.APP_DB_PASSWORD.'@'.APP_DB_HOST.'/'.APP_DB_DB;
		$dbOptions = array('debug' => 2,'portability' => DB_PORTABILITY_ALL);
	    $dsninfo = DB::parseDSN($dsn);
        $err = $this->connect($dsninfo, $this->getOption('persistent'));
        if (DB::isError($err)) {
            $err->addUserInfo($dsn);
            return $err;
        }
		$this->setFetchMode(DB_FETCHMODE_ASSOC);
	}
	
	function db_getRs($table, $filter='', $sort='', $lim='') 
	{
	   $sql = 'SELECT * FROM ' . TPF.$table;   
	   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
	   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
	   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
	   
		$res =& $this->query($sql);
		if (PEAR::isError($res)){$this->db_error($res);}
	    return $res;
	}

	function getRs($table, $filter='', $sort='', $lim='') 
	{
	   $sql = 'SELECT * FROM ' . TPF.$table;   
	   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
	   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
	   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
	   
		$res =& $this->query($sql);
		if (PEAR::isError($res)){$this->db_error($res);}
	    return $res;
	}

	/**/
	function getRow($table, $filter='', $sort='', $lim='') 
	{
	   $sql = 'SELECT * FROM ' . TPF.$table;   
	   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
	   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
	   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
	   
		$res =& $this->query($sql);
		if (PEAR::isError($res)){$this->db_error($res);}
		if($res->fetchInto($row))
		{
		    return $row;
		}
		
		return '';
	}

//-----------------------------------------

	function getRowCount($table, $filter='', $sort='', $lim='') 
	{
		$sql = 'SELECT * FROM ' . TPF.$table;   
	   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
	   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
	   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
   	  //echo'<br />'. $sql;
		$res =& $this->query($sql);
		if (PEAR::isError($res)){$this->db_error($res);}
		if($res->fetchInto($row))
		{
			return $res->numRows();
		}
		
		return 0;
	}

//-----------------------------------------

function getMaxId($table)
	{
			$sql = 'SELECT max(id) as id from '.TPF.$table;
			$res =& $this->query($sql);
			if (PEAR::isError($res)) {$this->db_error($res);return;}
			if($res->fetchInto($row))
				return $row['id'];
				
			return '0';
	}

//-----------------------------------------------------------------
function db_getMaxId($table)
	{
			$sql = 'SELECT max(id) as id from '.TPF.$table;
			$res =& $this->query($sql);
			if (PEAR::isError($res)) {$this->db_error($res);return;}
			if($res->fetchInto($row))
				return $row['id'];
				
			return '0';
	}
	
//-----------------------------------------------------------------
	function getDate($table,$fieldName,$filter)
	{
		
			$sql = "SELECT date_format(t.".$fieldName.",'%W, %d %M %Y') as mydate FROM ".TPF.$table." t where ".$filter;
			$res =& $this->query($sql);
			if (PEAR::isError($res)) {$this->db_error($res);return;}
			if($res->fetchInto($row))
				return $row['mydate'];
				
			return '';
	}

//-----------------------------------------------------
	function getField($table,$fieldName,$filter)
	{
			$this->setFetchMode(DB_FETCHMODE_ASSOC);
			$sql = 'SELECT * FROM '.TPF.$table.' where '.$filter;
			$res =& $this->query($sql);
			if (PEAR::isError($res)) {$this->db_error($res);return;}
			if($res->fetchInto($row))
				return $row[$fieldName] ;
				
			return '';
	}
//-----------------------------------------------------------------

	function db_getField($table,$fieldName,$filter)
	{
			$this->setFetchMode(DB_FETCHMODE_ASSOC);
			$sql = 'SELECT * FROM '.TPF.$table.' where '.$filter;
			$res =& $this->query($sql);
			if (PEAR::isError($res)) {$this->db_error($res);return;}
			if($res->fetchInto($row))
				return $row[$fieldName];
				
			return '';
	}
//-----------------------------------------------------------------
	function delete($table,$filter)
	{
			$noChildFound = true;
			$id = $this->getField($table,'id',$filter);
			if(!empty($id))
			{
				//return $noChildFound;

				$this->setFetchMode(DB_FETCHMODE_ASSOC);
				$sql = 'SELECT * FROM tablejoins where parentname = "'.$table.'"';
				 $res =& $this->query($sql);
				if (PEAR::isError($res)) {$this->db_error($res);return;}
				$childTables = array ();
				while($res->fetchInto($row))
				{
					$childTables[$row['childname']] = $row['fkname'];
					$childTablesCol[$row['childname']] = $row['childtitlecol'];
				}
				
				foreach($childTables as $ctable => $fk)
				{
					$titleCol= $childTablesCol[$ctable];
					$child = $this->getField($ctable,$titleCol,$fk.' = '.$id);
					$childID = $this->getField($ctable,'id',$fk.' = '.$id);
					if(!empty($child))
					{
						$str = 'This '.$table.'Entity is using in Table <b>'.$ctable.
										'</b> Under Id (<b>'.$childID.'</b>) Record OF  <b>'.$child.'</b> (Please First Remove Child Table Entry To Delete it.)';
					   $this->addError($str);

						$noChildFound = false;
					}
				} 
		}			
	 
			if($noChildFound)
			{
				$sql='delete from '.TPF.$table . ' where '. $filter;
				$res = $this->query($sql); if (PEAR::isError($res))
				{$this->db_error($res);	return;}	
			}
		return $noChildFound;
	}
//-----------------------------------------------------------------
//-----------------------------------------------------------------
	function db_delete($table,$filter)
	{
			$noChildFound = true;
			$id = $this->getField($table,'id',$filter);
			if(!empty($id))
			{
				//return $noChildFound;

				$this->setFetchMode(DB_FETCHMODE_ASSOC);
				$sql = 'SELECT * FROM tablejoins where parentname = "'.$table.'"';
				 $res =& $this->query($sql);
				if (PEAR::isError($res)) {$this->db_error($res);return;}
				$childTables = array ();
				while($res->fetchInto($row))
				{
					$childTables[$row['childname']] = $row['fkname'];
					$childTablesCol[$row['childname']] = $row['childtitlecol'];
				}
				
				foreach($childTables as $ctable => $fk)
				{
					$titleCol= $childTablesCol[$ctable];
					$child = $this->getField($ctable,$titleCol,$fk.' = '.$id);
					$childID = $this->getField($ctable,'id',$fk.' = '.$id);
					if(!empty($child))
					{
						$str = 'This '.$table.'Entity is using in Table <b>'.$ctable.
										'</b> Under Id (<b>'.$childID.'</b>) Record OF  <b>'.$child.'</b> (Please First Remove Child Table Entry To Delete it.)';
					   $this->addError($str);

						$noChildFound = false;
					}
				} 
		}			
	 
			if($noChildFound)
			{
				$sql='delete from '.TPF.$table . ' where '. $filter;
				$res = $this->query($sql); if (PEAR::isError($res))
				{$this->db_error($res);	return;}	
			}
		return $noChildFound;
	}
//-----------------------------------------------------------------

function getHtmlSelectTags($arrParam, $filter='', $sort='', $lim='') {
   $sql = 'SELECT ' . $arrParam['valueCol'] . ', ' . $arrParam['labelCol'] . ' FROM ' . TPF.$arrParam['table'];   
   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
   
	$this->setFetchMode(DB_FETCHMODE_ASSOC);
	$res =& $this->query($sql);
	if (PEAR::isError($res)){$this->db_error($res);	return;}
	$opt = "<option value=''>- - - - - - - - - -</option>";
	while($res->fetchInto($row))
	{
		$opt = $opt."<option value='".$row[$arrParam['valueCol']]."'>".$row[$arrParam['labelCol']]."</option>";		
    }	
   return $opt;
}

//-------------------------------------------------
function getHtmlSelectTagsSQL($sql,$arrParam,$selected='') {

	$res =& $this->query($sql);
	if (PEAR::isError($res)){$this->db_error($res);	return;}
	
	$opt = "<option value=''>- - - - - - - - - -</option>\n";
	while($res->fetchInto($row))
	{
//	  $arOptinos[$row[$arrParam['valueCol']]] = $row[$arrParam['labelCol']];
		$tmpSelected = '';
		if( $row[$arrParam['valueCol']] == $selected)
				$tmpSelected = 'selected';
			
		$opt = $opt."<option value='".$row[$arrParam['valueCol']]."' ".$tmpSelected.">".$row[$arrParam['labelCol']]."</option>\n";
    }   
   return $opt;
}
//-----------------------------------------

function getHtmlSelectArray($arrParam, $filter='', $sort='', $lim='') {
   $sql = 'SELECT ' . $arrParam['valueCol'] . ', ' . $arrParam['labelCol'] . ' FROM ' . TPF.$arrParam['table'];   
   if(!empty($filter)) $sql .= ' WHERE ' . $filter;
   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
   
	$this->setFetchMode(DB_FETCHMODE_ASSOC);
	$res =& $this->query($sql);

	if (PEAR::isError($res)){$pageBase->db_error($res);	return;}
	$retVal = '';
	$arOptinos = array();
	$arOptinos[''] = '- - - - - - - - - -';
	while($res->fetchInto($row))
	{
	  $arOptinos[$row[$arrParam['valueCol']]] = $row[$arrParam['labelCol']];
    }   
   return $arOptinos;
}

function getHtmlSelectArraySQL($sql,$arrParam) {

	$res =& $this->query($sql);

	if (PEAR::isError($res)){$this->db_error($res);	return;}
	$retVal = '';
	$arOptinos = array();
	$arOptinos[''] = '- - - - - - - - - -';
	while($res->fetchInto($row))
	{
	  $arOptinos[$row[$arrParam['valueCol']]] = $row[$arrParam['labelCol']];
    }   
   return $arOptinos;
}
//-----------------------------------------
function db_getHtmlTreeSelect($arrParam, $parent, $selected='', $pad='', $filter='', $sort='', $lim='') {
   $sql = 'SELECT ' . $arrParam['valueCol'] . ', ' . $arrParam['labelCol'] . ' FROM ' . TPF . $arrParam['table'] . 
          ' WHERE ' . $arrParam['parentCol'] . '=' . $parent;

   if(!empty($filter)) $sql .= ' AND ' . $filter;
   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
   
   
  	$this->setFetchMode(DB_FETCHMODE_ASSOC);   
    $rs =& $this->query($sql);	
	if (PEAR::isError($rs)){$this->db_error($rs);	die();}
	
   $retVal = '';
   
   while($rs->fetchInto($row)) {
      $sSeleccted = ($row[$arrParam['valueCol']] == $selected) ? ' selected' : '';
	  
	  $retVal .= '<option value="' . $row[$arrParam['valueCol']] . '"' . $sSeleccted . '>' . $pad . $row[$arrParam['labelCol']] .
	             '</option>' . "\n";
	  
	  $retVal .= $this->db_getHtmlTreeSelect($arrParam, $row[$arrParam['idCol']], $selected, '&nbsp;' . $pad . '-&nbsp;', $filter, $sort, $lim);
   } 
   return $retVal;
}

//----------------------------------------------------------------------------------
function db_getHtmlTreeMenu($arrParam, $parent, $selected='', $pad='', $filter='', $sort='', $lim='') 
{
		  $sql = 'SELECT *' . 
			' FROM ' . TPF . $arrParam['table'] .		   
            ' WHERE ' . $arrParam['parentCol'] . '=' . $parent;
	global $g;
	
   if(!empty($filter)) $sql .= ' AND ' . $filter;
   if(!empty($sort)) $sql .= ' ORDER BY ' . $sort;
   if(!empty($lim)) $sql .= ' LIMIT ' . $lim;
   
   $rs = $this->db->query($sql);  
   if (PEAR::isError($rs)){$this->db_error($rs);}
	 
	$tCount =  $rs->numRows();
    $retVal = '';
	$coma = ",";
 if($tCount > 0)
 {
	if($pad == '')
	  $retVal .= "\n[";
	  $curCount=0;
	   while($rs->fetchInto($row)) 
	   {	   
		   $sep = '';
			global $g;
			if($pad == '' and $g != 1)		{		   $sep = '_cmSplit,';				}
			if($row['link'] == '')
				$link = '';//	"cms.php?pageId=".$row['id'];
			else
				$link = APP_WEB_ROOT.'/'.$row['link'];
				
			  $coma = ($g == 1) ?   $coma='' : ', ';
			  $sSeleccted = ($row[$arrParam['valueCol']] == $selected) ? ' selected' : '';
			  $retVal .= "\n".$coma.$sep." ['&nbsp;&nbsp;','".
			  $row[$arrParam['labelCol']]."','".
			  $link."','".
			  $row['target']."','".
			  $row['description'].'('.$row['id'].")' ";
			  $g = 0;
			  $curCount++;
			  $retVal .= $this->db_getHtmlTreeMenu($arrParam, $row[$arrParam['idCol']], $selected, '&nbsp;' . $pad . '&nbsp;', $filter, $sort, $lim);
			  $tCount--;
			  $retVal .= ']' . "\n";	   
	   }
	if($pad == '')
		 $retVal .= "]";
 }   
 /**/
   return $retVal;
}

///----------------------------------------------------------------	

	function save($table,$dbArray,$id='')
	{
//		debug_var($dbArray);
		if(empty($id))
		{		
			$res= $this->autoExecute(TPF.$table, $dbArray, DB_AUTOQUERY_INSERT);	
			if (PEAR::isError($res)) {$vendorObj->db_error($res);return;}
			$msg = '1';
			$id = $this->getMaxId($table);
			return $id;
		}
		else
		{	
			$res = $this->autoExecute(TPF.$table, $dbArray,DB_AUTOQUERY_UPDATE, "id = ".$id);
			if (PEAR::isError($res)) {$pageBase->db_error($res);return;}
				$msg='2';
			return $id;
		}
	}

}//end of class
?>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -