Migrate an Enterprise Capture Workspace

This can be accomplished by using the WLST commands "exportWorkspace" on the source Enterprise Capture system and "importWorkspace" on the target Enterprise Capture system.

The exact steps are :

1) Run the wlst command (.sh for Linux, .cmd for Windows) from the Middleware home /common/bin directory:

  wlst.sh

2) Connect to the Capture managed server:

    wls:/offline> connect("weblogic","welcome1","t3://localhost:16400")

3) Retrieve Workspace ID, which is passed to the export command.  To get a list of current workspaces and their IDs:

    wls:/content/serverConfig>listWorkspaces()

The list will look like the following:

         8 wkspace1

         6 fred-wk2

         7 test-capture1

4) Run the export command:

    wls:/content/serverConfig>exportWorkspace(8,'/home/isyed/wkspace1.xml')

5) Import the workspace to the same instance (only if you deleted the workspace since exporting) or a different Capture instance:

   wls:/content/serverConfig>importWorkspace('/home/isyed/wkspace1.xml')

Multiple eclipse instances on Mac

OSX Eclipse Launcher utility plugin

Follow these simple instructions

1. Go to help --> Eclipse Marketplace...
2. Search for "OS X Eclipse laucher utility"
3. Cick Install, Next, Finish and restart your eclipse.

Once the plugin is installed, click on File --> Open Workspace.  Here you go, a new instance of eclipse is opened with a new workspace.

Advance Scripts for Oracle WebCenter Capture

More documentation can be found here: https://docs.oracle.com/cd/E29542_01/doc.1111/e28275/c01_intro.htm

Script to generate the logs:

----------------------------------------------------------------------------------------------
load("nashorn:mozilla_compat.js");

function logRPCEvent (rpc, funcName) {
var logger = rpc.getLogger();
logger.info("PrintRecogProcEvents.js entered function: " + funcName);
}

function initialize(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "initialize");
}

function processBatch(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "processBatch");
}

function restoreCaptureBatch(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "restoreCaptureBatch");
}

function beginPhase(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "beginPhase");
}

function endPhase(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "endPhase");
}

function extractBatchItem(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "extractBatchItem");
}

function barcodesFoundOnItem(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "barcodesFoundOnItem");
}

function batchItemAllValidBarcodes(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "batchItemAllValidBarcodes");
}

function determineSeparatorPage(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "determineSeparatorPage");
}

function batchItemValidBarcode(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "batchItemValidBarcode");
}

function determineDocType(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "determineDocType");
}

function beginDatabaseLookup(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "beginDatabaseLookup");
}

function determineIndexValues(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "determineIndexValues");
}

function renameOrigCaptureDocTitle(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "renameOrigCaptureDocTitle");
}

function createCaptureDoc(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "createCaptureDoc");
}

function postProcess(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "postProcess");
}

function endBatchProcess(rpc) { // RecognitionProcessorContext
logRPCEvent(rpc, "endBatchProcess");
}
----------------------------------------------------------------------------------------------

Script to Update the Date format 

----------------------------------------------------------------------------------------------
load("nashorn:mozilla_compat.js");
importClass(java.text.SimpleDateFormat);
importClass(java.util.Date);

function logRPCEvent(rpc, logText) {
    var logger = rpc.getLogger();
    logger.info("[WWW] - " + logText);
}

function determineIndexValues(rpc) { // RecognitionProcessorContext
    logRPCEvent(rpc, "determineIndexValues");
    convertDateFormat(rpc);
}



function convertDateFormat(rpc) {
    logRPCEvent(rpc, "convertDateFormat");
    var DOCUMENT_DATE_FIELD = "Document Date";

    var documentDate = getIndexID(rpc, DOCUMENT_DATE_FIELD);
    logRPCEvent(rpc, "Get Index Field Id: " + documentDate);

    var capturedDocument = rpc.getBle().getBatch().getDocuments().get(0);
    logRPCEvent(rpc, "Captured Document : " + capturedDocument);

    var indexValues = rpc.getDocument().getIndexValues();
    for (var i = 0; i < indexValues.size(); i++) {
        var iv = indexValues.get(i);
        if (iv.getFieldID().equals(documentDate)) {
            logRPCEvent(rpc, "FieldID: [" + iv.getFieldID() + "] FieldValue: [" + iv.getFieldValue() + "]");
            var documentDateCapturedValue = iv.getFieldValue();
            iv.setFieldValue(formatRequiredDate(documentDateCapturedValue));
        }
    }
}

