<?
/*-------------------------------------------------------------------------
	　唐津花市場Webサイト構築
	
	SQL生成共有関数
	
		SqlManager.inc
	
	説明        ：  SQL生成関数などを格納
	作成日時    ：  2010/11/22
	作成者      ：  K.Matsuo

---------------------------------------------------------------------------*/
Class SqlManager
{

	// Class 変数定義
	var $sSql       = "";       // SQL文
	var $sCntSql    = "";       // COUNT構文用SQL文
	var $sTable     = "";       // 照会対象テーブル名
	var $sField     = "";       // 照会対象フィールド名
	var $sWhere     = "";       // 抽出条件
	var $sHaving    = "";       // 抽出条件
	var $sOrder     = "";       // ソート順
	var $sDescFlg   = "0";      // ソート　設定フラグ
								//	0：昇順
								//	1：降順（DESC付き）
	var $aryFields  = null;     // insert,update 処理対象フィールド
	var $aryValues  = null;     // insert,update 処理設定値

	var	$aryBinds   = null;     // バインド変数リスト



/*-------------------------------------------------------------------------
	SqlManager()
	
		[説明]
				コンストラクタ
		[引数]
				なし
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SqlManager()
{
}



/*-------------------------------------------------------------------------
	GetSql()
	
		[説明]
				SQL文の取得
		[引数]
				なし
		[return]
				$sSql
	
---------------------------------------------------------------------------*/
function GetSql()
{
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	ClearSql()
	
		[説明]
				SQLの初期化
		[引数]
				なし
		[return]
				なし
	
---------------------------------------------------------------------------*/
function ClearSql()
{
	$this->sTable       = "";
	$this->sField       = "";
	$this->sWhere       = "";
	$this->sOrder       = "";
	$this->aryFields    = null;
	$this->aryValues    = null;
	$this->aryBinds     = null;  // バインド変数リスト
}




/*-------------------------------------------------------------------------
	SetTableName()
	
		[説明]
				テーブル名の指定
		[引数]
				$sData
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetTableName($sData)
{
	$this->sTable = $sData;
}



/*-------------------------------------------------------------------------
	SetFieldName()
	
		[説明]
				フィールド名の指定
		[引数]
				$sData
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetFieldName($sData)
{
	if ($this->sField != "") {
		$this->sField .= " , ". $sData;
	} else {
		$this->sField = $sData;
	}
}



/*-------------------------------------------------------------------------
	SetWhereCondition()
	
		[説明]
				Where条件文の追加
		[引数]
				$sData
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetWhereCondition($sData)
{
	if ($this->sWhere != "") {
		$this->sWhere .= " AND ". $sData;
	} else {
		$this->sWhere = $sData;
	}
}



/*-------------------------------------------------------------------------
	SetHavingCondition()
	
		[説明]
				Having条件文の追加
		[引数]
				$sData
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetHavingCondition($sData)
{
	if ($this->sHaving != "") {
		$this->sHaving .= " AND ". $sData;
	} else {
		$this->sHaving = $sData;
	}
}



/*-------------------------------------------------------------------------
	SetLikeCondition()
	
		[説明]
				Where条件、Likeワイルドカード条件の追加
		[引数]
				$sColum	:検索カラム名
				$sData	:データ条件
		[usage]
				SetLikeCondition ("COLUMNAME", "2000__");
				SetLikeCondition ("COLUMNAME", "%2000%");
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetLikeCondition($sColum, $sData)
{
	if ($this->sWhere != "") {
		$this->sWhere .= " AND ". $sColum." LIKE '".$sData."' ";
	} else {
		$this->sWhere = $sColum." LIKE '".$sData."' ";
	}
}



/*-------------------------------------------------------------------------
	GetWhereCondition()
	
		[説明]
				Where条件文の取得
		[引数]
				なし
		[return]
				$sWhere
	
---------------------------------------------------------------------------*/
function GetWhereCondition()
{
	return $this->sWhere;
}



/*-------------------------------------------------------------------------
	SetOrderName()
	
		[説明]
				Order文の指定
		[引数]
				$sData
		[return]
				なし
	
---------------------------------------------------------------------------*/
function SetOrderName($sData, $sDescFlg)
{
	if ($this->sOrder != "") {
		$this->sOrder .= " , ". $sData;
	} else {
		$this->sOrder = $sData;
	}
	if ($sDescFlg == "1") {
		$this->sOrder .= " DESC ";
	}
}




/*-------------------------------------------------------------------------
	AddValue()
	
		[説明]
				Insert,Update文で使用するフィールド名と値の設定
		[引数]
				$sField
				$sValue
		[return]
				なし
	
---------------------------------------------------------------------------*/
function AddValue($sField, $sValue)
{
	$i = sizeof($this->aryFields);
	$this->aryFields[$i] = $sField;
	$this->aryValues[$i] = $sValue;
}




/*-------------------------------------------------------------------------
	MakeSelectSql()
	
		[説明]
				指定項目を引数として生成されたSelect文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeSelectSql()
{
	// SQL生成
	$this->sSql =  "";
	if ($this->sField != "") {
		$this->sSql .= " SELECT ". $this->sField ." FROM ";
	} else {
		$this->sSql .= " SELECT * FROM ";
	}
	$this->sSql .= " ". $this->sTable ." ";
	if ($this->sWhere != "") {
		$this->sSql .= " WHERE ";
		$this->sSql .= $this->sWhere;
	}
	if ($this->sOrder != "") {
		$this->sSql .= " ORDER BY ";
		$this->sSql .= $this->sOrder;
	}
	// return
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	MakeGroupSql()
	
		[説明]
				指定項目を引数として生成されたGroup文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeGroupSql()
{
	// SQL生成
	$this->sSql =  "";
	$this->sSql .= " SELECT ". $this->sField ." FROM ";
	$this->sSql .= " ". $this->sTable ." ";
	if ($this->sWhere != "") {
		$this->sSql .= " WHERE ";
		$this->sSql .= $this->sWhere;
	}
	$this->sSql .= " GROUP BY ";
	$this->sSql .= $this->sField;
	if ($this->sHaving != "") {
		$this->sSql .= " HAVING ";
		$this->sSql .= $this->sHaving;
	}
	if ($this->sOrder != "") {
		$this->sSql .= " ORDER BY ";
		$this->sSql .= $this->sOrder;
		if ($this->sDescFlg == "1") {
			$this->sSql .= " DESC ";
		}
	}
	// return
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	MakeDeleteSql()
	
		[説明]
				指定項目を引数として生成されたDelete文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeDeleteSql()
{
	// SQL生成
	$this->sSql =  "";
	$this->sSql .= " DELETE FROM ";
	$this->sSql .= " ". $this->sTable ." ";
	if ($this->sWhere != "") {
		$this->sSql .= " WHERE ";
		$this->sSql .= $this->sWhere;
	}
	// return
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	MakeInsertSql()
	
		[説明]
				指定項目を引数として生成されたInsert文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeInsertSql()
{
	// SQL生成
	$i = 0;
	$j = 0;
	$this->sSql =  "";
	$this->sSql .= " INSERT INTO ";
	$this->sSql .= " ". $this->sTable ." (";
	while ($i < sizeof($this->aryFields)){
		$this->sSql .= $this->aryFields[$i];
		$i++;
		if ($i != sizeof($this->aryFields)) {
			$this->sSql .= ", ";
		}
	}
	$this->sSql .= ") VALUES (";
	while ($j < sizeof($this->aryFields)){
		$this->sSql .= $this->aryValues[$j];
		$j++;
		if ($j != sizeof($this->aryFields)) {
			$this->sSql .= ",";
		}
	}
	$this->sSql .= ")";
	// return
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	MakeUpdateSql()
	
		[説明]
				指定項目を引数として生成されたUpdate文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeUpdateSql()
{
	// SQL生成
	$i	= 0;
	$this->sSql =  "";
	$this->sSql .= " UPDATE ";
	$this->sSql .= " ". $this->sTable ." ";
	$this->sSql .= " SET ";
	while ($i < sizeof($this->aryFields)){
		$this->sSql .= " ". $this->aryFields[$i] ." = ". $this->aryValues[$i];
		$i++;
		if ($i != sizeof($this->aryFields)) {
			$this->sSql .= ", ";
		}
	}
	if ($this->sWhere != "") {
		$this->sSql .= " WHERE ";
		$this->sSql .= $this->sWhere;
	}
	// return
	return $this->sSql;
}




/*-------------------------------------------------------------------------
	MakeCountSql()
	
		[説明]
				指定項目を引数として生成されたCount文のSQLを返す。
		[引数]
				なし
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeCountSql()
{
	// SQL生成
	$this->sCntSql =  "";
		$this->sCntSql .= " SELECT COUNT(*) FROM ";
	$this->sCntSql .= " ". $this->sTable ." ";
	if ($this->sWhere != "") {
		$this->sCntSql .= " WHERE ";
		$this->sCntSql .= $this->sWhere;
	}
	// return
	return $this->sCntSql;
}





/*-------------------------------------------------------------------------
	MakeProcedureSql()
	
		[説明]
				プロシージャコール。
		[引数]
				$aryProData     :プロシージャへ渡す引数
							[0] :プロシージャ名
							[x] :引数
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeProcedureSql($aryProData)
{
	// SQL生成
	$this->sSql =  "";
	$this->sSql .= "BEGIN ";
	$this->sSql .= $aryProData[0]." ( ";
	for ($i = 1; $i < sizeof ($aryProData); $i++) {
		$this->sSql .= "'".$aryProData[$i]."' ";
		if ($i+1 < sizeof ($aryProData)) {
			$this->sSql .= ", ";
		}
	}
	$this->sSql .= "); ";
	$this->sSql .= "END;";
	// return
	return $this->sSql;
}



/*-------------------------------------------------------------------------
	MakeTypeProcedureSql()
	
		[説明]
				プロシージャコール。
		[引数]
				$aryProData     :プロシージャへ渡す引数
							[0] :プロシージャ名
							[x] :引数
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeTypeProcedureSql($aryProData, $aryType)
{
	// SQL生成
	$this->sSql =  "";
	$this->sSql .= "BEGIN ";
	$this->sSql .= $aryProData[0]." ( ";
	for ($i = 1; $i < sizeof ($aryProData); $i++) {
		if ($aryType[($i-1)] == 1) {
			$this->sSql .= "".$aryProData[$i]." ";
		} else {
			$this->sSql .= "'".$aryProData[$i]."' ";
		}
		if ($i+1 < sizeof ($aryProData)) {
			$this->sSql .= ", ";
		}
	}
	$this->sSql .= "); ";
	$this->sSql .= "END;";
	// return
	return $this->sSql;
}



/*-------------------------------------------------------------------------
	MakeFunctionSql()
	
		[説明]
				ファンクションコール。
		[引数]
				$aryProData     :ファンクションへ渡す引数
							[0] :ファンクション名
							[x] :引数
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeFunctionSql($aryProData)
{
	// SQL生成
	$sResult = "";
	$this->sSql =  "";
	$this->sSql .= "select ";
	$this->sSql .= $aryProData[0]." ( ";
	for ($i = 1; $i < sizeof ($aryProData); $i++) {
		$this->sSql .= "'".$aryProData[$i]."' ";
		if ($i+1 < sizeof ($aryProData)) {
			$this->sSql .= ", ";
		}
	}
	$this->sSql .= ") as Result from dual";

	// return
	return $this->sSql;
}


/*-------------------------------------------------------------------------
	MakeFunctionSqlPG()
	
		[説明]
				ファンクションコール。for PostgreSQL
		[引数]
				$aryProData     :ファンクションへ渡す引数
							[0] :ファンクション名
							[x] :引数
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeFunctionSqlPG($aryProData)
{
	// SQL生成
	$sResult = "";
	$this->sSql =  "";
	$this->sSql .= "select * from ";
	$this->sSql .= $aryProData[0]." ( ";
	for ($i = 1; $i < sizeof ($aryProData); $i++) {
		$this->sSql .= $aryProData[$i];
		if ($i+1 < sizeof ($aryProData)) {
			$this->sSql .= ", ";
		}
	}
	$this->sSql .= ")";

	// return
	return $this->sSql;
}


/*-------------------------------------------------------------------------
	MakeDirectSql()
	
		[説明]
				SQLセット（ダイレクト）
		[引数]
				$sDirectSql     :ファンクションへ渡す引数
		[return]
				SQL文
	
---------------------------------------------------------------------------*/
function MakeDirectSql($sDirectSql){
	$sResult = "";
	$this->sSql = $sDirectSql;
	return $this->sSql; //他関数互換
}


/*-------------------------------------------------------------------------
	EscapeLikeString()
	
		[説明]
				LIKE句の特殊文字をエスケープする
		[引数]
				LIKE句文字列
		[return]
				エスケープされたLIKE句文字列
	
---------------------------------------------------------------------------*/
function EscapeLikeString($sLike)
{
	$sLike = str_replace("\\", "\\\\", $sLike);
    $sLike = str_replace("%", "\%", $sLike);
    $sLike = str_replace("_", "\_", $sLike);

	return $sLike;
}



/*-------------------------------------------------------------------------
	AddBindByName()
	
		[説明]
				PHP変数をバインドする設定
		[引数]
				$sName
				$sValue
				$sLen
				$sType
		[return]
				なし
	
---------------------------------------------------------------------------*/
function AddBindByName($sName, $sValue, $sLen = -1, $sType = 0)
{
	$i = sizeof($this->aryBinds);

	$this->aryBinds[$i] = array();
	$this->aryBinds[$i]['NAME'] = $sName;
	$this->aryBinds[$i]['VALUE'] = $sValue;
	$this->aryBinds[$i]['LENGTH'] = $sLen;
	$this->aryBinds[$i]['TYPE'] = $sType;
}



}
?>