DataFormatString - Displaying nothing when there is a zero value, and currency when there is a value

Here it is:

{0:$0.00;$(0.00);''}



When the value is negative, it will be wrapped in parenthesis.  When it's positive, it will display the value.

And when it's zero, it will show nothing.

Grouping by Week, Month, and Quarter in SQL

We had a need for a report that showed how often entries were put in the database.  We also needed the report to show be able to show the totals by the week, the month, or the quarter depending on what the end user was looking for.  Here's my stored procedure:

CREATE PROCEDURE dbo.sp_carGraphTotalCAR
    (
        @start datetime,
        @stop datetime, 
        @dateGroup varchar(25)    
    )
AS


if @dateGroup = 'week'
  BEGIN
    select DateAdd(d, 1 - DATEPART(dw, entrydate), CONVERT(datetime, CONVERT(varchar,entrydate,112) ) ) as Week, count(carid) as Count from caritem
    where @start < entryDate and @stop > entryDate
    group by DateAdd(d, 1 - DATEPART(dw, entrydate), CONVERT(datetime, CONVERT(varchar, entrydate,112) ))
  END

else if @dateGroup = 'month'
  BEGIN
    select  DATEADD(MONTH, DATEDIFF(MONTH, 0, entrydate), 0),COUNT(carid) TotalCount from caritem
    where @start < entryDate and @stop > entryDate
    group by  DATEADD(MONTH, DATEDIFF(MONTH, 0, entrydate), 0)
  END

else --group by quarter
  BEGIN
    select  DATEADD(QUARTER, DATEDIFF(QUARTER, 0, entrydate), 0),COUNT(carid) TotalCount from caritem
    where @start < entryDate and @stop > entryDate
    group by  DATEADD(QUARTER, DATEDIFF(QUARTER, 0, entrydate), 0) 
  END

    RETURN

Writing machine activity to a SQL database using Arduino with Ethernet Shield

We had a request to keep track of the utilization of some of our CNC presses.  The managers want to be able to look back on a machine and see how often it was in use in a 24 hour period.  An Arduino Mega (although it didn't have to be the Mega, it could have been the Uno) with an Ethernet Shield was leveraged for the task.

The first step was to find out when the machine is active.  This particular machine has a pair of open contacts that close every time the foot pedal is pressed.  I hooked it up to one of my inputs on the Arduino and sent it 5 volts. Every time those contacts close, and the voltage goes high on my input, I add to my counter.  I then pass that information to SQL.  I also flash an LED on my Arduino housing to show that it recorded an event.

Here is the Arduino code:

#include <SPI.h>
#include <Ethernet.h>

#define e116Pin 42
#define e116LEDPin 47
#define e117LEDPin 48
#define e117Pin 30 // Echo Pin
#define e118LEDPin 49
#define e118Pin 36 // Echo Pin


byte mac[] = { 0x90, 0xA2, 0xDA, 0x0E, 0x03, 0xE1};
IPAddress ip(192,168,253,251);
byte server[] = { 192, 168,   33,  54 }; 
IPAddress myDns(192,168,33,40);
IPAddress gateway(192,168,33,45);
IPAddress subnet(255,255,0,0);
EthernetClient client;

int e116cnt = 0;  
int e116LastState = 1;
int e116CurrentState = 1;

int e117cnt = 0;  
int e117LastState = 1;
int e117CurrentState = 1;

int e118cnt = 0;  
int e118LastState = 1;
int e118CurrentState = 1;

unsigned long lastUpdate = millis();

void setup(){
 Serial.begin(9600); 

 pinMode(e116Pin, INPUT);
 pinMode(e116LEDPin, OUTPUT); 
 pinMode(e117Pin, INPUT);
 pinMode(e117LEDPin, OUTPUT); 
 pinMode(e118Pin, INPUT);
 pinMode(e118LEDPin, OUTPUT); 

 delay(1000);
 Ethernet.begin(mac, ip, myDns, gateway, subnet); 
}

void loop(){ 

 e116CurrentState = digitalRead(e116Pin);
 e117CurrentState = digitalRead(e117Pin);
 e118CurrentState = digitalRead(e118Pin);



  if (e116CurrentState == HIGH && e116LastState == LOW){
    e116cnt++;
    digitalWrite(e116LEDPin, HIGH);
    Serial.print("e116 Count: ");
    Serial.println(e116cnt);
  }
  else{ digitalWrite(e116LEDPin, LOW); }
  
  if (e117CurrentState == HIGH && e117LastState == LOW){
    e117cnt++;
    digitalWrite(e117LEDPin, HIGH);
    Serial.print("e117 Count: ");
    Serial.println(e117cnt);
  }
  else{ digitalWrite(e117LEDPin, LOW); }
  
  if (e118CurrentState == HIGH && e118LastState == LOW){
    e118cnt++;
    digitalWrite(e118LEDPin, HIGH);
    Serial.print("e118 Count: ");
    Serial.println(e118cnt);
  }
  else{ digitalWrite(e118LEDPin, LOW); }

  
    if (millis() - lastUpdate > 60000){ //reset after 60 seconds
    lastUpdate = millis();
    Serial.print("60 Sec Interval: ");
    Serial.println(millis());
    if (e116cnt > 0){
     UploadData(116, e116cnt);
     e116cnt = 0;
    }
    if (e117cnt > 0){
     UploadData(117, e117cnt);
     e117cnt = 0;
    }
    if (e118cnt > 0){
     UploadData(118, e118cnt);
     e118cnt = 0;
    }
   }
  
  e116LastState = e116CurrentState;
  e117LastState = e117CurrentState;
  e118LastState = e118CurrentState;
  
delay(10);
}

void UploadData(int equipNo, int count){
  client.connect(server , 80);
  
  if (client.connected()) {
    Serial.println("Connected to server -- writing"); 
    client.print("GET /log2sql.asp?no=");
    client.print(equipNo);
    client.print("&count=");
    client.print(count);
    client.print(" HTTP/1.1\n"); 
    client.print("Host:192.168.33.54 \n"); 
    client.print("Connection: close\n");     
    client.print("Content-Type: application/x-www-form-urlencoded\n");
    client.print("Content-Length: 0 \n\n" );
  }
    client.stop();
    client.flush();
}

I could have transmitted the information every time the count incremented, but that seemed like a lot of unnecessary network traffic.  Instead, I decided to save up all the pedal presses, and then transmit the number every 60 seconds.  The Arduino is not able to write directly to a SQL server, but it is able to write to a web page.  I created a classic ASP web page that accepts two variables, the machine number (I have multiple machines writing to the database), and the count of how many events happened in that 60 seconds).

