from appservices.common.util import * database_migration = Blueprint("database_migration",__name__) @database_migration.route("/add_category_data",methods=["POST"]) @adminid_access_token_required def add_category_data(): data_status = {"responseStatus": 0, "result": ""} try: categoryData = request.json.get("categoryData","") for each_data in categoryData: if each_data.get("type") == "table": insertableData = each_data.get("data",[]) for each_row in insertableData: termId = each_row.get("term_id") name = each_row.get("name") duration_type_table = Categories( name=name, termId=termId, displayInMenu=False, isFromOldDB=True, isFromOldDBLatest=True, selectTemplate="newsTemplate", createdOn=datetime.datetime.now(), status=1 ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_subcategory_data",methods=["POST"]) def add_subcategory_data(): data_status = {"responseStatus": 0, "result": ""} try: subCategoryData = request.json.get("subCategoryData","") for each_data in subCategoryData: if each_data.get("type") == "table": insertableData = each_data.get("data",[]) for each_row in insertableData: categoryTermId = each_row.get("parent") subcategoryTermId = each_row.get("term_id") category_queryset = Categories.objects(termId=categoryTermId).first() if category_queryset: subcategory_queryset = Categories.objects(termId=subcategoryTermId).first() if subcategory_queryset: subcategoryName = subcategory_queryset.name else: subcategoryName = "" subcategory_table = SubCategories( name=subcategoryName, categoryId=str(category_queryset.id), displayInMenu=False, isFromOldDB=True, termId=subcategoryTermId, selectTemplate="newsTemplate", createdOn=datetime.datetime.now(), status=1 ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_posts_data",methods=["GET"]) @adminid_access_token_required def add_posts_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_posts.json","r") as fr: # with open("/home/ubuntu/kxp/kxp_api/ke_posts_latest.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: if each_row.get("post_type") == "post": post_id = each_row.get("ID") #Fetching category data categories_list = PostsMeta.objects(postId=post_id).distinct("categoryId") categoriesList = [] for each_categ in categories_list: category_queryset = Categories.objects(termId=each_categ).first() if category_queryset: categoriesList.append(str(category_queryset.id)) post_date = each_row.get("post_date") post_content = each_row.get("post_content") post_title = each_row.get("post_title") postDate = datetime.datetime.strptime(each_row.get("post_date"), '%Y-%m-%d %H:%M:%S') print(post_date) print(post_content) print(post_title) print(postDate) print(categoriesList) duration_type_table = Posts( title=post_title, description=post_content, heading=post_title, categoriesList=categoriesList, status=1, termId=post_id, isFromOldDB=True, isFromOldDBLatest=True, createdOn=postDate ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_users_data",methods=["POST"]) @adminid_access_token_required def add_users_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_users.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: termId = each_row.get("ID") userName = each_row.get("user_login","") password = each_row.get("user_pass","") email = each_row.get("user_email","") createdOn = datetime.datetime.strptime(each_row.get("user_registered"), '%Y-%m-%d') displayName = each_row.get("display_name","") duration_type_table = Subscribers( termId=termId, userName=userName, displayName=displayName, email=email, password=password, status=1, isFromOldDB=True, createdOn=createdOn ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_users_extra_fields_data",methods=["POST"]) @adminid_access_token_required def add_users_extra_fields_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_usermeta.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: userId = each_row.get("user_id") user_queryset = Subscribers.objects(termId=userId).first() if user_queryset: print(each_row.get("umeta_id")) if each_row.get("meta_key") == "first_name": firstName = each_row.get("meta_value") user_queryset.update( firstName=firstName ) elif each_row.get("meta_key") == "last_name": lastName = each_row.get("meta_value") user_queryset.update( lastName=lastName ) elif each_row.get("meta_key") == "ke_capabilities": keCapabilities = each_row.get("meta_value") user_queryset.update( keCapabilities=keCapabilities ) elif each_row.get("meta_key") == "KXPtitle": kxpTitle = each_row.get("meta_value") user_queryset.update( kxpTitle=kxpTitle ) elif each_row.get("meta_key") == "sub": sub = each_row.get("meta_value") user_queryset.update( sub=sub ) elif each_row.get("meta_key") == "remark": remark = each_row.get("meta_value") user_queryset.update( remark=remark ) elif each_row.get("meta_key") == "denomination": denomination = each_row.get("meta_value") user_queryset.update( denomination=denomination ) elif each_row.get("meta_key") == "pmpro_bphone": cellNumber = each_row.get("meta_value") user_queryset.update( cellNumber=cellNumber ) elif each_row.get("meta_key") == "member_phone_number": phoneNumber = each_row.get("meta_value") user_queryset.update( phoneNumber=phoneNumber ) elif each_row.get("meta_key") == "pmpro_baddress1": address = each_row.get("meta_value") user_queryset.update( address=address ) elif each_row.get("meta_key") == "pmpro_bcity": city = each_row.get("meta_value") user_queryset.update( city=city ) elif each_row.get("meta_key") == "pmpro_bstate": state = each_row.get("meta_value") user_queryset.update( state=state ) elif each_row.get("meta_key") == "pmpro_bzipcode": zipCode = each_row.get("meta_value") user_queryset.update( zipCode=zipCode ) elif each_row.get("meta_key") == "pmpro_checkout_start_date": pmpro_checkout_start_date = each_row.get("meta_value") if pmpro_checkout_start_date: subscriptionStartDate = datetime.datetime.strptime(each_row.get("meta_value",""), '%Y-%m-%d') user_queryset.update( subscriptionStartDate=subscriptionStartDate ) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/update_posts_categories_data",methods=["POST"]) @adminid_access_token_required def update_posts_categories_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_term_relationships.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: postId = each_row.get("object_id") categId = each_row.get("term_taxonomy_id") categoriesList = [] subcategoriesList = [] post_queryset = Posts.objects(termId=postId).first() if post_queryset: existingCategoriesList = [str(x.id) for x in post_queryset.categoriesList] existingSubCategoriesList = [str(x.id) for x in post_queryset.subCategoriesList] #First checking subcategory subcategory_queryset = SubCategories.objects(termId=categId).first() if subcategory_queryset: subcategoriesList.append(str(subcategory_queryset.id)) if subcategory_queryset.categoryId: categoriesList.append(str(subcategory_queryset.categoryId.id)) else: post_category_queryset = Categories.objects(termId=categId).first() categoriesList.append(str(post_category_queryset.id)) if categoriesList: existingCategoriesList.extend(categoriesList) if subcategoriesList: existingSubCategoriesList.extend(subcategoriesList) newExistingCategoriesList = [] for each_categ in existingCategoriesList: if each_categ not in newExistingCategoriesList: newExistingCategoriesList.append(each_categ) newExistingSubCategoriesList = [] for each_categ in existingSubCategoriesList: if each_categ not in newExistingSubCategoriesList: newExistingSubCategoriesList.append(each_categ) newCategoriesList = [ObjectId(x) for x in newExistingCategoriesList] newSubCategoriesList = [ObjectId(x) for x in newExistingSubCategoriesList] print("&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&") print(postId) print(newCategoriesList) print(newSubCategoriesList) post_queryset.update( categoriesList=newCategoriesList, subCategoriesList=newSubCategoriesList ) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_matrimonial_posts_data",methods=["POST"]) @adminid_access_token_required def add_matrimonial_posts_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_posts.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: if each_row.get("post_type") == "matrimony": post_id = each_row.get("ID") post_date = each_row.get("post_date") #2022-09-06 04:36:52 post_content = each_row.get("post_content") post_title = each_row.get("post_title") post_status = each_row.get("post_status") postDate = datetime.datetime.strptime(post_date, '%Y-%m-%d %H:%M:%S') if post_status == "pending": displayStatus = 0 else: displayStatus = 1 matrimonial_table = MatrimonialAdvertisements( firstName=post_title, termId=post_id, advertisementText=post_content, createdOn=postDate, status=1, isFromOldDB=True, displayStatus=displayStatus ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/update_matrimonial_categories_data",methods=["POST"]) @adminid_access_token_required def update_matrimonial_categories_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_term_relationships.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) count = 0 for each_row in insertableData: postId = each_row.get("object_id") categId = each_row.get("term_taxonomy_id") seekingWhom = None categoryId = None subcateg_name = "" post_queryset = MatrimonialAdvertisements.objects(termId=postId).first() if post_queryset: subcategory_queryset = SubCategories.objects(termId=categId).first() if subcategory_queryset: subcateg_name = subcategory_queryset.name if subcateg_name: #Finding matrimonial category name from above name mat_categ_queryset = MatrimonialCategories.objects(name__icontains=subcateg_name).first() if mat_categ_queryset: categoryId = str(mat_categ_queryset.id) else: if "Seeking Groom" in subcateg_name: seekingWhom = "groom" elif "Seeking Bride" in subcateg_name: seekingWhom = "bride" if categoryId or seekingWhom: print("((((((((((((((categoryId))))))))))))))") print(categoryId) print(seekingWhom) count+=1 print(count) if categoryId: post_queryset.update( categoryId=ObjectId(categoryId) ) if seekingWhom: post_queryset.update( seekingWhom=seekingWhom ) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/add_classifieds_data",methods=["POST"]) @adminid_access_token_required def add_classifieds_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_posts.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: if each_row.get("post_type") == "classified": post_id = each_row.get("ID") post_date = each_row.get("post_date") #2022-09-06 04:36:52 post_content = each_row.get("post_content") post_title = each_row.get("post_title") post_status = each_row.get("post_status") postDate = datetime.datetime.strptime(post_date, '%Y-%m-%d %H:%M:%S') print(post_title) print(post_id) print(post_content) print(postDate) matrimonial_table = Classifieds( companyName=post_title, termId=post_id, classifiedSubject=post_title, classifiedDescription=post_content, createdOn=postDate, status=1, isFromOldDB=True ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/update_posts_attachment_data",methods=["GET"]) @adminid_access_token_required def update_posts_attachment_data(): data_status = {"responseStatus": 0, "result": ""} try: # with open("/home/ubuntu/kxp/kxp_api/ke_postmeta_latest.json","r") as fr: # postsData = json.load(fr) # for each_post in postsData: # if each_post.get("type") == "table": # insertableData = each_post.get("data",[]) # for each_row in insertableData: # if each_row.get("meta_key") == "_thumbnail_id": # post_id = each_row.get("meta_value") # print("************************") # print(post_id) # TempMediaPosts( # postId=post_id, # status=1, # isFromOldDBLatest=True, # createdOn=datetime.datetime.now() # ).save() #Fetching post Ids list posts_list = list(TempMediaPosts.objects(isFromOldDBLatest=True).distinct("postId")) # print(posts_list) # print(type(posts_list)) with open("/home/ubuntu/kxp/kxp_api/ke_posts_latest.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: if each_row.get("post_type") == "attachment": post_id = each_row.get("post_parent") attachment_post_id = each_row.get("ID") if attachment_post_id in posts_list: #Fetching posts data post_queryset = Posts.objects(termId=post_id).first() if post_queryset: poster_old_url = each_row.get("guid") poster_new_url = poster_old_url.replace("https://keralaexpress.com/wp-content","") # poster_new_url = print("**********************") print(post_id) print(poster_new_url) # post_queryset.update( # poster=poster_new_url # ) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route("/create_dummy_posts_removing_image_tag",methods=["POST"]) @adminid_access_token_required def create_dummy_posts_removing_image_tag(): data_status = {"responseStatus": 0, "result": ""} try: posts_queryset = Posts.objects(isFromOldDB=True).all()[:10] for each_post in posts_queryset: DummyPosts( title=each_post.title, description=each_post.description, heading=each_post.heading, categoriesList=each_post.categoriesList, subCategoriesList=each_post.subCategoriesList, status=1, termId=each_post.termId, poster=each_post.poster, isFromOldDB=True, createdOn=each_post.createdOn ).save() data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status # @database_migration.route("/removing_image_tag_for_posts",methods=["GET"]) # def removing_image_tag_for_posts(): # data_status = {"responseStatus": 0, "result": ""} # try: # posts_queryset = Posts.objects(isFromOldDBLatest=True).all() # for each_post in posts_queryset: # old_description = each_post.description # if old_description.startswith("') # print(each_post.termId) # print(findout_index) # print(old_description[:findout_index+1]) # new_description = old_description.replace(old_description[:findout_index+1],"") # each_post.update(description=new_description) # data_status["responseStatus"] = 1 # data_status["result"] = "Database table migration successful!" # return data_status # except: # app.logger.error(traceback.format_exc()) # return data_status @database_migration.route("/update_subscribers_expiry_and_package_dates",methods=["GET"]) @adminid_access_token_required def update_subscribers_expiry_and_package_dates(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_pmpro_memberships_users.json","r") as fr: postsData = json.load(fr) #Fetching packages free_package = Plans.objects(planName__icontains="free",status=1).first() pro_package = Plans.objects(planName__icontains="Pro",status=1).first() print(free_package) print(pro_package) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: #Fetching user queryset print(each_row.get("id","")) user_id = each_row.get("user_id","") print(user_id) membership_id = each_row.get("membership_id","") startdate = each_row.get("startdate","") enddate = each_row.get("enddate","") currentStatus = each_row.get("status","") if startdate == "0000-00-00": startdate = "1900-01-01" if enddate == "0000-00-00": enddate = "1900-01-01" startDateObject = datetime.datetime.strptime(startdate, "%Y-%m-%d") #"2021-12-02" endDateObject = datetime.datetime.strptime(enddate, "%Y-%m-%d") #"2021-12-02" planDaysCount = (endDateObject-startDateObject).days if membership_id == "1": #free planId = free_package elif membership_id == "2": #pro planId = pro_package user_queryset = Subscribers.objects(termId=user_id).first() print(user_queryset) if user_queryset: print("^^^^^^^^^^^^^^^^^^^^^^^^^6") print(planId) print(startDateObject) print(endDateObject) print(planDaysCount) print(currentStatus) user_queryset.update( planId = planId, subscriptionStartDate = startDateObject, planStartDate = startDateObject, planEndDate = endDateObject, planDaysCount = planDaysCount, currentStatus = currentStatus, ) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status # @database_migration.route("/update_categories_subcategories",methods=["GET"]) # def update_categories_subcategories(): # data_status = {"responseStatus": 0, "result": ""} # try: # posts_queryset = Posts.objects(isFromOldDBLatest=True).all() # for each_post in posts_queryset: # each_post.update( # categoriesList=[ # ObjectId("6443b0746965ebd1af5e94ff"), # ObjectId("6443b0746965ebd1af5e94f4"), # ObjectId("6443b0746965ebd1af5e950a") # ], # subCategoriesList=[ # ObjectId("64463da5be73710208624a5c"), # ObjectId("64463da5be73710208624a5d"), # ObjectId("64463da5be73710208624a5e"), # ObjectId("64463da5be73710208624a66"), # ObjectId("64463da5be73710208624a67") # ] # ) # data_status["responseStatus"] = 1 # data_status["result"] = "Database table migration successful!" # return data_status # except: # app.logger.error(traceback.format_exc()) # return data_status @database_migration.route("/add_posts_urls_data",methods=["GET"]) @adminid_access_token_required def add_posts_urls_data(): data_status = {"responseStatus": 0, "result": ""} try: with open("/home/ubuntu/kxp/kxp_api/ke_posts_latest.json","r") as fr: postsData = json.load(fr) for each_post in postsData: if each_post.get("type") == "table": insertableData = each_post.get("data",[]) for each_row in insertableData: if each_row.get("post_type") == "post": post_id = each_row.get("ID") post_name = each_row.get("post_name") unique_url = generate_unique_post_url(post_name) post_queryset = Posts.objects(termId=post_id).first() if unique_url: post_queryset.update(url=unique_url) else: post_name = post_queryset.title.replace(" ","") unique_url = generate_unique_post_url(post_name) post_queryset.update(url=unique_url) print("************") print(unique_url) data_status["responseStatus"] = 1 data_status["result"] = "Database table migration successful!" return data_status except: app.logger.error(traceback.format_exc()) return data_status @database_migration.route('/data_migration', methods=['GET',"POST"]) @adminid_access_token_required def data_migration(): # with open('/home/ubuntu/appservices/appservices/pincode.csv', "r") as csvfile: with open('/home/ubuntu/appservices/appservices/pincode100_to_1000.csv', "r",encoding='utf-8') as csvfile: # Create a CSV reader object csv_reader = csv.reader(csvfile) # Skip the header row if it contains column names next(csv_reader) # Iterate over each row in the CSV file count = 0 for row in csv_reader: # Unpack each column value blockPosName = row[3] pincode = row[4] cityName = row[7] StateName = row[8] Latitude = row[9] Longitude = row[10] print(StateName) country_queryset = Countries.objects(id="658952db2d7a371a1bea2fb5",status__in=[0,1]).first() countryId = country_queryset.id adminId = "6582f0b60f2b1fb508ce05a4" state_queryset = States.objects(stateName__icontains=StateName,status__in=[0,1]).first() if state_queryset: stateId = str(state_queryset.id) state_queryset.update(countryId=ObjectId(countryId)) else: create_state = States( stateName=StateName.upper(), adminId=adminId, countryId=countryId, status=1, createdOn=datetime.datetime.now(), ).save() stateId = str(create_state.id) city_queryset = Cities.objects(cityName__icontains=cityName,stateId=stateId,status__in=[0,1]).first() if city_queryset: cityId = str(city_queryset.id) # city_queryset.update(stateId=ObjectId(stateId)) else: create_city = Cities( cityName=cityName.upper(), stateId=stateId, status=1, adminId=adminId, createdOn=datetime.datetime.now(), ).save() cityId = str(create_city.id) block_queryset = BlocksPos.objects(name__icontains=blockPosName,cityId=cityId,status__in=[0,1]).first() if block_queryset: blockId = str(block_queryset.id) # block_queryset.update(cityId=ObjectId(cityId)) else: create_block = BlocksPos( name=blockPosName, cityId=cityId, status=1, adminId=adminId, createdOn=datetime.datetime.now(), ).save() blockId = str(create_block.id) pincode_queryset = Pincodes.objects(pincode=pincode,blockPoId=blockId,status__in=[0,1]).first() if pincode_queryset: pincodeId = str(pincode_queryset.id) pincode_queryset.update( # blockPoId=ObjectId(blockId), latitude=Latitude, longitude=Longitude ) else: create_pincode = Pincodes( pincode=pincode, blockPoId=blockId, latitude=Latitude, longitude=Longitude, status=1, adminId=adminId, createdOn=datetime.datetime.now(), ).save() pincodeId = str(create_pincode.id) count += 1 print(f'{count} completed') return jsonify({"responseStatus": 1, "result": "Database table migration successful!"})