/// <summary>
/// 获取工单报工数据。
/// </summary>
/// <param name="dsParams">包含查询条件的数据集对象。</param>
/// <param name="pageNo">第几页。</param>
/// <param name="pageSize">每页行数。</param>
/// <param name="pages">总页数。</param>
/// <param name="records">总记录数。</param>
/// <returns>包含工单数据的数据集对象。</returns>
public DataSet GetWorkOrderWorkFor(DataSet dsParams,
int pageNo, int pageSize, out int pages, out int records)
{
DataSet dsReturn = new DataSet();
pages = 0;
records = 0;
try
{
string sqlString = @"SELECT (P.ACK_DATA + ' ' + P.ACK_TIME) AS ACKDATETIME,P.AUFNR,P.APLFL,P.GMNGA,P.XMNGA,
P.REPORTOR,P.SHIFT_NAME,P.CON_END,P.WERKS,L.LOCATION_NAME
FROM POR_WORK_ORDER_REPORT P,FMM_LOCATION L
WHERE L.LOCATION_KEY = P.ROOM_KEY";
if (pageNo > 0 && pageSize > 0)
{
//查询结果翻页数据
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, dsReturn, POR_WORK_ORDER_REPORT_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, dsReturn, new string[] { POR_WORK_ORDER_REPORT_FIELDS.DATABASE_TABLE_NAME });
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetWorkOrderWorkFor Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet GetByFourParameters(string partId, string partType, string partModule, string partClass, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sqlCommon = new StringBuilder();
try
{
sqlCommon.Append(@"SELECT PART_ID,PART_DESC,PART_TYPE,PART_MODULE,PART_CLASS,
(SELECT COUNT(1)
FROM POR_PART_BYPRODUCT b
WHERE b.MAIN_PART_NUMBER=A.PART_ID
AND b.IS_USED='Y') AS TOTOL
FROM dbo.POR_PART A WHERE 1=1 AND A.PART_STATUS = 1");
if (!string.IsNullOrEmpty(partId))
{
sqlCommon.AppendFormat(" AND PART_ID LIKE '{0}%'", partId);
}
if (!string.IsNullOrEmpty(partType))
{
sqlCommon.AppendFormat(" AND PART_TYPE LIKE '{0}%'", partType);
}
if (!string.IsNullOrEmpty(partModule))
{
sqlCommon.AppendFormat(" AND PART_MODULE LIKE '{0}%'", partModule);
}
if (!string.IsNullOrEmpty(partClass))
{
sqlCommon.AppendFormat(" AND PART_CLASS LIKE '{0}%'", partClass);
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sqlCommon.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
"POR_PART",
"PART_ID ASC");
pconfig.Pages = pages;
pconfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetByFourParameters Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet GetCheckListJobs(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string equipmentKey = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_EQUIPMENT_KEY].ToString() : string.Empty;
string checkListJobKey = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_KEY].ToString() : string.Empty;
string checkListJobName = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_NAME].ToString() : string.Empty;
string checkListJobState = reqDS.ExtendedProperties.ContainsKey(EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_STATE) ? reqDS.ExtendedProperties[EMS_CHECKLIST_JOBS_FIELDS.FIELD_CHECKLIST_JOB_STATE].ToString() : string.Empty;
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
string sqlString = @"SELECT CJ.*,
E.EQUIPMENT_NAME,
C.CHECKLIST_NAME,
CASE CJ.PM_TYPE
WHEN 'SCHEDULE' THEN
PS.SCHEDULE_NAME
WHEN 'CONDITION' THEN
PC.CONDITION_NAME
ELSE
''
END AS PM_NAME
FROM EMS_CHECKLIST_JOBS CJ,
EMS_CHECKLIST C,
EMS_PM_SCHEDULE PS,
EMS_PM_CONDITION PC,
EMS_EQUIPMENTS E
WHERE CJ.CHECKLIST_KEY = C.CHECKLIST_KEY
AND CJ.PM_KEY = PS.SCHEDULE_KEY(+)
AND CJ.PM_KEY = PC.CONDITION_KEY(+)
AND CJ.EQUIPMENT_KEY = E.EQUIPMENT_KEY";
if (!string.IsNullOrEmpty(checkListJobState))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_STATE = '{0}'", checkListJobState);
}
if (!string.IsNullOrEmpty(checkListJobName))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_NAME LIKE '%{0}%'", checkListJobName);
}
if (!string.IsNullOrEmpty(checkListJobKey))
{
sqlString += string.Format(" AND CJ.CHECKLIST_JOB_KEY = '{0}'", checkListJobKey);
}
if (!string.IsNullOrEmpty(equipmentKey))
{
sqlString += string.Format(" AND E.EQUIPMENT_KEY = '{0}'", equipmentKey);
}
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_CHECKLIST_JOBS_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetCheckListJobs Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
public DataSet GetSchedulePM(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string equipmentKey = reqDS.ExtendedProperties.ContainsKey(EMS_PM_SCHEDULE_FIELDS.FIELD_EQUIPMENT_KEY) ? reqDS.ExtendedProperties[EMS_PM_SCHEDULE_FIELDS.FIELD_EQUIPMENT_KEY].ToString() : string.Empty;
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
string sqlString = @"SELECT PS.*,
U.USERNAME AS NOTIFY_USER_NAME,
UC.USERNAME AS NOTIFY_CC_USER_NAME,
ECS.EQUIPMENT_CHANGE_STATE_NAME,
ECR.EQUIPMENT_CHANGE_REASON_NAME,
E.EQUIPMENT_NAME,
C.CHECKLIST_NAME
FROM EMS_PM_SCHEDULE PS,
EMS_CHECKLIST C,
EMS_EQUIPMENTS E,
RBAC_USER U,
RBAC_USER UC,
EMS_EQUIPMENT_CHANGE_REASONS ECR,
EMS_EQUIPMENT_CHANGE_STATES ECS
WHERE PS.CHECKLIST_KEY = C.CHECKLIST_KEY
AND PS.EQUIPMENT_KEY = E.EQUIPMENT_KEY
AND PS.NOTIFY_USER_KEY = U.USER_KEY
AND PS.NOTIFY_CC_USER_KEY = UC.USER_KEY(+)
AND PS.EQUIPMENT_CHANGE_REASON_KEY = ECR.EQUIPMENT_CHANGE_REASON_KEY(+)
AND PS.EQUIPMENT_CHANGE_STATE_KEY = ECS.EQUIPMENT_CHANGE_STATE_KEY(+)";
if (!string.IsNullOrEmpty(equipmentKey))
{
sqlString += string.Format(" AND E.EQUIPMENT_KEY = '{0}'", equipmentKey);
}
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_PM_SCHEDULE_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetSchedulePM Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
private DataSet Query(DataSet dsParams, ref PagingQueryConfig pConfig, bool ispaging)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
////出库校验
//if (status == "CKJY")
//{
sBuilder.Append(@"select OUTBANDNO,VBELN,POSNR,CPBH,XHGG,OBDQTY,QC_RESULT,BATCHNO,STATUS,QC_PERSON,
case PKG_MAT when 0 then '合格' when 1 then '不合格' else ''end as PKG_MAT,
case BILL_BRND when 0 then '合格' when 1 then '不合格'else '' end as BILL_BRND ,
case CANTR when 0 then '合格' when 1 then '不合格' else '' end as CANTR ,
case F_OUTB_CUSTM when 0 then '合格' when 1 then '不合格'else '' end as F_OUTB_CUSTM ,
case EL when 0 then '合格' when 1 then '不合格'else '' end as EL ,
case DATA_FORMT when 0 then '合格' when 1 then '不合格'else '' end as DATA_FORMT,
case LIST_ABSENCE when 0 then '合格' when 1 then '不合格'else '' end as LIST_ABSENCE ,
case LiST_ERR when 0 then '合格' when 1 then '不合格'else '' end as LiST_ERR ,
case CELL when 0 then '合格' when 1 then '不合格'else '' end as CELL ,
case MOD_ERR when 0 then '合格' when 1 then '不合格'else '' end as MOD_ERR ,
case QLVL_ERR when 0 then '合格' when 1 then '不合格'else '' end as QLVL_ERR,
case FRAME when 0 then '合格' when 1 then '不合格'else '' end as FRAME ,
case BRND_PARM_ERR when 0 then '合格' when 1 then '不合格'else '' end as BRND_PARM_ERR ,
case CONT_LOCK_BRK when 0 then '合格' when 1 then '不合格'else '' end as CONT_LOCK_BRK ,
case CUSTM_CK when 0 then '合格' when 1 then '不合格'else '' end as CUSTM_CK
from (select OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT,BILL_BRND ,CANTR ,F_OUTB_CUSTM ,EL ,DATA_FORMT, LIST_ABSENCE , LiST_ERR , CELL ,
MOD_ERR ,QLVL_ERR,FRAME ,BRND_PARM_ERR ,CONT_LOCK_BRK ,CUSTM_CK
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已确认') and b.STATUS is null
union select distinct OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT=2,BILL_BRND=2 ,CANTR=2 ,F_OUTB_CUSTM= 2,EL=2 ,DATA_FORMT=2,
LIST_ABSENCE=2 ,LiST_ERR=2 , CELL=2 ,MOD_ERR=2 , QLVL_ERR=2,FRAME=2 ,
BRND_PARM_ERR=2 ,CONT_LOCK_BRK=2 ,CUSTM_CK=2
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已确认') and b.STATUS ='删除'
union select OUTBANDNO,a.VBELN,a.POSNR,CPBH,XHGG,OBDQTY,a.QC_RESULT,a.BATCHNO,a.STATUS,a.QC_PERSON,
PKG_MAT,BILL_BRND ,CANTR ,F_OUTB_CUSTM ,EL ,DATA_FORMT, LIST_ABSENCE , LiST_ERR , CELL ,
MOD_ERR ,QLVL_ERR,FRAME ,BRND_PARM_ERR ,CONT_LOCK_BRK ,CUSTM_CK
from AWMS_OUTB_ITEM a left join AWMS_OUTB_QC b on a.VBELN=b.VBELN and a.POSNR=b.POSNR
where a.STATUS in('已过账','已检验') and b.STATUS='正常') a where 1=1
");
//}
// //出库管理
// else if (status == "CKGL")
// {
// sBuilder.Append(@"select a.OUTBANDNO,a.VBELN,a.POSNR,a.status,CPBH,XHGG,QCQTY,OBDQTY,QC_RESULT,BATCHNO,
// c.PALLET_NO from AWMS_OUTB_ITEM a,AWMS_CONTAINER_DETAIL b ,[dbo].WIP_CONSIGNMENT c
// where a.Cabinet_NO=b.CONTAINER_CODE and a.Cabinet_KEY=b.CONTAINER_KEY and a.CPBH=c.SAP_NO
// and b.PALLET_NO=c.PALLET_NO and a.STATUS in('已确认','已过账','已检验')
// ");
// }
if (dsParams != null && dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParam = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable hsParam = CommonUtils.ConvertToHashtable(dtParam);
if (hsParam.ContainsKey(AWMS_OUTB_ITEM_FIELDS.Field_VBELN))
{
string vbelnNo = Convert.ToString(hsParam[AWMS_OUTB_ITEM_FIELDS.Field_VBELN]);
sBuilder.AppendFormat(" and a.VBELN like '{0}%'", vbelnNo.PreventSQLInjection());
}
if (hsParam.ContainsKey(AWMS_OUTB_ITEM_FIELDS.Field_OUTBANDNO))
{
string outboundNo = Convert.ToString(hsParam[AWMS_OUTB_ITEM_FIELDS.Field_OUTBANDNO]);
sBuilder.AppendFormat(" and a.OUTBANDNO like '{0}%'", outboundNo.PreventSQLInjection());
}
if (hsParam.ContainsKey(AWMS_CONTAINER_DETAIL_FIELDS.Field_PALLET_NO))
{
string outboundNo = Convert.ToString(hsParam[AWMS_CONTAINER_DETAIL_FIELDS.Field_PALLET_NO]);
sBuilder.AppendFormat(" and c.PALLET_NO like '{0}%'", outboundNo.PreventSQLInjection());
}
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pConfig.PageNo,
pConfig.PageSize,
out pages,
out records,
db,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"");
pConfig.Pages = pages;
pConfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("OutboundOperationEngine Query Error:" + ex.Message);
}
finally
{ }
return(dsReturn);
}
/// <summary>
/// 查询在线物料。
/// </summary>
/// <param name="model">查询条件。</param>
/// <param name="pconfig">分页对象。</param>
/// <returns>包含在线物料信息的数据集对象。</returns>
public DataSet Query(OnlineMaterialQueryModel model, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.Append(@"SELECT A.STORE_MATERIAL_KEY ,
SUM(A.CURRENT_QTY)AS QTY,
C.STORE_NAME AS STORE_NAME,
D.MATERIAL_CODE AS MATERIAL_CODE,
D.MATERIAL_NAME AS MATERIAL_NAME,
D.UNIT AS UNIT,
E.LOCATION_NAME AS ROOM_NAME,
C.OPERATION_NAME AS OPERATION_NAME
FROM WST_STORE_MATERIAL_DETAIL A
LEFT JOIN WST_STORE_MATERIAL B ON A.STORE_MATERIAL_KEY=B.STORE_MATERIAL_KEY
LEFT JOIN WST_STORE C ON B.STORE_KEY=C.STORE_KEY
LEFT JOIN POR_MATERIAL D ON B.MATERIAL_KEY=D.MATERIAL_KEY
LEFT JOIN FMM_LOCATION E ON C.LOCATION_KEY=E.LOCATION_KEY
WHERE CURRENT_QTY>0 ");
if (!string.IsNullOrEmpty(model.OperationName))
{
sbSql.AppendFormat(UtilHelper.BuilderWhereConditionString("C.OPERATION_NAME", model.OperationName.Split(',')));
}
if (!string.IsNullOrEmpty(model.StoreName))
{
sbSql.AppendFormat(UtilHelper.BuilderWhereConditionString("C.STORE_NAME", model.StoreName.Split(',')));
}
if (!string.IsNullOrEmpty(model.MaterialCode))
{
sbSql.AppendFormat(" AND D.MATERIAL_CODE LIKE '{0}%'", model.MaterialCode.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.MaterialLot))
{
sbSql.AppendFormat(" AND A.MATERIAL_LOT LIKE '{0}%'", model.MaterialLot.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.RoomName))
{
sbSql.AppendFormat(" AND E.LOCATION_NAME LIKE '{0}%'", model.RoomName.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(model.SupplierName))
{
sbSql.AppendFormat(" AND A.MATERIAL_SUPPLIER LIKE '{0}%'", model.SupplierName.PreventSQLInjection());
}
sbSql.Append(@" GROUP BY A.STORE_MATERIAL_KEY,C.STORE_NAME,D.MATERIAL_CODE,E.LOCATION_NAME,
D.MATERIAL_NAME,D.UNIT, E.LOCATION_NAME,C.OPERATION_NAME ");
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sbSql.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
"WST_STORE_MATERIAL",
"MATERIAL_CODE ASC");
pconfig.Pages = pages;
pconfig.Records = records;
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotList Error: " + ex.Message);
}
return(dsReturn);
}
public DataSet GetParts(DataSet reqDS)
{
DataSet resDS = new DataSet();
int pages = 0;
int records = 0;
if (reqDS != null &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_KEY) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGENO) &&
reqDS.ExtendedProperties.ContainsKey(PARAMETERS.INPUT_PAGESIZE))
{
string partName = reqDS.ExtendedProperties[PARAMETERS.INPUT_KEY].ToString();
int pageNo = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGENO]);
int pageSize = Convert.ToInt32(reqDS.ExtendedProperties[PARAMETERS.INPUT_PAGESIZE]);
try
{
EMS_EQUIPMENT_PARTS_FIELDS partsFields = new EMS_EQUIPMENT_PARTS_FIELDS();
List <string> interestColumns = new List <string>();
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_DESCRIPTION);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_TYPE);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_MODE);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_UNIT);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATOR);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATE_TIMEZONE_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_CREATE_TIME);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDITOR);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDIT_TIMEZONE_KEY);
interestColumns.Add(EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EDIT_TIME);
Conditions conditions = new Conditions();
if (!string.IsNullOrEmpty(partName))
{
conditions.Add(GlobalEnums.DatabaseLogicOperator.And, EMS_EQUIPMENT_PARTS_FIELDS.FIELD_EQUIPMENT_PART_NAME, GlobalEnums.DatabaseCompareOperator.Like, string.Format("%{0}%", partName));
}
string sqlString = DatabaseTable.BuildQuerySqlStatement(partsFields, interestColumns, conditions);
if (pageNo > 0 && pageSize > 0)
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, resDS, EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { EMS_EQUIPMENT_PARTS_FIELDS.DATABASE_TABLE_NAME });
}
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, string.Empty);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_PAGES, pages);
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_RECORDS, records);
}
catch (Exception ex)
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, ex.Message);
LogService.LogError("GetParts Error: " + ex.Message);
}
}
else
{
resDS.ExtendedProperties.Add(PARAMETERS.OUTPUT_MESSAGE, "提交数据不存在,请重新提交!");
}
return(resDS);
}
/// <summary>
/// 查询工序数据。
/// </summary>
/// <param name="dsParams">包含查询条件的数据集。</param>
/// <param name="operationName">工序名称。</param>
/// <param name="pageNo">页号。</param>
/// <param name="pageSize">每页记录数。</param>
/// <param name="pages">总页数。</param>
/// <param name="records">总记录数。</param>
/// <returns>包含工序数据的数据集对象。</returns>
public DataSet GetOperations(DataSet dsParams, string operationName, int pageNo, int pageSize, out int pages, out int records)
{
DataSet dsReturn = new DataSet();
pages = 0;
records = 0;
try
{
POR_ROUTE_OPERATION_VER_FIELDS operationsFields = new POR_ROUTE_OPERATION_VER_FIELDS();
#region 返回的数据列
List <string> interestColumns = new List <string>();
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_DESCRIPTIONS);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_IMAGE_KEY);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_DURATION);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_VERSION);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_STATUS);
interestColumns.Add(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_IS_REWORKABLE);
#endregion
#region 组织查询条件
Conditions conditions = new Conditions();
if (dsParams != null && dsParams.Tables.Contains(PARAMETERS_INPUT.DATABASE_TABLE_NAME))
{
DataTable inputParamDataTable = dsParams.Tables[PARAMETERS_INPUT.DATABASE_TABLE_NAME];
if (inputParamDataTable != null && inputParamDataTable.Columns.Contains(PARAMETERS_INPUT.FIELD_KEY))
{
foreach (DataRow row in inputParamDataTable.Rows)
{
object key = row[PARAMETERS_INPUT.FIELD_KEY];
if (key == null || key == DBNull.Value)
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY, DatabaseCompareOperator.Null, string.Empty);
}
else
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_VER_KEY, DatabaseCompareOperator.Equal, key.ToString());
}
}
}
}
#endregion
//已激活的工序。
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_OPERATION_STATUS, DatabaseCompareOperator.Equal, "1");
//工序名称
if (!string.IsNullOrEmpty(operationName))
{
conditions.Add(DatabaseLogicOperator.And, POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME, DatabaseCompareOperator.Like, string.Format("%{0}%", operationName));
}
//创建查询SQL
string sqlString = DatabaseTable.BuildQuerySqlStatement(operationsFields, interestColumns, conditions);
if (pageNo > 0 && pageSize > 0)//分页查询
{
AllCommonFunctions.CommonPagingData(sqlString, pageNo, pageSize, out pages, out records, db, dsReturn, POR_ROUTE_OPERATION_VER_FIELDS.DATABASE_TABLE_NAME);
}
else
{
db.LoadDataSet(CommandType.Text, sqlString, dsReturn, new string[] { POR_ROUTE_OPERATION_VER_FIELDS.DATABASE_TABLE_NAME });
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetOperations Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 查询包含批次信息的数据集。
/// </summary>
/// <param name="dsSearch">
/// 包含查询条件的数据集。
/// </param>
/// <param name="pconfig">
/// 分页查询的配置对象。
/// </param>
/// <param name="isPaging">
/// 是否分页查询。
/// </param>
/// <returns>包含批次信息的数据集。</returns>
private DataSet SearchLotList(DataSet dsSearch, ref PagingQueryConfig pconfig, bool isPaging)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
sBuilder.Append(@"SELECT A.[LOT_KEY],A.[LOT_NUMBER],A.[WORK_ORDER_KEY],A.[WORK_ORDER_NO],A.[WORK_ORDER_SEQ]
,A.[PART_VER_KEY],A.[PART_NUMBER],A.[PRO_ID],A.[PRO_LEVEL],A.[PRIORITY],A.[QUANTITY_INITIAL]
,A.[LOT_LINE_CODE],A.[LOT_LINE_KEY]
,A.[QUANTITY],A.[ROUTE_ENTERPRISE_VER_KEY],A.[CUR_ROUTE_VER_KEY],A.[CUR_STEP_VER_KEY]
,A.[CUR_PRODUCTION_LINE_KEY],A.[LINE_NAME],A.[START_WAIT_TIME],A.[START_PROCESS_TIME]
,A.[EDC_INS_KEY],A.[STATE_FLAG],A.[IS_MAIN_LOT],A.[SPLIT_FLAG],A.[LOT_SEQ],A.[REWORK_FLAG]
,A.[HOLD_FLAG],A.[SHIPPED_FLAG],A.[DELETED_TERM_FLAG],A.[IS_PRINT],A.[LOT_TYPE],A.[CREATE_TYPE]
,A.[COLOR],A.[PALLET_NO],A.[PALLET_TIME],A.[STATUS],A.[OPERATOR],A.[OPR_LINE],A.[OPR_COMPUTER]
,A.[OPR_LINE_PRE],A.[CHILD_LINE],A.[MATERIAL_CODE],A.[MATERIAL_LOT],A.[SUPPLIER_NAME],A.[SI_LOT]
,A.[EFFICIENCY],A.[FACTORYROOM_KEY],A.[FACTORYROOM_NAME],A.[CREATE_OPERTION_NAME],A.[CREATOR]
,A.[CREATE_TIME],A.[CREATE_TIMEZONE_KEY],A.[EDITOR],A.[EDIT_TIME],A.[EDIT_TIMEZONE],A.[SHIFT_NAME]
,A.[DESCRIPTIONS],A.[LOT_SIDECODE],A.[LOT_CUSTOMERCODE],A.[ORG_WORK_ORDER_NO],w.PART_NUMBER AS ORG_PART_NUMBER,
E.ENTERPRISE_NAME,
D.ROUTE_NAME,
B.ROUTE_STEP_NAME,
(SELECT TOP 1 EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE LOT_KEY=A.LOT_KEY
AND STEP_KEY=A.CUR_STEP_VER_KEY
AND END_TIMESTAMP IS NULL) AS EQUIPMENT_KEY,
(SELECT EQUIPMENT_NAME
FROM EMS_EQUIPMENTS
WHERE EQUIPMENT_KEY=(SELECT TOP 1 EQUIPMENT_KEY
FROM EMS_LOT_EQUIPMENT
WHERE LOT_KEY=A.LOT_KEY
AND STEP_KEY=A.CUR_STEP_VER_KEY
AND END_TIMESTAMP IS NULL)) AS EQUIPMENT_NAME
FROM POR_LOT A
LEFT JOIN POR_WORK_ORDER w ON w.ORDER_NUMBER=A.ORG_WORK_ORDER_NO
LEFT JOIN POR_ROUTE_STEP B ON A.CUR_STEP_VER_KEY= B.ROUTE_STEP_KEY
LEFT JOIN POR_ROUTE_ROUTE_VER D ON A.CUR_ROUTE_VER_KEY=D.ROUTE_ROUTE_VER_KEY
LEFT JOIN POR_ROUTE_ENTERPRISE_VER E ON A.ROUTE_ENTERPRISE_VER_KEY =E.ROUTE_ENTERPRISE_VER_KEY
WHERE A.STATUS < 2");
if (dsSearch != null && dsSearch.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParams = dsSearch.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = CommonUtils.ConvertToHashtable(dtParams);
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY))
{
string roomKey = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY]);
sBuilder.AppendFormat(" AND A.FACTORYROOM_KEY ='{0}'", roomKey.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER LIKE '%{0}%'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_START"))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_START"]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER >='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_END"))
{
string lotNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_NUMBER + "_END"]);
sBuilder.AppendFormat(" AND A.LOT_NUMBER <='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_HOLD_FLAG))
{
string holdFalg = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_HOLD_FLAG]);
sBuilder.AppendFormat(" AND A.HOLD_FLAG={0}", holdFalg.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LOT_TYPE))
{
string lotType = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LOT_TYPE]);
sBuilder.AppendFormat(" AND A.LOT_TYPE='{0}'", lotType.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_STATE_FLAG))
{
string stateFlag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_STATE_FLAG]);
sBuilder.AppendFormat(" AND A.STATE_FLAG={0}", stateFlag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATOR))
{
string creator = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATOR]);
sBuilder.AppendFormat(" AND A.CREATOR='{0}'", creator.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG))
{
string deletedTermFlag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_DELETED_TERM_FLAG]);
sBuilder.AppendFormat(" AND A.DELETED_TERM_FLAG={0}", deletedTermFlag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_SHIPPED_FLAG))
{
string flag = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_SHIPPED_FLAG]);
sBuilder.AppendFormat(" AND A.SHIPPED_FLAG={0}", flag.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PRO_ID))
{
string proId = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PRO_ID]);
sBuilder.AppendFormat(" AND A.PRO_ID LIKE '%{0}%'", proId.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PART_NUMBER))
{
string partNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PART_NUMBER]);
sBuilder.AppendFormat(" AND A.PART_NUMBER LIKE '%{0}%'", partNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_WORK_ORDER_NO))
{
string orderNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_WORK_ORDER_NO]);
sBuilder.AppendFormat(" AND A.WORK_ORDER_NO LIKE '%{0}%'", orderNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO))
{
string orderNumber = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_ORG_WORK_ORDER_NO]);
sBuilder.AppendFormat(" AND A.ORG_WORK_ORDER_NO LIKE '%{0}%'", orderNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_PALLET_NO))
{
string palletNo = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_PALLET_NO]);
sBuilder.AppendFormat(" AND A.PALLET_NO LIKE '%{0}%'", palletNo.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_LINE_NAME))
{
string lineName = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_LINE_NAME]);
sBuilder.AppendFormat(" AND A.LINE_NAME ='{0}'", lineName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_SHIFT_NAME))
{
string shiftName = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_SHIFT_NAME]);
sBuilder.AppendFormat(" AND A.SHIFT_NAME='{0}'", shiftName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME))
{
string enterpriseName = Convert.ToString(htParams[POR_ROUTE_ENTERPRISE_VER_FIELDS.FIELD_ENTERPRISE_NAME]);
sBuilder.AppendFormat(" AND E.ENTERPRISE_NAME='{0}'", enterpriseName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME))
{
string routeName = Convert.ToString(htParams[POR_ROUTE_ROUTE_VER_FIELDS.FIELD_ROUTE_NAME]);
sBuilder.AppendFormat(" AND D.ROUTE_NAME='{0}'", routeName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME))
{
string stepName = Convert.ToString(htParams[POR_ROUTE_OPERATION_VER_FIELDS.FIELD_ROUTE_OPERATION_NAME]);
sBuilder.AppendFormat(" AND B.ROUTE_STEP_NAME='{0}'", stepName.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATE_TIME + "_START"))
{
string createStartTime = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATE_TIME + "_START"]);
sBuilder.AppendFormat(" AND A.CREATE_TIME >='{0}'", createStartTime.PreventSQLInjection());
}
if (htParams.ContainsKey(POR_LOT_FIELDS.FIELD_CREATE_TIME + "_END"))
{
string createEndTime = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_CREATE_TIME + "_END"]);
sBuilder.AppendFormat(" AND A.CREATE_TIME<='{0}'", createEndTime.PreventSQLInjection());
}
}
if (!isPaging)
{
sBuilder.Append(" ORDER BY A.LOT_NUMBER ASC,A.CREATE_TIME DESC");
dsReturn = this._dbRead.ExecuteDataSet(CommandType.Text, sBuilder.ToString());
}
else
{
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
this._dbRead,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"ASC",
new string[] { "LOT_NUMBER", "CREATE_TIME" });
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_START_WAIT_TIME].DateTimeMode = DataSetDateTime.Unspecified;
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_START_PROCESS_TIME].DateTimeMode = DataSetDateTime.Unspecified;
dsReturn.Tables[0].Columns[POR_LOT_FIELDS.FIELD_EDIT_TIME].DateTimeMode = DataSetDateTime.Unspecified;
pconfig.Pages = pages;
pconfig.Records = records;
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotList Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 入库单查询
/// </summary>
/// <param name="ZMBLNR"></param>
/// <returns></returns>
public DataSet QueryWarehouseWarrant(DataSet dsParams, ref PagingQueryConfig pconfig)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
sBuilder.Append(@"SELECT ZMBLNR, WERKS, STATUS, ZMMTYP, AUFNR, VBELN_OEM, DEPT,
CDATE, CREATOR, MBLNR, MBLNR1, CAST(ISSYN AS VARCHAR(10)) AS ISSYN, SYNMAN, SYNDATE
FROM AWMS_WH_ENTRY a
WHERE STATUS = '正常' ");
if (dsParams != null && dsParams.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = CommonUtils.ConvertToHashtable(dtParams);
if (htParams.ContainsKey("ZMBLNR"))
{
string ZMBLNR = Convert.ToString(htParams["ZMBLNR"]);
sBuilder.AppendFormat(" AND ZMBLNR LIKE '{0}%'", ZMBLNR.PreventSQLInjection());
}
if (htParams.ContainsKey("AUFNR"))
{
string AUFNR = Convert.ToString(htParams["AUFNR"]);
sBuilder.AppendFormat(" AND AUFNR LIKE '{0}%'", AUFNR.PreventSQLInjection());
}
if (htParams.ContainsKey("MATNR"))
{
string MATNR = Convert.ToString(htParams["MATNR"]);
sBuilder.AppendFormat(" AND EXISTS (SELECT ZMBLNR FROM AWMS_WH_ENTRY_DETAIL b WHERE a.ZMBLNR = b.ZMBLNR AND MATNR LIKE '{0}%')",
MATNR.PreventSQLInjection());
}
if (htParams.ContainsKey("CREATOR"))
{
string CREATOR = Convert.ToString(htParams["CREATOR"]);
sBuilder.AppendFormat(" AND CREATOR LIKE '{0}%'", CREATOR.PreventSQLInjection());
}
if (htParams.ContainsKey("BDATE"))
{
string BDATE = Convert.ToString(htParams["BDATE"]);
sBuilder.AppendFormat(" AND CDATE >= '{0}'", BDATE.PreventSQLInjection());
}
if (htParams.ContainsKey("EDATE"))
{
string EDATE = Convert.ToString(htParams["EDATE"]);
sBuilder.AppendFormat(" AND CDATE <= '{0}'", EDATE.PreventSQLInjection());
}
}
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
db,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"ZMBLNR ASC");
pconfig.Pages = pages;
pconfig.Records = records;
if (records == 0)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "订单信息不存在！");
}
else
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, String.Empty);
}
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("WarehouseWarrant Query Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 获取物料领用记录。
/// </summary>
/// <param name="dtParams">
/// 包含查询条件的数据表对象。
/// --------------------------------------
/// {DO}{STORE_NAME}{OPERATION_NAME}{OPERATOR}{CHARG}{LLIEF}{MATNR}{MBLNR}{RECEIVE_TIME_START}{RECEIVE_TIME_END}
/// --------------------------------------
/// DO=Query
/// ---------------------------------------
/// DO=其他, 线上仓名称使用逗号分隔,store1,store1...
/// --------------------------------------
/// </param>
/// <param name="pconfig">
/// 分页查询的配置对象。
/// </param>
/// <returns>包含物料领用详细信息的数据集对象。</returns>
public DataSet GetReceiveMaterialHistory(DataTable dtParams, ref PagingQueryConfig pconfig)
{
string msg = string.Empty;
DataSet dsReturn = new DataSet();
try
{
string sql = @"SELECT W.SAP_ISSURE_KEY,
W.MBLNR ,
W.CHARG ,
W.AUFNR ,
W.PRO_ID,
W.GRADE,
W.EFFICIENCY,
W.LLIEF ,
W.SUPPLIER_CODE,
W.ERFMG ,
W.MATNR ,
W.MATXT ,
W.ERFME ,
B.OPERATION_NAME ,
B.STORE_NAME ,
F.LOCATION_NAME,
W.RECEIVE_TIME ,
W.SHIFT_NAME ,
W.OPERATOR,
W.MEMO
FROM WST_STORE_MATERIAL A
INNER JOIN WST_STORE B ON A.STORE_KEY = B.STORE_KEY
INNER JOIN POR_MATERIAL C ON A.MATERIAL_KEY = C.MATERIAL_KEY
INNER JOIN WST_STORE_MATERIAL_DETAIL D ON A.STORE_MATERIAL_KEY = D.STORE_MATERIAL_KEY
INNER JOIN WST_SAP_ISSURE W ON D.STORE_MATERIAL_DETAIL_KEY = W.STORE_MATERIAL_DETAIL_KEY
INNER JOIN FMM_LOCATION F ON B.LOCATION_KEY = F.LOCATION_KEY
WHERE B.OBJECT_STATUS=1 ";
//判断是查询还是LOAD
string doName = Convert.ToString(dtParams.Rows[0]["DO"]).Trim();
if (doName == "Query")
{
//领料项目号不为空
string materialLot = Convert.ToString(dtParams.Rows[0]["CHARG"]).Trim();
if (!string.IsNullOrEmpty(materialLot))
{
sql += string.Format(" AND W.CHARG LIKE '{0}%'", materialLot.PreventSQLInjection());
}
//工单号
string orderNo = Convert.ToString(dtParams.Rows[0]["AUFNR"]).Trim();
if (!string.IsNullOrEmpty(orderNo))
{
sql += string.Format(" AND W.AUFNR ='{0}'", orderNo.PreventSQLInjection());
}
//产品号
string proId = Convert.ToString(dtParams.Rows[0]["PRO_ID"]).Trim();
if (!string.IsNullOrEmpty(proId))
{
sql += string.Format(" AND W.PRO_ID ='{0}'", proId.PreventSQLInjection());
}
//转换效率
string efficiency = Convert.ToString(dtParams.Rows[0]["EFFICIENCY"]).Trim();
if (!string.IsNullOrEmpty(efficiency))
{
sql += string.Format(" AND W.EFFICIENCY ='{0}'", efficiency.PreventSQLInjection());
}
//等级
string grade = Convert.ToString(dtParams.Rows[0]["GRADE"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND W.GRADE ='{0}'", grade.PreventSQLInjection());
}
//供应商名称
string supplierName = Convert.ToString(dtParams.Rows[0]["LLIEF"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND W.LLIEF ='{0}'", supplierName.PreventSQLInjection());
}
//线上仓名称
string storeName = Convert.ToString(dtParams.Rows[0]["STORE_NAME"]).Trim();
if (!string.IsNullOrEmpty(grade))
{
sql += string.Format(" AND B.STORE_NAME ='{0}'", storeName.PreventSQLInjection());
}
//最早发料时间不为空
string receiveStartTime = Convert.ToString(dtParams.Rows[0]["RECEIVE_TIME_START"]).Trim();
if (!string.IsNullOrEmpty(receiveStartTime))
{
sql += string.Format(" AND W.RECEIVE_TIME >='{0}'", receiveStartTime.PreventSQLInjection());
}
//最晚发料时间不为空
string receiveEndTime = Convert.ToString(dtParams.Rows[0]["RECEIVE_TIME_END"]).Trim() + " 23:59:59";
if (!string.IsNullOrEmpty(receiveEndTime))
{
sql += string.Format(" AND W.RECEIVE_TIME <= '{0}'", receiveEndTime.PreventSQLInjection());
}
}
else
{
string storeName = Convert.ToString(dtParams.Rows[0]["STORE_NAME"]).Trim();
if (!string.IsNullOrEmpty(storeName))
{
string sStoreName = UtilHelper.BuilderWhereConditionString("B.STORE_NAME", storeName.Split(','));
sql += sStoreName;
}
}
if (pconfig == null)
{
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
}
else
{
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sql, pconfig.PageNo, pconfig.PageSize, out pages,
out records, db, dsReturn, "STORE_MATERIAL");
pconfig.Pages = pages;
pconfig.Records = records;
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
}
catch (Exception ex)
{
msg = ex.Message;
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
LogService.LogError(string.Format("GetReceiveMaterialHistory Error:{0}", ex.Message));
}
return(dsReturn);
}
/// <summary>
/// 查询包含托盘信息的数据集。
/// </summary>
/// <param name="dsSearch">
/// 包含查询条件的数据集。
/// </param>
/// <param name="pconfig">
/// 分页查询的配置对象。
/// </param>
/// <param name="isPaging">
/// 是否分页查询。
/// </param>
/// <returns>包含托盘信息的数据集。</returns>
private DataSet SearchPalletList(DataSet dsSearch, ref PagingQueryConfig pconfig, bool isPaging, bool isDetail)
{
DataSet dsReturn = new DataSet();
StringBuilder sBuilder = new StringBuilder();
try
{
if (!isDetail)
{
sBuilder.Append(@"SELECT * FROM
(
SELECT A.VIRTUAL_PALLET_NO,A.WORKNUMBER,A.SAP_NO,A.PRO_ID,A.GRADE,A.LOT_NUMBER_QTY,A.ROOM_KEY,
A.POWER_LEVEL,A.TOTLE_POWER,A.AVG_POWER,A.LOT_COLOR,A.CS_DATA_GROUP,
A.CREATE_TIME,A.CREATER,A.CHECK_TIME,A.CHECKER,A.TO_WH_TIME,A.TO_WH,
(SELECT MAX(PALLET_TIME) FROM POR_LOT WHERE PALLET_NO=A.VIRTUAL_PALLET_NO) PALLET_TIME
FROM WIP_CONSIGNMENT A
WHERE A.ISFLAG=1
) AS A
WHERE 1=1");
}
else
{
sBuilder.Append(@"
SELECT * FROM
(
SELECT c.VIRTUAL_PALLET_NO,c.WORKNUMBER,c.SAP_NO,c.PRO_ID,c.GRADE,c.LOT_NUMBER_QTY,
c.POWER_LEVEL,c.TOTLE_POWER,c.AVG_POWER,c.LOT_COLOR,c.CS_DATA_GROUP,
c.CREATE_TIME,c.CREATER,c.CHECK_TIME,c.CHECKER,c.TO_WH_TIME,c.TO_WH,c.ROOM_KEY,
d.ITEM_NO,
t.LOT_NUMBER,
t.WORK_ORDER_NO AS LOT_WORK_ORDER_NO,
t.PRO_ID AS LOT_PRO_ID,
t.PART_NUMBER AS LOT_PART_NUMBER,
t.LOT_SIDECODE,
t.LOT_CUSTOMERCODE,
t.COLOR AS LOT_LOT_COLOR,
t.PRO_LEVEL AS LOT_PRO_LEVEL,
t.QUANTITY AS LOT_QUANTITY,
t.EFFICIENCY AS LOT_EFFICIENCY,
b.GRADE_NAME AS LOT_GRADE_NAME,
a.DEVICENUM,
a.TTIME,
a.VC_MODNAME,
a.PM,a.FF,a.IPM,a.ISC,a.VPM,a.VOC,
a.COEF_PMAX,a.COEF_FF,a.COEF_IMAX,a.COEF_ISC,a.COEF_VMAX,a.COEF_VOC
FROM POR_LOT t
INNER JOIN WIP_CONSIGNMENT c ON c.VIRTUAL_PALLET_NO=t.PALLET_NO AND c.ISFLAG=1
INNER JOIN WIP_CONSIGNMENT_DETAIL d ON d.CONSIGNMENT_KEY=c.CONSIGNMENT_KEY AND d.LOT_NUMBER=t.LOT_NUMBER
LEFT JOIN WIP_IV_TEST a ON a.LOT_NUM=t.LOT_NUMBER AND a.VC_DEFAULT=1
LEFT JOIN V_ProductGrade b ON b.GRADE_CODE=t.PRO_LEVEL
WHERE t.DELETED_TERM_FLAG<2
) AS A
WHERE 1=1");
}
if (dsSearch != null && dsSearch.Tables.Contains(TRANS_TABLES.TABLE_MAIN_DATA))
{
DataTable dtParams = dsSearch.Tables[TRANS_TABLES.TABLE_MAIN_DATA];
Hashtable htParams = CommonUtils.ConvertToHashtable(dtParams);
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_ROOM_KEY))
{
string roomKey = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_ROOM_KEY]);
sBuilder.AppendFormat(" AND A.ROOM_KEY ='{0}'", roomKey.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO))
{
string palletNo = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO]);
if (!string.IsNullOrEmpty(palletNo))
{
string palletNos = UtilHelper.BuilderWhereConditionString("A.VIRTUAL_PALLET_NO", palletNo.Split(new char[] { ',', '\n', '#' }));
sBuilder.AppendFormat(palletNos);
}
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO + "_START"))
{
string lotNumber = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO + "_START"]);
sBuilder.AppendFormat(" AND A.VIRTUAL_PALLET_NO >='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO + "_END"))
{
string lotNumber = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_VIRTUAL_PALLET_NO + "_END"]);
sBuilder.AppendFormat(" AND A.VIRTUAL_PALLET_NO <='{0}'", lotNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_WORKNUMBER))
{
string orderNumber = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_WORKNUMBER]);
sBuilder.AppendFormat(" AND A.WORKNUMBER LIKE '%{0}%'", orderNumber.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_SAP_NO))
{
string sapNo = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_SAP_NO]);
sBuilder.AppendFormat(" AND A.SAP_NO ='{0}'", sapNo.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_PRO_ID))
{
string val = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_PRO_ID]);
sBuilder.AppendFormat(" AND A.PRO_ID ='{0}'", val.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_CS_DATA_GROUP))
{
string csDataGroup = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_CS_DATA_GROUP]);
sBuilder.AppendFormat(" AND A.CS_DATA_GROUP ='{0}'", csDataGroup.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_SHIFT))
{
string shiftName = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_SHIFT]);
sBuilder.AppendFormat(" AND A.SHIFT='{0}'", shiftName.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_CREATE_TIME + "_START"))
{
string createStartTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_CREATE_TIME + "_START"]);
sBuilder.AppendFormat(" AND A.PALLET_TIME >='{0}'", createStartTime.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_CREATE_TIME + "_END"))
{
string createEndTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_CREATE_TIME + "_END"]);
sBuilder.AppendFormat(" AND A.PALLET_TIME<='{0}'", createEndTime.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_CHECK_TIME + "_START"))
{
string createStartTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_CHECK_TIME + "_START"]);
sBuilder.AppendFormat(" AND A.CHECK_TIME >='{0}'", createStartTime.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_CHECK_TIME + "_END"))
{
string createEndTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_CHECK_TIME + "_END"]);
sBuilder.AppendFormat(" AND A.CHECK_TIME<='{0}'", createEndTime.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_TO_WH_TIME + "_START"))
{
string createStartTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_TO_WH_TIME + "_START"]);
sBuilder.AppendFormat(" AND A.TO_WH_TIME >='{0}'", createStartTime.PreventSQLInjection());
}
if (htParams.ContainsKey(WIP_CONSIGNMENT_FIELDS.FIELDS_TO_WH_TIME + "_END"))
{
string createEndTime = Convert.ToString(htParams[WIP_CONSIGNMENT_FIELDS.FIELDS_TO_WH_TIME + "_END"]);
sBuilder.AppendFormat(" AND A.TO_WH_TIME<='{0}'", createEndTime.PreventSQLInjection());
}
}
if (!isPaging)
{
sBuilder.Append(" ORDER BY A.VIRTUAL_PALLET_NO ASC,A.CREATE_TIME DESC");
dsReturn = this._dbRead.ExecuteDataSet(CommandType.Text, sBuilder.ToString());
}
else
{
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sBuilder.ToString(),
pconfig.PageNo,
pconfig.PageSize,
out pages,
out records,
this._dbRead,
dsReturn,
POR_LOT_FIELDS.DATABASE_TABLE_NAME,
"ASC",
new string[] { "VIRTUAL_PALLET_NO", "CREATE_TIME" });
pconfig.Pages = pages;
pconfig.Records = records;
}
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchPalletList Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 分页查询预设暂停数据。
/// </summary>
/// <param name="dsParams">包含查询条件的数据集对象。</param>
/// <param name="config">数据分页配置对象。</param>
/// <returns>包含预设暂停数据的数据集对象。</returns>
public DataSet Query(DataSet dsParams, ref PagingQueryConfig config)
{
DataSet dsReturn = new DataSet();
try
{
string sql = string.Format(@"SELECT a.*,b.PRO_ID,c.ROUTE_NAME,d.ROUTE_STEP_NAME,b.QUANTITY
FROM WIP_FUTUREHOLD a
LEFT JOIN POR_LOT b ON a.LOT_KEY=b.LOT_KEY
LEFT JOIN POR_ROUTE_ROUTE_VER c ON b.CUR_ROUTE_VER_KEY=c.ROUTE_ROUTE_VER_KEY
LEFT JOIN POR_ROUTE_STEP d ON b.CUR_STEP_VER_KEY=d.ROUTE_STEP_KEY
WHERE a.STATUS=1 ");
string lotNo = string.Empty;
string orderNo = string.Empty;
string operationName = string.Empty;
string action = string.Empty;
string roomKey = string.Empty;
//组织SQL字符串。
if (dsParams != null && dsParams.Tables.Contains(TRANS_TABLES.TABLE_PARAM))
{
DataTable dtParams = dsParams.Tables[TRANS_TABLES.TABLE_PARAM];
Hashtable htParams = FanHai.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dtParams);
if (htParams.Contains(POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY))
{
roomKey = Convert.ToString(htParams[POR_LOT_FIELDS.FIELD_FACTORYROOM_KEY]);
sql += string.Format(" AND b.FACTORYROOM_KEY = '{0}'", roomKey.PreventSQLInjection());
}
if (htParams.Contains(WIP_FUTUREHOLD_FIELDS.FIELDS_LOT_NUMBER))
{
lotNo = Convert.ToString(htParams[WIP_FUTUREHOLD_FIELDS.FIELDS_LOT_NUMBER]);
sql += string.Format(" AND a.LOT_NUMBER LIKE '{0}%'", lotNo.PreventSQLInjection());
}
if (htParams.Contains(WIP_FUTUREHOLD_FIELDS.FIELDS_WORKORDER_NUMBER))
{
orderNo = Convert.ToString(htParams[WIP_FUTUREHOLD_FIELDS.FIELDS_WORKORDER_NUMBER]);
sql += string.Format(" AND a.WORKORDER_NUMBER LIKE '{0}%'", orderNo.PreventSQLInjection());
}
if (htParams.Contains(WIP_FUTUREHOLD_FIELDS.FIELDS_OPERATION_NAME))
{
operationName = Convert.ToString(htParams[WIP_FUTUREHOLD_FIELDS.FIELDS_OPERATION_NAME]);
sql += string.Format(" AND a.OPERATION_NAME='{0}'", operationName.PreventSQLInjection());
}
if (htParams.Contains(WIP_FUTUREHOLD_FIELDS.FIELDS_ACTION_NAME))
{
action = Convert.ToString(htParams[WIP_FUTUREHOLD_FIELDS.FIELDS_ACTION_NAME]);
sql += string.Format(" AND a.ACTION_NAME='{0}'", action.PreventSQLInjection());
}
}
//进行分页查询。
int pages = 0;
int records = 0;
AllCommonFunctions.CommonPagingData(sql, config.PageNo, config.PageSize, out pages,
out records, db, dsReturn, WIP_FUTUREHOLD_FIELDS.DATABASE_TABLE_NAME);
config.Pages = pages;
config.Records = records;
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError(string.Format("LotFutureHoldEngine-Query Error:{0}", ex.Message));
}
return(dsReturn);
}