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";
}
