2007-8-15 17:45
jessi0624
Option Explicit
Private Const myConnectionString1 As String = _
"Provider=SQLOLEDB.1;Persist Security Info=True;User ID=***;" + _
"Initial Catalog=*****;Data Source=********;" + _
"Workstation ID=*****;Password=********"
Private Const myConnectionString2 As String = _
"Provider=SQLOLEDB.1;Persist Security Info=True;User ID=*******;" + _
"Initial Catalog=*******;Data Source=******;" + _
"Workstation ID=*****;Password=*******"
Private BeginDate, EndDate, CurrentDate As Date
Private CangKuCode, CangKuName As String
'Private myConnection As ADODB.Connection
'Private mySQLCommand As ADODB.Command
Private myConnection1 As ADODB.Connection
Private myConnection2 As ADODB.Connection
Private mySQLCommand As ADODB.Command
Private mySQLCommand2 As ADODB.Command
Private mySQLCommand3 As ADODB.Command
Private myOrderList As New Collection
Private wantWH As Boolean
Private Sub CreateConnection(ByVal theOption As Integer)
'create a connection
Select Case theOption
Case 1
Set myConnection1 = New ADODB.Connection
Call myConnection1.Open(myConnectionString1)
Case 2
Set myConnection2 = New ADODB.Connection
Call myConnection2.Open(myConnectionString2)
Case Else
Set myConnection1 = New ADODB.Connection
Call myConnection1.Open(myConnectionString1)
Set myConnection2 = New ADODB.Connection
Call myConnection2.Open(myConnectionString2)
End Select
End Sub
Private Sub DestroyConnection(ByVal theOption As Integer)
'close a connection
On Error GoTo Finish
Select Case theOption
Case 1
If (myConnection1.State = ObjectStateEnum.adStateOpen) Then
myConnection1.Close
End If
Case 2
If (myConnection2.State = ObjectStateEnum.adStateOpen) Then
myConnection2.Close
End If
Case Else
If (myConnection1.State = ObjectStateEnum.adStateOpen) Then
myConnection1.Close
End If
If (myConnection2.State = ObjectStateEnum.adStateOpen) Then
myConnection2.Close
End If
End Select
Finish:
End Sub
Sub MaterialOrderLog()
'
'
'
Re_Select:
Dim Answer As VbMsgBoxResult
BeginDate = InputBox("ÊäÈëͳ¼Æ¿ªÊ¼ÈÕÆÚ", "¿ªÊ¼ÈÕÆÚ", Date - 7)
EndDate = InputBox("ÊäÈëͳ¼Æ½áÊøÈÕÆÚ", "½áÊøÈÕÆÚ", Date)
On Error GoTo ErrorHandler
Call CreateConnection(1)
Set mySQLCommand = New ADODB.Command
Set mySQLCommand.ActiveConnection = myConnection1
Set mySQLCommand2 = New ADODB.Command
Set mySQLCommand2.ActiveConnection = myConnection1
Set mySQLCommand3 = New ADODB.Command
Set mySQLCommand3.ActiveConnection = myConnection1
CurrentDate = getDBCurrentDate()
'Dim NewDate As Date
'NewDate = DateValue(BeginDate) - 1
'Call MsgBox("current: " & CurrentDate & " begin: " & BeginDate & " end: " & EndDate & " new date: " & NewDate)
'If ("1" = "1") Then Exit Sub
Dim sn As String
Dim progTitle As String
progTitle = "²É¹ºÍê³ÉÇé¿öͳ¼Æ"
sn = progTitle & "-×Ô" & BeginDate & "µ½" & EndDate
Dim UserName As String
UserName = Application.UserName
Dim sht As Worksheet
Dim found As Boolean
found = False
For Each sht In Sheets
If (sht.Name = sn) Then
found = True
End If
Next sht
If (Not found) Then
Sheets.Add before:=Sheets(1)
Sheets(1).Name = sn
Else
Sheets(sn).Move before:=Sheets(1)
End If
Call Sheets(sn).Activate
Cells.Select
Selection.Clear
'myRecordSet.MoveFirst
'Call ActiveSheet.Range("A1").CopyFromRecordset(myRecordSet)
'Dim bMaterial As Pmaterial
ActiveSheet.Range("A1") = progTitle
Range(Cells(1, 1), Cells(1, 12)).Select
Range("L1").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Font.FontStyle = "¼Ó´Ö"
.Font.Size = 14
End With
ActiveSheet.Range("A2") = "¿ªÊ¼ÈÕÆÚ:" & BeginDate & "£» ÖÕ½áÈÕÆÚ£º" & EndDate & " ²Ù×÷ʱ¼ä:" & CurrentDate
Range("A2:L2").Select
Range("F2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Font.FontStyle = "¼Ó´Ö"
End With
Dim ln As Integer
'Application.StatusBar = False
Application.ScreenUpdating = False
'Application.Interactive = False
'Call getKHInfo
Set myOrderList = New Collection
Call getOrder(BeginDate, EndDate)
Dim irow As Integer
Dim icol As Integer
irow = 4
Dim anOrder As POrder
Dim ADate As Date
For Each anOrder In myOrderList
ln = 0
ln = ln + 1
Cells(irow, ln) = anOrder.DocEntry
ln = ln + 1
If (anOrder.OrderStatus = "C") Then
Cells(irow, ln) = "¹Ø±Õ"
ElseIf (anOrder.OrderStatus = "O") Then
Cells(irow, ln) = "¿ª"
Else
Cells(irow, ln) = anOrder.OrderStatus
End If
ln = ln + 1
Cells(irow, ln) = anOrder.OrderDate
ln = ln + 1
If (anOrder.ShipDate > "2000-1-1") Then Cells(irow, ln) = anOrder.ShipDate
ln = ln + 1
If (anOrder.getLatestJHdate > "2000-1-1") Then Cells(irow, ln) = anOrder.getLatestJHdate
ln = ln + 1
If (anOrder.getLatestJHdate > "2000-1-1" And anOrder.ShipDate > "2000-1-1") Then
Cells(irow, ln) = getDayDiff(anOrder.getLatestJHdate, anOrder.ShipDate)
End If
If (anOrder.ShipDate > "2000-1-1") Then
If (anOrder.getLatestJHdate > "2000-1-1") Then
ADate = anOrder.getLatestJHdate
Else
ADate = CurrentDate
End If
If (getDayDiff(ADate, anOrder.ShipDate) < 0) Then
Range(Cells(irow, 1), Cells(irow, 13)).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
ln = ln + 1
Cells(irow, ln) = anOrder.GYSName
ln = ln + 1
Cells(irow, ln) = anOrder.ItemName
ln = ln + 1
Cells(irow, ln) = anOrder.OrderUnit
ln = ln + 1
Cells(irow, ln) = anOrder.OrderCount
ln = ln + 1
Cells(irow, ln) = anOrder.getTotalCount
ln = ln + 1
Cells(irow, ln) = anOrder.getTotalCount - anOrder.OrderCount
ln = ln + 1
Cells(irow, ln) = anOrder.getFinishedDetail
'Cells(irow, ln + 1) = anOrder.ItemCode
irow = irow + 1
Next anOrder
ln = 0
ln = ln + 1
Cells(3, ln) = "²É¹º¶©µ¥ºÅ"
ln = ln + 1
Cells(3, ln) = "¶©µ¥×´Ì¬"
ln = ln + 1
Cells(3, ln) = "¶©¹ºÈÕÆÚ"
ln = ln + 1
Cells(3, ln) = "³Ðŵµ½»õÈÕÆÚ"
ln = ln + 1
Cells(3, ln) = "×î½üµ½»õÈÕÆÚ"
ln = ln + 1
Cells(3, ln) = "µ½»õºÍ³ÐŵÈÕÆÚ±È½Ï"
ln = ln + 1
Cells(3, ln) = "¹©Ó¦ÉÌ"
ln = ln + 1
Cells(3, ln) = "²É¹ºÎïÁÏ"
ln = ln + 1
Cells(3, ln) = "²É¹ºµ¥Î»"
ln = ln + 1
Cells(3, ln) = "²É¹ºÊýÁ¿"
ln = ln + 1
Cells(3, ln) = "Íê³ÉÊýÁ¿"
ln = ln + 1
Cells(3, ln) = "δÍê³ÉÊýÁ¿"
ln = ln + 1
Cells(3, ln) = "²É¹ºÈë¿â£¨º¬ÍË»õ£©ÏêÇé"
Call MakeList(1, ln, 3, irow - 1)
Range(Cells(3, 1), Cells(3, ln)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.FontStyle = "¼Ó´Ö"
End With
With Selection.Font
.Size = 10
End With
Range(Cells(4, 1), Cells(irow - 1, ln - 1)).Select
'Selection.NumberFormat = "0.00"
Selection.HorizontalAlignment = xlCenter
'Selection.IndentLevel = 1
'Range(Cells(3, 1), Cells(3, 11).End(xlDown)).Select
'Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("B4") _
, Order2:=xlAscending, Key3:=Range("C4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal, DataOption3:=xlSortNormal
'Range(Cells(4, 5), Cells(4, 5).End(xlDown)).Select
'Selection.NumberFormat = "0.00"
'Selection.HorizontalAlignment = xlRight
'Selection.IndentLevel = 1
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Call myPageSetUp(sn)
Call Cells(4, 1).Select
Application.ScreenUpdating = True
ErrorExit:
Set mySQLCommand = Nothing
Call DestroyConnection(0)
Application.Interactive = True
Exit Sub
ErrorHandler:
Call MsgBox(Err.Description, vbCritical)
Resume ErrorExit
End Sub
Private Function getDBCurrentDate() As String
Dim myRecordSet As ADODB.Recordset
mySQLCommand.CommandText = "select convert(varchar, convert(datetime, getdate(), 9), 120) as cdate"
mySQLCommand.CommandType = adCmdUnknown
Set myRecordSet = mySQLCommand.Execute
getDBCurrentDate = myRecordSet.Fields("cdate")
myRecordSet.Close
Set myRecordSet = Nothing
End Function
Private Sub getKHInfo()
Dim myRecordSet As ADODB.Recordset
'Dim myData As New Collection
Dim sqlCMD, sqlTail As String
sqlCMD = " select count(distinct groupname) as total from ocrg where grouptype = 'C' "
mySQLCommand.CommandText = sqlCMD
'Call MsgBox(mySQLCommand.CommandText)
mySQLCommand.CommandType = adCmdUnknown
Set myRecordSet = mySQLCommand.Execute
If (Not myRecordSet.EOF) Then myRecordSet.MoveFirst
ReDim myRegion(myRecordSet.Fields("total"))
sqlCMD = " select distinct groupname as groupname from ocrg where grouptype = 'C' "
mySQLCommand.CommandText = sqlCMD
'Call MsgBox(mySQLCommand.CommandText)
mySQLCommand.CommandType = adCmdUnknown
Set myRecordSet = mySQLCommand.Execute
If (Not myRecordSet.EOF) Then myRecordSet.MoveFirst
Dim i As Integer
i = 0
While Not myRecordSet.EOF
i = i + 1
myRegion(i) = myRecordSet.Fields("groupname")
Call MsgBox(myRegion(i))
myRecordSet.MoveNext
Wend
myRecordSet.Close
Set myRecordSet = Nothing
End Sub
Private Sub getOrder(ByVal BDate As String, ByVal EDate As String)
wantWH = False
Dim myRecordSet, myRecordSet2, myRecordSet3 As ADODB.Recordset
'Dim myData As New Collection
Dim sqlCMD, sqlTail As String
Dim getJHsqlCMD, getReturn1CMD, getReturn2CMD As String
Set myOrderList = New Collection
sqlCMD = " select t0.docentry as 'DocEntry', t1.linenum as LineNum, " & _
" t0.docstatus as 'OrderStatus', t0.docdate as 'OrderDate', isnull(t0.u_cnsj, '') as 'ShipDate', " & _
" t0.cardname as 'GYSName', t0.cardcode as 'GYSCode', " & _
" t1.itemcode as 'ItemCode', t1.Dscription as 'ItemName', isnull(t1.unitMsr, '') as 'CGUnit', " & _
" t1.Quantity as count, t1.price as price, t1.linetotal as cost " & _
" from opor t0 inner join por1 t1 on t0.docentry = t1.docentry "
sqlTail = " WHERE t0.docdate >= convert(datetime, '" & BDate & "', 120) " & _
" and t0.docdate <= convert(datetime, '" & EDate & "', 120) " & _
" and t0.canceled = 'N' "
If (Not wantWH) Then sqlTail = sqlTail & " and (not t1.itemcode like 'WG%') "
sqlTail = sqlTail & " Order By t1.itemcode, t0.docentry"
getJHsqlCMD = " select t1.docentry as 'DocEntry', t1.linenum as LineNum, " & _
" t0.docdate as 'OrderDate', " & _
" t1.Quantity as count, t1.price as price, t1.linetotal as cost " & _
" from OPDN t0 inner join pdn1 t1 on t0.docentry = t1.docentry "
mySQLCommand.CommandText = sqlCMD & sqlTail
'Call MsgBox(mySQLCommand.CommandText)
mySQLCommand.CommandType = adCmdUnknown
Set myRecordSet = mySQLCommand.Execute
If (Not myRecordSet.EOF) Then myRecordSet.MoveFirst
Dim anOrder As POrder
Dim jhChain As Collection
Dim anJHRecord As POrder
While Not myRecordSet.EOF
Set anOrder = New POrder
anOrder.DocEntry = myRecordSet.Fields("DocEntry")
anOrder.LineNumber = myRecordSet.Fields("LineNum")
anOrder.OrderStatus = myRecordSet.Fields("OrderStatus")
anOrder.OrderDate = myRecordSet.Fields("OrderDate")
anOrder.ShipDate = myRecordSet.Fields("ShipDate")
anOrder.GYSName = myRecordSet.Fields("GYSName")
anOrder.GYSCode = myRecordSet.Fields("GYSCode")
anOrder.ItemCode = myRecordSet.Fields("ItemCode")
anOrder.ItemName = myRecordSet.Fields("ItemName")
anOrder.OrderUnit = myRecordSet.Fields("CGUnit")
anOrder.OrderCount = myRecordSet.Fields("count")
anOrder.OrderPrice = myRecordSet.Fields("price")
anOrder.OrderCost = myRecordSet.Fields("cost")
Set jhChain = New Collection
mySQLCommand2.CommandText = getJHsqlCMD & " where baseentry = " & anOrder.DocEntry & " and baseline = " & anOrder.LineNumber
mySQLCommand2.CommandType = adCmdUnknown
Set myRecordSet2 = mySQLCommand2.Execute
If (Not myRecordSet2.EOF) Then myRecordSet2.MoveFirst
While Not myRecordSet2.EOF
Set anJHRecord = New POrder
anJHRecord.DocEntry = myRecordSet2.Fields("DocEntry")
anJHRecord.LineNumber = myRecordSet2.Fields("LineNum")
anJHRecord.OrderDate = myRecordSet2.Fields("OrderDate")
'Call MsgBox(anJHRecord.DocEntry & " " & anJHRecord.OrderDate)
anJHRecord.OrderCount = myRecordSet2.Fields("count")
anJHRecord.OrderPrice = myRecordSet2.Fields("price")
anJHRecord.OrderCost = myRecordSet2.Fields("cost")
Call jhChain.Add(anJHRecord, anJHRecord.DocEntry & "-" & anJHRecord.LineNumber)
myRecordSet2.MoveNext
Wend
Set anOrder.myFinished = jhChain
Call myOrderList.Add(anOrder, anOrder.DocEntry & "-" & anOrder.LineNumber)
myRecordSet.MoveNext
Wend
Skip1:
'Set getSale = myData
myRecordSet.Close
Set myRecordSet = Nothing
End Sub
Private Function getCollectionValue(ByVal cc As Collection, ByVal key As String) As Double
On Error GoTo theErrorHandler
getCollectionValue = cc.Item(key).OnHand
Exit Function
theErrorHandler:
getCollectionValue = 0
End Function
Private Function getDayDiff(ByVal date1 As Date, ByVal date2 As Date) As Integer
getDayDiff = DateDiff("d", date1, date2)
End Function
Private Sub myPageSetUp(ByVal sn As String)
Call Sheets(sn).Activate
With ActiveSheet.PageSetup
.PrintTitleRows = "$3:$3"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintGridlines = True
.CenterFooter = "&D"
.RightFooter = "&P"
End With
End Sub
Sub MakeList(ByVal col1 As Integer, ByVal col2 As Integer, ByVal row1 As Integer, ByVal row2 As Integer)
Range(Cells(row1, col1), Cells(row2, col2)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(row1, col1), Cells(row2, col2)), , xlYes).Name = _
"Áбí"
End Sub