userinfo.cs

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

        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[1].Rows[1]["UserID"].ToString());
            return userID;
        }

        /// <summary>
        /// 根据手机号得到TradeID
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public int GetTradeByMobile(string mobile)
        {
            String strsql = String.Format("select TradeID from dbo.UserInfo where IsValid =1 and Mobile = '{0}'", mobile);
            try
            {
                object obj = db.ExecuteScalar(CommandType.Text, strsql);
                return int.Parse(obj.ToString());
            }
            catch
            {
                return -1;
            }
        }

        /// <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, "@TradeID", DbType.Int32, "TradeID", 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, "@Hukou", DbType.String, "Hukou", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Race", DbType.String, "Race", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Jiguan", DbType.String, "Jiguan", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Jointime", DbType.String, "Jointime", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@IDcard", DbType.String, "IDcard", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@NowAddress", DbType.String, "NowAddress", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Beizhu", DbType.String, "Beizhu", 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, "@TradeID", DbType.Int32, "TradeID", 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, "@Race", DbType.String, "Race", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Jiguan", DbType.String, "Jiguan", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Jointime", DbType.String, "Jointime", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@IDcard", DbType.String, "IDcard", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@NowAddress", DbType.String, "NowAddress", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Beizhu", DbType.String, "Beizhu", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Speciality", DbType.String, "Speciality", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Birthday", DbType.String, "Birthday", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Hukou", DbType.String, "Hukou", 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; }
        }


        public int ImportCustomerInfoData(Common.Model.UserInfo userInfoData)
        {
            Database dc = DatabaseFactory.CreateDatabase("Connection String");
            DbCommand insertCommand = dc.GetStoredProcCommand("AddCustomerInfo");
            dc.AddInParameter(insertCommand, "@TypeID", DbType.Int32, "TypeID", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@GroupID", DbType.Int32, "GroupID", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Sex", DbType.Int32, "Sex", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@TradeID", DbType.Int32, "TradeID", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Duty", DbType.String, "Duty", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Unit", DbType.String, "Unit", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@UnitAddress", DbType.String, "UnitAddress", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@IDcard", DbType.String, "IDcard", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Birthday", DbType.String, "Birthday", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@JoinDate", DbType.String, "JoinDate", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@Beizhu", DbType.String, "Beizhu", DataRowVersion.Current);

            dc.AddInParameter(insertCommand, "@HomeAddress", DbType.String, "HomeAddress", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@Mobile", DbType.String, "Mobile", DataRowVersion.Current);
            dc.AddInParameter(insertCommand, "@CustomerDefineID", DbType.String, "CustomerDefineID", DataRowVersion.Current);


            DbCommand updateCommand = dc.GetStoredProcCommand("UpdateCustomerInfo");
            dc.AddInParameter(updateCommand, "@TypeID", DbType.Int32, "TypeID", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@GroupID", DbType.Int32, "GroupID", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Sex", DbType.Int32, "Sex", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@TradeID", DbType.Int32, "TradeID", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Duty", DbType.String, "Duty", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Unit", DbType.String, "Unit", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@UnitAddress", DbType.String, "UnitAddress", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@IDcard", DbType.String, "IDcard", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Birthday", DbType.String, "Birthday", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@JoinDate", DbType.String, "JoinDate", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@Beizhu", DbType.String, "Beizhu", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@HomeAddress", DbType.String, "HomeAddress", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@Mobile", DbType.String, "Mobile", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@CustomerDefineID", DbType.String, "CustomerDefineID", DataRowVersion.Current);
            dc.AddInParameter(updateCommand, "@CustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current);

            DbCommand deleteCommand = dc.GetStoredProcCommand("DeleteCustomerInfo");
            dc.AddInParameter(deleteCommand, "@CustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current);
            //try
            //{
            return dc.UpdateDataSet(userInfoData, Common.Model.UserInfo.TABLE_CUSTOMER_INFO,
                insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);
            //}
            // catch
            //{ return 0; }
        }





    }
}