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); } }
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); } }
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); } }
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); } }