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

LDAP Configuration(On Debian)

LDAP .

            LDAP stands for Lightweight Directory Access Protocol.

Reference. Google is the best reference for any kind of  problem & also for LDAP :)

LDAP Installation.

                        when you are going to install ldap in your are syetm than you must have extra

packages installed for ldap.

For Example.  Apache2 , PHP5

Apache Installation.

apt-get install apache2

For server-side, HTML-embedded scripting languag supports u have to install  libapache2-mod-php5 & also apache2-utils it's a utility programs for webservers.

PHP Installation

apt-get install php5

and php5-common for  Common files for packages built from the php and php5-ldap  it's  LDAP module for php5.

After Installion of apache2 and php5 u have to check them either both r working or not.

Apache testing.

on the webbrowser type http://localhost

It will return u a index.html page but if it's did't it mean there is some problem in ur apache2.

Php testing.

you have to make a php file for cheching php kit it's working or not

Make a php file name test.php

#] vi /var/www/test.php

write some php code or this code is batter for testing

<?php  phpinfo(); ?>

Now type this on webbrowser http://localhost/test.php

phpinfo method will return u a complete informationa about php thats installed in ur system.

LDAP Installation

apt-get install slapd it's a OpenLDAP server (slapd).

After installation there is two most important and editable files

/etc/ldap/slapd.con  (for server configuration)

/etc/ldap/ldap.con  (for client configuration)

when u r going to edit slapd.conf than first make backup for that file.Now you have to change little bit in the configuration file opn it in vi or any other editor.

vi /etc/ldap/slapd.conf

Add suffix it's base of your directory in database

suffix          "dc=imtiaz,dc=emergen,dc=org"

also u have to change and add some attributes.

access to attrs=userPassword

        by dn="cn=admin,dc=imtiaz,dc=emergen,dc=org" write

        by anonymous auth

        by self write

        by * none

The userPassword by default can be changed  by the entry owning it if they are authenticated.

Others should not be able to see it, except the admin entry below.a These access lines apply to database #1 only

or if u feel difficult to edit this file or u don't want to to that than u can configure with the

help of command

dpkg-reconfigure slapd

Add the suffic(i.e. dc=imtiaz,dc=emergen,dc=org) and company name (i.e. Emergen Consulting) and ldap root password. If u select all the option defalut than batter or i u want to set according to ur requirement than u can read and make changes :)

If u wana know about ldap is working or not thats u can check with the builtin command

called

slapdtest

There is a lot of commads they can help u to know about ldap.

Now its time to install phpldapadmin its' web based interface for administering LDAP servers

u can install with the help of

apt-get install phpldapadmin

else  if u have a .gz file also u can extract that.

when u complete ur installation or extrection than there is one configuration file called config.php its available in ../phpldapadmin/config/config.php edit that file.

vi ../phpldapadmin/config/config.php

Before > $ldapservers->SetValue($i,'server','name','My LDAP Server');

After   < $ldapservers->SetValue($i,'server','name','Imtiaz Directory Service');

Before > // $ldapservers->SetValue($i,'server','host','127.0.0.1');

After   < $ldapservers->SetValue($i,'server','host','localhost');

Bwfore > // $ldapservers->SetValue($i,'server','port','389');

After    < $ldapservers->SetValue($i,'server','port','389');

Before > // $ldapservers->SetValue($i,'server','base',array(''));

After   < $ldapservers->SetValue($i,'server','base',array('dc=imtiaz,dc=emergen,dc=org'));

Before > // $ldapservers->SetValue($i,'server','auth_type','cookie');

After < $ldapservers->SetValue($i,'server','auth_type','session');

Before >#  $ldapservers->SetValue($i,'login','dn','cn=Manager,dc=example,dc=com');

After  < $ldapservers->SetValue($i,'login','dn','cn=admin,dc=imtiaz,dc=emergen,dc=org');

Before >  $ldapservers->SetValue($i,'login','pass','secret');

After   < $ldapservers->SetValue($i,'login','pass','emergen');

After change this now it's time to run the phpldapadmin

type this on the web browser

http://ip-address/phpldapadmin  or http:// localhost/phpldapadmin (if ur on local system)

u'll see a phpldapadmin login page on the web browser than login & use who ever u want to use and create what ever u what to create :)

