RENAME TABLE education.fincancialYear TO education.financialYear; ALTER TABLE education.academicYear ADD shortName varchar(200) NULL; ALTER TABLE education.branch ADD bankName varchar(200) NULL; ALTER TABLE education.branch ADD accountNo varchar(100) NULL; ALTER TABLE education.branch ADD bankBranchName varchar(255) NULL; ALTER TABLE education.branch ADD ifscCode varchar(100) NULL; --------------------------------AFTER DEPLOYMENT-12-05-2022------------------------------- ALTER TABLE users add column dataVisibility varchar(250); ALTER TABLE users add column reportTo varchar(250); ALTER TABLE page add column menuTitle varchar(250); ALTER TABLE page add column pageType varchar(250); ALTER TABLE page add column subMenu varchar(250); menu table created department table created --------------------------14-05-2022-------------------------------------- ALTER TABLE course add column tax varchar(250); ALTER TABLE feePlan add column ledgerHead varchar(250); ALTER TABLE page add column icon varchar(200); ALTER TABLE page add column menuOrder varchar(50); ALTER TABLE menu add column subMenuIcon varchar(250); alter table enquire add column file_id varchar(200); uploadedFile table created -- Point to Remember If page is subTitle Type Then icon will be add to Menu modal then concatenate from there ------- -- ====================================================30-05-2022========================================================================== alter table branch add column isMainBranch varchar(100); alter table users add column branch varchar(250); ----------------5-06-2022---------- registerType table added AdmissionDocument table added ----------------5-06-2022---------- Fee plan menu to removed -- ==========================================-EXISTING TABLE ADD COLUMN WILL BE ADDED HERE-============================================ ALTER TABLE course add column courseType varchar(200); ALTER TABLE course add column testAmount varchar(200); -- ====================================14-06-2022================================== ALTER TABLE page add column subMenuOrder varchar(20); ALTER TABLE page add column dropDownOrder varchar(20); ALTER TABLE leadStage add column type varchar(200); ALTER TABLE followup add column followupPurpose varchar(200); -- =================================14-06-20202============================================================================================================== ALTER TABLE enquire add column campaign_name varchar(250); -- ======21-06-2022===== ALTER TABLE users add column userType varchar(250); -- ===========30-06-2022========================== ALTER TABLE admissionDocument add column Type varchar(200); alter table student add column applicationType varchar(200); alter table student add column package text; alter table student add column document text; alter table student add column batchType varchar(255); ALTER TABLE studentPlan add column discountAmount varchar(200); Query OK, 0 rows affected (0.54 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE studentPlan add column planType varchar(200); Query OK, 0 rows affected (0.73 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE studentPlan add column planAmount varchar(200); Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE studentPlan add column lateAdmissionAmount varchar(200); Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE studentPlan add column paymentPlanType varchar(200); Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE studentPlan add column addDateTime varchar(200); Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE studentPlan add column admissionStage varchar(200); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 new table UidInfo added ALTER TABLE student add column academicSession varchar(200); ------------------------------17-08-2022--------------------------- ALTER TABLE student add column enquire_id varchar(255) NULL ALTER TABLE enquire add column enquirePurpose varchar(255) NULL ALTER TABLE Setting_Api add column authToken text NULL ALTER TABLE Setting_Api add column appKey varchar(255) NULL ALTER TABLE Setting_Api add column appSecret text NULL ALTER TABLE Setting_Api add column port varchar(255) NULL ALTER TABLE Setting_Api add column tenantId text NULL ALTER TABLE Setting_Api add column accessKey text null; -----------------------------------21-08-2022------------------------------------ ALTER TABLE Setting_Image add column size varchar(100) null; ALTER TABLE Setting_Image add column type varchar(100) null; ---------------------------------23-08-2022----------------------------------------------- ALTER TABLE users add column isPhoneVisible varchar(20) null; ALTER TABLE users add column isEmailVisible varchar(20) null; -------------------------above done------------------------------------------------------------ -- ===========================23-08-2022============================================== ALTER TABLE users add column isLogin varchar(10) null ALTER TABLE users add column loginToken varchar(255) null; ALTER TABLE page add column type varchar(255) null; ------------------------------25-08-2022--------------- ALTER TABLE enquire add column addTime varchar(100); ----------------------2022-09-10------- ALTER TABLE enquire add column counselor varchar(255) null; ALTER TABLE users ADD COLUMN isLockDevice varchar(10); ALTER TABLE users ADD COLUMN deviceToken text; ALTER TABLE users ADD COLUMN isDeviceSet varchar(10); ALTER TABLE enquire add column regno varchar(100) null; ALTER TABLE enquire add column AdmId varchar(100) null; ALTER TABLE enquire CHANGE COLUMN regno Regno varchar(100); ALTER TABLE enquire add column Email varchar(100) null; ALTER TABLE Student_Registration add column Email varchar(100) null; ALTER TABLE enquire add column ProfilePath text null; ALTER TABLE Student_Registration add column venu text null; ALTER TABLE enquire add column MotherName varchar(255) null; ALTER TABLE Student_Registration add column board varchar(100) null; mysql> ALTER TABLE Student_Registration add column schoolName varchar(200) null; Query OK, 0 rows affected (1.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE Student_Registration add column schoolBoard varchar(200) null; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE Student_Registration add column schoolAddress varchar(200) null; Query OK, 0 rows affected (0.59 sec) Records: 0 Duplicates: 0 Warnings: 0 SELECT PrimaryContact,Name,ss FROM enquire INNER JOIN (SELECT PrimaryContact as dd,count(*) as ss FROM enquire GROUP BY enquire.PrimaryContact HAVING COUNT(enquire.PrimaryContact) > 1) dup ON enquire.PrimaryContact = dd Limit 10,0 select PrimaryContact,Name,count(*) as Total from enquire group by PrimaryContact having count(*) > 1; SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ALTER TABLE Student_Registration add column studyCenter varchar(255) null; ALTER TABLE users add column moduleType varchar(250) null; // to identifiy module based user like Lead,Admission,Stock ALTER TABLE enquire add utm_medium varchar(200) null; ALTER TABLE enquire add utm_campaign varchar(200) null; ALTER TABLE enquire add utm_term varchar(200) null; ALTER TABLE enquire add utm_content varchar(250) null; ALTER TABLE uploadedFile add column fileSize varchar(200) null; ALTER TABLE `enquire` ADD `update_at` datetime on update CURRENT_TIMESTAMP NOT NULL AFTER `utm_content`; CREATE TABLE `education`.`parallel` ( `parallel_id` INT NOT NULL AUTO_INCREMENT , `StudentId` VARCHAR(200) NOT NULL , `LogDate` DATE NOT NULL , `logTime` TIMESTAMP NOT NULL , `SNR` VARCHAR(200) NOT NULL , `addDateTime` VARCHAR(200) NOT NULL , PRIMARY KEY (`parallel_id`)) ENGINE = InnoDB; SELECT followupType,followupBy,COUNT(followupType),SUM(followupType) FROM `followup` WHERE addDate LIKE '2022-10-14%' AND (followupPurpose <> 'Welcome' or followupPurpose is null) GROUP by followupType,followupBy SELECT feedbackType,followupBy,COUNT(feedbackType) FROM `followup` WHERE addDate LIKE '2022-10-13%' AND (followupPurpose <> 'Welcome' or followupPurpose is null) GROUP by feedbackType,followupBy SELECT feedbackType,COUNT(feedbackType),followupBy FROM `followup` WHERE followupType = 'Call' AND addDate LIKE '2022-10-15%' GROUP by followupBy,feedbackType SELECT Source,COUNT(followupType),followupType FROM `followup` INNER JOIN enquire ON enquire.enquire_id = followup.leadId WHERE followup.addDate LIKE '2022-10-15%' AND(followupPurpose <> 'Welcome' or followupPurpose is null) GROUP by followupBy,followupType ALTER TABLE enquire add column OtherAchRankMark varchar(200) null; ------------------2022-11-23------------------ ALTER TABLE Setting_ScheduleTemplate add column conditions text; ALTER TABLE enquire add column visiting_time varchar(100); mysql> ALTER TABLE enquire add column metre_mark varchar(100); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE enquire add column metre_rank varchar(100); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE enquire add column metre_status varchar(100); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE enquire add column last_visited_at varchar(200) null; alter table enquire change column last_visited_at last_visited_date varchar(200) null; alter table enquire add column last_visited_time varchar(200) null; ALTER TABLE uploadedFile add column rule_id varchar(100); ALTER TABLE uploadedFile add column updateCount varchar(100); SELECT last_visited_date,last_visited_time,UNIX_TIMESTAMP( STR_TO_DATE(CONCAT(last_visited_date,' ',last_visited_time), '%Y-%m-%d %l:%i:%s%p')) as dt FROM enquire ORDER BY enquire_id desc SELECT visiting_time,UNIX_TIMESTAMP( STR_TO_DATE(CONCAT(visiting_date,' ',visiting_time), '%Y-%m-%d %l:%i:%s%p'))as dt FROM `setting_visitor_master` WHERE visiting_date = '2022-12-08' SELECT enquire_id,addDate,visiting_date,last_visited_date,last_visited_time,addTime,lead_Id from enquire INNER JOIN setting_visitor_master on enquire_id = lead_Id WHERE visiting_date = '2022-12-08' ALTER table Setting_ScheduleTemplate change column templateId template text; alter table Setting_ScheduleTemplate add column priority varchar(100); alter table Setting_ScheduleTemplate add column mapping_name varchar(255); ALTER TABLE `enquire` ADD `Remark` TEXT NOT NULL AFTER `last_visited_time`; alter table student add column registration_no varchar(200) null; ALTER TABLE student add column correspondence_address text null; ALTER TABLE student add column correspondence_state varchar(250) null; ALTER TABLE student add column correspondence_city varchar(250) null; ALTER TABLE student add column correspondence_pincode varchar(250) null; ALTER TABLE student add column counselor_enquire varchar(250) null; ALTER TABLE student add column counselor_admission varchar(250) null; ALTER TABLE student add column class_study_in varchar(250) null; ALTER TABLE student add column last_class_board varchar(250) null; ALTER TABLE student add column last_school varchar(250) null; ALTER TABLE student add column batch_starting_date varchar(250) null; ALTER TABLE student add column stream varchar(250) null; ALTER TABLE student add column institute_exam_overall_rank varchar(250) null; ALTER TABLE student add column institute_exam_district_rank varchar(250) null; ALTER TABLE student add column institute_exam_center varchar(250) null; ALTER TABLE student add column education_info text null; ALTER TABLE student add column other_education text null; ALTER TABLE student add column facility text null; ALTER TABLE student add column board varchar(250) null; ALTER TABLE student add column school_name text null; ALTER TABLE student add column admissionStage varchar(250) null; ALTER TABLE student add column source varchar(250) null; ALTER TABLE enquire add column Batch_Type varchar(150) null; ALTER TABLE enquire add column Student_Group varchar(150) null; ALTER TABLE enquire add column LotID varchar(150) null; ALTER TABLE enquire add column Counterno varchar(150) null; ALTER TABLE enquire add column SeminarDate varchar(150) null; ALTER TABLE enquire add column LastDate varchar(150) null; ALTER TABLE enquire add column GraseDate varchar(150) null; ALTER TABLE enquire add column LateFine varchar(150) null; ALTER TABLE enquire add column BatchStartingDate varchar(150) null; ALTER TABLE enquire add column PrintRemarks varchar(150) null; ALTER TABLE enquire add column OverAll_Rank varchar(150) null; ALTER TABLE enquire add column District_Rank text null; ALTER TABLE enquire add column EXAM_CENTRE text null; ALTER TABLE enquire add column Scholarship text null; ALTER TABLE enquire add column Max_Cash_on_Admn text null; ALTER TABLE enquire add column Hostel_Fee_payable text null; ALTER TABLE enquire add column Fee_Normal text null; ALTER TABLE enquire add column Fee_Extended text null; ALTER TABLE enquire add column Fee_LongTerm text null; ALTER TABLE uploadedFile add column fileSize text null; ALTER TABLE uploadedFile add column rule_id text null; ALTER TABLE uploadedFile add column Fee_LongTerm text null; ALTER TABLE enquire ROW_FORMAT=DYNAMIC; ALTER TABLE studentPlan add column payment_status text; insert into accountGroup(groupName,status,addBy,addDate) values("Bank Accounts","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Branch/Division","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Capital A/c","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Cash In Hand","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Closing Stock","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Current Assets","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Current Liabilities","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Deposits (Assets)","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Direct Expenses","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Direct Income","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Duties & Taxes","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Fixed Assets","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Indirect Expenses","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Indirect Income","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Investments","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Loan & Advances (Assets)","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Loan(liablity)","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Misc. Expenses (ASSET)","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Opening Stock","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Provisions","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Purchase A/c","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Reserves & Surplus","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Sales A/c","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Secured Loan","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Sundry Creditors","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Sundry Debtors","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Suspense A/c","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Unsecured Loan","Active","1",now()); insert into accountGroup(groupName,status,addBy,addDate) values("Receipt","Active","1",now()); alter table receiptDetail change ledgerId ledger_id varchar(255); alter table receiptDetail change courseId course_id varchar(255); alter table receiptDetail add column master_receipt_id varchar(200); alter table receiptDetail add column stu_uid varchar(200); alter table receiptDetail add column stu_adm_id varchar(200); ALTER TABLE users add column current_financial_id varchar(200); ALTER TABLE users add column client_id varchar(200); ALTER TABLE feeReceipt add column ip_address varchar(255); ALTER TABLE feeReceipt add column header_agent text; ALTER TABLE feeReceipt add column remark text; alter table feeReceipt change column fee_receipt_id receipt_id bigint unsigned auto_increment; alter table feeReceipt change column fee_receipt_id fee_receipt_id bigint unsigned auto_increment; alter table receiptDetail change lastReceiptCount lastReceiptCount varchar(200) alter table receiptDetail change receiptType receiptType varchar(200) alter table receiptDetail change receiptNo receiptNo varchar(200) alter table feeReceipt change column chequeNo chequeNo varchar(200); alter table feeReceipt change column payerBank payerBank varchar(200); alter table feeReceipt change column chequeDate chequeDate varchar(200); alter table feeReceipt change column onlineTranId onlineTranId varchar(200); ALTER TABLE studentPlan change payment_status installment_payment_status text; ALTER TABLE student add column financial_yr_id varchar(200) ALTER TABLE student_plan add column financial_yr_id varchar(200) ALTER TABLE student_plan add column client_id varchar(200) ALTER TABLE student add column client_id varchar(200) ALTER TABLE receiptDetail add column client_id varchar(200) ALTER TABLE feeReceipt add column client_id varchar(200) ALTER TABLE accountGroup add column client_id varchar(200) -- =======================client_id column is necessary in every table============================== -- ===================================================== select fee from studentPlan,JSON_TABLE(fee,'$[*]' COLUMNS( c1 VARCHAR(200) PATH '$.serial' ERROR ON ERROR)) AS jt SELECT * FROM JSON_TABLE( '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]', "$[1]" COLUMNS( xval VARCHAR(100) PATH "$.x", yval VARCHAR(100) PATH "$.y" ) ) AS jt1; select installment_no,installment_date ,stuId from fee, JSON_TABLE(fee,'$[*]' COLUMNS(installment_no varchar(100) PATH '$.serial', installment_date PATH '$.date' )) as FR WHERE installment_date alter table studentPlan add column is_enrollment_generated varchar(100); // $totalCount = DB::select(DB::raw("SELECT installment_no,installment_date,stuId FROM studentPlan ,JSON_TABLE(fee,'$[*]' COLUMNS(installment_no VARCHAR(100) PATH '$.serial',installment_date VARCHAR(100) PATH '$.date')) AS // jt1 where installment_date > '2023-02-03'")); // echo "
";

    // print_r($totalCount);
    // exit;
    date of admission
name
rollno
uid
course
branch
batch
batchType
batchStarting Date
Course actual fee
discount
payable amount
plan charge
tax
net + tax = final payable
paid
dues



ALTER TABLE cheque_detail add column settlement_entry_date varchar(200) null;
ALTER TABLE cheque_detail add column ledger_head_id varchar(200) null;
ALTER TABLE feeReceipt add column receipt_status varchar(200) null;

update page set pageRoute = 'dummy-dashboard' where page_id = 19;   // change in route name of dummy-dashboard




INSERT INTO edu_menu(label,parent_id,name,route,icon,sort_index)values
('menu.general','','General','','','1'),
('menu.setting','','Settings','','','2'),
('menu.student','','Student','','','3'),
('menu.user','','Users','','','4'),
('menu.lead','','Leads','','','5'),
('menu.acount','','Account','','','6'),
('menu.setting.account','2','Account','','','3'),
('menu.setting.academic','2','Academic','','','2'),
('menu.setting.lead','2','Lead','','','1'),
('menu.setting.lead.integration','9','Integration','apiList','','1'),
('menu.setting.lead.template','9','Template','smsTemplateList','','2'),
('menu.setting.lead.template_mapping','9','Template Mapping','scheduleTemplateList','','3'),
('menu.setting.lead.schedule_campaign','9','Schedule Campaign','scheduleCampaignList','','4'),
('menu.setting.lead.default_number','9','Default Number','defaultNumberList','','5'),
('menu.setting.lead.gallery','9','Gallery','imageList','','6'),
('menu.setting.lead.lead_score','9','Lead Score','leadFeedbackTypeList','','7'),
('menu.setting.lead.lead_category','9','Lead Category','leadStageList','','8'),
('menu.setting.academic.branch','8','Branch','branchList','','1'),
('menu.setting.academic.finance','8','Finance','financeList','','2'),
('menu.setting.academic.academic_year','8','Academic Year','academicList','','3'),
('menu.setting.academic.course','8','Course','courseList','','4'),
('menu.setting.academic.rollList','8','Roll','rollList','','5'),
('menu.setting.academic.uid','8','UID','uidList','','6'),
('menu.setting.academic.application_type','8','Application Type','applicationTypeList','','7'),
('menu.setting.academic.batch_type','8','Batch Type','batchTypeList','','8'),
('menu.setting.academic.batch','8','Batch','batchList','','9'),
('menu.setting.academic.discount_type','8','Discount Type','discountList','','10'),
('menu.setting.academic.document_type','8','Document Type','documentList','','11'),
('menu.setting.academic.package','8','Course Package','coursePackageList','','12'),
('menu.setting.account.receipt','7','Course Receipt','accountReceiptList','','1'),
('menu.setting.account.ledger_group','7','Account Group','groupList','','2'),
('menu.setting.account.ledger','7','Account Ledger','ledgerList','','3'),
('menu.account.bank_reconcil','6','Bank Reconcil','reconcilList','','1'),
('menu.lead.task','5','Task','taskList','TODO','1'),
('menu.lead.duplicate','5','Duplicate','duplicateList','','2'),
('menu.lead.daily_report','5','Daily Report','followupReport','','3'),
('menu.lead.leads','5','Leads','enquireList','','4'),
('menu.user.user','4','User','userList','','1'),
('menu.user.role','4','Role','roleList','','2'),
('menu.user.teams','4','Team','','','3')




mysql> ALTER TABLE followup add column talk_to varchar(255);


mysql> ALTER TABLE edu_task add column task_type varchar(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE edu_task add column task_priority varchar(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE edu_task add column task_reminder varchar(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE edu_task add column task_completed_at varchar(200);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


Notify me through