Laserfiche WCC Integration SQL Queries

Integration Application

ReportTotalRemainingDocs

getRemainingDocs:
SELECT DISTINCT TOC.tocid,pagecount, (SELECT str_val FROM propval WHERE prop_id =14 AND propval.tocid =TOC.tocid) as CR_NUMBER, (SELECT str_val FROM propval WHERE prop_id =27 AND propval.tocid =TOC.tocid) as MLTR_NUMBER FROM TOC,DOC WHERE etype <> 0 and TOC.tocid=DOC.tocid and DOC.pagenum not in (select WCC_PAGE_NUM FROM WCC_DOCS_MIGRATION where WCC_TOC_ID =TOC.tocid)

getArchivedPages:
SELECT COUNT(*) as WCC_PAGES_NUM FROM WCC_DOCS_MIGRATION WHERE WCC_STATUS=0 OR WCC_STATUS=1

getSentForReviewPages:
SELECT COUNT(*) as SENT_FOR_REVIEW_PAGES_NUM FROM WCC_DOCS_MIGRATION WHERE WCC_STATUS=2

getArchDocsNum:
SELECT DISTINCT MainTable.tocid,pagecount, (SELECT str_val FROM propval WHERE prop_id =14 AND propval.tocid =MainTable.tocid) as CR_NUMBER, (SELECT str_val FROM propval WHERE prop_id =27 AND propval.tocid =MainTable.tocid) as MILITARY_NUMBER FROM toc MainTable,WCC_DOCS_MIGRATION, DOC  WHERE etype <> 0 AND MainTable.tocid=WCC_TOC_ID and DOC.pagenum in (select WCC_PAGE_NUM FROM WCC_DOCS_MIGRATION where WCC_TOC_ID =MainTable.tocid AND (WCC_STATUS=1 OR WCC_STATUS=0))

getArchDocsNumTimoutError:
SELECT DISTINCT MainTable.tocid,pagecount, (SELECT str_val FROM propval WHERE prop_id =14 AND propval.tocid =MainTable.tocid) as CR_NUMBER, (SELECT str_val FROM propval WHERE prop_id =27 AND propval.tocid =MainTable.tocid) as MILITARY_NUMBER, (SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID = MainTable.tocid AND (WCC_STATUS=1 OR WCC_STATUS=0)) as WCC_PAGES_NUM, (SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID = MainTable.tocid AND (WCC_STATUS=2)) as SENT_FOR_REVIEW_PAGES_NUM FROM toc MainTable,WCC_DOCS_MIGRATION  WHERE etype <> 0 AND MainTable.tocid=WCC_TOC_ID

getTotalDocsNum:
SELECT pagecount FROM toc WHERE etype <> 0

getTotalDocsNumWith
SELECT COUNT(*) as TOTAL_DOCS FROM toc as tocTable, propval as propTable WHERE etype <> 0 AND propTable.tocid = tocTable.tocid AND prop_id =" & propId & " AND str_val is not null

getArchivedPagesByTocID:
SELECT COUNT(*) as ArchPages FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =" & tocID,


ReportProductionFrm

Print:
select CHECKED_BY, (select COUNT(*) from (select checked_by,WCC_ID from dbo.WCC_DOCS_MIGRATION where (WCC_STATUS=1 OR WCC_STATUS=0) and CONVERT(datetime,checked_date,103)>=CONVERT(datetime,'" & txtFromDate.Text & "  00:00',103) and CONVERT(datetime,checked_date,103)<=CONVERT(datetime,'" & txtTotDate.Text & " 23:59',103) and CHECKED_BY=MAIN_TABLE.CHECKED_BY group by checked_by,WCC_ID) as ARCH_COUNT) as NUM_DOCUMENTS  from dbo.WCC_DOCS_MIGRATION as MAIN_TABLE where (WCC_STATUS=1 OR WCC_STATUS=0) and CONVERT(datetime,checked_date,103)>=CONVERT(datetime,'" & txtFromDate.Text & "  00:00',103) and CONVERT(datetime,checked_date,103)<=CONVERT(datetime,'" & txtTotDate.Text & " 23:59',103) group by CHECKED_BY

SELECT COUNT(CHECKED_BY) as NUM_PAGES FROM WCC_DOCS_MIGRATION WHERE (WCC_STATUS=1 OR WCC_STATUS=0) and CHECKED_BY=' dataSet.Tables(0).Rows(i)("CHECKED_BY") & "' and CONVERT(datetime,checked_date,103)>=CONVERT(datetime,'" & txtFromDate.Text & "  00:00',103) and CONVERT(datetime,checked_date,103)<=CONVERT(datetime,'" & txtTotDate.Text & " 23:59',103)

