예제 #1
0
        private void DoSales
        (
            ExcelWorksheet worksheet,
            Dictionary <string, string> colDic,
            MasterMap mMap,
            DTO.ImportPost ip
        )
        {
            int iStart = worksheet.Dimension.Start.Row;

            iStart = 2;

            int iEnd = GetLastRow(worksheet);

            if (iEnd > 0)
            {
                for (int i = iStart; i <= iEnd; i++)
                {
                    var row = worksheet.Row(i);

                    string warnings = "";
                    string requires = "";

                    var rec = new Sale();

                    foreach (string key in colDic.Keys)
                    {
                        try
                        {
                            var els = colDic[key].Split('|');

                            int ix = int.Parse(els[0]);

                            string val = worksheet.GetValue(i, ix).ToString().Trim();

                            string pattern = els[1];

                            if (!String.IsNullOrEmpty(pattern))
                            {
                                Regex regex = new Regex(pattern);
                                Match match = regex.Match(val);
                                if (match.Success)
                                {
                                    val = match.Groups[1].Value;
                                }
                            }

                            var colSpecs = mMap.Columns.First(o => o.ColumnName == key);

                            if (!String.IsNullOrEmpty(val))
                            {
                                dynamic realVal = null;

                                switch (colSpecs.ColumnDataType)
                                {
                                case 1:
                                    realVal = val;
                                    break;

                                case 2:
                                    val = Regex.Replace(val, "[^0-9.]", "");
                                    var ppos = val.IndexOf('.');
                                    if (ppos != -1)
                                    {
                                        val = val.Substring(0, ppos - 1);
                                    }
                                    realVal = Convert.ToInt64(val);
                                    break;

                                case 3:
                                    val     = Regex.Replace(val, "[^0-9.]", "");
                                    realVal = Convert.ToDouble(val);
                                    break;

                                case 4:
                                    DateTime realDT;
                                    if (DateTime.TryParse(val, out realDT))
                                    {
                                        realVal = realDT;
                                    }

                                    if (realVal == null)
                                    {
                                        long ldate = long.Parse(val);
                                        realVal = DateTime.FromOADate(ldate);
                                    }
                                    break;

                                case 5:
                                    realVal = OrmHelper.IsTrue(val);
                                    break;

                                case 6:
                                    val     = Regex.Replace(val, "[^0-9.]", "");
                                    realVal = Decimal.Parse(val);
                                    break;
                                }

                                if (realVal != null)
                                {
                                    OrmHelper.SetPropertyValue <Sale>(ref rec, key, realVal);
                                }
                            }
                        }
                        catch
                        {
                            warnings += $"'{key}',";
                        }
                    }

                    bool isFubar = false;

                    foreach (var col in mMap.Columns)
                    {
                        if (col.Required)
                        {
                            dynamic realVal = OrmHelper.GetPropertyValue <Sale>(rec, col.ColumnName);
                            if (realVal == null)
                            {
                                requires += $"'{col.ColumnName}',";
                                isFubar   = true;
                            }
                        }
                    }

                    if (!isFubar)
                    {
                        rec.ImportId = ip.ImportId;
                        rec.Row      = i;
                        rec.Sheet    = ip.SheetNo;

                        OrmHelper.SetAuditColumns <Sale>(ref rec, true, ip.PersonId);

                        _db.Sale.Add(rec);
                    }

                    if (isFubar || warnings.Length > 0)
                    {
                        if (isFubar)
                        {
                            ip.SkipRows++;
                        }
                        if (warnings.Length > 0)
                        {
                            ip.WarnRows++;
                        }

                        string errInfo = "{";
                        errInfo += "warns:[" + warnings.TrimEnd(',') + "],";
                        errInfo += "skips:[" + requires.TrimEnd(',') + "]";
                        errInfo += "}";

                        var ie = new ImportError()
                        {
                            ImportId = ip.ImportId,
                            Status   = isFubar ? 1 : 0,
                            Row      = i,
                            Sheet    = ip.SheetNo,
                            Errors   = errInfo
                        };

                        OrmHelper.SetAuditColumns <ImportError>(ref ie, true, ip.PersonId);

                        _db.ImportError.Add(ie);
                    }
                }
            }
        }
예제 #2
0
        public async Task <DTO.ImportPost> ImportSheet(DTO.ImportPost import)
        {
            ExcelPackage sheet = new ExcelPackage(import.Stream);

            var pMap = _db.PersonMap
                       .Include(o => o.Columns)
                       .Include(o => o.MasterMap)
                       .Include(o => o.MasterMap.Columns)
                       .FirstOrDefault(o => o.Id == import.PersonMapId);

            var mMap = pMap.MasterMap;

            var blobber = new Azure.Blobber();

            var bName = await blobber.Upload(import.Stream, mMap.TableName.ToLower());

            var importRec = new Import()
            {
                FileName    = import.FileName,
                FileLength  = import.FileLength,
                PersonMapID = import.PersonMapId,
                Status      = 1,
                BlobName    = bName
            };

            OrmHelper.SetAuditColumns <Import>(ref importRec, true, import.PersonId);

            _db.Import.Add(importRec);
            await _db.SaveChangesAsync();

            import.ImportId = importRec.Id;

            var headers = new Dictionary <string, int>();

            int sheetNo = 0;

            foreach (ExcelWorksheet worksheet in sheet.Workbook.Worksheets)
            {
                if (worksheet == null)
                {
                    continue;
                }
                if (worksheet.Dimension == null)
                {
                    continue;
                }

                int cols = worksheet.Dimension.Columns;
                int rows = worksheet.Dimension.Rows;

                if (cols == 0 || rows == 0)
                {
                    continue;
                }

                if (rows > 0)
                {
                    for (int i = 1; i <= cols; i++)
                    {
                        //loop for headers
                        string header = worksheet.GetValue(1, i).ToString().Trim();

                        if (!headers.ContainsKey(header))
                        {
                            headers.Add(header, i);
                        }
                    }
                }

                var MTC = new Dictionary <string, string>();

                foreach (var el in pMap.Columns)
                {
                    if (headers.ContainsKey(el.TheirHeader))
                    {
                        var fieldName = mMap.Columns.First(o => o.Header == el.OurHeader).ColumnName;
                        MTC.Add(fieldName, $"{headers[el.TheirHeader]}|{el.Regex}");
                    }
                    else
                    {
                        Debug.WriteLine(el.TheirHeader);
                    }
                }

                import.SheetNo = sheetNo;

                switch (mMap.TableName.ToLower())
                {
                case "sale":
                    DoSales(worksheet, MTC, mMap, import);
                    break;
                }

                sheetNo++;
            }

            importRec.Status = 2;

            importRec.WarnRows = import.WarnRows;
            importRec.SkipRows = import.SkipRows;

            await _db.SaveChangesAsync();

            return(import);
        }