MALICIOUS
100
Risk Score
Malware Insights
MITRE ATT&CK
T1566.001 Spearphishing Attachment
T1059.005 Visual Basic
T1539 Steal or Forge Credentials
The sample is an Excel file containing VBA macros and exhibits a high-severity heuristic for MFA/one-time-code harvesting lures. The document body, while containing financial data, also includes an email address 'vietnamskynvkd01@gmai.com' which is likely part of the phishing pretext. The VBA macro code, though truncated, suggests it is designed to execute malicious actions, potentially related to credential theft.
Heuristics 3
-
ADODB.RecordSet — CVE-2015-0097 related high CVE_2015_0097_RELATEDADODB.RecordSet — CVE-2015-0097 related
-
MFA / one-time-code harvesting lure high SE_MFA_LUREDocument asks for a one-time code, authenticator approval, or MFA confirmation — consistent with credential phishing kits that steal session tokens or abuse multi-factor authentication
-
VBA macros detected medium OLE_VBA_MACROSDocument contains VBA macro code
Extracted artifacts 1
Files carved from inside the sample during analysis.
| Filename | Kind | Source | Size |
|---|---|---|---|
macros.bas |
vba-macro | oletools.olevba.extract_macros (decoded VBA source) | 28620 bytes |
SHA-256: 8d2f5307692cfac8bdd088fff907dc8b36500e8529a66bb8665c0b903d4c1a4d |
|||
Preview scriptFirst 1,000 lines of the extracted script
Attribute VB_Name = "ThisWorkbook"
Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Attribute VB_Name = "Sheet1"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Attribute VB_Name = "Sheet2"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Attribute VB_Name = "Sheet3"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Attribute VB_Name = "Module1"
Public bCheckPass As Boolean
Public bNotCheck As Boolean
Public gdbConnect As ADODB.Connection
Public Sub InAn(cn As ADODB.Connection, sql As String, tungay As Date, denngay As Date, sLoaiCN As String, tkttype As String, sMaDT As String, sUserName As String)
'On Error GoTo Loi
' =======================================================
' 1. KHAI BAO BIEN
' =======================================================
Dim hang As Long
Dim vheader As Integer
Dim STT As Long
Dim sql0 As String, sql1 As String, sql2 As String, sqlRM As String
Dim sqlT3 As String
Dim tmpsql As String
Dim vQTQN As String, vDienGiai As String
Dim vMaKH As String, vMaDT As String
Dim AgentID As String, NhomKH As String, TenKH As String, vAG As String
Dim AmountVND As Double
Dim vNoDk As Double
Dim rs As New ADODB.Recordset
Dim rs0 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
Dim ws As Worksheet
' Gan sheet bao cao hien tai
Set ws = ThisWorkbook.Sheets("Sheet1")
' =======================================================
' 2. KHOI TAO MOI TRUONG VA GIA TRI
' =======================================================
cn.CursorLocation = adUseClient
cn.CommandTimeout = 0
Set gdbConnect = cn
hang = 14
AmountVND = 0
vNoDk = 0
Application.Cursor = xlWait
Application.DisplayAlerts = False
' Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
bCheckPass = True
bNotCheck = False
' =======================================================
' 3. THONG TIN HEADER DAI LY
' =======================================================
sql1 = " Select Top 1 Agent_Code From dbo.tblKH_KhachHang tt "
Set rs = cn.Execute(sql1)
AgentID = IIf(IsNull(rs.Fields("Agent_Code")), "DANGTQ", rs.Fields("Agent_Code"))
vAG = AgentID ' Gan gia tri vAG cho cau lenh SQL ben duoi
sql0 = "Select * From tblRC_Agents Where CODE ='" & AgentID & "'"
Set rs0 = cn.Execute(sql0)
vheader = 1
If Not rs0.EOF Then
Cells(vheader + 1, 1) = IIf(IsNull(rs0.Fields("NAME")), "", rs0.Fields("NAME"))
Cells(vheader + 2, 1) = IIf(IsNull(rs0.Fields("ADDRESS")), "", rs0.Fields("ADDRESS"))
Cells(vheader + 3, 1) = "Tel: " & IIf(IsNull(rs0.Fields("TEL")), "", rs0.Fields("TEL")) & " - Fax: " & IIf(IsNull(rs0.Fields("FAX")), "", rs0.Fields("FAX"))
Cells(vheader + 4, 1) = "Email: " & IIf(IsNull(rs0.Fields("EMAIL")), "", rs0.Fields("EMAIL")) + " - Website: " & IIf(IsNull(rs0.Fields("WEBSITE")), "", rs0.Fields("WEBSITE"))
Cells(23, 1) = Cells(23, 1) & IIf(IsNull(rs0.Fields("NAME")), "", rs0.Fields("NAME"))
Cells(24, 1) = Cells(24, 1) & IIf(IsNull(rs0.Fields("ADDRESS")), "", rs0.Fields("ADDRESS"))
Cells(25, 1) = Cells(25, 1) & "VND: " & IIf(IsNull(rs0.Fields("Account")), "", rs0.Fields("Account")) & "USD: " & IIf(IsNull(rs0.Fields("Account_USD")), "", rs0.Fields("Account_USD"))
Cells(26, 1) = Cells(26, 1) & IIf(IsNull(rs0.Fields("Bank")), "", rs0.Fields("Bank"))
Cells(27, 1) = Cells(27, 1) & IIf(IsNull(rs0.Fields("Bank_Address")), "", rs0.Fields("Bank_Address"))
Cells(30, 17) = Cells(30, 17) & IIf(IsNull(rs0.Fields("NAME")), "", rs0.Fields("NAME"))
rs0.MoveNext
End If
Cells(4, 7) = " From date: " & Format(tungay, "dd/MM/yyyy")
Cells(5, 7) = " To date: " & Format(denngay, "dd/MM/yyyy")
' =======================================================
' 4. TAO BANG TAM #tmpBC TU FUNCTION SIEU TOC
' =======================================================
sql0 = "Declare @Agent_n_Others NVARCHAR(357), @MA_DT NVARCHAR(50), @FromDate DATETIME, @EndDate DATETIME, @LoaiCN NVARCHAR(10) "
sql0 = sql0 & " Set @Agent_n_Others = '" & vAG & "' "
sql0 = sql0 & " Set @LoaiCN = '" & sLoaiCN & "' "
sql0 = sql0 & " Set @FromDate = convert(datetime, '" & Format(tungay, "dd/MM/yyyy") & "',103) "
sql0 = sql0 & " Set @EndDate = convert(datetime, '" & Format(denngay, "dd/MM/yyyy") & "',103) "
sql0 = sql0 & " Set @MA_DT = '" & sMaDT & "' "
sql0 = sql0 & " SELECT "
sql0 = sql0 & " (Case When Curr <> 'VND' Then 'QT' Else (Case When dbo.FNC_GET_QT_OR_QN(route) = 'QT' Then 'QT' Else 'QN' End) End) as QTQN, "
sql0 = sql0 & " Comm*roe as Comm_VND, "
sql0 = sql0 & " TT.*, (Select KH.TenKhachHang From tblKH_KhachHang KH Where MaKhachHang = tt.Cust_Code) as TenKhachHang "
sql0 = sql0 & " INTO #tmpBC FROM [dbo].[FNC_CNKH_F3] (@Agent_n_Others, @MA_DT, @FromDate, @EndDate, @LoaiCN) TT"
Set rs0 = cn.Execute(sql0)
sql0 = "Select TOP 1 Report_Config From #tmpBC "
Set rs = cn.Execute(sql0)
Dim configStr As String
configStr = rs.Fields("Report_Config").Value
' =======================================================
' 5. TINH TOAN SO DU VA THONG TIN KHACH HANG
' =======================================================
vMaDT = sMaDT
NhomKH = sLoaiCN
sqlT3 = " Declare @Agent NVARCHAR(10),@MA_DT NVARCHAR (15), @FromDate DATETIME, @EndDate DATETIME, @LoaiCN NVARCHAR (10) "
sqlT3 = sqlT3 + " Set @Agent = '" & AgentID & "' "
If vMaDT <> "" Then
sqlT3 = sqlT3 + " Set @MA_DT ='" & vMaDT & "' "
Else
sqlT3 = sqlT3 + " Set @MA_DT ='' "
End If
sqlT3 = sqlT3 + " Set @LoaiCN = '" & sLoaiCN & "' "
sqlT3 = sqlT3 + " Set @FromDate = convert(datetime, '" & Format(tungay, "dd/MM/yyyy") & "',103) "
sqlT3 = sqlT3 + " Set @EndDate = convert(datetime, '" & Format(denngay, "dd/MM/yyyy") & "',103) "
sqlT3 = sqlT3 + " Select "
sqlT3 = sqlT3 + " Sum(Case When DUDK >0 Then isnull(DUDK,0) Else 0 End) as DUNODK, "
sqlT3 = sqlT3 + " Sum(Case When DUDK <0 Then isNull(DUDK,0) Else 0 End) as DUCODK, "
sqlT3 = sqlT3 + " Sum(PSNO),Sum(PSCO), 0 as LKPSNO, 0 as LKPSCO, "
sqlT3 = sqlT3 + " Sum(Case When DUCK >0 Then isNull(DUCK,0) Else 0 End) as DUNOCK, "
sqlT3 = sqlT3 + " Sum(Case When DUCK <0 Then isNull(DUCK,0) Else 0 End) as DUCOCK "
sqlT3 = sqlT3 + " From [FNC_TongHopCNKH](@Agent ,@MA_DT , @FromDate , @EndDate , @LoaiCN )"
Set rs0 = cn.Execute(sqlT3)
If Not rs0.EOF Then
Cells(9, 19) = IIf(IsNull(rs0.Fields("DUNODK")), 0, (rs0.Fields("DUNODK"))) + IIf(IsNull(rs0.Fields("DUCODK")), 0, (rs0.Fields("DUCODK")))
vNoDk = Cells(9, 19)
Cells(22, 19) = IIf(IsNull(rs0.Fields("DUNOCK")), 0, rs0.Fields("DUNOCK")) + IIf(IsNull(rs0.Fields("DUCOCK")), 0, rs0.Fields("DUCOCK"))
End If
' =======================================================
' 5(2). BANG TONG HOP (SUMMARY) CUA TAT CA KHACH HANG
' =======================================================
sql2 = "SELECT (CASE WHEN Tkt_type = 'V' THEN 'S' ELSE Tkt_Type END) AS TKT_TYPE, " & _
"SUM(Fare*ROE) AS Fare, SUM(Tax*ROE) AS Tax, SUM(Charge*ROE) AS Charge, SUM(Vat*ROE) AS Vat, " & _
"SUM(SF*ROE) AS SF, SUM(Penalty*ROE) AS Penalty, " & _
"SUM((Fare+Tax+Charge+Vat)*ROE) AS TongGiaVe, " & _
"SUM(AIR_AMT*ROE) AS AIR_AMT, SUM(AIR_AMT_VND) AS AIR_AMT_VND, " & _
"SUM(Net_NT*ROE) AS Net_NT, SUM(PT_NT*ROE) AS PT_NT, SUM(PTNT_VND) AS PTNT_VND, " & _
"SUM(NetNT_VND) AS NetNT_VND, SUM(COMM_VND) AS COMM " & vbNewLine & _
"INTO dbo.#tmpBC1 FROM dbo.#tmpBC tt " & _
"GROUP BY (CASE WHEN Tkt_type = 'V' THEN 'S' ELSE Tkt_Type END)"
Set rs = cn.Execute(sql2)
sql2 = "SELECT (CASE WHEN Tkt_Type ='S' THEN 'Mua ve' WHEN Tkt_Type ='R' THEN 'Hoan ve' ELSE 'Thanh toan' END) AS ND, * " & vbNewLine & _
"FROM dbo.#tmpBC1 tt " & _
"ORDER BY CASE WHEN Tkt_Type = 'S' THEN '1' WHEN Tkt_Type = 'V' THEN '2' WHEN Tkt_Type = 'R' THEN '3' ELSE '4' END"
Set rs = cn.Execute(sql2)
' =======================================================
' KHAI BAO BIEN TAM (De vong lap chay sieu toc) - BOTTOM UP
' =======================================================
Dim strTktType As String
Dim valTongGiaVe As Double
Dim valTongGiaVe_VND As Double
Dim valSF As Double
Dim valPenalty As Double
Dim valComm As Double
Dim valNetNT_VND As Double
AmountVND = 0
hang = 16
While Not rs.EOF
' Nhac du lieu tu Recordset vao RAM dung 1 lan
strTktType = IIf(IsNull(rs.Fields("TKT_TYPE")), "", rs.Fields("TKT_TYPE"))
valTongGiaVe = IIf(IsNull(rs.Fields("PTNT_VND")), 0, rs.Fields("PTNT_VND"))
valSF = IIf(IsNull(rs.Fields("SF")), 0, rs.Fields("SF"))
valPenalty = IIf(IsNull(rs.Fields("Penalty")), 0, rs.Fields("Penalty"))
valComm = IIf(IsNull(rs.Fields("COMM")), 0, rs.Fields("COMM"))
valNetNT_VND = IIf(IsNull(rs.Fields("NetNT_VND")), 0, rs.Fields("NetNT_VND"))
' Cong don tien thanh toan
AmountVND = AmountVND + valNetNT_VND
' RAI DU LIEU RA EXCEL THEO TOA DO COT
Select Case strTktType
Case "S" ' Mua ve (Do vao dong hien tai)
Cells(hang, 17).Value = valTongGiaVe ' Cot 1 (O): Tong gia ve
Cells(hang, 18).Value = valComm ' Cot 5 (R): Hoa hong (Comm)
Cells(hang, 19).Value = valNetNT_VND ' Cot 6 (S): Phai thanh toan
Cells(hang, 20).Value = valNetNT_VND ' Cot 7 (T): Phai thanh toan VND
Case "R" ' Hoan ve (Do vao dong ke tiep)
Cells(hang + 1, 17).Value = valTongGiaVe
Cells(hang + 1, 18).Value = valComm
Cells(hang + 1, 19).Value = valNetNT_VND
Cells(hang + 1, 20).Value = valNetNT_VND
Case "D" ' Thanh toan (Do vao dong duoi cung)
Cells(hang + 2, 19).Value = valNetNT_VND
Cells(hang + 2, 20).Value = valNetNT_VND
End Select
rs.MoveNext
Wend
Cells(hang + 3, 20) = AmountVND
' =======================================================
' THONG TIN KHACH HANG CHI TIET
' =======================================================
If vMaDT <> "" And Len(vMaDT) <= 15 Then
sql1 = " Select Top 1 isNull(TenKhachHang,' ') TenKhachHang , isNull(TenGiaodich,' ') TenGiaodich, isNull(email,' ') as email , isNull(DienthoaiCQ,' ') as DienthoaiCQ From dbo.tblKH_KhachHang tt Where MaKhachHang ='" & vMaDT & "' "
Set rs = cn.Execute(sql1)
If Not rs.EOF Then
TenKH = IIf(IsNull(rs.Fields("TenKhachHang")), vMaDT, rs.Fields("TenKhachHang"))
Cells(8, 1) = Cells(8, 1) & " " & TenKH
Cells(9, 1) = Cells(9, 1) & " - " & IIf(IsNull(rs.Fields("TenGiaodich")), "", rs.Fields("TenGiaodich")) & " -Tel: " & rs.Fields("DienthoaiCQ") & " -eMail: " & rs.Fields("eMail")
End If
End If
' =======================================================
' 6. NHOM THEO KHACH HANG VA DO XUONG EXCEL (COPYFROMRECORDSET)
' =======================================================
sql2 = "Select Cust_code, TenKhachHang, Count(1) as SL, " & vbNewLine
sql2 = sql2 & " Sum(Case When Tkt_type = 'R' Then PTNT_VND Else 0 End ) as Hoan_VND, " & vbNewLine
sql2 = sql2 & " Sum(Case When (Tkt_type = 'S' or tkt_type = 'V') Then Isnull(PTNT_VND,0) Else 0 End ) as Ban_VND, " & vbNewLine
sql2 = sql2 & " Sum(Case When Tkt_type = 'D' Then PTNT_VND Else 0 End ) as Thu_VND, " & vbNewLine
sql2 = sql2 & " Sum(Case When Tkt_type = 'R' Then Comm_VND*(1) Else Comm_VND*(-1) End ) as COMM " & vbNewLine
sql2 = sql2 & " From #tmpBC tt Group by Cust_code, TenKhachHang "
Set rs0 = cn.Execute(sql2)
hang = 14
While Not rs0.EOF
hang = hang + 1
Cells(hang, 1).Select
Selection.EntireRow.Insert
If vMaDT = "" Or Len(vMaDT) > 5 Then
NhomKH = sLoaiCN
sqlT3 = " Declare @Agent NVARCHAR(10),@MA_DT NVARCHAR (15), @FromDate DATETIME, @EndDate DATETIME, @LoaiCN NVARCHAR (10) "
sqlT3 = sqlT3 + " Set @Agent = '" & AgentID & "' "
sqlT3 = sqlT3 + " Set @MA_DT ='" & rs0.Fields("Cust_Code") & "'"
sqlT3 = sqlT3 + " Set @LoaiCN = '" & sLoaiCN & "' "
sqlT3 = sqlT3 + " Set @FromDate = convert(datetime, '" & Format(tungay, "dd/MM/yyyy") & "',103) "
sqlT3 = sqlT3 + " Set @EndDate = convert(datetime, '" & Format(denngay, "dd/MM/yyyy") & "',103) "
sqlT3 = sqlT3 + " Select MA_DT,TEN_DT, "
sqlT3 = sqlT3 + " (Case When DUDK >0 Then DUDK Else 0 End) as DUNODK, "
sqlT3 = sqlT3 + " (Case When DUDK <0 Then DUDK Else 0 End) as DUCODK, "
sqlT3 = sqlT3 + " PSNO,PSCO, 0 as LKPSNO, 0 as LKPSCO, "
sqlT3 = sqlT3 + " (Case When DUCK >0 Then DUCK Else 0 End) as DUNOCK, "
sqlT3 = sqlT3 + " (Case When DUCK <0 Then DUCK Else 0 End) as DUCOCK "
sqlT3 = sqlT3 + " From [FNC_TongHopCNKH](@Agent ,@MA_DT , @FromDate , @EndDate , @LoaiCN )"
sqlT3 = sqlT3 + " Where Ma_DT = '" & rs0.Fields("Cust_Code") & "'"
Set rst3 = cn.Execute(sqlT3)
If Not rs0.EOF Then
Range("A" & hang & ":T" & hang & "").Font.Bold = True
Range("A" & hang & ":T" & hang & "").Font.ColorIndex = 54
Cells(hang, 5) = rs0.Fields("Cust_Code") & " - " & IIf(IsNull(rs0.Fields("TenKhachHang")), "", (rs0.Fields("TenKhachHang")))
Cells(hang, 17) = "Begin Debit"
vNoDk = 0
If Not rst3.EOF Then
Cells(hang, 20) = IIf(IsNull(rst3.Fields("DUNODK")), 0, (rst3.Fields("DUNODK"))) + IIf(IsNull(rst3.Fields("DUCODK")), 0, (rst3.Fields("DUCODK")))
vNoDk = Cells(hang, 20)
End If
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
End If
' ===============================================================
' THUC CHIEN: COPYFROMRECORDSET SIEU TOC CHO TUNG KHACH
' ===============================================================
sql1 = "SELECT "
sql1 = sql1 & " ROW_NUMBER() OVER(ORDER BY tt.ISS_Date, CASE WHEN tt.Tkt_Type = 'D' THEN 0 WHEN tt.Tkt_Type = 'S' THEN 1 WHEN tt.Tkt_Type = 'V' THEN 2 WHEN tt.Tkt_Type = 'R' THEN 3 ELSE 4 END, tt.TKT_NO, ISNULL(tt.Route,'Z')) as STT, "
sql1 = sql1 & " tt.ISS_DATE, ISNULL(tt.TKT_NO, ''), ISNULL(tt.ROUTE, ''), ISNULL(tt.Pax_Name, ''), "
sql1 = sql1 & " ISNULL(tt.DienGiai_PNR, ''), "
sql1 = sql1 & " ISNULL(tt.TKT_TYPE, ''), ISNULL(tt.CURR, ''), ISNULL(tt.ROE, 1), "
sql1 = sql1 & " ISNULL(tt.FARE, 0), "
sql1 = sql1 & " ISNULL(tt.TAX, 0), ISNULL(tt.CHARGE, 0), ISNULL(tt.VAT, 0), "
sql1 = sql1 & " ISNULL(tt.PENALTY, 0), "
sql1 = sql1 & " ISNULL(tt.SF, 0), "
sql1 = sql1 & " ISNULL(tt.PT_NT, 0),"
sql1 = sql1 & " ISNULL(tt.PTNT_VND, 0), "
sql1 = sql1 & " ISNULL(tt.Comm_VND, 0), "
sql1 = sql1 & " ISNULL(tt.Net_NT, 0), ISNULL(tt.NetNT_VND, 0), "
sql1 = sql1 & " 0, CASE WHEN tt.TKT_TYPE = 'D' THEN CONVERT(VARCHAR(10), tt.ISS_DATE, 103)+' / '+TAIKHOAN ELSE NULL END, "
sql1 = sql1 & " tt.PNR,Class,Flight_Nbr,Dep_date, Receipt_Nbr,STAFF_CODE,Remarks_RC,Remarks"
sql1 = sql1 & " FROM dbo.#tmpBC tt "
sql1 = sql1 & " WHERE tt.Cust_Code = '" & rs0.Fields("Cust_Code") & "' "
sql1 = sql1 & " ORDER BY tt.ISS_Date, CASE WHEN tt.Tkt_Type = 'D' THEN 0 WHEN tt.Tkt_Type = 'S' THEN 1 WHEN tt.Tkt_Type = 'V' THEN 2 WHEN tt.Tkt_Type = 'R' THEN 3 ELSE 4 END, tt.TKT_NO, ISNULL(tt.Route,'Z')"
Set rs = cn.Execute(sql1)
Dim numRows As Variant
numRows = CLng(rs.RecordCount)
If numRows > 0 Then
' Chen dong
Rows(hang & ":" & hang + numRows - 1).Insert Shift:=xlDown
' Do du lieu
Cells(hang, 1).CopyFromRecordset rs
' Tay format in dam di truyen
Range("A" & hang & ":W" & hang + numRows - 1).Font.Bold = False
Range("A" & hang & ":W" & hang + numRows - 1).Font.ColorIndex = xlAutomatic
' Tinh Luy Ke (Cot 21) bang Cong thuc Excel
Cells(hang, 21).Value = vNoDk + Cells(hang, 20).Value
If numRows > 1 Then
Range(Cells(hang + 1, 21), Cells(hang + numRows - 1, 21)).FormulaR1C1 = "=R[-1]C + RC[-1]"
Range(Cells(hang + 1, 21), Cells(hang + numRows - 1, 21)).Value = Range(Cells(hang + 1, 21), Cells(hang + numRows - 1, 21)).Value
End If
vNoDk = Cells(hang + numRows - 1, 21).Value
' Quet In dam cho rieng loai Tkt_Type = 'D' (Dong thanh toan)
Dim arrLoai As Variant
If numRows = 1 Then
If Cells(hang, 7).Value = "D" Then
Range("A" & hang & ":D" & hang).Font.Bold = True
End If
Else
arrLoai = Range(Cells(hang, 7), Cells(hang + numRows - 1, 7)).Value
Dim j As Long
For j = 1 To numRows
If arrLoai(j, 1) = "D" Then
Range("A" & hang + j - 1 & ":D" & hang + j - 1).Font.Bold = True
End If
Next j
End If
hang = hang + numRows
End If
rs.Close
hang = hang + 1
Cells(hang, 1).Select: Selection.EntireRow.Insert
' CHOT TONG KET SUB TOTAL TUNG KHACH
If vMaDT = "" Or Len(vMaDT) >= 15 Then
If IIf(IsNull(rs0.Fields("SL")), 0, rs0.Fields("SL")) > 1 Then
Cells(hang, 16) = "Sub.Total: (" & rs0.Fields("Cust_Code") & ")"
Cells(hang, 17) = "Begin."
If Not rst3.EOF Then
Cells(hang, 20) = IIf(IsNull(rst3.Fields("DUNODK")), 0, (rst3.Fields("DUNODK"))) + IIf(IsNull(rst3.Fields("DUCODK")), 0, (rst3.Fields("DUCODK")))
End If
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
If IIf(IsNull(rs0.Fields("BAN_VND")), 0, rs0.Fields("BAN_VND")) <> 0 Then
Cells(hang, 17) = "Sold.": Cells(hang, 20) = IIf(IsNull(rs0.Fields("BAN_VND")), 0, rs0.Fields("BAN_VND"))
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
If IIf(IsNull(rs0.Fields("HOAN_VND")), 0, rs0.Fields("HOAN_VND")) <> 0 Then
Cells(hang, 17) = "Ref.": Cells(hang, 20) = IIf(IsNull(rs0.Fields("HOAN_VND")), 0, rs0.Fields("HOAN_VND"))
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
If IIf(IsNull(rs0.Fields("COMM")), 0, rs0.Fields("COMM")) <> 0 Then
Cells(hang, 17) = "Comm.": Cells(hang, 20) = IIf(IsNull(rs0.Fields("COMM")), 0, rs0.Fields("COMM"))
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
If IIf(IsNull(rs0.Fields("THU_VND")), 0, rs0.Fields("THU_VND")) <> 0 Then
Cells(hang, 17) = "Paid.": Cells(hang, 20) = IIf(IsNull(rs0.Fields("THU_VND")), 0, rs0.Fields("THU_VND"))
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
End If
If vMaDT = "" Or Len(vMaDT) >= 15 Then
If Not rst3.EOF Then
Range("A" & hang & ":T" & hang & "").Font.ColorIndex = 50
Cells(hang, 17) = "End Debit"
Cells(hang, 20) = IIf(IsNull(rst3.Fields("DUNOCK")), 0, rst3.Fields("DUNOCK")) + IIf(IsNull(rst3.Fields("DUCOCK")), 0, rst3.Fields("DUCOCK"))
hang = hang + 1: Cells(hang, 1).Select: Selection.EntireRow.Insert
End If
End If
End If
rs0.MoveNext
Wend
' =======================================================
' 7. FORMAT COT SO VA TU DONG GIAN COT (CHI QUET 1 LAN DUY NHAT)
' =======================================================
Dim startRow As Long, endRow As Long
Dim curr As String
startRow = 15 ' Dong bat dau du lieu cua ban ghi dau tien
endRow = hang ' Chot dong cuoi cung vua duoc do du lieu hoan tat o vong lap tren
For i = startRow To endRow
' Lay ma tien te o cot H
curr = UCase(Trim(Cells(i, "H").Value))
' Chi format neu o cot H co du lieu, bo qua cac dong Sub.Total hoac tieu de rong
If curr <> "" Then
If curr <> "VND" Then
' Neu la USD hoac ngoai te khac: De 2 so thap phan
Range(Cells(i, "I"), Cells(i, "P")).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
Range("S" & i).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
Else
' Neu la VND: Bo so thap phan, ep ve so chan
Range(Cells(i, "I"), Cells(i, "P")).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
End If
End If
Next i
' Tu dong gian cot cho vua khit so tien de khong bi loi ######
'Columns("J:U").AutoFit
'MsgBox ("Bug")
Dim configArr() As String
' =======================================================
' 8. DON DEP, BAO MAT VA HIEN THI THEO CAU HINH (3 CAP DO)
' =======================================================
If configStr <> "" Then
configArr = Split(configStr, "|")
' Chon loc va xu ly cac cot ben trong Form (Tu 28 ve 1)
Dim c As Integer
Dim r As Long
For c = 52 To 1 Step -1
If UBound(configArr) >= (c - 1) Then
If Trim(configArr(c - 1)) = "0" Then
' --- CAP DO 0: HARD HIDE (XOA DATA + AN COT DE BAO MAT) ---
' Quet tu dong 17 den dung dong "hang" co du lieu
For r = 15 To hang
If ActiveSheet.Cells(r, c).MergeCells = False Then
ActiveSheet.Cells(r, c).ClearContents
End If
Next r
' An cot di
ActiveSheet.Columns(c).Hidden = True
ElseIf Trim(configArr(c - 1)) = "2" Then
' --- CAP DO 2: SOFT HIDE (CHI AN COT CHO GON, GIU NGUYEN DATA) ---
ActiveSheet.Columns(c).Hidden = True
ElseIf Trim(configArr(c - 1)) = "1" Then
' --- CAP DO 1: SHOW (BAT HIEN THI COT NAY LEN) ---
If ActiveSheet.Columns(c).Hidden = True Then
ActiveSheet.Columns(c).Hidden = False
End If
End If
End If
Next c
End If
' =======================================================
' DUA CON TRO VA MAN HINH VE DAU TRANG (O A1)
' =======================================================
' 1. Chon lai o A1 (Dong 1, Cot 1)
ActiveSheet.Range("A1").Select
' 2. Cuon man hinh ve sat goc tren cung ben trai (Tranh viec bi treo thanh cuon)
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
' =======================================================
' 8. DON DEP VA THOAT
' =======================================================
Application.Cursor = xlNormal
bNotCheck = True
bCheckPass = False
On Error Resume Next
cn.Execute "Drop table #tmpBC"
cn.Execute "Drop table #tmpBC1"
On Error GoTo 0
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Exit Sub
Loi:
' 1. Tra lai trang thai binh thuong cho Excel TRUOC TIEN
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.Cursor = xlNormal
' 2. Bat bang thong bao lich su cho nguoi dung
If Err.Number <> 0 Then
MsgBox "Da xay ra loi trong qua trinh trich xuat du lieu!" & vbCrLf & _
"Chi tiet loi: " & Err.Description, vbCritical, "Thong bao he thong"
End If
End Sub
Public Function CheckPass(gdbConnect As ADODB.Connection, UserName As String, Pass As String) As Boolean
Dim rs11 As ADODB.Recordset
Dim sql As String
sql = "Select * From tblHT_NguoiDung Where MatKhau = '" & Pass & "' And Ten ='" & UserName & "' And IsSupervisor = 1"
Set rs11 = gdbConnect.Execute(sql)
If rs11.RecordCount > 0 Then
bCheckPass = True
CheckPass = True
Else
bCheckPass = False
CheckPass = False
End If
End Function
Public Function Translate(ByVal strIN As String) As String
Dim sTemp As String
Dim i As Long
Dim iLocation As Long
For i = 1 To Len(strIN)
iLocation = (i Mod 5) + 1
sTemp = sTemp + ChrW(AscW(Mid(strIN, i, 1)) Xor AscW(Mid("VNiSC", iLocation, 1)))
Next
Translate = sTemp
End Function
Public Function EncryptString(ByVal strIN As String) As String
gsKEY = Key
If gsKEY = "" Then gsKEY = PrivateKEY
EncryptString = Translate(strIN)
End Function
Public Function DecryptString(ByVal strIN As String) As String
gsKEY = Key
If gsKEY = "" Then gsKEY = PrivateKEY
DecryptString = Translate(strIN)
End Function
Public Function String2Hex(ByVal strData As String) As String
Dim i As Integer
Dim sReturn As String
Dim tmp As String
For i = 1 To Len(strData)
tmp = Hex(AscW(Mid(strData, i, 1)))
If Len(Trim(tmp)) < 4 Then tmp = Right("000" & tmp, 4)
sReturn = sReturn & tmp
Next
String2Hex = sReturn
End Function
Public Function Hex2String(ByVal strData As String) As String
On Error GoTo Loi
Dim i As Integer
Dim sReturn As String
Dim tmp As String
For i = 1 To Len(strData) Step 4
tmp = ChrW(Val("&H" & Mid(strData, i, 4)))
sReturn = sReturn & tmp
Next
Hex2String = sReturn
Exit Sub
Loi:
MsgBox "Error "
End Function
|
|||
Open this report in the interactive analyzer, or submit your own file for analysis.