ReportArchDocsFrms
SELECT WCC_TOC_ID FROM WCC_DOCS_MIGRATION WHERE WCC_STATUS=1 AND CHECKED_BY='" & cmbUserName.SelectedValue &"' AND CONVERT(datetime,checked_date,103)>=CONVERT(datetime,'" & txtFromDate.Text &
"  00:00',103) and CONVERT(datetime,checked_date,103)<=CONVERT(datetime,'" & txtToDate.Text & " 23:59',103) GROUP BY WCC_TOC_ID

SELECT USER_ID, userFullName FROM WCC_USER Order by userFullName ASC

MappingOptionsFrm

MappingOptionsFrm_Load:
SELECT WCC_MAPPING_ID, WCC_MAPPING_SET_ID, WCC_MAPPING_SET, WCC_MAPPING_Type, WCC_MAPPING_PATH, WCC_MAPPING_dDocType, " & "WCC_MAPPING_xClassification, WCC_MAPPING_xServiceType FROM WCC_DOC_TYPES_MAPPING " & setFilterCondition & " order by WCC_MAPPING_ID


Globals

fillDocumentSetCombobox:
SELECT pset_id, pset_name FROM propset ORDER BY pset_id ASC

Form1

GetDataView

        Dim cmdstr As String = "SELECT Top " & recoedsCountsTxt.Value & " tocid,parentid,pagecount"
        If UserType = 1 Then 'normal user
            cmdstr += ",MIGRATED_PAGES_NUM FROM WCC_DOCS WHERE pagecount > MIGRATED_PAGES_NUM "
        Else 'reviewrs
            cmdstr += " FROM WCC_DOCS as tocTable, WCC_DOCS_MIGRATION WHERE tocid=WCC_TOC_ID AND WCC_STATUS=2 "
        End If

'If only the Doc Type selected from the filter
        If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & " tocTable.tocid, parentid,pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += ",MIGRATED_PAGES_NUM FROM WCC_DOCS as tocTable WHERE pagecount > MIGRATED_PAGES_NUM "
            Else 'reviewrs
                cmdstr += "FROM WCC_DOCS as tocTable, WCC_DOCS_MIGRATION WHERE tocTable.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If
            cmdstr &= " AND prop_id= " & getPropIdFromSetCombo() & " AND str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' "
        End If

'Advanced Filters
        If txtCPRNumber.Text <> "" Or txtMilitaryNumber.Text <> "" Or txtEmployeeName.Text <> "" Then 'Or (cmbDocType.SelectedIndex <> -1 And Not IsDBNull(cmbDocType.SelectedValue))
            Dim propIdToFilter As String = ""
            Dim valuesToCompare As String = ""


            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & " tocTable.tocid, parentid,pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += ",MIGRATED_PAGES_NUM FROM WCC_DOCS as tocTable WHERE pagecount > MIGRATED_PAGES_NUM "
            Else 'reviewrs
                cmdstr += "FROM WCC_DOCS as tocTable, WCC_DOCS_MIGRATION WHERE tocTable.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If

            If txtCPRNumber.Text <> "" Then
                propIdToFilter &= "14,"
                valuesToCompare &= " str_val='" & txtCPRNumber.Text.Trim & "' OR"
            End If

            If txtMilitaryNumber.Text <> "" Then
                propIdToFilter &= "27,"
                valuesToCompare &= " str_val='" & txtMilitaryNumber.Text.Trim & "' OR"
            End If
            If txtEmployeeName.Text <> "" Then
                propIdToFilter &= "13,"
                valuesToCompare &= " str_val like N'%" & txtEmployeeName.Text.Trim & "%' OR"
            End If
            If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
                propIdToFilter &= getPropIdFromSetCombo() & ","
                valuesToCompare &= " str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' OR"
            End If

            propIdToFilter = propIdToFilter.Substring(0, propIdToFilter.Length - 1)
            valuesToCompare = valuesToCompare.Substring(0, valuesToCompare.Length - 2)

            cmdstr += "AND prop_id in (" & propIdToFilter & ") AND (" & valuesToCompare & ")"
        End If
'Doc Set
        If cmbDocSet.SelectedIndex <> -1 Then cmdstr &= " AND pset_id=" & cmbDocSet.SelectedValue
        cmdstr &= " AND created > CONVERT(datetime,'" & HideDateGlobal & "',103)"
        If UserType <> 1 Then cmdstr += " group by tocTable.tocid,parentid,pagecount "

getData:

Dim cmdstr As String = "SELECT DISTINCT Top " & recoedsCountsTxt.Value & " TOC.tocid,TOC.parentid,TOC.pagecount "

        If UserType = 1 Then 'normal user
            cmdstr += " FROM TOC,DOC WHERE etype <> 0 and TOC.tocid=DOC.tocid and DOC.pagenum not in (select WCC_PAGE_NUM FROM WCC_DOCS_MIGRATION where WCC_TOC_ID =TOC.tocid) "
        Else 'reviewrs
            cmdstr += " FROM TOC, WCC_DOCS_MIGRATION WHERE etype <> 0 AND tocid=WCC_TOC_ID AND WCC_STATUS=2 "
        End If


        'If only the Doc Type selected from the filter
        If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & "  TOC.tocid,TOC.parentid,TOC.pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += " FROM TOC,DOC, propval as propTable WHERE etype <> 0 AND propTable.tocid = TOC.tocid  and DOC.pagenum not in (select WCC_PAGE_NUM FROM WCC_DOCS_MIGRATION where WCC_TOC_ID =TOC.tocid) and TOC.tocid=DOC.tocid"
            Else 'reviewrs
                cmdstr += " FROM TOC, propval as propTable, WCC_DOCS_MIGRATION WHERE etype <> 0 AND propTable.tocid = TOC.tocid " & _
                            "AND TOC.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If
            cmdstr &= " AND prop_id= " & getPropIdFromSetCombo() & " AND str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' "
        End If


        'Advanced Filters
        If txtCPRNumber.Text <> "" Or txtMilitaryNumber.Text <> "" Or txtEmployeeName.Text <> "" Then 'Or (cmbDocType.SelectedIndex <> -1 And Not IsDBNull(cmbDocType.SelectedValue))
            Dim propIdToFilter As String = ""
            Dim valuesToCompare As String = ""

            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & " TOC.tocid,TOC.parentid,TOC.pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += " FROM TOC,DOC, propval as propTable WHERE etype <> 0 AND propTable.tocid = TOC.tocid  and DOC.pagenum not in (select WCC_PAGE_NUM FROM WCC_DOCS_MIGRATION where WCC_TOC_ID =TOC.tocid) and TOC.tocid=DOC.tocid "
            Else 'reviewrs
                cmdstr += " FROM TOC, propval as propTable, WCC_DOCS_MIGRATION WHERE etype <> 0 AND propTable.tocid = TOC.tocid " & _
                            "AND TOC.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If

            If txtCPRNumber.Text <> "" Then
                propIdToFilter &= "14,"
                valuesToCompare &= " str_val='" & txtCPRNumber.Text.Trim & "' OR"
            End If

            If txtMilitaryNumber.Text <> "" Then
                propIdToFilter &= "27,"
                valuesToCompare &= " str_val='" & txtMilitaryNumber.Text.Trim & "' OR"
            End If
            If txtEmployeeName.Text <> "" Then
                propIdToFilter &= "13,"
                valuesToCompare &= " str_val like N'%" & txtEmployeeName.Text.Trim & "%' OR"
            End If
            If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
                propIdToFilter &= getPropIdFromSetCombo() & ","
                valuesToCompare &= " str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' OR"
            End If

            propIdToFilter = propIdToFilter.Substring(0, propIdToFilter.Length - 1)
            valuesToCompare = valuesToCompare.Substring(0, valuesToCompare.Length - 2)

            cmdstr += "AND prop_id in (" & propIdToFilter & ") AND (" & valuesToCompare & ")"
        End If

        'Doc Set
        If cmbDocSet.SelectedIndex <> -1 Then cmdstr &= " AND pset_id=" & cmbDocSet.SelectedValue
        cmdstr &= " AND created > CONVERT(datetime,'" & HideDateGlobal & "',103)"
        If UserType <> 1 Then cmdstr += " group by TOC.tocid,parentid,pagecount "

getDataSELECT_COUNT:

Dim cmdstr As String = "SELECT Top " & recoedsCountsTxt.Value & " tocid,parentid,pagecount"

        If UserType = 1 Then 'normal user
            cmdstr += ",(SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocid) as WCC_PAGES_NUM  FROM toc WHERE etype <> 0 AND pagecount > (SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocid)"
        Else 'reviewrs
            cmdstr += " FROM toc as tocTable, WCC_DOCS_MIGRATION WHERE etype <> 0 AND tocid=WCC_TOC_ID AND WCC_STATUS=2 "
        End If


        'If only the Doc Type selected from the filter
        If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & " tocTable.tocid, parentid,pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += ",(SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocTable.tocid) as WCC_PAGES_NUM " & _
                            "FROM toc as tocTable, propval as propTable WHERE etype <> 0 AND propTable.tocid = tocTable.tocid AND pagecount > (SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocTable.tocid) "
            Else 'reviewrs
                cmdstr += "FROM toc as tocTable, propval as propTable, WCC_DOCS_MIGRATION WHERE etype <> 0 AND propTable.tocid = tocTable.tocid " & _
                            "AND tocTable.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If
            cmdstr &= " AND prop_id= " & getPropIdFromSetCombo() & " AND str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' "
        End If


        'Advanced Filters
        If txtCPRNumber.Text <> "" Or txtMilitaryNumber.Text <> "" Or txtEmployeeName.Text <> "" Then 'Or (cmbDocType.SelectedIndex <> -1 And Not IsDBNull(cmbDocType.SelectedValue))
            Dim propIdToFilter As String = ""
            Dim valuesToCompare As String = ""

            cmdstr = "SELECT distinct Top " & recoedsCountsTxt.Value & " tocTable.tocid, parentid,pagecount " '
            If UserType = 1 Then 'normal user
                cmdstr += ",(SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocTable.tocid) as WCC_PAGES_NUM " & _
                            "FROM toc as tocTable, propval as propTable WHERE etype <> 0 AND propTable.tocid = tocTable.tocid AND pagecount > (SELECT COUNT(*) FROM WCC_DOCS_MIGRATION WHERE WCC_TOC_ID =tocTable.tocid) "
            Else 'reviewrs
                cmdstr += "FROM toc as tocTable, propval as propTable, WCC_DOCS_MIGRATION WHERE etype <> 0 AND propTable.tocid = tocTable.tocid " & _
                            "AND tocTable.tocid=WCC_TOC_ID AND WCC_STATUS=2 "
            End If

            If txtCPRNumber.Text <> "" Then
                propIdToFilter &= "14,"
                valuesToCompare &= " str_val='" & txtCPRNumber.Text.Trim & "' OR"
            End If

            If txtMilitaryNumber.Text <> "" Then
                propIdToFilter &= "27,"
                valuesToCompare &= " str_val='" & txtMilitaryNumber.Text.Trim & "' OR"
            End If
            If txtEmployeeName.Text <> "" Then
                propIdToFilter &= "13,"
                valuesToCompare &= " str_val like N'%" & txtEmployeeName.Text.Trim & "%' OR"
            End If
            If Not IsDBNull(cmbDocType.SelectedValue) AndAlso cmbDocType.SelectedIndex <> -1 Then
                propIdToFilter &= getPropIdFromSetCombo() & ","
                valuesToCompare &= " str_val like N'%" & cmbDocType.SelectedValue.Trim & "%' OR"
            End If

            propIdToFilter = propIdToFilter.Substring(0, propIdToFilter.Length - 1)
            valuesToCompare = valuesToCompare.Substring(0, valuesToCompare.Length - 2)

            cmdstr += "AND prop_id in (" & propIdToFilter & ") AND (" & valuesToCompare & ")"
        End If

        'Doc Set
        If cmbDocSet.SelectedIndex <> -1 Then cmdstr &= " AND pset_id=" & cmbDocSet.SelectedValue
        cmdstr &= " AND created > CONVERT(datetime,'" & HideDateGlobal & "',103)"
        If UserType <> 1 Then cmdstr += " group by tocTable.tocid,parentid,pagecount "


DocumentInfo

assignMainData:

Case 1 'General
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =1 AND tocid = " & tocId & ") as document " & _
" FROM propval", _
sqlConnection)
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("document")) Then docName = reader("document")
End If
reader.Close()
'New Meta Data
dDocTitle = tocName
xReferenceNumber = docName


Case 2 'النموذج الرئيسي
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =13 AND tocid = " & tocId & ") as name, " & _
"(SELECT str_val FROM propval WHERE prop_id =14 AND tocid = " & tocId & ") as CPR, " & _
"(SELECT str_val FROM propval WHERE prop_id =15 AND tocid = " & tocId & ") as doc_loc, " & _
"(SELECT str_val FROM propval WHERE prop_id =16 AND tocid = " & tocId & ") as doc_type " & _
" FROM propval", _
sqlConnection)
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("name")) Then empName = reader("name")
If Not IsDBNull(reader("CPR")) Then cprNum = reader("CPR")
If Not IsDBNull(reader("doc_loc")) Then docLoc = reader("doc_loc")
If Not IsDBNull(reader("doc_type")) Then docType = reader("doc_type")
End If
reader.Close()
'New Meta Data
xCPRNumber = cprNum 'bilal throw log error if cprNum and docName not the same
dDocTitle = empName 'bilal empName could be null.. wt to do?


Case 3, 6 'مستند الدفع + مستند دفع2
Dim tmpDocDatePropId As String
Dim tmpAmountPropId As String
If psetId = 3 Then
tmpDocDatePropId = "18"
tmpAmountPropId = "20"
Else
tmpDocDatePropId = "28"
tmpAmountPropId = "29"
End If

Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT num_val FROM propval WHERE prop_id =22 AND tocid = " & tocId & ") as payment_num, " & _
"(SELECT str_val FROM propval WHERE prop_id =13 AND tocid = " & tocId & ") as emp_name, " & _
"(SELECT str_val FROM propval WHERE prop_id =14 AND tocid = " & tocId & ") as crp_num, " & _
"(SELECT str_val FROM propval WHERE prop_id =21 AND tocid = " & tocId & ") as doc_type, " & _
"(SELECT str_val FROM propval WHERE prop_id =23 AND tocid = " & tocId & ") as doc_loc, " & _
"(SELECT str_val FROM propval WHERE prop_id =" & tmpDocDatePropId & " AND tocid = " & tocId & ") as doc_date, " & _
"(SELECT str_val FROM propval WHERE prop_id =" & tmpAmountPropId & " AND tocid = " & tocId & ") as amount " & _
" FROM propval", _
sqlConnection)
'(22 numval رقم مستند الدفع 4462.00000 MAND,13 empName !MAND,14 cpr MAND,21 docType 1 null,23 location) ,18,28 date null , 20,29 price null
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("payment_num")) Then paymentNum = reader("payment_num").ToString
If Not IsDBNull(reader("emp_name")) Then empName = reader("emp_name")
If Not IsDBNull(reader("crp_num")) Then cprNum = reader("crp_num")
If Not IsDBNull(reader("doc_type")) Then docType = reader("doc_type")
If Not IsDBNull(reader("doc_loc")) Then docLoc = reader("doc_loc")
If Not IsDBNull(reader("doc_date")) Then xDocumentDate = reader("doc_date")
If Not IsDBNull(reader("amount")) Then xAmount = reader("amount")
End If
reader.Close()
'New Meta Data
xCPRNumber = cprNum
dDocTitle = empName 'bilal empName could be null.. wt to do?
xPaymentNumber = paymentNum


