<%@ WebHandler Language="C#" Class="Api" %> using System; using System.Web; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.IO; using System.Collections.Generic; using Newtonsoft.Json; using System.Net; using System.Globalization; using getepay_sdk; public class Api : IHttpHandler { private string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public void ProcessRequest (HttpContext context) { // 1. Enable CORS for Flutter app integration (mobile/web) context.Response.Headers.Add("Access-Control-Allow-Origin", "*"); context.Response.Headers.Add("Access-Control-Allow-Headers", "Content-Type, Accept"); context.Response.Headers.Add("Access-Control-Allow-Methods", "GET, POST, OPTIONS"); context.Response.ContentType = "application/json"; // Handle CORS Preflight OPTIONS requests if (context.Request.HttpMethod == "OPTIONS") { context.Response.StatusCode = 200; context.Response.End(); return; } string action = context.Request.QueryString["action"]; if (string.IsNullOrEmpty(action)) { SendError(context, 400, "Action parameter is missing. Please use ?action=members, etc."); return; } try { switch (action.ToLower()) { case "members": HandleMembers(context); break; case "member_directory": HandleMemberDirectory(context); break; case "member_details": HandleMemberDetails(context); break; case "notices": HandleNotices(context); break; case "announcement": case "announcements": HandleAnnouncement(context); break; case "communications": HandleCommunications(context); break; case "communicationdetails": case "communication_details": HandleCommunicationDetails(context); break; case "forms": case "ndbaforms": case "ndba_forms": HandleForms(context); break; case "albums": HandleAlbums(context); break; case "photos": HandlePhotos(context); break; case "videos": HandleVideos(context); break; case "video_details": HandleVideoDetails(context); break; case "office_bearers": HandleOfficeBearers(context); break; case "committee_members": case "committeemembers": HandleCommitteeMembers(context); break; case "todays_birthdays": case "todaysbirthdays": HandleTodaysBirthdays(context); break; case "banners": HandleBanners(context); break; case "calculate_dues": HandleCalculateDues(context); break; case "member_ledger": HandleMemberLedger(context); break; case "send_otp": HandleSendOtp(context); break; case "apply_membership": if (context.Request.HttpMethod == "POST") { HandleApplyMembership(context); } else { SendError(context, 405, "HTTP Method not allowed. Use POST."); } break; case "initiate_payment": if (context.Request.HttpMethod == "POST") { HandleInitiatePayment(context); } else { SendError(context, 405, "HTTP Method not allowed. Use POST."); } break; case "verify_payment": HandleVerifyPayment(context); break; case "request_idcard": if (context.Request.HttpMethod == "POST") { HandleRequestIdCard(context); } else { SendError(context, 405, "HTTP Method not allowed. Use POST."); } break; case "get_kyc_history": HandleGetKycHistory(context); break; case "admin_get_kyc_requests": HandleAdminGetKycRequests(context); break; case "admin_update_kyc_status": if (context.Request.HttpMethod == "POST") { HandleAdminUpdateKycStatus(context); } else { SendError(context, 405, "HTTP Method not allowed. Use POST."); } break; case "member_login": HandleMemberLogin(context); break; case "member_login_password": HandleMemberLoginPassword(context); break; case "change_password": case "member_change_password": HandleChangePassword(context); break; case "member_register": HandleMemberRegister(context); break; case "member_update": HandleMemberUpdate(context); break; case "court_hearings": HandleVirtualCourtHearings(context); break; case "get_judges": HandleGetJudges(context); break; case "case_create": HandleCaseCreate(context); break; case "case_update": HandleCaseUpdate(context); break; case "check_crn": HandleCheckCRN(context); break; case "get_courts": HandleGetCourts(context); break; case "case_list": HandleCaseList(context); break; case "case_details": HandleCaseDetails(context); break; case "case_comment_add": HandleCaseCommentAdd(context); break; case "case_file_upload": HandleCaseFileUpload(context); break; case "search_lawyers": HandleSearchLawyers(context); break; default: SendError(context, 404, "Invalid API endpoint/action: " + action); break; } } catch (Exception ex) { SendError(context, 500, ex.Message); } } // --- 1. GET MEMBERS WITH SEARCH AND PAGING --- private void HandleMembers(HttpContext context) { string search = context.Request.QueryString["search"]; string pageStr = context.Request.QueryString["page"]; string limitStr = context.Request.QueryString["limit"]; int page = 1; int limit = 20; if (!string.IsNullOrEmpty(pageStr)) int.TryParse(pageStr, out page); if (!string.IsNullOrEmpty(limitStr)) int.TryParse(limitStr, out limit); int offset = (page - 1) * limit; using (SqlConnection con = new SqlConnection(connectionString)) { string query = ""; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; if (string.IsNullOrEmpty(search)) { // Return paginated list query = @"SELECT F_name, M_name, L_name, Ndbano, Bcdenroll, Mobile_1, Photo, sstatus FROM Memberslist ORDER BY id DESC OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY"; cmd.Parameters.AddWithValue("@offset", offset); cmd.Parameters.AddWithValue("@limit", limit); } else { // Return searched paginated list query = @"SELECT F_name, M_name, L_name, Ndbano, Bcdenroll, Mobile_1, Photo, sstatus FROM Memberslist WHERE F_name LIKE @search OR L_name LIKE @search OR Mobile_1 LIKE @search OR Bcdenroll LIKE @search OR Ndbano LIKE @search ORDER BY id DESC OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY"; cmd.Parameters.AddWithValue("@search", "%" + search + "%"); cmd.Parameters.AddWithValue("@offset", offset); cmd.Parameters.AddWithValue("@limit", limit); } cmd.CommandText = query; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, page = page, limit = limit, data = dt })); } } // --- GET MEMBER DIRECTORY (SEARCH BY SPECIFIC FIELD TYPE) --- private void HandleMemberDirectory(HttpContext context) { string searchType = context.Request.QueryString["search_type"]; string searchValue = context.Request.QueryString["search_value"]; if (string.IsNullOrEmpty(searchType)) { SendError(context, 400, "Parameter 'search_type' is required (options: 'S.No.', 'First Name', 'Mobile No.', 'BCD Enrollment No.', 'NDBA No.')."); return; } if (searchValue == null) { searchValue = ""; } using (SqlConnection con = new SqlConnection(connectionString)) { string query = ""; string typeLower = searchType.ToLower().Replace(" ", "").Replace(".", ""); if (typeLower == "sno" || typeLower == "ndbano" || typeLower == "ndba") { query = "SELECT * FROM Memberslist WHERE Ndbano LIKE @searchValue"; } else if (typeLower == "firstname" || typeLower == "name") { query = "SELECT * FROM Memberslist WHERE F_name LIKE @searchValue"; } else if (typeLower == "mobileno" || typeLower == "mobile") { query = "SELECT * FROM Memberslist WHERE Mobile_1 LIKE @searchValue"; } else if (typeLower == "bcdenrollmentno" || typeLower == "enrollment") { query = "SELECT * FROM Memberslist WHERE Bcdenroll LIKE @searchValue"; } else { SendError(context, 400, "Invalid 'search_type'. Allowed: 'S.No.', 'First Name', 'Mobile No.', 'BCD Enrollment No.', 'NDBA No.'"); return; } SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@searchValue", "%" + searchValue + "%"); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, count = dt.Rows.Count, data = dt })); } } // --- 2. GET SINGLE MEMBER PROFILE, DUES, RECEIPTS AND BILLS --- private void HandleMemberDetails(HttpContext context) { string id = context.Request.QueryString["id"] ?? context.Request.QueryString["member_id"]; if (string.IsNullOrEmpty(id)) { SendError(context, 400, "Parameter 'id' or 'member_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); EnsureColumnsExist(con); // 1. Fetch Profile info string profileQuery = "SELECT * FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand profileCmd = new SqlCommand(profileQuery, con); profileCmd.Parameters.AddWithValue("@id", id); SqlDataAdapter profileDa = new SqlDataAdapter(profileCmd); DataTable profileDt = new DataTable(); profileDa.Fill(profileDt); if (profileDt.Rows.Count == 0) { SendError(context, 404, "Member not found with enrollment or NDBA ID: " + id); return; } DataRow profileRow = profileDt.Rows[0]; string bcdEnroll = profileRow["Bcdenroll"].ToString(); // 2. Fetch Dues info from Membersduelist string duesQuery = "SELECT * FROM Membersduelist WHERE Bcdenroll = @bcd"; SqlCommand duesCmd = new SqlCommand(duesQuery, con); duesCmd.Parameters.AddWithValue("@bcd", bcdEnroll); SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd); DataTable duesDt = new DataTable(); duesDa.Fill(duesDt); // 3. Fetch Receipts from Membersallrecpit string receiptsQuery = "SELECT Recp_no, Recp_dt, Recp_md, Dd_no, Amount, Dd_bnk FROM Membersallrecpit WHERE Bcdenroll = @bcd ORDER BY Recp_dt DESC"; SqlCommand receiptsCmd = new SqlCommand(receiptsQuery, con); receiptsCmd.Parameters.AddWithValue("@bcd", bcdEnroll); SqlDataAdapter receiptsDa = new SqlDataAdapter(receiptsCmd); DataTable receiptsDt = new DataTable(); receiptsDa.Fill(receiptsDt); // 4. Fetch Bills from newdelhibar_bill string billsQuery = "SELECT Billno, Daten, Paymentmode, totalamount, btype, Status FROM newdelhibar_bill WHERE BCDno = @bcd ORDER BY Daten DESC"; SqlCommand billsCmd = new SqlCommand(billsQuery, con); billsCmd.Parameters.AddWithValue("@bcd", bcdEnroll); SqlDataAdapter billsDa = new SqlDataAdapter(billsCmd); DataTable billsDt = new DataTable(); billsDa.Fill(billsDt); var result = new { success = true, profile = DataRowToDictionary(profileRow), dues = duesDt.Rows.Count > 0 ? DataRowToDictionary(duesDt.Rows[0]) : null, receipts = receiptsDt, bills = billsDt }; context.Response.Write(JsonConvert.SerializeObject(result)); } } // --- 3. GET NOTICE BOARD LIST WITH THEIR IMAGES INCLUDED --- private void HandleNotices(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Fetch all notices string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_NoticeBoard ORDER BY pkid DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable noticesDt = new DataTable(); da.Fill(noticesDt); List> noticesList = new List>(); foreach (DataRow row in noticesDt.Rows) { var notice = new Dictionary(); notice["pkid"] = row["pkid"]; notice["title"] = row["title"]; notice["type"] = row["Type"]; notice["pdfPath"] = row["pdfPath"]; // Fetch attached images for each notice (if it is an Images type notice) string imgQuery = "SELECT attch FROM newdelhibar_NoticeBoardImg WHERE number = @noticeId"; SqlCommand imgCmd = new SqlCommand(imgQuery, con); imgCmd.Parameters.AddWithValue("@noticeId", row["pkid"].ToString()); SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd); DataTable imgDt = new DataTable(); imgDa.Fill(imgDt); List images = new List(); foreach (DataRow imgRow in imgDt.Rows) { images.Add(imgRow["attch"].ToString()); } notice["images"] = images; noticesList.Add(notice); } context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = noticesList })); } } // --- 4. GET PHOTO ALBUMS --- private void HandleAlbums(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT pkid, AlbumName, CoverPhotoPath, AlbumRank FROM newdelhibar_PhotoAlbumList ORDER BY CAST(AlbumRank AS INT) ASC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- 5. GET PHOTOS BY ALBUM ID --- private void HandlePhotos(HttpContext context) { string albumId = context.Request.QueryString["album_id"]; if (string.IsNullOrEmpty(albumId)) { SendError(context, 400, "Parameter 'album_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT pkid, Name, ImagePath, ImageRank, About FROM newdelhibar_imageGalleryList WHERE Album = @albumId ORDER BY CAST(ImageRank AS INT) ASC"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@albumId", albumId); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, albumId = albumId, data = dt })); } } // --- 6. POST: SUBMIT MEMBERSHIP APPLICATION --- private void HandleApplyMembership(HttpContext context) { string jsonInput = ""; using (StreamReader reader = new StreamReader(context.Request.InputStream)) { jsonInput = reader.ReadToEnd(); } if (string.IsNullOrEmpty(jsonInput)) { SendError(context, 400, "Request body is empty."); return; } dynamic data = JsonConvert.DeserializeObject(jsonInput); if (data == null) { SendError(context, 400, "Invalid JSON body provided."); return; } // Required parameters validation if (data.Name == null || data.Mobile == null || data.BarEnrollment == null) { SendError(context, 400, "Required parameters missing. 'Name', 'Mobile', and 'BarEnrollment' must be provided."); return; } string name = (string)data.Name; string fatherHusbandName = (string)data.FatherHusbandName ?? ""; string dob = (string)data.DOB ?? ""; string barEnrollment = (string)data.BarEnrollment; string otherStateEnrollment = (string)data.OtherStateEnrollment ?? ""; string placePractice = (string)data.PlacePractice ?? ""; string graduationDetails = (string)data.GraduationDetails ?? ""; string chamberNo = (string)data.ChamberNo ?? ""; string resAddress = (string)data.ResAddress ?? ""; string mobile = (string)data.Mobile; string email = (string)data.Email ?? ""; string aadhar = (string)data.Aadhar ?? ""; string voterId = (string)data.VoterId ?? ""; string bloodGroup = (string)data.BloodGroup ?? ""; string pendingCase = (string)data.PendingCase ?? ""; string retired = (string)data.Retired ?? ""; string applicantName = (string)data.ApplicantName ?? name; string recommendations = (string)data.Recommendations ?? ""; // base64 strings or URLs for signature and photos string signaturePath = (string)data.SignaturePath ?? ""; string photoPath = (string)data.PhotoPath ?? ""; string billNo = "ND" + DateTime.Now.Ticks.ToString().Substring(12); using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlTransaction transaction = conn.BeginTransaction(); try { // 1. Insert into NDBAMembers string memberQuery = @"INSERT INTO NDBAMembers ( Name, FatherHusbandName, DOB, BarEnrollment, OtherStateEnrollment, PlacePractice, GraduationDetails, ChamberNo, ResAddress, Mobile, Email, Aadhar, VoterId, BloodGroup, PendingCase, Retired, ApplicantName, Recommendations, SignaturePath, PhotoPath, Paymentsatats, daten ) VALUES ( @Name, @FatherHusbandName, @DOB, @BarEnrollment, @OtherStateEnrollment, @PlacePractice, @GraduationDetails, @ChamberNo, @ResAddress, @Mobile, @Email, @Aadhar, @VoterId, @BloodGroup, @PendingCase, @Retired, @ApplicantName, @Recommendations, @SignaturePath, @PhotoPath, 'Pending', @Daten )"; SqlCommand mCmd = new SqlCommand(memberQuery, conn, transaction); mCmd.Parameters.AddWithValue("@Name", name); mCmd.Parameters.AddWithValue("@FatherHusbandName", fatherHusbandName); mCmd.Parameters.AddWithValue("@DOB", dob); mCmd.Parameters.AddWithValue("@BarEnrollment", barEnrollment); mCmd.Parameters.AddWithValue("@OtherStateEnrollment", otherStateEnrollment); mCmd.Parameters.AddWithValue("@PlacePractice", placePractice); mCmd.Parameters.AddWithValue("@GraduationDetails", graduationDetails); mCmd.Parameters.AddWithValue("@ChamberNo", chamberNo); mCmd.Parameters.AddWithValue("@ResAddress", resAddress); mCmd.Parameters.AddWithValue("@Mobile", mobile); mCmd.Parameters.AddWithValue("@Email", email); mCmd.Parameters.AddWithValue("@Aadhar", aadhar); mCmd.Parameters.AddWithValue("@VoterId", voterId); mCmd.Parameters.AddWithValue("@BloodGroup", bloodGroup); mCmd.Parameters.AddWithValue("@PendingCase", pendingCase); mCmd.Parameters.AddWithValue("@Retired", retired); mCmd.Parameters.AddWithValue("@ApplicantName", applicantName); mCmd.Parameters.AddWithValue("@Recommendations", recommendations); mCmd.Parameters.AddWithValue("@SignaturePath", signaturePath); mCmd.Parameters.AddWithValue("@PhotoPath", photoPath); mCmd.Parameters.AddWithValue("@Daten", DateTime.Now); mCmd.ExecuteNonQuery(); // 2. Insert into newdelhibar_bill (Pending Membership Bill) string billQuery = @"INSERT INTO newdelhibar_bill ( Billno, Name, Mobile, NDBAno, BCDno, Daten, Address, Headermessage, Paymentmode, Footermessage, totalamount, btype, Status ) VALUES ( @Billno, @Name, @Mobile, '', @BCDno, @Daten, @Address, 'New Member Fee', 'Online', 'Online Fee', '1700', 'Member ship', 'Pending' )"; SqlCommand bCmd = new SqlCommand(billQuery, conn, transaction); bCmd.Parameters.AddWithValue("@Billno", billNo); bCmd.Parameters.AddWithValue("@Name", name); bCmd.Parameters.AddWithValue("@Mobile", mobile); bCmd.Parameters.AddWithValue("@BCDno", barEnrollment); bCmd.Parameters.AddWithValue("@Daten", DateTime.Now); bCmd.Parameters.AddWithValue("@Address", resAddress); bCmd.ExecuteNonQuery(); // 3. Insert into newdelhibar_rowbill (Row item details) string rowBillQuery = "INSERT INTO newdelhibar_rowbill (Billno, Particulars, Amount) VALUES (@Billno, 'New Membership Fee', '1700')"; SqlCommand rCmd = new SqlCommand(rowBillQuery, conn, transaction); rCmd.Parameters.AddWithValue("@Billno", billNo); rCmd.ExecuteNonQuery(); transaction.Commit(); context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Membership application submitted successfully.", billNo = billNo, amount = 1700, status = "Pending" })); } catch (Exception ex) { transaction.Rollback(); throw ex; } } } // --- 7. GET COMMUNICATIONS BOARD NOTICES --- private void HandleCommunications(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_CommunicationBoard ORDER BY pkid DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- GET ANNOUNCEMENT (SINGLE OR ALL) --- private void HandleAnnouncement(HttpContext context) { string id = context.Request.QueryString["id"]; if (string.IsNullOrEmpty(id)) { id = context.Request.QueryString["pkid"]; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); if (!string.IsNullOrEmpty(id)) { // Fetch single announcement string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_NoticeBoard WHERE pkid = @id"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", id); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 404, "Announcement not found with ID: " + id); return; } DataRow row = dt.Rows[0]; var notice = new Dictionary(); notice["pkid"] = row["pkid"]; notice["title"] = row["title"]; notice["type"] = row["Type"]; notice["pdfPath"] = row["pdfPath"]; // Fetch attached images string imgQuery = "SELECT attch FROM newdelhibar_NoticeBoardImg WHERE number = @noticeId"; SqlCommand imgCmd = new SqlCommand(imgQuery, con); imgCmd.Parameters.AddWithValue("@noticeId", id); SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd); DataTable imgDt = new DataTable(); imgDa.Fill(imgDt); List images = new List(); foreach (DataRow imgRow in imgDt.Rows) { images.Add(imgRow["attch"].ToString()); } notice["images"] = images; context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = notice })); } else { // Fetch all announcements string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_NoticeBoard ORDER BY pkid DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable noticesDt = new DataTable(); da.Fill(noticesDt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = noticesDt })); } } } // --- GET COMMUNICATION DETAILS (SINGLE OR ALL) --- private void HandleCommunicationDetails(HttpContext context) { string id = context.Request.QueryString["id"]; if (string.IsNullOrEmpty(id)) { id = context.Request.QueryString["pkid"]; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); if (!string.IsNullOrEmpty(id)) { // Fetch single communication string query = "SELECT pkid, title, [Type], pdfPath FROM newdelhibar_CommunicationBoard WHERE pkid = @id"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", id); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 404, "Communication not found with ID: " + id); return; } DataRow row = dt.Rows[0]; var comm = new Dictionary(); comm["pkid"] = row["pkid"]; comm["title"] = row["title"]; comm["type"] = row["Type"]; comm["pdfPath"] = row["pdfPath"] != DBNull.Value ? row["pdfPath"].ToString() : ""; // Fetch attached images string imgQuery = "SELECT attch FROM newdelhibar_CommunicationBoardImg WHERE number = @commId"; SqlCommand imgCmd = new SqlCommand(imgQuery, con); imgCmd.Parameters.AddWithValue("@commId", id); SqlDataAdapter imgDa = new SqlDataAdapter(imgCmd); DataTable imgDt = new DataTable(); imgDa.Fill(imgDt); List images = new List(); foreach (DataRow imgRow in imgDt.Rows) { images.Add(imgRow["attch"].ToString()); } comm["images"] = images; context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = comm })); } else { // Fetch all communications string query = "SELECT pkid, title, [Type] as [type], ISNULL(pdfPath, '') as pdfPath FROM newdelhibar_CommunicationBoard ORDER BY pkid DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } } // --- 8. GET NDBA FORMS --- private void HandleForms(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT Title, pdfPath FROM newdelhibar_NDBAForm ORDER BY id DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); List> formsList = new List>(); foreach (DataRow row in dt.Rows) { var form = new Dictionary(); form["title"] = row["Title"]; form["pdfPath"] = row["pdfPath"] != DBNull.Value ? row["pdfPath"].ToString().Replace("~/", "") : ""; formsList.Add(form); } context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = formsList })); } } // --- 9. GET VIDEO ALBUMS --- private void HandleVideos(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT id, AlbumName, CoverPhotoPath, AlbumRank FROM newdelhibar_VideoAlbumList ORDER BY CAST(AlbumRank AS INT) ASC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- 10. GET VIDEOS BY ALBUM ID --- private void HandleVideoDetails(HttpContext context) { string albumId = context.Request.QueryString["album_id"]; if (string.IsNullOrEmpty(albumId)) { SendError(context, 400, "Parameter 'album_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT id, video_rnk, video_title, vdo, vdo_album FROM newdelhibar_Vedio WHERE vdo_album = @albumId ORDER BY CAST(video_rnk AS INT) ASC"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@albumId", albumId); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, albumId = albumId, data = dt })); } } // --- 11. GET OFFICE BEARERS --- private void HandleOfficeBearers(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT id, Name, Post, Mobile, Photo FROM Officelist ORDER BY id DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- GET COMMITTEE MEMBERS --- private void HandleCommitteeMembers(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT Id, Designation, MemberName, PhoneNumber, PhotoPath, CreatedDate FROM tb_committee_members ORDER BY Id ASC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- GET TODAY'S BIRTHDAYS --- private void HandleTodaysBirthdays(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT * FROM Memberslist WHERE dob IS NOT NULL AND LTRIM(RTRIM(dob)) != '' AND LTRIM(RTRIM(dob)) != '0' AND LTRIM(RTRIM(dob)) != '1900-01-01'"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); DataTable filteredDt = dt.Clone(); int todayDay = DateTime.Today.Day; int todayMonth = DateTime.Today.Month; foreach (DataRow row in dt.Rows) { string dobStr = row["dob"]?.ToString().Trim(); if (string.IsNullOrEmpty(dobStr) || dobStr == "0" || dobStr.ToLower() == "null" || dobStr == "00/00/0000" || dobStr == "00-00-0000" || dobStr == "1900-01-01" || dobStr == "1/1/1900" || dobStr == "01/01/1900" || dobStr == "01-01-1900" || dobStr == "1-1-1900") { continue; } DateTime parsedDate; bool success = false; string[] formats = { "yyyy-MM-dd", "dd/MM/yyyy", "MM/dd/yyyy", "yyyy/MM/dd", "dd-MM-yyyy", "MM-dd-yyyy", "yyyy-M-d", "d/M/yyyy", "M/d/yyyy", "yyyy/M/d", "d-M-yyyy", "M-d-yyyy", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-ddTHH:mm:ss", "dd/MM/yyyy hh:mm:ss tt", "dd/MM/yyyy HH:mm:ss" }; if (DateTime.TryParseExact(dobStr, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate)) { success = true; } else if (DateTime.TryParse(dobStr, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate)) { success = true; } if (success) { if (parsedDate.Day == todayDay && parsedDate.Month == todayMonth) { filteredDt.ImportRow(row); } } else { try { string[] parts = dobStr.Split(new char[] { '/', '-' }); if (parts.Length >= 2) { int day = 0; int month = 0; if (parts[0].Length == 4) { int.TryParse(parts[1], out month); int.TryParse(parts[2].Split(' ')[0], out day); } else { int part1, part2; int.TryParse(parts[0], out part1); int.TryParse(parts[1], out part2); if ((part1 == todayDay && part2 == todayMonth) || (part1 == todayMonth && part2 == todayDay)) { if (part1 > 12) { day = part1; month = part2; } else if (part2 > 12) { month = part1; day = part2; } else { if (part1 == todayDay && part2 == todayMonth) { day = part1; month = part2; } else if (part1 == todayMonth && part2 == todayDay) { day = part2; month = part1; } } } } if (day == todayDay && month == todayMonth) { filteredDt.ImportRow(row); } } } catch { // ignore and continue } } } context.Response.Write(JsonConvert.SerializeObject(new { success = true, count = filteredDt.Rows.Count, data = filteredDt })); } } // --- 12. GET BANNERS --- private void HandleBanners(HttpContext context) { using (SqlConnection con = new SqlConnection(connectionString)) { string query = "SELECT id, Imagep, titlename, BannerSet, link FROM Advertise_Data ORDER BY id DESC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- 13. CALCULATE MEMBER DUES AND LATE FEES --- private void HandleCalculateDues(HttpContext context) { string memberId = context.Request.QueryString["member_id"]; if (string.IsNullOrEmpty(memberId)) { SendError(context, 400, "Parameter 'member_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Fetch profile info string profileQuery = "SELECT * FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand profileCmd = new SqlCommand(profileQuery, con); profileCmd.Parameters.AddWithValue("@id", memberId); SqlDataAdapter profileDa = new SqlDataAdapter(profileCmd); DataTable profileDt = new DataTable(); profileDa.Fill(profileDt); if (profileDt.Rows.Count == 0) { SendError(context, 404, "Member not found with enrollment or NDBA ID: " + memberId); return; } DataRow profileRow = profileDt.Rows[0]; string bcdEnroll = profileRow["Bcdenroll"].ToString(); string ndbaNo = profileRow["Ndbano"].ToString(); // Fetch dues info string duesQuery = "SELECT * FROM Membersduelist WHERE Bcdenroll = @bcd OR Ndbano = @ndba ORDER BY id DESC"; SqlCommand duesCmd = new SqlCommand(duesQuery, con); duesCmd.Parameters.AddWithValue("@bcd", bcdEnroll); duesCmd.Parameters.AddWithValue("@ndba", ndbaNo); SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd); DataTable duesDt = new DataTable(); duesDa.Fill(duesDt); string dues = "0"; string dueDateStr = ""; int lateFee = 0; int totalDues = 0; if (duesDt.Rows.Count > 0) { DataRow duesRow = duesDt.Rows[0]; dues = duesRow["dues"].ToString(); dueDateStr = duesRow["Ndbadate"].ToString(); if (dues != "0" && !string.IsNullOrEmpty(dueDateStr)) { DateTime dt1 = DateTime.Now.AddDays(-1); DateTime dt2 = Convert.ToDateTime(dueDateStr); // Replicating month count logic exactly int monthCount = dt1.Month - dt2.Month; if (dt1.Year != dt2.Year) { monthCount = ((dt1.Year - dt2.Year - 1) * 12) + (13 - dt2.Month + dt1.Month); } if (monthCount > 0) { lateFee = 100 * monthCount; } } int baseDuesVal = 0; int.TryParse(dues, out baseDuesVal); totalDues = baseDuesVal + lateFee; } var result = new { success = true, profile = DataRowToDictionary(profileRow), baseDues = dues, dueDate = dueDateStr, lateFee = lateFee, totalDues = totalDues }; context.Response.Write(JsonConvert.SerializeObject(result)); } } // --- 14. GET RUNNING SUBSCRIPTION & PAYMENTS LEDGER --- private void HandleMemberLedger(HttpContext context) { string memberId = context.Request.QueryString["member_id"]; if (string.IsNullOrEmpty(memberId)) { SendError(context, 400, "Parameter 'member_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Find Ndbadate for membership start date string profileQuery = "SELECT Ndbadate FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand profileCmd = new SqlCommand(profileQuery, con); profileCmd.Parameters.AddWithValue("@id", memberId); object ndbaDateObj = profileCmd.ExecuteScalar(); if (ndbaDateObj == null || ndbaDateObj == DBNull.Value) { SendError(context, 404, "Member not found or has no membership date."); return; } DateTime membershipDate = Convert.ToDateTime(ndbaDateObj); DateTime minDate = new DateTime(2016, 1, 1); if (membershipDate <= minDate) { membershipDate = minDate; } // Create DataTable for ledger items DataTable dt = new DataTable(); dt.Columns.Add("Date", typeof(DateTime)); dt.Columns.Add("RefNo", typeof(string)); dt.Columns.Add("Type", typeof(string)); dt.Columns.Add("Credit", typeof(decimal)); dt.Columns.Add("Debit", typeof(decimal)); // Load from Membersallrecpit string queryReceipts = @" SELECT Recp_dt AS Date, Recp_no AS RefNo, CASE WHEN CAST(Recp_dt AS DATE) = '2021-09-30' THEN 'COVID WAVE OFF' ELSE 'RECEIPT' END AS Type, Amount AS Credit, 0 AS Debit FROM Membersallrecpit WHERE Bcdenroll = @id"; SqlCommand cmdRec = new SqlCommand(queryReceipts, con); cmdRec.Parameters.AddWithValue("@id", memberId); SqlDataAdapter daRec = new SqlDataAdapter(cmdRec); daRec.Fill(dt); // Load from newdelhibar_bill string queryBills = @" SELECT Daten AS Date, Billno AS RefNo, btype AS Type, CASE WHEN btype = 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2)) ELSE 0 END AS Credit, CASE WHEN btype <> 'SUBSCRIPTION FEE' THEN TRY_CAST(totalamount AS DECIMAL(18,2)) ELSE 0 END AS Debit FROM newdelhibar_bill WHERE BCDno = @id AND btype = @btype"; SqlCommand cmdBill = new SqlCommand(queryBills, con); cmdBill.Parameters.AddWithValue("@id", memberId); cmdBill.Parameters.AddWithValue("@btype", "SUBSCRIPTION FEE"); SqlDataAdapter daBill = new SqlDataAdapter(cmdBill); daBill.Fill(dt); // Add membership monthly subscription fees DateTime today = DateTime.Today; DateTime loopDate = new DateTime(membershipDate.Year, membershipDate.Month, 1); while (loopDate <= today) { decimal amount = 100; dt.Rows.Add(loopDate, "SUB", "SUBSCRIPTION", 0, amount); loopDate = loopDate.AddMonths(1); } // Sort by Date ASC DataView dv = dt.DefaultView; dv.Sort = "Date ASC"; DataTable sortedDt = dv.ToTable(); // Calculate running balance sortedDt.Columns.Add("Balance", typeof(decimal)); decimal balance = 0; foreach (DataRow row in sortedDt.Rows) { decimal cr = Convert.ToDecimal(row["Credit"]); decimal dr = Convert.ToDecimal(row["Debit"]); balance += cr - dr; row["Balance"] = balance; } context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = sortedDt })); } } // --- 15. SEND OTP SMS --- private void HandleSendOtp(HttpContext context) { string memberId = context.Request.QueryString["member_id"]; string mobile = context.Request.QueryString["mobile"]; if (string.IsNullOrEmpty(mobile) && !string.IsNullOrEmpty(memberId)) { using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = "SELECT top 1 Mobile_1 FROM Memberslist WHERE Ndbano = @id"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", memberId); object mobObj = cmd.ExecuteScalar(); if (mobObj != null && mobObj != DBNull.Value) { mobile = mobObj.ToString(); } } } if (string.IsNullOrEmpty(mobile) || mobile == "0") { SendError(context, 400, "Mobile number not found or invalid."); return; } // Generate 4-digit OTP string[] saAllowedCharacters = { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0" }; string sOTP = ""; Random rand = new Random(); for (int i = 0; i < 4; i++) { sOTP += saAllowedCharacters[rand.Next(0, saAllowedCharacters.Length)]; } string messag = " NDBA User, Your OTP is " + sOTP + ". Do Not share this with anyone. TEAM NDBA"; string smsStatusMsg = ""; try { using (WebClient client = new WebClient()) { string url = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + mobile + "&text=" + messag + "&route=2"; smsStatusMsg = client.DownloadString(url); } } catch (Exception ex) { smsStatusMsg = "Failed to send SMS: " + ex.Message; } context.Response.Write(JsonConvert.SerializeObject(new { success = true, mobile = mobile, otp = sOTP, smsResponse = smsStatusMsg })); } // --- 16. POST: INITIATE PAYMENT (CREATE BILL & ORDER) --- private void HandleInitiatePayment(HttpContext context) { string jsonInput = ""; using (StreamReader reader = new StreamReader(context.Request.InputStream)) { jsonInput = reader.ReadToEnd(); } if (string.IsNullOrEmpty(jsonInput)) { SendError(context, 400, "Request body is empty."); return; } dynamic data = JsonConvert.DeserializeObject(jsonInput); if (data == null || data.MemberId == null || data.Amount == null) { SendError(context, 400, "Required parameters missing. 'MemberId' and 'Amount' must be provided."); return; } string memberId = (string)data.MemberId; string amount = (string)data.Amount; string particulars = (string)data.Particulars ?? "Member Fee"; string btype = (string)data.BillType ?? "Member ship"; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Fetch member details string query = "SELECT top 1 F_name, M_name, L_name, Mobile_1, Ndbano, Bcdenroll, Resid_1 FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", memberId); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 404, "Member not found."); return; } DataRow row = dt.Rows[0]; string name = row["F_name"].ToString() + " " + row["M_name"].ToString() + " " + row["L_name"].ToString(); string mobile = row["Mobile_1"].ToString(); string ndbaNo = row["Ndbano"].ToString(); string bcdNo = row["Bcdenroll"].ToString(); string address = row["Resid_1"].ToString(); // Generate unique bill number int nextId = 1; string idQuery = "SELECT top 1 id FROM newdelhibar_bill ORDER BY id DESC"; SqlCommand idCmd = new SqlCommand(idQuery, con); object lastIdObj = idCmd.ExecuteScalar(); if (lastIdObj != null && lastIdObj != DBNull.Value) { nextId = Convert.ToInt32(lastIdObj) + 1; } string billNo = nextId.ToString(); SqlTransaction transaction = con.BeginTransaction(); try { // Insert bill string billQuery = @"INSERT INTO newdelhibar_bill ( Billno, Name, Mobile, NDBAno, BCDno, Daten, Address, Headermessage, Paymentmode, Footermessage, totalamount, btype, Status ) VALUES ( @Billno, @Name, @Mobile, @NDBAno, @BCDno, @Daten, @Address, 'Online Member Fee', 'Online', 'Online Fee', @totalamount, @btype, 'Pending' )"; SqlCommand bCmd = new SqlCommand(billQuery, con, transaction); bCmd.Parameters.AddWithValue("@Billno", billNo); bCmd.Parameters.AddWithValue("@Name", name); bCmd.Parameters.AddWithValue("@Mobile", mobile); bCmd.Parameters.AddWithValue("@NDBAno", ndbaNo); bCmd.Parameters.AddWithValue("@BCDno", bcdNo); bCmd.Parameters.AddWithValue("@Daten", DateTime.Now); bCmd.Parameters.AddWithValue("@Address", address); bCmd.Parameters.AddWithValue("@totalamount", amount); bCmd.Parameters.AddWithValue("@btype", btype); bCmd.ExecuteNonQuery(); // Insert rowbill string rowBillQuery = "INSERT INTO newdelhibar_rowbill (Billno, Particulars, Amount) VALUES (@Billno, @Particulars, @Amount)"; SqlCommand rCmd = new SqlCommand(rowBillQuery, con, transaction); rCmd.Parameters.AddWithValue("@Billno", billNo); rCmd.Parameters.AddWithValue("@Particulars", particulars); rCmd.Parameters.AddWithValue("@Amount", amount); rCmd.ExecuteNonQuery(); // Call Getepay SDK string mid = ConfigurationManager.AppSettings["Mid"]; string terminalId = ConfigurationManager.AppSettings["terminalId"]; string key = ConfigurationManager.AppSettings["key"]; string iv = ConfigurationManager.AppSettings["iv"]; string url = ConfigurationManager.AppSettings["url"]; GetepayConfig config = new GetepayConfig(); config.mid = mid; config.terminalId = terminalId; config.key = key; config.iv = iv; config.url = url; System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12; GetepayRequest request = new GetepayRequest(); request.mid = mid; request.amount = amount; request.merchantTransactionId = billNo; request.transactionDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); request.terminalId = terminalId; request.udf1 = mobile; request.udf2 = "ndba.patialahousecourt@gmail.com"; request.udf3 = name; request.udf4 = ""; request.udf5 = ""; request.udf6 = ""; request.udf7 = ""; request.udf8 = ""; request.udf9 = ""; request.udf10 = url; request.ru = "https://newdelhibarassociation.in/Paymentstatus.aspx"; request.callbackUrl = ""; request.currency = "INR"; request.paymentMode = "ALL"; request.bankId = ""; request.txnType = ""; request.productType = ""; request.txnNote = ""; request.vpa = ""; GetepayOrderResponse orderResponse = Getepay.generateRequest(config, request); // Update payment ID string updateQuery = "UPDATE newdelhibar_bill SET payment_id = @paymentId WHERE Billno = @billNo"; SqlCommand uCmd = new SqlCommand(updateQuery, con, transaction); uCmd.Parameters.AddWithValue("@paymentId", orderResponse.paymentId.ToString()); uCmd.Parameters.AddWithValue("@billNo", billNo); uCmd.ExecuteNonQuery(); transaction.Commit(); context.Response.Write(JsonConvert.SerializeObject(new { success = true, billNo = billNo, paymentId = orderResponse.paymentId, paymentUrl = orderResponse.paymentUrl, amount = amount, status = "Pending" })); } catch (Exception ex) { transaction.Rollback(); throw ex; } } } // --- 17. VERIFY PAYMENT (REQUERY & PROCESS DUES UPDATE) --- private void HandleVerifyPayment(HttpContext context) { string billNo = context.Request.QueryString["bill_no"]; if (string.IsNullOrEmpty(billNo)) { SendError(context, 400, "Parameter 'bill_no' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string qrajneeti = "SELECT * FROM newdelhibar_bill WHERE Billno = @billNo"; SqlCommand cmd = new SqlCommand(qrajneeti, con); cmd.Parameters.AddWithValue("@billNo", billNo); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 404, "Bill not found."); return; } DataRow billRow = dt.Rows[0]; string paymentId = billRow["payment_id"].ToString(); string mobile = billRow["Mobile"].ToString(); string ndbaNo = billRow["NDBAno"].ToString(); string amount = billRow["totalamount"].ToString(); string status = billRow["Status"].ToString(); if (status == "SUCCESS") { context.Response.Write(JsonConvert.SerializeObject(new { success = true, status = "SUCCESS", billNo = billNo, message = "Payment is already marked as SUCCESS." })); return; } // Perform Getepay requery string mid = ConfigurationManager.AppSettings["Mid"]; string terminalId = ConfigurationManager.AppSettings["terminalId"]; string key = ConfigurationManager.AppSettings["key"]; string iv = ConfigurationManager.AppSettings["iv"]; string url = "https://portal.getepay.in:8443/getepayPortal/pg/invoiceStatus"; GetepayConfig config = new GetepayConfig(); config.mid = mid; config.terminalId = terminalId; config.key = key; config.iv = iv; config.url = url; GetepayRequery getepayRequery = new GetepayRequery(); getepayRequery.paymentId = paymentId; getepayRequery.terminalId = terminalId; getepayRequery.mid = mid; System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12; GetepayRequeryResponse requeryResponse = Getepay.requeryRequest(config, getepayRequery); string txnStatus = requeryResponse.txnStatus; if (txnStatus == "SUCCESS") { SqlTransaction transaction = con.BeginTransaction(); try { // Update status in bill string updateBill = "UPDATE newdelhibar_bill SET Status = 'SUCCESS' WHERE Billno = @billNo"; SqlCommand ubCmd = new SqlCommand(updateBill, con, transaction); ubCmd.Parameters.AddWithValue("@billNo", billNo); ubCmd.ExecuteNonQuery(); // Send SMS string messag = "We confirm receipt of online payment made via NDBA for " + amount + " against Membership Renew Bill No: " + billNo + ". - NDBA"; try { using (WebClient client = new WebClient()) { string smsUrl = "http://www.canlog.in/api/mt/SendSMS?user=NDBA1234&password=NDBA1234&senderid=NDBATM&channel=Trans&DCS=0&flashsms=0&number=" + mobile + "&text=" + HttpUtility.UrlEncode(messag) + "&route=2"; client.DownloadString(smsUrl); } } catch { } // Update Membersduelist string duesQuery = "SELECT * FROM Membersduelist WHERE Ndbano = @ndba ORDER BY id DESC"; SqlCommand duesCmd = new SqlCommand(duesQuery, con, transaction); duesCmd.Parameters.AddWithValue("@ndba", ndbaNo); SqlDataAdapter duesDa = new SqlDataAdapter(duesCmd); DataTable duesDt = new DataTable(); duesDa.Fill(duesDt); if (duesDt.Rows.Count > 0) { string baseDues = duesDt.Rows[0]["dues"].ToString(); DateTime dt2 = Convert.ToDateTime(duesDt.Rows[0]["Ndbadate"].ToString()); DateTime dt1 = DateTime.Now.AddDays(-1); int monthCount = dt1.Month - dt2.Month; if (dt1.Year != dt2.Year) { monthCount = ((dt1.Year - dt2.Year - 1) * 12) + (13 - dt2.Month + dt1.Month); } int lateFee = 0; if (monthCount > 0) { lateFee = 100 * monthCount; } int dueson = Convert.ToInt32(baseDues) + lateFee; int paidAmount = Convert.ToInt32(amount); int remainingDues = dueson - paidAmount; if (remainingDues >= 0) { string updue = "UPDATE Membersduelist SET dues = @remainingDues WHERE Ndbano = @ndba"; SqlCommand updueCmd = new SqlCommand(updue, con, transaction); updueCmd.Parameters.AddWithValue("@remainingDues", remainingDues); updueCmd.Parameters.AddWithValue("@ndba", ndbaNo); updueCmd.ExecuteNonQuery(); } else { int result = remainingDues / 100; int kj = Math.Abs(result); DateTime nextDueDate = dt2.AddMonths(kj + (monthCount > 0 ? monthCount : 0)); string updue = "UPDATE Membersduelist SET dues = '0', Ndbadate = @nextDueDate WHERE Ndbano = @ndba"; SqlCommand updueCmd = new SqlCommand(updue, con, transaction); updueCmd.Parameters.AddWithValue("@nextDueDate", nextDueDate); updueCmd.Parameters.AddWithValue("@ndba", ndbaNo); updueCmd.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } else { string updateBill = "UPDATE newdelhibar_bill SET Status = @status WHERE Billno = @billNo"; SqlCommand ubCmd = new SqlCommand(updateBill, con); ubCmd.Parameters.AddWithValue("@status", txnStatus); ubCmd.Parameters.AddWithValue("@billNo", billNo); ubCmd.ExecuteNonQuery(); } context.Response.Write(JsonConvert.SerializeObject(new { success = (txnStatus == "SUCCESS"), status = txnStatus, billNo = billNo })); } } // --- 18. POST: REQUEST NEW ID CARD / KYC --- private void HandleRequestIdCard(HttpContext context) { string jsonInput = ""; using (StreamReader reader = new StreamReader(context.Request.InputStream)) { jsonInput = reader.ReadToEnd(); } if (string.IsNullOrEmpty(jsonInput)) { SendError(context, 400, "Request body is empty."); return; } dynamic data = JsonConvert.DeserializeObject(jsonInput); if (data == null || data.ndba_no == null || data.mobile == null || data.address == null) { SendError(context, 400, "Required parameters missing. 'ndba_no', 'mobile', and 'address' are required."); return; } string ndbaNo = (string)data.ndba_no; string mobile = (string)data.mobile; string address = (string)data.address; string fatherName = (string)data.father_name ?? ""; string dob = (string)data.dob ?? ""; string email = (string)data.email ?? ""; string aadhar = (string)data.aadhar ?? ""; string voterId = (string)data.voter_id ?? ""; string chamberNo = (string)data.chamber_no ?? ""; string vehicleNo = (string)data.vehicle_no ?? ""; string stickerNo = (string)data.sticker_no ?? ""; string idProofType = (string)data.id_proof_type ?? "Aadhar Card"; var filesList = new List(); if (data.id_proof_files != null) { int index = 1; foreach (var fileBase64Obj in data.id_proof_files) { string fileBase64 = (string)fileBase64Obj; if (!string.IsNullOrEmpty(fileBase64)) { try { if (fileBase64.Contains(",")) { fileBase64 = fileBase64.Substring(fileBase64.IndexOf(",") + 1); } byte[] imageBytes = Convert.FromBase64String(fileBase64); string fileName = DateTime.Now.Ticks.ToString() + "_" + index + ".jpg"; string relativePath = "dataimp1/" + fileName; string physicalPath = context.Server.MapPath("~/" + relativePath); string dir = Path.GetDirectoryName(physicalPath); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } File.WriteAllBytes(physicalPath, imageBytes); filesList.Add("../" + relativePath); index++; } catch (Exception ex) { // ignore file save error, continue with other files } } } } string filesJson = JsonConvert.SerializeObject(filesList); using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); // First check if member exists with matching ndbaNo and mobile string checkQuery = "SELECT count(*) FROM Memberslist WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile"; SqlCommand checkCmd = new SqlCommand(checkQuery, conn); checkCmd.Parameters.AddWithValue("@ndbaNo", ndbaNo); checkCmd.Parameters.AddWithValue("@mobile", mobile); int count = (int)checkCmd.ExecuteScalar(); if (count == 0) { SendError(context, 404, "Member not found with matching NDBA No and Mobile number."); return; } string insertQuery = @" INSERT INTO KycRequests ( M_Type, Ndbano, mdate, Ndbadate, Bcdenroll, Prefix, F_name, M_name, L_name, Father_name, Dob, Resid_1, Resid_2, Resid_3, Resid_pin, Mobile_1, Mobile_2, Ceased, CeasedDate, Reasonceas, Email_id, Aadhar_id, Voter_id, Locker_no, Lid_Card_No, I_card_no, Chamber_Alloted, Chamber_no, Member_Ship_of_other_Bars, Vehicle_no, Sticker_no, Photo, M_Sign, s_no, sstatus, shop_no, datebcd, password, DEATHdate, idcardphot, IdProofType, IdProofFiles, Status, CreatedDate, UpdatedDate ) SELECT M_Type, Ndbano, mdate, Ndbadate, Bcdenroll, Prefix, F_name, M_name, L_name, @Father_name, @Dob, @Resid_1, Resid_2, Resid_3, Resid_pin, Mobile_1, Mobile_2, Ceased, CeasedDate, Reasonceas, @Email_id, @Aadhar_id, @Voter_id, Locker_no, Lid_Card_No, 'New', Chamber_Alloted, @Chamber_no, Member_Ship_of_other_Bars, @Vehicle_no, @Sticker_no, Photo, M_Sign, s_no, sstatus, shop_no, datebcd, password, DEATHdate, idcardphot, @IdProofType, @IdProofFiles, 'Pending', GETDATE(), GETDATE() FROM Memberslist WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile"; SqlCommand cmd = new SqlCommand(insertQuery, conn); cmd.Parameters.AddWithValue("@Resid_1", address); cmd.Parameters.AddWithValue("@Father_name", fatherName); cmd.Parameters.AddWithValue("@Dob", dob); cmd.Parameters.AddWithValue("@Email_id", email); cmd.Parameters.AddWithValue("@Aadhar_id", aadhar); cmd.Parameters.AddWithValue("@Voter_id", voterId); cmd.Parameters.AddWithValue("@Chamber_no", chamberNo); cmd.Parameters.AddWithValue("@Vehicle_no", vehicleNo); cmd.Parameters.AddWithValue("@Sticker_no", stickerNo); cmd.Parameters.AddWithValue("@IdProofType", idProofType); cmd.Parameters.AddWithValue("@IdProofFiles", filesJson); cmd.Parameters.AddWithValue("@ndbaNo", ndbaNo); cmd.Parameters.AddWithValue("@mobile", mobile); cmd.ExecuteNonQuery(); // Also update main member list to "New" for compatibility with legacy systems string updateMemberListQuery = "UPDATE Memberslist SET I_card_no = 'New' WHERE Ndbano = @ndbaNo AND Mobile_1 = @mobile"; SqlCommand updateMemberListCmd = new SqlCommand(updateMemberListQuery, conn); updateMemberListCmd.Parameters.AddWithValue("@ndbaNo", ndbaNo); updateMemberListCmd.Parameters.AddWithValue("@mobile", mobile); updateMemberListCmd.ExecuteNonQuery(); } context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "KYC request submitted successfully." })); } private void HandleGetKycHistory(HttpContext context) { string ndbaNo = context.Request.QueryString["ndba_no"]; if (string.IsNullOrEmpty(ndbaNo)) { SendError(context, 400, "Parameter 'ndba_no' is required."); return; } using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); string query = @" SELECT * FROM KycRequests WHERE Ndbano = @ndbaNo ORDER BY KycRequestId DESC"; SqlCommand cmd = new SqlCommand(query, conn); cmd.Parameters.AddWithValue("@ndbaNo", ndbaNo); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } private void HandleAdminGetKycRequests(HttpContext context) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); string query = @" SELECT * FROM KycRequests ORDER BY KycRequestId DESC"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } private void HandleAdminUpdateKycStatus(HttpContext context) { string jsonInput = ""; using (StreamReader reader = new StreamReader(context.Request.InputStream)) { jsonInput = reader.ReadToEnd(); } if (string.IsNullOrEmpty(jsonInput)) { SendError(context, 400, "Request body is empty."); return; } dynamic data = JsonConvert.DeserializeObject(jsonInput); if (data == null || data.kyc_request_id == null || data.status == null) { SendError(context, 400, "Required parameters missing. 'kyc_request_id' and 'status' are required."); return; } int kycRequestId = (int)data.kyc_request_id; string status = (string)data.status; string comments = (string)data.comments ?? ""; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); string updateKycQuery = @" UPDATE KycRequests SET Status = @Status, AdminComments = @Comments, UpdatedDate = GETDATE() WHERE KycRequestId = @KycRequestId"; SqlCommand cmd = new SqlCommand(updateKycQuery, conn); cmd.Parameters.AddWithValue("@Status", status); cmd.Parameters.AddWithValue("@Comments", comments); cmd.Parameters.AddWithValue("@KycRequestId", kycRequestId); cmd.ExecuteNonQuery(); string fetchQuery = "SELECT Ndbano, Resid_1, Father_name, Dob, Email_id, Aadhar_id, Voter_id, Chamber_no, Vehicle_no, Sticker_no FROM KycRequests WHERE KycRequestId = @KycRequestId"; SqlCommand fetchCmd = new SqlCommand(fetchQuery, conn); fetchCmd.Parameters.AddWithValue("@KycRequestId", kycRequestId); string ndbaNo = ""; string resid = ""; string fatherName = ""; string dob = ""; string email = ""; string aadhar = ""; string voterId = ""; string chamberNo = ""; string vehicleNo = ""; string stickerNo = ""; using (SqlDataReader reader = fetchCmd.ExecuteReader()) { if (reader.Read()) { ndbaNo = reader["Ndbano"].ToString(); resid = reader["Resid_1"].ToString(); fatherName = reader["Father_name"].ToString(); dob = reader["Dob"].ToString(); email = reader["Email_id"].ToString(); aadhar = reader["Aadhar_id"].ToString(); voterId = reader["Voter_id"].ToString(); chamberNo = reader["Chamber_no"].ToString(); vehicleNo = reader["Vehicle_no"].ToString(); stickerNo = reader["Sticker_no"].ToString(); } } if (!string.IsNullOrEmpty(ndbaNo)) { if (status == "Approved") { string updateMemberQuery = @" UPDATE Memberslist SET Resid_1 = @Resid_1, Father_name = @Father_name, Dob = @Dob, Email_id = @Email_id, Aadhar_id = @Aadhar_id, Voter_id = @Voter_id, Chamber_no = @Chamber_no, Vehicle_no = @Vehicle_no, Sticker_no = @Sticker_no, I_card_no = 'Approved' WHERE Ndbano = @Ndbano"; SqlCommand updateMemberCmd = new SqlCommand(updateMemberQuery, conn); updateMemberCmd.Parameters.AddWithValue("@Resid_1", resid); updateMemberCmd.Parameters.AddWithValue("@Father_name", fatherName); updateMemberCmd.Parameters.AddWithValue("@Dob", dob); updateMemberCmd.Parameters.AddWithValue("@Email_id", email); updateMemberCmd.Parameters.AddWithValue("@Aadhar_id", aadhar); updateMemberCmd.Parameters.AddWithValue("@Voter_id", voterId); updateMemberCmd.Parameters.AddWithValue("@Chamber_no", chamberNo); updateMemberCmd.Parameters.AddWithValue("@Vehicle_no", vehicleNo); updateMemberCmd.Parameters.AddWithValue("@Sticker_no", stickerNo); updateMemberCmd.Parameters.AddWithValue("@Ndbano", ndbaNo); updateMemberCmd.ExecuteNonQuery(); } else if (status == "Rejected") { string updateMemberQuery = @" UPDATE Memberslist SET I_card_no = 'Rejected' WHERE Ndbano = @Ndbano"; SqlCommand updateMemberCmd = new SqlCommand(updateMemberQuery, conn); updateMemberCmd.Parameters.AddWithValue("@Ndbano", ndbaNo); updateMemberCmd.ExecuteNonQuery(); } } } context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "KYC status updated successfully." })); } // --- MEMBER LOGIN --- private void HandleMemberLogin(HttpContext context) { string id = context.Request.QueryString["id"]; string mobile = context.Request.QueryString["mobile"]; if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(mobile)) { SendError(context, 400, "Parameters 'id' (NDBA/BCD) and 'mobile' are required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Fetch profile info matching Ndbano or Bcdenroll and Mobile_1 string query = "SELECT * FROM Memberslist WHERE (Ndbano = @id OR Bcdenroll = @id) AND Mobile_1 = @mobile"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@mobile", mobile); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 404, "Invalid Member ID or Mobile Number."); return; } DataRow memberRow = dt.Rows[0]; // Format profile data var profile = new Dictionary(); profile["F_name"] = memberRow["F_name"]; profile["M_name"] = memberRow["M_name"]; profile["L_name"] = memberRow["L_name"]; profile["Bcdenroll"] = memberRow["Bcdenroll"]; profile["Ndbano"] = memberRow["Ndbano"]; profile["Mobile_1"] = memberRow["Mobile_1"]; profile["Photo"] = memberRow["Photo"]; profile["sstatus"] = memberRow["sstatus"] != DBNull.Value && !string.IsNullOrEmpty(memberRow["sstatus"].ToString()) ? memberRow["sstatus"] : "Active"; // Determine Validity (check Ndbadate, default to "Valid - Lifetime" or "Dec 31, 2030") string validity = "Valid - Lifetime"; if (memberRow.Table.Columns.Contains("Ndbadate") && memberRow["Ndbadate"] != DBNull.Value) { DateTime ndbaDate; if (DateTime.TryParse(memberRow["Ndbadate"].ToString(), out ndbaDate)) { validity = "Valid until " + ndbaDate.AddYears(10).ToString("dd-MMM-yyyy"); } } profile["validity"] = validity; context.Response.Write(JsonConvert.SerializeObject(new { success = true, member = profile })); } } // --- VIRTUAL COURT HEARINGS --- private void HandleVirtualCourtHearings(HttpContext context) { string memberId = context.Request.QueryString["member_id"]; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string bcdenroll = memberId; string ndbano = memberId; // Attempt to resolve both Bcdenroll and Ndbano if memberId is provided if (!string.IsNullOrEmpty(memberId)) { string resolveQuery = "SELECT Bcdenroll, Ndbano FROM Memberslist WHERE Bcdenroll = @memberId OR Ndbano = @memberId"; SqlCommand resolveCmd = new SqlCommand(resolveQuery, con); resolveCmd.Parameters.AddWithValue("@memberId", memberId); using (SqlDataReader reader = resolveCmd.ExecuteReader()) { if (reader.Read()) { bcdenroll = reader["Bcdenroll"] != DBNull.Value ? reader["Bcdenroll"].ToString() : ""; ndbano = reader["Ndbano"] != DBNull.Value ? reader["Ndbano"].ToString() : ""; } } } string query = ""; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; if (string.IsNullOrEmpty(memberId)) { query = "SELECT pkid, CourtNumber, JudgeName, CONVERT(varchar, ScheduledTime, 126) as ScheduledTime, CONVERT(varchar, EndTime, 126) as EndTime, WebexLink, Bcdenroll FROM newdelhibar_VirtualCourtHearings WHERE (Bcdenroll IS NULL OR Bcdenroll = '') AND (ScheduledTime >= DATEADD(day, -30, GETDATE()) OR EndTime >= GETDATE()) ORDER BY ScheduledTime ASC"; } else { query = @" SELECT pkid, CourtNumber, JudgeName, CONVERT(varchar, ScheduledTime, 126) as ScheduledTime, CONVERT(varchar, EndTime, 126) as EndTime, WebexLink, Bcdenroll FROM newdelhibar_VirtualCourtHearings WHERE (Bcdenroll IS NULL OR Bcdenroll = '' OR Bcdenroll = @bcdenroll OR Bcdenroll = @ndbano) AND (ScheduledTime >= DATEADD(day, -30, GETDATE()) OR EndTime >= GETDATE()) UNION ALL SELECT c.Id as pkid, ISNULL(c.HearingCourtNumber, '') as CourtNumber, 'Case: ' + c.CaseSubject + ' (Case No. ' + CAST(c.Id as varchar) + ')' as JudgeName, CONVERT(varchar, c.HearingDate, 126) as ScheduledTime, CONVERT(varchar, DATEADD(hour, 1, c.HearingDate), 126) as EndTime, ISNULL(w.VCLink, '') as WebexLink, @bcdenroll as Bcdenroll FROM Cases c LEFT JOIN newdelhibar_Judges w ON c.HearingCourtType = w.CourtType AND ISNULL(c.HearingSubCourtName, '') = ISNULL(w.SubCourtName, '') AND c.HearingCourtNumber = w.Room WHERE (c.LawyerId = @bcdenroll OR c.LawyerId = @ndbano OR ',' + c.LawyerId + ',' LIKE '%,' + @bcdenroll + ',%' OR ',' + c.LawyerId + ',' LIKE '%,' + @ndbano + ',%') AND c.HearingDate IS NOT NULL AND c.HearingDate >= DATEADD(day, -30, GETDATE()) ORDER BY ScheduledTime ASC"; cmd.Parameters.AddWithValue("@bcdenroll", bcdenroll); cmd.Parameters.AddWithValue("@ndbano", ndbano); } cmd.CommandText = query; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } private void EnsureColumnsExist(SqlConnection con) { try { string alterQuery = @" IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Memberslist') AND name = 'Chamber_Alloted') BEGIN ALTER TABLE Memberslist ADD Chamber_Alloted VARCHAR(100); END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Memberslist') AND name = 'M_Sign') BEGIN ALTER TABLE Memberslist ADD M_Sign VARCHAR(250); END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Memberslist') AND name = 'Off_address') BEGIN ALTER TABLE Memberslist ADD Off_address VARCHAR(500); END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Memberslist') AND name = 'Phone') BEGIN ALTER TABLE Memberslist ADD Phone VARCHAR(50); END"; using (SqlCommand alterCmd = new SqlCommand(alterQuery, con)) { alterCmd.ExecuteNonQuery(); } } catch { } } // --- DYNAMIC ERROR SENDER --- private void SendError(HttpContext context, int statusCode, string message) { context.Response.StatusCode = statusCode; context.Response.Write(JsonConvert.SerializeObject(new { success = false, error = message })); } // --- MEMBER LOGIN WITH PASSWORD --- private void HandleMemberLoginPassword(HttpContext context) { string id = context.Request.QueryString["id"]; string password = context.Request.QueryString["password"]; if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(password)) { SendError(context, 400, "Parameters 'id' and 'password' are required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = "SELECT * FROM Memberslist WHERE (Ndbano = @id OR Bcdenroll = @id) AND password = @password"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@password", password); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count == 0) { SendError(context, 401, "Invalid credentials."); return; } DataRow memberRow = dt.Rows[0]; var profile = new Dictionary(); profile["F_name"] = memberRow["F_name"]; profile["M_name"] = memberRow["M_name"]; profile["L_name"] = memberRow["L_name"]; profile["Bcdenroll"] = memberRow["Bcdenroll"]; profile["Ndbano"] = memberRow["Ndbano"]; profile["Mobile_1"] = memberRow["Mobile_1"]; profile["Photo"] = memberRow["Photo"]; profile["sstatus"] = memberRow["sstatus"] != DBNull.Value && !string.IsNullOrEmpty(memberRow["sstatus"].ToString()) ? memberRow["sstatus"] : "Active"; string validity = "Valid - Lifetime"; if (memberRow.Table.Columns.Contains("Ndbadate") && memberRow["Ndbadate"] != DBNull.Value) { DateTime ndbaDate; if (DateTime.TryParse(memberRow["Ndbadate"].ToString(), out ndbaDate)) { validity = "Valid until " + ndbaDate.AddYears(10).ToString("dd-MMM-yyyy"); } } profile["validity"] = validity; context.Response.Write(JsonConvert.SerializeObject(new { success = true, member = profile })); } } // --- MEMBER CHANGE PASSWORD --- private void HandleChangePassword(HttpContext context) { string id = context.Request.QueryString["id"] ?? context.Request.Form["id"]; string newPassword = context.Request.QueryString["new_password"] ?? context.Request.Form["new_password"]; if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(newPassword)) { SendError(context, 400, "Parameters 'id' and 'new_password' are required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // First verify if the member exists string checkQuery = "SELECT COUNT(*) FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand checkCmd = new SqlCommand(checkQuery, con); checkCmd.Parameters.AddWithValue("@id", id); int count = (int)checkCmd.ExecuteScalar(); if (count == 0) { SendError(context, 404, "Member not found."); return; } // Update password string updateQuery = "UPDATE Memberslist SET password = @new_password WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand updateCmd = new SqlCommand(updateQuery, con); updateCmd.Parameters.AddWithValue("@id", id); updateCmd.Parameters.AddWithValue("@new_password", newPassword); int rows = updateCmd.ExecuteNonQuery(); if (rows > 0) { context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Password changed successfully." })); } else { SendError(context, 500, "Failed to update password."); } } } // --- MEMBER UPDATE --- private void HandleMemberUpdate(HttpContext context) { string id = context.Request.QueryString["id"] ?? context.Request.Form["id"]; string fName = context.Request.QueryString["f_name"] ?? context.Request.Form["f_name"]; string mName = context.Request.QueryString["m_name"] ?? context.Request.Form["m_name"]; string lName = context.Request.QueryString["l_name"] ?? context.Request.Form["l_name"]; string fatherName = context.Request.QueryString["father_name"] ?? context.Request.Form["father_name"]; string dob = context.Request.QueryString["dob"] ?? context.Request.Form["dob"]; string mobile1 = context.Request.QueryString["mobile_1"] ?? context.Request.Form["mobile_1"]; string mobile2 = context.Request.QueryString["mobile_2"] ?? context.Request.Form["mobile_2"]; string emailId = context.Request.QueryString["email_id"] ?? context.Request.Form["email_id"]; string resid1 = context.Request.QueryString["resid_1"] ?? context.Request.Form["resid_1"]; string resid2 = context.Request.QueryString["resid_2"] ?? context.Request.Form["resid_2"]; string resid3 = context.Request.QueryString["resid_3"] ?? context.Request.Form["resid_3"]; string residPin = context.Request.QueryString["resid_pin"] ?? context.Request.Form["resid_pin"]; string chamberNo = context.Request.QueryString["chamber_no"] ?? context.Request.Form["chamber_no"]; string vehicleNo = context.Request.QueryString["vehicle_no"] ?? context.Request.Form["vehicle_no"]; string stickerNo = context.Request.QueryString["sticker_no"] ?? context.Request.Form["sticker_no"]; string aadharId = context.Request.QueryString["aadhar_id"] ?? context.Request.Form["aadhar_id"]; string voterId = context.Request.QueryString["voter_id"] ?? context.Request.Form["voter_id"]; string prefix = context.Request.QueryString["prefix"] ?? context.Request.Form["prefix"]; string photoBase64 = context.Request.QueryString["photo_base64"] ?? context.Request.Form["photo_base64"]; string signatureBase64 = context.Request.QueryString["signature_base64"] ?? context.Request.Form["signature_base64"]; string chamberAlloted = context.Request.QueryString["chamber_alloted"] ?? context.Request.Form["chamber_alloted"]; string offAddress = context.Request.QueryString["off_address"] ?? context.Request.Form["off_address"]; string phone = context.Request.QueryString["phone"] ?? context.Request.Form["phone"]; if (string.IsNullOrEmpty(id)) { try { using (var reader = new StreamReader(context.Request.InputStream)) { string json = reader.ReadToEnd(); if (!string.IsNullOrEmpty(json)) { dynamic data = JsonConvert.DeserializeObject(json); if (data != null) { if (data.id != null) id = data.id.ToString(); if (data.f_name != null) fName = data.f_name.ToString(); if (data.m_name != null) mName = data.m_name.ToString(); if (data.l_name != null) lName = data.l_name.ToString(); if (data.father_name != null) fatherName = data.father_name.ToString(); if (data.dob != null) dob = data.dob.ToString(); if (data.mobile_1 != null) mobile1 = data.mobile_1.ToString(); if (data.mobile_2 != null) mobile2 = data.mobile_2.ToString(); if (data.email_id != null) emailId = data.email_id.ToString(); if (data.resid_1 != null) resid1 = data.resid_1.ToString(); if (data.resid_2 != null) resid2 = data.resid_2.ToString(); if (data.resid_3 != null) resid3 = data.resid_3.ToString(); if (data.resid_pin != null) residPin = data.resid_pin.ToString(); if (data.chamber_no != null) chamberNo = data.chamber_no.ToString(); if (data.vehicle_no != null) vehicleNo = data.vehicle_no.ToString(); if (data.sticker_no != null) stickerNo = data.sticker_no.ToString(); if (data.aadhar_id != null) aadharId = data.aadhar_id.ToString(); if (data.voter_id != null) voterId = data.voter_id.ToString(); if (data.prefix != null) prefix = data.prefix.ToString(); if (data.photo_base64 != null) photoBase64 = data.photo_base64.ToString(); if (data.signature_base64 != null) signatureBase64 = data.signature_base64.ToString(); if (data.chamber_alloted != null) chamberAlloted = data.chamber_alloted.ToString(); if (data.off_address != null) offAddress = data.off_address.ToString(); if (data.phone != null) phone = data.phone.ToString(); } } } } catch { } } if (string.IsNullOrEmpty(id)) { SendError(context, 400, "Parameter 'id' is required."); return; } string photoPath = ""; if (!string.IsNullOrEmpty(photoBase64)) { try { if (photoBase64.Contains(",")) { photoBase64 = photoBase64.Substring(photoBase64.IndexOf(",") + 1); } byte[] imageBytes = Convert.FromBase64String(photoBase64); string fileName = DateTime.Now.Ticks.ToString() + ".jpg"; string relativePath = "dataimp1/" + fileName; string physicalPath = context.Server.MapPath("~/" + relativePath); string dir = Path.GetDirectoryName(physicalPath); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } File.WriteAllBytes(physicalPath, imageBytes); photoPath = "../" + relativePath; } catch { } } string signaturePath = ""; if (!string.IsNullOrEmpty(signatureBase64)) { try { if (signatureBase64.Contains(",")) { signatureBase64 = signatureBase64.Substring(signatureBase64.IndexOf(",") + 1); } byte[] imageBytes = Convert.FromBase64String(signatureBase64); string fileName = "Signature_" + DateTime.Now.Ticks.ToString() + ".png"; string relativePath = "Uploads/" + fileName; string physicalPath = context.Server.MapPath("~/" + relativePath); string dir = Path.GetDirectoryName(physicalPath); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } File.WriteAllBytes(physicalPath, imageBytes); signaturePath = "../" + relativePath; } catch { } } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); EnsureColumnsExist(con); // First verify if the member exists string checkQuery = "SELECT COUNT(*) FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand checkCmd = new SqlCommand(checkQuery, con); checkCmd.Parameters.AddWithValue("@id", id); int count = (int)checkCmd.ExecuteScalar(); if (count == 0) { SendError(context, 404, "Member not found."); return; } // Update details string updateQuery = @"UPDATE Memberslist SET F_name = @f_name, M_name = @m_name, L_name = @l_name, Father_name = @father_name, Dob = @dob, Mobile_1 = @mobile_1, Mobile_2 = @mobile_2, Email_id = @email_id, Resid_1 = @resid_1, Resid_2 = @resid_2, Resid_3 = @resid_3, Resid_pin = @resid_pin, Chamber_no = @chamber_no, Vehicle_no = @vehicle_no, Sticker_no = @sticker_no, Aadhar_id = @aadhar_id, Voter_id = @voter_id, Prefix = @prefix, Chamber_Alloted = @chamber_alloted, Off_address = @off_address, Phone = @phone" + (!string.IsNullOrEmpty(photoPath) ? ", Photo = @Photo" : "") + (!string.IsNullOrEmpty(signaturePath) ? ", M_Sign = @Signature" : "") + " WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand updateCmd = new SqlCommand(updateQuery, con); if (!string.IsNullOrEmpty(photoPath)) { updateCmd.Parameters.AddWithValue("@Photo", photoPath); } if (!string.IsNullOrEmpty(signaturePath)) { updateCmd.Parameters.AddWithValue("@Signature", signaturePath); } updateCmd.Parameters.AddWithValue("@id", id); updateCmd.Parameters.AddWithValue("@f_name", fName ?? ""); updateCmd.Parameters.AddWithValue("@m_name", mName ?? ""); updateCmd.Parameters.AddWithValue("@l_name", lName ?? ""); updateCmd.Parameters.AddWithValue("@father_name", fatherName ?? ""); updateCmd.Parameters.AddWithValue("@dob", dob ?? ""); updateCmd.Parameters.AddWithValue("@mobile_1", mobile1 ?? ""); updateCmd.Parameters.AddWithValue("@mobile_2", mobile2 ?? ""); updateCmd.Parameters.AddWithValue("@email_id", emailId ?? ""); updateCmd.Parameters.AddWithValue("@resid_1", resid1 ?? ""); updateCmd.Parameters.AddWithValue("@resid_2", resid2 ?? ""); updateCmd.Parameters.AddWithValue("@resid_3", resid3 ?? ""); updateCmd.Parameters.AddWithValue("@resid_pin", residPin ?? ""); updateCmd.Parameters.AddWithValue("@chamber_no", chamberNo ?? ""); updateCmd.Parameters.AddWithValue("@vehicle_no", vehicleNo ?? ""); updateCmd.Parameters.AddWithValue("@sticker_no", stickerNo ?? ""); updateCmd.Parameters.AddWithValue("@aadhar_id", aadharId ?? ""); updateCmd.Parameters.AddWithValue("@voter_id", voterId ?? ""); updateCmd.Parameters.AddWithValue("@prefix", prefix ?? "Mr."); updateCmd.Parameters.AddWithValue("@chamber_alloted", chamberAlloted ?? ""); updateCmd.Parameters.AddWithValue("@off_address", offAddress ?? ""); updateCmd.Parameters.AddWithValue("@phone", phone ?? ""); int rows = updateCmd.ExecuteNonQuery(); if (rows >= 0) { // Fetch the updated member details to return them string selectQuery = "SELECT * FROM Memberslist WHERE Bcdenroll = @id OR Ndbano = @id"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); selectCmd.Parameters.AddWithValue("@id", id); SqlDataAdapter da = new SqlDataAdapter(selectCmd); DataTable dt = new DataTable(); da.Fill(dt); var profile = new Dictionary(); if (dt.Rows.Count > 0) { DataRow memberRow = dt.Rows[0]; profile = DataRowToDictionary(memberRow); string validity = "Valid - Lifetime"; if (memberRow.Table.Columns.Contains("Ndbadate") && memberRow["Ndbadate"] != DBNull.Value) { DateTime ndbaDate; if (DateTime.TryParse(memberRow["Ndbadate"].ToString(), out ndbaDate)) { validity = "Valid until " + ndbaDate.AddYears(10).ToString("dd-MMM-yyyy"); } } profile["validity"] = validity; } context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Profile updated successfully.", member = profile })); } else { SendError(context, 500, "Failed to update profile details."); } } } // --- MEMBER REGISTER --- private void HandleMemberRegister(HttpContext context) { string ndbaNo = context.Request.QueryString["ndba_no"]; string bcdEnroll = context.Request.QueryString["bcd_enroll"]; string prefix = context.Request.QueryString["prefix"] ?? "Mr."; string fName = context.Request.QueryString["f_name"]; string mName = context.Request.QueryString["m_name"] ?? ""; string lName = context.Request.QueryString["l_name"]; string fatherName = context.Request.QueryString["father_name"]; string mobile = context.Request.QueryString["mobile"]; string email = context.Request.QueryString["email"]; string password = context.Request.QueryString["password"]; if (string.IsNullOrEmpty(ndbaNo) || string.IsNullOrEmpty(bcdEnroll) || string.IsNullOrEmpty(fName) || string.IsNullOrEmpty(lName) || string.IsNullOrEmpty(fatherName) || string.IsNullOrEmpty(mobile) || string.IsNullOrEmpty(email) || string.IsNullOrEmpty(password)) { SendError(context, 400, "All required fields must be supplied."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); // Check if already exists string checkQuery = "SELECT COUNT(*) FROM Memberslist WHERE Bcdenroll = @bcd OR Ndbano = @ndba"; SqlCommand checkCmd = new SqlCommand(checkQuery, con); checkCmd.Parameters.AddWithValue("@bcd", bcdEnroll); checkCmd.Parameters.AddWithValue("@ndba", ndbaNo); int count = (int)checkCmd.ExecuteScalar(); if (count > 0) { SendError(context, 409, "A member with this BCD Enrollment Number or NDBA Number is already registered."); return; } string insertQuery = @"INSERT INTO Memberslist ( Ndbano, Bcdenroll, Prefix, F_name, M_name, L_name, Father_name, Mobile_1, Email_id, password ) VALUES ( @Ndbano, @Bcdenroll, @Prefix, @F_name, @M_name, @L_name, @Father_name, @Mobile_1, @Email_id, @password )"; SqlCommand insertCmd = new SqlCommand(insertQuery, con); insertCmd.Parameters.AddWithValue("@Ndbano", ndbaNo); insertCmd.Parameters.AddWithValue("@Bcdenroll", bcdEnroll); insertCmd.Parameters.AddWithValue("@Prefix", prefix); insertCmd.Parameters.AddWithValue("@F_name", fName); insertCmd.Parameters.AddWithValue("@M_name", mName); insertCmd.Parameters.AddWithValue("@L_name", lName); insertCmd.Parameters.AddWithValue("@Father_name", fatherName); insertCmd.Parameters.AddWithValue("@Mobile_1", mobile); insertCmd.Parameters.AddWithValue("@Email_id", email); insertCmd.Parameters.AddWithValue("@password", password); int rows = insertCmd.ExecuteNonQuery(); if (rows > 0) { context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Member registered successfully." })); } else { SendError(context, 500, "Failed to register member."); } } } // --- CASE MODULE HELPER: self-healing table creation --- private void EnsureTablesCreated() { using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string sql = @" IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Cases] ( [Id] INT IDENTITY(1,1) PRIMARY KEY, [CaseSubject] NVARCHAR(255) NOT NULL, [CaseDescription] NVARCHAR(MAX) NULL, [ClientName] NVARCHAR(255) NOT NULL, [ClientAddress] NVARCHAR(MAX) NULL, [ClientPhone] NVARCHAR(50) NULL, [ClientEmail] NVARCHAR(255) NULL, [CaseCategory] NVARCHAR(100) NULL, [LawyerId] NVARCHAR(MAX) NOT NULL, [LawyerName] NVARCHAR(MAX) NOT NULL, [CaseStatus] NVARCHAR(50) NOT NULL DEFAULT 'Open', [CaseStartDate] DATETIME NULL, [CaseCloserDate] DATETIME NULL, [NextFollowUpDate] DATETIME NULL, [CreatedBy] NVARCHAR(100) NOT NULL, [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(), [UpdatedAt] DATETIME NOT NULL DEFAULT GETDATE() ); END IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'LawyerId') BEGIN IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cases' AND COLUMN_NAME = 'LawyerId') != -1 BEGIN ALTER TABLE [dbo].[Cases] ALTER COLUMN [LawyerId] NVARCHAR(MAX) NOT NULL; END END IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'LawyerName') BEGIN IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Cases' AND COLUMN_NAME = 'LawyerName') != -1 BEGIN ALTER TABLE [dbo].[Cases] ALTER COLUMN [LawyerName] NVARCHAR(MAX) NOT NULL; END END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CaseComments]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[CaseComments] ( [Id] INT IDENTITY(1,1) PRIMARY KEY, [CaseId] INT NOT NULL, [CommentText] NVARCHAR(MAX) NOT NULL, [AuthorId] NVARCHAR(100) NOT NULL, [AuthorName] NVARCHAR(255) NOT NULL, [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT [FK_CaseComments_Cases] FOREIGN KEY ([CaseId]) REFERENCES [dbo].[Cases]([Id]) ON DELETE CASCADE ); END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CaseFiles]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[CaseFiles] ( [Id] INT IDENTITY(1,1) PRIMARY KEY, [CaseId] INT NOT NULL, [FileName] NVARCHAR(255) NOT NULL, [FilePath] NVARCHAR(MAX) NOT NULL, [UploadedBy] NVARCHAR(100) NOT NULL, [CreatedAt] DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT [FK_CaseFiles_Cases] FOREIGN KEY ([CaseId]) REFERENCES [dbo].[Cases]([Id]) ON DELETE CASCADE ); END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'CRNNumber') BEGIN ALTER TABLE [dbo].[Cases] ADD [CRNNumber] NVARCHAR(100) NULL; END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'HearingCourtType') BEGIN ALTER TABLE [dbo].[Cases] ADD [HearingCourtType] NVARCHAR(100) NULL; END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'HearingSubCourtName') BEGIN ALTER TABLE [dbo].[Cases] ADD [HearingSubCourtName] NVARCHAR(100) NULL; END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'HearingCourtNumber') BEGIN ALTER TABLE [dbo].[Cases] ADD [HearingCourtNumber] NVARCHAR(100) NULL; END IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[Cases]') AND name = N'HearingDate') BEGIN ALTER TABLE [dbo].[Cases] ADD [HearingDate] DATETIME NULL; END"; SqlCommand cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); } } // --- SEARCH LAWYERS --- private void HandleSearchLawyers(HttpContext context) { string search = context.Request.QueryString["search"]; if (string.IsNullOrEmpty(search)) { search = ""; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = @"SELECT TOP 20 Bcdenroll, F_name, M_name, L_name, Mobile_1, Ndbano FROM Memberslist WHERE F_name LIKE @search OR L_name LIKE @search OR Bcdenroll LIKE @search ORDER BY F_name ASC"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@search", "%" + search + "%"); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- CHECK CRN UNIQUE --- private void HandleCheckCRN(HttpContext context) { string crn = context.Request.QueryString["crn"]; string excludeIdStr = context.Request.QueryString["exclude_id"]; int excludeId = 0; int.TryParse(excludeIdStr, out excludeId); if (string.IsNullOrEmpty(crn)) { SendError(context, 400, "Parameter 'crn' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = ""; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; if (excludeId > 0) { query = "SELECT Id FROM Cases WHERE CRNNumber = @crn AND Id != @excludeId"; cmd.Parameters.AddWithValue("@excludeId", excludeId); } else { query = "SELECT Id FROM Cases WHERE CRNNumber = @crn"; } cmd.CommandText = query; cmd.Parameters.AddWithValue("@crn", crn); object result = cmd.ExecuteScalar(); int caseId = result != null && result != DBNull.Value ? Convert.ToInt32(result) : 0; context.Response.Write(JsonConvert.SerializeObject(new { success = true, exists = (caseId > 0), caseId = caseId })); } } // --- GET COURTS --- private void HandleGetCourts(HttpContext context) { EnsureJudgesTableCreated(); using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = "SELECT pkid, CourtType, ISNULL(SubCourtName, '') AS SubCourtName, Room AS CourtNumber, Name AS JudgeName, VCLink AS WebexLink FROM newdelhibar_Judges ORDER BY CourtType, SubCourtName, Room ASC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- GET JUDGES --- private void HandleGetJudges(HttpContext context) { EnsureJudgesTableCreated(); using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = "SELECT pkid, CourtType, ISNULL(SubCourtName, '') AS SubCourtName, Name, Designation, Room, VCLink, VCMeetingID, ISNULL(Direction, '') AS Direction FROM newdelhibar_Judges ORDER BY CourtType, SubCourtName, Name ASC"; SqlCommand cmd = new SqlCommand(query, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } private void EnsureJudgesTableCreated() { string checkQuery = "SELECT 1 FROM sys.tables WHERE name = 'newdelhibar_Judges'"; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmdCheck = new SqlCommand(checkQuery, conn)) { object obj = cmdCheck.ExecuteScalar(); if (obj == null) { string createQuery = @" CREATE TABLE newdelhibar_Judges ( pkid INT IDENTITY(1,1) PRIMARY KEY, CourtType NVARCHAR(100) NOT NULL, SubCourtName NVARCHAR(100) NULL, Name NVARCHAR(250) NOT NULL, Designation NVARCHAR(250) NOT NULL, Room NVARCHAR(100) NOT NULL, VCLink NVARCHAR(1000) NOT NULL, VCMeetingID NVARCHAR(250) NOT NULL, Direction NVARCHAR(50) NULL, CreatedDate DATETIME DEFAULT GETDATE() )"; using (SqlCommand cmdCreate = new SqlCommand(createQuery, conn)) { cmdCreate.ExecuteNonQuery(); } } else { // Ensure the 'Direction' column exists try { string checkColQuery = "IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[newdelhibar_Judges]') AND name = N'Direction') " + "ALTER TABLE newdelhibar_Judges ADD Direction NVARCHAR(50) NULL;"; using (SqlCommand cmdCheckCol = new SqlCommand(checkColQuery, conn)) { cmdCheckCol.ExecuteNonQuery(); } } catch { /* ignore */ } } } } } // --- CREATE CASE --- private void HandleCaseCreate(HttpContext context) { EnsureTablesCreated(); string json = ""; using (var reader = new StreamReader(context.Request.InputStream)) { json = reader.ReadToEnd(); } dynamic data = JsonConvert.DeserializeObject(json); if (data == null) { SendError(context, 400, "Invalid or empty request body."); return; } string caseSubject = data.CaseSubject; string caseDescription = data.CaseDescription; string clientName = data.ClientName; string clientAddress = data.ClientAddress; string clientPhone = data.ClientPhone; string clientEmail = data.ClientEmail; string caseCategory = data.CaseCategory; string lawyerId = data.LawyerId; string lawyerName = data.LawyerName; string caseStatus = data.CaseStatus ?? "Open"; string caseStartDateStr = data.CaseStartDate; string caseCloserDateStr = data.CaseCloserDate; string nextFollowUpDateStr = data.NextFollowUpDate; string createdBy = data.CreatedBy; string crnNumber = data.CRNNumber; string hearingCourtType = data.HearingCourtType; string hearingSubCourtName = data.HearingSubCourtName; string hearingCourtNumber = data.HearingCourtNumber; string hearingDateStr = data.HearingDate; if (string.IsNullOrEmpty(caseSubject) || string.IsNullOrEmpty(clientName) || string.IsNullOrEmpty(lawyerId) || string.IsNullOrEmpty(createdBy)) { SendError(context, 400, "Required fields: CaseSubject, ClientName, LawyerId, and CreatedBy."); return; } DateTime? caseStartDate = null; DateTime? caseCloserDate = null; DateTime? nextFollowUpDate = null; DateTime? hearingDate = null; DateTime tempDate; if (!string.IsNullOrEmpty(caseStartDateStr) && DateTime.TryParse(caseStartDateStr, out tempDate)) caseStartDate = tempDate; if (!string.IsNullOrEmpty(caseCloserDateStr) && DateTime.TryParse(caseCloserDateStr, out tempDate)) caseCloserDate = tempDate; if (!string.IsNullOrEmpty(nextFollowUpDateStr) && DateTime.TryParse(nextFollowUpDateStr, out tempDate)) nextFollowUpDate = tempDate; if (!string.IsNullOrEmpty(hearingDateStr) && DateTime.TryParse(hearingDateStr, out tempDate)) hearingDate = tempDate; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = @"INSERT INTO Cases (CaseSubject, CaseDescription, ClientName, ClientAddress, ClientPhone, ClientEmail, CaseCategory, LawyerId, LawyerName, CaseStatus, CaseStartDate, CaseCloserDate, NextFollowUpDate, CreatedBy, CRNNumber, HearingCourtType, HearingSubCourtName, HearingCourtNumber, HearingDate) VALUES (@CaseSubject, @CaseDescription, @ClientName, @ClientAddress, @ClientPhone, @ClientEmail, @CaseCategory, @LawyerId, @LawyerName, @CaseStatus, @CaseStartDate, @CaseCloserDate, @NextFollowUpDate, @CreatedBy, @CRNNumber, @HearingCourtType, @HearingSubCourtName, @HearingCourtNumber, @HearingDate); SELECT SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@CaseSubject", caseSubject); cmd.Parameters.AddWithValue("@CaseDescription", (object)caseDescription ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientName", clientName); cmd.Parameters.AddWithValue("@ClientAddress", (object)clientAddress ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientPhone", (object)clientPhone ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientEmail", (object)clientEmail ?? DBNull.Value); cmd.Parameters.AddWithValue("@CaseCategory", (object)caseCategory ?? DBNull.Value); cmd.Parameters.AddWithValue("@LawyerId", lawyerId); cmd.Parameters.AddWithValue("@LawyerName", lawyerName); cmd.Parameters.AddWithValue("@CaseStatus", caseStatus); cmd.Parameters.AddWithValue("@CaseStartDate", (object)caseStartDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@CaseCloserDate", (object)caseCloserDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@NextFollowUpDate", (object)nextFollowUpDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@CreatedBy", createdBy); cmd.Parameters.AddWithValue("@CRNNumber", (object)crnNumber ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingCourtType", (object)hearingCourtType ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingSubCourtName", (object)hearingSubCourtName ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingCourtNumber", (object)hearingCourtNumber ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingDate", (object)hearingDate ?? DBNull.Value); object newId = cmd.ExecuteScalar(); context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Case created successfully.", case_id = Convert.ToInt32(newId) })); } } // --- UPDATE CASE --- private void HandleCaseUpdate(HttpContext context) { EnsureTablesCreated(); string json = ""; using (var reader = new StreamReader(context.Request.InputStream)) { json = reader.ReadToEnd(); } dynamic data = JsonConvert.DeserializeObject(json); if (data == null) { SendError(context, 400, "Invalid or empty request body."); return; } int caseId = 0; if (data.CaseId == null || !int.TryParse(data.CaseId.ToString(), out caseId)) { SendError(context, 400, "CaseId is required and must be an integer."); return; } string caseSubject = data.CaseSubject; string caseDescription = data.CaseDescription; string clientName = data.ClientName; string clientAddress = data.ClientAddress; string clientPhone = data.ClientPhone; string clientEmail = data.ClientEmail; string caseCategory = data.CaseCategory; string lawyerId = data.LawyerId; string lawyerName = data.LawyerName; string caseStatus = data.CaseStatus; string caseStartDateStr = data.CaseStartDate; string caseCloserDateStr = data.CaseCloserDate; string nextFollowUpDateStr = data.NextFollowUpDate; string crnNumber = data.CRNNumber; string hearingCourtType = data.HearingCourtType; string hearingSubCourtName = data.HearingSubCourtName; string hearingCourtNumber = data.HearingCourtNumber; string hearingDateStr = data.HearingDate; DateTime? caseStartDate = null; DateTime? caseCloserDate = null; DateTime? nextFollowUpDate = null; DateTime? hearingDate = null; DateTime tempDate; if (!string.IsNullOrEmpty(caseStartDateStr) && DateTime.TryParse(caseStartDateStr, out tempDate)) caseStartDate = tempDate; if (!string.IsNullOrEmpty(caseCloserDateStr) && DateTime.TryParse(caseCloserDateStr, out tempDate)) caseCloserDate = tempDate; if (!string.IsNullOrEmpty(nextFollowUpDateStr) && DateTime.TryParse(nextFollowUpDateStr, out tempDate)) nextFollowUpDate = tempDate; if (!string.IsNullOrEmpty(hearingDateStr) && DateTime.TryParse(hearingDateStr, out tempDate)) hearingDate = tempDate; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = @"UPDATE Cases SET CaseSubject = @CaseSubject, CaseDescription = @CaseDescription, ClientName = @ClientName, ClientAddress = @ClientAddress, ClientPhone = @ClientPhone, ClientEmail = @ClientEmail, CaseCategory = @CaseCategory, LawyerId = @LawyerId, LawyerName = @LawyerName, CaseStatus = @CaseStatus, CaseStartDate = @CaseStartDate, CaseCloserDate = @CaseCloserDate, NextFollowUpDate = @NextFollowUpDate, UpdatedAt = GETDATE(), CRNNumber = @CRNNumber, HearingCourtType = @HearingCourtType, HearingSubCourtName = @HearingSubCourtName, HearingCourtNumber = @HearingCourtNumber, HearingDate = @HearingDate WHERE Id = @CaseId"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@CaseId", caseId); cmd.Parameters.AddWithValue("@CaseSubject", caseSubject); cmd.Parameters.AddWithValue("@CaseDescription", (object)caseDescription ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientName", clientName); cmd.Parameters.AddWithValue("@ClientAddress", (object)clientAddress ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientPhone", (object)clientPhone ?? DBNull.Value); cmd.Parameters.AddWithValue("@ClientEmail", (object)clientEmail ?? DBNull.Value); cmd.Parameters.AddWithValue("@CaseCategory", (object)caseCategory ?? DBNull.Value); cmd.Parameters.AddWithValue("@LawyerId", lawyerId); cmd.Parameters.AddWithValue("@LawyerName", lawyerName); cmd.Parameters.AddWithValue("@CaseStatus", caseStatus); cmd.Parameters.AddWithValue("@CaseStartDate", (object)caseStartDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@CaseCloserDate", (object)caseCloserDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@NextFollowUpDate", (object)nextFollowUpDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@CRNNumber", (object)crnNumber ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingCourtType", (object)hearingCourtType ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingSubCourtName", (object)hearingSubCourtName ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingCourtNumber", (object)hearingCourtNumber ?? DBNull.Value); cmd.Parameters.AddWithValue("@HearingDate", (object)hearingDate ?? DBNull.Value); int rows = cmd.ExecuteNonQuery(); if (rows > 0) { context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Case updated successfully." })); } else { SendError(context, 404, "Case not found."); } } } // --- CASE LIST --- private void HandleCaseList(HttpContext context) { EnsureTablesCreated(); string lawyerId = context.Request.QueryString["lawyer_id"]; string search = context.Request.QueryString["search"]; if (string.IsNullOrEmpty(lawyerId)) { SendError(context, 400, "Parameter 'lawyer_id' is required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = ""; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; if (string.IsNullOrEmpty(search)) { query = @"SELECT * FROM Cases WHERE (LawyerId = @lawyerId OR ',' + LawyerId + ',' LIKE '%,' + @lawyerId + ',%') ORDER BY UpdatedAt DESC"; cmd.Parameters.AddWithValue("@lawyerId", lawyerId); } else { query = @"SELECT * FROM Cases WHERE (LawyerId = @lawyerId OR ',' + LawyerId + ',' LIKE '%,' + @lawyerId + ',%') AND (CaseSubject LIKE @search OR ClientName LIKE @search OR ClientPhone LIKE @search OR CaseCategory LIKE @search OR CaseStatus LIKE @search OR CRNNumber LIKE @search) ORDER BY UpdatedAt DESC"; cmd.Parameters.AddWithValue("@lawyerId", lawyerId); cmd.Parameters.AddWithValue("@search", "%" + search + "%"); } cmd.CommandText = query; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, data = dt })); } } // --- CASE DETAILS --- private void HandleCaseDetails(HttpContext context) { EnsureTablesCreated(); string caseIdStr = context.Request.QueryString["case_id"]; int caseId = 0; if (string.IsNullOrEmpty(caseIdStr) || !int.TryParse(caseIdStr, out caseId)) { SendError(context, 400, "Parameter 'case_id' is required and must be an integer."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string caseQuery = @" SELECT c.*, w.VCLink AS WebexLink FROM Cases c LEFT JOIN newdelhibar_Judges w ON c.HearingCourtType = w.CourtType AND ISNULL(c.HearingSubCourtName, '') = ISNULL(w.SubCourtName, '') AND c.HearingCourtNumber = w.Room WHERE c.Id = @CaseId"; SqlCommand caseCmd = new SqlCommand(caseQuery, con); caseCmd.Parameters.AddWithValue("@CaseId", caseId); SqlDataAdapter caseDa = new SqlDataAdapter(caseCmd); DataTable caseDt = new DataTable(); caseDa.Fill(caseDt); if (caseDt.Rows.Count == 0) { SendError(context, 404, "Case not found."); return; } DataRow caseRow = caseDt.Rows[0]; string commentsQuery = "SELECT * FROM CaseComments WHERE CaseId = @CaseId ORDER BY CreatedAt DESC"; SqlCommand commentsCmd = new SqlCommand(commentsQuery, con); commentsCmd.Parameters.AddWithValue("@CaseId", caseId); SqlDataAdapter commentsDa = new SqlDataAdapter(commentsCmd); DataTable commentsDt = new DataTable(); commentsDa.Fill(commentsDt); string filesQuery = "SELECT * FROM CaseFiles WHERE CaseId = @CaseId ORDER BY CreatedAt DESC"; SqlCommand filesCmd = new SqlCommand(filesQuery, con); filesCmd.Parameters.AddWithValue("@CaseId", caseId); SqlDataAdapter filesDa = new SqlDataAdapter(filesCmd); DataTable filesDt = new DataTable(); filesDa.Fill(filesDt); context.Response.Write(JsonConvert.SerializeObject(new { success = true, @case = DataRowToDictionary(caseRow), comments = commentsDt, files = filesDt })); } } // --- ADD COMMENT --- private void HandleCaseCommentAdd(HttpContext context) { EnsureTablesCreated(); string json = ""; using (var reader = new StreamReader(context.Request.InputStream)) { json = reader.ReadToEnd(); } dynamic data = JsonConvert.DeserializeObject(json); if (data == null) { SendError(context, 400, "Invalid or empty request body."); return; } int caseId = 0; if (data.CaseId == null || !int.TryParse(data.CaseId.ToString(), out caseId)) { SendError(context, 400, "CaseId is required and must be an integer."); return; } string commentText = data.CommentText; string authorId = data.AuthorId; string authorName = data.AuthorName; if (string.IsNullOrEmpty(commentText) || string.IsNullOrEmpty(authorId) || string.IsNullOrEmpty(authorName)) { SendError(context, 400, "CommentText, AuthorId, and AuthorName are required."); return; } using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = @"INSERT INTO CaseComments (CaseId, CommentText, AuthorId, AuthorName) VALUES (@CaseId, @CommentText, @AuthorId, @AuthorName); UPDATE Cases SET UpdatedAt = GETDATE() WHERE Id = @CaseId;"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@CaseId", caseId); cmd.Parameters.AddWithValue("@CommentText", commentText); cmd.Parameters.AddWithValue("@AuthorId", authorId); cmd.Parameters.AddWithValue("@AuthorName", authorName); cmd.ExecuteNonQuery(); context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "Comment added successfully." })); } } // --- UPLOAD FILE --- private void HandleCaseFileUpload(HttpContext context) { EnsureTablesCreated(); if (context.Request.Files.Count == 0) { SendError(context, 400, "No file uploaded."); return; } string caseIdStr = context.Request.Form["case_id"]; string uploadedBy = context.Request.Form["uploaded_by"]; int caseId = 0; if (string.IsNullOrEmpty(caseIdStr) || !int.TryParse(caseIdStr, out caseId)) { SendError(context, 400, "Parameter 'case_id' is required and must be an integer."); return; } if (string.IsNullOrEmpty(uploadedBy)) { SendError(context, 400, "Parameter 'uploaded_by' is required."); return; } HttpPostedFile file = context.Request.Files[0]; if (file.ContentLength == 0) { SendError(context, 400, "Uploaded file is empty."); return; } string fileName = Path.GetFileName(file.FileName); string folderPath = context.Server.MapPath("~/Uploads/Cases/"); if (!Directory.Exists(folderPath)) { Directory.CreateDirectory(folderPath); } string uniqueFileName = Guid.NewGuid().ToString() + "_" + fileName; string relativePath = "Uploads/Cases/" + uniqueFileName; string fullPath = Path.Combine(folderPath, uniqueFileName); file.SaveAs(fullPath); using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); string query = @"INSERT INTO CaseFiles (CaseId, FileName, FilePath, UploadedBy) VALUES (@CaseId, @FileName, @FilePath, @UploadedBy); UPDATE Cases SET UpdatedAt = GETDATE() WHERE Id = @CaseId;"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@CaseId", caseId); cmd.Parameters.AddWithValue("@FileName", fileName); cmd.Parameters.AddWithValue("@FilePath", relativePath); cmd.Parameters.AddWithValue("@UploadedBy", uploadedBy); cmd.ExecuteNonQuery(); context.Response.Write(JsonConvert.SerializeObject(new { success = true, message = "File uploaded successfully.", file_name = fileName, file_path = relativePath })); } } private Dictionary DataRowToDictionary(DataRow row) { if (row == null) return null; var dict = new Dictionary(); foreach (DataColumn col in row.Table.Columns) { dict[col.ColumnName] = row[col] == DBNull.Value ? null : row[col]; } return dict; } public bool IsReusable { get { return false; } } }