ALTER TABLE `bank_details` ADD `upi_id_number` VARCHAR( 100 ) NOT NULL ;
CREATE TABLE IF NOT EXISTS `upi_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tran_id` varchar(50) DEFAULT NULL,
  `value_date` date DEFAULT NULL,
  `transaction_date` date DEFAULT NULL,
  `posted_date` date DEFAULT NULL,
  `ref_no` varchar(50) DEFAULT NULL,
  `remarks` text,
  `withdraw_amt` decimal(10,2) DEFAULT NULL,
  `deposit_amt` decimal(10,2) DEFAULT NULL,
  `balance` decimal(10,2) DEFAULT NULL,
  `account_no` varchar(50) NOT NULL,
  `pacs_acc_no` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `upi_transactions_final` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tran_id` varchar(50) DEFAULT NULL,
  `value_date` date DEFAULT NULL,
  `transaction_date` date DEFAULT NULL,
  `posted_date` date DEFAULT NULL,
  `ref_no` varchar(50) DEFAULT NULL,
  `remarks` text,
  `withdraw_amt` decimal(10,2) DEFAULT NULL,
  `deposit_amt` decimal(10,2) DEFAULT NULL,
  `balance` decimal(10,2) DEFAULT NULL,
  `account_no` varchar(50) NOT NULL,
  `pacs_acc_no` varchar(20) NOT NULL,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tran_id` (`tran_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

DELIMITER $$

CREATE TRIGGER `before_insert_upi_transactions`
BEFORE INSERT ON `upi_transactions`
FOR EACH ROW
BEGIN
    -- Set pacs_acc_no before inserting
    DECLARE v_acc_no VARCHAR(50);

    -- Get acc_no from deposit_Acc based on the condition
    SELECT `acc_no` 
    INTO v_acc_no
    FROM `deposit_Acc` 
    WHERE RIGHT(`acc_no`, 9) = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.`remarks`, 'trnsf', -1), '/', 1))
    LIMIT 1;

    -- Update NEW.pacs_acc_no with the found acc_no
    IF v_acc_no IS NOT NULL THEN
        SET NEW.`pacs_acc_no` = v_acc_no;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER `after_insert_upi_transactions`
AFTER INSERT ON `upi_transactions`
FOR EACH ROW
BEGIN
    -- Insert into upi_transactions_final with status 'Pending'
    INSERT IGNORE INTO `upi_transactions_final`(`id`, `tran_id`, `value_date`, `transaction_date`, `posted_date`, `ref_no`, `remarks`, `withdraw_amt`, `deposit_amt`, `balance`, `account_no`, `pacs_acc_no`, `status`)
    VALUES (NEW.`id`, NEW.`tran_id`, NEW.`value_date`, NEW.`transaction_date`, NEW.`posted_date`, NEW.`ref_no`, NEW.`remarks`, NEW.`withdraw_amt`, NEW.`deposit_amt`, NEW.`balance`, NEW.`account_no`, NEW.`pacs_acc_no`, 'Pending');

    -- Delete the record from upi_transactions after inserting into upi_transactions_final
    DELETE FROM `upi_transactions` WHERE `id` = NEW.`id`;
END$$

DELIMITER ;



 -- DELETE FROM `upi_transactions` WHERE `id` = NEW.`id`;

DELIMITER $$
CREATE  PROCEDURE `Maintenance_Charges`()
BEGIN
  DECLARE trdt DATE;
  DECLARE trdt1 DATE;
  DECLARE amt, bal,chg DOUBLE;
  DECLARE accno, subhead, deperment1, lf_no1, monthdt VARCHAR(30);
  DECLARE mem_shcode, mem_hdcode VARCHAR(6);
  DECLARE vchno VARCHAR(200);
  DECLARE done INT DEFAULT FALSE;

  -- Cursor declaration
  DECLARE cur CURSOR FOR 
    SELECT d.acc_no, d.sub_head, d.deperment, d.lf_no
FROM deposit_acc d
JOIN amc_services_modular a 
WHERE a.business_type = 'Maintenance Charges'
  AND (d.acc_type = 'SV' OR d.acc_type = 'mcur' OR d.acc_type = 'other')
  AND NOT EXISTS (
    SELECT 1 
    FROM voucher v
    WHERE v.acc_no = d.acc_no
      AND v.date >= (DATE_ADD(a.provision_dt, INTERVAL 1 MONTH)) 
      AND v.date <= LAST_DAY(DATE_ADD(a.provision_dt, INTERVAL 12 MONTH))
  );
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- Disable the trigger
  UPDATE trigger_control SET is_enabled = 0 WHERE trigger_name = 'smsfacility';

  -- Fetch transaction date and month
  SELECT LAST_DAY(DATE_ADD(provision_dt, INTERVAL 12 MONTH)), (DATE_ADD(provision_dt, INTERVAL 12 MONTH)),
         DATE_FORMAT(DATE_ADD(provision_dt, INTERVAL 12 MONTH), "%M%y"),service_charge  
  INTO trdt,trdt1, monthdt,chg 
  FROM amc_services_modular 
  WHERE business_type = 'Maintenance Charges';

  SET done = FALSE;
  OPEN cur;

  -- Loop through the cursor
  cur_loop: LOOP
    FETCH cur INTO accno, subhead, deperment1, lf_no1;
    IF done THEN
      LEAVE cur_loop;
    END IF;

    SET vchno = CONCAT('MTN/', NOW());

    -- Fetch codes
    SELECT code, head INTO mem_shcode, mem_hdcode FROM sub_head WHERE code = subhead;

    -- Calculate balance
    SELECT (SUM(cr) - SUM(dr)) INTO bal FROM voucher WHERE acc_no = accno;

    -- Deduct SMS charges if sufficient balance
    IF bal >= chg THEN
      INSERT INTO withdrawl (acc_no, amt, date, Withdrawl_Type, slip_no) 
      VALUES (accno, chg, trdt, 'TRANSFER', vchno);

      INSERT INTO voucher (vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status, sub_head, head, user_code, D_Time, lfnof, deperment) 
      VALUES (vchno, trdt, CONCAT('MTN CHG-', monthdt), 'TRANSFER', chg, accno, '', '1', mem_shcode, mem_hdcode, 'system', now(), lf_no1, deperment1);

      INSERT INTO voucher (vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, sub_head, head, user_code, D_Time, lfnof, deperment) 
      VALUES (vchno, trdt, CONCAT('MTN CHG-', monthdt), 'TRANSFER', chg, '', accno, '1', '57913', '57900', 'system', now(), lf_no1, deperment1);
    END IF;

    -- Update provision date
    UPDATE amc_services_modular 
    SET provision_dt = trdt1, pay_dt = trdt 
    WHERE business_type = 'Maintenance Charges';

  END LOOP;

  CLOSE cur;

  -- Re-enable the trigger
  UPDATE trigger_control SET is_enabled = 1 WHERE trigger_name = 'smsfacility';

END$$
DELIMITER ;

ALTER TABLE `synopsis_loan` ADD `tolmem` INT NOT NULL ;
ALTER TABLE `synopsis` ADD `tolmem` INT NOT NULL ;
ALTER TABLE `int_payble` ADD `tolmem` INT NOT NULL ;

ALTER  VIEW `synop_details` AS select `synopsis`.`no` AS `no`,1 AS `slno`,1 AS `sl_no`,'Paid Up Share-Capital' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,0 AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00001' AS `sub_head`,0 AS `pay_re`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '1') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,2 AS `slno`,2 AS `sl_no`,'Deposit' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,0 AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00002' AS `sub_head`,0 AS `pay_re`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '2') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,3 AS `slno`,3 AS `sl_no`,'Borrowings From C.C.B Ltd.' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,'' AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00003' AS `sub_head`,0 AS `pay_rec`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '3') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,4 AS `slno`,4 AS `sl_no`,'Bank Deposits & Investments' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,0 AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00004' AS `sub_head`,0 AS `pay_rec`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '4') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,5 AS `slno`,5 AS `sl_no`,'Share Investment' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,0 AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00005' AS `sub_head`,0 AS `pay_rec`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '5') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,6 AS `slno`,6 AS `sl_no`,'Loan and Advances To Members' AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,0 AS `Descrepancy`,0 AS `OverduePrin`,0 AS `Interest`,0 AS `OverdueInt`,'00006' AS `sub_head`,0 AS `pay_re`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` where (`synopsis`.`sl_no` = '6') group by `synopsis`.`sl_no` union all select `synopsis`.`no` AS `no`,'' AS `slno`,`synopsis`.`sl_no` AS `sl_no`,concat(' ',`synopsis`.`account`) AS `account`,sum(`synopsis`.`BalanceSheetFigure`) AS `BalanceSheetFigure`,sum(`synopsis`.`DetailList`) AS `DetailList`,round((sum(`synopsis`.`BalanceSheetFigure`) - sum(`synopsis`.`DetailList`)),2) AS `Descrepancy`,sum(`synopsis`.`OverduePrin`) AS `OverduePrin`,sum(`synopsis`.`Interest`) AS `Interest`,sum(`synopsis`.`OverdueInt`) AS `OverdueInt`,`synopsis`.`sub_head` AS `sub_head`,sum(`synopsis`.`pay_rec`) AS `pay_rec`,sum(`synopsis`.`tolmem`) AS `tolmem` from `synopsis` group by `synopsis`.`sub_head`;


ALTER  VIEW `sofdetails` AS (select `synop_details`.`no` AS `no`,`synop_details`.`slno` AS `slno`,`synop_details`.`sl_no` AS `sl_no`,concat(`synop_details`.`sub_head`,' | ',`synop_details`.`account`) AS `account`,`synop_details`.`BalanceSheetFigure` AS `BalanceSheetFigure`,`synop_details`.`DetailList` AS `DetailList`,if((sum(`synop_details`.`BalanceSheetFigure`) > sum(`synop_details`.`DetailList`)),concat('[-] ',(sum(`synop_details`.`BalanceSheetFigure`) - sum(`synop_details`.`DetailList`))),concat('[+] ',(sum(`synop_details`.`DetailList`) - sum(`synop_details`.`BalanceSheetFigure`)))) AS `Descrepancy`,`synop_details`.`OverduePrin` AS `OverduePrin`,`synop_details`.`Interest` AS `Interest`,`synop_details`.`OverdueInt` AS `OverdueInt`,`synop_details`.`sub_head` AS `sub_head`,`synop_details`.`pay_re` AS `pay_rec`,tolmem from `synop_details` where ((`synop_details`.`sl_no` = 1) or (`synop_details`.`sl_no` = 2) or (`synop_details`.`sl_no` = 3)) group by `synop_details`.`sub_head` order by `synop_details`.`sl_no`,`synop_details`.`sub_head`);

ALTER  VIEW `sofdetails1` AS (select `synop_details`.`no` AS `no`,`synop_details`.`slno` AS `slno`,`synop_details`.`sl_no` AS `sl_no`,concat(`synop_details`.`sub_head`,' | ',`synop_details`.`account`) AS `account`,`synop_details`.`BalanceSheetFigure` AS `BalanceSheetFigure`,`synop_details`.`DetailList` AS `DetailList`,if((sum(`synop_details`.`BalanceSheetFigure`) > sum(`synop_details`.`DetailList`)),concat('[-] ',(sum(`synop_details`.`BalanceSheetFigure`) - sum(`synop_details`.`DetailList`))),concat('[+] ',(sum(`synop_details`.`DetailList`) - sum(`synop_details`.`BalanceSheetFigure`)))) AS `Descrepancy`,`synop_details`.`OverduePrin` AS `OverduePrin`,`synop_details`.`Interest` AS `Interest`,`synop_details`.`OverdueInt` AS `OverdueInt`,`synop_details`.`sub_head` AS `sub_head`,`synop_details`.`pay_re` AS `pay_rec`,`synop_details`.`tolmem` AS `tolmem` from `synop_details` where (`synop_details`.`slno` = 4) group by `synop_details`.`sub_head`) union (select `synop_details`.`no` AS `no`,`synop_details`.`slno` AS `slno`,`synop_details`.`sl_no` AS `sl_no`,concat(`synop_details`.`sub_head`,' | ',`synop_details`.`account`) AS `account`,`synop_details`.`BalanceSheetFigure` AS `BalanceSheetFigure`,`synop_details`.`DetailList` AS `DetailList`,if((sum(`synop_details`.`BalanceSheetFigure`) > sum(`synop_details`.`DetailList`)),concat('[-] ',(sum(`synop_details`.`BalanceSheetFigure`) - sum(`synop_details`.`DetailList`))),concat('[+] ',(sum(`synop_details`.`DetailList`) - sum(`synop_details`.`BalanceSheetFigure`)))) AS `Descrepancy`,`synop_details`.`OverduePrin` AS `OverduePrin`,`synop_details`.`Interest` AS `Interest`,`synop_details`.`OverdueInt` AS `OverdueInt`,`synop_details`.`sub_head` AS `sub_head`,`synop_details`.`pay_re` AS `pay_rec`,`synop_details`.`tolmem` AS `tolmem` from `synop_details` where ((`synop_details`.`sl_no` = 4) and (`synop_details`.`slno` <> 4) and (`synop_details`.`sub_head` like '28%')) group by `synop_details`.`sub_head` order by `sl_no`,`sub_head`) union (select `synop_details`.`no` AS `no`,`synop_details`.`slno` AS `slno`,`synop_details`.`sl_no` AS `sl_no`,concat(`synop_details`.`sub_head`,' | ',`synop_details`.`account`) AS `account`,`synop_details`.`BalanceSheetFigure` AS `BalanceSheetFigure`,`synop_details`.`DetailList` AS `DetailList`,if((sum(`synop_details`.`BalanceSheetFigure`) > sum(`synop_details`.`DetailList`)),concat('[-] ',(sum(`synop_details`.`BalanceSheetFigure`) - sum(`synop_details`.`DetailList`))),concat('[+] ',(sum(`synop_details`.`DetailList`) - sum(`synop_details`.`BalanceSheetFigure`)))) AS `Descrepancy`,`synop_details`.`OverduePrin` AS `OverduePrin`,`synop_details`.`Interest` AS `Interest`,`synop_details`.`OverdueInt` AS `OverdueInt`,`synop_details`.`sub_head` AS `sub_head`,`synop_details`.`pay_re` AS `pay_rec`,`synop_details`.`tolmem` AS `tolmem` from `synop_details` where ((`synop_details`.`sl_no` = 4) and (`synop_details`.`slno` <> 4) and (not((`synop_details`.`sub_head` like '28%')))) group by `synop_details`.`sub_head` order by `synop_details`.`sl_no`,`sub_head`);

ALTER  VIEW `sofdetails2` AS (select `synop_details`.`no` AS `no`,`synop_details`.`slno` AS `slno`,`synop_details`.`sl_no` AS `sl_no`,concat(`synop_details`.`sub_head`,' | ',`synop_details`.`account`) AS `account`,`synop_details`.`BalanceSheetFigure` AS `BalanceSheetFigure`,`synop_details`.`DetailList` AS `DetailList`,if((sum(`synop_details`.`BalanceSheetFigure`) > sum(`synop_details`.`DetailList`)),concat('[-] ',(sum(`synop_details`.`BalanceSheetFigure`) - sum(`synop_details`.`DetailList`))),concat('[+] ',(sum(`synop_details`.`DetailList`) - sum(`synop_details`.`BalanceSheetFigure`)))) AS `Descrepancy`,`synop_details`.`OverduePrin` AS `OverduePrin`,`synop_details`.`Interest` AS `Interest`,`synop_details`.`OverdueInt` AS `OverdueInt`,`synop_details`.`sub_head` AS `sub_head`,`synop_details`.`pay_re` AS `pay_rec`,tolmem from `synop_details` where ((`synop_details`.`sl_no` = 5) or (`synop_details`.`sl_no` = 6)) group by `synop_details`.`sub_head` order by `synop_details`.`sl_no`,`synop_details`.`sub_head`);

ALTER  VIEW `j_a2` AS select `applicant`.`no` AS `no`,`applicant`.`code` AS `code`,`applicant`.`initial` AS `initial`,`applicant`.`fname` AS `fname`,`applicant`.`mname` AS `mname`,`applicant`.`lname` AS `lname`,`applicant`.`father_name` AS `father_name`,`applicant`.`gender` AS `gender`,`applicant`.`dob` AS `dob`,`applicant`.`address` AS `address`,`applicant`.`city` AS `city`,`applicant`.`state` AS `state`,`applicant`.`pin` AS `pin`,`applicant`.`mobile` AS `mobile`,`applicant`.`pan` AS `pan`,`applicant`.`adhar` AS `adhar`,`applicant`.`voter` AS `voter`,`applicant`.`occupation` AS `occupation`,`applicant`.`income` AS `income`,`applicant`.`verify` AS `verify`,`applicant`.`Acc_No` AS `Acc_No`,spno from `applicant`,deposit_acc dc where dc.acc_no=applicant.acc_no group by `applicant`.`Acc_No`;

ALTER TABLE `saving_information` ADD `eft` VARCHAR( 30 ) NOT NULL ;

ALTER TABLE saving_information ADD voter VARCHAR( 30 ) NOT NULL ;

 create view loan_depo_security as SELECT l.acc_no,sum(n.amt) as amt,l.sub_head FROM `nsc_detail` n, loan_acc l WHERE l.acc_no=n.acc_no and l.sub_head='23109' group by n.acc_no
 union all SELECT l.acc_no,sum(d.amt) as amt,l.sub_head FROM `loan_acc` l, deposit_acc d WHERE l.acc_no=d.loan_acc_no and l.sub_head='23115' group by l.acc_no ;

CREATE TABLE IF NOT EXISTS `salary_summary` (
  `acc_no` varchar(20) DEFAULT NULL,
  `basic` double(20,2) DEFAULT NULL,
  `pf` double(20,2) DEFAULT NULL,
  `esic` double(20,2) DEFAULT NULL,
  `da` double(20,2) DEFAULT NULL,
  `hra` double(20,2) DEFAULT NULL,
  `minc` double(20,2) DEFAULT NULL,
  `ptax` double(20,2) DEFAULT NULL,
  `net_salary` double(20,2) DEFAULT NULL,
  `salary_date` date DEFAULT NULL,
  `no` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP PROCEDURE IF EXISTS `autosalarypf`;

DELIMITER $$

CREATE PROCEDURE `autosalarypf`()
    NO SQL
BEGIN

    DECLARE sb VARCHAR(20);
    DECLARE basicamt, pf1, esic, da1, minc1, ptax1, hra1, netsalary,netsalary1 DOUBLE(20,2);
    DECLARE refno, row, maxid INT(250);
    DECLARE vchno VARCHAR(250);
    DECLARE chkdt DATE;
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR 
        SELECT 
            `basic`,
            ROUND((SUM(basic) * (pf)) / 100) AS pfamt,
            ROUND((SUM(basic) * (oth)) / 100) AS esi,
            `savingacc`,
            ROUND((SUM(basic) * (da)) / 100) AS da,
            minc,
            ptax,
            ROUND((SUM(basic) * (hra)) / 100) AS hra
        FROM emp_dtl 
        WHERE savingacc <> '' 
          AND lastpaydt <> DATE_FORMAT(NOW(), '%Y-%m-%d') 
        GROUP BY savingacc;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    cur_loop: LOOP
        FETCH cur INTO basicamt, pf1, esic, sb, da1, minc1, ptax1, hra1;
        IF done THEN
            LEAVE cur_loop;
        END IF;

        -- ✅ Prevent salary generation if already paid this month
        IF EXISTS (
            SELECT 1 FROM salary_summary 
            WHERE acc_no = sb 
              AND MONTH(salary_date) = MONTH(CURDATE()) 
              AND YEAR(salary_date) = YEAR(CURDATE())
        ) THEN
            ITERATE cur_loop;
        END IF;

        SET vchno = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s');
        SET netsalary = (basicamt + hra1 + da1 + esic + minc1 + ptax1 ) - (pf1 );
		SET netsalary1 = (basicamt + hra1 + da1  + esic + minc1 + ptax1);
        -- PF
        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('PF', vchno), NOW(), 'EPF', 'TRANSFER', pf1, '', sb, '1', '17100', '17111', 'System', NOW());

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SPF', vchno), NOW(), 'SPF', 'TRANSFER', pf1, '', sb, '1', '17100', '17103', 'System', NOW());

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SPF', vchno), NOW(), 'SPF', 'TRANSFER', pf1, '', sb, '1', '63100', '63106', 'System', NOW());

        -- Net Salary
        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SLY', vchno), NOW(), 'SALARY', 'TRANSFER', netsalary1, '', sb, '1', '63100', '63102', 'System', NOW());

        INSERT INTO deposit(Acc_no, Amt, Total_amt, Deposit_Type, cheque_no, Date, shilp) 
        VALUES (sb, netsalary, netsalary, 'SALARY', '', NOW(), CONCAT('SLY', vchno));

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time) 
        VALUES (CONCAT('SLY', vchno), NOW(), 'SALARY', 'TRANSFER', netsalary, sb, '', '1', '14100', '14101', 'System', NOW());

        -- Update last payment date
        UPDATE emp_dtl 
        SET lastpaydt = DATE_FORMAT(NOW(), '%Y-%m-%d') 
        WHERE savingacc = sb;

        -- Insert Salary Summary
        INSERT INTO salary_summary(acc_no, basic, pf, esic, da, hra, minc, ptax, net_salary, salary_date)
        VALUES (sb, basicamt, pf1, esic, da1, hra1, minc1, ptax1, netsalary, CURDATE());

    END LOOP cur_loop;

    CLOSE cur;

