本文介绍了如何在genarting报告时将行转换为coloumn和coloumn to row的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Public Function GetTrackingReportByVehicle(ByRef sessionObj As SessionState.HttpSessionState, ByVal client_ID As String, ByVal veh_name As String, ByVal veh_id As Integer, ByVal startTime As Date, ByVal endTime As Date) As DataTable
Try
Dim dt As New DataTable
Dim mDataTable1 As New DataTable
Dim mDataTableRpt As New DataTable
Dim mVehicleTrackingDatatable As New DataTable
Dim client_name As String = sessionObj("Database_Name")
Dim mSqlQuery As String
Dim asstGrpSesionRgnSet As New Cls_RegionalSettings
Dim mix As Integer
Dim mVehicleID As String
Dim mVehicleName As String
Dim mDataTable As New DataTable
Dim startTimeStr As String
Dim endTimeStr As String
Dim dynamicdatarow As DataRow
Dim i As Integer = 0
Dim strEngineStatus As String = ""
Dim flagNoChange As Boolean = True
Dim S1, E1 As DateTime
Dim mTotONTime As Integer = 0
Dim mTotOFFTime As Integer = 0
Dim mLastString As Integer = 0
Dim mPrevDateTime As Date
Dim mCurrDateTime As Date
Dim mPrevDate As Date
Dim mCurrDate As Date
Dim mHour As Integer
Dim mMinute As Integer
Dim mStart As Integer
Dim flagFirstTime As Boolean = True
Dim mDistance As Double
Dim mTotalDistance As Double
Dim mStartTime As Date
Dim mEndTime As Date
Dim mCurSpeed As Integer
Dim flagNoData As Boolean
Dim DistCorrFactor As Double = 0
Dim mDist As Double
'Dim mStartLocation, mEndLocation As String
Double.TryParse(sessionObj("DistCorrFactor"), DistCorrFactor)
mSqlQuery = "Select Client_TimeZone FROM Client_Master WHERE (Client_ID = '" & sessionObj("Client_ID") & "')"
mDataTable1 = mClsDataLayer.GetHostDataTable(mSqlQuery)
If mDataTable1.Rows.Count <> 0 Then
asstGrpSesionRgnSet.timeOffsetGMTInMinutes = mDataTable1.Rows(0)(0)
asstGrpSesionRgnSet.DateTimeformatString = "dd/MM/yyyy, hh:mm:ss tt"
asstGrpSesionRgnSet.RegionName = ""
asstGrpSesionRgnSet.DistanceSpeedUnit = 0
End If
startTimeStr = asstGrpSesionRgnSet.convertToUTC(startTime)
endTimeStr = asstGrpSesionRgnSet.convertToUTC(endTime)
mDataTable.Columns.Add("Vehicle Detail")
mDataTable.Columns.Add("StartTime")
' mDataTable.Columns.Add("Start Location")
mDataTable.Columns.Add("EndTime")
'mDataTable.Columns.Add("End Location")
mDataTable.Columns.Add("KM")
mDistance = 0.0
mTotalDistance = 0.0
flagFirstTime = True
flagNoData = True
mVehicleID = veh_id
mVehicleName = veh_name
mSqlQuery = "SELECT VehPos_R_DateTime, VehPos_ID, VehPos_Longitude, VehPos_Latitude, VehPos_Speed, VehPos_Location FROM Vehicle_Position WHERE VehPos_Veh_ID = " & Trim(mVehicleID) & " AND VehPos_Client_ID = '" & sessionObj("Client_ID") & "' And (VehPos_R_DateTime Between '" & startTimeStr & "' And '" & endTimeStr & "') ORDER BY VehPos_R_DateTime ASC"
mVehicleTrackingDatatable = mClsDataLayer.ExecuteDataTableQuery(sessionObj, mSqlQuery, client_name)
If mVehicleTrackingDatatable Is Nothing Then
Exit Function
End If
dynamicdatarow = mDataTable.NewRow
dynamicdatarow("Vehicle Detail") = "Vehicle No : " + mVehicleName
dynamicdatarow("StartTime") = ""
'dynamicdatarow("Start Location") = ""
dynamicdatarow("EndTime") = ""
'dynamicdatarow("End Location") = ""
dynamicdatarow("KM") = ""
mDataTable.Rows.Add(dynamicdatarow)
If mVehicleTrackingDatatable.Rows.Count <> 0 Then
mStart = 0
mPrevDateTime = CType(mVehicleTrackingDatatable.Rows(mStart)("VehPos_R_DateTime"), Date)
mPrevDateTime = mPrevDateTime.AddHours(5)
mPrevDateTime = mPrevDateTime.AddMinutes(30)
mPrevDate = mPrevDateTime.Date
mCurSpeed = mVehicleTrackingDatatable.Rows(mStart)("VehPos_Speed")
If mCurSpeed > 0 Then
flagNoData = False
flagFirstTime = False
mStartTime = mPrevDateTime
mEndTime = mPrevDateTime
End If
'mStartLocation = mVehicleTrackingDatatable.Rows(mStart)("VehPos_Location")
mStart = 1
For i = mStart To mVehicleTrackingDatatable.Rows.Count - 1
If (mVehicleTrackingDatatable.Rows(i)("VehPos_Longitude") = 0) Or
(mVehicleTrackingDatatable.Rows(i)("VehPos_Latitude") = 0) Then
Continue For
End If
mCurrDateTime = CType(mVehicleTrackingDatatable.Rows(i)("VehPos_R_DateTime"), Date)
mCurrDateTime = mCurrDateTime.AddHours(5)
mCurrDateTime = mCurrDateTime.AddMinutes(30)
mCurrDate = mCurrDateTime.Date
mCurSpeed = mVehicleTrackingDatatable.Rows(i)("VehPos_Speed")
mHour = mCurrDateTime.Hour
mMinute = mCurrDateTime.Minute
If mMinute >= 60 Then
mMinute = mMinute - 60
mHour = mHour + 1
End If
If mHour > 23 Then
mCurrDate = mCurrDate.AddDays(1)
End If
If (mPrevDateTime.Date <> mCurrDate.Date) Then
dynamicdatarow = mDataTable.NewRow
dynamicdatarow("Vehicle Detail") = mPrevDateTime.Date.ToString("d")
If flagNoData Then
dynamicdatarow("StartTime") = "NA"
' dynamicdatarow("Start Location") = "NA"
dynamicdatarow("EndTime") = "NA"
' dynamicdatarow("End Location") = "NA"
dynamicdatarow("KM") = 0
Else
dynamicdatarow("StartTime") = mStartTime.ToLongTimeString
' dynamicdatarow("Start Location") = mStartLocation
dynamicdatarow("EndTime") = mEndTime.ToLongTimeString
' dynamicdatarow("End Location") = mEndLocation
dynamicdatarow("KM") = Math.Round(mDistance, 2)
End If
mDataTable.Rows.Add(dynamicdatarow)
mPrevDateTime = mCurrDate
mLastString = i
mTotONTime = 0
mTotOFFTime = 0
flagFirstTime = True
flagNoData = True
mDistance = 0.0
'mStartLocation = mVehicleTrackingDatatable.Rows(mStart)("VehPos_Location")
Else
If (flagFirstTime) And (mCurSpeed > 0) Then
flagNoData = False
flagFirstTime = False
mStartTime = mCurrDateTime
End If
mDist = ClsGetLandmarkLocation.calculateDistance(Double.Parse(mVehicleTrackingDatatable.Rows(i - 1)("VehPos_Longitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i - 1)("VehPos_Latitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i)("VehPos_Longitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i)("VehPos_Latitude"))) * 1.852
mDist = mDist + ((mDist * DistCorrFactor) / 100)
mDistance = mDistance + mDist
mDist = ClsGetLandmarkLocation.calculateDistance(Double.Parse(mVehicleTrackingDatatable.Rows(i - 1)("VehPos_Longitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i - 1)("VehPos_Latitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i)("VehPos_Longitude")), Double.Parse(mVehicleTrackingDatatable.Rows(i)("VehPos_Latitude"))) * 1.852
mDist = mDist + ((mDist * DistCorrFactor) / 100)
mTotalDistance = mTotalDistance + mDist
' mEndLocation = mVehicleTrackingDatatable.Rows(mStart)("VehPos_Location")
If mCurSpeed > 0 Then
mEndTime = mCurrDateTime
End If
End If
Next
End If
If mLastString <> mVehicleTrackingDatatable.Rows.Count - 1 Then
dynamicdatarow = mDataTable.NewRow
dynamicdatarow("Vehicle Detail") = mPrevDateTime.Date.ToString("d")
If flagNoData Then
dynamicdatarow("StartTime") = "NA"
' dynamicdatarow("Start Location") = "NA"
dynamicdatarow("EndTime") = "NA"
'dynamicdatarow("End Location") = "NA"
dynamicdatarow("KM") = 0
Else
dynamicdatarow("StartTime") = mStartTime.ToLongTimeString
'dynamicdatarow("Start Location") = mStartLocation
dynamicdatarow("EndTime") = mEndTime.ToLongTimeString
'dynamicdatarow("End Location") = mEndLocation
dynamicdatarow("KM") = Math.Round(mDistance, 2)
End If
mDataTable.Rows.Add(dynamicdatarow)
End If
dynamicdatarow = mDataTable.NewRow
dynamicdatarow("Vehicle Detail") = "Total Distance"
dynamicdatarow("StartTime") = ""
' dynamicdatarow("Start Location") = ""
dynamicdatarow("EndTime") = ""
' dynamicdatarow("End Location") = ""
dynamicdatarow("KM") = Math.Round(mTotalDistance, 2)
mDataTable.Rows.Add(dynamicdatarow)
Return mDataTable
Catch ex As Exception
Throw New Exception("Cannot prepare Tracking report!" & vbCrLf & ex.Message)
End Try
End Function推荐答案
这篇关于如何在genarting报告时将行转换为coloumn和coloumn to row的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！