Tag Archives: C#

Notes in CSI / Syteline

With our implementation of CSI/Syteline it was important to bring over our work instructions which meant extracting the notes out of the old system and putting them into the new one. The issue here is that there is no real method to do this within the system so direct SQL access is required in order to pull this off.

Please keep in mind that being a SQL DBA is one of my many part time jobs and not a specialty, so some of these steps may not have been as efficient as they could have been.

For our two different production systems the notes were stored using two different ways, though the process for putting them into CSI was basically the same:

  • For JDE our notes were integrated into the routing line and due to the structure they required some re-processing. Fortunately these routers were very stable so I didn’t have to worry about a “live migration” of the notes. Using a brief piece of code I extracted the lines and formatted them to Excel (although theoretically the same steps could be accomplished in Excel without using code).
  • For our JobBOSS notes I needed to be able to pull these over at or near go-live. This meant that I had to do some very light processing of the note text within Visual Studio.

In CSI there are three tables of importance:

  1. NoteHeaders – in this table is a list of what “token” goes to which table. Since these are work instructions on the router the notes will be associated with the jobroute table. The important step here is that at least one note needs to be created to generate this entry. There are other possible ways, but this is the simplest method: make a note on an operation line, and then check in the NoteHeaders table to see what number goes to jobroute. The note can then be deleted.
  2. SpecificNotes – This table will have the note text (and note title) that will contain the work instructions
  3. ObjectNotes – This table will have the note key associated with the note text in the SpecificNotes table, the table the notes goes to (using the number from the NoteHeaders table) and the row pointer to the record in the table that it is associated with.

A complicating factor for our JobBOSS implementation is that we were required to rework and/or resequence the router lines (for instance something that was “Op100-CNC” in JobBOSS might be “Op80-Mill” in CSI). If nothing was changed on the routers it would be possible to “brute force” the note entry by adding all the notes, pulling the row pointers in sequence and then building a query from that to add the associations in the ObjectNotes table (because if I had 100 router lines, and 100 notes then I’d know that the 50th note in the SpecificNotes table goes to the 50th record in the jobroute table). However due to the changing router I had to handle the notes using the same code in C# in Visual Studio that I was using to put the routers together, since only that code knew what the sequence would be.

The first step then was to extract the router lines from JobBOSS. I used a LINQ query like this:

var operations = from o in jo.Job_Operations
                 join o2 in jo.Jobs on o.Job equals o2.Job1
                 join o3 in jo.Materials on o2.Part_Number equals o3.Material1
                 where o.Status.Trim().Equals("T") && o2.Priority<9 && o3.Status.Trim().Equals("Active")&&o2.Part_Number!=null
                 orderby o3.Material1,o.Sequence
                 select new { o.Job,o3.Material1,o.Sequence,o.Description,o.Est_Setup_Hrs,o.Inside_Oper,o.Operation_Service,
                                   o.Run,o.Run_Method,o.Vendor,o.WC_Vendor,o.Work_Center,o.Last_Updated,o.Last_Updated_By,o.Est_Unit_Cost,
                                    o.Note_Text,o.Cost_Unit};

The “where” clause is limiting the jobBOSS jobs to be pulled over to templates (“T”) that go to Active part numbers.

Many, many lines of code later I get to the note section. Due to the nature of the notes tables I was unable to get the code to insert the notes directly into the database and instead had to write out a query out to a text file that I then ran in SSMS. I cycled through each line in the router and wrote out an Insert query for each note:

string sqlCmd = "INSERT INTO[SpecificNotes](SpecificNoteToken, NoteContent, NoteDesc, NoteExistsFlag, " +
"CreatedBy, UpdatedBy, CreateDate, RecordDate, RowPointer, InWorkflow)" +
"values((SELECT ISNULL(MAX(SpecificNoteToken) + 1, 0) from dbo.[SpecificNotes]),'" +
n.NoteText + "','WORK INSTRUCTIONS', 0, 'sa', 'sa', DEFAULT, DEFAULT, DEFAULT, 0);";

I wrote the lines of text out as they were generated and then copied the contents of the resulting file and pasted them into SSMS. Before running it I had to put the text “SET IDENTITY_INSERT SpecificNotes ON” at the beginning and “SET IDENTITY_INSERT SpecificNotes OFF” at the end.

