Beispiel #1
0
 private void cmTransFomat_Click(object sender, EventArgs e)
 {
     try
     {
         txtMoCode.Text = txtMoCode.Text.Replace("\r", "").Replace("\n", ";").Replace("\t", ",");
     }
     catch (Exception ex)
     {
         frmMessege frmmsg = new frmMessege(ex.ToString(), "cmTransFomat_Click()");
         frmmsg.ShowDialog(this);
     }
 }
Beispiel #2
0
        private void btnQurey_Click(object sender, EventArgs e)
        {
            iLoginEx.WriteUserProfileValue("APS21Query", "dtDateL", dtDateL.Value.ToString("yyyy-MM-dd"));
            iLoginEx.WriteUserProfileValue("APS21Query", "dtDateH", dtDateH.Value.ToString("yyyy-MM-dd"));
            iLoginEx.WriteUserProfileValue("APS21Query", "chkDate", chkDate.Checked ? "1" : "0");
            iLoginEx.WriteUserProfileValue("APS21Query", "chkShowPreparedMO", chkShowPreparedMO.Checked ? "1" : "0");
            iLoginEx.WriteUserProfileValue("APS21Query", "chkShowFinishedMO", chkShowFinishedMO.Checked ? "1" : "0");
            iLoginEx.WriteUserProfileValue("APS21Query", "chkNoShowColor", chkNoShowColor.Checked ? "1" : "0");
            iLoginEx.WriteUserProfileValue("APS21Query", "chkSkipFinish", chkSkipFinish.Checked ? "1" : "0");
            iLoginEx.WriteUserProfileValue("APS21Query", "txtcPersonCode", txtcPersonCode.Text);
            iLoginEx.WriteUserProfileValue("APS21Query", "txtcInvStd", txtcInvStd.Text);
            iLoginEx.WriteUserProfileValue("APS21Query", "txtcInvCode", txtcInvCode.Text);
            iLoginEx.WriteUserProfileValue("APS21Query", "txtMoCode", txtMoCode.Text);
            iLoginEx.WriteUserProfileValue("APS21Query", "txtcSOCode", txtcSOCode.Text);
            iLoginEx.WriteUserProfileValue("APS21Query", "chkALLMaking", chkALLMaking.Checked ? "1" : "0");
            try
            {
                OleDbConnection myConn = new OleDbConnection(iLoginEx.ConnString());
                if (myConn.State == System.Data.ConnectionState.Open)
                {
                    myConn.Close();
                }
                myConn.Open();
                OleDbCommand myCommand = new OleDbCommand("", myConn);


                string   cinvCodeChild = "";
                string[] paraCinvCode  = null;
                selectSQL             = " if exists (select 1  from tempdb.dbo.sysobjects (nolock) where upper(name) = upper('APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "') and type='U')   \r\n";
                selectSQL            += " drop table tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + " ;   \r\n";
                myCommand.CommandText = selectSQL;
                myCommand.ExecuteNonQuery();

                selectSQL             = " create table tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "(  \r\n";
                selectSQL            += " MoCode nvarchar(30)  null,MoDId int null,SortSeq int null  )   \r\n";
                selectSQL            += "   \r\n";
                selectSQL            += "   \r\n";
                myCommand.CommandText = selectSQL;
                myCommand.ExecuteNonQuery();


                selectSQL  = "select UID=" + iLoginEx.UID().ToString() + ",isnull(ud.ViewSort,0) as 'ViewSort',a.MoClass,d.cDepName as '部门',ud.Priority as '优先级',c.MoCode as'制造单号',a.sortseq as'行号M',a.OrderCode as'销售订单',person.cpersonname as '业务员',  \r\n";
                selectSQL += " a.OrderSeq as'行号S',a.InvCode as '产品编码',i.cInvName as '产品名称',i.cInvStd as '规格型号',convert(bit,case when isnull(a.Status,0)=3 then 1 else 0 end) as'投产',  \r\n";
                selectSQL += " b.StartDate as'计划生产日期',isnull(a.qty,0) as '排产数量',isnull(a.QualifiedInQty,0) as '完工数量',  \r\n";
                selectSQL += "  convert(bit,case when len(isnull(a.CloseUser,''))>0  or Status=4 then 1 else 0 end) as'结案',soa.cDefine31 as 'LOGO' ,soa.cDefine33 as '软件信息',a.Define29 as '备注',调库存=isnull(ud.ReProduce,0),a.MoId,a.ModId  \r\n";
                selectSQL += "   from mom_orderdetail a   (nolock)   \r\n";
                selectSQL += "  left join  mom_morder b  (nolock)on a.MoId=b.MoId  and a.ModId=b.ModId    \r\n";
                selectSQL += "  left join  mom_order c  (nolock) on a.MoId=c.MoId    \r\n";
                selectSQL += "  left join  Department d  (nolock) on d.cDepCode=a.MDeptCode    \r\n";
                selectSQL += "  left join  SO_SODetails soa (nolock) on  a.OrderDId =soa.iSOsID  \r\n";
                selectSQL += "  left join  SO_SOMain sob (nolock) on soa.ID =sob.ID    \r\n";
                selectSQL += "  left join  person  (nolock) on sob.cPersonCode= person.cpersoncode   \r\n";
                selectSQL += "  left join Inventory i (nolock) on a.InvCode=i.cInvCode  \r\n";
                selectSQL += "  left join zhrs_t_mom_orderdetail_userDefine ud (nolock) on ud.MoID=a.MoID and ud.MoDId=a.MoDId  \r\n";
                selectSQL += "  where 1=1   \r\n";
                if (!(chkShowFinishedMO.Checked && chkShowPreparedMO.Checked))
                {
                    if (chkShowFinishedMO.Checked)
                    {
                        selectSQL += "  and  isnull(a.OrderCode,'')<>'' ";
                    }
                    if (chkShowPreparedMO.Checked)
                    {
                        selectSQL += "  and  isnull(a.OrderCode,'')='' ";
                    }
                }
                if (chkALLMaking.Checked)
                {
                    selectSQL += " and  isnull(a.CloseUser,'')='' and (isnull(a.qty,0)-isnull(a.QualifiedInQty,0))>=0   \r\n";
                }
                else
                {
                    if (chkSkipFinish.Checked)
                    {
                        selectSQL += " and  isnull(a.CloseUser,'')='' and (isnull(a.qty,0)-isnull(a.QualifiedInQty,0))>0   \r\n";
                    }

                    //销售订单号
                    txtcSOCode.Text = txtcSOCode.Text.Trim();
                    txtcSOCode.Text = txtcSOCode.Text.Replace(";", ";");

                    if (txtcSOCode.Text.Length > 0)
                    {
                        cinvCodeChild = "";
                        paraCinvCode  = txtcSOCode.Text.Split(';');
                        if (paraCinvCode.Length > 0)
                        {
                            for (int i = 0; i < paraCinvCode.Length; i++)
                            {
                                cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',";
                            }

                            selectSQL += " and  a.OrderCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n";
                        }
                        else
                        {
                            selectSQL += " and  a.OrderCode ='" + txtcSOCode.Text + "' \r\n";
                        }
                    }


                    //制造单号
                    string[] paraSortSeq = null;

                    txtMoCode.Text = txtMoCode.Text.Trim();
                    txtMoCode.Text = txtMoCode.Text.Replace(";", ";");
                    txtMoCode.Text = txtMoCode.Text.Replace(",", ",");

                    if (txtMoCode.Text.Length > 0)
                    {
                        paraCinvCode = txtMoCode.Text.Split(';');
                        if (paraCinvCode.Length > 0)
                        {
                            for (int i = 0; i < paraCinvCode.Length; i++)
                            {
                                if (paraCinvCode[i].Length > 0)
                                {
                                    paraSortSeq = paraCinvCode[i].Split(',');
                                    if (paraSortSeq.Length > 1)
                                    {
                                        for (int k = 1; k < paraSortSeq.Length; k++)
                                        {
                                            if (paraSortSeq[k].Length > 0)
                                            {
                                                myCommand.CommandText = " insert into  tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "(MoCode,SortSeq)values('" + paraSortSeq[0] + "'," + paraSortSeq[k] + ")   \r\n";
                                                myCommand.ExecuteNonQuery();
                                            }
                                        }
                                    }
                                    else
                                    {
                                        myCommand.CommandText = " insert into  tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + "(MoCode)values('" + paraCinvCode[i].ToString() + "')   \r\n";
                                        myCommand.ExecuteNonQuery();
                                    }
                                }
                            }
                            selectSQL += " and  exists (select 1 from tempdb..APS21_moQurey" + iLoginEx.GetMacAddress().Replace(":", "") + " q where q.MoCode=c.MoCode and (q.SortSeq=a.SortSeq or q.SortSeq is null))  \r\n";
                        }
                        else
                        {
                            selectSQL += " and  c.MoCode ='" + txtMoCode.Text + "' \r\n";
                        }
                    }


                    //物料编码
                    txtcInvCode.Text = txtcInvCode.Text.Trim();
                    txtcInvCode.Text = txtcInvCode.Text.Replace(";", ";");

                    if (txtcInvCode.Text.Length > 0)
                    {
                        cinvCodeChild = "";
                        paraCinvCode  = txtcInvCode.Text.Split(';');
                        if (paraCinvCode.Length > 0)
                        {
                            for (int i = 0; i < paraCinvCode.Length; i++)
                            {
                                cinvCodeChild += "'" + paraCinvCode[i].ToString() + "',";
                            }

                            selectSQL += " and   a.InvCode in (" + cinvCodeChild + "'\r\n'" + ") \r\n";
                        }
                        else
                        {
                            selectSQL += " and   a.InvCode ='" + txtcInvCode.Text + "' \r\n";
                        }
                    }

                    //规格型号
                    txtcInvStd.Text = txtcInvStd.Text.Trim();
                    txtcInvStd.Text = txtcInvStd.Text.Replace(" ", ";");
                    txtcInvStd.Text = txtcInvStd.Text.Replace("\t", ";");
                    txtcInvStd.Text = txtcInvStd.Text.Replace("\r", ";");
                    txtcInvStd.Text = txtcInvStd.Text.Replace("\n", ";");
                    txtcInvStd.Text = txtcInvStd.Text.Replace(";", ";");
                    while (txtcInvStd.Text.IndexOf(";;") > -1)
                    {
                        txtcInvStd.Text = txtcInvStd.Text.Replace(";;", ";");
                    }

                    if (txtcInvStd.Text.Length > 0)
                    {
                        string mySelectQuery = "select cInvCode  from Inventory (nolock) where ";
                        cinvCodeChild = "";
                        paraCinvCode  = txtcInvStd.Text.Split(';');
                        if (paraCinvCode.Length > 0)
                        {
                            for (int i = 0; i < paraCinvCode.Length; i++)
                            {
                                cinvCodeChild += "  cinvstd like '%" + paraCinvCode[i].ToString() + "%'  or ";
                            }

                            cinvCodeChild += iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r";
                            mySelectQuery += cinvCodeChild.Replace("or " + iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r", "");
                        }
                        else
                        {
                            mySelectQuery += " cinvstd like '%" + txtcInvStd.Text + "%'  ";
                        }

                        cinvCodeChild = "";


                        myCommand.CommandText = mySelectQuery;
                        OleDbDataReader myReader = myCommand.ExecuteReader();
                        while (myReader.Read())
                        {
                            cinvCodeChild += "'" + Convert.ToString(myReader["cInvCode"]) + "',";
                        }
                        myReader.Close();
                        myReader.Dispose();
                        cinvCodeChild += iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r";
                        selectSQL     += " and  a.InvCode in (" + cinvCodeChild.Replace("," + iLoginEx.Chr(8) + iLoginEx.Chr(8) + "\r\r", "") + ") \r\n";
                    }

                    if (chkDate.Checked)
                    {
                        selectSQL += " and b.StartDate >= N'" + dtDateL.Value.ToString("yyyy-MM-dd") + "' And b.StartDate<= N'" + dtDateH.Value.ToString("yyyy-MM-dd") + "'  \r\n";
                    }
                }



                this.Close();
            }
            catch (Exception ex)
            {
                frmMessege frmmsg = new frmMessege(ex.ToString(), "btnQurey_Click()");
                frmmsg.ShowDialog(this);
            }
        }
Beispiel #3
0
        private void frmQuery_Load(object sender, EventArgs e)
        {
            try
            {
                selectSQL       = "";
                dtDateH.Enabled = false;
                dtDateL.Enabled = false;
                chkDate.Checked = true;

                DateTime dt         = DateTime.Now;
                DateTime startMonth = dt.AddDays(1 - dt.Day);              //本月月初
                DateTime endMonth   = startMonth.AddMonths(1).AddDays(-1); //本月月末//

                dtDateL.Value = startMonth;
                dtDateH.Value = endMonth;
                dtDateL.Value = iLoginEx.ReadUserProfileValue("APS21Query", "dtDateL").Length == 0 ? startMonth : Convert.ToDateTime(iLoginEx.ReadUserProfileValue("APS21Query", "dtDateL"));
                dtDateH.Value = iLoginEx.ReadUserProfileValue("APS21Query", "dtDateH").Length == 0 ? startMonth : Convert.ToDateTime(iLoginEx.ReadUserProfileValue("APS21Query", "dtDateH"));

                chkDate.Checked           = iLoginEx.ReadUserProfileValue("APS21Query", "chkDate") == "1" ? true : false;
                chkShowPreparedMO.Checked = iLoginEx.ReadUserProfileValue("APS21Query", "chkShowPreparedMO") == "1" ? true : false;
                chkShowFinishedMO.Checked = iLoginEx.ReadUserProfileValue("APS21Query", "chkShowFinishedMO") == "1" ? true : false;
                chkNoShowColor.Checked    = iLoginEx.ReadUserProfileValue("APS21Query", "chkNoShowColor") == "1" ? true : false;
                chkSkipFinish.Checked     = iLoginEx.ReadUserProfileValue("APS21Query", "chkSkipFinish") == "1" ? true : false;
                chkALLMaking.Checked      = iLoginEx.ReadUserProfileValue("APS21Query", "chkALLMaking") == "1" ? true : false;
                txtcPersonCode.Text       = iLoginEx.ReadUserProfileValue("APS21Query", "txtcPersonCode");
                txtcInvStd.Text           = iLoginEx.ReadUserProfileValue("APS21Query", "txtcInvStd");
                txtcInvCode.Text          = iLoginEx.ReadUserProfileValue("APS21Query", "txtcInvCode");
                txtMoCode.Text            = iLoginEx.ReadUserProfileValue("APS21Query", "txtMoCode");
                txtcSOCode.Text           = iLoginEx.ReadUserProfileValue("APS21Query", "txtcSOCode");


                if (chkALLMaking.Checked)
                {
                    chkSkipFinish.Enabled  = false;
                    txtcInvCode.Enabled    = false;
                    txtcInvStd.Enabled     = false;
                    txtcSOCode.Enabled     = false;
                    txtcPersonCode.Enabled = false;
                    txtMoCode.Enabled      = false;
                    chkDate.Enabled        = false;
                    dtDateH.Enabled        = false;
                    dtDateL.Enabled        = false;
                }
                else
                {
                    chkSkipFinish.Enabled  = true;
                    txtcInvCode.Enabled    = true;
                    txtcInvStd.Enabled     = true;
                    txtcSOCode.Enabled     = true;
                    txtcPersonCode.Enabled = true;
                    txtMoCode.Enabled      = true;
                    chkDate.Enabled        = true;
                    if (chkDate.Checked)
                    {
                        dtDateH.Enabled = true;
                        dtDateL.Enabled = true;
                    }
                }
            }
            catch (Exception ex)
            {
                frmMessege frmmsg = new frmMessege(ex.ToString(), "frmQuery_Load()");
                frmmsg.ShowDialog(this);
            }
        }
Beispiel #4
0
        private void LoadData()
        {
            try
            {
                txtcInvCode.Text = cInvCode;
                txtcInvName.Text = cInName;
                txtcInvStd.Text  = cInStd;
                txtQty.Text      = Qty.ToString();

                this.Text = "制造单子件 " + MoCode;

                string selectSQL = null;

                OleDbConnection myConn = new OleDbConnection(iLoginEx.ConnString());

                if (myConn.State == System.Data.ConnectionState.Open)
                {
                    myConn.Close();
                }
                myConn.Open();

                selectSQL  = "select a.SortSeq as '行号',a.OpSeq as '工序行号',rt.Description as '工序说明',a.InvCode as '物料编码', i.cInvName as '物料名称',i.cInvStd as '规格型号',  \r\n";
                selectSQL += "  a.BaseQtyN/case when isnull(a.BaseQtyD,1)=0 then 1 else isnull(a.BaseQtyD,1) end  as '单位用量',a.Qty as '用量',a.IssQty as '已领数',a.TransQty as '已调拨数',  \r\n";
                selectSQL += "  case  when a.WIPType=1 then '入库倒冲' else case when a.WIPType=2 then '工序倒冲' else case when a.WIPType=3 then '领用' else case when a.WIPType=4 then '直接供应'  end end end end as '供应类型',  \r\n";
                selectSQL += "  isnull(a.WhCode,'')+'-'+isnull(w.cWhName,'') as '仓库', isnull(a.Remark,a.Define29) as '备注'   \r\n";
                selectSQL += "  from mom_moallocate a (nolock)  \r\n";
                selectSQL += "  left join inventory i (nolock) on a.invcode=i.cinvcode  \r\n";
                selectSQL += "  left join Warehouse w (nolock) on a.WhCode=w.cWhCode   \r\n";
                selectSQL += "  left join mom_orderdetail m (nolock)on a.modid=m.modid  \r\n";
                selectSQL += "  left join (  \r\n";
                selectSQL += " select OpSeq ,RoutingId=MoDId,Description from sfc_moroutingdetail   \r\n";
                selectSQL += "  union   \r\n";
                selectSQL += "  select OpSeq,RoutingId=PRoutingId,Description from sfc_proutingdetail   \r\n";
                selectSQL += " union   \r\n";
                selectSQL += "  select OpSeq,RoutingId=EcnRoutingId,Description from ecn_proutingdetail   \r\n";
                selectSQL += " ) rt on a.OpSeq=rt.OpSeq and m.RoutingId=rt.RoutingId  \r\n";
                selectSQL += "  where a.modid=" + MoDid.ToString() + "  \r\n";

                OleDbCommand myCommand = new OleDbCommand(selectSQL, myConn);
                this.dataGridView1.AutoGenerateColumns = true;
                //设置数据源


                DataSet          ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(myCommand);
                da.Fill(ds);
                this.dataGridView1.DataSource = ds.Tables[0];//数据源


                //标准居中
                this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

                //设置自动换行

                this.dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;

                //设置自动调整高度

                this.dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    dataGridView1.Columns[i].ReadOnly = true;
                    if (i >= 7 && i <= 9)
                    {
                        dataGridView1.Columns[i].DefaultCellStyle.Format    = "#,###0.00";
                        dataGridView1.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    }
                }

                dataGridView1.Columns[6].DefaultCellStyle.Format    = "#,###0.0000";
                dataGridView1.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;


                if (myConn.State == System.Data.ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            catch (Exception ex)
            {
                frmMessege frmmsg = new frmMessege(ex.ToString(), "LoadData()");
                frmmsg.ShowDialog(this);
            }
        }