Case 4 'الكشوف
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =17 AND tocid = " & tocId & ") as doc_name, " & _
"(SELECT str_val FROM propval WHERE prop_id =18 AND tocid = " & tocId & ") as doc_date, " & _
"(SELECT str_val FROM propval WHERE prop_id =19 AND tocid = " & tocId & ") as party " & _
" FROM propval", _
sqlConnection)
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("doc_name")) Then docName = reader("doc_name")
If Not IsDBNull(reader("doc_date")) Then xDocumentDate = reader("doc_date")
If Not IsDBNull(reader("party")) Then party = reader("party")
End If
reader.Close()
'New Meta Data
dDocTitle = docName


Case 7 'PFC
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =26 AND tocid = " & tocId & ") as name, " & _
"(SELECT str_val FROM propval WHERE prop_id =27 AND tocid = " & tocId & ") as mil_number " & _
" FROM propval", _
sqlConnection)
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("name")) Then empName = reader("name")
If Not IsDBNull(reader("mil_number")) Then xMilitaryNumber = reader("mil_number")
End If
reader.Close()
'New Meta Data
dDocTitle = empName


Case 8 'Payments
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =32 AND tocid = " & tocId & ") as doc_num, " & _
"(SELECT str_val FROM propval WHERE prop_id =30 AND tocid = " & tocId & ") as doc_type, " & _
"(SELECT str_val FROM propval WHERE prop_id =31 AND tocid = " & tocId & ") as doc_date, " & _
"(SELECT str_val FROM propval WHERE prop_id =33 AND tocid = " & tocId & ") as amount " & _
" FROM propval", _
sqlConnection)
'doc_num could be payment or ref???
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("doc_num")) Then paymentNum = reader("doc_num")
If Not IsDBNull(reader("doc_type")) Then docType = reader("doc_type")
If Not IsDBNull(reader("doc_date")) Then xDocumentDate = reader("doc_date")
If Not IsDBNull(reader("amount")) Then xAmount = reader("amount")
End If
reader.Close()
'New Meta Data


Case 9 'Service Extention
Dim commandSetType As SqlCommand = New SqlCommand( _
"SELECT TOP 1 " & _
"(SELECT str_val FROM propval WHERE prop_id =14 AND tocid = " & tocId & ") as CR_NUMBER, " & _
"(SELECT str_val FROM propval WHERE prop_id =35 AND tocid = " & tocId & ") as ref_num " & _
" FROM propval", _
sqlConnection)
'doc_num could be payment or ref???
Dim reader As SqlDataReader = commandSetType.ExecuteReader()
If reader.Read Then
If Not IsDBNull(reader("CR_NUMBER")) Then cprNum = reader("CR_NUMBER")
If Not IsDBNull(reader("ref_num")) Then referenceNumber = reader("ref_num")
End If
reader.Close()
'New Meta Data
xCPRNumber = cprNum
xReferenceNumber = referenceNumber

mapFields

Dim sqlStmt As String = "SELECT WCC_MAPPING_dDocType, WCC_MAPPING_xClassification, WCC_MAPPING_xServiceType " & _ "FROM WCC_DOC_TYPES_MAPPING WHERE WCC_MAPPING_SET_ID=" & psetId
        If docType <> "" Then sqlStmt += " AND WCC_MAPPING_Type like N'" & docType & "' "
        If logicalPathStr <> "" Then sqlStmt += " AND WCC_MAPPING_PATH like N'" & logicalPathStr & "' "

