@[ListAllWorkflows]
§Name [All currently defined Workflows]

§Company [SolidWorks]

§Description
[This query will list all Workflows and the name of the first tranistion in each.]

§Version [1.1]

§Arguments
[
]

§Sql
[
SELECT W.Name as 'Workflow Name', W.Description as 'Workflow Description', T.Name as 'First Transition'
FROM Workflows W, Transitions T
WHERE W.InitialTransitionID = T.TransitionID And
      W.WorkflowID != 1                                  /* Only include real WF  */
]


/*******************                        ********************/


@[LoggedInUserGroups]
§Name [Groups that the currently logged-in user is a member of]

§Company [SolidWorks]

§Description
[This query will list all groups the current logged in user is a member of.]

§Version [1.1]

§Arguments
[
UserID	TheUserID[S] [Select user]
]

§Sql
[
SELECT U.Username as 'User Name', U.FullName as 'Full Name', G.Groupname as 'Group Name',
       G.Description as 'Description'
FROM Users U, Groups G, GroupMembers M
WHERE U.UserID = {TheUserID} AND
      M.UserID = {TheUserID} AND
	   M.GroupID = G.GroupID
]


/*******************                        ********************/


@[GroupMembers]
§Name [User details of selected group(s)]

§Company [SolidWorks]

§Description
[This query will list all members of the selected groups.]

§Version [1.1]

§Arguments
[
GroupID	TheGroupID[N] [Select group(s)]
]

§Sql
[
SELECT U.Username, G.Groupname, G.Description
FROM Users U, Groups G, GroupMembers M
WHERE {G.GroupID, TheGroupID, =, OR} AND
      G.GroupID = M.GroupID AND
      U.UserID = M.UserID
]


/*******************                        ********************/


@[FileDetails]
§Name [File details of all files in a project]

§Company [SolidWorks]

§Description
[This query will list file details of the latest version
of all files in the selected project.]

§Version [1.2]

