userconporation.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class UserConporation
    {
        private const string SQL_ADD_CORPORATION = "INSERT INTO UserCorporation (UserID, CorporationID) VALUES ({0}, {1})";
        private const string SQL_VIEW_SAMP_CORPORATION = "SELECT UserCorporation.UserID, Corporation.CorporationID, Corporation.CorporationName " +
            "FROM UserCorporation INNER JOIN Corporation ON UserCorporation.CorporationID = Corporation.CorporationID WHERE " +
            "UserCorporation.UserID = {0} AND UserCorporation.IsValid <> 0";
        private const string SQL_DEL_CORPORATION = "UPDATE UserCorporation SET IsValid = 0 WHERE UserID = {0} AND CorporationID = {1}";
        private const string SQL_DEL_USERCORPORATIONINFO = "UPDATE UserCorporation SET IsValid = 0 WHERE UserID = {0}";
        private const string SQL_DEL_CORPORATIONINFO_BY_ID = "UPDATE Corporation SET IsValid = 0 WHERE CorporationID = {0}";

        private const string SQL_DEL_Groupinfo_BY_ID = "UPDATE GroupInfo SET IsValid = 0 WHERE GroupID = {0}";

        //private const string SQL_DEL_CORPORATIONINFO_BY_ID = "exec dbo.prCorporationDel {0}";

        private const string SQL_INSERT_CORPORATION = "INSERT INTO Corporation  (AreaID, CountyID, VillageID, CorporationName) VALUES ({0},{1},{2},'{3}')";

        private const string SQL_INSERT_GROUP = "INSERT INTO GroupInfo  (TypeID, GroupName) VALUES ({0},'{1}')";
        private const string SQL_UPDATE_CORPORATION = "UPDATE Corporation SET CorporationName ='{0}' WHERE CorporationID = {1}";


        private const string SQL_UPDATE_GROUP = "UPDATE GroupInfo SET GroupName ='{0}' WHERE GroupID = {1}";
       
        
        private const string SQL_SAMP_CORPORATIOM = "SELECT CorporationName from Corporation WHERE CorporationID = '{0}'";

        private const string SQL_SAMP_GROUP = "SELECT GroupName from GroupInfo WHERE GroupID = '{0}'";
       
        
        
        
        
        private Database db;
        public UserConporation()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }
        public DataTable GetMostCorporation()
        {
            string strSQL = "SELECT Corporation.CorporationID, Corporation.CorporationName, Area.AreaName " +
                "FROM Corporation INNER JOIN Area ON Corporation.AreaID = Area.AreaID " +
                "WHERE Corporation.IsValid <> 0";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
        public DataTable GetMostCorporationByAreaID(int areaID)
        {
            if (areaID != 1)
            {
                string strSQL = string.Format("SELECT Corporation.CorporationID, Corporation.CorporationName, Area.AreaName " +
                    "FROM Corporation INNER JOIN Area ON Corporation.AreaID = Area.AreaID " +
                    "WHERE Corporation.IsValid <> 0 AND Corporation.AreaID = {0} ORDER BY Corporation.CorporationID", areaID);
                DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            else
            {
                string strSQL = "SELECT Corporation.CorporationID, Corporation.CorporationName, Area.AreaName " +
                    "FROM Corporation INNER JOIN Area ON Corporation.AreaID = Area.AreaID " +
                    "WHERE Corporation.IsValid <> 0 AND Area.IsValid<>0  ORDER BY Corporation.CorporationID";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
        }


        public DataTable GetMostGroupByTypeID(int typeID)
        {
            if (typeID != 1)
            {
                string strSQL = string.Format("SELECT GroupInfo.GroupID, GroupInfo.GroupName, Type.TypeName " +
                    "FROM GroupInfo INNER JOIN Type ON GroupInfo.TypeID = Type.TypeID " +
                    "WHERE GroupInfo.IsValid <> 0 AND GroupInfo.TypeID = {0} ORDER BY GroupInfo.GroupID", typeID);
                DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            else
            {
                string strSQL = "SELECT GroupInfo.GroupID, GroupInfo.GroupName, Type.TypeName " +
                    "FROM GroupInfo INNER JOIN Type ON GroupInfo.TypeID = Type.TypeID " +
                    "WHERE GroupInfo.IsValid <> 0 AND Type.IsValid<>0  ORDER BY GroupInfo.GroupID";
                DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
        }




        public DataTable GetConporationInfo()
        {
            String strSql = "SELECT CorporationID, AreaID, CountyID, VillageID, CorporationName " +
                "FROM Corporation WHERE (IsValid <> 0)";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        public DataTable GetCorporationInfoByAreaID(int areaID)
        {
            String strSql = string.Format("SELECT CorporationID, AreaID, CountyID, VillageID, CorporationName " +
                "FROM Corporation WHERE (IsValid <> 0) AND AreaID = {0}", areaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }


        public DataTable GetGroupInfoByTypeID(int areaID)
        {
            String strSql = string.Format("SELECT GroupID, TypeID, GroupName " +
                "FROM GroupInfo WHERE (IsValid <> 0) AND TypeID = {0}", areaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        //public bool CheckMainSMSTypeName(string smsTypeName)
        //{
        //    string strSQL = string.Format("SELECT COUNT(MainSMSTypeInfoID) AS Result FROM MainSMSTypeInfo WHERE (MainSMSTypeName='{0}') AND (IsValid <> 0)", smsTypeName);
        //    int result = 0;
        //    DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
        //    object obj = db.ExecuteScalar(dbCommand);
        //    try
        //    {
        //        result = int.Parse(obj.ToString());
        //        if (result > 0)
        //            return false;
        //        else
        //            return true;
        //    }
        //    catch
        //    {
        //        return false;
        //    }
        //}

        public bool CheckCorporation(string corporationName)
        {
            string strSQL = string.Format("SELECT COUNT(CorporationID) AS Result FROM Corporation WHERE (CorporationName='{0}') AND (IsValid <> 0)", corporationName);
            int result = 0;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            object obj = db.ExecuteScalar(dbCommand);
            try
            {
                result = int.Parse(obj.ToString());
                if (result > 0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return false;
            }
        }

        public bool CheckCorporation1(string corporationName)
        {
            string strSQL = string.Format("SELECT COUNT(areaID) AS Result FROM Corporation WHERE (CorporationName='{0}') AND (IsValid <> 0)", corporationName);
            int result = 0;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            object obj = db.ExecuteScalar(dbCommand);
            try
            {
                result = int.Parse(obj.ToString());
                if (result > 0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 添加用户组织协会信息
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="corporationID"></param>
        /// <returns></returns>
        public int AddUserCorporation(int userID, int corporationID)
        {
            string strSQL = string.Format(SQL_ADD_CORPORATION, userID, corporationID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        /// <summary>
        /// 添加组织协会信息
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="countyID"></param>
        /// <param name="villageID"></param>
        /// <param name="corporationName"></param>
        /// <returns></returns>
        public int AddCorporation(int areaID, int countyID, int villageID, string corporationName)
        {
            string strSQL = string.Format(SQL_INSERT_CORPORATION, areaID, countyID, villageID, corporationName);
            if (CheckCorporation1(corporationName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            else
                return 0;
        }



        public int AddGroupinfo(int areaID, string corporationName)
        {
            string strSQL = string.Format(SQL_INSERT_GROUP, areaID, corporationName);
            if (CheckCorporation1(corporationName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            else
                return 0;
        }
        /// <summary>
        /// 修改组织协会
        /// </summary>
        /// <param name="corporationID"></param>
        /// <param name="corporationName"></param>
        /// <returns></returns>
        public int UpdateCorporation(int corporationID, string corporationName)
        {
            string strSQL = string.Format(SQL_UPDATE_CORPORATION, corporationName, corporationID);
            if (CheckCorporation(corporationName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }



        public int UpdateGroup(int corporationID, string corporationName)
        {
            string strSQL = string.Format(SQL_UPDATE_GROUP, corporationName, corporationID);
            if (CheckCorporation(corporationName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }








        public int UpdateUserCorporation(int corporationID, int userID)
        {
            string strSQL = string.Format("UPDATE UserCorporation SET CorporationID = {0} WHERE UserID = {1}", corporationID, userID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        /// <summary>
        /// 查询单个用户组织协会信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable GetSampUserCorporationInfo(int userID)
        {
            string strSQL = string.Format(SQL_VIEW_SAMP_CORPORATION, userID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
        public DataTable GetSimpCorporation(int corporationID)
        {
            string strSQL = string.Format(SQL_SAMP_CORPORATIOM, corporationID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }



        public DataTable GetSimpGroup(int corporationID)
        {
            string strSQL = string.Format(SQL_SAMP_GROUP, corporationID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }








        public int DelSampManCorporatio(int userID, int corporationID)
        {
            string strSql = string.Format(SQL_DEL_CORPORATION, userID, corporationID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        public int DelUserCorporation(int userID)
        {
            string strSQL = string.Format(SQL_DEL_USERCORPORATIONINFO, userID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }

        public int DelCustomerGroup(int userID)
        {
            string strSQL = string.Format(SQL_DEL_USERCORPORATIONINFO, userID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        public int DelCorporationInfoByID(int ID)
        {
            string strSQL = string.Format(SQL_DEL_CORPORATIONINFO_BY_ID, ID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }



        public int DelGroupInfoByID(int ID)
        {
            string strSQL = string.Format(SQL_DEL_Groupinfo_BY_ID, ID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        public DataTable GetAreaNameAndCountyNameAndVillageNameAndCorporation()
        {
            string strSQL = "SELECT Corporation.CorporationID, Corporation.CorporationName, Area.AreaName, County.CountyName, Village.VillageName " +
                "FROM Corporation INNER JOIN Area ON Corporation.AreaID = Area.AreaID INNER JOIN " +
                "County ON Corporation.CountyID = County.CountyID INNER JOIN Village ON Corporation.VillageID = Village.VillageID WHERE Corporation.IsValid <> 0";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据组织协会得到用户名
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable GetUserNameByCorporationID(int ID)
        {
            string strSQL = string.Format("SELECT UserInfo.Name, UserInfo.UserID FROM UserInfo " +
                "INNER JOIN UserCorporation ON UserInfo.UserID = UserCorporation.UserID WHERE (UserCorporation.CorporationID = {0})", ID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
    }
}