mainform1.cs

本系统为资产管理系统

			string Query =  "select assetNo as 资产编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where not exists (select * from employee where employee.employeeNo = asset.employeeNo)";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da  = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"stat");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "stat";
			this.dataGrid1.Focus();
			this.dataGrid1.AllowDrop = true;
			conn.Close();
		}
		private void ButtonClickM1()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where not exists(select * from maintenance where asset.assetNo = maintenance.assetNo);";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand =  new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"noMaintenance");
		    this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember =  "noMaintenance";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickM2()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where exists (select * from asset where asset.assetNo = Maintenance.assetNo);";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"outMaintenance");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "outMaintenance";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickM3()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where not exists (select * from agent where agent.agentNo = maintenance.agentNo);";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"inMaintenance");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "inMaintenance";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickV1()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where purchaseprice < currentvalue; ";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"high");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "high";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickV2()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where purchaseprice > currentvalue; ";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"low");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "low";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickV3()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where not exists (select * from valuation where asset.assetNo = valuation.assetNo); ";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"novaluation");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "novaluation";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickAg1()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query2 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'main') DROP VIEW main";
			SqlCommand cm = new SqlCommand(Query2,conn);
			cm.ExecuteNonQuery(); 
			string Query1 = "create view main as select distinct maintenance.agentNo,count(*) 'maintenancecount' from maintenance,agent where maintenance.agentNo = agent.agentNo group by maintenance.agentNo";
			SqlCommand cmd1 = new SqlCommand(Query1,conn);
			cmd1.ExecuteNonQuery();
			string Query =	"select  distinct agent.agentNo as 维护商号,agentName as 名称,maintenancecount as 维护量,agentaddress as 地址,agenttelNo as 联系电话,agentfaxNo as 传真,agentemail as Email,agentwebsite as 维护商网址 from agent,main where agent.agentNo = main.agentNo";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"novaluation");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "novaluation";
			string Query3 = "drop view main";
			SqlCommand cmd2 = new SqlCommand(Query3,conn);
			cmd2.ExecuteNonQuery();
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickAg2()
		{
			this.panel5.Visible = true;
			SqlConnection conn = new SqlConnection(s.getConnectionString);
			conn.Open();
			string Query = "select agentNo as 维护商编号,agentName as 名称,agentAddress as 地址,agentZipCode as 邮政编码,agentTelNo as 联系电话,agentFaxNo as 传真,agentEmail as Email,agentWebSite as 维护商网址 from agent where not exists (select * from maintenance where agent.agentNo = maintenance.agentNo); ";
			SqlCommand cmd = new SqlCommand(Query,conn);
			SqlDataAdapter da = new SqlDataAdapter();
			da.SelectCommand = new SqlCommand(Query,conn);
			DataSet ds = new DataSet();
			da.Fill(ds,"novaluation");
			this.dataGrid1.DataSource = ds;
			this.dataGrid1.DataMember = "novaluation";
			this.panel6.Focus();
			conn.Close();
		}
		private void ButtonClickAg3()
		{
			this.panel5.Visible = false;
			//querymaintenance qm;
			if (qm != null)
			{
				qm.Close();
			}
				qm = new querymaintenance();
				qm.MdiParent = this;
				qm.Show();
				qm.click1 += new AssetManagement.querymaintenance.ClickHandler(click);
		}
		private void click()
		{
			   string str = "";
				str = qm.getAssetCategory;
			    this.panel5.Visible = true;
			    qm.Close();
				Connection s = new Connection();
				SqlConnection conn = new SqlConnection(s.getConnectionString);
				conn.Open();
				//	SqlCommand cmd = new SqlCommand("search_agent_on_detailedAgentCatagory",conn);
				String sql = "select * from  dbo.search_agent_on_detailedAgentCatagory('" + str +"')" ;
				SqlCommand cmd = new SqlCommand(sql,conn);
				SqlDataAdapter da = new SqlDataAdapter();
				da.SelectCommand = new SqlCommand(sql,conn);
				DataSet ds = new DataSet();
				da.Fill(ds,"novaluation");
				this.dataGrid1.DataSource = ds;
				this.dataGrid1.DataMember = "novaluation";
				qm.Close();
				conn.Close();
		
		}
		private string getSql(string s1,string s2)
		{
			string s = "";
			switch (s1)
			{
				case "员工":
				switch (s2)
				{
					case "姓名":
				   s = "select employeeNo as 员工号,employeeName as 员工姓名,address as 住址,workTelExt as 单位电话,homeTelNum as 家庭电话,empEmail as Email from employee where employeename";
						return s;
					case "员工编号":
						s = "select employeeNo as 员工号,employeeName as 员工姓名,address as 住址,workTelExt as 单位电话,homeTelNum as 家庭电话,empEmail as Email from employee where employeeNo";
						return s;
						
					case "员工住址":
						s = "select employeeNo as 员工号,employeeName as 员工姓名,address as 住址,workTelExt as 单位电话,homeTelNum as 家庭电话,empEmail as Email from employee employee where address";
						return s;
					case "":
						s = "select employeeNo as 员工号,employeeName as 员工姓名,address as 住址,workTelExt as 单位电话,homeTelNum as 家庭电话,empEmail as Email from employee";
						return s;
					default:
						return s;
				}
				case "维护":
				switch (s2)
				{
					case "维护编号":
						s = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where maintenanceNo";
						return s;
						
					case "记录者编号":
                        s = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where employeeNo";
						return s;
						
					case "资产编号":
						s = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where agentNo";
						return s;
						
					case "维护商编号":
						s = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance where assetNo";
						return s;
					case "":
						s = "select maintenanceNo as 维护编号,assetNo as 资产编号,agentNo as 维护商编号,employeeNo as 记录者编号,maintenanceDate as 维护日期,maintenanceDescription as 维护备注,maintenanceCost as 维护费用 from maintenance";
						return s;
					default:
						return s;
				}
				case "维护商":
				switch (s2)
				{
					case "维护商编号":
                        s = "select agentNo as 维护商编号,agentName as 名称,agentAddress as 地址,agentZipCode as 邮政编码,agentTelNo as 联系电话,agentFaxNo as 传真,agentEmail as Email,agentWebSite as 维护商网址 from agent where agentNo";
						return s;
						
					case "所在地址":
						s = "select agentNo as 维护商编号,agentName as 名称,agentAddress as 地址,agentZipCode as 邮政编码,agentTelNo as 联系电话,agentFaxNo as 传真,agentEmail as Email,agentWebSite as 维护商网址 from agent where agentAddress";
						return s;
					case "维护商名称":
						s = "select agentNo as 维护商编号,agentName as 名称,agentAddress as 地址,agentZipCode as 邮政编码,agentTelNo as 联系电话,agentFaxNo as 传真,agentEmail as Email,agentWebSite as 维护商网址 from agent where agentname";
						return s;
					case "":
						s = "select agentNo as 维护商编号,agentName as 名称,agentAddress as 地址,agentZipCode as 邮政编码,agentTelNo as 联系电话,agentFaxNo as 传真,agentEmail as Email,agentWebSite as 维护商网址 from agent";
						return s;
					default:
						return s;
						
				}
					
				case "资产":
				switch (s2)
				{
					case "资产编号":
						s = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where assetNo";
						return s;
						
					case "资产种类":
						s = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where exists (select * from assetcategory where asset.assetcategoryNo = assetcategory.assetcategoryNO and assetcategorydescription";
						return s;
						
					case "所属员工":
                        s = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where employeeNo";
						return s;
						
					case "序列号":
						s = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset where serialNo";
						return s;
					case "":
						s = "select assetNo as 资产编号,employeeNo as 所属员工编号,assetDescription as 资产备注,serialNo as 序列号,dateAcquired as 购进日期,purchasePrice as 购入价格,currentValue as 当前价值,dateSold as 卖出日期,nextMaintenanceDate as 下次维护时间 from asset";
						return s;
					default:
						return s;
						
				}
				case "估价":
				switch (s2)
				{
					case "估价编号":
						s = "select valuationNo as 估价编号,assetNo as 资产编号,employeeNo as 记录者编号,valuationDate as 估价日期,valuationPrice as 估价价格 from valuation where maintenanceNo";
						return s;
						
					case "资产编号":
						s = "select valuationNo as 估价编号,assetNo as 资产编号,employeeNo as 记录者编号,valuationDate as 估价日期,valuationPrice as 估价价格 from valuation where assetNo";
						return s;
						
					case "估价人编号":
						s = "select valuationNo as 估价编号,assetNo as 资产编号,employeeNo as 记录者编号,valuationDate as 估价日期,valuationPrice as 估价价格 from valuation where employeeNo";
						return s;
					case "":
						s = "select valuationNo as 估价编号,assetNo as 资产编号,employeeNo as 记录者编号,valuationDate as 估价日期,valuationPrice as 估价价格 from valuation";
						return s;
					default:
						return s;
						
				}
				default:
					this.comboBox1.Text = "";
					this.