Hi all,
I have to generate a CSV file from all PersonWantsOrg entries (~100.000 and counting) and because of I have to take values from others tables too I use a script to generate the rows:
here my script
Public Function CCC_Export_Requests_LineDefinition_DWH As String
Dim SB As New StringBuilder()
Dim pwo = New ObjectWalker(Base)
Dim uidOrg As String
Try
uidOrg = pwo.GetValue("UID_Org").ToString
If (Not String.IsNullOrEmpty(uidOrg)) Then
Dim uid_unsgroupb = Connection.GetSingleProperty("UNSGroupB","UID_UNSGroupB","UID_AccProduct = (select UID_AccProduct from ITShopORg where UID_ItShopOrg = '" + uidorg + "')")
If (String.IsNullOrEmpty(uid_unsgroupb)) Then
Throw New Exception("Can't find UNSGroupB for the request")
End If
Dim unsGroup As ISingleDbObject = Connection.CreateSingle("UnsGroupB",uid_unsgroupb)
unsGroup.Load
Dim unsRoot As ISingleDbObject = Connection.CreateSingle("unsRootB",unsGroup.GetValue("uid_unsrootb"))
unsRoot.Load
Dim roleName As String = unsGroup.GetValue("Displayname")
Dim roleCode As String = unsGroup.GetValue("CanonicalName")
' Dim shortDescription As String = unsGroup.GetValue("CCC_ShortDescription")
Dim systemCode As String = unsRoot.GetValue("CanonicalName")
Dim systemName As String = unsRoot.GetValue("Displayname")
Dim companycode = unsroot.GetValue("DistinguishedName").ToString.Replace("system=","").Replace(",company=","").Replace(systemCode,"")
Dim companyName As String = Connection.GetSingleProperty("Eset","Ident_ESet","CustomProperty01 = '" + companycode + "' and UID_ESetType in (select UID_ESetType from ESetType where Ident_ESetType = 'Company')")
Dim isTotalRevoked As String = "Yes"
Dim datatotaleRevoke As Date = pwo.GetValue("FK(UID_PersonOrdered).CCC_TotalRevokeDate").Date
If (Not datatotaleRevoke = Nothing ) Then
isTotalRevoked = ""
End If
SB.Append( pwo.GetValue("FK(UID_PersonOrdered).CustomProperty01").String)
SB.Append("~")
SB.Append( pwo.GetValue("FK(UID_PersonOrdered).FirstName").String )
SB.Append("~")
SB.Append(pwo.GetValue("FK(UID_PersonOrdered).LastName").String)
SB.Append("~")
SB.Append(pwo.GetValue("CCC_RequestCode").String)
SB.Append("~")
SB.Append(pwo.GetValue("CCC_RequestCode").String)
SB.Append("~")
SB.Append( systemCode)
SB.Append("~")
SB.Append( systemName)
SB.Append("~")
SB.Append( roleCode)
SB.Append("~")
SB.Append( roleName)
SB.Append("~")
SB.Append( pwo.GetValue("FK(UID_PWOState).CCC_Status_Code").String)
SB.Append("~")
SB.Append( pwo.GetValue("FK(UID_PWOState).Ident_PWOState").String)
SB.Append("~")
SB.Append(isTotalRevoked)
SB.Append("~")
SB.Append(pwo.GetValue("OrderDate").Date.ToString("dd/MM/yyyy HH.mm.ss"))
SB.Append("~")
If (Not String.IsNullOrEmpty(pwo.GetValue("DateActivated").toString)) Then
SB.Append(pwo.GetValue("DateActivated").Date.ToString("dd/MM/yyyy"))
End If
SB.Append("~")
SB.Append(pwo.GetValue("XDateUpdated").Date.ToString("dd/MM/yyyy HH.mm.ss"))
SB.Append("~")
Dim nomeCognomeApprover As String = ""
Dim UID_Approver As String = CCC_PersonApprover( pwo.GetValue("UID_PersonWantsOrg"), pwo.GetValue("UID_PersonOrdered"))
If Not String.IsNullOrEmpty(UID_Approver) Then
nomeCognomeApprover = Connection.GetSingleProperty("Person","FirstName","UID_Person = '" + UID_Approver + "'").ToString
nomeCognomeApprover = nomeCognomeApprover + " " + Connection.GetSingleProperty("Person","LastName","UID_Person = '" + UID_Approver + "'").toString
End If
SB.Append(nomeCognomeApprover)
SB.Append("~")
Dim result As String = ""
Dim UID_productCategory As String = Connection.GetSingleProperty ("AccProductParamCategory","UID_AccProductParamCategory","UID_AccProductParamCategory = (select UID_AccProductParamCategory from AccProduct where UID_ACCProduct = (select UID_Accproduct from ITShopOrg where UID_ITShopOrg = '" + CCC_GetItShopOrgUID(pwo) + "'))")
Dim accProductParameterCol As IColDbObject = Connection.CreateCol("AccProductParameter")
accProductParameterCol.Prototype.PutValue("UID_AccProductParamCategory", UID_productCategory)
accProductParameterCol.Load()
If (accProductParameterCol.Count > 0) Then
Dim SBAF As New StringBuilder()
SBAF.Append("<FIELDS>")
For Each colElement As IColElem In accProductParameterCol
Dim accProductParameter As ISingleDbObject = Connection.CreateSingle("accProductParameter", colElement.GetValue("UID_AccProductParameter").ToString)
accProductParameter.Load
SBAF.Append("<FIELD>")
SBAF.Append("<NAME>" + accProductParameter.GetValue("DisplayValue").toString + "</NAME>")
SBAF.Append("<VALUES>")
Dim values As String = pwo.GetValue(accProductParameter.GetValue("ColumnName").toString).ToString
If (Not String.IsNullOrEmpty(values)) Then
For Each value As String In values.Split(chr(7))
SBAF.Append("<VALUE>" + value.Trim + "</VALUE>")
Next
End If
SBAF.Append("</VALUES>")
SBAF.Append("</FIELD>")
' msgbox(accProductParameter.GetValue("DisplayValue").toString + " " + accProductParameter.GetValue("ColumnName").toString)
Next
SBAF.Append("</FIELDS>")
SB.Append(SBAF.ToString().Replace(Chr(255) & Chr(254), " ").Replace(Chr(13) & Chr(10), " "))
End If
Else
RaiseMessage(MsgSeverity.Info, "Request UID_PersonWantsOrg " + pwo.GetValue("UID_PErsonwantsorg").ToString + " doesn't have a product available anymore")
Throw New Exception("Request UID_PersonWantsOrg " + pwo.GetValue("UID_PErsonwantsorg").ToString + " doesn't have a product available anymore")
SB.Append("")
End If
Catch ex As Exception
RaiseMessage(MsgSeverity.Serious, "UID_PersonWantsOrg " + pwo.GetValue("UID_PErsonwantsorg").ToString + " UID_ORG " + uidOrg)
RaiseMessage(MsgSeverity.Serious, ViException.FormatStackTrace(ex))
Throw ex
End Try
Return SB.ToString()
End Function
But it tooks 3 hours to generate my file.
Is there any way to imrpove the speed? Is there anything I could do bettere in my script?
Shoudl I use a view where possible??
Thanks
Michele