Example #1
0
        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));
        }
Example #2
0
        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));
        }
Example #3
0
        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));
        }
Example #4
0
        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;
        }
Example #5
0
        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);
            }
        }
Example #6
0
        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);
        }
Example #7
0
 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))));
 }
Example #8
0
        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);
        }
Example #9
0
        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("会员卡迁移完成");
        }
Example #10
0
        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("会员卡迁移完成");
        }
Example #11
0
        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();
        }