-- ========================================================= -- SEED DATA — demo organization for testing the PHP system -- Run AFTER schema.sql -- All demo passwords = "Demo@123" -- bcrypt hash below corresponds to "Demo@123" -- ========================================================= -- Import into the same database as schema.sql via phpMyAdmin. SET @pw = '$2y$10$wM1cZ2nE4Q8sY6f7K0xJpeU3Lb1H2vQpY9D8r3x1m6N7s8T9u0V1q'; -- placeholder, regenerate via PHP (see note at bottom) -- --------------------------------------------------------- -- Organization -- --------------------------------------------------------- INSERT INTO organizations (id, name) VALUES (1, 'VeloWox Pvt Ltd'); -- --------------------------------------------------------- -- Departments -- --------------------------------------------------------- INSERT INTO departments (id, org_id, name, type) VALUES (1, 1, 'Sales', 'sales'), (2, 1, 'Service', 'service'), (3, 1, 'Admin/HR', 'hr'), (4, 1, 'Owner Office', 'admin'); -- --------------------------------------------------------- -- Employees (HR + login) -- Role hierarchy: owner -> dept_head -> team_lead -> agent -- --------------------------------------------------------- INSERT INTO employees (id, org_id, employee_code, full_name, email, phone, gender, dob, city, state, department_id, role, reporting_to, date_of_joining, employment_status, password_hash) VALUES (1, 1, 'EMP-OWN-001', 'Rakesh Sharma', 'owner@velowox.com', '9810000001', 'male', '1980-01-15', 'Panipat', 'Haryana', 4, 'owner', NULL, '2018-01-01', 'active', @pw), (2, 1, 'EMP-SAL-001', 'Anita Verma', 'saleshead@velowox.com', '9810000002', 'female','1985-03-20', 'Panipat', 'Haryana', 1, 'dept_head', 1, '2019-02-01', 'active', @pw), (3, 1, 'EMP-SVC-001', 'Mahesh Rawat', 'servicehead@velowox.com', '9810000003', 'male', '1983-07-11', 'Karnal', 'Haryana', 2, 'dept_head', 1, '2019-02-01', 'active', @pw), (4, 1, 'EMP-HR-001', 'Sunita Yadav', 'hr@velowox.com', '9810000004', 'female','1988-11-02', 'Panipat', 'Haryana', 3, 'hr', 1, '2020-01-15', 'active', @pw), -- Sales Team Alpha (5, 1, 'EMP-SAL-010', 'Ramesh Sharma', 'tlalpha@velowox.com', '9810000010', 'male', '1990-05-10', 'Panipat', 'Haryana', 1, 'team_lead', 2, '2021-03-01', 'active', @pw), (6, 1, 'EMP-SAL-011', 'Aman Verma', 'agent1@velowox.com', '9810000011', 'male', '1995-08-22', 'Panipat', 'Haryana', 1, 'agent', 5, '2022-01-10', 'active', @pw), (7, 1, 'EMP-SAL-012', 'Priya Nair', 'agent2@velowox.com', '9810000012', 'female','1996-12-05', 'Karnal', 'Haryana', 1, 'agent', 5, '2022-04-15', 'active', @pw), -- Sales Team Beta (8, 1, 'EMP-SAL-020', 'Suresh Khanna', 'tlbeta@velowox.com', '9810000020', 'male', '1989-02-18', 'Gurugram','Haryana', 1, 'team_lead', 2, '2021-06-01', 'active', @pw), (9, 1, 'EMP-SAL-021', 'Kiran Bedi', 'agent3@velowox.com', '9810000021', 'female','1997-09-09', 'Gurugram','Haryana', 1, 'agent', 8, '2022-08-01', 'active', @pw), -- Service Team North (10,1, 'EMP-SVC-010', 'Dinesh Sinha', 'tlservicenorth@velowox.com','9810000030','male', '1987-04-25', 'Karnal', 'Haryana', 2, 'team_lead', 3, '2021-05-01', 'active', @pw), (11,1, 'EMP-SVC-011', 'Manoj Tiwari', 'svcagent1@velowox.com', '9810000031', 'male', '1992-10-30', 'Karnal', 'Haryana', 2, 'agent', 10, '2022-02-01', 'active', @pw), -- Service Team East (12,1, 'EMP-SVC-020', 'Deepak Sinha', 'tlserviceeast@velowox.com','9810000040','male', '1991-01-12', 'Sonipat', 'Haryana', 2, 'team_lead', 3, '2021-09-01', 'active', @pw), (13,1, 'EMP-SVC-021', 'Naveen Kumar', 'svcagent2@velowox.com', '9810000041', 'male', '1993-06-18', 'Sonipat', 'Haryana', 2, 'agent', 12, '2022-05-01', 'on_notice', @pw); -- --------------------------------------------------------- -- Teams -- --------------------------------------------------------- INSERT INTO teams (id, department_id, team_lead_id, team_name) VALUES (1, 1, 5, 'Team Alpha (Sales)'), (2, 1, 8, 'Team Beta (Sales)'), (3, 2, 10, 'Team Service-North'), (4, 2, 12, 'Team Service-East'); INSERT INTO team_members (team_id, employee_id) VALUES (1, 5), (1, 6), (1, 7), (2, 8), (2, 9), (3, 10), (3, 11), (4, 12), (4, 13); -- --------------------------------------------------------- -- Service zone mapping (city/state -> service team) -- --------------------------------------------------------- INSERT INTO service_zone_mapping (city, state, service_team_id) VALUES ('Panipat', 'Haryana', 3), ('Karnal', 'Haryana', 3), ('Sonipat', 'Haryana', 4), ('Gurugram', 'Haryana', 4), ('Ludhiana', 'Punjab', 4), ('New Delhi','Delhi NCR',4); -- --------------------------------------------------------- -- Leave types already seeded in schema.sql (Casual, Sick, Earned, Unpaid) -- Leave balances for current year -- --------------------------------------------------------- INSERT INTO leave_balances (employee_id, leave_type_id, year, allotted, used, carried_forward) VALUES (6, 1, 2026, 12, 2, 0), (6, 2, 2026, 7, 1, 0), (6, 3, 2026, 15, 0, 2), (7, 1, 2026, 12, 1, 0), (7, 2, 2026, 7, 0, 0), (7, 3, 2026, 15, 0, 0), (5, 1, 2026, 12, 0, 0), (5, 3, 2026, 15, 5, 1), (10,1, 2026, 12, 3, 0), (10,3, 2026, 15, 0, 0), (12,1, 2026, 12, 0, 0), (12,3, 2026, 15, 0, 0); -- --------------------------------------------------------- -- Sample leave requests -- --------------------------------------------------------- INSERT INTO leave_requests (employee_id, leave_type_id, from_date, to_date, total_days, reason, status, applied_at) VALUES (6, 1, '2026-06-16', '2026-06-17', 2, 'Family function', 'pending', NOW()), (7, 2, '2026-06-13', '2026-06-13', 1, 'Fever', 'pending', NOW()), (12,3, '2026-06-20', '2026-06-24', 5, 'Personal travel', 'approved', NOW() - INTERVAL 3 DAY), (8, 1, '2026-06-14', '2026-06-14', 1, 'Bank work', 'rejected', NOW() - INTERVAL 2 DAY); UPDATE leave_requests SET approved_by = 1, approved_at = NOW(), rejection_reason = 'Critical week, please reschedule' WHERE employee_id = 8 AND status = 'rejected'; UPDATE leave_requests SET approved_by = 3, approved_at = NOW() WHERE employee_id = 12 AND status = 'approved'; -- --------------------------------------------------------- -- Salary structures (current, effective_to = NULL) -- --------------------------------------------------------- INSERT INTO salary_structures (employee_id, effective_from, basic, hra, conveyance_allowance, medical_allowance, special_allowance, pf_employee, professional_tax, tds, created_by) VALUES (1, '2018-01-01', 60000, 24000, 3000, 2000, 11000, 1800, 200, 5000, 1), (2, '2019-02-01', 45000, 18000, 2500, 1500, 8000, 1500, 200, 3000, 1), (3, '2019-02-01', 45000, 18000, 2500, 1500, 8000, 1500, 200, 3000, 1), (4, '2020-01-15', 35000, 14000, 2000, 1000, 6000, 1200, 200, 1500, 1), (5, '2021-03-01', 28000, 11200, 1600, 1000, 4200, 960, 200, 800, 2), (6, '2022-01-10', 20000, 8000, 1200, 1000, 2800, 700, 200, 0, 2), (7, '2022-04-15', 20000, 8000, 1200, 1000, 2800, 700, 200, 0, 2), (8, '2021-06-01', 28000, 11200, 1600, 1000, 4200, 960, 200, 800, 2), (9, '2022-08-01', 20000, 8000, 1200, 1000, 2800, 700, 200, 0, 2), (10,'2021-05-01', 28000, 11200, 1600, 1000, 4200, 960, 200, 800, 3), (11,'2022-02-01', 20000, 8000, 1200, 1000, 2800, 700, 200, 0, 3), (12,'2021-09-01', 28000, 11200, 1600, 1000, 4200, 960, 200, 800, 3), (13,'2022-05-01', 20000, 8000, 1200, 1000, 2800, 700, 200, 0, 3); -- --------------------------------------------------------- -- Payroll run for current month (draft for most depts) -- --------------------------------------------------------- INSERT INTO payroll_runs (org_id, pay_month, pay_year, status) VALUES (1, MONTH(CURDATE()), YEAR(CURDATE()), 'draft'); SET @run_id = LAST_INSERT_ID(); -- Generate payslips from salary_structures snapshot INSERT INTO payslips (payroll_run_id, employee_id, basic, hra, conveyance_allowance, medical_allowance, special_allowance, pf_employee, professional_tax, tds, days_present, days_in_month, gross_earnings, total_deductions, net_pay, payment_status) SELECT @run_id, ss.employee_id, ss.basic, ss.hra, ss.conveyance_allowance, ss.medical_allowance, ss.special_allowance, ss.pf_employee, ss.professional_tax, ss.tds, 22, 26, ss.gross_salary, ss.total_deductions, ss.net_salary, 'pending' FROM salary_structures ss WHERE ss.effective_to IS NULL; -- Mark Admin/HR and Owner runs as processed (for demo variety) -- (We split by creating a second run for those, simpler: just update status display per dept in app logic. -- For demo simplicity we leave single run; hr.php groups by dept and shows run_status from this single run.) UPDATE payroll_runs SET status = 'draft' WHERE id = @run_id; -- --------------------------------------------------------- -- LEADS — sample data across statuses, cities, departments -- --------------------------------------------------------- INSERT INTO leads (org_id, source, customer_name, phone, city, state, department_id, assigned_team_id, assigned_employee_id, status, business_value, created_at, status_updated_at) VALUES (1, 'meta', 'Vikram Malhotra', '9990001001', 'Panipat', 'Haryana', 1, 1, 6, 'installed', 45000, NOW() - INTERVAL 20 DAY, NOW() - INTERVAL 2 DAY), (1, 'meta', 'Sneha Kapoor', '9990001002', 'Panipat', 'Haryana', 1, 1, 6, 'installed', 38000, NOW() - INTERVAL 18 DAY, NOW() - INTERVAL 1 DAY), (1, 'meta', 'Rohit Chauhan', '9990001003', 'Karnal', 'Haryana', 1, 1, 7, 'waiting_install', 42000, NOW() - INTERVAL 10 DAY, NOW() - INTERVAL 2 DAY), (1, 'meta', 'Pooja Sharma', '9990001004', 'Karnal', 'Haryana', 1, 1, 7, 'confirmed', 50000, NOW() - INTERVAL 8 DAY, NOW() - INTERVAL 1 DAY), (1, 'google','Manoj Bansal', '9990001005', 'Panipat', 'Haryana', 1, 1, 6, 'in_pipeline', 0, NOW() - INTERVAL 5 DAY, NOW() - INTERVAL 1 DAY), (1, 'meta', 'Sonal Tripathi', '9990001006', 'Gurugram','Haryana', 1, 2, 9, 'in_pipeline', 0, NOW() - INTERVAL 4 DAY, NOW()), (1, 'meta', 'Ajay Mehta', '9990001007', 'Gurugram','Haryana', 1, 2, 9, 'confirmed', 55000, NOW() - INTERVAL 6 DAY, NOW() - INTERVAL 1 DAY), (1, 'meta', 'Rina Joshi', '9990001008', 'Gurugram','Haryana', 1, 2, 9, 'rejected', 0, NOW() - INTERVAL 7 DAY, NOW() - INTERVAL 2 DAY), (1, 'website','Tarun Goyal', '9990001009', 'Panipat', 'Haryana', 1, 1, 6, 'not_contacted', 0, NOW() - INTERVAL 1 DAY, NOW() - INTERVAL 1 DAY), (1, 'meta', 'Geeta Rani', '9990001010', 'Sonipat', 'Haryana', 1, 2, 9, 'rejected', 0, NOW() - INTERVAL 9 DAY, NOW() - INTERVAL 3 DAY), (1, 'meta', 'Harpreet Singh', '9990001011', 'Ludhiana','Punjab', 1, 2, 8, 'waiting_install', 47000, NOW() - INTERVAL 12 DAY, NOW() - INTERVAL 1 DAY), (1, 'meta', 'Nidhi Arora', '9990001012', 'New Delhi','Delhi NCR',1,2, 8, 'installed', 52000, NOW() - INTERVAL 25 DAY, NOW() - INTERVAL 4 DAY), (1, 'meta', 'Sandeep Yadav', '9990001013', 'Karnal', 'Haryana', 1, 1, 5, 'new', 0, NOW(), NOW()); -- --------------------------------------------------------- -- Installations (linked to waiting_install / installed leads) -- --------------------------------------------------------- -- lead ids assumed sequential starting at 1 based on insert order above INSERT INTO installations (lead_id, service_team_id, service_employee_id, scheduled_date, installed_date, status, final_business_value) VALUES (1, 3, 11, NULL, DATE(NOW() - INTERVAL 2 DAY), 'installed', 45000), (2, 3, 11, NULL, DATE(NOW() - INTERVAL 1 DAY), 'installed', 38000), (3, 3, 11, DATE(NOW() + INTERVAL 2 DAY), NULL, 'scheduled', 0), (11,4, 13, DATE(NOW() + INTERVAL 3 DAY), NULL, 'pending', 0), (12,4, 12, NULL, DATE(NOW() - INTERVAL 4 DAY), 'installed', 52000); -- --------------------------------------------------------- -- Call logs -- --------------------------------------------------------- INSERT INTO call_logs (lead_id, employee_id, call_time, duration_seconds, outcome, notes) VALUES (1, 6, NOW() - INTERVAL 19 DAY, 240, 'interested', 'Customer interested, scheduled visit'), (2, 6, NOW() - INTERVAL 17 DAY, 180, 'interested', 'Confirmed pricing'), (3, 7, NOW() - INTERVAL 9 DAY, 300, 'interested', 'Site survey done'), (4, 7, NOW() - INTERVAL 7 DAY, 220, 'interested', 'Advance received'), (5, 6, NOW() - INTERVAL 4 DAY, 90, 'connected', 'Will call back next week'), (6, 9, NOW() - INTERVAL 3 DAY, 150, 'connected', 'Asked for brochure'), (7, 9, NOW() - INTERVAL 5 DAY, 200, 'interested', 'Deal confirmed'), (8, 9, NOW() - INTERVAL 6 DAY, 60, 'not_interested', 'Price too high'), (10,9, NOW() - INTERVAL 8 DAY, 70, 'not_interested', 'Went with competitor'), (11,8, NOW() - INTERVAL 11 DAY, 260, 'interested', 'Confirmed, awaiting install'), (13,5, NOW(), 0, 'not_reachable', 'No answer, will retry'); -- --------------------------------------------------------- -- Lead status history (sample trail for lead #1) -- --------------------------------------------------------- INSERT INTO lead_status_history (lead_id, old_status, new_status, changed_by, changed_at, remarks) VALUES (1, 'new', 'not_contacted', 6, NOW() - INTERVAL 20 DAY, 'Lead received from Meta'), (1, 'not_contacted', 'in_pipeline', 6, NOW() - INTERVAL 19 DAY, 'First call done, interested'), (1, 'in_pipeline', 'confirmed', 6, NOW() - INTERVAL 15 DAY, 'Advance payment received'), (1, 'confirmed', 'waiting_install', 6, NOW() - INTERVAL 10 DAY, 'Handed to service team'), (1, 'waiting_install', 'installed', 11, NOW() - INTERVAL 2 DAY, 'Installation completed'); -- ========================================================= -- NOTE ON PASSWORDS -- The @pw placeholder above is NOT a verified hash. -- After importing, run this PHP snippet once to set real password hashes: -- -- php -r "echo password_hash('Demo@123', PASSWORD_BCRYPT);" -- -- Then run: -- UPDATE employees SET password_hash = ''; -- -- Or use /tools/reset_passwords.php included in this package. -- =========================================================