PC World 2003 December/PCWorld_2003-12_cd.bin/Software/komercni/alc/ep2sr51csy.msi/Ep.CAB/dbdef.txt2

Text File | 2003-09-24 | 48KB | 1,460 lines

<%

Const dbtJet=1
Const dbtMsSql=2


Dim dbt


Function WriteBool(Value)
    If dbt = dbtJet Then
        If Value Then
            Write("TRUE")
        Else
            Write("FALSE")
        End If
    Else
        If Value Then
            Write("1")
        Else
            Write("0")
        End If
    End If
End Function

Function WriteNow
    If dbt = dbtJet Then
        Write("Now()")
    Else
        Write("GETDATE()")
    End If
End Function
%>


<% Sub vLicHistAsset %>
SELECT
    tblLicHistAtom.lintLicHistId    AS lintLicHistId,
    tblAsset.txtId                    AS txtInvoice,
    tblAsset.lintLocationId            AS lintLocationId,
    vInvoice.txtCompany                AS txtCompany
FROM ((
    tblAtom
    INNER JOIN
        tblAsset
    ON tblAtom.intAtomId = tblAsset.lintAtomId)
    INNER JOIN
        tblLicHistAtom
    ON tblAtom.intAtomId = tblLicHistAtom.lintAtomId)
    INNER JOIN
        vInvoice
    ON vInvoice.lintAtomId = tblAtom.intAtomId
<% End Sub %>


<% Sub vLicHistItem %>
SELECT
    tblLicHistItem.intLicHistItemId            AS lintLicHistItemId,
    tblLicHistItem.lintLicHistId            AS lintLicHistId,
    tblLicHistItem.txtSerialNumber            AS txtSerialNumber,
    tblLicHistItem.lintComputerNodeId        AS lintComputerNodeId,
    tblNode.txtName                            AS txtComputer,
    tblLicHistItem.lintCancelledByLicHistId    AS lintCancelledById,
    tblLicHist.dteDate                        AS dteCancelledDate,
    tblUser.txtUser                            AS txtCancelledByUser
FROM
    tblNode RIGHT JOIN ((
        tblLicHistItem
        LEFT JOIN
            tblLicHist
        ON tblLicHistItem.lintCancelledByLicHistId = tblLicHist.intLicHistId)
        LEFT JOIN
            tblUser
        ON tblLicHist.lintUserId = tblUser.intUserId)
    ON tblNode.intNodeId = tblLicHistItem.lintComputerNodeId
<% End Sub %>


<% Sub vInstHist %>
SELECT
    tblInstHist.intInstHistId        AS lintInstHistId,
    tblInstHist.lintComputerNodeId    AS lintComputerNodeId,
    tblInstHist.lintProductId        AS lintProductId,
    tblNode.txtName                    AS txtComputer,
    vProduct.txtName                AS txtProduct,
    vProduct.lintLangId                AS lintLangId,
    tblInstHist.dteDate                AS dteDate,
    tblInstHist.intCount            AS intCount,
    tblUser.txtName                    AS txtUser,
    tblLicHistItem.txtSerialNumber    AS txtSerialNumber,
    tblInstHist.txtMemo                AS txtMemo
FROM
    tblLicHistItem
    RIGHT JOIN (
        tblUser
        RIGHT JOIN (
            vProduct
            INNER JOIN (
                tblInstHist
                INNER JOIN
                    tblNode
                ON tblInstHist.lintComputerNodeId = tblNode.intNodeId)
            ON vProduct.intProductId = tblInstHist.lintProductId)
        ON tblUser.intUserId = tblInstHist.lintUserId)
    ON tblLicHistItem.intLicHistItemId = tblInstHist.lintLicHistItemId

WHERE NOT tblNode.bolHidden=<% WriteBool(True) %>
<% End Sub %>


<% Sub vProductComputerBase %>
SELECT
    tblInstHist.lintProductId            AS lintProductId,
    tblInstHist.lintComputerNodeId        AS lintComputerNodeId
FROM
    vComputer
    INNER JOIN (
        tblInstHist
        INNER JOIN
            vProduct
        ON tblInstHist.lintProductId=vProduct.intProductId)
    ON vComputer.intComputerNodeId=tblInstHist.lintComputerNodeId
WHERE NOT vComputer.bolHidden=<% WriteBool(True) %>
GROUP BY tblInstHist.lintProductId, tblInstHist.lintComputerNodeId
HAVING SUM (tblInstHist.intCount)>0
<% End Sub %>


<% Sub vProductAllComputers %>
SELECT
    vProduct.intProductId            AS lintProductId,
    vComputer.intComputerNodeId        AS lintComputerNodeId
FROM vComputer <% If dbt=dbtJet Then %>,<% Else %>CROSS JOIN<% End If %> vProduct
WHERE NOT vComputer.bolHidden=<% WriteBool(True) %>
<% End Sub %>


<% Sub vLic1 %>
SELECT
    tblLicHist.lintProductId                AS lintProductId,
    SUM(tblLicHist.intCountChange)            AS intCount,
    SUM(tblLicHist.intMultiCountChange)        AS intMultiCount
FROM
    tblLicHist
    INNER JOIN
        vProduct
    ON tblLicHist.lintProductId=vProduct.intProductId
WHERE tblLicHist.dteDate <= <% WriteNow() %> AND
 (tblLicHist.dteDateExpire IS NULL OR <% WriteNow() %> <= tblLicHist.dteDateExpire) AND
 NOT EXISTS(
    SELECT 1
    FROM tblLicHistItem
    WHERE tblLicHist.intLicHistId=tblLicHistItem.lintLicHistId AND tblLicHistItem.lintComputerNodeId IS NOT NULL)
GROUP BY tblLicHist.lintProductId
<% End Sub %>


<% Sub vLic2 %>
SELECT
    tblLicHist.lintProductId            AS lintProductId,
    tblLicHistItem.lintComputerNodeId    AS lintComputerNodeId
FROM
    vComputer
    INNER JOIN (
        vProduct
        INNER JOIN (
            tblLicHist
            INNER JOIN
                tblLicHistItem
            ON tblLicHist.intLicHistId=tblLicHistItem.lintLicHistId)
        ON vProduct.intProductId=tblLicHist.lintProductId)
    ON vComputer.intComputerNodeId=tblLicHistItem.lintComputerNodeId
WHERE NOT vComputer.bolHidden=<% WriteBool(True) %> AND
      tblLicHist.dteDate <= <% WriteNow() %> AND (tblLicHist.dteDateExpire IS NULL OR <% WriteNow() %> <= tblLicHist.dteDateExpire) AND
      tblLicHistItem.lintCancelledByLicHistId IS NULL
<% End Sub %>


<% Sub vLicHist %>
SELECT
    tblLicHist.intLicHistId                AS lintLicHistId,
    tblLicHist.lintProductId            AS lintProductId,
    tblLicHist.dteDate                    AS dteDate,
    tblLicHist.dteDateExpire            AS dteDateExpire,
    (tblLicHist.intMultiCountChange+tblLicHist.intCountChange) AS intChange,
    tblLicHist.intMultiCountChange        AS intMultiCountChange,
    tblLicHist.txtMemo                    AS txtMemo,
    vProduct.txtName                    AS txtProduct,
    vProduct.lintLangId                    AS lintLangId
FROM
    vProduct
    INNER JOIN
        tblLicHist
    ON vProduct.intProductId = tblLicHist.lintProductId
<% End Sub %>


<% Sub vSwInstLast %>
SELECT
    intInstHistId,
    lintComputerNodeId,
    lintProductId,
    dteDate,
    lintUserId,
    txtMemo,
    intCount,
    lintLicHistItemId
FROM tblInstHist AS I
WHERE dteDate =
    (SELECT MAX(dteDate)
     FROM tblInstHist AS I2, vProduct
     WHERE  I.lintComputerNodeId = I2.lintComputerNodeId AND I.lintProductId = I2.lintProductId AND I2.intCount = 1 AND vProduct.intProductId = I2.lintProductId
    )
<% End Sub %>

<% Sub vLicAudit %>
SELECT
        vProduct.intProductId                    AS lintProductId,
        vProduct.txtName                        AS txtProduct,
        vProduct.lintLangId                        AS lintLangId,
        tblCompany.txtName                        AS txtCompany,
        vProduct.lintProductTypeId                AS lintProductTypeId,
    <% If dbt = dbtJet Then %>
        IIF(vLic1.intCount IS NULL,0,vLic1.intCount)                                            AS intLicCount,
        IIF(vLic1.intMultiCount IS NULL,0,vLic1.intMultiCount)                                    AS intLicMultiCount,
        IIF(vLic2Count.intCount IS NULL,0,vLic2Count.intCount)                                    AS intLicOEMCount,
        IIF(vProductInstCount.intCount IS NULL,0,vProductInstCount.intCount)                    AS intInstAllCount,
        IIF(vProductInstNotebootCount.intCount IS NULL,0,vProductInstNotebootCount.intCount)    AS intInstNotebookCount,
        IIF(vProductInstOEMCount.intCount IS NULL,0,vProductInstOEMCount.intCount)                AS intInstOEMCount,
        IIF(vInstTrialCount.intCount IS NULL,0,vInstTrialCount.intCount)                        AS intInstTrialCount,
        (intInstAllCount-intInstNotebookCount-intInstOEMCount-intInstTrialCount)                AS intInstCount,
        IIF(lintProductTypeId=2,0,IIF(intLicMultiCount>0,0,(intInstCount+intInstOEMCount)-(intLicCount+intLicOEMCount)))    AS intLicMissCount,
        IIF(lintProductTypeId=2,0,IIF(intLicMultiCount>0,intLicCount,intLicCount-intInstCount))                                AS intLicAddCount,
        IIF(lintProductTypeId=2,0,intLicOEMCount-intInstOEMCount)                                                            AS intLicOEMAddCount
    <% Else %>
        ISNULL(vLic1.intCount,0)                        AS intLicCount,
        ISNULL(vLic1.intMultiCount,0)                    AS intLicMultiCount,
        ISNULL(vLic2Count.intCount,0)                    AS intLicOEMCount,
        ISNULL(vProductInstCount.intCount,0)            AS intInstAllCount,
        ISNULL(vProductInstNotebootCount.intCount,0)    AS intInstNotebookCount,
        ISNULL(vProductInstOEMCount.intCount,0)            AS intInstOEMCount,
        ISNULL(vInstTrialCount.intCount,0)                AS intInstTrialCount,
        (ISNULL(vProductInstCount.intCount,0)-ISNULL(vProductInstNotebootCount.intCount,0)-ISNULL(vProductInstOEMCount.intCount,0)-ISNULL(vInstTrialCount.intCount,0))    AS intInstCount,
        CASE vProduct.lintProductTypeId WHEN 2
            THEN 0
            ELSE CASE WHEN vLic1.intMultiCount IS NULL OR vLic1.intMultiCount=0
                THEN (ISNULL(vProductInstCount.intCount,0)-ISNULL(vProductInstNotebootCount.intCount,0)-ISNULL(vInstTrialCount.intCount,0))-(ISNULL(vLic1.intCount,0)+ISNULL(vLic2Count.intCount,0))
                ELSE 0
            END
        END AS intLicMissCount,
        CASE vProduct.lintProductTypeId WHEN 2
            THEN 0
            ELSE CASE WHEN vLic1.intMultiCount IS NULL OR vLic1.intMultiCount=0
                THEN ISNULL(vLic1.intCount,0)-(ISNULL(vProductInstCount.intCount,0)-ISNULL(vProductInstNotebootCount.intCount,0)-ISNULL(vProductInstOEMCount.intCount,0)-ISNULL(vInstTrialCount.intCount,0))
                ELSE ISNULL(vLic1.intCount,0)
            END
        END AS intLicAddCount,
        CASE vProduct.lintProductTypeId WHEN 2
            THEN 0
            ELSE (ISNULL(vLic2Count.intCount,0)-ISNULL(vProductInstOEMCount.intCount,0))
        END AS intLicOEMAddCount
    <% End If %>
FROM
    tblCompany
    RIGHT JOIN ((
        vLic1
        RIGHT JOIN (
            vLic2Count
            RIGHT JOIN (
                vProductInstOEMCount
                RIGHT JOIN (
                    vProductInstNotebootCount
                    RIGHT JOIN (
                        vProductInstCount
                        RIGHT JOIN
                            vProduct
                        ON vProductInstCount.lintProductId = vProduct.intProductId)
                    ON vProductInstNotebootCount.lintProductId = vProduct.intProductId)
                ON vProductInstOEMCount.lintProductId = vProduct.intProductId)
            ON vLic2Count.lintProductId = vProduct.intProductId)
        ON vLic1.lintProductId = vProduct.intProductId)
        LEFT JOIN
            vInstTrialCount
        ON vProduct.intProductId = vInstTrialCount.lintProductId)
    ON tblCompany.intCompanyId = vProduct.lintProducerCompanyId
WHERE vProductInstCount.intCount IS NOT NULL OR vLic1.intCount IS NOT NULL OR vLic1.intMultiCount IS NOT NULL OR vLic2Count.intCount IS NOT NULL
<% End Sub %>


<% Sub tblLicHistItem %>
CREATE TABLE tblLicHistItem (
    intLicHistItemId            int                IDENTITY    CONSTRAINT PK_tblLicHistItem_intLicHistItemId PRIMARY KEY,
    lintLicHistId                int                NOT NULL    CONSTRAINT FK_tblLicHistItem_lintLicHistId_tblLicHist_intLicHistId REFERENCES tblLicHist ( intLicHistId ),
    lintComputerNodeId            int                            CONSTRAINT FK_tblLicHistItem_lintComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ),
    txtSerialNumber                varchar(255),
    lintCancelledByLicHistId    int                            CONSTRAINT FK_tblLicHistItem_lintCancelledBy_tblLicHist_intLicHistId REFERENCES tblLicHist ( intLicHistId )
);

CREATE INDEX tblLicHistItem_intLicHistItemId            ON tblLicHistItem ( intLicHistItemId );
CREATE INDEX tblLicHistItem_lintComputerNodeId            ON tblLicHistItem ( lintComputerNodeId );
CREATE INDEX tblLicHistItem_lintCancelledByLicHistId    ON tblLicHistItem ( lintCancelledByLicHistId );
<% End Sub %>


<%
Sub CreateDbX
%>
CREATE TABLE tblVersion ( /*
Verze databßze
V tabulce je jeden zßznam. */
    intVersion            int, /* verze struktury, =9 pro 2002 SR4, =10 pro 2002 SR5 */
    intDataVersion        int, /* verze zßkladnφch dat, =5 od 2002 */
    intLanguage            int /* jazyk databßze, 0=ΦeÜtina, 1=slovenÜtina */
);


CREATE TABLE tblUser ( /* U₧ivatel programu */
    intUserId            int                IDENTITY CONSTRAINT PK_tblUser_intUserId PRIMARY KEY,
    txtName                varchar(255), /* celΘ jmΘno */
    txtPassword            varchar(255), /* heslo */
    txtEmail            varchar(255),
    intFlags            int, /*
        bitovΘ pole:
        F_MOVE=0x02, prßvo p°esouvat objekty
        F_CREATE=0x04, prßvo vytvß°et a upravovat objekty
        F_DELETE=0x08, prßvo odstra≥ovat objekty
        F_NOTADMIN=0x10, u₧ivatel NEnφ sprßvce aplikace
        F_BLOCKLOGIN=0x20, u₧ivatel je zablokovan² (nem∙₧e se p°ihlßsit)
    */
    txtUser                varchar(255) /* login */
);