My ASP Code:

<html>
<body>

<%
   Set cn = Server.CreateObject("ADODB.Connection")
   Set cmd = Server.CreateObject("ADODB.Command")
   cn.Open "Provider=SQLOLEDB; Data Source =sqlsrv08; Initial Catalog = equipmentStats; User Id = iusr_net; Password=12ab6HG7xf"
    
   set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_InsertLog"
   cmd.CommandType = 4
   cmd.Parameters.Refresh
   cmd.Parameters(1) = Request.QueryString("no")
   cmd.Parameters(2) = Request.QueryString("count")
   cmd.Execute
%>

</body>
</html>

When the ASP page receives that information, it connects to SQL through a stored procedure and writes the information to the database. When the information is written to the database, SQL also records the current date and time of the event.  Looking back at the data, I'm able to tell up to the minute what happened on each machine.

My SQL Stored Procedure:

ALTER PROCEDURE dbo.sp_InsertLog (
@equipNo int,
@count int
)
    
AS SET NOCOUNT ON 

    insert Log (EquipmentNo, [Count], [TimeStamp])
    values       (@equipNo, @count, GETDATE())

RETURN

Automated Time Sync via Serial Communication with Valcom V-2924A

We had a problem with our Valcom V-2924A losing time.  It would drift up to a couple of minutes per month.  The only way to correct the time is to hook up a laptop up to the serial port and run the Valcom program, and manually adjust the time.  This would work fine, except the clock has such a huge drift.  We even replaced the Valcom unit, and the replacement wasn't any better.  The clock drift itself wouldn't even be that big of a problem, except this unit runs our buzzers at the beginning and end of the shift.  It has no way of synchronizing with our time clocks.

I downloaded a program called Free Serial Port Monitor at http://www.serial-port-monitor.com/.  I told it to monitor my com port, and then I ran the Valcom program to check the time and write the time back.  Once I found the commands it was using to set the time, I replicated those commands in a c# console application. The only problem I found was at the end of the time string going to the Valcom, it had a hexadecimal number between (decimal) 672 and 720.  I couldn't figure out how it knows which value to pick, but it obviously needs a correct value here to work.  Since I couldn't ever figure out which one it needed, I just looped and sent them all.  Since that only worked most of the time, I sent them each 10 times.  I'm assuming those three digits are a checksum of some kind, but regardless the brute force method seemed to work well.

Since we have a dedicated computer hooked up to the Valcom to set the time once a month anyway, I scheduled a task on it to run my C# console application every hour.  No more drifting time.

Here's my code:

static class Program {
       static SerialPort serialPort = new SerialPort();

        static void Main(string[] args) {
           serialPort.PortName = LoadComPortFromCSV();
           serialPort.BaudRate = 9600;
           serialPort.Parity = Parity.None;
           serialPort.DataBits = 8;
           serialPort.StopBits = StopBits.One;
           //serialPort.Handshake = Handshake.XOnXOff;
           serialPort.DataReceived += serialPort_DataReceived;
           serialPort.DtrEnable = true;
           serialPort.RtsEnable = true;
           OpenSerial();

           if (serialPort.IsOpen) {
               serialPort.WriteLine("U010B6");
               serialPort.Write(new byte[] { 13, 10 }, 0, 2);
               Console.WriteLine("Sending U010B6");
               System.Threading.Thread.Sleep(3000);

               
               for (int j = 0; j < 5; j++) {
                   for (var i = 672; i < 720; i++) {
                       serialPort.Write("T" + DateTime.Now.ToString("HHmmssMMddyy") + i.ToString("X"));
                       serialPort.Write(new byte[] {13, 10}, 0, 2);
                       Console.WriteLine("Sending T" + DateTime.Now.ToString("HHmmssMMddyy") + i.ToString("X"));
                       System.Threading.Thread.Sleep(50);
                   }
               }

               Console.WriteLine();
           }
        }

        private static String LoadComPortFromCSV() {
            var port = string.Empty;
            try {
                var reader = new StreamReader(File.OpenRead(Environment.CurrentDirectory +  @"\port.csv"));
                while (!reader.EndOfStream) {
                    port = reader.ReadLine().TrimStart();
                }
            }
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
            return port;
        }

        static void serialPort_DataReceived(object sender, SerialDataReceivedEventArgs e) {
            var sp = (SerialPort)sender;
            var indata = sp.ReadExisting();
            Console.Write(indata);
            CloseSerial();
            }

        public static void OpenSerial() {
            try {
                if (!serialPort.IsOpen) {
                    Console.WriteLine("Opening " + serialPort.PortName);
                    serialPort.Open();
                }}
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
        }

        public static void CloseSerial() {
            try { if (!serialPort.IsOpen) serialPort.Close();}
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
            }
        }
    }