mes-client/FileWatcher/UploadMakePlan.cs

using System;
using System.IO;
using System.Windows.Forms;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Office.Interop.Excel;

namespace FileWatcher
{
    public partial class UploadMakePlan : Form
    {

        string connectionString = "Connection Timeout=0;Pooling=false;Password=select!#%*(;User ID=N_MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.7)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";

        StringBuilder sql = new StringBuilder();

        DataHelper dh;

        public UploadMakePlan()
        {
            InitializeComponent();
            StartPosition = FormStartPosition.CenterScreen;
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            dh = new DataHelper(connectionString);
            DateTime.Value = DateTime.Value.AddDays(1);
        }

        private void Upload_Click(object sender, EventArgs e)
        {

            DateTime today = DateTime.Value;
            string todayDate = today.ToString("MMdd");
            string code = "";
            string[] param = new string[] { "MakePlan", "1", code };
            dh.CallProcedure("SP_GETMAXNUMBER", ref param);
            code = param[2];
            string id = "";
            System.Data.DataTable dt = (System.Data.DataTable)dh.ExecuteSql("select mp_id from MakePlan where to_char(mp_begintime,'yyyymmdd')='" + today.ToString("yyyyMMdd") + "'", "select");
            if (dt.Rows.Count == 0)
            {
                id = dh.GetSEQ("MakePlan_seq");
                dh.ExecuteSql("insert into MakePlan(MP_ID, MP_CODE, MP_KIND, MP_INDATE, MP_STATUS, MP_STATUSCODE, MP_BEGINTIME)" +
                    "values(" + id + ",'" + code + "','日计划',sysdate,'在录入','ENTERING',to_date('" + today.ToString("yyyyMMdd") + "','yyyymmdd'))", "insert");
            }
            else
            {
                id = dt.Rows[0]["mp_id"].ToString();
                dh.ExecuteSql("delete from MakePlandetail where mpd_mpid=" + id, "delete");
            }
            string ErrMessage = "";
            using (FileStream file = new FileStream(FilePath.Text, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                int detno = 1;

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    string sheetName = sheet.SheetName;
                    if (sheetName == "工艺库")
                    {
                        continue;
                    }
                    {
                        //Console.WriteLine($"Processing sheet: {sheetName}");
                        // 获取标题行（第4行）
                        string wccode = sheet.GetRow(2).GetCell(1).StringCellValue; // 第4行索引为3
                        if (wccode == "")
                        {
                            OperateResult.AppendText("工作中心不存在");
                            return;
                        }
                        IRow headerRow = sheet.GetRow(4); // 第4行索引为3
                        if (headerRow == null)
                        {
                            Console.WriteLine("Header row not found.");
                            continue;
                        }

                        // 获取标题列的索引
                        int dateIndex = -1;
                        int orderdetnoIndex = -1;
                        int ordercodeIndex = -1;
                        int planqtyIndex = -1;
                        int remarkIndex = -1;
                        int stepcodeIndex = -1;

                        for (int colIndex = 0; colIndex < headerRow.LastCellNum; colIndex++)
                        {
                            ICell cell = headerRow.GetCell(colIndex);
                            if (cell != null)
                            {
                                string headerValue = cell.ToString().Trim().Replace("\r\n", "").Replace("\n", "").Replace("\r", "");
                                if (headerValue == "订单序号")
                                {
                                    orderdetnoIndex = colIndex;
                                }
                                else if (headerValue == "订单编号")
                                {
                                    ordercodeIndex = colIndex;
                                }
                                else if (headerValue == "计划数量")
                                {
                                    planqtyIndex = colIndex;
                                }
                                else if (headerValue == "工序名称")
                                {
                                    stepcodeIndex = colIndex;
                                }
                                else if (headerValue == "日期")
                                {
                                    dateIndex = colIndex;
                                }
                                else if (headerValue.Contains("备注") && !headerValue.Contains("生产异常备注"))
                                {
                                    remarkIndex = colIndex;
                                }
                            }
                        }
                        if (orderdetnoIndex == -1 || ordercodeIndex == -1 || planqtyIndex == -1 || remarkIndex == -1 || stepcodeIndex == -1)
                        {
                            OperateResult.AppendText("列头缺少，请检查表结构");
                            return;
                        }
                        bool GetPlan = false;
                        using (OracleConnection conn = new OracleConnection(connectionString))
                        {
                            conn.Open();
                            for (int rowIndex = 5; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第5行开始解析数据
                            {
                                IRow row = sheet.GetRow(rowIndex);
                                if (row != null)
                                {
                                    string date = row.GetCell(dateIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    if (date == "")
                                    {
                                        break;
                                    }
                                    string orderdetno = row.GetCell(orderdetnoIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    string ordercode = row.GetCell(ordercodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    string planqty = row.GetCell(planqtyIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).NumericCellValue.ToString();
                                    string remark = row.GetCell(remarkIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    string stepcode = row.GetCell(stepcodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    if (todayDate == date && !string.IsNullOrEmpty(wccode) && !string.IsNullOrEmpty(orderdetno) && !string.IsNullOrEmpty(ordercode))
                                    {
                                        string day = today.ToString("yyyy-MM-dd");
                                        if (dh.CheckExist("MakePlanDetail left join makeplan on mpd_mpid=mp_id ", "trunc(mp_begintime)=to_date('" + day + "','yyyy-mm-dd') and mpd_orderdetno='" + orderdetno + "' and mpd_ordercode='" + ordercode + "' and mpd_wccode='" + wccode + "'"))
                                        {
                                            ErrMessage += " 序号'" + orderdetno + "' 销售订单'" + ordercode + "' 工作中心'" + wccode + "' 重复\n";
                                        }
                                        //销售订单 + 订单序号存在ERP中，才允许上传
                                        if (!dh.CheckExist("saledetail@ERP left join sale@ERP on sa_id=sd_said", "SD_DETNO='" + orderdetno + "' and sa_code='" + ordercode + "'"))
                                        {
                                            ErrMessage += " 序号'" + orderdetno + "' 销售订单'" + ordercode + "'不存在\n";
                                        }
                                        if (!dh.CheckExist("make", "ma_saledetno='" + orderdetno + "' and ma_salecode='" + ordercode + "'"))
                                        {
                                            ErrMessage += " 序号'" + orderdetno + "' 销售订单'" + ordercode + "'未匹配到工单号\n";
                                        }
                                        //dt = (System.Data.DataTable)dh.ExecuteSql("select * from saledetail@ERP left join sale@ERP on sa_id=sd_said left join " +
                                        //    "(select min(mpd_outqty)mpd_outqty, mpd_ordercode,mpd_orderdetno  from(select mpd_stepcode,nvl(sum(mpd_outqty),0)mpd_outqty, " +
                                        //    "mpd_ordercode, mpd_orderdetno from MakePlanDetail where mpd_wccode='" + wccode + "' and nvl(mpd_stepcode,' ')='" + stepcode + "' group by mpd_ordercode, mpd_orderdetno,mpd_stepcode)group " +
                                        //    "by mpd_ordercode, mpd_orderdetno,mpd_stepcode) on sa_code = mpd_ordercode and sd_detno =mpd_orderdetno " +
                                        //    "where sd_qty<nvl(mpd_outqty,0)+" + planqty + " and sa_code='" + ordercode + "' and sd_detno='" + orderdetno + "'", "select");
                                        //if (dt.Rows.Count > 0)
                                        //{
                                        //    OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "'工序" + stepcode + "累计排产数量超出");
                                        //    return;
                                        //}
                                        string insertQuery = "INSERT INTO MakePlanDetail (mpd_mpid,mpd_detno,mpd_id,mpd_wccode, mpd_orderdetno, mpd_ordercode,mpd_outqty,mpd_remark,mpd_stepcode) VALUES (" + id + "," + detno + ",MakePlanDetail_seq.nextval,:1, :2, :3,:4,:5,:6)";
                                        using (OracleCommand cmd = new OracleCommand(insertQuery, conn))
                                        {
                                            cmd.Parameters.Add(new OracleParameter(":1", wccode));
                                            cmd.Parameters.Add(new OracleParameter(":2", orderdetno));
                                            cmd.Parameters.Add(new OracleParameter(":3", ordercode));
                                            cmd.Parameters.Add(new OracleParameter(":4", planqty));
                                            cmd.Parameters.Add(new OracleParameter(":5", remark));
                                            cmd.Parameters.Add(new OracleParameter(":6", stepcode));
                                            cmd.ExecuteNonQuery();
                                        }
                                        GetPlan = true;
                                    }
                                }
                                detno = detno + 1;
                            }
                        }
                        dh.ExecuteSql("delete from MakePlanDetail where mpd_orderdetno is null", "delete");
                        if (GetPlan)
                        {
                            OperateResult.AppendText($"Processing sheet: {sheetName}计划上传成功\n");
                        }
                        else
                        {
                            OperateResult.AppendText($"Processing sheet: {sheetName}没有需要上传的计划\n");
                        }
                    }
                }
                string Error = "";
                param = new string[] { id, "0", Error };
                if (ErrMessage == "")
                {
                    dh.CallProcedure("USER_PLANSPLIT_COMMIT", ref param);
                    dh.CallProcedure("USER_PLANINSERT_WORK", ref param);
                }
                else
                {
                    OperateResult.AppendText(ErrMessage);
                }
            }
        }

        private void ChooseFile_Click(object sender, EventArgs e)
        {
            DialogResult result;
            result = ImportExcel1.ShowDialog();
            if (result == DialogResult.OK)
            {
                FilePath.Text = ImportExcel1.FileName;
            }
        }
    }
}