mes-client/UAS_MES_ZT/FunctionCode/Special/Special_AfSale.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
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_AfSale : Form
    {
        ExcelHandler eh;
        AutoSizeFormClass asc = new AutoSizeFormClass();
        DataHelper dh;

        string bsncode = "";//序列号录入框值

        LogStringBuilder sql = new LogStringBuilder();
        /// <summary>
        /// 已选的不良
        /// </summary>
        List<string> ChoosedList = new List<string>();
        /// <summary>
        /// 待选的不良
        /// </summary>
        List<string> WaitList = new List<string>();

        public Special_AfSale()
        {
            InitializeComponent();
        }

        private void Special_BoxSplit_Load(object sender, EventArgs e)
        {

            asc.controllInitializeSize(this);
            dh = SystemInf.dh;
            eh = new ExcelHandler();
            LoadBadGroupData();
            LoadBadCodeListView();

            cu_code.TableName = "CUSTOMER";
            cu_code.DBTitle = "客户查询";
            cu_code.SelectField = "cu_id # ID,cu_code  # 客户编号,cu_name # 客户名称";
            cu_code.SetValueField = new string[] { "cu_id", "cu_code", "cu_name"};
            cu_code.FormName = Name;
           // cu_code.Condition = "(pi_type ='自由出货' or  pi_inoutno like('XSCK%') or  pi_inoutno like('QTCK%') or  pi_inoutno like('WWLL%'))";
           // cu_code.DbChange += Pi_inoutno_DbChange;

        }

        /// <summary>
        /// 加载不良代码的ListView的信息
        /// </summary>
        private void LoadBadCodeListView()
        {
            object bg_code = bc_groupcode.SelectedValue;
            sql.Clear();
            sql.Append("select bg_code||':'||bg_name bg_code,bc_code,bc_name,'' bc_remark from badgroupdetail ");
            sql.Append("left join badgroup on  bg_id=bgd_bgid left join badcode on bgd_badcode=bc_code where ");
            sql.Append("bg_code='" + (bg_code != null ? bg_code.ToString() : "") + "' and bg_code is not null order by bgd_detno");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            BadInfSource.DataSource = dt;
            //往listview中添加数据
            WaitRejectList.Items.Clear();
            WaitRejectList.BeginUpdate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!ChoosedList.Contains(dt.Rows[i]["bc_code"].ToString()))
                {
                    ListViewItem lvi = new ListViewItem();
                    //第一列是勾选列，设置列头文本为空
                    lvi.Text = "";
                    WaitList.Add(dt.Rows[i]["bc_code"].ToString());
                    for (int j = 0; j < dt.Columns.Count; j++)
                        lvi.SubItems.Add(dt.Rows[i][j].ToString());
                    WaitRejectList.Items.Add(lvi);
                }
            }
            WaitRejectList.EndUpdate();
        }


        DataTable dt;

        /// <summary>
        /// 加载已选不良的ListView
        /// </summary>
        private void LoadChoosedBadListView()
        {
            sql.Clear();
            sql.Append("select AS_BGCODE||':'||AS_BGNAME bg_code,AS_BADCODE bc_code,AS_BADNAME bc_name,' ' mb_badremark from afsales  ");
            sql.Append(" ");
            sql.Append(" where as_sn='" + sncode.Text + "'");
            dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            ChoosedRejectList.Items.Clear();
            ChoosedRejectList.BeginUpdate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem lvi = new ListViewItem();
                //第一列是勾选列，设置列头文本为空
                lvi.Text = "";
                ChoosedList.Add(dt.Rows[i]["bc_code"].ToString());
                for (int j = 0; j < dt.Columns.Count; j++)
                    lvi.SubItems.Add(dt.Rows[i][j].ToString());
                ChoosedRejectList.Items.Add(lvi);
            }
            ChoosedRejectList.EndUpdate();

        }


        /// <summary>
        /// 填充不良组别信息
        /// </summary>
        private void LoadBadGroupData()
        {
            //如果ms_makecode的值修改过再去查询
            sql.Clear();
            sql.Append("select bg_code||':'||bg_name bg_name,bg_code from badgroup left join (select pb_badgroup,pr_kind ");
            sql.Append("from product left join productkind on pk_name=pr_kind ");
            sql.Append("left join productbadgroup on pk_code=PB_KINDCODE )");
            sql.Append("on pb_badgroup=bg_code where bg_statuscode='AUDITED' order by case when bg_code='" + User.DefaultBadGroup + "' then 0 else 1 end");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            bc_groupcode.DisplayMember = "bg_name";
            bc_groupcode.ValueMember = "bg_code";
            bc_groupcode.DataSource = dt;
        }





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

        private void sncode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                LoadBadCodeListView();
                LoadChoosedBadListView();
                DataTable dt = (DataTable)dh.ExecuteSql("select to_char(ms_indate,'yyyy-mm-dd') ms_indate,ms_sncode,ms_makecode,ms_prodcode,pr_orispeccode from makeserial left join PRODUCT  ON ms_prodcode = pr_code where ms_sncode = '" + sncode.Text+"'", "select");
                if (dt.Rows.Count > 0)
                {
                    BaseUtil.SetFormValue(this.Controls, dt);
                    OperateResult.AppendText(">>SN:" + sncode.Text + "采集成功\n", Color.Green);
                }
                else
                {
                    OperateResult.AppendText(">>SN:"+sncode.Text+ "采集成功,无生产记录\n", Color.Black);
                    ms_sncode.Text = sncode.Text;
                }
                bc_code.Focus();
                bc_code.SelectAll();

            }
        }

        private void Save_Click(object sender, EventArgs e)
        {
            if (ms_sncode.Text == "")
            {
                OperateResult.AppendText(">>序列号不允许为空\n", Color.Red);
                return;
            }
            int RejectCount = ChoosedRejectList.Items.Count;
            string[] bccode = new string[RejectCount];
            string[] bgcode = new string[RejectCount];
            string[] bgname = new string[RejectCount];
            string[] bcname = new string[RejectCount];

            if (ChoosedRejectList.Items.Count == 0)
            {
                OperateResult.AppendText(">>不良品必须选择不良原因和不良代码!\n", Color.Red);
                return;
            }
            else
            {
                for (int i = 0; i < RejectCount; i++)
                {
                    //获取不良代码组
                    bgcode[i] = ChoosedRejectList.Items[i].SubItems[1].Text.Split(':')[0];
                    //获取不良代码组名称
                    bgname[i] = ChoosedRejectList.Items[i].SubItems[1].Text.Split(':')[1];
                    //获取所有的不良代码
                    bccode[i] = ChoosedRejectList.Items[i].SubItems[2].Text;
                    //获取不良名称
                    bcname[i] = ChoosedRejectList.Items[i].SubItems[3].Text;
                }
            }
            dh.ExecuteSql("delete from AFSALES where as_sn='" + ms_sncode.Text + "' ", "delete");
            //string seq = dh.GetSEQ("AFSALES_seq");

            sql.Clear();
            sql.Append("insert into AFSALES(as_id,as_sn,as_badcode,as_badname,as_indate,as_inman,as_makecode");
            sql.Append(",as_prodcode,as_orispeccode,as_madedate,as_bgcode,as_bgname,as_datedif,AS_REMARK,AS_CUST,AS_FCSMAN,AS_SCSMAN,AS_FVMAN)");
            sql.Append("select AFSALES_seq.nextval,'" + ms_sncode.Text+ "',:bc_code,:bc_name,sysdate,'"+User.UserCode+ "',ms_makecode,");
            sql.Append("ms_prodcode,pr_orispeccode,ms_indate,:bg_code,:bg_name, datedif,'"+ remarkin.Text + "','"+cu_code.Text+ "',t1,t2,fv from ((select max(ms_indate) ms_indate ,max(ms_prodcode) ms_prodcode,max(pr_orispeccode) pr_orispeccode,max(ms_makecode) ms_makecode,round(sysdate- nvl(max(ms_indate),sysdate),2) datedif,max(T1) t1,max(T2) t2,max(FV) fv from makeserial left join MES_T1T2FV_VIEW on ms_code = SP_SNCODE left join product on ms_prodcode = pr_code where ms_sncode = '" + ms_sncode.Text+"' and ms_nextmacode is null)) ");
            if (bgcode.Length > 0)
            {
                dh.BatchInsert(sql.GetString(), new string[] { "bc_code", "bc_name", "bg_code", "bg_name" }, bccode, bcname, bgcode, bgname);
            }

            LogicHandler.DoCommandLog(Tag.ToString(), User.UserCode, ms_makecode.Text, User.UserLineCode, User.UserSourceCode, "售后判定", "售后判定成功", ms_sncode.Text,"" );
            OperateResult.AppendText(">>SN:" + sncode.Text + "售后判定成功\n", Color.Green, sncode);
            string CU = cu_code.Text;
            BaseUtil.CleanControls(this.Controls);
            remarkin.Text = "";
            ChoosedRejectList.Items.Clear();
            WaitList.Clear();
            ChoosedList.Clear();
            LoadBadGroupData();
            LoadBadCodeListView();
            cu_code.Text = CU;
            sncode.Focus();
            sncode.SelectAll();

        }

        private void bc_groupcode_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadBadCodeListView();
        }

        private void bc_code_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                sql.Clear();
                sql.Append("select  bg_code||':'||bg_name bg_code,bc_code,bc_name,'' bc_remark from  badgroupdetail left join ");
                sql.Append("badgroup on  bg_id=bgd_bgid left join badcode on bgd_badcode = bc_code where bc_code='" + bc_code.Text + "'  and rownum=1 ");
                DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < ChoosedRejectList.Items.Count; i++)
                    {
                        if (ChoosedRejectList.Items[i].ListView.Items[i].SubItems[2].Text == bc_code.Text)
                        {
                            OperateResult.AppendText(">>已添加过不良代码" + bc_code.Text + "\n", Color.Red, bc_code);
                            return;
                        }
                    }
                    ListViewItem lvi = new ListViewItem();
                    lvi.Text = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                        lvi.SubItems.Add(dt.Rows[0][j].ToString());
                    ChoosedRejectList.Items.Add(lvi);
                    bc_code.Clear();
                }
                else OperateResult.AppendText(">>" + bc_code.Text + "不良代码不存在\n", Color.Red, bc_code);
            }
        }

        private void Filter_UserControlTextChanged(object sender, EventArgs e)
        {
            try
            {
                DataRow[] dr = (BadInfSource.DataSource as DataTable).Select("bc_code like '%" + Filter.Text + "%' or bc_name like '%" + Filter.Text + "%'");
                DataTable dt = (BadInfSource.DataSource as DataTable).Clone();
                for (int i = 0; i < dr.Length; i++)
                {
                    dt.Rows.Add(dr[i].ItemArray);
                }
                WaitRejectList.Items.Clear();
                WaitRejectList.BeginUpdate();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!ChoosedList.Contains(dt.Rows[i]["bc_code"].ToString()))
                    {
                        ListViewItem lvi = new ListViewItem();
                        //第一列是勾选列，设置列头文本为空
                        lvi.Text = "";
                        WaitList.Add(dt.Rows[i]["bc_code"].ToString());
                        for (int j = 0; j < dt.Columns.Count; j++)
                            lvi.SubItems.Add(dt.Rows[i][j].ToString());
                        WaitRejectList.Items.Add(lvi);
                    }
                }
                WaitRejectList.EndUpdate();
            }
            catch (Exception)
            {

            }
        }

        private void ChooseedReject_Click(object sender, EventArgs e)
        {
            if (sncode.Text == "")
            {
                OperateResult.AppendText(">>序列号不能为空\n", Color.Red);
                sncode.Focus();
                return;
            }
            int CheckedNum = 0;
            //是否已经添加到不良
            bool AddToReject = false;
            for (int i = WaitRejectList.Items.Count - 1; i >= 0; i--)
            {
                if (WaitRejectList.Items[i].Checked)
                {
                    AddToReject = false;
                    for (int j = 0; j < ChoosedRejectList.Items.Count; j++)
                    {
                        if ((ChoosedRejectList.Items[j].SubItems[2].Text == WaitRejectList.Items[i].SubItems[2].Text))
                        {
                            AddToReject = true;
                            OperateResult.AppendText(">>已存在" + ChoosedRejectList.Items[j].SubItems[2].Text + "不良\n", Color.Red);
                            break;
                        }
                    }
                    //未添加则进行添加
                    if (!AddToReject)
                    {
                        WaitRejectList.Items[i].Checked = false;
                        ChoosedRejectList.Items.Add((ListViewItem)WaitRejectList.Items[i].Clone());
                        WaitRejectList.Items[i].Remove();
                        CheckedNum++;
                    }
                }
            }
            if (CheckedNum == 0)
            {
                OperateResult.AppendText(">>请勾选不良代码\n", Color.Red);
            }
        }

        private void WaitReject_Click(object sender, EventArgs e)
        {
            int CheckedNum = 0;
            //已存在在ListView中的Item是不能添加到其他ListView中的，需要调用其克隆的方法
            for (int i = ChoosedRejectList.Items.Count - 1; i >= 0; i--)
            {
                if (ChoosedRejectList.Items[i].Checked)
                {
                    ChoosedRejectList.Items[i].Checked = false;
                    WaitRejectList.Items.Add((ListViewItem)ChoosedRejectList.Items[i].Clone());
                    ChoosedRejectList.Items[i].Remove();
                    CheckedNum++;
                }
            }
            if (CheckedNum == 0)
            {
                for (int i = ChoosedRejectList.Items.Count - 1; i >= 0; i--)
                {
                    WaitRejectList.Items.Add((ListViewItem)ChoosedRejectList.Items[i].Clone());
                    ChoosedRejectList.Items[i].Remove();
                }
            }
        }

        private void WaitRejectList_DoubleClick(object sender, EventArgs e)
        {
            for (int i = WaitRejectList.Items.Count - 1; i >= 0; i--)
            {
                if (WaitRejectList.Items[i].Checked)
                {
                    //双击右移
                    ChooseedReject.PerformClick();
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string fileload = openFileDialog1.FileName;
                dt = eh.ImportExcel(fileload, "test");

                List<string> snlist = new List<string>();
                List<string> ngcodelist = new List<string>();
                List<string> ngnamelist = new List<string>();
                List<string> bdcodelist = new List<string>();
                List<string> bdnamelist = new List<string>();
                List<string> emcodelist = new List<string>();
                List<string> timelist = new List<string>();
                List<string> remarklist = new List<string>();
                List<string> custlist = new List<string>();


                try

                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                      
                        if (dt.Rows[i]["SN"].ToString().Trim() == "")
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行SN为空\n", Color.Red);
                            continue;
                        }

                        if (dh.CheckExist("AFSALES", "as_sn = '" + dt.Rows[i]["SN"].ToString() + "'"))
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行SN已判定\n", Color.Red);
                            continue;
                        }

                        if (snlist.Contains(dt.Rows[i]["SN"].ToString()))
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行SN已存在与此表格\n", Color.Red);
                            continue;
                        }
                        if (dt.Rows[i]["不良代码"].ToString().Trim() == "")
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行不良代码为空\n", Color.Red);
                            continue;
                        }

                        if ( !dh.CheckExist("badcode", "bc_code = '" + dt.Rows[i]["不良代码"].ToString() + "'"))
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行不良代码不存在\n", Color.Red);
                            continue;
                        }

                        if (dt.Rows[i]["人员"].ToString().Trim() == "" )
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行人员为空\n", Color.Red);
                            continue;
                        }

                        if (!dh.CheckExist("employee", "em_code = '" + dt.Rows[i]["人员"].ToString() + "'"))
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行人员不存在\n", Color.Red);
                            continue;
                        }


                        String timeRegex1 = "^(?<year>\\d{2,4})/(?<month>\\d{1,2})/(?<day>\\d{1,2})$";

                        if (dt.Rows[i]["时间"].ToString().Trim() == "")
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行时间为空\n", Color.Red);
                            continue;
                        }

                        if ( !Regex.IsMatch(dt.Rows[i]["时间"].ToString(), timeRegex1))
                        {
                            OperateResult.AppendText(">>第" + (i + 1) + "行时间不符合要求\n", Color.Red);
                            continue;
                        }

                        snlist.Add(dt.Rows[i]["SN"].ToString());
                        emcodelist.Add(dt.Rows[i]["人员"].ToString());
                        timelist.Add(dt.Rows[i]["时间"].ToString());
                        remarklist.Add(dt.Rows[i]["备注"].ToString());
                        custlist.Add(dt.Rows[i]["客户"].ToString());
                        bdcodelist.Add(dt.Rows[i]["不良代码"].ToString());
                        DataTable ddt = (DataTable)dh.ExecuteSql("select bc_groupname,bc_groupcode,bc_name from badcode where bc_code = '" + dt.Rows[i]["不良代码"].ToString() + "'", "select");

                        ngnamelist.Add(ddt.Rows[0]["bc_groupname"].ToString());
                        ngcodelist.Add(ddt.Rows[0]["bc_groupcode"].ToString());
                        bdnamelist.Add(ddt.Rows[0]["bc_name"].ToString());
                    }
                    if (snlist.Count > 0)
                    {
                        sql.Clear();
                        sql.Append("insert into TEMP_AF(ID,SN,BDCODE,BDNAME,BGCODE,BGNAME,INDATE,INMAN,REMARK,CUST)");
                        sql.Append("values(afsales_seq.nextval,:snlist,:bdcodelist,:bdnamelist,:ngcodelist,:ngnamelist,:timelist,:emcodelist,:remarklist,:custlist)");

                        dh.BatchInsert(sql.GetString(), new string[] { "snlist", "bdcodelist", "bdnamelist", "ngcodelist", "ngnamelist", "timelist", "emcodelist", "remarklist", "custlist" }
                        , snlist.ToArray(), bdcodelist.ToArray(), bdnamelist.ToArray(), ngcodelist.ToArray(), ngnamelist.ToArray(), timelist.ToArray(), emcodelist.ToArray(), remarklist.ToArray(), custlist.ToArray());
                        sql.Clear();
                        sql.Append("insert into afsales(as_id,as_sn,as_badcode,as_badname,as_bgcode,as_bgname,as_indate,as_inman,AS_REMARK,as_cust,as_madedate,as_prodcode,as_orispeccode,as_makecode,AS_FCSMAN,AS_SCSMAN,AS_FVMAN) ");
                        sql.Append(" select ID,SN,BDCODE,BDNAME,BGCODE,BGNAME,to_date(INDATE,'yyyy/mm/dd'),INMAN,REMARK,CUST,max(ms_indate) ms_indate ,max(ms_prodcode) ms_prodcode,max(pr_orispeccode) ");
                        sql.Append(" pr_orispeccode,max(ms_makecode) ms_makecode,t1,t2,fv  from TEMP_AF left join makeserial on sn = ms_sncode and ms_nextmacode is null left join MES_T1T2FV_VIEW on ms_code = SP_SNCODE left join  ");
                        sql.Append(" product on ms_prodcode = pr_code group by ID,SN,BDCODE,BDNAME,BGCODE,BGNAME,to_date(INDATE,'yyyy/mm/dd'),INMAN,REMARK,CUST,t1,t2,fv ");
                        dh.ExecuteSql(sql.GetString(), "insert");
                    }
                    dh.ExecuteSql("delete TEMP_AF", "delete");

                    OperateResult.AppendText(">>导入" + snlist.Count + "行完成\n", Color.Green);
                }
                catch (Exception ex)
                {
                    dh.ExecuteSql("delete TEMP_AF", "delete");
                    MessageBox.Show(ex.Message);
                }

            }
        }
    }
}