Concatenate SQL Server column values into a single, comma-delimited value

Our vendors can be licensed in one or more states. Until today, our search results only showed the state in which the vendor lived. Now, the search results show the state in which they live and the (most often) neighboring states they also cover (if applicable).

Technically, we're saving the vendor's locations (county/city and state) in a table. So it's one vendor to zero or more locations (plus the "home" state in which they live, which is their mailing address).

Our employees search for vendors based on a specific location. Using a radius search based on lat/long coordinates, I return a list of vendors. As mentioned, one column is the state (ex: OH).

Well, some vendors near Cincinnati, OH cover locations in Kentucky (KY) and Indian (IN). So...I wanted to show, as an example, the following if the vendor covered locations in those states:

OH, KY, IN

Maybe there's a better way, but I came across "solution 4" on this site, which lead me to create the following function (the "magic" happens with the bold line):

ALTER FUNCTION [dbo].[fx_GetStatesCovered]
 (
 @VendorID int
 )
RETURNS varchar(max)
AS
 BEGIN

 DECLARE @StatesCovered char(2)

 SELECT 
  @StatesCovered = States.Abbr
 FROM 
  Vendors WITH (NOLOCK)
  JOIN States WITH (NOLOCK) 
  ON Vendors.AddressStateID = States.ID
 WHERE 
  Closers.ID = @VendorID

 DECLARE @Abbr1 VARCHAR(MAX)
 SET @Abbr1= @StatesCovered + ', '
 
 SELECT @Abbr1 = @Abbr1 + Abbr + ', ' FROM
 (SELECT DISTINCT Abbr
 FROM VendorCounties WITH (NOLOCK)
 JOIN States WITH (NOLOCK) ON VendorCounties.StateID = States.ID
 WHERE VendorID = @VendorID AND Abbr != @StatesCovered) AS x

 RETURN LEFT(@Abbr1,LEN(@Abbr1)-1)

 END

GO

Getting the UPS shipping label via their API

After integrating our site with FedEx, we decided to also offer support for UPS. Some of our clients use FedEx and some use UPS. The FedEx integration was fairly quick and easy. The UPS integration is basically the same so far.

To get started, UPS provides a document and code sample for creating a shipment via the UPS Shipping Web Service API, but the code does not show how to get the actual shipping label.

Hopefully the following code helps someone else. For example, UPS returns the label in two parts when you request a GIF image of the label. The response gives you the GIF image and an HTML page. The HTML page references the image. Specifically, the page looks for a file named labelTRACKINGNUMBER.gif.

