public ActionResult ExportDemissionRateInfo(DemissionRateAndWorkScheduleDTO search)
{
//get Export datas
var apiUrl = "ProductionResourcePlan/ExportDemissionRateInfoAPI";
var responMessage = APIHelper.APIPostAsync(search, apiUrl);
var result = responMessage.Content.ReadAsStringAsync().Result;
var list = JsonConvert.DeserializeObject <List <DemissionRateAndWorkScheduleDTO> >(result);
var stream = new MemoryStream();
var fileName = PathHelper.SetGridExportExcelName("DemissionRateAndWorkSchedule");
var filePath = Path.Combine(PathHelper.GetGridExportExcelDirectory, fileName);
var stringHeads = new string[] {
"序号",
"厂区",
"OP类型",
"生产日期",
"生产阶段",
"NPI离职率",
"NPI预计招募人力",
"MP离职率",
"MP预计招募人力",
"排班机制",
};
using (var excelPackage = new ExcelPackage(stream))
{
//set sheet name
var worksheet = excelPackage.Workbook.Worksheets.Add("DemissionRateAndWorkSchedule");
//set Title
for (int colIndex = 0; colIndex < stringHeads.Length; colIndex++)
{
worksheet.Cells[1, colIndex + 1].Value = stringHeads[colIndex];
}
//set cell value
for (int index = 0; index < list.Count; index++)
{
var currentRecord = list[index];
//seq
worksheet.Cells[index + 2, 1].Value = index + 1;
worksheet.Cells[index + 2, 2].Value = currentRecord.Organization_Name;
worksheet.Cells[index + 2, 3].Value = currentRecord.OPType;
worksheet.Cells[index + 2, 4].Value = currentRecord.Product_Phase;
worksheet.Cells[index + 2, 5].Value = currentRecord.Product_Date.ToString("yyyy-MM-dd hh:ss");
worksheet.Cells[index + 2, 6].Value = (currentRecord.DemissionRate_NPI / 100).ToString("p");
worksheet.Cells[index + 2, 7].Value = currentRecord.NPI_RecruitStaff_Qty;
worksheet.Cells[index + 2, 8].Value = (currentRecord.DemissionRate_MP / 100).ToString("p");;
worksheet.Cells[index + 2, 9].Value = currentRecord.MP_RecruitStaff_Qty;
worksheet.Cells[index + 2, 10].Value = currentRecord.WorkSchedule;
}
worksheet.Cells.AutoFitColumns();
excelPackage.Save();
}
return(new FileContentResult(stream.ToArray(), "application/octet-stream")
{
FileDownloadName = Server.UrlEncode(fileName)
});
}
public string SaveDemissionInfo(DemissionRateAndWorkScheduleDTO dto)
{
try
{
if (dto.DemissionRateAndWorkSchedule_UID == 0)
{
var result = demissionRateAndWorkScheduleRepository.IsExistSchedule(dto);
if (result)
{
return("数据已经存在，请修改！");
}
DemissionRateAndWorkSchedule dtoModel = AutoMapper.Mapper.Map <DemissionRateAndWorkSchedule>(dto);
demissionRateAndWorkScheduleRepository.Add(dtoModel);
unitOfWork.Commit();
return("添加成功");
}
else
{
var item = demissionRateAndWorkScheduleRepository.GetById(dto.DemissionRateAndWorkSchedule_UID);
item.Product_Date = dto.Product_Date;
item.DemissionRate_MP = dto.DemissionRate_MP;
item.DemissionRate_NPI = dto.DemissionRate_NPI;
item.MP_RecruitStaff_Qty = dto.MP_RecruitStaff_Qty;
item.NPI_RecruitStaff_Qty = dto.NPI_RecruitStaff_Qty;
item.WorkSchedule = dto.WorkSchedule;
unitOfWork.Commit();
return("修改成功");
}
}
catch (Exception ex)
{
return("保存失败");
}
}
public List <DemissionRateAndWorkScheduleDTO> ExportDemissionRateList(DemissionRateAndWorkScheduleDTO searchModel)
{
string startDate = string.Empty;
string endDate = string.Empty;
var sql = @"SELECT DemissionRateAndWorkSchedule_UID,Plant_Organization_UID,B.Organization_Name, BG_Organization_UID,C.Organization_Name AS OPType,
Product_Phase, Product_Date,DemissionRate_NPI AS DemissionRate_NPI,DemissionRate_MP AS DemissionRate_MP,
NPI_RecruitStaff_Qty,MP_RecruitStaff_Qty,WorkSchedule FROM dbo.DemissionRateAndWorkSchedule A
JOIN dbo.System_Organization B ON A.Plant_Organization_UID = B.Organization_UID
JOIN dbo.System_Organization C ON A.BG_Organization_UID = C.Organization_UID
WHERE 1 = 1 ";
if (searchModel.Plant_Organization_UID != 0)
{
sql = sql + string.Format("AND Plant_Organization_UID = {0} ", searchModel.Plant_Organization_UID);
}
if (searchModel.BG_Organization_UID != 0)
{
sql = sql + string.Format("AND BG_Organization_UID = {0}", searchModel.BG_Organization_UID);
}
if (searchModel.StartDate != null)
{
startDate = searchModel.StartDate.Value.ToString(FormatConstants.DateTimeFormatStringByDate);
sql = sql + "AND CONVERT(VARCHAR(10),Product_Date,120) >= '" + startDate + "' ";
}
if (searchModel.EndDate != null)
{
endDate = searchModel.EndDate.Value.ToString(FormatConstants.DateTimeFormatStringByDate);
sql = sql + "AND CONVERT(VARCHAR(10),Product_Date,120) <= '" + endDate + "' ";
}
return(DataContext.Database.SqlQuery <DemissionRateAndWorkScheduleDTO>(sql).ToList());
}
public ActionResult SaveDemissionInfo(DemissionRateAndWorkScheduleDTO dto)
{
//新加
if (dto.DemissionRateAndWorkSchedule_UID == 0)
{
dto.Created_Date = DateTime.Now;
dto.Created_UID = this.CurrentUser.AccountUId;
dto.Modified_Date = DateTime.Now;
dto.Modified_UID = this.CurrentUser.AccountUId;
}
else
{
dto.Modified_Date = DateTime.Now;
dto.Modified_UID = this.CurrentUser.AccountUId;
}
dto.DemissionRate_NPI = dto.DemissionRate_NPI / 100;
dto.DemissionRate_MP = dto.DemissionRate_MP / 100;
////判断是否是重复的值
//var repeatList = demissionList.Where(p => p.Plant_Organization_UID == item.Plant_Organization_UID && p.BG_Organization_UID == item.BG_Organization_UID && p.Product_Date == item.Product_Date && p.Product_Phase == item.Product_Phase);
//if (repeatList.Count() > 0)
//{
// var model = repeatList.FirstOrDefault();
// errorInfo = string.Format("导入的Excel中厂区={0},OP={1},生产阶段={2},生产时间={3}", Plant_Organization, BG_Organization, model.Product_Phase, model.Product_Date);
// return errorInfo;
//}
var apiUrl = string.Format("ProductionResourcePlan/SaveDemissionInfoAPI");
HttpResponseMessage responMessage = APIHelper.APIPostAsync(dto, apiUrl);
var result = responMessage.Content.ReadAsStringAsync().Result;
return(Content(result, "application/json"));
}
public List <DemissionRateAndWorkScheduleDTO> ExportDemissionRateInfoByID(string uids)
{
uids = "," + uids + ",";
DemissionRateAndWorkScheduleDTO searchModel = new DemissionRateAndWorkScheduleDTO();
var list = demissionRateAndWorkScheduleRepository.ExportDemissionRateList(searchModel);
var query = list.Where(m => uids.Contains("," + m.DemissionRateAndWorkSchedule_UID + ","));
return(query.OrderByDescending(p => p.Product_Date).ToList());
}
public ActionResult TurnoverSchedulingInfo(DemissionRateAndWorkScheduleDTO dto, Page page)
{
//获取用户所属的三级权限，从而获得4级权限功能厂，不考虑跨厂区权限
if (this.CurrentUser.GetUserInfo.Plant_OrganizationUIDList.Count() != 0)
{
dto.Plant_Organization_UID = this.CurrentUser.GetUserInfo.Plant_OrganizationUIDList.First();
}
if (this.CurrentUser.GetUserInfo.OPType_OrganizationUIDList.Count() != 0)
{
dto.BG_Organization_UID = this.CurrentUser.GetUserInfo.OPType_OrganizationUIDList.First();
}
var apiUrl = string.Format("ProductionResourcePlan/QueryTurnoverSchedulingInfoAPI");
HttpResponseMessage responMessage = APIHelper.APIPostAsync(dto, page, apiUrl);
var result = responMessage.Content.ReadAsStringAsync().Result;
return(Content(result, "application/json"));
}
public List <DemissionRateAndWorkScheduleDTO> QueryTurnoverSchedulingInfo(DemissionRateAndWorkScheduleDTO searchModel, Page page, out int totalCount)
{
string startDate = string.Empty;
string endDate = string.Empty;
var sql = @"SELECT DemissionRateAndWorkSchedule_UID,Plant_Organization_UID,B.Organization_Name, BG_Organization_UID,C.Organization_Name AS OPType,
Product_Phase, Product_Date,DemissionRate_NPI AS DemissionRate_NPI,DemissionRate_MP AS DemissionRate_MP,
NPI_RecruitStaff_Qty,MP_RecruitStaff_Qty,WorkSchedule FROM dbo.DemissionRateAndWorkSchedule A
JOIN dbo.System_Organization B ON A.Plant_Organization_UID = B.Organization_UID
JOIN dbo.System_Organization C ON A.BG_Organization_UID = C.Organization_UID
WHERE 1 = 1 ";
if (searchModel.Plant_Organization_UID != 0)
{
sql = sql + string.Format("AND Plant_Organization_UID = {0} ", searchModel.Plant_Organization_UID);
}
if (searchModel.BG_Organization_UID != 0)
{
sql = sql + string.Format("AND BG_Organization_UID = {0}", searchModel.BG_Organization_UID);
}
if (searchModel.StartDate != null)
{
startDate = searchModel.StartDate.Value.ToString(FormatConstants.DateTimeFormatStringByDate);
sql = sql + "AND CONVERT(VARCHAR(10),Product_Date,120) >= '" + startDate + "' ";
}
if (searchModel.EndDate != null)
{
endDate = searchModel.EndDate.Value.ToString(FormatConstants.DateTimeFormatStringByDate);
sql = sql + "AND CONVERT(VARCHAR(10),Product_Date,120) <= '" + endDate + "' ";
}
totalCount = DataContext.Database.SqlQuery <DemissionRateAndWorkScheduleDTO>(sql).ToList().Count();
var list = DataContext.Database.SqlQuery <DemissionRateAndWorkScheduleDTO>(sql).Skip(page.PageSize * page.PageNumber).Take(page.PageSize).ToList();
return(list);
}
public bool IsExistSchedule(DemissionRateAndWorkScheduleDTO dto)
{
try
{
var query = from dem in DataContext.DemissionRateAndWorkSchedule
select new DemissionRateAndWorkScheduleDTO
{
DemissionRateAndWorkSchedule_UID = dem.DemissionRateAndWorkSchedule_UID,
Plant_Organization_UID = dem.Plant_Organization_UID,
Organization_Name = dem.System_Organization.Organization_Name,
OPType = dem.System_Organization1.Organization_Name,
BG_Organization_UID = dem.BG_Organization_UID,
Product_Phase = dem.Product_Phase,
Product_Date = dem.Product_Date,
DemissionRate_NPI = dem.DemissionRate_NPI,
DemissionRate_MP = dem.DemissionRate_MP,
NPI_RecruitStaff_Qty = dem.NPI_RecruitStaff_Qty,
MP_RecruitStaff_Qty = dem.MP_RecruitStaff_Qty,
WorkSchedule = dem.WorkSchedule,
Created_UID = dem.Created_UID,
Created_Date = dem.Created_Date,
Modified_UID = dem.Modified_UID,
Modified_Date = dem.Modified_Date
};
var repeatList = query.Where(p => p.Plant_Organization_UID == dto.Plant_Organization_UID && p.BG_Organization_UID == dto.BG_Organization_UID && p.Product_Date == dto.Product_Date && p.Product_Phase == dto.Product_Phase);
if (repeatList.Count() > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
return(true);
}
}
public List <DemissionRateAndWorkScheduleDTO> ExportDemissionRateInfo(DemissionRateAndWorkScheduleDTO searchModel, Page page)
{
var list = demissionRateAndWorkScheduleRepository.ExportDemissionRateList(searchModel);
return(list.OrderByDescending(p => p.Product_Date).ToList());
}
public PagedListModel <DemissionRateAndWorkScheduleDTO> QueryTurnoverSchedulingInfo(DemissionRateAndWorkScheduleDTO searchModel, Page page)
{
var totalCount = 0;
var list = demissionRateAndWorkScheduleRepository.QueryTurnoverSchedulingInfo(searchModel, page, out totalCount);
return(new PagedListModel <DemissionRateAndWorkScheduleDTO>(totalCount, list));
}
public string ImportTurnoverExcel(HttpPostedFileBase uploadName)
{
string errorInfo = string.Empty;
using (var xlPackage = new ExcelPackage(uploadName.InputStream))
{
var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
int totalRows = worksheet.Dimension.End.Row;
int totalColumns = worksheet.Dimension.End.Column;
if (worksheet == null)
{
errorInfo = "没有worksheet内容";
return(errorInfo);
}
//头样式设置
var propertiesHead = new[]
{
"厂区",
"OP类型",
"生产日期",
"生产阶段",
"NPI离职率",
"NPI预计招募人力",
"MP离职率",
"MP预计招募人力",
"排班机制",
};
//1 验证表头
bool isExcelError = false;
for (int i = 1; i <= totalColumns; i++)
{
if (worksheet.Cells[1, i].Value != null && !string.IsNullOrWhiteSpace(worksheet.Cells[1, i].Value.ToString()))
{
var resultsheet = worksheet.Cells[1, i].Value.ToString();
var hasItem = propertiesHead.FirstOrDefault(m => m.Contains(resultsheet));
if (hasItem == null)
{
isExcelError = true;
break;
}
}
else
{
isExcelError = true;
break;
}
}
if (isExcelError)
{
errorInfo = "Excel格式不正确";
return(errorInfo);
}
//获得厂区
var plantsAPI = string.Format("Fixture/QueryAllPlantAPI?PLANT_UID={0}&leval={1}", GetPlantOrgUid(), "1");
HttpResponseMessage plantsmessage = APIHelper.APIGetAsync(plantsAPI);
var jsonPlants = plantsmessage.Content.ReadAsStringAsync().Result;
var plants = JsonConvert.DeserializeObject <List <SystemOrgDTO> >(jsonPlants);
var demissionList = new List <DemissionRateAndWorkScheduleDTO>();
for (int i = 2; i <= totalRows; i++)
{
var Plant_Organization = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 1].Value);
var BG_Organization = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 2].Value);
var Product_Phase = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 3].Value);
var productDate = DateTime.FromOADate(double.Parse(worksheet.Cells[i, 4].Value.ToString()));
var NPIRate = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 5].Value);
var NPIQty = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 6].Value);
var MPRate = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 7].Value);
var MPQty = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 8].Value);
var Schedule = ExcelHelper.ConvertColumnToString(worksheet.Cells[i, 9].Value);
var DemissionModel = new DemissionRateAndWorkScheduleDTO();
int Plant_Organization_UID = 0;
int BG_Organization_UID = 0;
#region 验证厂区 OP类型
// 1 验证厂区
if (string.IsNullOrWhiteSpace(Plant_Organization))
{
isExcelError = true;
errorInfo = string.Format("第{0}行厂区代号没有值", i);
return(errorInfo);
}
else
{
Plant_Organization = Plant_Organization.Trim();
var systemOrgDTO = plants.Where(m => m.Organization_Name == Plant_Organization).FirstOrDefault();
if (systemOrgDTO != null)
{
Plant_Organization_UID = systemOrgDTO.Organization_UID;
}
else
{
isExcelError = true;
errorInfo = string.Format("第{0}行厂区代号的值没有找到", i);
return(errorInfo);
}
}
//2 验证OP类型
if (string.IsNullOrWhiteSpace(BG_Organization))
{
isExcelError = true;
errorInfo = string.Format("第{0}行OP代号不能为空", i);
return(errorInfo);
}
else
{
BG_Organization = BG_Organization.Trim();
var apiUrlAPI = string.Format("Fixture/GetOrgByParantAPI?Parant_UID={0}&type={1}", Plant_Organization_UID, 1);
HttpResponseMessage responMessage2 = APIHelper.APIGetAsync(apiUrlAPI);
var result2 = responMessage2.Content.ReadAsStringAsync().Result;
var optypes = JsonConvert.DeserializeObject <List <SystemOrgDTO> >(result2);
var bg = optypes.Where(m => m.Organization_Name == BG_Organization).FirstOrDefault();
if (bg != null)
{
BG_Organization_UID = bg.Organization_UID;
}
else
{
isExcelError = true;
errorInfo = string.Format("第{0}行OP代号的值没有找到", i);
return(errorInfo);
}
}
#endregion
#region 验证栏位
if (string.IsNullOrEmpty(Product_Phase))
{
errorInfo = string.Format("第[{0}]行生产阶段不能为空", i);
return(errorInfo);
}
//Product_Date = Convert.ToDateTime(worksheet.Cells[iRow, 3].Value);
if (string.IsNullOrEmpty(productDate.ToString()))
{
errorInfo = string.Format("第[{0}]行生产日期不能为空", i);
return(errorInfo);
}
if (string.IsNullOrEmpty(NPIRate))
{
errorInfo = string.Format("第[{0}]行NPI离职率不能为空", i);
return(errorInfo);
}
decimal validDecimal = 0;
var isDecimal = decimal.TryParse(NPIRate, out validDecimal);
if (!isDecimal)
{
errorInfo = string.Format("第{0}行NPI离职率必须为数字", i);
return(errorInfo);
}
//NPI预计招募人力
if (string.IsNullOrEmpty(NPIQty))
{
errorInfo = string.Format("第[{0}]行NPI预计招募人力不能为空", i);
return(errorInfo);
}
int validInt = 0;
var isInt = int.TryParse(NPIQty, out validInt);
if (!isInt)
{
errorInfo = string.Format("第{0}行NPI预计招募人力必须为整数", i);
return(errorInfo);
}
//MP离职率
if (string.IsNullOrEmpty(MPRate))
{
errorInfo = string.Format("第[{0}]行MP离职率不能为空", i);
return(errorInfo);
}
isDecimal = decimal.TryParse(MPRate, out validDecimal);
if (!isDecimal)
{
errorInfo = string.Format("第{0}行MP离职率必须为数字", i);
return(errorInfo);
}
//MP预计招募人力
if (string.IsNullOrEmpty(MPQty))
{
errorInfo = string.Format("第[{0}]行MP预计招募人力不能为空", i);
return(errorInfo);
}
isInt = int.TryParse(MPQty, out validInt);
if (!isInt)
{
errorInfo = string.Format("第{0}行MP预计招募人力必须为整数", i);
return(errorInfo);
}
if (string.IsNullOrEmpty(Schedule))
{
errorInfo = string.Format("第[{0}]行排班机制不能为空", i);
return(errorInfo);
}
#endregion
DemissionRateAndWorkScheduleDTO item = new DemissionRateAndWorkScheduleDTO();
item.Plant_Organization_UID = Plant_Organization_UID;
item.BG_Organization_UID = BG_Organization_UID;
item.Product_Phase = Product_Phase;
item.Product_Date = productDate;
item.DemissionRate_NPI = Convert.ToDecimal(NPIRate) * 100;
item.NPI_RecruitStaff_Qty = Convert.ToInt32(NPIQty);
item.DemissionRate_MP = Convert.ToDecimal(MPRate) * 100;
item.MP_RecruitStaff_Qty = Convert.ToInt32(MPQty);
item.WorkSchedule = Schedule;
item.Created_UID = this.CurrentUser.GetUserInfo.Account_UID;
item.Modified_UID = this.CurrentUser.GetUserInfo.Account_UID;
item.Created_Date = DateTime.Now;
item.Modified_Date = DateTime.Now;
item.LanguageID = PISSessionContext.Current.CurrentWorkingLanguage.System_Language_UID;
//判断是否是重复的值
var repeatList = demissionList.Where(p => p.Plant_Organization_UID == item.Plant_Organization_UID && p.BG_Organization_UID == item.BG_Organization_UID && p.Product_Date == item.Product_Date && p.Product_Phase == item.Product_Phase);
if (repeatList.Count() > 0)
{
var model = repeatList.FirstOrDefault();
errorInfo = string.Format("导入的Excel中厂区={0},OP={1},生产阶段={2},生产时间={3}", Plant_Organization, BG_Organization, model.Product_Phase, model.Product_Date);
return(errorInfo);
}
demissionList.Add(item);
}
if (demissionList.Distinct().Count() != totalRows - 1)
{
errorInfo = "导入的Excel有重复行";
return(errorInfo);
}
//检查数据库是否有重复
string json = JsonConvert.SerializeObject(demissionList);
var checkApiUrl = string.Format("ProductionResourcePlan/CheckImportTurnoverExcelAPI");
HttpResponseMessage checkResponMessage = APIHelper.APIPostAsync(json, checkApiUrl);
errorInfo = checkResponMessage.Content.ReadAsStringAsync().Result.Replace("\"", "");
if (!string.IsNullOrEmpty(errorInfo))
{
return(errorInfo);
}
var apiUrl = string.Format("ProductionResourcePlan/ImportTurnoverExcelAPI");
HttpResponseMessage responMessage = APIHelper.APIPostAsync(json, apiUrl);
var result = responMessage.Content.ReadAsStringAsync().Result;
}
return(string.Empty);
}
