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