-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `xesstech_dayloan`
--

-- --------------------------------------------------------

--
-- Table structure for table `area`
--

CREATE TABLE `area` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `main_area` varchar(255) NOT NULL,
  `sub_area` varchar(255) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `area`
--

INSERT INTO `area` (`id`, `com_id`, `main_area`, `sub_area`, `createdOn`) VALUES
(1, 9, 'Kumarapalayam', '', '2025-09-16 11:47:27'),
(4, 9, 'Chennai', '', '2025-09-22 04:28:00'),
(6, 9, 'thiruppur', '', '2025-09-22 05:11:41');

-- --------------------------------------------------------

--
-- Table structure for table `collection_agent`
--

CREATE TABLE `collection_agent` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `mobile_no` varchar(15) NOT NULL,
  `address` text NOT NULL,
  `aadhar_no` varchar(30) NOT NULL,
  `photo` longblob NOT NULL,
  `report_in` datetime NOT NULL,
  `reporting_time` datetime NOT NULL,
  `status` varchar(100) NOT NULL,
  `line` varchar(300) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `collection_agent`
--

INSERT INTO `collection_agent` (`id`, `com_id`, `name`, `mobile_no`, `address`, `aadhar_no`, `photo`, `report_in`, `reporting_time`, `status`, `line`, `created_at`) VALUES
(1, 9, 'baby', '7675675652', 'komarapalayam', '4850394580594', '', '2025-09-25 15:15:55', '2025-09-25 15:15:55', 'active', 'main area', '2025-09-25 09:46:45');

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `cust_name` varchar(255) NOT NULL,
  `cust_mobile` varchar(15) NOT NULL,
  `cust_mobile2` varchar(15) DEFAULT NULL,
  `building_no` varchar(50) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `district` varchar(100) DEFAULT NULL,
  `pincode` varchar(10) DEFAULT NULL,
  `image` longblob,
  `proof` varchar(255) DEFAULT NULL,
  `adhar_no` varchar(20) DEFAULT NULL,
  `pan_no` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ledger_day_wise`
--

CREATE TABLE `ledger_day_wise` (
  `id` int NOT NULL,
  `com_id` varchar(11) NOT NULL,
  `uid` varchar(11) NOT NULL,
  `date` date NOT NULL,
  `ledger` varchar(200) NOT NULL,
  `particulars` varchar(200) NOT NULL,
  `voucher_type` varchar(200) NOT NULL,
  `voucher_number` varchar(200) NOT NULL,
  `credit` decimal(10,0) NOT NULL,
  `debit` decimal(10,0) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ledger_day_wise`
--

INSERT INTO `ledger_day_wise` (`id`, `com_id`, `uid`, `date`, `ledger`, `particulars`, `voucher_type`, `voucher_number`, `credit`, `debit`, `createdOn`) VALUES
(2, '9', '4', '2025-09-15', 'Cash A/c', ' ', ' ', ' ', 0, 100000, '2025-09-15 13:43:03'),
(3, '9', '1', '2025-09-27', 'Cash A/c', 'DL2025005-Raja', 'Loan', '', 10000, 0, '2025-09-27 07:46:52');

-- --------------------------------------------------------

--
-- Table structure for table `ledger_entry`
--

CREATE TABLE `ledger_entry` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `com_id` int NOT NULL,
  `ledger_name` varchar(255) NOT NULL,
  `ledger_under` varchar(100) NOT NULL,
  `opening` decimal(10,0) NOT NULL,
  `acc_type` varchar(50) NOT NULL,
  `status` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ledger_entry`
--

INSERT INTO `ledger_entry` (`id`, `uid`, `com_id`, `ledger_name`, `ledger_under`, `opening`, `acc_type`, `status`, `created_at`) VALUES
(6, 4, 9, 'Cash A/c', 'Cash in hand', 100000, 'Debit', 'Active', '2025-09-15 13:43:03');

-- --------------------------------------------------------

--
-- Table structure for table `ledger_under`
--

CREATE TABLE `ledger_under` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `under_name` varchar(255) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ledger_under`
--

