mes-client/UAS_MES_YDCY/FunctionCode/Special/Special_ChangeMake.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicMethod;

namespace UAS_MES_NEW.Special
{
    public partial class Special_ChangeMake : Form
    {
        DataHelper dh;

        LogStringBuilder sql = new LogStringBuilder();
        AutoSizeFormClass asc = new AutoSizeFormClass();
        DataTable Dbfind;
        DataTable dt;

        List<String> sb = new List<string>();

        public Special_ChangeMake()
        {
            InitializeComponent();
        }

        private void Special_BoxSplit_Load(object sender, EventArgs e)
        {
            macode.TableName = " make left join product on ma_prodcode=pr_code";
            macode.SelectField = "ma_code # 工单编号,pr_code # 产品编号,pr_spec # 型号";
            macode.FormName = Name;
            macode.SetValueField = new string[] { "ma_code" };
            macode.Condition = "ma_statuscode='STARTED'";
            macode.DbChange += pr_code_DbChange;

            ma_code.TableName = " make left join product on ma_prodcode=pr_code";
            ma_code.SelectField = "ma_code # 工单编号,pr_code # 产品编号,pr_spec # 型号";
            ma_code.FormName = Name;
            ma_code.SetValueField = new string[] { "ma_code" };
            ma_code.Condition = "ma_statuscode='STARTED'";
            ma_code.DbChange += pr_code_DbChange1;

            dh = SystemInf.dh;
            //asc.controllInitializeSize(this);

        }

        private void pr_code_DbChange(object sender, EventArgs e)
        {
            Dbfind = macode.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
            DataTable dt = (DataTable)dh.ExecuteSql("select ma_qty,ma_prodcode,pr_detail from make left join product on ma_prodcode=pr_code where ma_code='" + macode.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                prcode.Text = dt.Rows[0]["ma_prodcode"].ToString();
                prdetail.Text = dt.Rows[0]["pr_detail"].ToString();
            }
        }

        private void pr_code_DbChange1(object sender, EventArgs e)
        {
            Dbfind = ma_code.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
            DataTable dt = (DataTable)dh.ExecuteSql("select ma_qty,ma_prodcode,pr_detail from make left join product on ma_prodcode=pr_code where ma_code='" + ma_code.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                pr_code.Text = dt.Rows[0]["ma_prodcode"].ToString();
                pr_detail.Text = dt.Rows[0]["pr_detail"].ToString();
            }
        }



        private bool ReSN(string sncode)
        {
            DataTable dt = (DataTable)dh.ExecuteSql("select ms_sncode,ms_makecode from makeserial where ms_sncode='" + sncode + "'  order by ms_id desc", "select");
            if (dt.Rows.Count == 0)
            {
                OperatResult.AppendText("" + sncode + "不存在\n");
                return false;
            }
            else
            {
                if (dt.Rows[0]["ms_makecode"].ToString() != macode.Text)
                {
                    OperatResult.AppendText("" + sncode + "不存在工单" + macode.Text + "\n");
                    return false;
                }
                sncode = dt.Rows[0]["ms_sncode"].ToString();
            }

            if (macode.Text == "" || ma_code.Text == "")
            {
                OperatResult.AppendText("工单号不允许为空\n");
                return false;
            }
            if (!dh.CheckExist("make", "ma_code='" + macode.Text + "'"))
            {
                OperatResult.AppendText("工单号" + macode.Text + "不存在\n");
                return false;
            }
            if (!dh.CheckExist("make", "ma_code='" + ma_code.Text + "'"))
            {
                OperatResult.AppendText("工单号" + ma_code.Text + "不存在\n");
                return false;
            }
            if (dh.getFieldDataByCondition("make", "ma_prodcode", "ma_code='" + macode.Text + "'").ToString() != dh.getFieldDataByCondition("make", "ma_prodcode", "ma_code='" + ma_code.Text + "'").ToString())
            {
                OperatResult.AppendText("工单号产品编号不对应，不允许切换\n");
                return false;
            }
            List<string> sqls = new List<string>();
            sqls.Add("update CRAFTMATERIAL set cm_makecode='" + ma_code.Text + "' where cm_makecode= '" + macode.Text + "' and cm_sncode='" + sncode + "'");
            sqls.Add("update steppassed set sp_makecode='" + ma_code.Text + "' where sp_makecode= '" + macode.Text + "' and sp_sncode='" + sncode + "'");
            sqls.Add("update makeserial set ms_makecode='" + ma_code.Text + "' where ms_makecode= '" + macode.Text + "' and ms_sncode='" + sncode + "'");
            sqls.Add("update makeprocess set mp_makecode='" + ma_code.Text + "' where mp_makecode= '" + macode.Text + "' and mp_sncode='" + sncode + "'");
            sqls.Add("update commandlog set cl_makecode='" + ma_code.Text + "' where cl_makecode= '" + macode.Text + "' and cl_sncode='" + sncode + "'");
            sqls.Add("update makebad set mb_makecode='" + ma_code.Text + "' where mb_makecode= '" + macode.Text + "' and mb_sncode='" + sncode + "'");
            sqls.Add("update labelprintlog set lpl_makecode='" + ma_code.Text + "' where lpl_makecode= '" + macode.Text + "' and lpl_value='" + sncode + "'");
            sqls.Add("update make set ma_inqty=(select count(1) from makeserial where ms_makecode='" + ma_code.Text + "') where ma_code='" + ma_code.Text + "' ");
            sqls.Add("update make set ma_endqty=(select count(1) from makeserial where ms_makecode='" + ma_code.Text + "' and ms_status=2) where ma_code='" + ma_code.Text + "'");
            dh.ExecuteSQLTran(sqls.ToArray());
            OperatResult.AppendText("工单号切换成功\n");
            LogicHandler.DoCommandLog(Tag.ToString(), User.UserName, macode.Text, User.UserLineCode, User.UserSourceCode, "工单变更", "工单变更" + macode.Text + "切换到" + ma_code.Text, sncode, "");
            return true;
        }

