<?php

function createQuery ( )

// builds the appropriate SQL based on the paramaters sent through

// coded to ensuring querying the database in ajax is secure and SQL is not present in any urls

{

$sqlCode = $_REQUEST [ 'sqlCode' ] ;

switch ( $sqlCode )

{

case 1 : // comm_comms.js new_comm_note_save()

$query = "SELECT COUNT(comm_id) AS notecount FROM tbl_comm_notes WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 2 : // comm_comms.js new_comm_note_save()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

$query = "INSERT INTO tbl_comm_notes (comm_id, note_by, note_detail, visible_to_client, visible_to_contractor, note_type) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",'" . pg_escape_string ( unescape ( $_POST [ 'sqlParam3' ] ) ) . "'," . ReturnforQuery ( $sqlParam4 ) . "," . ReturnforQuery ( $sqlParam5 ) . ",2)" ;

break ;

case 3 : // comm_comms.js open_comm_window()

$query = "SELECT COUNT(comm_id) AS attachcount FROM tbl_comm_attachment WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) . " and deleted is null" ;

break ;

case 4 : // comm_comms.js comm_save()

$query = "UPDATE tbl_communication SET client_ref = ' $sqlParam1 '"

. ", title = ' $sqlParam2 '"

. ", _ref = ' $sqlParam3 '"

. ", spatial_north = $sqlParam5 "

. ", spatial_east = $sqlParam6 "

// . ", spatial_geom = '$sqlParam7'"

. ", ward = ' $sqlParam8 '"

. ", other = ' $sqlParam9 '"

// . ", ram_data_id = $sqlParam10"

// . ", linear_from = '$sqlParam11'"

// . ", linear_to = '$sqlParam12'"

. ", file_location = ' $sqlParam13 '"

. ", type = $sqlParam14 "

. ", _owner_id = $sqlParam15 "

. ", linear_whole_network = $sqlParam16 "

. ", medium = $sqlParam17 "

// . ", spatial_datum = '$sqlParam14'"

. " WHERE comm_id = " . ReturnforQuery ( $sqlParam4 ) ;

break ;

case 5 : // comm_comms.js choose_project_dbl_click()

$query = "SELECT project_id FROM tbl_task WHERE task_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 6 : // comm_comms.js newCommNext()

$query = "SELECT comm_number_last FROM tbl_contract WHERE contract_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 7 : // comm_comms.js newCommNext()

$query = "SELECT nextval('tbl_communication_comm_id_seq') AS nextcommid" ;

break ;

case 8 : // comm_comms.js newCommNext()

$query = "INSERT INTO tbl_communication (comm_id

,comm_number

,contract_id

,date_received

,status

,medium

,client_ref

,title

,created_by_

,_owner_id

,_ref

,file_location

,type) VALUES (

" . ReturnforQuery ( $sqlParam1 )

. "," . ReturnforQuery ( $sqlParam2 )

. "," . ReturnforQuery ( $sqlParam3 )

. ",'" . $sqlParam4 . "',1,"

. ReturnforQuery ( $sqlParam5 )

. ",'" . pg_escape_string ( unescape ( $_POST [ 'sqlParam6' ] ) )

. "','" . pg_escape_string ( unescape ( $_POST [ 'sqlParam7' ] ) )

. "'," . ReturnforQuery ( $sqlParam8 ) . ","

. ReturnforQuery ( $sqlParam9 ) . ",'"

. pg_escape_string ( unescape ( $_POST [ 'sqlParam10' ] ) )

. "','" . pg_escape_string ( unescape ( $_POST [ 'sqlParam11' ] ) ) . "', $sqlParam12 )" ;

break ;

case 9 : // comm_comms.js newCommNext()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

if ( $sqlParam4 != 'true' && $sqlParam4 != 'false' )

exit ;

if ( $sqlParam5 != 'true' && $sqlParam5 != 'false' )

exit ;

$query = "INSERT INTO tbl_comm_notes (comm_id, note_by, note_detail, visible_to_client, visible_to_contractor, note_type) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",'" . pg_escape_string ( unescape ( $_POST [ 'sqlParam3' ] ) ) . "'," . ReturnforQuery ( $sqlParam4 ) . "," . ReturnforQuery ( $sqlParam5 ) . ",1)" ;

break ;

case 10 : // comm_comms.js newCommNext()

$query = "UPDATE tbl_contract SET comm_number_last = " . ReturnforQuery ( $sqlParam1 ) . " WHERE contract_id = " . ReturnforQuery ( $sqlParam2 ) ;

break ;

case 11 : // comm_comms.js commFirstNameSelection()

$query = "SELECT address_id, phone_1, email_address FROM tbl_contact con WHERE contact_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 12 : // comm_comms.js commLastNameSelection()

$query = "SELECT con.phone_1, con.email_address, add.* FROM tbl_contact con, tbl_contact_address add WHERE con.address_id = add.address_id AND con.contact_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 13 : // comm_comms.js selectContactDblClicked()

$query = "SELECT first_name, last_name, phone_1, email_address, organisation_id, position_held, addy.* FROM tbl_contact con left join tbl_contact_address addy on addy.address_id = con.address_id WHERE contact_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 14 : // comm_filters.js updateFilter()

$query = "DELETE FROM lnk_task_filter WHERE _user_id = " . ReturnforQuery ( $sqlParam1 ) . " AND contract_id = " . ReturnforQuery ( $sqlParam2 ) ;

break ;

case 15 : // comm_filters.js applyFilterChanges()

