Jump to content

Selecting and Counting Specific Columns


Recommended Posts

Hello,

Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. 

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db

What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. 

Any help will be much appreciated. Thank you!

database_sample.png

Link to post
Share on other sites

Now I'm trying to count the number of employees per supervisor that has the attendance_status of Absent using this query.

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name), COUNT(attendance_status) FROM data_db WHERE attendance_status = 'ABSENT' GROUP BY supevisor"

But what it does is it also affects the second row(total count of the employees name) only showing employees that has the tagging of absent when I just want it to affect the third column. 

Edited by Eminence
Link to post
Share on other sites

Just another quick question related to this, do I need to have a separated column in the database if I want to calculate the difference of the count of the total employees' schedule status tagged as work and the count of the total employees' attendance status tagged as absent?

What I want to do is calculate the absenteeism rate per supervisor based on the formula above however, I don' have a column for the absenteeism rate.

Link to post
Share on other sites

Below is my current SQL statement. I'm still figuring out how to calculate the percentage of the total count of scheduled vs absent employees though.

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name), COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS count1, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS count1 FROM data_db WHERE supervisor != '' GROUP BY supervisor"

 

Link to post
Share on other sites

You have two different fields named 'count1'. Not sure what's up with that. I would try something like this --

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS abs_count, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS late_count, abs_count / emp_count * 100 AS abs_rate FROM data_db WHERE supervisor != '' GROUP BY supervisor"

 

Link to post
Share on other sites
2 hours ago, Danp2 said:

You have two different fields named 'count1'. Not sure what's up with that. I would try something like this --

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS abs_count, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS late_count, abs_count / emp_count * 100 AS abs_rate FROM data_db WHERE supervisor != '' GROUP BY supervisor"

 

 

Thanks @Danp2. After running, it says no such column: abs_count, do I also have to create a column for abs_count in SQLite instead? 

Link to post
Share on other sites

There is very little limit on the complexity of SQL requests in SQLite.

What remains unclear is the actual DB schema, the semantics of every column and the meaning of the data stored there.
Post the schema and a sample data so that we can build a request satisfying your requirements. Be as clear and formal as possible.

 

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
2 hours ago, jchd said:

There is very little limit on the complexity of SQL requests in SQLite.

What remains unclear is the actual DB schema, the semantics of every column and the meaning of the data stored there.
Post the schema and a sample data so that we can build a request satisfying your requirements. Be as clear and formal as possible.

 

Here's what I have

 

database_sample.png

Edited by Eminence
Corrected database screenshot
Link to post
Share on other sites

I don't get it. This isn't the schema (list of all DDL statements). Please list all "CREATE TABLE ..." statements.

What's the column "attendance_status" you're basing your request on?

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
CREATE TABLE data_db (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_number         STRING,
    employee_name           STRING,
    supervisor              STRING,
    date                    DATETIME,
    schedule_status         STRING,
    attendance_status       STRING,
    operational_minutes     STRING,
    late_hours              STRING
);

My bad. Here's the DDL. The data comes from a .CSV file generated from an internal web application.

So I'm trying to count the total number of employees' schedule_status tagged as WORK and their attendance_status tagged as ABSENT and from there, calculate the Abs. Rate %. 

Edited by Eminence
Link to post
Share on other sites

So I tried this:

Dividing count of attendance_status tagged with absent over the count of shedule_status tagged with work.

Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) as emp_count, COUNT(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 END) AS abs_count, COUNT(CASE WHEN late_hours > 0.00 THEN 1 END) AS late_count, (COUNT(CASE WHEN attendance_status IS 'ABSENT' THEN 1 END) / COUNT(CASE WHEN schedule_status IS 'WORK' THEN 1 END) * 100) FROM data_db WHERE supervisor != '' GROUP BY supervisor"

It does return a value of 0 though, whereas changing the order to schedule_status over the count of attendance_status it does return a different result. Any ideas?

Link to post
Share on other sites

Ok I believe I get the picture, more or less.

First there are some problems with your table design.

1) SQLite doesn't have datatypes STRING nor DATETIME. Those columns are parsed as being unknown datatypes with affinity BLOB. Probably not what you intended.
2) "operational_minutes" should probably call "operational_hours"
3) Both "operational_minutes" and "late_hours" should be REAL, not STRING (nor TEXT).
4) You can't make further use of result columns aliased. That's why "abs_count" is unknown.
5) You probably don't need to use "autoincrement" for the id primary key. That doesn't mean what you think it means.

To fix this I defined a table like this:

CREATE TABLE "data_db" (
  "id" INTEGER PRIMARY KEY,
  "employee_number" TEXT,
  "employee_name" TEXT,
  "supervisor" TEXT,
  "date" TEXT,
  "schedule_status" TEXT,
  "attendance_status" TEXT,
  "operational_hours" REAL,
  "late_hours" REAL);

I had to type (you pasted a picture) some random data in:

id  employee_number employee_name supervisor    date       schedule_status attendance_status operational_hours late_hours
1   100123          John Doe      Bryan McGrath 2018-08-09 WORK            WORK                           7.47       1.55
2   100124          Jane Doe      Bryan McGrath 2018-08-09 WORK            ABSENT                            0          0
3   178223          Joe Grifith   Neil Grant    2018-08-09 WORK            WORK                              8          0
4   182123          Ryan Banks    Summer Harley 2018-08-09 WORK            ABSENT                            0          0
5   143708          Paul Tanner   Summer Harley 2018-08-08 WORK            WORK                           7.15       0.34
6   075891          Bill Moots    Neil Grant    2018-08-09 WORK            WORK                           8.12          0
7   000156          Shirley Vroot Neil Grant    2018-08-07 WORK            ABSENT                            0          0
8   000163          Shirley Wok   Neil Grant    2018-08-07 WORK            WORK                           7.52          0
9   542980          Dan Vroot     Neil Grant    2018-08-07 WORK            WORK                           6.45          0
10  514802          Sheila Vroot  Neil Grant    2018-08-07 WORK            WORK                           8.33       1.06


Then this statement should be close to what you want:
SELECT
  supervisor,
  COUNT(employee_name) emp_count,
  SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 else 0 END) abs_count,
  SUM(CASE WHEN late_hours > 0 THEN 1 else 0 END) late_count,
  printf('%2.2f', 100.0 * SUM(CASE WHEN attendance_status LIKE 'ABSENT' THEN 1 else 0 END) / COUNT(employee_name)) abs_rate
FROM
  data_db
WHERE supervisor != ''
GROUP BY supervisor

The result then is:

supervisor      emp_count   abs_count   late_count  abs_rate
Bryan McGrath           2           1            1     50.00
Neil Grant              6           1            1     16.67
Summer Harley           2           1            1     50.00

