public static void SaveFromExcel(XLWorkbook wb, string creatorSid)
{
var attrs = ClassifierAttributes.Get();
int wtCnt = WorkType.GetList().Count();
var ws = wb.Worksheet(1);
var workTypes = WorkType.GetList();
int r = 0;
int headerRow = r+2;
foreach (var row in ws.Rows())
{
r++;
if (r <= headerRow) continue;
int c = 1;
if (String.IsNullOrEmpty(row.Cell(c).Value.ToString())) break;
string catName = row.Cell(c).Value.ToString();
c++;
string catNumber = row.Cell(c).Value.ToString();
c++;
int catComplexity;
int.TryParse(row.Cell(c).Value.ToString(), out catComplexity);
var cat = new ClassifierCaterory() { Name = catName, Number = catNumber, Complexity = catComplexity, CurUserAdSid = creatorSid };
cat.Save();
int colOffset = c;
c++;
while (!String.IsNullOrEmpty(ws.Row(headerRow).Cell(c).Value.ToString()))
{
int idCategory = cat.Id;
var headerVal = ws.Row(headerRow).Cell(c).Value;
string wtValue = headerVal.ToString().Trim();
int pointIndex = wtValue.IndexOf(".", StringComparison.Ordinal);
string currWorkTypeSysName = wtValue;
string wtType = String.Empty;
if (pointIndex > -1)
{
currWorkTypeSysName = wtValue.Remove(pointIndex);
wtType = wtValue.Substring(pointIndex);
}
int idWorkType = workTypes.Any(x => x.SysName == currWorkTypeSysName)
? workTypes.First(x => x.SysName == currWorkTypeSysName).Id
: 0;
var clItem = new ClassifierItem() { IdCategory = idCategory, IdWorkType = idWorkType, CurUserAdSid = creatorSid };
//string cellAddress = row.Cell(c).Address.ToStringRelative();
//var cVal = ws.Cell(cellAddress).Value;
if (currWorkTypeSysName.Equals("НПР"))
{
string s = "";
}
if (String.IsNullOrEmpty(wtType))
{
int flag = (c - colOffset - 1) / wtCnt;
switch (flag)
{
//первая шеренга типов работ - это время
case 0:
wtType = ".t";
break;
//ыторая шеренга типов работ - это прайс
case 1:
wtType = ".wi";
break;
}
}
if (!String.IsNullOrEmpty(wtType) && (wtType.Equals(".t") || wtType.Equals(".wi")))
{
string cellValue = row.Cell(c).Value.ToString();
switch (wtType)
{
case ".t":
int t;
int.TryParse(cellValue, out t);
clItem.Time = t;
break;
case ".wi":
decimal wi;
decimal.TryParse(cellValue, out wi);
clItem.Price = wi;
clItem.CostPeople = clItem.Price + attrs.Wage;
clItem.CostCompany = clItem.Price + attrs.Overhead;
break;
//case ".f":
// decimal f;
// decimal.TryParse(cellValue, out f);
// clItem.Price = f;
// break;
//case ".wc":
// decimal wc;
// decimal.TryParse(cellValue, out wc);
// clItem.Price = wc;
// break;
}
clItem.Save();
}
c++;
}
}
}
public static IEnumerable<ClassifierItem> GetList()
{
var dt = Db.Service.ExecuteQueryStoredProcedure("get_classifier_list");
var list = new List<ClassifierItem>();
foreach (DataRow row in dt.Rows)
{
var model = new ClassifierItem(row);
list.Add(model);
}
return list;
}