public static FilteredLists GetResolvedFilteredComplaneList(string sSearch, string banumberFilter, string phoneFilter,
string nameFilter, string rankFilter, string complainFilter, DateTime fromDate, DateTime toDate,
string resolvedByFilter, DateTime resolverFromdate, DateTime resolverTodate, string actionFilter,
string remarksFilter,int skipSize,int takeSize,string sortOrder,int sortColumnIndex)
{
string complainQuery =
"select SQL_CALC_FOUND_ROWS resolvedcomplains.id,resolvedcomplains.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menucomplainType.Value as ComplainType," +
" allphoneinfo.PhoneNumber,menusrank.Value as Rank,resolvedcomplains.Description,resolvedcomplains.ComplainDate,resolvedcomplains.ResolvedBy," +
" resolvedcomplains.ActionTaken,resolvedcomplains.ResolvedDate,resolvedcomplains.Remarks" +
" from resolvedcomplains,menucomplainType,phoneuserpersonalinfo,allphoneinfo," +
" menusRank where menucomplaintype.Id=resolvedcomplains.MenuComplainTypeId" +
" and phoneuserpersonalinfo.Id = resolvedcomplains.TelephoneUserId and phoneuserpersonalinfo.RankId = menusrank.id and resolvedcomplains.AllPhoneInfoID=allphoneinfo.ID ";
if (sSearch.Trim().Length > 0)
{
complainQuery += "and ((phoneuserpersonalinfo.BANumber like '%"+sSearch+"%')" +
"or (menucomplainType.Value like '%"+sSearch+"%')" +
"or (phoneuserpersonalinfo.FullName like '%"+sSearch+"%')" +
"or (allphoneinfo.PhoneNumber like '%"+sSearch+"%')" +
"or (menusrank.Value like '%"+sSearch+"%')" +
// "or (resolvedcomplains.Description like '%"+sSearch+"%')" +
"or (resolvedcomplains.ResolvedBy like '%"+sSearch+"%')" +
"or (resolvedcomplains.ActionTaken like '%"+sSearch+"%')" +
"or (resolvedcomplains.Remarks like '%"+sSearch+"%'))";
}
if (banumberFilter.Trim().Length > 0)
{
complainQuery += "and (phoneuserpersonalinfo.BANumber = '"+banumberFilter+"')";
}
if (complainFilter.Trim().Length > 0)
{
complainQuery += "and (menucomplainType.Value = '" + complainFilter + "')";
}
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 (resolvedByFilter.Trim().Length > 0)
{
complainQuery += "and (resolvedcomplains.ResolvedBy like '%" + resolvedByFilter + "%')";
}
if (actionFilter.Trim().Length > 0)
{
complainQuery += "and (resolvedcomplains.ActionTaken like '%" + actionFilter + "%')";
}
if (remarksFilter.Trim().Length > 0)
{
complainQuery += "and (resolvedcomplains.Remarks like '%" + remarksFilter + "%')";
}
complainQuery += " and ((resolvedcomplains.ComplainDate >= DATE_FORMAT('" + fromDate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')) and (resolvedcomplains.ComplainDate <= DATE_FORMAT('" + toDate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')))";
complainQuery += " and ((resolvedcomplains.ResolvedDate >= DATE_FORMAT('" + resolverFromdate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')) and (resolvedcomplains.ResolvedDate <= DATE_FORMAT('" + resolverTodate.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",
"resolvedcomplains.ComplainDate",
"menucomplainType.Value", "resolvedcomplains.ResolvedBy",
"resolvedcomplains.ResolvedDate", "resolvedcomplains.ActionTaken", "resolvedcomplains.Remarks"
});
complainQuery += " order by "+tableDBColumns[sortColumnIndex]+" "+sortOrder+" ";
complainQuery+=" limit "+skipSize+", "+takeSize+";";
complainQuery+=" SELECT FOUND_ROWS();";
complainQuery += " SELECT count(*) from resolvedcomplains;";
complainQuery += "select distinct phoneuserpersonalinfo.BANumber from phoneuserpersonalinfo;select distinct menusrank.Value from menusrank;select distinct menucomplainType.Value from menucomplainType; select distinct resolvedcomplains.ResolvedBy from resolvedcomplains where resolvedcomplains.ResolvedBy!='';";
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<TelephoneComplain> aList = new List<TelephoneComplain>();
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
TelephoneComplain aTelephoneComplain = new TelephoneComplain();
aTelephoneComplain.BANumber = dataRow["BANumber"].ToString();
aTelephoneComplain.Name = dataRow["FullName"].ToString();
aTelephoneComplain.ComplainType = dataRow["ComplainType"].ToString();
aTelephoneComplain.NewPhoneNumber = dataRow["PhoneNumber"].ToString();
aTelephoneComplain.Rank = dataRow["Rank"].ToString();
aTelephoneComplain.Description = dataRow["Description"].ToString();
aTelephoneComplain.ComplainDate = dataRow["ComplainDate"].ToString();
aTelephoneComplain.ComplainId = dataRow["Id"].ToString();
// aTelephoneComplain.Status = dataRow["Status"].ToString();
aTelephoneComplain.ResolveBy = dataRow["ResolvedBy"].ToString();
aTelephoneComplain.ResolvedDate = dataRow["ResolvedDate"].ToString();
aTelephoneComplain.ActionTaken = dataRow["ActionTaken"].ToString();
aTelephoneComplain.Remarks = dataRow["Remarks"].ToString();
aList.Add(aTelephoneComplain);
}
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> ComplainTypeList = new List<string>();
foreach (DataRow dataRow in ds.Tables[5].Rows)
{
ComplainTypeList.Add(dataRow["Value"].ToString());
}
List<string> ResolverList = new List<string>();
foreach (DataRow dataRow in ds.Tables[6].Rows)
{
ResolverList.Add(dataRow["ResolvedBy"].ToString());
}
FilteredLists aFilteredComplains = new FilteredLists();
aFilteredComplains.aComplainList = aList;
aFilteredComplains.FilteredSize = filteredCount;
aFilteredComplains.TotalSize = lTotalCount;
aFilteredComplains.BaNumberList = BANumberList;
aFilteredComplains.RankList = RankList;
aFilteredComplains.ComplainTypeList = ComplainTypeList;
aFilteredComplains.ResolverList = ResolverList;
return aFilteredComplains;
}
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 static FilteredLists GetFilteredComplaneList(string sSearch,
string banumberFilter, string phoneFilter,
string nameFilter, string rankFilter, string complainFilter, DateTime fromDate, DateTime toDate, int skipSize, int takeSize, string sortOrder, int sortColumnIndex)
{
string complainQuery =
"select SQL_CALC_FOUND_ROWS complains.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menucomplainType.Value as ComplainType," +
" allphoneinfo.PhoneNumber,menusrank.Value as Rank,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 ";
if (sSearch.Trim().Length > 0)
{
complainQuery += "and ((phoneuserpersonalinfo.BANumber like '%" + sSearch + "%')" +
"or (menucomplainType.Value like '%" + sSearch + "%')" +
"or (phoneuserpersonalinfo.FullName like '%" + sSearch + "%')" +
"or (allphoneinfo.PhoneNumber like '%" + sSearch + "%')" +
"or (menusrank.Value like '%" + sSearch + "%')";
}
if (banumberFilter.Trim().Length > 0)
{
complainQuery += "and (phoneuserpersonalinfo.BANumber = '" + banumberFilter + "')";
}
if (complainFilter.Trim().Length > 0)
{
complainQuery += "and (menucomplainType.Value = '" + complainFilter + "')";
}
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 + "')";
}
complainQuery += " and ((complains.ComplainDate >= DATE_FORMAT('" + fromDate.ToString("yyyy-MM-dd HH:mm:ss") + "','%Y-%m-%d %H:%i:%s')) and (complains.ComplainDate <= 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",
"complains.ComplainDate",
"menucomplainType.Value"
});
complainQuery += " order by " + tableDBColumns[sortColumnIndex] + " " + sortOrder + " ";
complainQuery += " limit " + skipSize + ", " + takeSize + ";";
complainQuery += " SELECT FOUND_ROWS();";
complainQuery += " SELECT count(*) from complains;";
complainQuery += "select distinct phoneuserpersonalinfo.BANumber from phoneuserpersonalinfo;select distinct menusrank.Value from menusrank;select distinct menucomplainType.Value from menucomplainType;";
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<TelephoneComplain> aList = new List<TelephoneComplain>();
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
TelephoneComplain aTelephoneComplain = new TelephoneComplain();
aTelephoneComplain.BANumber = dataRow["BANumber"].ToString();
aTelephoneComplain.Name = dataRow["FullName"].ToString();
aTelephoneComplain.ComplainType = dataRow["ComplainType"].ToString();
aTelephoneComplain.NewPhoneNumber = dataRow["PhoneNumber"].ToString();
aTelephoneComplain.Rank = dataRow["Rank"].ToString();
aTelephoneComplain.ComplainDate = dataRow["ComplainDate"].ToString();
aTelephoneComplain.ComplainId = dataRow["Id"].ToString();
// aTelephoneComplain.Status = dataRow["Status"].ToString();
aList.Add(aTelephoneComplain);
}
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> ComplainTypeList = new List<string>();
foreach (DataRow dataRow in ds.Tables[5].Rows)
{
ComplainTypeList.Add(dataRow["Value"].ToString());
}
FilteredLists aFilteredComplains = new FilteredLists();
aFilteredComplains.aComplainList = aList;
aFilteredComplains.FilteredSize = filteredCount;
aFilteredComplains.TotalSize = lTotalCount;
aFilteredComplains.BaNumberList = BANumberList;
aFilteredComplains.RankList = RankList;
aFilteredComplains.ComplainTypeList = ComplainTypeList;
//List<TelephoneComplain> searchedComplains;
//sSearch = sSearch.Trim();
//if ((sSearch == ""))
//{
// searchedComplains = complanList;
//}
//else
//{
// searchedComplains =
// complanList.Where(c => c.BANumber.ToLower().Trim().Contains(sSearch.ToLower())
// || c.ComplainType.Trim().ToLower().Contains(sSearch.ToLower()) ||
// c.Name.Trim().ToLower().Contains(sSearch.ToLower()) ||
// c.NewPhoneNumber.Trim().ToLower().Contains(sSearch.ToLower()) ||
// c.ComplainType.Trim().ToLower().Contains(sSearch.ToLower())).ToList();
//}
//var filteredCompanies = searchedComplains
// .Where(c => (banumberFilter == "" || c.BANumber.Trim().ToLower() == banumberFilter.Trim().ToLower())
// &&
// (phoneFilter == "" || c.NewPhoneNumber.Trim().ToLower().Contains(phoneFilter.Trim()))
// &&
// (nameFilter == "" || c.Name.ToLower().Contains(nameFilter.Trim().ToLower()))
// &&
// (rankFilter == "" || c.Rank.Trim().ToLower() == rankFilter.Trim().ToLower())
// &&
// (complainFilter == "" || c.ComplainType.Trim().ToLower() == complainFilter.Trim().ToLower())
// &&
// (fromDate == DateTime.MinValue || fromDate < Convert.ToDateTime(c.ComplainDate))
// &&
// (toDate == DateTime.MaxValue || Convert.ToDateTime(c.ComplainDate) < toDate)
// );
//List<TelephoneComplain> filteredComplaneList = filteredCompanies.ToList();
return aFilteredComplains;
}