CREATE INDEX tblUser_intUserId ON tblUser ( intUserId );


CREATE TABLE tblIcon ( /* Ikona */
    intIconId            int                IDENTITY CONSTRAINT PK_tblIcon_intIconId PRIMARY KEY,
    oleIcon                image, /* obrßzek ve formßtu bmp */
    txtDesc                varchar(255) /* nßzev ikony */
);

CREATE INDEX tblIcon_intIconId ON tblIcon ( intIconId );


CREATE TABLE tblKind ( /* Definice vlastnosti */
    intKindId            int                IDENTITY    CONSTRAINT PK_tblKind_intKindId PRIMARY KEY,
    txtName                varchar(255)    NOT NULL, /* nßzev vlastnosti, nap°. Nßzev sestavy, IP adresa, apod. */
    lintIconId            int                            CONSTRAINT FK_tblKind_lintIconId_tblIcon_intIconId REFERENCES tblIcon ( intIconId ), /* ikona vlastnosti */
    intFlags            int, /*
        bitovΘ pole:
        F_NOINHERIT=0x20, vlastnost nenφ d∞diΦnß
        F_UNIQUE=0x1000, kontrolovat jedineΦnost hodnot
        */
    lintValueKindId        int                            CONSTRAINT FK_tblKind_lintValueKindId_tblKind_intKindId REFERENCES tblKind ( intKindId )
);

CREATE INDEX tblKind_intKindId         ON tblKind ( intKindId );
CREATE INDEX tblKind_lintIconId         ON tblKind ( lintIconId );
CREATE INDEX tblKind_lintValueKindId ON tblKind ( lintValueKindId );


CREATE TABLE tblKindValue ( /* Polo₧ka Φφselnφku hodnot vlastnosti
Nap°. pro vlastnosti Freeware jsou v Φφselnφku 2 hodnoty: Ano a Ne. */
    lintKindId            int                NOT NULL CONSTRAINT FK_tblKindValue_lintKindId_tblKind_intKindId REFERENCES tblKind ( intKindId ), /* definice vlastnosti */
    txtValue            varchar(255) /* hodnota */
);

CREATE INDEX tblKindValue_lintKindId ON tblKindValue ( lintKindId );


CREATE TABLE tblNode ( /* Objekt (uzel stromu) */
    intNodeId            int                IDENTITY    CONSTRAINT PK_tblNode_intNodeId PRIMARY KEY,
    lintParentId        int                            CONSTRAINT FK_tblNode_lintParentId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ), /* otec uzlu */
    lintIconId            int                            CONSTRAINT FK_tblNode_lintIconId_tblIcon_intIconId REFERENCES tblIcon ( intIconId ), /* ikona objektu */
    intState            int, /*
        bitovΘ pole:
        S_BASKET=1, objekt KoÜ
        S_DELLOCK=2, objekt je chrßn∞n p°ed smazßnφm
        S_TEMPLATES=4 objekt èablony objekt∙
        S_MOVELOCK=8, nep°esouvateln² objekt
        S_HIDDEN=0x10, skryt² objekt
        S_HWSCANNER=0x20, objekt èablony scanneru
        S_PROPTABTEMPL=0x40, objekt Zßlo₧ky seznamu vlastnostφ
        S_NOHISTORY=0x80, u objektu se nevede historie
        S_NEWCOMP=0x100, objekt NovΘ poΦφtaΦe
    */
    txtName                varchar(255), /* nßzev objektu */
    bolHidden            bit /* 0 pro objekty v koÜi a Üablony, jinak 1 */
);

CREATE INDEX tblNode_intNodeId        ON tblNode ( intNodeId );
CREATE INDEX tblNode_lintParentId    ON tblNode ( lintParentId );
CREATE INDEX tblNode_lintIconId        ON tblNode ( lintIconId );
CREATE INDEX tblNode_txtName        ON tblNode ( txtName );


CREATE TABLE tblProperty ( /* Vlastnost */
    intPropertyId        int                IDENTITY CONSTRAINT PK_tblProperty_intPropertyId PRIMARY KEY,
    lintNodeId            int                NOT NULL CONSTRAINT FK_tblProperty_lintNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ), /* objekt */
    lintKindId            int                NOT NULL CONSTRAINT FK_tblProperty_lintKindId_tblNode_intKindId REFERENCES tblKind ( intKindId ), /* definice vlastnosti */
    txtValue            varchar(255), /* hodnota vlastnosti */
    intState            int /*
        bitovΘ pole:
        S_DELLOCK=2, vlastnost je zamΦena proti smazßnφ
        S_NODEICON=4, vlastnost urΦuje ikonu uzlu
        0x18, hodnota urΦuje Φßst nßzvu objektu:
            0, ₧ßdnou
            S_NODENAMEPRE=8, p°edpona
            S_NODENAMEBASE=0x10, kmen
            S_NODENAMEPOST=0x18, p°φpona
        S_HIDDEN=0x20, skrytß vlastnost
        S_NOINHERIT=0x40, vlastnost se ned∞dφ
    */
);

CREATE INDEX tblProperty_intPropertyId    ON tblProperty ( intPropertyId );
CREATE INDEX tblProperty_lintNodeId        ON tblProperty ( lintNodeId );
CREATE INDEX tblProperty_lintKindId        ON tblProperty ( lintKindId );
CREATE INDEX tblProperty_txtValue        ON tblProperty ( txtValue );


CREATE TABLE tblHistory ( /* Zßznam historie objektu */
    intHistoryId        int                IDENTITY CONSTRAINT PK_tblHistory_intHistoryId PRIMARY KEY,
    lintNodeId            int                NOT NULL CONSTRAINT FK_tblHistory_lintNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ), /* objekt */
    lintUserId            int                NOT NULL CONSTRAINT FK_tblHistory_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ), /* u₧ivatel, kter² operaci provedl */
    dteDateTime            datetime, /* datum a Φas provedenφ */
    intFlags            int, /*
        hodnota v bitech 0xff00:
        F_C_MOVEDEST=1, sem presunut "X"
        F_C_MOVESRC=2, odtud presunut "X"
        F_C_TO=3, presunut do "X"
        F_C_INSERTNEW=4, sem vlozen novy "X" (v otcovskem)
        F_C_TONEW=5, vytvoren v "X"
        F_C_SCANMOVEDEST=6, sem presunut "X"
        F_C_SCANMOVESRC=7, odtud presunut "X"
        F_C_SCANTO=8, presunut do "X"
        F_C_SCANINSERTNEW=9, sem vlozen novy "X"
        F_C_SCANTONEW=10, vytvoren v "X"
    */
    lintOpNodeId        int, /* objekt X */
    txtOpNodeName        varchar(255) /* Nßzev objektu X s cestou v p°φpad∞,
     ₧e objekt byl odstran∞n z db a lintOpNodeId je NULL. */
);

CREATE INDEX tblHistory_intHistoryId    ON tblHistory ( intHistoryId );
CREATE INDEX tblHistory_lintNodeId        ON tblHistory ( lintNodeId );
CREATE INDEX tblHistory_lintUserId        ON tblHistory ( lintUserId );
CREATE INDEX tblHistory_lintOpNodeId    ON tblHistory ( lintOpNodeId );


CREATE TABLE tblNotice ( /* Poznßmka k objektu */
    intNoticeId            int                IDENTITY CONSTRAINT PK_tblNotice_intNoticeId PRIMARY KEY,
    lintNodeId            int                NOT NULL CONSTRAINT FK_tblNotice_lintNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ), /* objekt */
    lintUserId            int                NOT NULL CONSTRAINT FK_tblNotice_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ), /* u₧ivatel, kter² poznßmku vytvo°il */
    dteDateTime            datetime, /* datum a Φas */
    txtDesc                text /* text poznßmky */
);

