// -----------------------------------------------------
// List Documents for a client
// -----------------------------------------------------
public void ListClient(int clientUID)
{
this.documentList = new List <Document>();
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString = string.Format(
" SELECT " +
Document.SQLDocumentConcat("DOC") +
" FROM [Document] DOC " +
" WHERE [SourceCode] = 'CLIENT' " +
" AND [FKClientUID] = {0} " +
" AND [IsVoid] <> 'Y' " +
" ORDER BY PARENTUID ASC, SequenceNumber ASC ",
clientUID);
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Document _Document = new Document();
Document.LoadDocumentFromReader(_Document, "DOC", reader);
this.documentList.Add(_Document);
}
}
}
}
}
// -----------------------------------------------------
// List Documents
// -----------------------------------------------------
private void List(string Condition)
{
this.documentList = new List <Document>();
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString = string.Format(
" SELECT " +
Document.SQLDocumentConcat("DOC") +
" FROM [Document] DOC " +
" WHERE DOC.[SourceCode] = 'FCM' " +
" AND DOC.[IsVoid] = 'N' " +
Condition +
" ORDER BY PARENTUID ASC, SequenceNumber ASC "
);
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Document _Document = new Document();
Document.LoadDocumentFromReader(_Document, "DOC", reader);
this.documentList.Add(_Document);
}
}
}
}
}
// -----------------------------------------------------
// List Documents for a Document Set
// -----------------------------------------------------
public void ListDocSet(int documentSetUID)
{
this.documentList = new List <Document>();
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString = string.Format(
" SELECT " +
Document.SQLDocumentConcat("DOC") +
" ,[LNK].[FKParentDocumentUID] " +
" ,[LNK].[FKParentDocumentSetUID] " +
" ,[LNK].[SequenceNumber] " +
" FROM [Document] DOC " +
" ,[DocumentSetDocument] LNK" +
" WHERE " +
" [LNK].[FKDocumentUID] = [DOC].[UID] " +
" AND [DOC].[SourceCode] = 'FCM' " +
" AND [LNK].[IsVoid] = 'N' " +
" AND [DOC].[IsVoid] = 'N' " +
" AND [LNK].[FKDocumentSetUID] = {0} " +
" ORDER BY [LNK].FKParentDocumentUID ASC, [LNK].SequenceNumber ",
documentSetUID
);
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Document _Document = new Document();
Document.LoadDocumentFromReader(_Document, "DOC", reader);
// This is necessary because when the list comes from DocumentSet, the parent may change
//
_Document.ParentUID = Convert.ToInt32(reader["FKParentDocumentUID"].ToString());
this.documentList.Add(_Document);
}
}
}
}
}
/// <summary>
/// Get Document Details
/// </summary>
/// <returns></returns>
public bool Read(bool includeVoid = false)
{
//
// EA SQL database
//
bool ret = false;
string commandString = "";
string sincludeVoid = " AND DOC.IsVoid = 'N' ";
if (includeVoid)
{
sincludeVoid = " ";
}
if (this.UID > 0)
{
commandString = string.Format(
" SELECT " +
Document.SQLDocumentConcat("DOC") +
" FROM [Document] DOC" +
" WHERE " +
" DOC.UID = {0} " +
sincludeVoid,
UID);
}
else
{
commandString = string.Format(
" SELECT " +
Document.SQLDocumentConcat("DOC") +
" FROM [Document] DOC " +
" WHERE " +
" DOC.IsVoid = 'N' " +
" AND DOC.CUID = '{0}' ",
CUID);
}
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
try
{
LoadDocumentFromReader(this, "DOC", reader);
ret = true;
}
catch (Exception)
{
CUID = "";
UID = 0;
this.Name = "Not found UID " + this.UID.ToString("0000") + " CUID: " + this.CUID;
this.FileName = "Not found UID " + this.UID.ToString("0000") + " CUID: " + this.CUID;
this.RecordType = "";
}
}
}
}
return(ret);
}
public static DocumentSetDocumentLinkList ListRelatedDocuments(int documentSetUID, int documentUID, string type)
{
DocumentSetDocumentLinkList ret = new DocumentSetDocumentLinkList();
ret.documentSetDocumentLinkList = new List <DocumentSetDocumentLink>();
string linktype = "";
if (type == "ALL" || string.IsNullOrEmpty(type))
{
// do nothing
}
else
{
linktype = " AND DSDL.LinkType = '" + type + "'";
}
using (var connection = new SqlConnection(ConnString.ConnectionString))
{
var commandString = string.Format
(
"SELECT DSDL.[UID] DSDLUID " +
" ,DSDL.[FKParentDocumentUID] DSDLFKParentDocumentUID " +
" ,DSDL.[FKChildDocumentUID] DSDLFKChildDocumentUID " +
" ,DSDL.[LinkType] DSDLLinkType " +
" ,DSDL.[IsVoid] DSDLIsVoid " +
" ," +
Document.SQLDocumentConcat("DOCUMENTCHILD") +
" ," +
Document.SQLDocumentConcat("DOCUMENTPARENT") +
" ," +
DocumentSetDocument.SQLDocumentConcat("DSDCHILD") +
" ," +
DocumentSetDocument.SQLDocumentConcat("DSDPARENT") +
" " +
" FROM [management].[dbo].[DocumentSetDocumentLink] DSDL " +
" ,[management].[dbo].[DocumentSetDocument] DSDCHILD " +
" ,[management].[dbo].[DocumentSetDocument] DSDPARENT " +
" ,[management].[dbo].[Document] DOCUMENTCHILD " +
" ,[management].[dbo].[Document] DOCUMENTPARENT " +
" WHERE " +
" DSDL.[IsVoid] = 'N' " +
" AND DSDL.[FKParentDocumentUID] = {0} " +
linktype +
" AND DSDL.FKDocumentSetUID = {1} " +
" AND DSDL.FKChildDocumentUID = DSDCHILD.UID " +
" AND DSDL.FKParentDocumentUID = DSDPARENT.UID " +
" AND DSDCHILD.FKDocumentUID = DOCUMENTCHILD.UID " +
" AND DSDPARENT.FKDocumentUID = DOCUMENTPARENT.UID "
, documentUID
, documentSetUID
);
using (var command = new SqlCommand(
commandString, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DocumentSetDocumentLink _Document = new DocumentSetDocumentLink();
_Document.documentChild = new Document();
_Document.documentParent = new Document();
_Document.documentSetDocumentChild = new DocumentSetDocument();
_Document.documentSetDocumentParent = new DocumentSetDocument();
// Link information
//
_Document.FKChildDocumentUID = Convert.ToInt32(reader["DSDLFKChildDocumentUID"].ToString());
_Document.FKParentDocumentUID = Convert.ToInt32(reader["DSDLFKParentDocumentUID"].ToString());
_Document.LinkType = reader["DSDLLinkType"].ToString();
_Document.documentParent.UID = Convert.ToInt32(reader["DOCUMENTPARENTUID"].ToString());
_Document.documentParent.SimpleFileName = reader["DOCUMENTPARENTUID"].ToString();
_Document.documentParent.CUID = reader["DOCUMENTPARENTSimpleFileName"].ToString();
_Document.documentParent.Name = reader["DOCUMENTPARENTName"].ToString();
_Document.documentChild.UID = Convert.ToInt32(reader["DOCUMENTCHILDUID"].ToString());
_Document.documentChild.CUID = reader["DOCUMENTCHILDSimpleFileName"].ToString();
_Document.documentChild.Name = reader["DOCUMENTCHILDName"].ToString();
_Document.documentChild.SequenceNumber = Convert.ToInt32(reader["DOCUMENTCHILDSequenceNumber"].ToString());
_Document.documentChild.IssueNumber = Convert.ToInt32(reader["DOCUMENTCHILDIssueNumber"].ToString());
_Document.documentChild.Location = reader["DOCUMENTCHILDLocation"].ToString();
_Document.documentChild.Comments = reader["DOCUMENTCHILDComments"].ToString();
_Document.documentChild.SourceCode = reader["DOCUMENTCHILDSourceCode"].ToString();
_Document.documentChild.FileName = reader["DOCUMENTCHILDFileName"].ToString();
_Document.documentChild.SimpleFileName = reader["DOCUMENTCHILDSimpleFileName"].ToString();
_Document.documentChild.FKClientUID = Convert.ToInt32(reader["DOCUMENTCHILDFKClientUID"].ToString());
_Document.documentChild.ParentUID = Convert.ToInt32(reader["DOCUMENTCHILDParentUID"].ToString());
_Document.documentChild.RecordType = reader["DOCUMENTCHILDRecordType"].ToString();
_Document.documentChild.IsProjectPlan = reader["DOCUMENTCHILDIsProjectPlan"].ToString();
_Document.documentChild.DocumentType = reader["DOCUMENTCHILDDocumentType"].ToString();
_Document.documentSetDocumentChild.UID = Convert.ToInt32(reader["DSDCHILDUID"].ToString());
_Document.documentSetDocumentChild.FKDocumentUID = Convert.ToInt32(reader["DSDCHILDFKDocumentUID"].ToString());
_Document.documentSetDocumentChild.FKDocumentSetUID = Convert.ToInt32(reader["DSDCHILDFKDocumentSetUID"].ToString());
_Document.documentSetDocumentChild.Location = reader["DSDCHILDLocation"].ToString();
_Document.documentSetDocumentChild.StartDate = Convert.ToDateTime(reader["DSDCHILDStartDate"].ToString());
_Document.documentSetDocumentChild.EndDate = Convert.ToDateTime(reader["DSDCHILDEndDate"].ToString());
_Document.documentSetDocumentChild.FKParentDocumentUID = Convert.ToInt32(reader["DSDCHILDFKParentDocumentUID"].ToString());
_Document.documentSetDocumentChild.SequenceNumber = Convert.ToInt32(reader["DSDCHILDSequenceNumber"].ToString());
_Document.documentSetDocumentChild.FKParentDocumentSetUID = Convert.ToInt32(reader["DSDCHILDFKParentDocumentSetUID"].ToString());
ret.documentSetDocumentLinkList.Add(_Document);
}
}
}
}
return(ret);
}