rathorevaibhav
commented
2 years ago Here are the SQL commands that need to be run:
-- opportunity status
update opportunities set status='Active' where status=LOWER('Active');
update opportunities set status='Inactive' where status=LOWER('Inactive');
-- opportunity type
update opportunities set type='Internship' where type=LOWER('Internship');
update opportunities set type='Job' where type=LOWER('Job');
update opportunities set type='Paid GIG' where type=LOWER('Paid GIG');
update opportunities set type='UnPaid GIG' where type=LOWER('UnPaid GIG');
-- opportunity department_or_team
update opportunities set department_or_team='Accounts, Finance, Tax, Company Secretary, Audit' where department_or_team=LOWER('Accounts, Finance, Tax, Company Secretary, Audit');
update opportunities set department_or_team='Administration and Public Relations' where department_or_team=LOWER('Administration and Public Relations');
update opportunities set department_or_team='Design, Creative, User Experience' where department_or_team=LOWER('Design, Creative, User Experience');
update opportunities set department_or_team='Engineering, Research & Development' where department_or_team=LOWER('Engineering, Research & Development');
update opportunities set department_or_team='Front Office, Data Entry' where department_or_team=LOWER('Front Office, Data Entry');
update opportunities set department_or_team='HR, Recruitment, Administration, IR' where department_or_team=LOWER('HR, Recruitment, Administration, IR');
update opportunities set department_or_team='IT/ITES, Software Development' where department_or_team=LOWER('IT/ITES, Software Development');
update opportunities set department_or_team='Journalism, Editing, Content' where department_or_team=LOWER('Journalism, Editing, Content');
update opportunities set department_or_team='Legal, Regulatory, Intellectual Property' where department_or_team=LOWER('Legal, Regulatory, Intellectual Property');
update opportunities set department_or_team='Production, Manufacturing, Maintenance, Operations, Quality Control' where department_or_team=LOWER('Production, Manufacturing, Maintenance, Operations, Quality Control');
update opportunities set department_or_team='Marketing, Sales, Retail, Business Development' where department_or_team=LOWER('Marketing, Sales, Retail, Business Development');
update opportunities set department_or_team='Project Management, Site Engineering' where department_or_team=LOWER('Project Management, Site Engineering');
update opportunities set department_or_team='Supply Chain, Logistics, Purchase, Materials' where department_or_team=LOWER('Supply Chain, Logistics, Purchase, Materials');
update opportunities set department_or_team='Teaching, Education, Training, Counselling' where department_or_team=LOWER('Teaching, Education, Training, Counselling');
update opportunities set department_or_team='Other' where department_or_team=LOWER('Other');
-- opportunity compensation_type
update opportunities set compensation_type='Yes' where status=LOWER('Yes');
update opportunities set compensation_type='No' where status=LOWER('No');
-- institution status
update institutions set status='Active' where status=LOWER('Active');
update institutions set status='Inactive' where status=LOWER('Inactive');
-- institution type
update institutions set type='Govt Degree College' where type=LOWER('Govt Degree College');
update institutions set type='Aided Degree College' where type=LOWER('Aided Degree College');
update institutions set type='Govt Polytechnic' where type=LOWER('Govt Polytechnic');
update institutions set type='Central University' where type=LOWER('Central University');
update institutions set type='State University' where type=LOWER('State University');
update institutions set type='Private University / College' where type=LOWER('Private University / College');
update institutions set type='School' where type=LOWER('School');
update institutions set type='Govt. ITI' where type=LOWER('Govt. ITI');
update institutions set type='Private ITI' where type=LOWER('Private ITI');
update institutions set type='NGO' where type=LOWER('NGO');
update institutions set type='Aided Polytechnic' where type=LOWER('Aided Polytechnic');
-- employment connection status
update employment_connections set status='Interview To Be Scheduled' where status=LOWER('Interview To Be Scheduled');
update employment_connections set status='Interview Scheduled' where status=LOWER('Interview Scheduled');
update employment_connections set status='Interview Complete' where status=LOWER('Interview Complete');
update employment_connections set status='Rejected by Employer' where status=LOWER('Rejected by Employer');
update employment_connections set status='Offer Made by Employer' where status=LOWER('Offer Made by Employer');
update employment_connections set status='Offer Accepted by Student' where status=LOWER('Offer Accepted by Student');
update employment_connections set status='Offer Rejected by Student' where status=LOWER('Offer Rejected by Student');
update employment_connections set status='Internship Started' where status=LOWER('Internship Started');
update employment_connections set status='Internship Complete' where status=LOWER('Internship Complete');
update employment_connections set status='Student Dropped Out' where status=LOWER('Student Dropped Out');
update employment_connections set status='No Longer Employed Here' where status=LOWER('No Longer Employed Here');
update employment_connections set status='Unknown' where status=LOWER('Unknown');
-- employers industry
update employers set industry='Advertising, PR, MR, Event Management' where industry=LOWER('Advertising, PR, MR, Event Management');
update employers set industry='Agriculture & Farm Services' where industry=LOWER('Agriculture & Farm Services');
update employers set industry='Architecture, Interior Design' where industry=LOWER('Architecture, Interior Design');
update employers set industry='Automobile, Auto Anciliary, Auto Components' where industry=LOWER('Automobile, Auto Anciliary, Auto Components');
update employers set industry='Aviation, Aerospace, Aeronautical' where industry=LOWER('Aviation, Aerospace, Aeronautical');
update employers set industry='Banking, Financial Services, Broking' where industry=LOWER('Banking, Financial Services, Broking');
update employers set industry='BPO, Call Centre, ITeS' where industry=LOWER('BPO, Call Centre, ITeS');
update employers set industry='Chemicals, PetroChemical, Plastic, Rubber' where industry=LOWER('Chemicals, PetroChemical, Plastic, Rubber');
update employers set industry='Construction, Engineering, Cements, Metals' where industry=LOWER('Construction, Engineering, Cements, Metals');
update employers set industry='Courier, Transportation, Freight, Warehousing' where industry=LOWER('Courier, Transportation, Freight, Warehousing');
update employers set industry='Education, Teaching, Training' where industry=LOWER('Education, Teaching, Training');
update employers set industry='Electrical & Electronics' where industry=LOWER('Electrical & Electronics');
update employers set industry='FMCG, Foods, Beverages' where industry=LOWER('FMCG, Foods, Beverages');
update employers set industry='Government, Defence' where industry=LOWER('Government, Defence');
update employers set industry='Insurance' where industry=LOWER('Insurance');
update employers set industry='IT-Hardware & Networking' where industry=LOWER('IT-Hardware & Networking');
update employers set industry='IT-Software, Software Services' where industry=LOWER('IT-Software, Software Services');
update employers set industry='KPO, Research, Analytics' where industry=LOWER('KPO, Research, Analytics');
update employers set industry='Legal Services' where industry=LOWER('Legal Services');
update employers set industry='Manufacturing' where industry=LOWER('Manufacturing');
update employers set industry='Marketing and Sales' where industry=LOWER('Marketing and Sales');
update employers set industry='Media, Entertainment, Internet, Telecom' where industry=LOWER('Media, Entertainment, Internet, Telecom');
update employers set industry='Medical, Healthcare, Hospitals' where industry=LOWER('Medical, Healthcare, Hospitals');
update employers set industry='NGO, Social Services, Regulators, Industry Associations' where industry=LOWER('NGO, Social Services, Regulators, Industry Associations');
update employers set industry='Oil and Gas, Energy, Power, Infrastructure' where industry=LOWER('Oil and Gas, Energy, Power, Infrastructure');
update employers set industry='Pharmaceutical, Biotech, Clinical Research' where industry=LOWER('Pharmaceutical, Biotech, Clinical Research');
update employers set industry='Printing, Packaging' where industry=LOWER('Printing, Packaging');
update employers set industry='Real Estate, Property' where industry=LOWER('Real Estate, Property');
update employers set industry='Recruitment, Staffing' where industry=LOWER('Recruitment, Staffing');
update employers set industry='Retail, Wholesale, E-Commerce' where industry=LOWER('Retail, Wholesale, E-Commerce');
update employers set industry='Shipping, Marine' where industry=LOWER('Shipping, Marine');
update employers set industry='Strategy, Management Consulting' where industry=LOWER('Strategy, Management Consulting');
update employers set industry='Textiles, Garments, Accessories, Fashion Design' where industry=LOWER('Textiles, Garments, Accessories, Fashion Design');
update employers set industry='Travel, Tourism, Hospitality' where industry=LOWER('Travel, Tourism, Hospitality');
update employers set industry='Wellness, Fitness, Sports, Beauty' where industry=LOWER('Wellness, Fitness, Sports, Beauty');
update employers set industry='Other' where industry=LOWER('Other');
-- employers status
update employers set status='Active' where status=LOWER('Active');
update employers set status='Inactive' where status=LOWER('Inactive');
Describe the task Related to https://github.com/gitmedha/medha-react-ui/issues/430
We need to manually run the update script in database to ensure fields have correct values and no dualities based on uppercase/lowercase overlaps.
Check for these things
Query to run (requires updates to values for every above field)
The new values should be from picklist.