$query = "UPDATE tbl__user SET current_contract = " . ReturnforQuery ( $sqlParam1 ) . " WHERE _user_id = " . ReturnforQuery ( $sqlParam2 ) ;

//$_SESSION['current_contract'] = (int)$sqlParam1;

break ;

case 16 : // comm_contracts.js addressSelection()

$query = "SELECT * FROM tbl_contact_address WHERE address_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 17 : // comm_contract_admin.js contractAdminLoadData()

$query = "SELECT * FROM tbl_contract WHERE contract_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 18 : // comm_contract_admin.js saveContractChanges()

$query = "UPDATE tbl_contract SET contract_name = '" . ReturnforQuery ( $sqlParam1 ) . "', client_organisation_id = " . ReturnforQuery ( $sqlParam2 ) . ", client_organisation_id_2 = " . ReturnforQuery ( $sqlParam3 ) . ", work_coordinator = " . ReturnforQuery ( $sqlParam4 ) . ", deputy_work_coordinator = " . ReturnforQuery ( $sqlParam5 ) . " WHERE contract_id = " . ReturnforQuery ( $sqlParam6 ) ;

break ;

case 19 : // comm_contract_admin.js add_contract_confirm_control()

$query = "SELECT nextval('tbl_contract_contract_id_seq') AS nextValue " ;

break ;

case 20 : // comm_contract_admin.js add_contract_confirm_control()

$query = "INSERT INTO tbl_contract (contract_id, contract_name, contract_short_name, client_organisation_id, client_organisation_id_2, work_coordinator, deputy_work_coordinator, active, added_by) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 , true ) . "," . ReturnforQuery ( $sqlParam3 , true ) . "," . ReturnforQuery ( $sqlParam4 ) . "," . ReturnforQuery ( $sqlParam5 ) . "," . ReturnforQuery ( $sqlParam6 ) . "," . ReturnforQuery ( $sqlParam7 ) . ",true," . ReturnforQuery ( $sqlParam8 ) . ")" ;

break ;

case 21 : // comm_contract_admin.js add_contract_confirm_control()

$query = "INSERT INTO lnk__users_contracts (_user_id, contract_id) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ")" ;

break ;

case 22 : // comm_contract_admin.js refreshActivityGrid()

$query = "SELECT tsk.task_number, task_name, (SELECT project_number FROM tbl_project WHERE project_id = tsk.project_id) AS project_number, (SELECT project_name_short FROM tbl_project WHERE project_id = tsk.project_id) AS project_name FROM tbl_task AS tsk WHERE tsk.task_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 23 : // comm_contract_admin.js refreshActivityGrid()

$query = "SELECT project_number, project_name_short FROM tbl_project WHERE project_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 24 : // comm_contract_admin.js updateDBActivityChange()

$query = "UPDATE lnk_task_activity SET enforced = " . ReturnforQuery ( $sqlParam1 ) . " WHERE task_activity_id = " . ReturnforQuery ( $sqlParam2 ) ;

break ;

case 25 : // comm_contract_admin.js updateDBActivityChange()

$query = "UPDATE lnk_task_activity SET response_time = " . ReturnforQuery ( $sqlParam1 ) . " WHERE task_activity_id = " . ReturnforQuery ( $sqlParam2 ) ;

break ;

case 26 : // comm_contract_admin.js add_activity_dbl_click()

// if ($id = lnk_task_activity_should_update($sqlParam1,"",$sqlParam2))

// {

// $query = "UPDATE lnk_task_activity SET active= true WHERE task_activity_id = $id";

// }

// else

// {

//$query = "INSERT INTO lnk_task_activity (project_id, activity_id, active, added_by) VALUES (" . ReturnforQuery($sqlParam1) . "," . ReturnforQuery($sqlParam2) . ",true," . ReturnforQuery($sqlParam3) . ")";

$query = "INSERT INTO lnk_task_activity (task_id, project_id, activity_id, active, added_by) SELECT task_id," . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",true," . ReturnforQuery ( $sqlParam3 ) . " FROM tbl_task WHERE project_id = " . ReturnforQuery ( $sqlParam1 ) . " AND task_id NOT IN (SELECT task_id FROM lnk_task_activity WHERE active = true AND activity_id = " . ReturnforQuery ( $sqlParam2 ) . " AND project_id = " . ReturnforQuery ( $sqlParam1 ) . ")" ;

// }

break ;

case 27 : // comm_contract_admin.js add_activity_dbl_click()

if ( $id = lnk_task_activity_should_update ( "" , $sqlParam1 , $sqlParam2 ) )

$query = "update lnk_task_activity set active = true where task_activity_id = $id " ;

else

$query = "INSERT INTO lnk_task_activity (task_id, activity_id, active, added_by) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",true," . ReturnforQuery ( $sqlParam3 ) . ")" ;

break ;

case 28 : // comm_contract_admin.js contract_admin_remove_activity()

$query = "update lnk_task_activity set active = false where task_activity_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 29 : // comm_contract_admin.js contract_admin_remove_project()

$query = "UPDATE tbl_project SET removed = TRUE, removed_by = " . ReturnforQuery ( $sqlParam1 ) . ", removed_date = now() WHERE project_id = " . ReturnforQuery ( $sqlParam2 ) ;

break ;

case 30 : // comm_comms.js commCheckFormValues()

$query = "SELECT * FROM tbl_communication WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 31 : // comm_actions.js new_action_dueDateRefresh()

$query = "SELECT enforced, response_time, default_budget FROM lnk_task_activity WHERE task_activity_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 32 : // comm_actions.js new_action_save()

