public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500")
{
MySqlDataReader dataReader = pMysql.Select("select id, name, link, type, web_home_webcast_banner, currentseason, facebook_link, ageasofyear, " +
"intopmenu, workshoponly from events; ");
pMysql.Message = "tbl_events - extraction(from table events) - START";
string pom1 = "null";
string pom2 = "null";
while (dataReader.Read())
{
pom1 = (dataReader[2].ToString() == "") ? "null" : "'" + dataReader[2].ToString() + "'";
pom2 = (dataReader[6].ToString() == "") ? "null" : "'" + dataReader[6].ToString() + "'";
pPostgres.Insert("insert into tbl_events(id, name, link, event_types_id, web_home_webcast_banner, facebook_link, " +
"ageasofyear,intopmenu, currentseason, " +
"workshoponly) values('" + dataReader[0] + "','" + dataReader[1].ToString().Replace("'", "''") + "'," + pom1 + ",'" + dataReader[3] + "','" + CheckBool(dataReader[4].ToString()) + "'," + pom2 + "" +
",'" + dataReader[7] + "','" + CheckBool(dataReader[8].ToString()) + "','" + dataReader[5] + "','" + CheckBool(dataReader[9].ToString()) + "');");
//pPostgres.Insert("insert into tbl_current_season(season_id, events_id, is_current_season) values('"+ dataReader[5] + "','"+ dataReader[0] + "',True);");
}
// this is DUMMY evenet with ID 6 - this event was missing in original DB
pPostgres.Insert("insert into tbl_events(id, event_types_id, name) values(6,2, 'DUMMY EVENT');");
pPostgres.Insert("insert into tbl_events(id, event_types_id, name) values(0,2,'DUMMY EVENT');");
pPostgres.Message = "tbl_events - extraction - FINISH";
}
public void Remigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500")
{
MySqlDataReader dataReader = pMysql.Select("select id, name, link, type, web_home_webcast_banner, currentseason, facebook_link, ageasofyear, " +
"intopmenu, workshoponly, update_date, insert_date from events; ");
pMysql.Message = "tbl_events - extraction(from table events) - START";
string pom1 = "null";
string pom2 = "null";
while (dataReader.Read())
{
// update part start HERE
if (!String.IsNullOrEmpty(dataReader["update_date"].ToString()) && Convert.ToDateTime(dataReader["update_date"].ToString()) >= Convert.ToDateTime(pDate))
{
List <string> MySQLData = new List <string> {
"null", dataReader["name"].ToString(), dataReader["link"].ToString(), dataReader["web_home_webcast_banner"].ToString(), dataReader["facebook_link"].ToString(),
dataReader["ageasofyear"].ToString(), dataReader["intopmenu"].ToString(), dataReader["currentseason"].ToString(), dataReader["workshoponly"].ToString()
};
List <string> PostgreSQLData = new List <string> {
"tbl_events", "name", "link", "web_home_webcast_banner", "facebook_link", "ageasofyear", "intopmenu", "currentseason", "workshoponly"
};
UpdatePostgreRow(dataReader["id"].ToString(), MySQLData, PostgreSQLData, pPostgres);
}
// update part END
}
pPostgres.Message = "tbl_events - extraction - FINISH";
}
public string AddNewStudio(string pStudioName, PostgreSQL_DB pPostgres)
{
int MaxStudioId = Convert.ToInt32(GetId("select max(id) from tbl_studios;", pPostgres));
pPostgres.Insert("insert into tbl_studios(id, name) values('" + ++MaxStudioId + "','" + pStudioName + "')");
return(MaxStudioId.ToString());
}
public void CreateDummyRoutines(string pRoutineId, PostgreSQL_DB pPostgres)
{
string check = GetId("select id from tbl_routines where id = " + pRoutineId + ";", pPostgres);
if (check == "null")
{
pPostgres.Insert("insert into tbl_routines(id, studios_id, name) values(" + pRoutineId + ", 0, 'DUMMY');");
}
}
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 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 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 CreateDummyTblDateDancers(string pTblDateDancersId, PostgreSQL_DB pPostgres)
{
string check = GetId("select id from tbl_date_dancers where id = " + pTblDateDancersId + ";", pPostgres);
if (check == "null")
{
pPostgres.Insert("insert into tbl_date_dancers(id) values(" + pTblDateDancersId + ");");
}
}
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 CreateDummyFaculty(string pFacultyId, PostgreSQL_DB pPostgres)
{
string check = GetId("select id from tbl_faculty where id = " + pFacultyId + ";", pPostgres);
if (check == "null")
{
pPostgres.Insert("insert into tbl_faculty(id, events_id, dancer_id, bio, website) values(" + pFacultyId + ",6,20797, 'DUMMY', 'DUMMY');");
}
}
public void CreateDummyPromoCode(string pPromoCode, PostgreSQL_DB pPostgres)
{
string check = GetId("select id from tbl_promo_codes where id = " + pPromoCode + ";", pPostgres);
if (check == "null")
{
pPostgres.Insert("insert into tbl_promo_codes(id, promo_codes_type_id, name,uses) values(" + pPromoCode + ",1, 'DUMMY',1);");
}
}
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 bool Connect()
{
Pgdbbreakthefloor = new PostgreSQL_DB(_form.textPostgreDbName.Text, _form.textBoxPostgreServerName.Text, _form.textBoxPostgreUID.Text,
_form.textBoxPostgresPortName.Text, _form.textBoxPostgresPassword.Text, _form);
DanceteaManager = new MySQL_DB(_form.textTeaDbName.Text, _form.textTeaServerName.Text, _form.textTeaUID.Text, _form.textTeaPassword.Text, _form);
Mybreak_db = new MySQL_DB(_form.textMyBreakDbName.Text, _form.textMyBreakServerName.Text, _form.textMyBreakUID.Text, _form.textMyBreakPassword.Text, _form);
if (DanceteaManager.OpenConnection() && Mybreak_db.OpenConnection() && Pgdbbreakthefloor.OpenConnection())
{
return(true);
}
return(false);
}
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500")
{
MySqlDataReader dataReader = pMysql.Select("select * from store_promocodes;");
pMysql.Message = "tbl_store_promocodes - extraction - START ";
while (dataReader.Read())
{
string TypeId = GetId("select id, name from tbl_promo_codes_type where name like '" + dataReader["type"] + "';", pPostgres);
pPostgres.Insert("insert into tbl_store_promocodes(id, name, description, value, charges, uses, active, expires, promo_codes_type_id) " +
"values(" + dataReader["id"] + ",'" + dataReader["name"] + "','" + dataReader["description"] + "'" +
"," + dataReader["value"] + "," + dataReader["charges"] + "," + dataReader["uses"] + "" +
"," + dataReader["active"] + ",'" + FromUnixTime(Convert.ToInt64(dataReader["expires"])) + "'," + TypeId + ");");
}
pPostgres.Message = "tbl_store_promocodes - extraction - FINISH";
}
public string GetId(string pParam, PostgreSQL_DB pPostgres)
{
NpgsqlDataReader query;
query = pPostgres.Select(pParam);
string pom;
while (query.Read())
{
pom = query[0].ToString();
query.Dispose();
return(pom);
}
query.Dispose();
return("null");
}
public void SupRemigration(MySQL_DB pMysql, PostgreSQL_DB pPostgres, string pDate = "1.1.2500")
{
pMysql.Message = "DUMMY data1 GENERATE - START";
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(36, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(64, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(119, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(120, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(121, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(122, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(152, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(157, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(165, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(195, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(196, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(197, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(198, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(199, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(200, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(219, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(307, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(324, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(358, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(432, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(436, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(437, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(502, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(503, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(534, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(535, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(536, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(537, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(538, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(539, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(540, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(541, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(542, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(543, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(545, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(546, 1,6);");
pPostgres.Insert("insert into tbl_tour_dates(id, season_id, event_id) values(580, 1,6);");
pMysql.Message = "DUMMY data1 GENERATE - FINISH";
}
/*
* pId - ID in MySQL table
* pMysqlList - first is Table_name or "null", others are ROWS
* pPosgresList - first is Table_name others are rows
* condition: count and order of the rows must be the same
*/
public void UpdatePostgreRow(String pId, List <String> pMysqlList, List <String> pPosgresList, PostgreSQL_DB pPostgres)
{
string core_update_string = null;
for (int i = 1; i < pMysqlList.Count; i++)
{
core_update_string += pPosgresList[i];
core_update_string += "=";
String pMysqlValue = null;
if (pMysqlList[i] == "False" || pMysqlList[i] == "True")
{
pMysqlValue = pMysqlList[i].Replace("False", "0").Replace("True", "1");
}
else
{
pMysqlValue = pMysqlList[i];
}
core_update_string += "'" + pMysqlValue.Replace("'", "''") + "'";
if (i < pMysqlList.Count - 1)
{
core_update_string += ", ";
}
}
pPostgres.Update("update " + pPosgresList[0] + " set " + core_update_string + " where id=" + pId + ";");
}