mes-client/UAS_MES_ZT/FunctionCode/Make/Make_PositionStockQuery.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicMethod;

namespace UAS_MES_NEW.Make
{
    public partial class Make_PositionStockQuery : Form
    {
        AutoSizeFormClass asc = new AutoSizeFormClass();

        DataHelper dh;

        DataTable dt;

        LogStringBuilder sql = new LogStringBuilder();

        DataTable Dbfind;

        string ma_id;
        //上一次查询的工单号
        string last_macoe = "";

        string craftcode_condition = "";

        public Make_PositionStockQuery()
        {
            InitializeComponent();
        }

        private void Make_PositionStock_Load(object sender, EventArgs e)
        {
            asc.controllInitializeSize(this);
            ma_code.TableName = "make left join product on ma_prodcode=pr_code ";
            ma_code.SelectField = "ma_code # 工单号,ma_prodcode # 产品编号,pr_orispeccode # 型号,ma_qty # 工单数量,ma_craftcode # 工艺路线,pr_detail # 产品名称";
            ma_code.FormName = Name;
            ma_code.DBTitle = "工单查询";
            ma_code.SetValueField = new string[] { "ma_code", "ma_prodcode", "pr_detail||'-'||pr_orispeccode pr_detail", "ma_craftcode","ma_qty" };
            ma_code.Condition = "ma_statuscode='STARTED' order by ma_auditdate desc ";
            ma_code.DbChange += Ma_code_DbChange;
            sc_stepcode.Text = User.CurrentStepCode; 
            ma_code.SetLockCheckBox(Lock);
            Lock.GetMakeCodeCtl(ma_code);
            dh = SystemInf.dh;
        }

        private void Ma_code_DbChange(object sender, EventArgs e)
        {
            Dbfind = ma_code.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
            FillDataGridView();
        }

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

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