END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS sms_charge_log (
  acc_no VARCHAR(30) PRIMARY KEY,
  charge_date DATE,
  month_label VARCHAR(20)
);

DROP PROCEDURE IF EXISTS `sms_charge`;
DELIMITER $$

CREATE PROCEDURE `sms_charge`()
BEGIN
  -- Variable declarations
  DECLARE trdt DATE;
  DECLARE amt, bal DOUBLE;
  DECLARE accno, subhead, deperment1, lf_no1, monthdt VARCHAR(30);
  DECLARE mem_shcode, mem_hdcode VARCHAR(6);
  DECLARE vchno VARCHAR(200);
  DECLARE txn_count INT DEFAULT 0;
  DECLARE done INT DEFAULT FALSE;

  -- Cursor
  DECLARE cur CURSOR FOR 
    SELECT d.acc_no, d.sub_head, d.deperment, d.lf_no
    FROM deposit_acc d
    JOIN amc_services_modular a ON 1=1
    WHERE a.business_type = 'SMS facility'
      AND NOW() >= LAST_DAY(DATE_ADD(a.provision_dt, INTERVAL 3 MONTH))
      AND DATE_ADD(d.date_open, INTERVAL 3 MONTH) <= LAST_DAY(DATE_ADD(a.provision_dt, INTERVAL 3 MONTH))
      AND d.smsfacility = 'YES'
      AND d.acc_type IN ('SV', 'mcur', 'other')
      AND d.acc_no NOT IN (
          SELECT acc_no FROM sms_charge_log
          WHERE month_label = DATE_FORMAT(DATE_ADD(a.provision_dt, INTERVAL 3 MONTH), "%M%y")
      );

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- Step 1: Disable trigger
  UPDATE trigger_control SET is_enabled = 0 WHERE trigger_name = 'smsfacility';

  -- Step 2: Calculate current charge month
  SELECT LAST_DAY(DATE_ADD(provision_dt, INTERVAL 3 MONTH)),
         DATE_FORMAT(DATE_ADD(provision_dt, INTERVAL 3 MONTH), "%M%y")
    INTO trdt, monthdt
    FROM amc_services_modular 
    WHERE business_type = 'SMS facility'
    LIMIT 1;

  -- Step 3: Open cursor
  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO accno, subhead, deperment1, lf_no1;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- Voucher no.
    SET vchno = CONCAT('SMS/', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '/', accno);

    -- Subhead
    SELECT code, head INTO mem_shcode, mem_hdcode FROM sub_head WHERE code = subhead;

    -- Balance
    SELECT IFNULL(SUM(cr), 0) - IFNULL(SUM(dr), 0) INTO bal FROM voucher WHERE acc_no = accno;

    -- Deduct if balance is enough
    IF bal >= 12 THEN
      -- Withdraw
      INSERT INTO withdrawl (acc_no, amt, date, Withdrawl_Type, slip_no)
      VALUES (accno, 12, NOW(), 'TRANSFER', vchno);

      -- Debit from user
      INSERT INTO voucher (vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status,
        sub_head, head, user_code, D_Time, lfnof, deperment)
      VALUES (vchno, NOW(), CONCAT('SMS CHG-', monthdt), 'TRANSFER', 12, accno, '', '1',
        mem_shcode, mem_hdcode, 'system', NOW(), lf_no1, deperment1);

      -- Credit to income
      INSERT INTO voucher (vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status,
        sub_head, head, user_code, D_Time, lfnof, deperment)
      VALUES (vchno, NOW(), CONCAT('SMS CHG-', monthdt), 'TRANSFER', 12, '', accno, '1',
        '57936', '57900', 'system', NOW(), lf_no1, deperment1);

      -- Mark success
      INSERT INTO sms_charge_log (acc_no, charge_date, month_label)
      VALUES (accno, NOW(), monthdt);

      -- Count
      SET txn_count = txn_count + 1;
    END IF;
  END LOOP;

  CLOSE cur;

  -- Step 4: Only update provision_dt if at least 1 success
  IF txn_count > 0 THEN
    UPDATE amc_services_modular 
    SET provision_dt = trdt 
    WHERE business_type = 'SMS facility';
  END IF;

  -- Step 5: Enable trigger back
  UPDATE trigger_control SET is_enabled = 1 WHERE trigger_name = 'smsfacility';
