/// <summary> /// 描述:获取所有的组织框架 /// 创建标识:cpf /// 创建时间:2017-9-19 20:37:36 /// </summary> /// <returns></returns> public List <MaterialInfoModel> SearchMaterialPageList(MaterialSearchModel param, out int totalCount) { var list = new List <MaterialInfoModel>(); var selectSql = new StringBuilder(); var countSql = new StringBuilder(); var whereSql = new StringBuilder(); whereSql.Append(" WHERE 1 = 1 "); if (string.IsNullOrEmpty(param.ProductName)) { whereSql.Append(string.Format(" AND MIProductName like '%{0}'", param.ProductName)); } if (string.IsNullOrEmpty(param.CustomerPart)) { whereSql.Append(string.Format(" AND MICustomerPart like '%{0}'", param.CustomerPart)); } if (string.IsNullOrEmpty(param.WorkOrder)) { whereSql.Append(string.Format(" AND MIWorkOrder like '%{0}'", param.WorkOrder)); } selectSql.Append(string.Format(@" SELECT newTable.* FROM ( SELECT TOP ( {0} * {1} ) ROW_NUMBER() OVER ( ORDER BY MIOperateTime DESC) RowNum ,[Id] ,[MICustomerPart] ,[MIProductName] ,[MICustomer] ,[MIPicture] ,[MIIsValid] ,[MICreateUserId] ,[MICreateUserName] ,[MICreateTime] ,[MIOperateUserId] ,[MIOperateUserName] ,[MIOperateTime] ,[MIWorkOrder] ,[MIMaterial] ,[MIMaterialText] ,[MITool] ,[MITotalQty] FROM {2} with(NOLOCK) {3} ORDER BY MIOperateTime DESC) newTable WHERE newTable.RowNum > ( ( {0} - 1 ) * {1} ) ", param.CurrentPage, param.PageSize, tableName, whereSql.ToString())); countSql.Append(string.Format(@"SELECT COUNT(1) FROM {0} with(NOLOCK) {1} ", tableName, whereSql.ToString())); var ds = SqlHelper.ExecuteDataSet(CommandType.Text, selectSql.ToString()); totalCount = SqlHelper.ExecuteCount(CommandType.Text, countSql.ToString()); if (ds != null && ds.Tables.Count > 0) { DataTable dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <MaterialInfoModel>(dt); } return(list); }
//To Delete //public ActionResult Importool() //{ // var result = new ResultInfoModel() // { // IsSuccess = false // }; // StringBuilder strbuild = new StringBuilder(); // string FileName; // string savePath; // HttpPostedFileBase file = Request.Files["file"]; // if (file == null || file.ContentLength <= 0) // { // result.Message = "please choose file"; // return Content(JsonHelper.JsonSerializer(result)); // } // else // { // string fileName = Path.GetFileName(file.FileName); // int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte // string fileEx = Path.GetExtension(fileName);//获取上传文件的扩展名 // string NoFileName = Path.GetFileNameWithoutExtension(fileName);//获取无扩展名的文件名 // int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M // string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 // FileName = NoFileName + fileEx; // if (!FileType.Contains(fileEx)) // { // result.Message = "please upload .xls and .xlsx"; // return Content(JsonHelper.JsonSerializer(result)); // } // if (filesize >= Maxsize) // { // result.Message = string.Format("file size can't big than {0}", Maxsize); // return Content(JsonHelper.JsonSerializer(result)); // } // string path = Server.MapPath("~/App_Data/uploads"); // savePath = Path.Combine(path, FileName); // file.SaveAs(savePath); // } // string strConn; // strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;"; // using (OleDbConnection conn = new OleDbConnection(strConn)) // { // conn.Open(); // OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); // DataSet myDataSet = new DataSet(); // try // { // myCommand.Fill(myDataSet, "ExcelInfo"); // } // catch (Exception ex) // { // result.Message = ex.Message; // return Content(JsonHelper.JsonSerializer(result)); // } // DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); // var importResult = new Importresult(); // importResult.FalseInfo = new List<FalseInfo>(); // try // { // for (int i = 0; i < table.Rows.Count; i++) // { // var model = new MaToolModel // { // //BMCode = table.Rows[i]["Code"].ToString() ?? "", // //BMCodeDesc = table.Rows[i]["CodeDesc"].ToString() ?? "", // //BMType = MTTypeEnum.Tool.GetHashCode(), // //BMIsValid = EnabledEnum.Enabled.GetHashCode(), // }; // var inserResult = MaterialBusiness.SaveMaTool(model, LoginUser); // } // result.IsSuccess = true; // } // catch (Exception ex) // { // result.Message = ex.Message; // return Content(JsonHelper.JsonSerializer(result)); // } // conn.Close(); // } // return Content(JsonHelper.JsonSerializer(result)); //} #endregion #region 导出 public FileResult MaterialExcel(MaterialSearchModel searchModel) { searchModel.PageSize = 1000; var totalCount = 0; var result = MaterialBusiness.SearchMaterialPageList(searchModel, out totalCount).ToList(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("Process Type"); row1.CreateCell(1).SetCellValue("Customer"); row1.CreateCell(2).SetCellValue("RASP P/N"); row1.CreateCell(3).SetCellValue("Part Name"); row1.CreateCell(4).SetCellValue("Production Unit"); row1.CreateCell(5).SetCellValue("Customer P/N "); row1.CreateCell(6).SetCellValue("Cavity"); row1.CreateCell(7).SetCellValue("Cycletime"); row1.CreateCell(8).SetCellValue("cycletime/cav"); row1.CreateCell(9).SetCellValue("Standard Headcount"); row1.CreateCell(10).SetCellValue("Standard scrap"); row1.CreateCell(11).SetCellValue("Materials P/N"); row1.CreateCell(12).SetCellValue("g/cav(SAP)"); row1.CreateCell(13).SetCellValue("Mold No."); row1.CreateCell(14).SetCellValue("ASS AC"); row1.CreateCell(15).SetCellValue("Work Order"); for (int i = 0; i < result.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(result[i].MIProcessType); rowtemp.CreateCell(1).SetCellValue(result[i].MICustomer); rowtemp.CreateCell(2).SetCellValue(result[i].MISapPN); rowtemp.CreateCell(3).SetCellValue(result[i].MIProductName); rowtemp.CreateCell(4).SetCellValue(result[i].MIInjectionMC); rowtemp.CreateCell(5).SetCellValue(result[i].MICustomerPN); rowtemp.CreateCell(6).SetCellValue(Convert.ToDouble(result[i].MICavity)); rowtemp.CreateCell(7).SetCellValue(Convert.ToDouble(result[i].MICycletime)); rowtemp.CreateCell(8).SetCellValue(Convert.ToDouble(result[i].MICycletimeCav)); rowtemp.CreateCell(9).SetCellValue(Convert.ToDouble(result[i].MIStandardHeadcount)); rowtemp.CreateCell(10).SetCellValue(result[i].MTStandardScrap); rowtemp.CreateCell(11).SetCellValue(result[i].MIMaterialPN); rowtemp.CreateCell(12).SetCellValue(Convert.ToDouble(result[i].MICavityG)); rowtemp.CreateCell(13).SetCellValue(result[i].MIMoldNo); rowtemp.CreateCell(14).SetCellValue(result[i].MIAssAC); rowtemp.CreateCell(15).SetCellValue(result[i].MIWorkOrder); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); var exportFileName = string.Format("{0}{1}.xls", "MaterialInfo", DateTime.Now.ToString("yyyyMMddHHmmss")); return(File(ms, "application/vnd.ms-excel", exportFileName)); }
public ActionResult Video(MaterialSearchModel model, int page = 1) { MaterialFilter filter = new MaterialFilter(); filter.AddTimeFrom = model.AddTimeFrom; filter.AddTimeTo = model.AddTimeTo; filter.MaterialType = (int)MaterialType.Video; filter.Title = model.Title; PageReq <MaterialFilter> pager = PageReq <MaterialFilter> .Create(filter, page); var data = jlService.MaterialPage(pager); return(View(data)); }
/// <summary> /// 查询物料 /// </summary> /// <param name="model"></param> /// <returns></returns> public ActionResult Search(MaterialSearchModel model) { //return IEnumerable<MaterialInfoModel> var totalCount = 0; var result = MaterialBusiness.SearchMaterialPageList(model, out totalCount); var page = new Page(totalCount, model.CurrentPage); //var resultModel = new MaterialSearchResultModel //{ // Models = result.Skip((page.CurrentPage - 1) * page.PageSize).Take(page.PageSize), // Page = page //}; var resultModel = new MaterialSearchResultModel { Models = result, Page = page }; return(View(resultModel)); }
/// <summary> /// 查询物料列表 /// </summary> /// <param name="param"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <MaterialInfoModel> SearchMaterialPageList(MaterialSearchModel param, out int totalCount) { var result = _materialDal.SearchMaterialPageList(param, out totalCount); return(result); }