private ConstructionPlanMonthDetail GetCommonProperty(ConstructionPlanMonth item, int month,
ConstructionPlanImportCommon cpic, AllQuery all, DateTime dtNow, string planDate, ref DataTable dt, ref DataTable dtChart)
{
ConstructionPlanMonthDetail c = new ConstructionPlanMonthDetail();
#region 通用属性
c.Line = cpic.Line;
c.Month = month;
c.WorkType = 112;//默认委外维护
c.WorkTypeName = all.workType.Where(a => a.ID == c.WorkType).FirstOrDefault().Name;
c.EqpType = item.Code;
c.EqpTypeName = item.EqpTypeName;
c.Location = item.Location;
c.LocationBy = item.LocationBy;
c.LocationName = all.locations.Where(a => a.LocationBy == c.LocationBy && a.ID == c.Location).FirstOrDefault().Name;
c.Department = cpic.Department;
c.Team = item.Team;
c.TeamName = all.team.Where(a => a.ID == c.Team).FirstOrDefault().Name;
c.PMType = (int)GetPMTypeByFrequency(item.Frequency, true);
c.PMTypeName = all.pmType.Where(a => a.ID == c.PMType).FirstOrDefault().Name;
c.PMCycle = item.Cycle;
c.PMFrequency = item.Frequency * 12;
c.Unit = item.Unit;
c.PlanQuantity = item.Quantity;
c.RealQuantity = item.Quantity;
c.Query = cpic.ID;
c.PlanDate = planDate;
c.UpdateTime = dtNow;
c.UpdateBy = _userID;
InsertRow(ref dt, c);
InsertRow(ref dtChart, c, cpic.Year);
#endregion
return(c);
}
public ActionResult Save([FromForm] ConstructionPlanImportCommon importCommon, IFormFile file)
{
var ret = _service.Save(importCommon, file);
return(Ok(ret.Result));
//return Ok("");
}
public async Task <int> Save(ConstructionPlanImportCommon obj)
{
return(await WithConnection(async c =>
{
string sql = " insert into construction_plan_import_common " +
" values (0,@Year,@Department,@DepartmentName,@Line,@LineName, " +
" @Company,@IsCreatedMonth,@CreatedTime,@CreatedBy,@ImportedTime,@ImportedBy); ";
sql += "SELECT LAST_INSERT_ID() ";
int newid = await c.QueryFirstOrDefaultAsync <int>(sql, obj);
return newid;
}));
}
public async Task <ApiResult> Create(int query)
{
ApiResult ret = new ApiResult();
List <ConstructionPlanMonthDetail> month = new List <ConstructionPlanMonthDetail>();
DataTable dt = GetColumnName(false);
DataTable dtChart = GetColumnName(true);
DateTime dtNow = DateTime.Now;
// 每天工作时间统计，年表中的任务分配给时间做少的一天
List <List <int> > dayMinInMonth = new List <List <int> >();
for (int i = 0; i < 12; i++)
{
List <int> tmp = new List <int>();
for (int j = 0; j < 31; j++)
{
tmp.Add(0);
}
dayMinInMonth.Add(tmp);
}
try
{
List <ConstructionPlanMonth> cpms = await _importRepo.ListMonthByQuery(query);
List <ConstructionPlanYear> cpys = await _importRepo.ListYearByQuery(query);
ConstructionPlanImportCommon cpic = await _importRepo.GetByID(query);
AllQuery all = new AllQuery();
//all.eqpTypes = await _importRepo.ListAllEqpTypes();
all.locations = await _importRepo.ListAllLocations();
all.team = await _importRepo.ListAllOrgByType(OrgType.Team);
all.workType = await _importRepo.ListDictionarysByParent(MSS.Platform.Workflow.WebApi.Model.Common.WORK_TYPE);
all.pmType = await _importRepo.ListDictionarysByParent(MSS.Platform.Workflow.WebApi.Model.Common.PM_TYPE);
int year = cpic.Year;
int index = 0;
// 12月份循环
for (int i = 1; i < 13; i++)
{
int j = 0;
// 处理月表
foreach (var item in cpms)
{
string m = "." + i.ToString("D2");
if (item.Frequency % 31 == 0)
{
string d = "." + MSS.Platform.Workflow.WebApi.Model.Common.GetLastDay(i, year);
string planDate = year + m + ".01-" + year + m + d;
ConstructionPlanMonthDetail c = GetCommonProperty(item, i, cpic, all, dtNow, planDate, ref dt, ref dtChart);
//dt.Rows[j+index][14] = c.PlanDate;
month.Add(c);
}
else
{
List <int> day = dayMinInMonth[i - 1];
List <int> allDate = GetDay(item, ref day);
foreach (var date in allDate)
{
string planDate = year + m + "." + date.ToString("D2");
ConstructionPlanMonthDetail c = GetCommonProperty(item, i, cpic, all, dtNow, planDate, ref dt, ref dtChart);
//dt.Rows[j+index][14] = c.PlanDate;
month.Add(c);
}
}
j++;
}
index += j;
}
// 处理年表
foreach (var item in cpys)
{
int j = 0;
List <int> allMonth = GetMonth(item);
foreach (var m in allMonth)
{
int min = dayMinInMonth[m - 1].Min();
int day = dayMinInMonth[m - 1].IndexOf(min);
dayMinInMonth[m - 1][day] += item.Once;
string planDate = year + "." + m.ToString("D2") + "." + (day + 1).ToString("D2");
ConstructionPlanMonthDetail c = GetCommonProperty(item, m, cpic, all, dtNow, planDate, ref dt, ref dtChart);
//dt.Rows[index+j][14] = c.PlanDate;
month.Add(c);
j++;
}
index += j;
}
//自动创建检修表单
//List<MaintenanceList> mls = GetMLists(month, cpic.Year, dtNow);
// 创建的数据存入数据,按时间排序？
using (TransactionScope scope = new TransactionScope())
{
_importRepo.BulkLoad(dt);
_importRepo.BulkLoad(dtChart);
await _importRepo.UpdateCommonStatus(query, _userID);
scope.Complete();
}
// 创建的数据按照月份拆分后，按照时间排序后，分页
List <List <ConstructionPlanMonthDetail> > months = new List <List <ConstructionPlanMonthDetail> >();
List <object> retList = new List <object>();
for (int i = 0; i < 12; i++)
{
months.Add(new List <ConstructionPlanMonthDetail>());
}
foreach (var item in month)
{
months[item.Month - 1].Add(item);
}
foreach (var item in months)
{
retList.Add(new { rows = item.OrderBy(a => a.PlanDate).Take(10), total = item.Count });
}
ret.data = retList;
}
catch (Exception ex)
{
ret.code = Code.Failure;
ret.msg = ex.Message;
}
return(ret);
}
public async Task <ApiResult> Save(ConstructionPlanImportCommon importCommon, IFormFile file)
{
ApiResult ret = new ApiResult();
QueryItem qi = new QueryItem();
//保存每个
List <DeptAndLine> dls = new List <DeptAndLine>();
//ConstructionPlanImport parm = new ConstructionPlanImport();
//parm.monthPlans = new List<DataTable>();
//parm.yearPlans = new List<DataTable>();
importCommon.ImportedTime = DateTime.Now;
importCommon.ImportedBy = _userID;
//parm.importCommon = importCommon;
ISheet sheet = null;
IWorkbook workbook = null;
//合并单元格时用到，有值时覆盖，无值时获取
string code = "", eqpTypeName = "";
bool isYear = true;
int query = 0;
try
{
List <ConstructionPlanImportCommon> cpics = await _repo.ListByYearAndCompany(importCommon.Year, importCommon.Company);
List <QueryItem> allLines = await _repo.ListAllLines();
List <QueryItem> allDepartments = await _repo.ListAllOrgByType(OrgType.Department);
List <QueryItem> allTeams = await _repo.ListAllOrgByType(OrgType.Team);
//List<QueryItem> allEqpTypes = await _repo.ListAllEqpTypes();
List <QueryItem> allLocations = await _repo.ListAllLocations();
if (file.Length > 0)
{
//利用IFormFile里面的OpenReadStream()方法直接读取文件流
Stream stream = file.OpenReadStream();
string fileType = Path.GetExtension(file.FileName);
#region 判断excel版本
//2007以上版本excel
if (fileType == ".xlsx")
{
workbook = new XSSFWorkbook(stream);
}
//2007以下版本excel
else if (fileType == ".xls")
{
workbook = new HSSFWorkbook(stream);
}
else
{
ret.code = Code.ImportError;
ret.msg = "传入的不是Excel文件";
return(ret);
}
#endregion
using (TransactionScope scope = new TransactionScope())
{
for (int sheetNo = 0; sheetNo < workbook.NumberOfSheets; sheetNo++)
{
sheet = workbook.GetSheetAt(sheetNo);
if (sheet.GetRow(0) != null)
{
string title = sheet.GetRow(0).GetCell(0).StringCellValue.Trim().Replace(" ", "");
if (title.IndexOf("年表") > -1)
{
isYear = true;
}
else if (title.IndexOf("月表") > -1)
{
isYear = false;
}
else
{
continue;
}
string common = sheet.GetRow(2).GetCell(0).StringCellValue.Trim()
.Replace(" ", "").Replace("(", "").Replace(")", "").Replace("（", "").Replace("）", "");
string[] tmp = common.Split('部');
importCommon.LineName = tmp[1];
importCommon.DepartmentName = tmp[0] + "部";
#region 部门和线路名称与已定义的名称匹配
qi = GetIDByName(allLines, tmp[1], "路线", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
importCommon.Line = qi.ID;
}
qi = GetIDByName(allDepartments, importCommon.DepartmentName, "部门", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
importCommon.Department = qi.ID;
}
#endregion
if (cpics.Count > 0)
{
var tmpIds = cpics
.Where(a => a.LineName == importCommon.LineName && a.DepartmentName == importCommon.DepartmentName)
.Select(a => a.ID);
if (tmpIds.Count() > 0)
{
List <int> ids = tmpIds.ToList();
//await _repo.Delete(ids);
if (isYear)
{
await _repo.Delete(ids, "construction_plan_year");
}
else
{
await _repo.Delete(ids, "construction_plan_month");
}
query = tmpIds.FirstOrDefault();
}
}
else if (dls.Where(a => a.Line == importCommon.Line && a.Department == importCommon.Department).Count() == 0)
{
query = await _repo.Save(importCommon);
dls.Add(new DeptAndLine()
{
Department = importCommon.Department, Line = importCommon.Line
});
}
DataTable dt = GetColumnName(isYear);
DataRow dataRow = null;
//遍历行
for (int j = 5; j <= sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
dataRow = dt.NewRow();
if (row == null || row.FirstCellNum < 0)
{
continue;
}
string str = row.GetCell(0).ToString().Trim();
if (str.Replace(" ", "").Contains("总计"))
{
break;
}
string rowNo = (j + 1).ToString();
code = GetMergeCell(str, code, "代码(第" + rowNo + "行)", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
dataRow[0] = code;
}
#region 处所、班组匹配name
//设备设施
string name = row.GetCell(1).ToString().Trim();
eqpTypeName = GetMergeCell(name, eqpTypeName, "设备设施(第" + rowNo + "行)", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
dataRow[2] = eqpTypeName;
}
dataRow[1] = 0;//此字段已弃用，为了不修改相关代码，默认写0
//qi = GetIDByName(allEqpTypes, eqpTypeName, "设备设施(第" + rowNo + "行)", ref ret);
//if (ret.code != Code.Success) return ret;
//else dataRow[1] = qi.ID;
//dataRow[1] = 0;//目前id和name没有保持一致，先把id默认0
//处所
name = row.GetCell(2).ToString().Trim();
dataRow[5] = name;
qi = GetIDByName(allLocations, name, "处所(第" + rowNo + "行)", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
dataRow[3] = qi.ID;
} dataRow[4] = qi.LocationBy;
//dataRow[3] = 0; dataRow[4]=0;
//班组
name = row.GetCell(3).ToString().Trim();
dataRow[7] = name;
qi = GetIDByName(allTeams, name, "班组(第" + rowNo + "行)", ref ret);
if (ret.code != Code.Success)
{
return(ret);
}
else
{
dataRow[6] = qi.ID;
}
//dataRow[6] = 0;
#endregion
//遍历列
for (int i = 4; i < dt.Columns.Count - 2; i++)
{
if (i == 9 || i == 10)
{
continue;
}
ICell cellData = row.GetCell(i);
//if (cellData != null)
{
if (i < 9)
{
str = cellData.ToString().Trim();
if (ValidateCell(str, "第" + rowNo + "行第" + (i + 1).ToString() + "列", ref ret))
{
return(ret);
}
dataRow[i + 4] = str;
}
else if (i > 10)
{
dataRow[i + 2] = cellData == null || string.IsNullOrEmpty(cellData.ToString().Trim()) ? 0 : 1;
}
}
}
//最后一列common表主键
dataRow[dt.Columns.Count - 1] = query;
dt.Rows.Add(dataRow);
}
_repo.BulkLoad(dt);
}
}
scope.Complete();
}
}
return(ret);
}
catch (Exception ex)
{
ret.code = Code.Failure;
ret.msg = ex.Message;
return(ret);
}
}