CREATE INDEX tblNotice_intNoticeId    ON tblNotice ( intNoticeId );
CREATE INDEX tblNotice_lintNodeId    ON tblNotice ( lintNodeId );
CREATE INDEX tblNotice_lintUserId    ON tblNotice ( lintUserId );


CREATE TABLE tblSetting (
    txtKey                varchar(255)    NOT NULL CONSTRAINT PK_tblSetting_txtKey PRIMARY KEY,
    txtValue            varchar(255),
    intValue            int
);

CREATE INDEX tblSetting_txtKey ON tblSetting ( txtKey );
<%
End Sub
%>


<%
Sub CreateDbX_v10
%>
CREATE TABLE tblAtom (
    intAtomId            int                IDENTITY CONSTRAINT PK_tblAtom_intAtomId PRIMARY KEY,
    lintAtomKindId        int                NOT NULL
);

CREATE INDEX tblAtom_intAtomId        ON tblAtom ( intAtomId );
CREATE INDEX tblAtom_lintAtomKindId ON tblAtom ( lintAtomKindId );


CREATE TABLE tblAsset (
    lintAtomId            int                NOT NULL CONSTRAINT FK_tblAsset_lintAtomId_tblAtom_intAtomId REFERENCES tblAtom ( intAtomId ),
    txtId                varchar(255),
    lintLocationId        int
);

CREATE INDEX tblAsset_lintAtomId        ON tblAsset ( lintAtomId );
CREATE INDEX tblAsset_txtId                ON tblAsset ( txtId );
CREATE INDEX tblAsset_lintLocationId    ON tblAsset ( lintLocationId );


CREATE TABLE tblCompany (
    intCompanyId        int                IDENTITY  CONSTRAINT PK_tblCompany_intCompanyId PRIMARY KEY,
    txtName                varchar(255)    NOT NULL,
    txtAddress1            varchar(255),
    txtAddress2            varchar(255),
    txtCity                varchar(255),
    txtZIP                varchar(255),
    txtState            varchar(255),
    txtWeb                varchar(255),
    txtPhone            varchar(255),
    bolProducer            bit                NOT NULL,
    bolReseller            bit                NOT NULL,
    bolPrint            bit                NOT NULL
);

CREATE INDEX tblCompany_intCompanyId    ON tblCompany ( intCompanyId );
CREATE INDEX tblCompany_txtName            ON tblCompany ( txtName );


CREATE TABLE tblProduct (
    intProductId          int            IDENTITY    CONSTRAINT PK_tblProduct_intProductId PRIMARY KEY,
    txtName                  varchar(255)    NOT NULL,
    lintLangId              int,
    lintProducerCompanyId int                        CONSTRAINT FK_tblProduct_lintProducerCompanyId_tblCompany_intCompanyId REFERENCES tblCompany ( intCompanyId ),
    lintProductTypeId      int            NOT NULL,
    lintProductCategoryId int,
    bolUser                  bit            NOT NULL,
    intSimple              int            NOT NULL    <% If dbt <> dbtJet Then %>CONSTRAINT DF_tblProduct_intSimple<% End If %> DEFAULT 0
);

CREATE INDEX tblProduct_intProductId            ON tblProduct ( intProductId );
CREATE INDEX tblProduct_txtName                    ON tblProduct ( txtName );
CREATE INDEX tblProduct_lintLangId                ON tblProduct ( lintLangId );
CREATE INDEX tblProduct_lintProducerCompanyId    ON tblProduct ( lintProducerCompanyId );
CREATE INDEX tblProduct_lintProductTypeId        ON tblProduct ( lintProductTypeId );
CREATE INDEX tblProduct_lintProductCategoryId    ON tblProduct ( lintProductCategoryId );
CREATE INDEX tblProduct_intSimple                ON tblProduct ( intSimple );


CREATE TABLE tblDetect (
    intDetectId            int                IDENTITY CONSTRAINT PK_tblDetect_intDetectId PRIMARY KEY,
    lintComputerNodeId    int                NOT NULL CONSTRAINT FK_tblDetect_lintComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ),
    dteCreated            datetime,
    dteImported            datetime
);

CREATE INDEX tblDetect_intDetectId            ON tblDetect ( intDetectId );
CREATE INDEX tblDetect_lintComputerNodeId    ON tblDetect ( lintComputerNodeId );


CREATE TABLE tblFile (
    intFileId            int                IDENTITY CONSTRAINT PK_tblFile_intFileId PRIMARY KEY,
    txtName                varchar(255),
    txtExt                varchar(255),
    intSize                int
);

CREATE INDEX tblFile_intFileId    ON tblFile ( intFileId );
CREATE INDEX tblFile_txtName    ON tblFile ( txtName );
CREATE INDEX tblFile_txtExt        ON tblFile ( txtExt );
CREATE INDEX tblFile_intSize    ON tblFile ( intSize );


CREATE TABLE tblDetFile (
    intDetFileId        int                IDENTITY    CONSTRAINT PK_tblDetFile_intDetFileId PRIMARY KEY,
    lintFileId            int                            CONSTRAINT FK_tblDetFile_lintFileId_tblFile_intFileId REFERENCES tblFile ( intFileId ),
    lintDetectId        int                            CONSTRAINT FK_tblDetFile_lintDetectId_tblDetect_intDetectId REFERENCES tblDetect ( intDetectId ),
    txtFolderPath        varchar(255),
    dteCreated            datetime,
    dteModified            datetime,
    dteAccessed            datetime,
    intAttribs            int,
    lintProductId        int                            CONSTRAINT FK_tblDetFile_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId )
);

CREATE INDEX tblDetFile_intDetFileId    ON tblDetFile ( intDetFileId );
CREATE INDEX tblDetFile_lintFileId        ON tblDetFile ( lintFileId );
CREATE INDEX tblDetFile_lintDetectId    ON tblDetFile ( lintDetectId );
CREATE INDEX tblDetFile_txtFolderPath    ON tblDetFile ( txtFolderPath );
CREATE INDEX tblDetFile_dteCreated        ON tblDetFile ( dteCreated );
CREATE INDEX tblDetFile_dteModified        ON tblDetFile ( dteModified );
CREATE INDEX tblDetFile_lintProductId    ON tblDetFile ( lintProductId );


CREATE TABLE tblImport (
    intImportId            int                IDENTITY  CONSTRAINT PK_tblImport_intImportId PRIMARY KEY,
    dteCreated            datetime        NOT NULL
);

CREATE INDEX tblImport_intImportId ON tblImport ( intImportId );


CREATE TABLE tblFileImport (
    intFileImportId        int                IDENTITY    CONSTRAINT PK_tblFileImport_intFileImportId PRIMARY KEY,
    lintImportId        int                NOT NULL    CONSTRAINT FK_tblFileImport_lintImportId_tblImport_intImportId REFERENCES tblImport ( intImportId ),
    lintDetFileId        int                            CONSTRAINT FK_tblFileImport_lintDetFileId_tblDetFile_intDetFileId REFERENCES tblDetFile ( intDetFileId ),
    lintFileId            int                            CONSTRAINT FK_tblFileImport_lintFileId_tblFile_intFileId REFERENCES tblFile ( intFileId ),
    txtName                varchar(255),
    txtExt                varchar(255),
    intSize                int,
    intFileVersionMS    int,
    intFileVersionLS    int,
    intProductVersionMS int,
    intProductVersionLS int
);

CREATE INDEX tblFileImport_intFileImportId        ON tblFileImport ( intFileImportId );
CREATE INDEX tblFileImport_lintImportId            ON tblFileImport ( lintImportId );
CREATE INDEX tblFileImport_lintDetFileId        ON tblFileImport ( lintDetFileId );
CREATE INDEX tblFileImport_lintFileId            ON tblFileImport ( lintFileId );
CREATE INDEX tblFileImport_txtName                ON tblFileImport ( txtName );
CREATE INDEX tblFileImport_txtExt                ON tblFileImport ( txtExt );
CREATE INDEX tblFileImport_intSize                ON tblFileImport ( intSize );
CREATE INDEX tblFileImport_intFileVersionMS        ON tblFileImport ( intFileVersionMS );
CREATE INDEX tblFileImport_intFileVersionLS        ON tblFileImport ( intFileVersionLS );
CREATE INDEX tblFileImport_intProductVersionMS    ON tblFileImport ( intProductVersionMS );
CREATE INDEX tblFileImport_intProductVersionLS    ON tblFileImport ( intProductVersionLS );


