userinfo.cs

该服务平台解决了计算机网络与移动网络之间信息交换问题

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class UserInfo
    {
        private Database db = null;
        public UserInfo()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }
        /// <summary>
        /// 得到UserInfo表中单一用户信息
        /// </summary>
        /// <param name="systemID"></param>
        /// <returns></returns>
        public DataTable GetSingleUserInfo(int systemID)
        {

            string strSql = string.Format("SELECT UserInfo.UserID, UserInfo.Name, UserInfo.Sex, UserInfo.Birthday, UserInfo.Jiguan, UserInfo.Beizhu, UserInfo.Mobile, UserInfo.IDcard," +
                "UserInfo.HomeAddress, NowAddress, UserInfo.UserDefineID, UserInfo.TradeID, Area.AreaName, UserInfo.AreaID, UserInfo.Race, UserInfo.Jointime, UserInfo.Hukou," +
                "Corporation.CorporationName, UserInfo.Duty, UserInfo.School, UserInfo.SchoolAge, UserInfo.Speciality, " +
                "Corporation.CorporationID, UserInfo.UserFunction FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN " +
                "Corporation ON UserInfo.CountyID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0) AND (UserInfo.UserID = {0})", systemID);

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }



        public DataTable GetSingleCustomerInfo(int systemID)
        {

            string strSql = string.Format("SELECT CustomerInfo.CustomerID, CustomerInfo.Name, CustomerInfo.Sex, CustomerInfo.Birthday, CustomerInfo.Beizhu, CustomerInfo.Mobile, CustomerInfo.IDcard," +
                "CustomerInfo.HomeAddress, CustomerInfo.Unit, CustomerInfo.CustomerDefineID, CustomerInfo.TradeID, Type.TypeName, CustomerInfo.TypeID, CustomerInfo.JoinDate, CustomerInfo.UnitAddress," +
                "GroupInfo.GroupName, CustomerInfo.Duty,  " +
                "GroupInfo.GroupID, CustomerInfo.CustomerFunction FROM CustomerInfo INNER JOIN Type ON CustomerInfo.TypeID = Type.TypeID INNER JOIN " +
                "GroupInfo ON CustomerInfo.GroupID = GroupInfo.GroupID WHERE (CustomerInfo.IsValid <> 0) AND (CustomerInfo.CustomerID = {0})", systemID);

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="AreaID">地市</param>
        /// <param name="countyID"></param>
        /// <param name="villageID"></param>
        /// <param name="userDefineID">用户自定义编号</param>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="homeAddress"></param>
        /// <param name="mobile"></param>
        /// <param name="tradeID"></param>
        /// <param name="userFuncation">用户类型标志</param>
        /// <returns></returns>
        public int AddUserInfo(int AreaID, int countyID, int villageID, string userDefineID, string name, int sex, string birthday, string race, string jiguan, string hukou,
            string homeAddress, string nowaddress, string idcard, string jointime, string mobile, int tradeID, int userFuncation, string duty, string school, string schoolAge,
            string speciality, string beizhu)
        {
            string strSQL = string.Format("INSERT INTO UserInfo (AreaID, CountyID, VillageID, UserDefineID, Name, Sex, Birthday, Race, Jiguan, Hukou, HomeAddress, NowAddress, IDcard, Jointime," +
                "Mobile, TradeID, UserFunction, Duty, School, SchoolAge, Speciality, Beizhu) VALUES " +
                "({0},{1},{2},'{3}','{4}',{5},'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16},'{17}','{18}','{19}','{20}','{21}')", AreaID, countyID, villageID, userDefineID,
                name, sex, birthday, race, jiguan, hukou, homeAddress, nowaddress, idcard, jointime, mobile, tradeID, userFuncation, duty, school, schoolAge, speciality, beizhu);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }



        public int AddCustomerInfo(int typeID, int groupID, string customeDefineID, string name, int sex, string birthday, string office, string OfficeAdd,
           string nowaddress, string idcard, string jointime, string mobile, int tradeID, int userFuncation, string duty, string beizhu)
        {
            string strSQL = string.Format("INSERT INTO CustomerInfo (TypeID, GroupID, CustomerDefineID, Name, Sex, Birthday, Unit, UnitAddress, HomeAddress, IDcard, JoinDate," +
                "Mobile, TradeID, CustomerFunction, Duty, Beizhu) VALUES " +
                "({0},{1},'{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},{13},'{14}','{15}')", typeID, groupID, customeDefineID,
                name, sex, birthday, office, OfficeAdd, nowaddress, idcard, jointime, mobile, tradeID, userFuncation, duty, beizhu);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }




        public int UpdateUserInfo(int areaID, int countyID, int VillageID, string defineID, string name, int sex, string birthday, string homeAddress,
            string mobile, int trade, int userFuncation, int userID, string school, string schoolAge, string Duty, string speciality, string card, string jiguan, string race, string hukou, string nowaddress, string jointime, string beizhu)
        {
            string strSql = string.Format("UPDATE UserInfo SET AreaID ={0}, CountyID ={1}, VillageID ={2}, UserDefineID ='{3}', Name ='{4}', Sex =  {5}, " +
                "Birthday ='{6}', HomeAddress ='{7}', Mobile ='{8}', TradeID ={9}, UserFunction ={10}, Duty ='{11}', School ='{12}', SchoolAge ='{13}', Speciality = '{14}',IDcard ='{15}',Jiguan='{16}',Race= '{17}',Hukou='{18}',NowAddress='{19}',Jointime='{20}',Beizhu='{21}' " +
                "WHERE UserID = {22}", areaID, countyID, VillageID, defineID, name, sex, birthday, homeAddress, mobile,
                trade, userFuncation, Duty, school, schoolAge, speciality, card, jiguan, race, hukou, nowaddress, jointime, beizhu, userID);

            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }


        public int UpdateCutomerInfo(int areaID, int countyID, string defineID, string name, int sex, string birthday, string homeAddress,
           string unit, string mobile, int trade, int userFuncation, int userID, string Duty, string card, string nowaddress, string jointime, string beizhu)
        {
            string strSql = string.Format("UPDATE CustomerInfo SET TypeID ={0}, GroupID ={1}, CustomerDefineID ='{2}', Name ='{3}', Sex ={4}, " +
                "Birthday ='{5}', HomeAddress ='{6}', Unit ='{7}', Mobile ='{8}', TradeID ={9}, CustomerFunction ={10}, Duty ='{11}',IDcard ='{12}',UnitAddress='{13}',JoinDate='{14}',Beizhu='{15}' " +
                "WHERE CustomerID = {16}", areaID, countyID, defineID, name, sex, birthday, homeAddress, unit, mobile,
                trade, userFuncation, Duty, card, nowaddress, jointime, beizhu, userID);

            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }



        public DataTable GetUserInfo(int userTypeID, out DataRow[] rows)
        {
            string queryStr = string.Format("UserTypeID = {0}", userTypeID);
            UserTypeKey userTypeKeyAccess = new UserTypeKey();
            TableDefine.UserTypeKeyDefine userTypeDataSet = userTypeKeyAccess.GetMostUserTypeKey();
            rows = userTypeDataSet.Tables["UserTypeKey"].Select(queryStr);

            DbCommand command = db.GetStoredProcCommand("GetUserInfoByUserTypeID");
            db.AddInParameter(command, "@UserTypeID", DbType.Int32, userTypeID);
            DataSet ds = db.ExecuteDataSet(command);
            return ds.Tables[0];
        }

        public DataTable GetUserInfo()
        {

            //string strSql = "SELECT UserInfo.UserID, UserInfo.Name, CASE WHEN (UserInfo.Sex) = 0 THEN '男' ELSE '女' END AS Sex, UserInfo.Duty, UserInfo.IsValid, Area.AreaName, " +
            //    "Corporation.CorporationName, UserInfo.Mobile,UserInfo.UserLocation FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN " +
            string strSql = "SELECT UserInfo.UserID, UserInfo.Name, CASE WHEN (UserInfo.Sex) = 0 THEN '男' WHEN (UserInfo.Sex) = 1 THEN '女' " +
                "ELSE '' END AS Sex, UserInfo.Duty, UserInfo.IsValid,UserInfo.Birthday,Area.AreaName, Corporation.CorporationName, UserInfo.Mobile, " +
                "UserInfo.UserLocation FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN Corporation ON " +
                "UserInfo.CountyID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0 and Area.IsValid <> 0 and Corporation.IsValid <> 0)";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }


        public DataTable GetCustomerInfo()
        {

            //string strSql = "SELECT UserInfo.UserID, UserInfo.Name, CASE WHEN (UserInfo.Sex) = 0 THEN '男' ELSE '女' END AS Sex, UserInfo.Duty, UserInfo.IsValid, Area.AreaName, " +
            //    "Corporation.CorporationName, UserInfo.Mobile,UserInfo.UserLocation FROM UserInfo INNER JOIN Area ON UserInfo.AreaID = Area.AreaID INNER JOIN " +
            string strSql = "SELECT CustomerInfo.CustomerID, CustomerInfo.Name, CASE WHEN (CustomerInfo.Sex) = 0 THEN '男' WHEN (CustomerInfo.Sex) = 1 THEN '女' " +
                "ELSE '' END AS Sex, CustomerInfo.Duty, CustomerInfo.IsValid,CustomerInfo.Birthday,Type.TypeName, GroupInfo.GroupName, CustomerInfo.Mobile, " +
                "CustomerInfo.Customerlocation FROM CustomerInfo INNER JOIN Type ON CustomerInfo.TypeID = Type.TypeID INNER JOIN GroupInfo ON " +
                "CustomerInfo.GroupID = GroupInfo.GroupID WHERE (CustomerInfo.IsValid <> 0 and Type.IsValid <> 0 and GroupInfo.IsValid <> 0)";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }



        public DataTable GetUserInfoByPKID(int pkid)
        {
            DbCommand command = db.GetStoredProcCommand("GetUserInfoByPKID");
            db.AddInParameter(command, "@PK_ID", DbType.Int32, pkid);
            DataSet ds = db.ExecuteDataSet(command);
            return ds.Tables[0];
        }

        public int DeleteUserInfo(int systemID)
        {
            string strSql = string.Format("UPDATE UserInfo SET IsValid = 0 WHERE UserID = {0}", systemID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }



        public int DeleteCustomerInfo(int systemID)
        {
            string strSql = string.Format("UPDATE CustomerInfo SET IsValid = 0 WHERE CustomerID = {0}", systemID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        /// <summary>
        /// 得到所有协会名称
        /// </summary>
        /// <returns></returns>
        public DataTable GetOrganise()
        {
            string strSql = "SELECT DISTINCT Organise FROM UserInfo";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetSchoolName()
        {
            string strSql = "SELECT DISTINCT SchoolName FROM UserInfo";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetGrade(string schoolName)
        {
            string strSql = string.Format("SELECT DISTINCT GradeName FROM UserInfo WHERE SchoolName = '{0}'", schoolName);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetClassName(string schoolName, string gradeName)
        {
            string strSql = string.Format("SELECT DISTINCT ClassName FROM UserInfo WHERE SchoolName = '{0}' AND GradeName = '{1}'",
                schoolName, gradeName);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetCompanyName()
        {
            string strSql = "SELECT DISTINCT CompanyName FROM UserInfo";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }