钟表维修管理系统技术解析(六) 数据统计
通过统计维修单据,销售单据,采购单据的金额数据,对每年,每月,每日的金额进行统计,以图表的形式显示出来
营业额统计查询界面用到的控件有:
控件名称 | 说明 |
文本控件(input type=”text”) | 第一要设置每个控件的id,第二设置大小不设置也有默认,第三(data-options)是数据操作:可以设置控件的一些属性和事件 |
按钮(easyui-linkbutton) | |
Morris插件 |
登陆功能的实现:
第一步:数据库
表1:工单录入表(pw_GongDianLuRu)
用于存放钟表录入的信息
列名 | 数据类型 | 主键/外键 | 说明 |
GongDanLuRuID | int | 主键 | 工单录入ID |
YuanShiDanHaoID | int | 外键 | 原始单号ID |
GongDanZhuanTaiID | int | 外键 | 工单状态ID |
JinEZhuangTaiID | int | 外键 | 金额状态ID |
GongDanLeiXingID | int | 外键 | 工单类型ID |
KeHuID | int | 外键 | 客户ID |
JianXiuLeiXingID | int | 外键 | 检修类型ID |
PinPaiID | int | 外键 | 品牌ID |
GongDanRiQi | date |
| 工单日期 |
GongDanBianHao | nchar(50) |
| 工单编号 |
SongXiuRiQi | data |
| 送修日期 |
YuFanRiQi | data |
| 预返日期 |
FenYongJinE | decimal(18, 2) |
| 费用金额 |
ZhongBiaoLeiXing | nchar(10) |
| 钟表类型 |
BiaoKuan | nchar(10) |
| 表款 |
GuiGe | text |
| 规格/名称 |
XiuPeiJiLu | text |
| 修配记录 |
JiXinXingHao | nchar(50) |
| 机芯号 |
BiaoXingHao | nchar(50) |
| 表型号 |
BiaoShenHao | nchar(50) |
| 表身号 |
BiaoDaiJieShu | nchar(50) |
| 表带节数 |
ZhongBiaoQiTaoMiaoShu | varchar(200) |
| 钟表其他描述 |
WaiGuan | text |
| 外观 |
KeHuZhiShu | varchar(200) |
| 客户自述 |
YouXiaoFou | Bit |
| 有效否 |
表2:采购单(pw_CaiGouDan)
列名 | 数据类型 | 主键/外键 | 说明 |
CaiGouDanID | int | 主键 | 采购单ID |
DaoHuoMenDianID | int | 外键 | 到货门店ID |
LuRuYuanID | int | 外键 | 录入员ID |
GongYingShangID | int | 外键 | 供应商ID |
SongHuoFangShiID | int | 外键 | 送货方式ID |
CaiGouLeiBieID | int | 外键 | 采购类别ID |
CaiGouFuKuanLeiXingID | int | 外键 | 采购付款类型ID |
FuKuanZhuangTaiID | int | 外键 | 付款状态ID |
RuKuZhuangTaiID | int | 外键 | 入库状态ID |
CaiGouDanBianHao | nchar(50) |
| 采购单编号 |
DanJuZhuangTai | data |
| 单据状态 |
CaiGouRiQi | data |
| 采购日期 |
DaoHuoRiQi | decimal(18, 2) |
| 到货日期 |
JinE | nchar(10) |
| 金额 |
FaPiaoHao | nchar(10) |
| 发票号 |
BeiZhu | text |
| 备注 |
DingGouFou | text |
| 订购否 |
DeleteFou | nchar(50) |
| 删除否 |
YouXiaoFou | nchar(50) |
| 有效否 |
GengXinShiJian | nchar(50) |
| 更新时间 |
表3:采购退货单单(pw_CaIGouTuiHuoDan)
列名 | 数据类型 | 主键/外键 | 说明 |
CaiGouTuiHuoDanID | int | 主键 | 采购退货单ID |
LuRuYuanID | int | 外键 | 录入员ID |
CaiGouDanID | int | 外键 | 采购单ID |
TuiHuoFangShiID | int | 外键 | 退货方式ID |
KuaiDiID | int | 外键 | 快递ID |
KuaiDiDanHao | nchar(50) |
| 快递编号 |
TuiKuanZhuangTai | nchar(50) |
| 退款状态 |
DanJuZhuangTai | nchar(50) |
| 单据状态 |
CaiGouTuiHuoDanBianHao | nchar(50) |
| 采购退货单编号 |
JinE | decimal(18, 2) |
| 金额 |
TuiHuoRiQi | Date |
| 退货日期 |
GengXinRiQi | Date |
| 更新日期 |
ChuKuFou | bit |
| 出库否 |
YouXiaoFou | bit |
| 有效否 |
DeleteFou | bit |
| 删除否 |
BeiZhu | text |
| 备注 |
表4:销售单(pw_XiaoShouDan)
列名 | 数据类型 | 主键/外键 | 说明 |
XiaoShouDanID | int | 主键 | 销售单ID |
LuRuYuanID | int | 外键 | 录入员ID |
FuKuanZhuangTaiID | int | 外键 | 付款状态ID |
KeHuID | int | 外键 | 客户ID |
PinPaiID | int | 外键 | 品牌ID |
ZhongBiaoKuanShiID | int | 外键 | 钟表款式ID |
XiaoShouLeiXingID | int | 外键 | 销售类型ID |
XiaoShouFangShiID | int | 外键 | 销售方式ID |
SongHuoFangShiID | int | 外键 | 送货方式ID |
FuKuanFangShiID | int | 外键 | 付款方式ID |
FuKuanLeiXingID | int | 外键 | 付款类型ID |
XiaoShouDanBianHao | nchar(10) |
| 销售单编号 |
JieZhangBianHao | nchar(10) |
| 结账编号 |
DiKeHao | nchar(10) |
| 底壳号 |
XiaoShouRiQi | date |
| 销售日期 |
YingShouJinE | decimal(18, 2) |
| 应收金额 |
ShiShouJinE | decimal(18, 2) |
| 实收金额 |
GengXinShiJian | datetime |
| 更新时间 |
BeiZhu | nchar(50) |
| 备注 |
BaoFeiYuanYin | nchar(50) |
| 报废原因 |
YouXiaoFou | Bit |
| 有效否 |
DeleteFou | Bit |
| 删除否 |
表5:销售售后单(pw_XiaoShouShouHouDan)
列名 | 数据类型 | 主键/外键 | 说明 |
XiaoShouShouHouDanID | int | 主键 | 销售售后单ID |
LuRuYuanID | int | 外键 | 录入员ID |
XiaoShouDanID | int | 外键 | 销售单ID |
XiaoShouShouHouLeiXingID | int | 外键 | 售后类型ID |
XiaoShouShouHouDanBianHao | nchar(50) |
| 售后单编号 |
RiQi | date |
| 日期 |
JinE | decimal(18, 2) |
| 金额 |
GengXinShiJian | datetime |
| 更新时间 |
YouXiaoFou | bit |
| 有效否 |
DeleteFou | bit |
| 删除否 |
BeiZhu | nchar(50) |
| 备注 |
第二步:控制器(Controllers)
Linq语法:
/// <summary>
/// 营业额统计
/// </summary>
/// <param name="Year">查询的年份</param>
/// <param name="Month">查询的月份</param>
/// <returns></returns>
public ActionResult YingYeETongJi(string Year, string Month)
{
var MarketMoney = 0;//销售金额
var ServiceMoney = 0;//维修结账金额
var PurchaseMoney = 0;//采购金额
var PurchaseReturnOfGoodsMoney = 0;//采购退货金额
var MarketReturnOfGoodsMoney = 0;//销售退货金额
var MarketExChangeMoneyMoney = 0;//销售换货金额
var MarketGuaranteeMoney = 0;//销售保修金额
var DateTimeData = Year;
var Count = "12";
var dtData = "";
var dateCount = "";
DataTable dt = new DataTable();
dt.Columns.Add("d", typeof(string));
dt.Columns.Add("visits", typeof(string));
//判断是否查询月份
if (Month != "" && Convert.ToInt32(Month.ToString().Trim()) > 0)
{
if (Convert.ToInt32(Month) < 10)
{
Month = "0" + Month;
}
DateTimeData = DateTimeData + "-" + Month;
Count = DateTime.DaysInMonth(Convert.ToInt32(Year), Convert.ToInt32(Month)).ToString();
}
//销售
var Market = (from dtMarket in myMdl.pw_XiaoShouDan
where dtMarket.YouXiaoFou == true && dtMarket.FuKuanZhuangTaiID == 44
select dtMarket).AsEnumerable().Select(n => new
{
n.ShiShouJinE,
XiaoShouRiQi = n.XiaoShouRiQi.Value.ToString("yyyy-MM-dd")
});
//维修结账
var Service = (from dtService in myMdl.pw_JieZhangQuBiao
where dtService.YouXiaoFou == true
select dtService).AsEnumerable().Select(n => new
{
n.ShiShouJinE,
FuKuanRiQi = n.FuKuanRiQi.Value.ToString("yyyy-MM-dd")
});
//采购
var Purchase = (from dtPurchase in myMdl.pw_CaiGouDan
where dtPurchase.YouXiaoFou == true
select dtPurchase).AsEnumerable().Select(n => new
{
n.JinE,
DaoHuoRiQi = n.DaoHuoRiQi.Value.ToString("yyyy-MM-dd")
});
//采购退货
var PurchaseReturnOfGoods = (from dtPurchaseReturnOfGoods in myMdl.pw_CaiGouTuiHuoDan
where dtPurchaseReturnOfGoods.TuiKuanZhuangTai == "已退款" && dtPurchaseReturnOfGoods.YouXiaoFou == true
select dtPurchaseReturnOfGoods).AsEnumerable().Select(n => new
{
n.JinE,
GengXinRiQi = n.GengXinRiQi.Value.ToString("yyyy-MM-dd")
});
//销售退货单
var MarketReturnOfGoods = (from dtMarketReturnOfGoods in myMdl.pw_XiaoShouShouHouDan
where dtMarketReturnOfGoods.XiaoShouShouHouLeiXingID == 48 && dtMarketReturnOfGoods.YouXiaoFou == true
select dtMarketReturnOfGoods).AsEnumerable().Select(n => new
{
n.JinE,
RiQi = n.RiQi.Value.ToString("yyyy-MM-dd")
});
//销售换货单
var MarketExChangeMoney = (from dtMarketExChangeMoney in myMdl.pw_XiaoShouShouHouDan
where dtMarketExChangeMoney.XiaoShouShouHouLeiXingID == 49 && dtMarketExChangeMoney.YouXiaoFou == true
select dtMarketExChangeMoney).AsEnumerable().Select(n => new
{
n.JinE,
RiQi = n.RiQi.Value.ToString("yyyy-MM-dd")
});
//销售保修
var MarketGuarantee = (from dtMarketGuarantee in myMdl.pw_XiaoShouShouHouDan
where dtMarketGuarantee.XiaoShouShouHouLeiXingID == 50 && dtMarketGuarantee.YouXiaoFou == true
select dtMarketGuarantee).AsEnumerable().Select(n => new
{
n.JinE,
RiQi = n.RiQi.Value.ToString("yyyy-MM-dd")
});
for (int i = 0; i < Convert.ToInt32(Count) + 1; i++)
{
if (Convert.ToInt32(i) < 10)
{
dateCount = "0" + (i + 1).ToString();
}
else
{
dateCount = i.ToString();
}
dtData = (DateTimeData + "-" + dateCount).ToString().Trim();
MarketMoney = 0;//销售金额
ServiceMoney = 0;//维修结账金额
PurchaseMoney = 0;//采购金额
PurchaseReturnOfGoodsMoney = 0;//采购退货金额
MarketReturnOfGoodsMoney = 0;//销售退货金额
MarketExChangeMoneyMoney = 0;//销售换货金额
MarketGuaranteeMoney = 0;//销售保修金额
DataRow dtRow = dt.Rows.Add();
//销售
Market = Market.Where(n => n.XiaoShouRiQi.Contains(dtData));
foreach (var item in Market)
{
MarketMoney += Convert.ToInt32(item.ShiShouJinE);
}
//维修结账
Service = Service.Where(n => n.FuKuanRiQi.Contains(dtData));
foreach (var item in Service)
{
ServiceMoney += Convert.ToInt32(item.ShiShouJinE);
}
//采购
Purchase = Purchase.Where(n => n.DaoHuoRiQi.Contains(dtData));
foreach (var item in Purchase)
{
PurchaseMoney += Convert.ToInt32(item.JinE);
}
//采购退货
PurchaseReturnOfGoods = PurchaseReturnOfGoods.Where(n => n.GengXinRiQi.Contains(dtData));
foreach (var item in PurchaseReturnOfGoods)
{
PurchaseReturnOfGoodsMoney += Convert.ToInt32(item.JinE);
}
//销售退货单
MarketReturnOfGoods = MarketReturnOfGoods.Where(n => n.RiQi.Contains(dtData));
foreach (var item in MarketReturnOfGoods)
{
MarketReturnOfGoodsMoney += Convert.ToInt32(item.JinE);
}
//销售换货单
MarketExChangeMoney = MarketExChangeMoney.Where(n => n.RiQi.Contains(dtData));
foreach (var item in MarketExChangeMoney)
{
MarketExChangeMoneyMoney += Convert.ToInt32(item.JinE);
}
//销售保修
MarketGuarantee = MarketGuarantee.Where(n => n.RiQi.Contains(dtData));
foreach (var item in MarketGuarantee)
{
MarketGuaranteeMoney += Convert.ToInt32(item.JinE);
}
var Money = ServiceMoney + MarketMoney - PurchaseMoney + PurchaseReturnOfGoodsMoney - MarketReturnOfGoodsMoney + MarketExChangeMoneyMoney + MarketGuaranteeMoney;
dtRow["d"] = dtData;
dtRow["visits"] = Money;
}
List<Dictionary<string, object>> Listreturn = ConvertHelper.DtToList(dt);
return Json(Listreturn, JsonRequestBehavior.AllowGet);
}
第三步、视图层(views)
<div id="_revealloading" class="reveal-loadding" style="display:block">
<span class="loading" style="display:block">
</span>
</div>
<br />
<table>
<tr>
<td style="width:50px"></td>
<td><input type="text" id="Year" maxlength="4" οnkeyup="value=value.replace(/[^\d]/g,''); inputFullYear(this);" onbeforepaste="clipboardData.setData('text',clipboardData.getData('text').replace(/[^\d]/g,''))"/></td>
<td><input type="text" id="Month" maxlength="2" οnkeyup="value=value.replace(/[^\d]/g,''); inputMonth(this);" onbeforepaste="clipboardData.setData('text',clipboardData.getData('text').replace(/[^\d]/g,''))"/></td>
<td><input type="button" class="goodButton1" οnclick="onClickLoadData()" style="width:100px;height:26px;border:0" value="查询" /></td>
</tr>
</table>
<br />
<center>
<div style="width:1130px">
<div class="row">
<div class="col-lg-12">
<div class="panel panel-primary">
<div class="panel-heading">
<h3 class="panel-title"><i class="fa fa-bar-chart-o"></i>营业额统计</h3>
</div>
<div class="panel-body">
<div id="morris-chart-area"></div>
</div>
</div>
</div>
</div>
</div>
</center>
jQuery代码:
var data = undefined;
var nowDate = new Date();//实例化date
$(document).ready(function () {
$("#Month").val(nowDate.getMonth() + 1)//绑定当前月份
$("#Year").val(nowDate.getFullYear()); //绑定当前年份
onClickLoadData();
$('#Year').keydown(function (e) {
if (e.keyCode == 13) {//键盘回车事件,触发查询功能
onClickLoadData()
}
});
$('#Month').keydown(function (e) {
if (e.keyCode == 13) {//键盘回车事件,触发查询功能
onClickLoadData()
}
});
});
function onClickLoadData() {
//打开进度条
$("#_revealloading").css("display", "block");
//通过ajax进行查询
$.ajax({
type: 'post',
url: '/ShuJuBiaoGe/YingYeETongJi?Year=' + $("#Year").val() + '&Month=' + $("#Month").val(),
success: function (requiredata) {
//查询成功,返回数据
data = requiredata;
document.getElementById('morris-chart-area').innerHTML = ""; //清空插件的数据,否则数据会出现重复现象,图像重叠
//为图表插件绑定数据
Morris.Area({
element: 'morris-chart-area',
data: data,
xkey: 'd',
ykeys: ['visits'],
labels: ['金额(RMB)'],
smooth: false
});
//关闭进度条
$("#_revealloading").css("display", "none");
}
});
}
function inputMonth(event) {
if (event.value>12) {//限制月份不能输入超出12的数
alert("月份数不能大于12!");
$("#Month").val(nowDate.getMonth() + 1);
}
}
function inputFullYear(event) {
if (event.value > nowDate.getFullYear()) {//限制年份输入不能超出当前时间年份
alert("年份数不能大于当前时间年份!");
$("#Year").val(nowDate.getFullYear());
}
}