using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using log4net;

namespace Impexpdata.Core
{
    public class DataExchangeService
    {
        private static readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static readonly DataAccess _dataAccess = new DataAccess();

        /// <summary>
        /// Create customer in the database.
        /// </summary>
        /// <param name="customerInfo"></param>
        public int CreateCustomer(CustomerInfo customerInfo)
        {
            var cmd = new SqlCommand("Customers_Save") { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.AddWithValue("Id", customerInfo.Id);
            cmd.Parameters.AddWithValue("Name", customerInfo.Name);
            cmd.Parameters.AddWithValue("Notes", customerInfo.Notes);
            if (customerInfo.CodeId.HasValue)
                cmd.Parameters.AddWithValue("CodeId", customerInfo.CodeId.Value);
            cmd.Parameters.AddWithValue("DateImported", DateTime.Now);

            var resultParam = cmd.Parameters.Add("@NewItemID", SqlDbType.Int);
            resultParam.Direction = ParameterDirection.Output;
            _dataAccess.ExecuteSql(cmd);

            return Convert.ToInt32(resultParam.Value);
        }

        public void UpdateCustomerExportDate(CustomerInfo customerInfo)
        {
            var cmd = new SqlCommand("Customers_UpdateExportDate") { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.AddWithValue("CustomerId", customerInfo.Id);
            cmd.Parameters.AddWithValue("DateExported", customerInfo.DateExported.Value);
            _dataAccess.ExecuteSql(cmd);
        }

        public List<CustomerInfo> GetCustomersFromDatabase()
        {
            var items = new List<CustomerInfo>();
            var cmd = new SqlCommand("Customers_GetList") { CommandType = CommandType.StoredProcedure };

            foreach (DataRow dr in _dataAccess.FillDataTable(cmd).Rows)
            {
                var customer = new CustomerInfo();
                if (dr["Id"] != DBNull.Value)
                    customer.Id = Convert.ToInt32(dr["Id"]);
                customer.Name = dr["Name"].ToString();
                customer.Notes = dr["Notes"].ToString();
                if (dr["Code"] != DBNull.Value)
                {
                    customer.CodeId = Convert.ToInt32(dr["Code"]);
                    customer.CodeName = dr["CodeName"].ToString();
                }
                else
                    customer.CodeId = null;

                if (dr["DateImported"] != DBNull.Value)
                {
                    customer.DateImported = Convert.ToDateTime(dr["DateImported"]);
                }

                if (dr["DateExported"] != DBNull.Value)
                {
                    customer.DateExported = Convert.ToDateTime(dr["DateExported"]);
                }

                items.Add(customer);
            }

            return items;
        }

        public bool IsEmptyCSVLine(string line)
        {
            var dataItems = line.Split(Convert.ToChar(Properties.CSVDelimiter));

            return dataItems.Length <= 0 || dataItems.All(t => string.IsNullOrEmpty(t));
        }

        /// <summary>
        /// Check if CSV line is comment.
        /// </summary>
        /// <param name="line"></param>
        /// <returns></returns>
        public bool IsCommentLine(string line)
        {
            return line.StartsWith(Properties.CSVComment);
        }

        public bool IsCustomerExists(int customerId)
        {
            var cmd = new SqlCommand("Customers_LoadById") { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.AddWithValue("Id", customerId);
            cmd.Connection = _dataAccess.GetConn();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    if (reader["Id"] != DBNull.Value)
                        return true;
                }
            }

            return false;
        }

        /// <summary>
        /// Validate customer from import data.
        /// </summary>
        /// <param name="customerImportData"></param>
        /// <returns></returns>
        public bool IsImportCustomerValid(string customerImportData)
        {
            var data = customerImportData.Split(Convert.ToChar(Properties.CSVDelimiter));
            var errors = new List<string>();

            if (data.Length > 0)
            {
                var isCustomerId = int.TryParse(data[0], out var customerId);
                if (!isCustomerId)
                    errors.Add("Customer ID is invalid.");
            }
            if (data.Length > 1)
            {
                if (string.IsNullOrEmpty(data[1]))
                    errors.Add("Customer name is missing.");
            }
            else
            {
                errors.Add("Customer name is missing.");
            }

            if (errors.Count == 0)
                return true;

            // write errors log
            Log.Error("--------------------------------------");
            Log.ErrorFormat("Invalid data in customer: {0}", customerImportData);
            foreach (var msg in errors)
            {
                Log.Error(msg);
            }
            Log.Error("--------------------------------------");

            return false;
        }

        /// <summary>
        /// Retrieve data from CSV file.
        /// </summary>
        public List<CustomerInfo> ReadImportDataFile(string filePath)
        {
            var items = new List<CustomerInfo>();

            try
            {
                if (!File.Exists(filePath))
                {
                    throw new FileNotFoundException("Import data file not found.");
                }
            }
            catch (FileNotFoundException ex)
            {
                Log.Error(ex.Message);

                return null;
            }

            using (var sr = new StreamReader(filePath))
            {
                while (!sr.EndOfStream)
                {
                    try
                    {
                        string data = sr.ReadLine();
                        if (!string.IsNullOrEmpty(data) && !IsEmptyCSVLine(data) && !IsCommentLine(data))
                        {
                            if (IsImportCustomerValid(data))
                            {
                                // assume that customer's data validated and correct
                                var dataItems = data.Split(Convert.ToChar(Properties.CSVDelimiter));
                                var customerInfo = new CustomerInfo();

                                customerInfo.Id = Convert.ToInt32(dataItems[0]);
                                customerInfo.Name = dataItems[1];

                                if (dataItems.Length >= 3)
                                    customerInfo.Notes = dataItems[2];

                                if (dataItems.Length >= 4)
                                {
                                    var isCode = int.TryParse(dataItems[3], out var code);
                                    if (isCode)
                                        customerInfo.CodeId = code;
                                }

                                items.Add(customerInfo);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Log.Error(ex.Message);
                    }
                }
            }

            return items;
        }

        public int ImportData(string filePath)
        {
            var customers = ReadImportDataFile(filePath);

            if (customers != null && customers.Count > 0)
            {
                var importedCustomersCount = 0;
                foreach (var customer in customers)
                {
                    if (!IsCustomerExists(customer.Id))
                    {
                        var newId = CreateCustomer(customer);
                        if (newId > 0)
                        {
                            importedCustomersCount++;
                            Log.InfoFormat("New customer with database ID {0} has been imported.", newId);
                        }
                    }
                    else
                    {
                        Log.InfoFormat("Customer with ID {0} in the database is already exists.", customer.Id);
                    }
                }

                if (importedCustomersCount > 0)
                    Log.InfoFormat("{0} customers has been imported.", importedCustomersCount);
                else
                    Log.Info("0 customers was imported.");

                return importedCustomersCount;
            }

            if (customers == null)
                return -1;

            return 0;
        }

        public int ExportData(string filePath)
        {
            var fileContent = new StringBuilder();
            var customers = GetCustomersFromDatabase();
            
            foreach (var customer in customers)
            {
                var code = string.Empty;
                // verify that CodeId record exists in database table [Codes]
                if (string.IsNullOrEmpty(customer.CodeName) && customer.CodeId != null)
                {
                    // CodeId stored in the table [Customers] but not exists in table [Codes]
                    code = "!!! INVALID CODE !!!";
                }
                else
                    code = customer.CodeName;

                customer.DateExported = DateTime.Now;

                fileContent.AppendLine(string.Format("{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}",
                    customer.Id, Properties.CSVDelimiter,
                    customer.Name, Properties.CSVDelimiter,
                    customer.Notes, Properties.CSVDelimiter,
                    code, Properties.CSVDelimiter,
                    customer.DateImported, Properties.CSVDelimiter,
                    customer.DateExported));
            }

            if (fileContent.Length > 0)
            {
                try
                {
                    var a = Path.GetDirectoryName(filePath);
                    if (Path.IsPathRooted(filePath) && !Directory.Exists(Path.GetDirectoryName(filePath)))
                        Directory.CreateDirectory(Path.GetDirectoryName(filePath));

                    File.WriteAllText(filePath, fileContent.ToString());

                    // data exported successfully
                    foreach (var customerInfo in customers)
                    {
                        UpdateCustomerExportDate(customerInfo);
                    }

                    Log.InfoFormat("{0} customers has been exported.", customers.Count);

                    return customers.Count;
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message);
                }
            }

            return 0;
        }
    }
}

.NET Framework

Project Request

ProjectC

Vehicle Maintenance

Movie Navigator

Customers Exchange

ATM

.NET Core

Mail Daemon

Custom

Buns of code

PHP

Mediabox

Loan Castle

Content Management

Laravel

Phone Check

Frontend

Forex Wall

Business projects

Certification Center

Amerikas Auto

Pet projects

Geolocation Assistant

Web Dynamics

Blackball

Good old times

DOS Clock

BrainOut

Airport Administrator

Atomizer

Customers Exchange