public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { // tbl_registration from dancetea.registration // ---------------------------------------------------------- MySqlDataReader dataReader = pMysql.Select("select * from registrations;"); pMysql.Message = "Tbl_registration (dancetea.registration) - extraction - START "; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_registration(id, tour_dates_id, studios_id, user_id, date, completed, confirmed, heard, details, enteredby_id, viewed, deleted, payment_method) " + "values('" + dataReader["id"] + "','" + dataReader["tourdateid"] + "'," + GetStudioId(dataReader["studio"].ToString(), pPostgres) + "," + "" + UserIdManage(dataReader["fname"].ToString().Replace("'", "''"), dataReader["lname"].ToString().Replace("'", "''"), dataReader["email"].ToString(), dataReader["title"].ToString(), pPostgres) + "," + "'" + Get_json_date(dataReader["date"].ToString(), dataReader["confirmdate"].ToString()) + "','1'," + CheckBool(dataReader["confirmed"].ToString()) + "," + "'" + dataReader["heard"].ToString().Replace("'", "''") + "'," + "'" + dataReader["details"].ToString().Replace("'", "''") + "'," + NVL(dataReader["enteredby"].ToString()) + ", " + CheckBool(dataReader["viewed"].ToString()) + ", " + "" + CheckBool(dataReader["deleted"].ToString()) + ",'" + dataReader["payment_method"] + "');"); } pPostgres.Insert("insert into tbl_registration(id) values(22996);"); pPostgres.Insert("insert into tbl_registration(id) values(23035);"); pPostgres.Insert("insert into tbl_registration(id) values(23127);"); pPostgres.Insert("insert into tbl_registration(id) values(24225);"); pPostgres.Insert("insert into tbl_registration(id) values(31298);"); pPostgres.Message = "Tbl_registration (dancetea.registration) - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("SELECT id, name, fee, eventid, discountfee, 12 AS season FROM tbl_event_reg_types_12 " + "union " + "SELECT id, name, fee, eventid, discountfee, 16 AS season FROM tbl_event_reg_types_16 " + "union " + "SELECT id, name, fee, eventid, discount_fee as discountfee, 18 AS season FROM tbl_event_reg_types_18 " + "union " + "SELECT id, name, fee, eventid, discount_fee as discountfee, 21 AS season FROM tbl_event_reg_types_21 " + "union " + "SELECT id, name, fee, eventid, discount_fee as discountfee, 23 AS season FROM tbl_event_reg_types_23"); pMysql.Message = "tbl_event_reg_types - extraction - START"; int counter = 0; while (dataReader.Read()) { string pEventNameId = GetId("select id from tbl_event_reg_type_names where name like '" + dataReader["name"] + "' limit 1;", pPostgres); pPostgres.Insert("insert into tbl_event_reg_types(id, old_id, seasons_id, fee, discountfee, events_id, event_reg_type_names_id) " + "values(" + ++counter + "," + dataReader["id"] + "," + dataReader["season"] + "," + dataReader["fee"] + "," + dataReader["discountfee"] + "," + "" + dataReader["eventid"] + "," + pEventNameId + ");"); } pPostgres.Message = "tbl_event_reg_types - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select distinct id, email, password, active, " + "substr(studioowner, 1,instr(studioowner,' ')) name, " + "substr(studioowner, instr(studioowner,' ')) surname, typeid " + "from tbl_users;"); pMysql.Message = "tbl_users - extraction - START - studio OWNERS"; while (dataReader.Read()) { // tbl person string test = dataReader["email"].ToString(); if (dataReader["email"].ToString() != "") { string person_type_id = (dataReader["typeid"].ToString() == "") ? "null" : dataReader["typeid"].ToString(); pPostgres.Insert("insert into tbl_person(person_types_id, fname, lname) values(" + person_type_id + ",'" + dataReader["name"].ToString().Replace("'", "''") + "','" + dataReader["surname"].ToString().Replace("'", "''") + "');"); string pom = GetId("select max(id) from tbl_person;", pPostgres); // tbl user pPostgres.Insert("insert into tbl_user(id, email, password, active, person_id) " + "values('" + dataReader["id"] + "','" + dataReader["email"] + "','" + dataReader["password"].ToString().Replace("'", "''") + "','" + CheckBool(dataReader["active"].ToString()) + "','" + pom + "');"); } } InsertDummyUsers(pPostgres); pPostgres.Message = "tbl_users - extraction - FINISH - studio OWNERS"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_event_registrations"); pMysql.Message = "Tbl_event_registrations - extraction - START "; while (dataReader.Read()) { string PersonId = GetPersonId(dataReader["address"].ToString().Replace("'", "''"), dataReader["city"].ToString().Replace("'", "''"), dataReader["state"].ToString().Replace("'", "''"), dataReader["zip"].ToString(), dataReader["countryid"].ToString(), dataReader["fname"].ToString().Replace("'", "''"), dataReader["lname"].ToString().Replace("'", "''"), dataReader["email"].ToString(), dataReader["contact_type"].ToString(), dataReader["phone"].ToString(), dataReader["phone2"].ToString(), pPostgres); string studioid = GetStudioId(dataReader["studioid"].ToString(), dataReader["organization"].ToString(), pPostgres); pPostgres.Insert( "insert into tbl_event_registrations(id, tour_date_id, studio_id, person_id, dates, registration_id, " + "user_id, entered_by_id, heard, notes, total_fees, fees_paid, balance_due, pay_choice) " + "values(" + dataReader["id"] + "," + dataReader["tourdateid"] + ", " + studioid + "," + "" + PersonId + ",'" + Get_json_date(dataReader["confirmdate"].ToString(), dataReader["regdate"].ToString()) + "'," + "" + NVL(dataReader["onlineregid"].ToString()) + "," + NVL(dataReader["onlineuserid"].ToString()) + ", " + "" + NVL(dataReader["enteredbyid"].ToString()) + ", '" + dataReader["heard"].ToString().Replace("'", "''") + "','" + dataReader["notes"].ToString().Replace("'", "''") + "'" + "," + NVL(dataReader["totalfees"].ToString()) + "," + NVL(dataReader["feespaid"].ToString()) + "," + NVL(Convert.ToDouble(dataReader["balancedue"]).ToString().Replace(",", ".")) + ", " + NVL(dataReader["paychoice"].ToString()) + ")") ; } pPostgres.Message = "Tbl_event_registrations - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select id, cast(`range` as CHAR), minimum_age, name " + "from tbl_age_divisions;"); pMysql.Message = "tbl_age_divisions - extraction - START"; string pom; while (dataReader.Read()) { pom = GetId(dataReader[3].ToString(), pPostgres); pPostgres.Insert("insert into tbl_age_divisions(id, range, minimum_age,playlist_workshop_levels_id) " + "values('" + dataReader[0] + "',null,'" + dataReader[2] + "','" + pom + "');"); } pPostgres.Message = "tbl_age_divisions - extraction - FINISH"; pPostgres.Update("update tbl_age_divisions set range='2' where id = '1';"); pPostgres.Update("update tbl_age_divisions set range='1' where id = '2';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '3';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '4';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '5';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '6';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '7';"); pPostgres.Update("update tbl_age_divisions set range='100' where id = '8';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '10';"); pPostgres.Update("update tbl_age_divisions set range='2' where id = '11';"); pPostgres.Update("update tbl_age_divisions set range='5' where id = '12';"); pPostgres.Insert("insert into tbl_age_divisions(id, playlist_workshop_levels_id) values(0,1)"); }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from store_orders;"); pMysql.Message = "tbl_store_orders - extraction - START"; int p_IsUnregistered = 1; while (dataReader.Read()) { if (dataReader["userid"].ToString() == "0") { string buyerid = UnregisteredBuyerManage(dataReader["fname"].ToString(), dataReader["lname"].ToString(), dataReader["email"].ToString(), pPostgres); pPostgres.Insert("insert into tbl_store_orders(id, user_id, order_hash, submitted, shipped, statsuser, " + "digitalonly, fees_paid, tracking, transactionid, label) " + "values('" + dataReader["id"] + "','" + buyerid + "','" + dataReader["order_hash"] + "','" + FromUnixTime(Convert.ToInt64(dataReader["submitted"])).ToString().Replace(". ", ".") + "'" + ",'" + CheckBool(dataReader["shipped"].ToString()) + "','" + dataReader["statsuser"] + "','" + CheckBool(dataReader["digitalonly"].ToString()) + "'," + "'" + dataReader["fees_paid"] + "','" + dataReader["tracking"] + "','" + dataReader["transactionid"] + "','" + "" + Get_json_label(CheckBool(dataReader["label_made"].ToString()).ToString(), dataReader["label_cost"].ToString(), dataReader["label_carrier"].ToString()) + "')"); } else { pPostgres.Insert("insert into tbl_store_orders(id, user_id, order_hash, submitted, shipped, statsuser, " + "digitalonly, fees_paid, tracking, transactionid, label) " + "values('" + dataReader["id"] + "','" + dataReader["userid"] + "','" + dataReader["order_hash"] + "','" + FromUnixTime(Convert.ToInt64(dataReader["submitted"])).ToString().Replace(". ", ".") + "'" + ",'" + CheckBool(dataReader["shipped"].ToString()) + "','" + dataReader["statsuser"] + "','" + CheckBool(dataReader["digitalonly"].ToString()) + "'," + "'" + dataReader["fees_paid"] + "','" + dataReader["tracking"] + "','" + dataReader["transactionid"] + "','" + "" + Get_json_label(CheckBool(dataReader["label_made"].ToString()).ToString(), dataReader["label_cost"].ToString(), dataReader["label_carrier"].ToString()) + "')"); } } pPostgres.Message = "tbl_store_orders - extraction - FINISH"; }
public string GetStudioId(string pStudioId, string pStudio_name, PostgreSQL_DB pPostgres) { if (!String.IsNullOrEmpty(pStudioId)) { return(pStudioId); } if (String.IsNullOrEmpty(pStudio_name)) { return("null"); } else { string studioId = GetId("select id from tbl_studios where name like '" + pStudio_name.Replace("'", "''") + "'", pPostgres); if (studioId != "dummy") { return(studioId); } else { return(AddNewStudio(pStudio_name, pPostgres)); } } }
private string GetAddressId(string pAddress, string pCity, string pState, string pZip, string pCountryId, PostgreSQL_DB pPostgres) { NpgsqlDataReader query; string p_city_id = GetId("select id from tbl_cities where name like '" + pCity.Replace("'", "''") + "'", pPostgres); query = pPostgres.Select("select distinct id " + "from tbl_addresses where address like '" + pAddress.Replace("'", "''") + "' and city_id = " + p_city_id + " and zip like '" + pZip + "' and country_id=" + NVL(pCountryId) + ";"); string pom; while (query.Read()) { pom = query[0].ToString(); query.Dispose(); return("'" + pom + "'"); } query.Dispose(); if (pAddress != "") { string city_id = GetId("select id from tbl_cities where name like '" + pCity.Replace("'", "''") + "'", pPostgres); string pomStateId = GetId("select id from tbl_states where name like '" + pState + "'", pPostgres); pPostgres.Insert("insert into tbl_addresses(state_id, address, city_id, zip) values(" + pomStateId + ",'" + pAddress.Replace("'", "''") + "'," + city_id + ",'" + pZip + "');"); string p_address_id = GetId("select max(id) from tbl_addresses", pPostgres); return(p_address_id); } else { return("null"); } }
private void ManageTeacher(string pTeacher, string proutine_id, PostgreSQL_DB pPostgres) { string s = pTeacher; string[] words = s.Split('/'); foreach (string word in words) { string name = ""; if (word.Contains(" ")) { name = word.Substring(0, Strings.InStr(1, word, " ") - 1); } string surname = word.Substring(Strings.InStr(1, word, " ")); string personid = GetId("select id from tbl_person where fname like '" + name.Replace("'", "''") + "' and lname like '" + surname.Replace("'", "''") + "' LIMIT 1;", pPostgres); if (personid == "null") { pPostgres.Insert("insert into tbl_person(fname, lname) values('" + name.Replace("'", "''") + "','" + surname.Replace("'", "''") + "')"); personid = GetId("select max(id) from tbl_person;", pPostgres); } if (!RowExists(personid, proutine_id, pPostgres)) { pPostgres.Insert("insert into tbl_routines_has_teacher(person_id, routine_id) values('" + personid + "','" + proutine_id + "')"); } } }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_date_scholarships"); pMysql.Message = "Tbl_date_scholarships - extraction - START "; while (dataReader.Read()) { // ------------------- if (!String.IsNullOrEmpty(dataReader["facultyid"].ToString())) { CreateDummyFaculty(NVL(dataReader["facultyid"].ToString()), pPostgres); } // ------------------- if (!String.IsNullOrEmpty(dataReader["datedancerid"].ToString())) { CreateDummyTblDateDancers(NVL(dataReader["datedancerid"].ToString()), pPostgres); } pPostgres.Insert("insert into tbl_date_scholarships(id, tour_dates_id, scholarships_id, winner, code, faculty_id, dancer_id, date_dancer_id) " + "values(" + dataReader["id"] + "," + dataReader["tourdateid"] + "," + dataReader["scholarshipid"] + "," + "" + CheckBool(dataReader["winner"].ToString()) + "," + NVL(dataReader["code"].ToString()) + "," + NVL(dataReader["facultyid"].ToString()) + "," + "" + NVL(dataReader["profileid"].ToString()) + "," + NVL(dataReader["datedancerid"].ToString()) + ")"); } pPostgres.Message = "Tbl_date_scholarships - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { pMysql.Message = "studios_has_person - extraction - START"; pPostgres.Insert("insert into studios_has_person(studios_id, person_id) select c.studios_id, a.id from tbl_person a join tbl_user b on (a.id=b.person_id) " + "join tbl_studio_contacts c on(b.email = c.value) where a.person_types_id in('1','2','3','4','5','6') and c.contact_type_id = 1;"); pPostgres.Message = "studios_has_person - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_tda_bestdancer_data;"); pMysql.Message = "tbl_tda_bestdancer_data - extraction - START"; while (dataReader.Read()) { // ------------------- if (!String.IsNullOrEmpty(dataReader["routineid"].ToString())) { CreateDummyRoutines(NVL(dataReader["routineid"].ToString()), pPostgres); } string pChoreographer = "0"; if (!String.IsNullOrEmpty(dataReader["choreographer"].ToString())) { pChoreographer = AddNewPerson(dataReader["choreographer"].ToString(), pPostgres); } pPostgres.Insert("insert into tbl_tda_bestdancer_data(id, tour_dates_id, dancer_id, routines_id, person_id, studios_id, iscompeting, " + "jacketname, jacketsize, hasphoto, ballet, danceoff, groupid, perc, place, jazz) " + "values(" + dataReader["id"] + "," + NVL(dataReader["tourdateid"].ToString()) + "," + NVL(dataReader["profileid"].ToString()) + "," + "" + NVL(dataReader["routineid"].ToString()) + "," + pChoreographer + "," + NVL(dataReader["studioid"].ToString()) + "," + "" + dataReader["iscompeting"] + ",'" + dataReader["jacketname"].ToString().Replace("'", "''") + "','" + dataReader["jacketsize"].ToString().Replace("'", "''") + "'," + "" + CheckBool(dataReader["hasphoto"].ToString()) + "," + NVL(dataReader["ballet"].ToString()) + "," + "'" + Get_json_danceoff(dataReader["danceoff"].ToString(), dataReader["danceoff_max"].ToString()) + "'," + "" + NVL(dataReader["groupid"].ToString()) + "," + "'" + Get_json_perc(dataReader["perc_solo"].ToString(), dataReader["perc_ballet"].ToString(), dataReader["perc_danceoff"].ToString(), dataReader["perc_round2"].ToString(), dataReader["perc_total"].ToString()) + "'," + "'" + Get_json_place(dataReader["round1_place"].ToString(), dataReader["round2_place"].ToString(), dataReader["round3_place"].ToString()) + "'," + NVL(dataReader["jazz"].ToString()) + ");"); } pPostgres.Message = "tbl_tda_bestdancer_data - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_date_routines;"); pMysql.Message = "tbl_date_routines - extraction - START"; while (dataReader.Read()) { string award_typename = string.IsNullOrWhiteSpace(dataReader["award_typename"].ToString()) ? "null" : "'" + dataReader["award_typename"].ToString().Replace("'", "''") + "'"; string fee = string.IsNullOrWhiteSpace(dataReader["fee"].ToString()) ? "null" : dataReader["fee"].ToString(); pPostgres.Insert("insert into tbl_date_routines(id, tour_dates_id, routine_id, studios_id, age_divisions_id, category_id, " + "performance_divisions_id, perf_div_type_id, routine_types_id, fee, canceled, custom_dancer_count, duration, " + "custom_fee, place_hsa, place_hsp, award_typename, uploaded_duration, extra_time, uploaded, prelims, vips, finals) " + "values(" + dataReader["id"] + "," + dataReader["tourdateid"] + "," + dataReader["routineid"] + "," + dataReader["studioid"] + "," + dataReader["agedivisionid"] + "," + "" + dataReader["routinecategoryid"] + "," + dataReader["perfcategoryid"] + " , " + CheckBool(dataReader["perfdivtype"].ToString()) + "," + dataReader["routinetypeid"] + "," + fee + "," + CheckBool(dataReader["canceled"].ToString()) + "," + "" + dataReader["custom_dancer_count"] + "," + dataReader["duration"] + "," + CheckBool(dataReader["custom_fee"].ToString()) + "," + dataReader["place_hsa"] + "," + dataReader["place_hsp"] + "," + "" + award_typename + ",'" + dataReader["uploaded_duration"] + "'," + dataReader["extra_time"] + "," + CheckBool(dataReader["uploaded"].ToString()) + "" + "," + Get_json_Prelims(dataReader["prelims"].ToString(), dataReader["prelims_score1"].ToString(), dataReader["prelims_score2"].ToString(), dataReader["prelims_score3"].ToString(), dataReader["prelims_score4"].ToString(), dataReader["prelims_score5"].ToString(), dataReader["prelims_score6"].ToString(), dataReader["prelims_awardid"].ToString(), dataReader["prelims_total_score"].ToString(), dataReader["prelims_dropped_score"].ToString(), dataReader["prelims_time"].ToString(), dataReader["number_prelims"].ToString(), dataReader["prelims_has_a"].ToString(), dataReader["prelims_dropped_score2"].ToString(), dataReader["room_prelims"].ToString()) + "" + "," + Get_json_VIP(dataReader["vips"].ToString(), dataReader["vips_score1"].ToString(), dataReader["vips_score2"].ToString(), dataReader["vips_score3"].ToString(), dataReader["vips_score4"].ToString(), dataReader["vips_score5"].ToString(), dataReader["vips_score6"].ToString(), dataReader["vips_awardid"].ToString(), dataReader["vips_dropped_score"].ToString(), dataReader["vips_total_score"].ToString(), dataReader["vips_time"].ToString(), dataReader["vips_dropped_score2"].ToString(), dataReader["number_vips"].ToString(), dataReader["vips_has_a"].ToString(), dataReader["room_vips"].ToString()) + "" + "," + Get_json_final(dataReader["finals"].ToString(), dataReader["finals_score1"].ToString(), dataReader["finals_score2"].ToString(), dataReader["finals_score3"].ToString(), dataReader["finals_score4"].ToString(), dataReader["finals_score5"].ToString(), dataReader["finals_score6"].ToString(), dataReader["finals_awardid"].ToString(), dataReader["finals_total_score"].ToString(), dataReader["finals_dropped_score"].ToString(), dataReader["finals_time"].ToString(), dataReader["number_finals"].ToString(), dataReader["finals_dropped_score2"].ToString(), dataReader["finals_has_a"].ToString(), dataReader["room_finals"].ToString()) + ")"); } pPostgres.Message = "tbl_date_routines - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_user_registrations_dancers;"); pMysql.Message = "Tbl_registrations_dancers - extraction - START "; while (dataReader.Read()) { string RegId = GetId("select id from tbl_registration where old_user_reg_id='" + dataReader["regid"] + "' limit 1;", pPostgres); pPostgres.Insert( "insert into tbl_registrations_dancers(id, registration_id, dancer_id, one_day, has_scholarship, attended_reg, is_commuter, " + "non_commuter, classes_only, workshop_level_id, fees, promo_codes_id, scholarship, best_dancer, event_teacher, events_id, reg_type_id) " + "values(" + dataReader["id"] + "," + RegId + "," + dataReader["profileid"] + "," + CheckBool(dataReader["oneday"].ToString()) + "," + CheckBool(dataReader["hasscholarship"].ToString()) + ", " + "" + CheckBool(dataReader["attendedreg"].ToString()) + ", " + CheckBool(dataReader["iscommuter"].ToString()) + "," + NVL(dataReader["noncommuterid"].ToString()) + ", " + CheckBool(dataReader["classesonly"].ToString()) + ", " + "" + dataReader["workshoplevelid"] + ",'" + Get_json_fees(dataReader["workshopfee"].ToString(), dataReader["attendeefee"].ToString()) + "'," + "" + NVL(dataReader["promocodeid"].ToString()) + ",'" + Get_json_scholarship(dataReader["scholarshipamt"].ToString(), dataReader["scholarshipfrom"].ToString()) + "'," + "" + CheckBool(dataReader["bestdancer"].ToString()) + ", " + CheckBool(dataReader["event_teacher"].ToString()) + "," + NVL(dataReader["intensiveid"].ToString()) + "," + NVL(dataReader["regtypeid"].ToString()) + ");"); } pPostgres.Message = "Tbl_registrations_dancers - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select id, cast(name as char) name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 11 season_id " + "from tbl_workshop_levels_11 " + "union " + "select id, name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 14 season_id " + "from tbl_workshop_levels_14 " + "union " + "select id, name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 17 season_id " + "from tbl_workshop_levels_17 " + "union " + "select id, cast(name as char) name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 2 season_id " + "from tbl_workshop_levels_2 " + "union " + "select id, name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 20 season_id " + "from tbl_workshop_levels_20 " + "union " + "select id, name, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, 22 season_id " + "from tbl_workshop_levels_22;"); pMysql.Message = "tbl_workshop_levels - extraction - START"; int pcounter = 0; while (dataReader.Read()) { string p_playlist_workshop_levels_id = GetId("select id from tbl_playlist_workshop_levels where name like '" + dataReader["name"] + "' limit 1", pPostgres); pPostgres.Insert("insert into tbl_workshop_levels(id, playlist_workshop_levels_id, discount_fee, full_fee, finale_discount_fee, finale_full_fee, one_day_full_fee, one_day_discount_fee, season_id) " + "values(" + ++pcounter + "," + p_playlist_workshop_levels_id + ",'" + dataReader["discount_fee"] + "','" + dataReader["full_fee"] + "','" + dataReader["finale_discount_fee"] + "','" + dataReader["finale_full_fee"] + "','" + dataReader["one_day_full_fee"] + "','" + dataReader["one_day_discount_fee"] + "'," + dataReader["season_id"] + ")"); } pPostgres.Message = "tbl_workshop_levels - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_user_registrations_routines;"); pMysql.Message = "tbl_registrations_routines - extraction - START "; while (dataReader.Read()) { string RegId = GetId("select id from tbl_registration where old_user_reg_id='" + dataReader["regid"] + "' limit 1;", pPostgres); string pFee = dataReader["fee"].ToString(); if (pFee.Length > 15) { pFee = ReturnNumber(pFee); } pPostgres.Insert("insert into tbl_registrations_routines(id, registration_id, performance_division_id, routine, routine_category_id, age_division_id, teacher, " + "type, time, fee, award_type) " + "values(" + dataReader["id"] + ", " + RegId + ", " + dataReader["perfdivisionid"] + ", '" + dataReader["routinename"].ToString().Replace("'", "''") + "', " + "" + dataReader["routinecategoryid"] + ", " + dataReader["agedivisionid"] + ",'" + dataReader["teacher"].ToString().Replace("'", "''") + "'," + "'" + Get_json_type(dataReader["is_finals"].ToString(), dataReader["is_prelims"].ToString(), dataReader["is_vips"].ToString(), dataReader["is_free_ballet"].ToString()) + "'," + "'" + Get_json_time(dataReader["extended_time"].ToString(), dataReader["extra_time"].ToString()) + "', " + NVL(pFee) + ",'" + dataReader["routine_awardtype"].ToString().Replace("'", "''") + "');"); } pPostgres.Insert("insert into tbl_registrations_routines(id, routine) values(118,'DUMMY');"); pPostgres.Insert("insert into tbl_registrations_routines(id, routine) values(83269,'DUMMY');"); pPostgres.Insert("insert into tbl_registrations_routines(id, routine) values(43491,'DUMMY');"); pPostgres.Insert("insert into tbl_registrations_routines(id, routine) values(102887,'DUMMY');"); pPostgres.Message = "tbl_registrations_routines - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_dts_registrations"); pMysql.Message = "tbl_dts_registrations - extraction - START "; while (dataReader.Read()) { string PersonId = GetPersonId(dataReader["address"].ToString().Replace("'", "''"), dataReader["city"].ToString().Replace("'", "''"), dataReader["state"].ToString().Replace("'", "''"), dataReader["zip"].ToString(), dataReader["countryid"].ToString(), dataReader["fname"].ToString().Replace("'", "''"), dataReader["lname"].ToString().Replace("'", "''"), dataReader["email"].ToString(), dataReader["contact_type"].ToString(), dataReader["phone"].ToString(), dataReader["phone2"].ToString(), dataReader["fax"].ToString(), pPostgres); string studioid = GetStudioId(dataReader["organization"].ToString(), pPostgres); pPostgres.Insert( "insert into tbl_dts_registrations(id,tour_dates_id,studios_id,person_id,registration_id,user_id,admin_id," + "heard,notes,total_fees,fees_paid,balance_due,boxsets, extra, edu) " + "values(" + dataReader["id"] + ", " + NVL(dataReader["tourdateid"].ToString()) + ", " + studioid + ", " + PersonId + ", " + "" + NVL(dataReader["onlineregid"].ToString()) + ", " + NVL(dataReader["onlineuserid"].ToString()) + ", " + NVL(dataReader["enteredbyid"].ToString()) + ", '" + dataReader["heard"].ToString().Replace("'", "''") + "', " + "'" + dataReader["notes"].ToString().Replace("'", "''") + "', " + NVL(dataReader["totalfees"].ToString()) + ", " + NVL(dataReader["feespaid"].ToString()) + ", " + NVL(dataReader["balancedue"].ToString()) + ", " + "'" + Get_json_boxsets(dataReader["boxsets_2009"].ToString(), dataReader["boxsets_2010"].ToString(), dataReader["boxsets_0910_combo"].ToString()) + "'," + "'" + Get_json_extra(dataReader["extra_dvdpre"].ToString(), dataReader["extra_gala"].ToString(), dataReader["extra_ff"].ToString(), dataReader["extra_ace"].ToString()) + "', " + "'" + Get_json_edu(dataReader["edu_studioco"].ToString(), dataReader["edu_nostudioco"].ToString(), dataReader["edu_college"].ToString(), dataReader["edu_k12"].ToString(), dataReader["edu_danceteam"].ToString(), dataReader["edu_other"].ToString(), dataReader["edu_other_val"].ToString()) + "')") ; } pPostgres.Message = "tbl_dts_registrations - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { pMysql.Message = "DUMMY data2 GENERATE - START"; pMysql.Message = "DUMMY data2 GENERATE - FINISH"; }
public void CreateDummyRegistration(string pRegistration, PostgreSQL_DB pPostgres) { string check = GetId("select id from tbl_registration where id = " + pRegistration + ";", pPostgres); if (check == "null") { pPostgres.Insert("insert into tbl_registration(id) values(" + pRegistration + ");"); } }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { pPostgres.Message = "tbl_time_type - extraction - FINISH"; pPostgres.Insert("insert into tbl_time_type(id, name) values('1','extra');"); pPostgres.Insert("insert into tbl_time_type(id, name) values('2','prelims');"); pPostgres.Insert("insert into tbl_time_type(id, name) values('3','vips');"); pPostgres.Insert("insert into tbl_time_type(id, name) values('4','finals');"); pPostgres.Message = "tbl_time_type - extraction - FINISH"; }
public void CreateDummyWaiver(string pWaiverId, PostgreSQL_DB pPostgres) { string check = GetId("select id from tbl_waivers where id = " + pWaiverId + ";", pPostgres); if (check == "null") { pPostgres.Insert("insert into tbl_waivers(id, dancer_id) values(" + pWaiverId + ", 20797);"); } }
public void CreateDummyDancer(string pDancerId, PostgreSQL_DB pPostgres) { string check = GetId("select id from tbl_dancer where id = " + pDancerId + ";", pPostgres); if (check == "null") { pPostgres.Insert("insert into tbl_dancer(id, person_id) values(" + pDancerId + ",0);"); } }
public void CreateDummyStudio(string pStudio, PostgreSQL_DB pPostgres) { string check = GetId("select id from tbl_studios where id = " + pStudio + ";", pPostgres); if (check == "null") { pPostgres.Insert("insert into tbl_studios(id, name) values(" + pStudio + ",'DUMMY DANCE STUDIO');"); } }
public void CreateDummyWorkshopLevel(string pWorkshopLevel, PostgreSQL_DB pPostgres) { string check = GetId("select id from tbl_workshop_levels where id = " + pWorkshopLevel + ";", pPostgres); if (check == "null") { pPostgres.Insert("insert into tbl_workshop_levels(id, playlist_workshop_levels_id, season_id) values(" + pWorkshopLevel + ",0,0);"); } }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { // all dancers related to the any studio... MySqlDataReader dataReader = pMysql.Select("select * from tbl_profiles where studioid is null;"); pMysql.Message = "tbl_dancers II. - extraction - START"; while (dataReader.Read()) { // insert new address of dancer (person) // private string GetAddressId(string pAddress, string pCity, string pState, string pZip, string pCountryId, PostgreSQL_DB pPostgres) string AddressId = GetAddressId(dataReader["address"].ToString(), dataReader["city"].ToString(), dataReader["state"].ToString(), dataReader["zip"].ToString(), dataReader["countryid"].ToString(), pPostgres); string GenderId = GetId("select id from tbl_gender where value like '" + dataReader["gender"] + "'", pPostgres); // inser dancer into person string birthdate = (dataReader["birth_date"].Equals("")) ? "null" : "'" + dataReader["birth_date"] + "'"; pPostgres.Insert("insert into tbl_person(address_id, gender_id, fname, lname, birthdate, person_types_id) " + "values(" + AddressId + ", " + GenderId + ",'" + dataReader["fname"].ToString().Replace("'", "''") + "'," + "'" + dataReader["lname"].ToString().Replace("'", "''") + "'," + birthdate + ",'8')"); string Max_person_id = GetId("select max(id) from tbl_person", pPostgres); // insert into tbl_dancer string parent_guardian = (dataReader["parent_guardian"].ToString() == "") ? "null" : dataReader["parent_guardian"].ToString(); string email_parents = (dataReader["email_parents"].ToString() == "") ? "null" : dataReader["email_parents"].ToString(); if (email_parents.Equals("''")) { email_parents = "null"; } pPostgres.Insert("insert into tbl_dancer(id, person_id, parent_guardian, email_parents) " + "values('" + dataReader["id"] + "'," + Max_person_id + ",'" + parent_guardian.Replace("'", "''") + "','" + email_parents.Replace("'", "''") + "')"); // insert into studio_has_contact_type if (dataReader["phone"].ToString() != "") { pPostgres.Insert("insert into person_has_contact_type(person_id, contact_type_id, value)" + "values(" + Max_person_id + ",2,'" + dataReader["phone"] + "')"); } if (dataReader["phone2"].ToString() != "") { pPostgres.Insert("insert into person_has_contact_type(person_id, contact_type_id, value)" + "values(" + Max_person_id + ",2,'" + dataReader["phone2"] + "')"); } if (dataReader["fax"].ToString() != "") { pPostgres.Insert("insert into person_has_contact_type(person_id, contact_type_id, value)" + "values(" + Max_person_id + ",8,'" + dataReader["fax"] + "')"); } if (dataReader["email"].ToString() != "") { pPostgres.Insert("insert into person_has_contact_type(person_id, contact_type_id, value)" + "values(" + Max_person_id + ",1,'" + dataReader["email"].ToString().Replace("'", "''") + "')"); } } pPostgres.Message = "tbl_dancers II. - extraction - FINISH"; }
private bool RowExists(string p_person_id, string p_routine_id, PostgreSQL_DB pPostgres) { string pom = GetId("select person_id from tbl_routines_has_teacher where person_id = '" + p_person_id + "' and routine_id = '" + p_routine_id + "'", pPostgres); if (pom == "null") { return(false); } return(true); }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { pPostgres.Message = "tbl_perf_div_types - creation - START"; pPostgres.Insert("insert into tbl_perf_div_types(id, name) values('1','Combined Top 3');"); pPostgres.Insert("insert into tbl_perf_div_types(id, name) values('2','Combined Top 1');"); pPostgres.Insert("insert into tbl_perf_div_types(id, name) values('3','Individual Top 3');"); pPostgres.Insert("insert into tbl_perf_div_types(id, name) values('4','Individual Top 1');"); pPostgres.Insert("insert into tbl_perf_div_types(id, name) values('0','Nothing');"); pPostgres.Message = "tbl_perf_div_types - creation - FINISH"; }
public string AddNewPersonTeacher(string pName, PostgreSQL_DB pPostgres) { string PersonType = GetId("select id from tbl_person_types where name like 'Teacher' limit 1;", pPostgres); pPostgres.Insert("insert into tbl_person(fname, person_types_id) " + "values('" + pName.ToString().Replace("'", "''") + "'," + PersonType + ")"); string Max_person_id = GetId("select max(id) from tbl_person", pPostgres); return(Max_person_id); }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select distinct userid, profileid from tbl_saved_dancers;"); pMysql.Message = "tbl_user_has_dancer - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_user_has_dancer(dancer_id, user_id) values('" + dataReader["profileid"] + "','" + dataReader["userid"] + "')"); } pPostgres.Message = "tbl_user_has_dancer - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select distinct id, name from tbl_event_types;"); pMysql.Message = "tbl_event_types - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_event_types(id, name) values('" + dataReader[0] + "','" + dataReader[1] + "')"); } pPostgres.Message = "tbl_event_types - extraction - FINISH"; }