CREATE TABLE tblEvent (
    intEventId            int                IDENTITY CONSTRAINT PK_tblEvent_intEventId PRIMARY KEY,
    dteSignalDate        datetime        NOT NULL,
    dteDate                datetime,
    txtMemo                text,
    lintUserId            int                NOT NULL CONSTRAINT FK_tblEvent_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ),
    bolChecked            bit                NOT NULL
);

CREATE INDEX tblEvent_intEventId ON tblEvent ( intEventId );
CREATE INDEX tblEvent_lintUserId ON tblEvent ( lintUserId );


CREATE TABLE tblFileStringInfo (
    lintFileId            int                NOT NULL CONSTRAINT FK_tblFileStringInfo_lintFileId_tblFile_intFileId REFERENCES tblFile ( intFileId ),
    intLanguage            int,
    intCodePage            int,
    txtComments            varchar(255),
    txtCompanyName        varchar(255),
    txtFileDescription    varchar(255),
    txtFileVersion        varchar(255),
    txtInternalName        varchar(255),
    txtLegalCopyright    varchar(255),
    txtLegalTrademarks    varchar(255),
    txtOriginalFilename varchar(255),
    txtProductName        varchar(255),
    txtProductVersion    varchar(255),
    txtPrivateBuild        varchar(255),
    txtSpecialBuild        varchar(255)
);

CREATE INDEX tblFileStringInfo_lintFileId        ON tblFileStringInfo ( lintFileId );
CREATE INDEX tblFileStringInfo_txtCompanyName    ON tblFileStringInfo ( txtCompanyName );
CREATE INDEX tblFileStringInfo_txtProductName    ON tblFileStringInfo ( txtProductName );


CREATE TABLE tblFileVerInfo (
    lintFileId            int                NOT NULL CONSTRAINT FK_tblFileVerInfo_lintFileId_tblFile_intFileId REFERENCES tblFile ( intFileId ),
    intFileVersionMS    int,
    intFileVersionLS    int,
    intProductVersionMS int,
    intProductVersionLS int,
    intFileFlagsMask    int,
    intFileFlags        int,
    intFileOS            int,
    intFileType            int,
    intFileSubtype        int,
    intFileDateMS        int,
    intFileDateLS        int
);

CREATE INDEX tblFileVerInfo_lintFileId            ON tblFileVerInfo ( lintFileId );
CREATE INDEX tblFileVerInfo_intFileVersionMS    ON tblFileVerInfo ( intFileVersionMS );
CREATE INDEX tblFileVerInfo_intFileVersionLS    ON tblFileVerInfo ( intFileVersionLS );
CREATE INDEX tblFileVerInfo_intProductVersionMS ON tblFileVerInfo ( intProductVersionMS );
CREATE INDEX tblFileVerInfo_intProductVersionLS ON tblFileVerInfo ( intProductVersionLS );


CREATE TABLE tblIdSeq (
    intIdSeqId            int                IDENTITY CONSTRAINT PK_tblIdSeq_intIdSeqId PRIMARY KEY,
    txtPrefix            varchar(255),
    intNextNumber        int,
    txtSuffix            varchar(255)
);

CREATE INDEX tblIdSeq_intIdSeqId ON tblIdSeq ( intIdSeqId );


CREATE TABLE tblInvoice (
    lintAtomId                int            NOT NULL    CONSTRAINT FK_tblInvoice_lintAtomId_tblAtom_intAtomId REFERENCES tblAtom ( intAtomId ),
    dteCreated                datetime,
    lintResellerCompanyId    int                        CONSTRAINT FK_tblInvoice_lintResellerCompanyId_tblCompany_intCompanyId REFERENCES tblCompany ( intCompanyId ),
    txtMemo text
);

CREATE INDEX tblInvoice_lintAtomId                ON tblInvoice ( lintAtomId );
CREATE INDEX tblInvoice_lintResellerCompanyId    ON tblInvoice ( lintResellerCompanyId );


CREATE TABLE tblMedia (
    lintAtomId            int                NOT NULL CONSTRAINT FK_tblMedia_lintAtomId_tblAtom_intAtomId REFERENCES tblAtom ( intAtomId ),
    lintMediaTypeId        int                NOT NULL,
    txtTitle            varchar(255),
    txtAuthor            varchar(255),
    txtMemo                text
);

CREATE INDEX tblMedia_lintAtomId        ON tblMedia ( lintAtomId );
CREATE INDEX tblMedia_lintMediaTypeId    ON tblMedia ( lintMediaTypeId );


CREATE TABLE tblRegValue (
    intRegValueId        int                IDENTITY CONSTRAINT PK_tblRegValue_intRegValueId PRIMARY KEY,
    lintDetectId        int                NOT NULL CONSTRAINT FK_tblRegValue_lintDetectId_tblDetect_intDetectId REFERENCES tblDetect ( intDetectId ),
    lintRootRegKeyId    int                NOT NULL,
    txtPath                varchar(255),
    txtName                varchar(255),
    txtValue            varchar(255)
);

CREATE INDEX tblRegValue_intRegValueId        ON tblRegValue ( intRegValueId );
CREATE INDEX tblRegValue_lintDetectId        ON tblRegValue ( lintDetectId );
CREATE INDEX tblRegValue_lintRootRegKeyId    ON tblRegValue ( lintRootRegKeyId );


CREATE TABLE tblOsVersionInfo (
    lintDetectId        int                NOT NULL CONSTRAINT FK_tblOsVersionInfo_lintDetectId_tblDetect_intDetectId REFERENCES tblDetect ( intDetectId ),
    intMajorVersion        int,
    intMinorVersion        int,
    intBuildNumber        int,
    intPlatformId        int,
    txtCSDVersion        varchar(255),
    intServicePackMajor int,
    intServicePackMinor int,
    intSuiteMask        int,
    intProductType        int
);

CREATE INDEX tblOsVersionInfo_lintDetectId  ON tblOsVersionInfo ( lintDetectId );
CREATE INDEX tblOsVersionInfo_intPlatformId ON tblOsVersionInfo ( intPlatformId );


CREATE TABLE tblProductAtom (
    lintProductId        int                NOT NULL CONSTRAINT FK_tblProductAtom_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    lintAtomId            int                NOT NULL CONSTRAINT FK_tblProductAtom_lintAtomId_tblAtom_intAtomId REFERENCES tblAtom ( intAtomId )
);

CREATE INDEX tblProductAtom_lintProductId    ON tblProductAtom ( lintProductId );
CREATE INDEX tblProductAtom_lintAtomId        ON tblProductAtom ( lintAtomId );


CREATE TABLE tblProductFile (
    lintProductId        int                NOT NULL CONSTRAINT FK_tblProductFile_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    lintFileId            int                NOT NULL CONSTRAINT FK_tblProductFile_lintFileId_tblFile_intFileId REFERENCES tblFile ( intFileId ),
    bolUser                bit                NOT NULL
);

CREATE INDEX tblProductFile_lintProductId    ON tblProductFile ( lintProductId );
CREATE INDEX tblProductFile_lintFileId        ON tblProductFile ( lintFileId );


CREATE TABLE tblInstTrial (
    lintProductId        int                NOT NULL CONSTRAINT FK_tblInstTrial_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    lintComputerNodeId    int                NOT NULL CONSTRAINT FK_tblInstTrial_lintComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId )
);

