mes-client/UAS-MES/FunctionCode/OQC/OQC_SamplingDataCollection.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using UAS_MES.CustomControl.DataGrid_View;
using UAS_MES.DataOperate;
using UAS_MES.Entity;
using UAS_MES.PublicMethod;

namespace UAS_MES.OQC
{
    public partial class OQC_SamplingDataCollection : Form
    {
        AutoSizeFormClass asc = new AutoSizeFormClass();
        DataHelper dh;
        DataTable dt;
        DataTable BadCode;

        List<DataGridViewRow> dgvr = new List<DataGridViewRow>();
        LogStringBuilder sql = new LogStringBuilder();
        string[] LevelDefect = new string[] { "A#A", "B#B", "C#C", "D#D" };

        DataTable TempForCheckType = new DataTable();

        string ErrorMessage = "";

        public OQC_SamplingDataCollection()
        {
            InitializeComponent();
        }

        private void 抽样数据采集_Load(object sender, EventArgs e)
        {
            asc.controllInitializeSize(this);
            BaseUtil.SetDgvColumnComboxData((DataGridViewComboBoxColumn)CheckTypeDGV.Columns["oi_leveldefect"], "oi_leveldefect", "display", "value", LevelDefect);
            dh = new DataHelper();
        }

        private void 抽样数据采集_SizeChanged(object sender, EventArgs e)
        {
            asc.controlAutoSize(this);
        }

        private void GetBatch_Click(object sender, EventArgs e)
        {
            if (LogicHandler.CheckStepAttribute(Tag.ToString(), User.UserSourceCode, out ErrorMessage))
            {
                DataTable[] dt = LogicHandler.GetOQCBatch(ms_sncode.Text == "" ? sncode.Text : ms_sncode.Text, obd_outboxcode.Text, ob_checkno.Text, "OQCDataCollection", out ErrorMessage);
                if (ErrorMessage == "")
                {
                    BaseUtil.SetFormValue(Controls, dt[0]);
                    //如果已经判定过则不可再编辑
                    if (ob_status.Text == "OK" || ob_status.Text == "NG" || ob_status.Text == "FOK" || ob_status.Text == "FNG")
                    {
                        BatchPass.Enabled = false;
                    }
                    else
                    {
                        BatchPass.Enabled = true;
                    }
                    oi_checkqty1.Text = dh.getFieldDataByCondition("oqcitems", "max(oi_checkqty)", "oi_checkno='" + ob_checkno.Text + "'").ToString();
                    dgvr.Clear();
                }
                else OperateResult.AppendText(">>" + ErrorMessage + "\n", Color.Red);
            }
            else
            {
                OperateResult.AppendText(">>" + ErrorMessage + "\n", Color.Red);
            }
        }

        private void Clean_Click(object sender, EventArgs e)
        {
            OperateResult.Clear();
        }

