Knowledge blog: 2016

Monday, November 21, 2016

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

Wednesday, February 17, 2016

Big Performance Mistake - Java Application Development.

1. The framework sample that you use, keep close eye on on following:

a. Every library included in project.
b. Every xml created that's load on runtime.
c. Every default code generate by tool

all above items will decrease a chances of bad performance for application.

Review a debug logs and try to resolve any unknown error and exception that caused but not disturbing application process but it might overloading JVM or database and other related applications / connections.

You might not able to feel any performance issue on development / test server because application is running with sample data / limit number of records and users.

2. Not every architect makes a good decisions:-

Custom measuring for example:-
a. Was impacted by garbage collection
b. just measured overall time but not # SQL Exceptions

DON'T ASSUME you know what code does somebody else developed

3. Use performance test tools dynatrace, New Relic, AppDynamics, HP Load Runner.

4. Idenfity how many sql exection in your code also number of same sql statement executed in your code.
a. Optimize the sql statement or cache data
b Connection equitation time

5. Identify how many number of threads are created and utilized by application and number of call made to API's and time take by each in API.

6. Bad Deployments
a. Different framework / library version between development and production server.
b. Perform Unit Test, Functional Test, Performance Test before deployment

7. Push without a plan ( Idea was great but implementation wasn't correct)
a. Need to check number of images on each page that might cause application slowness.
b. Number of redirection and size of resources are used in each page.
c. Use diagnostics tools to make sure overloading won't happen.

Tuesday, February 16, 2016

Secure tunnels to localhost

Expose local servers behind NATs and firewalls to the public internet over secure tunnels.

> ngrok (https://ngrok.com/download)

Monday, February 15, 2016

Domain Name Server (Bind9)

Install bind and related packages.

# apt-get install bind9 dnsutils

Enter in installed directory

cd /etc/bind/

Open name.conf and add rndc key

# vi name.conf

key "rndc-key" {

algorithm hmac-md5;

secret "XNBYnQX/sv+KJj3JgwPWPA==";

};

key "EMERGEN" {

algorithm hmac-md5;

secret "7ESb2eNqmOs9QTc8WnggPg==";

};

Open vi name.conf.local and add these lines

# vi name.conf.local

zone "emergen.org" in {

file "/etc/bind/master/emergen.org";

type master;

allow-transfer { key EMERGEN; };

};

zone "1.168.192.in-addr.arpa" {

type master;

file "/etc/bind/master/reverse-1.168.192.in-addr.arpa";

allow-transfer { key EMERGEN; };

};

Create directory for forward and reverse file.

# mkdir master

# cd master

Create a forward zone file

# vi emergen.org

$TTL 2d

@ IN SOA ns1.emergen.org. hostmaster.emergen.org. (

8 ; serial

3h ; refresh

1h ; retry

1w ; expiry

1d ) ; minimum

emergen.org. IN NS ns1.emergen.org.

IN MX 10 mail.emergen.org.

IN A 192.168.1.92

www IN A 192.168.1.92

mail IN A 192.168.1.92

Create Reverse zone file

# vi reverse-1.168.192.in-addr.arpa

$TTL 86400;

@ IN SOA ns1.emergen.org. hostmaster.emergen.org. (

23 ; serial

10800 ; Refresh after 3 hours

3600 ; Retry after 1 hour

604800 ; Expire after 1 week

86400 ) ; Minimum TTL of 1 day

;

; Name Servers

;

IN NS ns1.emergen.org.

IN NS ns2.emergen.org.

Restart bind

# /etc/init.d/bind9 restart

Test bind with the help of nslookup command.

# nslookup emergen.org

Server: 192.168.1.92

Address: 192.168.1.92#53

Name: emergen.org

Address: 192.168.1.92

If you'll gate this output that means Naming service is working fine.

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 :)

[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 :)

Debian Network Configuration

l IP Setting (Static+DHCP)

l DHCP

l DNS (BIND9)

l APT-PROXY

IP Setting (Static+DHCP)

l IP Setting (Static)

Open Interfaces file and add the these lines.

#>vi /etc/networking/interfacse

auto lo

interfaces lo inet loopback

address 127.0.0.1

netmask 255.0.0.0

ath0

iface eth0 inet static

address 192.168.1.12

netmask 255.255.255.0

getway 192.168.1.1

netmask 255.255.255.0

l IP Setting (DHCP)

ath0

iface eth0 inet dhcp

DHCP

l Install DHCP on the debain with the help of

#> apt-get install dhcp3-server

l Open dhcp.conf file to define the range and domain name and etc.

#>vi /etc/dhcp3/dhcp.conf

ddns-update-style none;

option domain-name “emergen.org” ;

option domain-name-server 192.168.2.1;

Increase default-lease-time & Max-lease-time

subnet 192.168.2.0 netmask 255.255.255.0{

range 192.168.2.1 192.168.2.100;

option subnet-mask 255.255.255.0;

option routers 192.168.2.1;

}

If you want to assign the static IP address to any special machine with the help of dhcp

that define the mack address of that machine NIC.

host hostname {

hardware ethernet 00:2E:4B:5R:EE;

fixed-address 192.168.2.15;

}

when ever you are going to restart the DHCP or the system it's have same IP address that u assign.

DNS (BIND9)

l Primary DNS

l Install DIND9

#> apt-get install bind9

l Stop that service

#> /etc/init.d/bind9 stop

l For defining the forword and reverse zone open name.cof file

#>vi /etc/bind/name.conf & define zone's

Don't delete the previous configuration just add these zone in name.conf file.

l Forward Zone

zone “emergen.org”{

type master;

notify no;

file “/etc/bind/emergen.org.db”;

}

l Reverse Zone

Zone “2.168.192.in-addr.arpa”{

type master;

notify no;

file “2.168.192.db”;

}

l Now create the forward & reverse zone file open a file.

#>vi /etc/bind/emergen.org.db

And add this script

$TTL 2d

@ IN SOA emergen.org. root.emergen.org. (

22 ;serial

3h ;refresh

1h ;retry

1w ; expiry

1d ) ; Minimum

emergen.org. IN NS dns1.emergen.org.

IN A 192.168.3.1

www IN A 192.168.3.1

server IN A 192.168.3.1

; If you want to define more host names that just add them

mail IN A 192.168.3.1

smtp IN A 192.168.3.1

pop IN A 192.168.3.1

l Now create reverse zone file

#> vi /etc/bind/2.168.192.db

And add these line

$TTL 2d

@ IN SOA emergen.org. root.emergen.org.(

2004111601 ; Serial

8H ; Refresh

4H ; Retry

4W ; Expire

1D ) ; Default TTL

IN NS dns1.emergen.org.

1.3.168.192.in-addr.arpa. IN PTR www.emergen.org.

l Start the BIND9 Service

#> /etc/init.d/bind9 start

l Test your BIND9 configuration

#> ping www.emergen.org or mail.emergen.org

#>nslookup emergen.org or mail.emergen.org

l Secondary DNS Server

l Again install bind9 DNS package with the same configuration files on second machine just you have to add some extra lines.

Add Zones in name.conf

zone "emergen.org"{

type slave;

file "/etc/bind/emergen.org.db";

masters{

192.168.3.1;

};

zone "3.168.192.in-addr.arpa"{

type slave;

file "/etc/bind/3.168.192.db";

masters{

192.168.3.1;

};

l Create file for foreword zone.

vi /etc/bind/emergen.org