public static int Insert(int offering_id, int organisation_id, int staff_id, int patient_id, int quantity, DateTime date_ordered, DateTime date_filled, DateTime date_cancelled, string descr)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO OfferingOrder (offering_id,organisation_id,staff_id,patient_id,quantity,date_ordered,date_filled,date_cancelled,descr) VALUES (" + "" + (offering_id == -1 ? "NULL" : offering_id.ToString()) + "," + "" + (organisation_id == 0 ? "NULL" : organisation_id.ToString()) + "," + "" + (staff_id == -1 ? "NULL" : staff_id.ToString()) + "," + (patient_id == -1 ? "NULL" : patient_id.ToString()) + "," + quantity + "," + (date_ordered == DateTime.MinValue ? "NULL" : "'" + date_ordered.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (date_filled == DateTime.MinValue ? "NULL" : "'" + date_filled.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (date_cancelled == DateTime.MinValue ? "NULL" : "'" + date_cancelled.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + "'" + descr + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string descr, int organisation_type_group_id)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO OrganisationType (descr, organisation_type_group_id) VALUES (" + "'" + descr + "'," + organisation_type_group_id + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int patient_id, int condition_id, DateTime date, int nweeksdue, string text, bool is_deleted)
{
text = text.Replace("'", "''");
string sql = "INSERT INTO PatientCondition (patient_id,condition_id,date,nweeksdue,text,is_deleted) VALUES (" + "" + patient_id + "," + "" + condition_id + "," + (date == DateTime.MinValue ? "NULL" : "'" + date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + nweeksdue + "," + "'" + text + "'," + (is_deleted ? "1" : "0") + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int entity_id, DateTime date_added, int added_by, int note_type_id, int body_part_id, int medical_service_type_id, string text, int site_id)
{
text = text.Replace("'", "''");
string sql = "INSERT INTO Note (entity_id,date_added,added_by,note_type_id,body_part_id,medical_service_type_id,text,site_id) VALUES (" + entity_id + "," + "'" + date_added.ToString("yyyy-MM-dd HH:mm:ss") + "'" + "," + added_by + "," + note_type_id + "," + (body_part_id == -1 ? "NULL" : body_part_id.ToString()) + "," + (medical_service_type_id == -1 ? "NULL" : medical_service_type_id.ToString()) + "," + "'" + text + "'," + (site_id == -1 ? "NULL" : site_id.ToString()) + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int note_id, int note_type_id, int body_part_id, string text, DateTime date_added, DateTime date_modified, DateTime date_deleted, int added_by, int modified_by, int deleted_by, int site_id)
{
text = text.Replace("'", "''");
string sql = "INSERT INTO NoteHistory (note_id,note_type_id,body_part_id,text,date_added,date_modified,date_deleted,added_by,modified_by,deleted_by,site_id) VALUES (" + "" + note_id + "," + "" + note_type_id + "," + "" + (body_part_id == -1 ? "NULL" : body_part_id.ToString()) + "," + "'" + text + "'," + "'" + date_added.ToString("yyyy-MM-dd HH:mm:ss") + "'," + (date_modified == DateTime.MinValue ? "NULL" : "'" + date_modified.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (date_deleted == DateTime.MinValue ? "NULL" : "'" + date_deleted.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (added_by == -1 ? "NULL" : added_by.ToString()) + "," + (modified_by == -1 ? "NULL" : modified_by.ToString()) + "," + (deleted_by == -1 ? "NULL" : deleted_by.ToString()) + "," + (site_id == -1 ? "NULL" : site_id.ToString()) + "" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int GetGenericCount(string tableName, string whereClause = null)
{
int startTime = Environment.TickCount;
try
{
string sql = "SELECT COUNT(*) FROM " + tableName + " ";
if (whereClause != null && whereClause.Length > 0)
{
sql += " WHERE " + whereClause;
}
if (Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["LogAllSql"]))
{
Logger.LogQuery(sql, Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["LogAllSqlStackTraces"]), false, true);
}
else if (Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["LogAllSqlStackTraces"]))
{
Logger.LogCallingMethod(true);
}
int result = Convert.ToInt32(DBBase.ExecuteSingleResult(sql));
return(result);
}
finally
{
if (Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["LogAllSql"]))
{
Logger.LogQuery(((double)(Environment.TickCount - startTime) / 1000.0).ToString() + " seconds", Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["LogAllSqlStackTraces"]), false, true);
}
}
}
public static bool RandomStringExists(string random_string)
{
random_string = random_string.Replace("'", "''");
string sql = "SELECT COUNT(setup_new_customer_id) FROM SetupNewCustomer WHERE random_string = '" + random_string + "'";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, "Mediclinic_Main")) > 0);
}
public static int Insert(string descr)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO StaffPosition (descr) VALUES (" + "'" + descr + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string descr, bool show_date, bool show_nweeksdue, bool show_text, int display_order, bool is_deleted)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO Condition (descr,show_date,show_nweeksdue,show_text,display_order,is_deleted) VALUES (" + "'" + descr + "'," + (show_date ? "1" : "0") + "," + (show_nweeksdue ? "1" : "0") + "," + (show_text ? "1" : "0") + "," + display_order + "," + (is_deleted ? "1" : "0") + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static bool LoginExists(string login, int staff_id_exclude = -1)
{
login = login.Replace("'", "''");
string sql = "SELECT COUNT(*) FROM Staff WHERE login COLLATE Latin1_General_CS_AS = '" + login + "'" + (staff_id_exclude == -1 ? "" : " AND staff_id <> " + staff_id_exclude);
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)) > 0);
}
public static int Insert(string email_address_to_send_printed_letters_to, bool ready_to_process)
{
email_address_to_send_printed_letters_to = email_address_to_send_printed_letters_to.Replace("'", "''");
string sql = "INSERT INTO BulkLetterSendingQueueBatch (email_address_to_send_printed_letters_to,ready_to_process) VALUES (" + "'" + email_address_to_send_printed_letters_to + "'," + (ready_to_process ? "1" : "0") + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int GetCountByProvNbr(string provider_number)
{
provider_number = provider_number.Replace("'", "''");
string sql = "SELECT COUNT(*) FROM Staff WHERE provider_number = '" + provider_number.ToString() + "'";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static bool UsernameExists(string username)
{
username = username.Replace("'", "''");
string sql = "SELECT COUNT(*) FROM UserDatabaseMapper WHERE username COLLATE Latin1_General_CS_AS = '" + username + "'";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, "Mediclinic_Main")) > 0);
}
public static int Insert(int invoice_id, decimal total, int refund_reason_id, string comment, int staff_id, string DB = null)
{
comment = comment.Replace("'", "''");
string sql = "INSERT INTO Refund (invoice_id,total,refund_reason_id,comment,refund_date_added,staff_id) VALUES (" + "" + invoice_id + "," + "" + total + "," + "" + refund_reason_id + "," + "'" + comment + "'," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + "" + staff_id + "" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, DB)));
}
public static int Insert(string descr, int display_order)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO BookingChangeHistoryReason (descr,display_order) VALUES (" + "'" + descr + "'," + "" + display_order + "" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
protected static int Insert(int credit_type_id, int entity_id, decimal amount, string voucher_descr, DateTime expiry_date, int voucher_credit_id, int invoice_id, int tyro_payment_pending_id, int added_by)
{
voucher_descr = voucher_descr.Replace("'", "''");
string sql = "INSERT INTO Credit (credit_type_id,entity_id,amount,voucher_descr,expiry_date,voucher_credit_id,invoice_id,tyro_payment_pending_id,added_by,date_added,deleted_by,date_deleted,pre_deleted_amount,modified_by,date_modified) VALUES (" + "" + credit_type_id + "," + (entity_id == -1 ? "NULL" : entity_id.ToString()) + "," + "" + amount + "," + "'" + voucher_descr + "'," + (expiry_date == DateTime.MinValue ? "NULL" : "'" + expiry_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (voucher_credit_id == -1 ? "NULL" : voucher_credit_id.ToString()) + "," + (invoice_id == -1 ? "NULL" : invoice_id.ToString()) + "," + (tyro_payment_pending_id == -1 ? "NULL" : tyro_payment_pending_id.ToString()) + "," + "" + added_by + "," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', NULL, NULL, 0, NULL, NULL);SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string DB, int invoice_id, decimal payment_amount, string customer_name)
{
customer_name = customer_name.Replace("'", "''");
string sql = "INSERT INTO PaymentPending (invoice_id,payment_amount,customer_name,date_added,out_date_processed,out_payment_result,out_payment_result_code,out_payment_result_text,out_bank_receipt_id,out_paytecht_payment_id) VALUES (" + "" + invoice_id + "," + "" + payment_amount + "," + "'" + customer_name + "'," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + "NULL," + "''," + "''," + "''," + "''," + "''" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, DB)));
}
public static bool SiteAlreadyCreatedForThisEmail(string email)
{
email = email.Replace("'", "''");
string sql = "SELECT COUNT(*) FROM SetupNewCustomer WHERE company_email = '" + email + "' AND date_added_db IS NOT NULL;";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, "Mediclinic_Main")) > 0);
}
public static int Insert(int invoice_id, decimal total, string reason, int staff_id)
{
reason = reason.Replace("'", "''");
string sql = "INSERT INTO CreditNote (invoice_id,total,reason,credit_note_date_added,staff_id,reversed_by,reversed_date,pre_reversed_amount) VALUES (" + "" + invoice_id + "," + "" + total + "," + "'" + reason + "'," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + "" + staff_id + "," + "NULL" + "," + "NULL" + "," + "0.00" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int contact_type_group_id, string descr, int display_order)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO ContactType (contact_type_group_id,descr,display_order) VALUES (" + "" + contact_type_group_id + "," + "'" + descr + "'" + "," + display_order + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string descr, int address_channel_type_id)
{
descr = descr.Replace("'", "''");
string sql = "INSERT INTO AddressChannel (descr,address_channel_type_id,address_channel_date_modified) VALUES (" + "'" + descr + "'," + "" + address_channel_type_id + "," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int sms_and_email_type_id, int patient_id, int booking_id, string email, string message)
{
email = email.Replace("'", "''");
message = message.Replace("'", "''");
string sql = "INSERT INTO EmailHistory (sms_and_email_type_id,patient_id,booking_id,email,message,datetime_sent) VALUES (" + sms_and_email_type_id + "," + (patient_id == -1 ? "NULL" : patient_id.ToString()) + "," + (booking_id == -1 ? "NULL" : booking_id.ToString()) + "," + "'" + email + "'," + "'" + message + "'," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int booking_patient_id, int offering_id, int quantity, int added_by, string area_treated)
{
area_treated = area_treated.Replace("'", "''");
string sql = "INSERT INTO BookingPatientOffering (booking_patient_id,offering_id,quantity,added_by,added_date,is_deleted,deleted_by,deleted_date, area_treated) VALUES (" + "" + booking_patient_id + "," + "" + offering_id + "," + "" + quantity + "," + "" + added_by + "," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + "0," + "NULL" + "," + "NULL" + ",'" + area_treated + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string username, string dbname)
{
username = username.Replace("'", "''");
dbname = dbname.Replace("'", "''");
string sql = "INSERT INTO UserDatabaseMapper (username,dbname) VALUES (" + "'" + username + "'," + "'" + dbname + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql, "Mediclinic_Main")));
}
public static int Insert(int patient_id, string name, string email)
{
name = name.Replace("'", "''");
email = email.Replace("'", "''");
string sql = "INSERT INTO ReferrerAdditionalEmail (patient_id,name,email,deleted_by,date_deleted) VALUES (" + patient_id + ",'" + name + "'," + "'" + email + "',NULL,NULL" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static decimal GetStaffReminders(DateTime fromDate, DateTime toDate)
{
string sql = "SELECT COALESCE(sum(cost), 0) FROM SMSHistory WHERE patient_id IS NULL AND booking_id IS NULL" +
(fromDate != DateTime.MinValue ? " AND datetime_sent >= '" + fromDate.ToString("yyyy-MM-dd HH:mm:ss") + "'" : "") +
(toDate != DateTime.MinValue ? " AND datetime_sent <= '" + toDate.ToString("yyyy-MM-dd HH:mm:ss") + "'" : "");
return(Convert.ToDecimal(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int organisation_id, int letter_type_id, int site_id, string code, string docname, bool is_send_to_medico, bool is_allowed_reclaim, bool is_manual_override, int num_copies_to_print)
{
code = code.Replace("'", "''");
docname = docname.Replace("'", "''");
string sql = "INSERT INTO LetterBest (organisation_id,letter_type_id,site_id,code,docname,is_send_to_medico,is_allowed_reclaim,is_manual_override,num_copies_to_print) VALUES (" + (organisation_id == 0 ? "NULL" : organisation_id.ToString()) + "," + letter_type_id + "," + site_id + "," + "'" + code + "'," + "'" + docname + "'," + (is_send_to_medico ? "1," : "0,") + (is_allowed_reclaim ? "1," : "0,") + (is_manual_override ? "1," : "0,") + "" + num_copies_to_print + "" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int staff_id, int patient_id, string username, int site_id, bool is_successful, string session_id, string ipaddress)
{
username = username.Replace("'", "''");
session_id = session_id.Replace("'", "''");
ipaddress = ipaddress.Replace("'", "''");
string sql = "INSERT INTO UserLogin (staff_id,patient_id,username,site_id,is_successful,session_id,ipaddress) VALUES (" + "" + (staff_id == -1 ? "NULL" : staff_id.ToString()) + "," + (patient_id == -1 ? "NULL" : patient_id.ToString()) + "," + "'" + username + "'," + (site_id < 0 ? "NULL" : site_id.ToString()) + "," + (is_successful ? "1," : "0,") + "'" + session_id + "'," + "'" + ipaddress + "'" + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(string name, string postcode, string state)
{
name = name.Replace("'", "''");
postcode = postcode.Replace("'", "''");
state = state.Replace("'", "''");
string sql = "INSERT INTO Suburb (name,postcode,state,previous) VALUES (" + "'" + name + "'," + "'" + postcode + "'," + "'" + state + "','');SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}
public static int Insert(int entity_id, int contact_type_id, string free_text, string addr_line1, string addr_line2, int address_channel_id, int suburb_id, int country_id, int site_id, bool is_billing, bool is_non_billing)
{
free_text = free_text.Replace("'", "''");
addr_line1 = addr_line1.Replace("'", "''");
addr_line2 = addr_line2.Replace("'", "''");
string sql = "INSERT INTO Contact (entity_id,free_text,addr_line1,addr_line2,contact_type_id,address_channel_id,suburb_id,country_id,site_id,is_billing,is_non_billing) VALUES (" + "" + entity_id + "," + "'" + free_text + "'," + "'" + addr_line1 + "'," + "'" + addr_line2 + "'," + "" + contact_type_id + "," + "" + (address_channel_id == -1 ? "NULL" : address_channel_id.ToString()) + "," + "" + (suburb_id == -1 ? "NULL" : suburb_id.ToString()) + "," + "" + (country_id == -1 ? "NULL" : country_id.ToString()) + "," + (site_id == -1 ? "NULL" : site_id.ToString()) + "," + (is_billing ? "1," : "0,") + (is_non_billing ? "1" : "0") + ");SELECT SCOPE_IDENTITY();";
return(Convert.ToInt32(DBBase.ExecuteSingleResult(sql)));
}