private void btn_ProjectDocAdd_click(object sender, EventArgs e)
{
using (frm_ProjectDocAdd addProjectDoc = new frm_ProjectDocAdd(projectNumber, ds_Projects, documentType))
{
addProjectDoc.ShowDialog();
try
{
ds_Projects.Tables["tblProjectDocument"].Clear();
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_Projects, "tblProjectDocument",
$"select * from [dbo].[tblProjectDocument]" +
$"where [ValidTo] is null");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to refresh tblProjectDocument" + Environment.NewLine + Environment.NewLine + ex.Message);
}
setProjectDocHistory(projectNumber, ds_Projects, documentType);
}
}
/// <summary>
/// Gets user tables for project DataSet, can be used with ds.dt.Merge() to add to existing DataSet
/// </summary>
/// <returns>DataSet containing just project related User tables</returns>
public DataSet getUserDataSet()
{
DataSet ds_prj_usr = new DataSet("Users");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_prj_usr, "tblUser",
$"select *, [LastName] + ', ' + [FirstName] as FullName " +
$"from [dbo].[tblUser] " +
$"where [ValidTo] is null " +
$"order by [LastName], [FirstName], [UserID]");
// Copies made of tblUser so that the can be referenced by LeadApplicant and
// PI fields of tblProjects via DataRelations without additional SQL Server hits
DataTable leadApp = ds_prj_usr.Tables["tblUser"].Copy();
leadApp.TableName = "tlkLeadApplicant";
ds_prj_usr.Tables.Add(leadApp);
DataTable PI = ds_prj_usr.Tables["tblUser"].Copy();
PI.TableName = "tlkPI";
ds_prj_usr.Tables.Add(PI);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_prj_usr DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
return(ds_prj_usr);
}
public DataSet GetAssetsHistoryDataSet()
{
DataSet ds_io = new DataSet("AssetsHistory");
SqlConnection conn = new SqlConnection
{
ConnectionString = SQL_Stuff.conString,
Credential = SQL_Stuff.credential
};
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_io, "tblTransferRequests",
@"SELECT RequestID, Project, VreNumber, RequestType, RequestedBy, RequesterNotes, ReviewedBy, ReviewDate, ReviewNotes
FROM dbo.tblTransferRequests
ORDER BY ReviewDate DESC");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblAssetsChangeLog",
@"SELECT ChangeID, RequestID, FileID, TransferMethod, TransferFrom, TransferTo, DsaReviewed, ChangeAccepted, RejectionNotes
FROM dbo.tblAssetsChangeLog");
SQL_Stuff.getDataTable(conn, null, ds_io, "tlkTransferRequestTypes",
"SELECT RequestTypeID, RequestTypeLabel FROM dbo.tlkTransferRequestTypes");
SQL_Stuff.getDataTable(conn, null, ds_io, "tlkFileTransferMethods",
"SELECT MethodID, MethodLabel FROM dbo.tlkFileTransferMethods");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblProject",
@"SELECT * FROM dbo.tblProject WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(conn, null, ds_io, "tblProjectPlatformInfo",
@"SELECT * FROM dbo.tblProjectPlatformInfo WHERE ValidTo IS NULL");
GetAssetGroups(ds_io, conn, null);
GetAssetsRegister(ds_io, conn, null);
GetDsasData(ds_io, conn, null);
GetUsersData(ds_io, conn, null);
}
return(ds_io);
}
public DataSet GetDsaData()
{
DataSet ds = new DataSet("DSAs");
SqlConnection conn = new SqlConnection
{
ConnectionString = SQL_Stuff.conString,
Credential = SQL_Stuff.credential
};
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds, "tblDsas",
@"SELECT DsaID, DocumentID, DataOwner, AmendmentOf, DsaName, DsaFileLoc, StartDate, ExpiryDate,
DataDestructionDate, AgreementOwnerEmail, DSPT, ISO27001, RequiresEncryption,
NoRemoteAccess, ValidFrom, ValidTo, Deprecated
FROM dbo.tblDsas
WHERE ValidTo IS NULL
ORDER BY DocumentID");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsaNotes",
@"SELECT dnID, Dsa, Note, Created, CreatedBy FROM dbo.tblDsaNotes");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsasProjects",
@"SELECT dpID, DocumentID, Project, ValidFrom, ValidTo FROM dbo.tblDsasProjects WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(conn, null, ds, "tblDsaDataOwners",
@"SELECT doID, DataOwnerName, RebrandOf, DataOwnerEmail FROM dbo.tblDsaDataOwners");
SQL_Stuff.getDataTable(conn, null, ds, "tblProject",
@"SELECT * FROM dbo.tblProject WHERE ValidTo IS NULL");
}
return(ds);
}
public void GetDsasData(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsas",
@"SELECT DsaID, DocumentID, DataOwner, AmendmentOf, DsaName, DsaFileLoc, StartDate, ExpiryDate,
DataDestructionDate, AgreementOwnerEmail, DSPT, ISO27001, RequiresEncryption,
NoRemoteAccess, ValidFrom, ValidTo, Deprecated
FROM dbo.tblDsas
WHERE ValidTo IS NULL
ORDER BY DocumentID");
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsasProjects", "SELECT * FROM dbo.tblDsasProjects WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(cn, tr, ds, "tblDsaDataOwners", "SELECT doID, DataOwnerName FROM dbo.tblDsaDataOwners");
}
/// <summary>
/// Method to return a DataSet (ds_usr) with content of SQL table dbo.tblUser, dbo.tblUserNotes
/// and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns></returns>
public DataSet getUsersDataSet()
{
DataSet ds_usr = new DataSet("Users");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
//use method from GetDB to create, fill and add DataTables to class DataSet
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUser",
$"select *, [LastName] + ', ' + [FirstName] as FullName " +
$"from [dbo].[tblUser] " +
$"where [ValidTo] is null " +
$"order by [LastName], [FirstName], [UserID]");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tlkUserStatus",
$"select * from [dbo].[tlkUserStatus] ");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tlkTitle",
$"select * from [dbo].[tlkTitle] ");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUserNotes",
$"select * from [dbo].[tblUserNotes]");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblUserProject",
$"select * from [dbo].[tblUserProject] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_usr, "tblProjects",
$"select * from [dbo].[tblProject] " +
$"where [ValidTo] is null " +
$"order by [ProjectNumber], [pID]");
//create a DataRelations to join dimensions to measures
ds_usr.Relations.Add("User_UserStatus"
, ds_usr.Tables["tlkUserStatus"].Columns["StatusID"] //parent
, ds_usr.Tables["tblUser"].Columns["Status"]); //child
ds_usr.Relations.Add("User_Title"
, ds_usr.Tables["tlkTitle"].Columns["TitleID"]
, ds_usr.Tables["tblUser"].Columns["Title"]);
ds_usr.Relations.Add("UserProject_Project"
, ds_usr.Tables["tblProjects"].Columns["ProjectNumber"]
, ds_usr.Tables["tblUserProject"].Columns["ProjectNumber"]);
}
}
catch (Exception e)
{
MessageBox.Show("Failed to populate ds_usr DataSet" + Environment.NewLine + Environment.NewLine + e);
//throw;
}
//return DataSet (ds_usr) as the output of this method
return(ds_usr);
}
/// <summary>
/// Method to return a DataSet (ds_prj) with content of SQL table dbo.tblProjects, dbo.tblProjectNotes,
/// dbo.tblUsers and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns>
/// Full DataSet containing tables of all currently valid project records and asociated user/lookup tables.
/// </returns>
public DataSet getKristalDataSet()
{
DataSet ds_krs = new DataSet("Kristal");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblKristal",
$"select * from [dbo].[tblKristal] " +
$"where [ValidTo] is null " +
$"order by [KristalRef], [KristalID]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tlkGrantStage",
$"select * from [dbo].[tlkGrantStage] order by StageNumber asc");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblProjectKristal",
$"select * from [dbo].[tblProjectKristal] " +
$"where [ValidTo] is null " +
$"order by [KristalRef], [ProjectNumber]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "vw_AllProjects",
$"select * from [dbo].[vw_AllProjects]" +
$"order by [ProjectNumber]");
SQL_Stuff.getDataTable(conn, null, ds_krs, "tblKristalNotes",
$"select * from [dbo].[tblKristalNotes]");
ds_krs.Relations.Add("Kristal_GrantStage"
, ds_krs.Tables["tlkGrantStage"].Columns["GrantStageID"]
, ds_krs.Tables["tblKristal"].Columns["GrantStageID"]);
ds_krs.Relations.Add("Kristal_Project"
, ds_krs.Tables["vw_AllProjects"].Columns["ProjectNumber"]
, ds_krs.Tables["tblProjectKristal"].Columns["ProjectNumber"]);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_krs DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
return(ds_krs);
}
/// <summary>
/// Method to return a DataSet (ds_prj) with content of SQL table dbo.tblProjects, dbo.tblProjectNotes,
/// dbo.tblUsers and other related lookup tables.
/// Creates DataRelations so that dimension tables can be linked to values in the measures table.
/// </summary>
/// <returns>
/// Full DataSet containing tables of all currently valid project records and asociated user/lookup tables.
/// </returns>
public DataSet getProjectsDataSet()
{
DataSet ds_prj = new DataSet("Projects");
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjects",
$"select * from [dbo].[tblProject] " +
$"where [ValidTo] is null " +
$"order by [ProjectNumber], [pID]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkStage",
$"select * from [dbo].[tlkStage] order by StageNumber asc");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkClassification",
$"select * from [dbo].[tlkClassification] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkRAG",
$"select * from [dbo].[tlkRAG] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkFaculty",
$"select * from [dbo].[tlkFaculty] ");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectNotes",
$"select * from [dbo].[tblProjectNotes] " +
$"order by [ProjectNumber], [Created] desc");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectPlatformInfo",
$"Select * from [dbo].[tblProjectPlatformInfo] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkPlatformInfo",
$"select * from [dbo].[tlkPlatformInfo]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblUserProject",
$"select * from [dbo].[tblUserProject] " +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblProjectDocument",
$"select * from [dbo].[tblProjectDocument]" +
$"where [ValidTo] is null");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tlkDocuments",
$"select * from [dbo].[tlkDocuments]");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblDocsAccepted",
$"select tbl.ProjectNumber " +
$" , tlk.DocumentID " +
$" , tlk.DocumentDescription " +
$" , max(tbl.Accepted) as maxAccepted " +
$"from[dbo].[tlkDocuments] tlk " +
$" left join[dbo].[tblProjectDocument] tbl " +
$" on tlk.DocumentID = tbl.DocumentType " +
$"where tlk.ValidTo is null and tbl.ValidTo is null " +
$" and tbl.ProjectNumber is not null " +
$"group by tbl.ProjectNumber " +
$" , tlk.DocumentID " +
$" , tlk.DocumentDescription " +
$"order by tbl.ProjectNumber, tlk.DocumentID");
SQL_Stuff.getDataTable(conn, null, ds_prj, "tblDatHours",
$"select ProjectNumber " +
$" , sum(case when datediff(m, Created, getdate()) = 0 " +
$" then[DatHours] else 0 end) as ThisMonth " +
$" , sum(case when datediff(m, Created, getdate()) between 1 and 6 " +
$" then[DatHours] else 0 end) as Last6Month " +
$" , sum(case when datediff(m, Created, getdate()) between 1 and 12 " +
$" then[DatHours] else 0 end) as Last12Month " +
$" , sum([DatHours]) as AllTime " +
$"from [dbo].[tblProjectDatTime] " +
$"group by ProjectNumber ");
// get the user tables needed to link to project details and merge with project dataset
DataSet ds_prj_usr = getUserDataSet();
ds_prj.Merge(ds_prj_usr);
}
ds_prj.Relations.Add("Project_Stage"
, ds_prj.Tables["tlkStage"].Columns["StageID"] //parent
, ds_prj.Tables["tblProjects"].Columns["Stage"]); //child
ds_prj.Relations.Add("Project_Classification"
, ds_prj.Tables["tlkClassification"].Columns["classificationID"]
, ds_prj.Tables["tblProjects"].Columns["Classification"]);
ds_prj.Relations.Add("Project_DATRAG"
, ds_prj.Tables["tlkRAG"].Columns["ragID"]
, ds_prj.Tables["tblProjects"].Columns["DATRAG"]);
ds_prj.Relations.Add("Project_Faculty"
, ds_prj.Tables["tlkFaculty"].Columns["facultyID"]
, ds_prj.Tables["tblProjects"].Columns["Faculty"]);
ds_prj.Relations.Add("ProjectPlatformInfo_PlatformInfo"
, ds_prj.Tables["tlkPlatformInfo"].Columns["PlatformInfoID"]
, ds_prj.Tables["tblProjectPlatformInfo"].Columns["PlatformInfoID"]);
ds_prj.Relations.Add("ProjectDocument_Document"
, ds_prj.Tables["tlkDocuments"].Columns["DocumentID"]
, ds_prj.Tables["tblProjectDocument"].Columns["DocumentType"]);
ds_prj = addProjectUserDataRelations(ds_prj);
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_prj DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
//return DataSet (ds_prj) as the output of this method
return(ds_prj);
}
public void GetUsersData(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblUser", "SELECT * FROM dbo.tblUser WHERE ValidTo IS NULL");
SQL_Stuff.getDataTable(cn, tr, ds, "tblUserProject", "SELECT * FROM dbo.tblUserProject WHERE ValidTo IS NULL");
}
public void GetAssetsRegister(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblAssetsRegister",
@"SELECT FileID, Project, DataFileName, VreFilePath, DataRepoFilePath, AssetID
FROM dbo.tblAssetsRegister");
}
public void GetAssetGroups(DataSet ds, SqlConnection cn, SqlTransaction tr)
{
SQL_Stuff.getDataTable(cn, tr, ds, "tblAssetGroups", "SELECT * FROM dbo.tblAssetGroups");
}
private void setProjectUserDocs(string pNumber, string pName)
{
lbl_ProjectNumber.Text = pNumber;
lbl_ProjectName.Text = pName;
DataSet ds_ProjectUserDocs = new DataSet();
try
{
//use the central connection string from the SQL_Stuff class
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
SQL_Stuff.getDataTable(conn, null, ds_ProjectUserDocs, "tblProjectUserDocs",
$"exec dbo.sp_UserDocStatusByProject '{pNumber}'");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to populate ds_ProjectUserDocs DataSet" + Environment.NewLine + Environment.NewLine + ex.Message);
}
DataTable dt_dgv_ProjectUserDocs = new DataTable();
dt_dgv_ProjectUserDocs.Columns.Add("User Status");
dt_dgv_ProjectUserDocs.Columns.Add("Name");
dt_dgv_ProjectUserDocs.Columns.Add("SEED Confidentiality Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("IRC User Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("LASER User Agreement");
dt_dgv_ProjectUserDocs.Columns.Add("Information Security Essentials");
dt_dgv_ProjectUserDocs.Columns.Add("Information Security Advanced");
dt_dgv_ProjectUserDocs.Columns.Add("SAFE Researcher Training");
DataRow row;
foreach (DataRow upRow in ds_ProjectUserDocs.Tables["tblProjectUserDocs"].Rows)
{
row = dt_dgv_ProjectUserDocs.NewRow();
row["User Status"] = upRow["User Status"];
row["Name"] = upRow["Name"];
row["SEED Confidentiality Agreement"] = upRow["SEED Confidentiality Agreement"];
row["IRC User Agreement"] = upRow["IRC User Agreement"];
row["LASER User Agreement"] = upRow["LASER User Agreement"];
row["Information Security Essentials"] = upRow["Information Security Essentials"];
row["Information Security Advanced"] = upRow["Information Security Advanced"];
row["SAFE Researcher Training"] = upRow["SAFE Researcher Training"];
dt_dgv_ProjectUserDocs.Rows.Add(row);
}
dt_dgv_ProjectUserDocs.DefaultView.Sort = "Name Asc";
dt_dgv_ProjectUserDocs = dt_dgv_ProjectUserDocs.DefaultView.ToTable();
dgv_ProjectUserDocs.DataSource = dt_dgv_ProjectUserDocs;
dgv_ProjectUserDocs.Columns["User Status"].Width = 50;
dgv_ProjectUserDocs.Columns["Name"].Width = 150;
dgv_ProjectUserDocs.Columns["SEED Confidentiality Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["IRC User Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["LASER User Agreement"].Width = 100;
dgv_ProjectUserDocs.Columns["Information Security Essentials"].Width = 100;
dgv_ProjectUserDocs.Columns["Information Security Advanced"].Width = 100;
dgv_ProjectUserDocs.Columns["SAFE Researcher Training"].Width = 100;
}