using System; using System.Configuration; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using MySql.Data.Common; using MySql.Data.Types; using MySql.Data.MySqlClient; namespace Admin.ShowAdmin.dao { public class ShowAdminDAO { private MySqlConnection conn; private int miShowId = 0; public int ShowID { get { return this.miShowId; } set { this.miShowId = value; } } public ShowAdminDAO() { } private string GetConnectionString() { return ConfigurationManager.ConnectionStrings["connShow"].ToString(); } ///////////////////////////////////////////////////////////////////////////////////////////// //// Show Calls ///////////////////////////////////////////////////////////////////////////////////////////// /* in p_show_tb_id INT(11), in p_venue_tb_id INT(11), in p_client_info_tb_id INT(11), in p_show_prep_start_dtm DATETIME, in p_show_start_dtm DATETIME, in p_show_end_dtm DATETIME, p_show_teardown_end_dtm DATETIME */ public int addShow(int iVenueId, int iClientId, int iPersonnelRequired, DateTime dtShowPrepStart, DateTime dtShowStart, DateTime dtShowEnd, DateTime dtTeardownEnd, int iShowQuoted, int iShowConfirmed, int iShowCompleted) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("add_show", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_venue_tb_id", iVenueId); cmd.Parameters.AddWithValue("p_client_info_tb_id", iClientId); cmd.Parameters.AddWithValue("p_personnel_required", iPersonnelRequired); cmd.Parameters.AddWithValue("p_show_prep_start_dtm", dtShowPrepStart); cmd.Parameters.AddWithValue("p_show_start_dtm", dtShowStart); cmd.Parameters.AddWithValue("p_show_end_dtm", dtShowEnd); cmd.Parameters.AddWithValue("p_show_teardown_end_dtm", dtTeardownEnd); cmd.Parameters.AddWithValue("p_show_quoted", iShowQuoted); cmd.Parameters.AddWithValue("p_show_confirmed", iShowConfirmed); cmd.Parameters.AddWithValue("p_show_completed", iShowCompleted); MySqlParameter pInOut = cmd.Parameters.Add("p_last_insert_id", MySqlDbType.Int32); pInOut.Direction = ParameterDirection.InputOutput; recsAffected = cmd.ExecuteNonQuery(); miShowId = (int)pInOut.Value; } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public int updateShow(int iShowId, int iVenueId, int iClientId, DateTime dtShowPrepStart, DateTime dtShowStart, DateTime dtShowEnd, DateTime dtTeardownEnd) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("update_show", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); cmd.Parameters.AddWithValue("p_venue_tb_id", iVenueId); cmd.Parameters.AddWithValue("p_client_info_tb_id", iClientId); cmd.Parameters.AddWithValue("p_show_prep_start_dtm", dtShowPrepStart); cmd.Parameters.AddWithValue("p_show_start_dtm", dtShowStart); cmd.Parameters.AddWithValue("p_show_end_dtm", dtShowEnd); cmd.Parameters.AddWithValue("p_show_teardown_end_dtm", dtTeardownEnd); recsAffected = cmd.ExecuteNonQuery(); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public int deleteShow(int iShowId) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("delete_show", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); recsAffected = cmd.ExecuteNonQuery(); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public DataTable getShowShortListing() { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("get_show_short_listing", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } public DataTable getAllShows() { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("get_all_shows", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } public DataTable searchShowsById(int iShowId) { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("search_shows_by_id", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } public DataTable searchShowsByClient(int iClientId) { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("search_shows_by_client", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_client_info_tb_id", iClientId); reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } public DataTable searchShowsByVenue(int iVenueId) { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("search_shows_by_venue", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_venue_tb_id", iVenueId); reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } public DataTable searchShowsByDateRange(DateTime dtStart, DateTime dtEnd) { DataTable dtShows = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("search_shows_by_date_range", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_range_start_dtm", dtStart); cmd.Parameters.AddWithValue("p_range_end_dtm", dtEnd); reader = cmd.ExecuteReader(); dtShows = new DataTable(); dtShows.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtShows; } ///////////////////////////////////////////////////////////////////////////////////////////// //// Fee Structure XREF Calls ///////////////////////////////////////////////////////////////////////////////////////////// /* in p_show_fee_structure_xref_tb_id INT(11), in p_show_tb_id INT(11), in p_fee_structure_tb_id INT(11), in p_fee_structure_quantification DECIMAL(8,3) */ public int addFeeStructureXREF(int iShowId, int iFeeStructureId, double dfeeStructureQuant) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("add_show_fee_structure_xref", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); cmd.Parameters.AddWithValue("p_fee_structure_tb_id", iFeeStructureId); cmd.Parameters.AddWithValue("p_fee_structure_quantification", dfeeStructureQuant); recsAffected = cmd.ExecuteNonQuery(); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public int updateFeeStructureXREF(int iFeeStructXREFId, int iShowId, int iFeeStructureId, double dfeeStructureQuant) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("update_show_fee_structure_xref", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_fee_structure_xref_tb_id", iFeeStructXREFId); cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); cmd.Parameters.AddWithValue("p_fee_structure_tb_id", iFeeStructureId); cmd.Parameters.AddWithValue("p_fee_structure_quantification", dfeeStructureQuant); recsAffected = cmd.ExecuteNonQuery(); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public int deleteFeeStructureXREF(int iFeeStructXREFId) { int recsAffected = 0; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("delete_show_fee_structure_xref", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_fee_structure_xref_tb_id", iFeeStructXREFId); recsAffected = cmd.ExecuteNonQuery(); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return recsAffected; } public DataTable getShowFeeStructures(int iShowId) { DataTable dtFeeStructs = null; MySqlDataReader reader = null; try { conn = new MySqlConnection(GetConnectionString()); conn.Open(); MySqlCommand cmd = new MySqlCommand("get_show_fee_structures", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_show_tb_id", iShowId); reader = cmd.ExecuteReader(); dtFeeStructs = new DataTable(); dtFeeStructs.Load(reader); } catch (Exception e) { string err = e.Message + "\n\n" + e.StackTrace; Console.WriteLine(err); } conn.Close(); return dtFeeStructs; } } }