        //物料批号Enter事件
        private void pr_batchnum_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (pr_batchnum.Text != "")
                {
                    if (ma_code.Text != "")
                    {
                        sql.Clear();
                        sql.Append("select cr_code from craft left join craftdetail on cd_crid=cr_id where cr_code in (" + craftcode_condition + ") ");
                        sql.Append("and cr_prodcode='" + ma_prodcode.Text + "'  and  cr_statuscode='AUDITED'  and  (cd_stepcode='" + User.CurrentStepCode + "'or '" + User.CurrentStepCode + "' in ('CT1','CT2') )");
                        dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                        if (dt.Rows.Count > 0)
                        {
                            //输入的是条码号的时候
                            dt = (DataTable)dh.ExecuteSql("select nvl(bar_remain,0) bar_remain,bar_prodcode from barcode inner join product on pr_code=bar_prodcode where bar_code ='" + pr_batchnum.Text + "' order by bar_id desc", "select");
                            if (dt.Rows.Count > 0)
                            {
                                string bar_remain = dt.Rows[0]["bar_remain"].ToString();
                                string bar_prodcode = dt.Rows[0]["bar_prodcode"].ToString();
                                sql.Clear();
                                sql.Append("select ma_id sp_id,mm_oneuseqty sp_oneuseqty,mm_prodcode sp_fsoncode, mm_repprodcode sp_soncode,''sp_prefix from make left join makematerial on mm_maid=ma_id left join Product on mm_prodcode=pr_code ");
                                sql.Append("where ma_code='" + ma_code.Text + "' and  ma_prodcode='" + ma_prodcode.Text + "' and ");
                                sql.Append("pr_mncode='" + User.CurrentStepCode + "' and (mm_prodcode ='" + bar_prodcode + "' or mm_repprodcode like '%" + bar_prodcode + "%') ");
                                dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                                if (dt.Rows.Count > 0)
                                {
                                    sql.Clear();
                                    sql.Append("select mss_makecode,mss_linecode from makesourcestock where mss_barcode = '" + pr_batchnum.Text + "'");
                                    DataTable BARCODE = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                                    if (BARCODE.Rows.Count > 0)
                                    {
                                        OperateResult.AppendText(">>物料批号" + pr_batchnum.Text + "已经被工单:" + BARCODE.Rows[0]["mss_makecode"] + ",线别:" + BARCODE.Rows[0]["mss_linecode"] + "备料\n", Color.Red, pr_batchnum);
                                        return;
                                    }
                                    InsertMakeSourceStock(dt, int.Parse(bar_remain));
                                }
                                else OperateResult.AppendText(">>物料批号" + pr_batchnum.Text + "对应的物料不是当前工单当前工序需要备的物料\n", Color.Red, pr_batchnum);
                            }
                            else
                            {
                                OperateResult.AppendText(">>物料批号" + pr_batchnum.Text + "对应的物料条码未找到库存信息\n", Color.Red, pr_batchnum);
                                ////是否包含备料的批管控物料号
                                //sql.Clear();
                                //sql.Append("select sp_id,sp_oneuseqty,sp_fsoncode,sp_soncode,sp_prefix from stepbom left join stepproduct on sp_sbid=sb_id ");
                                //sql.Append("where sb_craftcode='" + ma_craftcode.Text + "' and  sb_prodcode='" + ma_prodcode.Text + "' and ");
                                //sql.Append("sb_bomversion='" + ma_bomversion.Text + "'  and sp_stepcode='" + User.CurrentStepCode + "' and sp_tracekind=2 ");
                                //sql.Append("and (instr('" + pr_batchnum.Text + "',SP_SONCODE) > 0 or instr('" + pr_batchnum.Text + "',SP_FSONCODE)>0)");
                                //dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                                //if (dt.Rows.Count > 0)
                                //{
                                //    InsertMakeSourceStock(dt, 0);
                                //}
                                //else
                                //{
                                //    //匹配批管控物料中的前缀和长度 
                                //    sql.Clear();
                                //    sql.Append("select sp_id,sp_fsoncode,sp_oneuseqty,sp_soncode,sp_prefix from stepbom left join stepproduct on sp_sbid=sb_id ");
                                //    sql.Append("where sb_craftcode='" + ma_craftcode.Text + "' and  sb_prodcode='" + ma_prodcode.Text + "' and ");
                                //    sql.Append("sb_bomversion='" + ma_bomversion.Text + "'  and sp_stepcode='" + User.CurrentStepCode + "' and sp_tracekind=2 ");
                                //    sql.Append("and instr('" + pr_batchnum.Text + "',sp_prefix) > 0 and ((nvl(SP_LENGTH,0)>0 and sp_length ");
                                //    sql.Append("= length('" + pr_batchnum.Text + "')) or nvl(SP_LENGTH,0)=0)");
                                //    dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                                //    if (dt.Rows.Count > 0)
                                //    {
                                //        InsertMakeSourceStock(dt, 0);
                                //    }
                                //    else OperateResult.AppendText(">>物料批号" + pr_batchnum.Text + "对应的物料不是当前工单当前工序需要备的物料\n", Color.Red, pr_batchnum);
                                //}
                            }
                        }
                        else OperateResult.AppendText(">>当前岗位资源工序不在工单对应的途程中\n", Color.Red);
                    }
                    else OperateResult.AppendText(">>工单号不能为空\n", Color.Red, pr_batchnum);
                }
                else { };
            }
        }

        private void InsertMakeSourceStock(DataTable dt, int Barremain)
        {
            string sp_oneuseqty = dt.Rows[0]["sp_oneuseqty"].ToString();
            string sp_fsoncode = dt.Rows[0]["sp_fsoncode"].ToString();
            string sp_soncode = dt.Rows[0]["sp_soncode"].ToString();
    //        string sp_prefix = dt.Rows[0]["sp_prefix"].ToString();
            sql.Clear();
            sql.Append("insert into makesourcestock (mss_id,mss_makecode,mss_linecode ,mss_craftcode,");
            sql.Append("mss_stepcode,mss_barcode,mss_fprodcode,mss_indate,mss_inman,mss_qty,");
            sql.Append("mss_remain,mss_baseqty,mss_prodcode,mss_maid,mss_sourcecode) values(makesourcestock_seq.nextval,'" + ma_code.Text + "','" + User.UserLineCode + "',");
            sql.Append("'" + ma_craftcode.Text + "','" + sc_stepcode.Text + "','" + pr_batchnum.Text + "','" + sp_fsoncode + "',");
            sql.Append("sysdate,'" + User.UserCode + "','" + Barremain + "','" + Barremain + "','" + sp_oneuseqty + "','" + sp_soncode + "','" + ma_id + "','" + User.UserSourceCode + "')");
            dh.ExecuteSql(sql.GetString(), "insert");
            dh.UpdateByCondition("barcode", "bar_place='" + ma_code.Text + "'", "bar_code='" + pr_batchnum.Text + "' and bar_remain = "+ Barremain + "");
            LogicHandler.DoCommandLog(Tag.ToString(), User.UserCode, ma_code.Text, User.UserLineCode, User.UserSourceCode, "岗位备料", "上料物料" + pr_batchnum.Text, "", "");
            //数据插入成功后加载Grid的数据
            FillDataGridView();
            OperateResult.AppendText(">>物料批号" + pr_batchnum.Text + "备料成功\n", Color.Green, pr_batchnum);
        }

        //加载Grid数据
        private void FillDataGridView()
        {
            //if (!show_all_check.Checked)
            //{
                sql.Clear();
                sql.Append("select nvl(mss_makecode,' ') mss_makecode,mss_linecode,mm_prodcode sp_fsoncode,mm_repprodcode sp_soncode,mss_prodcode,mm_oneuseqty sp_oneuseqty,sum(nvl(mss_qty,0)) mss_qty ,sum(nvl(mss_remain,0))mss_remain,");
                sql.Append("pr_detail,pr_orispeccode,sum(nvl(mss_useqty,0)) mss_useqty from make left join makematerial on mm_maid=ma_id left join Product on mm_prodcode=pr_code ");
                sql.Append(" left join makesourcestock on  mss_craftcode=ma_craftcode and mss_makecode=ma_code  and mss_linecode = '" + User.UserLineCode + "' ");
                sql.Append("and mss_stepcode=pr_mncode and nvl(mm_repprodcode,' ')=nvl(mss_prodcode,' ') and mm_prodcode=mss_fprodcode  where ma_prodcode='" + ma_prodcode.Text + "' and ma_craftcode='" + ma_craftcode.Text + "' ");
                sql.Append("and pr_mncode='" + User.CurrentStepCode + "' and ma_code='" + ma_code.Text + "' group by nvl(mss_makecode,' ') ,mss_linecode,mm_prodcode ,mm_repprodcode ,mss_prodcode,mm_oneuseqty  ,pr_detail,pr_orispeccode order by mm_prodcode");
                DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
                BaseUtil.FillDgvWithDataTable(BatchProductDGV, dt);
            //}
            //else
            //{
            //    //sql.Clear();
            //    //sql.Append("select nvl(mss_makecode,' ') mss_makecode,mss_linecode,sp_fsoncode,nvl(mss_id,0) mss_id,sp_soncode,mss_prodcode,sp_oneuseqty,mss_barcode,nvl(mss_qty,0) mss_qty ,nvl(mss_remain,0)mss_remain,");
            //    //sql.Append("pr_detail,nvl(mss_useqty,0) mss_useqty from stepbom left join stepproduct on sp_sbid=sb_id left join product on ");
            //    //sql.Append("pr_code=sp_mothercode left join makesourcestock on  mss_craftcode=sb_craftcode ");
            //    //sql.Append("and mss_stepcode=sp_stepcode and sp_soncode=mss_prodcode and sp_fsoncode=mss_fprodcode where  ");
            //    //sql.Append(" sp_stepcode='" + User.CurrentStepCode + "' and sp_tracekind=2 and mss_linecode = '" + User.UserLineCode + "' order  by sp_soncode");
            //    //DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            //    //BaseUtil.FillDgvWithDataTable(BatchProductDGV, dt);
            //    sql.Clear();
            //    sql.Append("select nvl(mss_makecode,' ') mss_makecode,mss_linecode,mm_prodcode sp_fsoncode,nvl(mss_id,0) mss_id,mm_repprodcode sp_soncode,mss_prodcode,mm_oneuseqty sp_oneuseqty,mss_barcode,nvl(mss_qty,0) mss_qty ,nvl(mss_remain,0)mss_remain,");
            //    sql.Append("pr_detail,pr_orispeccode,nvl(mss_useqty,0) mss_useqty from make left join makematerial on mm_maid=ma_id left join Product on mm_prodcode=pr_code ");
            //    sql.Append(" left join makesourcestock on  mss_craftcode=ma_craftcode and mss_makecode=ma_code  and mss_linecode = '" + User.UserLineCode + "' ");
            //    sql.Append("and mss_stepcode=pr_mncode and nvl(mm_repprodcode,' ')=nvl(mss_prodcode,' ') and mm_prodcode=mss_fprodcode  where  nvl(ma_prodcode,' ')<>' ' and nvl(ma_craftcode,' ')<> ' ' and ");
            //    sql.Append(" pr_mncode='" + User.CurrentStepCode + "'  order by mm_prodcode");
            //    DataTable dt = (DataTable)dh.ExecuteSql(sql.GetString(), "select");
            //    BaseUtil.FillDgvWithDataTable(BatchProductDGV, dt);
            //}
        }

        private void Confirm_Click(object sender, EventArgs e)
        {
            pr_batchnum_KeyDown(sender, new KeyEventArgs(Keys.Enter));
        }

        private void Screen_Click(object sender, EventArgs e)
        {
            FillDataGridView();
        }

        private void ma_prodcode_TextChanged(object sender, EventArgs e)
        {
            if (ma_code.Text != "")
            {
                dt = (DataTable)dh.ExecuteSql("select ma_id,ma_craftcode,ma_statuscode,ma_code,ma_prodcode,pr_spec,pr_detail||'-'||pr_orispeccode pr_detail,ma_bomversion from make left join product on ma_prodcode=pr_code where ma_code='" + ma_code.Text + "'", "select");
                if (dt.Rows.Count > 0)
                {
                    ma_id = dt.Rows[0]["ma_id"].ToString();
                    string craftcode = dt.Rows[0]["ma_craftcode"].ToString();
                    BaseUtil.SetFormValue(this.Controls, dt);
                }
                else OperateResult.AppendText(">>工单号不存在\n", Color.Red);
            }
            else OperateResult.AppendText(">>工单号不允许为空\n", Color.Red);
        }

        private void BatchProduct_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //if (BatchProductDGV.Columns[e.ColumnIndex].Name == "DeleteRow")
            //{
            //    if (e.RowIndex >= 0)
            //    {
            //        string id = BatchProductDGV.Rows[e.RowIndex].Cells["mss_id"].Value.ToString();
            //        string useqty = BatchProductDGV.Rows[e.RowIndex].Cells["mss_useqty"].Value.ToString();
            //        string soncode = BatchProductDGV.Rows[e.RowIndex].Cells["sp_soncode"].Value.ToString();
            //        if (id != "0")
            //        {
            //            dh.ExecuteSql("delete from makesourcestock where mss_id='" + id + "'", "delete");
            //            LogicHandler.DoCommandLog(Tag.ToString(), User.UserCode, ma_code.Text, User.UserLineCode, User.UserSourceCode, "岗位备料", "删除物料" + soncode, "", "");
            //            OperateResult.AppendText(">>料号" + soncode + "删除成功\n");
            //            FillDataGridView();
            //        }
            //    }
            //}
        }

        private void ma_code_UserControlTextChanged(object sender, EventArgs e)
        {
            if (ma_code.Text.Length > 4)
            {
                string ErrMessage = "";
                if (LogicHandler.CheckMakeStatus(ma_code.Text, out ErrMessage))
                {
                    craftcode_condition = "";
                    DataTable dt = (DataTable)dh.ExecuteSql("select ma_craftcode from make where ma_code='" + ma_code.Text + "'", "select");
                    string craftcode = "";
                    if (dt.Rows.Count > 0)
                    {
                        craftcode = dt.Rows[0]["ma_craftcode"].ToString();
                    }
                    sql.Clear();
                    sql.Append("select distinct ms_craftcode ma_craftcode from makeserial left join craft on cr_code=ms_craftcode ");
                    sql.Append("where ms_makecode='" + ma_code.Text + "'  and ms_craftcode<>'" + craftcode + "'");
                    dt.Merge((DataTable)dh.ExecuteSql(sql.GetString(), "select"));
                    ma_craftcode.DisplayMember = "ma_craftcode";
                    ma_craftcode.ValueMember = "ma_craftcode";
                    ma_craftcode.DataSource = dt;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows.Count - 1 == i)
                            craftcode_condition += "'" + dt.Rows[i]["ma_craftcode"].ToString() + "'";
                        else
                            craftcode_condition += "'" + dt.Rows[i]["ma_craftcode"].ToString() + "',";
                    }
                }
            }
        }

        private void BatchProductDGV_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //if (BatchProductDGV.Columns[e.ColumnIndex].Name == "mss_qty")
            //{
            //    string id = BatchProductDGV.Rows[e.RowIndex].Cells["mss_id"].Value.ToString();
            //    string qty = "0";
            //    if (id != "0")
            //    {
            //        try
            //        {
            //            if (int.Parse(BatchProductDGV.Rows[e.RowIndex].Cells["mss_qty"].Value.ToString()) > 0)
            //            {
            //                qty = BatchProductDGV.Rows[e.RowIndex].Cells["mss_qty"].Value.ToString();
            //            }
            //            else
            //            {
            //                OperateResult.AppendText(">>数量必须大于0\n", Color.Red);
            //                return;
            //            }
            //        }
            //        catch (Exception)
            //        {
            //            OperateResult.AppendText(">>数量必须大于0\n", Color.Red);
            //            return;
            //        }
            //        dh.UpdateByCondition("makesourcestock", "mss_qty='" + qty + "'", "mss_id='" + id + "'");
            //        BatchProductDGV.Rows[e.RowIndex].Cells["mss_remain"].Value = int.Parse(BatchProductDGV.Rows[e.RowIndex].Cells["mss_qty"].Value.ToString()) - int.Parse(BatchProductDGV.Rows[e.RowIndex].Cells["mss_useqty"].Value.ToString());
            //        dh.UpdateByCondition("makesourcestock", "mss_remain=mss_qty-nvl(mss_useqty,0)", "mss_id='" + id + "'");
            //    }
            //}
        }

        private void BatchProductDGV_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            //if (BatchProductDGV.Columns[e.ColumnIndex].Name == "DeleteRow")
            //{
            //    if (BatchProductDGV.Rows[e.RowIndex].Cells["mss_id"].Value.ToString() == "0")
            //        e.Value = Properties.Resources.WhiteImage;
            //    else
            //        e.Value = Properties.Resources.bindingNavigatorDeleteItem_Image;
            //}
        }

        private void ma_craftcode_SelectedIndexChanged(object sender, EventArgs e)
        {
            Screen.PerformClick();
        }

        private void BatchProductDGV_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            //bool mouseOver = e.CellBounds.Contains(this.PointToClient(Cursor.Position));
            //if (e.ColumnIndex > 0)
            //{
            //    if (BatchProductDGV.Columns[e.ColumnIndex].Name == "mss_remain")
            //    {
            //        SolidBrush solidBrush = new SolidBrush(Color.FromArgb(51, 153, 255));
            //        e.Graphics.FillRectangle(mouseOver ? solidBrush : Brushes.LightSeaGreen, e.CellBounds);
            //        Rectangle border = e.CellBounds;
            //        border.Width -= 1;
            //        e.Graphics.DrawRectangle(Pens.White, border);
            //        e.PaintContent(e.CellBounds);
            //        e.Handled = true;
            //    }
            //}
        }

        private void pi_type_check_CheckedChanged(object sender, EventArgs e)
        {
            if (show_all_check.Checked)
            {
                pr_batchnum.Enabled = false;
                FillDataGridView();
            }
            else
            {
                pr_batchnum.Enabled = true;
                FillDataGridView();
            }
        }

        private void normalButton1_Click(object sender, EventArgs e)
        {
            bool makesources = false;
            for (int i = 0; i < BatchProductDGV.Rows.Count; i++)
            {
                string id_1 = BatchProductDGV.Rows[i].Cells["mss_id"].Value.ToString();
                if (id_1 != "0")
                {
                    makesources = true;
                }
            }
            if (makesources)
            {
                String dialog = MessageBox.Show("是否确认全部下料", "提示", MessageBoxButtons.OKCancel).ToString();
                if (dialog != "OK")
                {
                    OperateResult.AppendText("取消全部下料\n", Color.Black);
                    return;
                }
                else
                {
                    List<string> idcol = new List<string>();
                    List<string> makecodecol = new List<string>();
                    List<string> soncodecol = new List<string>();
                    for (int i = 0; i < BatchProductDGV.Rows.Count; i++)
                    {

                        string id = BatchProductDGV.Rows[i].Cells["mss_id"].Value.ToString();
                        string useqty = BatchProductDGV.Rows[i].Cells["mss_useqty"].Value.ToString();
                        string soncode = BatchProductDGV.Rows[i].Cells["sp_soncode"].Value.ToString();
                        string makecode = BatchProductDGV.Rows[i].Cells["mss_makecode"].Value.ToString();
                        if (id != "0")
                        {
                            idcol.Add(id);
                            makecodecol.Add(makecode);
                            soncodecol.Add("删除物料" + soncode);
                            OperateResult.AppendText(">>料号" + soncode + "删除成功\n");
                        }
                    }
                    sql.Clear();
                    sql.Append("insert into commandlog(cl_id,cl_caller,cl_man,cl_date,cl_linecode,cl_sourcecode,cl_makecode,cl_operate,");
                    sql.Append("cl_result,cl_sncode,cl_code) values( commandlog_seq.nextval,'" + Tag.ToString() + "','" + User.UserCode + "',sysdate,'" + User.UserLineCode + "' ,");
                    sql.Append("'" + User.UserSourceCode + " ' ,:iMakeCode,'岗位备料',:iResult,' ',' ')");
                    dh.BatchInsert(sql.GetString(), new String[] { "iMakeCode", "iResult" }, makecodecol.ToArray(), soncodecol.ToArray());
                    dh.BatchInsert("delete from makesourcestock where mss_id=:id", new String[] { "id" }, idcol.ToArray());
                    FillDataGridView();
                }
            }
            else
            {
                OperateResult.AppendText(">>没有上料记录，无需全部下料\n", Color.Red);
            }
        }
    }
}