$sqlParam12 = is_numeric ( $sqlParam12 ) ? "' $sqlParam12 '" : "null" ; //actual hrs

if ( $sqlParam13 != "" ) {

$sqlParam13 = "' $sqlParam13 '" ;

$status = '4' ;

$closed = "now()" ;

} else {

$status = '1' ;

$sqlParam13 = "null" ;

$closed = "null" ;

}

$query = "INSERT INTO tbl_action (action_id, action_number, comm_id, start_date, due_date, status, priority, activity_id, budget_hours, _owner, created_by, actual_hours, finish_date, closed_date,title) VALUES (" . ReturnforQuery ( $sqlParam11 ) . "," . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",'" . ReturnforQuery ( $sqlParam3 ) . "','" . ReturnforQuery ( $sqlParam4 ) . "'," . $status . "," . ReturnforQuery ( $sqlParam6 ) . "," . ReturnforQuery ( $sqlParam7 ) . "," . ReturnforQuery ( $sqlParam8 ) . "," . ReturnforQuery ( $sqlParam9 ) . "," . ReturnforQuery ( $sqlParam10 ) . ", $sqlParam12 , $sqlParam13 , $closed ,'" . pg_escape_string ( unescape ( $_POST [ "sqlParam14" ] ) ) . "')" ;

break ;

case 33 : // comm_actions.js new_action_save()

$query = "SELECT action_number_ref FROM tbl_communication WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 34 : // comm_actions.js new_action_save()

$query = "UPDATE tbl_communication SET action_number_ref = action_number_ref + 1 WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 35 : // comm_actions.js new_action_save()

$query = "SELECT first_name, last_name FROM tbl__user WHERE _user_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 36 : // comm_actions.js new_action_save()

$query = "insert INTO lnk_action_assignment (action_id, _user_id, created_by) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . "," . ReturnforQuery ( $sqlParam3 ) . ")" ;

break ;

case 37 : // comm_actions.js new_action_save()

$query = "SELECT nextval('tbl_action_action_id_seq') AS nextValue " ;

break ;

case 38 : // comm_actions.js new_action_save()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

if ( $sqlParam5 != 'true' && $sqlParam5 != 'false' )

exit ;

if ( $sqlParam6 != 'true' && $sqlParam6 != 'false' )

exit ;

$query = "INSERT INTO tbl_action_notes (action_id, note_by, note_detail, visible_to_client, visible_to_contractor, note_type) VALUES ('" . ReturnforQuery ( $sqlParam1 ) . "'," . ReturnforQuery ( $sqlParam2 ) . ",'" . pg_escape_string ( unescape ( $_POST [ 'sqlParam3' ] ) ) . "'," . ReturnforQuery ( $sqlParam5 ) . "," . ReturnforQuery ( $sqlParam6 ) . "," . ReturnforQuery ( $sqlParam4 ) . ")" ;

break ;

case 39 : // comm_comms.js open_comm_window()

$query = "SELECT act.action_id, act.action_number, stat.action_status_name, act.due_date FROM tbl_action act, ref_action_status stat WHERE act.status = stat.action_status_id AND act.comm_id = " . ReturnforQuery ( $sqlParam1 ) . " ORDER BY action_number" ;

break ;

case 40 : // comm_comms.js buildActionTabContent()

$query = "SELECT * FROM vw_action WHERE action_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 41 : // comm_actions.js new_action_note_save()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

$query = "INSERT INTO tbl_action_notes (action_id, note_by, note_detail, visible_to_client, visible_to_contractor, note_type) VALUES ('" . ReturnforQuery ( $sqlParam1 ) . "'," . ReturnforQuery ( $sqlParam2 ) . ",'" . pg_escape_string ( unescape ( $_POST [ 'sqlParam3' ] ) ) . "'," . ReturnforQuery ( $sqlParam4 ) . "," . ReturnforQuery ( $sqlParam5 ) . "," . ReturnforQuery ( $sqlParam6 ) . ")" ;

break ;

case 42 : // comm.js choose_user_confirm_control()

$query = "DELETE FROM lnk_action_assignment WHERE action_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 43 : // comm.js add_reminder()

$query = "insert into tbl_reminder(comm_id, user_id, reminder_date) values(" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",current_date+interval'" . $sqlParam3 . " " . $sqlParam4 . "')" ;

break ;

case 44 : // comm.js add_reminder()

$query = "insert into tbl_reminder(comm_id, user_id, reminder_date) values(" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",'" . $sqlParam3 . "')" ;

break ;

case 45 :

$query = "delete from tbl_reminder where reminder_id = " . $sqlParam1 ;

break ;

case 46 : // comms.js choose_user_confirm_control()

$query = "SELECT r.first_name, r.last_name FROM lnk_action_assignment lnk, tbl__user r WHERE lnk._user_id = r._user_id AND action_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 48 : // comm.js add_reminder()

$query = "insert into tbl_reminder(action_id, user_id, reminder_date) values(" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",'" . $sqlParam3 . "')" ;

break ;

case 50 : // comm.js add_reminder()

$query = "insert into tbl_reminder(action_id, user_id, reminder_date) values(" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",current_date+interval'" . $sqlParam3 . " " . $sqlParam4 . "')" ;

break ;

case 51 : //comms_contacts.js save_contact();

$query = "INSERT INTO tbl_contact(

title

,first_name

,last_name

,contact_type_id

,position_held

,phone_1

,phone_2

,mobile

,fax

,email_address

,contact_method_id

,_contact

,organisation_id

,address_id

