<%
|
Set rsDt = Server.CreateObject("ADODB.Recordset")
|
acc1=request("page")
|
if acc1=empty then acc1=clng(1)
|
QuantityInt = 2000
|
acc2=0
|
acc3=0
|
|
|
'sql="select ServiceOrdClass,count(DispatchOrdID),vtext,sum(ServiceOrdTraTxnPrice+StretcherMoney) from dbo.ServiceOrder,dbo.DispatchOrd,dictionary where ServiceOrdIDDt=ServiceOrdID and DispatchOrdState not in (10) and vID=ServiceOrdType and vtitle='ServiceOrderType' and DispatchOrd_NS_Time "&SqlOrdDateType&" GROUP BY vtext,ServiceOrdClass,vID order by ServiceOrdClass,vID"
|
'时间转算
|
sql="select "&Replace(Replace(SqlOrdDateType,"between","")," and ",",")
|
rs.open sql,objConn,1,1
|
DataSt=rs(0)
|
DataBe=rs(1)
|
rs.close()
|
|
'查看各分公司报表权限
|
if isDepartment("070109")=1 Or OrdClassInt>1 then
|
|
If UnitNameID<>"" And UnitNameID<>"0" Then
|
OrdClassListSql=""
|
sql="select vOrder2 from dictionary where vtitle='UnitName' and vID="&UnitNameID
|
rs.open sql,objConn,1,1
|
If not rs.Eof Then
|
UnitTypeID=rs(0)
|
rs.close()
|
sql="select vID,vtext,vOrder2 from dictionary where vID in ("&UnitTypeID&") and vtitle='OrderClass' order by vOrder"
|
rs.open sql,objConn,1,1
|
do while not rs.Eof
|
OrderClassID = rs("vOrder2")
|
OrdClassListSql = OrdClassListSql&","&OrderClassID
|
rs.movenext
|
Loop
|
OrdClassListSql=Mid(OrdClassListSql,2)
|
OrdClassListSql=Replace(OrdClassListSql,",","','")
|
End If
|
rs.close()
|
ElseIf isDepartment("070109")=1 Then
|
OrdClassListSql=""
|
sql="select vID,vtext,vOrder2 from dictionary where vType in (1,2) and vtitle='OrderClass' order by vOrder"
|
rs.open sql,objConn,1,1
|
do while not rs.Eof
|
OrderClassID = rs("vOrder2")
|
OrdClassListSql = OrdClassListSql&","&OrderClassID
|
rs.movenext
|
loop
|
rs.close()
|
OrdClassListSql=Mid(OrdClassListSql,2)
|
OrdClassListSql=Replace(OrdClassListSql,",","','")
|
End If
|
end If
|
|
If OrdDateType<>"0" then
|
sql="select top 1 vtext,vOrder2 from dictionary where vtitle='OrdDateType2' and vOrder>(select top 1 vOrder from dictionary where vtitle='OrdDateType2' and vID="&OrdDateType&") order by vOrder"
|
rs.open Sql,objConn,1,1
|
If not rs.Eof Then
|
oldSqlOrdDateType = rs("vOrder2")
|
Else
|
oldSqlOrdDateType=" between DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0) and dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0))"
|
End If
|
rs.close()
|
End If
|
sql="select "&Replace(Replace(oldSqlOrdDateType,"between","")," and ",",")
|
rs.open sql,objConn,1,1
|
oldDataSt=rs(0)
|
oldDataBe=rs(1)
|
rs.close()
|
|
sql="select GasCarID,CarOrdClass,CarLicense,sum(GasVolume) as GasVolume,sum(GasSumPrice) as GasSumPrice,GasOldMileage=convert(bigint,(select top 1 GasMileage from CarGas as b where a.GasCarID=b.GasCarID and min(a.GasDate)>b.GasDate and b.GasState>=0 order by b.GasDate desc)),convert(bigint,max(GasMileage)) as GasMileage from CarGas as a,CarData where CarID=GasCarID and GasState>=0 and GasDate between '"&DataSt&"' and '"&DataBe&"' and CarOrdClass in ('"&OrdClassListSql&"') GROUP BY GasCarID,CarLicense,CarOrdClass"
|
'Response.Write sql
|
rs.open sql,objConn,1,1
|
if not rs.eof then
|
rs.pagesize=QuantityInt
|
rs.absolutepage=acc1
|
acc2=rs.pagecount
|
acc3=rs.recordcount
|
else
|
SystemMessageType=2
|
SystemMessageTXT="数据库中相关无数据！"
|
end If
|
'If SystemMessageType<>"2" then
|
Dim FormArray()
|
ArrayX=50
|
If acc3<QuantityInt Or (is_CSV=1 And acc3>QuantityInt) Then
|
ArrayY=acc3
|
Else
|
ArrayY=QuantityInt
|
End If
|
'表头
|
FormFields="车牌,本月里程,上月里程,月度里程环比,本月加油总量(升),本月加油总额(元),本月油耗(升/百公里),上月油耗(升/百公里),月度油耗环比(升),变化比例,本月油费(元/百公里),上月油费(元/百公里),月度油费环比(元),变化比例,当月油耗变化金额"
|
FormFields=split(FormFields,",")
|
ArrayX=ubound(FormFields)+1
|
REDIM FormArray(ArrayX,ArrayY+1)
|
for i=0 to ubound(FormFields)
|
FormArray((i+1),0)=FormFields(i)
|
next
|
Y=1
|
do while not rs.Eof and Y<=ArrayY
|
'读取数据
|
GasCarID = rs("GasCarID")
|
CarLicense = rs("CarLicense")'&" "&rs("CarOrdClass")
|
GasVolume = CLng(rs("GasVolume"))
|
GasSumPrice= CLng(rs("GasSumPrice"))
|
GasOldMileage= rs("GasOldMileage")
|
GasMileage = CLng(rs("GasMileage"))
|
If GasOldMileage="" Or isnull(GasOldMileage) Then GasOldMileage=0
|
GasOldMileage= CLng(GasOldMileage)
|
|
oldGasVolume = 0
|
oldGasSumPrice= 0
|
oldGasOldMileage= 0
|
oldGasMileage = 0
|
If GasOldMileage>0 Then
|
sql="select GasCarID,CarLicense,sum(GasVolume) as GasVolume,sum(GasSumPrice) as GasSumPrice,GasOldMileage=convert(bigint,(select top 1 GasMileage from CarGas as b where a.GasCarID=b.GasCarID and min(a.GasDate)>b.GasDate and b.GasState>=0 order by b.GasDate desc)),convert(bigint,max(GasMileage)) as GasMileage from CarGas as a,CarData where CarID=GasCarID and GasState>=0 and GasDate between '"&oldDataSt&"' and '"&oldDataBe&"' and GasCarID="&GasCarID&" GROUP BY GasCarID,CarLicense"
|
rsDt.open sql,objConn,1,1
|
If not rsDt.Eof Then
|
oldGasVolume = CLng(rsDt("GasVolume"))
|
oldGasSumPrice= CLng(rsDt("GasSumPrice"))
|
oldGasOldMileage= rsDt("GasOldMileage")
|
oldGasMileage = CLng(rsDt("GasMileage"))
|
If oldGasOldMileage="" Or isnull(oldGasOldMileage) Then oldGasOldMileage=0
|
oldGasOldMileage= CLng(oldGasOldMileage)
|
End If
|
rsDt.close()
|
End If
|
|
'油耗计算
|
If GasOldMileage>0 Then
|
GasOil = FormatNumber(GasVolume/(GasMileage-GasOldMileage),4,-1) '本月油耗
|
GasFuelCost = FormatNumber(GasSumPrice/(GasMileage-GasOldMileage),4,-1) '本月油费
|
GasOil100 = FormatNumber(GasOil*100,2,-1)
|
GasFuelCost100 = FormatNumber(GasFuelCost*100,2,-1)
|
Else
|
GasOil100 = "--"
|
GasFuelCost100="--"
|
End If
|
If oldGasOldMileage>0 And GasOldMileage>0 Then
|
oldGasOil = FormatNumber(oldGasVolume/(oldGasMileage-oldGasOldMileage),4,-1) '上月油耗
|
oldGasFuelCost = FormatNumber(oldGasSumPrice/(oldGasMileage-oldGasOldMileage),4,-1) '上月油费
|
oldGasOil100 = FormatNumber(oldGasOil*100,2,-1,0,0)
|
oldGasFuelCost100 = FormatNumber(oldGasFuelCost*100,2,-1,0,0)
|
GasOil1 = FormatNumber((GasOil-oldGasOil)*100,2,-1,0,0)
|
GasOil2 = FormatPercent(GasOil/oldGasOil-1,2,-1,0,0)
|
GasFuelCost1 =FormatNumber((GasFuelCost-oldGasFuelCost)*100,2,-1,0,0)
|
GasFuelCost2 =FormatPercent(GasFuelCost/oldGasFuelCost-1,2,-1,0,0)
|
GasFuelCost3 =FormatNumber((GasFuelCost-oldGasFuelCost)*(GasMileage-GasOldMileage),2,-1,0,0)
|
If GasOil1>0 Then
|
GasOil1="<span style='color: #E91E63;'>+"&GasOil1&"</span>"
|
GasOil2="<span style='color: #E91E63;'>+"&GasOil2&"</span>"
|
Else
|
GasOil1="<span style='color: #4CAF50;'>"&GasOil1&"</span>"
|
GasOil2="<span style='color: #4CAF50;'>"&GasOil2&"</span>"
|
End If
|
If GasFuelCost1>0 Then
|
GasFuelCost1="<span style='color: #E91E63;'>+"&GasFuelCost1&"</span>"
|
GasFuelCost2="<span style='color: #E91E63;'>+"&GasFuelCost2&"</span>"
|
GasFuelCost3="<span style='color: #E91E63;'>+"&GasFuelCost3&"</span>"
|
Else
|
GasFuelCost1="<span style='color: #4CAF50;'>"&GasFuelCost1&"</span>"
|
GasFuelCost2="<span style='color: #4CAF50;'>"&GasFuelCost2&"</span>"
|
GasFuelCost3="<span style='color: #4CAF50;'>"&GasFuelCost3&"</span>"
|
End If
|
Else
|
oldGasOil100 = "--"
|
oldGasFuelCost100="--"
|
GasOil1 = "--"
|
GasOil2 = "--"
|
GasFuelCost1 ="--"
|
GasFuelCost2 ="--"
|
GasFuelCost3 ="--"
|
End If
|
|
Y=Y+1
|
'读取数据--End
|
|
'写入数组
|
FormArray(1,Y)=CarLicense
|
FormArray(2,Y)=GasMileage-GasOldMileage
|
FormArray(3,Y)=oldGasMileage-oldGasOldMileage
|
If (oldGasMileage-oldGasOldMileage)>0 then
|
FormArray(4,Y)=FormatPercent((GasMileage-GasOldMileage)/(oldGasMileage-oldGasOldMileage),2,-1,0,0)
|
Else
|
FormArray(4,Y)="--"
|
End If
|
FormArray(5,Y)=GasVolume
|
FormArray(6,Y)=GasSumPrice
|
FormArray(7,Y)=GasOil100
|
FormArray(8,Y)=oldGasOil100
|
FormArray(9,Y)=GasOil1
|
FormArray(10,Y)=GasOil2
|
FormArray(11,Y)=GasFuelCost100
|
FormArray(12,Y)=oldGasFuelCost100
|
FormArray(13,Y)=GasFuelCost1
|
FormArray(14,Y)=GasFuelCost2
|
FormArray(15,Y)=GasFuelCost3
|
|
'写入数组--End
|
|
rs.movenext
|
Loop
|
'REDIM FormArray(ArrayX,Y)
|
rs.close()
|
'End If
|
%>
|