// @Format the date to yyyy-MM-dd'T'HH:mm:ss'Z'
function formatRequiredDate(currentDate) {

    var REQUIRED_PATTERN = "yyyy-MM-dd'T'HH:mm:ss'Z'";
    var LOOKUP_DATE_PATTERN = "MM-dd-yyyy";
    var currentDateFormat = new SimpleDateFormat(LOOKUP_DATE_PATTERN);
    var inputDate = currentDateFormat.parse(currentDate);
    currentDateFormat.applyPattern(REQUIRED_PATTERN);

    return currentDateFormat.format(inputDate);
}

// @Return the field object from document
function getFieldDefination(document, field) {
    return document.getParentBatch().getWorkspace().getFieldDefinitions().findByName(field);
}

// @Get the index Id
function getIndexID(rpc, indexName) {
    var workspace = rpc.getWorkspaceEntity();
    var indexDef = findIndexDefinitionByName(workspace, indexName, rpc);

    if (indexDef != null) {
        var indexID = indexDef.getIndexFieldID();
    }
    logRPCEvent(rpc, " In getIndexID : indexID [" + indexID + "]");
    return indexID;
}

// @Get the index definition by name
function findIndexDefinitionByName(workspace, indexName, rpc) {
    logRPCEvent(rpc, " In findIndexDefinitionByName : Workspace [" + workspace + "], Index Name: [" + indexName + "]");
    var indexDefs = workspace.getIndexDefinitions();
    var size = indexDefs.size();
    var foundIndexDef = null;
    for (var i = 0; i < size; i++) {
        var indexDef = indexDefs.get(i);
        if (indexName.equals(indexDef.getFieldName())) {
            foundIndexDef = indexDef;
            break;
        }
    }
    logRPCEvent(rpc, " In findIndexDefinitionByName : foundIndexDef [" + foundIndexDef + "]");
    return foundIndexDef;
}
----------------------------------------------------------------------------------------------

Steps to configure in Capture:

1. Copy the following code to a text file.
2. Open the WebCenter Workspace Console.
3. Select the desired Workspace.
4. Select the "Advanced" train stop / tab.
5. Click the "Add New Script" button.
6. Select type "Recognition Processor" and name the script appropriately. For example, LogsRecProcEvents.
7. Select the file created above and click submit.
8. Select the "Processing" tab.
9. Select the desired Recognition Processor job and click the Edit (pencil) icon.
10. Go to the "Extensions" train stop / tab.
11. Click the "Script" pulldown menu.
12. Select the script created in the Advanced Tab, (e.g. LogsRecProcEvents).
13. Click "Submit" to apply the script.

Welcome to Vagrant