Here's the code. Enjoy!

     protected void Button1_Click(object sender, EventArgs e)  
     {  
       try  
       {  
         ShipService shipService = new ShipService();  
         ShipmentRequest shipmentRequest = new ShipmentRequest();  
   
         shipService.UPSSecurityValue = GetUpsShipSecurityValue();  
   
         UpsShipService.RequestType request = new UpsShipService.RequestType();  
         String[] requestOption = { "nonvalidate" };  
         request.RequestOption = requestOption;  
         shipmentRequest.Request = request;  
   
         UpsShipService.ShipmentType shipment = new UpsShipService.ShipmentType();  
         shipment.Description = "This is the description...";  
   
         shipment.PaymentInformation = GetUpsPaymentInfo();  
         shipment.Shipper = GetUpsShipper();  
         shipment.ShipFrom = GetUpsShipFrom();  
         shipment.ShipTo = GetUpsShipTo();  
         shipment.Service = GetUpsShipmentService();  
         shipment.Package = GetUpsPackageType();  
         shipmentRequest.LabelSpecification = GetUpsLabelSpecification();  
   
         shipmentRequest.Shipment = shipment;  
   
         ShipmentResponse shipmentReponse =
              shipService.ProcessShipment(shipmentRequest);  
   
         string graphicImage =
              shipmentReponse.ShipmentResults.PackageResults[0].ShippingLabel.GraphicImage;  
   
         byte[] byteLabel1 = Convert.FromBase64String(graphicImage);  
         string shippingLabelFileName1 = SaveShippingLabelGIF(byteLabel1, "_labels\\", shipmentReponse.ShipmentResults.ShipmentIdentificationNumber);  
   
         string htmlImage =
              shipmentReponse.ShipmentResults.PackageResults[0].ShippingLabel.HTMLImage;  
   
         byte[] htmlLabel = Convert.FromBase64String(htmlImage);  
         string shippingLabelFileNameHtml1 = 
              SaveShippingLabelHTML(htmlLabel, "_labels\\",shipmentReponse.ShipmentResults.ShipmentIdentificationNumber);    
   
         Label1.Text = string.Format("{0}: Tracking: {1}; Cost: {2}", 
              shipmentReponse.Response.ResponseStatus.Description, 
              shipmentReponse.ShipmentResults.ShipmentIdentificationNumber,
              shipmentReponse.ShipmentResults.ShipmentCharges.TotalCharges.MonetaryValue);  
   
         System.Uri uri = new System.Uri(Request.Url.AbsoluteUri);  

         HyperLink1.Text = shippingLabelFileNameHtml1;
         HyperLink1.Target = "_blank";
         HyperLink1.NavigateUrl = string.Format("http://{0}/_labels/{1}", uri.Host, shippingLabelFileNameHtml1);  
       }  
       catch (System.Web.Services.Protocols.SoapException exSoap)  
       {  
         Label1.Text = string.Format("Soap Exception: {0}", exSoap.Detail.InnerText);  
       }  
       catch (Exception ex)  
       {  
         Label1.Text = string.Format("Exception: {0}", ex.Message);  
       }  
   
     }  
   
     private UpsShipService.UPSSecurity GetUpsShipSecurityValue()  
     {  
       UpsShipService.UPSSecurity upsSecurity = new UpsShipService.UPSSecurity();  
   
       UpsShipService.UPSSecurityServiceAccessToken upsSecurityAccessToken = 
              new UpsShipService.UPSSecurityServiceAccessToken();  
       upsSecurityAccessToken.AccessLicenseNumber = "";  
       upsSecurity.ServiceAccessToken = upsSecurityAccessToken;  
   
       UpsShipService.UPSSecurityUsernameToken upsSecurityUserNameToken = 
              new UpsShipService.UPSSecurityUsernameToken();  
       upsSecurityUserNameToken.Username = "YOUR API USERNAME";  
       upsSecurityUserNameToken.Password = "YOUR API PASSWORD";  
       upsSecurity.UsernameToken = upsSecurityUserNameToken;  
   
       return upsSecurity;  
     }  
   
     private PaymentInfoType GetUpsPaymentInfo()  
     {  
       PaymentInfoType paymentInfo = new PaymentInfoType();  
       ShipmentChargeType shipmentCharge = new ShipmentChargeType();  
       BillShipperType billShipper = new BillShipperType();  
       billShipper.AccountNumber = "YOUR ACCOUNT NUMBER";  
       shipmentCharge.BillShipper = billShipper;  
       shipmentCharge.Type = "01"; // credit card type...required?  
   
       ShipmentChargeType[] shipmentChargeArray = { shipmentCharge };  
       paymentInfo.ShipmentCharge = shipmentChargeArray;  
   
       return paymentInfo;  
     }  
   
     private ShipperType GetUpsShipper()  
     {  
       ShipperType shipper = new ShipperType();  
       shipper.ShipperNumber = "YOUR ACCOUNT NUMBER";  
   
       ShipAddressType shipperAddress = new ShipAddressType();  
   
       String[] addressLine = { "3136 Kingsdale Center, #117" };  
       shipperAddress.AddressLine = addressLine;  
       shipperAddress.City = "Upper Arlington";  
       shipperAddress.PostalCode = "43221";  
       shipperAddress.StateProvinceCode = "OH";  
       shipperAddress.CountryCode = "US";  
         
       shipper.Address = shipperAddress;  
       shipper.Name = "Signature Closers";  
       shipper.AttentionName = "*** Attention Line ***";  
   
       ShipPhoneType shipperPhone = new ShipPhoneType();  
       shipperPhone.Number = "8886777462";  
       shipper.Phone = shipperPhone;  
   
       return shipper;  
     }  
   
     private ShipFromType GetUpsShipFrom()  
     {  
       ShipFromType shipFrom = new ShipFromType();  
       ShipAddressType shipFromAddress = new ShipAddressType();  
   
       String[] addressLine = { "3136 Kingsdale Center, #117" };  
       shipFromAddress.AddressLine = addressLine;  
       shipFromAddress.City = "Upper Arlington";  
       shipFromAddress.PostalCode = "43221";  
       shipFromAddress.StateProvinceCode = "OH";  
       shipFromAddress.CountryCode = "US";  
       shipFrom.Address = shipFromAddress;  
   
       shipFrom.Name = "Signature Closers";  
       shipFrom.AttentionName = "*** FROM Attention Line ***";  
   
       return shipFrom;  
     }  
   
     private ShipToType GetUpsShipTo()  
     {  
       ShipToType shipTo = new ShipToType();  
       ShipToAddressType shipToAddress = new ShipToAddressType();  
   
       String[] addressLine1 = { "5168 Birchwood Farms Dr." };  
       shipToAddress.AddressLine = addressLine1;  
       shipToAddress.City = "Mason";  
       shipToAddress.PostalCode = "45040";  
       shipToAddress.StateProvinceCode = "OH";  
       shipToAddress.CountryCode = "US";  
       shipTo.Address = shipToAddress;  
       shipTo.AttentionName = "Schap!";  
       shipTo.Name = "Schapplication";  
   
       ShipPhoneType shipToPhone = new ShipPhoneType();  
       shipToPhone.Number = "5135555555";  
       shipTo.Phone = shipToPhone;  
   
       return shipTo;  
     }  
   
     private UpsShipService.ServiceType GetUpsShipmentService()  
     {  
       //01 = Next Day Air  
       //02 = 2nd Day Air  
       //03 = Ground  
       //07 = Express  
       //08 = Expedited  
       //11 = UPS Standard  
       //12 = 3 Day Select  
       //13 = Next Day Air Saver  
       //14 = Next Day Air Early AM  
       //54 = Express Plus  
       //59 = 2nd Day Air A.M.  
       //65 = UPS Saver  
       //82 = UPS Today Standard  
       //83 = UPS Today Dedicated Courier  
       //84 = UPS Today Intercity  
       //85 = UPS Today Express  
       //86 = UPS Today Express Saver  
       //96 = UPS Worldwide Express Freight  
   
       UpsShipService.ServiceType service = 
              new UpsShipService.ServiceType();  
       service.Code = "01";  
       return service;  
     }  
   
     private UpsShipService.PackageType[] GetUpsPackageType()  
     {  
       UpsShipService.PackageType package = new UpsShipService.PackageType();  
   
       PackageWeightType packageWeight = new PackageWeightType();  
       packageWeight.Weight = "1";  
   
       ShipUnitOfMeasurementType uom = new ShipUnitOfMeasurementType();  
       uom.Code = "LBS";  
       packageWeight.UnitOfMeasurement = uom;  
       package.PackageWeight = packageWeight;  
   
       //01 = UPS Letter  
       //02 = Customer Supplied Package  
       //03 = Tube  
       //04 = PAK  
       //21 = UPS Express Bo   
       //24 = UPS 25KG Box  
       //25 = UPS 10KG Box  
       //30 = Pallet  
       //2a = Small Express Box  
       //2b = Medium Express Box   
       //2c = Large Express Box   
   
       PackagingType packType = new PackagingType();  
       packType.Code = "01";  
       package.Packaging = packType;  
       UpsShipService.PackageType[] pkgArray = { package };  
   
       return pkgArray;  
     }  
   
     private LabelSpecificationType GetUpsLabelSpecification()  
     {  
       LabelSpecificationType labelSpec = new LabelSpecificationType();
   
       LabelImageFormatType labelImageFormat = new LabelImageFormatType();  
       labelImageFormat.Code = "GIF";  
       labelSpec.LabelImageFormat = labelImageFormat;  
   
       return labelSpec;  
     }  
   
     /// <summary>  
     /// Saves the GIF part of the label as labelTRACKINGNUMBER.gif.  
     /// </summary>  
     /// <returns>The saved file name.</returns>  
     private string SaveShippingLabelGIF(byte[] labelBuffer, string folder, string trackingNumber)  
     {  
       string fileName = string.Format("label{0}.gif", trackingNumber);  
       return SaveShippingLabel(labelBuffer, folder, trackingNumber, fileName);  
     }  
   
     /// <summary>  
     /// Sets the HTML part of the label as "page_TRACKINGNUMBER.html" and saves the label.  
     /// </summary>  
     /// <returns>The saved file name.</returns>  
     private string SaveShippingLabelHTML(byte[] labelBuffer, string folder, string trackingNumber)  
     {  
       string fileName = string.Format("page_{0}.html", trackingNumber);  
       return SaveShippingLabel(labelBuffer, folder, trackingNumber, fileName);  
     }  
   
     private string SaveShippingLabel(byte[] labelBuffer, string folder, string trackingNumber, string fileName)  
     {  
       string returnFileName = fileName;  
   
       string phyPath = HttpRuntime.AppDomainAppPath;  
       string fullPath = String.Format("{0}{1}", phyPath, folder);  
       if (!System.IO.Directory.Exists(fullPath)) System.IO.Directory.CreateDirectory(fullPath);  
       string pathToCheck = String.Format("{0}{1}", fullPath, returnFileName);  
   
       // Should just delete the old file (code copied from another project)  
       if (System.IO.File.Exists(pathToCheck))  
       {  
         int counter = 1;  
         while (System.IO.File.Exists(pathToCheck))  
         {  
           returnFileName = counter.ToString() + fileName;  
           pathToCheck = String.Format("{0}{1}", fullPath, returnFileName);  
           counter++;  
         }  
       }  
   
       System.IO.FileStream LabelFile = new System.IO.FileStream(pathToCheck, System.IO.FileMode.Create);  
       LabelFile.Write(labelBuffer, 0, labelBuffer.Length);  
       LabelFile.Close();  
   
       return returnFileName;  
     }  