,postal_address_id

) VALUES (' $sqlParam1 '

,'" . trim ( $sqlParam2 ) . "'

,'" . trim ( $sqlParam3 ) . "'

,' $sqlParam4 '

,' $sqlParam5 '

,' $sqlParam6 '

,' $sqlParam7 '

,' $sqlParam8 '

,'" . trim ( $sqlParam9 ) . "'

,' $sqlParam10 '

,' $sqlParam11 '

, $sqlParam12

,' $sqlParam13 '

, $sqlParam14

, $sqlParam15

)" ;

break ;

case 52 : //comms_contacts.js save_user(); // removed, check saveExternalUser.php

$query = "SELECT (nextval(' $sqlParam1 ') - 1) AS id" ; //should be currval?

break ;

case 53 : //comms_contacts.js getBusinessId()

$query = "select organisation_id from tbl_organisation where organisation_name like ' $sqlParam1 ' limit 1" ;

break ;

case 54 : //comms_contacts.js getBusinessId()

$query = "insert into tbl_organisation (organisation_name) values (' $sqlParam1 ')" ;

break ;

case 55 : //comms_contacts.js save_contact();

if ( empty ( $sqlParam6 ) || ! is_numeric ( $sqlParam6 ) )

$sqlParam6 = "null" ;

$query = "insert into tbl_contact_address (address_1,address_2,address_3,city,postcode,sad_id) values (

' $sqlParam1 '

,' $sqlParam2 '

,' $sqlParam3 '

,' $sqlParam4 '

,' $sqlParam5 '

, $sqlParam6

)" ;

break ;

case 56 : //comms_filters.js update filter

$query = "INSERT INTO lnk_task_filter (_user_id,contract_id,task_id,project_id) VALUES ( $sqlParam1 , $sqlParam2 , $sqlParam3 , (SELECT project_id FROM tbl_task WHERE task_id = $sqlParam3 ))" ;

$i = 4 ;

do {

$p = "sqlParam $i " ;

$p = $$p ;

$i ++;

if ( empty ( $p ) || ! is_numeric ( $p ) )

break ;

$query .= " ,( $sqlParam1 , $sqlParam2 , $p ,(SELECT project_id FROM tbl_task WHERE task_id = $p ))" ;

} while ( true ) ;

break ;

case 57 : //comms_comms.js newCommNext

$query = "update tbl_comm_attachment set comm_id = $sqlParam2 where comm_attachment_id = $sqlParam1 " ;

break ;

case 58 : //comms_comms.js newCommNext

$query = "insert into lnk_communication_sent_by(contact_id,comm_id) values ( $sqlParam1 , $sqlParam2 )" ;

break ;

case 59 : //comms_contacts.js save_contact

$query = "insert into lnk_contact_contracts (contact_id,contract_id) values " ;

$i = 1 ;

$j = 2 ;

do {

$p1 = "sqlParam $i " ;

$p2 = "sqlParam $j " ;

$p1 = $$p1 ;

$p2 = $$p2 ;

$i += 2 ;

$j += 2 ;

if ( empty ( $p1 ) || empty ( $p2 ) || ! is_numeric ( $p1 ) || ! is_numeric ( $p2 ) )

break ;

$query .= "( $p1 , $p2 )," ;

} while ( true ) ;

$query = rtrim ( $query , "," ) ;

break ;

case 60 :

$query = "select * from lnk_action_assignment where action_id = $sqlParam1 " ;

break ;

case 61 : // comm_actions.js change_status_save()

$query = "UPDATE tbl_action SET status = $sqlParam1 WHERE action_id = $sqlParam2 " ;

break ;

case 62 : // comm_actions.js change_status_save()

$query = "SELECT ref.action_status_name AS oldstatname, (SELECT action_status_name FROM ref_action_status WHERE action_status_id = $sqlParam1 ) AS newstatname FROM ref_action_status ref, tbl_action act WHERE act.action_id = $sqlParam2 AND act.status = ref.action_status_id" ;

break ;

case 63 : // comm_comm.js new_comm_reminder_load()

$query = "SELECT * FROM tbl_reminder WHERE comm_id = $sqlParam1 AND user_id = $sqlParam2 " ;

break ;

case 64 : // comm_comm.js new_comm_reminder_load()

$query = "SELECT * FROM tbl_reminder WHERE action_id = $sqlParam1 AND user_id = $sqlParam2 " ;

break ;

case 65 :

$query = "select com.comm_id, com.comm_number from tbl_communication com,tbl_action where com.comm_id = tbl_action.comm_id and tbl_action.action_id = $sqlParam1 " ;

break ;

case 66 : //comms_contacts.js getBusinessId()

$query = "select organisation_name from tbl_organisation where organisation_id = ' $sqlParam1 ' limit 1" ;

break ;

case 67 : //comms_contacts.js save_contact();

$query = "update tbl_contact_address set address_1 = ' $sqlParam1 ',address_2 = ' $sqlParam2 ',address_3=' $sqlParam3 ',city=' $sqlParam4 ',postcode=' $sqlParam5 ' where address_id = $sqlParam6 " ;

break ;

case 68 :

$query = "update tbl_contact set

first_name = '" . trim ( $sqlParam1 ) . "'

,last_name = '" . trim ( $sqlParam2 ) . "'

,phone_1 = ' $sqlParam3 '

,email_address= '" . trim ( $sqlParam4 ) . "'

,organisation_id = ' $sqlParam5 '

where contact_id = ' $sqlParam6 '" ;

break ;

case 69 : // comm_comms.js buildActionTabContent()

$query = "SELECT * FROM vw_action WHERE action_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 70 :

