userinfo.cs

来自「该服务平台解决了计算机网络与移动网络之间信息交换问题」· CS 代码 · 共 283 行 · 第 1/2 页

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

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

        public DataTable GetDeptName(string companyName, string branchName)
        {
            string strSql = string.Format("SELECT DISTINCT Dept FROM UserInfo WHERE CompanyName = '{0}' AND Branch = '{1}'", 
                companyName,branchName);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        public DataTable GetMobile(int ID)
        {
            string strSQL = string.Format("SELECT Mobile FROM UserInfo WHERE UserID = '{0}'", ID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
        public int GetUserID(string mobile)
        {
            string strSql = string.Format("SELECT UserID FROM UserInfo WHERE Mobile = '{0}'", mobile);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            int userID = int.Parse(ds.Tables[0].Rows[0]["UserID"].ToString());
            return userID;
        }
        /// <summary>
        /// 统计各区县人数数量
        /// </summary>
        public int CountUser(int countyID)
        {
            object obj;
            String strSql = String.Format("SELECT COUNT(UserInfo.UserID) AS UserCount FROM UserInfo INNER JOIN UserCorporation ON UserInfo.UserID = UserCorporation.UserID "+
                "WHERE (UserInfo.IsValid <> 0) AND (UserCorporation.CorporationID = {0})", countyID);
            try
            {
                obj = db.ExecuteScalar(CommandType.Text, strSql);
                return int.Parse(obj.ToString());
            }
            catch
            { return 0; }
        }
        /// <summary>
        /// 统计各组织人数
        /// </summary>
        /// <param name="corporationID"></param>
        /// <returns></returns>
        public int CountUserByCorporationID(int corporationID)
        {
            object obj;
            String strSQL = string.Format("SELECT COUNT(UserID) FROM UserInfo WHERE (IsValid <> 0) AND (CorporationID = {0})", corporationID);
            try
            {
                obj = db.ExecuteScalar(CommandType.Text, strSQL);
                return int.Parse(obj.ToString());
            }
            catch
            {
                return 0;
            }
        }

        public DataTable GetUserInfoToSend()
        {
            String strSql = "SELECT UserInfo.UserID, UserInfo.AreaID, UserCorporation.CorporationID, " +
                "UserInfo.Mobile, UserInfo.TradeID FROM UserInfo INNER JOIN UserCorporation ON UserInfo.UserID " +
                "= UserCorporation.UserID WHERE (UserInfo.IsValid <> 0)";
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        public void MoveUserLocation(int userID, int localtionID)
        {
            String strSql = String.Format("UPDATE UserInfo SET UserLocation = {0} WHERE (UserID = {1})", localtionID, userID);
            db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        public int ImportUserInfoData(Common.Model.UserInfo userInfoData)
        {
            Database db = DatabaseFactory.CreateDatabase("Connection String");
            DbCommand insertCommand = db.GetStoredProcCommand("AddUserInfo");
            db.AddInParameter(insertCommand, "@AreaID", DbType.Int32, "AreaID", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@CountyID", DbType.Int32, "CountyID", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Sex", DbType.Int32, "Sex", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Duty", DbType.String, "Duty", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@School", DbType.String, "School", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@SchoolAge", DbType.String, "SchoolAge", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Speciality", DbType.String, "Speciality", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Birthday", DbType.String, "Birthday", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@HomeAddress", DbType.String, "HomeAddress", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Mobile", DbType.String, "Mobile", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@UserDefineID", DbType.String, "UserDefineID", DataRowVersion.Current);


            DbCommand updateCommand = db.GetStoredProcCommand("UpdateUserInfo");
            db.AddInParameter(updateCommand, "@AreaID", DbType.Int32, "AreaID", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@CountyID", DbType.Int32, "CountyID", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Sex", DbType.Int32, "Sex", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Duty", DbType.String, "Duty", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@School", DbType.String, "School", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@SchoolAge", DbType.String, "SchoolAge", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Speciality", DbType.String, "Speciality", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Birthday", DbType.String, "Birthday", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@HomeAddress", DbType.String, "HomeAddress", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Mobile", DbType.String, "Mobile", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@UserDefineID", DbType.String, "UserDefineID", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@UserID", DbType.Int32, "UserID", DataRowVersion.Current);

            DbCommand deleteCommand = db.GetStoredProcCommand("DeleteUserInfo");
            db.AddInParameter(deleteCommand, "@UserID", DbType.Int32, "UserID", DataRowVersion.Current);
            try
            {
                return db.UpdateDataSet(userInfoData, Common.Model.UserInfo.TABLE_USER_INFO,
                    insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);
            }
            catch
            { return 0; }
        }
    }
}