Login DN : cn=admin,dc=imtiaz.dc=emergen,dc=org

 [cn,dc that's u added in phpldapadmin config.php file]

Password  : emergen

[what ever password u was given on phpldapadmin config file.]

Note:- if ur ladp is not aceepting the login dn or password than it's batter to run

dpkg-reconfigure slapd.

When u finished to create  groups & also users than now it's time to check them either they r

completely created and u can use them, u can check that with the help of ssh but befor that u have to installed some more packages and also u have to make changes in some configuration files.

Now install

apt-get install libnss-ldap

apt-get installl libpam-ldap

After installation u have to make changes in file

vi  /etc/nsswitch.conf

passwd:  files ldap

group:   files ldap

shadow:  files ldap

vi /etc/pam.d/common-account

account sufficient      pam_unix.so

account sufficient      pam_ldap.so

vi /etc/pam.d/common-auth

auth    sufficient pam_unix.so nullok_secure

auth    sufficient pam_ldap.so use_first_pass

vi /etc/pam.d/common-password

password sufficient pam_unix.so nullok obscure min=4 max=8 md5

password sufficient pam_ldap.so use_first_pass debug

vi /etc/pam.d/common-session

session required        pam_unix.so

session optional        pam_foreground.so

session sufficient      pam_ldap.so

After editing all these files try to make ssh with ur added user.

if u want to check ur added user's than u can check with

#] getent passwd

It'll show u all the ldap added user's.

#] ssh ishah@localhost

if ur successful login than ur ldap is working fine  :)

Postfix , Dovecot and Its Integration with LDAP

Postfix is a very popular mail server . It is robust and easy to setup . It acts as a MTA ( mail transfer agent ) that can receive your mail and drop it into a local mailbox .

How to configure postfix:

The packages required to install postfix are postfix , postfix-ldap , postfix -pcre .

#apt-get install postfix postfix-pcre  postfix-ldap

The first configuration file for postfix is /etc/postfix/main.cf

#vi /etc/postfix/main.cf

# See /usr/share/postfix/main.cf.dist for a commented, more complete version

# Debian specific:  Specifying a file name will cause the first

# line of that file to be used as the name.  The Debian default

# is /etc/mailname.

myorigin = /etc/mailname

smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)

biff = no

# appending .domain is the MUA's job.

append_dot_mydomain = no

# Uncomment the next line to generate "delayed mail" warnings

#delay_warning_time = 4h

# TLS parameters

smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem

smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key

smtpd_use_tls=yes

smtpd_tls_session_cache_database = btree:${queue_directory}/smtpd_scache

smtp_tls_session_cache_database = btree:${queue_directory}/smtp_scache

# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for

# information on enabling SSL in the smtp client.

myhostname = asim.emergen.org

alias_maps = hash:/etc/aliases

alias_database = hash:/etc/aliases

myorigin = /etc/mailname

mydestination = asim.emergen.org, , localhost

relayhost =

mynetworks = 127.0.0.0/8 192.168.1.0

mailbox_size_limit = 0

mailbox_transport = dovecot

recipient_delimiter = +

inet_interfaces = all

The second configuration for postfix is /etc/mailname . Here we define the fully qualified domain name .

#vi /etc/mailname

asim.emergen.org

The third file is /etc/postfix/master.cf

smtp      inet  n       -       n       -       -       smtpd

pickup    fifo  n       -       -       60      1       pickup

cleanup   unix  n       -       -       -       0       cleanup

qmgr      fifo  n       -       -       300     1       qmgr

#qmgr     fifo  n       -       -       300     1       oqmgr

rewrite   unix  -       -       -       -       -       trivial-rewrite

bounce    unix  -       -       -       -       0       bounce

defer     unix  -       -       -       -       0       bounce

trace     unix  -       -       -       -       0       bounce

verify    unix  -       -       -       -       1       verify

flush     unix  n       -       -       1000?   0       flush

proxymap  unix  -       -       n       -       -       proxymap

smtp      unix  -       -       n       -       -       smtp

relay     unix  -       -       -       -       -       smtp

showq     unix  n       -       -       -       -       showq

error     unix  -       -       -       -       -       error

local     unix  -       n       n       -       -       local

virtual   unix  -       n       n       -       -       virtual

lmtp      unix  -       -       n       -       -       lmtp

anvil     unix  -       -       n       -       1       anvil

dovecot   unix  -       n       n       -       -       pipe

 flags=DRhu user=vmail:vmail argv=/usr/lib/dovecot/deliver -d ${recipient}

mailman unix  -       n       n       -       -       pipe

 flags=FR user=list

 argv=/var/lib/mailman/bin/postfix-to-mailman.py ${nexthop} ${user}

maildrop  unix  -       n       n       -       -       pipe

  flags=DRhu user=vmail argv=/opt/maildrop/bin/maildrop -d ${recipient}

uucp      unix  -       n       n       -       -       pipe

  flags=Fqhu user=uucp argv=uux -r -n -z -a$sender - $nexthop!rmail ($recipient)

ifmail    unix  -       n       n       -       -       pipe

  flags=F user=ftn argv=/usr/lib/ifmail/ifmail -r $nexthop ($recipient)

bsmtp     unix  -       n       n       -       -       pipe

  flags=Fq. user=bsmtp argv=/usr/lib/bsmtp/bsmtp -d -t$nexthop -f$sender $recipient

scalemail-backend unix  -       n       n       -       2       pipe

  flags=R user=scalemail argv=/usr/lib/scalemail/bin/scalemail-store ${nexthop} ${user} ${extension}

gnarwl    unix  -       n       n       -       -       pipe

  flags=F user=vmail argv=/usr/bin/gnarwl -a $user

