Estsh-Yiri/Estsh.Core.Services/Services/ProductionOrderEmergencyReleaseService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Estsh.Web.Util;
using System.Data;
using Estsh.Web.Dal;
using System.Collections;
using System.IO;
using NPOI.HSSF.UserModel;
using Com.Estsh.MES.App;
using System.Text;
using Aspose.Cells;
using Estsh.Web.Models;

namespace Estsh.Core.Services
{
    public class ProductionOrderEmergencyReleaseService
    {
        private ProductionOrderEmergencyReleaseDal dal = new ProductionOrderEmergencyReleaseDal(RemotingProxyProvider._remotingProxy);

        /// <summary>
        /// 获取客户产线列表
        /// </summary>
        /// <returns></returns>
        public ArrayList GetCustPDLine()
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetCustPDLine();
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 获取配置名称
        /// </summary>
        /// <returns></returns>
        //public ArrayList GetModelName()
        //{
        //    Hashtable result = new Hashtable();
        //    DataTable dt = dal.GetModelName();
        //    return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        //}
        /// <summary>
        /// 根据客户产线查询车型
        /// </summary>
        /// <returns></returns>
        public ArrayList GetMode_type(string CustPDLine)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetMode_type(CustPDLine);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 根据车型编号查询座椅配置
        /// </summary>
        /// <returns></returns>
        public ArrayList GetModelList(int ModelType)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetModelList(ModelType);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 获取班次
        /// </summary>
        /// <returns></returns>
        public ArrayList Getshift()
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.Getshift();
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 获取生产类型
        /// </summary>
        /// <returns></returns>
        public ArrayList GetProdType()
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetProdType();
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 根据配置编号获取总成清单
        /// </summary>
        /// <param name="modelID">配置编号</param>
        /// <returns>总成清单</returns>
        public ArrayList GetBomDetail(int modelID)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetBomDetail(modelID);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 根据配置编号获取总成清单
        /// </summary>
        /// <param name="modelID">配置编号</param>
        /// <returns>总成清单</returns>
        public DataTable GetBomDetailTable(int modelID)
        {
            DataTable dt = dal.GetBomDetail(modelID);
            return dt;
        }

        //库存插单保存按钮
        public bool isExist(string order_no)
        {
            return dal.isExist(order_no);
        }

        //加载单据编号
        public string GetOrderNo(string stockOrder, string p)
        {
            return dal.GetOrderNo(stockOrder, p);
        }

        public bool Insert_g_stock_order(int model_type_id, int model_id, string order_no, string cust_pdline, int shift_id, int qty, string type, string document_data)
        {
            return dal.Insert_g_stock_order(model_type_id, model_id, order_no, cust_pdline, shift_id, qty, type, document_data);

        }

        public bool Insert_g_stock_order_detail(string order_no, string part_id)
        {
            return dal.Insert_g_stock_order_detail(order_no, part_id);
        }

        ///<summary>
        /// 调用存储过程 dbo.sys_release_stock_order 释放生产指令
        /// </summary>
        /// <returns></returns>
        public string ReleaseStockOrder(string order_no,int ruid)
        {
            return dal.ReleaseStockOrder(order_no, ruid);
        }

        ///<summary>
        /// 调用存储过程 dbo.sys_assign_stock_order 分配虚拟料架
        /// </summary>
        /// <returns></returns>
        public string AssignStockOrder(string custPDLine)
        {
            return dal.AssignStockOrder(custPDLine);
        }

        /// <summary>
        /// 获取要打印的条码数据（前排）
        /// </summary>
        /// <returns></returns>
        public ArrayList GetSN(string orderNo)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetSN(orderNo);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 更新条码的打印状态
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>是否成功</returns>
        //public bool UpdateSNPrintStatus(string serialNumber)
        //{
        //    return dal.UpdateSNPrintStatus(serialNumber);
        //}

        /// <summary>
        /// 获取要打印的条码数据（后排）
        /// </summary>
        /// <returns></returns>
        public ArrayList GetBackSN(string orderNo)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetBackSN(orderNo);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 初始化全局的静态变量
        /// </summary>
        /// <param name="proxy"></param>
        public string TrayPagePrintCount(string param)
        {
            return dal.TrayPagePrintCount(param);
        }


