1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
| <%
| acc1=request("page")
| if acc1=empty then acc1=clng(1)
| QuantityInt = 20
| acc2=0
| acc3=0
|
| '查看各分公司报表权限
| if isDepartment("070109")=1 then
| OrdClassListSql=""
| If UnitNameID<>"" And UnitNameID<>"0" Then
| 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()
| Else
| 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
|
| '限制只查广州
| OrdClassListSql="BF','AB','SA"
|
| 'sql="select convert(nvarchar(10),year(DispatchOrd_NS_Time))+'年'+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+'月',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,year(DispatchOrd_NS_Time),month(DispatchOrd_NS_Time),vID order by year(DispatchOrd_NS_Time),month(DispatchOrd_NS_Time),vID"
|
| sql="select SumDate,Sum_OrdInt=SUM(Sum_OrdInt),vtext,Sum_TraTxnPrice=SUM(Sum_TraTxnPrice) from (select SumDate=convert(nvarchar(10),year(DispatchOrd_NS_Time))+'年'+right('0'+convert(nvarchar(10),month(DispatchOrd_NS_Time)),2)+'月',Sum_OrdInt=count(DispatchOrdID),vtext,case when isnull(SUM(ServiceOrdTraTxnPrice+StretcherMoney),0)=0 or DispatchOrdPerfomance=0 then 0 else SUM(ServiceOrdTraTxnPrice+StretcherMoney)/((select SUM(DispatchOrdPerfomance) from DispatchOrd where ServiceOrdIDDt=ServiceOrdID and DispatchOrdState not in (10))/DispatchOrdPerfomance) END as Sum_TraTxnPrice 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&CoopUnitSql&" and (ServiceOrdClass in ('"&OrdClassListSql&"') or DispatchOrdClass in ('"&OrdClassListSql&"')) GROUP BY ServiceOrdID,DispatchOrdPerfomance,vtext,year(DispatchOrd_NS_Time),month(DispatchOrd_NS_Time),vID) as aa GROUP BY SumDate,vtext order by SumDate"
|
| sql="select DateName (yy,DispatchOrd_NS_Time),DateName (mm,DispatchOrd_NS_Time),count(DispatchOrdID),SUM(Normalint),SUM(OTint),SUM(Betimesint),SUM(OTBint) from (select DispatchOrdID,DispatchOrd_NS_Time,Normalint=CASE WHEN sum(EntourageOT_is)=0 and sum(EntourageBetimesHH)=0 THEN 1 ELSE 0 END,OTint=CASE WHEN sum(EntourageOT_is)>0 THEN 1 ELSE 0 END,Betimesint=CASE WHEN sum(EntourageBetimesHH)>0 THEN 1 ELSE 0 END,OTBint=CASE WHEN sum(EntourageOT_is)>0 and sum(EntourageBetimesHH)>0 THEN 1 ELSE 0 END from DispatchOrd,DispatchOrd_Entourage where DispatchOrdIDDt=DispatchOrdID and DispatchOrd_NS_Time "&SqlOrdDateType&" and DispatchOrdClass in ('"&OrdClassListSql&"') and DispatchOrdState in (8,9) GROUP BY DispatchOrdID,DispatchOrd_NS_Time) as ss GROUP BY DateName (yy,DispatchOrd_NS_Time),DateName (mm,DispatchOrd_NS_Time) order by DateName (yy,DispatchOrd_NS_Time),DateName (mm,DispatchOrd_NS_Time)"
| 'If Right(sql,2)=", " Then sql=Left(sql,Len(sql)-2)
| 'Response.Write OrdClassListSql
| 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
| '读取数据
| DateMonth = rs(0)&"-"&rs(1)
| OrdInt = rs(2)
| Normalint = rs(3)
| OTint = rs(4)
| Betimesint = rs(5)
| OTBint = rs(6)
|
| '读取数据--End
| Y=Y+1
| '写入数组
| FormArray(1,Y)=DateMonth
| FormArray(2,Y)=OrdInt
| FormArray(3,Y)=Normalint
| FormArray(4,Y)=OTint
| FormArray(5,Y)=Betimesint
| FormArray(6,Y)=OTBint
| '写入数组--End
|
| rs.movenext
| Loop
| 'REDIM FormArray(ArrayX,Y)
| rs.close()
| End If
| %>
|
|
