[엑셀 VBA] 파워쿼리 자동화 4. CRUD

'SQL문 만들어 반환하기
Public Function getSQLByExpense(prefix As String, tbName As String) As String
   Dim SDATE As Date: SDATE = shEnquiry.Range(prefix & "_SDATE").Value
   Dim EDATE As Date: EDATE = shEnquiry.Range(prefix & "_EDATE").Value
   Dim db_DATE_field As String: db_DATE_field = "DATE_" & prefix
 
   Dim strSQL As String
   Dim POSITION1 As String: POSITION1 = shEnquiry.Range(prefix & "_POSITION1").Value
 
   If UCase(POSITION1) = "ALL" Then
      strSQL = "SELECT * FROM `" & tbName & "` WHERE " & db_DATE_field & ">='" & SDATE & "' AND " & db_DATE_field & "<='" & EDATE & "' ORDER BY POSITION1, EID, " & db_DATE_field & ";"
   Else
      strSQL = "SELECT * FROM `" & tbName & "` WHERE POSITION1='" & POSITION1 & "' AND (" & db_DATE_field & ">='" & SDATE & "' AND " & db_DATE_field & "<='" & EDATE & "') ORDER BY POSITION1, EID, " & db_DATE_field & ";"
   End If
 
   'Debug.Print strSQL
   getSQLByExpense = strSQL
End Function

' CA 데이터 갱신하기
Public Sub CA_Refresh()
   Call mdGlobal.setWorksheets
 
   Dim strFormula As String
   Dim strSQL As String: strSQL = ThisWorkbook.Worksheets("ENQUIRY").getSQLByExpense("CA", "ys_tbCA")
   svName = GetCustomPropertyValue("server")
   dbName = GetCustomPropertyValue("database")
 
   strFormula = "let Source = MySQL.Database(""" & svName & """, """ & dbName & """, [ReturnSingleDatabase=true, Query=""" & strSQL & """]), #""Transform"" = Table.TransformColumnTypes(Source,{{""DATE_CA"", type date}}) in #""Transform"""
 
   ThisWorkbook.Queries("qryCA").formula = strFormula
 
 
   On Error GoTo Error
   Application.EnableEvents = False
      ws.ENQUIRY.ListObjects("tableCA").TableObject.Refresh
      Call toolsSheet.fillRowNumber(ws.ENQUIRY.Cells(10, "B"), "D10")
   Application.EnableEvents = True
 
   Exit Sub
Error:
   MsgBox Err.Description
End Sub

'선택한 셀이 ListObject이면 선택한 셀의 행 번호 가져오기
Public Function getListObjectRowNumber() As Long
   If TypeName(Selection.ListObject) = "ListObject" Then
      getListObjectRowNumber = Selection.row - Selection.ListObject.Range.row
      Exit Function
   End If
   getListObjectRowNumber = -1
End Function

' CA UPDATE
Public Sub CA_Update()
   mdGlobal.set_query_data
   Dim row_num: row_num = ThisWorkbook.Worksheets("ENQUIRY").getListObjectRowNumber
 
   Dim id: id = lobj.tableCA.DataBodyRange(row_num, 1).Value
   If id = "" Or Not IsNumeric(id) Then
      MsgBox "There is no 'id' or not a valid 'id' for updating.": Exit Sub
   End If
 
   Dim arrFields(): arrFields = toolsSheet.RangeToArray(lobj.tableCA.HeaderRowRange)
   Dim arrValues(): arrValues = toolsSheet.RangeToArray(lobj.tableCA.DataBodyRange.Rows(row_num))
 
   Call mdGlobal.setMySQL(True)
      If MySQL.Update_Recordset(db.CA, id, arrFields, arrValues) = True Then MsgBox "Updated ..."
   Call mdGlobal.setMySQL(False)
End Sub

' 표 객체
Type pListObjects
   tableCA           As ListObject
   tableBEN          As ListObject
   tableCOMMUTE      As ListObject
   tableOT           As ListObject
   tableEMP          As ListObject
   tableCASHIER      As ListObject
   tableDINING       As ListObject
   tableDISHWASHER   As ListObject
   tableMANAGER      As ListObject
   tableBENEFITS     As ListObject
   tableSalary       As ListObject
   tableSalary_BEN   As ListObject
   tableSalary_CA    As ListObject
End Type
Public lobj As pListObjects이

' CA DELETE
Public Sub CA_Delete()
   If MsgBox("Sure to DELETE?", vbYesNoCancel, "DELETE") <> vbYes Then Exit Sub
 
   mdGlobal.setAll
   Dim row_num: row_num = ThisWorkbook.Worksheets("ENQUIRY").getListObjectRowNumber
 
   Dim id: id = lobj.tableCA.DataBodyRange(row_num, 1).Value
 
   If id = "" Or Not IsNumeric(id) Then
      MsgBox "There is no 'id' or not a valid 'id' for updating.": Exit Sub
   End If
 
   Call mdGlobal.setMySQL(True)
      If MySQL.Delete_Using_Conn(id, db.CA) = True Then
 
         lobj.tableCA.ListRows(row_num).Delete
         Call fillRowNumber(Cells(10, "B"), "D10")
 
         MsgBox "Deleted ..."
      End If
   Call mdGlobal.setMySQL(False)
End Sub

'MySQL에 삽압후 Sheet에도 삽입
Private Sub btnInsertCA_Click()
   With Me
      If valueCheck(.cboPos, "Select POSITION, please") = False Then Exit Sub
      If valueCheck(.cboEmpName, "Select Employee's Name, please") = False Then Exit Sub
      If valueCheck(.txtDateCA, "Select CA DATE, please") = False Then Exit Sub
      If valueCheck(.cboManager, "Select Who approved this CA, please") = False Then Exit Sub
 
      Dim EID As String: EID = .cboEmpName.list(.cboEmpName.ListIndex, 0)
      Dim FIRST_NAME As String: FIRST_NAME = .cboEmpName.Value
      Dim POSITION1 As String: POSITION1 = .cboPos.Value
      Dim DATE_CA As Date: DATE_CA = .txtDateCA.Value
      Dim CA_FOR As String: CA_FOR = "CA"
      Dim AMOUNT As Single: AMOUNT = .txtAmountCA.Value
      Dim APPROVED As String: APPROVED = .cboManager.Value
      Dim INPUTER As String: INPUTER = GetCustomPropertyValue("admin")
   End With
 
   Dim arrFields(), arrValues()
   Dim id
 
   mdGlobal.set_query_data
   arrFields = Array("EID", "FIRST_NAME", "POSITION1", "DATE_CA", "CA_FOR", "AMOUNT", "APPROVED", "INPUTER")
   arrValues = Array(EID, FIRST_NAME, POSITION1, DATE_CA, CA_FOR, AMOUNT, APPROVED, INPUTER)
 
   Call mdGlobal.setMySQL(True)
      id = MySQL.Insert_Array(db.CA, arrFields, arrValues)
      Debug.Print id
      If IsNumeric(id) Then
         Dim listRow As listRow
         Set listRow = lobj.tableCA.ListRows.Add(AlwaysInsert:=True)
 
         ReDim arrValues(1 To 10)
         arrValues = Array(id, EID, FIRST_NAME, POSITION1, DATE_CA, CA_FOR, AMOUNT, APPROVED, INPUTER, VBA.Now)
         listRow.Range.Value = arrValues
         mdGlobal.setWorksheets
         Call toolsSheet.fillRowNumber(ws.ENQUIRY.Cells(10, "B"), "D10")
      End If
   Call mdGlobal.setMySQL(False)
End Sub