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