Hello,
2015-08-08 changed the detection of view for a session detection with a cookie (default name of a php session)
A friend asked me to add a login and password to his php web site
I checked internet, I did not find a nice example (well, not quickly)
Security concern:
This type of login and password is half secure (if someone hack your root, he will get all login and passwords!!!)
Requirement package:
wampserver2.5-Apache-2.4.9-Mysql-5.6.17-php5.5.12-64b.exe
Requirement procedure:
To start a php web site locally, port 80 must be free, close skype or set his additionnals call option off on on another port
Install php WAMP
Copy index.php in c:\wamp\www
Type localhost in adress bar of any browser to access your local site (this will pick index.php as the first php web page)
----------------- index.php ----- use notepad.exe or notepad2.exe ------------------
<!DOCTYPE html>
<html:html>
<html:body>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><div>
<title>Login and password in PHP</title>
</head>
<body>
<?php
// debug mode is ON, lost of echo will display message about working order of the page
$debugmode = 1;
session_start();
// initialize global variables on first load of page
// view indicate a refresh if it set, but ! mean it is not set
if(!isset($_COOKIE["PHPSESSID"]))
{
// first load of page
// initialize session variables
$_SESSION['loginok'] = 0;
$_SESSION['securitylevel'] = 0;
$_SESSION['username'] = "";
$_SESSION['fullname'] = "Guest";
if($debugmode==1) {echo "globals SESSION variables initialized";};
}
else
{
// second load of page or more
if($debugmode==1) {echo "Not first load, no global variables initializing<br>";};
};
if(!isset($userstot))
{
// initialize program variables (in this sub, not public to functions or sub, not shared to any other program
if($debugmode==1) {echo "Globals regular variables initialized<br>";};
$i = 0;
$username[$i]="admin";
$password[$i]="admin";
$fullname[$i]="Serge Fournier";
$securitylevel[$i] = "100";
$i++;
$username[$i]="admin2";
$password[$i]="admin2";
$fullname[$i]="Serge Fournier 2";
$securitylevel[$i] = "10";
$userstot = count($username); // total users would be 2
$errorlogin = "";
}
if(isset($_POST['submitlogin']))
{
//if(empty($_POST['username'])){$_SESSION['loginok'] = 0;};
//if(empty($_POST['password'])){$_SESSION['loginok'] = 0;};
$usernameresult = trim($_POST['username']);
$passwordresult = trim($_POST['password']);
$usercnt = 0;
if($debugmode==1) {echo "Total number of users: $userstot <br>";};
foreach($username as $usernameloop)
{
if($usernameresult == $usernameloop)
{
if($debugmode==1) {echo "Username valid, cheking password...<br>";};
if($passwordresult == $password[$usercnt])
{
$_SESSION['loginok'] = 1;
$_SESSION['fullname'] = $fullname[$usercnt];
$_SESSION['username'] = $username[$usercnt];
$_SESSION['securitylevel'] = $securitylevel[$usercnt];
if($debugmode==1) {echo "session value (1 is login and password ok, 0 is bad something): " . $_SESSION['loginok']."<br>";};
};
};
$usercnt = $usercnt + 1;
};
if($_SESSION['loginok'] == 1)
{
$errorlogin = "";
}
else
{
$errorlogin = "ERROR Wrong login or password. This is case sensitive. Chek your caps lock state";
};
};
if(isset($_POST['submitlogoff']))
{
$_SESSION['loginok'] = 0;
};
if($_SESSION['loginok'] == 1)
{
// second load of page (this is a view refresh)
// main program ///////////////////////////////////////////////
echo "<h1>Main program</h1><br>";
echo "<br>Welcome ".$_SESSION['fullname']."<br>";
echo "<br>Your username is: ".$_SESSION['username']."<br>";
echo "<br>Your security level is: ".$_SESSION['securitylevel']."<br>";
// logoff button
$html = "";
$html.="<br><form id='logoff' action='{$_SERVER['PHP_SELF']}' method='post' accept-charset='UTF-8'>";
$html.="<fieldset>";
$html.="<legend>Logoff</legend>";
$html.="<input type='submit' name='submitlogoff' value='Logoff' />";
$html.="</fieldset>";
$html.="</form>";
echo $html;
ob_flush(); // empty any web browser buffer so text display immediatly
flush();
}
else
{
// login form, username and password box
if($debugmode==1) {echo "Session loginok value: " . $_SESSION['loginok'] . " (0 = you are not logged in)<br>";};
$html="<form id='login' action='{$_SERVER['PHP_SELF']}' method='post' accept-charset='UTF-8'>";
$html.="<fieldset>";
$html.="<legend>Login</legend>";
$html.="<label for='username' >UserName*:</label>";
$html.="<input type='text' name='username' id='username' maxlength='50' />";
$html.="<label for='password' >Password*:</label>";
$html.="<input type='password' name='password' id='password' maxlength='50' />";
$html.="<input type='submit' name='submitlogin' value='Login' /> $errorlogin";
$html.="</fieldset>";
$html.="</form>";
echo $html;
ob_flush(); // empty any web browser buffer so text display immediatly
flush();
};
?>
</html>
</body>
</html:body>
</html:html>
Saturday, August 1, 2015
Sunday, July 26, 2015
Bubble sort with supertotal in excel VBA
Hello,
So i decided, as i am a good guy, to reprogram my supertotal bubble sort in excel vba
Not that i am a good guy, but everyone think excel is a god, so i had to...
It will generate 3 excel tables with the dataset example (wich you can replace with a sql query)
It will still generate a log file starting with ZZZ
It will still generate a html file with all the tables
Requirements:
microsoft excel 2013
.xlsm extension (file with macro)
Activation of vba macros if excel present a warning
26/07/2015 19:48:14 Start of bubble sort example
TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting
TABLE 02 Split all the values to get only numbers and calculate a supertotal
TABLE 03 sorted after all was converted to number to make a supertotal
---------------------- excel VBA macro (alt F11, add module, insert this code) ---------------------
Sub bubblesort01()
'=== bubblesort_wildboy85.vbs
'=== requirement: wscript.exe
'=== very fast bubble sort
'=== by wildboy85 (sergefournier @ hotmail.com)
'=== objects needed
Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objshe = CreateObject("WScript.Shell")
Set objNet = CreateObject("WScript.Network")
'=== register base constants
Const hkcr = &H80000000 'HKEY_CLASSES_ROOT
Const HKCU = &H80000001 'HKEY_CURRENT_USER
Const hklm = &H80000002 'HKEY_LOCAL_MACHINE
Const hku = &H80000003 'HKEY_USERS
Const hkcc = &H80000005 'HKEY_CURRENT_CONFIG
Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adDouble = 5
'=== actual drive, actual directory, and "\"
'thepath = Application.ActiveWorkbook.path
thepath = Application.ActiveWorkbook.FullName
p = InStrRev(thepath, "\")
basedir = Left(thepath, p)
filnam = Right(thepath, Len(thepath) - p)
'=== log what we do, in same directory as the script
logall = 1
htmlout = 1
If logall = 1 Then
'=== debug log, get this file name, remove end, change start for ZZZ
'=== remove .vbs
path01 = Left(thepath, Len(thepath) - (Len(thepath) - InStr(thepath, ".") + 1))
name01 = Right(path01, Len(path01) - InStrRev(path01, "\"))
logname01 = "zzz_" & name01 & ".txt"
'=== always have a nice error trapping
err01 = 0: err02 = ""
On Error Resume Next
Set file02 = objFSo.OpenTextFile(logname01, 2, True)
err01 = Err: err02 = Err.Description
On Error GoTo 0
End If
If err01 = 0 Then
logall = 1
Else
'=== flag to tell our program to not write in log file if there is not log file open
logall = 0 '=== could not open logfile, no log
End If
'=== html output file, we want a nice table as output
If htmlout = 1 Then
path01 = Left(thepath, Len(thepath) - (Len(thepath) - InStr(thepath, ".") + 1))
name01 = Right(path01, Len(path01) - InStrRev(path01, "\"))
logname01 = name01 & "_output.html"
'=== always have a nice error trapping
err01 = 0: err02 = ""
On Error Resume Next
Set file03 = objFSo.OpenTextFile(logname01, 2, True)
err01 = Err: err02 = Err.Description
On Error GoTo 0
End If
If err01 = 0 Then
htmlout = 1
Else
'=== flag to tell our program to not write in log file if there is not log file open
htmlout = 0 '=== could not open logfile, no log
End If
If logall = 1 Then file02.WriteLine Date & " " & Time & " START Bubble sort wildboy85"
'=== the dataset could come from a database, but here we will add data ourseleves as an example
Set dataset01 = CreateObject("ADODB.Recordset")
dataset01.Fields.Append "Index", adVarChar, MaxCharacters
If logall = 1 Then file02.WriteLine Date & " " & Time & " First field appended"
dataset01.Fields.Append "item", adVarChar, MaxCharacters
dataset01.Fields.Append "quantity", adDouble
dataset01.Fields.Append "description", adVarChar, MaxCharacters
'dataset01.Fields.Append "supertotal DS", adVarChar, MaxCharacters
dataset01.Open
'=== accessing the column by name is slow, it would be faster by name if we have big loop
dataset01.AddNew
dataset01("Index") = "11a.1c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 1
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1z.10c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 2
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1a.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 45
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1b.2c.33"
dataset01("item") = "bolt"
dataset01("quantity") = 12
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1c.2c"
dataset01("item") = "bolt"
dataset01("quantity") = 5
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "10b.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 18
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.movefirst
'=== how many field? (columns)
fieldscount01 = dataset01.Fields.Count
'=== put the dataset demo in excel page (first) as a table
Dim myarray As Variant
array01 = dataset01.GetRows
'=== get number of rows (dimension 2 of the array)
On Error Resume Next
err01 = 0: err02 = ""
rowcount01 = UBound(array01, 2)
err01 = Err
err02 = Err.Description
On Error GoTo 0
If err01 = 0 Then
'=== 2 dimension in array, we continue (2 row of data is a minimum)
Set Sheet01 = Worksheets(1)
'=== delete old table01 if it exist
tablename01 = "table01"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = 1
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
If logall = 1 Then file02.WriteLine Date & " " & Time & " fieldcount: " & fieldscount01
If htmlout = 1 Then file03.WriteLine Date & " " & Time & " Start of bubble sort example<br><br>"
'=== this datasort will order our items badly, as if number 10 was smaller than number 1
dataset01.Sort = "index"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' excel table02 sorted with dataset sort command (as string)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'=== read dataset that was sorted as string
array01 = dataset01.GetRows
'=== delete old table01 if it exist
tablename01 = "table02"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = (4 + rowcount01) * 1
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
'=== html table
If htmlout = 1 Then file03.WriteLine " TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting<br>"
If htmlout = 1 Then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
If htmlout = 1 Then file03.WriteLine "<tr>"
For i = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & dataset01.Fields(i).Name & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "</tr>"
dataset01.movefirst
linecount = 0
Dim ara02()
Do While Not (dataset01.EOF) '=== magica loop, row
ReDim Preserve ara02(fieldscount01 - 1, linecount)
If htmlout = 1 Then file03.WriteLine "<tr>"
'=== put all values in an array, because a dataset this basic cannot switch a line of data
For i = 0 To fieldscount01 - 1
value01 = dataset01.Fields(i).Value
If IsNull(value01) Then value01 = ""
value01 = Trim(value01)
If htmlout = 1 Then file03.WriteLine "<td>" & value01 & "</td>"
ara02(i, linecount) = value01
Next
If htmlout = 1 Then file03.WriteLine "</tr>"
linecount = linecount + 1
dataset01.movenext
Loop
If htmlout = 1 Then file03.WriteLine "</table><br>"
'=== now we will split the line we will use to do the sort by number and letter (converted to numbers)
fieldtosort01 = 0 '=== field 0 contain what we want to sort, example: 1a.2b.3c
'=== we assume we will have only 3 data to sort, 1a 2b 3c
dataset01.movefirst
If htmlout = 1 Then file03.WriteLine "<br>TABLE 02 Split all the values to get only numbers and calculate a supertotal"
If htmlout = 1 Then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
If htmlout = 1 Then file03.WriteLine "<tr><td>split01</td><td>split02</td><td>split03</td>"
If htmlout = 1 Then file03.WriteLine "<td>Number</td><td>Letter</td><td>Number</td><td>Letter</td><td>Number</td><td>Letter</td>"
If htmlout = 1 Then file03.WriteLine "<td>Supertotal</td></tr>"
'=== split the chain 1a.2b.3c by adding dots in case 2b.3c does not exist
Dim split(2)
linecount = 0
Dim ara01()
Do While Not (dataset01.EOF) '=== magica loop, row
ReDim Preserve ara01(6, linecount) '=== the last dimension can be redimensionned so we reversed the dimensions
If htmlout = 1 Then file03.WriteLine "<tr>" '=== html line change
value01 = dataset01.Fields(fieldtosort01).Value
'=== clean value in case of dbnull (dbnull cannot be processed)
If IsNull(value01) Then value01 = ""
value01 = Trim(value01)
'=== add dots at the end of string to later split at the dots and get string inbetween
'=== so if the string have no dots, the split will still work and resturn empty string instead of trying to trap errors
value01 = value01 & "...."
split(0) = Left(value01, Len(value01) - (Len(value01) - InStr(value01, ".") + 1))
leftover = Mid(value01, InStr(value01, ".") + 1, Len(value01))
split(1) = Left(leftover, Len(leftover) - (Len(leftover) - InStr(leftover, ".") + 1))
leftover = Mid(leftover, InStr(leftover, ".") + 1, Len(leftover))
split(2) = Left(leftover, Len(leftover) - (Len(leftover) - InStr(leftover, ".") + 1))
If htmlout = 1 Then file03.WriteLine "<td>" & split(0) & "</td><td>" & split(1) & "</td><td>" & split(2) & "</td>"
'=== we now do an array of 6 fields/columns
'=== field0 is 1, field1 is a, field2 is 2, field3 is b, field4 is 3, field5 is c (from 1a.2b.3c)
splitcount = 0
For x03 = 0 To 5 Step 2 'order01, order01 letter, order02, order02 letter, order03, order03 letter (letter --> integer)
'=== split the split to get 2 value, 1 for number, 1 for alpha (split the chain "1a", "2b", "3c")
data01 = Trim(split(splitcount))
len01 = Len(data01)
If len01 > 1 Then
alpha01 = 0
For i = 1 To len01
'=== is this digit alpha?
asc01 = Asc(Mid(data01, i, 1))
If (asc01 > 64 And asc01 < 91) Or (asc01 > 96 And asc01 < 123) Then
alpha01 = 1
'=== this is a letter
If i > 1 Then
val01 = CInt(Left(data01, i - 1))
Else
End If
'=== code de classement final
'if logall=1 then file02.WriteLine "value before: " & data01 & " After: " & val01 & " + " & asc01 & chr(9) & val01+asc01
If asc01 > 96 Then '=== uppercase A
asc01 = asc01 - 96
ElseIf asc01 > 64 Then '=== lowercase a
asc01 = asc01 - 64
End If
ara01(x03, linecount) = val01 '=== new ordering integer for this column
'=== create a new column to class by letter after this one
ara01(x03 + 1, linecount) = asc01
End If
Next
If alpha01 = 0 Then
'=== remove the 0 before
ara01(x03, linecount) = CInt(data01)
ara01(x03 + 1, linecount) = 0
End If
ElseIf len01 > 0 Then
'=== only 1 digit, so we assume it's a number (just to be faster)
ara01(x03, linecount) = data01 '=== new ordering integer for this column
'=== column to class by letter after this one (1 digit = no letter = 0)
ara01(x03 + 1, linecount) = 0
Else
'=== len is 0, we do nothing
ara01(x03, linecount) = 0
ara01(x03 + 1, linecount) = 0
End If
splitcount = splitcount + 1
'=== results, all numerics
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(x03, linecount) & "</td><td>" & ara01(x03 + 1, linecount) & "</td>"
Next
'=== tricky part, doing a supertotal to sort only one number for all six fields/columns
supertotal = 0
mul01 = 5
super02 = ""
For x03 = 0 To 5
'=== do a super total for this line and the next for comparaison
super01 = (ara01(x03, linecount) + 1) * (1000 ^ (mul01 + 1))
supertotal = supertotal + super01
mul01 = mul01 - 1
'==== alphanumeric supertotal add 0 in front pf every digit, not working with dataset.sort either
'super02 = super02 & left("00000", 5-len((ara01(x03,linecount)+1))) & ara01(x03,linecount)+1
Next
'dataset01.fields("supertotal DS").value = super02
ara01(6, linecount) = supertotal
'dataset01.fields("supertotal DS").value = supertotal
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(6, linecount) & "</td>"
linecount = linecount + 1
If htmlout = 1 Then file03.WriteLine "</tr>"
dataset01.movenext
'=== all supertotal must be computed before we can sort
'=== so this loop was to split numbers, letters and compute a supertotal with multiplicated by 1000^positioninfieldvalue(0-5) values
Loop
If htmlout = 1 Then file03.WriteLine "</table>"
'=== sorting loop, using supertotal
'=== we move through dataset at the same time we move in the array that contain supertotal at position 6
If logall = 1 Then file02.WriteLine Date & " " & Time & " START bubble sort with supertotal"
'=== using SORT command from dataset would have been easy now, but noooo, error argument, number to weird
'=== even if this a string, it cannot sort with dateset.sort command
'dataset01.Sort = "supertotal DS"
dataset01.movefirst
'set row = CreateObject("ADODB.Recordsetrow")
'=== exchange lines in dataset, is that possible?
'dataset01.rows(1) = dataset01.rows(0)
'http://www.w3schools.com/asp/ado_ref_recordset.asp
''''''''''''''''''''''''''''''''''''''''''
' bubble sort
''''''''''''''''''''''''''''''''''''''''''
Dim supertotal01(1)
ReDim ara01tempo(linecount - 1)
If logall = 1 Then file02.WriteLine Date & " " & Time & " Bubble order START"
'=== classement bulle, ligne par ligne, 6 fois
For bubble01 = linecount - 2 To 0 Step -1
For y03 = 0 To bubble01 '=== magica loop, row
'=== if supertotal after is smaller, we exchange them
If ara01(6, y03 + 1) < ara01(6, y03) Then
For x04 = 0 To fieldscount01 - 1
'=== save actual matrix x elements
ara01tempo(x04) = ara02(x04, y03)
ara02(x04, y03) = ara02(x04, y03 + 1) '=== go up one row in matrix
ara02(x04, y03 + 1) = ara01tempo(x04)
Next
'=== exchange supertotal also
supertotaltemp = ara01(6, y03)
ara01(6, y03) = ara01(6, y03 + 1)
ara01(6, y03 + 1) = supertotaltemp
End If
Next
Next
If logall = 1 Then file02.WriteLine Date & " " & Time & " END bubble sort"
'=== final result
If htmlout = 1 Then file03.WriteLine "<br>TABLE 03 sorted after all was converted to number to make a supertotal"
If htmlout = 1 Then file03.WriteLine "<br><table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
If htmlout = 1 Then file03.WriteLine "<tr>"
For i = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & dataset01.Fields(i).Name & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "<td>Supertotal verification (not in DS)</td>"
If htmlout = 1 Then file03.WriteLine "</tr>"
For y = 0 To linecount - 1 '=== display results after bubble sort
If htmlout = 1 Then file03.WriteLine "<tr>"
For x = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & ara02(x, y) & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(6, y) & "</td>"
If htmlout = 1 Then file03.WriteLine "</tr>"
Next
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' table03 sorted with 6 columns (split of the first column) and super total
''''''''''''''''''''''''''''''''''''''''''''''''''''''
array01 = ara02
If htmlout = 1 Then file03.WriteLine "</table>"
'=== delete old table01 if it exist
tablename01 = "table03"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = (4 + rowcount01) * 2
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
If logall = 1 Then file02.WriteLine ""
If logall = 1 Then file02.WriteLine Date & " " & Time & " END"
Else
'=== error array have onle one dimension
MsgBox ("ERROR array only have one dimension" & vbCrLf & "the dataset must have more than one line of data" & vbCrLf & "meaning, 2 dimensions")
End If
If logall = 1 Then file02.Close
If htmlout = 1 Then file03.Close
End Sub
So i decided, as i am a good guy, to reprogram my supertotal bubble sort in excel vba
Not that i am a good guy, but everyone think excel is a god, so i had to...
It will generate 3 excel tables with the dataset example (wich you can replace with a sql query)
It will still generate a log file starting with ZZZ
It will still generate a html file with all the tables
Requirements:
microsoft excel 2013
.xlsm extension (file with macro)
Activation of vba macros if excel present a warning
26/07/2015 19:48:14 Start of bubble sort example
TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting
| Index | item | quantity | description |
| 10b.2c.33b | bolt | 18 | this is a bolt |
| 11a.1c.3b | bolt | 1 | this is a bolt |
| 1a.2c.33b | bolt | 45 | this is a bolt |
| 1b.2c.33 | bolt | 12 | this is a bolt |
| 1c.2c | bolt | 5 | this is a bolt |
| 1z.10c.3b | bolt | 2 | this is a bolt |
TABLE 02 Split all the values to get only numbers and calculate a supertotal
| split01 | split02 | split03 | Number | Letter | Number | Letter | Number | Letter | Supertotal |
| 10b | 2c | 33b | 10 | 2 | 2 | 3 | 33 | 2 | 1,1003003004034E+19 |
| 11a | 1c | 3b | 11 | 1 | 1 | 3 | 3 | 2 | 1,2002002004004E+19 |
| 1a | 2c | 33b | 1 | 1 | 2 | 3 | 33 | 2 | 2,002003004034E+18 |
| 1b | 2c | 33 | 1 | 2 | 2 | 3 | 33 | 0 | 2,003003004034E+18 |
| 1c | 2c | 1 | 3 | 2 | 3 | 0 | 0 | 2,004003004001E+18 | |
| 1z | 10c | 3b | 1 | 26 | 10 | 3 | 3 | 2 | 2,027011004004E+18 |
TABLE 03 sorted after all was converted to number to make a supertotal
| Index | item | quantity | description | Supertotal verification (not in DS) |
| 1a.2c.33b | bolt | 45 | this is a bolt | 2,002003004034E+18 |
| 1b.2c.33 | bolt | 12 | this is a bolt | 2,003003004034E+18 |
| 1c.2c | bolt | 5 | this is a bolt | 2,004003004001E+18 |
| 1z.10c.3b | bolt | 2 | this is a bolt | 2,027011004004E+18 |
| 10b.2c.33b | bolt | 18 | this is a bolt | 1,1003003004034E+19 |
| 11a.1c.3b | bolt | 1 | this is a bolt | 1,2002002004004E+19 |
---------------------- excel VBA macro (alt F11, add module, insert this code) ---------------------
Sub bubblesort01()
'=== bubblesort_wildboy85.vbs
'=== requirement: wscript.exe
'=== very fast bubble sort
'=== by wildboy85 (sergefournier @ hotmail.com)
'=== objects needed
Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objshe = CreateObject("WScript.Shell")
Set objNet = CreateObject("WScript.Network")
'=== register base constants
Const hkcr = &H80000000 'HKEY_CLASSES_ROOT
Const HKCU = &H80000001 'HKEY_CURRENT_USER
Const hklm = &H80000002 'HKEY_LOCAL_MACHINE
Const hku = &H80000003 'HKEY_USERS
Const hkcc = &H80000005 'HKEY_CURRENT_CONFIG
Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adDouble = 5
'=== actual drive, actual directory, and "\"
'thepath = Application.ActiveWorkbook.path
thepath = Application.ActiveWorkbook.FullName
p = InStrRev(thepath, "\")
basedir = Left(thepath, p)
filnam = Right(thepath, Len(thepath) - p)
'=== log what we do, in same directory as the script
logall = 1
htmlout = 1
If logall = 1 Then
'=== debug log, get this file name, remove end, change start for ZZZ
'=== remove .vbs
path01 = Left(thepath, Len(thepath) - (Len(thepath) - InStr(thepath, ".") + 1))
name01 = Right(path01, Len(path01) - InStrRev(path01, "\"))
logname01 = "zzz_" & name01 & ".txt"
'=== always have a nice error trapping
err01 = 0: err02 = ""
On Error Resume Next
Set file02 = objFSo.OpenTextFile(logname01, 2, True)
err01 = Err: err02 = Err.Description
On Error GoTo 0
End If
If err01 = 0 Then
logall = 1
Else
'=== flag to tell our program to not write in log file if there is not log file open
logall = 0 '=== could not open logfile, no log
End If
'=== html output file, we want a nice table as output
If htmlout = 1 Then
path01 = Left(thepath, Len(thepath) - (Len(thepath) - InStr(thepath, ".") + 1))
name01 = Right(path01, Len(path01) - InStrRev(path01, "\"))
logname01 = name01 & "_output.html"
'=== always have a nice error trapping
err01 = 0: err02 = ""
On Error Resume Next
Set file03 = objFSo.OpenTextFile(logname01, 2, True)
err01 = Err: err02 = Err.Description
On Error GoTo 0
End If
If err01 = 0 Then
htmlout = 1
Else
'=== flag to tell our program to not write in log file if there is not log file open
htmlout = 0 '=== could not open logfile, no log
End If
If logall = 1 Then file02.WriteLine Date & " " & Time & " START Bubble sort wildboy85"
'=== the dataset could come from a database, but here we will add data ourseleves as an example
Set dataset01 = CreateObject("ADODB.Recordset")
dataset01.Fields.Append "Index", adVarChar, MaxCharacters
If logall = 1 Then file02.WriteLine Date & " " & Time & " First field appended"
dataset01.Fields.Append "item", adVarChar, MaxCharacters
dataset01.Fields.Append "quantity", adDouble
dataset01.Fields.Append "description", adVarChar, MaxCharacters
'dataset01.Fields.Append "supertotal DS", adVarChar, MaxCharacters
dataset01.Open
'=== accessing the column by name is slow, it would be faster by name if we have big loop
dataset01.AddNew
dataset01("Index") = "11a.1c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 1
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1z.10c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 2
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1a.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 45
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1b.2c.33"
dataset01("item") = "bolt"
dataset01("quantity") = 12
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1c.2c"
dataset01("item") = "bolt"
dataset01("quantity") = 5
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "10b.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 18
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.movefirst
'=== how many field? (columns)
fieldscount01 = dataset01.Fields.Count
'=== put the dataset demo in excel page (first) as a table
Dim myarray As Variant
array01 = dataset01.GetRows
'=== get number of rows (dimension 2 of the array)
On Error Resume Next
err01 = 0: err02 = ""
rowcount01 = UBound(array01, 2)
err01 = Err
err02 = Err.Description
On Error GoTo 0
If err01 = 0 Then
'=== 2 dimension in array, we continue (2 row of data is a minimum)
Set Sheet01 = Worksheets(1)
'=== delete old table01 if it exist
tablename01 = "table01"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = 1
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
If logall = 1 Then file02.WriteLine Date & " " & Time & " fieldcount: " & fieldscount01
If htmlout = 1 Then file03.WriteLine Date & " " & Time & " Start of bubble sort example<br><br>"
'=== this datasort will order our items badly, as if number 10 was smaller than number 1
dataset01.Sort = "index"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' excel table02 sorted with dataset sort command (as string)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'=== read dataset that was sorted as string
array01 = dataset01.GetRows
'=== delete old table01 if it exist
tablename01 = "table02"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = (4 + rowcount01) * 1
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
'=== html table
If htmlout = 1 Then file03.WriteLine " TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting<br>"
If htmlout = 1 Then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
If htmlout = 1 Then file03.WriteLine "<tr>"
For i = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & dataset01.Fields(i).Name & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "</tr>"
dataset01.movefirst
linecount = 0
Dim ara02()
Do While Not (dataset01.EOF) '=== magica loop, row
ReDim Preserve ara02(fieldscount01 - 1, linecount)
If htmlout = 1 Then file03.WriteLine "<tr>"
'=== put all values in an array, because a dataset this basic cannot switch a line of data
For i = 0 To fieldscount01 - 1
value01 = dataset01.Fields(i).Value
If IsNull(value01) Then value01 = ""
value01 = Trim(value01)
If htmlout = 1 Then file03.WriteLine "<td>" & value01 & "</td>"
ara02(i, linecount) = value01
Next
If htmlout = 1 Then file03.WriteLine "</tr>"
linecount = linecount + 1
dataset01.movenext
Loop
If htmlout = 1 Then file03.WriteLine "</table><br>"
'=== now we will split the line we will use to do the sort by number and letter (converted to numbers)
fieldtosort01 = 0 '=== field 0 contain what we want to sort, example: 1a.2b.3c
'=== we assume we will have only 3 data to sort, 1a 2b 3c
dataset01.movefirst
If htmlout = 1 Then file03.WriteLine "<br>TABLE 02 Split all the values to get only numbers and calculate a supertotal"
If htmlout = 1 Then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
If htmlout = 1 Then file03.WriteLine "<tr><td>split01</td><td>split02</td><td>split03</td>"
If htmlout = 1 Then file03.WriteLine "<td>Number</td><td>Letter</td><td>Number</td><td>Letter</td><td>Number</td><td>Letter</td>"
If htmlout = 1 Then file03.WriteLine "<td>Supertotal</td></tr>"
'=== split the chain 1a.2b.3c by adding dots in case 2b.3c does not exist
Dim split(2)
linecount = 0
Dim ara01()
Do While Not (dataset01.EOF) '=== magica loop, row
ReDim Preserve ara01(6, linecount) '=== the last dimension can be redimensionned so we reversed the dimensions
If htmlout = 1 Then file03.WriteLine "<tr>" '=== html line change
value01 = dataset01.Fields(fieldtosort01).Value
'=== clean value in case of dbnull (dbnull cannot be processed)
If IsNull(value01) Then value01 = ""
value01 = Trim(value01)
'=== add dots at the end of string to later split at the dots and get string inbetween
'=== so if the string have no dots, the split will still work and resturn empty string instead of trying to trap errors
value01 = value01 & "...."
split(0) = Left(value01, Len(value01) - (Len(value01) - InStr(value01, ".") + 1))
leftover = Mid(value01, InStr(value01, ".") + 1, Len(value01))
split(1) = Left(leftover, Len(leftover) - (Len(leftover) - InStr(leftover, ".") + 1))
leftover = Mid(leftover, InStr(leftover, ".") + 1, Len(leftover))
split(2) = Left(leftover, Len(leftover) - (Len(leftover) - InStr(leftover, ".") + 1))
If htmlout = 1 Then file03.WriteLine "<td>" & split(0) & "</td><td>" & split(1) & "</td><td>" & split(2) & "</td>"
'=== we now do an array of 6 fields/columns
'=== field0 is 1, field1 is a, field2 is 2, field3 is b, field4 is 3, field5 is c (from 1a.2b.3c)
splitcount = 0
For x03 = 0 To 5 Step 2 'order01, order01 letter, order02, order02 letter, order03, order03 letter (letter --> integer)
'=== split the split to get 2 value, 1 for number, 1 for alpha (split the chain "1a", "2b", "3c")
data01 = Trim(split(splitcount))
len01 = Len(data01)
If len01 > 1 Then
alpha01 = 0
For i = 1 To len01
'=== is this digit alpha?
asc01 = Asc(Mid(data01, i, 1))
If (asc01 > 64 And asc01 < 91) Or (asc01 > 96 And asc01 < 123) Then
alpha01 = 1
'=== this is a letter
If i > 1 Then
val01 = CInt(Left(data01, i - 1))
Else
End If
'=== code de classement final
'if logall=1 then file02.WriteLine "value before: " & data01 & " After: " & val01 & " + " & asc01 & chr(9) & val01+asc01
If asc01 > 96 Then '=== uppercase A
asc01 = asc01 - 96
ElseIf asc01 > 64 Then '=== lowercase a
asc01 = asc01 - 64
End If
ara01(x03, linecount) = val01 '=== new ordering integer for this column
'=== create a new column to class by letter after this one
ara01(x03 + 1, linecount) = asc01
End If
Next
If alpha01 = 0 Then
'=== remove the 0 before
ara01(x03, linecount) = CInt(data01)
ara01(x03 + 1, linecount) = 0
End If
ElseIf len01 > 0 Then
'=== only 1 digit, so we assume it's a number (just to be faster)
ara01(x03, linecount) = data01 '=== new ordering integer for this column
'=== column to class by letter after this one (1 digit = no letter = 0)
ara01(x03 + 1, linecount) = 0
Else
'=== len is 0, we do nothing
ara01(x03, linecount) = 0
ara01(x03 + 1, linecount) = 0
End If
splitcount = splitcount + 1
'=== results, all numerics
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(x03, linecount) & "</td><td>" & ara01(x03 + 1, linecount) & "</td>"
Next
'=== tricky part, doing a supertotal to sort only one number for all six fields/columns
supertotal = 0
mul01 = 5
super02 = ""
For x03 = 0 To 5
'=== do a super total for this line and the next for comparaison
super01 = (ara01(x03, linecount) + 1) * (1000 ^ (mul01 + 1))
supertotal = supertotal + super01
mul01 = mul01 - 1
'==== alphanumeric supertotal add 0 in front pf every digit, not working with dataset.sort either
'super02 = super02 & left("00000", 5-len((ara01(x03,linecount)+1))) & ara01(x03,linecount)+1
Next
'dataset01.fields("supertotal DS").value = super02
ara01(6, linecount) = supertotal
'dataset01.fields("supertotal DS").value = supertotal
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(6, linecount) & "</td>"
linecount = linecount + 1
If htmlout = 1 Then file03.WriteLine "</tr>"
dataset01.movenext
'=== all supertotal must be computed before we can sort
'=== so this loop was to split numbers, letters and compute a supertotal with multiplicated by 1000^positioninfieldvalue(0-5) values
Loop
If htmlout = 1 Then file03.WriteLine "</table>"
'=== sorting loop, using supertotal
'=== we move through dataset at the same time we move in the array that contain supertotal at position 6
If logall = 1 Then file02.WriteLine Date & " " & Time & " START bubble sort with supertotal"
'=== using SORT command from dataset would have been easy now, but noooo, error argument, number to weird
'=== even if this a string, it cannot sort with dateset.sort command
'dataset01.Sort = "supertotal DS"
dataset01.movefirst
'set row = CreateObject("ADODB.Recordsetrow")
'=== exchange lines in dataset, is that possible?
'dataset01.rows(1) = dataset01.rows(0)
'http://www.w3schools.com/asp/ado_ref_recordset.asp
''''''''''''''''''''''''''''''''''''''''''
' bubble sort
''''''''''''''''''''''''''''''''''''''''''
Dim supertotal01(1)
ReDim ara01tempo(linecount - 1)
If logall = 1 Then file02.WriteLine Date & " " & Time & " Bubble order START"
'=== classement bulle, ligne par ligne, 6 fois
For bubble01 = linecount - 2 To 0 Step -1
For y03 = 0 To bubble01 '=== magica loop, row
'=== if supertotal after is smaller, we exchange them
If ara01(6, y03 + 1) < ara01(6, y03) Then
For x04 = 0 To fieldscount01 - 1
'=== save actual matrix x elements
ara01tempo(x04) = ara02(x04, y03)
ara02(x04, y03) = ara02(x04, y03 + 1) '=== go up one row in matrix
ara02(x04, y03 + 1) = ara01tempo(x04)
Next
'=== exchange supertotal also
supertotaltemp = ara01(6, y03)
ara01(6, y03) = ara01(6, y03 + 1)
ara01(6, y03 + 1) = supertotaltemp
End If
Next
Next
If logall = 1 Then file02.WriteLine Date & " " & Time & " END bubble sort"
'=== final result
If htmlout = 1 Then file03.WriteLine "<br>TABLE 03 sorted after all was converted to number to make a supertotal"
If htmlout = 1 Then file03.WriteLine "<br><table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
If htmlout = 1 Then file03.WriteLine "<tr>"
For i = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & dataset01.Fields(i).Name & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "<td>Supertotal verification (not in DS)</td>"
If htmlout = 1 Then file03.WriteLine "</tr>"
For y = 0 To linecount - 1 '=== display results after bubble sort
If htmlout = 1 Then file03.WriteLine "<tr>"
For x = 0 To fieldscount01 - 1
If htmlout = 1 Then file03.WriteLine "<td>" & ara02(x, y) & "</td>"
Next
If htmlout = 1 Then file03.WriteLine "<td>" & ara01(6, y) & "</td>"
If htmlout = 1 Then file03.WriteLine "</tr>"
Next
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' table03 sorted with 6 columns (split of the first column) and super total
''''''''''''''''''''''''''''''''''''''''''''''''''''''
array01 = ara02
If htmlout = 1 Then file03.WriteLine "</table>"
'=== delete old table01 if it exist
tablename01 = "table03"
On Error Resume Next
err01 = 0: err02 = ""
Sheet01.ListObjects(tablename01).Delete
err01 = Err
err02 = Err.Description
On Error GoTo 0
x = 1: y = (4 + rowcount01) * 2
'=== columns names
For i = 0 To fieldscount01 - 1
Cells(y, x + i).Value = dataset01.Fields(i).Name
Next
'=== range01 include column title
Set Range01 = Range(Cells(y, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
'=== range02 is data only
Set Range02 = Range(Cells(y + 1, x), Cells(y + 1 + rowcount01, x + fieldscount01 - 1))
Range02.Value = Application.WorksheetFunction.Transpose(array01)
Sheet01.ListObjects.Add(xlSrcRange, Range01, , xlYes).Name = tablename01
If logall = 1 Then file02.WriteLine ""
If logall = 1 Then file02.WriteLine Date & " " & Time & " END"
Else
'=== error array have onle one dimension
MsgBox ("ERROR array only have one dimension" & vbCrLf & "the dataset must have more than one line of data" & vbCrLf & "meaning, 2 dimensions")
End If
If logall = 1 Then file02.Close
If htmlout = 1 Then file03.Close
End Sub
Monday, July 13, 2015
Bubble sort vbs wsh script program by wildboy85
Hello,
Sorting data is easy
Doing it faster than with a regular bubble sort method is a little harder
Aa an example, we will use something not simple
6 columns to sort with numbers and letters
1a.2b.3c
This was originally for a BOM insertion (bill of material)
There is different categories of materials, (numbers) and different sub categories (letters)
We keep numbers + 1
We change letters to numbers + 1
We calculate a super total to sort them out in one bubble pass
On bubble pass is going from element 0 to elementmax - 1
Then another loop inside that will compare element now with element after and switch them if the element after's supertotal is smaller
This is a nested loop, that will loop a lot of time of there is a lot of elements
It would have done it 6 time is we had used a classic method to sort with 6 columns, like SQL do when you use ORDER BY field01, field02, field03, field04, field05, field06
By concatening the 6 field in one supertotal, we can bubble sort only once
This is a vb script
It will generate a log.txt
and a log.html with same start name as the script
Save it with notepad.exe
26/07/2015 19:48:14 Start of bubble sort example
TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting
TABLE 02 Split all the values to get only numbers and calculate a supertotal
TABLE 03 sorted after all was converted to number to make a supertotal
-------------------------------- bubblesort.vbs -------------------
'=== bubblesort_wildboy85.vbs
'=== requirement: wscript.exe
'=== very fast bubble sort
'=== by wildboy85 (sergefournier @ hotmail.com)
'=== objects needed
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objshe = CreateObject("WScript.Shell")
Set objNet = CreateObject("WScript.Network")
'=== register base constants
Const hkcr = &H80000000 'HKEY_CLASSES_ROOT
Const HKCU = &H80000001 'HKEY_CURRENT_USER
Const hklm = &H80000002 'HKEY_LOCAL_MACHINE
Const hku = &H80000003 'HKEY_USERS
Const hkcc = &H80000005 'HKEY_CURRENT_CONFIG
Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adDouble = 5
'=== actual drive, actual directory, and "\"
thepath = WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir = left(thepath,p)
filnam = right(thepath,len(thepath)-p)
'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
'=== restart the script in 32 bits if we are on a 64 bits system
'=== some databases drivers are not yet available in 64 bits
a64 = windir & "\syswow64\wscript.exe"
if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
'=== 64 bits system
argchain01 = ""
set args01 = Wscript.Arguments
if args01.count<>0 then
'=== when recalling this script in 32 bits, pass all the parameters
For Each arg01 in args01
argchain01 = argchain01 & " " & arg01
Next
end if
a = """" & a64 & """ """ & basedir & filnam & """" & argchain01
objshe.Run a,0, false
wscript.quit
end if
'=== log what we do, in same directory as the script
logall = 1
htmlout = 1
if logall=1 then
'=== debug log, get this file name, remove end, change start for ZZZ
'=== remove .vbs
path01 = left(thepath,len(thepath)-(len(thepath)-instr(thepath,".")+1))
name01 = right(path01,len(path01)-instrrev(path01,"\"))
logname01 = "zzz_" & name01 & ".txt"
'=== always have a nice error trapping
err01 = 0 : err02 = ""
on error resume next
Set File02 = objFSo.OpenTextFile(logname01, 2, true)
err01 = err : err02 = err.description
on error goto 0
end if
if err01 = 0 then
logall = 1
else
'=== flag to tell our program to not write in log file if there is not log file open
logall = 0 '=== could not open logfile, no log
end if
'=== html output file, we want a nice table as output
if htmlout=1 then
path01 = left(thepath,len(thepath)-(len(thepath)-instr(thepath,".")+1))
name01 = right(path01,len(path01)-instrrev(path01,"\"))
logname01 = name01 & "_output.html"
'=== always have a nice error trapping
err01 = 0 : err02 = ""
on error resume next
Set File03 = objFSo.OpenTextFile(logname01, 2, true)
err01 = err : err02 = err.description
on error goto 0
end if
if err01 = 0 then
htmlout = 1
else
'=== flag to tell our program to not write in log file if there is not log file open
htmlout = 0 '=== could not open logfile, no log
end if
if logall = 1 then file02.WriteLine date & " " & time & " START Bubble sort wildboy85"
'=== the dataset could come from a database, but here we will add data ourseleves as an example
set dataset01 = CreateObject("ADODB.Recordset")
dataset01.Fields.Append "Index", adVarChar, MaxCharacters
if logall = 1 then file02.WriteLine date & " " & time & " First field appended"
dataset01.Fields.Append "item", adVarChar, MaxCharacters
dataset01.Fields.Append "quantity", adDouble
dataset01.Fields.Append "description", adVarChar, MaxCharacters
'dataset01.Fields.Append "supertotal DS", adVarChar, MaxCharacters
dataset01.Open
'=== accessing the column by name is slow, it would be faster by name if we have big loop
dataset01.AddNew
dataset01("Index") = "1a.1c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 1
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1z.10c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 2
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1a.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 45
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1b.2c.33"
dataset01("item") = "bolt"
dataset01("quantity") = 12
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1c.2c"
dataset01("item") = "bolt"
dataset01("quantity") =5
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "10b.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 18
dataset01("description") = "this is a bolt"
dataset01.Update
'=== how many field? (columns)
fieldscount01 = dataset01.fields.count
if logall = 1 then file02.WriteLine date & " " & time & " fieldcount: " & fieldscount01
if htmlout = 1 then file03.WriteLine date & " " & time & " Start of bubble sort example<br><br>"
'=== this datasort will order our items badly, as if number 10 was smaller than number 1
dataset01.Sort = "index"
'=== html table
if htmlout = 1 then file03.WriteLine " TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting<br>"
if htmlout = 1 then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
if htmlout = 1 then file03.WriteLine "<tr>"
for i = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & dataset01.fields(i).name & "</td>"
next
if htmlout = 1 then file03.WriteLine "</tr>"
dataset01.movefirst
linecount = 0
do while not (dataset01.eof) '=== magica loop, row
redim preserve ara02(fieldscount01-1, linecount)
if htmlout = 1 then file03.WriteLine "<tr>"
'=== put all values in an array, because a dataset this basic cannot switch a line of data
for i = 0 to fieldscount01-1
value01 = dataset01.fields(i).value
If IsNull(value01) then value01=""
value01 = Trim(value01)
if htmlout = 1 then file03.WriteLine "<td>" & value01 & "</td>"
ara02(i, linecount) = value01
next
if htmlout = 1 then file03.WriteLine "</tr>"
linecount = linecount + 1
dataset01.movenext
loop
if htmlout = 1 then file03.WriteLine "</table><br>"
'=== now we will split the line we will use to do the sort by number and letter (converted to numbers)
fieldtosort01 = 0 '=== field 0 contain what we want to sort, example: 1a.2b.3c
'=== we assume we will have only 3 data to sort, 1a 2b 3c
dataset01.movefirst
if htmlout = 1 then file03.WriteLine "<br>TABLE 02 Split all the values to get only numbers and calculate a supertotal"
if htmlout = 1 then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
if htmlout = 1 then file03.WriteLine "<tr><td>split01</td><td>split02</td><td>split03</td>"
if htmlout = 1 then file03.WriteLine "<td>Number</td><td>Letter</td><td>Number</td><td>Letter</td><td>Number</td><td>Letter</td>"
if htmlout = 1 then file03.WriteLine "<td>Supertotal</td></tr>"
'=== split the chain 1a.2b.3c by adding dots in case 2b.3c does not exist
dim split(2)
linecount = 0
do while not (dataset01.eof) '=== magica loop, row
redim preserve ara01(6,linecount) '=== the last dimension can be redimensionned so we reversed the dimensions
if htmlout = 1 then file03.WriteLine "<tr>" '=== html line change
value01 = dataset01.fields(fieldtosort01).value
'=== clean value in case of dbnull (dbnull cannot be processed)
If IsNull(value01) then value01=""
value01 = Trim(value01)
'=== add dots at the end of string to later split at the dots and get string inbetween
'=== so if the string have no dots, the split will still work and resturn empty string instead of trying to trap errors
value01 = value01 & "...."
split(0) = left(value01,len(value01)-(len(value01)-instr(value01,".")+1))
leftover = mid(value01,instr(value01,".")+1,len(value01))
split(1)= left(leftover,len(leftover)-(len(leftover)-instr(leftover,".")+1))
leftover = mid(leftover,instr(leftover,".")+1,len(leftover))
split(2) = left(leftover,len(leftover)-(len(leftover)-instr(leftover,".")+1))
if htmlout = 1 then file03.WriteLine "<td>" & split(0) & "</td><td>" & split(1) & "</td><td>" & split(2) & "</td>"
'=== we now do an array of 6 fields/columns
'=== field0 is 1, field1 is a, field2 is 2, field3 is b, field4 is 3, field5 is c (from 1a.2b.3c)
splitcount = 0
for x03 = 0 to 5 step 2 'order01, order01 letter, order02, order02 letter, order03, order03 letter (letter --> integer)
'=== split the split to get 2 value, 1 for number, 1 for alpha (split the chain "1a", "2b", "3c")
data01 = trim(split(splitcount))
len01 = len(data01)
if len01>1 then
alpha01 = 0
for i = 1 to len01
'=== is this digit alpha?
asc01 = ASC(mid(data01,i,1))
if (asc01>64 and ASC01<91) or (asc01 >96 and asc01 <123) then
alpha01 = 1
'=== this is a letter
if i>1 then
val01 = cint(left(data01,i-1))
else
end if
'=== code de classement final
'if logall=1 then file02.WriteLine "value before: " & data01 & " After: " & val01 & " + " & asc01 & chr(9) & val01+asc01
if asc01>96 then '=== uppercase A
asc01 = asc01 - 96
elseif asc01>64 then '=== lowercase a
asc01 = asc01 - 64
end if
ara01(x03,linecount) = val01 '=== new ordering integer for this column
'=== create a new column to class by letter after this one
ara01(x03+1,linecount) = asc01
end if
next
if alpha01 = 0 then
'=== remove the 0 before
ara01(x03,linecount) = cint(data01)
ara01(x03+1,linecount) = 0
end if
elseif len01>0 then
'=== only 1 digit, so we assume it's a number (just to be faster)
ara01(x03,linecount) = data01 '=== new ordering integer for this column
'=== column to class by letter after this one (1 digit = no letter = 0)
ara01(x03+1,linecount) = 0
else
'=== len is 0, we do nothing
ara01(x03,linecount) = 0
ara01(x03+1,linecount) = 0
end if
splitcount = splitcount + 1
'=== results, all numerics
if htmlout = 1 then file03.WriteLine "<td>" & ara01(x03,linecount) & "</td><td>" & ara01(x03+1,linecount) & "</td>"
next
'=== tricky part, doing a supertotal to sort only one number for all six fields/columns
supertotal = 0
mul01 = 5
super02 = ""
for x03 = 0 to 5
'=== do a super total for this line and the next for comparaison
super01 = (ara01(x03,linecount)+1)*(1000^(mul01+1))
supertotal = supertotal + super01
mul01 = mul01 - 1
'==== alphanumeric supertotal add 0 in front pf every digit, not working with dataset.sort either
'super02 = super02 & left("00000", 5-len((ara01(x03,linecount)+1))) & ara01(x03,linecount)+1
next
'dataset01.fields("supertotal DS").value = super02
ara01(6,linecount) = supertotal
'dataset01.fields("supertotal DS").value = supertotal
if htmlout = 1 then file03.WriteLine "<td>" & ara01(6,linecount) & "</td>"
linecount = linecount + 1
if htmlout = 1 then file03.WriteLine "</tr>"
dataset01.movenext
'=== all supertotal must be computed before we can sort
'=== so this loop was to split numbers, letters and compute a supertotal with multiplicated by 1000^positioninfieldvalue(0-5) values
loop
if htmlout = 1 then file03.WriteLine "</table>"
'=== sorting loop, using supertotal
'=== we move through dataset at the same time we move in the array that contain supertotal at position 6
if logall = 1 then file02.WriteLine date & " " & time & " START bubble sort with supertotal"
'=== using SORT command from dataset would have been easy now, but noooo, error argument, number to weird
'=== even if this a string, it cannot sort with dateset.sort command
'dataset01.Sort = "supertotal DS"
dataset01.movefirst
'set row = CreateObject("ADODB.Recordsetrow")
'=== exchange lines in dataset, is that possible?
'dataset01.rows(1) = dataset01.rows(0)
'http://www.w3schools.com/asp/ado_ref_recordset.asp
''''''''''''''''''''''''''''''''''''''''''
' bubble sort
''''''''''''''''''''''''''''''''''''''''''
dim supertotal01(1)
redim ara01tempo(linecount-1)
if logall=1 then file02.WriteLine date & " " & time & " Bubble order START"
'=== classement bulle, ligne par ligne, 6 fois
for bubble01 = linecount-2 to 0 step -1
for y03 = 0 to bubble01 '=== magica loop, row
'=== if supertotal after is smaller, we exchange them
if ara01(6,y03+1) < ara01(6,y03) then
for x04 = 0 to fieldscount01-1
'=== save actual matrix x elements
ara01tempo(x04) = ara02(x04,y03)
ara02(x04,y03) = ara02(x04,y03+1) '=== go up one row in matrix
ara02(x04,y03+1) = ara01tempo(x04)
next
'=== exchange supertotal also
supertotaltemp = ara01(6,y03)
ara01(6,y03) = ara01(6,y03+1)
ara01(6,y03+1) = supertotaltemp
end if
next
next
if logall = 1 then file02.WriteLine date & " " & time & " END bubble sort"
'=== final result
if htmlout = 1 then file03.WriteLine "<br>TABLE 03 sorted after all was converted to number to make a supertotal"
if htmlout = 1 then file03.WriteLine "<br><table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
if htmlout = 1 then file03.WriteLine "<tr>"
for i = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & dataset01.fields(i).name & "</td>"
next
if htmlout = 1 then file03.WriteLine "<td>Supertotal verification (not in DS)</td>"
if htmlout = 1 then file03.WriteLine "</tr>"
for y = 0 to linecount-1 '=== display results after bubble sort
if htmlout = 1 then file03.WriteLine "<tr>"
for x = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & ara02(x,y) & "</td>"
next
if htmlout = 1 then file03.WriteLine "<td>" & ara01(6,y) & "</td>"
if htmlout = 1 then file03.WriteLine "</tr>"
next
if htmlout = 1 then file03.WriteLine "</table>"
if logall = 1 then file02.WriteLine ""
if logall = 1 then file02.WriteLine date & " " & time & " END"
if logall = 1 then file02.close
if htmlout = 1 then file03.close
Sorting data is easy
Doing it faster than with a regular bubble sort method is a little harder
Aa an example, we will use something not simple
6 columns to sort with numbers and letters
1a.2b.3c
This was originally for a BOM insertion (bill of material)
There is different categories of materials, (numbers) and different sub categories (letters)
We keep numbers + 1
We change letters to numbers + 1
We calculate a super total to sort them out in one bubble pass
On bubble pass is going from element 0 to elementmax - 1
Then another loop inside that will compare element now with element after and switch them if the element after's supertotal is smaller
This is a nested loop, that will loop a lot of time of there is a lot of elements
It would have done it 6 time is we had used a classic method to sort with 6 columns, like SQL do when you use ORDER BY field01, field02, field03, field04, field05, field06
By concatening the 6 field in one supertotal, we can bubble sort only once
This is a vb script
It will generate a log.txt
and a log.html with same start name as the script
Save it with notepad.exe
26/07/2015 19:48:14 Start of bubble sort example
TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting
| Index | item | quantity | description |
| 10b.2c.33b | bolt | 18 | this is a bolt |
| 11a.1c.3b | bolt | 1 | this is a bolt |
| 1a.2c.33b | bolt | 45 | this is a bolt |
| 1b.2c.33 | bolt | 12 | this is a bolt |
| 1c.2c | bolt | 5 | this is a bolt |
| 1z.10c.3b | bolt | 2 | this is a bolt |
TABLE 02 Split all the values to get only numbers and calculate a supertotal
| split01 | split02 | split03 | Number | Letter | Number | Letter | Number | Letter | Supertotal |
| 10b | 2c | 33b | 10 | 2 | 2 | 3 | 33 | 2 | 1,1003003004034E+19 |
| 11a | 1c | 3b | 11 | 1 | 1 | 3 | 3 | 2 | 1,2002002004004E+19 |
| 1a | 2c | 33b | 1 | 1 | 2 | 3 | 33 | 2 | 2,002003004034E+18 |
| 1b | 2c | 33 | 1 | 2 | 2 | 3 | 33 | 0 | 2,003003004034E+18 |
| 1c | 2c | 1 | 3 | 2 | 3 | 0 | 0 | 2,004003004001E+18 | |
| 1z | 10c | 3b | 1 | 26 | 10 | 3 | 3 | 2 | 2,027011004004E+18 |
TABLE 03 sorted after all was converted to number to make a supertotal
| Index | item | quantity | description | Supertotal verification (not in DS) |
| 1a.2c.33b | bolt | 45 | this is a bolt | 2,002003004034E+18 |
| 1b.2c.33 | bolt | 12 | this is a bolt | 2,003003004034E+18 |
| 1c.2c | bolt | 5 | this is a bolt | 2,004003004001E+18 |
| 1z.10c.3b | bolt | 2 | this is a bolt | 2,027011004004E+18 |
| 10b.2c.33b | bolt | 18 | this is a bolt | 1,1003003004034E+19 |
| 11a.1c.3b | bolt | 1 | this is a bolt | 1,2002002004004E+19 |
-------------------------------- bubblesort.vbs -------------------
'=== bubblesort_wildboy85.vbs
'=== requirement: wscript.exe
'=== very fast bubble sort
'=== by wildboy85 (sergefournier @ hotmail.com)
'=== objects needed
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objshe = CreateObject("WScript.Shell")
Set objNet = CreateObject("WScript.Network")
'=== register base constants
Const hkcr = &H80000000 'HKEY_CLASSES_ROOT
Const HKCU = &H80000001 'HKEY_CURRENT_USER
Const hklm = &H80000002 'HKEY_LOCAL_MACHINE
Const hku = &H80000003 'HKEY_USERS
Const hkcc = &H80000005 'HKEY_CURRENT_CONFIG
Const ForReading = 1
Const adVarChar = 200
Const MaxCharacters = 255
Const adDouble = 5
'=== actual drive, actual directory, and "\"
thepath = WScript.ScriptFullName
p = instrRev(thepath,"\")
basedir = left(thepath,p)
filnam = right(thepath,len(thepath)-p)
'=== windows dir
WinDir = objfso.GetSpecialFolder(0)
'=== restart the script in 32 bits if we are on a 64 bits system
'=== some databases drivers are not yet available in 64 bits
a64 = windir & "\syswow64\wscript.exe"
if objFSO.fileEXISTS(a64) and instr(lcase(wscript.fullname),"syswow64")=0 then
'=== 64 bits system
argchain01 = ""
set args01 = Wscript.Arguments
if args01.count<>0 then
'=== when recalling this script in 32 bits, pass all the parameters
For Each arg01 in args01
argchain01 = argchain01 & " " & arg01
Next
end if
a = """" & a64 & """ """ & basedir & filnam & """" & argchain01
objshe.Run a,0, false
wscript.quit
end if
'=== log what we do, in same directory as the script
logall = 1
htmlout = 1
if logall=1 then
'=== debug log, get this file name, remove end, change start for ZZZ
'=== remove .vbs
path01 = left(thepath,len(thepath)-(len(thepath)-instr(thepath,".")+1))
name01 = right(path01,len(path01)-instrrev(path01,"\"))
logname01 = "zzz_" & name01 & ".txt"
'=== always have a nice error trapping
err01 = 0 : err02 = ""
on error resume next
Set File02 = objFSo.OpenTextFile(logname01, 2, true)
err01 = err : err02 = err.description
on error goto 0
end if
if err01 = 0 then
logall = 1
else
'=== flag to tell our program to not write in log file if there is not log file open
logall = 0 '=== could not open logfile, no log
end if
'=== html output file, we want a nice table as output
if htmlout=1 then
path01 = left(thepath,len(thepath)-(len(thepath)-instr(thepath,".")+1))
name01 = right(path01,len(path01)-instrrev(path01,"\"))
logname01 = name01 & "_output.html"
'=== always have a nice error trapping
err01 = 0 : err02 = ""
on error resume next
Set File03 = objFSo.OpenTextFile(logname01, 2, true)
err01 = err : err02 = err.description
on error goto 0
end if
if err01 = 0 then
htmlout = 1
else
'=== flag to tell our program to not write in log file if there is not log file open
htmlout = 0 '=== could not open logfile, no log
end if
if logall = 1 then file02.WriteLine date & " " & time & " START Bubble sort wildboy85"
'=== the dataset could come from a database, but here we will add data ourseleves as an example
set dataset01 = CreateObject("ADODB.Recordset")
dataset01.Fields.Append "Index", adVarChar, MaxCharacters
if logall = 1 then file02.WriteLine date & " " & time & " First field appended"
dataset01.Fields.Append "item", adVarChar, MaxCharacters
dataset01.Fields.Append "quantity", adDouble
dataset01.Fields.Append "description", adVarChar, MaxCharacters
'dataset01.Fields.Append "supertotal DS", adVarChar, MaxCharacters
dataset01.Open
'=== accessing the column by name is slow, it would be faster by name if we have big loop
dataset01.AddNew
dataset01("Index") = "1a.1c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 1
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1z.10c.3b"
dataset01("item") = "bolt"
dataset01("quantity") = 2
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1a.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 45
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1b.2c.33"
dataset01("item") = "bolt"
dataset01("quantity") = 12
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "1c.2c"
dataset01("item") = "bolt"
dataset01("quantity") =5
dataset01("description") = "this is a bolt"
dataset01.Update
dataset01.AddNew
dataset01("Index") = "10b.2c.33b"
dataset01("item") = "bolt"
dataset01("quantity") = 18
dataset01("description") = "this is a bolt"
dataset01.Update
'=== how many field? (columns)
fieldscount01 = dataset01.fields.count
if logall = 1 then file02.WriteLine date & " " & time & " fieldcount: " & fieldscount01
if htmlout = 1 then file03.WriteLine date & " " & time & " Start of bubble sort example<br><br>"
'=== this datasort will order our items badly, as if number 10 was smaller than number 1
dataset01.Sort = "index"
'=== html table
if htmlout = 1 then file03.WriteLine " TABLE 01 Dataset was sorted with dataset.sort by index field, alphanumeric sorting<br>"
if htmlout = 1 then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
if htmlout = 1 then file03.WriteLine "<tr>"
for i = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & dataset01.fields(i).name & "</td>"
next
if htmlout = 1 then file03.WriteLine "</tr>"
dataset01.movefirst
linecount = 0
do while not (dataset01.eof) '=== magica loop, row
redim preserve ara02(fieldscount01-1, linecount)
if htmlout = 1 then file03.WriteLine "<tr>"
'=== put all values in an array, because a dataset this basic cannot switch a line of data
for i = 0 to fieldscount01-1
value01 = dataset01.fields(i).value
If IsNull(value01) then value01=""
value01 = Trim(value01)
if htmlout = 1 then file03.WriteLine "<td>" & value01 & "</td>"
ara02(i, linecount) = value01
next
if htmlout = 1 then file03.WriteLine "</tr>"
linecount = linecount + 1
dataset01.movenext
loop
if htmlout = 1 then file03.WriteLine "</table><br>"
'=== now we will split the line we will use to do the sort by number and letter (converted to numbers)
fieldtosort01 = 0 '=== field 0 contain what we want to sort, example: 1a.2b.3c
'=== we assume we will have only 3 data to sort, 1a 2b 3c
dataset01.movefirst
if htmlout = 1 then file03.WriteLine "<br>TABLE 02 Split all the values to get only numbers and calculate a supertotal"
if htmlout = 1 then file03.WriteLine "<table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
if htmlout = 1 then file03.WriteLine "<tr><td>split01</td><td>split02</td><td>split03</td>"
if htmlout = 1 then file03.WriteLine "<td>Number</td><td>Letter</td><td>Number</td><td>Letter</td><td>Number</td><td>Letter</td>"
if htmlout = 1 then file03.WriteLine "<td>Supertotal</td></tr>"
'=== split the chain 1a.2b.3c by adding dots in case 2b.3c does not exist
dim split(2)
linecount = 0
do while not (dataset01.eof) '=== magica loop, row
redim preserve ara01(6,linecount) '=== the last dimension can be redimensionned so we reversed the dimensions
if htmlout = 1 then file03.WriteLine "<tr>" '=== html line change
value01 = dataset01.fields(fieldtosort01).value
'=== clean value in case of dbnull (dbnull cannot be processed)
If IsNull(value01) then value01=""
value01 = Trim(value01)
'=== add dots at the end of string to later split at the dots and get string inbetween
'=== so if the string have no dots, the split will still work and resturn empty string instead of trying to trap errors
value01 = value01 & "...."
split(0) = left(value01,len(value01)-(len(value01)-instr(value01,".")+1))
leftover = mid(value01,instr(value01,".")+1,len(value01))
split(1)= left(leftover,len(leftover)-(len(leftover)-instr(leftover,".")+1))
leftover = mid(leftover,instr(leftover,".")+1,len(leftover))
split(2) = left(leftover,len(leftover)-(len(leftover)-instr(leftover,".")+1))
if htmlout = 1 then file03.WriteLine "<td>" & split(0) & "</td><td>" & split(1) & "</td><td>" & split(2) & "</td>"
'=== we now do an array of 6 fields/columns
'=== field0 is 1, field1 is a, field2 is 2, field3 is b, field4 is 3, field5 is c (from 1a.2b.3c)
splitcount = 0
for x03 = 0 to 5 step 2 'order01, order01 letter, order02, order02 letter, order03, order03 letter (letter --> integer)
'=== split the split to get 2 value, 1 for number, 1 for alpha (split the chain "1a", "2b", "3c")
data01 = trim(split(splitcount))
len01 = len(data01)
if len01>1 then
alpha01 = 0
for i = 1 to len01
'=== is this digit alpha?
asc01 = ASC(mid(data01,i,1))
if (asc01>64 and ASC01<91) or (asc01 >96 and asc01 <123) then
alpha01 = 1
'=== this is a letter
if i>1 then
val01 = cint(left(data01,i-1))
else
end if
'=== code de classement final
'if logall=1 then file02.WriteLine "value before: " & data01 & " After: " & val01 & " + " & asc01 & chr(9) & val01+asc01
if asc01>96 then '=== uppercase A
asc01 = asc01 - 96
elseif asc01>64 then '=== lowercase a
asc01 = asc01 - 64
end if
ara01(x03,linecount) = val01 '=== new ordering integer for this column
'=== create a new column to class by letter after this one
ara01(x03+1,linecount) = asc01
end if
next
if alpha01 = 0 then
'=== remove the 0 before
ara01(x03,linecount) = cint(data01)
ara01(x03+1,linecount) = 0
end if
elseif len01>0 then
'=== only 1 digit, so we assume it's a number (just to be faster)
ara01(x03,linecount) = data01 '=== new ordering integer for this column
'=== column to class by letter after this one (1 digit = no letter = 0)
ara01(x03+1,linecount) = 0
else
'=== len is 0, we do nothing
ara01(x03,linecount) = 0
ara01(x03+1,linecount) = 0
end if
splitcount = splitcount + 1
'=== results, all numerics
if htmlout = 1 then file03.WriteLine "<td>" & ara01(x03,linecount) & "</td><td>" & ara01(x03+1,linecount) & "</td>"
next
'=== tricky part, doing a supertotal to sort only one number for all six fields/columns
supertotal = 0
mul01 = 5
super02 = ""
for x03 = 0 to 5
'=== do a super total for this line and the next for comparaison
super01 = (ara01(x03,linecount)+1)*(1000^(mul01+1))
supertotal = supertotal + super01
mul01 = mul01 - 1
'==== alphanumeric supertotal add 0 in front pf every digit, not working with dataset.sort either
'super02 = super02 & left("00000", 5-len((ara01(x03,linecount)+1))) & ara01(x03,linecount)+1
next
'dataset01.fields("supertotal DS").value = super02
ara01(6,linecount) = supertotal
'dataset01.fields("supertotal DS").value = supertotal
if htmlout = 1 then file03.WriteLine "<td>" & ara01(6,linecount) & "</td>"
linecount = linecount + 1
if htmlout = 1 then file03.WriteLine "</tr>"
dataset01.movenext
'=== all supertotal must be computed before we can sort
'=== so this loop was to split numbers, letters and compute a supertotal with multiplicated by 1000^positioninfieldvalue(0-5) values
loop
if htmlout = 1 then file03.WriteLine "</table>"
'=== sorting loop, using supertotal
'=== we move through dataset at the same time we move in the array that contain supertotal at position 6
if logall = 1 then file02.WriteLine date & " " & time & " START bubble sort with supertotal"
'=== using SORT command from dataset would have been easy now, but noooo, error argument, number to weird
'=== even if this a string, it cannot sort with dateset.sort command
'dataset01.Sort = "supertotal DS"
dataset01.movefirst
'set row = CreateObject("ADODB.Recordsetrow")
'=== exchange lines in dataset, is that possible?
'dataset01.rows(1) = dataset01.rows(0)
'http://www.w3schools.com/asp/ado_ref_recordset.asp
''''''''''''''''''''''''''''''''''''''''''
' bubble sort
''''''''''''''''''''''''''''''''''''''''''
dim supertotal01(1)
redim ara01tempo(linecount-1)
if logall=1 then file02.WriteLine date & " " & time & " Bubble order START"
'=== classement bulle, ligne par ligne, 6 fois
for bubble01 = linecount-2 to 0 step -1
for y03 = 0 to bubble01 '=== magica loop, row
'=== if supertotal after is smaller, we exchange them
if ara01(6,y03+1) < ara01(6,y03) then
for x04 = 0 to fieldscount01-1
'=== save actual matrix x elements
ara01tempo(x04) = ara02(x04,y03)
ara02(x04,y03) = ara02(x04,y03+1) '=== go up one row in matrix
ara02(x04,y03+1) = ara01tempo(x04)
next
'=== exchange supertotal also
supertotaltemp = ara01(6,y03)
ara01(6,y03) = ara01(6,y03+1)
ara01(6,y03+1) = supertotaltemp
end if
next
next
if logall = 1 then file02.WriteLine date & " " & time & " END bubble sort"
'=== final result
if htmlout = 1 then file03.WriteLine "<br>TABLE 03 sorted after all was converted to number to make a supertotal"
if htmlout = 1 then file03.WriteLine "<br><table width=""70%"" BORDERCOLOR=""black"" class=MsoTableGrid border=1 CELLSPACING=0 cellpadding=2 style='border-collapse:collapse;border:none'>"
'=== fields names as columns titles of the table
if htmlout = 1 then file03.WriteLine "<tr>"
for i = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & dataset01.fields(i).name & "</td>"
next
if htmlout = 1 then file03.WriteLine "<td>Supertotal verification (not in DS)</td>"
if htmlout = 1 then file03.WriteLine "</tr>"
for y = 0 to linecount-1 '=== display results after bubble sort
if htmlout = 1 then file03.WriteLine "<tr>"
for x = 0 to fieldscount01-1
if htmlout = 1 then file03.WriteLine "<td>" & ara02(x,y) & "</td>"
next
if htmlout = 1 then file03.WriteLine "<td>" & ara01(6,y) & "</td>"
if htmlout = 1 then file03.WriteLine "</tr>"
next
if htmlout = 1 then file03.WriteLine "</table>"
if logall = 1 then file02.WriteLine ""
if logall = 1 then file02.WriteLine date & " " & time & " END"
if logall = 1 then file02.close
if htmlout = 1 then file03.close
Sunday, January 18, 2015
PHP basic page - learning to program php - lesson 01
Hello,
I started to program some php to make a web site with a database
To do so you need to install 2 programs.
For apache server and my sql server:
wampserver2.5-Apache-2.4.9-Mysql-5.6.17-php5.5.12-64b
To edit your mysql tables:
SQLyog-11.5.1-0.x64Community
Also, the web server will not start if SKYPE is open and use port 80
(or anything that use port 80 for that matter)
Installing WAMP will create in your C drive a folder like this: C:\wamp
Inside it, a web folder: C:\wamp\www
index.php is the starting file for the server
You should add a sub folder for a project you make like this:
C:\wamp\www\project02
Then in this folder create a index.php file with notepad2 (for the colors)
Inside index.php you can have a HTML part:
------------------------- index.php file start ----------------------------
<!DOCTYPE html>
<html:html>
<html:body>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><div align="center">
<title>my web site</title>
</head>
</html:body>
</html:html>
<html>
<body>
------------ comments start -------------
Now, since we are not newbs, we want to use inner html to build our page dynamically
So we insert a DIV zone called "zoneright"
Then a java script that will use inner html to update the content of that zone
Then a OD_flush et and flush to refresh the content (empty any buffer that was sent to your browser and apply change) without reloading the page
------------ comments end -------------
<?php
// $h is a variable to put html content
$h= "Nice web page that update dynamically";
// create a DIV zone with a name to use innrehtml later
$h.="<div id='zoneright'></div>";
// push the html content in the browser
echo $h;
// dynamic data to insert into the inner html zone "zoneright"
$h2 = "test 44";
// send a java script to the browser that will update the innerhtml zone for us
// syntax tip: before $h2 is a double quote " then a single quote '
// syntax tip: after $h2 is a single quote ' then a double quote "
echo '<script type="text/javascript">
document.getElementById("zoneright").innerHTML = "'.$h2.'";
</script>';
// empty all browser buffers to update the web page right now
ob_flush();
flush();
// wait 2 sec
sleep (2);
// new content
$h2 = "test 45";
// send another script to change inner html content for the new data in $h2
echo '<script type="text/javascript">
document.getElementById("zoneright").innerHTML = "'.$h2.'";
</script>';
// force brower to update right now again
ob_flush();
flush();
?>
-------------------- index.php file end ---------------------------
Now, save the index.php file
And load the local web site in your browser
http://localhost/project02
Now you learned to do some php, innerhtml and force a refresh on a page
Next, we will learn to use buttons to update the page content
I started to program some php to make a web site with a database
To do so you need to install 2 programs.
For apache server and my sql server:
wampserver2.5-Apache-2.4.9-Mysql-5.6.17-php5.5.12-64b
To edit your mysql tables:
SQLyog-11.5.1-0.x64Community
Also, the web server will not start if SKYPE is open and use port 80
(or anything that use port 80 for that matter)
Installing WAMP will create in your C drive a folder like this: C:\wamp
Inside it, a web folder: C:\wamp\www
index.php is the starting file for the server
You should add a sub folder for a project you make like this:
C:\wamp\www\project02
Then in this folder create a index.php file with notepad2 (for the colors)
Inside index.php you can have a HTML part:
------------------------- index.php file start ----------------------------
<!DOCTYPE html>
<html:html>
<html:body>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><div align="center">
<title>my web site</title>
</head>
</html:body>
</html:html>
<html>
<body>
------------ comments start -------------
Now, since we are not newbs, we want to use inner html to build our page dynamically
So we insert a DIV zone called "zoneright"
Then a java script that will use inner html to update the content of that zone
Then a OD_flush et and flush to refresh the content (empty any buffer that was sent to your browser and apply change) without reloading the page
------------ comments end -------------
<?php
// $h is a variable to put html content
$h= "Nice web page that update dynamically";
// create a DIV zone with a name to use innrehtml later
$h.="<div id='zoneright'></div>";
// push the html content in the browser
echo $h;
// dynamic data to insert into the inner html zone "zoneright"
$h2 = "test 44";
// send a java script to the browser that will update the innerhtml zone for us
// syntax tip: before $h2 is a double quote " then a single quote '
// syntax tip: after $h2 is a single quote ' then a double quote "
echo '<script type="text/javascript">
document.getElementById("zoneright").innerHTML = "'.$h2.'";
</script>';
// empty all browser buffers to update the web page right now
ob_flush();
flush();
// wait 2 sec
sleep (2);
// new content
$h2 = "test 45";
// send another script to change inner html content for the new data in $h2
echo '<script type="text/javascript">
document.getElementById("zoneright").innerHTML = "'.$h2.'";
</script>';
// force brower to update right now again
ob_flush();
flush();
?>
-------------------- index.php file end ---------------------------
Now, save the index.php file
And load the local web site in your browser
http://localhost/project02
Now you learned to do some php, innerhtml and force a refresh on a page
Next, we will learn to use buttons to update the page content
Thursday, December 18, 2014
sub et function
Les subs (mot clef: SUB) et les functions (mot clef: FUNCTION)
La différence entre un sub et une function
Le sub (sous programme) ne retourne pas de valeur
La function (fonction) retourne une valeur, un ensemble de valeur identique (array) ou une structure de valeurs
À l'intérieur de la function, cette valeur est contenue dans une variable qui a le même nom que la function
Quand la valeur est retournée au sub qui l'a appelée, elle est injectée dans la variable qui a servi à appeler la function
Exemple:
sub programmeprincipal
'=== ici on apelle la function "calculatethis" en lui envoyant deux valeur à calculer
valeur01 = calculatethis(5,20)
msgbox(valeur01)
end sub
function calculatethis(valeur10, valeur20)
'=== calculatethis est la valeur retournée au sub car elle a le même nom que la function
calculatethis = valeur10 + valeur20
end function
Le programme principal est toujours un sub (de préférence)
Le sub apparait dans la liste des programme macros à exécuter si dans excel, on appuie ALT F8
Le function n'apparait pas, car ce sont les sub qui utilisent les functions, pas l'utilisateur humain
Donc faire des function pour tous les traitements à faire que l'usager n'a pas besoin de faire directement est une bonne méthode pour garder le menu ALT F8 "propre" (dans excel vba)
Excel traite séquentiellement les instructions du sub, et quand il atteint end sub, il a terminé
Il ne va pas traiter la function, parce qu'il sait que celle-ci sera appelé par le sub
Les instruments de base de la programmation sont bien sûr les variables (algèbre), mais aussi variables en tableau (array) et les boucles
Le tableau 1 dimension est un groupe de variables séquentiel
Exemple:
sub programmeprincipal
'=== array01 est un groupe de valeurs qui contient deux valeurs
array01 = array(5, 20)
valeur01 = calculatethis(array01)
msgbox(valeur01)
end sub
function calculatethis(array10)
for each element01 in array10
calculatethis= calculatethis+ element01
next
end function
"For each" va boucler jusqu'à ce que chaque valeur dans le tableau ait été traitée
Chaque valeur sera déposée dans "element01" à chaque itération de la boucle
La boucle se répétera tant qu'il y a des valeurs dans "array10"
La différence entre un sub et une function
Le sub (sous programme) ne retourne pas de valeur
La function (fonction) retourne une valeur, un ensemble de valeur identique (array) ou une structure de valeurs
À l'intérieur de la function, cette valeur est contenue dans une variable qui a le même nom que la function
Quand la valeur est retournée au sub qui l'a appelée, elle est injectée dans la variable qui a servi à appeler la function
Exemple:
sub programmeprincipal
'=== ici on apelle la function "calculatethis" en lui envoyant deux valeur à calculer
valeur01 = calculatethis(5,20)
msgbox(valeur01)
end sub
function calculatethis(valeur10, valeur20)
'=== calculatethis est la valeur retournée au sub car elle a le même nom que la function
calculatethis = valeur10 + valeur20
end function
Le programme principal est toujours un sub (de préférence)
Le sub apparait dans la liste des programme macros à exécuter si dans excel, on appuie ALT F8
Le function n'apparait pas, car ce sont les sub qui utilisent les functions, pas l'utilisateur humain
Donc faire des function pour tous les traitements à faire que l'usager n'a pas besoin de faire directement est une bonne méthode pour garder le menu ALT F8 "propre" (dans excel vba)
Excel traite séquentiellement les instructions du sub, et quand il atteint end sub, il a terminé
Il ne va pas traiter la function, parce qu'il sait que celle-ci sera appelé par le sub
Les instruments de base de la programmation sont bien sûr les variables (algèbre), mais aussi variables en tableau (array) et les boucles
Le tableau 1 dimension est un groupe de variables séquentiel
Exemple:
sub programmeprincipal
'=== array01 est un groupe de valeurs qui contient deux valeurs
array01 = array(5, 20)
valeur01 = calculatethis(array01)
msgbox(valeur01)
end sub
function calculatethis(array10)
for each element01 in array10
calculatethis= calculatethis+ element01
next
end function
"For each" va boucler jusqu'à ce que chaque valeur dans le tableau ait été traitée
Chaque valeur sera déposée dans "element01" à chaque itération de la boucle
La boucle se répétera tant qu'il y a des valeurs dans "array10"
Sunday, December 14, 2014
Programmation, les variables d'algèbre
Maintenant que l'on sait comment les chiffres sont représentés dans un ordinateur, il faut les emmagasiner dans la mémoire et les utiliser afin de faire des calculs
Pour cela on va commencer par programmer du VBA (visual basic for application) dans excel
Pourquoi dans excel?
Programmer hors de excel nous ferais perdre beaucoup de temps à faire un HMI (homme to machine interface)
Ainsi, notre code en VBS (script windows éditable dans notepad) serait bien beau et gentil, mais afficherais seulement une boîte grise avec nos résultats de calculs dedans
Avec excel, on peut afficher des résultats dans la feuille au lieu d'une simple boîte grise laide
Ouvrez Excel, créez une nouvelle feuille (.XLS ou .XLSM) et appuyez ALT F11 afin d'entrer dans l'éditeur de VBA
Il est important que votre feuille excel soit de type XLSM, car le M veut dire macro, et les macros sont programmées en VBA
Ainsi, un fichier .XLSX ne contient aucun code de macro, ce qui le rend moins vulnérable aux virus qui peuvent se cacher dans une macro
Donc: nouveau fichier excel, XLS ou XLSM, ALT F11 pour ouvrir l'éditeur de macros
Excel emmagasine ses macro dans plusieurs endroits
Dans la feuille ou dans un module
Si une macro est dans la feuille, elle peut être reliée à une événement, comme l'ouverture de la feuille, la sauvegarde, la fermeture, etc.
Pour le moment on veut juste créer un nouveau module et on l'exécutera manuellement avec ALT F8 (fenêtre d'exécution des macros)
Nouveau classeur, sauvegardé en XLSM
ALT F11, insérer un nouveau module
Code à taper dans la fenêtre de droite:
Les bases du code
Il existe des SUB, ou sous programmes et des FUNCTION, ou fonctions
On doit en indiquer le début et la fin par des mots clef, tel que SUB ou FUNCTION, END SUB et END FUNCTION
Chaque sub ou function a un nom unique, ici on l'a appelé TEST
Mettre des tabulations devant votre code est très important (pour la clarté)
Ainsi, le premier niveau de tabulation indique qu'on est dans une fonction ou un sous programme
(il existe des commandes en dehors de sub on function)
Une variable est souvent utilisé pour contenir un résultat de calcul ou un message
Ici, on a déposé dans la variable MES01 (message numéro 01), le contenu "salut"
Ensuite on utilise la commande MSGBOX (message box) pour afficher le message
Les variables, ont les a apprises à l'école primaire:
Si a est égal à 5 et b est égal à 12, combien fait a + b? disait le professeur
En VBA, ce professeur serait remplacé par:
----- début code VBA -----
sub professeur
a = 5
b = 12
msgbox(a+b)
end sub
----- fin code -----
Faisons rouler notre premier programme
Sélectionnez le code du sub professeur:
Faites CTRL C
Allez dans le fenêtre du module1 de excel (ALT F11)
et faites coller (CTRL V)
Appuyez ensuite le bouton play (icône vert en forme de bouton play)
Si vous ne trouvez pas le boutons play, cela signifie que les zombies ont attaqué et que vous êtes présentement la génération d'humains suivante, vous habitez sans doute dans une caverne et vous n'avez jamais vu à quoi ressemble un bouton play... Cette formation ne s'adresse pas à vous, car la batterie du portable que vous avez trouvé dans une ruine sera bientôt épuisée. Allez chasser pour vous nourrir et ne faite pas de bruits pour ne pas attirer les zombies...
Résultat après avoir appuyé PLAY:
Alors, on se rappelle son algèbre de troisième année d'école maintenant?
Pour cela on va commencer par programmer du VBA (visual basic for application) dans excel
Pourquoi dans excel?
Programmer hors de excel nous ferais perdre beaucoup de temps à faire un HMI (homme to machine interface)
Ainsi, notre code en VBS (script windows éditable dans notepad) serait bien beau et gentil, mais afficherais seulement une boîte grise avec nos résultats de calculs dedans
Avec excel, on peut afficher des résultats dans la feuille au lieu d'une simple boîte grise laide
Ouvrez Excel, créez une nouvelle feuille (.XLS ou .XLSM) et appuyez ALT F11 afin d'entrer dans l'éditeur de VBA
Il est important que votre feuille excel soit de type XLSM, car le M veut dire macro, et les macros sont programmées en VBA
Ainsi, un fichier .XLSX ne contient aucun code de macro, ce qui le rend moins vulnérable aux virus qui peuvent se cacher dans une macro
Donc: nouveau fichier excel, XLS ou XLSM, ALT F11 pour ouvrir l'éditeur de macros
Excel emmagasine ses macro dans plusieurs endroits
Dans la feuille ou dans un module
Si une macro est dans la feuille, elle peut être reliée à une événement, comme l'ouverture de la feuille, la sauvegarde, la fermeture, etc.
Pour le moment on veut juste créer un nouveau module et on l'exécutera manuellement avec ALT F8 (fenêtre d'exécution des macros)
Nouveau classeur, sauvegardé en XLSM
ALT F11, insérer un nouveau module
Code à taper dans la fenêtre de droite:
Les bases du code
Il existe des SUB, ou sous programmes et des FUNCTION, ou fonctions
On doit en indiquer le début et la fin par des mots clef, tel que SUB ou FUNCTION, END SUB et END FUNCTION
Chaque sub ou function a un nom unique, ici on l'a appelé TEST
Mettre des tabulations devant votre code est très important (pour la clarté)
Ainsi, le premier niveau de tabulation indique qu'on est dans une fonction ou un sous programme
(il existe des commandes en dehors de sub on function)
Une variable est souvent utilisé pour contenir un résultat de calcul ou un message
Ici, on a déposé dans la variable MES01 (message numéro 01), le contenu "salut"
Ensuite on utilise la commande MSGBOX (message box) pour afficher le message
Les variables, ont les a apprises à l'école primaire:
Si a est égal à 5 et b est égal à 12, combien fait a + b? disait le professeur
En VBA, ce professeur serait remplacé par:
----- début code VBA -----
sub professeur
a = 5
b = 12
msgbox(a+b)
end sub
----- fin code -----
Faisons rouler notre premier programme
Sélectionnez le code du sub professeur:
Allez dans le fenêtre du module1 de excel (ALT F11)
et faites coller (CTRL V)
Appuyez ensuite le bouton play (icône vert en forme de bouton play)
Si vous ne trouvez pas le boutons play, cela signifie que les zombies ont attaqué et que vous êtes présentement la génération d'humains suivante, vous habitez sans doute dans une caverne et vous n'avez jamais vu à quoi ressemble un bouton play... Cette formation ne s'adresse pas à vous, car la batterie du portable que vous avez trouvé dans une ruine sera bientôt épuisée. Allez chasser pour vous nourrir et ne faite pas de bruits pour ne pas attirer les zombies...
Résultat après avoir appuyé PLAY:
Alors, on se rappelle son algèbre de troisième année d'école maintenant?
Tuesday, December 9, 2014
Programmation, le binaire, la base 2, les octets et la base 16 (hexadécimale)
Un de mes amis est en train d'apprendre à programmer alors voici un petit cours
Une mémoire d'ordinateur transistors dans lesquels le courant passe ou ne passe pas
On appelle cette mémoire des BITS
Un bit peut contenir 0 (le courant ne passe pas) ou 1 (le courant passe)
Chaque bit contient la possibilité mathématique qu'il représente
1 bit peut contenir 2 possibilités: 0 ou 1
2 bits peuvent contenir 4 possibilités:
00 = possibilité 0
01 = possibilité 1
10 = possibilité 2
11 = possibilité 3
3 bits peuvent contenir 8 possibilités:
000 = 0
001 = 1
010 = 2
011 = 3
100 = 4
101 = 5
110 = 6
111 = 7
On réalise rapidement que:
le bit 0 représente les unités (multiple de 1)
le bit 1 représente les 2aines (multiple de 2)
le bit 3 représente les 4 aines (multiples de 4)
Exemple:
bits:
2----------1----------0
4aines----2aines----unités
101 = 1 x 4 + 0 x 2 + 1 x 1 = chiffre 5
Calcul binaire avec les unités de 2:
8 bits ensembles sont un octet (byte en anglais)
Un ordinateur qui a un processeur 8 bits va grouper ses bits par 8 (et aussi souvent, les transmettre 8 à la fois vers la carte vidéo, la mémoire ou le disque dur)
Pour calculer plus rapidement un octet on utilise la base 16, aussi appelé base hexadécimale
On divise l'octet en 2 parties:
16aines - unités
0000 0000
On peut donc avoir 0 à 15 unités et 0 à 15 16aines
Donc chaque groupe de 0000 (quatre bits) est un chiffre de 0 à 15
Sur 4 bits, il y a 16 agencements possibles (on compte le 0)
Voici leur représentation binaire, décimale, et hexadécimale:
(note: devant un chiffre hexadécimal, on met un signe de dollar $ pour les différencier des décimales)
0000 = 0 = $0
0001 = 1 = $1
0010 = 2 = $2
0011 = 3 = $3
0100 = 4 = $4
0101 = 5 = $5
0110 = 6 = $6
0111 = 7 = $7
1000 = 8 = $8
1001 = 9 = $9
1010 = 10 = $A
1011 = 11 = $B
1100 = 12 = $C
1101 = 13 = $D
1110 = 14 = $E
1111 = 15 = $F
Donc si on calcule un peux en base 16 (hexadécimale):
0011 0100 = $34 le 3 représente les 16aines et le 4 les unités
Donc, 3 x 16 (3 16aines) + 4 (unités) = 52
Dans 1 octet (8bits) on peut mettre une valeur de 0 à 255 et pour plus de facilité la représenter en hexadécimal:
1111 1111 = 255 = $FF
0111 1111 = 254 = $7F
...
0000 0011 = 3 = $03
0000 0010 = 2 = $02
0000 0001 = 1 = $01
0000 0000 = 0 = $00
Soit 256 valeurs différentes si on compte le 0
Utilité pratique du calcul binaire et hexadécimal
Chaque bit peu représenter un pixel sur un écran graphique
Avec 8 octets, agencés verticalement dans la mémoire qui est représentée à l'écran, on a dessiné les premier caractères:
00111100 = $3C
01000010 = $42
01000010 = $42
01111110 = $7E
01000010 = $42
01000010 = $42
01000010 = $42
00000000 = $00
(on utilise même plus la base décimale, car elle est maintenant démodée)
Si on regarde bien cela représente un A, avec une ligne de pixels vide en base pour laisse un espacement entre chaque ligne dans l'écran
Peu à peu les lettre ont évolués et ont été incluse dans la mémoire du BIOS déjà toutes dessinées (on a pas à les dessiner chaque fois)
Ainsi, le code 65, qu'on appelle code ASCII, représente un A majuscule
66 un B, etc. jusqu'à Z
32, un espace
8 une tabulation
13 un retour de chariot
10 un changement de ligne
92 un backslash --> \
Si on va plus loin dans la base 2 on peut grouper les octets au lieu de grouper les bits
Donc on fait un groupe de deux octets
0000 0000 0000 0000 ou $0000 en hexa
L'octet de droite est celui qu'on appelle de poids faible, car il contient les unités
L'octet de gauche, celui de poids fort, car il contient les 256 aines
Ainsi,en hexadécimal (base 16), $0101 représente 1 x 256 + 1 = 257
Le groupement de 2 octets est un integer
Si on fais la calcul rapidement, un integer (16 bits, 2 octets) va pouvoir contenir 255 x 256 + 255 x 1, soit un chiffre de 0 à 65535
Un groupement de 4 octets, $00000000 est un long integer
Maintenant on sais compter en base 2, et en base 16, représentée en hexadécimal pour plus de facilité de lecture :)
Vous pouvez maintenant comprendre cette phrase:
Comprendre le binaire vous aidera aussi à comprendre les réseaux d'adresses IP
Une mémoire d'ordinateur transistors dans lesquels le courant passe ou ne passe pas
On appelle cette mémoire des BITS
Un bit peut contenir 0 (le courant ne passe pas) ou 1 (le courant passe)
Chaque bit contient la possibilité mathématique qu'il représente
1 bit peut contenir 2 possibilités: 0 ou 1
2 bits peuvent contenir 4 possibilités:
00 = possibilité 0
01 = possibilité 1
10 = possibilité 2
11 = possibilité 3
3 bits peuvent contenir 8 possibilités:
000 = 0
001 = 1
010 = 2
011 = 3
100 = 4
101 = 5
110 = 6
111 = 7
On réalise rapidement que:
le bit 0 représente les unités (multiple de 1)
le bit 1 représente les 2aines (multiple de 2)
le bit 3 représente les 4 aines (multiples de 4)
Exemple:
bits:
2----------1----------0
4aines----2aines----unités
101 = 1 x 4 + 0 x 2 + 1 x 1 = chiffre 5
Calcul binaire avec les unités de 2:
8 bits ensembles sont un octet (byte en anglais)
Un ordinateur qui a un processeur 8 bits va grouper ses bits par 8 (et aussi souvent, les transmettre 8 à la fois vers la carte vidéo, la mémoire ou le disque dur)
Pour calculer plus rapidement un octet on utilise la base 16, aussi appelé base hexadécimale
On divise l'octet en 2 parties:
16aines - unités
0000 0000
On peut donc avoir 0 à 15 unités et 0 à 15 16aines
Donc chaque groupe de 0000 (quatre bits) est un chiffre de 0 à 15
Sur 4 bits, il y a 16 agencements possibles (on compte le 0)
Voici leur représentation binaire, décimale, et hexadécimale:
(note: devant un chiffre hexadécimal, on met un signe de dollar $ pour les différencier des décimales)
0000 = 0 = $0
0001 = 1 = $1
0010 = 2 = $2
0011 = 3 = $3
0100 = 4 = $4
0101 = 5 = $5
0110 = 6 = $6
0111 = 7 = $7
1000 = 8 = $8
1001 = 9 = $9
1010 = 10 = $A
1011 = 11 = $B
1100 = 12 = $C
1101 = 13 = $D
1110 = 14 = $E
1111 = 15 = $F
Donc si on calcule un peux en base 16 (hexadécimale):
0011 0100 = $34 le 3 représente les 16aines et le 4 les unités
Donc, 3 x 16 (3 16aines) + 4 (unités) = 52
Dans 1 octet (8bits) on peut mettre une valeur de 0 à 255 et pour plus de facilité la représenter en hexadécimal:
1111 1111 = 255 = $FF
0111 1111 = 254 = $7F
...
0000 0011 = 3 = $03
0000 0010 = 2 = $02
0000 0001 = 1 = $01
0000 0000 = 0 = $00
Soit 256 valeurs différentes si on compte le 0
Utilité pratique du calcul binaire et hexadécimal
Chaque bit peu représenter un pixel sur un écran graphique
Avec 8 octets, agencés verticalement dans la mémoire qui est représentée à l'écran, on a dessiné les premier caractères:
00111100 = $3C
01000010 = $42
01000010 = $42
01111110 = $7E
01000010 = $42
01000010 = $42
01000010 = $42
00000000 = $00
(on utilise même plus la base décimale, car elle est maintenant démodée)
Si on regarde bien cela représente un A, avec une ligne de pixels vide en base pour laisse un espacement entre chaque ligne dans l'écran
Peu à peu les lettre ont évolués et ont été incluse dans la mémoire du BIOS déjà toutes dessinées (on a pas à les dessiner chaque fois)
Ainsi, le code 65, qu'on appelle code ASCII, représente un A majuscule
66 un B, etc. jusqu'à Z
32, un espace
8 une tabulation
13 un retour de chariot
10 un changement de ligne
92 un backslash --> \
Si on va plus loin dans la base 2 on peut grouper les octets au lieu de grouper les bits
Donc on fait un groupe de deux octets
0000 0000 0000 0000 ou $0000 en hexa
L'octet de droite est celui qu'on appelle de poids faible, car il contient les unités
L'octet de gauche, celui de poids fort, car il contient les 256 aines
Ainsi,en hexadécimal (base 16), $0101 représente 1 x 256 + 1 = 257
Le groupement de 2 octets est un integer
Si on fais la calcul rapidement, un integer (16 bits, 2 octets) va pouvoir contenir 255 x 256 + 255 x 1, soit un chiffre de 0 à 65535
Maintenant on sais compter en base 2, et en base 16, représentée en hexadécimal pour plus de facilité de lecture :)
Vous pouvez maintenant comprendre cette phrase:
Comprendre le binaire vous aidera aussi à comprendre les réseaux d'adresses IP
Subscribe to:
Posts (Atom)