智云MES后端API代码
blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
1
duhe
4 天以前 d703d7cc2f3a86692b1db16793ff2ffc60a1a355 
 WebAPI/Controllers/SCGL/Sc_PackUnionBillController.cs


@@ -14,6 +14,8 @@


using NPOI.SS.UserModel;


using Newtonsoft.Json;


using Newtonsoft.Json.Converters;


using System.Web;


using System.Net.Http.Headers;




namespace WebAPI.Controllers.SCGL


{


@@ -26,29 +28,45 @@


        public DAL.ClsSc_PackUnionBill BillNew0 = new DAL.ClsSc_PackUnionBill();   //


        public DAL.ClsSc_PackUnionBill BillOld0 = new DAL.ClsSc_PackUnionBill();   //




        #region 组托单列表




        #region [组托单查询功能]


        /// <summary>


        /// 组托单查询功能


        /// </summary>


        /// <returns></returns>


        [Route("Sc_PackUnionBill/Get_PackUnionBillList")]


        [HttpGet]


        public object GetMateOutBillList(int page, int limit, string sWhere)


        public object Get_PackUnionBillList(int page, int limit, string sWhere,string user)


        {


            List<object> columnNameList = new List<object>();


            try


            {


                //判断是否有查询权限


                if (!DBUtility.ClsPub.Security_Log("Sc_PackUnionBill_Query",1,false,user))


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = "无权限查询!";


                    objJsonResult.data = null;


                    return objJsonResult;


                }




                int count = 0;


                int pageNum = page;


                int pageSize = limit;


                if (sWhere == null || sWhere.Equals(""))


                {


                    sWhere = " where 1=1";


                    sWhere = " where 1=1 ";


                }


                else


                {


                    sWhere = " where 1=1" + sWhere;


                }




                count = new SQLHelper.ClsCN().RunProcReturn("select hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere, "h_v_PackUnionBillList").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)");


                DataSet ds0 = oCn.RunProcReturn("select count(*) CountNum from h_v_PackUnionBillList "+sWhere+"", "h_v_PackUnionBillList");


                count = int.Parse(ds0.Tables[0].Rows[0]["CountNum"].ToString());


                //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 desc  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();




@@ -60,24 +78,24 @@


                    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;


                }


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


            {


@@ -103,14 +121,14 @@


            try


            {


                //编辑权限


                //if (!DBUtility.ClsPub.Security_Log_second("Sc_PackUnionBill", 1, true, user))


                //{


                //    objJsonResult.code = "0";


                //    objJsonResult.count = 0;


                //    objJsonResult.Message = "无删除权限！";


                //    objJsonResult.data = null;


                //    return objJsonResult;


                //}


                if (!DBUtility.ClsPub.Security_Log_second("Sc_PackUnionBill_Drop", 1, false, user))


                {


                    objJsonResult.code = "0";


                    objJsonResult.count = 0;


                    objJsonResult.Message = "无删除权限！";


                    objJsonResult.data = null;


                    return objJsonResult;


                }




                string s = "";


                Int64 lngBillKey = 0;


@@ -150,7 +168,7 @@


                        objJsonResult.data = null;


                        return objJsonResult;


                    }


                    ds = oCn.RunProcReturn("Select * from Sc_PackUnionBillMain where HinterID='" + lngBillKey, "Sc_PackUnionBillMain");


                    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)


