WEBUI/WMS.BZServices/ImportDataOrExcelService.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using wms.sqlsugar.model.fj;
using WMS.BZSqlSugar;
using WMS.Info;
using WMS.Info.Models;
using WMS.Util;

namespace WMS.BZServices
{
    public class DBCells
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName { get; set; }
        /// <summary>
        /// 字段名
        /// </summary>
        public string CelName { get; set; }
        /// <summary>
        /// 字段说明
        /// </summary>
        public string CelMemo { get; set; }
        /// <summary>
        /// 字段类型
        /// </summary>
        public string CelType { get; set; }
        /// <summary>
        /// 字段是否允许未空
        /// </summary>
        public string CelIsNull { get; set; }
        /// <summary>
        /// 字段默认值
        /// </summary>
        public string CelDefualVal { get; set; }
        /// <summary>
        /// 是否为配置项
        /// </summary>
        public bool IsConfig { get; set; }
        /// <summary>
        /// Execl名称
        /// </summary>
        public string ExeclName { get; set; }
    }
    public class DBTableCell
    {
        public string BusType { get; set; } // 业务类型
        public string TableType { get; set; }  // 表类型
        public string TablesName { get; set; } // 表名
        public string CellsName { get; set; }  // 列名
        public string CellsMemo { get; set; }  // 备注
        public string CellsType { get; set; }  // 字段类型
        public string CellsNULLABLE { get; set; }     // 是否允许未空
    }
    public class ImportDataOrExcelService
    {
        private readonly Repository<BillMachinfo> _billMachinfoRepository;
        private readonly Repository<BaseWarehouse> _baseWarehouseRepository;
        private readonly Repository<BaseWarearea> _baseWareareaRepository;

        public ImportDataOrExcelService(Repository<BillMachinfo> billMachinfoRepository, Repository<BaseWarehouse> baseWarehouseRepository, Repository<BaseWarearea> baseWareareaRepository)
        {
            _billMachinfoRepository = billMachinfoRepository;
            _baseWarehouseRepository = baseWarehouseRepository;
            _baseWareareaRepository = baseWareareaRepository;
        }

        public JsonExecl ImportBillMatInfo(DataTable execlDt, int busType, string loginName)
        {
            JsonExecl json = new JsonExecl();
            json.Type = 0;
            string sql = "";

            // 保存需要操作的表名
            List<string> LstTable = new List<string>();
            // 保存数据库表字段信息
            List<DBTableCell> lstHeat = new List<DBTableCell>();
            // 保存导出的数据
            DataTable ImportDT = new DataTable();
            try
            {
                if (busType.ToString() != "100")
                    throw new Exception("单据类型不正确");

                if (execlDt == null)
                    throw new Exception("数据源不能为null");
                if (execlDt.Rows.Count <= 0)
                    throw new Exception("数据源数据行数必须大于0");
                // 获取源数据
                DataTable dtExeclSouse = execlDt;
                var warehouselist = _baseWarehouseRepository.Queryable().ToList();
                var warearealist = _baseWareareaRepository.Queryable().ToList();
                List<MachInfoExcelModel> bills = dtExeclSouse.ToList<MachInfoExcelModel>(rowMapper: new ColumnAttributeMapper<MachInfoExcelModel>());
                var Machinfos = _billMachinfoRepository.Queryable().ToList();
                List<BillMachinfo> BillMachinfolists = new List<BillMachinfo>();
                foreach (var item in bills)
                {
                    long? warehouseareaid = 0;
                    var warehouse = warehouselist.FirstOrDefault(o => o.Code == (item.WearhouseNo + item.NS));
                    if (warehouse != null)
                    {
                        warehouseareaid = warearealist.FirstOrDefault(o => o.WarehouseId == warehouse.Id)?.Id;
                    }
                    var nos = item.No.Split(' ');
                    if (nos.Length > 1)
                    {
                        var first = Regex.Replace(nos[0], @"[^0-9]+", "");
                        var sec = Regex.Replace(nos[1], @"[^0-9]+", "");
                        var Station = Int32.Parse(first) < Int32.Parse(sec)
                            ? item.WearhouseNo + item.NS + nos[0].Substring(1)
                            : item.WearhouseNo + item.NS + nos[1].Substring(1);
                        if (!Machinfos.Any(o => o.MachNo.ToLower() == nos[0].ToLower().Trim()))
                        {
                            BillMachinfolists.Add(new BillMachinfo()
                            {
                                WarehouseId = warehouse == null ? 0 : warehouse.Id,
                                WarehouseCode = warehouse == null ? "" : warehouse.Code,
                                WareAreaId = warehouseareaid ?? 0,
                                Name = "",
                                GrpCode = "",
                                Direction = "",
                                BillCode = "",
                                WorkOrder = "",
                                SetGrpCode = "",
                                ProBillCode = "",
                                ProWorkOrder = "",
                                ProSetGrpCode = "",
                                MacTypeCode = "",
                                MacTypeName = "",
                                MachNo = nos[0],
                                AgvPoint = item.AgvPoint,
                                Station = Station,
                                WareDirect = item.NS,
                                IsStop = 0,
                                AddWho = loginName,
                                EditWho = loginName,
                                AddTime = DateTime.Now,
                                EditTime = DateTime.Now,
                            }) ;
                        }
                        if (!Machinfos.Any(o => o.MachNo.ToLower() == nos[1].ToLower().Trim()))
                        {
                            BillMachinfolists.Add(new BillMachinfo()
                            {
                                WarehouseId = warehouse == null ? 0 : warehouse.Id,
                                WarehouseCode = warehouse == null ? "" : warehouse.Code,
                                WareAreaId = warehouseareaid ?? 0,
                                AgvPoint = item.AgvPoint,
                                Name = "",
                                GrpCode = "",
                                Direction = "",
                                BillCode = "",
                                WorkOrder = "",
                                SetGrpCode = "",
                                ProBillCode = "",
                                ProWorkOrder = "",
                                ProSetGrpCode = "",
                                MacTypeCode = "",
                                MacTypeName = "",
                                MachNo = nos[1],
                                Station = Station,
                                WareDirect = item.NS,
                                IsStop = 0,
                                AddWho = loginName,
                                EditWho = loginName,
                                AddTime = DateTime.Now,
                                EditTime = DateTime.Now,
                            });
                        }

                    }
                    else
                    {
                        if (!Machinfos.Any(o => o.MachNo.ToLower() == item.No.ToLower().Trim()))
                        {
                            BillMachinfolists.Add(new BillMachinfo()
                            {
                                WarehouseId = warehouse == null ? 0 : warehouse.Id,
                                WarehouseCode = warehouse == null ? "" : warehouse.Code,
                                WareAreaId = warehouseareaid ?? 0,
                                MachNo = item.No,
                                Name = "",
                                GrpCode="",
                                Direction="",
                                BillCode="",
                                WorkOrder = "",
                                SetGrpCode = "",
                                ProBillCode = "",
                                ProWorkOrder = "",
                                ProSetGrpCode = "",
                                MacTypeCode = "",
                                MacTypeName = "",
                                AgvPoint = item.AgvPoint,
                                Station = item.WearhouseNo + item.NS + item.No.Substring(1),
                                WareDirect = item.NS,
                                IsStop = 0,
                                AddWho = loginName,
                                EditWho = loginName,
                                AddTime = DateTime.Now,
                                EditTime = DateTime.Now,
                            });
                        }
                    }
                }
                if (BillMachinfolists.Any())
                {
                    _billMachinfoRepository.Insert(BillMachinfolists);
                }
                json.Type = 2;
                json.Message = "导入成功";
            }
            catch (Exception ex)
            {
                json.Type = 0;
                json.Message = ex.Message;
            }
            return json;
        }
    }

    public class MachInfoExcelModel
    {
        [Column("AGV地标码")]
        public string AgvPoint { get; set; }
        [Column("帘线机台号")]
        public string No { get; set; }
        [Column("分拣库")]
        public string WearhouseNo { get; set; }
        [Column("南北侧")]
        public string NS { get; set; }
        [Column("6位地码")]
        public string Six { get; set; }
    }
}