importer_v2.0/PDAImport.cs

using System;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using importer.RestObjects;
using importer.ImportObjects;
using System.IO;
using ExcelDataReader;
using System.Data;

namespace importer
{
    public class PDAImport
    {
        private static string _serviceUrl;
        private static RESTService _rservice;
        private static List<Customer> _customers;
        private static List<Make> _makes;
        private static List<Product> _products;
        private static List<Model> _models;
        private static List<InspectionType> _inspectionTypes;
        private static List<Template> _templates;
        private static List<User> _users;
        private static Status _status;
        private static Priority _priority;
        private static Reason _reason;
        public static string ServiceUrl { get { return _serviceUrl; } set { _serviceUrl = value; } }
        public static Dictionary<string, int> InspectionType;
        public static DataTable ReadFile()
        {
            string filePath = Path.Combine(Global.StorageDir, "PDAFILE.XLS");
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader reader;
                if (Path.GetExtension(filePath).ToUpper() == ".XLS")
                {
                    //1.1 Reading from a binary Excel file ('97-2003 format; *.xls)
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else
                {
                    //1.2 Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                //reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
                var conf = new ExcelDataSetConfiguration
                {
                    ConfigureDataTable = _ => new ExcelDataTableConfiguration
                    {
                        UseHeaderRow = true
                    }
                };
                var dataSet = reader.AsDataSet(conf);
                return dataSet.Tables[0];
            }
        }
        //Test Comment
        public static List<string> DistinctWorkOrders(DataTable data)
        {
            List<string> list = new List<string>();

            var info = (from row in data.AsEnumerable()
                        where (row.Field<string>("Segment Number") == "02" ||
                            row.Field<string>("Segment Number") == "03") &&
                            !string.IsNullOrEmpty(row.Field<string>("Service Tech"))
                        select row.Field<string>("DBS Workorder Number")).Distinct();
            list.AddRange(info);

            return list;
        }

        public static void BuildDefaults()
        {
            if (Global.Settings.Remote)
                _serviceUrl = Global.Settings.Prelub.RemoteService;
            else _serviceUrl = Global.Settings.Prelub.LocalService;
            _rservice = new RESTService();
            _customers = _rservice.RestGet<Customer>("customers.svc");
            _makes = _rservice.RestGet<Make>("makes.svc");
            _products = _rservice.RestGet<Product>("products.svc");
            _models = _rservice.RestGet<Model>("models.svc");
            _inspectionTypes = _rservice.RestGet<InspectionType>("inspectionTypes.svc");
            _templates = _rservice.RestGet<Template>("templates.svc");
            _users = _rservice.RestGet<User>("users.svc");
            _status = new Status();
            _status.ID = 1;
            _status.Name = "Open";
            _priority = new Priority();
            _priority.ID = 3;
            _priority.Name = "Medium";
            _reason = new Reason();
            _reason.ID = 9;
            _reason.Name = "Cust. Request";
            _reason.IsActive = true;
            _reason.CreateDate = new DateTime(2013, 10, 1, 9, 23, 7);
            _reason.CreateUserID = -1;
            _reason.UpdateDate = new DateTime(2013, 10, 1, 9, 23, 7);
            _reason.UpdateUserID = -1;

        }

    }
}