Sub CONNECT_RAPORU()
Dim conn As Object, rs As Object
Dim ws As Worksheet, hedefWS As Worksheet
Dim sqlQuery As String
Dim connectFirmaKodu As String, firmaPrefix As String
Dim serverName As String, dbName As String, dbNameLKS As String
Dim username As String, password As String
Dim i As Integer, sonSatir As Long, sonSutun As Long
Dim startDate As String, endDate As String
Set ws = ThisWorkbook.Sheets("ANA")
On Error Resume Next
Set hedefWS = ThisWorkbook.Sheets("CONNECT")
If hedefWS Is Nothing Then
Set hedefWS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
hedefWS.Name = "CONNECT"
End If
On Error GoTo 0
firmaPrefix = "LG_" & Split(ws.Range("B1").Value, "-")(1)
connectFirmaKodu = ws.Range("B10").Value
serverName = ws.Cells(2, 2).Value
dbName = ws.Cells(3, 2).Value
dbNameLKS = ws.Cells(3, 2).Value
username = ws.Cells(4, 2).Value
password = ws.Cells(5, 2).Value
startDate = Format(ws.Range("B6").Value, "yyyy-MM-dd")
endDate = Format(ws.Range("B7").Value, "yyyy-MM-dd")
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & dbName & _
";User ID=" & username & ";Password=" & password & ";"
sqlQuery = ""
sqlQuery = sqlQuery & "SELECT CASE APPROVAL.SENDERTITLE WHEN ' ' THEN APPROVAL.GENEXP ELSE APPROVAL.SENDERTITLE END AS Firma, "
sqlQuery = sqlQuery & "APPROVAL.DOCNR AS [Connect_E-Fat_No], APPROVAL.DOCDATE AS [C.E-Fat Tarh], "
sqlQuery = sqlQuery & "APPROVAL.DATE_ AS [C.E-Fat Olulturma Tarh], APPROVAL.DOCTOTAL AS [C.E-Fat Tutarı], "
sqlQuery = sqlQuery & "APPROVAL.DOCTAXTOTAL AS [C.E-Fat Kdv Tutarı], "
sqlQuery = sqlQuery & "CAST(ISNULL(INV.GROSSTOTAL / NULLIF(INV.TRRATE, 0), 0) AS DECIMAL(16, 2)) AS DOVIZ_KDV2, "
sqlQuery = sqlQuery & "CAST(INV.GROSSTOTAL - INV.TOTALDISCOUNTS + INV.TOTALEXPENSES AS DECIMAL(16, 2)) AS DOVIZ_MATRAH, "
sqlQuery = sqlQuery & "CAST(ISNULL(INV.TOTALVAT / NULLIF(INV.TRRATE, 0), 0) AS DECIMAL(16, 2)) AS DOVIZ_KDV, "
sqlQuery = sqlQuery & "APPROVAL.DOCTAXEXCLUSIVETOTAL AS [C.E-Fat Matrahı], APPROVAL.SENDER AS [Vergi Kimlik No], "
sqlQuery = sqlQuery & "APPROVAL.RESPONSECODE AS [Durum Kodu], APPROVAL.RESPONSEDESC AS [Durum Açıklaması], "
sqlQuery = sqlQuery & "INV.FICHENO AS [Logo Fatura No], INV.DATE_ AS [Logo Fatura Tarihi], INV.DOCODE AS [Logo Belge No], "
sqlQuery = sqlQuery & "CASE WHEN CL.ISPERSCOMP = 1 THEN CL.TCKNO ELSE CL.TAXNR END AS [Vergi Numarası], "
sqlQuery = sqlQuery & "INV.NETTOTAL AS [Logo Tutar], INV.TOTALVAT AS [Logo Kdv], INV.GROSSTOTAL AS [Logo Matrah], "
sqlQuery = sqlQuery & "CASE APPROVAL.STATUS "
sqlQuery = sqlQuery & "WHEN '0' THEN 'blob''ta' WHEN '1' THEN 'Onay satirlari olusturulmus' WHEN '2' THEN 'Onay bekliyor' "
sqlQuery = sqlQuery & "WHEN '3' THEN 'Onaylandi' WHEN '4' THEN 'Paketlendi/Kaydedildi' WHEN '5' THEN 'Onay satirlari olusturulmus' "
sqlQuery = sqlQuery & "WHEN '6' THEN 'Bankaya iletildi' WHEN '7' THEN 'LDX''e iletildi' WHEN '8' THEN 'Arsive gönderildi.' "
sqlQuery = sqlQuery & "WHEN '9' THEN 'Onay Reddi' WHEN '10' THEN 'Doküman Reddi' END AS STATUS, "
sqlQuery = sqlQuery & "CASE INV.CANCELLED WHEN '0' THEN 'İptal Edilmedi' WHEN '1' THEN 'İptal Edildi' END AS İPTAL_DURUMU, "
sqlQuery = sqlQuery & "CASE INV.TRCODE "
sqlQuery = sqlQuery & "WHEN '8' THEN 'Toptan Satış Faturası' WHEN '3' THEN 'Toptan Satış İade Faturası' "
sqlQuery = sqlQuery & "WHEN '9' THEN 'Verilen Hizmet Faturası' WHEN '14' THEN 'Satış Fiyat Farkı Faturası' "
sqlQuery = sqlQuery & "WHEN '1' THEN 'Satınalma Faturası' WHEN '4' THEN 'Alınan Hizmet Faturası' "
sqlQuery = sqlQuery & "WHEN '13' THEN 'Satınalma Fiyat Farkı Faturası' END AS TRCODE, "
sqlQuery = sqlQuery & "CASE APPROVAL.PROFILEID WHEN '0' THEN 'Temel Fatura' WHEN '1' THEN 'Ticari Fatura' END AS PROFILEID "
sqlQuery = sqlQuery & "FROM [" & dbNameLKS & "].." & firmaPrefix & "_01_INVOICE INV WITH(NOLOCK) "
sqlQuery = sqlQuery & "LEFT JOIN [connect]..LG_" & connectFirmaKodu & "_APPROVAL APPROVAL WITH(NOLOCK) "
sqlQuery = sqlQuery & "ON INV.FICHENO = APPROVAL.DOCNR AND INV.TRCODE IN (1, 3, 4, 8, 9, 13, 14) "
sqlQuery = sqlQuery & "LEFT JOIN [" & dbNameLKS & "].." & firmaPrefix & "_CLCARD CL WITH(NOLOCK) ON INV.CLIENTREF = CL.LOGICALREF "
sqlQuery = sqlQuery & "WHERE INV.DATE_ BETWEEN '" & startDate & "' AND '" & endDate & "' "
rs.Open sqlQuery, conn
hedefWS.Cells.Clear
If Not rs.EOF Then
For i = 1 To rs.Fields.Count
hedefWS.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
hedefWS.Cells(2, 1).CopyFromRecordset rs
hedefWS.Cells.EntireColumn.AutoFit
sonSatir = hedefWS.Cells(Rows.Count, 1).End(xlUp).Row
sonSutun = hedefWS.Cells(1, Columns.Count).End(xlToLeft).Column
hedefWS.Cells.Font.Size = 8
hedefWS.Range("E2:U" & sonSatir).NumberFormat = "#,##0.00"
With hedefWS.Range(hedefWS.Cells(1, 1), hedefWS.Cells(sonSatir, sonSutun)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
End With
Else
MsgBox "Seçilen tarih aralığında Connect verisi bulunamadı!", vbInformation
End If
rs.Close
conn.Close
End Sub
BU şekilde düzenleren düzelecektir. değiştirdiğim satırlarıda kırmızı ile belirttim
