public DataTable GetAllData(bool isConfig, string URL)
{
DataTable ReportDataTable = new DataTable();
try
{
//5/18/2015 NS modified for VSPLUS-1661
string SqlQuery = "SELECT [ID],[Name],[Category],[Description],[PageURL]+'?M=" + URL +
"' AS PageURL,[ImageURL],[ConfiguratorOnly] FROM ReportItems t1,SelectedFeatures sf,FeatureReports fr " +
"WHERE fr.FeatureID=sf.FeatureID AND fr.ReportID=t1.ID AND isworking='True' " +
" ORDER BY Category,Name";
//2/14/2014 NS modified
//string SqlQuery = "select [ID],[Name],[Category],[Description],[PageURL]+'?M=" + URL +
// "' as PageURL,[ImageURL],[ConfiguratorOnly] from ReportItems where isworking='True' " +
// (isConfig == false ? " and ConfiguratorOnly='False'" : "") +
// " order by Category,Name";
//string SqlQuery = "select t1.[ID] ReportID,ISNULL(t2.[ID],0) ID,[Name],[Category],[Description],[PageURL]+'?M=" + URL + "' as PageURL," +
// "[ImageURL],[ConfiguratorOnly],ISNULL(Frequency,'') Frequency,ISNULL(Days,'') Days,ISNULL(SpecificDay,0) SpecificDay," +
// "ISNULL(SendTo,'') SendTo,ISNULL(CopyTo,'') CopyTo,ISNULL(BlindCopyTo,'') BlindCopyTo,ISNULL(Title,'') Title," +
// "ISNULL(Body,'') Body,ISNULL(FileFormat,'') FileFormat from ReportItems t1 left outer join ScheduledReports t2 on t1.ID=t2.ReportID " +
// "where isworking='True' " + (isConfig == false ? " and ConfiguratorOnly='False'" : "");
ReportDataTable = objAdaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(ReportDataTable);
}
public DataTable GetData(string SName)
{
DataTable dt = new DataTable();
try
{
if (SName != "" && SName != null)
{
string SqlQuery = "Select Status, DominoServerTasks,Name, DominoVersion as Server,Category,TypeandName from Status where Type='Notes Database'and StatusCode in ('Issue','Maintenance','Not Responding','OK')and StatusCode is not null and [Name]='" + SName + "'";
dt = objAdaptor.FetchData(SqlQuery);
}
else
{
string SqlQuery = "Select Status, DominoServerTasks,Name, DominoVersion as Server,Category,TypeandName from Status where Type='Notes Database'and StatusCode in ('Issue','Maintenance','Not Responding','OK')and StatusCode is not null";
dt = objAdaptor.FetchData(SqlQuery);
}
}
catch (Exception)
{
throw;
}
return(dt);
}
public bool UpdateLogo(Company cobj)
{
bool update = false;
DataTable dt;
string sqlQuery;
try
{
//5/23/2013 NS modified
sqlQuery = "SELECT * FROM Company";
dt = objAdaptor.FetchData(sqlQuery);
if (dt.Rows.Count > 0)
{
sqlQuery = "UPDATE Company SET LogoPath = @LogoPath,CompanyName = @CompanyName";
SqlCommand cmd = new SqlCommand(sqlQuery);
cmd.Parameters.AddWithValue("@LogoPath", (object)cobj.LogoPath ?? DBNull.Value);
cmd.Parameters.AddWithValue("@CompanyName", (object)cobj.CompanyName ?? DBNull.Value);
update = objAdaptor.ExecuteNonQuerywithcmd(cmd);
}
else
{
sqlQuery = "INSERT INTO Company VALUES (@CompanyName,@LogoPath)";
SqlCommand cmd = new SqlCommand(sqlQuery);
cmd.Parameters.AddWithValue("@LogoPath", (object)cobj.LogoPath ?? DBNull.Value);
cmd.Parameters.AddWithValue("@CompanyName", (object)cobj.CompanyName ?? DBNull.Value);
update = objAdaptor.ExecuteNonQuerywithcmd(cmd);
}
//update= objAdaptor.ExecuteNonQuery(sqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(update);
}
public DataTable GetSharePointServerDetails()
{
DataTable dt = new DataTable();
try
{
//string SqlQuery = "select * from SharePointServerHealth";
//7/22/2014 NS added OperatingSystem and DominoVersion to the list
//10/9/2014 NS modified the query by adding Status to the QueryString
//1/20/2015 NS modified the query by adding Status sort for VSPLUS-1377
string SqlQuery = "select * from " +
"(Select Type,Name,Status,CASE WHEN Status='Not Responding' THEN 0 WHEN Status='Issue' THEN 1 ELSE 2 END StatusSort, " +
"ResponseTime,ContentMemory,OperatingSystem,DominoVersion, " +
"(CPU*100) as CPU, " +
"'''../images/icons/SharePoint_2013.jpg''' as imgsource, " +
"'Sharepointdetailspage.aspx?Name=' + Name + '&Type=' + Type + '&Status=' + Status + '&LastDate='+CONVERT(VARCHAR,LastUpdate,101) + ' ' + substring(convert(varchar(20), LastUpdate, 9), 13, 5) + ' ' + substring(convert(varchar(30), LastUpdate, 9), 25, 2) as redirectto, " +
"isnull(Memory,0)*100 as MemoryPercent " +
"from [VitalSigns].[dbo].[Status] st) as sts " +
"inner join " +
"(select * from (select * from ( " +
"select s.ID,s.ServerName,roles.rolename,sa.CPU_Threshold as CPUThreshold,sa.MemThreshold,sa.ResponseTime as ResponseThreshold, spf.Farm FROM Servers AS s " +
"LEFT OUTER JOIN ServerAttributes sa on sa.ServerID=s.ID " +
"INNER JOIN " +
"ServerTypes AS st ON st.ID = s.ServerTypeID " +
"LEFT OUTER JOIN " +
"(select ss.serverId, rm.id as roleId, rm.rolename from ServerRoles ss, RolesMaster rm " +
" where rm.id = ss.RoleId) as roles on s.ID = roles.serverId " +
"inner join (Select Main.ServerId, Left(Main.Students,Len(Main.Students)-1) As Farm From " +
"(Select distinct ST2.ServerId, (Select ST1.Farm + ', ' AS [text()] " +
"From dbo.SharePointFarms ST1 " +
"Where ST1.ServerId = ST2.ServerId " +
"ORDER BY ST1.ServerId " +
"For XML PATH ('') ) [Students] " +
"From dbo.SharePointFarms ST2) [Main]) as spf on spf.ServerId=s.ID where st.ServerType='SharePoint') " +
" as exchangeservers " +
") as pr " +
") as exg " +
"on exg.ServerName = sts.name and sts.Type = 'SharePoint' " +
"order by StatusSort";
dt = adaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(dt);
}
public DataTable GetReports(int ReportID)
{
DataTable dt = new DataTable();
string SqlQuery = "";
try
{
//2/12/2016 NS modified for VSPLUS-2588
if (ReportID != 0)
{
SqlQuery = "SELECT sr.ID,ReportID,CASE WHEN Frequency = 'Daily' THEN Frequency WHEN Frequency = 'Weekly' THEN " +
"Frequency + ' on ' + [Days] ELSE Frequency + ' on the ' + CAST(SpecificDay as varchar(2)) + ' day' END FrequencyDisp, " +
"Frequency,Days,SpecificDay,SendTo,CopyTo,BlindCopyTo,Title,Body,FileFormat,Name " +
"FROM [ScheduledReports] sr INNER JOIN [ReportItems] ri ON sr.ReportID=ri.ID " +
"WHERE sr.ID=" + ReportID.ToString();
dt = objAdaptor.FetchData(SqlQuery);
}
else if (ReportID != -1)
{
SqlQuery = "SELECT sr.ID,ReportID,CASE WHEN Frequency = 'Daily' THEN Frequency WHEN Frequency = 'Weekly' THEN " +
"Frequency + ' on ' + [Days] ELSE Frequency + ' on the ' + CAST(SpecificDay as varchar(2)) + ' day' END FrequencyDisp, " +
"Frequency,Days,SpecificDay,SendTo,CopyTo,BlindCopyTo,Title,Body,FileFormat,Name " +
"FROM [ScheduledReports] sr INNER JOIN [ReportItems] ri ON sr.ReportID=ri.ID ";
dt = objAdaptor.FetchData(SqlQuery);
}
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable GetPwrData()
{
DataTable Pwrsheltab = new DataTable();
try
{
string SqlQuery = "SELECT Replace(ScriptName,''', '''') as ScriptName,Replace(ScriptDetails,''', '''') as ScriptDetails,Replace(Category,''', '''') as Category,Replace(Description,''', '''') as Description FROM PowershellScripts";
Pwrsheltab = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(Pwrsheltab);
}
public DataTable ResponseThresholdForGeneric(string ServerName)
{
DataTable dt = new DataTable();
try
{
string que = "select ServerID,ResponseThreshold from Servers s inner join WindowsServers ds on ds.ServerID= s.ID where s.ServerName='" + ServerName + "'";
dt = adaptor.FetchData(que);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable SetGraphForDiskSpace(string serverName)
{
DataTable dt = new DataTable();
try
{
string strQuerry = "SELECT [DiskName], [DiskFree], [DiskSize]-[DiskFree] As DiskUsed FROM [DiskSpace] where [ServerName]='" + serverName + "' order by [DiskName]";
dt = adaptor.FetchData(strQuerry);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable Fillcombobox()
{
DataTable WebsphereCellnames = new DataTable();
try
{
string SqlQuery = "Select CellID,CellName from WebsphereCell";
WebsphereCellnames = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(WebsphereCellnames);
}
public DataTable GetLicensedetails()
{
DataTable BsTable = new DataTable();
try
{
string sqlQuery = "Select * from License";
BsTable = objAdaptor.FetchData(sqlQuery);
}
catch (Exception)
{
throw;
}
return(BsTable);
}
public DataTable SetGrid()
{
DataTable dt = new DataTable();
try
{
string strQuerry = "SELECT distinct UserName,DeviceName,device_type, DeviceID,OS_Type FROM [Traveler_Devices] where deviceid not in(select deviceid from MobileUserThreshold)";
dt = objAdaptor.FetchData(strQuerry);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable DistinctServerTypes()
{
DataTable serverTypes = new DataTable();
try
{
string SqlQuery = "select Distinct [Type] from Status";
serverTypes = objAdaptor.FetchData(SqlQuery);
}
catch (Exception)
{
throw;
}
return(serverTypes);
}
public DataTable getStatusAndLastScanDate(string Servername, string type)
{
DataTable dt = new DataTable();
try
{
string Sql = "Select Status, LastUpdate from Status where Type='" + type + "' and Name='" + Servername + "'";
dt = adaptor.FetchData(Sql);
}
catch (Exception)
{
throw;
}
return(dt);
}
public DataTable SetGridForLyncDisk(string ServerName)
{
DataTable dt = new DataTable();
try
{
string strQuerry = "select DSP.ID,DSP.ServerName,DSP.DiskName,DSP.DiskSize,DSP.DiskFree,DSP.PercentFree,DSP.PercentUtilization,DSP.AverageQueueLength,DSP.Updated,CAST(DDS.Threshold as varchar(50)) + ' ' + CASE WHEN DDS.ThresholdType='Percent' THEN '%' ELSE DDS.ThresholdType END ThresholdDisp,DDS.Threshold,DDS.ThresholdType, DDS.DiskName from DiskSpace DSP LEFT OUTER JOIN DiskSettings DDS " +
"ON (DDS.ServerID =(select ID from Servers where ServerName= DSP.ServerName) and DSP.ServerName='" + ServerName + "') and (DSP.DiskName=DDS.DiskName or DDS.DiskName='AllDisks' ) Where DSP.ServerName='" + ServerName + "' ORDER BY DSP.serverName,DSP.diskname";
dt = adaptor.FetchData(strQuerry);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable getfillgrid()
{
DataTable dt = new DataTable();
try
{
string s = "select *,servers.ID as SID,ServerName as Name,(Select Location from Locations where ID = LocationID) as Location,Description,IPAddress as Address from BlackBerryServers right join servers on BlackBerryServers.ServerID=servers.ID where servertypeID=(select ID from ServerTypes where ServerType='BES')";
//string s = "select * from BlackBerryServers";
dt = objAdaptor.FetchData(s);
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public DataTable GetMailServiceData()
{
DataTable StatusDataTable = new DataTable();
try
{
string SqlQuery = "select Status,Name,TypeANDName,Location,Category,Details from Status where StatusCode in ('Issue','Maintenance','Not Responding','OK') and Type='Mail'";
StatusDataTable = objAdaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(StatusDataTable);
}
/// <summary>
/// Get all Data from FeaturesDAL
/// </summary>
public DataTable GetAllData()
{
DataTable FeaturesDataTable = new DataTable();
Features ReturnLOCbject = new Features();
try
{
string SqlQuery = "SELECT * FROM [Features] ORDER BY Name";
FeaturesDataTable = objAdaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(FeaturesDataTable);
}
public DataTable GetAllData()
{
DataTable dt = new DataTable();
try
{
string SqlQuery = "SELECT Sr.ID,Sr.ServerName,S.ServerType,L.Location,Sr.LocationID,ls.ScanInterval," +
"ls.Enabled,ls.Category FROM Servers Sr INNER JOIN ServerTypes S on Sr.ServerTypeID=S.ID " +
"INNER JOIN Locations L ON Sr.LocationID=L.ID LEFT OUTER JOIN LyncServers ls ON sr.ID=ls.serverid " +
"WHERE S.ServerType='Skype for Business' ";
dt = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
/// <summary>
/// Get all Data from UserSecurityQuestions
/// </summary>
public DataTable Getimagepath(string loginname)
{
DataTable usersecuritydt = new DataTable();
Users retobj = new Users();
try
{
string sqlquery = "SELECT * from Users where LoginName ='" + loginname + "'";
usersecuritydt = objAdaptor.FetchData(sqlquery);
}
catch
{
}
finally
{
}
return(usersecuritydt);
}
/// <summary>
/// Get all Data from NotesDatabase
/// </summary>
/// <returns></returns>
public DataTable GetAllData()
{
DataTable NotesDatabasesDataTable = new DataTable();
try
{
string SqlQuery = "SELECT *,(select servername from servers where id=NotesDatabases.serverid) as servernames FROM NotesDatabases";
NotesDatabasesDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(NotesDatabasesDataTable);
}
public DataTable GetWebsphereCellStatus()
{
DataTable dt = new DataTable();
string SqlQuery = "";
try
{
SqlQuery = "select * from WebsphereCellStats";
dt = adaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(dt);
}
public DataTable GetAllData()
{
DataTable MSServersDataTable = new DataTable();
try
{
string SqlQuery = "select Sr.ID,Sr.ServerName,S.ServerType,L.Location,Sr.LocationID,sa.ScanInterval,sa.Enabled,sr.ipaddress,sa.category from Servers Sr inner join ServerTypes S on Sr.ServerTypeID=S.ID " +
" inner join Locations L on Sr.LocationID =L.ID left outer join ServerAttributes sa on sr.ID=sa.serverid where S.ServerType='Windows' ";
MSServersDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(MSServersDataTable);
}
public DataTable GetFeedback()
{
// string logopath;
DataTable Feedbacks = new DataTable();
try
{
string sqlQuery = "Select ID,Subject,Type,Message,Status from Feedback where status='pending'";
Feedbacks = objAdaptor.FetchData(sqlQuery);
//5/23/2013 NS modified
}
catch (Exception ex)
{
throw ex;
}
return(Feedbacks);
}
public DataTable GetAllData()
{
DataTable MSServersDataTable = new DataTable();
try
{
string SqlQuery = "select Sr.ID,Sr.ServerName,S.ServerType,L.Location,Sr.LocationID,sa.ScanInterval,sa.Enabled,sr.ipaddress,sa.category,wc.CellName, wn.NodeName,ws.CellID,ws.NodeID from Servers Sr inner join ServerTypes S on Sr.ServerTypeID=S.ID inner Join WebsphereServer ws on ws.ServerName=Sr.ServerName Inner join WebsphereCell wc on wc.CellID=ws.CellID inner join WebsphereNode wn on wn.NodeID=ws.NodeID inner join Locations L on Sr.LocationID =L.ID left outer join ServerAttributes sa on sr.ID=sa.serverid where S.ServerType='WebSphere'";
//string SqlQuery = "select Sr.ID,Sr.ServerName,S.ServerType,L.Location,Sr.LocationID,sa.ScanInterval,sa.Enabled,sr.ipaddress,sa.category,wn.NodeName,wc.CellName from Servers Sr inner join ServerTypes S on Sr.ServerTypeID=S.ID " +
// " inner join Locations L on Sr.LocationID =L.ID left outer join ServerAttributes sa on sr.ID=sa.serverid inner join WebsphereServer ws on sr.ID=ws.serverid inner join WebsphereNode wn on wn.NodeID=ws.NodeID inner join WebsphereCell wc on wc.CellID=ws.CellID where S.ServerType='WebSphere' ";
MSServersDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(MSServersDataTable);
}
public DataTable GetDagSettings(DagSettings objdag)
{
DataTable MSServersDataTable = new DataTable();
try
{
string SqlQuery = "select dg.ServerID,dg.ReplyQThreshold,dg.CopyQThreshold,(select sr.ServerName +'-'+ l.Location from servers sr inner join Locations l on l.ID=sr.LocationID where sr.ID=dg.PrimaryConnection) as PrimaryConnection,(select sr.ServerName +'-'+ l.Location from servers sr inner join Locations l on l.ID=sr.LocationID where sr.ID=dg.BackupConnection) as BackupConnection from DagSettings dg where ServerID=" + objdag.ServerID + "";
// select (ServerName +'-'+ Location) as Primaryname,dg.PrimaryConnection from servers sr inner join DagSettings dg on dg.PrimaryConnection=sr.ID inner join Locations l on l.ID=sr.LocationID where ServerID=34
//select (ServerName +'-'+ Location) as Backupname,dg.BackupConnection from servers sr inner join DagSettings dg on dg.BackupConnection=sr.ID inner join Locations l on l.ID=sr.LocationID where ServerID=34
MSServersDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(MSServersDataTable);
}
/// <summary>
/// Get all Data from DominoCluster
/// </summary>
public Settings GetData(Settings StObject)
{
Settings ReturnStObject = new Settings();
DataTable SettingsDataTable = new DataTable();
try
{
string SqlQuery = "SELECT svalue FROM Settings WHERE sname='" + StObject.sname + "'";
SettingsDataTable = objAdaptor.FetchData(SqlQuery);
ReturnStObject.svalue = SettingsDataTable.Rows[0]["svalue"].ToString();
}
catch
{
}
finally
{
}
return(ReturnStObject);
}
/// <summary>
/// Get all Data from LocationsDAL
/// </summary>
public DataTable GetAllData()
{
DataTable LogFileDataTable = new DataTable();
LogFile ReturnLOCbject = new LogFile();
try
{
string SqlQuery = "SELECT * FROM [LogFile]";
LogFileDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(LogFileDataTable);
}
/// <summary>
/// Get all Data from DominoCustomStatValues
/// </summary>
/// <returns></returns>
public DataTable GetAllData()
{
DataTable DominoCustomStatDataTable = new DataTable();
DominoCustomStatValues ReturnDSObject = new DominoCustomStatValues();
try
{
string SqlQuery = "SELECT ID,ServerName,StatName,ThresholdValue,GreaterThanORLessThan,TimesInARow,ConsoleCommand" +
" FROM DominoCustomStatValues";
DominoCustomStatDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(DominoCustomStatDataTable);
}
public DataTable GetAllData()
{
DataTable MSServersDataTable = new DataTable();
try
{
//3/21/2014 NS modified the query - need to add locationid
//string SqlQuery = "select Sr.ID,Sr.ServerName,S.ServerType,L.Location,sa.ScanInterval,sa.Enabled,sr.ipaddress,sa.category from Servers Sr inner join ServerTypes S on Sr.ServerTypeID=S.ID " +
// " inner join Locations L on Sr.LocationID =L.ID left outer join ServerAttributes sa on sr.ID=sa.serverid where S.ServerType='Exchange' ";
string SqlQuery = "select Sr.ID,Sr.ServerName,S.ServerType,L.Location,Sr.LocationID,sa.ScanInterval,sa.Enabled,sr.ipaddress,sa.category from Servers Sr inner join ServerTypes S on Sr.ServerTypeID=S.ID " +
" inner join Locations L on Sr.LocationID =L.ID left outer join ServerAttributes sa on sr.ID=sa.serverid where S.ServerType='SharePoint' ";
MSServersDataTable = objAdaptor.FetchData(SqlQuery);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(MSServersDataTable);
}
/// <summary>
/// Get all Data from UserSecurityQuestions
/// </summary>
public DataTable GetAllData(Users u)
{
DataTable UserStartupURLs = new DataTable();
ServerTypes ReturnUQobject = new ServerTypes();
try
{
string SqlQuery = "SELECT * FROM [UsersStartupURLs] ";
string whereClause = "";
if (u.Isdashboard)
{
whereClause += " WHERE isDashboard=1 ";
}
if (u.IsConfigurator)
{
if (whereClause != "")
{
whereClause += " OR IsConfigurator=1 ";
}
else
{
whereClause += " WHERE IsConfigurator=1 ";
}
}
if (u.Isconsolecomm)
{
if (whereClause != "")
{
whereClause += " OR Isconsolecomm=1 ";
}
else
{
whereClause += " WHERE Isconsolecomm=1 ";
}
}
if (whereClause != "")
{
SqlQuery = SqlQuery + whereClause;
}
UserStartupURLs = objAdaptor.FetchData(SqlQuery);
}
catch
{
}
finally
{
}
return(UserStartupURLs);
}
