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();
}
}
}
