Joseph Michael Pesch
VP Programming

C# Draw Thermometer Goal

by 14. March 2012 07:59

This sample shows how to take a generic thermometer image (with transparent background and empty contents, i.e. outline only) and draw in the content using C#.  The base thermometer image is attached and full code sample below. 

Here are samples of the base image and a test output using the URL shown below:

DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM

Here is the sample code:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Drawing;

public partial class DrawThermometer : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // Sample call:
    // DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM
    string colorString = Request.Params["colorString"];
    string goalHeadText = Request.Params["goalHeadText"];
    string goalBodyText = Request.Params["goalBodyText"];
    decimal goalAmt = Convert.ToDecimal(Request.Params["goalAmt"]);
    string amtSuffix = Request.Params["amtSuffix"];
    decimal actualAmt = 0;
    // ActualAmt is optional input parameter, when not provided we run a SQL query.
    if (Request.Params["actualAmt"] != null)
    {
      actualAmt = Convert.ToDecimal(Request.Params["actualAmt"]);
    }
    else
    {
      string sql = "select sum(SomeField) as actualAmt from SomeTable where Something = true";
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCN"].ConnectionString))
      {
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
          cn.Open();
          actualAmt = Convert.ToInt32(cmd.ExecuteScalar());
          actualAmt = Convert.ToDecimal(actualAmt * Convert.ToDecimal(.000001));
          cn.Close();
        }
      }
    }
    DrawImage(colorString, goalHeadText, goalBodyText, goalAmt, actualAmt, amtSuffix);
  }
  private void DrawImage(string colorString, string goalHeadText, string goalBodyText, decimal goalAmt, decimal actualAmt, string amtSuffix)
  {
    // Locals
    int width = 325;
    int height = 100;
    int barLeft = 83;
    int barTop = 44;
    int barWidth = 220;
    int barHeight = 29;
    int actualWidth = 0;
    int intervalWidth = 20;
    string val = string.Empty;
    ColorConverter converter = new ColorConverter();
    Color fillColor = (Color)converter.ConvertFromString(colorString);
    string imagePath = Server.MapPath("~/images/Thermometer.png");
    using (Image image = Image.FromFile(imagePath))
    {
      using (Graphics g = Graphics.FromImage(image))
      {
        Color color = fillColor;
        SolidBrush fillBrush = new SolidBrush(color);
        g.FillEllipse(fillBrush, new Rectangle(8, 23, 70, 70));
        g.FillRectangle(fillBrush, new Rectangle(barLeft - 10, barTop, 10, barHeight));
        actualWidth = Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * Convert.ToDecimal(barWidth));
        // Adjust for width of markers between bars...
        actualWidth = actualWidth - Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * 2);
        if (actualWidth > barWidth) actualWidth = barWidth;
        // TALL: Rectangle rect = new Rectangle(45, 135, 29, 145);
        // WIDE: Rectangle rect = new Rectangle(83, 44, 221, 29);
        Rectangle rect = new Rectangle(barLeft, barTop, actualWidth, barHeight);
        g.FillRectangle(fillBrush, rect);
        g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
        g.DrawString(goalBodyText, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, barLeft, barTop - 40); // use 28 when printing the goalHeadText...
        val = "$" + goalAmt.ToString() + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, width - val.Length * 18, barTop - 40); // use 28 when printing the goalHeadText...
        // Draw the current actual number
        // TALL: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 40, 275);
        // WIDE: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 16, 39);
        val = "$" + actualAmt.ToString("#.00") + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 12, FontStyle.Bold), Brushes.White
          , width - (barWidth - actualWidth) - 5 - (val.Length * 14), 47);
        // Draw the intervals
        bool target = false;
        for (int i = 1; i <= 10; i++)
        {
          Font font = new Font("Arial Narrow", 8, FontStyle.Bold);
          Brush brush = fillBrush;
          int interval = ((goalAmt > Convert.ToInt32(actualAmt)) ? Convert.ToInt32(goalAmt) : Convert.ToInt32(actualAmt)) / 10;
          int mark = i * interval;
          val = "" + mark.ToString();
          if (!target && (i.Equals(10) || ((mark >= goalAmt) || goalAmt - mark < interval / 2)))
          {
            target = true;
            font = new Font("Arial Narrow", 8, FontStyle.Bold);
            brush = Brushes.Red;
          }
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 60, 1, 8);
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 33, 1, 8);
          g.DrawString(val, font, brush, barLeft + (intervalWidth * i) - (val.Length * 4), height - 18);
        }
      }
      System.IO.MemoryStream stream = new System.IO.MemoryStream();
      image.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
      Context.Response.BinaryWrite(stream.ToArray());
    }

  }

}

