public JsonResult TelephoneDataProviderAction(JQueryDataTableParamModel aModel) { List<string> columnlist = new List<string>(new string[] { "BANumber", "Name", "Rank", "NewPhoneNumber", "Address", "Gender", "Status", "ConnectedDate", "DisconnectedDate" }); string serachValue = Request["columns[0][search][value]"]; var roleId = Request.Params["max"]; var banumberFilter = Convert.ToString(Request["sSearch_0"]).Trim(); var nameFilter = Convert.ToString(Request["sSearch_1"]).Trim(); var rankFilter = Convert.ToString(Request["sSearch_2"]).Trim(); var phoneFilter = Convert.ToString(Request["sSearch_3"]).Trim(); // var addressFilter = Convert.ToString(Request["sSearch_4"]).Trim(); var phoneTypeFilter = Convert.ToString(Request["sSearch_4"]).Trim(); var homeAddressFilter = Convert.ToString(Request["sSearch_5"]).Trim(); var officeAddressFilter = Convert.ToString(Request["sSearch_6"]); var genderFilter = Convert.ToString(Request["sSearch_7"]); var complainFilter = Convert.ToString(Request["sSearch_5"]); var hourlyFilter = Convert.ToString(Request["sSearch_8"]); var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]); DateTime fromDate = DateTime.MinValue; DateTime toDate = DateTime.MaxValue; if (hourlyFilter.Contains("-yadcf_delim-")) { string a1 = hourlyFilter.Replace("-yadcf_delim-", "~"); //Split date range filters with ~ fromDate = a1.Split('~')[0] == "" ? DateTime.MinValue : Convert.ToDateTime(a1.Split('~')[0]); toDate = a1.Split('~')[1] == "" ? DateTime.MaxValue : Convert.ToDateTime(a1.Split('~')[1]).AddHours(23.99); } string sSearch = string.Empty; if (aModel.sSearch == null) { sSearch = ""; } else { sSearch = aModel.sSearch.ToString(); } var banameSortDirection = Request["sSortDir_0"]; TelphoneUser aTelephoneUser = new TelphoneUser(); // List<TelphoneUser> aUsersList = aTelephoneUser.GetTelphoneUsers(); Telephone aTelephone = new Telephone(); FilteredLists filterTelphoneUsers = aTelephone.GetFilteredPhoneList(sSearch, banumberFilter, phoneFilter, nameFilter, rankFilter, homeAddressFilter, officeAddressFilter, phoneTypeFilter, genderFilter, fromDate, toDate, aModel.iDisplayStart, aModel.iDisplayLength, aModel.sSortDir_0, aModel.iSortCol_0); var result = from c in filterTelphoneUsers.aTelePhoneUserList select new[] { c.BANumber, c.Name, c.Rank, c.NewPhoneNumber, c.PhoneType, c.HomeAddress, c.OfficeAddress, c.Gender, c.ConnectedDate, "Edit", Convert.ToString(c.ID) }; List<string[]> resultList = result.ToList(); List<string> aRankList = new List<string>(); List<string> banumberList = new List<string>(); List<string> complainTypeList = new List<string>(); List<string> statusList=new List<string>(); return Json(new { sEcho = aModel.sEcho, yadcf_data_0 = filterTelphoneUsers.BaNumberList, yadcf_data_2 = filterTelphoneUsers.RankList, yadcf_data_4 = filterTelphoneUsers.PhoneTypeList, // yadcf_data_6 = statusList, iTotalRecords = filterTelphoneUsers.TotalSize, iTotalDisplayRecords = filterTelphoneUsers.FilteredSize, aaData = resultList }, JsonRequestBehavior.AllowGet); }
public ActionResult PhoneDisconnect(TelphoneUser aTelphoneUser) { DBGateway aGateway = new DBGateway(); string complainStatus = string.Empty; string query = string.Empty; string id = Request["Disconnect_PhoneId"]; string remarks = Request["Disconnect_Remarks"]; TelphoneUser.DisconnectPhoneOperation(id, aGateway, remarks); return RedirectToAction("ListPhones", "Telephone"); }
public ActionResult PhoneConnectDeleteAction(TelphoneUser aTelphoneUser) { string complainStatus = string.Empty; string query = string.Empty; string status = Request["Status"]; string id = Request["Connect_PhoneId"]; string remarks = Request["Connect_Remarks"]; TelphoneUser.UpdateDeletePhoneUserData(status, id, remarks); return RedirectToAction("ListPhones", "Telephone"); }
public static List<TelphoneUser> GetPhoneUserData(string id) { string query = "select telephoneusers.Id,telephoneusers.BANumber,telephoneusers.Name,menusrank.Value as Rank,telephoneusers.NewPhoneNumber,telephoneusers.Address,telephoneusers.Gender,telephoneusers.`Status`,telephoneusers.ConnectedDate,telephoneusers.DisconnectedDate from telephoneusers,menusrank where telephoneusers.RankId=menusrank.id and telephoneusers.ID=" + id + ""; List<TelphoneUser> phoneList = new List<TelphoneUser>(); DBGateway aGateway = new DBGateway(); DataSet aDataSet = aGateway.Select(query); foreach (DataRow dataRow in aDataSet.Tables[0].Rows) { TelphoneUser aTelphoneUser = new TelphoneUser(); aTelphoneUser.BANumber = dataRow["BANumber"].ToString(); aTelphoneUser.Name = dataRow["Name"].ToString(); aTelphoneUser.NewPhoneNumber = dataRow["NewPhoneNumber"].ToString(); aTelphoneUser.Rank = dataRow["Rank"].ToString(); aTelphoneUser.HomeAddress = dataRow["Address"].ToString(); aTelphoneUser.PhoneStatus = dataRow["Status"].ToString(); aTelphoneUser.ID = dataRow["Id"].ToString(); phoneList.Add(aTelphoneUser); } return phoneList; }
public FilteredLists GetFilteredPhoneList(string sSearch,string banumberFilter, string phoneFilter, string nameFilter, string rankFilter, string homeAddressFilter, string officeAddressFilter, string phoneTypeFilter, string genderFilter, DateTime fromDate, DateTime toDate,int skipSize,int takeSize,string sortOrder,int sortColumnIndex) { string query3 = "select allactivephoneinfo.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menusrank.Value as Rank,allphoneinfo.PhoneNumber,allactivephoneinfo.PhoneUsedFor,allactivephoneinfo.HomeAddress,allactivephoneinfo.OfficeAddress,phoneuserpersonalinfo.Sex as Gender,allactivephoneinfo.ConnectDate from phoneuserpersonalinfo,menusrank,allactivephoneinfo,allphoneinfo where menusrank.id=phoneuserpersonalinfo.RankId and phoneuserpersonalinfo.ID=allactivephoneinfo.PhoneUserPersonalInfoId and allphoneinfo.ID=allactivephoneinfo.AllPhoneInfoID;"; string complainQuery = " select SQL_CALC_FOUND_ROWS allactivephoneinfo.id,Banumber,fullname,menusrank.value as Rank,Phonenumber,phoneusedfor,Homeaddress,allactivephoneinfo.OfficeAddress,phoneuserpersonalinfo.Sex,connectdate from allactivephoneinfo,phoneuserpersonalinfo,allphoneinfo,menusrank where phoneuserpersonalinfo.ID=allactivephoneinfo.PhoneUserPersonalInfoId and allactivephoneinfo.AllPhoneInfoID=allphoneinfo.ID and phoneuserpersonalinfo.RankId=menusrank.id "; if (sSearch.Trim().Length > 0) { complainQuery += "and ((phoneuserpersonalinfo.BANumber like '%" + sSearch + "%')" + " or (phoneuserpersonalinfo.FullName like '%" + sSearch + "%')" + " or (allphoneinfo.PhoneNumber like '%" + sSearch + "%')" + " or (allactivephoneinfo.PhoneUsedFor like '%" + sSearch + "%')" + " or (allactivephoneinfo.HomeAddress like '%" + sSearch + "%')" + " or (allactivephoneinfo.OfficeAddress like '%" + sSearch + "%')" + " or (phoneuserpersonalinfo.Sex like '%" + sSearch + "%')" + " or (menusrank.Value like '%" + sSearch + "%'))"; } if (banumberFilter.Trim().Length > 0) { complainQuery += "and (phoneuserpersonalinfo.BANumber = '" + banumberFilter + "')"; } if (phoneTypeFilter.Trim().Length > 0) { complainQuery += "and (allactivephoneinfo.PhoneUsedFor like '%" + phoneTypeFilter + "%')"; } if (nameFilter.Trim().Length > 0) { complainQuery += "and (phoneuserpersonalinfo.FullName like '%" + nameFilter + "%')"; } if (phoneFilter.Trim().Length > 0) { complainQuery += "and (allphoneinfo.PhoneNumber like '%" + phoneFilter + "%')"; } if (rankFilter.Trim().Length > 0) { complainQuery += "and (menusrank.Value = '" + rankFilter + "')"; } if (homeAddressFilter.Trim().Length > 0) { complainQuery += "and (allactivephoneinfo.HomeAddress like '%" + homeAddressFilter + "%')"; } if (officeAddressFilter.Trim().Length > 0) { complainQuery += "and (allactivephoneinfo.OfficeAddress like '%" + officeAddressFilter + "%')"; } complainQuery += " and ((allactivephoneinfo.ConnectDate >= DATE_FORMAT('" + fromDate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')) and (allactivephoneinfo.ConnectDate <= DATE_FORMAT('" + toDate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')))"; List<string> tableDBColumns = new List<string>(new string[] { "phoneuserpersonalinfo.BANumber", "phoneuserpersonalinfo.FullName", "menusrank.Value", "allphoneinfo.PhoneNumber", "allactivephoneinfo.PhoneUsedFor", "allactivephoneinfo.HomeAddress","allactivephoneinfo.OfficeAddress","phoneuserpersonalinfo.Sex", "allactivephoneinfo.ConnectDate" }); complainQuery += " order by " + tableDBColumns[sortColumnIndex] + " " + sortOrder + " "; complainQuery += " limit " + skipSize + ", " + takeSize + ";"; complainQuery += " SELECT FOUND_ROWS();"; complainQuery += " SELECT count(*) from allactivephoneinfo;"; complainQuery += "select distinct phoneuserpersonalinfo.BANumber from phoneuserpersonalinfo;select distinct menusrank.Value from menusrank;select distinct allactivephoneinfo.PhoneUsedFor from allactivephoneinfo;"; DataSet ds = MySql.Data.MySqlClient.MySqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.ConnectionStrings["SignalSystemConnectionString"].ConnectionString, complainQuery); int filteredCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]); int lTotalCount = Convert.ToInt32(ds.Tables[2].Rows[0][0]); List<TelphoneUser> aList = new List<TelphoneUser>(); foreach (DataRow dataRow in ds.Tables[0].Rows) {TelephoneComplain aTelephoneComplain = new TelephoneComplain(); TelphoneUser aTelphoneUser = new TelphoneUser(); aTelphoneUser.BANumber = dataRow["BANumber"].ToString(); aTelphoneUser.Name = dataRow["FullName"].ToString(); aTelphoneUser.NewPhoneNumber = dataRow["PhoneNumber"].ToString(); aTelphoneUser.Rank = dataRow["Rank"].ToString(); aTelphoneUser.ConnectedDate = dataRow["ConnectDate"].ToString(); // aTelphoneUser.DisconnectedDate = dataRow["DisconnectedDate"].ToString(); aTelphoneUser.ID = dataRow["Id"].ToString(); // aTelphoneUser.PhoneStatus = dataRow["Status"].ToString(); aTelphoneUser.HomeAddress = dataRow["HomeAddress"].ToString(); aTelphoneUser.OfficeAddress = dataRow["OfficeAddress"].ToString(); aTelphoneUser.Gender = dataRow["Sex"].ToString(); aTelphoneUser.PhoneType = dataRow["PhoneUsedFor"].ToString(); aList.Add(aTelphoneUser); } List<string> BANumberList = new List<string>(); foreach (DataRow dataRow in ds.Tables[3].Rows) { BANumberList.Add(dataRow["BANumber"].ToString()); } List<string> RankList = new List<string>(); foreach (DataRow dataRow in ds.Tables[4].Rows) { RankList.Add(dataRow["Value"].ToString()); } List<string> PhoneTypeList = new List<string>(); foreach (DataRow dataRow in ds.Tables[5].Rows) { PhoneTypeList.Add(dataRow["PhoneUsedFor"].ToString()); } List<TelphoneUser> listTelphoneUsers = new List<TelphoneUser>(); DBGateway aGateway = new DBGateway(); DataSet aDataSet = aGateway.Select(query3); foreach (DataRow dataRow in aDataSet.Tables[0].Rows) { TelphoneUser aTelphoneUser = new TelphoneUser(); aTelphoneUser.BANumber = dataRow["BANumber"].ToString(); aTelphoneUser.Name = dataRow["FullName"].ToString(); aTelphoneUser.NewPhoneNumber = dataRow["PhoneNumber"].ToString(); aTelphoneUser.Rank = dataRow["Rank"].ToString(); aTelphoneUser.ConnectedDate = dataRow["ConnectDate"].ToString(); // aTelphoneUser.DisconnectedDate = dataRow["DisconnectedDate"].ToString(); aTelphoneUser.ID = dataRow["Id"].ToString(); // aTelphoneUser.PhoneStatus = dataRow["Status"].ToString(); aTelphoneUser.HomeAddress = dataRow["HomeAddress"].ToString(); aTelphoneUser.OfficeAddress = dataRow["OfficeAddress"].ToString(); aTelphoneUser.Gender = dataRow["Gender"].ToString(); aTelphoneUser.PhoneType = dataRow["PhoneUsedFor"].ToString(); listTelphoneUsers.Add(aTelphoneUser); } List<TelphoneUser> filteredPhoneList = listTelphoneUsers; FilteredLists aFilteredList=new FilteredLists(); aFilteredList.aTelePhoneUserList = aList; aFilteredList.FilteredSize = filteredCount; aFilteredList.TotalSize = lTotalCount; aFilteredList.BaNumberList = BANumberList; aFilteredList.RankList = RankList; aFilteredList.PhoneTypeList = PhoneTypeList; return aFilteredList; }
public List<TelphoneUser> GetTelphoneUsers() { DBGateway aGateway=new DBGateway(); string query = "select telephoneusers.Id,telephoneusers.BANumber,telephoneusers.Name,menusrank.Value as Rank,telephoneusers.NewPhoneNumber,telephoneusers.Address,telephoneusers.Gender,telephoneusers.`Status`,telephoneusers.ConnectedDate,telephoneusers.DisconnectedDate from telephoneusers,menusrank where telephoneusers.RankId=menusrank.id"; string query2 = "select complains.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menucomplainType.Value as ComplainType," + " allphoneinfo.PhoneNumber,menusrank.Value as Rank,complains.Description,complains.ComplainDate" + " from complains,menucomplainType,phoneuserpersonalinfo,allphoneinfo," + " menusRank where menucomplaintype.Id=complains.MenuComplainTypeId" + " and phoneuserpersonalinfo.Id = complains.TelephoneUserId and phoneuserpersonalinfo.RankId = menusrank.id and complains.AllPhoneInfoID=allphoneinfo.ID"; string query3 = "select allactivephoneinfo.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menusrank.Value as Rank,allphoneinfo.PhoneNumber,allactivephoneinfo.PhoneUsedFor,allactivephoneinfo.HomeAddress,allactivephoneinfo.OfficeAddress,phoneuserpersonalinfo.Sex as Gender,allactivephoneinfo.ConnectDate from phoneuserpersonalinfo,menusrank,allactivephoneinfo,allphoneinfo where menusrank.id=phoneuserpersonalinfo.RankId and phoneuserpersonalinfo.ID=allactivephoneinfo.PhoneUserPersonalInfoId and allphoneinfo.ID=allactivephoneinfo.AllPhoneInfoID;"; List<TelphoneUser> listTelphoneUsers=new List<TelphoneUser>(); DataSet aDataSet = aGateway.Select(query3); foreach (DataRow dataRow in aDataSet.Tables[0].Rows) { TelphoneUser aTelphoneUser = new TelphoneUser(); aTelphoneUser.BANumber = dataRow["BANumber"].ToString(); aTelphoneUser.Name = dataRow["FullName"].ToString(); aTelphoneUser.NewPhoneNumber = dataRow["PhoneNumber"].ToString(); aTelphoneUser.Rank = dataRow["Rank"].ToString(); aTelphoneUser.ConnectedDate = dataRow["ConnectDate"].ToString(); // aTelphoneUser.DisconnectedDate = dataRow["DisconnectedDate"].ToString(); aTelphoneUser.ID = dataRow["Id"].ToString(); // aTelphoneUser.PhoneStatus = dataRow["Status"].ToString(); aTelphoneUser.HomeAddress = dataRow["HomeAddress"].ToString(); aTelphoneUser.OfficeAddress = dataRow["OfficeAddress"].ToString(); aTelphoneUser.Gender = dataRow["Gender"].ToString(); aTelphoneUser.PhoneType = dataRow["PhoneUsedFor"].ToString(); listTelphoneUsers.Add(aTelphoneUser); } return listTelphoneUsers; }