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 "<pre>";

    // 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','','<iclass="fafa-bankiconSize"></i>','3'),
('menu.setting.academic','2','Academic','','<iclass="fafa-mortar-boardiconSize"></i>','2'),
('menu.setting.lead','2','Lead','','<iclass="icofonticofont-support"></i>','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','<iclass="icofonticofont-support"></i>','1'),
('menu.lead.task','5','Task','taskList','TODO','1'),
('menu.lead.duplicate','5','Duplicate','duplicateList','<iclass="icofonticofont-group-students"></i>','2'),
('menu.lead.daily_report','5','Daily Report','followupReport','<iclass="icofonticofont-support"></i>','3'),
('menu.lead.leads','5','Leads','enquireList','<iclass="icofonticofont-support"></i>','4'),
('menu.user.user','4','User','userList','<iclass="icofonticofont-support"></i>','1'),
('menu.user.role','4','Role','roleList','<iclass="icofonticofont-support"></i>','2'),
('menu.user.teams','4','Team','','<iclass="icofonticofont-support"></i>','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