Thermometer.png (5.78 kb)

Tags:

C#

Microsoft Word VBA Macro to Run Mail Merge on SQL DSN

by 8. March 2012 13:07

This is a sample macro to generate a dynamic SQL connection and SQL select statement using VBA in Microsoft Word.  The only item I was not able to resolve is getting the connection string to be DSN-less (i.e. it seems to always require and actual ODBC DSN).

Private Sub Document_Open()
  
  Dim msg As String
  msg = "Select ""Yes"" to run the standard half month interval query." & _
        vbCrLf & vbCrLf & _
        "For a custom query select ""No"" " & _
        "and go to Mailings > Edit Recipient List > Filter"
  RunQuery (MsgBox(msg, vbYesNo, "Run Standard Query") = vbYes)
  
End Sub

Private Function GetConnection() As String
  
  GetConnection = "DSN=DWSQL;" & _
       "Driver={SQL Server};" & _
       "Server=servername\instancename,portnumber;" & _
       "Database=databasename;" & _
       "Trusted_Connection=yes;" & _
       "UID=" & Environ("username") & ";" & _
       "WSID=" & Environ("computername")

End Function

Private Sub RunQuery(Standard As Boolean)
  
  Dim doc As Document
  Set doc = ActiveDocument

  Dim sql As String
  If Standard Then
    sql = "select * from TestMailView where StandardCriteria = 'Y'"
  Else
    sql = "select * from TestMailView"
  End If

  ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
  ActiveDocument.MailMerge.OpenDataSource Name:="", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:=GetConnection() _
    , SQLStatement:=sql, _
     SQLStatement1:="", SubType:=wdMergeSubTypeOther
     
  If Standard Then
    On Error GoTo MergeError
    With ActiveDocument.MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With
    'Close the main doc since we already generated the merged doc.
    doc.Close (False)
  End If
  
MergeError:
     If Err.Number <> 0 Then MsgBox Err.Description
  
End Sub

Tags:

Word Mail Merge Error: "Record 'number' contained too few data fields"

by 7. March 2012 10:44

Error message when you use an .odc file to connect to a specific table in a mail merge data source in Microsoft Word: "Record 'number' contained too few data fields"

This appears to be an issue when you use a SQL datasource and the table/view you are selecting is not in the default schema (i.e. dbo).  The Microsoft Support Article (link below) contains information on this.  Appears there is a registry setting (shown below) that can enable a fix for this.

Excerpt from the Microsoft Support Article:

How to enable the hotfix

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

To enable this hotfix, follow these steps:

  1. Exit all Office programs.
  2. Click Start, click Run, type regedit, and then click OK.
  3. If you are using a 2007 Office suite, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common
    If you are using Office 2003, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common
  4. On the Edit menu, point to New, and then click DWORD Value.
  5. Type UseQualifiedTableNameForMailMerge, and then press ENTER.
  6. Right-click UseQualifiedTableNameForMailMerge, and then click Modify.
  7. In the Value data box, type 1, and then click OK.
  8. On the File menu, click Exit to exit Registry Editor.


Microsoft Support Article

Tags: