示例#1
0
        public DataTable MemberData(string beginTime, string endTime, string[] queryString)
        {
            string sql = "select memcardid as cardno,member.`name` as membername,member.membertypeid as membertype,if(memhandletype.`value` >= 0,handlemoney+moremoney,-1*handlemoney) as payvalue,"
                         + "moremoney as discount,handlemoney as money,memhandletype.handlename as handletype,userinfo.nickname as operator,handletime from memcarddetail "
                         + "left join member on memcarddetail.memcardid = member.cardid "
                         + "left join userinfo on memcarddetail.operatorid = userinfo.userid "
                         + "left join memhandletype on memcarddetail.handletype = memhandletype.handletype where 1=1";

            if (beginTime != null && endTime != null)
            {
                sql += " and (memcarddetail.handletime between '" + beginTime + "' and '" + endTime + "') ";
            }
            if (queryString[0] != "" && queryString[0] != "0")
            {
                sql += " and memcarddetail.handletype = '" + queryString[0] + "' ";
            }
            if (queryString[1] != "")
            {
                sql += " and member.`name` = " + "'" + queryString[1] + "' ";
            }
            if (queryString[2] != "")
            {
                sql += " and memcarddetail.memcardid = '" + queryString[2] + "' ";
            }
            DataSet ds = SqlHelper_MySql.ExecuteDataset(m_connectString, CommandType.Text, sql);

            if (ds.Tables.Count > 0)
            {
                return(ds.Tables[0]);
            }
            else
            {
                return(new DataTable());
            }
        }
示例#2
0
        private DataTable TuiCaiData(string beginTime, string endTime, string[] queryString)
        {
            string sql = "select dishestype, dishesid, dishescount, dishesname, dishesprice, nickname, handletime, orderid from orderdetailview where handletype = 1";

            if (beginTime != null && endTime != null)
            {
                sql += " and (handletime between '" + beginTime + "' and '" + endTime + "') ";
            }
            DataSet ds = SqlHelper_MySql.ExecuteDataset(m_connectString, CommandType.Text, sql);

            if (ds.Tables.Count > 0)
            {
                return(ds.Tables[0]);
            }
            return(new DataTable());
        }
示例#3
0
        private DataTable PaiMingData(string beginTime, string endTime, string[] queryString)
        {
            //这里写好数据
            string sql = "SELECT dishestype, dishesid, dishesname, " +
                         "SUM(if(handletype=0,dishescount,-1*dishescount)) as totalcount, " +
                         "SUM(if(handletype=0,dishescount,-1*dishescount) * dishesprice) as totalmoney " +
                         "from orderdetailview ";

            if (beginTime != null && endTime != null)
            {
                sql += "where (handletime between '" + beginTime + "' and '" + endTime + "') ";
            }
            if (queryString[0] != "")
            {
                sql += " and dishestype = '" + queryString[0] + "' ";
            }
            sql += " GROUP BY dishesid, dishestype";

            DataSet ds = SqlHelper_MySql.ExecuteDataset(m_connectString, CommandType.Text, sql);

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                //实在懒的去整sql的百分比语句了,先取出来再搞一下
                dt.Columns.Add("preCount");
                dt.Columns.Add("preMoney");
                double totalCount = 0;
                double totalMoney = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    totalCount += Convert.ToDouble(dt.Rows[i].ItemArray[3]);
                    totalMoney += Convert.ToDouble(dt.Rows[i].ItemArray[4]);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i].SetField <double>("preCount", Convert.ToDouble(dt.Rows[i].ItemArray[3]) / totalCount);
                    dt.Rows[i].SetField <double>("preMoney", Convert.ToDouble(dt.Rows[i].ItemArray[4]) / totalMoney);
                }

                return(ds.Tables[0]);
            }
            else
            {
                return(new DataTable());
            }
        }
示例#4
0
        private void YingYeParameter(string beginTime, string endTime, string[] queryString, List <ReportParameter> paramList)
        {
            string sql = "select dishestype, SUM(if(handletype=0,dishescount,-1*dishescount) * dishesprice) as totalcount " +
                         " from orderdetailview " +
                         " where paytype = 0 ";

            if (beginTime != null && endTime != null)
            {
                sql += " and (handletime between '" + beginTime + "' and '" + endTime + "') ";
            }
            sql += " GROUP BY dishestype ";
            DataSet ds = SqlHelper_MySql.ExecuteDataset(m_connectString, CommandType.Text, sql);

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    paramList.Add(new ReportParameter(m_dishesNameMap[dt.Rows[i].ItemArray[0].ToString()],
                                                      dt.Rows[i].ItemArray[1].ToString()));
                }
            }

            sql = "select dishestype, SUM(if(handletype=0,dishescount,-1*dishescount) * dishesprice) as totalcount " +
                  " from orderdetailview " +
                  " where paytype = 1 ";
            if (beginTime != null && endTime != null)
            {
                sql += " and (handletime between '" + beginTime + "' and '" + endTime + "') ";
            }
            sql += " GROUP BY dishestype ";
            ds   = SqlHelper_MySql.ExecuteDataset(m_connectString, CommandType.Text, sql);

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    paramList.Add(new ReportParameter(m_dishesNameMap[dt.Rows[i].ItemArray[0].ToString()],
                                                      dt.Rows[i].ItemArray[1].ToString()));
                }
            }
        }
示例#5
0
        //显示会员卡信息
        private void showMemberCusterm()
        {
            showQueryTime();
            m_queryType = QueryType.Member;
            //增加类别
            m_comboBox.Items.Clear();
            string  sql        = "select handletype, handlename from memhandletype";
            string  connString = ConfigurationManager.ConnectionStrings["report.Properties.Settings.restaurantdbConnectionString"].ToString();
            DataSet ds         = SqlHelper_MySql.ExecuteDataset(connString,
                                                                CommandType.Text,
                                                                sql);

            if (ds.Tables.Count > 0)
            {
                DataTable dt  = ds.Tables[0];
                DataRow   row = dt.NewRow();
                row.SetField("handletype", 0);
                row.SetField("handlename", "全部");
                dt.Rows.InsertAt(row, 0);
                m_comboBox.DataSource    = dt;
                m_comboBox.DisplayMember = "handlename";
                m_comboBox.ValueMember   = "handletype";
                m_comboBox.DropDownStyle = ComboBoxStyle.DropDownList;
            }
            //个性化查询条件控件
            m_label1.Text = "类别";
            m_label2.Text = "会员名称";
            m_label3.Text = "会员卡号";

            m_query2.Text = "";
            m_query3.Text = "";

            flowLayoutPanel1.Controls.Add(m_label1);
            flowLayoutPanel1.Controls.Add(m_comboBox);
            flowLayoutPanel1.Controls.Add(m_label2);
            flowLayoutPanel1.Controls.Add(m_query2);
            flowLayoutPanel1.Controls.Add(m_label3);
            flowLayoutPanel1.Controls.Add(m_query3);
            flowLayoutPanel1.Controls.Add(m_queryButton);
        }