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);
}
}
}
}
