CodeSOD: External SQL

"Externalize your strings" is generally good advice. Maybe you pull them up into constants, maybe you move them into a resource file, but putting a barrier between your code and the strings you output makes everything more flexible.

But what about strings that aren't output? Things like, oh" database queries? We want to be cautious about embedding SQL directly into our application code, but our SQL code often is our business logic, so it makes sense to inline it. Most data access layers end up trying to abstract the details of SQL behind method calls, whether it's just a simple repository or an advanced ORM approach.

Sean found a" unique approach to resolving this tension in some Java code he inherited. He saw lots of references to keys in a hash-map, keys like user or pw or insert_account_table or select_all_transaction_table. But where did these keys get defined?

Like all good strings, they were externalized into a file called sql.txt. A simple regex-based parser loaded the data and created the dictionary. Now, any module which wanted to query the database had a map of any query they could possibly want to run. Just chuck 'em into a PreparedStatement object and you're ready to go.

Here, in its entirety, is the sql.txt file.

user = rootpw = passworddb_name = lrc_mydbcreate_account_table = create table if not exists account_table(username varchar(45) not null, password text not null, last_name text, first_name text, mid_name text, suffix_name text, primary key (username))create_course_table = create table if not exists course_table (course_abbr char(45) not null unique, course_name text, primary key(course_abbr))create_student_table = create table if not exists student_table (username varchar(45) not null, registration_date date, year_lvl char(45), photolink longblob, freetime time, course_abbr char(45) not null, status char(45) not null, balance double not null, foreign key fk_username(username) references account_table(username) on update cascade on delete cascade, foreign key fk_course_abbr(course_abbr) references course_table(course_abbr) on update cascade on delete cascade, primary key(username))create_admin_table = create table if not exists admin_table (username varchar(45) not null, delete_priv boolean, settle_priv boolean, db_access boolean, foreign key fk_username(username) references account_table(username) on update cascade on delete cascade, primary key(username))create_reservation_table = create table if not exists reservation_table (username varchar(45) not null, foreign key fk_username(username) references account_table(username) on update cascade on delete cascade, primary key(username))create_service_table = create table if not exists service_table (service_id int not null auto_increment, service_name text, amount double, page_requirement boolean, primary key (service_id))create_pc_table = create table if not exists pc_table (pc_id char(45) not null, ip_address varchar(45), primary key (pc_id))create_transaction_table = create table if not exists transaction_table (transaction_id int not null auto_increment, date_rendered date, amount_paid double unsigned not null,cost_payable double, username varchar(45) not null, service_id int not null, foreign key fk_username(username) references account_table(username) on update cascade on delete cascade, foreign key fk_service_id(service_id) references service_table(service_id) on update cascade on delete cascade, primary key (transaction_id))create_pc_usage_table = create table if not exists pc_usage_table (transaction_id int not null, pc_id char(45) not null, login_time time, logout_time time, foreign key fk_pc_id(pc_id) references pc_table(pc_id) on update cascade on delete cascade, foreign key fk_transaction_id(transaction_id) references transaction_table(transaction_id) on update cascade on delete cascade, primary key(transaction_id))create_pasa_hour_table = create table if not exists pasa_hour_table (transaction_id int not null auto_increment, date_rendered date, sender varchar(45) not null, amount_time time, current_free_sender time, deducted_free_sender time, receiver varchar(45) not null, current_free_receiver time, added_free_receiver time, primary key(transaction_id))create_receipt_table = create table if not exists receipt_table (dates date, receipt_id varchar(45) not null, transaction_id int not null, username varchar(45) not null, amount_paid double, amount_change double, foreign key fk_transaction_id(transaction_id) references transaction_table(transaction_id) on update cascade on delete cascade, foreign key fk_username(username) references account_table(username) on update cascade on delete cascade)create_cash_flow_table = create table if not exists cash_flow_table (dates date, cash_in double, cash_close double, cash_out double, primary key(dates))create_free_pc_usage_table = create table if not exists free_pc_usage_table (transaction_id int not null, foreign key fk_transaction_id(transaction_id) references transaction_table(transaction_id) on update cascade on delete cascade, primary key(transaction_id))create_diagnostic_table = create table if not exists diagnostic_table (sem_id int not null auto_increment , date_start date, date_end date, sem_num enum('first', 'second', 'mid year'), freetime time, time_penalty double, balance_penalty double, primary key(sem_id))create_pasa_balance_table = create table if not exists pasa_balance_table (transaction_id int not null auto_increment, date_rendered date, sender varchar(45) not null, amount double, current_balance_sender double, deducted_balance_sender double, receiver varchar(45) not null, current_balance_receiver double, added_balance_receiver double, primary key(transaction_id))insert_account_table = insert into account_table values (?, password(?), ?, ?, ?, ?)insert_course_table = insert into course_table values (?, ?)insert_student_table = insert into student_table values (?, now(), ?, ?, ?, ?, ?, ?)insert_admin_table = insert into admin_table values (?, ?, ?, ?)insert_reservation_table = insert into reservation_table values (?)insert_service_table = insert into service_table (service_name, amount, page_requirement) values (?, ?, ?)insert_pc_table = insert into pc_table values (?, ?)insert_transaction_table = insert into transaction_table (date_rendered, amount_paid, cost_payable, username, service_id) values (now(), ?, ?, ?, ?)insert_pc_usage_table = insert into pc_usage_table values (?, ?, ?, ?)insert_pasa_hour_table = insert into pasa_hour_table (date_rendered, sender, amount_time, current_free_sender, deducted_free_sender, receiver, current_free_receiver, added_free_receiver) values (curdate(), ?, ?, ?, ?, ?, ?, ?)insert_free_pc_usage_table = insert into free_pc_usage_table values (?)insert_cash_flow_table = insert into cash_flow_table values (curdate(), ?, ?, ?)insert_receipt_table = insert into receipt_table values (curdate(), ?, ?, ?, ?, ?)insert_diagnostic_table = insert into diagnostic_table (date_start, date_end, sem_num, freetime, time_penalty, balance_penalty) values (?, ?, ?, ?, ?, ?)insert_pasa_balance_table = insert into pasa_balance_table (date_rendered, sender, amount, current_balance_sender, deducted_balance_sender, receiver, current_balance_receiver, added_balance_receiver) values (curdate(), ?, ?, ?, ?, ?, ?, ?)delete_reservation_table = delete from reservation_table where username = ?delete_course_table = delete from course_table where course_abbr = ?delete_user_assoc_to_course = delete account_table, student_table from student_table inner join account_table on account_table.username = student_table.username where student_table.course_abbr = ?delete_service_table = delete from service_table where service_name = ?delete_user_student = delete account_table, student_table from student_table inner join account_table on account_table.username = student_table.username where student_table.username = ?delete_user_staff = delete account_table, admin_table from admin_table inner join account_table on account_table.username = admin_table.username where admin_table.username = ?select_total_cost = select sum(cost_payable - amount_paid) from transaction_table where username = ? and cost_payable > amount_paidselect_time_penalty = select time_penalty from diagnostic_table where sem_id = ?select_balance_penalty = select balance_penalty from diagnostic_table where sem_id = ?select_balance = select balance from student_table where username = ?select_accountabilities = select sum(cost_payable - amount_paid) from transaction_table where username = ? and cost_payable > amount_paidselect_count_service_table = select count(*) from service_tableselect_count_course_table = select count(*) from course_tableselect_course_count = select count(course_abbr) from student_table where course_abbr = ?select_course_abbr = select course_abbr from course_table where course_name = ?select_degree_name_abbr = select * from course_tableselect_service_name = select * from service_tableselect_service_name1 = select service_name from service_table where service_id = ?select_services_amount = select * from service_tableselect_username = select * from account_table where username = (?) and password = password(?)select_user = select * from account_table where username = (?)select_reserved_user = select * from reservation_table where username = (?)select_existing_course = select * from course_table where course_abbr = (?)select_existing_service = select * from service_table where service_name = (?)select_existing_transaction_id = select transaction_id from transaction_table where transaction_id = ?select_user_is_active = select status from student_table where username = ?select_page_requirement = select page_requirement from service_table where service_name = ?select_user_details = select account_table.username as 'Username', concat(account_table.last_name, ', ', account_table.first_name, ' ', account_table.suffix_name, ' ', account_table.mid_name) as 'Name', student_table.course_abbr as 'Degree Program', student_table.year_lvl as 'Year Level', student_table.freetime as 'Free Time' from account_table inner join student_table on account_table.username = student_table.username where student_table.username = ?select_amount_service = select amount from service_table where service_name = ?select_id_service = select * from service_table where service_name = ?select_freetime = select student_table.freetime from student_table inner join transaction_table on student_table.username = transaction_table.username where transaction_table.transaction_id = ?select_timediff = select timediff(time(?), timediff(time(logout_time), time(login_time))) as 'timedifference' from pc_usage_table where transaction_id = ?select_trans_user = select username from transaction_table where transaction_id = ?select_pc_id1 = select pc_id from pc_table where ip_address = ?select_timedifference = select timediff(time(?), timediff(curtime(), time(?))) as 'timedifference' from pc_usage_table where transaction_id = ?select_logout_time = select logout_time from pc_usage_table where transaction_id = ?select_login_time = select login_time from pc_usage_table where transaction_id = ?select_now = select curtime()select_time_consumed = select timediff(time(logout_time), time(login_time)) as 'timedifference' from pc_usage_table where time_to_sec(timediff(time(logout_time), time(login_time))) < time_to_sec(time(?)) and transaction_id = ?select_freetime_user = select freetime from student_table where username = ?select_cost_transaction = select cost_payable from transaction_table where transaction_id = ?select_amount_transaction = select amount_paid from transaction_table where transaction_id = ?select_pc_id_from_trans = select pc_id from pc_usage_table where transaction_id = ?select_pc_id2 = select pc_table.pc_id from pc_tableselect_transactions_with_accountabilities = select transaction_id from transaction_table where username = ? and amount_paid < cost_payableselect_picture = select photolink from student_table where username = ?select_diagnostic_table2 = select * from diagnostic_table where sem_id = ?select_diagnostic_table = select * from diagnostic_table order by diagnostic_table.date_end desc limit 1select_filtered_username = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table inner join student_table on account_table.username = student_table.username where account_table.username like (?) and student_table.username like (?) group by usernameselect_filtered_lastname = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where account_table.last_name like ? group by usernameselect_filtered_firstname = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where account_table.first_name like ? group by usernameselect_filtered_yearlvl = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where student_table.year_lvl like ? group by usernameselect_filtered_degprog = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where student_table.course_abbr like ? group by usernameselect_filtered_username2 = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where transaction_table.username like ? group by transaction_idselect_filtered_servicename = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where service_table.service_name like ? group by transaction_idselect_filtered_date = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where transaction_table.date_rendered like ? group by Transaction_idselect_all = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.freetime as 'Free Time', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.usernameselect_filtered_active = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.freetime as 'Free Time', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where student_table.status = 'active'select_filtered_inactive = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', student_table.year_lvl as 'Year Level', student_table.course_abbr as 'Degree Program', student_table.status as 'Status', student_table.freetime as 'Free Time', student_table.balance as 'Balance', ifnull((select sum(transaction_table.cost_payable - transaction_table.amount_paid) from transaction_table where transaction_table.cost_payable > transaction_table.amount_paid and transaction_table.username = account_table.username),0) as 'Accountabilities' from account_table join student_table on account_table.username = student_table.username where student_table.status = 'inactive'select_online_pc =select_reserved_pc = select reservation_table.username as 'Username' from reservation_tableselect_staff_table = select account_table.username as 'Username', account_table.last_name as 'Last Name', concat(account_table.first_name, ', ', account_table.suffix_name) as 'First Name', account_table.mid_name as 'Middle Name', admin_table.delete_priv as 'Delete Privilege', admin_table.settle_priv as 'Settle Privilege', admin_table.db_access as 'Database Access' from account_table inner join admin_table on account_table.username = admin_table.usernameselect_degree_table = select course_table.course_name as 'Degree Program', course_table.course_abbr as 'Abbreviation' from course_tableselect_service_table = select service_name as 'Service Name', amount as 'Amount' from service_tableselect_pasa_hour = select pasa_hour_table.date_rendered as 'Date', pasa_hour_table.amount_time as 'Amount Time', concat(pasa_hour_table.sender, ' ( ', pasa_hour_table.current_free_sender, ' - ', pasa_hour_table.deducted_free_sender, ' )') as 'Sender (Current - Deducted)', concat(pasa_hour_table.receiver, ' ( ', pasa_hour_table.current_free_receiver, ' - ', pasa_hour_table.added_free_receiver, ' )') as 'Receiver (Current - Added)' from pasa_hour_tableselect_pasa_bal = select date_rendered as 'Date', amount as 'Amount Time', concat(sender, ' ( ', current_balance_sender, ' - ', deducted_balance_sender, ' )') as 'Sender (Current - Deducted)', concat(receiver, ' ( ', current_balance_receiver, ' - ', added_balance_receiver, ' )') as 'Receiver (Current - Added)' from pasa_balance_tableselect_transaction_table = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where transaction_table.date_rendered = curdate()select_all_transaction_table = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_idselect_paid_transaction_table = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where transaction_table.cost_payable <= transaction_table.amount_paidselect_unpaid_transaction_table = select transaction_table.date_rendered as 'Date', transaction_table.transaction_id as 'Transaction ID', transaction_table.username 'Username', service_table.service_name 'Service Name', substring(transaction_table.cost_payable, 1, 5) as 'Cost', substring(transaction_table.amount_paid, 1, 5) as 'Amount Rendered' from transaction_table inner join service_table on transaction_table.service_id = service_table.service_id where transaction_table.cost_payable > transaction_table.amount_paidselect_usage_daily = select distinct a.pc_id as 'PC Number', (select count(b.pc_id) from pc_usage_table b where b.pc_id = a.pc_id && b.transaction_id in (select transaction_id from transaction_table where date_rendered = ?)) as 'Total # of Transactions', (select count(distinct c.username) from transaction_table c where c.transaction_id in (select d.transaction_id from pc_usage_table d where d.pc_id = a.pc_id) && c.transaction_id in (select transaction_id from transaction_table where date_rendered = ?)) as 'Total # of Users' from pc_usage_table a join transaction_table e on a.transaction_id = e.transaction_id where date_rendered = ?select_usage_monthly = select distinct a.pc_id as 'PC Number', (select count(b.pc_id) from pc_usage_table b where b.pc_id = a.pc_id && b.transaction_id in (select transaction_id from transaction_table where year(date_rendered) = ? and monthname(date_rendered) = ?)) as 'Total # of Transactions', (select count(distinct c.username) from transaction_table c where c.transaction_id in (select d.transaction_id from pc_usage_table d where d.pc_id = a.pc_id) && c.transaction_id in (select transaction_id from transaction_table where year(date_rendered) = ? and monthname(date_rendered) = ?)) as 'Total # of Users' from pc_usage_table a join transaction_table e on a.transaction_id = e.transaction_id where year(e.date_rendered) = ? and monthname(date_rendered) = ?select_usage_annual = select distinct a.pc_id as 'PC Number', (select count(b.pc_id) from pc_usage_table b where b.pc_id = a.pc_id && b.transaction_id in (select transaction_id from transaction_table where year(date_rendered) = ?)) as 'Total # of Transactions', (select count(distinct c.username) from transaction_table c where c.transaction_id in (select d.transaction_id from pc_usage_table d where d.pc_id = a.pc_id) && c.transaction_id in (select transaction_id from transaction_table where year(date_rendered) = ?)) as 'Total # of Users' from pc_usage_table a join transaction_table e on a.transaction_id = e.transaction_id where year(e.date_rendered) = ?select_usage_semestral = select distinct a.pc_id as 'PC Number', (select count(b.pc_id) from pc_usage_table b where b.pc_id = a.pc_id && b.transaction_id in (select transaction_id from transaction_table where date_rendered between (select date_start from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) and (select date_end from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)))) as 'Total # of Transactions', (select count(distinct c.username) from transaction_table c where c.transaction_id in (select d.transaction_id from pc_usage_table d where d.pc_id = a.pc_id) && c.transaction_id in (select transaction_id from transaction_table where transaction_table.date_rendered between (select date_start from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) and (select date_end from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)))) as 'Total # of Users' from pc_usage_table a join transaction_table e on a.transaction_id = e.transaction_id where e.date_rendered between (select date_start from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) and (select date_end from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?))select_student_daily = select account_table.username, concat(account_table.last_name, ', ', account_table.first_name, ', ', account_table.suffix_name, ', ', account_table.mid_name), student_table.course_abbr from account_table inner join student_table on student_table.username = account_table.username where account_table.username in (select transaction_table.username from transaction_table inner join pc_usage_table on transaction_table.transaction_id = pc_usage_table.transaction_id where year(transaction_table.date_rendered) = ? and transaction_table.date_rendered = ?)select_student_monthly = select account_table.username as 'Student Number', concat(account_table.last_name, ', ', account_table.first_name, ', ', account_table.suffix_name, ', ', account_table.mid_name) as 'Name', student_table.course_abbr as 'Degree Program' from account_table inner join student_table on student_table.username = account_table.username where account_table.username in (select transaction_table.username from transaction_table inner join pc_usage_table on transaction_table.transaction_id = pc_usage_table.transaction_id where year(transaction_table.date_rendered) = ? and monthname(transaction_table.date_rendered) = ?)select_student_annual = select account_table.username as 'Student Number', concat(account_table.last_name, ', ', account_table.first_name, ', ', account_table.suffix_name, ', ', account_table.mid_name) as 'Name', student_table.course_abbr as 'Degree Program' from account_table inner join student_table on student_table.username = account_table.username where account_table.username in (select transaction_table.username from transaction_table inner join pc_usage_table on transaction_table.transaction_id = pc_usage_table.transaction_id where year(transaction_table.date_rendered) = ?)select_student_semestral = select account_table.username as 'Student Number', concat(account_table.last_name, ', ', account_table.first_name, ', ', account_table.suffix_name, ', ', account_table.mid_name) as 'Name', student_table.course_abbr as 'Degree Program' from account_table inner join student_table on student_table.username = account_table.username where account_table.username in (select transaction_table.username from transaction_table inner join pc_usage_table on transaction_table.transaction_id = pc_usage_table.transaction_id where transaction_table.date_rendered between (select date_start from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) and (select date_end from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)))select_transaction_daily = select service_table.service_name as 'Service Name', sum(transaction_table.cost_payable) as 'Cost Payable' from service_table join transaction_table on service_table.service_id = transaction_table.service_id where transaction_table.date_rendered = ? group by transaction_table.service_idselect_transaction_monthly = select service_table.service_name as 'Service Name', sum(transaction_table.cost_payable) as 'Cost Payable' from service_table join transaction_table on service_table.service_id = transaction_table.service_id where year(transaction_table.date_rendered) = ? and monthname(transaction_table.date_rendered) = ? group by transaction_table.service_idselect_transaction_annual = select service_table.service_name as 'Service Name', sum(transaction_table.cost_payable) as 'Cost Payable' from service_table join transaction_table on service_table.service_id = transaction_table.service_id where year(transaction_table.date_rendered) = ? group by transaction_table.service_idselect_transaction_semestral = select service_table.service_name as 'Service Name', sum(transaction_table.cost_payable) as 'Cost Payable' from service_table join transaction_table on service_table.service_id = transaction_table.service_id where transaction_table.date_rendered between (select date_start from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) and (select date_end from diagnostic_table where sem_num = ? and (year(date_start) = ? or year(date_end) = ?)) group by transaction_table.service_idselect_latest_trans = select transaction_table.date_rendered as 'Date', service_table.service_name as 'Service Name', substring(transaction_table.amount_paid,1,5) as 'Cash Rendered', substring(transaction_table.cost_payable,1,5) as "Cost Payable" from transaction_table inner join service_table on service_table.service_id = transaction_table.service_id where transaction_table.username = ? order by transaction_table.transaction_id desc limit 5select_trans_by_user = select service_table.service_name as 'Service Name', sum(transaction_table.amount_paid) as 'Amount Paid', sum(transaction_table.cost_payable) as 'Cost Payable' from service_table join transaction_table on service_table.service_id = transaction_table.service_id where transaction_table.username = ? and transaction_table.amount_paid < transaction_table.cost_payable group by transaction_table.service_idupdate_activate_student = update student_table set status = 'active' where username = ?update_deactivate_student = update student_table set status = 'inactive' where username = ?update_profile_pic = update student_table set photolink = ? where username = ?update_amount = update transaction_table set amount_paid = ? where transaction_id = ?update_cash_close = update cash_flow_table set cash_close = cash_close + ? where dates = curdate()update_balance = update student_table set balance = ? where username = ?update_logout_expand = update pc_usage_table set logout_time = ? where transaction_id = ?update_cost_transaction = update transaction_table set cost_payable = (select cost_payable + ? where transaction_id = ?) where transaction_id = ?update_cost_transaction_plain = update transaction_table set cost_payable = ? where transaction_id = ?update_amount_transaction = update transaction_table set amount_paid = (select amount_paid + ? where transaction_id = ?) where transaction_id = ?update_pasa_hour_table = update pasa_hour_table set deducted_free_sender = ?, added_free_receiver = ? where transaction_id = ?update_pasa_balance_table = update pasa_balance_table set deducted_balance_sender = ?, added_balance_receiver = ? where transaction_id = ?update_receiver_time = update student_table set freetime = (select addtime(freetime,time(?)) where username = ?) where username = ?update_sender_time = update student_table set freetime = (select timediff(freetime,time(?)) where username = ?) where username = ?update_logout_pending = update pc_usage_table set logout_time = (select addtime(time(login_time), time(?))) where transaction_id = ?update_logout_time = update pc_usage_table set logout_time = curtime() where transaction_id = ?update_logout_time_with_reference = update pc_usage_table set logout_time = ? where transaction_id = ?update_user_time = update student_table set freetime = ? where username = ?update_reset_pw = update account_table set password = password(?) where username = ?update_all_status = update student_table set status = 'inactive'update_course_table = update course_table set course_abbr = ?, course_name = ? where course_abbr = ?update_user_password = update account_table set password = password(?) where username = ? and password = password(?)update_account_table = update account_table set username = ?, last_name = ?, first_name = ?, mid_name = ?, suffix_name = ? where username = ?update_admin_table = update admin_table set username = ?, delete_priv = ?, settle_priv = ?, db_access = ? where username = ?update_student_table = update student_table set username = ?, year_lvl = ?, course_abbr = ?, status = ? where username = ?update_service_table = update service_table set service_name = ?, amount = ?, page_requirement = ? where service_name = ?

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!