public List <ReportGoodsRank> GetStatisticsTodayGoodsSale()
{
long beginAt = TimeStamp.ConvertDateTimeInt(DateTime.Now.Date);
String sql = String.Format("SELECT g.name, SUM(s.money) AS money, COUNT(*) AS count FROM sales_records AS s LEFT JOIN goods AS g ON g.id = s.goods_id WHERE s.created_at BETWEEN {0} AND {1} GROUP BY g.id ORDER BY g.sort DESC;", beginAt, TimeStamp.GetNowTimeStamp());
return(dal.GetStatisticsBySql(sql));
}
public List <ReportGoodsRank> GroupStatisticsByYear(int year)
{
long begin = TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(String.Format("{0}-01-01 00:00:00", year)));
long end = TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(String.Format("{0}-12-31 23:59:59", year)));
String sql = String.Format("SELECT FROM_UNIXTIME(sale.created_at, '%Y') AS 'year', FROM_UNIXTIME(sale.created_at, '%m') AS 'month', cat.`name`, COUNT(*) AS count, SUM(money) AS money FROM sales_records AS sale LEFT JOIN goods AS g ON sale.goods_id = g.id LEFT JOIN goods_categories AS cat ON cat.id = g.category_id WHERE sale.created_at BETWEEN {0} AND {1} GROUP BY FROM_UNIXTIME(sale.created_at, '%y'), FROM_UNIXTIME(sale.created_at, '%m'), cat.id ORDER BY FROM_UNIXTIME(sale.created_at, '%y'), FROM_UNIXTIME(sale.created_at, '%m')", begin, end);
return(dal.GetStatisticsBySql(sql));
}
public List <ReportGoodsRank> GroupStatisticsByDay(int year, int month)
{
long begin = TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(String.Format("{0}-{1}-01 00:00:00", year, month)));
long end = TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(String.Format("{0}-{1}-01 00:00:00", month == 12 ? year + 1 : year, month == 12 ? 1 : month + 1)));
String sql = String.Format("SELECT FROM_UNIXTIME(sale.created_at, '%d') AS 'day', cat.id, cat.`name`, COUNT(*) AS count, SUM(money) AS money FROM sales_records AS sale LEFT JOIN goods AS g ON sale.goods_id = g.id LEFT JOIN goods_categories AS cat ON cat.id = g.category_id WHERE sale.created_at BETWEEN {0} AND {1} GROUP BY FROM_UNIXTIME(sale.created_at, '%d'), cat.id ORDER BY FROM_UNIXTIME(sale.created_at, '%d')", begin, end);
return(dal.GetStatisticsBySql(sql));
}
private void btnSubmit_Click(object sender, EventArgs e)
{
if (this.card == null || this.card.Record == null)
{
return;
}
String msg = "会员卡已成功延期!";
this.card.Record.ExpiredAt = TimeStamp.ConvertDateTimeInt(this.validTime.Value.Date.AddHours(23));
if (!this.recordBll.EditMemberCardRecord(this.card.Record))
{
msg = "会员卡延期失败!";
}
MessageBox.Show(msg, "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.btnSubmit.Enabled = false;
}
private void btnSearch_Click(object sender, EventArgs e)
{
int cid = (this.cmbCategory.SelectedItem as Models.GoodsCategory).Id;
long begin = TimeStamp.ConvertDateTimeInt(this.dateBegin.Value.Date);
long end = TimeStamp.ConvertDateTimeInt(this.dateEnd.Value);
List <Models.ReportGoodsRank> records = bll.GetStatisticsByCategory(cid, begin, end);
this.dgvGoodsRank.Rows.Clear();
if (records == null)
{
return;
}
foreach (Models.ReportGoodsRank item in records)
{
this.dgvGoodsRank.Rows.Add(item.GoodsName, item.Price, item.Count);
}
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
if (String.IsNullOrEmpty(this.txtMoney.Text.Trim()))
{
MessageBox.Show("请先选择充值次数及金额!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (this.card.Record == null || this.card.Record.Status == Status.Disabled)
{
this.card.Record = new MemberCardRecord();
this.card.Record.MemberCardId = this.card.Id;
this.card.Record.CreatedAt = TimeStamp.GetNowTimeStamp();
}
MemberCardCategoryValue cat = this.cmbMoney.SelectedItem as MemberCardCategoryValue;
this.card.Record.Balance += cat.ValueNum;
this.card.Record.BeginAt = TimeStamp.ConvertDateTimeInt(DateTime.Now.Date);
this.card.Record.ExpiredAt = TimeStamp.ConvertDateTimeInt(this.validTime.Value.Date.AddHours(23));
if (this.card.Record.Id > 0 ? recordBll.EditMemberCardRecord(this.card.Record) : recordBll.AddMemberCardRecord(this.card.Record))
{
SaleLog log = new SaleLog();
log.CreatedAt = TimeStamp.ConvertDateTimeInt(DateTime.Now);
log.MemberId = this.card.Id;
log.MemberNo = this.card.CardNo;
log.GoodsId = cat.GoodsId;
log.Money = Convert.ToDecimal(this.txtMoney.Text.Trim());
log.Summary = String.Format("卡号：{0}在{1}充值{2}元", this.card.CardNo, DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), cat.Money);
saleBll.AddLog(log);
Callback(log.Summary);
this.labMoney.Text = String.Format("帐户剩余次数：{0}次", this.card.Record.Balance.ToString());
}
this.btnSubmit.Enabled = false;
this.validTime.Visible = false;
this.labTip.Visible = false;
this.card = null;
this.cmbMoney.DataSource = null;
this.txtMoney.Clear();
MessageBox.Show("充值成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public List <SaleLog> GetAllSaleLogsByDate(DateTime begin, DateTime end)
{
return(dal.findByWhere(String.Format("created_at BETWEEN {0} AND {1}", TimeStamp.ConvertDateTimeInt(begin), TimeStamp.ConvertDateTimeInt(end))));
}
public List <ReportGoodsRank> GetGoodsRank(DateTime begin, DateTime end)
{
List <ReportGoodsRank> ranks = null;
String sql = String.Format("SELECT goods.category_id, gc.`name`, COUNT(*) AS count, SUM(money) AS price FROM sales_records AS sr LEFT JOIN goods ON sr.goods_id = goods.id LEFT JOIN goods_categories AS gc ON gc.id = goods.category_id WHERE sr.created_at BETWEEN {0} AND {1} GROUP BY gc.id, gc.`name`", TimeStamp.ConvertDateTimeInt(begin), TimeStamp.ConvertDateTimeInt(end));
using (MySqlDataReader rdr = Tools.MySqlHelper.ExecuteReader(Tools.MySqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql))
{
ranks = new List <ReportGoodsRank>();
while (rdr.Read())
{
ReportGoodsRank rank = new ReportGoodsRank();
rank.Id = rdr["category_id"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["category_id"]);
rank.GoodsName = rdr["name"] == DBNull.Value ? "" : rdr["name"].ToString();
rank.Count = rdr["count"] == DBNull.Value ? 0 : Convert.ToInt32(rdr["count"]);
rank.Price = rdr["price"] == DBNull.Value ? 0 : Convert.ToDecimal(rdr["price"]);
ranks.Add(rank);
}
}
return(ranks);
}
private void btnMemberValue_Click(object sender, EventArgs e)
{
String sql = "INSERT INTO members_cards_records(member_card_id, balance, begin_at, expired_at, status, created_at) VALUES";
int row = 0;
int i = 0;
foreach (Member member in members)
{
if (Convert.ToInt32(member.Money) < 1 || member.CreatedAt == "0001/1/1 0:00:00" || member.CreatedAt == "0001-1-1 0:00:00" || member.EndAt == "")
{
continue;
}
MemberCard card = this.getCard(member.No);
sql += String.Format("({0}, {1}, {2}, {3}, 'Enabled', {4}),", card.Id, member.Money, Tools.TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(member.BeginAt)), TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(member.EndAt)), Tools.TimeStamp.GetNowTimeStamp());
i++;
if (i > 5000)
{
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
Console.WriteLine("本次处理：" + row);
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
sql = "INSERT INTO members_cards_records(member_card_id, balance, begin_at, expired_at, status, created_at) VALUES";
}
}
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
if (row < 1)
{
Console.WriteLine("最后一次处理失败：" + sql);
return;
}
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
Console.WriteLine("最后一次处理：" + row);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
MessageBox.Show("会员卡迁移完成");
}
private void btnMigrationMember_Click(object sender, EventArgs e)
{
String sql = "INSERT INTO members_cards(card_no, category_id, created_at) VALUES";
int row = 0;
int i = 0;
foreach (Member member in members)
{
sql += String.Format("('{0}', {1}, {2}),", member.No, member.CategoryId, member.BeginAt == "0001/1/1 0:00:00" || member.BeginAt == "0001-1-1 0:00:00" ? 0 : TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(member.BeginAt)));
i++;
if (i > 5000)
{
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
Console.WriteLine("本次处理：" + row);
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
sql = "INSERT INTO members_cards(card_no, category_id, created_at) VALUES";
}
}
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
if (row < 1)
{
Console.WriteLine("最后一次处理失败：" + sql);
return;
}
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
Console.WriteLine("最后一次处理：" + row);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
MessageBox.Show("会员卡迁移完成");
}
private void btnWriterLog_Click(object sender, EventArgs e)
{
String sql = "INSERT INTO sales_records(goods_id, money, summary, created_at) VALUES";
int row = 0;
int i = 0;
foreach (SaleLog log in logs)
{
int goods_id = Convert.ToInt32(log.BeginAt);
switch (log.BeginAt.ToString())
{
case "0":
goods_id = 24;
break;
case "-1":
goods_id = 18;
break;
case "-2":
goods_id = 19;
break;
case "-3":
if (log.CategoryId == 6)
{
goods_id = 21;
}
else if (log.CategoryId == 7)
{
goods_id = 22;
}
else if (log.CategoryId == 8)
{
goods_id = 23;
}
break;
}
sql += String.Format("({0}, {1}, '{2}',{3}),", goods_id, log.Money, log.EndAt, log.CreatedAt == "0001/1/1 0:00:00" ? 0 : TimeStamp.ConvertDateTimeInt(Convert.ToDateTime(log.CreatedAt)));
i++;
this.txtId.Text = log.Id.ToString();
if (i > 5000)
{
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
Console.WriteLine("本次处理：" + row);
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
Thread.Sleep(500);
sql = "INSERT INTO sales_records(goods_id, money, summary, created_at) VALUES";
}
}
sql = sql.Substring(0, sql.Length - 1);
row = SQLiteHelper.ExecuteNonQuery(ds + this.txtTarget.Text.Trim(), CommandType.Text, sql);
if (row < 1)
{
Console.WriteLine("最后一次处理失败：" + sql);
return;
}
Console.WriteLine("最后一次处理：" + row);
row = Tools.MySqlHelper.ExecuteNonQuery(this.txtServer.Text.Trim(), CommandType.Text, sql);
this.labOk.Text = i.ToString();
this.labNone.Text = (Convert.ToInt32(this.labTotal.Text) - Convert.ToInt32(this.labOk.Text)).ToString();
this.labTotal.Text = "已完成";
this.logs.Clear();
}
