ViettechReport/SuperCms/Controllers/SupportController.cs

using DocumentFormat.OpenXml.Packaging;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.StaticFiles;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SuperCms.Extensions;
using SuperCms.Models;
using Syncfusion.DocIO;
using Syncfusion.DocIO.DLS;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using BorderStyle = Syncfusion.DocIO.DLS.BorderStyle;
using HorizontalAlignment = Syncfusion.DocIO.DLS.HorizontalAlignment;

namespace SuperCms.Controllers
{
    public class SupportController : BaseController
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(Program));
        private IHostingEnvironment _hostingEnvironment;
        OracleController oracle;

        public SupportController(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }

        public IActionResult Index(String tableType, String actionFile)
        {
            try
            {
                if (!CheckAuthToken())
                {
                    return Redirect("/Home/Login");
                }
                //UtilsController.PaymentConverter(null);

                SupportViewModel model = new SupportViewModel();
                model.typeTable = tableType != null ? tableType : UtilsController.Constant.PAYMENT_FILE;
                model.actionFile = actionFile != null ? actionFile : UtilsController.Constant.SHOW_FILE;

                // get file to show


                return View("Index", model);
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }

        public ActionResult Download(String tableType)
        {
            try
            {
                String zipPath = "";
                if (tableType == UtilsController.Constant.PAYMENT_FILE)
                {
                    string startPath = "./Files/Payments/" + DateTime.Now.ToString("dd_MM_yyyy");
                    zipPath = "./Files/Payments/" + "Payments_" + DateTime.Now.ToString("dd_MM_yyyy") + ".zip";
                    if (UtilsController.CheckFile(zipPath))
                    {
                        // delete file
                        UtilsController.DeleteFile(zipPath);
                    }
                    ZipFile.CreateFromDirectory(startPath, zipPath);
                }
                else
                {
                    string startPath = "./Files/Invoices/" + DateTime.Now.ToString("dd_MM_yyyy");
                    zipPath = "./Files/Invoices/" + "Invoices_" + DateTime.Now.ToString("dd_MM_yyyy") + ".zip";
                    if (UtilsController.CheckFile(zipPath))
                    {
                        // delete file
                        UtilsController.DeleteFile(zipPath);
                    }
                    ZipFile.CreateFromDirectory(startPath, zipPath);
                }

                var provider = new FileExtensionContentTypeProvider();
                if (!provider.TryGetContentType(zipPath, out var contentType))
                {
                    contentType = "application/octet-stream";
                }

                var bytes = UtilsController.GetFileByte(zipPath);
                return File(bytes, contentType, Path.GetFileName(zipPath));

            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return null;
            }
        }

        [ValidateAntiForgeryToken]
        public ActionResult UploadAction(String typeTable)
        {
            try
            {
                if (!CheckAuthToken())
                {
                    return Redirect("/Home/Login");
                }
                IFormFile file = Request.Form.Files[0];
                string folderName = "UploadExcel";
                string webRootPath = _hostingEnvironment.WebRootPath;
                string newPath = Path.Combine(webRootPath, folderName);
                StringBuilder sb = new StringBuilder();
                if (!Directory.Exists(newPath))
                {
                    Directory.CreateDirectory(newPath);
                }
                if (file.Length > 0)
                {
                    string sFileExtension = Path.GetExtension(file.FileName).ToLower();
                    ISheet sheet;
                    string fullPath = Path.Combine(newPath, file.FileName);
                    using (var stream = new FileStream(fullPath, FileMode.Create))
                    {
                        file.CopyTo(stream);
                        stream.Position = 0;
                        if (sFileExtension == ".xls")
                        {
                            HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats  
                            sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook  
                        }
                        else
                        {
                            XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format  
                            sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook   
                        }
                        IRow headerRow = sheet.GetRow(0); //Get Header Row
                        int cellCount = headerRow.LastCellNum;
                        sb.Append("<table class='table table-bordered'><tr>");
                        for (int j = 0; j < cellCount; j++)
                        {
                            NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                            if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                            sb.Append("<th>" + cell.ToString() + "</th>");
                        }
                        sb.Append("</tr>");
                        sb.AppendLine("<tr>");

                        List<Contract> contracts = new List<Contract>();

                        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null)
                            {
                                continue;
                            }
                            if (row.Cells.All(d => d.CellType == CellType.Blank))
                            {
                                continue;
                            };
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
                                }
                                else
                                {
                                    sb.Append("<td> </td>");
                                }
                            }
                            sb.AppendLine("</tr>");

                            Contract contract = new Contract();
                            contract.id = row.GetCell(0) != null ? row.GetCell(0).ToString() : "";
                            contract.telco = row.GetCell(13) != null ? row.GetCell(13).ToString() : "";
                            contract.syntaxTelco = row.GetCell(2) != null ? row.GetCell(2).ToString() : "";
                            contract.company = row.GetCell(1) != null ? row.GetCell(1).ToString() : "";
                            contract.service = row.GetCell(3) != null ? row.GetCell(3).ToString() : "";
                            contract.contractCode = row.GetCell(4) != null ? row.GetCell(4).ToString() : "";
                            contract.address = row.GetCell(8) != null ? row.GetCell(8).ToString() : "";
                            contract.account = row.GetCell(9) != null ? row.GetCell(9).ToString() : "";
                            contract.swiftCode = row.GetCell(11) != null ? row.GetCell(11).ToString() : "";
                            contract.time = row.GetCell(5) != null ? row.GetCell(5).ToString() : "";
                            contract.money = row.GetCell(6) != null ? row.GetCell(6).ToString() : "";
                            contract.beneficiary = row.GetCell(7) != null ? row.GetCell(7).ToString() : "";
                            contract.syntaxConpany = row.GetCell(12) != null ? row.GetCell(12).ToString() : "";
                            contract.bank = row.GetCell(10) != null ? row.GetCell(10).ToString() : "";
                            contract.addressTelco = row.GetCell(14) != null ? row.GetCell(14).ToString() : "";
                            contract.taxCode = row.GetCell(15) != null ? row.GetCell(15).ToString() : "";

                            contracts.Add(contract);

                        }
                        HttpContext.Session.SetComplexData("contractsImport", contracts);

                        sb.Append("</table>");
                    }
                }
                return this.Content(sb.ToString());
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }

        [ValidateAntiForgeryToken]
        public JsonResult ImportAction(String typeTable, String timeNow)
        {
            if (!CheckAuthToken())
            {
                return Json(new
                {
                    code = UtilsController.Constant.ERROR,
                    href = "/Home/Login"
                });
            }
            try
            {
                bool check = false;
                List<Contract> contracts = HttpContext.Session.GetComplexData<List<Contract>>("contractsImport");
                List<Contract> listITL = new List<Contract>();
                for (int i = 0; i < contracts.Count; i++)
                {

                    if (typeTable == UtilsController.Constant.PAYMENT_FILE)
                    {
                        // following by telco
                        if (contracts[i].syntaxTelco == "NATCOM")
                        {
                            check = UtilsController.PaymentNatcomAllDumpt(contracts[i]);
                        }
                        else
                        {
                            check = UtilsController.PaymentDumpt(contracts[i]);
                        }
                    }
                    else
                    {
                        if (contracts[i].syntaxTelco == "NATCOM")
                        {
                            check = UtilsController.InvoiceNatcomInterlandDumpt(contracts[i]);
                        }
                        else
                        {
                            if (contracts[i].syntaxConpany == "GTS")
                            {
                                check = UtilsController.InvoiceGTSDumpt(contracts[i]);
                            }
                            else if (contracts[i].syntaxConpany == "VTECH" || contracts[i].syntaxConpany == "VIETTECH")
                            {
                                check = UtilsController.InvoiceVTECHDumpt(contracts[i]);
                            }
                            else if (contracts[i].syntaxConpany == "ITL" || contracts[i].syntaxConpany == "INTERLAND")
                            {
                                check = UtilsController.InvoiceITLDumpt(contracts[i]);
                            }
                            else
                            {
                                log.Error("No match syntax company " + contracts[i].syntaxConpany);
                            }
                        }
                    }
                }

                DateTime startTimeNowDateTime = DateTime.Parse(timeNow, CultureInfo.InvariantCulture);
                DateTime startdate = new DateTime(startTimeNowDateTime.Year, startTimeNowDateTime.Month, startTimeNowDateTime.Day, 0, 0, 0);
                DateTime enddate = new DateTime(startTimeNowDateTime.Year, startTimeNowDateTime.Month, startTimeNowDateTime.Day, 23, 59, 59);

                ////convert a doc file to html
                //UtilsController.ReadDocFileToHtml();


                HttpContext.Session.RemoveComplexData("contractsImport");

                return check ? Json(new
                {
                    code = UtilsController.Constant.SUCCESS,
                    message = "Import Successful",
                    href = "/Import"
                }) : Json(new
                {
                    code = UtilsController.Constant.ERROR,
                    message = "Import Fails",
                    href = "/Import"
                });
            }
            catch (Exception ex)
            {
                log.Error("Exp: " + ex);
            }
            return Json(new
            {
                code = UtilsController.Constant.ERROR,
                message = "Import Fails",
                href = "/Import"
            });
        }

        [ValidateAntiForgeryToken]
        public IActionResult DownloadTemplate()
        {
            String file = "./Files/Template/template.xlsx";
            var provider = new FileExtensionContentTypeProvider();
            if (!provider.TryGetContentType(file, out var contentType))
            {
                contentType = "application/octet-stream";
            }

            var bytes = UtilsController.GetFileByte(file);
            return File(bytes, contentType, Path.GetFileName(file));
        }
    }
}