<%@ Page Language="C#" AutoEventWireup="true" ValidateRequest="false" %>

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Threading" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>问答积分升级程序</title>
    <script runat="server" type="text/C#">
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        Dictionary<string, int> m_TableRecord = new Dictionary<string, int>();
        private int m_CurrentRecordNumber = 0;
        private bool m_IsFinished = false;

        protected void btnUpgrade_Click(object sender, EventArgs e)
        {
            try
            {
                UpgradeDatabase();
                ShowMessage("会员问答积分升级完毕。");
            }
            catch (Exception ex)
            {
                ShowMessage(ex.Message.ToString());
                return;
            }

        }

        private void ShowMessage(string message)
        {
            Response.Write(string.Format("<script>alert('{0}')<" + "/script>", message));
        }

        /// <summary>
        /// 升级数据库
        /// </summary>
        /// <returns></returns>
        private void UpgradeDatabase()
        {
            try
            {
                SqlConnection conn;
                try
                {
                    string strConn = ConfigurationManager.ConnectionStrings["Connection String"].ToString();
                    conn = new SqlConnection(strConn);
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw new Exception("数据库连接字符串错误。");
                }
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand("SELECT 1 FROM SysColumns INNER JOIN SysObjects ON SysColumns.Id=SysObjects.Id WHERE SysObjects.name='PE_Users' AND SysObjects.type='u' AND SysColumns.name='QAPoint'", conn);

                cmd.Transaction = tran;
                cmd.CommandTimeout = 36000;
                if (cmd.ExecuteScalar() != null)
                {
                    string strSql = string.Empty;
                    if (chkIsLog.Checked)
                    {
                        strSql = @"SET NOCOUNT ON
                                    declare UpgradeCursor cursor 
                                    for
                                    select username,qapoint from pe_users where qapoint is not null
                                    declare @username varchar(20)
                                    declare @qapoint int
                                    open UpgradeCursor
                                    fetch next from UpgradeCursor into @username, @qapoint
                                    while(@@fetch_status=0)
                                    begin--
	                                    UPDATE PE_Users SET UserExp=UserExp+@qapoint*@Proportional WHERE username=@username and QAPoint IS NOT NULL AND UserExp IS NOT NULL
	                                    declare @logId int
	                                    select @logId=isnull(max(logid),0)+1 from pe_userexplog
	                                    insert into PE_UserexpLog (logid,username,expnum,incomepayout,logtime,remark,inputer,isconsume) 
	                                    values(@logid,@username,@qapoint*@Proportional,1,getdate(),'问答积分升级为用户积分','system',1)
                                    fetch next from UpgradeCursor into @username , @qapoint
                                    end
                                    close UpgradeCursor
                                    DEALLOCATE UpgradeCursor";
                    }
                    else
                    {
                        strSql = "UPDATE PE_Users SET UserExp=UserExp+QAPoint*@Proportional WHERE QAPoint IS NOT NULL AND UserExp IS NOT NULL";
                    }

                    cmd.CommandText = strSql;
                    cmd.Parameters.Add(new SqlParameter("@Proportional", SqlDbType.Float));
                    cmd.Parameters[0].SqlValue = txtProportional.Text;
                    m_CurrentRecordNumber = cmd.ExecuteNonQuery();
                    // 删除约束和问答积分字段
                    cmd.CommandText = @"IF EXISTS( SELECT 1 FROM sysobjects a inner join sysobjects b on a.id=b.parent_obj WHERE 
                                    a.name='pe_users' and b.name = 'DF_PE_Users_QAPoint' and b.type='d' and a.type='u')
                                    BEGIN
                                    ALTER TABLE PE_Users DROP CONSTRAINT DF_PE_Users_QAPoint
                                    END
                                    IF EXISTS(SELECT 1 FROM SysColumns INNER JOIN SysObjects ON SysColumns.Id=SysObjects.Id WHERE
                                    SysObjects.name='PE_Users' AND SysObjects.type='u' AND SysColumns.name='QAPoint')
                                    BEGIN
                                    ALTER TABLE PE_Users DROP Column QAPoint
                                    END";
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                else
                {
                    throw new Exception("该数据库中问答积分已经升级过");
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div style="font-size: larger; text-align: center; margin: 5px; padding: 5px 0px;">
        <h3>
            问答积分升级程序</h3>
    </div>
    <div style="text-align: center; margin: 5px; padding: 5px 0px;">
        <table style="width: 100%">
            <tr>
                <td style="width: 50%; padding-right: 10px; padding-bottom: 30px; text-align: right;">
                    <asp:Label ID="labProportional" runat="server" Text="兑换比例："></asp:Label><br />
                    <span style="font-size: small; color: Blue;">1个问答积分兑换2个会员积分&nbsp;&nbsp;<br />
                        则兑换比例为2&nbsp;&nbsp;</span>
                </td>
                <td style="text-align: left; padding-bottom: 30px;">
                    <asp:TextBox ID="txtProportional" runat="server" Text="1.00"></asp:TextBox>
                    <asp:RegularExpressionValidator ID="Validator1" runat="server" ValidationExpression="^([0-9])+(.[0-9]{1,2})?$"
                        ControlToValidate="txtProportional" Display="Dynamic" ForeColor="Red">请输入正实数，最多两位小数。</asp:RegularExpressionValidator>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtProportional"
                        ErrorMessage="请输入正实数，最多两位小数。" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td style="padding-right: 10px; padding-bottom: 30px; text-align: right;">
                    <asp:Label ID="Label2" runat="server" Text="是否记录会员积分变更明细："></asp:Label>
                    <br />
                    <span style="font-size: small; color: Blue;">记录变更明细会降低效率&nbsp;&nbsp;<br />
                        用户数达到十万级以上不建议记录&nbsp;&nbsp;</span>
                </td>
                <td style="text-align: left; padding-bottom: 30px;">
                    <asp:CheckBox ID="chkIsLog" runat="server" Text="记录变更明细" />
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Button ID="btnUpgrade" runat="server" Style="width: 50px;" Text="升级" OnClick="btnUpgrade_Click" />
                </td>
            </tr>
        </table>
    </div>
    <div id="BOX_overlay" style="display: none;">
        <div id="RegUser">
            <div>
                <label>
                    <font color="#FF0000">数据正在更新中……</font></label><br />
                <img alt="" src="<%=VirtualPathUtility.AppendTrailingSlash(Request.ApplicationPath) %>admin/Images/progressbar.gif" />
            </div>
        </div>
    </div>
    <script src="<%=VirtualPathUtility.AppendTrailingSlash(Request.ApplicationPath) %>admin/JS/ModalPopup.js"
        type="text/javascript"></script>
    </form>
</body>
</html>