CREATE INDEX tblInstTrial_lintProductId         ON tblInstTrial ( lintProductId );
CREATE INDEX tblInstTrial_lintComputerNodeId ON tblInstTrial ( lintComputerNodeId );


CREATE TABLE tblLicHist (
    intLicHistId            int            IDENTITY    CONSTRAINT PK_tblLicHist_intLicHistId PRIMARY KEY,
    lintProductId            int            NOT NULL    CONSTRAINT FK_tblLicHist_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    intCountChange            int                        <% If dbt <> dbtJet Then %>CONSTRAINT DF_tblLicHist_intCountChange<% End If %> DEFAULT 0,
    intMultiCountChange        int                        <% If dbt <> dbtJet Then %>CONSTRAINT DF_tblLicHist_intMultiCountChange<% End If %> DEFAULT 0,
    dteDate                    datetime    NOT NULL,
    dteDateExpire            datetime,
    txtMemo                    text,
    lintUserId                int                        CONSTRAINT FK_tblLicHist_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ),
    lintCancelLicHistId        int                        CONSTRAINT FK_tblLicHist_lintCancelId_tblLicHist_intLicHistId REFERENCES tblLicHist ( intLicHistId )
);

CREATE INDEX tblLicHist_intLicHistId            ON tblLicHist ( intLicHistId );
CREATE INDEX tblLicHist_lintProductId            ON tblLicHist ( lintProductId );
CREATE INDEX tblLicHist_lintUserId                ON tblLicHist ( lintUserId );
CREATE INDEX tblLicHist_lintCancelLicHistId    ON tblLicHist ( lintCancelLicHistId );


<% Call tblLicHistItem %>


CREATE TABLE tblLicHistAtom (
    lintLicHistId        int                NOT NULL CONSTRAINT FK_tblLicHistAtom_lintLicHistId_tblLicHist_intLicHistId REFERENCES tblLicHist ( intLicHistId ),
    lintAtomId            int                NOT NULL CONSTRAINT FK_tblLicHistAtom_lintAtomId_tblAtom_intAtomId REFERENCES tblAtom ( intAtomId )
);

CREATE INDEX tblLicHistAtom_lintLicHistId    ON tblLicHistAtom ( lintLicHistId );
CREATE INDEX tblLicHistAtom_lintAtomId        ON tblLicHistAtom ( lintAtomId );


CREATE TABLE tblInstHist (
    intInstHistId        int                IDENTITY    CONSTRAINT PK_tblInstHist_intInstHistId PRIMARY KEY,
    lintComputerNodeId    int                NOT NULL    CONSTRAINT FK_tblInstHist_lintComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ),
    lintProductId        int                NOT NULL    CONSTRAINT FK_tblInstHist_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    dteDate                datetime,
    lintUserId            int                            CONSTRAINT FK_tblInstHist_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ),
    txtMemo                text,
    intCount            int,
    lintLicHistItemId    int                            CONSTRAINT FK_tblInstHist_lintLicHistItemId_tblLicHistInst_intLicHistItemId REFERENCES tblLicHistItem ( intLicHistItemId )
);

CREATE INDEX tblInstHist_intInstHistId        ON tblInstHist ( intInstHistId );
CREATE INDEX tblInstHist_lintComputerNodeId ON tblInstHist ( lintComputerNodeId );
CREATE INDEX tblInstHist_lintProductId        ON tblInstHist ( lintProductId );
CREATE INDEX tblInstHist_lintUserId            ON tblInstHist ( lintUserId );
CREATE INDEX tblInstHist_lintLicHistItemId    ON tblInstHist ( lintLicHistItemId );


CREATE TABLE tblLicTrans (
    lintProductId            int            NOT NULL CONSTRAINT FK_tblLicTrans_lintProductId_tblProduct_intProductId REFERENCES tblProduct ( intProductId ),
    lintComputerNodeId        int            NOT NULL CONSTRAINT FK_tblLicTrans_lintComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId ),
    lintLicComputerNodeId    int            NOT NULL CONSTRAINT FK_tblLicTrans_lintLicComputerNodeId_tblNode_intNodeId REFERENCES tblNode ( intNodeId )
);

CREATE INDEX tblLicTrans_lintProductId            ON tblLicTrans ( lintProductId );
CREATE INDEX tblLicTrans_lintComputerNodeId        ON tblLicTrans ( lintComputerNodeId );
CREATE INDEX tblLicTrans_lintLicComputerNodeId    ON tblLicTrans ( lintLicComputerNodeId );




CREATE VIEW vProduct
AS
    SELECT
        tblProduct.intProductId                AS intProductId,
        tblProduct.txtName                    AS txtName,
        tblProduct.lintLangId                AS lintLangId,
        tblProduct.lintProducerCompanyId    AS lintProducerCompanyId,
        tblProduct.lintProductTypeId        AS lintProductTypeId,
        tblProduct.lintProductCategoryId    AS lintProductCategoryId,
        tblProduct.bolUser                    AS bolUser
    FROM
        tblProduct
        INNER JOIN
            tblSetting
        ON tblProduct.intSimple = tblSetting.intValue
    WHERE tblSetting.txtKey='SwSimple'
;


CREATE VIEW vComputer
AS
    SELECT
        tblNode.intNodeId        AS intComputerNodeId,
        tblNode.lintParentId    AS lintParentId,
        tblNode.lintIconId        AS lintIconId,
        tblNode.intState        AS intState,
        tblNode.txtName            AS txtName,
        tblNode.bolHidden        AS bolHidden
    FROM
        tblNode
        INNER JOIN (
            tblSetting
            INNER JOIN (
                tblProperty INNER JOIN
                    tblSetting AS tblSetting_1
                ON tblProperty.txtValue = tblSetting_1.txtValue)
            ON tblSetting.intValue = tblProperty.lintKindId)
        ON tblNode.intNodeId = tblProperty.lintNodeId
    WHERE tblSetting_1.txtKey='ClassComputer' AND tblSetting.txtKey='ClassKind'
;


CREATE VIEW vComputerUnit
AS
    SELECT
        tblNode.intNodeId        AS intComputerUnitNodeId,
        tblNode.lintParentId    AS lintParentId,
        tblNode.lintIconId        AS lintIconId,
        tblNode.intState        AS intState,
        tblNode.txtName            AS txtName,
        tblNode.bolHidden        AS bolHidden
    FROM
        tblNode
        INNER JOIN (
            tblSetting
            INNER JOIN (
                tblProperty INNER JOIN
                    tblSetting AS tblSetting_1
                ON tblProperty.txtValue = tblSetting_1.txtValue)
            ON tblSetting.intValue = tblProperty.lintKindId)
        ON tblNode.intNodeId = tblProperty.lintNodeId
    WHERE tblSetting_1.txtKey='ClassComputerUnit' AND tblSetting.txtKey='ClassKind'
;


CREATE VIEW vInstDetect
AS
    SELECT DISTINCT
        tblDetect.intDetectId        AS lintDetectId,
        tblNode.intNodeId            AS lintComputerNodeId,
        tblDetFile.lintProductId    AS lintProductId
    FROM (
        tblNode
        INNER JOIN
            tblDetect
        ON tblNode.intNodeId = tblDetect.lintComputerNodeId)
        INNER JOIN
            tblDetFile
        ON tblDetect.intDetectId = tblDetFile.lintDetectId
    WHERE tblDetFile.lintProductId IS NOT NULL
;


CREATE VIEW vProductComputerBase
AS <% Call vProductComputerBase %>;


CREATE VIEW vProductComputer
AS
    SELECT
        vProductComputerBase.lintProductId            AS lintProductId,
        vProductComputerBase.lintComputerNodeId        AS lintComputerNodeId,
        tblLicTrans.lintLicComputerNodeId            AS lintLicComputerNodeId
    FROM
        vProductComputerBase
        LEFT JOIN
            tblLicTrans
        ON vProductComputerBase.lintComputerNodeId = tblLicTrans.lintComputerNodeId AND vProductComputerBase.lintProductId = tblLicTrans.lintProductId
