查询和显示数据.doc

DimStrBtimeAsStringDimStrEtimeAsStringDimerrtimeConstUNIT=0.03937*1440PrivateSubCommand1_Click()CallTimeConv*查询和显示数据*Iferrtime0ThenDimconnstr,myconn,mysqlconnstr=provider=winccoledbprovider.1;datasource=.wincc;catalog=cc_database_06_03_11_17_11_58R;userid=DBA,password=SQL;*mysql=TAG:R,DATABASEprodu,+StrBtime+,+StrEtime+mysql2=TAG:R,DATABASEtempu,+StrBtime+,+StrEtime+mysql3=TAG:R,DATABASEupq,+StrBtime+,+StrEtime+mysql4=TAG:R,DATABASEprodd,+mytime1+,+mytime2+mysql5=TAG:R,DATABASEtempd,+mytime1+,+mytime2+mysql6=TAG:R,DATABASEdnq,+mytime1+,+mytime2+Setmyconn=CreateObject(adodb.connection)myconn.ConnectionString=connstrmyconn.CursorLocation=3myconn.OpenIfGetConState(myconn.State)=adStateClosedThenMsgBox数据库连接不成功，可能是WINCC生产监控系统程序未激活，请先激活监控系统,vbOKCancel,数据库连接错误myconn.CloseExitSubEndIfSetors=CreateObject(adodb.recordset)Setorstemp=CreateObject(adodb.recordset)Setorsupq=CreateObject(adodb.recordset)Setocom=CreateObject(mand)ocom.CommandType=1Setocom.ActiveConnection=myconnocom.CommandText=mysqlSetors=ocom.Executem=ors.Fields.Countk=ors.RecordCountMSHFlexGrid1.rows=k+2Setocom2=CreateObject(mand)ocom2.CommandType=1Setocom2.ActiveConnection=myconnocom2.CommandText=mysql2Setorstemp=ocom2.ExecuteSetocom3=CreateObject(mand)ocom3.CommandType=1Setocom3.ActiveConnection=myconnocom3.CommandText=mysql3Setorsupq=ocom3.ExecuteIf(m0)Thenors.Move0orstemp.Move0orsupq.Move0n=1upq=0pctime=DateAdd(h,8,ors.Fields(1).Value)DoWhileNotors.EOFpctime2=DateAdd(h,8,ors.Fields(1).Value)修改显示记录的时间（格林威治时间）为本机时区MSHFlexGrid1.TextMatrix(n,0)=pctime2MSHFlexGrid1.TextMatrix(n,1)=ors.Fields(2).ValueMSHFlexGrid1.TextMatrix(n,2)=orstemp.Fields(2).ValueMSHFlexGrid1.TextMatrix(n,3)=orsupq.Fields(2).Valuen=n+1If(n10000)ThenExitDoIf(orsupq.Fields(2).Value=10)Thenupq=upq+1EndIfors.MoveNextorstemp.MoveNextorsupq.MoveNextLooppercentok=FormatNumber(upq/k*100),1)prodnum(0).Text=kprodok(1).Text=upqprodper(0).Text=percentokEndIfors.CloseSetors=Nothingorstemp.CloseSetorstemp=Nothingorsupq.CloseSetorsupq=Nothing*以上为读取序号的程序*myconn.CloseSetmyconn=NothingElseMsgBox输入的时间不正确EndIfEndSubPrivateSubCommand2_Click()CallTimeConvMsgBox查询的时间段为&StrBtime&到&StrEtimeDimrptAsNewReportDimtxtAsclsTextrpt.SetPrinter28000,12000,Portraitrpt.SetMargin5*UNIT,10*UNIT,10*UNIT,10*UNITrpt.AttachFlexGridMe.MSHFlexGrid1rpt.LeftSection.AlignMode=tyContentrpt.RightSection.AlignMode=tyContentrpt.Align=tymiddlerpt.ReadTemplateApp.Path&test.txtrpt.PreviewEndSubPrivateSubForm_Load()CallAutoValueCallMsflexGird1InitEndSub*生成组合框的内容*PublicSubAutoValue()Fori=2006To2010Byear.Item(0).AddItem(i)NextFori=0To12Bmon.Item(10).AddItem(i)NextFori=0To31Bday.Item(9).AddItem(i)NextFori=0To23Bhour.Item(8).AddItem(i)NextFori=0To59Bmin.Item(7).AddItem(i)NextFori=0To59Bsec.Item(6).AddItem(i)NextFori=2006To2010Eyear.Item(1).AddItem(i)NextFori=0To12Emon.Item(11).AddItem(i)NextFori=0To31Eday.Item(5).AddItem(i)NextFori=0To23Ehour.Item(4).AddItem(i)NextFori=0To59Emin.Item(3).AddItem(i)NextFori=0To59Esec.Item(2).AddItem(i)NextEndSub*转换本地时区为格林威治时间*8PublicSubTimeConv()testby=IsNumeric(Byear.Item(0).Text)IfNottestbyThenMsgBox你输入的年份不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Byear.Item(0).SetFocusExitSubEndIfByeartext=Byear.Item(0).Texttestbm=IsNumeric(Bmon.Item(10).Text)IfNottestbmThenMsgBox你输入的月份不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Bmon.Item(10).SetFocusExitSubEndIfBmonText=Bmon.Item(10).Texttestbd=IsNumeric(Bday.Item(9).Text)IfNottestbdThenMsgBox你输入的日期不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Bday.Item(9).SetFocusExitSubEndIfBdayText=Bday.Item(9).Texttestbh=IsNumeric(Bhour.Item(8).Text)IfNottestbhThenMsgBox你输入的小时数不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Bhour.Item(8).SetFocusExitSubEndIfBhourText=Bhour.Item(8).Texttestbm=IsNumeric(Bmin.Item(7).Text)IfNottestbmThenMsgBox你输入的分钟数不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Bmin.Item(7).SetFocusExitSubEndIfBminText=Bmin.Item(7).Texttestbs=IsNumeric(Bsec.Item(6).Text)IfNottestbsThenMsgBox你输入的秒数不是数字型（起始时间段），请重新输入,vbOKCancel,错误的起始时间Bsec.Item(6).SetFocusExitSubEndIfBsecText=Bsec.Item(6).Texttestey=IsNumeric(Eyear.Item(1).Text)IfNottesteyThenMsgBox你输入的年份不是数字型（终止时间段），请重新输入,vbOKCancel,错误的终止时间Eyear.Item(1).SetFocusExitSubEndIfEyearText=Eyear.Item(1).Texttestem=IsNumeric(Emon.Item(11).Text)IfNottestemThenMsgBox你输入的月份不是数字型（终止时间段），请重新输入,vbOKCancel,错误的终止时间Emon.Item(11).SetFocusExitSubEndIfEmonText=Emon.Item(11).Texttested=IsNumeric(Eday.Item(5).Text)IfNottestedThenMsgBox你输入的日期不是数字型（终止时间段），请重新输入,vbOKCancel,错误的起始时间Eday.Item(5).SetFocusExitSubEndIfEdayText=Eday.Item(5).Texttesteh=IsNumeric(Ehour.Item(4).Text)IfNottestehThenMsgBox你输入的小时数不是数字型（终止时间段），请重新输入,vbOKCancel,错误的终止时间Ehour.Item(4).SetFocusExitSubEndIfEhourText=Ehour.Item(4).Texttestemi=IsNumeric(Emin.Item(3).Text)IfNottestemiThenMsgBox你输入的分钟数不是数字型（终止时间段），请重新输入,vbOKCancel,错误的终止时间Emin.Item(3).SetFocusExitSubEndIfEminText=Emin.Item(3).Texttestes=IsNumeric(Esec.Item(2).Text)IfNottestesThenMsgBox你输入的秒数不是数字型（终止时间段），请重新输入,vbOKCancel,错误的终止时间Esec.Item(2).SetFocusExitSubEndIfEsecText=Esec.Item(2).TextBeginDatetime=Byeartext+-+BmonText+-+BdayText+BhourText+:+BminText+:+BsecTextEndDatetime=EyearText+-+EmonText+-+EdayText+EhourText+:+EminText+:+EsecTexterrtime=DateDiff(s,BeginDatetime,EndDatetime)Iferrtime=8ThenSubhour=CInt(BhourText)-8BeginNewday=DateDiff(d,0,BeginDatetime)ElseSubhour=CInt(BhourText)+16BeginNewday=DateDiff(d,1,BeginDatetime)EndIfStrsubhour=CStr(Subhour)根据时间是否大于等于8决定日期是否-1，做为新的起始时间TbeginNewday=CDate(BeginNewday)根据时间是否大于等于8决定日期是否-1，做为新的起始日期SbeginNewday=CStr(TbeginNewday)convertdatetimetochararrayNewbtime=Strsubhour+:+BminText+:+BsecTextNewdatetimeb=SbeginNewday+NewbtimeStrBtime=CStr(Newdatetimeb)得到校正的格林威治时间（开始查询时间）IfEhourText=8ThenSubhourE=CInt(EhourText)-8EndNewday=DateDiff(d,0,EndDatetime)ElseSubhourE=CInt(EhourText)+16EndNewday=DateDiff(d,1,EndDatetime)EndIfStrsubhourE=CStr(SubhourE)根据时间是否大于等于8决定日期是否-1，做为新的起始时间TendNewday=CDate(EndNewday)根据时间是否大于等于8决定日期是否-1，做为新的起始