Note that your DB currently contains text instead of numeric values.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By DJ143
      I have a autoit exe file which is used in upload/browse file functionality.  This has been integrated with selenium framework and I am invoking the autoit exe using Java process and runtime. 
      Now the issue is when I run the scripts and invoke the autoit exe in local it works perfectly.  But when I use selenium grid or jenkins to run the scripts in another windows server it is not working.
      Can anyone please suggest any solution for this?
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
    • By t0nZ
      Code to read a Spiceworks Database and export text data files in INI format.
      Focused on exporting data about PC inventory and useful to migrate to another inventory system.
      Tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)
      It creates one  text file for every single machine.
       
      ;Spiceworks Db Exporter ; ;- NSC - t0nZ 2021 ;code to read a Spiceworks Database and export text data files in INI format. ;focused on exporting data about PC inventory. ;useful to migrate to another inventory system. ;tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019) ;It creates one text files for every single machine. ; --> please adapt paths to your environment. #include <SQLite.au3> #include <String.au3> #include <File.au3> dataINIfromDBspiceworks() #Region spiceworks immport Func dataINIfromDBspiceworks() ConsoleWrite("start import from db Spiceworks" & @CRLF) Local $dbspicepath = "C:\scambio" Local $dbspice = "spiceworks_prod.db" ; =====================>>>>> START SQL DLL _SQLite_Startup() ;======================<<<<<<<<<<<<<<<<<<< _SQLite_Open($dbspicepath & '\' & $dbspice) ;- Local $aCPdata Local $hQuery Local $recordcount = 0 Local $salvarec = 0 ;------------------------------------------------ Local $spiceQuery = "SELECT serial_number," & _ "server_name," & _ "manufacturer," & _ "(model || '-' || raw_model)," & _ "processor_type," & _ "raw_processor_type," & _ "processor_architecture," & _ "raw_processor_type," & _ "number_of_processors," & _ "'speed'," & _ "CAST (memory AS FLOAT) / 1073741824," & _ "current_user," & _ "domain," & _ "network_adapters.dns_domain," & _ "'logon'," & _ "operating_system," & _ "os_architecture," & _ "version," & _ "'lang'," & _ "('C:;Fixed;' || (CAST (disks.size AS FLOAT) / 1048576) || ';' || (CAST (disks.free_space AS FLOAT) / 1048576) )," & _ "devices.ip_address," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "network_adapters.gateway," & _ "(network_adapters.description ||' - '|| network_adapters.name)," & _ "devices.mac_address," & _ "devices.updated_on," & _ "user_tag " & _ "FROM devices " & _ "inner JOIN " & _ "network_adapters ON network_adapters.computer_id = devices.id " & _ "inner JOIN " & _ "disks ON disks.computer_id = devices.id " & _ "WHERE disks.name = 'C:' " & _ "ORDER BY devices.updated_on DESC;" _SQLite_Query(-1, $spiceQuery, $hQuery) While _SQLite_FetchData($hQuery, $aCPdata) = $SQLITE_OK writeINI($aCPdata) $recordcount += 1 If $recordcount = $salvarec + 10 Then $salvarec = $recordcount ConsoleWrite($recordcount & " processed records " & @CRLF) EndIf WEnd ;________________________________________________ _SQLite_Close() _SQLite_Shutdown() EndFunc ;==>dataINIfromDBspiceworks Func writeINI($aCPdata) Local $folderdataINI = "c:\scambio\ini" If Not FileExists($folderdataINI) Then DirCreate($folderdataINI) $aCPdata[28] = StringRegExpReplace($aCPdata[28], "[\D]", "") ; this "2021-04-17 02:34:16" to that "20210417023416 Local $cpini = $folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" If FileExists($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") Then FileDelete($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") ; deleted previous files ! ConsoleWrite("deleted: " & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" & @CRLF) EndIf ;Section Unique :serial, computer name Local $aSectionUnique[2][2] = [["serial", $aCPdata[0]], ["computername", $aCPdata[1]]] IniWriteSection($cpini, "UNIQUE", $aSectionUnique, 0) ;Section Machine :manufacturer,model,cpuname,cpuid,cpuarc,cpuvendor,cpucores,cpuspeed,RAM,disk1capacity,disk1used Local $aSectionMachine[9][2] = [["manufacturer", $aCPdata[2]], ["model", $aCPdata[3]], ["cpuname", $aCPdata[4]], ["cpuid", $aCPdata[5]], ["cpuarc", $aCPdata[6]], ["cpuvendor", $aCPdata[7]], ["cpucores", $aCPdata[8]], ["cpuspeed", $aCPdata[9]], ["ram", $aCPdata[10]]] IniWriteSection($cpini, "MACHINE", $aSectionMachine, 0) ;Section User : username,domain,DNSdomain,logonServer Local $aSectionUser[4][2] = [["username", $aCPdata[11]], ["domain", $aCPdata[12]], ["DNSdomain", $aCPdata[13]], ["logonServer", $aCPdata[14]]] IniWriteSection($cpini, "USER", $aSectionUser, 0) ;Section OS : OSver, OSarch, OSbuild, OSlang Local $aSectionOS[4][2] = [["OSver", $aCPdata[15]], ["OSarch", $aCPdata[16]], ["OSbuild", $aCPdata[17]], ["OSlang", $aCPdata[18]]] IniWriteSection($cpini, "OS", $aSectionOS, 0) ;Section Disks: username,domain,DNSdomain,logonServer Local $aSectionDrives[1][2] = [["drives", $aCPdata[19]]] IniWriteSection($cpini, "DRIVES", $aSectionDrives, 0) ;Section network:localIP1,localIP2,localIP3,localIP4,publicIP,GW,adapter,mac Local $aSectionNetwork[8][2] = [["localIP1", $aCPdata[20]], ["localIP2", $aCPdata[21]], ["localIP3", $aCPdata[22]], ["localIP4", $aCPdata[23]], ["publicIP", $aCPdata[24]], ["GW", $aCPdata[25]], ["adapter", $aCPdata[26]], ["mac", $aCPdata[27]]] IniWriteSection($cpini, "NETWORK", $aSectionNetwork, 0) ;Section PLUS :date, groupid Local $aSectionPlus[2][2] = [["date", $aCPdata[28]], ["groupid", $aCPdata[29]]] IniWriteSection($cpini, "PLUS", $aSectionPlus, 0) EndFunc ;==>writeINI #EndRegion spiceworks import  
      Spiceworks on premise, now pretty abadonware, has a non crypted SQLite DB usually located in:
      c:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db The query was the difficult part (at least for me), and I export in INI format because it was part of my effort to migrate from Spiceworks to a custom made system (Computer Plucker see this post) where I already parse .INI files in a custom MySQL and/or SQLite DB.
       
    • By adityaparakh
      Hello ,
      I am trying to use Websockets in AutoIt.
      It is to fetch live stock market prices , API is provided and documentation available for python language.
      The link for the code snippet is :
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Introduction
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Instruments/paths/~1instruments~1subscription/post
       
      https://github.com/symphonyfintech/xts-pythonclient-api-sdk
       
      Second Link is to subscribe to a list of ExchangeInstruments.
      Now I would like to get live stock ltp (LastTradedPrice) for a few stocks whose "ExchangeInstrumentID" I know.
      I am able to use the WinHttp object to perform actions using simple codes like below :
      I have the secretKey and appkey and can generate the needed token. And get the unique ExchangeInstrumentID.

      Below code is just for example of how I am using WinHttp. Unrelated to socket part.
      Global $InteractiveAPItoken = IniRead(@ScriptDir & "\Config.ini", "token", "InteractiveAPItoken", "NA") $baseurl = "https://brokerlink.com/interactive/" $functionurl = "orders" $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") $oHTTP.Open("POST", $baseurl & $functionurl, False) $oHTTP.SetRequestHeader("Content-Type", "application/json;charset=UTF-8") $oHTTP.SetRequestHeader("authorization", $InteractiveAPItoken) $pD = '{ "exchangeSegment": "NSEFO", "exchangeInstrumentID": ' & $exchangeInstrumentID & ', "productType": "' & $producttype & '", "orderType": "MARKET", "orderSide": "' & $orderside & '", "timeInForce": "DAY", "disclosedQuantity": 0, "orderQuantity": ' & $qty & ', "limitPrice": 0, "stopPrice": 0, "orderUniqueIdentifier": "' & $orderidentifier & '"}' $oHTTP.Send($pD) $oReceived = $oHTTP.ResponseText $oStatusCode = $oHTTP.Status
          
          
      But am struggling to understand and use socket.
      Would be of great help if you can have a look at the link mentioned above and help with the code sample for AutoIt.
      To connect and listen to a socket.
      Thanks a lot
       
    • By Hermes
      Hi, I have a site that has the following elements below: 
      <div>More element here</div> <div>More element here</div> <div>More element here</div> When I do this in Auto It:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div") _WD_HighlightElement($sSession, $oSelectDiv, 1) I also tried to add [3], but it doesnt seems to work:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div[3]") _WD_HighlightElement($sSession, $oSelectDiv, 1) It always highlight the first one, but I am trying to highlight the 3rd in the list. Is there anyway to select the 3rd div without having to add any class/id in the divs, and without using XPATH? The structure of the elements in that site were built that way.
×
×
  • Create New...