$sqlParam6 = jsempty ( $sqlParam6 ) ? 'null' : $sqlParam6 ; //can be empty

$sqlParam7 = jsempty ( $sqlParam7 ) ? 'null' : $sqlParam7 ;

$sqlParam6 = is_numeric ( $sqlParam6 ) ? $sqlParam6 : 'null' ;

$sqlParam7 = is_numeric ( $sqlParam7 ) ? $sqlParam7 : 'null' ;

$query = "

update tbl_action set start_date = ' $sqlParam2 '

, due_date = ' $sqlParam3 '

, priority = $sqlParam4

, activity_id = $sqlParam5

, budget_hours = $sqlParam6

, actual_hours = $sqlParam7

, title = ' $sqlParam8 '

where action_id = $sqlParam1

" ;

break ;

case 71 :

$sqlParam1 = jsempty ( $sqlParam1 ) ? 0 : $sqlParam1 ;

$sqlParam2 = jsempty ( $sqlParam2 ) ? 0 : $sqlParam2 ;

$sqlParam3 = jsempty ( $sqlParam3 ) ? 0 : $sqlParam3 ;

$query = "select task_activity_id

from lnk_task_activity

where (task_id = ' $sqlParam2 '

or project_id = ' $sqlParam1 ')

and activity_id = ' $sqlParam3 '" ;

break ;

case 72 : // comm_actions.js change_status_save()

if ( $sqlParam1 == 4 ) { //closed

$now = "now()" ;

$finished = "' $sqlParam3 '" ;

} else {

$now = "null" ;

$finished = "null" ;

}

//bug caused by new process this will clean up the null value sometimes sent

if ( $sqlParam3 == "null" && $sqlParam1 == 4 ) {

$finished = "now()" ;

}

$query = "update tbl_action set finish_date = $finished , closed_date = $now where action_id = $sqlParam2 " ;

break ;

case 73 : //comm_contract_admin new_activity_save()

$sqlParam2 = $sqlParam2 ;

$query = "INSERT INTO tbl_activity (activity_code, activity_name, contract_id) VALUES (' $sqlParam1 ',' $sqlParam2 ', $sqlParam3 )" ;

break ;

case 74 :

if ( ! is_numeric ( $sqlParam1 ) )

exit ;

$closedDate = "" ;

if ( $sqlParam2 == "Closed" )

$closedDate = ", date_closed = now()" ;

$query = "update tbl_communication set status = (select comm_status_id from ref_comm_status where comm_status_name = ' $sqlParam2 ') $closedDate where comm_id = $sqlParam1 " ;

break ;

case 75 :

if ( ! is_numeric ( $sqlParam1 ) )

exit ;

$query = "select action_status_name from tbl_action,ref_action_status where tbl_action.status = ref_action_status.action_status_id and tbl_action.action_id = $sqlParam1 " ;

break ;

case 76 :

$query = "SELECT comm_status_name,date_closed,type FROM tbl_communication,ref_comm_status WHERE comm_id = $sqlParam1 AND ref_comm_status.comm_status_id = tbl_communication.status" ;

break ;

case 77 :

$sqlParam2 = $sqlParam2 ;

$query = "INSERT INTO tbl_user_audit (_user_id, description, res_height, res_width, contract_id) VALUES ( $sqlParam1 ,' $sqlParam2 ',' $sqlParam3 ',' $sqlParam4 ', $sqlParam5 )" ;

break ;

case 78 : // comm_contract_admin.js add_activity_dbl_click()

$query = "INSERT INTO lnk_task_activity (project_id, activity_id, active, added_by) VALUES (" . ReturnforQuery ( $sqlParam1 ) . "," . ReturnforQuery ( $sqlParam2 ) . ",true," . ReturnforQuery ( $sqlParam3 ) . ")" ;

break ;

case 79 : // comm_contract_admin.js contract_admin_remove_activity()

$query = "UPDATE lnk_task_activity SET active = false WHERE project_id IN (SELECT lnk.project_id FROM lnk_task_activity lnk WHERE lnk.task_activity_id = " . ReturnforQuery ( $sqlParam1 ) . ") AND activity_id = (SELECT lnk1.activity_id FROM lnk_task_activity lnk1 WHERE lnk1.task_activity_id = " . ReturnforQuery ( $sqlParam1 ) . ")" ;

break ;

case 80 : // comm_contract_admin.js contract_admin_remove_activity()

$query = "DELETE FROM tbl_comm_attachment WHERE comm_id = 1 AND attached_by = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 81 : // comm_contract_admin.js contract_admin_remove_activity()

$query = "SELECT att.*, .first_name, .last_name FROM tbl_comm_attachment att, tbl__user WHERE att.comm_attachment_id = " . ReturnforQuery ( $sqlParam1 ) . " AND att.attached_by = ._user_id ORDER BY date_attached" ;

break ;

case 82 : //comms_comms.js newCommNext

$query = "UPDATE tbl_comm_attachment SET comm_id = $sqlParam2 WHERE comm_id = 1 AND attached_by = $sqlParam1 " ;

break ;

case 83 : //comms_comms.js newCommNext

$query = "SELECT com.title, com.comm_number, con.first_name, con.last_name, email_address FROM tbl_communication com, tbl_contact con, lnk_communication_sent_by snt WHERE com.comm_id = " . ReturnforQuery ( $sqlParam1 ) . " AND com.comm_id = snt.comm_id AND snt.contact_id = con.contact_id" ;

break ;

case 84 : //comms_comms.js newCommNext

$query = "SELECT COUNT(*) AS remindercount FROM tbl_reminder WHERE comm_id = $sqlParam1 AND user_id = $sqlParam2 " ;

