public static void ProcessPointsExpression() { string sql = @" SELECT [Value] FROM bx_Settings WHERE TypeName = 'MaxLabs.bbsMax.Settings.PointSettings' AND [Key] = '*'; "; using (SqlConnection connection = new SqlConnection(Settings.Current.IConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sql, connection); command.CommandTimeout = 60; PointSettings setting = null; try { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { setting = new PointSettings(); setting.Parse(reader.GetString(0)); } } if (setting == null) return; PointExpressionColumCollection colums = GetGeneralPointExpressionColums(); List<string> columNames = new List<string>(); string expression = setting.GeneralPointExpression; if (expression == string.Empty) return; for (int i = 0; i < colums.Count; i++) { string pattern = @"\b" + colums[i].FriendlyShow + @"\b"; Regex reg = new Regex(pattern, RegexOptions.IgnoreCase); if (reg.IsMatch(expression)) { columNames.Add(colums[i].Colum); expression = reg.Replace(expression, "[" + colums[i].Colum + "]"); //expression = Regex.Replace(expression, pattern, "[" + colums[i].Colum + "]", RegexOptions.IgnoreCase); } } sql = @" ALTER TRIGGER [bx_Users_Exp_AfterUpdate] ON [bx_Users] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF (" + GetUpdatePointCondition(columNames) + @") BEGIN DECLARE @MaxValue int; SET @MaxValue = 2147483647; SET ARITHABORT OFF; SET ANSI_WARNINGS OFF; UPDATE [bx_Users] SET Points = ISNULL(" + expression + @",@MaxValue) WHERE [UserID] IN(SELECT DISTINCT [UserID] FROM [INSERTED]); END IF (UPDATE([IsActive])) BEGIN DECLARE @NewUserID int,@NewUsername nvarchar(50),@DeletedCount int,@InsertCount int; SELECT @InsertCount=COUNT(*) FROM [INSERTED] WHERE [IsActive]=1; SELECT @DeletedCount=COUNT(*) FROM [DELETED] WHERE [IsActive]=1; SELECT TOP 1 @NewUserID = UserID,@NewUsername = Username FROM [bx_Users] WITH (NOLOCK) WHERE [IsActive] = 1 ORDER BY [UserID] DESC; UPDATE [bx_Vars] SET NewUserID = @NewUserID, NewUsername = @NewUsername, TotalUsers = TotalUsers + @InsertCount - @DeletedCount WHERE [ID]=(SELECT TOP 1 ID FROM [bx_Vars]); IF @@ROWCOUNT = 0 BEGIN DECLARE @TotalUsers int; SELECT @TotalUsers = COUNT(*) FROM [bx_Users] WITH (NOLOCK) WHERE [IsActive] = 1 AND [UserID]<>0; INSERT [bx_Vars] (NewUserID,NewUsername,TotalUsers)VALUES(@NewUserID,@NewUsername,@TotalUsers); END SELECT 'ResetVars' AS XCMD; END END "; command.CommandText = sql; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); sql = @" ALTER PROCEDURE bx_UpdateUserGeneralPoint @UserID int AS BEGIN SET NOCOUNT ON; DECLARE @MaxValue int; SET @MaxValue = 2147483647; SET ARITHABORT OFF; SET ANSI_WARNINGS OFF; UPDATE bx_Users SET Points = ISNULL(" + expression + @",@MaxValue) WHERE [UserID] = @UserID; END "; command.CommandText = sql; command.CommandType = CommandType.Text; command.ExecuteNonQuery(); } catch (Exception ex) { CreateLog(ex); ErrorMessages.Add("�����ܻ���ʧ��,������̨�ֶ����ܻ��ֹ�ʽ��ԭ��ʽ��" + setting.GeneralPointExpression + "��"); //throw new Exception("�����ܻ��ֵĴ������ʹ洢����ʧ��"+ "(��ʽ" + setting.GeneralPointExpression + ")" + ex.Message + sql); } finally { connection.Close(); } } }
public static void ConvertPoints() { string sql = @" IF EXISTS (SELECT * FROM sysobjects WHERE [type] = N'U' AND [name] = N'bbsMax_ExtendedPoints') AND EXISTS (SELECT * FROM [sysobjects] WHERE [type]='U' AND [name]='bbsMax_ExtendedPoints') BEGIN SELECT * FROM bbsMax_ExtendedPoints; END ELSE SELECT -9999 AS PointID; IF EXISTS (SELECT * FROM sysobjects WHERE [type] = N'U' AND [name] = N'System_bbsMax_Settings') AND EXISTS (SELECT * FROM [sysobjects] WHERE [type]='U' AND [name]='System_bbsMax_Settings') BEGIN SELECT * FROM System_bbsMax_Settings; END ELSE SELECT '-9999' AS Catalog; "; PointSettings pointSetting = new PointSettings(); UserPointCollection points = new UserPointCollection(); Dictionary<int, bool> allowImports = new Dictionary<int, bool>(); Dictionary<int, bool> allowExports = new Dictionary<int, bool>(); Dictionary<int, int> ratios = new Dictionary<int, int>(); int exchangeMinBalance = 0; double exchangeTax = 0.2; int tradePointID = 0; double tradingTax = 0.2; int transferMinBalance = 0; using (SqlConnection connection = new SqlConnection(Settings.Current.IConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sql, connection); command.CommandTimeout = 60; try { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int pointID = reader.GetInt32(reader.GetOrdinal("PointID")); if (pointID == -9999) return; if (pointID > 0 && pointID < 9) { pointID = pointID - 1; if (allowExports.ContainsKey(pointID)) continue; UserPoint userPoint = new UserPoint(); allowExports.Add(pointID, reader.GetBoolean(reader.GetOrdinal("AllowExport"))); allowImports.Add(pointID, reader.GetBoolean(reader.GetOrdinal("AllowImport"))); ratios.Add(pointID, reader.GetInt32(reader.GetOrdinal("Ratio"))); userPoint.Type = (MaxLabs.bbsMax.Enums.UserPointType)pointID; userPoint.Name = reader.GetString(reader.GetOrdinal("PointName")); userPoint.UnitName = reader.GetString(reader.GetOrdinal("PointUnit")); userPoint.InitialValue = reader.GetInt32(reader.GetOrdinal("DefaultPoint")); userPoint.Display = reader.GetBoolean(reader.GetOrdinal("IsPublic")); userPoint.Enable = reader.GetBoolean(reader.GetOrdinal("IsEnabled")); userPoint.MaxValue = reader.GetInt32(reader.GetOrdinal("MaxValue")); userPoint.MinValue = reader.GetInt32(reader.GetOrdinal("MinValue")); points.Add(userPoint); } } if (reader.NextResult()) { string pointformula = string.Empty; while (reader.Read()) { string catalog = reader.GetString(reader.GetOrdinal("Catalog")); if (catalog == "-9999") break; if (string.Compare(catalog, "PointSetting", true) == 0) { string key = reader.GetString(reader.GetOrdinal("SettingKey")); string value = reader.GetString(reader.GetOrdinal("SettingValue")); try { switch (key.ToLower()) { case "exchangeminbalance": exchangeMinBalance = int.Parse(value); break; case "exchangetax": exchangeTax = double.Parse(value); break; case "tradepointid": tradePointID = int.Parse(value) - 1; break; case "tradingtax": tradingTax = double.Parse(value); break; case "transferminbalance": transferMinBalance = int.Parse(value); break; case "pointname": pointSetting.GeneralPointName = value; break; case "pointformula": pointformula = value; break; default: break; } } catch { } } } if (string.IsNullOrEmpty(pointformula) == false) pointSetting.GeneralPointExpression = GetGeneralPointExpression(pointformula); } } pointSetting.UserPoints = points; if (allowImports.Count > 0) { //�һ����� PointExchangeProportionCollection ExchangeProportions = new PointExchangeProportionCollection(); for (int i = 0; i < 8; i++) { if (ratios.ContainsKey(i)) { ExchangeProportions.Add((UserPointType)i, ratios[i] == 0 ? 1 : ratios[i]); } else ExchangeProportions.Add((UserPointType)i, 1); } pointSetting.ExchangeProportions = ExchangeProportions; //�һ����� PointExchangeRuleCollection PointExchangeRules = new PointExchangeRuleCollection(); foreach (KeyValuePair<int, bool> pair in allowImports) { if (pair.Value)//������� { foreach (KeyValuePair<int, bool> tempPair in allowExports) { if (tempPair.Key == pair.Key) continue; if (tempPair.Value)//����ҳ� { PointExchangeRule rule = new PointExchangeRule(); rule.PointType = (UserPointType)tempPair.Key; rule.TargetPointType = (UserPointType)pair.Key; try { rule.TaxRate = (int)(exchangeTax * 100); } catch { } PointExchangeRules.Add(rule); } } } } pointSetting.PointExchangeRules = PointExchangeRules; try { pointSetting.TradeRate = (int)(tradingTax * 100); } catch { } //PointTransferRuleCollection PointTransferRules = new PointTransferRuleCollection(); //PointTransferRule tRule = new PointTransferRule(); //tRule.CanTransfer = true; //try //{ // tRule.PointType = (UserPointType)tradePointID; //} //catch //{ //} //tRule.TaxRate = pointSetting.TradeRate; //PointTransferRules.Add(tRule); //pointSetting.PointTransferRules = PointTransferRules; pointSetting.PointTransferRules = new PointTransferRuleCollection(); pointSetting.PointIcons = new PointIconCollection(); } sql = @" UPDATE bx_Settings SET [Value] = @PointString WHERE TypeName = 'MaxLabs.bbsMax.Settings.PointSettings' AND [Key] = '*'; IF @@ROWCOUNT = 0 INSERT INTO bx_Settings ([Key], [Value], [TypeName]) VALUES ('*', @PointString, 'MaxLabs.bbsMax.Settings.PointSettings'); DROP TABLE bbsMax_ExtendedPoints; "; command.CommandText = sql; SqlParameter param = new SqlParameter("@PointString", SqlDbType.NText); param.Value = pointSetting.ToString(); command.Parameters.Add(param); command.ExecuteNonQuery(); } catch (Exception ex) { CreateLog(ex); throw new Exception("������չ��������ʧ��" + ex.Message + sql); } finally { connection.Close(); } } }