Sub IssuedCreditNote()

'Dobropis faktúry vydanej

Dim mManager
Dim invID

Set src = Sheets("Header")
x = Split(src.Cells(i, 13), "-")
varsymbol = Trim(src.Cells(i, 13))


On Error GoTo errhandler
invID = x(1)
Set invSQL = g3.SQLSelectAsRowset("select i1.id, d.id, d.code, d.name from IssuedInvoices i1 join docqueues d on d.id = i1.docqueue_id where i1.varsymbol = '" & invID & "' and d.code = '" & Trim(x(0)) & "'")
GoTo 1
errhandler:

Dim txt As String
Dim s As Integer


s = Len(varsymbol)
Do Until s = 0
If IsNumeric(Mid(varsymbol, s, 1)) Then
s = s - 1
Else
invID = Mid(varsymbol, s + 1, Len(varsymbol) - s)
GoTo 1
End If
Loop



1
Set invSQL = g3.SQLSelectAsRowset("select i1.id, d.id, d.code, d.name from IssuedInvoices i1 join docqueues d on d.id = i1.docqueue_id where i1.varsymbol = '" & invID & "'")



'Set invSQL = g3.SQLSelectAsRowset("select i1.id, d.id, d.code, d.name from IssuedInvoices i1 join docqueues d on d.id = i1.docqueue_id where i1.varsymbol = '" & invID & "' and d.code = '" & Trim(x(0)) & "'")

invID = invSQL.Data(0)

Set mManager = g3.CreateDocumentImportManager("@IssuedInvoice", "@IssuedCreditNote")

mManager.AddInputDocument (invID)
mManager.Execute

Set invObjekt = g3.CreateObject("@issuedinvoice")

Set invData = invObjekt.GetValues(invID)

Set DocQSQL = g3.SQLSelectAsRowset("select id, name from docqueues where code = '" & docCode & "' and name like '" & src.Cells(i, 15) & "%'")

mManager.OutputDocument.ValueByName("DocQueue_ID") = DocQSQL.Data(0)
mManager.OutputDocument.ValueByName("Description") = invData.ValueByName("DisplayName")
mManager.OutputDocument.ValueByName("AccPresetDef_ID") = "U101000000"
mManager.OutputDocument.ValueByName("ExternalNumber") = src.Cells(i, 13)
mManager.OutputDocument.ValueByName("PaymentType_ID") = "1000000101"
mManager.OutputDocument.ValueByName("VATReportPreference") = "C1"

mOID = mManager.SaveOutputDocument


End Sub


Sub VATIssuedDepositCreditNote()
'Dobropis daòového zálohového listu vydaného

varsymbol = src.Cells(i, 13)


x = Split(varsymbol, "-")

On Error GoTo errhandler
invID = x(1)
Set invSQL = g3.SQLSelectAsRowset("select i1.id, d.id, d.code, d.name from VATIssuedDInvoices i1 join docqueues d on d.id = i1.docqueue_id where i1.externalNumber = '" & invID & "' and d.code = '" & x(0) & "'")
GoTo 1
errhandler:
invID = Mid(Trim(varsymbol), 4, Len(varsymbol) - 5)
Set invSQL = g3.SQLSelectAsRowset("select i1.id, d.id, d.code, d.name from VATIssuedDInvoices i1 join docqueues d on d.id = i1.docqueue_id where i1.externalNumber = '" & invID & "'")

1

Dim mManager


Set mManager = g3.CreateDocumentImportManager("@VATIssuedDepositInvoice", "@VATIssuedDepositCreditNote")
Set DocQSQL = g3.SQLSelectAsRowset("select id,code, name from docqueues where code = 'D201'")

mManager.AddInputDocument (invSQL.Data(0))
mManager.Execute
mManager.OutputDocument.ValueByName("DocQueue_ID") = DocQSQL.Data(0)
mOID = mManager.SaveOutputDocument

End Sub

Sub VATIssuedDepositInvoice()

'Daòový zálohový list vydaný
x = Split(src.Cells(i, 13), "-")
invID = x(1)


Dim mManager

Set invSQL = g3.SQLSelectAsRowset("select i1.id, i2.tamount from IssuedDInvoices i1 join docqueues d on d.id = i1.docqueue_id join IssuedDInvoices2 i2 on i2.parent_id = i1.id where i1.varsymbol = '" & invID & "' and d.code = '" & x(0) & "'")
Set mManager = g3.CreateDocumentImportManager("@IssuedDepositInvoice", "@VATIssuedDepositInvoice")

mManager.AddInputDocument (invSQL.Data(0))