break ;

case 85 :

$query = "UPDATE tbl_contract SET imap_server = ' $sqlParam1 ', imap_directory = ' $sqlParam2 ', g_drive_location = ' $sqlParam3 ', ramm_filter = ' $sqlParam4 ',allow_external_mail = $sqlParam6 WHERE contract_id = $sqlParam5 " ;

break ;

case 86 :

if ( empty ( $sqlParam1 ) || ! is_numeric ( $sqlParam1 ) )

$sqlParam1 = "null" ;

if ( empty ( $sqlParam2 ) || ! is_numeric ( $sqlParam2 ) )

$sqlParam2 = "null" ;

$query = "UPDATE tbl_contract SET default_response_time = $sqlParam1 , default_budget_hours = $sqlParam2 , weekly_report = $sqlParam3 , weekly_report_summary = $sqlParam4 ,visible_to_client = $sqlParam6 , visible_to_contractor = $sqlParam7 WHERE contract_id = $sqlParam5 " ;

break ;

case 87 :

$query = "UPDATE tbl__user SET home_grid_setting = ' $sqlParam1 ' WHERE _user_id = $sqlParam2 " ;

break ;

case 88 :

$query = "SELECT home_grid_setting FROM tbl__user WHERE _user_id = $sqlParam1 " ;

break ;

case 89 :

$query = "select c.*

, ca.*

, o.*

, pa.address_id as address_id_pa

, pa.address_1 as address_1_pa

, pa.address_2 as address_2_pa

, pa.address_3 as address_3_pa

, pa.city as city_pa

, pa.postcode as postcode_pa

, pa.sad_id as sad_id_pa

, pa.box as box_pa

, pa.extra as extra_pa

, rct.contact_type_name

from tbl_contact c

left join tbl_contact_address ca on c.address_id = ca.address_id

left join tbl_contact_address pa on c.postal_address_id = pa.address_id

left join tbl_organisation o on c.organisation_id = o.organisation_id

, ref_contact_type rct

where

c.contact_id = $sqlParam1

and rct.contact_type_id = c.contact_type_id" ;

break ;

case 90 :

$query = "select * from tbl_contact c,tbl__user u, ref_contact_type rct where u._user_id = c._user_id and c.contact_id = $sqlParam1 and rct.contact_type_id = c.contact_type_id" ;

break ;

case 91 :

$query = "update tbl__user set banned = true, ban_reason='Your user account has been deactivated by internally. If you believe this was in error, please contact your administrator.' where _user_id = (select _user_id from tbl_contact where contact_id = $sqlParam1 )" ;

break ;

case 92 :

$query = "update tbl__user set banned = false, ban_reason='' where _user_id = (select _user_id from tbl_contact where contact_id = $sqlParam1 )" ;

break ;

case 93 :

$sqlParam1 = empty ( $sqlParam1 ) ? "null" : $sqlParam1 ;

$sqlParam7 = empty ( $sqlParam7 ) ||! is_numeric ( $sqlParam7 ) ? "null" : $sqlParam7 ;

$query = "update tbl_contact set

organisation_id = $sqlParam1

,position_held = ' $sqlParam2 '

,title = ' $sqlParam3 '

,first_name = '" . trim ( $sqlParam4 ) . "'

,last_name = '" . trim ( $sqlParam5 ) . "'

,contact_type_id = $sqlParam6

,contact_method_id = $sqlParam7

,phone_1 = ' $sqlParam8 '

,phone_2 = ' $sqlParam9 '

,mobile = ' $sqlParam10 '

,fax = ' $sqlParam11 '

,email_address = '" . trim ( $sqlParam12 ) . "'

where contact_id = $sqlParam13

" ;

break ;

case 94 :

$query = "update tbl_contact set address_id = $sqlParam2 where contact_id = $sqlParam1 " ;

break ;

case 95 :

$query = "SELECT * FROM vw_action WHERE comm_id = $sqlParam1 AND status <> 4 ORDER BY action_number" ;

break ;

case 96 :

$query = "SELECT COUNT(action_id) AS openactcount FROM tbl_action WHERE comm_id = $sqlParam1 AND status <> 4" ;

break ;

case 97 :

$query = "INSERT INTO lnk_attendees (comm_id,contact_id) VALUES ( $sqlParam2 , $sqlParam1 )" ;

break ;

case 98 :

$query = "DELETE FROM lnk_attendees WHERE comm_id = $sqlParam2 AND contact_id = $sqlParam1 " ;

break ;

case 99 :

$query = "SELECT COUNT(contact_id) AS attcount FROM lnk_attendees WHERE comm_id = $sqlParam1 " ;

break ;

case 100 :

if ( ! is_numeric ( $sqlParam1 ) || ! is_numeric ( $sqlParam2 ) )

{

$sqlParam1 = 0 ;

$sqlParam2 = 0 ;

}

$query = "select min(comm_number) from tbl_communication where comm_id > $sqlParam1 and contract_id = $sqlParam2 " ;

break ;

case 101 :

if ( ! is_numeric ( $sqlParam1 ) || ! is_numeric ( $sqlParam2 ) )

{

$sqlParam1 = 0 ;

$sqlParam2 = 0 ;

}

$query = "select comm_id from tbl_communication where comm_number = $sqlParam1 and contract_id = $sqlParam2 " ;

break ;

case 102 :

$query = "SELECT visible_to_client, visible_to_contractor from tbl_contract WHERE contract_id = $sqlParam1 " ;

break ;

