(11439,'2025-09-18 00:00:00',38,2,44,2,2,''),(11440,'2025-09-18 00:00:00',37,2,90,4.5,4.5,''),(11441,'2025-09-18 00:00:00',39,2,95,9,9,''),(11442,'2025-09-18 00:00:00',9,2,95,9,9,''),(11443,'2025-09-18 00:00:00',38,2,4,2,2,''),(11449,'2025-09-18 00:00:00',44,16,14,1,1,''),(11450,'2025-09-18 00:00:00',11,16,14,2,2,''),(11451,'2025-09-18 00:00:00',15,16,14,9,9,''),(11452,'2025-09-18 00:00:00',46,16,14,4.5,4.5,''),(11453,'2025-09-18 00:00:00',45,16,14,2,2,''),(11455,'2025-09-17 00:00:00',22,16,100,0,3,'Additonal hrs on Anything'),(11456,'2025-09-17 00:00:00',22,16,15,0,1.5,'Additional hrs on Deb'),(11457,'2025-09-18 00:00:00',33,16,100,9,9,''),(11458,'2025-09-18 00:00:00',22,16,100,4.5,4.5,''),(11459,'2025-09-18 00:00:00',16,16,14,2.5,2.53,'Additional hrs on Tineon'),(11460,'2025-09-18 00:00:00',32,16,87,4.5,4.5,''),(11461,'2025-09-18 00:00:00',48,16,15,3,3,''),(11462,'2025-09-18 00:00:00',22,16,65,4.5,4.5,''),(11463,'2025-09-18 00:00:00',47,17,21,2,2,''),(11464,'2025-09-17 00:00:00',44,18,66,0,1,'Additional Hrs on Eventably (Karl)'),(11465,'2025-09-18 00:00:00',23,18,66,4,4,''),(11466,'2025-09-18 00:00:00',18,18,98,8,8,''),(11467,'2025-09-18 00:00:00',24,18,19,9,9,''),(11468,'2025-09-18 00:00:00',44,18,66,4,4,''),(11469,'2025-09-19 00:00:00',44,16,14,1,1,''),(11470,'2025-09-19 00:00:00',11,16,14,9,9,''),(11471,'2025-09-19 00:00:00',46,16,14,4.5,4.5,''),(11472,'2025-09-19 00:00:00',45,16,14,2,2,''),(11473,'2025-09-18 00:00:00',2,2,45,0,0.33,'Additional hr on Bill\'s project'),(11474,'2025-09-18 00:00:00',45,18,66,0,1,'Additional Hrs on Eventably (Karl)'),(11475,'2025-09-19 00:00:00',23,18,66,1,1,''),(11476,'2025-09-19 00:00:00',18,18,98,8,8,''),(11477,'2025-09-19 00:00:00',24,18,19,9,9,''),(11478,'2025-09-19 00:00:00',44,18,66,1,1,''),(11479,'2025-09-19 00:00:00',2,2,22,2.5,2.58,'Additional hr on Eyeplastics'),(11480,'2025-09-19 00:00:00',2,2,7,1,1,''),(11481,'2025-09-19 00:00:00',38,2,44,4.5,5.5,'Additional hr on Lenny\'s project'),(11482,'2025-09-19 00:00:00',37,2,90,8,8,''),(11483,'2025-09-19 00:00:00',39,2,95,9,9,''),(11484,'2025-09-19 00:00:00',9,2,95,9,9,''),(11485,'2025-09-19 00:00:00',2,2,90,0.5,0.5,''),(11486,'2025-09-18 00:00:00',7,16,16,0,4.5,'Additional hrs on Unicorn'),(11487,'2025-09-19 00:00:00',32,17,75,4.5,4.5,''),(11496,'2025-09-19 00:00:00',42,16,100,4.5,4.5,''),(11497,'2025-09-19 00:00:00',33,16,100,9,9,''),(11498,'2025-09-19 00:00:00',22,16,100,4.5,4.5,''),(11499,'2025-09-19 00:00:00',16,16,14,4.5,4.5,''),(11500,'2025-09-19 00:00:00',48,16,15,3,0,'Less hrs on Deb project'),(11501,'2025-09-19 00:00:00',45,16,100,1,2.5,'Additional hrs on Anything Roatan'),(11502,'2025-09-19 00:00:00',7,16,16,9,9,''),(11503,'2025-09-22 00:00:00',15,16,14,9,9,''),(11504,'2025-09-22 00:00:00',11,16,14,2,2,''),(11505,'2025-09-22 00:00:00',46,16,14,4.5,4.5,''),(11506,'2025-09-22 00:00:00',45,16,14,2,2,''),(11507,'2025-09-22 00:00:00',47,17,21,3.5,3.5,''),(11508,'2025-09-22 00:00:00',32,17,75,3,4.5,'Additional on Soren\'s project'),(11509,'2025-09-19 00:00:00',45,18,66,0,1,'Additional Hrs on Eventably (Karl)'),(11510,'2025-09-22 00:00:00',18,18,98,8,8,''),(11511,'2025-09-22 00:00:00',24,18,19,9,9,''),(11512,'2025-09-19 00:00:00',38,2,4,0,0.5,'Additional hr on Ton\'s project'),(11513,'2025-09-22 00:00:00',2,2,22,2.5,2.58,'Additional hr on Eyeplastics'),(11514,'2025-09-22 00:00:00',2,2,45,0.5,1.33,'Additional hr on Bill\'s project'),(11515,'2025-09-22 00:00:00',38,2,44,2,2,''),(11516,'2025-09-22 00:00:00',37,2,90,4.5,4.5,''),(11517,'2025-09-22 00:00:00',39,2,95,9,9,''),(11518,'2025-09-22 00:00:00',9,2,95,9,9,''),(11519,'2025-09-22 00:00:00',2,2,90,0.5,0.5,''),(11520,'2025-09-22 00:00:00',38,2,4,4.5,6.67,'Additional hr on Ton\'s project'),(11521,'2025-09-22 00:00:00',51,2,45,8,8,''),(11522,'2025-09-22 00:00:00',33,16,100,9,9,''),(11523,'2025-09-22 00:00:00',22,16,100,4.5,4.5,''),(11524,'2025-09-22 00:00:00',16,16,14,2.5,2.5,''),(11525,'2025-09-22 00:00:00',7,16,16,6,6.03,'Additional hrs on Ludo project'),(11526,'2025-09-22 00:00:00',45,16,100,0.5,0.5,''),(11527,'2025-09-23 00:00:00',18,18,98,8,8,''),(11528,'2025-09-23 00:00:00',24,18,19,9,9,''),(11529,'2025-09-23 00:00:00',47,17,21,2,3,'Additional on Soren\'s project'),(11530,'2025-09-23 00:00:00',15,16,14,9,9,''),(11531,'2025-09-23 00:00:00',11,16,14,2,2,''),(11532,'2025-09-23 00:00:00',46,16,14,4.5,4.5,''),(11533,'2025-09-23 00:00:00',45,16,14,2,2,''),(11534,'2025-09-23 00:00:00',42,16,100,2,2,''),(11535,'2025-09-23 00:00:00',33,16,100,9,9,''),(11536,'2025-09-23 00:00:00',22,16,100,4.5,4.5,''),(11537,'2025-09-23 00:00:00',16,16,14,2.5,2.83,'Additional hrs on Tineon'),(11538,'2025-09-23 00:00:00',7,16,16,6,6,''),(11539,'2025-09-22 00:00:00',2,2,7,0,0.33,'Additional hr on Chris\'s project'),(11540,'2025-09-23 00:00:00',2,2,22,2.5,2.5,''),(11541,'2025-09-23 00:00:00',2,2,45,1.5,1.5,''),(11542,'2025-09-23 00:00:00',37,2,90,8,8,''),(11543,'2025-09-23 00:00:00',39,2,95,9,9,''),(11544,'2025-09-23 00:00:00',9,2,95,9,9,''),(11545,'2025-09-23 00:00:00',38,2,4,2.5,3,'Additional hr on Ton\'s project'),(11546,'2025-09-23 00:00:00',51,2,45,2,2,''),(11547,'2025-09-24 00:00:00',18,18,98,8,8,''),(11548,'2025-09-24 00:00:00',24,18,19,9,9,''),(11549,'2025-09-23 00:00:00',2,2,7,0,0.57,'Additional hr on Chris\'s project'),(11560,'2025-09-24 00:00:00',15,16,14,9,9,''),(11561,'2025-09-24 00:00:00',11,16,14,2,2,''),(11562,'2025-09-24 00:00:00',46,16,14,4.5,4.5,''),(11563,'2025-09-24 00:00:00',45,16,14,2,2,''),(11564,'2025-09-23 00:00:00',32,16,87,0,1,'Additional hrs on SmartRider'),(11565,'2025-09-24 00:00:00',33,16,100,9,9,''),(11566,'2025-09-24 00:00:00',22,16,100,4.5,4.5,''),(11567,'2025-09-24 00:00:00',16,16,14,2.5,2.5,''),(11568,'2025-09-24 00:00:00',7,16,16,6,6,''),(11569,'2025-09-24 00:00:00',32,16,87,7,7,''),(11570,'2025-09-24 00:00:00',47,17,21,3,3,''),(11571,'2025-09-24 00:00:00',2,2,22,2.5,2.5,''),(11572,'2025-09-24 00:00:00',2,2,45,1,1.67,'Additional hr on Bill\'s project'),(11573,'2025-09-24 00:00:00',37,2,90,8,8,''),(11574,'2025-09-24 00:00:00',39,2,95,9,9,''),(11575,'2025-09-24 00:00:00',9,2,95,9,9,''),(11576,'2025-09-24 00:00:00',38,2,4,0.5,0.5,''),(11577,'2025-09-24 00:00:00',51,2,45,2,2,''),(11578,'2025-09-24 00:00:00',2,2,48,1,1.33,'Additional hr on Romain\'s project'),(11579,'2025-09-24 00:00:00',2,2,90,0.5,0.53,'Additional hr on MedFi project'),(11580,'2025-09-25 00:00:00',2,2,22,2.5,2.5,''),(11581,'2025-09-25 00:00:00',2,2,7,2,2,''),(11582,'2025-09-25 00:00:00',37,2,90,8,8.5,'Additional hr on MedFi project'),(11583,'2025-09-25 00:00:00',39,2,95,9,9,''),(11584,'2025-09-25 00:00:00',9,2,95,9,9,''),(11585,'2025-09-25 00:00:00',38,2,37,4,2,'Less hr on Chase due to task completed on the project'),(11586,'2025-09-25 00:00:00',2,2,40,0.5,0.67,'Additional hr on Yasser\'s project'),(11587,'2025-09-25 00:00:00',15,16,14,9,9,''),(11588,'2025-09-25 00:00:00',11,16,14,2,2,''),(11589,'2025-09-25 00:00:00',46,16,14,4.5,4.5,''),(11590,'2025-09-25 00:00:00',45,16,14,2,2,''),(11594,'2025-09-25 00:00:00',16,16,14,2.5,2.55,'Additional hrs on Tineon'),(11595,'2025-09-25 00:00:00',7,16,16,6,6,''),(11596,'2025-09-25 00:00:00',32,16,87,6,6,''),(11599,'2025-09-25 00:00:00',18,18,98,8,8,''),(11600,'2025-09-25 00:00:00',24,18,19,9,9,''),(11601,'2025-09-25 00:00:00',32,17,75,2,0,'Less work on Soren\'s project, waiting for client response.'),(11602,'2025-09-25 00:00:00',47,17,21,2,2,''),(11603,'2025-09-26 00:00:00',15,16,14,9,9,''),(11604,'2025-09-26 00:00:00',11,16,14,2,2,''),(11605,'2025-09-26 00:00:00',46,16,14,4.5,4.5,''),(11606,'2025-09-26 00:00:00',45,16,14,2,2,''),(11607,'2025-09-26 00:00:00',47,17,21,1.5,1.5,''),(11608,'2025-09-26 00:00:00',18,18,98,8,8,''),(11609,'2025-09-26 00:00:00',24,18,19,9,9,''),(11616,'2025-09-25 00:00:00',38,2,4,0,0.5,'Additional hr on Ton\'s project'),(11623,'2025-09-26 00:00:00',2,2,22,2.5,2.53,'Additional hr on Eyeplastic project'),(11624,'2025-09-26 00:00:00',2,2,7,1,1.33,'Additional hr on Chris\'s project'),(11625,'2025-09-26 00:00:00',37,2,90,9,9,''),(11626,'2025-09-26 00:00:00',39,2,95,9,9,''),(11627,'2025-09-26 00:00:00',9,2,95,9,9,''),(11628,'2025-09-26 00:00:00',2,2,40,0.5,0.6,'Additional hr on Yasser\'s project'),(11635,'2025-09-26 00:00:00',48,16,15,1.5,1.5,''),(11636,'2025-09-26 00:00:00',42,16,100,4.5,4.5,''),(11637,'2025-09-26 00:00:00',33,16,100,9,9,''),(11638,'2025-09-26 00:00:00',22,16,100,4.5,4.5,''),(11639,'2025-09-26 00:00:00',16,16,14,2.5,2.52,''),(11640,'2025-09-26 00:00:00',7,16,16,6,6,''),(11641,'2025-09-26 00:00:00',32,16,87,5,5,''),(11642,'2025-09-29 00:00:00',38,2,45,0.5,1.33,'Additional hr on Bill\'s project'),(11643,'2025-09-29 00:00:00',2,2,95,9,9,''),(11644,'2025-09-29 00:00:00',37,2,90,9,9,''),(11645,'2025-09-29 00:00:00',39,2,95,9,9,''),(11646,'2025-09-26 00:00:00',32,17,75,0,1,'Additional hours on Soren\'s project'),(11647,'2025-09-29 00:00:00',47,17,21,3,3,''),(11648,'2025-09-29 00:00:00',15,16,14,9,9,''),(11649,'2025-09-29 00:00:00',11,16,14,4.5,4.5,''),(11650,'2025-09-29 00:00:00',46,16,14,4.5,4.5,''),(11651,'2025-09-29 00:00:00',18,18,98,8,8,''),(11652,'2025-09-29 00:00:00',24,18,19,9,9,''),(11653,'2025-09-29 00:00:00',42,16,100,2,2,''),(11654,'2025-09-29 00:00:00',33,16,100,9,9,''),(11655,'2025-09-29 00:00:00',22,16,100,4.5,4.5,''),(11656,'2025-09-29 00:00:00',16,16,14,2.5,2.5,''),(11657,'2025-09-29 00:00:00',7,16,16,6,6,''),(11658,'2025-09-26 00:00:00',7,2,33,0,0.5,'Additional hr on Bluediamond project'),(11659,'2025-09-29 00:00:00',38,2,4,0,1.5,'Additional hr on Ton\'s project'),(11660,'2025-09-30 00:00:00',2,2,95,9,9,''),(11661,'2025-09-30 00:00:00',37,2,90,9,9,''),(11662,'2025-09-30 00:00:00',39,2,95,9,9,''),(11663,'2025-09-30 00:00:00',38,2,4,6,7.83,'Additional hr on Ton\'s project'),(11664,'2025-09-30 00:00:00',32,16,87,4.5,4.5,''),(11665,'2025-09-30 00:00:00',33,16,100,9,9,''),(11666,'2025-09-30 00:00:00',22,16,100,4.5,4.5,''),(11667,'2025-09-30 00:00:00',16,16,14,2.5,2.58,'Additional hrs on Tineon'),(11668,'2025-09-30 00:00:00',7,16,16,6,6,''),(11669,'2025-09-30 00:00:00',32,17,75,4.5,4.5,''),(11670,'2025-09-30 00:00:00',47,17,21,2,2,''),(11676,'2025-09-30 00:00:00',44,16,14,1,1,''),(11677,'2025-09-30 00:00:00',45,16,14,2,2,''),(11678,'2025-09-30 00:00:00',15,16,14,9,9,''),(11679,'2025-09-30 00:00:00',11,16,14,2,2,''),(11680,'2025-09-30 00:00:00',46,16,14,4.5,4.5,''),(11681,'2025-10-01 00:00:00',18,18,98,8,8,''),(11682,'2025-10-01 00:00:00',24,18,19,9,9,''),(11683,'2025-09-30 00:00:00',38,2,45,0,0.33,'Additional hr on Bill\'s project'),(11691,'2025-09-30 00:00:00',7,2,33,0,0.5,''),(11692,'2025-10-01 00:00:00',9,2,95,9,9,''),(11693,'2025-10-01 00:00:00',2,2,7,1,1.5,'Additional hr on Chris\'s project'),(11694,'2025-10-01 00:00:00',37,2,90,9,9,''),(11695,'2025-10-01 00:00:00',39,2,95,9,9,''),(11696,'2025-10-01 00:00:00',38,2,4,4.5,5,'Additional hr on Ton\'s project'),(11697,'2025-10-01 00:00:00',2,2,90,0.5,0.83,'Additional hr on MedFi\'s project'),(11698,'2025-10-01 00:00:00',2,2,22,2.5,2.5,''),(11699,'2025-10-01 00:00:00',7,2,33,2,2,''),(11700,'2025-10-01 00:00:00',44,16,14,1,1,''),(11701,'2025-10-01 00:00:00',45,16,14,2,2,''),(11702,'2025-10-01 00:00:00',15,16,14,9,9,''),(11703,'2025-10-01 00:00:00',11,16,14,2,2,''),(11704,'2025-10-01 00:00:00',46,16,14,4.5,4.5,''),(11705,'2025-10-01 00:00:00',32,16,87,4.5,9,'Additional hrs on Smart Rider'),(11706,'2025-10-01 00:00:00',33,11,100,9,9,''),(11707,'2025-10-01 00:00:00',22,11,100,4.5,4.5,''),(11708,'2025-10-01 00:00:00',16,16,14,2.5,2.72,'Additional hrs on Tineon project'),(11709,'2025-10-01 00:00:00',7,16,16,6,6,''),(11710,'2025-10-01 00:00:00',45,11,100,4.5,4.5,''),(11711,'2025-10-01 00:00:00',2,2,40,0,0.67,'Additional hr on Yasser\'s project'),(11712,'2025-10-03 00:00:00',9,2,95,9,9,''),(11713,'2025-10-03 00:00:00',2,2,7,0.5,0.5,''),(11714,'2025-10-03 00:00:00',37,2,90,9,9,''),(11715,'2025-10-03 00:00:00',39,2,95,9,9,''),(11716,'2025-10-03 00:00:00',38,2,4,4.5,6.83,'Additional hr on Ton\'s project'),(11717,'2025-10-03 00:00:00',2,2,90,0.5,0.5,''),(11718,'2025-10-03 00:00:00',2,2,22,2.5,2.5,''),(11719,'2025-10-03 00:00:00',38,2,45,1,1,''),(11720,'2025-10-03 00:00:00',47,17,21,1,2.5,'Additional on Soren\'s project'),(11721,'2025-10-03 00:00:00',45,16,14,2,2,''),(11722,'2025-10-03 00:00:00',32,16,87,4.5,4.5,''),(11723,'2025-10-03 00:00:00',33,11,100,9,9,''),(11724,'2025-10-03 00:00:00',16,16,14,2.5,2.5,''),(11725,'2025-10-03 00:00:00',7,16,16,6,6,''),(11726,'2025-10-03 00:00:00',45,11,100,2,2,''),(11727,'2025-10-03 00:00:00',18,18,98,8,8,''),(11728,'2025-10-03 00:00:00',24,18,19,9,9,''),(11729,'2025-10-03 00:00:00',15,16,14,9,9,''),(11730,'2025-10-03 00:00:00',11,16,14,2,2,''),(11731,'2025-10-03 00:00:00',46,16,14,4.5,4.5,''),(11732,'2025-10-03 00:00:00',11,11,100,4.5,4.5,''),(11736,'2025-10-06 00:00:00',47,17,21,1,0,'Waiting for Soren\'s reply.'),(11737,'2025-10-06 00:00:00',45,18,66,0.5,0.5,''),(11738,'2025-10-06 00:00:00',23,18,66,1,1.5,'Additional Hrs on Eventably'),(11739,'2025-10-06 00:00:00',18,18,98,8,8,''),(11740,'2025-10-06 00:00:00',24,18,19,9,9,''),(11741,'2025-10-06 00:00:00',32,16,87,9,9,''),(11742,'2025-10-06 00:00:00',16,16,14,4.5,4.65,'Additional hrs on Tineon project'),(11743,'2025-10-06 00:00:00',7,16,16,6,6,''),(11744,'2025-10-06 00:00:00',7,2,40,2,2,''),(11745,'2025-10-06 00:00:00',9,2,95,9,9,''),(11746,'2025-10-06 00:00:00',2,2,7,1,1,''),(11747,'2025-10-06 00:00:00',37,2,90,9,9,''),(11748,'2025-10-06 00:00:00',39,2,95,9,9,''),(11749,'2025-10-06 00:00:00',38,2,4,4.5,5.5,'Additional hr on Ton\'s project'),(11750,'2025-10-06 00:00:00',2,2,90,0.5,0.5,''),(11751,'2025-10-06 00:00:00',2,2,22,2.5,2.5,''),(11752,'2025-10-06 00:00:00',44,16,14,1,1,''),(11753,'2025-10-06 00:00:00',45,16,14,2,2,''),(11754,'2025-10-06 00:00:00',45,11,100,2,0,'Less hrs on Anything Roatan'),(11755,'2025-10-06 00:00:00',15,16,14,9,9,''),(11756,'2025-10-06 00:00:00',11,11,101,4.5,5,'Additional hrs on Max - WP project'),(11757,'2025-10-06 00:00:00',46,16,14,4.5,4.5,''),(11758,'2025-10-06 00:00:00',11,11,100,4.5,4,'Less hrs on Anything Roatan App Development'),(11759,'2025-10-03 00:00:00',32,17,75,0,2,'Additional on Soren\'s project'),(11760,'2025-10-07 00:00:00',18,18,98,8,8,''),(11761,'2025-10-07 00:00:00',24,18,19,9,9,''),(11762,'2025-10-07 00:00:00',45,16,14,2,2,''),(11763,'2025-10-07 00:00:00',15,16,14,9,9,''),(11764,'2025-10-07 00:00:00',11,11,101,6,6,''),(11765,'2025-10-07 00:00:00',46,16,14,4.5,4.5,''),(11766,'2025-10-07 00:00:00',11,11,100,3,3,''),(11771,'2025-10-06 00:00:00',2,2,45,0,0.33,'Additional hr on Bill\'s project'),(11772,'2025-10-07 00:00:00',7,2,40,3,3,''),(11773,'2025-10-07 00:00:00',9,2,95,9,9,''),(11774,'2025-10-07 00:00:00',2,2,7,1,1.03,'Additional hr on Chris\'s project'),(11775,'2025-10-07 00:00:00',37,2,90,9,9,''),(11776,'2025-10-07 00:00:00',39,2,95,9,9,''),(11777,'2025-10-07 00:00:00',38,2,4,0.5,5.33,'Additional hr on Ton\'s project'),(11778,'2025-10-07 00:00:00',2,2,90,0.5,0.53,'Additional hr on MedFi\'s project'),(11779,'2025-10-07 00:00:00',2,2,22,2.5,2.5,''),(11780,'2025-10-07 00:00:00',48,16,15,1.5,1.5,''),(11781,'2025-10-07 00:00:00',45,16,87,4.5,4.5,''),(11782,'2025-10-07 00:00:00',33,11,100,9,9,''),(11783,'2025-10-07 00:00:00',32,16,87,4.5,4.5,''),(11784,'2025-10-07 00:00:00',16,16,14,4.5,4.65,'Additional hrs on Tineon'),(11785,'2025-10-07 00:00:00',7,16,16,6,6,''),(11786,'2025-10-08 00:00:00',18,18,98,8,8,''),(11787,'2025-10-08 00:00:00',24,18,19,9,9,''),(11788,'2025-10-08 00:00:00',44,2,4,2,2.5,'Additional hr on Ton\'s project'),(11789,'2025-10-08 00:00:00',7,2,40,3,1,'Less hr on Yasser\'s project due to waiting for support response'),(11790,'2025-10-08 00:00:00',9,2,95,9,9,''),(11791,'2025-10-08 00:00:00',2,2,7,1,1.33,'Additional hr on Chris\'s project'),(11792,'2025-10-08 00:00:00',37,2,90,9,9,''),(11793,'2025-10-08 00:00:00',39,2,95,9,9,''),(11794,'2025-10-08 00:00:00',38,2,4,0.5,1,'Additional hr on Ton\'s project'),(11795,'2025-10-08 00:00:00',2,2,90,0.5,0.83,'Additional hr on MedFi\'s project'),(11796,'2025-10-08 00:00:00',2,2,22,2.5,2.5,''),(11797,'2025-10-08 00:00:00',38,2,44,1,0.5,'Less hr on Lenny\'s project due to task completed early'),(11798,'2025-10-08 00:00:00',38,2,45,1,2.33,'Additional hr on Bill\'s project'),(11799,'2025-10-08 00:00:00',33,11,100,9,9,''),(11800,'2025-10-08 00:00:00',32,16,87,4.5,4.5,''),(11801,'2025-10-08 00:00:00',16,16,14,4.5,4.77,''),(11802,'2025-10-08 00:00:00',7,16,16,6,6,''),(11803,'2025-10-08 00:00:00',44,16,14,1,4.5,'Additional hrs on Tineon (Steffen)'),(11804,'2025-10-08 00:00:00',45,16,14,2,2,''),(11805,'2025-10-08 00:00:00',15,16,14,9,9,''),(11806,'2025-10-08 00:00:00',11,11,101,2.5,5,'Additional hrs on Max - WP project'),(11807,'2025-10-08 00:00:00',46,16,14,4.5,4.5,''),(11808,'2025-10-08 00:00:00',11,11,100,4.5,2,'Less hrs on Anything Roatan App Development'),(11809,'2025-10-08 00:00:00',11,11,50,2,2,''),(11810,'2025-10-08 00:00:00',2,2,11,0,0.67,'Additional hr on Romain\'s project'),(11811,'2025-10-08 00:00:00',7,2,33,0,0.5,'Additional hr on Neville\'s project'),(11812,'2025-10-09 00:00:00',22,11,100,4.5,4.5,''),(11813,'2025-10-09 00:00:00',48,16,15,4.5,2,'Less hrs on Deb project'),(11814,'2025-10-09 00:00:00',33,11,100,9,9,''),(11815,'2025-10-09 00:00:00',32,16,87,4.5,4.5,''),(11816,'2025-10-09 00:00:00',16,16,14,2.5,2.68,'Additional hrs on Tineon'),(11817,'2025-10-09 00:00:00',7,16,16,6,6,''),(11818,'2025-10-09 00:00:00',7,2,40,0.5,1,'Additional hr on Elmanar\'s project'),(11819,'2025-10-09 00:00:00',9,2,95,9,9,''),(11820,'2025-10-09 00:00:00',2,2,7,0.5,0.5,''),(11821,'2025-10-09 00:00:00',37,2,90,9,9,''),(11822,'2025-10-09 00:00:00',39,2,95,9,9,''),(11823,'2025-10-09 00:00:00',38,2,4,2,3.5,'Additional hr on Ton\'s project'),(11824,'2025-10-09 00:00:00',2,2,90,0.5,0,'Less hr on MedFi\'s project due to no call yesterday with client'),(11825,'2025-10-09 00:00:00',2,2,22,2.5,2.5,''),(11826,'2025-10-09 00:00:00',38,2,45,0.5,0.5,''),(11827,'2025-10-09 00:00:00',18,18,98,8,8,''),(11828,'2025-10-09 00:00:00',24,18,19,9,9,''),(11829,'2025-10-09 00:00:00',47,17,21,3,4,'Additional on Soren\'s project'),(11830,'2025-10-09 00:00:00',45,16,14,2,2,''),(11831,'2025-10-09 00:00:00',15,16,14,9,9,''),(11832,'2025-10-09 00:00:00',11,11,101,6,7,''),(11833,'2025-10-09 00:00:00',46,16,14,4.5,4.5,'Additional hrs on Max - WP project'),(11834,'2025-10-09 00:00:00',11,16,14,2,1,'Less hrs on Tineon (Steffen)'),(11835,'2025-10-09 00:00:00',11,11,50,1,1,''),(11836,'2025-10-10 00:00:00',18,18,98,8,8,''),(11837,'2025-10-10 00:00:00',24,18,19,9,9,''),(11838,'2025-10-09 00:00:00',2,2,11,0,0.5,'Additional hr on Romain\'s project'),(11839,'2025-10-09 00:00:00',2,2,33,0,0.33,'Additional hr on Neville\'s project'),(11840,'2025-10-10 00:00:00',45,16,14,2,2,''),(11841,'2025-10-10 00:00:00',15,16,14,9,9,''),(11842,'2025-10-10 00:00:00',11,11,101,7,7,''),(11843,'2025-10-10 00:00:00',46,16,14,4.5,4.5,''),(11844,'2025-10-10 00:00:00',11,16,14,2,2,''),(11845,'2025-10-10 00:00:00',9,2,95,9,9,''),(11846,'2025-10-10 00:00:00',37,2,90,9,9,''),(11847,'2025-10-10 00:00:00',39,2,95,9,9,''),(11848,'2025-10-10 00:00:00',38,2,4,2,4.17,'Additional hr on Ton\'s project'),(11849,'2025-10-10 00:00:00',2,2,90,0.5,0.67,'Additional hr on MedFi project'),(11850,'2025-10-10 00:00:00',2,2,22,2.5,2.53,'Additional hr on Eyeplastics project'),(11851,'2025-10-10 00:00:00',22,11,100,4.5,4.5,''),(11852,'2025-10-10 00:00:00',48,16,15,2,0,'Less hrs on PetCloud'),(11853,'2025-10-10 00:00:00',33,11,100,9,9,''),(11854,'2025-10-10 00:00:00',32,16,87,4.5,4.5,''),(11855,'2025-10-10 00:00:00',16,16,14,2.5,2.6,'Additional hrs on Tineon'),(11856,'2025-10-10 00:00:00',7,16,16,6,6,''),(11857,'2025-10-10 00:00:00',45,11,100,3,3,''),(11858,'2025-10-13 00:00:00',18,18,98,8,8,''),(11859,'2025-10-13 00:00:00',24,18,19,9,9,''),(11860,'2025-10-10 00:00:00',2,2,45,0,0.33,'Additional hr on Bill\'s project'),(11861,'2025-10-10 00:00:00',2,2,11,0,0.5,'Additional hr on Romain\'s project'),(11862,'2025-10-13 00:00:00',22,11,100,4.5,4.5,''),(11863,'2025-10-13 00:00:00',48,16,15,4.5,4.5,''),(11864,'2025-10-13 00:00:00',32,16,87,4.5,3,'Less hrs on Smart Rider'),(11865,'2025-10-13 00:00:00',16,16,14,2.5,2.58,'Additional hrs on Tineon'),(11866,'2025-10-13 00:00:00',7,16,16,6,6,''),(11878,'2025-10-13 00:00:00',44,16,14,0.5,0.5,''),(11879,'2025-10-13 00:00:00',45,16,14,2,2,''),(11880,'2025-10-13 00:00:00',15,16,14,9,9,''),(11881,'2025-10-13 00:00:00',11,11,101,7,7,''),(11882,'2025-10-13 00:00:00',46,16,14,4.5,4.5,''),(11883,'2025-10-13 00:00:00',11,16,14,2,2,''),(11884,'2025-10-13 00:00:00',7,2,40,1,1,''),(11885,'2025-10-13 00:00:00',9,2,95,9,9,''),(11886,'2025-10-13 00:00:00',37,2,90,9,9,''),(11887,'2025-10-13 00:00:00',39,2,95,9,9,''),(11888,'2025-10-13 00:00:00',38,2,4,2,2,''),(11889,'2025-10-13 00:00:00',2,2,90,0.5,0.5,''),(11890,'2025-10-13 00:00:00',2,2,22,2.5,2.5,''),(11891,'2025-10-13 00:00:00',7,2,33,0.5,0.5,''),(11892,'2025-10-13 00:00:00',2,2,7,1,1.33,'Additional hr on Chris\'s project'),(11893,'2025-10-13 00:00:00',2,2,48,0.5,1.03,'Additional hr on Romain\'s project'),(11894,'2025-10-13 00:00:00',38,2,44,2,1.67,'Less hr on Lenny\'s project due to waiting for server support'),(11895,'2025-10-13 00:00:00',32,17,75,0,0.5,'Additional on Soren\'s project'),(11896,'2025-10-13 00:00:00',47,17,21,0,1,'Additional on Soren\'s project'),(11897,'2025-10-14 00:00:00',47,17,21,0.5,1.5,'Additional hours on Soren\'s project'),(11898,'2025-10-14 00:00:00',18,18,98,8,8,''),(11899,'2025-10-14 00:00:00',24,18,19,9,9,''),(11900,'2025-10-14 00:00:00',45,16,14,2,2,''),(11901,'2025-10-14 00:00:00',15,16,14,9,9,''),(11902,'2025-10-14 00:00:00',11,11,101,7,7,''),(11903,'2025-10-14 00:00:00',46,16,14,4.5,4.5,''),(11904,'2025-10-14 00:00:00',11,16,14,2,2,''),(11905,'2025-10-14 00:00:00',9,2,95,9,9,''),(11906,'2025-10-14 00:00:00',37,2,90,9,9,''),(11907,'2025-10-14 00:00:00',39,2,95,9,9,''),(11908,'2025-10-14 00:00:00',2,2,90,0.5,0,'Less hr on MedFi due to no call yesterday with client'),(11909,'2025-10-14 00:00:00',2,2,22,2.5,2.5,''),(11910,'2025-10-14 00:00:00',7,2,33,0.5,2.5,'Additional hr on Bluediamond project'),(11911,'2025-10-14 00:00:00',33,11,100,4.5,0,'Jaspreet not join office in second half (Chris project)'),(11912,'2025-10-14 00:00:00',22,11,100,4.5,4.5,''),(11913,'2025-10-14 00:00:00',48,16,15,3,5,'Additional hrs on Deb'),(11914,'2025-10-14 00:00:00',16,16,14,2.5,2.75,'Additional hrs on Tineon'),(11915,'2025-10-14 00:00:00',7,16,16,6,6,''),(11916,'2025-10-14 00:00:00',45,11,100,2,2,''),(11917,'2025-10-15 00:00:00',44,16,14,1,1,''),(11918,'2025-10-15 00:00:00',45,16,14,2,2,''),(11919,'2025-10-15 00:00:00',15,16,14,9,9,''),(11920,'2025-10-15 00:00:00',11,11,101,7,7,''),(11921,'2025-10-15 00:00:00',46,16,14,4.5,4.5,''),(11922,'2025-10-15 00:00:00',11,16,14,2,2,''),(11923,'2025-10-14 00:00:00',32,17,75,0,4,'Additional hours on Soren\'s project'),(11924,'2025-10-15 00:00:00',18,18,98,8,8,''),(11925,'2025-10-15 00:00:00',24,18,19,9,9,''),(11926,'2025-10-14 00:00:00',38,2,4,0,1.17,'Additional hr on Ton\'s project'),(11927,'2025-10-15 00:00:00',9,2,95,9,9,''),(11928,'2025-10-15 00:00:00',37,2,90,9,9,''),(11929,'2025-10-15 00:00:00',39,2,95,9,9,''),(11930,'2025-10-15 00:00:00',2,2,90,0.5,0.67,'Additional hr on MedFi\'s project'),(11931,'2025-10-15 00:00:00',2,2,22,2.5,2.5,''),(11932,'2025-10-15 00:00:00',7,2,33,2,2,''),(11933,'2025-10-15 00:00:00',33,11,100,9,9,''),(11934,'2025-10-15 00:00:00',22,11,100,4.5,4.5,''),(11935,'2025-10-15 00:00:00',48,16,15,4.5,4.5,''),(11936,'2025-10-15 00:00:00',16,16,14,2.5,2.57,'Additional hrs on Tineon'),(11937,'2025-10-15 00:00:00',7,16,16,6,6,''),(11938,'2025-10-15 00:00:00',22,16,15,4.5,3,'Less hrs on Deb project'),(11939,'2025-10-16 00:00:00',45,11,14,2,NULL,''),(11940,'2025-10-16 00:00:00',15,11,14,9,NULL,''),(11941,'2025-10-16 00:00:00',11,11,101,7,NULL,''),(11942,'2025-10-16 00:00:00',46,11,14,4.5,NULL,''),(11943,'2025-10-16 00:00:00',11,11,14,2,NULL,''),(11944,'2025-10-15 00:00:00',44,2,4,0,1,'Additional hr on Ton\'s project'),(11945,'2025-10-16 00:00:00',18,18,98,8,NULL,''),(11946,'2025-10-16 00:00:00',24,18,19,9,NULL,''),(11947,'2025-10-16 00:00:00',33,16,100,9,NULL,''),(11948,'2025-10-16 00:00:00',22,16,100,4.5,NULL,''),(11949,'2025-10-16 00:00:00',48,16,15,4.5,NULL,''),(11950,'2025-10-16 00:00:00',16,16,14,2.5,NULL,''),(11951,'2025-10-16 00:00:00',7,16,16,6,NULL,''),(11952,'2025-10-16 00:00:00',22,16,15,4.5,NULL,''),(11953,'2025-10-16 00:00:00',38,2,4,1,NULL,''),(11954,'2025-10-16 00:00:00',9,2,95,9,NULL,''),(11955,'2025-10-16 00:00:00',37,2,90,9,NULL,''),(11956,'2025-10-16 00:00:00',39,2,95,9,NULL,''),(11957,'2025-10-16 00:00:00',2,2,90,1,NULL,''),(11958,'2025-10-16 00:00:00',2,2,22,2.5,NULL,''),(11959,'2025-10-16 00:00:00',7,2,33,1,NULL,''),(11960,'2025-10-16 00:00:00',2,2,7,1,NULL,'');

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `last_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `hashed_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `super_user` tinyint(1) DEFAULT '0',
  `role_id` int NOT NULL,
  `developer_id` int DEFAULT NULL,
  `zoho_refresh_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zoho_access_token` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zoho_expires_in` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (3,'Govind','Namdev','govind@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2024-12-10 05:34:04',0,2,1,NULL,NULL,NULL),(4,'Manish','Pathak','manish@mangoitsolutions.com','$2b$10$AuV0wDGNYH.YffDzUaBygeX9lJTaNqDHfBYTjoF7gI66rkyHvJDC6','2024-04-05 07:55:08','2024-09-24 07:25:54',1,1,0,NULL,NULL,NULL),(5,'Manish1','Pathak1','manish1@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:55:08','2024-09-24 07:25:57',1,1,0,NULL,NULL,NULL),(6,'Ashish','Gavshinde','ashish@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2025-10-16 07:01:37',0,2,2,'1000.9733196565a3519c0a8ed9d85fe3beb7.ee670034f4fb5cded3114048e9e10782','1000.44a1138af1c6098fd9c5e438461ab4f6.ca3924d1ecbe4ca750873ea464196fcb',1760594496),(7,'Vivek','Pateria','vivekp@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2025-10-09 07:01:34',0,2,17,'1000.4d5d0f1b189d3c0e8942fabc6adec3c9.b0ae8128638c215f490fa0bd1be7ca52','1000.b6c1b6fd5e115da076765c99a16e2b15.21fbd8ea263226f31a7b51ed1b6fb1f4',1759989693),(8,'JP','Tiwari','jp@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2025-10-15 07:17:20',0,2,16,'1000.9a9749c47d4acd18a7b5170a61715e75.78544d733475dd272106910ca4255f20','1000.37a6074ae62021943de9a49d595a9a99.eb91176e9eeb4aeec38baaf6c8f962af',1760509039),(9,'Tarun','Pasangya','tarun@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2024-09-24 07:26:09',0,2,11,NULL,NULL,NULL),(10,'Pritesh','Khandelwal','pritesh@mangoitsolutions.com','$2b$10$6j8d3m07QqoozO8k6FsjFOdIM3j1eRXA/Qn17WZCOg5SHduroWQvW','2024-04-05 07:43:52','2025-10-03 09:54:58',0,2,18,NULL,NULL,NULL);

--
-- Dumping routines for database 'mis_live'
--
/*!50003 DROP PROCEDURE IF EXISTS `delete_developer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_developer`(IN `p_developer_id` INT)
BEGIN
    DECLARE dev_exists INT;

    
    SELECT COUNT(*) INTO dev_exists
    FROM time_sheet_data
    WHERE developer_id = p_developer_id OR team_lead_id = p_developer_id;

    
    IF dev_exists = 0 THEN
        
        DELETE FROM developer_reporting_manager WHERE developer_id = p_developer_id;

        
        DELETE FROM master_developer WHERE id = p_developer_id;

        
        SELECT ROW_COUNT() AS affected_rows;
    ELSE
        
        SELECT 0 AS affected_rows;
    END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getLastTimesheetForTpl` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getLastTimesheetForTpl`(
    IN developerIds VARCHAR(255),   -- Comma-separated list of developer IDs
    IN input_team_lead_id INT
)
BEGIN
    DECLARE sql_query TEXT;

    -- Build dynamic query
    SET sql_query = CONCAT(
        'SELECT ts1.id, ts1.date, ts1.developer_id, ts1.project_id, ts1.hours, ts1.actual_hours, ts1.remark, mp.tpl_id
         FROM time_sheet_data ts1
         JOIN (
             SELECT 
                 ts.developer_id, 
                 MAX(ts.`date`) AS last_date
             FROM 
                 time_sheet_data ts
             JOIN 
                 master_projects mp ON ts.project_id = mp.id
             WHERE 
                 ts.developer_id IN (', developerIds, ')
                 AND (mp.tpl_id = ', input_team_lead_id, ' OR mp.team_lead_id = ', input_team_lead_id, ')
             GROUP BY 
                 ts.developer_id
         ) ts2 
         ON ts1.developer_id = ts2.developer_id 
         AND ts1.`date` = ts2.last_date
         JOIN 
             master_projects mp ON ts1.project_id = mp.id 
         WHERE 
             (mp.tpl_id = ', input_team_lead_id, ' OR mp.team_lead_id = ', input_team_lead_id, ')'
    );

    -- Assign to user-defined variable
    SET @final_sql = sql_query;

    -- Prepare and execute
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getLastTimesheetForTpl_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getLastTimesheetForTpl_bk`(IN `developerIds` VARCHAR(255), IN `input_team_lead_id` INT)
BEGIN
    
    SET @sql = CONCAT('
        SELECT ts1.id, ts1.date, ts1.developer_id, ts1.project_id, ts1.hours, ts1.actual_hours, remark, mp.tpl_id
        FROM time_sheet_data ts1
        JOIN (
            SELECT 
                ts.developer_id, 
                MAX(ts.`date`) AS last_date
            FROM 
                time_sheet_data ts
            JOIN 
                master_projects mp ON ts.project_id = mp.id
            WHERE 
                ts.developer_id IN (', developerIds, ')
                AND mp.tpl_id = ', input_team_lead_id, '
            GROUP BY 
                ts.developer_id
        ) ts2 ON ts1.developer_id = ts2.developer_id 
        AND ts1.`date` = ts2.last_date
        JOIN 
            master_projects mp ON ts1.project_id = mp.id 
        WHERE 
            mp.tpl_id = ', input_team_lead_id);

    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_dashboard_data` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_dashboard_data`(IN `previousDays` BOOLEAN, IN `fromDate` DATE, IN `toDate` DATE)
BEGIN
    DECLARE latest_date DATETIME;
    DECLARE previous_date DATETIME;
    DECLARE limit_of_records INT;
    DECLARE where_condition VARCHAR(1000);  

    
    IF previousDays THEN
        SELECT value INTO limit_of_records 
        FROM configuration 
        WHERE key_name = 'max_fifteen';

        
        SELECT DATE_FORMAT(date, '%Y-%m-%d') 
        INTO latest_date 
        FROM time_sheet_data 
        ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
        LIMIT 1;

        
        SELECT DATE_FORMAT(date, '%Y-%m-%d') AS previous_date
        INTO previous_date
        FROM time_sheet_data 
        WHERE DATE_FORMAT(date, '%Y-%m-%d') 
        BETWEEN (
            SELECT DISTINCT DATE_FORMAT(date, '%Y-%m-%d') 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
            LIMIT limit_of_records, 1
        ) 
        AND (
            SELECT DISTINCT DATE_FORMAT(date, '%Y-%m-%d') 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') 
            DESC LIMIT 1, 1
        )
        GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
        ORDER BY SUM(actual_hours) DESC 
        LIMIT 1;

        
        SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') IN (',
            QUOTE(DATE_FORMAT(previous_date, '%Y-%m-%d')), ', ',
            QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')), ')');

    ELSE
        
        IF fromDate IS NULL AND toDate IS NULL THEN
            
            SELECT DATE_FORMAT(date, '%Y-%m-%d') 
            INTO latest_date 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
            LIMIT 1;

            SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')));

        ELSE
            
            IF toDate IS NULL THEN
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')));
            ELSE
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') BETWEEN ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')), ' AND ',
                    QUOTE(DATE_FORMAT(toDate, '%Y-%m-%d')));
            END IF;
        END IF;
    END IF;

    
    SET @sql = CONCAT('
        SELECT 
            t.id,
            t.developer_id,
            t.project_id,
            p.tpl_id,
            p.team_lead_id,
            d.name AS developer_name,
            CASE 
                WHEN tl.name IS NULL THEN d.name
                ELSE tl.name
            END AS tl_name,
            p.project_name,
            t.date,
            t.hours,
            t.actual_hours,
            t.remark,
            d.department
        FROM 
            time_sheet_data AS t
        LEFT JOIN 
            master_projects AS p ON p.id = t.project_id
        LEFT JOIN 
            master_developer AS d ON d.id = t.developer_id
        LEFT JOIN 
            master_developer AS tl ON tl.id = p.team_lead_id
        ', where_condition, '
        ORDER BY 
            t.project_id, t.date DESC;
    ');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_dashboard_data_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_dashboard_data_bk`(IN `previousDays` BOOLEAN, IN `fromDate` DATE, IN `toDate` DATE)
BEGIN
    DECLARE latest_date DATETIME;
    DECLARE previous_date DATETIME;
    DECLARE limit_of_records INT;
    DECLARE where_condition VARCHAR(1000);  

    
    IF previousDays THEN
        SELECT value INTO limit_of_records 
        FROM configuration 
        WHERE key_name = 'max_fifteen';

        
        SELECT DATE_FORMAT(date, '%Y-%m-%d') 
        INTO latest_date 
        FROM time_sheet_data 
        ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
        LIMIT 1;

        
        SELECT DATE_FORMAT(date, '%Y-%m-%d') AS previous_date
        INTO previous_date
        FROM time_sheet_data 
        WHERE DATE_FORMAT(date, '%Y-%m-%d') 
        BETWEEN (
            SELECT DISTINCT DATE_FORMAT(date, '%Y-%m-%d') 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
            LIMIT limit_of_records, 1
        ) 
        AND (
            SELECT DISTINCT DATE_FORMAT(date, '%Y-%m-%d') 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') 
            DESC LIMIT 1, 1
        )
        GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
        ORDER BY SUM(actual_hours) DESC 
        LIMIT 1;

        
        SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') IN (',
            QUOTE(DATE_FORMAT(previous_date, '%Y-%m-%d')), ', ',
            QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')), ')');

    ELSE
        
        IF fromDate IS NULL AND toDate IS NULL THEN
            
            SELECT DATE_FORMAT(date, '%Y-%m-%d') 
            INTO latest_date 
            FROM time_sheet_data 
            ORDER BY DATE_FORMAT(date, '%Y-%m-%d') DESC 
            LIMIT 1;

            SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')));

        ELSE
            
            IF toDate IS NULL THEN
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')));
            ELSE
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') BETWEEN ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')), ' AND ',
                    QUOTE(DATE_FORMAT(toDate, '%Y-%m-%d')));
            END IF;
        END IF;
    END IF;

    
    SET @sql = CONCAT('
        SELECT 
            t.id,
            t.developer_id,
            t.project_id,
            p.tpl_id,
            p.team_lead_id,
            d.name AS developer_name,
            CASE 
                WHEN tl.name IS NULL THEN d.name
                ELSE tl.name
            END AS tl_name,
            p.project_name,
            t.date,
            t.hours,
            t.actual_hours,
            t.remark
        FROM 
            time_sheet_data AS t
        LEFT JOIN 
            master_projects AS p ON p.id = t.project_id
        LEFT JOIN 
            master_developer AS d ON d.id = t.developer_id
        LEFT JOIN 
            master_developer AS tl ON tl.id = p.team_lead_id
        ', where_condition, '
        ORDER BY 
            t.project_id, t.date DESC;
    ');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_project_developer_reports` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_project_developer_reports`(IN `p_year` INT, IN `p_month` VARCHAR(3), IN `r_type` VARCHAR(10), IN `p_project_id` INT, IN `p_developer_id` INT)
BEGIN
    DECLARE dynamic_query VARCHAR(4000);
    DECLARE first_day DATE;
    DECLARE last_day DATE;
    DECLARE table_name VARCHAR(50);
    DECLARE id_field VARCHAR(50);
    DECLARE name_field VARCHAR(50);
    DECLARE join_condition VARCHAR(100);
    DECLARE TPL_TL_join VARCHAR(200);
    DECLARE dynamic_query_for_tpl_tl VARCHAR(200);

	SET @row_number = 0;

    IF r_type = 'project' THEN
        SET table_name = 'master_projects';
        SET id_field = 'project_id';
        SET name_field = 'project_name';
        SET join_condition = CONCAT('md.id = tsd.project_id AND YEAR(tsd.date) = ', p_year);
    ELSEIF r_type = 'developer' THEN
        SET table_name = 'master_developer';
        SET id_field = 'developer_id';
        SET name_field = 'name';
        SET join_condition = CONCAT('md.id = tsd.developer_id AND YEAR(tsd.date) = ', p_year);
    END IF;

    SET TPL_TL_join = ' LEFT JOIN master_developer tpl ON md.tpl_id = tpl.id
                    LEFT JOIN master_developer tl ON md.team_lead_id = tl.id';
    SET dynamic_query_for_tpl_tl = ', tpl.name as "tpl_name", tl.name as "tl_name"';

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.name');
    ELSEIF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.project_name AS name ' , dynamic_query_for_tpl_tl);
    ELSE
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.', name_field ,' AS name ', IF(r_type = 'project', dynamic_query_for_tpl_tl , ''));
    END IF;

    IF p_month IS NOT NULL AND p_month <> '' THEN

        
        SET first_day = STR_TO_DATE(CONCAT(p_year, '-', p_month, '-01'), '%Y-%b-%d');
        SET last_day = LAST_DAY(first_day);        

        SET @dynamic_query = CONCAT(@dynamic_query, 
            ', IFNULL(ROUND(SUM(CASE WHEN YEAR(date) = ', p_year ,' AND MONTH(date) = ',MONTH(first_day),' THEN actual_hours END), 2), "-") AS Total');


        SET @date = first_day;
        WHILE @date <= last_day DO
            SET @dynamic_query = CONCAT(@dynamic_query, 
                ', IFNULL(ROUND(SUM(CASE WHEN DATE(date) = "', DATE_FORMAT(@date, '%Y-%m-%d'), '" THEN actual_hours END), 2), "-") AS "', DATE_FORMAT(@date, '%d-%m-%Y'), '"');
            SET @date = DATE_ADD(@date, INTERVAL 1 DAY);
        END WHILE;

        IF p_project_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_developer md LEFT JOIN time_sheet_data tsd ON md.id = tsd.developer_id AND ( YEAR(date) = ', p_year, ' AND MONTH(date) = ', MONTH(first_day), ')');
        ELSEIF p_developer_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_projects md LEFT JOIN time_sheet_data tsd ON md.id = tsd.project_id AND ( YEAR(date) = ', p_year, ' AND MONTH(date) = ', MONTH(first_day), ') ', TPL_TL_join);
        ELSE
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM ', table_name, ' AS md LEFT JOIN time_sheet_data tsd ON md.id = tsd.', id_field, ' AND ( YEAR(date) = ', p_year, ' OR MONTH(date) = ', MONTH(first_day), ')' , IF(r_type = 'project', TPL_TL_join , ''));
        END IF;
        
    ELSE       
        SET @dynamic_query = CONCAT(@dynamic_query, 
            ', IFNULL(ROUND(SUM(CASE WHEN YEAR(date) = ', p_year ,' THEN actual_hours END), 2), "-") AS Total');
         SET @dynamic_query = CONCAT(@dynamic_query, 
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 1 THEN actual_hours END), 2), "-") AS Jan',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 2 THEN actual_hours END), 2), "-") AS Feb',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 3 THEN actual_hours END), 2), "-") AS Mar',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 4 THEN actual_hours END), 2), "-") AS Apr',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 5 THEN actual_hours END), 2), "-") AS May',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 6 THEN actual_hours END), 2), "-") AS Jun',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 7 THEN actual_hours END), 2), "-") AS Jul',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 8 THEN actual_hours END), 2), "-") AS Aug',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 9 THEN actual_hours END), 2), "-") AS Sep',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 10 THEN actual_hours END), 2), "-") AS Oct',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 11 THEN actual_hours END), 2), "-") AS Nov',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 12 THEN actual_hours END), 2), "-") AS "Dec"'
            );
        IF p_project_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_developer md LEFT JOIN time_sheet_data tsd ON md.id = tsd.developer_id AND YEAR(date) = ', p_year);
        ELSEIF p_developer_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_projects md LEFT JOIN time_sheet_data tsd ON md.id = tsd.project_id AND YEAR(date) = ', p_year , TPL_TL_join);
        ELSE 
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM ', table_name ,' md LEFT JOIN time_sheet_data tsd ON ', join_condition , IF(r_type = 'project', TPL_TL_join , ''));
        END IF;

    END IF;

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' WHERE tsd.project_id = ', p_project_id);
    END IF;

    IF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' WHERE tsd.developer_id = ', p_developer_id);
    END IF;

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.name ');
    ELSEIF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.project_name , tpl.name, tl.name');
    ELSE
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.', name_field, IF(r_type = 'project', ', tpl.name, tl.name' , ''));
    END IF;

    
    PREPARE stmt FROM @dynamic_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_developer_with_reporting_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_developer_with_reporting_managers`(
    IN `p_name` VARCHAR(255),
    IN `p_team_lead` TINYINT(1),
    IN `p_reporting_manager_ids` TEXT,
    IN `p_zoho_email` VARCHAR(255),
    IN `p_tl_hours` TEXT,
    IN `p_department` VARCHAR(255)
)
BEGIN
    DECLARE v_developer_id INT;
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    
    SET i = 0;

    
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    
    INSERT INTO master_developer (name, team_lead, zoho_email_id, available_hours, reporting_manager_id, department)
    VALUES (p_name, p_team_lead, p_zoho_email, v_total_hours, v_reporting_manager_id, p_department);

    SET v_developer_id = LAST_INSERT_ID();

    
    IF v_reporting_manager_id IS NULL THEN
        UPDATE master_developer SET reporting_manager_id = v_developer_id WHERE id = v_developer_id;
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (v_developer_id, v_developer_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (v_developer_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    
    SELECT v_developer_id AS insertId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_developer_with_reporting_managers_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_developer_with_reporting_managers_bk`(IN `p_name` VARCHAR(255), IN `p_team_lead` BOOLEAN, IN `p_reporting_manager_ids` JSON, IN `p_zoho_email` VARCHAR(255), IN `p_zoho_hours` DECIMAL(10.2))