        private void sncode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                GetBatch.PerformClick();
                if (CheckSnCode())
                {
                    dt = (DataTable)dh.ExecuteSql("select or_id,or_badcode bc_code,or_description bc_name,or_bgcode bg_code from OQCNGReason where or_checkno='" + ob_checkno.Text + "' and or_sncode='" + sncode.Text + "'", "select");
                    BaseUtil.FillDgvWithDataTable(ChoosedDGV, dt);
                    GetBatchTypeGridData();
                }
            }
        }

        private bool CheckSnCode()
        {
            //输入的序列号不能为空
            if (sncode.Text != "")
            {
                //判断当前的检验状态
                if (ob_status.Text == "待检验" || ob_status.Text == "检验中")
                {
                    dt = (DataTable)dh.ExecuteSql("select obd_sncode,ob_source,ob_makecode from OQCBatchDetail left join OQCBatch  on ob_id=obd_obid where obd_sncode='" + sncode.Text + "' and ob_checkno='" + ob_checkno.Text + "'", "select");
                    if (dt.Rows.Count > 0)
                    {
                        if (dt.Rows[0]["ob_source"].ToString() != "工序")
                        {
                            return true;
                        }
                        else
                        {
                            string ErrorMessage = "";
                            string oMakeCode = "";
                            string oMsID = "";
                            if (LogicHandler.CheckStepSNAndMacode(dt.Rows[0]["ob_makecode"].ToString(), User.UserSourceCode, sncode.Text, User.UserCode, out oMakeCode, out oMsID, out ErrorMessage))
                            {
                                return true;
                            }
                            else
                            {
                                OperateResult.AppendText(">>" + ErrorMessage + "\n", Color.Red);
                                return false;
                            }
                        }
                    }
                    else
                    {
                        OperateResult.AppendText(">>序列号" + sncode.Text + "不属于当前抽检批次\n", Color.Red);
                        return false;
                    }
                }
                else
                {
                    OperateResult.AppendText(">>送检批次必须是待检验或者检验中\n", Color.Red);
                    return false;
                }
            }
            else
            {
                OperateResult.AppendText(">>序列号不能为空\n", Color.Red);
                return false;
            }
        }

        private void GetBatchTypeGridData()
        {
            dt = (DataTable)dh.ExecuteSql("select oi_checkkind,max(oi_sampleqty) oi_count,nvl(max(oi_checkqty),0) oi_checkedcount from OQCBatch  left join OQCItems on ob_checkno=oi_checkno  where oi_checkno='" + ob_checkno.Text + "' group by oi_checkkind ", "select");
            if (dt.Rows.Count == 0)
            {
                OperateResult.AppendText(">>请先维护抽样计划\n", Color.Red);
                return;
            }
            string checkkind = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                checkkind += ("'" + dt.Rows[i]["oi_checkkind"].ToString() + "',");
            }
            sql.Clear();
            sql.Append("select 1 choose,ci_name,oi_itemcode,oi_checkkind,oi_sampleqty,nvl((oi_checkqty),0) oi_checkqty, ");
            sql.Append("nvl(ois_id,0) ois_id,nvl(ois_ifng,0) oi_ng,nvl(ois_defectlevel,'-1') oi_leveldefect, ");
            sql.Append("ois_remark,case nvl(ois_id,0) when 0 then '未检验' else '已检验' end ois_status ");
            sql.Append("from OQCItems left join  QUA_CHECKITEM on oi_itemcode=ci_code left join OQCItemSamples on  ois_sncode='" + sncode.Text + "' and ois_checkno=oi_checkno and ");
            sql.Append("ois_itemcode=oi_itemcode and ois_projectcode = oi_projectcode where oi_checkno ='" + ob_checkno.Text + "' ");
            sql.Append("and oi_checkkind in (" + checkkind.Substring(0, checkkind.Length - 1) + ")");
            dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            BaseUtil.FillDgvWithDataTable(CheckTypeDGV, dt);
            sql.Clear();
            sql.Append("select bg_code,bg_code||':'||bg_name bg_name from PRODUCTBADGROUP left join productkind on ");
            sql.Append("pk_code= pb_kindcode left join product on pr_kind=pk_name left join ");
            sql.Append("badgroup on  bg_code=pb_badgroup where pr_code='" + ob_prodcode.Text + "'");
            BadCode = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            if (BadCode.Rows.Count > 0)
            {
                BadGroupCode.DisplayMember = "bg_name";
                BadGroupCode.ValueMember = "bg_code";
                BadGroupCode.DataSource = BadCode;
            }
            else
            {
                BadCode = (DataTable)dh.ExecuteSql("select bg_code,bg_name from badgroup where bg_statuscode='AUDITED'", "select");
                BadGroupCode.DisplayMember = "bg_name";
                BadGroupCode.ValueMember = "bg_code";
                BadGroupCode.DataSource = BadCode;
            }
        }

        private void Confirm_Click(object sender, EventArgs e)
        {
            //只能操作检验中或者待检验的批次
            if (ob_status.Text == "待检验" || ob_status.Text == "检验中")
            {
                if (!CheckSnCode())
                    return;
                //判断检验项是否勾选，如勾选取出明细数据
                DataTable dt = (DataTable)CheckTypeDGV.DataSource;
                //判断是否有勾选的明
                DataTable Savedt = BaseUtil.filterDataTable(dt, "choose<>0");
                if (Savedt.Rows.Count > 0)
                {
                    bool IfContainNG = false;
                    for (int i = 0; i < Savedt.Rows.Count; i++)
                    {
                        string if_ng = Savedt.Rows[i]["oi_ng"].ToString();
                        if (if_ng != "0" && if_ng != "")
                        {
                            IfContainNG = true;
                        }
                    }
                    if (!IfContainNG && ChoosedDGV.Rows.Count > 0)
                    {
                        OperateResult.AppendText(">>全部通过不允许勾选不良代码\n", Color.Red);
                        return;
                    }
                    if ((IfContainNG && ChoosedDGV.RowCount > 0) || !IfContainNG)
                    {
                        //用于存放更新的数据
                        List<string> ois_ifng_update = new List<string>();
                        List<string> ois_defectlevel_update = new List<string>();
                        List<string> ois_remark_update = new List<string>();
                        List<string> ois_id_update = new List<string>();
                        //用于存放插入的数据
                        List<string> ois_ifng_insert = new List<string>();
                        List<string> ois_defectlevel_insert = new List<string>();
                        List<string> ois_remark_insert = new List<string>();
                        List<string> oi_itemcode_insert = new List<string>();
                        for (int i = 0; i < Savedt.Rows.Count; i++)
                        {
                            //将布尔值转换为0,-1
                            if (Savedt.Rows[i]["ois_id"].ToString() != "" && Savedt.Rows[i]["ois_id"].ToString() != "0")
                            {
                                ois_ifng_update.Add(Savedt.Rows[i]["oi_ng"].ToString());
                                ois_defectlevel_update.Add(Savedt.Rows[i]["oi_leveldefect"].ToString());
                                ois_remark_update.Add(Savedt.Rows[i]["ois_remark"].ToString());
                                ois_id_update.Add(Savedt.Rows[i]["ois_id"].ToString());
                            }
                            else
                            {
                                oi_itemcode_insert.Add(Savedt.Rows[i]["oi_itemcode"].ToString());
                                ois_ifng_insert.Add(Savedt.Rows[i]["oi_ng"].ToString());
                                ois_defectlevel_insert.Add(Savedt.Rows[i]["oi_leveldefect"].ToString());
                                ois_remark_insert.Add(Savedt.Rows[i]["ois_remark"].ToString());
                            }
                        }
                        //执行批量更新的SQL
                        if (ois_id_update.Count > 0)
                        {
                            dh.BatchInsert("update OQCItemSamples set ois_ifng=:ois_ifng,ois_defectlevel=:ois_defectlevel,ois_remark=:ois_remark,ois_indate=sysdate where ois_id=:ois_id",
                         new string[] { "ois_ifng", "ois_defectlevel", "ois_remark", "ois_id" }, ois_ifng_update.ToArray(), ois_defectlevel_update.ToArray(), ois_remark_update.ToArray(), ois_id_update.ToArray());
                        }
                        //如果主键为空,并且存在需要插入的数据则执行插入操作 
                        if (oi_itemcode_insert.Count > 0)
                        {
                            string[] id = new string[oi_itemcode_insert.Count];
                            id = dh.GetSEQ("OQCItemSamples_SEQ", id.Length);
                            sql.Clear();
                            sql.Append("insert when (not exists (select ois_id from OQCItemSamples where ois_checkno='" + ob_checkno.Text + "' and ois_sncode='" + sncode.Text + "' and ois_itemcode =:ois_itemcode)) then ");
                            sql.Append("into OQCItemSamples (ois_id,ois_checkno,ois_makecode, ois_sncode,ois_projectcode,ois_itemcode,");
                            sql.Append("ois_ifng,ois_defectlevel,ois_remark,ois_indate)select :id,'" + ob_checkno.Text + "',");
                            sql.Append("'" + ob_makecode.Text + "','" + sncode.Text + "','" + ob_projectcode.Text + "',:ois_itemcode,:ois_ifng,:ois_defectlevel,:ois_remark,sysdate from dual");
                            dh.BatchInsert(sql.GetString(), new string[] { "ois_itemcode", "id", "ois_itemcode", "ois_ifng", "ois_defectlevel", "ois_remark" },
                                 oi_itemcode_insert.ToArray(), id, oi_itemcode_insert.ToArray(), ois_ifng_insert.ToArray(), ois_defectlevel_insert.ToArray(), ois_remark_insert.ToArray());
                        }
                        //采集不良信息
                        List<string> bc_code = new List<string>();
                        List<string> bc_name = new List<string>();
                        List<string> bg_code = new List<string>();
                        for (int i = 0; i < ChoosedDGV.RowCount; i++)
                        {
                            bc_code.Add(ChoosedDGV.Rows[i].Cells["bc_code1"].Value.ToString());
                            bc_name.Add(ChoosedDGV.Rows[i].Cells["bc_name1"].Value.ToString());
                            bg_code.Add(ChoosedDGV.Rows[i].Cells["bg_code1"].Value.ToString());
                        }
                        dh.ExecuteSql("delete from OQCNGReason where or_sncode='" + sncode.Text + "' and or_checkno='" + ob_checkno.Text + "'", "delete");
                        if (bc_name.Count > 0)
                        {
                            //dh.UpdateByCondition("makeserial", "ms_badtimes=ms_badtimes+1", "ms_sncode='" + sncode.Text + "'");
                            sql.Clear();
                            sql.Append("insert into OQCNGReason(or_id, or_checkno, or_makecode, or_sncode, or_badcode, or_description, or_remark,or_bgcode)");
                            sql.Append("values (OQCNGReason_seq.nextval,'" + ob_checkno.Text + "','" + ob_makecode.Text + "','" + sncode.Text + "',:a,:b,'" + ob_remark.Text + "',:c)");
                            dh.BatchInsert(sql.GetString(), new string[] { "a", "b", "c" }, bc_code.ToArray(), bc_name.ToArray(), bg_code.ToArray());
                        }
                        //更新项目编号中的抽检数、不合格数，根据采样项目记录
                        sql.Clear();
                        sql.Append("UPDATE OQCItems SET (oi_ngqty,oi_checkqty)=(select nvl(sum(case when ");
                        sql.Append("nvl(ois_ifng,0)=0 then 0 else 1 end),0) ,count(1) from OQCItemSamples where ois_checkno=oi_checkno ");
                        sql.Append("and ois_itemcode=oi_itemcode)where oi_checkno ='" + ob_checkno.Text + "' and oi_projectcode ='" + ob_projectcode.Text + "'");
                        dh.ExecuteSql(sql.GetString(), "update");
                        //含有不良的将序列号更新为不良
                        if (IfContainNG)
                            dh.ExecuteSql("update makeserial set ms_status=3 where ms_sncode='" + sncode.Text + "'", "update");
                        else
                            dh.ExecuteSql("update makeserial set ms_status=1 where ms_sncode='" + sncode.Text + "'", "update");
                        //更新检验状态，如果是待检验的更新为检验中
                        if (ob_status.Text == "待检验")
                        {
                            dh.ExecuteSql("update OQCBatch set ob_status='CHECKING' where ob_checkno='" + ob_checkno.Text + "' and ob_status='UNCHECK'", "update");
                            dh.ExecuteSql("update makecraftdetail set mcd_inqty=mcd_inqty+" + ob_nowcheckqty.Text + " where mcd_macode='" + ob_makecode.Text + "' and mcd_stepcode='" + User.CurrentStepCode + "'","select");
                        }
                        //更新批次中的合格数不合格数：用抽检批检验项目表获取最大的抽检数和不合格数
                        sql.Clear();
                        sql.Append("select count(1)from (select ois_sncode from OQCITEMSAMPLES where ");
                        sql.Append("ois_checkno = '" + ob_checkno.Text + "' and ois_ifng = 1 group by ois_sncode)");
                        string ngqty = (dh.ExecuteSql(sql.GetString(), "select") as DataTable).Rows[0][0].ToString();
                        dh.ExecuteSql("update OQCBATCH set ob_ngqty='" + ngqty + "',ob_okqty=(select max(oi_checkqty)-'" + ngqty + "' from OQCItems where oi_checkno ='" + ob_checkno.Text + "') where ob_checkno ='" + ob_checkno.Text + "'", "update");
                        OperateResult.AppendText(">>序列号 " + sncode.Text + "操作成功\n", Color.Green);
                        GetBatch.PerformClick();
                        ChoosedDGV.DataSource = null;
                        BaseUtil.CleanDGVData(CheckTypeDGV);
                        BaseUtil.CleanDGVData(WaitChooseDGV);
                        sncode.Text = "";
                        sncode.Focus();
                    }
                    else OperateResult.AppendText(">>含有未通过项请勾选不良明细\n", Color.Red);
                }
                else OperateResult.AppendText(">>请勾选送检明细\n", Color.Red);
            }
            else OperateResult.AppendText(">>必须是待检验或者检测中的送检批才能进行此操作\n", Color.Red);
        }

        private void BatchPass_Click(object sender, EventArgs e)
        {
            if (CheckBefore())
            {
                if (int.Parse(ob_ngqty.Text == "" ? "0" : ob_ngqty.Text) <= int.Parse(ob_maxngacceptqty.Text == "" ? ob_maxngacceptqty.Text : ob_maxngacceptqty.Text))
                {
                    dh.ExecuteSql("update OQCBatch set ob_status='OK',ob_result='OK' where ob_checkno='" + ob_checkno.Text + "'", "select");
                    string ErrorMessage;
                    LogicHandler.UpdateOQCMessage(ms_sncode.Text, ob_checkno.Text, ob_makecode.Text, "OQC批判过", User.UserSourceCode, User.UserName, "批次通过", out ErrorMessage);
                    //记录操作日志
                    LogicHandler.InsertMakeProcess(ms_sncode.Text, ob_makecode.Text, User.UserSourceCode, "批结果判定", "批次通过", User.UserName);
                    GetBatch.PerformClick();
                    BatchPass.Enabled = false;
                    OperateResult.AppendText(">>" + ob_checkno.Text + "通过批成功\n", Color.Green);
                }
                else
                    OperateResult.AppendText(">>当前批次不合格数为" + ob_ngqty.Text + ",大于最大不合格允通过数" + ob_maxngacceptqty.Text + "\n", Color.Red);
            }
        }

        private bool CheckBefore()
        {
            if (ob_maxngacceptqty.Text == "" || ob_maxngacceptqty.Text == "0")
            {
                if (ob_aqlcode.Text == "")
                    OperateResult.AppendText(">>最大不合格允许通过数为空的时候必须填写维护AQL标准\n", Color.Red);
                else
                {
                    dt = (DataTable)dh.ExecuteSql("select nvl(ad_minqty,0)ad_minqty,nvl(ad_maxqty,0)ad_maxqty,nvl(ad_maxngacceptqty,0)ad_maxngacceptqty from QUA_Aql left join qua_aqldetail on ad_alid=al_id where al_code='" + ob_aqlcode.Text + "'", "select");
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            int batchqty = int.Parse(ob_nowcheckqty.Text == "" ? "0" : ob_nowcheckqty.Text);
                            int minqty = int.Parse(dt.Rows[i]["ad_minqty"].ToString());
                            int maxqty = int.Parse(dt.Rows[i]["ad_maxqty"].ToString());
                            if (batchqty > minqty && batchqty <= maxqty)
                            {
                                ob_maxngacceptqty.Text = dt.Rows[i]["ad_maxngacceptqty"].ToString();
                                return true;
                            }
                        }
                    }
                }
            }
            else
            {
                sql.Clear();
                sql.Append("select wm_concat(oi_itemcode) codes from OQCitems where ");
                sql.Append("nvl(oi_checkqty,0)<oi_sampleqty  and oi_sampleqty>0 and rownum<30 and oi_checkno='" + ob_checkno.Text + "'");
                dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["codes"].ToString() != "")
                    {
                        OperateResult.AppendText(">>项目编号抽检数未达到样本数,项目编号[" + dt.Rows[0]["codes"].ToString() + "]\n", Color.Red);
                        return false;
                    }
                    else
                        return true;
                }
            }
            return false;
        }

        private void WaitReject_Click(object sender, EventArgs e)
        {
            BaseUtil.CleanDGVData(ChoosedDGV);
        }

        private void ChooseedReject_Click(object sender, EventArgs e)
        {
            DataTable wait = BaseUtil.filterDataTable((DataTable)WaitChooseDGV.DataSource, "ChooseAll<>0");
            DataTable choose = (ChoosedDGV.DataSource as DataTable);
            List<string> Chooseed = new List<string>();
            for (int i = 0; i < choose.Rows.Count; i++)
            {
                Chooseed.Add(choose.Rows[i]["bc_code"].ToString());
            }
            if (wait.Rows.Count > 0)
            {
                for (int i = 0; i < wait.Rows.Count; i++)
                {
                    if (!Chooseed.Contains(wait.Rows[i]["bc_code"].ToString()))
                    {
                        DataRow dr1 = choose.NewRow();
                        dr1["bc_code"] = wait.Rows[i]["bc_code"];
                        dr1["bc_name"] = wait.Rows[i]["bc_name"];
                        dr1["bg_code"] = wait.Rows[i]["bg_code"];
                        choose.Rows.Add(dr1);
                    }
                }
            }
            else OperateResult.AppendText(">>请勾选不良明细\n", Color.Red);
        }

        private void CheckTypeDGV_DataError(object sender, DataGridViewDataErrorEventArgs e) { }

        //如果当前行的Cell有修改过就将CheckBox勾选上
        private void CheckTypeDGV_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > 0 && e.ColumnIndex > 0)
                CheckTypeDGV.Rows[e.RowIndex].Cells[0].Value = true;
        }

        private void WaitChooseDGV_DataError(object sender, DataGridViewDataErrorEventArgs e) { }

        private void WaitChooseDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void CheckTypeDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }

        private void bccode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                dt = (DataTable)dh.ExecuteSql("select bc_code bg_code,bc_name bg_name from badgroup left join badcode on bg_code=bc_groupcode where bc_code='" + bccode.Text + "'", "select");
                if (dt.Rows.Count > 0)
                {
                    try
                    {
                        dt.Merge(ChoosedDGV.DataSource as DataTable);
                        BaseUtil.FillDgvWithDataTable(ChoosedDGV, dt);
                    }
                    catch (Exception) { }
                }
                else OperateResult.AppendText(">>不良代码不存在\n", Color.Red);
            }
        }

        private void ob_checkno_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                GetBatch.PerformClick();
            }
        }

        private void ms_sncode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                GetBatch.PerformClick();
        }

        private void obd_outboxcode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                GetBatch.PerformClick();
        }

        private void BadGroupCode_SelectedIndexChanged(object sender, EventArgs e)
        {
            string bg_code = BadGroupCode.SelectedValue.ToString();
            sql.Clear();
            sql.Append("select 0 ChooseAll,bc_code,bc_name,bg_code from badgroupdetail left join badgroup on bgd_bgcode=bg_code ");
            sql.Append("left join badcode on bgd_badcode=bc_code where bg_code='" + ((bg_code != "" && bg_code != "System.Data.DataRowView") ? bg_code.ToString() : "") + "' and bg_code is not null ");
            sql.Append(" order by bc_code");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            BaseUtil.FillDgvWithDataTable(WaitChooseDGV, dt);
        }
    }
}