public static void Delete(int organisation_type_id)
{
try
{
DBBase.ExecuteNonResult("DELETE FROM OrganisationType WHERE organisation_type_id = " + organisation_type_id.ToString());
}
catch (System.Data.SqlClient.SqlException sqlEx)
{
if (sqlEx.Errors.Count > 0 && sqlEx.Errors[0].Number == 547) // Assume the interesting stuff is in the first error
{
throw new ForeignKeyConstraintException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, sqlEx);
}
else
{
throw;
}
}
}
public static Tuple <int, string> Insert(int invoice_id, string db, decimal amount, DateTime out_date_processed, string out_result, string out_healthpointRefTag, decimal out_healthpointTotalBenefitAmount, DateTime out_healthpointSettlementDateTime, DateTime out_healthpointTerminalDateTime, string out_healthpointMemberNumber, string out_healthpointProviderId, string out_healthpointServiceType, decimal out_healthpointGapAmount, string out_healthpointPhfResponseCode, string out_healthpointPhfResponseCodeDescription)
{
out_result = out_result.Replace("'", "''");
out_healthpointRefTag = out_healthpointRefTag.Replace("'", "''");
out_healthpointMemberNumber = out_healthpointMemberNumber.Replace("'", "''");
out_healthpointProviderId = out_healthpointProviderId.Replace("'", "''");
out_healthpointServiceType = out_healthpointServiceType.Replace("'", "''");
out_healthpointPhfResponseCode = out_healthpointPhfResponseCode.Replace("'", "''");
out_healthpointPhfResponseCodeDescription = out_healthpointPhfResponseCodeDescription.Replace("'", "''");
string sql = "INSERT INTO TyroHealthClaim (invoice_id,tyro_transaction_id,amount,date_added,out_date_processed,out_result,out_healthpointRefTag,out_healthpointTotalBenefitAmount,out_healthpointSettlementDateTime,out_healthpointTerminalDateTime,out_healthpointMemberNumber,out_healthpointProviderId,out_healthpointServiceType,out_healthpointGapAmount,out_healthpointPhfResponseCode,out_healthpointPhfResponseCodeDescription,date_cancelled) VALUES (" + "" + invoice_id + "," + "''," + "" + amount + "," + "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'," + (out_date_processed == DateTime.MinValue ? "NULL" : "'" + out_date_processed.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + "'" + out_result + "'," + "'" + out_healthpointRefTag + "'," + "" + out_healthpointTotalBenefitAmount + "," + (out_healthpointSettlementDateTime == DateTime.MinValue ? "NULL" : "'" + out_healthpointSettlementDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + (out_healthpointTerminalDateTime == DateTime.MinValue ? "NULL" : "'" + out_healthpointTerminalDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'") + "," + "'" + out_healthpointMemberNumber + "'," + "'" + out_healthpointProviderId + "'," + "'" + out_healthpointServiceType + "'," + "" + out_healthpointGapAmount + "," + "'" + out_healthpointPhfResponseCode + "'," + "'" + out_healthpointPhfResponseCodeDescription + "'" + ",NULL);SELECT SCOPE_IDENTITY();";
int tyro_health_claim_id = Convert.ToInt32(DBBase.ExecuteSingleResult(sql));
string tyro_transaction_id = db.Substring(11) + "-" + tyro_health_claim_id.ToString().PadLeft(6, '0');
string sql_update = "UPDATE TyroHealthClaim SET tyro_transaction_id = '" + tyro_transaction_id.Replace("'", "''") + "' WHERE tyro_health_claim_id = " + tyro_health_claim_id;
DBBase.ExecuteNonResult(sql_update);
return(new Tuple <int, string>(tyro_health_claim_id, tyro_transaction_id));
}
public static void Update(int bulk_letter_sending_queue_id, int bulk_letter_sending_queue_batch_id, int letter_print_history_send_method_id, int added_by, int patient_id, int referrer_id, int booking_id, string phone_number, string email_to_address, string email_to_name, string email_from_address, string email_from_name, string text, string email_subject, string email_attachment_location, bool email_attachment_delete_after_sending, bool email_attachment_folder_delete_after_sending, int email_letter_letter_id, bool email_letter_keep_history_in_db, bool email_letter_keep_history_in_file, int email_letter_letter_print_history_send_method_id, string email_letter_history_dir, string email_letter_history_filename, int email_letter_site_id, int email_letter_organisation_id, int email_letter_booking_id, int email_letter_patient_id, int email_letter_register_referrer_id_to_use_instead_of_patients_reg_ref, int email_letter_staff_id, int email_letter_health_card_action_id, string email_letter_source_template_path, string email_letter_output_doc_path, bool email_letter_is_double_sided_printing, string email_letter_extra_pages, string email_letter_item_seperator, string sql_to_run_on_completion, string sql_to_run_on_failure, DateTime datetime_sending_start, DateTime datetime_sent)
{
phone_number = phone_number.Replace("'", "''");
email_to_address = email_to_address.Replace("'", "''");
email_to_name = email_to_name.Replace("'", "''");
email_from_address = email_from_address.Replace("'", "''");
email_from_name = email_from_name.Replace("'", "''");
text = text.Replace("'", "''");
email_subject = email_subject.Replace("'", "''");
email_attachment_location = email_attachment_location.Replace("'", "''");
email_letter_history_dir = email_letter_history_dir.Replace("'", "''");
email_letter_history_filename = email_letter_history_filename.Replace("'", "''");
email_letter_source_template_path = email_letter_source_template_path.Replace("'", "''");
email_letter_output_doc_path = email_letter_output_doc_path.Replace("'", "''");
email_letter_extra_pages = email_letter_extra_pages.Replace("'", "''");
string sql = "UPDATE BulkLetterSendingQueue SET bulk_letter_sending_queue_batch_id = " + bulk_letter_sending_queue_batch_id + ",letter_print_history_send_method_id = " + letter_print_history_send_method_id + ",added_by = " + (added_by == -1 ? "NULL" : added_by.ToString()) + ",patient_id = " + (patient_id == -1 ? "NULL" : patient_id.ToString()) + ",referrer_id = " + (referrer_id == -1 ? "NULL" : referrer_id.ToString()) + ",booking_id = " + (booking_id == -1 ? "NULL" : booking_id.ToString()) + ",phone_number = '" + phone_number + "',email_to_address = '" + email_to_address + "',email_to_name = '" + email_to_name + "',email_from_address = '" + email_from_address + "',email_from_name = '" + email_from_name + "',text = '" + text + "',email_subject = '" + email_subject + "',email_attachment_location = '" + email_attachment_location + "',email_attachment_delete_after_sending = " + (email_attachment_delete_after_sending ? "1," : "0,") + "email_attachment_folder_delete_after_sending = " + (email_attachment_folder_delete_after_sending ? "1," : "0,") + "email_letter_letter_id = " + (email_letter_letter_id == -1 ? "NULL" : email_letter_letter_id.ToString()) + ",email_letter_keep_history_in_db = " + (email_letter_keep_history_in_db ? "1," : "0,") + "email_letter_keep_history_in_file = " + (email_letter_keep_history_in_file ? "1," : "0,") + "email_letter_letter_print_history_send_method_id = " + (email_letter_letter_print_history_send_method_id == -1 ? "NULL" : email_letter_letter_print_history_send_method_id.ToString()) + ",email_letter_history_dir = '" + email_letter_history_dir + "',email_letter_history_filename = '" + email_letter_history_filename + "',email_letter_site_id = " + (email_letter_site_id == -1 ? "NULL" : email_letter_site_id.ToString()) + ",email_letter_organisation_id = " + (email_letter_organisation_id == 0 ? "NULL" : email_letter_organisation_id.ToString()) + ",email_letter_booking_id = " + (email_letter_booking_id == -1 ? "NULL" : email_letter_booking_id.ToString()) + ",email_letter_patient_id = " + (email_letter_patient_id == -1 ? "NULL" : email_letter_patient_id.ToString()) + ",email_letter_register_referrer_id_to_use_instead_of_patients_reg_ref = " + (email_letter_register_referrer_id_to_use_instead_of_patients_reg_ref == -1 ? "NULL" : email_letter_register_referrer_id_to_use_instead_of_patients_reg_ref.ToString()) + ",email_letter_staff_id = " + (email_letter_staff_id == -1 ? "NULL" : email_letter_staff_id.ToString()) + ",email_letter_health_card_action_id = " + (email_letter_health_card_action_id == -1 ? "NULL" : email_letter_health_card_action_id.ToString()) + ",email_letter_source_template_path = '" + email_letter_source_template_path + "',email_letter_output_doc_path = '" + email_letter_output_doc_path + "',email_letter_is_double_sided_printing = " + (email_letter_is_double_sided_printing ? "1," : "0,") + "email_letter_extra_pages = '" + email_letter_extra_pages + "',email_letter_item_seperator = '" + email_letter_item_seperator + "',sql_to_run_on_completion = '" + sql_to_run_on_completion + "',sql_to_run_on_failure = '" + sql_to_run_on_failure + "',datetime_sending_start = '" + datetime_sending_start.ToString("yyyy-MM-dd HH:mm:ss") + "',datetime_sent = '" + datetime_sent.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE bulk_letter_sending_queue_id = " + bulk_letter_sending_queue_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Update(int patient_history_id, int patient_id, bool is_clinic_patient, bool is_gp_patient, bool is_deleted, bool is_deceased, string flashing_text, int flashing_text_added_by, DateTime flashing_text_last_modified_date, string private_health_fund, string concession_card_number, DateTime concession_card_expiry_date, bool is_diabetic, bool is_member_diabetes_australia, DateTime diabetic_assessment_review_date, int ac_inv_offering_id, int ac_pat_offering_id, string login, string pwd, bool is_company, string abn, string next_of_kin_name, string next_of_kin_relation, string next_of_kin_contact_info, int title_id, string firstname, string middlename, string surname, string nickname, string gender, DateTime dob)
{
flashing_text = flashing_text.Replace("'", "''");
private_health_fund = private_health_fund.Replace("'", "''");
concession_card_number = concession_card_number.Replace("'", "''");
login = login.Replace("'", "''");
pwd = pwd.Replace("'", "''");
abn = abn.Replace("'", "''");
next_of_kin_name = next_of_kin_name.Replace("'", "''");
next_of_kin_relation = next_of_kin_relation.Replace("'", "''");
next_of_kin_contact_info = next_of_kin_contact_info.Replace("'", "''");
firstname = firstname.Replace("'", "''");
middlename = middlename.Replace("'", "''");
surname = surname.Replace("'", "''");
nickname = nickname.Replace("'", "''");
gender = gender.Replace("'", "''");
string sql = "UPDATE PatientHistory SET patient_id = " + patient_id + ",is_clinic_patient = " + (is_clinic_patient ? "1," : "0,") + "is_gp_patient = " + (is_gp_patient ? "1," : "0,") + "is_deleted = " + (is_deleted ? "1," : "0,") + "is_deceased = " + (is_deceased ? "1," : "0,") + "flashing_text = '" + flashing_text + "',flashing_text_added_by = " + (flashing_text_added_by == -1 ? "NULL" : flashing_text_added_by.ToString()) + "',flashing_text_last_modified_date = " + (flashing_text_last_modified_date == DateTime.MinValue ? "NULL" : "'" + flashing_text_last_modified_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",private_health_fund = '" + private_health_fund + "',concession_card_number = '" + concession_card_number + "',concession_card_expiry_date = " + (concession_card_expiry_date == DateTime.MinValue ? "NULL" : "'" + concession_card_expiry_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",is_diabetic = " + (is_diabetic ? "1" : "0") + ",is_member_diabetes_australia = " + (is_member_diabetes_australia ? "1" : "0") + ", diabetic_assessment_review_date =" + (diabetic_assessment_review_date == DateTime.MinValue ? "NULL" : "'" + diabetic_assessment_review_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",ac_inv_offering_id = " + (ac_inv_offering_id == -1 ? "NULL" : ac_inv_offering_id.ToString()) + ",ac_pat_offering_id = " + (ac_pat_offering_id == -1 ? "NULL" : ac_pat_offering_id.ToString()) + ",login = '******',pwd = '" + pwd + "'" + ",is_company = " + (is_company ? "1" : "0") + ", abn = '" + abn + "',next_of_kin_name = '" + next_of_kin_name + "',next_of_kin_relation = '" + next_of_kin_relation + "',next_of_kin_contact_info = '" + next_of_kin_contact_info + "',title_id = " + title_id + ",firstname = '" + firstname + "',middlename = '" + middlename + "',surname = '" + surname + "',nickname = '" + nickname + "',gender = '" + gender + "',dob = " + (dob == DateTime.MinValue ? "NULL" : "'" + dob.ToString("yyyy-MM-dd HH:mm:ss") + "'") + " WHERE patient_history_id = " + patient_history_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Delete(int entity_id, bool checkForeignKeys = true)
{
if (checkForeignKeys)
{
// do NOT delete the "entity" that this is for - that should be done "explicitly" elsewhere
// but make sure there is no entity that it relies on
if (SiteDB.GetCountByEntityID(entity_id) > 0)
{
throw new ForeignKeyConstraintException("Can not delete entity_id " + entity_id + " because a ForeignKey Site record depends on it ");
}
if (PersonDB.GetCountByEntityID(entity_id) > 0)
{
throw new ForeignKeyConstraintException("Can not delete entity_id " + entity_id + " because a ForeignKey Person record depends on it ");
}
if (OrganisationDB.GetCountByEntityID(entity_id) > 0)
{
throw new ForeignKeyConstraintException("Can not delete entity_id " + entity_id + " because a ForeignKey Organisation record depends on it ");
}
if (BookingDB.GetCountByEntityID(entity_id) > 0)
{
throw new ForeignKeyConstraintException("Can not delete entity_id " + entity_id + " because a ForeignKey Booking record depends on it ");
}
if (InvoiceDB.GetCountByEntityID(entity_id) > 0)
{
throw new ForeignKeyConstraintException("Can not delete entity_id " + entity_id + " because a ForeignKey Invoice record depends on it ");
}
}
// delete all things associated with the entity
if (Utilities.GetAddressType().ToString() == "Contact")
{
ContactDB.DeleteByEntityID(entity_id);
}
else if (Utilities.GetAddressType().ToString() == "ContactAus")
{
ContactAusDB.DeleteByEntityID(entity_id);
}
NoteDB.DeleteByEntityID(entity_id);
DBBase.ExecuteNonResult("DELETE FROM Entity WHERE entity_id = " + entity_id.ToString() + "; DBCC CHECKIDENT(Entity,RESEED,1); DBCC CHECKIDENT(Entity);");
}
protected static void CloneDB(string mdFileToClone, string logFileToClone, string newName)
{
//string stubDBLocation = @"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Mediclinic_Balwyn201309181901.bak";
string stubDBLocation = @"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Mediclinic_StubDB.bak";
string sql = @"
--Alter Database " + newName + @" SET SINGLE_USER With ROLLBACK IMMEDIATE;
--Drop database " + newName + @";
RESTORE DATABASE " + newName + @"
FROM DISK = '" + stubDBLocation + @"'
WITH MOVE '" + mdFileToClone + @"' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\" + newName + @".mdf',
MOVE '" + logFileToClone + @"' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\" + newName + @"_log.ldf',
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10;
";
DBBase.ExecuteNonResult(sql, "master");
}
public static void Reverse(int creditnote_id, int reversed_by)
{
CreditNote creditNote = CreditNoteDB.GetByID(creditnote_id);
if (creditNote == null)
{
throw new CustomMessageException("Adjustment note - does not exist");
}
if (creditNote.IsReversed)
{
throw new CustomMessageException("Adjustment note already reversed");
}
// set total=0, set who and when it was reversed, and original amount
string sql = "UPDATE CreditNote SET total = 0, reversed_by = " + reversed_by + ",reversed_date = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',pre_reversed_amount = " + creditNote.Total + " WHERE creditnote_id = " + creditnote_id.ToString();
DBBase.ExecuteNonResult(sql);
// set invoice as not paid
InvoiceDB.UpdateIsPaid(null, creditNote.Invoice.InvoiceID, false);
// update the GL for the year this was done
}
public static void Update(int setup_new_customer_id, string company_name, string firstname, string surname, string company_email, string address_line1, string address_line2, string city, string state_province_region, string postcode, string country, string phone_nbr, string max_nbr_providers, string field1, string field2, string field3, string field4, bool setasprov_field1, bool setasprov_field2, bool setasprov_field3, bool setasprov_field4, string random_string, DateTime date_added_info, DateTime date_added_db, string db_name)
{
company_name = company_name.Replace("'", "''");
firstname = firstname.Replace("'", "''");
surname = surname.Replace("'", "''");
company_email = company_email.Replace("'", "''");
address_line1 = address_line1.Replace("'", "''");
address_line2 = address_line2.Replace("'", "''");
city = city.Replace("'", "''");
state_province_region = state_province_region.Replace("'", "''");
postcode = postcode.Replace("'", "''");
country = country.Replace("'", "''");
phone_nbr = phone_nbr.Replace("'", "''");
max_nbr_providers = max_nbr_providers.Replace("'", "''");
field1 = field1.Replace("'", "''");
field2 = field2.Replace("'", "''");
field3 = field3.Replace("'", "''");
field4 = field4.Replace("'", "''");
random_string = random_string.Replace("'", "''");
db_name = db_name.Replace("'", "''");
string sql = "UPDATE SetupNewCustomer SET company_name = '" + company_name + "',firstname = '" + firstname + "',surname = '" + surname + "',company_email = '" + company_email + "',address_line1 = '" + address_line1 + "',address_line2 = '" + address_line2 + "',city = '" + city + "',state_province_region = '" + state_province_region + "',postcode = '" + postcode + "',country = '" + country + "',phone_nbr = '" + phone_nbr + "',max_nbr_providers = '" + max_nbr_providers + "',field1 = '" + field1 + "',field2 = '" + field2 + "',field3 = '" + field3 + "',field4 = '" + field4 + "'" + ",setasprov_field1 = " + (setasprov_field1 ? "1" : "0") + ",setasprov_field2 = " + (setasprov_field2 ? "1" : "0") + ",setasprov_field3 = " + (setasprov_field3 ? "1" : "0") + ",setasprov_field4 = " + (setasprov_field4 ? "1" : "0") + ",random_string = '" + random_string + "',date_added_info = '" + date_added_info.ToString("yyyy-MM-dd HH:mm:ss") + "',date_added_db = " + (date_added_db == DateTime.MinValue ? "NULL" : "'" + date_added_db.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",db_name = '" + db_name + "' WHERE setup_new_customer_id = " + setup_new_customer_id.ToString();
DBBase.ExecuteNonResult(sql, "Mediclinic_Main");
}
public static void UpdateActive(int condition_id)
{
string sql = "UPDATE Condition SET is_deleted = 0 WHERE condition_id = " + condition_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateBookingScreenShowKey(int staff_id, bool bk_screen_show_key)
{
string sql = "UPDATE Staff SET bk_screen_show_key = " + (bk_screen_show_key ? "1" : "0") + " WHERE staff_id = " + staff_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Update(int receipt_id, int receipt_payment_type_id, decimal total, decimal amount_reconciled, bool is_failed_to_clear, bool is_overpaid, DateTime reconciliation_date, int reversed_by, DateTime reversed_date, decimal pre_reversed_amount)
{
string sql = "UPDATE Receipt SET receipt_payment_type_id = " + receipt_payment_type_id + ",total = " + total + ",amount_reconciled = " + amount_reconciled + ",is_failed_to_clear = " + (is_failed_to_clear ? "1" : "0") + ",is_overpaid = " + (is_overpaid ? "1" : "0") + ",reconciliation_date = " + (reconciliation_date == DateTime.MinValue ? "NULL" : "'" + reconciliation_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",reversed_by = " + (reversed_by == -1 ? "NULL" : reversed_by.ToString()) + ",reversed_date = " + (reversed_date == DateTime.MinValue ? "NULL" : "'" + reversed_date.ToString("yyyy-MM-dd HH:mm:ss") + "'") + ",pre_reversed_amount = " + pre_reversed_amount + " WHERE receipt_id = " + receipt_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateNumDaysToDisplayOnBookingScreen(int staff_id, int num_days_to_display_on_booking_screen)
{
string sql = "UPDATE Staff SET num_days_to_display_on_booking_screen = " + num_days_to_display_on_booking_screen + " WHERE staff_id = " + staff_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateShowHeaderOnBookingScreen(int staff_id, bool show_header_on_booking_screen, string DB = null)
{
string sql = "UPDATE Staff SET show_header_on_booking_screen = " + (show_header_on_booking_screen ? "1" : "0") + " WHERE staff_id = " + staff_id.ToString();
DBBase.ExecuteNonResult(sql, DB);
}
public static void UpdateLoggedOffByStaffID(int staff_id)
{
string sql = "UPDATE UserLogin SET is_logged_off = 1 WHERE staff_id = " + staff_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Update(int referrer_id, int person_id)
{
string sql = "UPDATE Referrer SET person_id = " + person_id + " WHERE referrer_id = " + referrer_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void SetReconciled(int receipt_id)
{
string sql = "UPDATE Receipt SET amount_reconciled = total WHERE receipt_id = " + receipt_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateReadyToProcess(int bulk_letter_sending_queue_batch_id, bool ready_to_process)
{
string sql = "UPDATE BulkLetterSendingQueueBatch SET ready_to_process = " + (ready_to_process ? "1" : "0") + " WHERE bulk_letter_sending_queue_batch_id = " + bulk_letter_sending_queue_batch_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Delete(string dbname)
{
DBBase.ExecuteNonResult("DELETE FROM UserDatabaseMapper WHERE dbname = '" + dbname + "'", "Mediclinic_Main");
}
public static void UpdateCancelled(string out_healthpointRefTag)
{
string sql = "UPDATE TyroHealthClaim SET date_cancelled = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE out_healthpointRefTag = '" + out_healthpointRefTag + "'";
DBBase.ExecuteNonResult(sql);
}
public static void Update(int letter_print_history_id, int letter_id, int patient_id)
{
string sql = "UPDATE LetterBestPrintHistory SET letter_id = " + letter_id + ",patient_id = " + (patient_id == -1 ? "NULL" : patient_id.ToString()) + " WHERE letter_print_history_id = " + letter_print_history_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateDisplayOrderAll(int amount)
{
string sql = "UPDATE BookingChangeHistoryReason SET display_order = display_order + (" + amount + "); UPDATE BookingChangeHistoryReason SET display_order = 0 WHERE display_order < 0;";
DBBase.ExecuteNonResult(sql);
}
public static void UpdateActive(int referrer_id)
{
string sql = "UPDATE Referrer SET is_deleted = 0 WHERE referrer_id = " + referrer_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateDisplayOrderAll(int amount)
{
string sql = "UPDATE Condition SET display_order = display_order + (" + amount + "); UPDATE Condition SET display_order = 0 WHERE display_order < 0;";
DBBase.ExecuteNonResult(sql);
}
public static void UpdateIsActive(int health_card_id, bool is_active)
{
string sql = "UPDATE HealthCard SET is_active = " + (is_active? "1" : "0") + " WHERE health_card_id = " + health_card_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void Update(int overpayment_id, int receipt_id, decimal total)
{
string sql = "UPDATE Overpayment SET receipt_id = " + receipt_id + ",total = " + total + " WHERE overpayment_id = " + overpayment_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateAllCardsInactive(int patient_id, int health_card_id_to_exclude = -1)
{
string sql = "UPDATE HealthCard SET is_active = 0 WHERE patient_id = " + patient_id.ToString() + (health_card_id_to_exclude == -1 ? "" : " AND health_card_id <> " + health_card_id_to_exclude);
DBBase.ExecuteNonResult(sql);
}
public static void UpdateActive(int booking_patient_offering_id, int staff_id)
{
string sql = "UPDATE BookingPatientOffering SET is_deleted = 0, deleted_by = " + staff_id + ", deleted_date = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE booking_patient_offering_id = " + booking_patient_offering_id.ToString();
DBBase.ExecuteNonResult(sql);
}
protected void Button4_Click(object sender, EventArgs e)
{
// need to be able to have them put in their own field instead of physio/podiatry ....
/*
* select * from UserDatabaseMapper order by id desc
* delete UserDatabaseMapper where dbname in ('Mediclinic_0002','Mediclinic_0003','Mediclinic_0004','Mediclinic_0005')
*/
// also need to not import field type phys/pod ....
try
{
decimal decNum;
int intNum;
if (txtInitialStaffFirstname.Text.Trim().Length == 0)
{
throw new CustomMessageException("Staff Firstname is a required field.");
}
if (txtInitialStaffSurname.Text.Trim().Length == 0)
{
throw new CustomMessageException("Staff Surname is a required field.");
}
if (txtInitialStaffLogin.Text.Trim().Length == 0)
{
throw new CustomMessageException("Staff Login is a required field.");
}
if (txtMedicareEclaimsLicenseNbr.Text.Trim().Length == 0)
{
throw new CustomMessageException("Medicare Eclaims License Nbr is a required field.");
}
if (!Decimal.TryParse(txtSMSPrice.Text.Trim(), out decNum))
{
throw new CustomMessageException("SMS Price must be a decimal.");
}
if (!Int32.TryParse(txtMaxNbrProviders.Text.Trim(), out intNum))
{
throw new CustomMessageException("Max Nbr Providers must be a number.");
}
if (txtSiteName.Text.Trim().Length == 0)
{
throw new CustomMessageException("Clinic Site Name is a required field.");
}
if (txtEmail_FromName.Text.Trim().Length == 0)
{
throw new CustomMessageException("Email Sending From Name is a required field.");
}
if (!Utilities.IsValidEmailAddress(txtEmail_FromEmail.Text.Trim()))
{
throw new CustomMessageException("Email Sending From Email must be a valid email address.");
}
if (!Utilities.IsValidEmailAddress(txtAdminAlertEmail_To.Text.Trim()))
{
throw new CustomMessageException("Admin Alert Email must be a valid email address.");
}
if (txtField1.Text.Trim().Length == 0)
{
throw new CustomMessageException("Field 1 is a required field.");
}
}
catch (CustomMessageException ex)
{
Label4.Text = "<font color=\"red\"><br>" + ex.Message + "</b></font>";
return;
}
string initialStaffFirstname = txtInitialStaffFirstname.Text.Trim();
string initialStaffSurname = txtInitialStaffSurname.Text.Trim();
string initialStaffLogin = GetUniqueLogin(txtInitialStaffLogin.Text.Trim().ToLower());
//
// clone the DB with new name
//
string newDBName = GetNewDBName();
CloneDB("Mediclinic_StubDB", "Mediclinic_StubDB_log", newDBName);
//
// update any config items
//
SystemVariableDB.Update("MedicareEclaimsLicenseNbr", txtMedicareEclaimsLicenseNbr.Text.Trim(), newDBName);
SystemVariableDB.Update("SMSPrice", txtSMSPrice.Text.Trim(), newDBName);
SystemVariableDB.Update("MaxNbrProviders", txtMaxNbrProviders.Text.Trim(), newDBName);
SystemVariableDB.Update("AllowAddSiteClinic", ddlAllowAddSiteClinic.SelectedValue, newDBName);
SystemVariableDB.Update("AllowAddSiteAgedCare", ddlAllowAddSiteAgedCare.SelectedValue, newDBName);
SystemVariableDB.Update("BannerMessage", txtBannerMessage.Text.Trim(), newDBName);
SystemVariableDB.Update("ShowBannerMessage", ddlShowBannerMessage.SelectedValue, newDBName);
SystemVariableDB.Update("Email_FromName", txtEmail_FromName.Text, newDBName);
SystemVariableDB.Update("Email_FromEmail", txtEmail_FromEmail.Text.Trim(), newDBName);
SystemVariableDB.Update("AdminAlertEmail_To", txtAdminAlertEmail_To.Text.Trim(), newDBName);
//
// update login/pwd for first staff memeber and for support staff
//
string sql_update_staff = @"
UPDATE Person
SET
Person.firstname = '" + initialStaffFirstname + @"'
,Person.surname = '" + initialStaffSurname + @"'
FROM Person
JOIN Staff ON Person.person_id = Staff.person_id
WHERE Staff.staff_id = 1;
UPDATE Staff SET login = '******' WHERE staff_id = 1;
UPDATE Staff SET pwd = '" + initialStaffLogin + @"' WHERE staff_id = 1;
UPDATE Staff SET login = '******' WHERE staff_id = -2;
UPDATE Staff SET login = '******' WHERE staff_id = -3;
UPDATE Staff SET login = '******' WHERE staff_id = -4;
";
if (txtField1.Text.Trim().Length > 0)
{
sql_update_staff += @"INSERT Field (descr, has_offerings) VALUES ('" + txtField1.Text.Trim() + @"' , 1);" + Environment.NewLine;
}
if (txtField2.Text.Trim().Length > 0)
{
sql_update_staff += @"INSERT Field (descr, has_offerings) VALUES ('" + txtField2.Text.Trim() + @"' , 1);" + Environment.NewLine;
}
sql_update_staff += @"UPDATE Site SET name = '" + txtSiteName.Text.Trim() + @"' WHERE site_type_id = 1;" + Environment.NewLine;
DBBase.ExecuteNonResult(sql_update_staff, newDBName);
UserDatabaseMapperDB.Insert(initialStaffLogin, newDBName);
UserDatabaseMapperDB.Insert(newDBName + "_support1", newDBName);
UserDatabaseMapperDB.Insert(newDBName + "_support2", newDBName);
UserDatabaseMapperDB.Insert(newDBName + "_support3", newDBName);
Label4.Text =
@"Database created successfully.<br />
<table border=""0"" cellpadding=""0"" cellspacing=""0"">
<tr>
<td>Database : </td>
<td style=""width:20px;""></td>
<td><b>" + newDBName + @"</b></td>
</tr>
<tr>
<td>Staff Username & Password : </td>
<td></td>
<td><b>" + initialStaffLogin + @"</b></td>
</tr>
</table>";
}
public static void Update(int health_card_action_id, int health_card_id, int health_card_action_type_id, DateTime action_date)
{
string sql = "UPDATE HealthCardAction SET health_card_id = " + health_card_id + ",health_card_action_type_id = " + health_card_action_type_id + ",action_date = '" + action_date.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE health_card_action_id = " + health_card_action_id.ToString();
DBBase.ExecuteNonResult(sql);
}
public static void UpdateQuantity(int booking_patient_offering_id, int quantity)
{
string sql = "UPDATE BookingPatientOffering SET quantity = " + quantity + " WHERE booking_patient_offering_id = " + booking_patient_offering_id.ToString();
DBBase.ExecuteNonResult(sql);
}