case 103 : // comm_comms.js new_comm_note_save()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

$query = "update tbl_comm_notes set

visible_to_client = $sqlParam2 ,

visible_to_contractor = $sqlParam3 ,

note_detail = '" . pg_escape_string ( unescape ( $_POST [ 'sqlParam4' ] ) ) . "'

where comm_note_id = " . $sqlParam1 ;

//store the old one in the audit table tbl_old_notes

executeQuery ( "insert into tbl_old_notes (comm_note_id, note_detail, note_date, updated_by)

select comm_note_id, note_detail, note_date, $sqlParam5 from tbl_comm_notes where comm_note_id = $sqlParam1

" ) ;

break ;

case 104 :

$query = "update tbl_comm_notes set

visible_to_client = $sqlParam2

where comm_note_id = " . $sqlParam1 ;

break ;

case 105 :

$query = "update tbl_comm_notes set

visible_to_contractor = $sqlParam2

where comm_note_id = " . $sqlParam1 ;

break ;

case 106 : // comm_comms.js new_comm_note_save()

// Note: unescape needed to be called before the string was passed through pg escape. This is also the case for cases 2,9,38, and 41 (all notes saving related)

$query = "update tbl_action_notes set

visible_to_client = $sqlParam2 ,

visible_to_contractor = $sqlParam3 ,

note_detail = '" . pg_escape_string ( unescape ( $_POST [ 'sqlParam4' ] ) ) . "'

where action_note_id = " . $sqlParam1 ;

//store the old one in the audit table tbl_old_notes

executeQuery ( "insert into tbl_old_notes (action_note_id, note_detail, note_date, updated_by)

select action_note_id, note_detail, note_date, $sqlParam5 from tbl_action_notes where action_note_id = $sqlParam1

" ) ;

break ;

case 107 :

$query = "update tbl_action_notes set

visible_to_client = $sqlParam2

where action_note_id = " . $sqlParam1 ;

break ;

case 108 :

$query = "update tbl_action_notes set

visible_to_contractor = $sqlParam2

where action_note_id = " . $sqlParam1 ;

break ;

case 109 :

$query = "select * from tbl_contact c, lnk_contact_contracts l

where c.contact_id = l.contact_id and l.contract_id = " . $_SESSION [ 'current_contract' ] . "

and c.first_name ilike ' $sqlParam1 ' and c.last_name ilike ' $sqlParam2 ' and c.phone_1 ilike ' $sqlParam3 '

" ;

break ;

case 110 :

$query = "update tbl_contact set postal_address_id = null where contact_id = " . $sqlParam1 ;

break ;

case 111 :

$query = "update tbl_contact set postal_address_id = $sqlParam2 where contact_id = $sqlParam1 " ;

break ;

case 112 :

$query = "delete from lnk_contact_contracts where contract_id = $sqlParam1 and contact_id = $sqlParam2 " ;

break ;

case 113 : // comm_comms.js commCheckFormValues()

$query = "SELECT * FROM vw_linear_ref WHERE comm_id = " . ReturnforQuery ( $sqlParam1 ) ;

break ;

case 114 : // comm_comms.js comm_save()

$query = "delete from lnk_comm_linear_ref where comm_id = " . ReturnforQuery ( $sqlParam4 ) . ";" ;

break ;

case 115 : // comm_comms.js comm_save()

$query = "insert into lnk_comm_linear_ref(comm_id, ramm_id, start_m, end_m)

values( $sqlParam1 , $sqlParam2 , $sqlParam3 , $sqlParam4 );" ;

break ;

case 116 :

$query = "insert into ref_action_priority(action_priority_name, is_default)

values('" . pg_escape_string ( unescape ( $sqlParam1 ) ) . "', false)" ;

break ;

case 117 :

$query = "insert into ref_comm_type(comm_type_name, active)

values('" . pg_escape_string ( unescape ( $sqlParam1 ) ) . "', false)" ;

break ;

case 118 :

$query = "insert into ref_comm_medium(comm_medium_name, active)

values('" . pg_escape_string ( unescape ( $sqlParam1 ) ) . "', false)" ;

break ;

case 119 : // comm_comms.js

$contract_id = $_REQUEST [ 'contract_id' ] ;

executeQuery ( "delete from lnk_custom_contract_options where contract_id = $contract_id " ) ;

foreach ( $_POST [ 'priority' ] as $key => $val ) {

executeQuery ( "insert into lnk_custom_contract_options (option_id, ref_table, contract_id) values ( $key , 'ref_action_priority', $contract_id )" ) ;

}

foreach ( $_POST [ 'comm_type' ] as $key => $val ) {

executeQuery ( "insert into lnk_custom_contract_options (option_id, ref_table, contract_id) values ( $key , 'ref_comm_type', $contract_id )" ) ;

}

foreach ( $_POST [ 'medium' ] as $key => $val ) {

executeQuery ( "insert into lnk_custom_contract_options (option_id, ref_table, contract_id) values ( $key , 'ref_comm_medium', $contract_id )" ) ;

}

$query = "select * from lnk_custom_contract_options where contract_id = $contract_id " ;

break ;

case 120 : // comms_contract_admin.js

$params = $_REQUEST ;

$contract_id = $params [ 'contract_id' ] ;

executeQuery ( "delete from lnk_contract_reports where contract_id = $contract_id " ) ;

foreach ( $params as $k => $v ) {

if ( substr ( $k , 0 , 2 ) == 'i_' or substr ( $k , 0 , 2 ) == 'e_' ) {

$key = explode ( '_' , $k ) ;

$sql = "insert into lnk_contract_reports(contract_id, report_id, type_code)

values(" . $contract_id . "," . $key [ 1 ] . ",'" . strtoupper ( $key [ 0 ] ) . "');" ;

executeQuery ( $sql ) ;

}

}

break ;

case 121 : // comms_reporting.js

$contract_id = $_REQUEST [ 'contract_id' ] ;

$report_instance_id = $_REQUEST [ 'report_instance' ] ;

$sql = "select * from vw_contract_reports

where contract_id = $contract_id

and report_id = (select report_id from tbl_report_instances where report_instance_id = $report_instance_id );" ;

$result = executeQuery ( $sql ) ;

echo count ( $result ) > 0 ? "true" : "false" ;

break ;

default :

$query = "You forgot a break statement after your query definition.. again!" ;

break ;

}

return $query ;

}

