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());
}
}
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());
}
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());
}
}
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()));
}
}
}
//显示会员卡信息
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);
}