Set DocQSQL = g3.SQLSelectAsRowset("select id, name from docqueues where code = '" & docCode & "' and name like '" & src.Cells(i, 12) & "%'")

mData = mManager.SetParam("DocQueue_ID", DocQSQL.Data(0))
mData = mManager.SetParam("ImportDocuments", True)
mData = mManager.SetParam("ImportDepositRows", True)
mData = mManager.SetParam("DepositAmount", invSQL.Data(1))

mManager.Execute

mManager.OutputDocument.ValueByName("ExternalNumber") = Trim(x(0)) & x(1)
mManager.OutputDocument.ValueByName("Amount") = invSQL.Data(1)

newID = mManager.SaveOutputDocument

Set iObj = g3.CreateObject("@VATIssuedDepositInvoiceRow")
Set idata = g3.CreateObject("@VATIssuedDepositInvoiceRow")

Set fixSQL = g3.SQLSelectAsRowset("select i2.id from VATIssuedDInvoices i1 join VATIssuedDInvoices2 i2 on i2.parent_id = i1.id and rowtype = 4 where i1.id = '" & newID & "'")
Set idata = iObj.GetValues(fixSQL.Data(0))

idata.ValueByName("VATRate_ID") = "02000X0000"
idata.ValueByName("VATINDEX_ID") = "3000000000"

iObj.UpdateValues fixSQL.Data(0), idata

End Sub


Sub IssuedDepositInvoice()

'Zálohový list vydaný


Set IDIObject = g3.CreateObject("@IssuedDepositInvoice") 'Vytvorenie novej prázdnej faktúry
Set IDIRowObject = g3.CreateObject("@IssuedDepositInvoiceRow")
Set IDIData = g3.CreateValues("@IssuedDepositInvoice")
Set RowsCol = IDIData.ValueByName("Rows")
IDIObject.PrefillValues IDIData

Set DocQSQL = g3.SQLSelectAsRowset("select id, name from docqueues where code = '" & docCode & "' and DocumentType = '10'")

IDIData.ValueByName("DocQueue_ID") = DocQSQL.Data(0)

Set periodSQL = g3.SQLSelectAsRowset("SELECT id, code from periods WHERE code =" & Year(src.Cells(i, 8)) & "")
IDIData.ValueByName("Period_ID") = periodSQL.Data(0)

Dim txt As String
Dim s As Integer


If IsNumeric(Left(src.Cells(i, 4), 1)) Then
IDIData.ValueByName("VarSymbol") = Trim(CStr(src.Cells(i, 4)))
Else
txt = Trim(src.Cells(i, 4))
s = Len(txt)
Do Until s = 0
If IsNumeric(Mid(txt, s, 1)) Then
s = s - 1
Else
IDIData.ValueByName("VarSymbol") = Mid(txt, s + 1, Len(txt) - s)
GoTo 1
End If
Loop
End If
1
IDIData.ValueByName("DocDate$DATE") = src.Cells(i, 8) 'Naèítanie dátumov



icDPH = Trim(adr.Cells(adr.Range("C:C").Find(src.Cells(i, 3)).Row, 5))
Set c2SQL = g3.SQLSelectAsRowset("select id from firms where VATIdentNumber = '" & icDPH & "'")
IDIData.ValueByName("Firm_ID") = c2SQL.Data(0)

For J = 2 To 10000 'Pridávame riadky, kým sa rovná èíslo faktúre
If Worksheets("Rows").Cells(J, 3) = src.Cells(i, 3) Then
If Worksheets("Rows").Cells(J, 5) = "1" Then
Worksheets("Rows").Cells(J, 5) = "20"
End If



Set IDIRowData = g3.CreateValues("@IssuedDepositInvoiceRow")
IDIRowObject.PrefillValues IDIRowData
IDIRowData.ValueByName("RowType") = 4
IDIRowData.ValueByName("Text") = Worksheets("Rows").Cells(J, 9)
IDIRowData.ValueByName("TAmount") = Worksheets("Rows").Cells(J, 4) - Worksheets("Rows").Cells(J, 10)

Set divSQL = g3.SQLSelectAsRowset("select id from divisions where code = '" & Worksheets("Rows").Cells(J, 5) & "'")


IDIRowData.ValueByName("Division_ID") = divSQL.Data(0)
IDIRowData.ValueByName("VATRate_ID") = "00000X0000"


RowsCol.Add IDIRowData
End If
Next

If (Not WasError) And (Not src.Cells(i, 1) = "OK") Then
NewIDI_ID = IDIObject.CreateNewFromValues(IDIData)
src.Cells(i, 1) = "OK"
Else
src.Cells(i, 2) = Error
End If

End Sub