MES-WEB-API.git

			@@ -6,6 +6,16 @@
			using System.Net.Http;
			using System.Web.Http;
			using WebAPI.Models;
			using NPOI;
			using NPOI.OOXML;
			using NPOI.OpenXml4Net;
			using System.IO;
			using NPOI.HSSF.UserModel;
			using NPOI.SS.UserModel;
			using Newtonsoft.Json;
			using Newtonsoft.Json.Converters;
			using System.Web;
			using System.Net.Http.Headers;

			namespace WebAPI.Controllers.SCGL
			{
			@@ -22,16 +32,56 @@

			[Route("Sc_PackUnionBill/Get_PackUnionBillList")]
			[HttpGet]
			public object GetMateOutBillList(string sWhere)
			public object GetMateOutBillList(int page, int limit, string sWhere)
			{
			List<object> columnNameList = new List<object>();
			try
			{
			ds = oCn.RunProcReturn("select * from h_v_PackUnionBillList order by 日期 desc", "h_v_PackUnionBillList");
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "返回记录成功！";
			objJsonResult.data = ds.Tables[0];
			return objJsonResult;
			int count = 0;
			int pageNum = page;
			int pageSize = limit;
			if (sWhere == null \|\| sWhere.Equals(""))
			{
			sWhere = " where 1=1";
			}
			else
			{
			sWhere = " where 1=1" + sWhere;
			}

			DataSet ds0 = oCn.RunProcReturn("select count(*) from h_v_PackUnionBillList " + sWhere, "h_v_PackUnionBillList");
			count = ds0.Tables[0].Rows.Count;
			//string sql = string.Format(@"select top " + pageSize + " hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from(select row_number() over (order by hmainid desc) as RowNumber,hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere + ") as A where RowNumber >" + pageSize + " *(" + pageNum + "-1)");
			string sql = string.Format(@"select hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere + " order by hmainid offset ((" + pageNum + "-1)*" + pageSize + ") rows fetch next " + pageSize + " rows only;");
			ds = new SQLHelper.ClsCN().RunProcReturn(sql, "h_v_PackUnionBillList");
			string aa = ds.Tables[0].Columns[0].ToString();

			foreach (DataColumn col in ds.Tables[0].Columns)
			{

			Type dataType = col.DataType;
			string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
			columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
			}

			if (ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "1";
			objJsonResult.count = count;
			objJsonResult.Message = "返回记录成功！";
			objJsonResult.data = JsonConvert.DeserializeObject<DataTable>(JsonConvert.SerializeObject(ds.Tables[0], new IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd HH:mm:ss" })); //序列化DataSet中的时间格式，然后再反序列化回来
			objJsonResult.list = columnNameList;
			return objJsonResult;
			}
			else
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "暂无记录！";
			objJsonResult.data = null;
			objJsonResult.list = columnNameList;
			return objJsonResult;
			}
			}
			catch (Exception ex)
			{
			@@ -54,111 +104,123 @@
			[HttpGet]
			public object set_DeleteBill(string HInterID, string user)
			{
			//编辑权限
			if (!DBUtility.ClsPub.Security_Log_second("Sc_PackUnionBill", 1, true, user))
			try
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "无删除权限！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//编辑权限
			//if (!DBUtility.ClsPub.Security_Log_second("Sc_PackUnionBill", 1, false, user))
			//{
			// objJsonResult.code = "0";
			// objJsonResult.count = 0;
			// objJsonResult.Message = "无删除权限！";
			// objJsonResult.data = null;
			// return objJsonResult;
			//}

			string s = "";
			Int64 lngBillKey = 0;
			lngBillKey = DBUtility.ClsPub.isLong(HInterID);
			if (lngBillKey == 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据ID为空！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			DLL.ClsKf_MateOutBackBill oBill = new DLL.ClsKf_MateOutBackBill();
			if (oBill.ShowBill(lngBillKey, ref DBUtility.ClsPub.sExeReturnInfo))
			{
			if (oBill.omodel.HBillStatus > 1)
			string s = "";
			Int64 lngBillKey = 0;
			lngBillKey = DBUtility.ClsPub.isLong(HInterID);
			if (lngBillKey == 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据当前处于不能删除状态,不能删除！";
			objJsonResult.Message = "单据ID为空！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			if (oBill.omodel.HChecker != "" && oBill.omodel.HChecker != null)
			DAL.ClsSc_PackUnionBill oBill = new DAL.ClsSc_PackUnionBill();
			if (oBill.ShowBill(lngBillKey, ref DBUtility.ClsPub.sExeReturnInfo))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据已经审核,不能删除！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			if (!DBUtility.Xt_BaseBillFun.Fun_AllowDeleteBill(oBill, ref s))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = s + "，不允许删除";
			objJsonResult.data = null;
			return objJsonResult;
			}
			ds = oCn.RunProcReturn("Select * from Sc_PackUnionBillMain where HinterID='" + lngBillKey, "Sc_PackUnionBillMain");
			var HBarCode_Pack = ds.Tables[0].Rows[0]["HBarCode_Pack"].ToString();
			if (ds.Tables.Count < 1)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "不存在箱码";
			if (oBill.omodel.HBillStatus > 1)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据当前处于不能删除状态,不能删除！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			if (oBill.omodel.HChecker != "" && oBill.omodel.HChecker != null)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据已经审核,不能删除！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			if (!DBUtility.Xt_BaseBillFun.Fun_AllowDeleteBill(oBill, ref s))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = s + "，不允许删除";
			objJsonResult.data = null;
			return objJsonResult;
			}
			ds = oCn.RunProcReturn("Select * from Sc_PackUnionBillMain where HinterID=" + lngBillKey, "Sc_PackUnionBillMain");
			var HBarCode_Pack = ds.Tables[0].Rows[0]["HBarCode_Pack"].ToString();
			if (ds.Tables.Count < 1)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "不存在箱码";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			//判断是否入库
			ds = oCn.RunProcReturn("select * from Sc_ScanLineInStock where HBarCode_Pack='" + HBarCode_Pack + "'", "Sc_ScanLineInStock");
			if (ds != null && ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "该箱条码已入库不允许取消!";
			objJsonResult.data = null;
			return objJsonResult;
			}

			ds = oCn.RunProcReturn("Select 1 from Gy_BarCodeBill where HBarCode='" + HBarCode_Pack + "'", "Gy_BarCodeBill");
			if (ds == null && ds.Tables[0].Rows.Count == 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "无效箱条码!";
			objJsonResult.data = null;
			return objJsonResult;
			}
			ds = oCn.RunProcReturn("Select 1 from Gy_BarCodeBill where HBarCode='" + HBarCode_Pack + "' and HStopflag='1'", "Gy_BarCodeBill");
			if (ds != null && ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "此箱条码已作废!";
			objJsonResult.data = null;
			return objJsonResult;
			}
			}

			oCn.BeginTran();
			oCn.RunProc("Update Gy_BarCodeBill set HStopflag='1' where HBarCode='" + HBarCode_Pack + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillSub_Sum where HBarCode_Pack='" + HBarCode_Pack + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillSub where HInterID='" + lngBillKey + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillMain where HInterID='" + lngBillKey + "'");
			oCn.Commit();
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "删除成功";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			//判断是否入库
			ds = oCn.RunProcReturn("select * from Sc_ScanLineInStock where HBarCode_Pack='" + HBarCode_Pack + "'", "Sc_ScanLineInStock");
			if (ds != null && ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "该箱条码已入库不允许取消!";
			objJsonResult.data = null;
			return objJsonResult;
			}

			ds = oCn.RunProcReturn("Select 1 from Gy_BarCodeBill where HBarCode='" + HBarCode_Pack + "'", "Gy_BarCodeBill");
			if (ds == null && ds.Tables[0].Rows.Count == 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "无效箱条码!";
			objJsonResult.data = null;
			return objJsonResult;
			}
			ds = oCn.RunProcReturn("Select 1 from Gy_BarCodeBill where HBarCode='" + HBarCode_Pack + "' and HStopflag='1'", "Gy_BarCodeBill");
			if (ds != null && ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "-1";
			objJsonResult.Message = "此箱条码已作废!";
			objJsonResult.data = null;
			return objJsonResult;
			}
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据未找到";
			objJsonResult.data = null;
			return objJsonResult;
			}

			oCn.BeginTran();
			oCn.RunProc("Update Gy_BarCodeBill set HStopflag='1' where HBarCode='" + HBarCode_Pack + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillSub_Sum where HBarCode_Pack='" + HBarCode_Pack + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillSub where HInterID='" + lngBillKey + "'");
			oCn.RunProc("Delete From Sc_PackUnionBillMain where HInterID='" + lngBillKey + "'");
			oCn.Commit();
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "删除成功";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			catch (Exception e)
			{
			oCn.RollBack();
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "单据未找到";
			objJsonResult.Message = e.Message;
			objJsonResult.data = null;
			return objJsonResult;
			}
			@@ -182,5 +244,102 @@
			return new ApiResult<DataSet> { code = 1, msg = "查询成功", data = dataSet };
			}
			#endregion

			#region 标准工时单列表

			[Route("Sc_PackUnionBill/GetSc_StationInBillList")]
			[HttpGet]
			public object GetSc_StationInBillList(string sWhere)
			{
			try
			{
			ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain"+sWhere+ "order by hmainid desc", "h_v_Sc_StationInBillListMain");
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "返回记录成功！";
			objJsonResult.data = ds.Tables[0];
			return objJsonResult;
			}
			catch (Exception ex)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "没有返回任何记录！" + ex.ToString();
			objJsonResult.data = null;
			return objJsonResult;
			}
			}

			#endregion

			#region 标准工时导出Excel表格数据
			[Route("Sc_PackUnionBill/Sc_StationInBillSetExcel")]
			[HttpGet]
			public HttpResponseMessage Sc_StationInBillSetExcel()
			{
			HSSFWorkbook workbook = new HSSFWorkbook();
			ISheet sheet = workbook.CreateSheet("sheet1");
			IRow row = sheet.CreateRow(0);


			row.CreateCell(0).SetCellValue("进站生产订单号");
			row.CreateCell(1).SetCellValue("流转卡号");
			row.CreateCell(2).SetCellValue("进出站所用时间");
			row.CreateCell(3).SetCellValue("工时");
			row.CreateCell(4).SetCellValue("不良数量");
			row.CreateCell(5).SetCellValue("报废数量");
			row.CreateCell(6).SetCellValue("进站数量");
			row.CreateCell(7).SetCellValue("出站数量");
			row.CreateCell(8).SetCellValue("出站时间");
			row.CreateCell(9).SetCellValue("进站时间");
			row.CreateCell(10).SetCellValue("制单人");
			row.CreateCell(11).SetCellValue("制单日期");
			//精确控制列宽
			sheet.SetColumnWidth(1, 5000);
			sheet.SetColumnWidth(2, 5000);
			sheet.SetColumnWidth(3, 5000);
			sheet.SetColumnWidth(4, 5000);
			sheet.SetColumnWidth(5, 5000);
			sheet.SetColumnWidth(6, 5000);
			sheet.SetColumnWidth(7, 5000);
			sheet.SetColumnWidth(8, 5000);
			sheet.SetColumnWidth(9, 5000);
			sheet.SetColumnWidth(10, 5000);

			DataSet ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");
			for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
			{
			IRow row1 = sheet.CreateRow(i + 1);
			row1.CreateCell(0).SetCellValue(ds.Tables[0].Rows[i]["进站生产订单号"].ToString());
			row1.CreateCell(1).SetCellValue(ds.Tables[0].Rows[i]["流转卡号"].ToString());
			row1.CreateCell(2).SetCellValue(ds.Tables[0].Rows[i]["进出站所用时间"].ToString());
			row1.CreateCell(3).SetCellValue(ds.Tables[0].Rows[i]["工时"].ToString());
			row1.CreateCell(4).SetCellValue(ds.Tables[0].Rows[i]["不良数量"].ToString());
			row1.CreateCell(5).SetCellValue(ds.Tables[0].Rows[i]["报废数量"].ToString());
			row1.CreateCell(6).SetCellValue(ds.Tables[0].Rows[i]["进站数量"].ToString());
			row1.CreateCell(7).SetCellValue(ds.Tables[0].Rows[i]["出站数量"].ToString());
			row1.CreateCell(8).SetCellValue(ds.Tables[0].Rows[i]["出站时间"].ToString());
			row1.CreateCell(9).SetCellValue(ds.Tables[0].Rows[i]["进站时间"].ToString());
			row1.CreateCell(10).SetCellValue(ds.Tables[0].Rows[i]["制单人"].ToString());
			row1.CreateCell(11).SetCellValue(ds.Tables[0].Rows[i]["制单日期"].ToString());
			}
			System.IO.MemoryStream ms = new System.IO.MemoryStream();
			workbook.Write(ms);
			ms.Position = 0;

			var response = new HttpResponseMessage(HttpStatusCode.OK);
			response.Content = new StreamContent(ms);

			response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
			var fileName = "标准工时_.xls";

			response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
			{
			FileName = System.Web.HttpUtility.UrlEncode(fileName)
			};
			return response;
			}

			#endregion
			}
			}