private void searchType_SelectedIndexChanged(object sender, EventArgs e)
{
dgv.Columns.Clear();
if (searchType.SelectedIndex == 0)//退单
{
BathClass.add_cols_to_dgv(dgv, "单据号");
BathClass.add_cols_to_dgv(dgv, "手牌号");
BathClass.add_cols_to_dgv(dgv, "系统账号");
BathClass.add_cols_to_dgv(dgv, "项目名称");
BathClass.add_cols_to_dgv(dgv, "单价");
BathClass.add_cols_to_dgv(dgv, "数量");
BathClass.add_cols_to_dgv(dgv, "金额");
BathClass.add_cols_to_dgv(dgv, "技师号");
BathClass.add_cols_to_dgv(dgv, "录入时间");
BathClass.add_cols_to_dgv(dgv, "录入员工");
BathClass.add_cols_to_dgv(dgv, "退单员工");
BathClass.add_cols_to_dgv(dgv, "退单原因");
BathClass.add_cols_to_dgv(dgv, "退单时间");
}
else if (searchType.SelectedIndex == 1)
{
//BathClass.add_cols_to_dgv(dgv, "手牌号");
//BathClass.add_cols_to_dgv(dgv, "系统账号");
//BathClass.add_cols_to_dgv(dgv, "金额");
//BathClass.add_cols_to_dgv(dgv, "结账时间");
//BathClass.add_cols_to_dgv(dgv, "操作员工");
}
}
private void find_summery()
{
dgv.Columns.Clear();
dgv.Rows.Clear();
BathClass.add_cols_to_dgv(dgv, "技师号");
BathClass.add_cols_to_dgv(dgv, "总数量");
BathClass.add_cols_to_dgv(dgv, "未结账数量");
BathClass.add_cols_to_dgv(dgv, "已结账数量");
var orders = db.Orders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders = orders.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders = orders.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
var tech_orders = orders.GroupBy(x => x.technician);
var orders_paid = db.HisOrders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders_paid = orders_paid.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders_paid = orders_paid.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
var tech_orders_paid = orders_paid.GroupBy(x => x.technician);
foreach (var order in tech_orders)
{
var order_paid = tech_orders_paid.FirstOrDefault(x => x.Key == order.Key);
var count_paid = order_paid.Sum(x => x.number);
var count_unpaid = order.Sum(x => x.number);
dgv.Rows.Add(order.Key, count_paid + count_unpaid, count_unpaid, count_paid);
}
}
private void find_details()
{
dgv.Columns.Clear();
dgv.Rows.Clear();
BathClass.add_cols_to_dgv(dgv, "技师号");
BathClass.add_cols_to_dgv(dgv, "项目");
BathClass.add_cols_to_dgv(dgv, "手牌号");
BathClass.add_cols_to_dgv(dgv, "录入时间");
BathClass.add_cols_to_dgv(dgv, "数量");
var orders = db.Orders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders = orders.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders = orders.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
foreach (var order in orders)
{
dgv.Rows.Add(order.technician, order.menu, order.text, order.inputTime.ToString("MM-dd HH:mm"), order.number);
}
var orders_paid = db.HisOrders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders_paid = orders_paid.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders_paid = orders_paid.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
foreach (var order in orders_paid)
{
dgv.Rows.Add(order.technician, order.menu, order.text, order.inputTime.ToString("MM-dd HH:mm"), order.number);
}
}
//明细查询
private void find_details()
{
dgv.Columns.Clear();
dgv.Rows.Clear();
BathClass.add_cols_to_dgv(dgv, "技师号");
BathClass.add_cols_to_dgv(dgv, "项目");
BathClass.add_cols_to_dgv(dgv, "手牌号");
BathClass.add_cols_to_dgv(dgv, "录入时间");
BathClass.add_cols_to_dgv(dgv, "数量");
var orders = db.Orders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders = orders.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders = orders.Where(x => x.techType == "点钟");
foreach (var order in orders)
{
dgv.Rows.Add(order.technician, order.menu, order.text, order.inputTime.ToString("MM-dd HH:mm"), order.number);
}
var orders_paid = db.HisOrders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders_paid = orders_paid.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders_paid = orders_paid.Where(x => x.techType == "点钟");
foreach (var order in orders_paid)
{
dgv.Rows.Add(order.technician, order.menu, order.text, order.inputTime.ToString("MM-dd HH:mm"), order.number);
}
//dgv.DataSource = from x in orders
// orderby x.inputTime
// select new
// {
// 技师号 = x.technician,
// 编号 = x.id,
// 项目 = x.menu,
// 输入时间 = x.inputTime,
// 数量 = x.number,
// 已结账 = x.paid
// };
}
//汇总查询
private void find_summery()
{
dgv.Columns.Clear();
dgv.Rows.Clear();
BathClass.add_cols_to_dgv(dgv, "技师号");
BathClass.add_cols_to_dgv(dgv, "总数量");
BathClass.add_cols_to_dgv(dgv, "未结账数量");
BathClass.add_cols_to_dgv(dgv, "已结账数量");
var orders = db.Orders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders = orders.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders = orders.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
var tech_orders = orders.GroupBy(x => x.technician);
var orders_paid = db.HisOrders.Where(x => x.inputTime >= m_startTime && x.inputTime <= m_endTime);
orders_paid = orders_paid.Where(x => x.technician != null && x.technician == m_User.id && x.deleteEmployee == null);
orders_paid = orders_paid.Where(x => x.techType == null || x.techType == "上钟" || x.techType == "轮钟");
var tech_orders_paid = orders_paid.GroupBy(x => x.technician);
foreach (var order in tech_orders)
{
var order_paid = tech_orders_paid.FirstOrDefault(x => x.Key == order.Key);
var count_paid = order_paid.Sum(x => x.number);
var count_unpaid = order.Sum(x => x.number);
dgv.Rows.Add(order.Key, count_paid + count_unpaid, count_unpaid, count_paid);
}
//dgv.DataSource = from x in orders
// group x by x.technician into xt
// select new
// {
// 技师号 = xt.Key,
// 总数量 = xt.Sum(y => y.number),
// 已结账数量 = xt.Count(y => y.paid),
// 未结账数量 = xt.Count(y => !y.paid)
// };
}
//显示信息
private void dgv_show()
{
dgv.Columns.Clear();
dgv.Rows.Clear();
if (!get_clear_table_time())
{
return;
}
if (checkType.SelectedIndex == 0)//售卡统计
{
var cardLst = db.CardSale.Where(x => x.payTime >= lastTime &&
x.payTime <= thisTime && x.explain == null && x.abandon == null);
if (cId.Checked)
{
cardLst = cardLst.Where(x => x.memberId == id.Text);
}
if (cType.Checked)
{
var mt = db.MemberType.FirstOrDefault(x => x.name == mType.Text);
if (mt != null)
{
int typeId = mt.id;
cardLst = cardLst.Where(x => db.CardInfo.FirstOrDefault(y => y.CI_CardNo == x.memberId).CI_CardTypeNo == typeId);
}
}
dgv.DataSource = from x in cardLst
orderby x.payTime
select new
{
卡号 = x.memberId,
卡类型 = db.MemberType.FirstOrDefault(y => y.id == db.CardInfo.FirstOrDefault(z => z.CI_CardNo == x.memberId).CI_CardTypeNo).name,
持卡人 = db.CardInfo.FirstOrDefault(z => z.CI_CardNo == x.memberId).CI_Name,
售卡日期 = x.payTime,
售卡金额 = x.balance,
收现金 = x.cash,
收银联 = x.bankUnion,
手牌 = x.seat,
备注 = x.note,
售卡工号 = x.payEmployee
};
}
else if (checkType.SelectedIndex == 1)//售卡汇总
{
dgv.DataSource = null;
var cardLst = db.CardSale.Where(x => x.payTime >= lastTime &&
x.payTime <= thisTime && x.explain == null && x.abandon == null);
if (cId.Checked)
{
cardLst = cardLst.Where(x => x.memberId == id.Text);
}
if (cType.Checked)
{
var mt = db.MemberType.FirstOrDefault(x => x.name == mType.Text);
if (mt != null)
{
int typeId = mt.id;
cardLst = cardLst.Where(x => db.CardInfo.FirstOrDefault(y => y.CI_CardNo == x.memberId).CI_CardTypeNo == typeId);
}
}
BathClass.add_cols_to_dgv(dgv, "服务员");
BathClass.add_cols_to_dgv(dgv, "卡余额");
BathClass.add_cols_to_dgv(dgv, "数量");
BathClass.add_cols_to_dgv(dgv, "现金");
BathClass.add_cols_to_dgv(dgv, "银联");
var users = cardLst.Select(x => x.payEmployee).Distinct();
foreach (var user in users)
{
double user_cash = 0, user_bank = 0;
var cards_users = cardLst.Where(x => x.payEmployee == user);
//首先考虑赠送卡
var cards_users_free = cards_users.Where(x => x.note == "赠送卡");
if (cards_users_free.Any())
{
dgv.Rows.Add(user, "赠送卡", cards_users_free.Count());
}
int count = cards_users.Count();
cards_users = cards_users.Where(x => x.note == null);
var card_types = cards_users.Select(x => x.balance).Distinct();
foreach (var card_type in card_types)
{
var cards_users_type = cards_users.Where(x => x.balance == card_type);
if (cards_users_type.Any())
{
double cash = 0, bank = 0;
var card_cash = cards_users_type.Where(x => x.cash != null);
var card_bank = cards_users_type.Where(x => x.bankUnion != null);
if (card_cash.Any())
{
cash = card_cash.Sum(x => x.cash).Value;
}
if (card_bank.Any())
{
bank = card_bank.Sum(x => x.bankUnion).Value;
}
user_bank += bank;
user_cash += cash;
dgv.Rows.Add(user, card_type, cards_users_type.Count(), cash, bank);
}
}
dgv.Rows.Add("", "小计", count, user_cash, user_bank);
dgv.Rows[dgv.Rows.Count - 1].DefaultCellStyle.BackColor = Color.LightSkyBlue;
}
}
else if (checkType.SelectedIndex == 2)//充值统计
{
var cardLst = db.CardCharge.Where(x => x.CC_InputDate >= lastTime && x.CC_InputDate <= thisTime);
cardLst = cardLst.Where(x => x.CC_ItemExplain == "会员卡充值-收");
if (cId.Checked)
{
cardLst = cardLst.Where(x => x.CC_OldCardNo == id.Text);
}
if (cType.Checked)
{
var mt = db.MemberType.FirstOrDefault(x => x.name == mType.Text);
if (mt != null)
{
int typeId = mt.id;
cardLst = cardLst.Where(x => db.CardInfo.FirstOrDefault(y => y.CI_CardNo == x.CC_CardNo).CI_CardTypeNo == typeId);
}
}
dgv.DataSource = from x in cardLst
orderby x.CC_InputDate
select new
{
卡号 = x.CC_CardNo,
卡类型 = db.MemberType.FirstOrDefault(y => y.id == db.CardInfo.FirstOrDefault(z => z.CI_CardNo == x.CC_CardNo).CI_CardTypeNo).name,
持卡人 = db.CardInfo.FirstOrDefault(z => z.CI_CardNo == x.CC_CardNo).CI_Name,
充值日期 = x.CC_InputDate,
充值金额 = x.CC_DebitSum,
充值工号 = x.CC_InputOperator
};
}
}