smtp-amavis  unix   -    -    n   -    2 lmtp

 -o smtp_data_done_timeout=1200

 -o disable_dns_lookups=yes

 -o smtp_send_xforward_command=yes

127.0.0.1:10025  inet  n - n - - smtpd

 -o content_filter=

 -o local_recipient_maps=

 -o relay_recipient_maps=

 -o smtpd_restriction_classes=

 -o smtpd_client_restrictions=

 -o smtpd_helo_restrictions=

 -o smtpd_sender_restrictions=

 -o smtpd_recipient_restrictions=permit_mynetworks,reject

 -o mynetworks=127.0.0.0/8

 -o strict_rfc821_envelopes=yes

After this , start the service

#/etc/init.d/postfix start

 To verify whether postfix is working correctly 

#telnet localhost 25

Trying 127.0.0.1...

Connected to localhost.localdomain.

Escape character is '^]'.

220 asim.emergen.org ESMTP Postfix (Ubuntu)

Dovecot

Dovcecot acts as an MDA ( mail delivery agent ) .It handles final delivery of messages for a system's local recipients .

The package required to install dovecot is dovecot – common , dovecot – pop3d and dovecot – imapd .

#apt-get install dovecvot-common dovecot-pop3d dovecot-imapd

The main configuration file is

#vi /etc/dovecot/dovecot.conf

protocols = imap imaps pop3 pop3s

#ssl_disable = no

#ssl_cert_file = /etc/postfix/tls/server.pem

#ssl_key_file = /etc/postfix/tls/key.pem

log_timestamp = "%Y-%m-%d %H:%M:%S "

#login_greeting = Dovecot ready.

#max_mail_processes = 1024

mail_extra_groups = vmail

#mail_debug = yes

default_mail_env = maildir:%h:INDEX=/var/dovecot/indexes/%h

disable_plaintext_auth = no

protocol imap {

 # mail_plugins = quota imap_quota

#  mail_plugin_dir = /usr/lib/dovecot/modules/imap

}

protocol pop3 {

  pop3_uidl_format = %08Xu%08Xv

 # mail_plugins = quota

 # mail_plugin_dir = /usr/lib/dovecot/modules/pop3

}

protocol lda {

 # mail_plugins = quota

 # mail_plugin_dir = /usr/lib/dovecot/modules/imap

  postmaster_address = postmaster@emergen.org

  auth_socket_path = /var/run/dovecot-auth-master

  log_path = /var/log/dovecot-deliver.log

  info_log_path = /var/log/dovecot-deliver.log

}

auth default {

  mechanisms = plain

  socket listen {

     master {

protocols = imap imaps pop3 pop3s

#ssl_disable = no

#ssl_cert_file = /etc/postfix/tls/server.pem

#ssl_key_file = /etc/postfix/tls/key.pem

log_timestamp = "%Y-%m-%d %H:%M:%S "

#login_greeting = Dovecot ready.

#max_mail_processes = 1024

mail_extra_groups = vmail

#mail_debug = yes

default_mail_env = maildir:%h:INDEX=/var/dovecot/indexes/%h

disable_plaintext_auth = no

protocol imap {

 # mail_plugins = quota imap_quota

#  mail_plugin_dir = /usr/lib/dovecot/modules/imap

}

protocol pop3 {

  pop3_uidl_format = %08Xu%08Xv

 # mail_plugins = quota

 # mail_plugin_dir = /usr/lib/dovecot/modules/pop3

}

protocol lda {

 # mail_plugins = quota

 # mail_plugin_dir = /usr/lib/dovecot/modules/imap

  postmaster_address = postmaster@emergen.org

  auth_socket_path = /var/run/dovecot-auth-master

  log_path = /var/log/dovecot-deliver.log

  info_log_path = /var/log/dovecot-deliver.log

}

auth default {

  mechanisms = plain

  socket listen {

     master {

  path = /var/run/dovecot-auth-master

       mode = 0600

       user =  vmail # User running Dovecot LDA

     }

   }

  passdb pam {

  }

  # LDAP database

  userdb ldap {

    args = /etc/dovecot/dovecot-ldap.conf

  }

  user = vmail

}

plugin {

  # Default quota for all users is set to 100 MB with a

  # limit of 5000 messages in Mailbox.

 # quota = maildir:storage=102400:messages=500

}

                                                                                                           64,1          Bot

The second configuration file is

#vi /etc/dovecot/dovecot-ldap.conf

hosts = asim.emergen.org

ldap_version = 3

base = ou=people,dc=asim,dc=emergen,dc=org

scope = subtree

user_attrs = uid,mailMessageStore,,,,,mailQuotaSize=quota

user_filter = (&(&(objectClass=qmailUser)(accountStatus=active))(|(mailAlternateAddress=%u)(mail=%u)(uid=%u)))

user_global_uid = 1005

user_global_gid = 1005

Now, we have to define the authentication type in the file /etc/default/saslauthd .

#vi /etc/default/saslauthd