pricemenu.aspx.cs

简单的Web平台。能够读取Excel文件

        /////////////////////////////////////////////////////////////

        string strVersion = null;
        string strModels = null;
        for (int i = 0; i < trvPrice.Nodes.Count; i++) // 价格
        {
            TreeNode tnPrice = trvPrice.Nodes[i];
            for (int j = 0; j < tnPrice.ChildNodes.Count; j++) // 厂牌
            {
                TreeNode tnMake = tnPrice.ChildNodes[j];
                for (int k = 0; k < tnMake.ChildNodes.Count; k++)  // 车系
                {
                    TreeNode tnModel = tnMake.ChildNodes[k];
                    if (tnModel.Checked)
                    {
                        strVersion = tnModel.ValuePath.Split('/')[0];
                        strModels += tnModel.Value + ",";
                    }
                }
            }
        }

        if (null == strModels)
            return;

        strModels = strModels.Remove(strModels.Length - 1, 1);
        string strScript = "<script language='javascript'>" +
                           "parent.frames['Main_Bottom_MainFrame'].location = 'EditMenu.aspx?version=" + strVersion + "&models=" + strModels + " ';" +
                           "document.all.Button1.disabled = 'true';" +
                           "</script>";
        ClientScript.RegisterStartupScript(this.GetType(), "clientScript", strScript);
    }

    /// <summary>
    /// 发布价格
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnPublish_Click(object sender, EventArgs e)
    {
        string strVersion = trvPrice.CheckedNodes[0].Value;
        string strSQL_Check = "SELECT count(*) FROM MatchStatus WHERE Version LIKE '" + strVersion + "'";
        string strSQL_PubPrice = "UPDATE priceInfo SET 状态=3 WHERE 价格版本号 LIKE '" + strVersion + "'";

        DBConnector dbConn = new DBConnector();

        int iMatchedModelCount = dbConn.QueryIntValue( strSQL_Check );

        if ( iMatchedModelCount <= 0 )
        {
            MessageBox.Show( this, "未导入任何价格数据，不能发布价格" );
            return;
        }

        int iRow = dbConn.Execute( strSQL_PubPrice );
        
        trvPrice.CheckedNodes[0].Checked = false;       // 取消勾选项
        btnPublish.Enabled = false;
        btnAbandon.Enabled = false;
        btnDel.Enabled = false;
    }

    /// <summary>
    /// 废弃价格
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnAbandon_Click(object sender, EventArgs e)
    {
        string strVersion = trvPrice.CheckedNodes[0].Value;
        string strSQL_AbanPrice = "UPDATE priceInfo SET 状态=4 WHERE 价格版本号 LIKE '" + strVersion + "'";

        DBConnector dbConn = new DBConnector();
        int iRow = dbConn.Execute( strSQL_AbanPrice );

        trvPrice.CheckedNodes[0].Checked = false;
        btnPublish.Enabled = false;
        btnAbandon.Enabled = false;
        btnDel.Enabled = false;
    }

    #endregion

    /************************用户定义函数***************************/
    #region 用户定义函数
    /// <summary>
    /// 生成价格项目结点
    /// </summary>
    /// <returns>成功返回True，错误返回False</returns>
    private bool PopulatePriceItem()
    {
        DataSet dsPriceItem;
        string strSQL_priceItem = "SELECT 价格名称,价格版本号 FROM priceInfo";

        if (0 != Convert.ToInt32(ddlPriceItemFilter.SelectedValue))
        {
            strSQL_priceItem += " WHERE 状态=";
            strSQL_priceItem += ddlPriceItemFilter.SelectedValue;
        }

        dsPriceItem = m_dbConn.Query(strSQL_priceItem);

        if (null == dsPriceItem)
        {
            return false;
        }

        // 添加结点
        for (int i = 0; i < dsPriceItem.Tables[0].Rows.Count; i++)
        {
            TreeNode tn = new TreeNode(dsPriceItem.Tables[0].Rows[i][0].ToString(),
                                       dsPriceItem.Tables[0].Rows[i][1].ToString());
            tn.NavigateUrl = "BrowsePrice.aspx?version=" + dsPriceItem.Tables[0].Rows[i][1].ToString();
            trvPrice.Nodes.Add(tn);
            PopulateMakeLeaf(tn);     // 添加厂牌条目
        }

        return true;
    }

    /// <summary>
    /// 添加厂牌信息
    /// </summary>
    /// <param name="treeNode"></param>
    /// <returns></returns>
    private bool PopulateMakeLeaf(TreeNode treeNode)
    {
        string strSQL_MakeInfo = "SELECT * FROM Make";
        string strVersion = treeNode.Value;

        DBConnector dbConn = new DBConnector();
        DataSet dsMakeInfo = dbConn.Query( strSQL_MakeInfo );

        if (  null == dsMakeInfo )
        {
            return false;
        }

        for (int i = 0; i < dsMakeInfo.Tables[0].Rows.Count; i++ )
        {
            TreeNode tn = new TreeNode( dsMakeInfo.Tables[0].Rows[i]["MakeName"].ToString(),
                                        dsMakeInfo.Tables[0].Rows[i]["MakeCode"].ToString());
            tn.NavigateUrl = "BrowsePrice.aspx?version=" + strVersion;
            treeNode.ChildNodes.Add( tn );
            PopulateModelLeaf( tn );
        }
        return true;
    }

    /// <summary>
    /// 在TreeView中添加车系信息
    /// </summary>
    /// <param name="treeNode">添加位置</param>
    /// <returns>成功返回True，错误返回False</returns>
    private bool PopulateModelLeaf(TreeNode treeNode)
    {
        /*
        string strPriceVersion = treeNode.Text; // 价格版本号        
        string strSQL_PriceInfo = "SELECT * FROM priceInfo WHERE 价格版本号 LIKE '" + strPriceVersion + "'";
        DataSet dsPriceInfo = m_dbConn.Query( strSQL_PriceInfo );   // 当前价格详细信息DateSet

        if ( null == dsPriceInfo )
        {
            return false;
        }
        
        string strMakeCode = dsPriceInfo.Tables[0].Rows[0]["厂牌编号"].ToString();  // 价格所属厂牌
        string strTalbeName = dsPriceInfo.Tables[0].Rows[0]["数据表名称"].ToString(); // 价格信息所在表名称

        /////////////////////////////////////////////////////////////////////////////////////////
        // 添加车系结点
        string strSQL_Model = "SELECT * FROM Model WHERE MakeCode LIKE '" + strMakeCode + "'";
        DataSet dsModelInfo = m_dbConn.Query( strSQL_Model );

        if ( null == dsModelInfo )
        {
            return false;
        }

        for (int i = 0; i < dsModelInfo.Tables[0].Rows.Count; i++ )
        {
            TreeNode tn = new TreeNode(dsModelInfo.Tables[0].Rows[i]["ModelName"].ToString(),
                                       dsModelInfo.Tables[0].Rows[i]["ModelCode"].ToString());
            //////////////////////////////////////////////////////////////////
            // 使Node不会触发SelectedNodeChanged事件
            tn.NavigateUrl = "#";
            tn.Target = "_self";
            //////////////////////////////////////////////////////////////////
            treeNode.ChildNodes.Add( tn );
            // PopulateSubModelLeaf( tn ); 取消车型信息
        }
        /////////////////////////////////////////////////////////////////////////////////////////

        return true;
        */

        string strMakeCode = treeNode.Value;
        string strVersion = treeNode.Parent.Value;
        string strSQL_ModelInfo = "SELECT * FROM Model WHERE MakeCode LIKE '" + strMakeCode + "'";
        if (0 == ddlComStatus.SelectedValue.CompareTo("已导入"))
        {
            strSQL_ModelInfo += " AND ModelCode IN (SELECT ModelCode FROM MatchStatus WHERE Version LIKE '" + strVersion + "')";
        }
        else if (0 == ddlComStatus.SelectedValue.CompareTo("未导入"))
        {
            strSQL_ModelInfo += " AND ModelCode NOT IN (SELECT ModelCode FROM MatchStatus WHERE Version LIKE '" + strVersion + "')";
        }        

        DBConnector dbConn = new DBConnector();
        DataSet dsModelInfo = dbConn.Query(strSQL_ModelInfo);

        if (null == dsModelInfo)
        {
            return false;
        }

        //////////////////////////////////////////////////////////////////////////////////
        // 全部车系
        if ( 0 == ddlComStatus.SelectedValue.CompareTo("全部") )
        {
            for (int i = 0; i < dsModelInfo.Tables[0].Rows.Count; i++ )
            {
                TreeNode tn = new TreeNode(dsModelInfo.Tables[0].Rows[i]["ModelName"].ToString(),
                                           dsModelInfo.Tables[0].Rows[i]["ModelCode"].ToString());                
                tn.NavigateUrl = "EditLimition.aspx?version=" + strVersion + "&model=" + dsModelInfo.Tables[0].Rows[i]["ModelCode"].ToString();
                treeNode.ChildNodes.Add(tn);
            }

            string strSQL_ImpModel = strSQL_ModelInfo + " AND ModelCode IN (SELECT ModelCode FROM MatchStatus WHERE Version LIKE '" + strVersion + "')";

            DataSet dsImpModel = dbConn.Query( strSQL_ImpModel );
            for (int i = 0; i < treeNode.ChildNodes.Count; i++ )
            {
                for (int j = 0; j < dsImpModel.Tables[0].Rows.Count; j++)
                {
                    if ( treeNode.ChildNodes[i].Value == dsImpModel.Tables[0].Rows[j]["ModelCode"].ToString() )
                    {
                        treeNode.ChildNodes[i].Checked = true;
                    }
                }
            }
        }
        /////////////////////////////////////////////////////////////////////////////////
        // 查看已导入,未导入车系
        else
        {
            for (int i = 0; i < dsModelInfo.Tables[0].Rows.Count; i++)
            {
                TreeNode tn = new TreeNode(dsModelInfo.Tables[0].Rows[i]["ModelName"].ToString(),
                                            dsModelInfo.Tables[0].Rows[i]["ModelCode"].ToString());
                tn.NavigateUrl = "EditLimition.aspx?version=" + strVersion + "&model=" + dsModelInfo.Tables[0].Rows[i]["ModelCode"].ToString();
                treeNode.ChildNodes.Add(tn);
            }
        }
        /////////////////////////////////////////////////////////////////////////////////

        return true;
    }

    /// <summary>
    /// 在TreeView中添加车型信息(已取消对车型的查询)
    /// </summary>
    /// <param name="treeNode">添加位置</param>
    /// <returns>成功返回True，错误返回False</returns>
    private bool PopulateSubModelLeaf(TreeNode treeNode)
    {
        string strModelCode = treeNode.Value;      // 车系编号
        string strSQL_SubMdlInfo = "SELECT * FROM SubModel WHERE ModelCode LIKE '" + strModelCode + "'";

        DataSet dsSubModel = m_dbConn.Query( strSQL_SubMdlInfo );       // 车型信息的DataSet
        if (null == dsSubModel)
        {
            return false;
        }

        // 添加车型结点
        for (int i = 0; i < dsSubModel.Tables[0].Rows.Count; i++ )
        {
            TreeNode tn = new TreeNode(dsSubModel.Tables[0].Rows[i]["SubMdlName"].ToString(),
                                       dsSubModel.Tables[0].Rows[i]["SubMdlCode"].ToString());
            //////////////////////////////////////////////////////////////////
            // 使Node不会触发SelectedNodeChanged事件
            tn.NavigateUrl = "#";
            tn.Target = "_self";
            //////////////////////////////////////////////////////////////////
            treeNode.ChildNodes.Add( tn );
        }

        // 判断已导入的价格
        string strRootValue = treeNode.ValuePath.Split('/')[0];
        string strSQL_PriceInfo = "SELECT 数据表名称 FROM priceInfo WHERE 价格版本号 LIKE '" + strRootValue + "'";
        string strTableName = m_dbConn.QueryString( strSQL_PriceInfo ); // 价格信息所在数据表名称

        string strSQL_SubMdlList = "SELECT 所属车型 FROM " + strTableName + " GROUP BY 所属车型";
        DataSet dsSubMdl = m_dbConn.Query( strSQL_SubMdlList );

        if (null == dsSubMdl)
            return true;

        for (int i = 0; i < dsSubMdl.Tables[0].Rows.Count; i++ )
        {
            for (int j = 0; j < treeNode.ChildNodes.Count; j++ )
            {
                if (dsSubMdl.Tables[0].Rows[i]["所属车型"].ToString()==treeNode.ChildNodes[j].Value)
                {
                    treeNode.ChildNodes[j].Checked = true;
                }
            }
        }

        return true;
    }

    /// <summary>
    /// 取得某套价格中，选择的总车系数
    /// </summary>
    /// <param name="treeNode"></param>
    /// <returns></returns>
    private int GetCheckedItemCount(TreeNode treeNode)
    {
        int iCheckedModelCount = 0;

        for (int i = 0; i < treeNode.ChildNodes.Count; i++ )
        {
            if ( treeNode.ChildNodes[i].Checked )
            {
                iCheckedModelCount++;
            }
        }

        return iCheckedModelCount;
    }

    #endregion

    /**************************数据成员*****************************/
    #region 数据成员
    DBConnector m_dbConn = new DBConnector();   // 数据库联接对象
    #endregion        
    
}