        private void Split_Click(object sender, EventArgs e)
        {
            string Delete = MessageBox.Show(this.ParentForm, "是否确认切换工单？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
            if (Delete == "Yes")
            {
                for (int i = 0; i < sb.Count; i++)
                {
                    if (ReSN(sb[i].ToString()))
                        OperatResult.AppendText("SN：" + sb[i].ToString() + "切换到工单" + ma_code.Text + "完成\n", Color.Green);
                }
                WeighRecord.Items.Clear();
                sb.Clear();
            }
        }

        private void input_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (serBtn.Checked)
                {
                    dt = (DataTable)dh.ExecuteSql("select ms_sncode, ms_makecode,ms_prodcode,ms_sncode,ms_craftcode cr_code,ms_status,ms_outboxcode,pr_detail from makeserial left join product on pr_code=ms_prodcode where ms_sncode='" + input.Text + "' order by ms_id desc", "select");
                    if (dt.Rows.Count > 0)
                    {
                        if (macode.Text == "")
                        {
                            macode.Text = dt.Rows[0]["ms_makecode"].ToString();
                            prcode.Text = dt.Rows[0]["ms_prodcode"].ToString();
                            prdetail.Text = dt.Rows[0]["pr_detail"].ToString();
                        }
                        if (dt.Rows[0]["ms_status"].ToString() == "3")
                        {
                            OperatResult.AppendText("SN：" + input.Text + "为不良品，不允许采集\n", Color.Red, input);
                            return;
                        }
                        if (dt.Rows[0]["ms_outboxcode"].ToString() != "")
                        {
                            OperatResult.AppendText("SN：" + input.Text + "已装箱" + dt.Rows[0]["ms_outboxcode"].ToString() + "，不允许采集\n", Color.Red, input);
                            return;
                        }
                        if (sb.Contains(dt.Rows[0]["ms_sncode"].ToString()))
                        {
                            OperatResult.AppendText("SN：" + dt.Rows[0]["ms_sncode"].ToString() + "已采集\n", Color.Red, input);
                            return;
                        }
                        WeighRecord.Items.Add(dt.Rows[0]["ms_sncode"].ToString());
                        sb.Add(dt.Rows[0]["ms_sncode"].ToString());
                    }
                    else
                    {
                        OperatResult.AppendText("SN：" + input.Text + "不存在\n", Color.Red, input);
                        return;
                    }
                }
            }
        }

        private void 数据清空_Click(object sender, EventArgs e)
        {
            BaseUtil.CleanControls(this.Controls);
            WeighRecord.Items.Clear();
            sb.Clear();
        }

        private void Special_Reset_SizeChanged(object sender, EventArgs e)
        {
            asc.controlAutoSize(this);
        }

        private void Export_Click(object sender, EventArgs e)
        {
            ExportFileDialog.Description = "选择导出的路径";
            DialogResult result = ExportFileDialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                ExcelHandler eh = new ExcelHandler();
                sql.Clear();
                sql.Append("select '' SN from dual ");
                dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                eh.ExportExcel(dt, ExportFileDialog.SelectedPath + "\\导出模板.xls");
                //string close = MessageBox.Show(this.ParentForm, "导出成功，是否打开文件", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
            }
        }

        private void ImportExcel_Click(object sender, EventArgs e)
        {
            ImportExcel1.Filter = "(*.xls)|*.xls";
            DialogResult result;
            result = ImportExcel1.ShowDialog();
            if (result == DialogResult.OK)
            {
                DataTable dt = ExcelToDataTable(ImportExcel1.FileName, true);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (serBtn.Checked)
                    {
                        input.Text = dt.Rows[i]["SN"].ToString();
                        input_KeyDown(sender, new KeyEventArgs(Keys.Enter));
                    }
                }
            }
        }

        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    workbook = new HSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //构建datatable的列
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }
                                //填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.BLANK:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.NUMERIC:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.STRING:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                                case CellType.FORMULA:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }
    }
}