![]() |
Company Name |
![]() |
![]() |
Work with SQL |
This script was designed for the "ActOpus" software to enter data from a SQL database (using ADO). It could be used as an example of the interaction with a SQL databases.
Script "Download charts with SQL" is designed for data input of the end users consumption graphics in "ActOpus" software.
The database file for actualization of the required day must be downloaded in the software for the work of the script. The script parses the contents of the "Layout Code" in the "Consumers2" table and this code provides loading schedules of consumers to the table "Load schedule", carrying out the necessary indexing by number of points and numbers of consumers. For the right script work it is required the communication with the SQL database. The script is providing access to the database using ADO interface.
The script must be located in the "Macro" subfolder of the installation directory of the software.
Run of the script is exercised by using the "Run" command in the "Calculation" folder of the main menu. In addition, it is possible to run a script using the shell of the macro - the command "Macro..." in the "Calculation" folder of the main menu. The selection window for selection of the date displays the date for which the load schedule of consumers will be got from the database when script is run.
The script operates the following algorithm for each user during loading the schedules:
The script may take up to three message boxes at the end of its work:
In these windows are displayed only consumers who had a schedule in the SQL database on the selected by the user date.
The script uses one query for each user by the "PDG_h" table during the access the database:
strSQLquery="SELECT * FROM PDG_h WHERE (kpo_obj='" & MktCode &_ "') And (data='" & strDate & "') And (id_pok=33)"
It is mean that the code of the parameter "The hourly schedule of consumption bid" has "33" value (id_pok = 33).
' This script allows to upload to the "ActOpus" database data from the SQL-server database ' according to the consumers schedules. It uses ADO. ' (C) Mashalov E. Ural CDA, 2003 ' Create the data access objects Set ADO = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") ' Describe the parameters of a database connection DSN = "Driver=SQL Server;UID=user;PWD=letmein;SERVER=sqlserver;DATABASE=Graphic;Mode=Read" ' Open the SQL database ADO.Open(DSN) ' Executes the query of the date for which you need to enter data on schedules strDate = Rastr.SendCommandMain(7,"","",0) ' Construct a date string for the SQL-query DateDD = Left(strDate,2) DateYY = Right(strDate,2) DateMM = Mid(strDate,3,2) strDate = DateMM & "." & DateDD & "." & DateYY ' Initialize the list of reports LoadedList = vbCrLf RejectedList = vbCrLf PresentList = vbCrLf ' Connect to the "Consumer2" table and "Load schedule" in the "ActOpus" database Set ConsumerTable = Rastr.Tables("Consumer2") Set PLoadTable = Rastr.Tables("Pload") ' Looking for users schedules in the SQL-database for ConsNdx = 0 to ConsumerTable.Size-1 ConsNum = ConsumerTable.Cols("Num").Z(ConsNdx) MktCode = ConsumerTable.Cols("MktCode").Z(ConsNdx) ConsName = ConsumerTable.Cols("Name").Z(ConsNdx) ConsPmax = ConsumerTable.Cols("Pmax").Z(ConsNdx) ConsPmid = ConsumerTable.Cols("Pmid").Z(ConsNdx) ConsPmin = ConsumerTable.Cols("Pmin").Z(ConsNdx) ' Prepare and execute a SQL-query on the model code of a table "Consumer2" strSQLquery="SELECT * FROM PDG_h WHERE (kpo_obj='" & MktCode &_ "') And (data='" & strDate & "') And (id_pok=33)" RS.Open strSQLquery,ADO ' Check the presence of the schedule for this user If Not RS.EOF Then PLoadTable.SetSel("Num=" & ConsNum) Answ = vbYes ' If the schedule for this user is exist in the "ActOpus" database, than ask the user: ' "Should it to upgrade the schedule?" if PLoadTable.Count > 0 Then Answ = MsgBox ("By the user " & ConsNum & " [" & ConsName & "] "the schedule is already loaded. Update?", vbYesNo) if Answ = vbYes Then ' If the user wants to update the schedule, the current schedule in the "ActOpus" database will be erased. PLoadTable.DelRows End if End if ' If the user declined to update the schedule, go to the next If Answ = vbYes Then ' If the user wants to update the schedule, the existence of the user base load will be checked if ConsPmax <> 0 Or ConsPmin <> 0 Or ConsPmin <> 0 Then PLoadTable.SetSel("") ' Load the schedule into the "Loads schedule" table ' The entire schedule is stored in the one record, in the fields h0 - h23 'Therefore, we choose fields for the RS (recordset) object dynamically RS.MoveFirst While Not RS.EOF For i=1 To 24 If i<=9 Then RetVal=RS.Fields("h0"&i).Value Else RetVal=RS.Fields("h"&i).Value End If ' Add a new record to the "Load schedule" table and fill it PLoadTable.AddRow PLNdx = PLoadTable.Size-1 PLoadTable.Cols("Num").Z(PLNdx) = ConsNum PLoadTable.Cols("NPoint").Z(PLNdx) = i PLoadTable.Cols("P").Z(PLNdx) = RetVal PLoadTable.Cols("Q").Z(PLNdx) = 0 Next RS.MoveNext Wend LoadedList = LoadedList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf Else RejectedList = RejectedList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf End if Else PresentList = PresentList + CStr(ConsNum) + " [" + ConsName + "]" + vbCrLf End If End If RS.Close Next ' after the end of the work, the database will be closed and the user will be informed ' about the work is done ADO.Close if Len(LoadedList) > 2 Then MsgBox "The schedules downloaded for:" & LoadedList End if if Len(RejectedList) > 2 Then MsgBox "Do not have a base loads :" & RejectedList End if if Len(PresentList) > 2 Then MsgBox "Already have a load schedules:" & PresentList End if
![]() |
![]() |
Copyright © 2001-2006 Bitrix |