§Arguments
[
  ProjectID pProjectID [1] [Select folder. E.g "$\Documents", or browse for folder.]
  String FileExtension [1] [Enter extension of files to list, e.g. "doc*. Use "%" for wildcard.]
]

§Sql
[
  SELECT P.Path + D.Filename as 'Document Name', D.LatestRevisionNo as 'Latest Version',
         R.Date as 'Latest Access Date', R.Comment as 'Version Comment', IsNull(S.Name, '') as 'Workflow State'
  FROM Documents D, Revisions R, DocumentsInProjects Dp, Projects P, Status S
  WHERE P.ProjectID = {pProjectID} And
        Dp.ProjectID = P.ProjectID AND
        Dp.DocumentID = D.DocumentID AND
        D.DocumentID = R.DocumentID AND
        D.Filename LIKE '%.' + {FileExtension} AND
        R.RevNr= D.LatestRevisionNo And
        D.CurrentStatusID = S.StatusID
]


/*******************                        ********************/


@[DocumentDataOfSelectedFiles]
§Name [DocumentData of the selected files]

§Company [SolidWorks]

§Description
[This query will list the document data of the selected files.]

§Version [1.2]

§Arguments
[
  FileID TheFileID[M][]
]

§Sql
[
  Declare @Variables Table( iIndex Int Identity(1,1),VariableID Int )
  Declare @DocumentData Table( VariableID Int, ValueText nvarchar(255), DocumentID Int )
  Declare @Documents Table (DocumentID Int )
 
  Insert Into @Documents( DocumentID )
  Select DocumentID
  From Documents
  Where {DocumentID, TheFileID, =, OR}

  Insert Into @Variables( VariableID )
  Select distinct top 5  V.VariableID
  From VariableValue V, Documents D
  Where V.DocumentID = D.DocumentID And
        V.ValueText is not null
  Order by VariableID
  
  Insert Into @DocumentData( VariableID, ValueText, DocumentID  ) 
  Select V.VariableID, Vv.ValueText, D.DocumentID
  From VariableValue Vv, Documents D, @Variables V
  Where Vv.DocumentID = D.DocumentID And
        Vv.VariableID = V.VariableID And
        Vv.ValueText is not null And
        Vv.RevisionNo = ( Select Max( Vv1.RevisionNo )
                          From VariableValue Vv1
                          Where Vv1.DocumentID = D.DocumentID And
                                Vv1.VariableID = Vv.VariableID And
                                Vv1.ValueText is not null ) And
        Vv.ConfigurationID = ( Select Max( Vv2.ConfigurationID )
                               From VariableValue Vv2
                               Where Vv2.DocumentID = D.DocumentID And
                                     Vv2.VariableID = Vv.VariableID And
                                     Vv2.RevisionNo = Vv.RevisionNo And
                                     Vv2.ValueText is not null )

Select Doc.Filename,
       IsNull( (Select Dd.ValueText
                From @DocumentData Dd
                Where Dd.DocumentID = D.DocumentID And
                      Dd.VariableID = (Select VariableID From @Variables Where iIndex = 1 ) ), '' ) as 'Variable Data 1',
       IsNull( (Select Dd.ValueText
                From @DocumentData Dd
                Where Dd.DocumentID = D.DocumentID And
                      Dd.VariableID = (Select VariableID From @Variables Where iIndex = 2 ) ), '' ) as 'Variable Data 2',
       IsNull( (Select Dd.ValueText
                From @DocumentData Dd
                Where Dd.DocumentID = D.DocumentID And
                      Dd.VariableID = (Select VariableID From @Variables Where iIndex = 3 ) ), '' ) as 'Variable Data 3',
       IsNull( (Select Dd.ValueText
                From @DocumentData Dd
                Where Dd.DocumentID = D.DocumentID And
                      Dd.VariableID = (Select VariableID From @Variables Where iIndex = 4 ) ), '' ) as 'Variable Data 4',
       IsNull( (Select Dd.ValueText
                From @DocumentData Dd
                Where Dd.DocumentID = D.DocumentID And
                      Dd.VariableID = (Select VariableID From @Variables Where iIndex = 5 ) ), '' ) as 'Variable Data 5'
From @Documents D, Documents Doc
Where D.DocumentID = Doc.DocumentID
  
]




/*******************                        ********************/



@[ListAllReferencedDocs]
§Name [Documents referenced by this document]

§Company [SolidWorks]

§Description
[The query lists all documents directly or indirectly referenced by the
selected document(s).]

§Version [1.2]

§Arguments
[
  ProjectID pProjectID [1] [Enter folder of assembly. E.g "$\Assy", or browse for folder.]
  String FileExtension [1] [Enter file extension of assembly. E.g. "iam" or "sldasm"]
]

§Sql
[ 
  Declare @XrefTable Table( iIndex Int, ParentID Int, DocumentID Int )
  Declare @iIndex Int
  Declare @ProjectID Int
  Declare @pcExt nvarchar(255)

  Set @pcExt= {FileExtension}
  Set @iIndex = 0
  Set @ProjectID = {pProjectID}
 
  Insert Into @XrefTable( iIndex, ParentID, DocumentID )
  Select Distinct  @iIndex, 0, Doc.DocumentID
  From Documents Doc, DocumentsInProjects Dp
  Where Dp.ProjectID = @ProjectID And
        Dp.DocumentID = Doc.DocumentID And
        Doc.Filename like '%.' + @pcExt
  
  While( @@RowCount > 0 )
  Begin
     Set @iIndex = @iIndex + 1 

     Insert Into @XrefTable( iIndex, ParentID, DocumentID )
     Select Distinct @iIndex, X.DocumentID, X.XRefDocument
     From @XrefTable r, XRefs X
     Where
        r.iIndex = (@iIndex - 1) And
        X.DocumentID = r.DocumentID
  End

  Select iIndex as 'Level',
         IsNull( (Select Filename From Documents Where DocumentID = X.ParentID), '') as 'Parent Name',
         (Select Filename From Documents Where DocumentID = X.DocumentID) as 'Part'
  From @XrefTable X
]



/*******************                        ********************/



@[DocumentsInWorkflowstate]
§Name [Documents in selected workflow state]

§Company [SolidWorks]

§Description
[This query lists all documents in a selected state and to which the user has access rights.]

§Version [1.1]

§Arguments
[
String   FileName [N] [Enter file names. Use "%" for wildcard. E.g. "in%.%"]
ProjectID StartProjectID [1] [Enter start folder. E.g "$\Top folder", or browse for folder.]
StatusID CurrentStatusID [1] [Select status]
UserID	TheUserID[S] [Select user]
]

§Sql
[           
Declare @iStartFolder Int
Declare @StatusID Int
Declare @UserID Int

Set @iStartFolder = {StartProjectID}
Set @StatusID = {CurrentStatusID}
Set @UserID = {TheUserID}

Select Doc.DocumentID, P.Path + Doc.Filename as 'Document Name', Doc.LatestRevisionNo as 'Latest Version'
From  Projects P, DocumentsInProjects Dip, Documents Doc
Where P.Path like Replace(Replace(Replace( (Select Path From Projects Where ProjectID = @iStartFolder  )
                                            ,'[','[[]'),'_','[_]'),'%','[%]') + '%' And
      P.ProjectID = Dip.ProjectID And
      Doc.DocumentID = Dip.DocumentID And
      Dip.Deleted = 0 And
      Doc.CurrentStatusID = @StatusID And
      {Doc.Filename, FileName, LIKE, OR} And
      Exists ( SELECT ProjectID   
               FROM UserProjectRights
               WHERE ProjectID = P.ProjectID AND
                     UserID = @UserID And
                     Type & 1 = 1 
               Union
               ( SELECT Gpr.ProjectID
                 FROM GroupProjectRights Gpr, GroupMembers Gm
                 WHERE Gpr.ProjectID = P.ProjectID AND
                       Gpr.GroupID = Gm.GroupID And
                       Gm.UserID = @UserID And
                       Gpr.Type & 1 = 1 ) )
      
]




/*******************                        ********************/



@[ListAssemblyTree]
§Name [Assembly Tree Structure]

§Company [SolidWorks]

§Description
[This query lists the tree structure of an assembly]

§Version [1.2]

 

§Arguments
[
  FileID TheFileID[S][]
]

 
§Sql

[
  Declare @XrefTable Table( iIndex Int, ParentID Int, DocumentID Int )
  Declare @iIndex Int
  Declare @iRootID Int

  Set @iIndex = 0
  Set @iRootID = {TheFileID}

  Insert Into @XrefTable( iIndex, ParentID, DocumentID )
  Select Distinct  @iIndex,  0, @iRootID
  From Documents Doc

  While( @@RowCount > 0 )
  Begin
     Set @iIndex = @iIndex + 1 
     Insert Into @XrefTable( iIndex, ParentID, DocumentID )
     Select @iIndex, X.DocumentID, X.XRefDocument
     From @XrefTable r, XRefs X
     Where
        r.iIndex = (@iIndex - 1) And
        X.DocumentID = r.DocumentID
  End

  Select iIndex as 'Level',
         IsNull( (Select Filename From Documents Where DocumentID = X.ParentID), '') as 'Parent Name',
         (Select Filename From Documents Where DocumentID = X.DocumentID) as 'Part'
  From @XrefTable X


]


/*******************                        ********************/

@[DocumentsByConfigurationName]
§Name [Documents with specified configuration]

§Company [SolidWorks]

§Description
[This query lists all documents with a specified configuration name.]

§Version [1.1]

§Arguments
[
String   ConfigName [N] [Enter configuration names. Use "%" for wildcard. E.g. "Type%" or "%Type%"]
]

§Sql
[           

SELECT DISTINCT Projects.Path, Documents.Filename, DocumentConfiguration.ConfigurationName
FROM         Projects INNER JOIN
                      DocumentsInProjects ON Projects.ProjectID = DocumentsInProjects.ProjectID INNER JOIN
                      DocumentConfiguration INNER JOIN
                      DocumentRevisionConfiguration ON DocumentConfiguration.ConfigurationID = DocumentRevisionConfiguration.ConfigurationID INNER JOIN
                      Documents ON DocumentRevisionConfiguration.DocumentID = Documents.DocumentID ON 
                      DocumentsInProjects.DocumentID = Documents.DocumentID
WHERE     (DocumentConfiguration.ConfigurationName LIKE {ConfigName})
ORDER BY Projects.Path
]


/*******************                        ********************/

@[Active PDMWE Licenses]
§Name [Show active PDMWE licenses]

§Company [SolidWorks]

§Description
[-]

§Version [1.1]

§Arguments
[
]

§Sql
[
select
distinct
liu.hostname,
liu.username,
gliu.DatabaseName,
case 
  when gliu.ProgramType=0 then 'Enterprise'
  when gliu.ProgramType=1 then 'Contributor'
  when gliu.ProgramType=2 then 'Viewer'
  when gliu.ProgramType=3 then 'Web'
end as LicenseType
from 
ConisioMasterDb.dbo.LoggedInUsers liu,
ConisioMasterDb.dbo.LoggedInUsers gliu,
master..sysprocesses sp
Where
liu.spid = gliu.spid and
liu.spid = sp.spid and
sp.program_name like '%EdmServ%'


]


@[All Parents]
§Name [All Parents of Selected File]

§Company [InFlow Technology]

§Description
[This query lists the parents of a file]

§Version [1.2]

 

§Arguments
[
  FileID TheFileID[S][]
]

 
§Sql

[

DECLARE @Child char
DECLARE @ChildID Int

Set @ChildID={TheFileID}

SELECT Filename as 'Parent', XRefs.RevNr as 'Parent Rev', XRefs.XRefRevNr as 'Child Rev', XRefs.RefCount as 'Qty'

FROM Documents
INNER JOIN XRefs
ON Documents.DocumentID = XRefs.DocumentID
WHERE XRefs.XRefDocument = @ChildID
Order By Documents.Filename, XRefs.RevNr

]