Exemple #1
0
    private void BindGrid(string type)
    {
        //at = SpringUtil.at();

        /* 學研機構 */
        String sql = "SELECT u.UserId,u.UserName,u.Email,u.IsApproved,u.CreateDate,p.Type,p.Name,p.Corp,p.Tel,p.Address,";

        sql += "   IIf(p.Kind='A','學術單位',IIf(p.Kind='B','研究單位','其他')) as Kind ,m.Password as Pwd ";
        sql += "  from vw_aspnet_MembershipUsers u , UserProfiles p , aspnet_Membership m where u.PasswordAnswer='1' and u.UserName=p.UserName and u.UserId=m.UserId ";
        if (type.Equals("A"))
        {
            sql += "   order by u.UserId";
        }
        else if (type.Equals("B"))
        {
            sql += "  and u.IsApproved=true order by u.UserId";
        }
        else if (type.Equals("C"))
        {
            sql += "  and  u.IsApproved=false order by u.UserId";
        }

        //DataSet ds = new DataSet();
        //at.DataSetFill(ds, CommandType.Text, sql);
        OleDbCommand cmd = new OleDbCommand(sql);
        DataSet      ds  = SQLUtil.QueryDS(cmd);

        int cnt1 = ds.Tables[0].Rows.Count;

        PagedDataSource pgitems = new PagedDataSource();

        pgitems.DataSource       = ds.Tables[0].DefaultView;
        pgitems.AllowPaging      = true;
        pgitems.PageSize         = 10;
        pgitems.CurrentPageIndex = PageNumber;
        if (pgitems.PageCount > 1)
        {
            rptPages.Visible = true;
            ArrayList pages = new ArrayList();
            for (int i = 0; i < pgitems.PageCount; i++)
            {
                pages.Add((i + 1).ToString());
            }
            rptPages.DataSource = pages;
            rptPages.DataBind();
        }
        else
        {
            rptPages.Visible = false;
        }

        MemberGrid1.DataSource = pgitems;
        MemberGrid1.DataBind();
        if (MemberGrid1.Items.Count > 0)
        {
            Panel1.Visible = false;
        }
        else
        {
            Panel1.Visible = true;
        }

        pageNow.Text   = (PageNumber + 1).ToString();
        pageTotal.Text = pgitems.PageCount.ToString();
        cntTotal.Text  = cnt1.ToString();


        /* 業者廠商 */
        sql  = "SELECT u.UserId,u.UserName,u.Email,u.IsApproved,u.CreateDate,p.Type,p.Name,p.Corp,p.Tel,p.Address,";
        sql += "  IIf(p.Kind='A','公民營處(清)理機構',IIf(p.Kind='B','許可再利用機構',IIf(p.Kind='C','公告再利用機構',IIf(p.Kind='D','應回收廢棄物處理機構','其他')))) as Kind ,m.Password as Pwd ";
        sql += "  from vw_aspnet_MembershipUsers u , UserProfiles p , aspnet_Membership m where u.PasswordAnswer='2' and u.UserName=p.UserName and u.UserId=m.UserId ";
        if (type.Equals("A"))
        {
            sql += "   order by u.UserId";
        }
        else if (type.Equals("B"))
        {
            sql += "  and u.IsApproved=true order by u.UserId";
        }
        else if (type.Equals("C"))
        {
            sql += "  and  u.IsApproved=false order by u.UserId";
        }

        //ds = new DataSet();
        //at.DataSetFill(ds, CommandType.Text, sql);
        OleDbCommand cmd2 = new OleDbCommand(sql);

        ds = SQLUtil.QueryDS(cmd2);

        int cnt2 = ds.Tables[0].Rows.Count;



        PagedDataSource pgitems2 = new PagedDataSource();

        pgitems2.DataSource       = ds.Tables[0].DefaultView;
        pgitems2.AllowPaging      = true;
        pgitems2.PageSize         = 10;
        pgitems2.CurrentPageIndex = PageNumber2;
        if (pgitems2.PageCount > 1)
        {
            rptPages2.Visible = true;
            ArrayList pages = new ArrayList();
            for (int i = 0; i < pgitems2.PageCount; i++)
            {
                pages.Add((i + 1).ToString());
            }
            rptPages2.DataSource = pages;
            rptPages2.DataBind();
        }
        else
        {
            rptPages2.Visible = false;
        }

        MemberGrid2.DataSource = pgitems2;
        MemberGrid2.DataBind();
        if (MemberGrid2.Items.Count > 0)
        {
            Panel2.Visible = false;
        }
        else
        {
            Panel2.Visible = true;
        }

        pageNow2.Text   = (PageNumber2 + 1).ToString();
        pageTotal2.Text = pgitems2.PageCount.ToString();
        cntTotal2.Text  = cnt2.ToString();


        cnt.Text = Convert.ToString(cnt1 + cnt2);
    }