1. Download and Install the Vagrant  - (https://www.vagrantup.com/downloads.html)
i. Check the vagrant installed version: vagrant -vagrant
ii. Check the OS path if you can find the vagrant command.

2. Download and Install the Oracle Virtual Box - (https://www.virtualbox.org/wiki/Downloads)

3. Create a new folder for Vagrant box instances - vagrant-boxes

4. Get the required box name from the vagrant catalog - https://app.vagrantup.com/boxes/search

5. Go to the newly created folder (vagrant-boxes) and run command to create the vagrant instance.
- vagrant init ubuntu/trusty64
        i.  ubuntu/trusty64 is the vagrant box available in the catalog.
       ii. You get the following error:
                  The box 'xxxxxx' could not be found or could not be accessed in the remote catalog.
Use the following command to download the vagrant intance
- vagrant box add ubuntu/trusty64 https://app.vagrantup.com/ubuntu/boxes/trusty64


6. The vagrant will create an instance configuration file (VagranFile). Review the file and update the configuration only if you are sure what you are doing.

7.  Run the command to up the vagrant instance, the command will download the instance and make it ready to use.
   - vagrant up
   i.e Just make sure you are on the right path/directory.

   - vagrant destroy : to delete the vagrant instance
   - vagrant suspend : to shutdown the vagrant instance
   - vanrant resume  : to started the suspended instance
   - bagramt reload  : to reload the instance incase of change in vm configuration.

8.  The VM system specification(RAM, Processor, Hard drive) can be updated in vagrantfile (the configuration file which created in step#5)
#Provider Settings
config.vm.provider "virtualbox" do |vb|
vb.memory = 2048
vm.cpus = 4
end

- vagrant reload
   The specification can be updated through the oracle box. ??!! (Need to verify)

9.  SSH the box through vagrant command and continue with setuping other apps (apache, mysql, etc) on box.
- vagrant ssh

10. Enable the network settings in vagrantfile (the configuration file which created in step#5)

#Network Settings
config.vm.network "forwarded_port", guest: 80, host: 8080
#If wanted to use private network ip then above statment must be commited
config.vm.network "private_network", ip: "192.168.10.10"

    - vagrant reload

11. Add hostname/domain name on the local environment, it's completely based on OS like in Linux: \etc\hosts
        192.168.10.10 mybox.local www.mybox.local

12. Setup share folder in vagrantfile (the configuration file which created in step#5)

#Folder Settings
config.vm.synced_folder ".", "/vagrant_data", mount_options =>["dmode=777","fmode=666"]
# . is a current folder of host OS and it can be different path like /home/isyed/Downloads
# "/vagrant_data" is destination folder and it can also be any file path in gest os.
# mount_options =>["dmode=777","fmode=666"]  are the permission on the folder to make it more secure
# Check NFS with Vagrant to increase the performance for network shared folders - :nfs => { :mount_options =>["dmode=777","fmode=666"]}
   config.vm.synced_folder ".", "/vagrant_data", :nfs => { :mount_options =>["dmode=777","fmode=666"]}

- vagrant reload

13. Setup a provision Settings : You can add the list of command which is going to be executed by vagrant once the instance is recreated.

#Provision Settings
config.vm.provision "shell", inline: <<-SHELL
apt-get update
apt-get install -y apache2
SHELL

The provision command can be stored in different provision file, to do so update the provision settings:
#Provision Settings
config.vm.provision "shell", path:"bootstrap.sh"

Add new file bootstrap.sh file and add the all the commands which you need to execute after the instance is up.

Vagrantfile

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

Vagrant.configure("2") do |config|

  

  config.vm.box = "ubuntu/trusty64"

  # config.vm.box_check_update = false

  # config.vm.network "forwarded_port", guest: 80, host: 8080

  # config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

  # config.vm.network "private_network", ip: "192.168.0.8"

  # config.vm.network "public_network"

  # config.vm.synced_folder "../data", "/vagrant_data"

    

   # config.vm.hostname = "vlamp"

   # config.ssh.username = "vlamp"

   # config.vm.network "public_network", bridge:"en0: Wi-Fi (AirPort)", ip: "192.168.0.120”

   config.vm.network "public_network", :bridge => "eth0", ip: "192.168.0.180", :netmask => "255.255.255.128", auto_config: false

   # config.vm.network "public_network", use_dhcp_assigned_default_route: true

   # Attempt to 'guess' the default network

   # config.vm.network :public_network, :bridge => 'en0: Wi-Fi (AirPort)'

   config.vm.provider "virtualbox" do |vb|

     

     # Set Instance Name

     vb.name = "vlamp"

     # Display the VirtualBox GUI when booting the machine

     vb.gui = false

  

     # Customize the amount of memory on the VM:

      vb.memory = "1024"

   end

   config.vm.provision "shell", inline: <<-SHELL

     apt-get update

     #apt-get install -y apache2

   SHELL

end

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

Vagrant.configure("2") do |config|
  config.vm.box = "centos/7"
  # config.vm.box_check_update = false
  # config.vm.network "forwarded_port", guest: 80, host: 8080
    config.vm.network "private_network", ip: "192.168.10.10"
  # config.vm.network "public_network"

   config.vm.synced_folder ".", "/vagrant", type: "virtualbox"
   config.vm.provider "virtualbox" do |vb|
     vb.gui = false
vb.name = "coremedia9"
vb.cpus = 4
     vb.memory = "1024"
   end
 
  # config.push.define "atlas" do |push|
  #   push.app = "YOUR_ATLAS_USERNAME/YOUR_APPLICATION_NAME"
  # end

  # config.vm.provision "shell", inline: <<-SHELL
  #   apt-get update
  #   apt-get install -y apache2
  # SHELL

end

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

 

Disable 8dot3

Disable 8dot3 naming, what is requested during UCM install procedure, by running "Installer DisableNtfs8dot3" and then restarting windows. This procedure can be run after UCM installation is done.

Run: DomainHome\ucm\cs\bin\Installer.exe DisableNtfs8dot3
Page 153: http://download.oracle.com/docs/cd/E17904_01/doc.1111/e14495.pdf

If that still leaves 8dot3 naming enabled, modify registry:
1. Start/Run/regedit
2. In the left pane of the window navigate to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem]
3. In the right pane of the window, edit the DWORD "NtfsDisable8dot3NameCreation" and set its value to 1 (The default value is 2)
4. Reboot the system

RCU-6080:Global prerequisite check failed

RCU-6080:Global prerequisite check failed – The selected Oracle database is a multitenant container database (CDB).

Steps : 

1) Check where are your PDB installed

SQL> select name from v$services;

NAME
—————————————————————-
pdborcl
weblogicXDB
weblogic
SYS$BACKGROUND
SYS$USERS

2. Alter your database:

SQL>  alter pluggable database all open;

3.  insert the correct service name. On my case, is pdborcl:

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