Skip to content
Advertisement

How to update my db automatically every day by inserting new record using existing data using event

I have a table ‘accounts’. Every day I need to insert automatically new record for all users as opening balance (flag = OP). Opening balance amount is the previous day’s last entry’s cash in hand amount.

How can I write an event for this?

This is my table:

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL,
  `client_id` int(11) DEFAULT NULL,
  `flag` enum('credit','debit','op','bank') DEFAULT NULL,
  `department_fee` decimal(14,2) DEFAULT NULL,
  `service_charges` decimal(14,2) DEFAULT NULL,
  `gst` decimal(14,2) DEFAULT NULL,
  `bank_charges` decimal(14,2) DEFAULT NULL,
  `amount` decimal(14,2) DEFAULT NULL,
  `cashin_hand` decimal(14,2) DEFAULT NULL,
  `entry_date` date DEFAULT NULL,
  `created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

data

INSERT INTO `accounts` (`id`, `client_id`, `flag`, `department_fee`, `service_charges`, `gst`, `bank_charges`, `amount`, `cashin_hand`, `entry_date`, `created`) VALUES
(1, 1, 'credit', '500.00', '0.00', NULL, NULL, '500.00', '500.00', '2018-12-01', '2018-12-01 05:02:56'),
(2, 1, 'credit', '20.00', '180.00', NULL, NULL, '200.00', '700.00', '2018-12-01', '2018-12-01 05:03:13'),
(3, 1, 'debit', NULL, NULL, NULL, NULL, '300.00', '400.00', '2018-12-01', '2018-12-01 05:03:50'),
(4, 1, 'credit', '220.00', '80.00', NULL, NULL, '300.00', '700.00', '2018-12-01', '2018-12-01 05:56:57'),
(9, 1, 'op', NULL, NULL, NULL, NULL, '100.00', NULL, '2018-10-31', '2018-10-31 04:06:18'),
(10, 1, 'op', NULL, NULL, NULL, NULL, '500.00', NULL, '2018-10-31', '2018-10-31 04:06:47'),
(12, 1, 'op', NULL, NULL, NULL, NULL, '700.00', NULL, '2018-10-31', '2018-10-31 06:19:29'),
(13, 1, 'op', NULL, NULL, NULL, NULL, '250.00', NULL, '2018-10-31', '2018-10-31 06:23:21'),
(14, 1, 'debit', NULL, NULL, NULL, NULL, '570.00', NULL, '2018-10-31', '2018-10-31 06:24:21'),
(15, 1, 'credit', '23.00', '10.00', '22.00', '5.00', '60.00', NULL, '2018-10-31', '2018-10-31 07:03:00'),
(16, 1, 'credit', '20.00', '180.00', NULL, NULL, '200.00', NULL, '2018-11-30', '2018-11-30 07:39:40'),
(17, 1, 'credit', '30.00', '70.00', NULL, NULL, '100.00', NULL, '2018-11-30', '2018-11-30 08:24:08'),
(18, 1, 'debit', NULL, NULL, NULL, NULL, '100.00', NULL, '2018-11-30', '2018-11-30 08:25:58'),
(19, 1, 'credit', '500.00', '10.00', NULL, NULL, '510.00', '510.00', '2018-12-05', '2018-12-05 06:26:58'),
(20, 1, 'op', NULL, NULL, NULL, NULL, '510.00', '510.00', '2019-01-05', '2019-01-05 12:17:33'),
(22, 1, 'op', NULL, NULL, NULL, NULL, '510.00', '510.00', '2019-01-07', '2019-01-07 12:24:57'),
(23, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '30.00', '2020-09-26', '2020-09-26 14:42:30'),
(24, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '90.00', '2020-09-26', '2020-09-26 14:43:07'),
(25, 286, 'debit', NULL, NULL, NULL, NULL, '3500.00', '-3410.00', '2020-09-26', '2020-09-26 14:43:18'),
(26, 286, 'credit', '2942.00', '0.00', NULL, NULL, '2942.00', '-468.00', '2020-09-26', '2020-09-26 14:43:35'),
(27, 286, 'credit', '1994.00', '30.00', NULL, NULL, '2024.00', '1556.00', '2020-09-26', '2020-09-26 14:47:07'),
(28, NULL, 'credit', '1004.00', '25.00', NULL, NULL, '1029.00', '1029.00', '2020-09-26', '2020-09-26 11:45:20'),
(29, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1596.00', '2020-09-26', '2020-09-26 16:42:19'),
(30, 286, 'credit', '1304.00', '25.00', NULL, NULL, '1329.00', '2925.00', '2020-09-26', '2020-09-26 16:56:51'),
(31, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '2975.00', '2020-09-26', '2020-09-26 17:04:26'),
(32, 286, 'credit', '480.00', '50.00', NULL, NULL, '530.00', '3505.00', '2020-09-26', '2020-09-26 17:09:52'),
(33, 286, 'credit', '3402.00', '0.00', NULL, NULL, '3402.00', '3402.00', '2020-09-28', '2020-09-28 10:39:35'),
(34, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '3442.00', '2020-09-28', '2020-09-28 17:38:04'),
(35, 286, 'credit', '222.00', '15.00', NULL, NULL, '237.00', '3679.00', '2020-09-28', '2020-09-28 17:38:27'),
(36, 286, 'credit', '57.00', '15.00', NULL, NULL, '72.00', '3751.00', '2020-09-28', '2020-09-28 17:38:44'),
(37, 286, 'credit', '40.00', '10.00', NULL, NULL, '50.00', '3801.00', '2020-09-28', '2020-09-28 17:39:01'),
(38, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '3881.00', '2020-09-28', '2020-09-28 17:39:18'),
(39, 286, 'credit', '70.00', '10.00', NULL, NULL, '80.00', '3961.00', '2020-09-28', '2020-09-28 17:39:37'),
(40, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '4001.00', '2020-09-28', '2020-09-28 17:39:53'),
(41, 286, 'credit', '69.90', '20.00', NULL, NULL, '89.90', '4090.90', '2020-09-28', '2020-09-28 17:40:15'),
(42, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '4120.90', '2020-09-28', '2020-09-28 17:40:32'),
(43, 286, 'credit', '600.00', '50.00', NULL, NULL, '650.00', '5395.90', '2020-09-28', '2020-09-28 17:42:11'),
(44, 286, 'credit', '575.00', '50.00', NULL, NULL, '625.00', '5445.90', '2020-09-28', '2020-09-28 17:44:50'),
(45, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5445.90', '2020-09-28', '2020-09-28 17:45:32'),
(46, 286, 'credit', '125.00', '50.00', NULL, NULL, '175.00', '5620.90', '2020-09-28', '2020-09-28 17:45:41'),
(47, 286, 'credit', '0.00', '80.00', NULL, NULL, '80.00', '5700.90', '2020-09-28', '2020-09-28 17:46:10'),
(48, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5750.90', '2020-09-28', '2020-09-28 17:46:46'),
(49, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '5850.90', '2020-09-28', '2020-09-28 17:47:09'),
(50, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '5890.90', '2020-09-28', '2020-09-28 17:47:21'),
(51, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '5930.90', '2020-09-28', '2020-09-28 17:47:39'),
(52, 286, 'credit', '0.00', '33.00', NULL, NULL, '33.00', '5963.90', '2020-09-28', '2020-09-28 17:48:07'),
(53, 286, 'credit', '105.00', '20.00', NULL, NULL, '125.00', '6088.90', '2020-09-28', '2020-09-28 17:50:47'),
(54, 286, 'credit', '6090.00', '0.00', NULL, NULL, '6090.00', '6090.00', '2020-09-29', '2020-09-29 10:21:25'),
(55, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '6130.00', '2020-09-29', '2020-09-29 14:22:10'),
(56, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '6190.00', '2020-09-29', '2020-09-29 14:23:25'),
(57, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '6220.00', '2020-09-29', '2020-09-29 14:23:46'),
(58, 286, 'credit', '0.00', '90.00', NULL, NULL, '90.00', '6310.00', '2020-09-29', '2020-09-29 14:27:57'),
(59, 286, 'debit', NULL, NULL, NULL, NULL, '5000.00', '1310.00', '2020-09-29', '2020-09-29 14:36:10'),
(60, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1350.00', '2020-09-29', '2020-09-29 17:34:04'),
(61, 286, 'credit', '418.00', '15.00', NULL, NULL, '433.00', '1783.00', '2020-09-29', '2020-09-29 17:34:20'),
(62, 286, 'credit', '39.90', '25.00', NULL, NULL, '64.90', '1847.90', '2020-09-29', '2020-09-29 17:34:42'),
(63, 286, 'credit', '60.00', '10.00', NULL, NULL, '70.00', '1917.90', '2020-09-29', '2020-09-29 17:34:58'),
(64, 286, 'credit', '20.00', '10.00', NULL, NULL, '30.00', '1947.90', '2020-09-29', '2020-09-29 17:35:12'),
(65, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1987.90', '2020-09-29', '2020-09-29 17:35:27'),
(66, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '2017.90', '2020-09-29', '2020-09-29 17:35:40'),
(67, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '2097.90', '2020-09-29', '2020-09-29 17:35:55'),
(68, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2137.90', '2020-09-29', '2020-09-29 17:36:15'),
(69, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '2167.90', '2020-09-29', '2020-09-29 17:36:49'),
(70, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '2217.90', '2020-09-29', '2020-09-29 17:37:07'),
(71, 286, 'credit', '0.00', '90.00', NULL, NULL, '90.00', '2307.90', '2020-09-29', '2020-09-29 17:37:22'),
(72, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '2407.90', '2020-09-29', '2020-09-29 17:44:08'),
(73, 286, 'credit', '0.00', '150.00', NULL, NULL, '150.00', '2557.90', '2020-09-29', '2020-09-29 17:44:26'),
(74, 286, 'credit', '0.00', '45.00', NULL, NULL, '45.00', '2602.90', '2020-09-29', '2020-09-29 17:45:21'),
(75, 286, 'credit', '2603.00', '0.00', NULL, NULL, '2603.00', '2603.00', '2020-09-30', '2020-09-30 10:18:44'),
(76, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '2633.00', '2020-09-30', '2020-09-30 17:45:11'),
(77, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '2663.00', '2020-09-30', '2020-09-30 17:45:27'),
(78, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2703.00', '2020-09-30', '2020-09-30 17:45:43'),
(79, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2743.00', '2020-09-30', '2020-09-30 17:46:14'),
(80, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '2803.00', '2020-09-30', '2020-09-30 17:46:37'),
(81, 286, 'credit', '230.00', '50.00', NULL, NULL, '280.00', '3083.00', '2020-09-30', '2020-09-30 17:47:00'),
(82, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '3113.00', '2020-09-30', '2020-09-30 17:47:25'),
(83, 286, 'credit', '200.00', '20.00', NULL, NULL, '220.00', '3333.00', '2020-09-30', '2020-09-30 17:48:30'),
(84, 286, 'credit', '80.00', '10.00', NULL, NULL, '90.00', '3423.00', '2020-09-30', '2020-09-30 17:48:56'),
(85, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '3453.00', '2020-09-30', '2020-09-30 17:49:17'),
(86, 286, 'credit', '551.00', '20.00', NULL, NULL, '571.00', '4024.00', '2020-09-30', '2020-09-30 17:49:20'),
(87, 286, 'credit', '60.00', '10.00', NULL, NULL, '70.00', '4094.00', '2020-09-30', '2020-09-30 17:49:40'),
(88, 286, 'credit', '1257.00', '25.00', NULL, NULL, '1282.00', '5376.00', '2020-09-30', '2020-09-30 17:50:07'),
(89, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '5406.00', '2020-09-30', '2020-09-30 17:50:28'),
(90, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '5466.00', '2020-09-30', '2020-09-30 17:51:07'),
(91, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '5496.00', '2020-09-30', '2020-09-30 17:51:24'),
(92, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5546.00', '2020-09-30', '2020-09-30 17:51:39'),
(93, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '5646.00', '2020-09-30', '2020-09-30 17:52:24'),
(94, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '5706.00', '2020-09-30', '2020-09-30 17:55:34'),
(95, 286, 'credit', '0.00', '20.00', NULL, NULL, '20.00', '5726.00', '2020-09-30', '2020-09-30 17:57:02'),
(96, 286, 'credit', '35.00', '115.00', NULL, NULL, '150.00', '150.00', '2020-10-01', '2020-10-01 17:37:00'),
(97, 286, 'credit', '123.00', '22.00', NULL, NULL, '145.00', '295.00', '2020-10-01', '2020-10-01 17:37:38'),
(98, 286, 'credit', '142.00', '20.00', NULL, NULL, '162.00', '457.00', '2020-10-01', '2020-10-01 17:38:02'),
(99, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '497.00', '2020-10-01', '2020-10-01 17:43:08'),
(100, 286, 'credit', '175.00', '50.00', NULL, NULL, '225.00', '722.00', '2020-10-01', '2020-10-01 17:43:25'),
(101, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '772.00', '2020-10-01', '2020-10-01 17:43:29'),
(102, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '832.00', '2020-10-01', '2020-10-01 17:43:45'),
(103, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '882.00', '2020-10-01', '2020-10-01 17:44:19'),
(104, 286, 'credit', '5726.00', '0.00', NULL, NULL, '5726.00', '6608.00', '2020-10-01', '2020-10-01 17:45:27'),
(105, 286, 'credit', '100.00', '20.00', NULL, NULL, '120.00', '120.00', '2020-10-05', '2020-10-05 17:26:54'),
(106, 286, 'credit', '549.00', '40.00', NULL, NULL, '589.00', '709.00', '2020-10-05', '2020-10-05 17:27:47'),
(107, 286, 'credit', '220.00', '20.00', NULL, NULL, '240.00', '949.00', '2020-10-05', '2020-10-05 17:28:04'),
(108, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '989.00', '2020-10-05', '2020-10-05 17:28:17'),
(109, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1029.00', '2020-10-05', '2020-10-05 17:28:33'),
(110, 286, 'credit', '0.00', '40.00', NULL, NULL, '40.00', '1069.00', '2020-10-05', '2020-10-05 17:28:52'),
(111, 286, 'credit', '234.00', '20.00', NULL, NULL, '254.00', '1323.00', '2020-10-05', '2020-10-05 17:29:33'),
(112, 286, 'credit', '750.00', '100.00', NULL, NULL, '850.00', '2173.00', '2020-10-05', '2020-10-05 17:29:39'),
(113, 286, 'credit', '72.00', '15.00', NULL, NULL, '87.00', '2260.00', '2020-10-05', '2020-10-05 17:29:49'),
(114, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '2745.00', '2020-10-05', '2020-10-05 17:30:03'),
(115, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '2825.00', '2020-10-05', '2020-10-05 17:30:07'),
(116, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2865.00', '2020-10-05', '2020-10-05 17:30:21'),
(117, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '3350.00', '2020-10-05', '2020-10-05 17:30:37'),
(118, 286, 'credit', '555.00', '30.00', NULL, NULL, '585.00', '3935.00', '2020-10-05', '2020-10-05 17:30:46'),
(119, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '3985.00', '2020-10-05', '2020-10-05 17:31:09'),
(120, 286, 'credit', '485.00', '40.00', NULL, NULL, '525.00', '4510.00', '2020-10-05', '2020-10-05 17:31:13'),
(121, 286, 'credit', '20.00', '10.00', NULL, NULL, '30.00', '4540.00', '2020-10-05', '2020-10-05 17:31:23'),
(122, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '4590.00', '2020-10-05', '2020-10-05 17:31:42'),
(123, 286, 'credit', '485.00', '40.00', NULL, NULL, '525.00', '5115.00', '2020-10-05', '2020-10-05 17:32:00'),
(124, 286, 'credit', '100.00', '20.00', NULL, NULL, '120.00', '5235.00', '2020-10-05', '2020-10-05 17:32:22'),
(125, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '5720.00', '2020-10-05', '2020-10-05 17:33:54'),
(126, 286, 'debit', NULL, NULL, NULL, NULL, '485.00', '5235.00', '2020-10-05', '2020-10-05 17:34:25'),
(127, 286, 'credit', '6608.00', '0.00', NULL, NULL, '6608.00', '11943.00', '2020-10-05', '2020-10-05 17:37:14'),
(128, 286, 'credit', '80.00', '20.00', NULL, NULL, '100.00', '11926.00', '2020-10-05', '2020-10-05 17:37:45'),
(129, 286, 'credit', '360.00', '40.00', NULL, NULL, '400.00', '12343.00', '2020-10-05', '2020-10-05 17:44:21'),
(130, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '12403.00', '2020-10-05', '2020-10-05 17:44:49'),
(131, 286, 'debit', NULL, NULL, NULL, NULL, '4000.00', '8403.00', '2020-10-05', '2020-10-05 17:53:31'),
(132, 286, 'debit', NULL, NULL, NULL, NULL, '4000.00', '4403.00', '2020-10-05', '2020-10-05 17:53:51'),
(133, 286, 'debit', NULL, NULL, NULL, NULL, '340.00', '4063.00', '2020-10-05', '2020-10-05 17:55:45'),
(134, 286, 'debit', NULL, NULL, NULL, NULL, '220.00', '3843.00', '2020-10-05', '2020-10-05 17:56:43'),
(135, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '100.00', '2020-10-06', '2020-10-06 17:12:17'),
(136, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '585.00', '2020-10-06', '2020-10-06 17:13:30'),
(137, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '645.00', '2020-10-06', '2020-10-06 17:19:41'),
(138, 286, 'credit', '0.00', '25.00', NULL, NULL, '25.00', '670.00', '2020-10-06', '2020-10-06 17:20:23'),
(139, 286, 'debit', NULL, NULL, NULL, NULL, '3500.00', '-2830.00', '2020-10-06', '2020-10-06 17:21:08'),
(140, 286, 'credit', '3843.00', '0.00', NULL, NULL, '3843.00', '1013.00', '2020-10-06', '2020-10-06 17:21:36'),
(141, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1063.00', '2020-10-06', '2020-10-06 17:22:17'),
(142, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '1093.00', '2020-10-06', '2020-10-06 17:22:30'),
(143, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1173.00', '2020-10-06', '2020-10-06 17:22:36'),
(144, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1213.00', '2020-10-06', '2020-10-06 17:23:03'),
(145, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1253.00', '2020-10-06', '2020-10-06 17:23:23'),
(146, 286, 'credit', '0.00', '40.00', NULL, NULL, '40.00', '1293.00', '2020-10-06', '2020-10-06 17:23:41'),
(147, 286, 'credit', '160.00', '20.00', NULL, NULL, '180.00', '1473.00', '2020-10-06', '2020-10-06 17:23:58'),
(148, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1553.00', '2020-10-06', '2020-10-06 17:24:22'),
(149, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1593.00', '2020-10-06', '2020-10-06 17:24:40'),
(150, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1633.00', '2020-10-06', '2020-10-06 17:25:07'),
(151, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1683.00', '2020-10-06', '2020-10-06 17:25:27'),
(152, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1733.00', '2020-10-06', '2020-10-06 17:27:45'),
(153, 286, 'debit', NULL, NULL, NULL, NULL, '500.00', '-500.00', '2020-10-07', '2020-10-07 14:44:10'),
(154, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '-440.00', '2020-10-07', '2020-10-07 16:59:13'),
(155, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '-340.00', '2020-10-07', '2020-10-07 16:59:42'),
(156, 286, 'credit', '200.00', '50.00', NULL, NULL, '250.00', '-90.00', '2020-10-07', '2020-10-07 17:01:38'),
(157, 286, 'credit', '127.00', '23.00', NULL, NULL, '150.00', '60.00', '2020-10-07', '2020-10-07 17:02:22'),
(158, 286, 'credit', '8.00', '10.00', NULL, NULL, '18.00', '78.00', '2020-10-07', '2020-10-07 17:10:39'),
(159, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '118.00', '2020-10-07', '2020-10-07 17:10:54'),
(160, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '158.00', '2020-10-07', '2020-10-07 17:11:07'),
(161, 286, 'credit', '120.00', '80.00', NULL, NULL, '200.00', '358.00', '2020-10-07', '2020-10-07 17:11:32'),
(162, 286, 'credit', '100.00', '50.00', NULL, NULL, '150.00', '508.00', '2020-10-07', '2020-10-07 17:11:48'),
(163, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '538.00', '2020-10-07', '2020-10-07 17:12:04'),
(164, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '568.00', '2020-10-07', '2020-10-07 17:12:20'),
(165, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '618.00', '2020-10-07', '2020-10-07 17:12:20'),
(166, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '648.00', '2020-10-07', '2020-10-07 17:12:38'),
(167, 286, 'credit', '60.00', '20.00', NULL, NULL, '80.00', '728.00', '2020-10-07', '2020-10-07 17:12:58'),
(168, 286, 'credit', '160.00', '20.00', NULL, NULL, '180.00', '908.00', '2020-10-07', '2020-10-07 17:13:16'),
(169, 286, 'credit', '1733.00', '0.00', NULL, NULL, '1733.00', '2641.00', '2020-10-07', '2020-10-07 17:19:42'),
(170, 286, 'credit', '2641.00', '0.00', NULL, NULL, '2641.00', '2641.00', '2020-10-08', '2020-10-08 10:39:54'),
(171, 286, 'debit', NULL, NULL, NULL, NULL, '2200.00', '441.00', '2020-10-08', '2020-10-08 13:38:02'),
(172, 286, 'debit', NULL, NULL, NULL, NULL, '410.00', '31.00', '2020-10-08', '2020-10-08 13:38:52'),
(173, 262, 'debit', NULL, NULL, NULL, NULL, '5500.00', '-5500.00', '2020-10-08', '2020-10-08 17:00:22'),
(174, 262, 'credit', '115.00', '30.00', NULL, NULL, '145.00', '-5355.00', '2020-10-08', '2020-10-08 17:00:36'),
(175, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-4845.00', '2020-10-08', '2020-10-08 17:00:49'),
(176, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-4335.00', '2020-10-08', '2020-10-08 17:01:04'),
(177, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-3825.00', '2020-10-08', '2020-10-08 17:01:20'),
(178, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-3315.00', '2020-10-08', '2020-10-08 17:01:34'),
(179, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-2805.00', '2020-10-08', '2020-10-08 17:01:52'),
(180, 262, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '-2775.00', '2020-10-08', '2020-10-08 17:04:53'),
(181, 262, 'credit', '80.00', '10.00', NULL, NULL, '90.00', '-2685.00', '2020-10-08', '2020-10-08 17:05:06'),
(182, 262, 'credit', '747.00', '15.00', NULL, NULL, '762.00', '-1923.00', '2020-10-08', '2020-10-08 17:05:19'),
(183, 262, 'credit', '120.00', '10.00', NULL, NULL, '130.00', '-1793.00', '2020-10-08', '2020-10-08 17:05:34'),
(184, 262, 'credit', '180.00', '15.00', NULL, NULL, '195.00', '-1598.00', '2020-10-08', '2020-10-08 17:06:09'),
(185, 262, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '-1548.00', '2020-10-08', '2020-10-08 17:06:24'),
(186, 286, 'credit', '119.00', '391.00', NULL, NULL, '510.00', '541.00', '2020-10-08', '2020-10-08 17:07:45'),
(187, 286, 'credit', '0.00', '20.00', NULL, NULL, '20.00', '561.00', '2020-10-08', '2020-10-08 17:09:33'),
(188, 262, 'credit', '91.00', '349.00', NULL, NULL, '440.00', '-1108.00', '2020-10-08', '2020-10-08 17:11:00'),
(189, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '641.00', '2020-10-08', '2020-10-08 17:16:40'),
(190, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '681.00', '2020-10-08', '2020-10-08 17:16:55'),
(191, 286, 'credit', '550.00', '40.00', NULL, NULL, '590.00', '1271.00', '2020-10-08', '2020-10-08 17:18:18'),
(192, 286, 'credit', '120.00', '80.00', NULL, NULL, '200.00', '1471.00', '2020-10-08', '2020-10-08 17:18:39'),
(193, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1551.00', '2020-10-08', '2020-10-08 17:18:55'),
(194, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1591.00', '2020-10-08', '2020-10-08 17:19:11'),
(195, 286, 'credit', '0.00', '200.00', NULL, NULL, '200.00', '1791.00', '2020-10-08', '2020-10-08 17:19:35'),
(196, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1841.00', '2020-10-08', '2020-10-08 17:22:09'),
(197, 1, 'credit', '70.00', '25.00', '5.00', '2.00', '102.00', '102.00', '2020-10-09', '2020-10-09 17:12:44'),
(198, 1, 'credit', '60.00', '25.00', '4.00', '1.00', '90.00', '192.00', '2020-10-09', '2020-10-09 17:13:06'),
(199, 1, 'debit', NULL, NULL, NULL, NULL, '100.00', '92.00', '2020-10-09', '2020-10-09 17:13:21'),
(200, 1, 'credit', '200.00', '25.00', '4.00', '2.00', '231.00', '323.00', '2020-10-09', '2020-10-09 17:15:35');

Advertisement

Answer

In maria db there is a add new event form,and we can set the event there if you have the correct query.below was my query.Now it working well.

INSERT INTO accounts (
client_id, 
flag, 
amount,
cashin_hand,
entry_date,
created,
modified) SELECT A1.client_id,'OP' ,A1.cashin_hand,A1.cashin_hand,current_date(),current_timestamp(),current_timestamp() FROM (SELECT client_id, Max(id) id  FROM accounts GROUP BY client_id) A INNER JOIN accounts A1 on A.client_id = A1.client_id AND A.id = A1.id
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement