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