INSERT INTO `ledger_under` (`id`, `com_id`, `under_name`, `createdOn`) VALUES
(1, 9, 'Bank', '2025-09-15 08:28:00'),
(2, 9, 'Expenses', '2025-09-15 08:28:00'),
(3, 9, 'Cash in hand', '2025-09-15 08:28:00'),
(4, 9, 'Broker', '2025-09-15 08:28:00'),
(5, 9, 'Others', '2025-09-15 08:28:00'),
(6, 9, 'Income', '2025-09-15 08:28:00'),
(7, 9, 'Capital A/C', '2025-09-15 08:28:00'),
(8, 9, 'Sundry Debtors', '2025-09-15 08:28:00'),
(9, 9, 'Customer', '2025-09-15 08:28:00'),
(10, 9, 'Sundry Creditors', '2025-09-15 08:28:00'),
(11, 9, 'Supplier', '2025-09-15 08:28:00'),
(12, 9, 'Current Assets', '2025-09-15 08:28:00'),
(13, 9, 'Loan And Advances', '2025-09-15 08:28:00'),
(14, 9, 'Fixed Assets', '2025-09-15 08:28:00'),
(15, 9, 'Loans (Liability)', '2025-09-15 08:28:00'),
(16, 9, 'Unsecured Loans', '2025-09-15 08:28:00'),
(17, 9, 'Profit And Loss Account', '2025-09-15 08:28:00'),
(18, 9, 'Expenditure Accounts', '2025-09-15 08:28:00');

-- --------------------------------------------------------

--
-- Table structure for table `line_entry`
--

CREATE TABLE `line_entry` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `area` varchar(200) NOT NULL,
  `line` varchar(200) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `line_entry`
--

INSERT INTO `line_entry` (`id`, `com_id`, `area`, `line`, `created_on`) VALUES
(1, 9, 'erode', 'main line', '0000-00-00 00:00:00'),
(2, 9, 'salem', 'main line', '0000-00-00 00:00:00'),
(3, 9, 'Kollampalayam', 'second', '2025-09-22 04:49:43'),
(4, 9, 'chennai', 'second', '2025-09-22 04:49:43'),
(5, 9, 'covai', 'primary', '2025-09-22 05:12:00'),
(6, 9, 'thiruppur', 'primary', '2025-09-22 05:12:00');

-- --------------------------------------------------------

--
-- Table structure for table `loan_entry`
--