BEGIN
    DECLARE v_developer_id INT;
    DECLARE v_reporting_manager_id INT;
    DECLARE i INT DEFAULT 0;
    
    
    IF JSON_EXTRACT(p_reporting_manager_ids, '$[0]') = 0 THEN
        
        INSERT INTO master_developer (name, team_lead, zoho_email_id, available_hours, reporting_manager_id) 
        VALUES (p_name, p_team_lead, p_zoho_email, p_zoho_hours, NULL);
        
        
        SET v_developer_id = LAST_INSERT_ID();
        
        
        UPDATE master_developer SET reporting_manager_id = v_developer_id WHERE id = v_developer_id;
        
        
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (v_developer_id, v_developer_id, p_zoho_hours);
    ELSE
        
        SET v_reporting_manager_id = JSON_EXTRACT(p_reporting_manager_ids, '$[0]');
        
        INSERT INTO master_developer (name, team_lead, zoho_email_id, available_hours, reporting_manager_id) 
        VALUES (p_name, p_team_lead, p_zoho_email, p_zoho_hours, v_reporting_manager_id);
        
        SET v_developer_id = LAST_INSERT_ID();
        
        WHILE i < JSON_LENGTH(p_reporting_manager_ids) DO
            SET v_reporting_manager_id = JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']'));
            
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (v_developer_id, v_reporting_manager_id, p_zoho_hours);
            
            SET i = i + 1;
        END WHILE;
    END IF;
    
    
    SELECT v_developer_id AS new_developer_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_developer_with_reporting_managers_bk2` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_developer_with_reporting_managers_bk2`(IN `p_name` VARCHAR(255), IN `p_team_lead` TINYINT(1), IN `p_reporting_manager_ids` TEXT, IN `p_zoho_email` VARCHAR(255), IN `p_tl_hours` TEXT)
