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