;


CREATE VIEW vProductComputerDetect
AS
    SELECT
        tblDetect.intDetectId        AS lintDetectId,
        tblNode.intNodeId            AS lintComputerNodeId,
        vProduct.intProductId        AS lintProductId,
        tblNode.txtName                AS txtComputer,
        vProduct.txtName            AS txtProduct,
        vProduct.lintLangId            AS lintLangId
    FROM
        tblDetect
        INNER JOIN (
            vProduct
            INNER JOIN (
                tblNode
                INNER JOIN
                    vProductComputer
                ON tblNode.intNodeId = vProductComputer.lintComputerNodeId)
            ON vProduct.intProductId = vProductComputer.lintProductId)
        ON tblDetect.lintComputerNodeId = tblNode.intNodeId
;


CREATE VIEW vInstDetectAdditional
AS
    SELECT
        vProductComputerDetect.lintDetectId            AS lintDetectId,
        vProductComputerDetect.lintComputerNodeId    AS lintComputerNodeId,
        vProductComputerDetect.lintProductId        AS lintProductId,
        vProductComputerDetect.txtComputer            AS txtComputer,
        vProductComputerDetect.txtProduct            AS txtProduct,
        vProductComputerDetect.lintLangId            AS lintLangId
    FROM
        vProductComputerDetect
        LEFT JOIN
            vInstDetect
        ON (vProductComputerDetect.lintDetectId = vInstDetect.lintDetectId) AND (vProductComputerDetect.lintProductId = vInstDetect.lintProductId)
    WHERE vInstDetect.lintDetectId IS NULL
;


CREATE VIEW vInstDetectMissing
AS
    SELECT
        vInstDetect.lintDetectId            AS lintDetectId,
        vInstDetect.lintComputerNodeId        AS lintComputerNodeId,
        vProduct.intProductId                AS lintProductId,
        tblNode.txtName                        AS txtComputer,
        vProduct.txtName                    AS txtProduct,
        vProduct.lintLangId                    AS lintLangId
    FROM ((
        vProduct
        INNER JOIN
            vInstDetect
        ON vProduct.intProductId = vInstDetect.lintProductId)
        LEFT JOIN
            vProductComputer
        ON (vInstDetect.lintComputerNodeId = vProductComputer.lintComputerNodeId AND vInstDetect.lintProductId = vProductComputer.lintProductId))
        INNER JOIN
            tblNode
        ON vInstDetect.lintComputerNodeId = tblNode.intNodeId
    WHERE <% If dbt = dbtJet Then %>vProductComputer.lintComputerNodeId IS NULL
          <% Else %>ISNULL(vProductComputer.lintComputerNodeId,0) <> vInstDetect.lintComputerNodeId
          <% End If %>
;


CREATE VIEW vLic1
AS <% Call vLic1 %>;


CREATE VIEW vLic2
AS <% Call vLic2 %>;


CREATE VIEW vLic2Count
AS
    SELECT
        vLic2.lintProductId        AS lintProductId,
        COUNT(*)                AS intCount
    FROM vLic2
    GROUP BY lintProductId
;


CREATE VIEW vInvoice
AS
    SELECT
        tblInvoice.lintAtomId    AS lintAtomId,
        tblCompany.txtName        AS txtCompany
    FROM
        tblCompany
        RIGHT JOIN
            tblInvoice
        ON tblCompany.intCompanyId = tblInvoice.lintResellerCompanyId
;

CREATE VIEW vInstHist
AS <% Call vInstHist %>;


CREATE VIEW vInstTrialCount
AS
    SELECT
        tblInstTrial.lintProductId                AS lintProductId,
        COUNT(tblInstTrial.lintComputerNodeId) AS intCount
    FROM
        tblInstTrial
        INNER JOIN
            vProductComputer
        ON tblInstTrial.lintComputerNodeId = vProductComputer.lintComputerNodeId AND tblInstTrial.lintProductId = vProductComputer.lintProductId
    GROUP BY tblInstTrial.lintProductId
;


CREATE VIEW vLicHistAsset
AS <% Call vLicHistAsset %>;


CREATE VIEW vLicHist
AS <% Call vLicHist %>;


CREATE VIEW vLicHistItem
AS <% Call vLicHistItem %>;


CREATE VIEW vLicInst
AS
    SELECT
        vProductComputer.lintProductId            AS lintProductId,
        vProductComputer.lintComputerNodeId        AS lintComputerNodeId
    FROM vProductComputer
    WHERE lintLicComputerNodeId IS NULL
;


CREATE VIEW vLicTrans
AS
    SELECT
        tblLicTrans.lintProductId            AS lintProductId,
        tblLicTrans.lintComputerNodeId        AS lintComputerNodeId,
        tblNode.txtName                        AS txtLicComputer
    FROM
        tblLicTrans
        INNER JOIN
            tblNode
        ON tblLicTrans.lintLicComputerNodeId = tblNode.intNodeId
;



CREATE VIEW vProductAllComputers
AS <% Call vProductAllComputers %>;


CREATE VIEW vProductInstCount
AS
    SELECT
        vProductComputer.lintProductId        AS lintProductId,
        COUNT(*)                            AS intCount
    FROM vProductComputer
    GROUP BY lintProductId
;


CREATE VIEW vProductInstNotebootCount
AS
    SELECT
        vProductComputer.lintProductId        AS lintProductId,
        Count(*)                            AS intCount
    FROM
        vProductComputer
        LEFT JOIN
            tblInstTrial
        ON vProductComputer.lintComputerNodeId = tblInstTrial.lintComputerNodeId AND vProductComputer.lintProductId = tblInstTrial.lintProductId
    WHERE vProductComputer.lintLicComputerNodeId IS NOT NULL AND tblInstTrial.lintComputerNodeId IS NULL
    GROUP BY vProductComputer.lintProductId
;


CREATE VIEW vProductInstOEMCount
AS
    SELECT
        vProductComputer.lintProductId        AS lintProductId,
        COUNT(*)                            AS intCount
    FROM vProductComputer, vLic2
    WHERE vLic2.lintProductId=vProductComputer.lintProductId AND vLic2.lintComputerNodeId= vProductComputer.lintComputerNodeId
    GROUP BY vProductComputer.lintProductId
;


CREATE VIEW vLicAudit
AS <% Call vLicAudit %>;


CREATE VIEW vSWComputersBase
AS
    SELECT
        PC.lintProductId            AS lintProductId,
        N.intComputerNodeId            AS lintComputerNodeId,
        N.txtName                    AS txtComputer,
        NUP.txtName                    AS txtParent
    FROM
        vProductComputer AS PC
        INNER JOIN (
            vComputer AS N
            LEFT JOIN
                tblNode AS NUP
            ON NUP.intNodeId=N.lintParentId)
        ON N.intComputerNodeId=PC.lintComputerNodeId
;


CREATE VIEW vSWComputers
AS
    SELECT DISTINCT
        vSWComputersBase.lintProductId            AS lintProductId,
        vSWComputersBase.lintComputerNodeId        AS lintComputerNodeId,
        vSWComputersBase.txtComputer            AS txtComputer,
        tblInstTrial.lintProductId                AS lintTrial,
        vLicTrans.txtLicComputer                AS txtLicComputer,
        vSWComputersBase.txtParent                AS txtParent
    FROM
        vLicTrans
        RIGHT JOIN (
            tblInstTrial
            RIGHT JOIN
                vSWComputersBase
            ON tblInstTrial.lintComputerNodeId = vSWComputersBase.lintComputerNodeId AND tblInstTrial.lintProductId = vSWComputersBase.lintProductId)
        ON vLicTrans.lintComputerNodeId = vSWComputersBase.lintComputerNodeId AND vLicTrans.lintProductId = vSWComputersBase.lintProductId