BEGIN
    DECLARE v_developer_id INT;
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    -- Calculate total hours
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    -- Reset loop counter
    SET i = 0;

    -- Get primary reporting manager (self if 0)
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    -- Insert developer
    INSERT INTO master_developer (name, team_lead, zoho_email_id, available_hours, reporting_manager_id)
    VALUES (p_name, p_team_lead, p_zoho_email, v_total_hours, v_reporting_manager_id);

    SET v_developer_id = LAST_INSERT_ID();

    -- If self-reporting, update the developer's own ID
    IF v_reporting_manager_id IS NULL THEN
        UPDATE master_developer SET reporting_manager_id = v_developer_id WHERE id = v_developer_id;
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (v_developer_id, v_developer_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (v_developer_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    -- Return the inserted ID
    SELECT v_developer_id AS insertId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `saveOrUpdateTimesheets` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `saveOrUpdateTimesheets`(IN `timesheetData` JSON)
BEGIN
    DECLARE timesheetDate DATE;
    DECLARE developerId INT;
    DECLARE projectId INT;
    DECLARE teamLeadId INT;
    DECLARE hours FLOAT;
    DECLARE actualHours FLOAT;
    DECLARE remark VARCHAR(300);
    DECLARE cursor_index INT DEFAULT 0;
    DECLARE json_length INT;
    DECLARE teamLeadToDelete INT;

    -- Step 1: Loop through JSON to delete today's data for team_leads present in JSON
    SET json_length = JSON_LENGTH(timesheetData);

    WHILE cursor_index < json_length DO
        SET teamLeadToDelete = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].team_lead_id')));
        SET actualHours = CASE
            WHEN JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours')) IS NULL
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = '' 
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = 'null'
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) REGEXP '^[0-9]+(.[0-9]+)?$'
                THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) AS DECIMAL(10, 2))
            ELSE NULL
        END;
        IF teamLeadToDelete IS NOT NULL AND teamLeadToDelete != '' AND actualHours IS NULL THEN
            DELETE FROM time_sheet_data
            WHERE DATE(`date`) = CURDATE()
              AND team_lead_id = teamLeadToDelete;
        END IF;

        SET cursor_index = cursor_index + 1;
    END WHILE;

    -- Reset cursor for main loop
    SET cursor_index = 0;

    -- Step 2: Loop again to insert or update
    WHILE cursor_index < json_length DO

        SET timesheetDate = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].date')));
        SET developerId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].developer_id')));
        SET projectId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].project_id')));
        SET teamLeadId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].team_lead_id')));
        SET hours = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].hours')));

        SET actualHours = CASE
            WHEN JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours')) IS NULL
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = '' 
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = 'null'
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) REGEXP '^[0-9]+(.[0-9]+)?$'
                THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) AS DECIMAL(10, 2))
            ELSE NULL
        END;

        SET remark = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].remark')));

        IF EXISTS (
            SELECT 1 FROM time_sheet_data
            WHERE DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId
        ) THEN
            UPDATE time_sheet_data
            SET 
                team_lead_id = teamLeadId,
                actual_hours = actualHours,
                remark = remark,
                `date` = CONCAT(timesheetDate, ' ', TIME(`date`))
            WHERE 
                DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId;
        ELSE
            IF NOT (DATE(timesheetDate) = CURDATE() AND hours = 0) THEN
                INSERT INTO time_sheet_data (
                    `date`, developer_id, project_id, team_lead_id, hours, actual_hours, remark
                ) VALUES (
                    CONCAT(timesheetDate, ' 00:00:00'),
                    developerId, projectId, teamLeadId, hours, actualHours, remark
                );
            END IF;
        END IF;

        SET cursor_index = cursor_index + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `saveOrUpdateTimesheets_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `saveOrUpdateTimesheets_bk`(IN `timesheetData` JSON)
BEGIN
    DECLARE timesheetDate DATE;
    DECLARE developerId INT;
    DECLARE projectId INT;
    DECLARE teamLeadId INT;
    DECLARE hours FLOAT;
    DECLARE actualHours FLOAT;
    DECLARE remark VARCHAR(300);
    DECLARE cursor_index INT DEFAULT 0;
    DECLARE json_length INT;

    
    SET json_length = JSON_LENGTH(timesheetData);

    
    WHILE cursor_index < json_length DO
        
        SET timesheetDate = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].date')));
        SET developerId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].developer_id')));
        SET projectId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].project_id')));
        SET teamLeadId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].team_lead_id')));
        SET hours = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].hours')));
        
		SET actualHours = CASE
			WHEN JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours')) IS NULL
				THEN NULL
			WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = '' 
				THEN NULL
			WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = 'null'
				THEN NULL
			WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) REGEXP '^[0-9]+(\.[0-9]+)?$'
				THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) AS DECIMAL(10, 2))
			ELSE NULL
		END;
                    
        SET remark = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].remark')));

        
        IF EXISTS (
            SELECT 1 FROM time_sheet_data
            WHERE DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId
        ) THEN
            
            UPDATE time_sheet_data
            SET 
                team_lead_id = teamLeadId,
                hours = hours,
                actual_hours = actualHours,
                remark = remark,
                `date` = CONCAT(timesheetDate, ' ', TIME(`date`))  
            WHERE 
                DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId;
        ELSE
            
            INSERT INTO time_sheet_data (
                `date`, developer_id, project_id, team_lead_id, hours, actual_hours, remark
            ) VALUES (
                CONCAT(timesheetDate, ' 00:00:00'),  
                developerId, projectId, teamLeadId, hours, actualHours, remark
            );
        END IF;

        
        SET cursor_index = cursor_index + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_and_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_and_managers`(IN `dev_id` INT, IN `dev_data` JSON)