CREATE TABLE `loan_entry` (
  `id` int NOT NULL,
  `loan_id` varchar(50) NOT NULL,
  `com_id` int NOT NULL,
  `loan_date` date NOT NULL,
  `cust_id` int NOT NULL,
  `cus_name` varchar(100) NOT NULL,
  `cust_mobile` varchar(15) NOT NULL,
  `cust_contact` varchar(15) NOT NULL,
  `aadhaar_no` varchar(100) NOT NULL,
  `cust_address` varchar(500) NOT NULL,
  `cust_address2` varchar(500) NOT NULL,
  `area` varchar(255) NOT NULL,
  `loan_type` varchar(20) NOT NULL,
  `loan_amt` decimal(10,0) NOT NULL,
  `dues` varchar(50) NOT NULL,
  `interest` decimal(10,0) NOT NULL,
  `interest_amt` decimal(10,0) NOT NULL,
  `ledger` varchar(50) NOT NULL,
  `due_schedule` json DEFAULT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total_amt` decimal(10,0) NOT NULL,
  `per_due_amt` decimal(10,0) NOT NULL,
  `next_due_date` date NOT NULL,
  `last_due_date` date NOT NULL,
  `pre_interest` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `loan_entry`
--

INSERT INTO `loan_entry` (`id`, `loan_id`, `com_id`, `loan_date`, `cust_id`, `cus_name`, `cust_mobile`, `cust_contact`, `aadhaar_no`, `cust_address`, `cust_address2`, `area`, `loan_type`, `loan_amt`, `dues`, `interest`, `interest_amt`, `ledger`, `due_schedule`, `createdOn`, `total_amt`, `per_due_amt`, `next_due_date`, `last_due_date`, `pre_interest`) VALUES
(3, '1', 9, '2025-09-18', 0, 'Nasreen', '1234567890', '1234567890', '1234567890', 'Erode', 'Erode', 'Kollampalayam', 'Daily', 10000, '60', 20, 2000, '', NULL, '2025-09-18 12:07:56', 12000, 200, '2025-09-19', '2025-11-17', 'No'),
(4, 'DL2025001', 9, '0000-00-00', 0, 'djfshkh', '4508734603', '9849096374', '905690346753', 'fsushsdflsdfksjdflsdkfjsdkl', 'jdflksdfjslkdfjsd', 'Kollampalayam', 'Weekly', 2000000, '10', 12, 240000, '', NULL, '2025-09-22 03:29:01', 2240000, 224000, '0000-00-00', '0000-00-00', 'No'),
(5, 'DL2025002', 9, '2025-09-22', 0, 'Raja', '1234567890', '1234567890', '1234567890', 'Erode', 'Erode', 'salem', 'Daily', 10000, '60', 20, 2000, '', NULL, '2025-09-22 05:49:31', 12000, 167, '2025-09-23', '2025-11-21', 'Yes'),
(6, 'DL2025003', 9, '2025-09-24', 0, 'Mani', '9800998899', '9878876786', '', '23/9,veppadai , erode', '', 'Kumarapalayam', 'Daily', 10000, '60', 0, 0, '', NULL, '2025-09-24 10:03:41', 10000, 167, '2025-09-25', '2025-11-23', 'No'),
(7, 'DL2025004', 9, '2025-09-24', 0, 'fvf', '12345678', '3456789', '1234567890', 'dfghj', 'fgvfgfs', 'thiruppur', 'Weekly', 10000, '10', 0, 0, 'Cash A/c', NULL, '2025-09-24 10:20:47', 10000, 1000, '2025-10-01', '2025-12-03', 'No'),
(10, 'DL2025005', 9, '2025-09-27', 0, 'Raja', '1234567890', '1234567890', '1234567890', 'Erode', 'Erode', 'Kumarapalayam', '0', 10000, '60', 20, 2000, 'Cash A/c', NULL, '2025-09-27 07:46:52', 12000, 200, '2025-09-28', '2025-11-26', '0');

-- --------------------------------------------------------

--
-- Table structure for table `loan_type`
--

CREATE TABLE `loan_type` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `loan_type` varchar(50) NOT NULL,
  `due_count` varchar(200) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `loan_type`
--

INSERT INTO `loan_type` (`id`, `com_id`, `loan_type`, `due_count`, `createdOn`) VALUES
(1, 9, 'Daily', '60', '2025-09-16 11:12:40'),
(2, 9, 'Weekly', '10', '2025-09-18 08:18:23'),
(4, 9, 'Monthly', '5', '2025-09-24 07:39:29');

-- --------------------------------------------------------

--
-- Table structure for table `mask`
--

CREATE TABLE `mask` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `status` enum('yes','no') DEFAULT 'yes',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `registration`
--

CREATE TABLE `registration` (
  `id` int NOT NULL,
  `com_name` varchar(255) NOT NULL,
  `mob_num` varchar(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `registration`
--

INSERT INTO `registration` (`id`, `com_name`, `mob_num`, `email`, `status`, `created_at`) VALUES
(5, 'KanInfoTech', '9788777788', 'kaninfotech@gmail.com', 'active', '2025-09-15 08:28:00');

-- --------------------------------------------------------

--
-- Table structure for table `report_time`
--

CREATE TABLE `report_time` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `time_from` time NOT NULL,
  `time_to` time NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `area`
--
ALTER TABLE `area`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `collection_agent`
--
ALTER TABLE `collection_agent`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `adhar_no` (`adhar_no`),
  ADD UNIQUE KEY `pan_no` (`pan_no`),
  ADD KEY `com_id` (`com_id`);

--
-- Indexes for table `ledger_day_wise`
--
ALTER TABLE `ledger_day_wise`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ledger_entry`
--
ALTER TABLE `ledger_entry`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ledger_under`
--
ALTER TABLE `ledger_under`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `line_entry`
--
ALTER TABLE `line_entry`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `loan_entry`
--
ALTER TABLE `loan_entry`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `loan_type`
--
ALTER TABLE `loan_type`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mask`
--
ALTER TABLE `mask`
  ADD PRIMARY KEY (`id`),
  ADD KEY `com_id` (`com_id`);

--
-- Indexes for table `registration`
--
ALTER TABLE `registration`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `report_time`
--
ALTER TABLE `report_time`
  ADD PRIMARY KEY (`id`),
  ADD KEY `com_id` (`com_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `area`
--
ALTER TABLE `area`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `collection_agent`
--
ALTER TABLE `collection_agent`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger_day_wise`
--
ALTER TABLE `ledger_day_wise`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `ledger_entry`
--
ALTER TABLE `ledger_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `ledger_under`
--
ALTER TABLE `ledger_under`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `line_entry`
--
ALTER TABLE `line_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `loan_entry`
--
ALTER TABLE `loan_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `loan_type`
--
ALTER TABLE `loan_type`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `mask`
--
ALTER TABLE `mask`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `registration`
--
ALTER TABLE `registration`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `report_time`
--
ALTER TABLE `report_time`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `customers`
--
ALTER TABLE `customers`
  ADD CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`com_id`) REFERENCES `company` (`reg_id`);

--
-- Constraints for table `mask`
--
ALTER TABLE `mask`
  ADD CONSTRAINT `mask_ibfk_1` FOREIGN KEY (`com_id`) REFERENCES `company` (`reg_id`);

--
-- Constraints for table `report_time`
--
ALTER TABLE `report_time`
  ADD CONSTRAINT `report_time_ibfk_1` FOREIGN KEY (`com_id`) REFERENCES `company` (`reg_id`);
COMMIT;