CIOs have improved all of their processes...seriously?

Recently, I came across a Gartner report on Reimagining IT that talks about CIOs being freed up to focus on enterprise growth and strategic impact. Both of which have eluded CIOs for a number of years.

Yes, interesting that only now are CIOs able to focus on growth and impact. However, I found their strategies for impacting growth to be more interesting. Why? Well, "Improving business processes" was #1 for the last three years, but it dropped to #5 in 2011. What? Have we improved our business processes and no longer need to address them? I doubt it. And I see that the results confirm it. Plus, I think CIOs just don't want to say they're "working on improving processes," because it's potentially cliche or boring or dated. (Granted, some companies have done wonderful things to their processes and they are ready to move on. Congrats.)

Let's look at the top 3...

#1 in 2011: Increasing enterprise growth

OK, how are you going to do that if your processes are out of whack and you're fighting issues with your processes? Good luck. You need solid, yet flexible, processes that support innovation and growth. Good process drives growth. You can call it "increasing enterprise growth," but you know you're just improving your business processes to allow you to grow your enterprise.

And increasing enterprise growth isn't going anywhere. It's projected to be #1 in 2014 also.

What about #2? Attracting and retaining new customers

Customers are demanding better service. Great processes can give your employees the information necessary to better service customers, so they stick around. What processes do you have in place to attract and retain customers? Are you going to ignore your process and throw more people at a customer?