BEGIN
    
    DECLARE done INT DEFAULT FALSE;
    DECLARE manager_id INT;

    
    DECLARE manager_cursor CURSOR FOR 
        SELECT 
            JSON_UNQUOTE(JSON_EXTRACT(m, '$.tpl_id')) 
        FROM JSON_TABLE(JSON_EXTRACT(dev_data, '$.tpl_list'), '$[*]' COLUMNS (m JSON PATH '$')) AS tmp;

    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        
        ROLLBACK;
    END;

    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    
    UPDATE master_developer
    SET 
        name = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.name')),
        status = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.status')),
        available_hours = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.available_hours'))
    WHERE id = dev_id;

    
    DELETE FROM developer_reporting_manager WHERE developer_id = dev_id;

    
    OPEN manager_cursor;
    read_loop: LOOP
        FETCH manager_cursor INTO manager_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id) 
        VALUES (dev_id, manager_id);
    END LOOP;
    CLOSE manager_cursor;

    
    COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_attendance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_attendance`(IN `attendance_data` JSON)
BEGIN
    DECLARE dev_id INT;
    DECLARE tl_id INT;
    DECLARE att_date DATE;
    DECLARE att_value FLOAT; 
    DECLARE i INT DEFAULT 0;
    DECLARE total_records INT;

    
    SET total_records = JSON_LENGTH(attendance_data);

    
    WHILE i < total_records DO
        
        SET dev_id = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].developer_id')));
        SET tl_id = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].tl_id')));
        SET att_date = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance_date')));
        SET att_value = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance')));

        
        IF EXISTS (
            SELECT 1
            FROM developer_attendance
            WHERE developer_id = dev_id AND attendance_date = att_date
        ) THEN
            
            UPDATE developer_attendance
            SET attendance = att_value,
                updated_at = CURRENT_TIMESTAMP
            WHERE developer_id = dev_id AND attendance_date = att_date;
        ELSE
            
            INSERT INTO developer_attendance (developer_id, attendance_date, attendance, created_at, updated_at)
            VALUES (dev_id, att_date, att_value, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
        END IF;

        IF att_value IN (0, 2) THEN
            DELETE FROM time_sheet_data
            WHERE developer_id = dev_id
              AND team_lead_id = tl_id
              AND date = att_date;
        END IF;

        SET i = i + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_attendance_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_attendance_bk`(IN `attendance_data` JSON)
BEGIN
    DECLARE dev_id INT;
    DECLARE att_date DATE;
    DECLARE att_value FLOAT; 
    DECLARE i INT DEFAULT 0;
    DECLARE total_records INT;

    
    SET total_records = JSON_LENGTH(attendance_data);

    
    WHILE i < total_records DO
        
        SET dev_id = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].developer_id')));
        SET att_date = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance_date')));
        SET att_value = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance')));

        
        IF EXISTS (
            SELECT 1
            FROM developer_attendance
            WHERE developer_id = dev_id AND attendance_date = att_date
        ) THEN
            
            UPDATE developer_attendance
            SET attendance = att_value,
                updated_at = CURRENT_TIMESTAMP
            WHERE developer_id = dev_id AND attendance_date = att_date;
        ELSE
            
            INSERT INTO developer_attendance (developer_id, attendance_date, attendance, created_at, updated_at)
            VALUES (dev_id, att_date, att_value, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
        END IF;

        
        SET i = i + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_with_reporting_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_with_reporting_managers`(
    IN `p_id` INT,
    IN `p_name` VARCHAR(255),
    IN `p_team_lead` BOOLEAN,
    IN `p_reporting_manager_ids` TEXT,
    IN `p_zoho_email_id` VARCHAR(255),
    IN `p_tl_hours` TEXT,
    IN `p_status` VARCHAR(50),
    IN `p_department` VARCHAR(255)
)
BEGIN
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    
    SET i = 0;

    
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    
    UPDATE master_developer 
    SET 
        name = p_name,
        team_lead = p_team_lead,
        zoho_email_id = p_zoho_email_id,
        available_hours = v_total_hours,
        reporting_manager_id = IFNULL(v_reporting_manager_id, p_id),
        status = p_status,
        department = p_department
    WHERE id = p_id;

    
    DELETE FROM developer_reporting_manager WHERE developer_id = p_id;

    
    IF v_reporting_manager_id IS NULL THEN
        
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (p_id, p_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (p_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    
    SELECT p_id AS updated_developer_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_with_reporting_managers_bk2` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_with_reporting_managers_bk2`(IN `p_id` INT, IN `p_name` VARCHAR(255), IN `p_team_lead` BOOLEAN, IN `p_reporting_manager_ids` TEXT, IN `p_zoho_email_id` VARCHAR(255), IN `p_tl_hours` TEXT, IN `p_status` VARCHAR(50))
BEGIN
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    -- Calculate total hours
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    -- Reset loop counter
    SET i = 0;

    -- Get primary reporting manager (self if 0)
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    -- Update master_developer
    UPDATE master_developer 
    SET 
        name = p_name,
        team_lead = p_team_lead,
        zoho_email_id = p_zoho_email_id,
        available_hours = v_total_hours,
        reporting_manager_id = IFNULL(v_reporting_manager_id, p_id),
        status = p_status
    WHERE id = p_id;

    -- Delete existing reporting managers
    DELETE FROM developer_reporting_manager WHERE developer_id = p_id;

    -- If self-reporting
    IF v_reporting_manager_id IS NULL THEN
        -- Self reporting
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (p_id, p_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (p_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    -- Return the updated ID
    SELECT p_id AS updated_developer_id;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_wit_reporting_managers_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_wit_reporting_managers_bk`(IN `p_id` INT, IN `p_name` VARCHAR(255), IN `p_team_lead` BOOLEAN, IN `p_reporting_manager_id` JSON, IN `p_zoho_email_id` VARCHAR(255), IN `p_available_hours` DECIMAL(10.2), IN `p_status` VARCHAR(50))
BEGIN
    IF JSON_EXTRACT(p_reporting_manager_id, '$[0]') = 0 THEN
        
        UPDATE master_developer 
        SET name = p_name, 
            team_lead = p_team_lead, 
            zoho_email_id = p_zoho_email_id, 
            available_hours = p_available_hours,
            status = p_status,
            reporting_manager_id = p_id
        WHERE id = p_id;

        
        DELETE FROM developer_reporting_manager WHERE developer_id = p_id;

        
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (p_id, p_id, p_available_hours);
    ELSE
        
        UPDATE master_developer 
        SET name = p_name, 
            team_lead = p_team_lead, 
            zoho_email_id = p_zoho_email_id, 
            available_hours = p_available_hours,
            status = p_status,
            reporting_manager_id = JSON_EXTRACT(p_reporting_manager_id, '$[0]')
        WHERE id = p_id;

        
        DELETE FROM developer_reporting_manager WHERE developer_id = p_id;

        
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        SELECT p_id, jt.value, p_available_hours
        FROM JSON_TABLE(p_reporting_manager_id, '$[*]' COLUMNS (value INT PATH '$')) AS jt;
    END IF;

    
    SELECT p_id AS updated_developer_id;
END ;;
DELIMITER ;


