Software Development For IMET Corporation
Senior Project II
Team: Sebastian Casas (Team Leader)
Primary Advisor: Dr Orlando Hernandez (TCNJ)
Secondary Advisor: Tom Krol (IMET)
Technical Advisor: Chintan Sutaria (CalcuQuote)
May 2017
Software Development for IMET Corporation
By: Sebastian Casas
A Senior Project Report Submitted in Fulfillment for the Degree of Bachelor of Science in Computer Engineering
May 10, 2017
Acknowledgements
I would like to express my deepest appreciation to everyone who helped me along the course of this project experience. First and foremost my primary Advisor, Dr Orlando Hernandez offered me the opportunity to work on his project and scheduled our weekly conference calls. He provided me with clarification and guidance for this project.
Furthermore I would also like to acknowledge with much appreciation the crucial role of my secondary project advisor, Tom Krol, who gave the permission to access all the required equipment and the information and hardware necessary to complete the project. Last but not least, many thanks go to my technical advisor who provided me with an overview of the software required to complete the project. He answered many questions involving the implementation of the project and was my first point of contact when I ran into issues with software or when I needed help with how to approach something.
Abstract
The goal of this project was to develop software that will improve IMET’s inventory control system, and hopefully at the same time optimize the company’s profit. There is no current procedure set to handle the extra parts in their stock. The project consists of writing an application in C# to read through a CSV file of the extra parts in the inventory and those available for resale. Using the manufacturers’ parts numbers for a part, the program then uses the Digi-Key Application Programming Interface to obtain more information on a specific part, such as its picture, description and cost. Once all of the information is obtained, the data is placed onto a server that uses Magento E-Commerce to publish the part on IMET’s website (Design Notes) and make it available for purchase by the public.
Keywords: Inventory Control, Database Management, C#, E-Commerce, Magento, Digi-Key, API
Graphs and Illustrations
Figure 1. Server Diagram- Shows how the servers will work together for the SQL implementation
Figure 2. Magento E-Commerce Architecture Diagram- Explains how Magento works
Figure 3. OAuth2.0 Framework Diagram- Shows the steps required to obtain an access token from Digi-Key
Introduction
This project was built for IMET Corporation. IMET Corporation is an award-winning resource for contract manufacturing, electronics engineering and product development for medical, industrial, military, and consumer products. They are located in Southampton, Pennsylvania. Their services include Product Development, Electronics Engineering, Contract Manufacturing, and Printed Circuit Board Assembly.
IMET is currently in the process of redesigning its retail website, Design Notes. They are making updates to their ecommerce platform. This project will be implemented on the website, once it launches. Currently, IMET has a lot of parts in their inventory that they are not using for manufacturing. These items are just sitting there, which is not the most efficient method to go about it. This project will improve the efficiency of the company by taking those excess items in the inventory and making them available on the Design Notes website so that people can view and purchase those parts. This effort requires obtaining the available excess parts in the inventory, using the Digi-Key Application Programming Interface (API) to obtain more information on the part such as picture and descriptions, and then pushing the data onto a server that has Magento E- Commerce installed on it. From there, Magento E-Commerce automatically pushes all of the data to the website. Magento E-Commerce offers a simple open-source ecommerce platform for online businesses and retailers
Specifications: Software Requirements Specifications
- Intended Audience and Reading Suggestion
All documentation, hardware, and software that was used and created for this project will be handed off to Tom Krol, of IMET Corporation who will have complete ownership of it once he is handed it. Any remaining parts needed for completion will be completed by IMETs development team. The project itself is going to be available on IMET’s website Design Notes, so anyone will be able to use it.
In order to understand the functionality of this project, familiarity with database management is needed to understand the Structured Query Language (SQL) implementation. In order to understand how part information is retrieved from Digi-Key, family with using API’s is imperative. All code is commented, so that people who may not be familiar with the languages will have knowledge of what is going on within the program.
- Product Scope
The idea behind this project is to improve the efficiency of IMET’s inventory control by making the excess items appear on the Design Notes website. From a high level perspective, the steps required to accomplish this are to obtain the items in the inventory, locate the items that are not being used, use Digi-Key’s API to pull the required information for each part, and finally push all of the data to the Magento server.
- Operating Environment
I will be discussing the operating environments in which the software will operate, including the hardware platform, operating system and versions, and any other software components or applications with which it must peacefully coexist.
First of all, the SQL implementation required access to IMET’s EXACTMAX database which contains all of items in their inventory. This database was loaded on Microsoft SQL Server 2014. The use of operating systems used was limited to Windows for this part of the project, since that is the only operating system that supports Microsoft SQL. For the C# implementation, Microsoft Visual Studio 2015 was used to develop, run, and test all of the code.
The Digi-Key API access was attempted by writing a program in C# to obtain proper authorization and access to the API by requesting and obtaining access tokens. To do this, the OpenAuthorization 2.0 security framework was implemented. The code for this is included in the appendix section of the report
- System Feature 1
The first feature of this project is to obtain the extra items from the inventory that will be moved to the website. This project provides two methods to obtain those parts. The first uses SQL to query to the required tables in the EXACTMAX database. The parts available for sale were all stored at a specific stock location, so the goal was to ultimately end at that location. Once all of the queries were made, a stored procedure was created to simplify the amount of queries required to obtain the results. The second method involves IMET moving their inventory to QuickBooks. From QuickBooks, a CSV file is generated that contains all of the available parts. For this method, a C# program was created to convert the contents of the CSV file to a standard API output format. In this case, XML was chosen as the API output format. The code is in the appendix section.
- System Feature 2
The next feature involves establishing a connection to the API to make the required calls. In order to establish the connection a couple of steps were required. The first was to create an account with DigiKey. Once an account was created, then the application that was built had to be registered to the account. Once the application was created, two important keys are obtained. One is the Client Secret, and the other is the Client ID. These two features are passed along with the request to obtain an access token. The code for this attempt is included in the appendix section.
- System Feature 3
The last feature of this project is to move the parts to Design Notes. In order to accomplish this, the data had to be pushed to the Magento server. In order to access the server, Chintan Sutaria, the technical advisor of this project, created a copy of his Amazon Web Services (AWS) EC2 instance. Once the instance was running, I was provided with a PEM key. In order to access the server, I used the terminal on my computer to input a command string that connected me to the server. This involved making sure that the PEM key that I was provided was stored in the correct directory. Once the data is loaded to this server, the items will appear on the website.
- Security Requirements
This this project has access to IMET’s inventory, the information that was accessed was confidential, and this data needed to remain safe throughout the implementation of the project. The information located in the database is already safe, since the access to the database is restricted and only authorized users have access to the connection. If this method were chosen for the implementation though, the use of the C# program would have still been needed because using the C# application requires the use of a prepared statement. A prepared statement in C# executes a stored procedure from SQL. The reason this layered protection is needed is to avoid having the application run directly from the SQL server, since it contains information on all of the databases and potentially creates a greater issue if that information were to be accessed by an unauthorized user. The security requirements for accessing the API were all handled by the Digi-Key OpenAuthorization framework, which is discussed in the report.
- Software Quality Attributes
Since this project is made up of software that will be used in real-life applications, the design and implementation needed to keep software quality attributes in mind. The software quality attributes for this project are listed in this section. The first one discussed is conceptual integrity. The overall design shall be consistent and coherent (including any coding) and the web page will be organized efficiently to allow for optimal browsing experience. This part is taken care of by Magento.) The next attribute is maintainability. The system will be accepting of changes that may occur as a result of adding functionality or fixing errors and will make it easier for future updates. This attribute came in handy when changes needed to be made to the project. For example when IMET decided to move away from the database it was using before, this attribute made it easy to implement another solution. Other important attributes are reliability and availability. It is expected that the information obtained will be accurate and will be available upon request of the user.
- Obstacles
This section describes obstacles that were encountered throughout the first and second semester of the project and the steps that were taken to find a solution. First semester obstacles will be described first. The biggest obstacle the first semester was setting up and getting access to Microsoft SQL Server. Since my personal computer uses OS X operating system, I tried to launch an instance of AWS EC2 to run Windows SQL Server. Unfortunately, because I could only use the free tier version, there were many restrictions that made launching the appropriate instance possible, so it was decided to move away from this option. The next option was to use my senior project budget to purchase a laptop online. Since my budget was limited because there was only one person on the team, I did not have a lot of flexibility in terms of choosing a laptop to buy. I purchased one online that was in the budget range. When I received the laptop I realized that it did not meet the hardware requirements to install Windows SQL Server, so it had to be returned. At this point, the end of the semester was approaching, so IMET stepped in and was generous enough to let me borrow one of their laptops. At this point, I was able to access and connect to the database.
The second semester came around and when we had our first conference call, I was informed that IMET was in the process of undergoing changes that would affect my project. IMET decided to move away from the database that I was querying to, so I was told that I needed to wait to see what IMET was going to do in terms of where they keep their inventory. It was decided that they would move their inventory to QuickBooks, so a new implementation was needed for this method of accessing the inventory. This greatly affected the work that was done the first semester, since everything had to be redone up to that point for the new implementation.
Once I determined the steps needed to carry out the implementation, I found myself struggling with creating the part of the application that requests a token from DigiKey using OAuth 2.0. This is a very complex procedure, and the tutorials available are complex and hard to follow for people who are not experienced with it. I did as much as I could as time allowed, and got to a reasonable place. All of the code for the implementation is included in this report and was commented.
- Revisions
| Date | Description |
| 9/15/16 | First Draft |
| 9/25/16 | Revision 1- Removed unnecessary sections and added more details where needed |
| 10/25/16 | Updates system features/defined vocabulary |
| 11/13/16 | Adjusted project requirements, introduced AWS |
| 11/26/16 | First Semester rough draft, added obstacles, second semester goals |
| 1/30/17 | Updated project requirements- waiting for IMET changes |
| 3/5/17 | Added C# implementation, Digi Key API |
| 4/10/17 | Updated Final System Features |
| 5/4/17 | Final Draft Complete |
Chapter 1: Background and Role
IMET corporation is an electronics manufacturing company located in Southampton, Pennsylvania. I decided to choose this project because I was really interested in working mostly with software. Additionally, I had no prior experience with SQL or C# so I figured that this would be a great opportunity for me to increase my coding experience. This was a project consisting of only myself on the team, however there were several advisors assigned to the project to guide me through the process. One advisor was Dr Hernandez, my secondary advisor was Tom Krol from IMET, and lastly my technical advisor was Chintan Sutaria from CalcuQuote. The team planned a startup meeting to gather the exact requirements and expectations of the project.
Originally, this project was supposed to be a quoting system to quote orders from their website, but it was determined that IMET needed this functionality as soon as possible, so they went through another source to obtain this. The requirements then changed to moving the extra parts in their inventory to Design Notes so that people could purchase the parts they were not using. Chintan provided a rough schedule that I should follow which included a general idea of the steps required for the project.
Since I am the only student involved in this project, I claim full responsibility for the full life cycle of this project from gathering requirements to delivery. Additionally, I will be keeping creating and maintaining the documentation for this project including all reports and presentations. Once the semester is over, all software and the corresponding documentation will be handed off to Tom from IMET, and Chintan from CalcuQuote. Any remaining gaps in the project will be completed by Chintan’s development team in order to ensure that the project will function on the new website once it launches.
Chapter 2: Conclusion
Overall, this project was fun to work on. I have learned many things that I did not know previously, especially working with databases and two new coding languages. There were a couple delays in the first semester that were primarily caused by the Windows restriction. I tried to use my project budget to order a laptop, but it did not meet the hardware requirements to install SQL on it, so it had to be returned. Amazon AWS was looked into to set up an instance of Windows on my OSX machine, but the free tier along with security issues were delaying the project even more. Fortunately, IMET was able to provide me with a laptop at this point and at this point, the pace was picked up. The second semester obstacles were caused as a result of running out of time, since I had to change the project requirements the second semester to make up for the changes IMET was going through. The final goal of this project, is to get all of the items available for sale from IMET’s database to Design Notes. This involved querying IMET’s database to the correct stock location, then using the DigiKey API service to obtain the information needed for the parts that are not in IMET’s database. The remaining steps to get an access token will be carried out by IMET. The motivation behind this project was to provide IMET Corporation with a better way to manage the extra items in their inventory that they are not using. My approach to this solution provides two methods to obtain the available parts (SQL stored procedure and CSV file). The implementation of the OAuth 2.0 authorization framework was attempted in C#. The Design Notes website is currently not live, because it is being redesigned. Once a valid access token and refresh token has been generated, the required calls will be made to the API to obtain the picture and cost of each item. This data can then be passed to the Magento Server, and Magento will populate the data to the website. Once the website relaunches, people will be presented with the option to purchase the extra items in IMET’s inventory.
References
“C#”, “OAuth 2.0 C#”, “CSV to XML C#” Stack Overflow.Web. 03 Mar. 2017.
“C# Documentation.” Welcome to Visual Studio 2015. Microsoft, Web. 10 Apr. 2017.
Digi-Key OAuth 2.0 Protocol Tutorial, Reviewed 4/2017. https://api-portal.digikey.com/app_overview
Magento E-Commerce Tutorial, Reviewed 4/2107. https://www.tutorialspoint.com/magento/magento_architecture.htm
Appendix A. Team Members
Name: Sebastian Casas (Team Leader)
Biography: I am a Computer Engineering major at TCNJ. I am originally from Woodbridge NJ, and I attended St Joseph High School in Metuchen, NJ. I always knew I wanted to be some type of engineer in the future and I love technology, so I decided to become a computer engineering major. Throughout my experience at TCNJ, I gained many leadership opportunities and hope to someday, pursue an advanced degree in either Systems Engineering or Software Engineering.
The developer of this software project is:
Sebastian Casas, who can be reached via e-mail at: casass1@tcnj.edu
The primary advisor of this project is:
Orlando Hernandez, who can be reached via email at hernande@tcnj.edu
Supporting advisors for this project are:
Tom Krol, tkrol@imetcorporation.com
Chintan Sutaria, csutaria@calcuquote.com
Appendix B. Gantt Chart
Appendix C: Budget Chart
No money from the budget was used for this project.
Appendix D. SQL Code
USE EXACTMAXIMET
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Sebastian Casas>
— Create date: <1/31/2017>
— Description: <Returns items from stock location with MPN>
— =============================================
CREATE PROCEDURE [Return_ItemsForSale]
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Procdure to be executed:
Select V.*
From Part_Stock S
Join Part_Vendor V ON V.PRTNUM_07 = S.PRTNUM_06
WHERE STK_06 Like ‘%CON1C7C%’
END
GO
Appendix E. Code to Convert CSV to XML
using System.IO;
using System.Linq;
using System.Xml.Linq;
using System.Xml; // File headers
namespace Convert_CsvXml
{
class Convert
{
static void Main()
{
string[] lines = File.ReadAllLines(@”/Users/sebastiancasas/Projects/Convert_CsvXML/output.csv”); //Read CSV file
XElement xml = new XElement(“Part”,
from str in lines
let columns1 = str.Split(‘,’) // Organize output file according to these columns in the CSV file
select new XElement(“Part”,
new XElement(“Manufacturer”, columns1[0]),
new XElement(“MPN”, columns1[1]),
new XElement(“Description”, columns1[2]),
new XElement(“Quantity_on_Hand”, columns1[3]),
new XElement(“UM”, columns1[4]),
new XElement(“Cost”, columns1[5])
)
);
xml.Save(@”C:\XMLOutputFile.xml”); // Save contents to XML file stored on C:
}
}
}
Appendix F. GetToken Code
using System;
using System.Linq;
using System.Text;
namespace TokenRequestAttempt
{
class Tests
{
//These are the required classes for the jSon Object
public class RootObject //This is the main object
{
public Status status { get; set; }
public List<Datum> data { get; set; }
public pagination pagination { get; set; }
}
public class Status //This is the jSON status
{
public bool error { get; set; }
public int code { get; set; }
public string type { get; set; }
public string message { get; set; }
}
public class Datum //This is the actual data that is returned
{
public string access_token { get; set; }
public string created_at { get; set; }
public int expires_in { get; set; }
public string refresh_token { get; set; }
public string token_type { get; set; }
public int account_id { get; set; }
public int id { get; set; }
public string email { get; set; }
public custom_attributes custom_attributes { get; set; }
}
public void getToken()
{
//Reuse of this value will be necessary among all calls
//Use this method first because none of the other calls can work without your access toekn
RestSharp.Deserializers.JsonDeserializer deserial = new RestSharp.Deserializers.JsonDeserializer();
var client = new RestClient(“https:/oauth2/token”);
var request = new RestRequest(Method.POST);
request.AddHeader(“cache-control”, “no-cache”);
request.AddHeader(“content-type”, “application/json”);
request.RequestFormat = DataFormat.Json;
request.AddHeader(“authorization”, “client_id:, client_secret:);
request.AddParameter(“application/json”, “{\n\”grant_type\”:\”client_credentials\”\n}”, ParameterType.RequestBody);
IRestResponse response = client.Execute(request);
RootObject returnData = deserial.Deserialize<RootObject>(response);
if (returnData.data[0].access_token != null)
{
access = returnData.data[0].access_token; //This correctly gets the Access Token.
}
}
public Datum getSpecificUser(string email)
{
//Simple way to get a user’s information based on the email
//Email is passed as a variable as an example of how to use this method
List<Datum> OneLoginList = new List<Datum>();
RestSharp.Deserializers.JsonDeserializer deserial = new RestSharp.Deserializers.JsonDeserializer();
var client = new RestClient(“https:” + email + “”);
var request = new RestRequest(Method.GET);
request.AddHeader(“cache-control”, “no-cache”);
request.AddHeader(“authorization”, “bearer:” + access + “”);
IRestResponse response = client.Execute(request);
RootObject returnData = deserial.Deserialize<RootObject>(response);
Datum record = returnData.data[0];
return record;
}
protected void anyLeft()
{
RestSharp.Deserializers.JsonDeserializer deserial = new RestSharp.Deserializers.JsonDeserializer();
var client = new RestClient(“https:”);
var request = new RestRequest(Method.GET);
request.AddHeader(“cache-control”, “no-cache”);
request.AddHeader(“authorization”, “bearer:” + access + “”);
IRestResponse response = client.Execute(request);
RootObject returnData = deserial.Deserialize<RootObject>(response);
var name = returnData.data;
string content = response.Content;
bool isthere = content.Contains(“X-RateLimit-Remaining”);
dynamic cc = JsonConvert.DeserializeObject<dynamic>(content);
var RemainingCalls = cc[“data”][“X-RateLimit-Remaining”].Value;
var TimeToReset = cc[“data”][“X-RateLimit-Reset”].Value;
if (RemainingCalls.ToString() == “0”)
{
int TimeToWait = Convert.ToInt32(TimeToReset) * 1000;
System.Threading.Thread.Sleep(TimeToWait);
}
}
} } }