Reducing enterprise costs is #3

Seriously? It's fairly well known that companies have cut resources ("to the bone" as one colleague puts it). Now is the time to reduce costs through better processes! Let your employees do more with the resources they have by delivering information to them or automating non-value add work.

Include your employees in the process planning. They want to be involved. And they've likely got great ideas for improving the process!

So, there you have it. "Improving business processes" has been kicked to the curb, and replaced by three others that are really just disguising reasons to have better processes.

The right organizations do understand BPM

Recently, I had the opportunity to meet with a group of people from a financial institution in Cincinnati. We discussed their current BPM (business process management) initiative and strategy. They get it!

To start, the project has executive support from both the business side of the organization and the IT side. They want it! And they have a backlog of processes that they want BPM to manage.

And the team implementing BPM has a strategy for implementing the processes. Included in that strategy is the ability to understand and monitor the processes after implementation. They'll extend control to the business while still providing the governance a BPM implementation needs.

I expect to hear great things from this organization in the future.

Cheers!

Adding a GridView Column of Checkboxes

The following post came in helpful today:

http://www.asp.net/data-access/tutorials/adding-a-gridview-column-of-checkboxes-vb

Cheers!

Register number with the DO NOT CALL list

I received the following from my company's HR department today:

"Cell phone numbers have recently been released to telemarketing companies, and so it’s possible that you may start to receive sales calls. To prevent this, register your cell phone number with the National DO NOT CALL list which blocks your number for five (5) years. Dial 1 888-382-1222 from the cell phone number that you want to have blocked. Visit https://www.donotcall.gov/default.aspx for more information."

I registered my two numbers using the website.

Cheers!

Snow days hurt when you only have a one-hour class

Sure, snow days are great. No class.

However, my BUS 102 class only meets once a week for one hour. So, we've got about 15 hours of class. Taking an hour away for a snow day hurts. The snow day was needed, because ice was covering everything in Butler Co. I did not want my students getting hurt.

Today, I'll be adjusting the schedule. I've been thinking about how to get all of the assignments in without driving my students crazy (or at least no more than I've already driven them!). And I think this snow day may help us all understand what "business writing" looks like. Who knows, maybe I'll use this same approach in the future even when there are no snow days.

Cheers!