如何利用极致业务基础平台构建一个通用企业ERP之十六物料进出明细报表的设计
1.做ERP经常要做很多分析报表，因为老板喜欢看的就是报表，利用极致业务基础平台做报表很简单，只要会写sql即可。
本篇我们介绍一个物料进出明细报表的设计，设计报表我们先设计过滤界面，如下所示：
在该报表过滤界面我们主要是为了报表的sql中的where部分的条件值的获取。
代码如下：
/// <summary>
/// 进销存过滤报表基类界面
/// </summary>
class SCMFilter : Jeez.Runtime.Base.General.JeezFilterUI
{
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtOrg;//组织机构
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtWHFrom;//开始仓库
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtWHTo;//结束仓库
private static int orgID = 0;
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtMatGroupFrom;//开始物料组
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtMatGroupTo;//结束物料组
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtMatFrom;//开始物料
private Jeez.Control.JeezUltraTextBox.JeezUltraTextBox txtMatTo;//结束物料
private Jeez.Control.JeezWinRadioButton.JeezWinRadioButton rdCheck;//查询类型，只查审核还是未审核还是全部
private Jeez.Control.JeezUltraCalendarCombo.JeezUltraCalendarCombo dtStart;//开始日期
private Jeez.Control.JeezUltraCalendarCombo.JeezUltraCalendarCombo dtEnd;//结束日期
private DateTime _dtStartDate = Jeez.Core.Toolkit.GetDateTimeDefaultValue();//获取当前服务器时间
public DateTime dtStartDate
{
get { return _dtStartDate; }
set { _dtStartDate = value; }
}
private DateTime _dtEndDate = Jeez.Core.Toolkit.GetDateTimeDefaultValue();
public DateTime dtEndDate
{
get { return _dtEndDate; }
set { _dtEndDate = value; }
}
private string _WHFromNumber;
private string _WHToNumber;
private string _OrgNumber;
private string _MatFromNumber;
private string _MatToNumber;
private string _MatGroupFromNumber;
private string _MatGroupToNumber;
private int _Check;
private bool _NoBalShow = false;
public string WHFromNumber
{
get { return _WHFromNumber; }
}
public string WHToNumber
{
get { return _WHFromNumber; }
}
public string OrgNumber
{
get { return _OrgNumber; }
}
public string MatFromNumber
{
get { return _MatFromNumber; }
}
public string MatToNumber
{
get { return _MatToNumber; }
}
public string MatGroupFromNumber
{
get { return _MatGroupFromNumber; }
}
public string MatGroupToNumber
{
get { return _MatGroupToNumber; }
}
public int Check
{
get { return _Check; }
}
/// <summary>
/// 加载控件定义事件
/// </summary>
public override void LoadUI()
{
base.LoadUI();
txtOrg = base.GetControlByName("txtOrg") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtWHFrom = base.GetControlByName("txtWHouse") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtWHTo = base.GetControlByName("txtWToHouse") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtMatGroupFrom = base.GetControlByName("txMGFrom") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtMatGroupTo = base.GetControlByName("txMGTo") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtMatFrom = base.GetControlByName("txMFrom") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
txtMatTo = base.GetControlByName("txMTo") as Jeez.Control.JeezUltraTextBox.JeezUltraTextBox;
dtStart = base.GetControlByName("dtStart") as Jeez.Control.JeezUltraCalendarCombo.JeezUltraCalendarCombo;
dtEnd = base.GetControlByName("dtEnd") as Jeez.Control.JeezUltraCalendarCombo.JeezUltraCalendarCombo;
rdCheck = base.GetControlByName("rdType") as Jeez.Control.JeezWinRadioButton.JeezWinRadioButton;
//隐藏平台过滤引擎其他页签
this.tabFilter.Tabs[1].Visible = false;
this.tabFilter.Tabs[2].Visible = false;
this.tabFilter.Tabs[3].Visible = false;
txtOrg.ValueChanged += new EventHandler(txtOrg_TagChanged);
dtStart.ValueChanged += new EventHandler(dtStart_ValueChanged);
}
void dtStart_ValueChanged(object sender, EventArgs e)
{
DateTime dt = dtStart.PropertyPage.Datetime;
dtEnd.PropertyPage.Datetime = dt.AddDays(31);
}
protected override void JeezFilterUI_Load(object sender, EventArgs e)
{
base.JeezFilterUI_Load(sender, e);
////初始化组织机构默认值为当前用户组织
////填充当前用户组织机构信息
EntityObject eoEmp = EntityObjectFactory.GetInstance(objContext, EntityIDEnum.Employee).FindFirst("SysUserID={0}", objContext.UserID);
if (eoEmp != null && txtOrg != null)
{
txtOrg.Tag = eoEmp.GetProperty("Organizationid");
txtOrg.VALUE = eoEmp.GetRelatedObject("OrganizationID").GetProperty("Number").ToString();
txtWHFrom.AutoDropdownListFilter = string.Format("OrganizationID={0}", (int)eoEmp.GetRelatedObject("OrganizationID").PrimaryKeyValue);
txtWHTo.AutoDropdownListFilter = string.Format("OrganizationID={0}", (int)eoEmp.GetRelatedObject("OrganizationID").PrimaryKeyValue);
orgID = (int)eoEmp.GetProperty("OrganizationId");
}
}
/// <summary>
/// 实现选择了组织机构后，仓库只能输入该组织的仓库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void txtOrg_TagChanged(object sender, EventArgs e)
{
txtWHFrom.AutoDropdownListFilter = "";
txtWHTo.AutoDropdownListFilter = "";
if (txtOrg.Tag != null && (string)txtOrg.PropertyPage.Value != "")
{
EntityObject eoOrg = EntityObjectFactory.GetInstance(objContext, EntityIDEnum.Organization).FindFirst("Number={0}", (string)txtOrg.PropertyPage.Value);
if (eoOrg != null)
{
txtWHFrom.AutoDropdownListFilter = string.Format("OrganizationID={0}", (int)eoOrg.PrimaryKeyValue);
txtWHTo.AutoDropdownListFilter = string.Format("OrganizationID={0}", (int)eoOrg.PrimaryKeyValue);
orgID = (int)eoOrg.PrimaryKeyValue;
}
}
}
/// <summary>
/// 弹出窗体过滤
/// </summary>
/// <param name="sender"></param>
/// <param name="EntityID"></param>
/// <param name="RunTimeFilter"></param>
protected override void ShowUISelectedForm(object sender, int EntityID, ArrayList RunTimeFilter)
{
if ((sender.Equals(txtWHFrom) || sender.Equals(txtWHTo)) && orgID!= 0)
{
Jeez.Runtime.Base.General.RunTimefilterDefine fd;
StringBuilder sb = new StringBuilder();
sb.Append(" (Select o.ID From jzOrganization o left join jzOrganization_Hiberarchy h on o.ID=h.ChildID Where h.ParentID=");
sb.Append(orgID.ToString());
sb.Append(" or o.ID =");
sb.Append(orgID.ToString());
sb.Append(") ");
fd = new Jeez.Runtime.Base.General.RunTimefilterDefine("[8216]", 0, 0, 9, 0, sb.ToString());
RunTimeFilter = new ArrayList();
RunTimeFilter.Add(fd);
base.ShowUISelectedForm(sender, EntityIDEnum.WareHouse, RunTimeFilter);
}
else
{
base.ShowUISelectedForm(sender, EntityID, RunTimeFilter);
}
}
protected override void cmdOK_Click(object sender, EventArgs e)
{
_WHFromNumber = txtWHFrom.VALUE;
_WHToNumber = txtWHTo.VALUE;
_MatGroupFromNumber = txtMatGroupFrom.VALUE;
_MatGroupToNumber = txtMatGroupTo.VALUE;
_MatFromNumber = txtMatFrom.VALUE;
_MatToNumber = txtMatTo.VALUE;
_Check = rdCheck.CheckedIndex;
_OrgNumber = txtOrg.VALUE;
_dtStartDate = dtStart.Datetime;
_dtEndDate = dtEnd.Datetime;
base.cmdOK_Click(sender, e);
}
}
报表代码如下，主要是获取过滤界面的值，然后利用这些值来构建你要做的报表的过滤条件。
代码如下：大家不要看代码这么长，其实唯一要变的就是这个sql语句及相关的where部分
/// <summary>
/// 物料进出明细报表
/// </summary>
class MatInAndOutDetailRpt : Jeez.MulEntityInput.BasefrmList
{
private string _OrgNumber;
private string _WHFromNumber;
private string _WHToNumber;
private string _MatGroupFromNumber;
private string _MatGroupToNumber;
private string _MatFromNumber;
private string _MatToNumber;
public string strDateTitle = "";
private string strTitle = "";
private int _Check;
private DateTime _dtStartDate = Jeez.Core.Toolkit.GetDateTimeDefaultValue();
private DateTime _dtEndDate = Jeez.Core.Toolkit.GetDateTimeDefaultValue();
/// <summary>
/// 调用公共过滤界面
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
protected override JeezFilterUI OpenFilterUIByShowDialog(Jeez.DataEntity.UI.JeezUIMainContainer o)
{
try
{
JeezFilterUI f = base.OpenFilterUIByShowDialog(o);
SCMFilter bf = f as SCMFilter;
if (bf != null)
{
_OrgNumber = bf.OrgNumber;
_WHFromNumber = bf.WHFromNumber;
_WHToNumber = bf.WHToNumber;
_MatGroupFromNumber = bf.MatGroupFromNumber;
_MatGroupToNumber = bf.MatGroupToNumber;
_MatFromNumber = bf.MatFromNumber;
_MatToNumber = bf.MatToNumber;
_Check = bf.Check;
_dtStartDate = bf.dtStartDate;
_dtEndDate = bf.dtEndDate;
}
return f;
}
catch (Exception ex)
{
return null;
}
}
public override void RefreshMe()
{
this.SQLTableName = "#t" + Guid.NewGuid().ToString().Replace("-", "");
this.StrSQL = GetSQL();
SetTitle();
base.RefreshMe();
}
void SetTitle()
{
strTitle = string.Format("查询时间范围：{0} ", strDateTitle);
}
protected override void picTitle_Paint(object sender, System.Windows.Forms.PaintEventArgs e)
{
base.picTitle_Paint(sender, e);
if (this.strTitle != "")
{
Font tfont = new Font(this.lblInfo.Font.FontFamily.Name, 10);
int iLen = Convert.ToInt32(e.Graphics.MeasureString(this.strTitle, tfont).Width);
e.Graphics.DrawString(strTitle, new System.Drawing.Font("宋体", 10F, System.Drawing.FontStyle.Bold), SolidBrush, this.Width - iLen - 500, this.lblCapion.Location.Y + 18);
}
}
private string GetSQL()
{
string TempTableNext1 = "#1jz" + System.Guid.NewGuid().ToString().Replace("-", "");
string TempTableNext2 = "#2jz" + System.Guid.NewGuid().ToString().Replace("-", "");
string TempTableNext3 = "#3jz" + System.Guid.NewGuid().ToString().Replace("-", "");
string TempTableNext4 = "#4jz" + System.Guid.NewGuid().ToString().Replace("-", "");
string TempTableNext5 = "#5jz" + System.Guid.NewGuid().ToString().Replace("-", "");
StringBuilder strUpdateBeginData = new StringBuilder();
System.Text.StringBuilder strWH = new StringBuilder(" ");
if (_OrgNumber != string.Empty)
{
strWH.Append(string.Format(" and b.Number='{0}'", _OrgNumber));
}
if (_WHFromNumber != string.Empty)
{
strWH.Append(string.Format(" and c.Number>='{0}'", _WHFromNumber));
}
if (_WHToNumber != string.Empty)
{
strWH.Append(string.Format(" and c.Number<='{0}'", _WHToNumber));
}
if (_MatGroupFromNumber != string.Empty)
{
strWH.Append(string.Format(" And g.Number>='{0}'", _MatGroupFromNumber));
}
if (_MatGroupToNumber != string.Empty)
{
strWH.Append(string.Format(" And g.Number<='{0}'", _MatGroupToNumber));
}
if (_MatFromNumber != string.Empty)
{
strWH.Append(string.Format(" And d.Number>='{0}'", _MatFromNumber));
}
if (_MatToNumber != string.Empty)
{
strWH.Append(string.Format(" And d.Number<='{0}'", _MatToNumber));
}
System.Text.StringBuilder strRelate = new StringBuilder();
System.Text.StringBuilder strWH1 = new StringBuilder(" ");
if (_Check == 1)
{
strWH1.Append(string.Format(" and p.CheckID=0"));
}
else if (_Check == 0)
{
strWH1.Append(string.Format(" and p.CheckID>0"));
}
else
{
}
strDateTitle = "";
if (_dtStartDate != Jeez.Core.Toolkit.GetDateTimeDefaultValue())
{
strDateTitle += string.Format(" 从{0}", _dtStartDate.Date.Year.ToString() + "-" + _dtStartDate.Date.Month.ToString() + "-" + _dtStartDate.Date.Day.ToString());
strWH1.Append(string.Format(" and p.Date>='{0}'", _dtStartDate));
}
if (_dtEndDate != Jeez.Core.Toolkit.GetDateTimeDefaultValue())
{
strDateTitle += string.Format(" 到{0}", _dtEndDate.Date.Year.ToString() + "-" + _dtEndDate.Date.Month.ToString() + "-" + _dtEndDate.Date.Day.ToString());
strWH1.Append(string.Format(" and p.Date<'{0}'", _dtEndDate.AddDays(1).Date));
}
return string.Format(@"
select '1' as Flag,a.*
into {0}
from (
---采购入库单
select '采购入库单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzCaigouRukuDetail t
inner join jzCaigouRuku p On p.ID=t.CaigouRukuID
inner join jzOrganization o on p.OrganizationID=o.ID
where o.ID>0 {1}
union all
--仓库调拨
select '仓库调拨单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.RWareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzDiaoboDetail t
inner join jzDiaoboBill p On p.ID=t.DiaoboBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>0 {1}
union all
--盘点单
select '盘点单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzPandianDetail t
inner join jzPandianBill p On p.ID=t.PandianBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where Many>0 {1}
union all
select '成品入库单' as BillType, p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzProductRukuDetail t
inner join jzProductRuku p On p.ID=t.ProductRukuID
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>=0 {1}
union all
select '销售退货单' as BillType,p.EmployeeID, P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzSaleRukuDetail t
inner join jzSaleRukuBill p On p.ID=t.SaleRukuBillId
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>=0 {1}
) a
--出库统计
select '2' as Flag,t.*
into {2}
from (
--销售出库单
select '销售出库单' as BillType,p.EmployeeID, P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzSaleChukuDetail t
inner join jzSaleChukuBill p On p.ID=t.SaleChukuBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>=0 {1}
union all
--领料单
select '内部领料单' as BillType,p.EmployeeID, P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzLingliaoDetail t
inner join jzLingliao p On p.ID=t.LingliaoID
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>=0 {1}
union all
--仓库调拨
select '仓库调拨单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzDiaoboDetail t
inner join jzDiaoboBill p On p.ID=t.DiaoboBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where p.ID>0 {1}
union all
--盘点单
select '盘点单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzPandianDetail t
inner join jzPandianBill p On p.ID=t.PandianBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where Many<=0 {1}
union all
--采购退货单
select '采购退货单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzCaigouChukuDetail t
inner join jzCaigouChuku p On p.ID=t.CaigouChukuID
inner join jzOrganization o on p.OrganizationID=o.ID
where o.ID>0 {1}
--报废单
union all
select '报废单' as BillType,p.EmployeeID,P.Date,P.BillNo,o.ID as OrgID,t.WareHouseID,t.MatID,t.JiliangdanweiID,t.Many as InQty,t.RAmount as InAmount
from jzBaofeiDetail t
inner join jzBaofeiBill p On p.ID=t.BaofeiBillID
inner join jzOrganization o on p.OrganizationID=o.ID
where o.ID>0 {1}
) t
--合并统计结果
select a.BillType,a.BillNo,a.EmployeeID,a.Date,a.OrgID,a.WareHouseID,a.MatID,a.JiliangdanweiID,
case when Flag='1' then InQty else 0 end as InQty,
case when Flag='1' then InAmount else 0 end as InAmount,
case when Flag='2' then InQty else 0 end as OutQty,
case when Flag='2' then InAmount else 0 end as OutAmount
into {3}
from
(
select * from {0}
union all
select * from {2}
) a
select a.BillType,a.BillNO,emp.Name as EmpName,a.Date,b.Name as OrgName,c.Number+'-'+c.Name as WareName ,d.Number+'-'+d.Name as MatName,d.Standard+'/'+d.Model as MatStand,e.Name as UnitName ,
a.InQty,a.InAmount,Case when a.InQty<>0 then Round(a.InAmount/a.InQty,4) else 0 end as InDanjia,
a.OutQty,a.OutAmount,Case when a.OutQty<>0 then Round(a.OutAmount/a.OutQty,4) else 0 end as OutDanjia
,g.Number+'-'+g.Name as MatGroupName
into " + this.SQLTableName + @" from {3} a
left join jzOrganization b on b.ID=a.OrgID
left join jzWareHouse c on c.ID=a.WareHouseID
left join jzMat d on d.ID=a.MatID
left join jzMatGroup g on g.ID=d.MatGroupID
left join jzJiliangdanwei e on e.ID=a.JiliangdanweiID
left join jzEmployee emp on emp.ID=a.EmployeeID
where b.ID>0 {4}
order by a.Date desc
drop table {0}
drop table {2}
drop table {3}
", TempTableNext1, strWH1, TempTableNext2, TempTableNext3, strWH);
}
}
一点经验是，写报表我们一般都是在sql查询分析器先写好，大多数利用临时表，只要最后一个sql语句保持查询出的结果，和我们报表上的Grid列名称对应上即可。
报表如上但是利用我们的套打引擎却可以做很多老板想要的分组结果，套打如下：
这样报表虽然是明细，但是分组统计小计都可以轻松打印出来传给boss查阅!
极致平台开发十大特点：
1. 一个数据库下可以同时进行N套业务系统开发，开发出来的产品可以根据您客户的需要按模块界面组发布，客户想要啥模块就可以给啥模块。而且一个数据库下开发所有功能，当客户需要从你的人力资源增加客户关系管理模块的时候，你只要做个升级包就可以了。解决企业多个业务系统信息孤岛问题。
2. 智能升级功能，当客户从A模块增加B模块的时候，您只需要做一个升级包即可，给客户升级后，客户原来录入的数据不会有影响，而且所有客户端都是智能感应智能升级，大大节省您的部署成本。
3. 工作流套打报表均可以运行时候自定义，比如费用报销单，您100家客户就有一百种费用报销的流程，套打的格式，用我们平台您只需要设计好这个费用报销单，至于哪个客户走什么流程，完全可以让客户自己去定义，而不需要像传统开发那样，提前在开发中设置好，100个客户就维护100套代码。套打也是如此。
4. 支持数据授权，当您开发多组织架构的系统的时候，我们只要业务单据引用组织机构即可，然后组织机构支持数据授权，这样就可以不需要编写任何一行代码就可以做到，组织与组织之间数据彼此隔离，我想给哪个用户看哪个组织的数据只要给这个用户这个组织的数据权限即可。
5. 支持字段授权，对于一些表的核心字段对用户进行屏蔽直接利用我们平台的字段授权功能即可，比如职员薪酬字段进行字段授权，让有的用户在看职员信息的时候，自动隐藏薪酬的数据。这也是无需编写任何一行代码。
6. 单据界面自动生成，我们开发的时候只要设计好实体，也就是传统开发所说的表结构即可，还可以设置哪些字段是必录，可见，不允许重复，在界面生成的时候，会自动生成一个界面，而且这个界面的增删改查是无需写一行代码的，您只要对您特有业务逻辑编码即可，相对传统开发，你代码量可以节省2/3,开发周期缩短2/3
7.一次开发同时具有单机局域互联网三个版本，客户想要单机就给单机想要互联网版就给互联网版。
8．强大的公式引擎，让您可以灵活设计计算类的项目，比如工资，预算。
9．包含强大的各种控件，比如文本控件支持F8调用，编码名称自动带出。Grid控件支持表头过滤，单元格融合，固定列，表格列，表格行各种公式汇总，复合表头，表格宽度可以自己随意调整，而且关闭后会自动记录之前的宽度。还支持表格列随意调整顺序。
10.平台内置很多基础功能，比如权限管理，用户角色管理，还有实施的一些导入导出工具都能帮助客户大大提高一个项目验收进度。
官网：www.jeez.com.cn
平台介绍：www.jeez.com.cn/jbf
平台下载地址：http://www.jeez.com.cn/upfiles/jbfsetuppro.rar
（下载即可有3个月免费试用）
联系电话：13826519021 18988763421 QQ:180315586 420977542 (加我注明极致软件即可)
平台销售经理:李先生
将互联网时代的管理软件做到极致!
==================================================================