;


CREATE VIEW vDetectUnassigned
AS
    SELECT
        tblDetFile.lintDetectId        AS lintDetectId,
        COUNT(*)                    AS UnassignedCount
    FROM tblDetFile
    WHERE lintProductId IS NULL
    GROUP BY tblDetFile.lintDetectId
;


CREATE VIEW vDetProduct
AS
    SELECT DISTINCT
        lintDetectId,
        lintProductId
    FROM tblDetFile
;


CREATE VIEW vDetFolderProduct
AS
    SELECT DISTINCT
        lintDetectId,
        txtFolderPath,
        lintProductId
    FROM tblDetFile
;


CREATE VIEW vFilesFromPath
AS
    SELECT
        intFileId,
        tblFile.txtName                    AS txtFileName,
        txtExt,
        intSize,
        tblFileStringInfo.lintFileId    AS lintFileStringInfoFileId,
        intLanguage,
        intCodePage,
        txtComments,
        txtCompanyName,
        txtFileDescription,
        txtFileVersion,
        txtInternalName,
        txtLegalCopyright,
        txtLegalTrademarks,
        txtOriginalFilename,
        txtProductName,
        txtProductVersion,
        txtPrivateBuild,
        txtSpecialBuild,
        tblFileVerInfo.lintFileId        AS lintFileVerInfoFileId,
        intFileVersionMS,
        intFileVersionLS,
        intProductVersionMS,
        intProductVersionLS,
        intFileFlagsMask,
        intFileFlags,
        intFileOS,
        intFileType,
        intFileSubtype,
        intFileDateMS,
        intFileDateLS,
        intDetFileId,
        tblDetFile.lintFileId            AS lintDetFileFileId,
        lintDetectId,
        txtFolderPath,
        dteCreated,
        dteModified,
        dteAccessed,
        intAttribs,
        lintProductId,
        intProductId,
        vProduct.txtName                AS txtvProductName,
        lintLangId,
        lintProducerCompanyId,
        lintProductTypeId,
        lintProductCategoryId,
        bolUser
    FROM ((
        tblFile
        INNER JOIN (
            vProduct
            RIGHT JOIN
                tblDetFile
            ON vProduct.intProductId = tblDetFile.lintProductId)
        ON tblFile.intFileId = tblDetFile.lintFileId)
        LEFT JOIN
            tblFileStringInfo
        ON tblFile.intFileId = tblFileStringInfo.lintFileId)
        LEFT JOIN
            tblFileVerInfo
        ON tblFile.intFileId = tblFileVerInfo.lintFileId
;


CREATE VIEW vImportFile
AS
    SELECT
        tblFile.intFileId        AS lintFileId,
        FI.intFileImportId        AS lintFileImportId
    FROM (
        tblFileImport AS FI
        INNER JOIN
            tblFile
        ON (FI.intSize = tblFile.intSize) AND (FI.txtExt = tblFile.txtExt) AND (FI.txtName = tblFile.txtName))
        LEFT JOIN
            tblFileVerInfo AS FVI
        ON tblFile.intFileId = FVI.lintFileId
    WHERE (FI.intFileVersionMS IS NULL AND FI.lintFileId IS NULL) OR (FI.intFileVersionMS=FVI.intFileVersionMS AND FI.intFileVersionLS=FVI.intFileVersionLS AND FI.intProductVersionMS=FVI.intProductVersionMS AND FI.intProductVersionLS=FVI.intProductVersionLS)
;


CREATE VIEW vRegPathGroup
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath
    FROM tblRegValue
    GROUP BY lintDetectId, lintRootRegKeyId, txtPath
;


CREATE VIEW vRegDisplayName
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtDisplayName, intRegValueId
    FROM tblRegValue
    WHERE txtName='DisplayName'
;


CREATE VIEW vRegDisplayVersion
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtDisplayVersion
    FROM tblRegValue
    WHERE tblRegValue.txtName='DisplayVersion'
;


CREATE VIEW vRegInstallDate
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtInstallDate
    FROM tblRegValue
    WHERE tblRegValue.txtName='InstallDate'
;


CREATE VIEW vRegLanguage
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtLanguage
    FROM tblRegValue
    WHERE tblRegValue.txtName='Language'
;


CREATE VIEW vRegProductId
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtProductId
    FROM tblRegValue
    WHERE tblRegValue.txtName='ProductId'
;


CREATE VIEW vRegPublisher
AS
    SELECT lintDetectId, lintRootRegKeyId, txtPath, txtValue AS txtPublisher
    FROM tblRegValue
    WHERE tblRegValue.txtName='Publisher'
;


CREATE VIEW vSwInstLast
AS <% Call vSwInstLast %>;


INSERT INTO tblIdSeq (intNextNumber) VALUES (0);
INSERT INTO tblIdSeq (intNextNumber) VALUES (0);
<%
End Sub
%>


<%
Sub UpgradeDbX_v4v5
%>
ALTER TABLE tblNode
    ADD bolHidden bit;


ALTER TABLE tblSetting
    ADD intValue int;
<%
End Sub
%>


<%
Sub UpgradeDbX_7a8
%>
DROP VIEW vProductAllComputers;
CREATE VIEW vProductAllComputers
AS <% Call vProductAllComputers %>;


DROP VIEW vProductComputerBase;
CREATE VIEW vProductComputerBase
AS <% Call vProductComputerBase %>;


<% If dbt <> dbtJet Then %>
    ALTER VIEW vLicAudit
    AS <% Call vLicAudit %>;
<% End If %>


CREATE VIEW vSwInstLast
AS SELECT * FROM tblInstHist;

<%
End Sub
%>


<%
Sub UpgradeDbX_z9na10
%>

<% Call tblLicHistItem %>

ALTER TABLE tblInstHist
    ADD    lintLicHistItemId    int        CONSTRAINT FK_tblInstHist_lintLicHistItemId_tblLicHistInst_intLicHistItemId REFERENCES tblLicHistItem ( intLicHistItemId );

CREATE INDEX tblInstHist_lintLicHistItemId ON tblInstHist ( lintLicHistItemId );


ALTER TABLE tblLicHist
    ADD    dteDateExpire            datetime,
        lintUserId                int            CONSTRAINT FK_tblLicHist_lintUserId_tblUser_intUserId REFERENCES tblUser ( intUserId ),
        lintCancelLicHistId        int            CONSTRAINT FK_tblLicHist_lintCancel_tblLicHist_intLicHistId REFERENCES tblLicHist ( intLicHistId );


CREATE INDEX tblLicHist_lintUserId            ON tblLicHist ( lintUserId );
CREATE INDEX tblLicHist_lintCancelLicHistId    ON tblLicHist ( lintCancelLicHistId );


DROP VIEW vLic1;
CREATE VIEW vLic1
AS <% Call vLic1 %>;


DROP VIEW vLic2;
CREATE VIEW vLic2
AS <% Call vLic2 %>;


DROP VIEW vLicHistAsset;
CREATE VIEW vLicHistAsset
AS <% Call vLicHistAsset %>;

DROP VIEW vLicHist;
CREATE VIEW vLicHist
AS <% Call vLicHist %>;

CREATE VIEW vLicHistItem
AS <% Call vLicHistItem %>;

DROP VIEW vInstHist;
CREATE VIEW vInstHist
AS <% Call vInstHist %>;

DROP VIEW vSwInstLast;
CREATE VIEW vSwInstLast
AS <% Call vSwInstLast %>;

<%
End Sub
%>



<%
Sub Init
    Dim s
    dbt=dbtMsSql
    If Not IsEmpty(con) Then
        s=LCase(con.ConnectionString)
        If InStr(s,"microsoft.jet.oledb.") > 0 Then
            dbt=dbtJet
        End If
    End If
End Sub


' Main
Call Init
Execute "Call " & param("fnc")
%>