Permalink
Fetching contributors…
Cannot retrieve contributors at this time
201 lines (134 sloc) 4.45 KB
<?php
class nuSqlClass{
public $STR = '';
public $OBJ = '';
function __construct($sql){
$this->STR = $sql;
$S = new PHPSQLParser($sql, true);
$this->OBJ = $S->parsed;
}
public function setMulitArray($n){
if(!isset($this->OBJ[$n])){
$this->OBJ[$n] = array();
}
}
public function removeSelectElements(){
$w = $this->OBJ['SELECT'];
$this->OBJ['SELECT'] = array();
return $w;
}
public function addSelectElement($be){
$this->setMulitArray('SELECT');
$c = array();
$c['expr_type'] = ExpressionType::CONSTANT;
$c['base_expr'] = $be;
$this->OBJ['SELECT'][] = $c;
for($i = 0 ; $i < count($this->OBJ['SELECT']) ; $i++){
$this->OBJ['SELECT'][$i]['delim'] = ',';
}
$this->OBJ['SELECT'][$i-1]['delim'] = '';
}
public function addWhereElement($be){
if(!isset($this->OBJ['WHERE'])){
$this->OBJ['WHERE'] = array();
}
$c = array();
$c['expr_type'] = ExpressionType::CONSTANT;
$c['base_expr'] = $be;
$c['sub_tree'] = false;
$c['nubuilder'] = 1;
$this->OBJ['WHERE'][] = $c;
}
public function addBracketsToWhere(){
if(!isset($this->OBJ['WHERE'])){
$this->OBJ['WHERE'] = array();
return;
}
$bo = array();
$bo['expr_type'] = ExpressionType::CONSTANT;
$bo['base_expr'] = '(';
$bo['sub_tree'] = false;
$bo['nubuilder'] = 1;
$bc = array();
$bc['expr_type'] = ExpressionType::CONSTANT;
$bc['base_expr'] = ')';
$bc['sub_tree'] = false;
$bc['nubuilder'] = 1;
array_unshift($this->OBJ['WHERE'], $bo);
$this->OBJ['WHERE'][] = $bc;
}
public function SQL(){
$s = new PHPSQLCreator($this->OBJ);
return $s->created;
}
}
//-- tests ----
function test111(){
$s = nus();
$SQL = new nuSqlClass($s);
$SQL->removeSelectElements();
$SQL->addSelectElement('cus_name');
$SQL->addSelectElement('cus_address');
$SQL->addWhereElement("and cus_name = 'bob'");
print $SQL->SQL() . '<br><br><br>';
$SQL->addBracketsToWhere();
print $SQL->SQL() . '<br><br><br>';
$Q = new nuSqlClass($SQL->SQL());
print $Q->SQL() . '<br><br><br>';
}
function nus(){
$s = "
SELECT
CONCAT(docket_type_weigh_link.docket_type_weigh_link_id, '_',IFNULL(sr.contract_id, ''),'_',IFNULL(dr.contract_id, '')) AS docket_type_lookup_id,
IFNULL(sp.pro_code, dp.pro_code) AS pro_code,
IFNULL(sr.cot_season, dr.cot_season) AS season,
docket_type.*,
docket_type_weigh_link.dtw_source_weigh AS source_weigh,
docket_type_weigh_link.dtw_destination_weigh AS destination_weigh,
sr.cot_number AS source_cot_number,
dr.cot_number AS destination_cot_number,
sr.cot_quantity AS source_cot_quantity,
dr.cot_quantity AS destination_cot_quantity,
sr.cot_source_remaining AS source_remaining,
dr.cot_destination_remaining AS destination_remaining,
sc.con_code AS source_con_code,
IF(sr.cot_multigrade='1','Y','') AS source_multi,
sgr.gra_code AS source_grade,
dgr.gra_code AS destination_grade,
dc.con_code AS destination_con_code,
sct.ctt_description AS source_description,
dct.ctt_description AS destination_description,
dot_show_in_bridge_dockets AS bridge_docket_type_filter
FROM docket_type_weigh_link
LEFT JOIN docket_type ON dtw_docket_type_id = docket_type_id
LEFT JOIN contract_type AS sct ON dot_source_contract_type_id = sct.contract_type_id
LEFT JOIN contract_type AS dct ON dot_destination_contract_type_id = dct.contract_type_id
LEFT JOIN contract AS sr ON sct.contract_type_id = sr.cot_contract_type_id
LEFT JOIN contract AS dr ON dct.contract_type_id = dr.cot_contract_type_id
LEFT JOIN product sp ON sr.cot_product_id = sp.product_id
LEFT JOIN product dp ON dr.cot_product_id = dp.product_id
LEFT JOIN contact AS sc ON sr.cot_seller_contact_id = sc.contact_id
LEFT JOIN contact AS dc ON dr.cot_buyer_contact_id = dc.contact_id
LEFT JOIN grade AS sgr ON sr.cot_grade_id = sgr.grade_id
LEFT JOIN grade AS dgr ON dr.cot_grade_id = dgr.grade_id
WHERE IFNULL(sr.cot_season,dr.cot_season) = IFNULL(dr.cot_season,sr.cot_season)
AND IFNULL(sr.cot_product_id,dr.cot_product_id) = IFNULL(dr.cot_product_id,sr.cot_product_id)
AND dot_transfer = '0'
AND IFNULL(sr.cot_status,'') != 'Completed'
AND IFNULL(dr.cot_status,'') != 'Completed'
AND
(
sr.cot_grade_id = dr.cot_grade_id
OR
IFNULL(sr.cot_grade_id,'') = ''
OR
IFNULL(dr.cot_multigrade,'1') = '1'
OR
IFNULL(sr.cot_grade_id,'') = ''
OR
IFNULL(dr.cot_multigrade,'1') = '1'
)
";
return $s;
}
?>