def migrate_lines_budget_repair(data): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() for d in data: query = "SELECT pr_contenido from presupuesto_reparacion where pr_id='" + d['id_complete'] + "'" cursor_m.execute(query) lines_srt = cursor_m.fetchall()[0][0] lines = json.loads(lines_srt) try: count = len(lines['nombre']) except: count = 0 for x in xrange(count): try: lines['precio'][x] = float(lines['precio'][x]) except ValueError: lines['precio'][x] = 0 try: lines['unidades'][x] = float(lines['unidades'][x]) except ValueError: lines['unidades'][x] = 0 try: lines['dto'][x] = float(lines['dto'][x]) except ValueError: lines['dto'][x] = 0 cursor_p.execute( 'INSERT INTO budget_budgetlinerepair (budget_id, product, unit_price, quantity, discount) VALUES (%s, %s, %s, %s, %s)', (d['id_budget'], lines['nombre'][x], lines['precio'][x], lines['unidades'][x], lines['dto'][x])) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_interventions(): print("MIGRATING INTERVENTIONS...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT a_id, a_d_id, a_z_id, a_e_id, a_crea_id, a_asignado_id, a_nota, a_fecha, a_notaoperario from aviso order by a_id asc" cursor_m.execute(query) last_id = 0 for (a_id, a_d_id, a_z_id, a_e_id, a_crea_id, a_asignado_id, a_nota, a_fecha, a_notaoperario) in cursor_m: cursor_p.execute( 'INSERT INTO intervention_intervention (id, address_id, zone_id, status_id, created_by_id, assigned_id, description, date, note, starred) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, FALSE)', (a_id, a_d_id, a_z_id, a_e_id, a_crea_id, a_asignado_id, a_nota, a_fecha, a_notaoperario)) last_id = a_id last_id += 1 cursor_p.execute('ALTER SEQUENCE intervention_intervention_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID INTERVENTION " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("MIGRATING INTERVENTIONS-LOGS...") migrate_intervention_logs() print("MIGRATING INTERVENTIONS-MODIFICATIONS...") migrate_interventions_modifications()
def migrate_repairs_ath(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT ra_id, ra_estado, ra_sn, ra_modelo, ra_presupuesto, ra_d_id, ra_fecha, ra_creado_id, ra_garantia," \ " ra_bypass, ra_conector, ra_transformador, ra_nota, ra_interno, ra_anoequipo, ra_fabricante, ra_online " \ "from reparacion_ath ORDER by ra_id asc" cursor_m.execute(query) last_id = 0 for (ra_id, ra_estado, ra_sn, ra_modelo, ra_presupuesto, ra_d_id, ra_fecha, ra_creado_id, ra_garantia, ra_bypass, ra_conector, ra_transformador, ra_nota, ra_interno, ra_anoequipo, ra_fabricante, ra_online) in cursor_m: b_online = "A"+ra_online b_transformer = bool(int(ra_transformador)) b_warranty = bool(int(ra_garantia)) b_bypass = bool(int(ra_bypass)) b_connector = bool(int(ra_conector)) cursor_p.execute( 'INSERT INTO repair_athrepair (id, status_id, serial_number, model, budget_id, address_id, date, created_by_id, warranty, bypass, connector, transformer, description, intern_description, year, notice_maker_number, online_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (ra_id, ra_estado, ra_sn, ra_modelo, ra_presupuesto, ra_d_id, ra_fecha, ra_creado_id, b_warranty, b_bypass, b_connector, b_transformer, ra_nota, ra_interno, ra_anoequipo, ra_fabricante, b_online)) last_id = ra_id last_id += 1 cursor_p.execute('ALTER SEQUENCE repair_athrepair_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID REPAIR-ATH " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_repairs_idegis(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT ri_id, ri_estado, ri_sn, ri_modelo, ri_presupuesto, ri_d_id, ri_fecha, ri_creado_id, ri_garantia," \ " ri_sondaph, ri_orp, ri_electrodo, ri_nota, ri_interno, ri_anoequipo, ri_fabricante, ri_online " \ "from reparacion_idegis ORDER by ri_id asc" cursor_m.execute(query) last_id = 0 for (ri_id, ri_estado, ri_sn, ri_modelo, ri_presupuesto, ri_d_id, ri_fecha, ri_creado_id, ri_garantia, ri_sondaph, ri_orp, ri_electrodo, ri_nota, ri_interno, ri_anoequipo, ri_fabricante, ri_online) in cursor_m: b_online = "X"+ri_online b_orp = bool(int(ri_orp)) b_warranty = bool(int(ri_garantia)) b_electrode = bool(int(ri_electrodo)) b_ph = bool(int(ri_sondaph)) cursor_p.execute( 'INSERT INTO repair_idegisrepair (id, status_id, serial_number, model, budget_id, address_id, date, created_by_id, warranty, ph, electrode, orp, description, intern_description, year, notice_maker_number, online_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (ri_id, ri_estado, ri_sn, ri_modelo, ri_presupuesto, ri_d_id, ri_fecha, ri_creado_id, b_warranty, b_ph, b_electrode, b_orp, ri_nota, ri_interno, ri_anoequipo, ri_fabricante, b_online)) last_id = ri_id last_id += 1 cursor_p.execute('ALTER SEQUENCE repair_idegisrepair_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID REPAIR-IDEGIS " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_budgets(): print("MIGRATING BUDGETS...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, p_nulo from presupuesto ORDER by p_id asc" cursor_m.execute(query) last_id = 0 for (p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, p_nulo) in cursor_m: nulo = bool(int(p_nulo)) cursor_p.execute( 'INSERT INTO budget_budgetstandard (id, address_id, created_by_id, date, introduction, conditions, tax, invalid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, nulo)) last_id = p_id last_id += 1 cursor_p.execute( 'ALTER SEQUENCE budget_budgetstandard_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID BUDGET " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("ADDING BUDGET LINES...") migrate_lines_budget()
def migrate_budgets_repair(): print("MIGRATING BUDGETS-REPAIR...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT pr_id, pr_u_id, pr_fecha, pr_intro, pr_condiciones, pr_impuesto from presupuesto_reparacion" cursor_m.execute(query) data = [] for row in cursor_m: data.append(row) cursor_m.close() cursor_m = cnx_m.cursor() final_data = [] for d in data: id_repair = d[0][1:] prefix = d[0][:1].encode('UTF-8') if prefix is 'x' or prefix is 'X': is_ath = False query2 = "SELECT ri_id, ri_d_id from reparacion_idegis where ri_id = " + id_repair else: is_ath = True query2 = "SELECT ra_id, ra_d_id from reparacion_ath where ra_id = " + id_repair cursor_m.execute(query2) ids = cursor_m.fetchall() final_data.append({ 'add': ids[0][1], 'data': d, 'id_repair': id_repair, 'is_ath': is_ath }) lines_data = [] for fd in final_data: tax = float(fd['data'][5]) if fd['is_ath']: cursor_p.execute( 'INSERT INTO budget_budgetrepair (address_id, created_by_id, date, introduction, conditions, tax, invalid, ath_repair_id, intern_id ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id', (fd['add'], fd['data'][1], fd['data'][2], fd['data'][3], fd['data'][4], tax, False, fd['id_repair'], 1)) else: cursor_p.execute( 'INSERT INTO budget_budgetrepair (address_id, created_by_id, date, introduction, conditions, tax, invalid, idegis_repair_id, intern_id ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id', (fd['add'], fd['data'][1], fd['data'][2], fd['data'][3], fd['data'][4], tax, False, fd['id_repair'], 1)) id_insert = cursor_p.fetchone()[0] lines_data.append({ 'id_budget': id_insert, 'id_complete': fd['data'][0] }) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("ADDING BUDGET-REPAIR LINES...") migrate_lines_budget_repair(lines_data)
def migrate_budgets_repair(): print("MIGRATING BUDGETS-REPAIR...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT pr_id, pr_u_id, pr_fecha, pr_intro, pr_condiciones, pr_impuesto from presupuesto_reparacion" cursor_m.execute(query) data = [] for row in cursor_m: data.append(row) cursor_m.close() cursor_m = cnx_m.cursor() final_data = [] for d in data: id_repair = d[0][1:] prefix = d[0][:1].encode('UTF-8') if prefix is 'x' or prefix is 'X': is_ath = False query2 = "SELECT ri_id, ri_d_id from reparacion_idegis where ri_id = " + id_repair else: is_ath = True query2 = "SELECT ra_id, ra_d_id from reparacion_ath where ra_id = " + id_repair cursor_m.execute(query2) ids = cursor_m.fetchall() final_data.append({'add': ids[0][1], 'data': d, 'id_repair': id_repair, 'is_ath': is_ath}) lines_data = [] for fd in final_data: tax = float(fd['data'][5]) if fd['is_ath']: cursor_p.execute( 'INSERT INTO budget_budgetrepair (address_id, created_by_id, date, introduction, conditions, tax, invalid, ath_repair_id, intern_id ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id', (fd['add'], fd['data'][1], fd['data'][2], fd['data'][3], fd['data'][4], tax, False, fd['id_repair'], 1)) else: cursor_p.execute( 'INSERT INTO budget_budgetrepair (address_id, created_by_id, date, introduction, conditions, tax, invalid, idegis_repair_id, intern_id ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id', (fd['add'], fd['data'][1], fd['data'][2], fd['data'][3], fd['data'][4], tax, False, fd['id_repair'], 1)) id_insert = cursor_p.fetchone()[0] lines_data.append({'id_budget': id_insert, 'id_complete': fd['data'][0]}) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("ADDING BUDGET-REPAIR LINES...") migrate_lines_budget_repair(lines_data)
def migrate_interventions_modifications(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT m_a_id, m_u_id, m_modificacion, m_fecha from modificacion_aviso" cursor_m.execute(query) for (m_a_id, m_u_id, m_modificacion, m_fecha) in cursor_m: cursor_p.execute( 'INSERT INTO intervention_interventionmodification (intervention_id, created_by_id, note, date) VALUES (%s, %s, %s, %s)', (m_a_id, m_u_id, m_modificacion, m_fecha)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_intervention_logs(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT lav_a_id, lav_e_id, lav_asignado_id, lav_u_id, lav_fecha from log_aviso" cursor_m.execute(query) for (lav_a_id, lav_e_id, lav_asignado_id, lav_u_id, lav_fecha) in cursor_m: cursor_p.execute( 'INSERT INTO intervention_interventionlog (intervention_id, status_id, assigned_id, created_by_id, date) VALUES (%s, %s, %s, %s, %s)', (lav_a_id, lav_e_id, lav_asignado_id, lav_u_id, lav_fecha)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_logs_idegis(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "Select li_ri_id, li_er_id, li_fecha from log_idegis" cursor_m.execute(query) for (li_ri_id, li_er_id, li_fecha) in cursor_m: cursor_p.execute( 'INSERT INTO repair_idegisrepairlog (repair_id, status_id, date) VALUES (%s, %s, %s)', (li_ri_id, li_er_id, li_fecha)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_messages(): print("MIGRATING MESSAGES...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "Select me_id, me_origen_id, me_destino_id, me_mensaje, me_fecha, me_asunto from mensaje" cursor_m.execute(query) for (me_id, me_origen_id, me_destino_id, me_mensaje, me_fecha, me_asunto) in cursor_m: cursor_p.execute( 'INSERT INTO core_message (id, from_user_id, to_user_id, body, date, subject) VALUES (%s, %s, %s, %s, %s, %s)', (me_id, me_origen_id, me_destino_id, me_mensaje, me_fecha, me_asunto)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_phones(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT t_c_id, t_telefono, t_alias from telefono_cliente" cursor_m.execute(query) for (t_c_id, t_telefono, t_alias) in cursor_m: trim_phone = t_telefono.replace(" ", "") cursor_p.execute( 'INSERT INTO client_phone (client_id, phone, alias) VALUES (%s, %s, %s)', (t_c_id, trim_phone, t_alias)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_addresses(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT d_id, d_c_id, d_z_id, d_direccion, d_alias, d_latitud, d_longitud from direccion_cliente order by d_id asc" cursor_m.execute(query) last_id = 0 for (d_id, d_c_id, d_z_id, d_direccion, d_alias, d_latitud, d_longitud) in cursor_m: cursor_p.execute( 'INSERT INTO client_address (id, client_id, default_zone_id, address, alias, latitude, longitude) VALUES (%s, %s, %s, %s, %s, %s, %s)', (d_id, d_c_id, d_z_id, d_direccion, d_alias, d_latitud, d_longitud)) last_id = d_id last_id += 1 cursor_p.execute('ALTER SEQUENCE client_address_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID CLIENT-ADDR " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_budgets(): print("MIGRATING BUDGETS...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, p_nulo from presupuesto ORDER by p_id asc" cursor_m.execute(query) last_id = 0 for (p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, p_nulo) in cursor_m: nulo = bool(int(p_nulo)) cursor_p.execute( 'INSERT INTO budget_budgetstandard (id, address_id, created_by_id, date, introduction, conditions, tax, invalid) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (p_id, p_d_id, p_u_id, p_fecha, p_intro, p_condiciones, p_impuesto, nulo)) last_id = p_id last_id += 1 cursor_p.execute('ALTER SEQUENCE budget_budgetstandard_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID BUDGET " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("ADDING BUDGET LINES...") migrate_lines_budget()
def migrate_clients(): print("MIGRATING CLIENTS...") cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT c_id, c_nombre, c_email, c_cod, c_dni FROM cliente ORDER by c_id asc" cursor_m.execute(query) last_id = 0 for (c_id, c_nombre, c_email, c_cod, c_dni) in cursor_m: cursor_p.execute( 'INSERT INTO client_client (id, name, email, intern_code, dni) VALUES (%s, %s, %s, %s, %s)', (c_id, c_nombre, c_email, c_cod, c_dni)) last_id = c_id last_id += 1 cursor_p.execute('ALTER SEQUENCE client_client_id_seq RESTART WITH ' + str(last_id)) print("\tUPDATED LAST ID CLIENT " + str(last_id)) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p) print("MIGRATING CLIENT-PHONES...") migrate_phones() print("MIGRATING CLIENTS-ADDRS...") migrate_addresses()
def migrate_lines_budget_repair(data): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() for d in data: query = "SELECT pr_contenido from presupuesto_reparacion where pr_id='" + d[ 'id_complete'] + "'" cursor_m.execute(query) lines_srt = cursor_m.fetchall()[0][0] lines = json.loads(lines_srt) try: count = len(lines['nombre']) except: count = 0 for x in xrange(count): try: lines['precio'][x] = float(lines['precio'][x]) except ValueError: lines['precio'][x] = 0 try: lines['unidades'][x] = float(lines['unidades'][x]) except ValueError: lines['unidades'][x] = 0 try: lines['dto'][x] = float(lines['dto'][x]) except ValueError: lines['dto'][x] = 0 cursor_p.execute( 'INSERT INTO budget_budgetlinerepair (budget_id, product, unit_price, quantity, discount) VALUES (%s, %s, %s, %s, %s)', (d['id_budget'], lines['nombre'][x], lines['precio'][x], lines['unidades'][x], lines['dto'][x])) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)
def migrate_lines_budget(): cnx_m, cursor_m = make_connection_mysql() cnx_p, cursor_p = make_connection_postgres() query = "SELECT p_id, p_contenido from presupuesto" cursor_m.execute(query) for (p_id, p_contenido) in cursor_m: lines = json.loads(p_contenido) try: count = len(lines['nombre']) except: count = 0 for x in xrange(count): try: lines['precio'][x] = float(lines['precio'][x]) except ValueError: lines['precio'][x] = 0 try: lines['unidades'][x] = float(lines['unidades'][x]) except ValueError: lines['unidades'][x] = 0 try: lines['dto'][x] = float(lines['dto'][x]) except ValueError: lines['dto'][x] = 0 cursor_p.execute( 'INSERT INTO budget_budgetlinestandard (budget_id, product, unit_price, quantity, discount) VALUES (%s, %s, %s, %s, %s)', (p_id, lines['nombre'][x], lines['precio'][x], lines['unidades'][x], lines['dto'][x])) close_connection_mysql(cnx_m, cursor_m) close_connection_postgres(cnx_p, cursor_p)