from calendar import Calendar, calendar import csv import base64 from itertools import chain, count from random import Random, randint, random from select import select import time import requests import os import pymysql import subprocess import random import calendar import datetime import runpy import time from collections import Counter import os.path from datetime import datetime, timedelta, date,timezone import schedule import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart import email.utils import traceback from config import get_database_config, get_config, DatabaseConnection from sendmail import sendExceptionMail import logging data_insert_limit = 100 counts = 0 # for generating log for how long script runs log_file_path = "script_log.txt" def log(message): with open(log_file_path, "a") as log_file: log_file.write(message + "\n") # environment = 'staging' #production, staging, local environment_selected, config = get_config() print(environment_selected) # db table Details live_ovpn_table_name = config["ovpn_table_name"] live_country_table_name = config["country_table_name"] decode_vpn_path = "decodeOvpn/" ovpn_path = config['ovpn_main_file_path'] #script details abs_path = os.path.abspath(__file__) full_script_name = environment_selected.upper() + " " + os.path.basename(abs_path) server_ip = config["server_ip"] #---DATABASE_CONFIG--- db_config = get_database_config(config) def update_script_run_counter(): counter_file_path = "script_run_counter.txt" # Check if the counter file exists if not os.path.exists(counter_file_path): # If the file doesn't exist, create it and initialize the counter to 1 with open(counter_file_path, "w") as counter_file: counter_file.write("1") return 1 else: # If the file exists, read the current counter value, increment it, and update the file with open(counter_file_path, "r+") as counter_file: counter = int(counter_file.read()) counter += 1 counter_file.seek(0) counter_file.write(str(counter)) return counter def run_vpn_script(): try: with open(f"/var/www/html/ovpn/vpnGate/kr_temporary_last_run.txt", "w") as f: dt = time.gmtime() ts = calendar.timegm(dt) f.write(str(ts)) print('Script time updated...!') except Exception as e: print(e) def init_logging(): """Initialize logging to a log file.""" WORKING_DIRECTORY = os.path.dirname(os.path.abspath(__file__)) logs_dir = os.path.join(WORKING_DIRECTORY, 'kr_temp_server_logs') if not os.path.exists(logs_dir): os.makedirs(logs_dir) log_file_path = os.path.join(logs_dir, 'kr_temp_' + datetime.now().strftime('%Y-%m-%d') + '.log') print("log_file_path ------------",log_file_path) if not os.path.exists(log_file_path): logging.basicConfig(filename=log_file_path, format='%(asctime)s - %(levelname)s - %(message)s', datefmt='%Y-%m-%d %H:%M:%S', level=logging.INFO) else: logging.basicConfig(filename=log_file_path, format='%(asctime)s - %(levelname)s - %(message)s', datefmt='%Y-%m-%d %H:%M:%S', level=logging.INFO) def checkcountrydata(): try: db = DatabaseConnection(db_config) # db = pymysql.connect(host = "104.248.13.122",user = "ovpnuser",passwd = "y7OZho5X4jejchHM",database = "ovpn-temp") db.connect() sql = f"select country_code from {live_country_table_name}" number0flines = db.execute_query(sql) results = db.fetch_all() webcountrycode = [] commanlist = [] sqlcountrycode = [] CSV_URL = "http://www.vpngate.net/api/iphone/" with requests.Session() as s: download = s.get(CSV_URL) decoded_content = download.content.decode("utf-8") cr = csv.reader(decoded_content.splitlines(), delimiter=",") my_list = list(cr) if number0flines == 0: for contrydata in my_list[2:-1]: countrycode = contrydata[6] webcountrycode.append(countrycode) commanlist = list(set(webcountrycode)) for checklist in commanlist: for i in range(2, len(my_list)): if my_list[i][6] == checklist: arraydata = my_list[i] query = "INSERT INTO {} (country , country_code , created_at , updated_at, premium, is_active) VALUES (%s , %s , %s ,%s , %s , %s)".format( live_country_table_name ) dt = time.gmtime() ts = calendar.timegm(dt) alldata = ( arraydata[5], arraydata[6], ts, ts, 1, 0, ) a = db.execute_query(query, alldata) results = db.fetch_all() db.connection.commit() break else: for codesof in results: codes = codesof["country_code"] sqlcountrycode.append(codes) for contrydata in my_list[2:-1]: countrycode = contrydata[6] webcountrycode.append(countrycode) commanlist = list(set(webcountrycode)) neewcode = list(set(commanlist) - set(sqlcountrycode)) for checklist in neewcode: for i in range(2, len(my_list)): if my_list[i][6] == checklist: arraydata = my_list[i] query = "INSERT INTO {} (country , country_code , created_at , updated_at , is_active) VALUES (%s , %s , %s ,%s , %s)".format( live_country_table_name ) dt = time.gmtime() ts = calendar.timegm(dt) alldata = ( arraydata[5], arraydata[6], ts, ts, 0, ) a = db.execute_query(query, alldata) results = db.fetch_all() db.connection.commit() break except Exception: excp = traceback.format_exc() print(excp) excp_time = time.time() excp_time_formatted = datetime.fromtimestamp(excp_time).strftime('%Y-%m-%d %H:%M:%S') sendExceptionMail(str(excp), full_script_name, server_ip, str(excp_time_formatted)) runpy.run_path(path_name="kr_temporary_server.py") finally: if db: db.close() def insertwithlimit(): try: db = DatabaseConnection(db_config) # db = pymysql.connect(host = "104.248.13.122",user = "ovpnuser",passwd = "y7OZho5X4jejchHM",database = "ovpn-temp") db.connect() sql = f"select country_code from {live_country_table_name}" db.execute_query(sql) results = db.fetch_all() webcountrycode = [] commanlist = [] sqlcountrycode = [] sqliparr = [] webarrip = [] mysqlarrip = [] newmylist = [] webcountryip = [] newcode = [] CSV_URL = "http://www.vpngate.net/api/iphone/" with requests.Session() as s: download = s.get(CSV_URL) decoded_content = download.content.decode("utf-8") cr = csv.reader(decoded_content.splitlines(), delimiter=",") my_list = list(cr) my_list.remove(my_list[0]) my_list.remove(my_list[1]) my_list.remove(my_list[-1]) my_list = my_list[2:-1] # for record in filtered_list: # print(record[1]) # exit() sql = f"SELECT country_id, (SELECT country_code from {live_country_table_name} as c WHERE c.country_id = v.country_id) as country_code FROM {live_ovpn_table_name} v GROUP by country_id" db.execute_query(sql) checkdata = db.fetch_all() for arrRowsData in my_list: ipcheck = arrRowsData[1] # print(ipcheck) webcountryip.append(ipcheck) # webcountrycode.append(countrycode) # exit() for arrRowsData in my_list: ipcheck = arrRowsData[6] # print(ipcheck) webcountrycode.append(ipcheck) # for codesof in results: # codes = codesof[0] # sqlcountrycode.append(codes) # print("sql country code list" ,list(set(sqlcountrycode))) commanlist = list(set(webcountrycode)) sql = f"select * from {live_ovpn_table_name}" db.execute_query(sql) result = db.fetch_all() # print('result', result) for arrRowsData in my_list: ipcheck = arrRowsData[1] # print(ipcheck) # print(ipcheck) webarrip.append(ipcheck) for iplist in result: # print(result) # exit() hostname = iplist["ip_address"] mysqlarrip.append(hostname) newiplist = list(set(webarrip) - set(mysqlarrip)) print(" total new ip list = ", len(newiplist)) # exit() for codelist in checkdata: newcode.append(codelist["country_code"]) # print(commanlist, newcode) updatedlist = list(set(commanlist) - set(newcode)) print("updated list", updatedlist) # exit() # print("comman list = ", commanlist) # print("newcode list = ", set(newcode)) # print("updated list = ", updatedlist) # exit() # counts = 0 # print(len(filtered_list)) # exit() for temp in newcode: counts = 0 for abc in my_list: if abc[6] == temp: if counts == data_insert_limit: counts = 0 break else: # print(abc) counts = counts + 1 newmylist.append(abc) print("newmylist -------->", len(newmylist)) print("mylist -------->", len(my_list)) # exit() sql = f"select * from {live_ovpn_table_name}" db.execute_query(sql) ipfetch = db.fetch_all() for iplist in ipfetch: hostname = iplist["ip_address"] sqliparr.append(hostname) for i in sqliparr: for abc in newmylist: if i == abc[1]: newmylist.remove(abc) print("newmylist -------->", len(newmylist)) query = f"SELECT country_id,(SELECT country_code from {live_country_table_name} c WHERE c.country_id = vs.country_id) as country_code, COUNT(country_id) countrycount FROM {live_ovpn_table_name} as vs GROUP by country_id" db.execute_query(query) checktotalcount = db.fetch_all() # print("total data = " , checktotalcount) # print(checktotalcount) # exit() for i in range(0, len(checktotalcount)): countrynumber = checktotalcount[i]["country_code"] for abc in newmylist: # print("newcount =" , newcount) # print(abc[6].upper(), countrynumber) if abc[6].upper() == countrynumber: ip_address = abc[1] select_query = f"SELECT COUNT(*) as count FROM {live_ovpn_table_name} WHERE ip_address = %s" db.execute_query(select_query, (ip_address,)) result = db.fetch_one() existing_count = result["count"] if existing_count == 0: arrRowsData = abc hostname = abc[0] a = base64.b64decode(arrRowsData[14]) query = "select country_id from {} WHERE country_code = %s".format( live_country_table_name ) alldata = (arrRowsData[6], ) print("new", alldata) b = db.execute_query(query, alldata) c_id = db.fetch_all() if c_id: country_id = c_id[0]["country_id"] print("new", country_id) dt = time.gmtime() ts = calendar.timegm(dt) os.makedirs(decode_vpn_path, exist_ok=True) file_name_aaa = ( decode_vpn_path + hostname + "_" + arrRowsData[1] + ".text" ) with open(file_name_aaa, "wb") as fh: fh.write(a) os.makedirs(ovpn_path, exist_ok=True) file_name_bbb = ( ovpn_path + hostname + "_" + arrRowsData[1] + ".ovpn" ) f = open(file_name_bbb, "a+") with open(file_name_aaa, "r") as thelib: mydata = thelib.readlines() filedata = [] for data in mydata: if ( data.startswith("#") or data.startswith(" ") or data.startswith("\n") ): continue else: # print("Main data",data) f.write(data) f.close() with open(file_name_bbb, "r") as thelib: reopendata = thelib.read() country_code_2 = arrRowsData[6].upper() # print("newmylist_ip = ", ip_address) query = "INSERT INTO {} (country_id, ip_address, ovpn, created_at , updated_at, country_code) VALUES (%s, %s, %s, %s, %s, %s)".format( live_ovpn_table_name ) values = ( country_id, ip_address, reopendata, ts, ts, country_code_2, ) # if ip_address not in unique_ip_addresses: db.execute_query(query, values) db.connection.commit() last_inserted_id = db.last_row_id print("last_inserted_id", last_inserted_id) # exit() # Rename the file_bbb new_filename = f"{ovpn_path}{last_inserted_id}_{ip_address}.ovpn" os.rename(file_name_bbb, new_filename) else: print("country_id not found in checktotalcount") else: print(f"Skipping duplicate IP address: {ip_address}") for temp in updatedlist: counts = 0 for abc in my_list[2:-1]: if abc[6].upper() == temp: ip_address = abc[1] hostname = abc[0] # if ip_address in unique_ip_addresses: # print(f"Skipping duplicate IP address: {ip_address}") # continue select_query = f"SELECT COUNT(*) as count FROM {live_ovpn_table_name} WHERE ip_address = %s" db.execute_query(select_query, (ip_address,)) result = db.fetch_one() existing_count = result["count"] if existing_count == 0: a = base64.b64decode(abc[-1]) query = "select country_id from {} WHERE country_code = %s".format( live_country_table_name ) alldata = (abc[6],) # print("alldata = ", alldata) print("updated", alldata) b = db.execute_query(query, alldata) c_id_updated = db.fetch_all() # print("result = ",results) if c_id_updated: country_id = c_id_updated[0]["country_id"] print("updated", country_id) # print("updatedlist_ip = ", ip_address) db.connection.commit() os.makedirs(decode_vpn_path, exist_ok=True) file_name_aaa = decode_vpn_path + hostname + "_" + abc[1] + ".text" with open(file_name_aaa, "wb") as fh: fh.write(a) os.makedirs(ovpn_path, exist_ok=True) file_name_bbb = ovpn_path + hostname + "_" + abc[1] + ".ovpn" f = open(file_name_bbb, "a+") with open(file_name_aaa, "r") as thelib: mydata = thelib.readlines() filedata = [] for data in mydata: if ( data.startswith("#") or data.startswith(" ") or data.startswith("\n") ): continue else: # print("Main data",data) f.write(data) f.close() with open(file_name_bbb, "r") as thelib: reopendata = thelib.read() # dt = time.gmtime() dt = time.gmtime() ts = calendar.timegm(dt) country_code_in_updated = abc[6].upper() query = "INSERT INTO {} (country_id, ip_address, ovpn, created_at , updated_at, country_code) VALUES (%s ,%s ,%s, %s, %s, %s)".format( live_ovpn_table_name ) alldata = ( country_id, ip_address, reopendata, ts, ts, country_code_in_updated, ) db.execute_query(query, alldata) db.connection.commit() last_inserted_id = db.last_row_id print("last_inserted_id", last_inserted_id) # Rename the file_bbb new_filename = f"{ovpn_path}{last_inserted_id}_{ip_address}.ovpn" os.rename(file_name_bbb, new_filename) else: print("country_id not found in updated") else: print(f"Skipping duplicate IP address: {ip_address}") delete_condition = "ping_count < -2" select_ping_query = ( f"SELECT * FROM {live_ovpn_table_name} WHERE {delete_condition}" ) db.execute_query(select_ping_query) selected_data = db.fetch_all() for data in selected_data: server_id = data["server_id"] ip_address = data["ip_address"] file_path = ovpn_path + str(server_id) + "_" + ip_address + ".ovpn" if os.path.isfile(file_path): os.remove(file_path) print(f"Removed: {file_path}") # print("Ovpn Files removed successfully.") # exit() db.connection.commit() delete_query = f"DELETE FROM {live_ovpn_table_name} WHERE {delete_condition}" db.execute_query(delete_query) db.connection.commit() # print("last line") files = os.listdir(decode_vpn_path) # Iterate through the files and remove them for file in files: file_path = os.path.join(decode_vpn_path, file) if os.path.isfile(file_path): os.remove(file_path) # print("text Files removed successfully.") run_vpn_script() init_logging() utc_now = datetime.now(timezone.utc) ist_now = utc_now + timedelta(hours=5, minutes=30) script_end_time = ist_now.strftime("%Y-%m-%d %H:%M:%S") logging.info(f"Script completed time is : {script_end_time}") except Exception: excp = traceback.format_exc() print(excp) excp_time = time.time() excp_time_formatted = datetime.fromtimestamp(excp_time).strftime('%Y-%m-%d %H:%M:%S') sendExceptionMail(str(excp), full_script_name, server_ip, str(excp_time_formatted)) runpy.run_path(path_name="kr_temporary_server.py") finally: if db: db.close() # main funtion that is calling all upper functoins and logs the time and how many time script runs def main(): run_count = 0 checkcountrydata() insertwithlimit() # def job(): # checkcountrydata() # insertwithlimit() # start_time = time.time() # print({datetime.fromtimestamp(start_time).strftime('%Y-%m-%d %H:%M:%S')}) # schedule.every(15).minutes.do(job) # while True: # schedule.run_pending() # time.sleep(1) if __name__ == "__main__": main()