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_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") { 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") { // 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 * 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 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 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 * 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 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 * 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 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 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") { // 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"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select routineid, profileid, tourdateid from tbl_date_routine_dancers group by routineid, profileid, tourdateid;"); pMysql.Message = "tbl_date_routine_dancers - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_date_routine_dancers(tour_dates_id, routine_id, dancer_id) values(" + dataReader["tourdateid"] + "," + dataReader["routineid"] + "," + dataReader["profileid"] + ");"); } pPostgres.Message = "tbl_date_routine_dancers - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from store_product_sizes;"); pMysql.Message = "tbl_store_products_has_size - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_store_products_has_size(store_products_id,store_sizes_id) values('" + dataReader["productid"] + "','" + dataReader["sizeid"] + "')"); } pPostgres.Message = "tbl_store_products_has_size - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_faculty;"); pMysql.Message = "tbl_faculty - extraction - START"; int MaxDancerId = Convert.ToInt32(GetId("select max(id) from tbl_dancer;", pPostgres)); while (dataReader.Read()) { string event_id = GetId("select id from tbl_events where lower(name) like lower('" + dataReader["tour"] + "')", pPostgres); string person_id = GetId("select id from tbl_person where lower(fname) like lower('" + dataReader["fname"].ToString().Replace("'", "''") + "') " + "and lower(lname) like lower('" + dataReader["lname"].ToString().Replace("'", "''") + "')", pPostgres); string style_1 = GetId("select id from tbl_performance_divisions where name like '" + dataReader["style1"] + "'", pPostgres); string style_2 = GetId("select id from tbl_performance_divisions where name like '" + dataReader["style2"] + "'", pPostgres); string dancer_id = "null"; if (person_id != "null") { dancer_id = GetId("select id from tbl_dancer where person_id ='" + person_id + "'", pPostgres); if (dancer_id == "null") { pPostgres.Insert("insert into tbl_dancer(id, person_id) values(" + ++MaxDancerId + ", " + person_id + ")"); dancer_id = MaxDancerId.ToString(); } } if (dancer_id == "null") { pPostgres.Insert("insert into tbl_person(fname, lname) values('" + dataReader["fname"].ToString().Replace("'", "''") + "','" + dataReader["lname"].ToString().Replace("'", "''") + "')"); person_id = GetId("select max(id) from tbl_person;", pPostgres); pPostgres.Insert("insert into tbl_dancer(id, person_id) values(" + ++MaxDancerId + ", " + person_id + ")"); dancer_id = MaxDancerId.ToString(); } pPostgres.Insert("insert into tbl_faculty(id, events_id, dancer_id, bio, website, director, twitter, instagram, youtube) " + "values(" + dataReader["id"] + "," + event_id + "," + dancer_id + ",'" + dataReader["bio"].ToString().Replace("'", "''") + "','" + dataReader["website"].ToString().Replace("'", "''") + "'" + ",'" + dataReader["director"].ToString().Replace("'", "''") + "','" + dataReader["twitter"].ToString().Replace("'", "''") + "','" + dataReader["instagram"].ToString().Replace("'", "''") + "'," + "'" + dataReader["youtube"].ToString().Replace("'", "''") + "');"); if (style_1 != "null") { pPostgres.Insert("insert into faculty_has_performance(faculty_id, performance_divisions_id) " + "values(" + dataReader["id"] + "," + style_1 + ");"); } if (style_2 != "null") { pPostgres.Insert("insert into faculty_has_performance(faculty_id, performance_divisions_id) " + "values(" + dataReader["id"] + "," + style_2 + ");"); } } AddDummyFaculty(pPostgres); pPostgres.Message = "tbl_faculty - extraction - FINISH"; }
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 id, name from store_colors;"); pMysql.Message = "store_colors - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_store_colors(id, name) values(" + (int)dataReader[0] + ",'" + dataReader[1] + "')"); } pPostgres.Message = "store_colors - 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"; }
private void DummyStudioCreation(MySQL_DB pMysql, PostgreSQL_DB pPostgres) { MySqlDataReader dataReader = pMysql.Select("select distinct studioid from tbl_profiles where studioid not in (select id from tbl_studios);"); pMysql.Message = "DUMMY tbl_studios - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_studios(id, name) values('" + dataReader[0] + "','DUMMY DANCE STUDIO')"); } pPostgres.Message = "DUMMY tbl_studios - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_staff;"); pMysql.Message = "tbl_staff - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_staff(id, fname, lname, staff_types_id) " + "values('" + dataReader["id"] + "','" + dataReader["fname"].ToString().Replace("'", "''") + "','" + dataReader["lname"].ToString().Replace("'", "''") + "','" + dataReader["stafftypeid"] + "')"); } pPostgres.Message = "tbl_staff - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select id, name from tbl_performance_divisions;"); pMysql.Message = "tbl_performance_divisions - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_performance_divisions(id, name) values(" + dataReader[0] + ",'" + dataReader[1] + "')"); } pPostgres.Insert("insert into tbl_performance_divisions(id, name) values(0,'Nothing');"); pPostgres.Message = "tbl_performance_divisions - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_dts_reg_types;"); pMysql.Message = "tbl_dts_reg_types - extraction - START "; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_dts_reg_types(id, name, fee) " + "values('" + dataReader["id"] + "','" + dataReader["name"] + "','" + dataReader["fee"] + "');"); } pPostgres.Message = "tbl_dts_reg_types - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from store_giftcards;"); pMysql.Message = "tbl_store_giftcards - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_store_giftcards(id, code, initial_balance, balance, created) " + "values('" + dataReader["id"] + "','" + dataReader["code"] + "','" + dataReader["initial_balance"] + "','" + dataReader["balance"] + "','" + FromUnixTime(Convert.ToInt64(dataReader["created"])).ToString().Replace(". ", ".") + "')"); } pPostgres.Message = "tbl_store_giftcards - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_user_hearts;"); pMysql.Message = "tbl_user_hearts - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_user_hearts(id, user_id, store_hearts_id) " + "values('" + dataReader["id"] + "','" + dataReader["heartid"] + "','" + dataReader["userid"] + "')"); } pPostgres.Message = "tbl_user_hearts - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_date_mybtf_exceptions;"); pMysql.Message = "tbl_date_mybtf_exceptions - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_date_mybtf_exceptions(id, tour_dates_id, email, level) " + "values('" + dataReader["id"] + "','" + dataReader["tourdateid"] + "','" + dataReader["email"] + "','" + dataReader["level"] + "')"); } pPostgres.Message = "tbl_date_mybtf_exceptions - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select id, name, abbr from tbl_countries;"); pMysql.Message = "tbl_countries (from tbl_countries)- extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_countries(id, name, abbr) values('" + dataReader[0] + "','" + dataReader[1].ToString().Replace("'", "''") + "','" + dataReader[2] + "')"); // PROBLEM WITH NULL???? } pPostgres.Message = "tbl_countries - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from store_product_subtypes;"); pMysql.Message = "tbl_store_product_subtypes - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_store_product_subtypes(id, name, product_types_id) " + "values(" + dataReader["id"] + ",'" + dataReader["name"] + "'," + dataReader["typeid"] + ");"); } pPostgres.Message = "tbl_store_product_subtypes - extraction - FINISH"; }
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500") { MySqlDataReader dataReader = pMysql.Select("select * from tbl_competition_cash_awards;"); pMysql.Message = "tbl_competition_cash_awards - extraction - START"; while (dataReader.Read()) { pPostgres.Insert("insert into tbl_competition_cash_awards(id, tour_dates_id, highscoretype, place, amount, description) " + "values('" + dataReader["id"] + "','" + dataReader["tourdateid"] + "','" + dataReader["highscoretype"] + "','" + dataReader["place"] + "','" + dataReader["amount"] + "','" + dataReader["description"] + "')"); } pPostgres.Message = "tbl_competition_cash_awards - extraction - FINISH"; }