public static bool ApiAuthenticate(Dictionary<string, string> sessionData, HttpSessionStateBase Session, System.Web.Http.Controllers.HttpActionContext actionContext)
{
string code = EncrDecrAction.Encrypt(
EncrDecrAction.Encrypt(EncrDecrAction.Encrypt(Session["UserId"].ToString(), true), true)
+ EncrDecrAction.Encrypt(EncrDecrAction.Encrypt(Session["UserRoleId"].ToString(), true), true)
+ EncrDecrAction.Encrypt(EncrDecrAction.Encrypt(Session["UserName"].ToString(), true), true)
+ EncrDecrAction.Encrypt(EncrDecrAction.Encrypt(Session["RoleName"].ToString(), true), true)
+ EncrDecrAction.Encrypt(EncrDecrAction.Encrypt(Session["ParentRoleName"].ToString(), true), true), true);
if (code == Session["SRES"].ToString())
{
UrlHelper urlHelper = new UrlHelper(HttpContext.Current.Request.RequestContext);
var routeValueDictionary = urlHelper.RequestContext.RouteData.Values;
string controller = routeValueDictionary["controller"].ToString();
string action = actionContext.Request.Method.ToString();
int argument = actionContext.Request.RequestUri.Segments.Count() - 3;
string query = "select * from appviews where LOWER(Controller) = LOWER(@Controller) and LOWER(Action) = LOWER(@Action) and " + sessionData["RoleName"] + "= 1 and Argument=@Argument and ControllerType='api'";
Hashtable conditionTable = new Hashtable();
conditionTable["Controller"] = controller;
conditionTable["Action"] = action;
conditionTable["Argument"] = argument;
DBGateway aDbGateway = new DBGateway();
DataSet aDataSet = aDbGateway.Select(query, conditionTable);
if (aDataSet.Tables[0].Rows.Count > 0)
{
return true;
}
}
return false;
}
public static void DeleteComplain(string id)
{
string query =
"delete from complains where id=" + id + ";";
DBGateway aGateway = new DBGateway();
string deleteResult = aGateway.Delete(query);
}
public static void AddEquipmentDetails(string type, string amount, string description)
{
DBGateway aGateway = new DBGateway();
string insertQuery =
"INSERT INTO `signalappdb`.`equipemntdescription` (`TypeId`, `Amount`, `Description`) VALUES (" + type + ", " +
amount + ", '" + description + "');";
aGateway.Insert(insertQuery);
}
public static void DeleteEquipment(string id)
{
string deleteQuery;
deleteQuery = "DELETE FROM `signalappdb`.`equipemntdescription` WHERE `Id`=" + id + ";";
DBGateway aGateway = new DBGateway();
string updateResult = aGateway.Delete(deleteQuery);
}
public static void AddTechnicalDetails(string type, string amount, string description, string OnAirDate)
{
DBGateway aGateway = new DBGateway();
string insertQuery =
"INSERT INTO `signalappdb`.`technicaldescription` (`TypeId`, `Amount`, `Description`,`OnAirDate`) VALUES (" + type +
", " +
amount + ", '" + description + "','"+OnAirDate+"');";
aGateway.Insert(insertQuery);
}
public static List<Dictionary<string, string>> GetLocations()
{
List<Dictionary<string, string>> aList = new List<Dictionary<string, string>>();
DBGateway aGateway = new DBGateway();
DataSet aSet = aGateway.Select("select * from menustations;");
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
Dictionary<string, string> aData = new Dictionary<string, string>();
aData.Add("ID",dataRow["ID"].ToString());
aData.Add("Value", dataRow["Name"].ToString());
aList.Add(aData);
}
return aList;
}
public static List<Dictionary<string, string>> GetRanks()
{
List<Dictionary<string, string>> ranks = new List<Dictionary<string, string>>();
DBGateway gateway = new DBGateway("SignalSystemConnectionString");
string query = "select * from menusrank;";
DataSet aSet = gateway.Select(query);
foreach(DataRow aRow in aSet.Tables[0].Rows)
{
Dictionary<string, string> aDictionary = new Dictionary<string, string>();
aDictionary.Add("ID", aRow["id"].ToString());
aDictionary.Add("Value", aRow["Value"].ToString());
ranks.Add(aDictionary);
}
return ranks;
}
public static List<EquipmentDetails> GetSingleEquipmentDetails(string id)
{
string query =
"select equipemntdescription.id,equipemntdescription.typeid,equipemntdescription.amount,equipemntdescription.description,equipmnttype.typename from equipemntdescription,equipmnttype where equipemntdescription.typeid=equipmnttype.id and equipemntdescription.id=" +
id;
DBGateway aGateway=new DBGateway();
List<EquipmentDetails> aList = new List<EquipmentDetails>();
DataSet aDataSet = aGateway.Select(query);
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
EquipmentDetails aEquipmentDetails = new EquipmentDetails();
aEquipmentDetails.EquipmentId = dataRow["Id"].ToString();
aEquipmentDetails.TypeID = (int)dataRow["TypeID"];
aEquipmentDetails.Amount = (int)dataRow["Amount"];
aEquipmentDetails.Description = dataRow["Description"].ToString();
aEquipmentDetails.TypeName = dataRow["typename"].ToString();
aList.Add(aEquipmentDetails);
}
return aList;
}
public static List<MenuItem> GetMenuItemLists(HttpSessionStateBase Session)
{
Dictionary<string, string> sessionData = SessionHandler.GetSessionData(Session);
List<MenuItem> menu = new List<MenuItem>();
DBGateway aGateway = new DBGateway();
DataSet aDataSet = aGateway.Select("select * from appmenuitems, appviews where appmenuitems.AppViewId = appviews.Id and " + sessionData["RoleName"] + " = 1 order by menuorder asc, submenuorder asc");
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
MenuItem aMenuItem = new MenuItem();
aMenuItem.Id = dataRow["Id"].ToString();
aMenuItem.MenuName = dataRow["MenuName"].ToString();
aMenuItem.MenuParentName = dataRow["MenuParentName"].ToString();
aMenuItem.Controller = dataRow["Controller"].ToString();
aMenuItem.Action = dataRow["Action"].ToString();
menu.Add(aMenuItem);
}
return menu;
}
public static bool Authenticate(string userName, string userPassword, HttpSessionStateBase Session)
{
DBAuthentication authentication = new DBAuthentication(userName, userPassword);
bool result = authentication.IsValid();
if (result == false)
{
return result;
}
DBGateway aDbGateway = new DBGateway();
Hashtable conditionTable = new Hashtable();
string query = "select * from users,roles where users.UserName='******' and users.UserCredential='" + userPassword + "' and users.UserRoleId = roles.ID";
conditionTable["UserName"] = userName;
DataSet aDataSet = aDbGateway.Select(query, conditionTable);
aDataSet.Tables[0].Columns.Add("LogInValue");
aDataSet.Tables[0].Rows[0]["LogInValue"] = userPassword;
List<string> cols = new List<string>();
Dictionary<string,string> userData = new Dictionary<string, string>();
foreach (DataColumn column in aDataSet.Tables[0].Columns)
{
cols.Add(column.ColumnName);
}
foreach (DataRow row in aDataSet.Tables[0].Rows)
{
foreach (string col in cols)
{
userData.Add(col,row[col].ToString());
}
}
SessionHandler.SetSessionData(userData, Session);
return true;
}
public static List<TechnicalDetails> GetSingleTechnicalDetails(string id)
{
string query =
"select technicaldescription.id,technicaldescription.typeid,technicaldescription.amount,technicaldescription.description,technicaldescription.OnAirDate,equipmnttype.typename from technicaldescription,equipmnttype where technicaldescription.typeid=equipmnttype.id and technicaldescription.id=" +
id;
DBGateway aGateway = new DBGateway();
List<TechnicalDetails> aList = new List<TechnicalDetails>();
DataSet aDataSet = aGateway.Select(query);
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
TechnicalDetails aTechnicalDetails = new TechnicalDetails();
aTechnicalDetails.TechnicalId = dataRow["Id"].ToString();
aTechnicalDetails.TypeID = (int) dataRow["TypeID"];
aTechnicalDetails.Amount = (int) dataRow["Amount"];
aTechnicalDetails.Description = dataRow["Description"].ToString();
aTechnicalDetails.OnAirDate = Convert.ToDateTime(dataRow["OnAirDate"].ToString());
// aData.MailArrivalDate = Convert.ToDateTime(dataRow["DateArrival"].ToString());
aTechnicalDetails.TypeName = dataRow["typename"].ToString();
aList.Add(aTechnicalDetails);
}
return aList;
}
public static void UpdateTechnicalDetails(TechnicalDetails aTechnicalDetails, string OAD)
{
DateTime dt = Convert.ToDateTime(aTechnicalDetails.OnAirDate);
// var date = DateTime.Parse(aTechnicalDetails.OnAirDate);
string updateQuery;
updateQuery = "UPDATE `signalappdb`.`technicaldescription` SET `TypeId`=" + aTechnicalDetails.TypeID +
", `Amount`=" +
aTechnicalDetails.Amount + ",`OnAirDate`='" + OAD + "', `Description`='" + aTechnicalDetails.Description +
"' WHERE `Id`=" +
aTechnicalDetails.TechnicalId + ";";
DBGateway aGateway = new DBGateway();
string updateResult = aGateway.Update(updateQuery);
}
public static List<EquipmentType> GetEquipmentTypes()
{
List<EquipmentType> equipmentTypes = new List<EquipmentType>();
DBGateway aGateway = new DBGateway();
string queryType = "select * from equipmnttype";
DataSet ctDataSet = aGateway.Select(queryType);
foreach (DataRow dataRow in ctDataSet.Tables[0].Rows)
{
TelephoneComplainType aType = new TelephoneComplainType();
EquipmentType aEquipmentType = new EquipmentType();
aEquipmentType.TypeID = (int)dataRow["Id"];
aEquipmentType.Name = dataRow["TypeName"].ToString();
equipmentTypes.Add(aEquipmentType);
}
return equipmentTypes;
}
public static List<EquipmentType> GEtEquipmentTypes()
{
DBGateway aGateway = new DBGateway();
//List<EquipmentType> aList=new List<EquipmentType>();
string query1 = "select * from equipmnttype";
List<EquipmentType> aList = new List<EquipmentType>();
DataSet aDataSet = aGateway.Select(query1);
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
EquipmentType aTypeList = new EquipmentType();
aTypeList.TypeID = (int)dataRow["Id"];
aTypeList.Name = dataRow["TypeName"].ToString();
aList.Add(aTypeList);
}
return aList;
}
public static void AddEquimpnetType(string typeName)
{
DBGateway aGateway = new DBGateway();
string insertType = "INSERT INTO `signalappdb`.`equipmnttype` (`TypeName`) VALUES ('" + typeName + "');";
aGateway.Insert(insertType);
}
public List<EquipmentDetails> GetEquipmentList(string equipmentType,string description)
{
DBGateway aGateway=new DBGateway();
string query = "select equipemntdescription.id,equipemntdescription.typeid,equipemntdescription.amount," +
"equipemntdescription.description,equipmnttype.typename from equipemntdescription," +
"equipmnttype where equipemntdescription.typeid=equipmnttype.id";
if (equipmentType.Trim().Length != 0)
{
query += " and equipmnttype.TypeName like '%"+equipmentType+"%' ";
}
if (description.Trim().Length != 0)
{
query += " and equipemntdescription.description like '%" + description + "%' ";
}
query += ";";
List<EquipmentDetails> aList = new List<EquipmentDetails>();
DataSet aDataSet = aGateway.Select(query);
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
EquipmentDetails aEquipmentDetails = new EquipmentDetails();
aEquipmentDetails.EquipmentId = dataRow["Id"].ToString();
aEquipmentDetails.TypeID = (int)dataRow["TypeID"];
aEquipmentDetails.Amount = (int)dataRow["Amount"];
aEquipmentDetails.Description = dataRow["Description"].ToString();
aEquipmentDetails.TypeName = dataRow["typename"].ToString();
aList.Add(aEquipmentDetails);
}
return aList;
}
public static void UpdateDeletePhoneUserData(string status, string id, string remarks)
{
DBGateway aGateway = new DBGateway();
string query;
if (status == "0") //delete phone
{
query = "delete from telephoneusers where id=" + id + ";";
aGateway.Delete(query);
}
else if (status == "1") //connect phone
{
query = "UPDATE `signalappdb`.`telephoneusers` SET `Status`='Connected',`ConnectedDate`='" +
DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE `Id`=" + id + ";";
aGateway.Update(query);
string resolver = UtilityLibrary.GetUserId();
string resolveQuery =
"INSERT INTO `signalappdb`.`complains` (`Description`, `Status`, `MenuComplainTypeId`, `TelephoneUserId`, `ComplainDate`, `ResolvedDate`, `Remarks`, `ActionTaken`, `ResolvedBy`) VALUES ('New Connection Activation', 'Resolved', 5, " +
id + ", '" + DateTime.Now + "', 'DateTime.Now', '" + remarks + "', 'Connection Provided', '" + resolver +
"');";
aGateway.Insert(resolveQuery);
}
}
public List<UserRole> GetRoleLevels(int roleID)
{
DBGateway aGateway=new DBGateway();
string query = "select * from roles;";
DataSet aDataSet = aGateway.Select(query);
List<UserRole> userRoles = new List<UserRole>();
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
UserRole aUserRole = new UserRole();
aUserRole.Id = Convert.ToInt32(dataRow["ID"].ToString());
aUserRole.RoleName = dataRow["RoleName"].ToString();
aUserRole.ParentRoleName = dataRow["ParentRoleName"].ToString();
userRoles.Add(aUserRole);
}
List<UserRole> userRolesFinal =
userRoles.Where(c => c.ParentRoleName == c.RoleName).ToList().Select(c =>
{
c.Level = 0;
return c;
}
).ToList();
bool continueParse = true;
List<UserRole> tempList = userRolesFinal;
int j = 1;
while (continueParse)
{
List<UserRole> childListLevel = new List<UserRole>();
foreach (UserRole aRole in tempList)
{
List<UserRole> childList = userRoles.Where(c => c.ParentRoleName == aRole.RoleName && !userRolesFinal.Any(p2 => p2.Id == c.Id)).ToList();
childList = childList.Select(c =>
{
c.Level = j;
return c;
}).ToList();
if (childList.Count > 0)
{
childListLevel.AddRange(childList);
}
}
if (childListLevel.Count > 0)
{
// userRolesFinal.AddRange(childListLevel);
userRolesFinal = userRolesFinal.Concat(childListLevel).ToList();
tempList.Clear();
tempList.AddRange(childListLevel);
childListLevel.Clear();
j++;
}
else
{
continueParse = false;
}
}
int userLevel = userRolesFinal.Where(c => c.Id == roleID).First().Level;
List<UserRole> removeRoles = userRolesFinal.Where(s => s.Level <= userLevel).ToList();
foreach (UserRole aRemovableRole in removeRoles)
{
if (aRemovableRole.Id != roleID)
{
userRolesFinal.Remove(aRemovableRole);
}
}
return userRolesFinal;
}
public static void DisconnectPhoneOperation(string id, DBGateway aGateway, string remarks)
{
string query;
query = "UPDATE `signalappdb`.`telephoneusers` SET `Status`='Connected',`DisconnectedDate`='" +
DateTime.Now.ToString("yyyy-MM-dd") + "' WHERE `Id`=" + id + ";";
aGateway.Update(query);
query =
"INSERT INTO deletedtelephoneusers (deletedtelephoneusers.BANumber,deletedtelephoneusers.Name,deletedtelephoneusers.RankId,deletedtelephoneusers.`Status`,deletedtelephoneusers.NewPhoneNumber,deletedtelephoneusers.Address,deletedtelephoneusers.Gender,deletedtelephoneusers.ConnectedDate,deletedtelephoneusers.DisconnectedDate) SELECT telephoneusers.BANumber,telephoneusers.Name,telephoneusers.RankId,telephoneusers.`Status`,telephoneusers.NewPhoneNumber,telephoneusers.Address,telephoneusers.Gender,telephoneusers.ConnectedDate,telephoneusers.DisconnectedDate FROM telephoneusers where telephoneusers.id=" +
id + "";
aGateway.Insert(query);
query = "delete from complains where TelephoneUserId=" + id;
aGateway.Delete(query);
query = "delete from telephoneusers where id=" + id + ";";
aGateway.Delete(query);
string resolver = UtilityLibrary.GetUserId();
string resolveQuery =
"INSERT INTO `signalappdb`.`complains` (`Description`, `Status`, `MenuComplainTypeId`, `TelephoneUserId`, `ComplainDate`, `ResolvedDate`, `Remarks`, `ActionTaken`, `ResolvedBy`) VALUES ('Connection Colsed', 'Resolved', 6, " +
id + ", '" + DateTime.Now + "', 'DateTime.Now', '" + remarks + "', 'Connection Disconnected', '" + resolver +
"');";
// aGateway.Insert(resolveQuery);
}
public List<TechnicalDetails> GetEquipmentList(string equipmentType, string description)
{
DBGateway aGateway = new DBGateway();
string query = "select technicaldescription.id,technicaldescription.typeid,technicaldescription.amount," +
"technicaldescription.description,technicaldescription.OnAirDate,equipmnttype.typename from technicaldescription," +
"equipmnttype where technicaldescription.typeid=equipmnttype.id";
if (equipmentType.Trim().Length != 0)
{
query += " and equipmnttype.TypeName like '%" + equipmentType + "%' ";
}
if (description.Trim().Length != 0)
{
query += " and technicaldescription.description like '%" + description + "%' ";
}
query += ";";
List<TechnicalDetails> aList = new List<TechnicalDetails>();
DataSet aDataSet = aGateway.Select(query);
foreach (DataRow dataRow in aDataSet.Tables[0].Rows)
{
TechnicalDetails aTechnicalDetails = new TechnicalDetails();
aTechnicalDetails.TechnicalId = dataRow["Id"].ToString();
aTechnicalDetails.TypeID = (int) dataRow["TypeID"];
aTechnicalDetails.Amount = (int) dataRow["Amount"];
aTechnicalDetails.Description = dataRow["Description"].ToString();
aTechnicalDetails.OnAirDate = Convert.ToDateTime(dataRow["OnAirDate"].ToString());
aTechnicalDetails.TypeName = dataRow["typename"].ToString();
aList.Add(aTechnicalDetails);
}
return aList;
}
public static void UpdateEquipmentDetails(EquipmentDetails aEquipmentDetails)
{
string updateQuery;
updateQuery = "UPDATE `signalappdb`.`equipemntdescription` SET `TypeId`=" + aEquipmentDetails.TypeID + ", `Amount`=" +
aEquipmentDetails.Amount + ", `Description`='" + aEquipmentDetails.Description + "' WHERE `Id`=" +
aEquipmentDetails.EquipmentId + ";";
DBGateway aGateway = new DBGateway();
string updateResult = aGateway.Update(updateQuery);
}
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 List<TelephoneComplain> GetTelephoneComplain(string id,string type)
{
string queryResolve = "select resolvedcomplains.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menucomplainType.Value as ComplainType," +
" allphoneinfo.PhoneNumber,menusrank.Value as Rank,resolvedcomplains.Description,resolvedcomplains.ComplainDate,resolvedcomplains.Remarks,resolvedcomplains.ResolvedDate,resolvedcomplains.ResolvedBy" +
" 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 and resolvedcomplains.id=" +
id + ";";
string queryPending = "select complains.id,phoneuserpersonalinfo.BANumber,phoneuserpersonalinfo.FullName,menucomplainType.Value as ComplainType," +
" allphoneinfo.PhoneNumber,menusrank.Value as Rank,complains.Description,complains.ComplainDate,complains.Remarks,complains.ResolvedDate,complains.ResolvedBy" +
" 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 and complains.id=" +
id + ";";
List<TelephoneComplain> aTelephoneComplainList = new List<TelephoneComplain>();
DBGateway aGateway = new DBGateway();
DataSet aDataSet;
if (type == "resolved")
{
aDataSet=aGateway.Select(queryResolve);
}
else
{
aDataSet=aGateway.Select(queryPending);
}
foreach (DataRow dataRow in aDataSet.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.ActionTaken = dataRow["ActionTaken"].ToString();
aTelephoneComplain.ResolvedDate = dataRow["ResolvedDate"].ToString();
aTelephoneComplain.Remarks = dataRow["Remarks"].ToString();
aTelephoneComplain.ResolveBy = dataRow["ResolvedBy"].ToString();
aTelephoneComplainList.Add(aTelephoneComplain);
}
List<TelephoneComplainType> complainTypes = new List<TelephoneComplainType>();
string queryType = "select * from menucomplaintype";
DataSet ctDataSet = aGateway.Select(queryType);
foreach (DataRow dataRow in ctDataSet.Tables[0].Rows)
{
TelephoneComplainType aType = new TelephoneComplainType();
aType.TypeId = dataRow["Id"].ToString();
aType.TypeValue = dataRow["Value"].ToString();
complainTypes.Add(aType);
}
aTelephoneComplainList.ForEach(list => list.ProblemTypes = complainTypes);
return aTelephoneComplainList;
}
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;
}
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 static List<Dictionary<string, string>> GetTelephoneRequestTypes()
{
List<Dictionary<string, string>> menurequesttype = new List<Dictionary<string, string>>();
DBGateway gateway = new DBGateway("SignalSystemConnectionString");
string query = "select * from menurequesttype;";
DataSet aSet = gateway.Select(query);
foreach (DataRow aRow in aSet.Tables[0].Rows)
{
Dictionary<string, string> aDictionary = new Dictionary<string, string>();
aDictionary.Add("ID", aRow["id"].ToString());
aDictionary.Add("Value", aRow["Value"].ToString());
menurequesttype.Add(aDictionary);
}
return menurequesttype;
}
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 static void EditComplain(TelephoneComplain aTelephoneComplain, string updateQuery)
{
string complainStatus;
DBGateway aGateway = new DBGateway();
if (aTelephoneComplain.Status == "0")
{
complainStatus = "Pending";
updateQuery = "UPDATE `signalappdb`.`complains` SET `Description`='" + aTelephoneComplain.Description +
"', `Status`='" + complainStatus + "', `MenuComplainTypeId`=" +
aTelephoneComplain.ComplainType + " WHERE `Id`=" + aTelephoneComplain.ComplainId + ";";
string updateResult = aGateway.Update(updateQuery);
}
else if (aTelephoneComplain.Status == "1")
{
Telephone aTelephone = new Telephone();
string convertedDateText = aTelephone.DMYToMDY(aTelephoneComplain.ResolvedDate);
DateTime dt = DateTime.Parse(convertedDateText);
string userName = UtilityLibrary.GetUserId();
complainStatus = "Resolved";
updateQuery = "UPDATE `signalappdb`.`complains` SET `Description`='" + aTelephoneComplain.Description +
"', `Status`='" + complainStatus + "', `Remarks`='" + aTelephoneComplain.Remarks +
"', `MenuComplainTypeId`=" + aTelephoneComplain.ComplainType + ", `ResolvedDate`='" +
dt.ToString("yyyy-MM-dd HH:mm:ss") + "', `ActionTaken`='" + aTelephoneComplain.ActionTaken +
"', `ResolvedBy`='" + userName + "' WHERE `Id`=" + aTelephoneComplain.ComplainId + ";";
//string insertQuery = "INSERT INTO `signalappdb`.`resolvedcomplains` (`Description`, `Status`, `MenuComplainTypeId`, `TelephoneUserId`, `ComplainDate`, `ResolvedDate`, `Remarks`, `ActionTaken`, `ResolvedBy`, `AllPhoneInfoID`) VALUES ('"+aTelephoneComplain.Description+"', 'resolved', "+aTelephoneComplain.ProblemTypes+", (select ), '2015-12-17 23:07:26', '2015-12-19 23:07:26', 'o', 'dd', 'maruf', 10);";
string insertQuery = "INSERT INTO `signalappdb`.`resolvedcomplains` (`Description`, `Status`, `MenuComplainTypeId`, `TelephoneUserId`,`AllPhoneInfoID`,`ComplainDate`, `ResolvedDate`, `Remarks`, `ActionTaken`, `ResolvedBy`) select description,status, MenuComplainTypeId,TelephoneUserId,AllPhoneInfoID,ComplainDate,'" +dt.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + aTelephoneComplain.Remarks + "', '" + aTelephoneComplain.ActionTaken + "', '" + aTelephoneComplain.ResolveBy + "' from complains WHERE ID=" + aTelephoneComplain.ComplainId + ";";
int insertCount = aGateway.Insert(insertQuery);
string deleteQuery = "Delete From Complains where id=" + aTelephoneComplain.ComplainId + "";
string delMsg = aGateway.Delete(deleteQuery);
}
}