www.pudn.com > whpower2007-8-24.rar > MatCode.cs, change:2007-05-16,size:9749b


using System; 
using System.Collections.Generic; 
using System.Text; 
using System.Data; 
using System.Data.SqlClient; 
using com.etong.BusinessRule.Zwf; 
using com.etong.SqlDataConnect; 
 
namespace com.etong.BusinessRule.MatCode 
{ 
    public class MatCode 
    { 
        private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);        
        public MatCode() 
        { 
            // 
            // TODO: 在此处添加构造函数逻辑 
            // 
        } 
        /*************************************************************************************************************   
                                             * Module: 物资代码管理 
                                             * Desc: 添加、更新、删除、查询记录 
                                             * Author:徐秀梅 
                                             * Date: 2007-03 
        **************************************************************************************************************/ 
        /// <summary> 
        /// 添加物资代码记录 
        /// </summary> 
        /// <returns>返回一bool类型值</returns> 
        public bool MatCodeAdd(string matcode,string matname,string matdecs) 
        { 
            QueryParameterCollection Params = new QueryParameterCollection(); 
            Params.Clear();           
            Params.Add("@matcode", matcode); 
            Params.Add("@matname", matname); 
            Params.Add("@matdecs", matdecs); 
            string sql = "insert into tb_sys_MatCode([MatCode],[MatName],[MatDecs]) values (@matcode,@matname,@matdecs)"; 
            MSDA.Open(); 
 
            NewObj obj=new NewObj(); 
            bool returnvalue = obj.insert(sql, Params, CommandType.Text);            
 
            MSDA.Close(); 
            return returnvalue;  
        } 
        /// <summary> 
        ///更新物资代码记录 
        /// </summary> 
        /// <returns>返回一bool类型值</returns> 
        public bool MatCodeUpdate(int id, string matcode, string matname, string matdecs) 
        { 
            QueryParameterCollection Params = new QueryParameterCollection(); 
            Params.Clear(); 
            Params.Add("@id", id); 
            Params.Add("@matcode", matcode); 
            Params.Add("@matname", matname); 
            Params.Add("@matdecs", matdecs); 
            string sql = "update tb_sys_MatCode set [MatCode]=@matcode,[MatName]=@matname,[MatDecs]=@matdecs where [ID]=@id"; 
            MSDA.Open(); 
 
            NewObj obj = new NewObj(); 
            bool returnvalue = obj.Update(sql, Params, CommandType.Text); 
 
            MSDA.Close(); 
            return returnvalue; 
        } 
        /// <summary> 
        ///删除物资代码记录 
        /// </summary>         
        public void MatCodeDelete(int id) 
        { 
            string sql = "delete from tb_sys_MatCode where [ID]='" + id + "'"; 
            MSDA.Open(); 
            MSDA.ExecuteNonQuery(CommandType.Text, sql, null); 
            MSDA.Close(); 
        } 
        /// <summary> 
        /// 查找指定或全部物资代码记录 
        /// </summary> 
        /// <param name="SearchTxt">查找内容</param> 
        /// <param name="i">查找关键字,例:1:物资代码;2:物资代码名称;3:物资代码ID;非1,2,3且SearchTxt为空:查找全部</param> 
        public DataView MatCodeSearch(string SearchTxt, string i) 
        { 
            string sql= "select [ID],[MatCode],[MatName],[MatDecs] from tb_sys_MatCode"; 
            if (i == "1" && SearchTxt != "") 
            { 
               sql = sql + "  where [MatCode]='" + SearchTxt + "'"; 
            } 
            if (i == "2" && SearchTxt != "") 
            { 
               sql = sql+ " where [MatName]='" + SearchTxt + "'"; 
            } 
            if (i == "3" && SearchTxt != "") 
            { 
                sql = sql + " where [ID]='" + SearchTxt + "'"; 
            }   
            DataSet ds = new DataSet(); 
            MSDA.Open(); 
            NewObj newobject = new NewObj(); 
            
            DataView dv = newobject.Search(sql, null, CommandType.Text).Tables[0].DefaultView; 
 
            
            MSDA.Close(); 
            return dv;  
        } 
        /// <summary> 
        /// 判断物资代码是否符合要求 
        /// </summary> 
        /// <param name="SearchTxt">物资代码</param>   
        ///  /// <returns>返回一int类型值,返回值-2:物资代码没校验码;-1:物资代码错误;0:物资代码校验码为0;其他:物资代码啊校验码为返回值</returns> 
        public bool MatCodeCheck(string matcode) 
        { 
            bool flag = false; 
            char[] str=null; 
            int len = matcode.Length; 
            for (int i = 0; i < len; i++) 
            { 
                str = matcode.ToCharArray(); 
            } 
            if (len<=5|| len == 8 || len == 10) 
            { 
              flag = true; 
            } 
            if (len == 14) 
            { 
                //判断校验位是否正确 
                int checkid=0; 
                for (int i =12; i >= 0; i--,i--) 
                {                   
                    checkid +=int.Parse( str[i].ToString());                      
                } 
                checkid = checkid * 3; 
                for (int i = 11; i >= 0; i--,i--) 
                { 
                    checkid +=int.Parse( str[i].ToString());  
                } 
                char[] checkstr = null; 
                int len2 = checkid.ToString().Length; 
                for (int i = 0; i < len2; i++) 
                { 
                    checkstr =checkid.ToString().ToCharArray(); 
                }                 
                if (Convert.ToInt32( checkstr[len2-1].ToString()) != 0) 
                { 
                    checkid = 10 - Convert.ToInt32(checkstr[len2-1].ToString()); 
                } 
                else 
                    checkid = 0; 
                if (int.Parse(str[len-1].ToString()) == checkid) 
                    flag = true; 
                 
            } 
            return flag; 
  
        } 
    } 
 public class InExcel 
    { 
        private MSSqlDataAccess MSDA = new MSSqlDataAccess(0); 
        public InExcel() 
        { 
            // 
            // TODO: 在此处添加构造函数逻辑 
            // 
        } 
        /*************************************************************************************************************   
                                             * Module: Excel表格导入 
                                             * Desc: 导入 
                                             * Author:徐秀梅 
                                             * Date: 2007-04 
        **************************************************************************************************************/ 
        /// <summary> 
        /// 导入Excel表格原始数据 
        /// </summary> 
        /// <returns>返回一bool类型值</returns> 
        public string inexcel(string sFile) 
        { 
 
            string message = ""; 
            //取出表格中数据放在数据集中 
            MSDA.Open();             
            DataSet ds = new DataSet(); 
            DataTable dt = new DataTable();             
            string sql = "select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" + sFile + ";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$"; 
            ds = MSDA.ExecuteDataset(CommandType.Text, sql, null, ds, "excel"); 
            MSDA.Close(); 
            dt = ds.Tables["excel"]; 
            if (dt!=null||dt.Rows.Count > 0) 
            { 
                //Excel表格存在数据就删除tb_sys_MatCode表的数据 
                MSDA.Open(); 
                string delsql = "delete from tb_sys_MatCode"; 
                MSDA.ExecuteNonQuery(CommandType.Text, delsql, null); 
                MSDA.Close(); 
                //向tb_sys_MatCode表写入Excel表格导入的数据 
                for (int i =0; i <dt.Rows.Count; i++) 
                { 
                    MatCode mymatcode = new MatCode(); 
                    if (dt.Rows[i][0].ToString() == "") 
                    { 
                        message = "第" + (i+1) + "行物资代码不能为空!"; 
                        return message; 
                    }                     
                    string matcode = dt.Rows[i][0].ToString(); 
                    DataView dv = mymatcode.MatCodeSearch(matcode, "1"); 
                    if (dv.Count > 0) 
                    { 
                        message = "第" + (i + 1) + "行物资代码已经存在!"; 
                        return message; 
                    } 
                    if (mymatcode.MatCodeCheck(matcode)==false) 
                    { 
                        message = "第" + (i + 1) + "行物资代码格式错误!"; 
                        return message; 
                    } 
                    string matname = dt.Rows[i][2].ToString(); 
                    string matdecs = dt.Rows[i][1].ToString(); 
                    string insertsql = "insert into tb_sys_MatCode([MatCode],[MatName],[MatDecs]) values('"+matcode+"','"+matname+"','"+matdecs+"')"; 
                    MSDA.Open(); 
                    SqlTransaction tran = MSDA.BeginTransaction(); 
                    try 
                    { 
                        MSDA.ExecuteNonQuery(CommandType.Text,insertsql,null); 
                        tran.Commit();                         
                    } 
                    catch 
                    { 
                        tran.Rollback(); 
                        throw;                        
                    } 
                    finally 
                    { 
                        MSDA.Close(); 
                    } 
                } 
            } 
            return message; 
        } 
    } 
}