END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS `sms_charge_log` (
  `charge_date` date DEFAULT NULL,
  `month_label` varchar(20) DEFAULT NULL,
  `acc_no` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `sms_charge_log` ADD `acc_no1` VARCHAR( 30 ) NOT NULL ;
update `sms_charge_log` set acc_no1=acc_no WHERE 1;
ALTER TABLE `sms_charge_log` DROP `acc_no` ;
ALTER TABLE `sms_charge_log` CHANGE `acc_no1` `acc_no` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;
INSERT INTO sub_head (code, sub_head, Type, head, CASH, SL_NO, MARGE, loan_type, GRACE, acc_code, sector_code, bank, BRANCH, id) VALUES ('57946', 'TCS REFUND', '', '57900', 'CASH', '0', '953', '', '0', '', '', '', '', '');

INSERT INTO `sub_head` (`code`, `sub_head`, `Type`, `head`, `CASH`, `SL_NO`, `MARGE`, `loan_type`, `GRACE`, `acc_code`, `sector_code`, `bank`, `BRANCH`, `id`) VALUES ('18814', 'SUSHIL DHARA INSURANCE', 'CASH', '18800', 'CASH', '0', '225', '', '0', '', '', '', '', NULL);

ALTER  VIEW `j_a` AS select `applicant`.`no` AS `no`,`applicant`.`code` AS `code`,`applicant`.`initial` AS `initial`,`applicant`.`fname` AS `fname`,`applicant`.`mname` AS `mname`,`applicant`.`lname` AS `lname`,`applicant`.`father_name` AS `father_name`,`applicant`.`gender` AS `gender`,`applicant`.`dob` AS `dob`,`applicant`.`address` AS `address`,`applicant`.`city` AS `city`,`applicant`.`state` AS `state`,`applicant`.`pin` AS `pin`,`applicant`.`mobile` AS `mobile`,`applicant`.`pan` AS `pan`,`applicant`.`adhar` AS `adhar`,`applicant`.`voter` AS `voter`,`applicant`.`occupation` AS `occupation`,`applicant`.`income` AS `income`,`applicant`.`verify` AS `verify`,`applicant`.`cust` AS `cust`,`applicant`.`Acc_No` AS `Acc_No`,concat(`applicant`.`initial`,' ',`applicant`.`fname`,' ',`applicant`.`mname`,' ',`applicant`.`lname`) AS `name`,`dc`.`lf_no` AS `lf_no`,`dc`.`status` AS `status`,`dc`.`bankacc` AS `bankacc`,`dc`.`block` AS `block`,`dc`.`groupno` AS `groupno`,`dc`.`sub_head` AS `sub_head`,(select head from sub_head s where dc.sub_head=s.code) as head,dc.date_open from (`applicant` join `deposit_acc` `dc`) where (`applicant`.`code` = `dc`.`app_code`) group by `applicant`.`Acc_No` union all select `applicant`.`no` AS `no`,`applicant`.`code` AS `code`,`applicant`.`initial` AS `initial`,`applicant`.`fname` AS `fname`,`applicant`.`mname` AS `mname`,`applicant`.`lname` AS `lname`,`applicant`.`father_name` AS `father_name`,`applicant`.`gender` AS `gender`,`applicant`.`dob` AS `dob`,`applicant`.`address` AS `address`,`applicant`.`city` AS `city`,`applicant`.`state` AS `state`,`applicant`.`pin` AS `pin`,`applicant`.`mobile` AS `mobile`,`applicant`.`pan` AS `pan`,`applicant`.`adhar` AS `adhar`,`applicant`.`voter` AS `voter`,`applicant`.`occupation` AS `occupation`,`applicant`.`income` AS `income`,`applicant`.`verify` AS `verify`,`applicant`.`cust` AS `cust`,`applicant`.`Acc_No` AS `Acc_No`,concat(`applicant`.`initial`,' ',`applicant`.`fname`,' ',`applicant`.`mname`,' ',`applicant`.`lname`) AS `name`,'' AS `lf_no`,`dc`.`status` AS `status`,'' AS `bankacc`,'' AS `block`,'' AS `groupno`,`dc`.`sub_head` AS `sub_head`,dc.date_open,(select head from sub_head s where dc.sub_head=s.code) as head from (`applicant` USE INDEX (`by_acc_no`) join `mstr_bnk_acc` `dc` USE INDEX (`by_acc_no`)) where (`applicant`.`Acc_No` = `dc`.`acc_no`) group by `applicant`.`Acc_No`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_crop_loan_process`$$

CREATE PROCEDURE `sp_crop_loan_process`(
    IN p_action VARCHAR(20),
    IN p_vch_prefix VARCHAR(50)
)
BEGIN
    -- ─── ACTION = 'UPLOAD' → Post-process after bulk insert ───
    IF p_action = 'UPLOAD' THEN
        
        -- Update acc_no from deposit_acc where bank_acc matched
        UPDATE `crop_depo` C 
        INNER JOIN DEPOSIT_ACC D ON D.BANKACC = C.BANK_ACC 
        SET C.ACC_NO = D.ACC_NO 
        WHERE C.acc_no = '';
        
        -- Update vchno with prefix + auto no
        UPDATE `crop_depo` SET vchno = CONCAT(p_vch_prefix, no);
        
        -- Update bank_acc from deposit_acc where acc_no matched
        UPDATE `crop_depo` C 
        INNER JOIN DEPOSIT_ACC D ON C.ACC_NO = D.ACC_NO 
        SET C.BANK_ACC = D.BANKACC 
        WHERE C.BANK_ACC = '';
    
    -- ─── ACTION = 'SUBVENTION' → Insert Voucher + Deposit ───
    ELSEIF p_action = 'SUBVENTION' THEN
        
        -- Insert into voucher
        INSERT INTO `voucher`(vch_no, date, vch_type, deposit_type, cr, acc_no, 
            cash_acc, status, head, sub_head, user_code, D_time, transtion_by, lfnof, deperment) 
        SELECT vchno, NOW(), p_vch_prefix, 'TRANSFER', c.amt, c.acc_no, 
            '', '', head, sub_head, 'system', NOW(), '', '', 'MainBranch' 
        FROM crop_depo c 
        INNER JOIN j_a h ON h.acc_no = c.acc_no 
        WHERE c.acc_no <> '';
        
        -- Insert into deposit
        INSERT INTO `deposit`(Acc_no, Amt, Total_amt, Deposit_Type, cheque_no, Date, shilp) 
        SELECT acc_no, amt, amt, 'TRANSFER', '', NOW(), vchno 
        FROM crop_depo 
        WHERE acc_no <> '';
    
    END IF;
    
END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS `autosalarypf`;

DELIMITER $$

CREATE DEFINER=`admin`@`localhost` PROCEDURE `autosalarypf`()
    NO SQL
BEGIN
    DECLARE sb VARCHAR(20);
    DECLARE basicamt, pf1, esic, da1, minc1, ptax1, hra1, netsalary, netsalary1 DOUBLE(20,2);
    DECLARE refno, row1, maxid INT;
    DECLARE vchno VARCHAR(250);
    DECLARE chkdt DATE;
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE cur CURSOR FOR 
        SELECT 
            `basic`,
            ROUND((`basic` * `pf`) / 100) AS pfamt,
            ROUND((`basic` * `oth`) / 100) AS esi,
            `savingacc`,
            ROUND((`basic` * `da`) / 100) AS da,
            `minc`,
            `ptax`,
            ROUND((`basic` * `hra`) / 100) AS hra
        FROM emp_dtl 
        WHERE savingacc <> '' 
          AND savingacc IS NOT NULL
          AND lastpaydt <> DATE_FORMAT(NOW(), '%Y-%m-%d')
          AND lastpaydt < DATE_FORMAT(NOW(), '%Y-%m-01');
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    cur_loop: LOOP
        FETCH cur INTO basicamt, pf1, esic, sb, da1, minc1, ptax1, hra1;
        IF done THEN
            LEAVE cur_loop;
        END IF;

        -- ✅ Prevent salary generation if already paid this month
        IF EXISTS (
            SELECT 1 FROM salary_summary 
            WHERE acc_no = sb 
              AND MONTH(salary_date) = MONTH(CURDATE()) 
              AND YEAR(salary_date) = YEAR(CURDATE())
        ) THEN
            ITERATE cur_loop;
        END IF;

        SET vchno = DATE_FORMAT(NOW(), '%Y%m%d%H%i%s');
        SET netsalary1 = (basicamt + hra1 + da1 + minc1);
        SET netsalary = netsalary1 - (pf1 + esic + ptax1);

        -- PF
        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('PF', vchno), NOW(), 'EPF', 'TRANSFER', pf1, '', sb, '1', '17100', '17111', 'System', NOW());

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SPF', vchno), NOW(), 'SPF', 'TRANSFER', pf1, '', sb, '1', '17100', '17103', 'System', NOW());

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SPF', vchno), NOW(), 'SPF', 'TRANSFER', pf1, '', sb, '1', '63100', '63106', 'System', NOW());

        -- Net Salary
        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, dr, acc_no, cash_acc, status, head, sub_head, user_code, D_time)  
        VALUES (CONCAT('SLY', vchno), NOW(), 'SALARY', 'TRANSFER', netsalary1, '', sb, '1', '63100', '63102', 'System', NOW());

        INSERT INTO deposit(Acc_no, Amt, Total_amt, Deposit_Type, cheque_no, Date, shilp) 
        VALUES (sb, netsalary, netsalary, 'SALARY', '', NOW(), CONCAT('SLY', vchno));

        INSERT INTO voucher(vch_no, date, vch_type, deposit_type, cr, acc_no, cash_acc, status, head, sub_head, user_code, D_time) 
        VALUES (CONCAT('SLY', vchno), NOW(), 'SALARY', 'TRANSFER', netsalary, sb, '', '1', '14100', '14101', 'System', NOW());

        -- Update last payment date
        UPDATE emp_dtl 
        SET lastpaydt = DATE_FORMAT(NOW(), '%Y-%m-%d') 
        WHERE savingacc = sb;

        -- Insert Salary Summary
        INSERT INTO salary_summary(acc_no, basic, pf, esic, da, hra, minc, ptax, net_salary, salary_date)
        VALUES (sb, basicamt, pf1, esic, da1, hra1, minc1, ptax1, netsalary, CURDATE());

    END LOOP cur_loop;
    CLOSE cur;
END$$

DELIMITER ;