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;
}
