Можно ли заполнить компонент Farpoint Spread 6.0 vaSpread с помощью SQL-запроса в VB6?
Я написал запрос с использованием T-SQL на SQL Server 2008 R2, который предоставляет правильную информацию, которую мне нужно отобразить в компоненте vaSpread с именем SSlist в Visual Basic 6. Я уже открыл соединение с базой данных, но у меня возникли трудности найти ресурсы о том, как заполнить компонент vaSpread напрямую, используя мой запрос T-SQL. Мне просто нужно отобразить его в точности так, как он отображается при запуске в Microsoft SQL Server Management Studio.
Мой запрос:
SELECT QC.LINE_CD AS 'Line Code', QC.LINE_NM AS 'Line Name', PN.GUBUN, WO.WRK_QTY AS 'Work QTY', CM.LINE_TARGET AS 'Line Target',
CM.RETURN_TARGET AS 'Return Target', SUM(PN.R_QTY) AS 'Rework QTY', SUM(PN.S_QTY) AS 'Scrap QTY',
SUM(PN.UPRC_AMT) AS 'UPRC AMT', (SUM(COALESCE(PN.UPRC_AMT,0)*PN.S_QTY)+SUM(PN.R_QTY)*3.8) AS 'Cost'
FROM QC_LINE_MST AS QC
LEFT JOIN (SELECT PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO, PE.R_QTY, PE.S_QTY, ND.UPRC_AMT FROM PROC_ERR AS PE
INNER JOIN (SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA) AS ND ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD
WHERE PE.WRK_YMD BETWEEN '20161116' AND '20161201' AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0)
) AS PN ON QC.LINE_CD = PN.LINE_CD
LEFT JOIN (SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET FROM COD_MST AS A
INNER JOIN (SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09'
) AS B ON A.CODE = B.CODE
WHERE A.GUBN='QC08') CM ON QC.LINE_CD = CM.CODE
LEFT JOIN (SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY FROM WRK_ORD
WHERE WRK_YMD BETWEEN '20161116' AND '20161201' GROUP BY LINE_CD
) AS WO ON QC.LINE_CD = WO.LINE_CD
GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET
ORDER BY QC.LINE_CD
Я искал в Интернете, пытаясь выяснить, как заполнить мой vaSpread с помощью этого запроса, но либо я смотрю не в том месте, либо ресурсы на Farpoint Spread 6.0 недостаточны. Если у кого-то есть какие-либо идеи о том, как это реализовать, или если вы можете направить меня к какой-нибудь полезной литературе, это будет очень цениться Также, если у кого-то есть идеи, как очистить мой SQL-запрос и сделать его более эффективным, это тоже приветствуется. Я довольно новичок в этом. Спасибо, и дайте мне знать, если мне нужно предоставить больше информации! Я с нетерпением жду ваших предложений.
1 ответ
Проведя дополнительные исследования, я узнал, что вместо использования компонента FarPoint 6.0 vaSpread (не OLEDB) следует использовать компонент FarPoint 6.0 FpSpread (с поддержкой OLEDB), чтобы автоматически заполнять электронную таблицу. Однако требуется метод для автоматического заполнения нового компонента FpSpread:
1) компонент ADODC, подключенный к базе данных
2) хранимая процедура в базе данных
Видя, как у меня уже было активное соединение, а также необходимо использовать определенные записи столбцов в расчетах для заполнения других столбцов, я решил использовать метод заполнения электронных таблиц вручную с использованием циклов FOR. Мой код прилагается ниже, так что любой, у кого есть подобные проблемы, может использовать мой код для идей.
With SSlist
//SQL Query to USA_ERP.QC_LINE_MST Table to receive total number of Rows in Record Set
SqlStmt = CSQL("SELECT COUNT(*) AS 'Count' FROM QC_LINE_MST")
Rs.Open SqlStmt, CN, adOpenForwardOnly, adLockReadOnly
LastRow = Val(Rs.Fields("Count"))
RowB4Last = Val(Rs.Fields("Count")) - 1
.MaxRows = LastRow
Rs.Close
//Formatting for Last Row (Totals row)
For RowCount = 1 To LastRow
.Row = RowCount
.RowHeight(.Row) = 18
//Font and cell formatting for Line Columns
For ColCount = 1 To 1
.Col = ColCount
.CellType = CellTypeStaticText
.TypeHAlign = TypeHAlignCenter
.FontBold = True
.TypeVAlign = TypeVAlignCenter
Next
If .Row = LastRow Then
//Merge for Totals label of Last Row (Totals row)
For ColCount = 1 To 2
.Col = ColCount
.Text = "Totals"
.RowMerge = MergeRestricted
Next
//Font and cell formatting for Last Row (Totals row)
For ColCount = 1 To 15
.Col = ColCount
.CellType = CellTypeStaticText
.TypeHAlign = TypeHAlignCenter
.FontBold = True
.TypeVAlign = TypeVAlignCenter
Next
End If
Next
//Main SQL Query to USA_ERP Database
SqlStmt = CSQL("SELECT QC.LINE_CD AS 'Line Code', QC.LINE_NM AS 'Line Name', PN.GUBUN, WO.WRK_QTY AS 'Work QTY', CM.LINE_TARGET AS 'Line Target', " & _
"CM.RETURN_TARGET AS 'Return Target', SUM(PN.R_QTY) AS 'Rework QTY', SUM(PN.S_QTY) AS 'Scrap QTY', " & _
"SUM(PN.UPRC_AMT) AS 'UPRC AMT', (SUM(COALESCE(PN.UPRC_AMT,0)*PN.S_QTY)+SUM(PN.R_QTY)*3.8) AS 'Cost' " & _
"FROM QC_LINE_MST AS QC " & _
"LEFT JOIN (SELECT PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO, PE.R_QTY, PE.S_QTY, ND.UPRC_AMT FROM PROC_ERR AS PE " & _
"INNER JOIN (SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA) AS ND ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD " & _
"WHERE PE.WRK_YMD BETWEEN '$S' AND '$S' AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0) " & _
") AS PN ON QC.LINE_CD = PN.LINE_CD " & _
"LEFT JOIN (SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET FROM COD_MST AS A " & _
"INNER JOIN (SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09' " & _
") AS B ON A.CODE = B.CODE " & _
"WHERE A.GUBN='QC08') CM ON QC.LINE_CD = CM.CODE " & _
"LEFT JOIN (SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY FROM WRK_ORD " & _
"WHERE WRK_YMD BETWEEN '$S' AND '$S' GROUP BY LINE_CD " & _
") AS WO ON QC.LINE_CD = WO.LINE_CD " & _
"GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET " & _
"ORDER BY QC.LINE_CD " _
, Format(DTPDate(0).Value, "YYYYMMDD"), Format(DTPDate(1).Value, "YYYYMMDD"), Format(DTPDate(0).Value, "YYYYMMDD"), Format(DTPDate(1).Value, "YYYYMMDD"))
Rs.Open SqlStmt, CN, adOpenForwardOnly, adLockReadOnly
While Not Rs.EOF
//Start at First Row for First Record from RecordSet (Rs), loop through all Records from RecordSet (Rs)
For RowCount = 1 To LastRow
.Row = RowCount
//Initialize/Re-initialize calculation variables for every Record
LineScrap = 0
CustomerScrap = 0
ResidentScrap = 0
ReworkQTY = 0
FailCost = 0
//Check to see if LastRow (Totals Row)
If .Row = LastRow Then
//If LastRow, populate columns with Total values
For ColCount = 1 To 15
.Col = ColCount
If .Col = 1 Then
ElseIf .Col = 2 Then
.ColMerge = MergeRestricted
ElseIf .Col = 3 Then
.Text = TotalProduction
ElseIf .Col = 4 Then
.Text = Val(Rs.Fields("Line Target"))
ElseIf .Col = 5 Then
.Text = TotalRework
ElseIf .Col = 6 Then
.Text = TotalScrap
ElseIf .Col = 7 Then
.Text = TotalReworkPPM
ElseIf .Col = 8 Then
.Text = TotalScrapPPM
ElseIf .Col = 9 Then
.Text = TotalFailCosts
ElseIf .Col = 10 Then
.Text = Val(Rs.Fields("Return Target"))
ElseIf .Col = 11 Then
.Text = TotalCustReturn
ElseIf .Col = 12 Then
.Text = TotalOnSiteReturn
ElseIf .Col = 13 Then
.Text = TotalCustReturnPPM
ElseIf .Col = 14 Then
.Text = TotalOnSiteReturnPPM
ElseIf .Col = 15 Then
.Text = TotalScrapPPM
Else
End If
Next
//Close database connection
Rs.Close
//Exit Subroutine logic
Exit Sub
End If
//Choose the correct variable to store "Scrap QTY" value from RecordSet (Rs) based on "GUBUN" value of Record
If IsNull(Rs.Fields("Scrap QTY")) = False Then
If Trim(Rs.Fields("GUBUN")) = "Customer" Then
CustomerScrap = Val(Rs.Fields("Scrap QTY"))
ElseIf Trim(Rs.Fields("GUBUN")) = "On Site" Then
ResidentScrap = Val(Rs.Fields("Scrap QTY"))
ElseIf Trim(Rs.Fields("GUBUN")) = "MIP NG" Then
LineScrap = Val(Rs.Fields("Scrap QTY"))
End If
//If "Scrap QTY" is NULL then set correct variable to 0 based on "GUBUN" value of Record
Else
If Trim(Rs.Fields("GUBUN")) = "Customer" Then
CustomerScrap = 0
ElseIf Trim(Rs.Fields("GUBUN")) = "On Site" Then
ResidentScrap = 0
Else
LineScrap = 0
End If
End If
//Store "Rework QTY" in correct variable
//If "Rework QTY" is NULL, store 0
If IsNull(Rs.Fields("Rework QTY")) = False Then
ReworkQTY = Val(Rs.Fields("Rework QTY"))
Else
ReworkQTY = 0
End If
//Populate spread (SSList) with correct values using RecordSet (Rs) and calculated variables
//Line Column
.Col = 1
.Text = Rs.Fields("Line Code")
//Model Column
.Col = 2
.Text = Rs.Fields("Line Name")
//Prod (EA) Column
.Col = 3
//If "Work QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Trim(Val(Rs.Fields("Work QTY")) + LineScrap)
Else
.Text = 0
End If
//Calculate running total for 'Prod (EA)' Column through all Records/loops
TotalProduction = TotalProduction + Val(.Text)
//In Line Target (PPM) Column
.Col = 4
//If "Line Target" Record is Null set cell value to 0
If IsNull(Rs.Fields("Line Target")) = False Then
.Text = Trim(Val(Rs.Fields("Line Target")))
Else
.Text = 0
End If
//In Line Rework QTY Column
.Col = 5
//If "Rework QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Rework QTY")) = False Then
.Text = ReworkQTY
Else
.Text = 0
End If
//Calculate running total for 'In Line Rework QTY' Column through all Records/loops
TotalRework = TotalRework + Val(.Text)
//In Line Scrap QTY Column
.Col = 6
//Set cell value to LineScrap variable
.Text = LineScrap
//Calculate running total for 'In Line Scrap QTY' Column through all Records/loops
TotalScrap = TotalScrap + Val(.Text)
//In Line Rework PPM QTY Column
.Col = 7
//If "Work QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Round(ReworkQTY / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 6)
Else
.Text = 0
End If
//Calculate running total for 'In Line Rework PPM QTY' Column through all Records/loops
TotalReworkPPM = TotalReworkPPM + Val(.Text)
//In Line Scrap PPM QTY Column
.Col = 8
//If "Work QTY" is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Round(LineScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 6)
Else
.Text = 0
End If
//Calculate runing total for 'In Line Scrap PPM QTY' Column through all Records/loops
TotalScrapPPM = TotalScrapPPM + Val(.Text)
//In Line Fail Costs ($) Column
.Col = 9
//If "GUBUN" Record is "MIP NG" and "Cost" Record is Not Null set cell value to "Cost" Record
//Otherwise, set cell value to 0
If Trim(Rs.Fields("GUBUN")) = "MIP NG" Then
If IsNull(Trim(Rs.Fields("Cost"))) = False Then
.Text = Val(Rs.Fields("Cost"))
Else
.Text = 0
End If
Else
.Text = 0
End If
//Calculate running total for 'In Line Fail Costs ($)' Column through all Records/loops
TotalFailCosts = TotalFailCosts + Val(.Text)
//Customer Return Target PPM QTY Column
.Col = 10
//If "Return Target" Record is Null set cell value to 0
If IsNull(Rs.Fields("Return Target")) = False Then
.Text = Trim(Val(Rs.Fields("Return Target")))
Else
.Text = 0
End If
//Customer Return QTY Column
.Col = 11
//Set cell value to CustomerScrap variable
.Text = CustomerScrap
//Calculate running total for 'Customer Return QTY' Column through all Records/loops
TotalCustReturn = TotalCustReturn + Val(.Text)
//On Site Return QTY Column
.Col = 12
//Set cell value to ResidentScrap variable
.Text = ResidentScrap
//Calculate running total for 'On Site Return QTY' Column through all Records/loops
TotalOnSiteReturn = TotalOnSiteReturn + Val(.Text)
//Customer Return PPM QTY Column
.Col = 13
//If "Work QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Round(CustomerScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 2)
Else
.Text = 0
End If
//Calculate running total for 'Customer Return PPM QTY' Column through all Records/loops
TotalCustReturnPPM = TotalCustReturnPPM + Val(.Text)
//On Site Return PPM QTY Column
.Col = 14
//If "Work QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Round(ResidentScrap / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 2)
Else
.Text = 0
End If
//Calculate running total for 'On Site Return PPM QTY' Column through all Records/loops
TotalOnSiteReturnPPM = TotalOnSiteReturnPPM + Val(.Text)
//Total Loss PPM Column
.Col = 15
//If "Work QTY" Record is Null set cell value to 0
If IsNull(Rs.Fields("Work QTY")) = False Then
.Text = Round((CustomerScrap + LineScrap) / (Val(Rs.Fields("Work QTY")) + LineScrap) * 10 ^ 6, 0)
Else
.Text = 0
End If
//Calculate running total for 'Total Loss PPM' Column through all Records/loops
TotalLossPPM = TotalLossPPM + Val(.Text)
//Move to the next Record in RecordSet (Rs)
Rs.MoveNext
Next
Wend
End With
Этот код запускается с активным подключением к базе данных CN с набором RecordSet Rs. Цикл FOR в основном проходит через каждый столбец каждой строки и заполняет каждую ячейку правильными значениями, необходимыми на основе логики, переходя к следующей записи в RecordSet после каждой строки. Последняя строка в моем SQL-запросе RecordSet представляет собой итоговую строку, которая содержит данные только в определенных столбцах. Достигнув этой последней строки, он заполняет ячейки либо рассчитанными промежуточными итогами, либо, когда это возможно, значениями в RecordSet. После заполнения последней строки таблицы подпрограмма заканчивается.
Я не знаю, есть ли у кого-нибудь интерес к этой проблеме, но, надеюсь, это может кому-то помочь. Возможно, это не идеальный или самый эффективный способ заполнения компонента FarPoint vaSpread, но он работает 100% времени, и в зависимости от вашего запроса SQL вы можете сделать это в будущем. В частности, у меня настроен запрос, поэтому все объединения происходят в одной справочной таблице (QC.LINE_MST), заполненной строковыми кодами или "Line_CD", которые я хотел бы видеть в таблице. Это позволяет мне просто добавить новые "Line_CD" в эту справочную таблицу, чтобы мой запрос и, следовательно, моя программа подхватили его при следующем запросе. Эта логика также обрабатывает значения NULL из таблицы SQL, устанавливая все значения NULL на 0 до выполнения любых вычислений или заполнения ячеек. Единственный раз, когда необходимо обновить эту логику, это когда вы хотите добавить новые информационные столбцы в таблицу, что лично мне не нужно делать.
Если у кого-то есть какие-либо предложения по поводу кода, способов сделать его более эффективным или более аккуратным форматированием, оставьте комментарий ниже.