        ///////////////////////////////////////////////////////////////////////////////////////////////////

        //条码补打

        /// <summary>
        /// 获取补打信息
        /// </summary>
        /// <param name="serialNumber">过程条码 OR 合格证条码</param>
        /// <returns></returns>
        public ArrayList GetReprintDataBySN(string type, string serialNumber, int count, bool isOrderByInLineTime)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetReprintDataBySN(type, serialNumber, count, isOrderByInLineTime);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 更新条码的补打状态
        /// </summary>
        /// <param name="sn">条码</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateSNReprintStatus(string serialNumber)
        {
            return dal.UpdateSNReprintStatus(serialNumber);
        }

        /// <summary>
        /// 获取要 打印过程条码 信息
        /// </summary>
        /// <param name="serialNumber"></param>
        //public void PrintSerialNumber(string serialNumber)
        public ArrayList PrintSerialNumber(string serialNumber)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.PrintSerialNumber(serialNumber);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 根据配置编号获取过程条码打印的张数
        /// </summary>
        public int GetModelBarcodeCount(string serialNumber)
        {
            return dal.GetModelBarcodeCount(serialNumber);
        }

        /// <summary>
        /// 更新条码的打印状态
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>是否成功</returns>
        public bool UpdateSNPrintStatus(string serialNumber)
        {
            return dal.UpdateSNPrintStatus(serialNumber);
        }

        // 打印合格证

        /// <summary>
        /// 根据配置编号获取合格证条码打印的张数
        /// </summary>
        public int GetModelQACount(string serialNumber)
        {
            return dal.GetModelQACount(serialNumber);
        }

        /// <summary>
        /// 根据零件号查询零件ID
        /// </summary>
        public int getPartId(string partNo)
        {
            return dal.getPartId(partNo);
        }

        //打印后排的的合格证条码

        /// <summary>
        /// 根据条码获取对应后排条码的打印信息
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <returns>后排条码的打印信息</returns>
        public ArrayList GetBackSNs(string serialNumber)
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetBackSNs(serialNumber);
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }


        /// <summary>
        /// 读取采购订单文件并更新至数据库
        /// </summary>
        /// <param name="inputStream">文件全路径</param>
        /// <param name="userInfo">登录用户信息</param>
        public Hashtable ReadExcelFile(Stream inputStream, int userId)
        {
            Hashtable result = new Hashtable();
            try
            {
                
                Hashtable Cache = new Hashtable();
                DataTable dt = AsposeExcelTools.ExcelFileToDataTable(inputStream);

                DataView dv = new DataView(dt);


                //去除第一行&重新排序

                dv.AllowDelete = true;
                dv[0].Delete();
                //dv.Sort = "Column1";
                dt = dv.ToTable();

                StringBuilder ErrorMessage = new StringBuilder(1024);

                // 验证数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i == 13207)
                    {
                        string a = i.ToString();
                    }

                    if (string.IsNullOrEmpty(dt.Rows[i][0].ToString()) || string.IsNullOrEmpty(dt.Rows[i][2].ToString()) || string.IsNullOrEmpty(dt.Rows[i][3].ToString()) || string.IsNullOrEmpty(dt.Rows[i][4].ToString()) || string.IsNullOrEmpty(dt.Rows[i][5].ToString()) || string.IsNullOrEmpty(dt.Rows[i][6].ToString()) || string.IsNullOrEmpty(dt.Rows[i][7].ToString()))
                    {
                        ErrorMessage.Append("行" + (i + 1).ToString() + ":信息不能为空!" + Environment.NewLine);
                    }
                    else
                    {

                        //零件号查询
                        string partNo = dt.Rows[i][4].ToString();
                        int partNoId = dal.GetPartID(partNo);

                        if (partNoId <= 0)
                        {
                            ErrorMessage.Append("配置零件号“" + partNo + "” 不存在!" + Environment.NewLine);
                        }

                        ////配置名称查询
                        //string model_name = dt.Rows[i][0].ToString();
                        //int model_id = dal.GetModelName(model_name);

                        //if (string.IsNullOrEmpty(model_name.ToString()))
                        //{
                        //    ErrorMessage.Append("配置描述 “" + model_id + "” 不存在!" + Environment.NewLine);
                        //}

                        //客户产线查询
                        string cust_pdline = dt.Rows[i][0].ToString();
                        string cust_pdline_name = dal.GetCustPdline(cust_pdline);

                        if (string.IsNullOrEmpty(cust_pdline_name.ToString()))
                        {
                            ErrorMessage.Append("客户编号 “" + cust_pdline + "” 不存在!" + Environment.NewLine);
                        }

                        //班次查询
                        string shiftName = dt.Rows[i][2].ToString();
                        int shiftId = dal.GetShiftId(shiftName);

                        if (shiftId <= 0)
                        {
                            ErrorMessage.Append("班次“" + shiftName + "” 不存在!" + Environment.NewLine);
                        }

                        //生产类型查询
                        string ProdType = dt.Rows[i][6].ToString();
                        int ProdTypeId = dal.GetProdTypeId(ProdType);

                        if (ProdTypeId <= 0)
                        {
                            ErrorMessage.Append("生产类型“" + ProdType + "” 不存在!" + Environment.NewLine);
                        }

                        //零件和客户产线关系
                        int custPdlinePart = dal.GetCustPdlinePart(partNo, cust_pdline);

                        if (custPdlinePart <= 0)
                        {
                            ErrorMessage.Append("行" + (i + 1).ToString() + ":客户与配置零件号对应关系不正确!" + Environment.NewLine);
                        }

                        //零件和客户产线关系
                        string orderData = dt.Rows[i][3].ToString();
                        string batchNumber = dt.Rows[i][7].ToString();
                        int ifExist = dal.GetOrderNo(partNoId.ToString(), cust_pdline_name, orderData, batchNumber, shiftId.ToString(), ProdType);

                        if (ifExist > 0)
                        {
                            ErrorMessage.Append("行" + (i + 1).ToString() + ":数据已导入!" + Environment.NewLine);
                        }
                    }
                }

                if (dt == null)
                {
                    result.Add("message", "导入数据为空，请重新导入");
                    result.Add("flag", "error");
                    return result;
                }

                // 校验数据时发生错误
                if (ErrorMessage.Length != 0)
                {
                    result.Add("message", ErrorMessage.ToString());
                    result.Add("flag", "error");
                    return result;
                }

               

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string orderNo = dal.GetOrderNo("StockOrder", "A");

                    if (i == 13207)
                    {
                        string a = i.ToString();
                    }
                    string documentData = dt.Rows[i][3].ToString();
                    int qty = Convert.ToInt32(dt.Rows[i][5].ToString());
                    string shiftName = dt.Rows[i][2].ToString();
                    int shiftId = dal.GetShiftId(shiftName);
                    string ProdType = dt.Rows[i][6].ToString();
                    string partNo = dt.Rows[i][4].ToString();
                    string batchNumber = dt.Rows[i][7].ToString();
                    dal.ReleaseStockOrderImport(orderNo, partNo, shiftId, documentData, qty, ProdType,batchNumber, userId);
                }


                result.Add("message", "导入成功");
                result.Add("flag", "error");
                return result;
            }
            catch (Exception ex)
            {
                result.Add("message", "导入失败");
                result.Add("flag", "error");
                return result;
            }
        }

        /// <summary>
        /// 根据分页条件获取分页菜单数据
        /// </summary>
        /// <param name="AdjustOrderName"></param>
        /// <param name="pager"></param>
        /// <param name="direction"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public Hashtable getProductionOrderListByPage(Pager pager, String direction, String sort, string cbCustPDLine)
        {
            Hashtable result = new Hashtable();

            String strWhere = " p.cust_pdline_name='" + cbCustPDLine.ToString() + "'";
            strWhere += " and g.work_flag <0 ";
            strWhere += " and  a.create_ymd>(SELECT CONVERT(VARCHAR(10), GETDATE()- 15, 120))";
            strWhere += " AND a.enabled='Y'";

            String orderBy = " a.seq ";
            result = dal.getProductionOrderListByPage(pager.pageSize, pager.pageNo, strWhere, orderBy);
            return result;
        }

    }
}