Beispiel #1
0
        public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
        {
            CrystalReport6 rpt = new CrystalReport6();

            rpt.Site = this.Site;
            return(rpt);
        }
Beispiel #2
0
        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;
        }