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