generatePhysicalPath

Dim sqlQuery As String = "SELECT storeid,pagenum FROM doc WHERE tocid =" & tocId
        If UserType = 1 Then
            sqlQuery += " AND pagenum not in (SELECT WCC_PAGE_NUM from WCC_DOCS_MIGRATION WHERE WCC_TOC_ID=tocid)"
        Else
            sqlQuery += " AND pagenum in (SELECT WCC_PAGE_NUM from WCC_DOCS_MIGRATION WHERE WCC_TOC_ID=tocid AND WCC_STATUS=2)"
        End If

WebCenter Customer Component Logging

1. Include import java.util.logging.Logger; in customer component class.

2. Create object of Logger class
 private final Logger logger = Logger.getLogger(ComponentManager.class.getName());

3. Add the logs similar to WebCenter Content.
logger.info("<" + new Date().toString()+ "> <Notice> EBSCheckIn Sql Query @ [ "+ selectSQL+" ] - JNDI @ ["+bean.getAxfDataSource()+"]");

Above will work else you need to add following statements in class.

1. Import the handler class - import java.util.logging.Handler;

2. Create object of handler class.
 private Handler consoleHandler = new ConsoleHandler();

3.  Map the handler with logger class.

                         logger.addHandler(consoleHandler);

consoleHandler.setLevel(Level.ALL);

logger.setLevel(Level.ALL);

Try Some Other Patter:-
___________________
1. Import log class

     import intradoc.common.Log;

2.  Add the log info message

Log.info("<" + new Date().toString()+ "> <Notice> EBSCheckIn Sql Query @ [ "+ selectSQL+" ] - JNDI @ ["+bean.getAxfDataSource()+"]");

Try Some thing else :-
___________________

import intradoc.common.Report;

Report.info(section, message, t);

Report.info("filestore", msg, null);

Report.info(null, msg, null);

Report.info("scheduledevents", msg, null);

Report.info("taskmanager", null, ignore);

------------------------------------------

Report.trace("publish", "waiting for publishing thread", ignore);

Report.trace("applet", "MainFrameListener: null command", null);

Report.trace("system", null, excep);

Report.trace("system", "counters or depends is empty", null);

Report.trace("filestore", "adding " + action + " operation to commit log", null);

Report.trace("schemainit", "adding Application target", null);

Report.trace("userstorage", "addAuthData userInfo=" + userInfo + " timeStamp=" + tAuthData.m_timeStamp, null);

  Report.trace("archiver", "Writing export file: " + this.m_archiveDir + "/exports.hda", null);
 
  Report.trace("componentloader", "adding " + dir + " (from " + key + "=" + val + ") to search list", null)
 
   Report.trace("search", "using executable '" + searchExe + "' for searching", null);