//Mails the admins if there is an error in the Query

function mailError ( $ResultError ) {

$hua = $_SERVER [ "HTTP_USER_AGENT" ] ;

$UserName = "" ;

$usercode = "" ;

$userEmail = "" ;

$userFirstName = "" ;

$userLastName = "" ;

$userGotDetails = false ;

$ldapView = "" ;

global $APP_NAME , $ERRORS_TO ;

if ( strpos ( $hua , "_" ) )

{

$offset = strpos ( $hua , "_" ) + 5 ; // 5 = numbe of characters in '_'

$hua = substr ( $hua , $offset ) ;

// Get everything in the string up until the first semicolon. That will get you the username in IE.

$UserName = substr ( $hua , 0 , strpos ( $hua , ';' ) ) ;

$LDAP_Array = GetUserDetailsFromAD ( $UserName ) ;

if ( $LDAP_Array != false ) { $userGotDetails = true ; }

$usercode = substr ( $LDAP_Array [ 'dn' ] , 4 , 6 ) ;

$userEmail = $LDAP_Array [ 'email' ] ;

$userFirstName = $LDAP_Array [ 'firstName' ] ;

$userLastName = $LDAP_Array [ 'lastName' ] ;

$userFullName = $LDAP_Array [ 'cn' ] ;

}

$mailString = "There has been a SQL error in the $APP_NAME .



" ;

if ( $userGotDetails )

{

$mailString .= "<b>The user is:</b> <a href=' $ldapView '> $userFullName </a> ( $usercode , <a href='mailto: $userEmail '> $userEmail </a> )



" ;

}

$mailString .= "<b>The page requested was:</b> " . $_SERVER [ 'REQUEST_URI' ] . "



" ;

$mailString .= "<b>The request method was :</b> " . $_SERVER [ 'REQUEST_METHOD' ] . "



" ;

$mailString .= "<b>The request variables were:</b> " ;

foreach ( $_REQUEST as $key => $value )

{

if ( $key == 'PHPSESSID' ) { continue ; }

$mailString .= "<br>[ $key ] => $value " ;

}

$mailString .= "<br /><br />" ;

$mailString .= "<b>DBMS/Debug Message:</b>



" . trim ( str_replace ( " \t " , "" , $ResultError ) ) . "

" ;

$mailString .= "<p>If you would like to edit who receives these emails, please change the \$ ERRORS_TO global</p>" ;

$to = $ERRORS_TO ;

# Redirect all emails in the dev environment:

global $REDIRECT_ALL_EMAIL_TO ;

if ( isset ( $REDIRECT_ALL_EMAIL_TO ) and $REDIRECT_ALL_EMAIL_TO ) {

# Prepend a note indicating the original recipient:

$mailString = "(Redirected for development testing. Was sent To: $to )

" . $mailString ;

$to = $REDIRECT_ALL_EMAIL_TO ;

}

// To send HTML mail, the Content-type header must be set

$headers = 'MIME-Version: 1.0' . " \r

" ;

$headers .= 'Content-type: text/html; charset=iso-8859-1' . " \r

" ;

// Additional headers

$headers .= 'From: ' . $_SERVER [ 'HTTP_HOST' ] . " $APP_NAME <dev@..co.nz> \r

" ;

$subjectLine = "SQL Error in $APP_NAME " ;

$mailString = "<html><body> $mailString </html></body>" ;

mail ( $to , $subjectLine , nl2br ( $mailString ) , $headers ) ;

}

# This function will always be loaded first from dbfunctions.php. Am leaving this here for a while in case there are any problems. -Terry 20th Feb 2014

if ( ! function_exists ( 'ReturnforQuery' ) ) {

function ReturnforQuery ( $value , $isString = false )

{ //formats the string nicely for us to use in a query.

if ( is_null ( $value ) ) {

if ( is_bool ( $value ) ) {

return "FALSE" ;

} else {

return "NULL" ;

}

} elseif ( $value == '' ) {

return "NULL" ;

} else {

if ( $isString === true ) {

$value = trim ( $value ) ;

if ( is_null ( $value ) ) {

return '' ; # note: this line is different to the version in dbfunctions.php

} elseif ( $value == '' or $value == 'NULL' ) {

return '' ; # note: this line is different to the version in dbfunctions.php

} else {

$value = addslashes ( trim ( $value ) ) ;

// Escape troublesome characters!

$value = str_replace ( ')' , '\)' , $value ) ;

$value = str_replace ( '(' , '\(' , $value ) ;

$value = str_replace ( '*' , '\*' , $value ) ;

return "' $value '" ;

}

} elseif ( is_bool ( $value ) ) {

if ( $value ) {

return "TRUE" ;

} else {

return "FALSE" ;

}

} else {

return $value ;

}

}

}