With my notes added to the database I then rerun the code to generate the Current Operations lines. This time, a different piece of code in the routine goes out to find a note that is now in the CSI database that matches the note line held by the object of the Current Operation line:

//get notes from CSI
var snotes = from s in si.SpecificNotes
            select new { s.NoteContent, s.SpecificNoteToken };
List<sNotes> sNoteList = new List<sNotes>();
foreach (var s in snotes)
            {
                sNoteList.Add(new sNotes());
                sNoteList.Last().Token = (int)s.SpecificNoteToken;
                sNoteList.Last().Note = s.NoteContent.Trim().Replace("'", "''"); ;
            }
//Marry the notes in the note DB to the process lines, OYE!
foreach (var n in syteCurrOps2)
            {
                foreach (var s in sNoteList)
                {
                    if (!s.Used)
                    {
                        //Note: essential that the notes read out of the database match the ones inserted (i.e. avoid whitespace issues!)
                        if (n.NoteText.Trim().Equals(s.Note))
                        { n.NoteNum = s.Token; s.Used = true; worker.ReportProgress(i++); break; } 
                    }
                }
            } 

Now that I know which note in the database goes to which router lines, I need a line in the ObjectNotes table to match them up. Restating a key point here, a normal note must be manually added to a Current Operation line so that CSI will know which table the note is associated with. Just add a line and then query the lines in the NoteHeaders table to see what number must be used on the SQL Insert lines for the ObjectNotes table:

In this case in the code I have a number 4 in the insert statement, but I would want to change it to a 1:

//NOTE: change #4# below to the number specified in the NoteHeaderToken field in the NoteHeaders table
//      This number is set when the "sample" note was created earlier (which is why a sample note must be created before insert)
string sqlCmd = "INSERT INTO [ObjectNotes](ObjectNoteToken,NoteHeaderToken,RefRowPointer,NoteType,SpecificNoteToken," +
"NoteExistsFlag,CreatedBy,UpdatedBy,CreateDate,RecordDate,RowPointer,InWorkflow)" +
"values((SELECT ISNULL(MAX(ObjectNoteToken) + 1, 0) from dbo.[ObjectNotes]),4,'" + n.RefRow + "',0," + n.NoteNum + ",0," +
"'sa','sa',DEFAULT,DEFAULT,DEFAULT,0);";
sw.WriteLine(sqlCmd);

Just as with the notes query, I write this query list out to a text file so that they can be run in SSMS (and again setting the “SET IDENTITY_INSERT SpecificNotes ON/OFF” lines before/after the query).

The last step is that since all these notes were entered in SQL and not the interface, CSI needs to know which specific Current Operation lines have notes. We do this by setting the bit operator for the NoteExistsFlag field to one. So again, I just dump out a a series of lines to put into SSMS to update all of the lines based on the row pointers for the lines that now have notes:

foreach (var n in syteCurrOps2)
    {
        if (!n.RefRow.Equals("") && n.NoteNum > 0)
          {
              string sqlCmd = "UPDATE jobroute_mst SET NoteExistsFlag=1 WHERE RowPointer='" + n.RefRow+ "';";
              sw.WriteLine(sqlCmd);
              worker.ReportProgress(i++);
          }
          else if (n.RefRow.Equals("") && n.NoteNum > 0)
              logger("Rowless line: " + "Item: " + n.Item + " Seq " + n.Seq);
    }

So in the end there are three sets of SQL codes exported, one for each of the two related Notes tables, and one for the table that is having the notes added to it. The last line of code in that snippet logs lines that the code thinks should have a note, but couldn’t find one. This typically happened due to odd characters (‘,”,*, etc.) or white space mismatch issues, but over all it was quite reliable.

Slightly off topic, but you may see the “worker.ReportProgress” line in some of the code. Due to the fact that some of the queries took a long time to run I had to put my main methods into new threads (multi-threading). Every now and then in the code I put these ReportProgress lines in so that my (very) inexact progress meter would update on the interface to let me know it was running (and to give me some idea of where it was at in the execution process). So under my code for my “export button” I had something like this to launch the Current Operation export process:

//extracts have to launch in new a thread so that the parent thread won't hang and progress can be tracked
worker.WorkerSupportsCancellation = true;
worker.WorkerReportsProgress = true; 
if (comboBoxExpType.Text.Equals("CurrentOps"))
                worker.DoWork += new DoWorkEventHandler(exportCurrentOps);
worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
worker.ProgressChanged += new ProgressChangedEventHandler(worker_ProgressChanged);
worker.RunWorkerAsync();

Nortel BCM Log Parser

I had originally coded this project in VB and never got it into a distributable format.  For example, I had all the file paths and database connection strings hard coded.  After educating myself a bit on C# I recoded the application in that language, and have the program using LINQ instead of (oof!) OLE.  I also now have it coded to accept values specific to a user’s environment via an XML settings file.

What the program does at this point is that it reads the call detail recording logs that are stored in a directory and commits them to an MS SQL/SQLexpress database.  For my own purposes I have all the default settings set on the BCM for recording the log files, so if you’ve changed any of these settings this program may not work.  As well, I have my logs FTP’d every day to a server share that I can access via UNC (i.e. \\server\logfiles).

I have a little setup menu within the program so that you can create the database, set the settings, and then write the settings out to an XML file for future use. Using the XML settings file the program can be scheduled to run if the XML settings file is given to the program as a variable (ex, NortelLogParser c:\settings.xml).

In the future I hope to develop a web front end for the database, but for the time being you’ll have to use Excel or some other tool to query the database for the information that you want.

Click here to download.

UPDATE: I just noticed (12/14/2011) that the field detection is still a little buggy, and a feature that I added after the fact of associating a record with the file it came out of lags by a day.  The program is still better than nothing, but I do have to patch it up a bit when I get some time.

Command Line Argument

Firing up the way-back machine, I recalled interfaces from Exchange versions past that would immediately return some nice data, such as mailbox size, item counts, etc.  I set out to try and recreate some of these interfaces, but it seemed that a lot of the coding examples were in C#, a language that I wasn’t very familiar with.

Undeterred, I went through some C# lessons and I now have what I hope is a decent “working IT guy’s” knowledge of it’s inner workings.  Unfortunately at this point, I can’t exactly remember what I was missing from the past Exchange interfaces that drove me to learn C# to begin with, apart from the table that had the mailbox sizes.

I eventually determined that the Powershell command that I needed was:

Get-MailboxStatistics -server <exchange server name>| Select-Object DisplayName, TotalItemSize, ItemCount,StorageLimitStatus,LastLogonTime | Out-GridView”

I figured my C# coding skills would have to apply to something else as I could just as easily put that line into a batch file! For some reason, though, launching the command from a batch file would open the ‘Grid View’, but it would close immediately, so back to C#

I had the program working fine on my desktop, and had it set to either ask for a server name, or accept a command line argument that would contain the server name. For reasons I still don’t fathom, the command line argument wouldn’t work on the Exchange server itself. I eventually had to change the code from:

if (args[0] != null)
ServerName = args[0];

to:

if (args.Length>0)
{
if (args[0] != null)
{
ServerName = args[0];

Which makes more sense, I guess, but I don’t know why the former only worked on the desktop unless there’s some variation of the .Net runtimes between the desktop and server in how they handle command line arguments?

Anyway, if you’d care for a little program that auto-launches this grid view, I’ve posted it here.  It includes the user’s display name, their mailbox size, the number of items, and if they’ve exceeded their quota.  You may need this note if you get an ‘index out of bound’ error due to the Powershell visual GUI elements not being installed (obviously you’ll need a 64 bit machine with the Exchange Powershell add-on installed).

Remote Desktop With XP

Faced with an aging remote access server with dubious patches I wanted to move a little more quickly to a Windows Server 2008 R2 terminal server, but there is an issue: there’s no support for NLA connections within XP out of the box.  True, there’s some registry hacks to enable the feature, but I couldn’t have end user’s doing that, so I resolved to write a program to do it for them.  It wasn’t until I was almost done that I discovered that, alas, Microsoft had already written one.

I hate to be outdone to that extent so I’m still posting mine here: a zip file which contains a Windows forms version, and a command line version, both of which run remarkably faster than Microsoft’s patch.  My program(s) will check to make sure that it’s the proper version of the OS (Windows XP with service pack 3) and that the registry entries need to be added (with visual output).  No backups are taken, so use at your own (minimal) risk!

(Although I typically share my code, I must admit to being slightly embarrassed at it’s condition since it’s my first full fledged C# program.  I’ll share if I get around to cleaning it up so that it doesn’t look like a VB guy wrote a C# program 🙂

Now to patch up all the other Windows XP issues with the Remote Desktop Connection services in Windows 2008 r2….