public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
CrystalReport6 rpt = new CrystalReport6();
rpt.Site = this.Site;
return(rpt);
}
private void initdb()
{
string connstr = ConfigurationManager.AppSettings["connectionstring"];
SqlConnection myconn = new SqlConnection(connstr);
string commstr = "truncate table total_table";
SqlCommand mycomm = new SqlCommand(commstr, myconn);
myconn.Open();
mycomm.ExecuteNonQuery();
myconn.Close();
DataSet myds = new DataSet();
myds.Tables.Add();
myds.Tables[0].Columns.Add("时间");
myds.Tables[0].Columns.Add("会员消费");
myds.Tables[0].Columns.Add("非会员消费");
myds.Tables[0].Columns.Add("借球");
myds.Tables[0].Columns.Add("会员卡充值");
myds.Tables[0].Columns.Add("商品销售");
myds.Tables.Add();
myds.Tables.Add();
myds.Tables.Add();
myds.Tables.Add();
myds.Tables.Add();
if (dateTimePicker1.Value.Date == dateTimePicker2.Value.Date)
{
commstr = "select min(datepart(hh,begdatetime)) as '时间',sum(xf_point) as '会员消费',sum(xf_money) as '非会员消费' from card_consumption where begdatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(hh,begdatetime)";
}
else
{
commstr = "select convert(char(10),max(begdatetime),120) as '时间',sum(xf_point) as '会员消费',sum(xf_money) as '非会员消费' from card_consumption where begdatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(dd,begdatetime)";
}
mycomm.CommandText = commstr;
new SqlDataAdapter(mycomm).Fill(myds.Tables[1]);
if (dateTimePicker1.Value.Date == dateTimePicker2.Value.Date)
{
commstr = "select min(datepart(hh,adddatetime)) as '时间',sum(chongzhi) as '会员卡冲值' from vipcard_cz where adddatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(hh,adddatetime)";
}
else
{
commstr = "select convert(char(10),max(adddatetime),120) as '时间',sum(chongzhi) as '会员卡冲值' from vipcard_cz where adddatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(dd,adddatetime)";
}
mycomm.CommandText = commstr;
new SqlDataAdapter(mycomm).Fill(myds.Tables[2]);
if (dateTimePicker1.Value.Date == dateTimePicker2.Value.Date)
{
commstr = "select min(datepart(hh,adddatetime)) as '时间',sum(salemoney) as '会员卡销售' from vipcard_sale where adddatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(hh,adddatetime)";
}
else
{
commstr = "select convert(char(10),max(adddatetime),120) as '时间',sum(salemoney) as '会员卡销售' from vipcard_sale where adddatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(dd,adddatetime)";
}
mycomm.CommandText = commstr;
new SqlDataAdapter(mycomm).Fill(myds.Tables[3]);
if (dateTimePicker1.Value.Date == dateTimePicker2.Value.Date)
{
commstr = "select min(datepart(hh,begdatetime)) as '时间',count(*)*5 as '非会员借球金额' from card_consumption where ball_number is not null and xf_point is null and xf_money is not null and begdatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(hh,enddatetime)";
}
else
{
commstr = "select convert(char(10),max(begdatetime),120) as '时间',count(*)*5 as '非会员借球金额' from card_consumption where ball_number is not null and xf_point is null and xf_money is not null and begdatetime between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(dd,enddatetime)";
}
mycomm.CommandText = commstr;
new SqlDataAdapter(mycomm).Fill(myds.Tables[4]);
if (dateTimePicker1.Value.Date == dateTimePicker2.Value.Date)
{
commstr = "select min(datepart(hh,goods_sale.adddate)) as '时间',sum(goods_amounts*goods_price) from goods_sale,goods_info where goods_info.goods_huohao=goods_sale.goods_huohao and goods_sale.adddate between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(hh,goods_sale.adddate)";
}
else
{
commstr = "select min(datepart(dd,goods_sale.adddate)) as '时间',sum(goods_amounts*goods_price) from goods_sale,goods_info where goods_info.goods_huohao=goods_sale.goods_huohao and goods_sale.adddate between '" + pdv1.Value + "' and '" + pdv2.Value + "' group by datepart(dd,goods_sale.adddate)";
}
mycomm.CommandText = commstr;
new SqlDataAdapter(mycomm).Fill(myds.Tables[5]);
for (int i = 0; i < myds.Tables[1].Rows.Count; i++)
{
DataRow mydr = myds.Tables[0].NewRow();
mydr[0] = myds.Tables[1].Rows[i][0];
mydr[1] = myds.Tables[1].Rows[i][1];
mydr[2] = myds.Tables[1].Rows[i][2];
myds.Tables[0].Rows.Add(mydr);
}
for (int i = 0; i < myds.Tables[4].Rows.Count; i++)
{
bool flg = true;
for (int j = 0; j < myds.Tables[0].Rows.Count; j++)
{
if (myds.Tables[4].Rows[i][0].ToString() == myds.Tables[0].Rows[j][0].ToString())
{
if (myds.Tables[0].Rows[j][3].ToString() != "")
{
myds.Tables[0].Rows[j][3] = Convert.ToInt32(myds.Tables[0].Rows[j][3].ToString()) + Convert.ToInt32(myds.Tables[4].Rows[i][1].ToString());
}
else
{
myds.Tables[0].Rows[j][3] = myds.Tables[4].Rows[i][1].ToString();
}
flg = false;
}
}
if (flg)
{
DataRow mydr = myds.Tables[0].NewRow();
mydr[0] = myds.Tables[4].Rows[i][0];
mydr[3] = myds.Tables[4].Rows[i][1];
myds.Tables[0].Rows.Add(mydr);
}
}
for (int i = 0; i < myds.Tables[5].Rows.Count; i++)
{
bool flg = true;
for (int j = 0; j < myds.Tables[0].Rows.Count; j++)
{
if (myds.Tables[5].Rows[i][0].ToString() == myds.Tables[0].Rows[j][0].ToString())
{
if (myds.Tables[0].Rows[j][5].ToString() != "")
{
myds.Tables[0].Rows[j][5] = Convert.ToInt32(myds.Tables[0].Rows[j][5].ToString()) + Convert.ToInt32(myds.Tables[5].Rows[i][1].ToString());
}
else
{
myds.Tables[0].Rows[j][5] = myds.Tables[4].Rows[i][1].ToString();
}
flg = false;
}
}
if (flg)
{
DataRow mydr = myds.Tables[0].NewRow();
mydr[0] = myds.Tables[5].Rows[i][0];
mydr[5] = myds.Tables[5].Rows[i][1];
myds.Tables[0].Rows.Add(mydr);
}
}
for (int i = 0; i < myds.Tables[2].Rows.Count; i++)
{
bool flg = true;
for (int j = 0; j < myds.Tables[0].Rows.Count; j++)
{
if (myds.Tables[2].Rows[i][0].ToString() == myds.Tables[0].Rows[j][0].ToString())
{
myds.Tables[0].Rows[j][4] = myds.Tables[2].Rows[i][1];
flg = false;
}
}
if (flg)
{
DataRow mydr = myds.Tables[0].NewRow();
mydr[0] = myds.Tables[2].Rows[i][0];
mydr[4] = myds.Tables[2].Rows[i][1];
myds.Tables[0].Rows.Add(mydr);
}
}
for (int i = 0; i < myds.Tables[3].Rows.Count; i++)
{
bool flg = true;
for (int j = 0; j < myds.Tables[0].Rows.Count; j++)
{
if (myds.Tables[3].Rows[i][0].ToString() == myds.Tables[0].Rows[j][0].ToString())
{
if (myds.Tables[0].Rows[j][4].ToString() != "")
{
myds.Tables[0].Rows[j][4] = (Convert.ToInt32(myds.Tables[0].Rows[j][4].ToString()) + Convert.ToInt32(myds.Tables[3].Rows[i][1].ToString()));
}
else
{
myds.Tables[0].Rows[j][4] = myds.Tables[3].Rows[i][1];
}
flg = false;
}
}
if (flg)
{
DataRow mydr = myds.Tables[0].NewRow();
mydr[0] = myds.Tables[3].Rows[i][0];
mydr[4] = myds.Tables[3].Rows[i][1];
myds.Tables[0].Rows.Add(mydr);
}
}
for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < myds.Tables[0].Columns.Count; j++)
{
if (myds.Tables[0].Rows[i][j].ToString() == "")
{
myds.Tables[0].Rows[i][j] = 0;
}
}
}
myconn.Open();
for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
{
commstr = "insert into total_table(时间,会员消费,非会员消费,借球,会员卡充值,商品销售) values('" + myds.Tables[0].Rows[i][0] + "'," + myds.Tables[0].Rows[i][1] + "," + myds.Tables[0].Rows[i][2] + "," + myds.Tables[0].Rows[i][3] + "," + myds.Tables[0].Rows[i][4] + "," + myds.Tables[0].Rows[i][5] + ")";
mycomm.CommandText = commstr;
mycomm.ExecuteNonQuery();
}
myconn.Close();
CrystalReport6 CrystalReport = new CrystalReport6();
crystalReportViewer1.ReportSource = CrystalReport;
}