|
.net in-line script 做过的一个查询页，记录一下 - 今天TOP 20
- 某一会员某一天统计
- 会员所有天记录统计
<script runat="server">
void Page_Load(object sender, EventArgs e) {
//查询时间默认为今天
if(!Page.IsPostBack){
txtReportTime.Text = System.DateTime.Today.ToString();
bindGrid();
}
else{
//开始进行查询
if(ddlReportType.SelectedValue == "0"){//0为今天TOP20
//if(QueryControls.Visible){//首进为今天天TOP20，隐藏查询条件面板
QueryControls.Visible = false;
QueryControls02.Visible = false;
//}
}
bindGrid();
}
}
void bindGrid(){
System.Data.IDataReader dr;
dr = MyQueryMethod();
dgDotReport.DataSource = dr;
dgDotReport.DataBind();
}
void disposeGrid(){
//清掉datagrid
dgDotReport.Dispose();
dgDotReport.DataSource = "";
dgDotReport.DataBind();
}
System.Data.IDataReader MyQueryMethod() {
string connectionString = "server=\'(local)\'; user id=\'sa\'; password=\'kemin%@)9999\'; database=\'diligencexxx\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString;
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
if(ddlReportType.SelectedValue == "0"){//默认今天TOP 20
queryString = "SELECT TOP 20 incept AS '会员', u.usename AS [昵称], " +
"[1朵鲜花] = SUM(CASE lpid WHEN '5F016DD5-7C82-4155-A8A7-32EB3430B359' THEN num ELSE 0 END)," +
"[9朵鲜花] = SUM(CASE lpid WHEN '3BF9C5CC-4380-44FF-8488-AF20C8964FD4' THEN num ELSE 0 END)," +
"[99朵鲜花] = SUM(CASE lpid WHEN '171BCE1A-DAC0-4898-894B-0EAAF9FE12B0' THEN num ELSE 0 END)," +
"[钻戒] = SUM(CASE lpid WHEN '7F179E09-ADDA-4250-9DEA-249E045FE2C5' THEN num ELSE 0 END)," +
"[别墅] = SUM(CASE lpid WHEN 'E32870E9-EE54-44A7-A9FD-853C9BA03D58' THEN num ELSE 0 END)," +
"[总点值] = SUM(CASE lpid WHEN '5F016DD5-7C82-4155-A8A7-32EB3430B359' THEN num ELSE 0 END)*10 + SUM(CASE lpid WHEN '3BF9C5CC-4380-44FF-8488-AF20C8964FD4' THEN num ELSE 0 END)*80 + SUM(CASE lpid WHEN '171BCE1A-DAC0-4898-894B-0EAAF9FE12B0' THEN num ELSE 0 END) * 500 + SUM(CASE lpid WHEN '7F179E09-ADDA-4250-9DEA-249E045FE2C5' THEN num ELSE 0 END)*3000 + SUM(CASE lpid WHEN 'E32870E9-EE54-44A7-A9FD-853C9BA03D58' THEN num ELSE 0 END)*8000" +
"FROM gift_incept g " +
"INNER JOIN use_userinfo u " +
"ON g.incept = u.id" +
" WHERE indate BETWEEN '"+ DateTime.Today.ToString() +"' AND '"+ DateTime.Today.AddDays(1).ToString() +"'" +//.NET 时间
" GROUP BY incept, u.usename " +
" ORDER BY [总点值] DESC";
}else{
string sReportTime;
bool bReportOneDay;
string sMemberId;
//统计某一天，或所有天
if(ddlReportType.SelectedValue == "1"){
sReportTime = " AND indate BETWEEN @theDate AND @theDateAfter";
bReportOneDay = true;
}else{
sReportTime = " ";
bReportOneDay = false;
}
disposeGrid();
//打开查询条件面板
if(ddlReportType.SelectedValue == "1"){
QueryControls.Visible = true;
QueryControls02.Visible = false;
sMemberId = txtMemberId.Text;
}else{
QueryControls02.Visible = true;
QueryControls.Visible = false;
sMemberId = txtMemberId02.Text;
}
queryString = "SELECT incept AS '会员', u.usename AS [昵称], " +
"[1朵鲜花] = SUM(CASE lpid WHEN '5F016DD5-7C82-4155-A8A7-32EB3430B359' THEN num ELSE 0 END)," +
"[9朵鲜花] = SUM(CASE lpid WHEN '3BF9C5CC-4380-44FF-8488-AF20C8964FD4' THEN num ELSE 0 END)," +
"[99朵鲜花] = SUM(CASE lpid WHEN '171BCE1A-DAC0-4898-894B-0EAAF9FE12B0' THEN num ELSE 0 END)," +
"[钻戒] = SUM(CASE lpid WHEN '7F179E09-ADDA-4250-9DEA-249E045FE2C5' THEN num ELSE 0 END)," +
"[别墅] = SUM(CASE lpid WHEN 'E32870E9-EE54-44A7-A9FD-853C9BA03D58' THEN num ELSE 0 END)," +
"[总点值] = SUM(CASE lpid WHEN '5F016DD5-7C82-4155-A8A7-32EB3430B359' THEN num ELSE 0 END)*10 + SUM(CASE lpid WHEN '3BF9C5CC-4380-44FF-8488-AF20C8964FD4' THEN num ELSE 0 END)*80 + SUM(CASE lpid WHEN '171BCE1A-DAC0-4898-894B-0EAAF9FE12B0' THEN num ELSE 0 END) * 500 + SUM(CASE lpid WHEN '7F179E09-ADDA-4250-9DEA-249E045FE2C5' THEN num ELSE 0 END)*3000 + SUM(CASE lpid WHEN 'E32870E9-EE54-44A7-A9FD-853C9BA03D58' THEN num ELSE 0 END)*8000" +
" FROM gift_incept g " +
" INNER JOIN use_userinfo u " +
" ON g.incept = u.id" +
" WHERE incept = @MemberId " +
sReportTime +
" GROUP BY incept, u.usename " +
" ORDER BY [总点值] DESC";
//MemberId
System.Data.IDataParameter dbParam_MemberId = new System.Data.SqlClient.SqlParameter();
dbParam_MemberId.ParameterName = "@MemberId";
dbParam_MemberId.Value = sMemberId;
dbParam_MemberId.DbType = System.Data.DbType.String;
dbCommand.Parameters.Add(dbParam_MemberId);
if(bReportOneDay){
//theDate
System.Data.IDataParameter dbParam_theDate = new System.Data.SqlClient.SqlParameter();
dbParam_theDate.ParameterName = "@theDate";
DateTime thDate = Convert.ToDateTime(txtReportTime.Text);
dbParam_theDate.Value = thDate;
dbParam_theDate.DbType = System.Data.DbType.Date;
dbCommand.Parameters.Add(dbParam_theDate);
//theDateAfter
System.Data.IDataParameter dbParam_theDateAfter = new System.Data.SqlClient.SqlParameter();
dbParam_theDateAfter.ParameterName = "@theDateAfter";
DateTime thDateAfter = new DateTime();
thDateAfter = thDate.AddDays(1);
dbParam_theDateAfter.Value = thDateAfter;
dbParam_theDateAfter.DbType = System.Data.DbType.Date;
dbCommand.Parameters.Add(dbParam_theDateAfter);
}
} dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
dbConnection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return dataReader;
}
///处理从日历读取时间数据
private void fillReportTime(object sender, System.EventArgs e)
{
txtReportTime.Text = calReportTime.SelectedDate.ToString();
calReportTime.Visible = false;
//disposeGrid();
}
private void showCalendar(object sender, System.EventArgs e){
calReportTime.Visible = true;
disposeGrid();
}
</script>
|
