一、页面设计
页面要求:
1.画面名:YMSRLGPK 智能仓库盘库明细表
2.查询条件:MAT_NO材料号,MTRL_TEXT材质,MAT_SPEC1外径,MAT_SPEC2壁厚,时间戳TIMESTAMP3.优化时间戳,把其查询条件设置成下拉,下拉项在tym00lg_kcmx_rcv查,并设置时间戳为必选项
4.立库表名表名:tym00lg_kcmx_rcv5.MES表名:tymsrlgc01c
6.功能:分查询和比对两个功能。根据查询条件查两表数据,勾选立库表查询结果放入MES表中进行比较,并将对比结果传入立库表中。勾选立库表数据时,MES表会查询对应材料号的数据。
![](https://i-blog.csdnimg.cn/direct/e431cc956eef487f95f1be6e26983b1c.png)
二、流程
1.注册画面
分别在EPESOBJ与EPEA01中注册前后台。如图2-1,2-2所示。
![](https://i-blog.csdnimg.cn/direct/8cc4b2e889d34fb4b7f0533ed3a2f8f5.png)
![](https://i-blog.csdnimg.cn/direct/2679bac674b1485384f89c3b39db1c5a.png)
2.配置功能号
在EPED54中配置YMSRLGPKLK_INQ和YMSRLGPKLK_INQ1,对应各自的表。
三、编码
1.前台
(1)load加载类:
在load中写入绑定数据的组件和功能号(需要依次对应),功能号不需要在后续查询代码中做编码操作来对应。
EF.Utility.SetGridColumn(new EF.EFDevGrid[] { this.efDevGrid1, this.efDevGrid3 }, new string[] { "YMSRLGPKLK_INQ", "YMSRLGPKLK_INQ1" }); ;
时间戳下拉值设置:
在load中中查表获取时间戳数据将其绑定在GridControl控件上,同时设置了时间戳列的下拉属性。
#region 时间戳
this.DS_ymsrlgpklk = EF.Utility.GetCustomGridValue(this.efDevGrid1);
this.DS_ymsrlgpklk.Tables[0].TableName = "TYMSRLGPKLK";
this.BS_ymsrlgpklk.DataMember = "TYMSRLGPKLK";
this.BS_ymsrlgpklk.DataSource = DS_ymsrlgpklk;
this.efDevGrid1.DataSource = BS_ymsrlgpklk;
EI.EIInfo inBlock = new EI.EIInfo();
this.sqlStr = " select DISTINCT timestamp from tym00lg_kcmx_rcv where 1=1 ";
EI.EIInfo outBlock = EF.Utility.ExecQueryPart(curr_part_name, sqlStr);
if (outBlock.GetSys().flag == 0 && outBlock.Tables[0].Rows.Count > 0)
{
this.timestamp.Properties.DataSource = outBlock.Tables[0];
BE2.Common.Utility.SetLookUpEditProperty(this.timestamp, outBlock.Tables[0], "TIMESTAMP","TIMESTAMP", true);
}
//DsCodeName = EF.Utility.ExecQueryPart(curr_part_name, this.sqlStr);
//this.timestamp.Properties.DataSource = DsCodeName.Tables[0];
//this.timestamp.Properties.DisplayMember = "TIMESTAMP";
//this.timestamp.Properties.Columns.Add(newDevExpress.XtraEditors.Controls.LookUpColumnInfo("TIMESTAMP", "时间戳"));
#endregion
在查询方法中传入时间戳:
inBlock.Tables[0].Rows[0]["TIMESTAMP"] = (this.timestamp.EditValue == null || this.timestamp.EditValue.ToString().Trim() == " " ? " " : this.timestamp.EditValue.ToString().Trim());
时间戳下拉结果如图3-1所示。
![](https://i-blog.csdnimg.cn/direct/c0d37c8117b744fcba8df4435c0c6e5f.png)
(2)查询
查询立库表信息(查询MES代码类似):
private void query_data(int recordFrom, int pageSize)
{
try
{
//MAT_NO材料号,MTRL_TEXT材质,MAT_SPEC1外径,MAT_SPEC2壁厚,时间戳TIMESTAMP
EI.EIInfo inBlock = new EI.EIInfo(); ;
inBlock.Tables[0].Columns.Add("MAT_NO");
inBlock.Tables[0].Columns.Add("MTRL_TEXT");
inBlock.Tables[0].Columns.Add("MAT_SPEC1");
inBlock.Tables[0].Columns.Add("MAT_SPEC2");
inBlock.Tables[0].Columns.Add("TIMESTAMP");
inBlock.Tables[0].Rows.Add();
inBlock.Tables[0].Rows[0]["MAT_NO"] = this.mat_no.Text.Trim();
inBlock.Tables[0].Rows[0]["MTRL_TEXT"] = this.mtrl_text.Text.Trim();
inBlock.Tables[0].Rows[0]["MAT_SPEC1"] = this.mat_spec1.Text.Trim();
inBlock.Tables[0].Rows[0]["MAT_SPEC2"] = this.mat_spec2.Text.Trim();
//下拉
inBlock.Tables[0].Rows[0]["TIMESTAMP"] = (this.timestamp.EditValue == null || this.timestamp.EditValue.ToString().Trim() == " " ? " " : this.timestamp.EditValue.ToString().Trim());
//分页信息
EF.DSUtility.PageInfoDataTable pageInfo = new EF.DSUtility.PageInfoDataTable();
pageInfo.AddPageInfoRow(recordFrom, pageSize);
inBlock.Tables.Add("PageInfo");
inBlock.Tables["PageInfo"].Merge(pageInfo);
//调用后台
EI.EIInfo outBlock = EI.EITuxedo.CallService("ymsrlgpklk_inq", inBlock);
//设置返回信息
if (outBlock.sys_info.flag == 0)
{
//将信息压入指定的GRID,
EF.Utility.SetCustomGridValue(efDevGrid1, outBlock, false);
//设置分页显示信息
YMUtility.DEV_grid_RecordCountMessage(efDevGrid1, currentPage, outBlock);
//列宽自动调整。
this.gridView1.BestFitColumns();
this.EFMsgInfo = string.Format("查询到[{0}]条记录。", outBlock.Tables[0].Rows.Count);
}
else
{
this.EFMsgInfo = outBlock.sys_info.msg; //获取异常信息
EF.EFMessageBox.Show(outBlock.sys_info.msg, EF.EF_Args.epEname, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
catch (Exception err)
{
EF.EFMessageBox.Show(err.Message, EF.EF_Args.epEname, MessageBoxButtons.OK, MessageBoxIcon.Information);
this.EFArgs.buttonStatusHold = true;
return;
}
}
(3) 比对
private void FormYMSRLGPKLK_EF_DO_F3(object sender, EF_Args e)
{
EI.EIInfo inblock = new EI.EIInfo();
//将选中立库数据传入后台
DataTable dt = this.efDevGrid1.GetSelectedDataRow();
inblock.Tables[0].Merge(dt);
// 调用后台services保存信息
EI.EIInfo outblock = EI.EIManager.Instance.CallService(curr_part_name, "ymsrlgpklk_compare", inblock);
if (outblock.sys_info.flag == 0)
{
FormYMSRLGPKLK_EF_DO_F2(null, null);
}
else
{
this.EFMsgInfo = outblock.sys_info.msg;
EF.EFMessageBox.Show(outblock.sys_info.msg, EF.EF_Args.epEname, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
this.EFMsgInfo = string.Format("比对成功!");
}
#endregion
在页面中选中立库表的信息执行比对,这里我们在调用后台一行打断点,查询勾选传入的数据,如图3-2所示:
![](https://i-blog.csdnimg.cn/direct/1ebc4852b96843cc8be6ec89ddbcf7ab.png)
(4)焦点行查询
我们点击查询到的立库表的一行,在MES表中会显示对应相同材料号的数据,这样方便作对比。
查询:
private void query()
{
DataRow row = gridView1.GetDataRow(gridView1.FocusedRowHandle);
string mat_no = row["MAT_NO"].ToString();
string mtrl_text = row["MTRL_TEXT"].ToString();
string mat_spec1 = row["MAT_SPEC1"].ToString();
string mat_spec2 = row["MAT_SPEC2"].ToString();
string timestamp = row["TIMESTAMP"].ToString();
//传入查询条件区
//MAT_NO材料号,MTRL_TEXT材质,MAT_SPEC1外径,MAT_SPEC2壁厚,时间戳TIMESTAMP
EI.EIInfo inBlock = new EI.EIInfo();
inBlock.Tables[0].Columns.Add("MAT_NO");
inBlock.Tables[0].Columns.Add("MTRL_TEXT");
inBlock.Tables[0].Columns.Add("MAT_SPEC1");
inBlock.Tables[0].Columns.Add("MAT_SPEC2");
inBlock.Tables[0].Columns.Add("TIMESTAMP");
inBlock.Tables[0].Rows.Add(mat_no);
inBlock.Tables[0].Rows.Add(mtrl_text);
inBlock.Tables[0].Rows.Add(mat_spec1);
inBlock.Tables[0].Rows.Add(mat_spec2);
inBlock.Tables[0].Rows.Add(timestamp);
用选中立库的材料号查对应的MES信息
EI.EIInfo outBlock = EI.EITuxedo.CallService("ymsrlgpklk_inq1", inBlock);
//设置返回信息
if (outBlock.sys_info.flag != 0)
{
this.EFMsgInfo = outBlock.sys_info.msg;
EF.EFMessageBox.Show(outBlock.sys_info.msg, EF.EF_Args.epEname, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
//将信息压入指定的GRID,
EF.Utility.SetCustomGridValue(efDevGrid3, outBlock, false);
//列宽自动调整。
this.gridView2.BestFitColumns();
this.EFMsgInfo = string.Format("查询到[{0}]条记录。", outBlock.Tables[0].Rows.Count);
}
}
调用query方法:
private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
{
try
{
if (gridView1.FocusedRowHandle >= 0)
{
query();
this.efDevGrid1.SetSelectedColumnChecked(this.gridView1.FocusedRowHandle, true);
}
}
catch (Exception ex)
{
EF.EFMessageBox.Show(ex.Message);
}
}
2.后台
(1)查询
传入前台数据后,设置时间戳为必选项。
if (bcls_rec->Tables[0].Columns.Contains("TIMESTAMP"))
{
timestamp = bcls_rec->Tables[0].Rows[0]["TIMESTAMP"].ToString().Trim();
Log::Debug("", __FUNCTION__, "传入参数timestamp = [{0}]", timestamp);
}
(2)比对
我们需要将比对结果放入MES表,所以我们先在系统表信息中配置REMARK_1作为存储列。
更新数据项-->应用到数据库开发环境。如图3-3显示。
![](https://i-blog.csdnimg.cn/direct/ac682a33023d4af998fd5986171c15dd.png)
#include "stdafx.h"
BM2F_ENTERACE(ymsrlgpklk_compare)
int f_ymsrlgpklk_compare(EIClass * bcls_rec, EIClass * bcls_ret, CDbConnection * conn)
{
//参数和分页参数
int doFlag = 0;
int recordFrom = 0; //起始页
int pageSize = 0; //每页记录数
int rowCount = 0;
//比对结果:notes
CString notes = "";
//立库表参数
CString mat_no = " ";
CString mtrl_text = " ";
CString mat_spec1 = " ";
CString mat_spec2 = " ";
CString timestamp = " ";
CString mat_spec3 = " ";
CString total_len = " ";
CString mat_theory_wt = " ";
CString mat_act_wt = " ";
CString mat_num = " ";
CString psc = " ";
CString whole_backlog_code = " ";
CString next_whole_backlog_code = " ";
CString next_whole_backlog_name;
CString steel_coils_no = " ";
CString slitting_no = " ";
CString sample_lot_no = " ";
CString prod_code = " ";
CString plan_no = " ";
CString sale_order_sub_no = "";
CString heat_no = " ";
CString bth_no = " ";
CString prod_cname = " ";
CString mtrl_no = " ";
CString sg_code = " ";
CString sg_sign = " ";
CString sg_std = " ";
CString stlm_no = " ";
CString location_no = " ";
/* 数据库操作类定义 */
CDbCommand cmd_inq(conn);
CString sqlstr = " ";
CString sqlstr_upd = "";
try
{
CModel tymsrlgc01c("TYMSRLGC01C");
rowCount = bcls_rec->Tables[0].Rows.get_Count();
Log::Trace("", "", "111");
// 获取前台传入参数
for (int i = 0; i < rowCount; i++)
{
notes = " ";
//将需要比较的列参赋值
mat_no = bcls_rec->Tables[0].Rows[i]["MAT_NO"];
mtrl_text = bcls_rec->Tables[0].Rows[i]["MTRL_TEXT"];
mat_spec1 = bcls_rec->Tables[0].Rows[i]["MAT_SPEC1"];
mat_spec2 = bcls_rec->Tables[0].Rows[i]["MAT_SPEC2"];
timestamp = bcls_rec->Tables[0].Rows[i]["TIMESTAMP"];
mat_spec3 = bcls_rec->Tables[0].Rows[i]["MAT_SPEC3"];
total_len = bcls_rec->Tables[0].Rows[i]["TOTAL_LEN"];
mat_theory_wt = bcls_rec->Tables[0].Rows[i]["MAT_THEORY_WT"];
mat_act_wt = bcls_rec->Tables[0].Rows[i]["MAT_ACT_WT"];
mat_num = bcls_rec->Tables[0].Rows[i]["MAT_NUM"];
psc = bcls_rec->Tables[0].Rows[i]["PSC"];
whole_backlog_code = bcls_rec->Tables[0].Rows[i]["WHOLE_BACKLOG_CODE"];
next_whole_backlog_code = bcls_rec->Tables[0].Rows[i]["NEXT_WHOLE_BACKLOG_CODE"];
next_whole_backlog_name = bcls_rec->Tables[0].Rows[i]["NEXT_WHOLE_BACKLOG_NAME"];
steel_coils_no = bcls_rec->Tables[0].Rows[i]["STEEL_COILS_NO"];
slitting_no = bcls_rec->Tables[0].Rows[i]["SLITTING_NO"];
sample_lot_no = bcls_rec->Tables[0].Rows[i]["SAMPLE_LOT_NO"];
prod_code = bcls_rec->Tables[0].Rows[i]["PROD_CODE"];
plan_no = bcls_rec->Tables[0].Rows[i]["PLAN_NO"];
sale_order_sub_no = bcls_rec->Tables[0].Rows[i]["SALE_ORDER_SUB_NO"];
heat_no = bcls_rec->Tables[0].Rows[i]["HEAT_NO"];
bth_no = bcls_rec->Tables[0].Rows[i]["BTH_NO"];
prod_cname = bcls_rec->Tables[0].Rows[i]["PROD_CNAME"];
mtrl_no = bcls_rec->Tables[0].Rows[i]["MTRL_NO"];
sg_code = bcls_rec->Tables[0].Rows[i]["SG_CODE"];
sg_sign = bcls_rec->Tables[0].Rows[i]["SG_SIGN"];
sg_std = bcls_rec->Tables[0].Rows[i]["SG_STD"];
stlm_no = bcls_rec->Tables[0].Rows[i]["STLM_NO"];
location_no = bcls_rec->Tables[0].Rows[i]["LOCATION_NO"];
tymsrlgc01c.Reset();
tymsrlgc01c["MAT_NO"] = mat_no;
tymsrlgc01c["STOCK_NO"] = "JLD07";
Log::Trace("", "", "222");
//比对逻辑
if (!tymsrlgc01c.Query("MAT_NO,STOCK_NO"))
{
notes += "MES材料不存在,";
}
else
{
//MES与立库材料号相同,开始对比其他列
if (tymsrlgc01c["MTRL_TEXT"].ToString() != mtrl_text){
//不同
notes += "材质不同,";
}
if (tymsrlgc01c["MAT_SPEC1"].ToString() != mat_spec1){
notes += "外径不同,";
}
if (tymsrlgc01c["MAT_SPEC2"].ToString() != mat_spec2){
notes += "壁厚不同,";
}
if (tymsrlgc01c["MAT_SPEC3"].ToString() != mat_spec3){
notes += "材料规格3不同,";
}
if (tymsrlgc01c["TOTAL_LEN"].ToString() != total_len){
notes += "总长度不同,";
}
if (tymsrlgc01c["MAT_THEORY_WT"].ToString() != mat_theory_wt){
notes += "材料理论重量不同,";
}
if (tymsrlgc01c["MAT_ACT_WT"].ToString() != mat_act_wt){
notes += "材料实际重量不同,";
}
if (tymsrlgc01c["MAT_NUM"].ToString() != mat_num){
notes += "材料件数(根数)不同,";
}
if (tymsrlgc01c["PSC"].ToString() != psc){
notes += "产品规范码不同,";
}
if (tymsrlgc01c["WHOLE_BACKLOG_CODE"].ToString() != whole_backlog_code){
notes += "全程工序代码不同,";
}
if (tymsrlgc01c["NEXT_WHOLE_BACKLOG_CODE"].ToString() != next_whole_backlog_code){
notes += "后全程工序代码不同,";
}
if (tymsrlgc01c["NEXT_WHOLE_BACKLOG_NAME"].ToString() != next_whole_backlog_name){
notes += "后全程工序名称不同,";
}
if (tymsrlgc01c["STEEL_COILS_NO"].ToString() != steel_coils_no){
notes += "钢卷号不同,";
}
if (tymsrlgc01c["SLITTING_NO"].ToString() != slitting_no){
notes += "分条号不同,";
}
if (tymsrlgc01c["SAMPLE_LOT_NO"].ToString() != sample_lot_no){
notes += "检批号不同,";
}
if (tymsrlgc01c["PROD_CODE"].ToString() != prod_code){
notes += "品名代码不同,";
}
if (tymsrlgc01c["PLAN_NO"].ToString() != plan_no){
notes += "计划号不同,";
}
if (tymsrlgc01c["SALE_ORDER_SUB_NO"].ToString() != sale_order_sub_no){
notes += "销售合同子项号不同,";
}
if (tymsrlgc01c["HEAT_NO"].ToString() != heat_no){
notes += "炉批号不同,";
}
if (tymsrlgc01c["BTH_NO"].ToString() != bth_no){
notes += "批号不同,";
}
if (tymsrlgc01c["PROD_CNAME"].ToString() != prod_cname){
notes += "品名中文不同,";
}
if (tymsrlgc01c["MTRL_NO"].ToString() != mtrl_no){
notes += "材质号不同,";
}
if (tymsrlgc01c["SG_CODE"].ToString() != sg_code){
notes += "牌号代码不同,";
}
if (tymsrlgc01c["SG_SIGN"].ToString() != sg_sign){
notes += "牌号(钢级)不同,";
}
if (tymsrlgc01c["SG_STD"].ToString() != sg_std){
notes += "标准不同,";
}
if (tymsrlgc01c["STLM_NO"].ToString() != stlm_no){
notes += "工艺卡号不同,";
}
if (tymsrlgc01c["LOCATION_NO"].ToString() != location_no){
notes += "库位号_1不同,";
}
}
Log::Trace("", "", "333");
//将notes赋值到立库表的比对结果中
if (notes.GetLength() > 2)
{
notes = notes.Substring(0, notes.GetLength() - 1);
}
// 构造SQL更新语句
sqlstr_upd = "UPDATE TYM00LG_KCMX_RCV SET REMARK_1 = :remarks WHERE mat_no =@mat_no";
// 设置命令文本
cmd_inq.SetCommandText(sqlstr_upd);
cmd_inq.Parameters.Set("mat_no", mat_no);
cmd_inq.Parameters.Set("remarks", notes);
// 执行更新操作
int rows_affected = cmd_inq.ExecuteNonQuery();
// 检查受影响的行数
if (rows_affected > 0) {
// 更新成功
Log::Trace("", __FUNCTION__, "Updated %d rows.", rows_affected);
}
else {
// 没有行被更新,可能是条件没有匹配到记录
Log::Error("", __FUNCTION__, "No rows updated.");
}
// 关闭命令对象,清理资源
Log::Trace("", __FUNCTION__, "sqlstr_upd = [{0}]", sqlstr_upd);
cmd_inq.Close();
}
}
catch (CDbException& ex)
{
CFormattable arguments[] = { ex.GetCode(), ex.GetMsg() };
CMessageFormat::Format(s.msg, "Database Error,sqlcode=[{0},{1}]", arguments, 2);
CString str = sqlstr + "\r\n" + ex.GetMsg();
strncpy(s.sysmsg, (const char*)str, sizeof(s.sysmsg) - 1);
s.flag = -1;
doFlag = -1;
}
catch (CApplicationException& ex)
{
s.flag = ex.GetCode();
doFlag = -1;
}
catch (CException& ex)
{
strcpy(s.msg, ex.GetMsg());
s.flag = ex.GetCode();
doFlag = -1;
}
return doFlag;
}