Exemple #2
0
    private void BindGrid()
    {
        //at = SpringUtil.at();

        /* 學研機構 */
        String sql = "SELECT  m.UserId,m.UserName,m.Email, m.IsApproved, m.CreateDate, u.Type, u.Name, u.Corp, u.Tel, u.Address, u.Kind , '' as Pwd ";

        sql += " from UserProfiles u , vw_aspnet_MembershipUsers m where  u.UserName=m.UserName and u.Type='1'  ";
        if (!a1.Text.Trim().Equals(""))
        {
            sql += " and u.UserName='******'";
        }
        if (!a2.Text.Trim().Equals(""))
        {
            sql += " and u.Name like '%" + a2.Text.Trim() + "%'";
        }
        if (!a3.Text.Trim().Equals(""))
        {
            sql += " and u.Corp like '%" + a3.Text.Trim() + "%'";
        }
        if (!a4.Text.Trim().Equals(""))
        {
            sql += " and u.Address like '%" + a4.Text.Trim() + "%' or  u.ZipCht like '%" + a4.Text.Trim() + "%' ";
        }
        if (!a5.SelectedValue.Equals("Z"))
        {
            if (!a5.SelectedValue.Equals("C") && !a5.SelectedValue.Equals("D") && !a5.SelectedValue.Equals("E") && !a5.SelectedValue.Equals("F") && !a5.SelectedValue.Equals("G"))
            {
                if (a5.SelectedValue.Equals("A"))
                {
                    sql += " and u.Kind='A' ";
                }
                else if (a5.SelectedValue.Equals("B"))
                {
                    sql += " and u.Kind='B' ";
                }
                else if (a5.SelectedValue.Equals("H"))
                {
                    sql += " and u.Kind='C' ";
                }
            }
            else
            {
                sql += " and 1=0";
            }
        }
        if (!a6.Text.Trim().Equals(""))
        {
            sql += " and 1=0";
        }

        //DataSet ds = new DataSet();
        //at.DataSetFill(ds, CommandType.Text, sql);

        OleDbCommand cmd = new OleDbCommand(sql);
        DataSet      ds  = SQLUtil.QueryDS(cmd);

        foreach (DataRow dRow in ds.Tables[0].Rows)
        {
            user = Membership.GetUser(dRow["UserName"].ToString(), false);
            p    = Profile.GetProfile(dRow["UserName"].ToString());

            if (p.UserProfile.Kind.Equals("A"))
            {
                dRow["Kind"] = "學術單位";
            }
            else if (p.UserProfile.Kind.Equals("B"))
            {
                dRow["Kind"] = "研究單位";
            }
            else
            {
                dRow["Kind"] = "其他";
            }

            dRow["Pwd"] = user.GetPassword();
        }
        MemberGrid1.DataSource   = ds;
        MemberGrid1.DataKeyNames = new string[] { "UserId" };
        MemberGrid1.DataBind();


        /* 業者廠商 */
        sql  = "SELECT  m.UserId,m.UserName,m.Email, m.IsApproved, m.CreateDate, u.Type, u.Name, u.Corp, u.Tel, u.Address, u.Kind , '' as Pwd ";
        sql += " from UserProfiles u , vw_aspnet_MembershipUsers m where  u.UserName=m.UserName and u.Type='2'  ";
        if (!a1.Text.Trim().Equals(""))
        {
            sql += " and u.UserName='******'";
        }
        if (!a2.Text.Trim().Equals(""))
        {
            sql += " and u.Name like '%" + a2.Text.Trim() + "%'";
        }
        if (!a3.Text.Trim().Equals(""))
        {
            sql += " and u.Corp like '%" + a3.Text.Trim() + "%'";
        }
        if (!a4.Text.Trim().Equals(""))
        {
            sql += " and u.Address like '%" + a4.Text.Trim() + "%' or  u.ZipCht like '%" + a4.Text.Trim() + "%' ";
        }
        if (!a5.SelectedValue.Equals("Z"))
        {
            if (!a5.SelectedValue.Equals("A") && !a5.SelectedValue.Equals("B") && !a5.SelectedValue.Equals("H"))
            {
                if (a5.SelectedValue.Equals("C"))
                {
                    sql += " and u.Kind='A' ";
                }
                else if (a5.SelectedValue.Equals("D"))
                {
                    sql += " and u.Kind='B' ";
                }
                else if (a5.SelectedValue.Equals("E"))
                {
                    sql += " and u.Kind='C' ";
                }
                else if (a5.SelectedValue.Equals("F"))
                {
                    sql += " and u.Kind='D' ";
                }
                else if (a5.SelectedValue.Equals("G"))
                {
                    sql += " and u.Kind='E' ";
                }
            }
            else
            {
                sql += " and 1=0";
            }
        }
        if (!a6.Text.Trim().Equals(""))
        {
            sql += " and u.Owner like '%" + a6.Text.Trim() + "%'";
        }


        //ds = new DataSet();
        //at.DataSetFill(ds, CommandType.Text, sql);

        OleDbCommand cmd2 = new OleDbCommand(sql);

        ds = SQLUtil.QueryDS(cmd2);

        foreach (DataRow dRow in ds.Tables[0].Rows)
        {
            user = Membership.GetUser(dRow["UserName"].ToString(), false);
            p    = Profile.GetProfile(dRow["UserName"].ToString());

            if (p.UserProfile.Kind.Equals("A"))
            {
                dRow["Kind"] = "公民營處(清)理機構";
            }
            else if (p.UserProfile.Kind.Equals("B"))
            {
                dRow["Kind"] = "許可再利用機構";
            }
            else if (p.UserProfile.Kind.Equals("C"))
            {
                dRow["Kind"] = "公告再利用機構";
            }
            else if (p.UserProfile.Kind.Equals("D"))
            {
                dRow["Kind"] = "應回收廢棄物處理機構";
            }
            else if (p.UserProfile.Kind.Equals("E"))
            {
                dRow["Kind"] = "其他";
            }
            dRow["Pwd"] = user.GetPassword();
        }
        MemberGrid2.DataSource   = ds;
        MemberGrid2.DataKeyNames = new string[] { "UserId" };
        MemberGrid2.DataBind();
    }