excesql.cs

股票操作员每日一记 源程序 股票操作员每日一记 源程序

			{											
				myCmd.Connection = myCn;				
				myCmd.Transaction = myTrans;

				foreach(string str in strSQLs)
				{
					myCmd.CommandText = str;
					myCmd.ExecuteNonQuery();
				}
				myTrans.Commit();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException e)
			{			
				myTrans.Rollback();
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}



		//定义变量储存文本框的输入，出于安全的目的对所要查询的数据的值其进行转换
		public static string inpuText(string inpuText)
		{
		string changeText="";
　　	inpuText = inpuText.Replace("&", "&");
　　	inpuText = inpuText.Replace("<", "");
　　	inpuText = inpuText.Replace(">", "");
　　	inpuText = inpuText.Replace("'", "’");
　　	inpuText = inpuText.Replace("chr(13)", "<br>");
　　	inpuText = inpuText.Replace("chr(10)", "<br>");
		changeText=inpuText;
		return changeText;
		}

		//添加时去掉回车按钮的值
		public static string CHAREncode_2(string fString)
		{
			 
			fString =fString.Replace("chr(10)","");
			fString =fString.Replace("chr(13)","");			 
			return fString;
		}
		 

//		 在当提取数据库的时候，某些特殊的东西需要将它们显示出来
//		function HTMLEncode(fString)
//
//			fString = replace(fString, ">", "&gt;")
//		fString = replace(fString, "<", "&lt;")
//		fString = Replace(fString, CHR(32), "&nbsp;")
//		fString = Replace(fString, CHR(13), "")
//		fString = Replace(fString, CHR(10) & CHR(10), "</P><P>")
//		fString = Replace(fString, CHR(10), "<BR>")
//		HTMLEncode = fString
//
//						 end function
//								 %>

 

		/// <summary>
		/// OleDbDataReader  返回dr对象
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		public  static OleDbDataReader ExecuteReader(string strSQLs)
		{
		 
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand();			
			myCn.Open();	 
			try
			{											
				myCmd.Connection = myCn;				
			    myCmd.CommandText = strSQLs;				 
			    return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(System.Data.SqlClient.SqlException e)
			{			
				 
				throw new Exception(e.Message);
			}
		 
		}
//
//		<asp:ListItem Value="1">总经理/财务/管理人员</asp:ListItem>
//		<asp:ListItem Value="2">客服人员</asp:ListItem>
//		<asp:ListItem Value="3">业务人员</asp:ListItem>	
//		<asp:ListItem Value="4">部门经理</asp:ListItem>
//		<asp:ListItem Value="5">录入人员</asp:ListItem>
//		<asp:ListItem Value="6">项目主管</asp:ListItem>
//		<asp:ListItem Value="7">电子商务部</asp:ListItem>
		/// <summary>
		/// 得到Handler_I
		/// </summary>
		/// <param name="UserName"></param>
		/// <param name="EmpCode"></param>
		/// <returns></returns>
		public static string getHandler_ID(String op,String EmpCode,string dptcode)
		{
			StringBuilder commandText = new StringBuilder();			
		    OleDbDataReader dr=null;
			int j=Convert.ToInt32(op);
			if(j<3)
			{
				 dr=ExecuteReader("select Handler_ID from base_emp where isdel=0");
				while(dr.Read())
				{				 
				    commandText.Append("'"+dr["Handler_ID"]+"',");	
				 

				}
				dr.Close();
	 		}
			else if("3".Equals(op)||"5".Equals(op)||"7".Equals(op))
			{
				commandText.Append("'"+EmpCode+"',");	
			 
			}
			
			else if("4".Equals(op))
			{
				string code1=dptcode+","+GetFullCode_1(dptcode)+"000";
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE isdel=0 and DPTCODE in ("+code1+") ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			}
			else if("6".Equals(op))
			{
				string code2=dptcode+","+GetFullCode_1(dptcode)+"000";
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE isdel=0 and DPTCODE in ("+code2+") ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			} 
			
			else 
			{
				commandText.Append("'"+EmpCode+"',");	
			 
			}
			commandText.Append("'999999999'");
			
			return commandText.ToString();
		}

		//得到部门id
		private static string GetFullCode_1(string dptcode_p)
		{
			string fullname = "";

			if(dptcode_p =="ZZZ")
			{
				return "";
			}

			DataTable dpttable =ExceSql.GetBySqlToDS("SELECT dptcode FROM BASE_DEPT where  dptcode_p='"+dptcode_p+"' or dptcode_p='"+dptcode_p+"' ").Tables[0]; //LoadDeptTable();
			foreach(DataRow dr in dpttable.Rows)
			{
				 
				fullname = GetFullCode_1(dr["DPTCODE"].ToString()) + dr["dptcode"].ToString() + "," + fullname;
				 
			}

			return fullname;
		}


		//		<asp:ListItem Value="1">总经理/财务/管理人员</asp:ListItem>
		//		<asp:ListItem Value="2">客服人员</asp:ListItem>
		//		<asp:ListItem Value="3">业务人员</asp:ListItem>	
		//		<asp:ListItem Value="4">部门经理</asp:ListItem>
		//		<asp:ListItem Value="5">录入人员</asp:ListItem>
		//		<asp:ListItem Value="6">项目主管</asp:ListItem>
		//		<asp:ListItem Value="7">电子商务部</asp:ListItem>
		/// <summary>
		/// 分配客户时用来得到Handler_I
		/// </summary>
		/// <param name="UserName"></param>
		/// <param name="EmpCode"></param>
		/// <returns></returns>
		public static string getHandler_ID_2(String op,String EmpCode,string dptcode)
		{
			string code1=dptcode+","+GetFullCode_1(dptcode)+"000";
			StringBuilder commandText = new StringBuilder();			
			OleDbDataReader dr=null;
			int j=Convert.ToInt32(op);
			if(j<3)
			{
				dr=ExecuteReader("select Handler_ID from base_emp where isdel=0 order by  doop ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	
				 

				}
				dr.Close();
			}
			else if("3".Equals(op)||"5".Equals(op))
			{
				 
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE doop in(0,1,2,4,6) and isdel=0 and DPTCODE in ("+code1+") ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			 
			}
			
			else if("4".Equals(op))
			{
				 
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE isdel=0 and DPTCODE in ("+code1+") ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			}

				//电子商务
			else if("7".Equals(op))
			{
				 
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE doop in(0,1,4,6) and isdel=0   ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			 
			}

			else if("6".Equals(op))
			{
				 
				dr=ExecuteReader("SELECT Handler_ID FROM BASE_EMP WHERE isdel=0 and DPTCODE in ("+code1+") ");
				while(dr.Read())
				{				 
					commandText.Append("'"+dr["Handler_ID"]+"',");	 
				}
				dr.Close();
			} 
			
			else 
			{
				commandText.Append("'"+EmpCode+"',");	
			 
			}
			commandText.Append("'999999999'");
			
			return commandText.ToString();
		}









        public static String null2String(String str,string re_value)
        {
            return str == null ? re_value : str.Trim();
        }
		public static String null2String(String str) 
		{
			return str == null ? "":str.Trim();
		}
		public static String null2ObjString(String str) 
		{
			return str == null ? "":str.ToString().Trim();
		}
		//String.Format(    "yyyy-MM-dd    ",yourDateTime); 


		public static String null2DateStr(String str) 
		{
			return str == null ? "":String.Format("yyyy-MM-dd",str.ToString().Trim());
		}

		public static int null2Int(String str) 
		{
			if("".Equals(str))
			{
				return 0;
			}
			else if(str == null)
			{
				return 0;
			} 
			else
			{
				return Convert.ToInt32(str);
					
			} 
		}

		public static Decimal null2De(String str) 
		{
			if("".Equals(str))
			{
				return 0;
			}
			else if(str == null)
			{
				return 0;
			} 
			else
			{
				
				return Convert.ToDecimal(str);

			}

		}
 
public static String isStringTrim(String string1, String ret) {
		String isNull = "";
		if (string1 != null) {
			string1 = string1.Trim();
		}
		if (null == string1 || "".Equals(string1) || "null".Equals(string1)) {
			isNull = ret;
		} else {
			isNull = string1;
		}
		return isNull;
	}

    ///**
    // * ?ж????????????
    // * 
    // * @param string
    // * @return
    // */
    //public static int isIntTrim(String string1) {
    //    int isNull = 0;
    //    // if (string != null) {
    //    // string = string.trim();
    //    // }
    //    if (null == string1 || "".Equals(string1) || "null".Equals(string1))
    //    {
    //        isNull = 0;
    //    } else {
    //        isNull = Integer.parseInt(string1);
    //    }
    //    return isNull;
    //}

    ///**
    // * 
    // * 
    // * @param string
    // * @return
    // */
    //public static double isDoubleTrim(String string1) {
    //    double isNull = 0;
    //    if (string1 != null) {
    //        string1 = string1.trim();
    //    }
    //    if (null == string1 || "".equals(string1) || "null".equals(string1)) {
    //        isNull = 0;
    //    } else {
    //        isNull = Double.parseDouble(string1);
    //    }
    //    return isNull;
    //}

    ///**
    // * 
    // * 
    // * @param string
    // * @return
    // */
    //public static bool isStringNull(String string1) {
    //    boolean isNull = false;
    //    if (string1 != null) {
    //        string1 = string1.trim();
    //    }
    //    if (null == string1 || "".equals(string1) || "null".equals(string1))
    //        isNull = true;
    //    return isNull;
    //}

		public static Int64 GETMAXCODE( ref String strPRMDPTCODE)
		{
			
			StringBuilder commandText = new StringBuilder("select max(acccode) as code from BASE_ACC_TYPE ");
		 
			try
			{
				OleDbConnection Conn = new OleDbConnection(strConn);
		 
				OleDbCommand DSCmd = new OleDbCommand(commandText.ToString(),Conn);
				Conn.Open();
			 
				Int32 status = 0;
				
				if(status ==  0 ) 
				{				
					OleDbDataReader dataReader = DSCmd.ExecuteReader();
					while (dataReader.Read())
					{
						 
						if (dataReader["code"] != DBNull.Value)
							 
							strPRMDPTCODE = dataReader["code"].ToString();
						 
					}
					
					dataReader.Close();
					if(status ==  0 )
					{
						
					}
					else
					{	
						
					}
				}		
				Conn.Close();
				return 1;
			}
			catch(Exception ex)
			{
			
				throw new Exception(ex.Message);				
			}
		}
 

		public static String  GetMaxId(String tablename)
		{	 
		    String GetMaxId="0";
			OleDbConnection Conn = new OleDbConnection(strConn);		 
			OleDbCommand DSCmd = new OleDbCommand("SELECT MAX(id+1) as sequ FROM "+tablename,Conn);
			Conn.Open();
			try
			{
		 
	 		 				
					OleDbDataReader dataReader = DSCmd.ExecuteReader();
					if (dataReader.Read())
					{
						 
						if (dataReader["sequ"] != DBNull.Value)
							 
					   GetMaxId =dataReader["sequ"].ToString();
	 
					}
					
				dataReader.Close();			 
				Conn.Close();
				return GetMaxId;
			}
			catch(Exception ex)
			{
				Conn.Close();
			   throw new Exception(ex.Message);				
			}
		}