using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Oracle.ManagedDataAccess.Client;

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;

        string imaster;

        public UploadMakePlan()
        {
            InitializeComponent();
            StartPosition = FormStartPosition.CenterScreen;
        }

        string fieltype = "";
        string ftpulr = "";
        private void Form1_Load(object sender, EventArgs e)
        {
            dh = new DataHelper(connectionString);
        }

        private void Upload_Click(object sender, EventArgs e)
        {
            DateTime today = DateTime.Value;
            string todayDate = today.ToString("MMdd");
            string code = dh.GetSerialNumberByCaller("MakePlan");
            string id = "";
            DataTable dt = (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");
            }

            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.Contains(todayDate))
                    {
                        OperateResult.AppendText(sheetName);
                        OperateResult.AppendText($"Processing sheet: {sheetName}\n");
                        //Console.WriteLine($"Processing sheet: {sheetName}");

                        // 获取标题行（第4行）
                        IRow headerRow = sheet.GetRow(3); // 第4行索引为3
                        if (headerRow == null)
                        {
                            Console.WriteLine("Header row not found.");
                            continue;
                        }

                        // 获取标题列的索引
                        int wccodeIndex = -1;
                        int orderdetnoIndex = -1;
                        int ordercodeIndex = -1;
                        int planqtyIndex = -1;
                        int remarkIndex = -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 == "工作中心")
                                {
                                    wccodeIndex = colIndex;
                                }
                                else if (headerValue == "订单序号")
                                {
                                    orderdetnoIndex = colIndex;
                                }
                                else if (headerValue == "订单编号")
                                {
                                    ordercodeIndex = colIndex;
                                }
                                else if (headerValue == "计划数量")
                                {
                                    planqtyIndex = colIndex;
                                }
                                else if (headerValue.Contains("备注") && !headerValue.Contains("生产异常备注"))
                                {
                                    remarkIndex = colIndex;
                                }
                            }
                        }

                        if (wccodeIndex == -1 || orderdetnoIndex == -1 || ordercodeIndex == -1 || planqtyIndex == -1 || remarkIndex == -1)
                        {
                            Console.WriteLine("Required columns not found in the header.");
                            continue;
                        }

                        using (OracleConnection conn = new OracleConnection(connectionString))
                        {
                            conn.Open();
                            for (int rowIndex = 4; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第5行开始解析数据
                            {
                                IRow row = sheet.GetRow(rowIndex);
                                if (row != null)
                                {
                                    string wccode = row.GetCell(wccodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.ToString();
                                    if (wccode == "")
                                    {
                                        continue;
                                    }
                                    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();
                                    if (!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 + "'"))
                                        {
                                            OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "' 工作中心'" + wccode + "' 重复");
                                            return;
                                        }
                                        //销售订单+订单序号存在ERP中，才允许上传
                                        if (dh.CheckExist("saledetail@ERP left join sale@ERP on sa_id=sd_said", "SD_DETNO='" + orderdetno + "' and sa_code='" + ordercode + "'"))
                                        {
                                            OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "'不存在");
                                            return;
                                        }
                                        if (dh.CheckExist("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 sum(mpd_outqty)mpd_outqty, mpd_ordercode, mpd_orderdetnofrom MakePlanDetail  group by " +
                                            "mpd_ordercode, mpd_orderdetno, MPD_WCCODE)group by mpd_ordercode, mpd_orderdetno) on sa_code = mpd_ordercode and sd_detno" +
                                            " = mpd_orderdetno", "sd_qty<mpd_outqty+" + planqty + " and sa_code='" + ordercode + "' and sd_detno='" + orderdetno + "'"))
                                        {
                                            OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "'累计排产数量超出");
                                            return;
                                        }
                                        string insertQuery = "INSERT INTO MakePlanDetail (mpd_mpid,mpd_detno,mpd_id,mpd_wccode, mpd_orderdetno, mpd_ordercode,mpd_outqty,mpd_remark) VALUES (" + id + "," + detno + ",MakePlanDetail_seq.nextval,:1, :2, :3,:4,:5)";
                                        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.ExecuteNonQuery();
                                        }
                                    }
                                }
                                detno = detno + 1;
                            }
                        }
                        dh.ExecuteSql("delete from MakePlanDetail where mpd_orderdetno is null", "delete");
                        OperateResult.AppendText("计划上传成功");
                    }
                }
            }
            //上传日期1
            if (DateTime1.Value != DateTime.Value)
            {
                today = DateTime1.Value;
                todayDate = today.ToString("MMdd");
                code = dh.GetSerialNumberByCaller("MakePlan");
                id = "";
                dt = (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");
                }

                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.Contains(todayDate))
                        {
                            OperateResult.AppendText(sheetName);
                            OperateResult.AppendText($"Processing sheet: {sheetName}\n");
                            //Console.WriteLine($"Processing sheet: {sheetName}");

                            // 获取标题行（第4行）
                            IRow headerRow = sheet.GetRow(3); // 第4行索引为3
                            if (headerRow == null)
                            {
                                Console.WriteLine("Header row not found.");
                                continue;
                            }

                            // 获取标题列的索引
                            int wccodeIndex = -1;
                            int orderdetnoIndex = -1;
                            int ordercodeIndex = -1;
                            int planqtyIndex = -1;
                            int remarkIndex = -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 == "工作中心")
                                    {
                                        wccodeIndex = colIndex;
                                    }
                                    else if (headerValue == "订单序号")
                                    {
                                        orderdetnoIndex = colIndex;
                                    }
                                    else if (headerValue == "订单编号")
                                    {
                                        ordercodeIndex = colIndex;
                                    }
                                    else if (headerValue == "计划数量")
                                    {
                                        planqtyIndex = colIndex;
                                    }
                                    else if (headerValue.Contains("备注") && !headerValue.Contains("生产异常备注"))
                                    {
                                        remarkIndex = colIndex;
                                    }
                                }
                            }

                            if (wccodeIndex == -1 || orderdetnoIndex == -1 || ordercodeIndex == -1 || planqtyIndex == -1 || remarkIndex == -1)
                            {
                                Console.WriteLine("Required columns not found in the header.");
                                continue;
                            }

                            using (OracleConnection conn = new OracleConnection(connectionString))
                            {
                                conn.Open();
                                for (int rowIndex = 4; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第5行开始解析数据
                                {
                                    IRow row = sheet.GetRow(rowIndex);
                                    if (row != null)
                                    {
                                        string wccode = row.GetCell(wccodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.ToString();
                                        if (wccode == "")
                                        {
                                            continue;
                                        }
                                        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();
                                        if (!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 + "'"))
                                            {
                                                string insertQuery = "INSERT INTO MakePlanDetail (mpd_mpid,mpd_detno,mpd_id,mpd_wccode, mpd_orderdetno, mpd_ordercode,mpd_outqty,mpd_remark) VALUES (" + id + "," + detno + ",MakePlanDetail_seq.nextval,:1, :2, :3,:4,:5)";
                                                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.ExecuteNonQuery();
                                                }
                                            }
                                            else
                                            {
                                                OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "' 工作中心'" + wccode + "' 重复");
                                            }
                                        }
                                    }
                                    detno = detno + 1;
                                }
                            }
                            dh.ExecuteSql("delete from MakePlanDetail where mpd_orderdetno is null", "delete");
                            OperateResult.AppendText("计划上传成功");
                        }
                    }
                }
            }

            //上传日期2
            if (DateTime2.Value != DateTime1.Value && DateTime2.Value != DateTime.Value)
            {
                today = DateTime2.Value;
                todayDate = today.ToString("MMdd");
                code = dh.GetSerialNumberByCaller("MakePlan");
                id = "";
                dt = (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");
                }

                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.Contains(todayDate))
                        {
                            OperateResult.AppendText(sheetName);
                            OperateResult.AppendText($"Processing sheet: {sheetName}\n");
                            //Console.WriteLine($"Processing sheet: {sheetName}");

                            // 获取标题行（第4行）
                            IRow headerRow = sheet.GetRow(3); // 第4行索引为3
                            if (headerRow == null)
                            {
                                Console.WriteLine("Header row not found.");
                                continue;
                            }

                            // 获取标题列的索引
                            int wccodeIndex = -1;
                            int orderdetnoIndex = -1;
                            int ordercodeIndex = -1;
                            int planqtyIndex = -1;
                            int remarkIndex = -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 == "工作中心")
                                    {
                                        wccodeIndex = colIndex;
                                    }
                                    else if (headerValue == "订单序号")
                                    {
                                        orderdetnoIndex = colIndex;
                                    }
                                    else if (headerValue == "订单编号")
                                    {
                                        ordercodeIndex = colIndex;
                                    }
                                    else if (headerValue == "计划数量")
                                    {
                                        planqtyIndex = colIndex;
                                    }
                                    else if (headerValue.Contains("备注") && !headerValue.Contains("生产异常备注"))
                                    {
                                        remarkIndex = colIndex;
                                    }
                                }
                            }

                            if (wccodeIndex == -1 || orderdetnoIndex == -1 || ordercodeIndex == -1 || planqtyIndex == -1 || remarkIndex == -1)
                            {
                                Console.WriteLine("Required columns not found in the header.");
                                continue;
                            }

                            using (OracleConnection conn = new OracleConnection(connectionString))
                            {
                                conn.Open();
                                for (int rowIndex = 4; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第5行开始解析数据
                                {
                                    IRow row = sheet.GetRow(rowIndex);
                                    if (row != null)
                                    {
                                        string wccode = row.GetCell(wccodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.ToString();
                                        if (wccode == "")
                                        {
                                            continue;
                                        }
                                        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();
                                        if (!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 + "'"))
                                            {
                                                string insertQuery = "INSERT INTO MakePlanDetail (mpd_mpid,mpd_detno,mpd_id,mpd_wccode, mpd_orderdetno, mpd_ordercode,mpd_outqty,mpd_remark) VALUES (" + id + "," + detno + ",MakePlanDetail_seq.nextval,:1, :2, :3,:4,:5)";
                                                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.ExecuteNonQuery();
                                                }
                                            }
                                            else
                                            {
                                                OperateResult.AppendText(" 序号'" + orderdetno + "' 销售订单'" + ordercode + "' 工作中心'" + wccode + "' 重复");
                                            }
                                        }
                                    }
                                    detno = detno + 1;
                                }
                            }
                            dh.ExecuteSql("delete from MakePlanDetail where mpd_orderdetno is null", "delete");
                            OperateResult.AppendText("计划上传成功");
                        }
                    }
                }
            }
        }

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