                    {


@@ -249,7 +267,8 @@


        {


            try


            {


                ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");


                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 = "返回记录成功！";


@@ -269,81 +288,76 @@


        #endregion




        #region 标准工时导出Excel表格数据




        [Route("Sc_PackUnionBill/Sc_StationInBillSetExcel")]


        [HttpGet]


        public object Sc_StationInBillSetExcel()


        public HttpResponseMessage Sc_StationInBillSetExcel(string sWhere)


        {


            try


            {


                ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");


                IWorkbook book = new HSSFWorkbook();


                ISheet sheet = book.CreateSheet("sheet1");


                IRow row1 = sheet.CreateRow(0);


                row1.CreateCell(0).SetCellValue("进站生产订单号");


                row1.CreateCell(1).SetCellValue("流转卡号");


                row1.CreateCell(2).SetCellValue("进出站所用时间");


                row1.CreateCell(3).SetCellValue("工时");


                row1.CreateCell(4).SetCellValue("不良数量");


                row1.CreateCell(5).SetCellValue("报废数量");


                row1.CreateCell(6).SetCellValue("进站数量");


                row1.CreateCell(7).SetCellValue("出站数量");


                row1.CreateCell(8).SetCellValue("出站时间");


                row1.CreateCell(9).SetCellValue("进站时间");


                row1.CreateCell(10).SetCellValue("制单人");


                row1.CreateCell(11).SetCellValue("制单日期");


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)


                {


                    IRow row = sheet.CreateRow(i + 1);


                    row.CreateCell(0).SetCellValue(ds.Tables[0].Rows[i]["进站生产订单号"].ToString());


                    row.CreateCell(1).SetCellValue(ds.Tables[0].Rows[i]["流转卡号"].ToString());


                    row.CreateCell(2).SetCellValue(ds.Tables[0].Rows[i]["进出站所用时间"].ToString());


                    row.CreateCell(3).SetCellValue(ds.Tables[0].Rows[i]["工时"].ToString());


                    row.CreateCell(4).SetCellValue(ds.Tables[0].Rows[i]["不良数量"].ToString());


                    row.CreateCell(5).SetCellValue(ds.Tables[0].Rows[i]["报废数量"].ToString());


                    row.CreateCell(6).SetCellValue(ds.Tables[0].Rows[i]["进站数量"].ToString());


                    row.CreateCell(7).SetCellValue(ds.Tables[0].Rows[i]["出站数量"].ToString());


                    row.CreateCell(8).SetCellValue(ds.Tables[0].Rows[i]["出站时间"].ToString());


                    row.CreateCell(9).SetCellValue(ds.Tables[0].Rows[i]["进站时间"].ToString());


                    row.CreateCell(10).SetCellValue(ds.Tables[0].Rows[i]["制单人"].ToString());


                    row.CreateCell(11).SetCellValue(ds.Tables[0].Rows[i]["制单日期"].ToString());


                }


                string filename = "C:/Users/Administrator/Desktop/标准工时表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";


                MemoryStream BookStream = new MemoryStream();


                book.Write(BookStream);


                BookStream.Seek(0,SeekOrigin.Begin);


                byte[] buffer = BookStream.ToArray();


                using (FileStream fs = new FileStream(filename, FileMode.CreateNew, FileAccess.Write))


                {


                    try


                    {


                        fs.Write(buffer, 0, buffer.Length);


                        fs.Flush();


                    }


                    catch


                    {


            HSSFWorkbook workbook = new HSSFWorkbook();


            ISheet sheet = workbook.CreateSheet("sheet1");


            IRow row = sheet.CreateRow(0); 


            




                    }


                    finally


                    {


                        fs.Dispose();


                        BookStream.Dispose();


                    }


                }


                objJsonResult.code = "1";


                objJsonResult.count = 1;


                objJsonResult.Message = "成功导出表格至桌面";


                objJsonResult.data = null;


                return objJsonResult;


            }


            catch (Exception ex)


            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("制单人");


            row.CreateCell(12).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);


            sheet.SetColumnWidth(11, 5000);


            sheet.SetColumnWidth(12, 5000);


            sheet.SetColumnWidth(13, 5000);




            DataSet ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain" + sWhere + " order by hmainid desc", "h_v_Sc_StationInBillListMain");


            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)


            {


                objJsonResult.code = "0";


                objJsonResult.count = 0;


                objJsonResult.Message = "导出失败" + ex.ToString();


                objJsonResult.data = null;


                return objJsonResult;


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


                row1.CreateCell(12).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