/// <summary>
/// 获取物料明细信息。
/// </summary>
/// <param name="sapOperation">SAP工作中心名称，多个工作中心使用逗号分割。C1,C2...</param>
/// <param name="storeNameList">线上仓名称，多个线上仓使用逗号分割。store1,store2..</param>
/// <returns>
/// 包含物料明细信息的数据集对象。
/// 【W.SAP_ISSURE_KEY,ROWNUM,W.MBLNR, W.MATNR,W.CHARG, W.MATXT, W.AUFNR,W.ERFME,W.ERFMG, W.LLIEF,B.WORK_CENTER】
/// </returns>
public DataSet GetMaterialDetail(string sapOperation, string storeNameList)
{
string msg = string.Empty;
DataSet dsReturn = new DataSet();
try
{
string sSapOperation = UtilHelper.BuilderWhereConditionString("B.WORK_CENTER", sapOperation.Split(','));
string sStoreNameList = UtilHelper.BuilderWhereConditionString(" D.STORE_NAME", storeNameList.Split(','));
string sql = string.Format(@"SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY W.CHARG) ROWNUM,W.SAP_ISSURE_KEY,W.MBLNR,W.MATNR,W.CHARG,W.MATXT,W.AUFNR,W.ERFME,W.ERFMG,W.LLIEF,B.WORK_CENTER
FROM WST_SAP_ISSURE W
JOIN POR_WORK_ORDER A ON A.ORDER_NUMBER=W.AUFNR
JOIN POR_WORK_ORDER_BOM B ON W.MATNR = B.MATERIAL_CODE AND W.AUFNR = B.ORDER_NUMBER
WHERE W.ISRECEIVED = '0'
AND A.FACTORY_NAME IN (SELECT DISTINCT E.FACTORY_NAME
FROM WST_STORE D
LEFT JOIN V_LOCATION E ON D.LOCATION_KEY = E.ROOM_KEY
WHERE 1=1 {1})",
sSapOperation,
sStoreNameList);
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
}
catch (Exception ex)
{
msg = ex.Message;
LogService.LogError(string.Format("GetMaterialDetail Error:{0}", ex.Message));
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
}
return(dsReturn);
}
/// <summary>
/// 根据线边仓名称获取车间名称和主键。
/// </summary>
/// <param name="STORES">用户拥有权限的线边仓名称,使用逗号分隔:store1,store2...</param>
/// <returns>包含车间名称和车间主键的数据集对象。</returns>
public DataSet GetWorkShopInfo(string stores)
{
DataSet dsReturn = new DataSet();
try
{
string sql = "";
//线边仓名称长度>0,即方法参数传入了线边仓名称。
if (stores.Length > 0)
{
sql = @"SELECT DISTINCT V.PARENT_KEY,V.PARENT_NAME
FROM V_LOCATION_RET V,WST_STORE S
WHERE S.LOCATION_KEY = V.LOCATION_KEY";
string sqlCondition = UtilHelper.BuilderWhereConditionString("STORE_NAME", stores.Split(','));
sql += sqlCondition;
}
else//如果没有传入线边仓名称。
{
sql = @"SELECT DISTINCT V.PARENT_KEY,V.PARENT_NAME
FROM V_LOCATION_RET V,WST_STORE S
WHERE S.LOCATION_KEY = V.LOCATION_KEY";
}
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetWorkShopInfo Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 根据线别数据获取线别所属的车间。
/// </summary>
/// <param name="lines">使用逗号分开的所有线别值。</param>
/// <returns>
/// 包含工厂车间信息的数据集对象。
/// 【LOCATION_KEY，LOCATION_NAME】。
/// </returns>
public DataSet GetFactoryRoomByLines(string lines)
{
DataSet resDS = new DataSet();
try
{
string sqlCondition = UtilHelper.BuilderWhereConditionString("c.LINE_NAME", lines.Split(','));
string sqlString = @"SELECT DISTINCT PARENT_KEY as LOCATION_KEY,PARENT_NAME as LOCATION_NAME
FROM V_LOCATION_RET a,FMM_LOCATION_LINE b,FMM_PRODUCTION_LINE c
WHERE a.LOCATION_KEY=b.LOCATION_KEY
AND b.LINE_KEY=c.PRODUCTION_LINE_KEY
AND LOCATION_LEVEL=9"
+ sqlCondition +
"ORDER BY a.PARENT_NAME ASC";
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { FMM_LOCATION_FIELDS.DATABASE_TABLE_NAME });
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, string.Empty);
}
catch (Exception ex)
{
LogService.LogError("GetAllLoactions Error: " + ex.Message);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, ex.Message);
}
return(resDS);
}
/// <summary>
/// 通过工序和车间主键获取线上仓。
/// </summary>
/// <param name="operation">工序名称,</param>
/// <param name="roomKey">车间主键,</param>
/// <param name="stores">拥有权限的线上仓名称，使用逗号分隔store1,store2...。</param>
/// <returns>包含线上仓的数据集对象。</returns>
public DataSet GetStores(string operation, string roomKey, string stores)
{
string msg = string.Empty;
DataSet dsReturn = new DataSet();
try
{
string condition = UtilHelper.BuilderWhereConditionString("STORE_NAME", stores.Split(','));
string sql = string.Format(@"SELECT STORE_KEY,STORE_NAME
FROM WST_STORE
WHERE OPERATION_NAME='{0}'
AND OBJECT_STATUS=1
AND LOCATION_KEY='{1}'
{2}",
operation.PreventSQLInjection(),
roomKey.PreventSQLInjection(),
condition);
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
msg = ex.Message;
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, msg);
LogService.LogError(string.Format("GetStores Error:{0}", ex.Message));
}
return(dsReturn);
}
/// <summary>
/// 根据线上仓数据获取所属的车间。
/// </summary>
/// <param name="lines">使用逗号分开的所有线上仓名称。</param>
/// <returns>
/// 包含工厂车间信息的数据集对象。
/// 【LOCATION_KEY，LOCATION_NAME】
/// </returns>
public DataSet GetFactoryRoomByStores(string stores)
{
DataSet resDS = new DataSet();
try
{
string sqlCondition = UtilHelper.BuilderWhereConditionString("a.STORE_NAME", stores.Split(','));
string sqlString = @"SELECT DISTINCT b.PARENT_KEY as LOCATION_KEY,b.LOCATION_NAME
FROM WST_STORE a,
(SELECT LOCATION_KEY,LOCATION_KEY PARENT_KEY,LOCATION_NAME
FROM V_LOCATION_RET
WHERE LOCATION_LEVEL=5
UNION
SELECT LOCATION_KEY,PARENT_KEY,PARENT_NAME LOCATION_NAME
FROM V_LOCATION_RET
WHERE LOCATION_LEVEL=9) b
WHERE a.LOCATION_KEY=b.LOCATION_KEY"
+ sqlCondition +
"ORDER BY b.LOCATION_NAME ASC";
db.LoadDataSet(CommandType.Text, sqlString, resDS, new string[] { FMM_LOCATION_FIELDS.DATABASE_TABLE_NAME });
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, string.Empty);
}
catch (Exception ex)
{
LogService.LogError("GetFactoryRoomByStores Error: " + ex.Message);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(resDS, ex.Message);
}
return(resDS);
}
/// <summary>
/// 根据线别名称和工厂车间主键获取线别信息。
/// </summary>
/// <param name="factoryRoomKey">工厂车间主键。</param>
/// <param name="lines">可选，使用“逗号(,)”分开的线别名称字符串。“X01,X02,C01...”</param>
/// <returns>
/// 包含线别信息的数据集对象。
/// [LINE_NAME,PRODUCTION_LINE_KEY,LINE_CODE,ROOM_NAME,ROOM_KEY]
/// </returns>
public DataSet GetLinesInfo(string factoryRoomKey, string lines)
{
DataSet dsReturn = new DataSet();
string sqlCondition = "";
const string CONST_SQL = @"SELECT distinct a.LINE_NAME,a.PRODUCTION_LINE_KEY,a.LINE_CODE,c.ROOM_NAME,c.ROOM_KEY
FROM FMM_PRODUCTION_LINE a
LEFT JOIN FMM_LOCATION_LINE b ON a.PRODUCTION_LINE_KEY=b.LINE_KEY
LEFT JOIN V_LOCATION c ON b.LOCATION_KEY=c.AREA_KEY
WHERE c.ROOM_KEY='{0}'";
try
{
string sql = string.Format(CONST_SQL, factoryRoomKey.PreventSQLInjection());
//线别名称长度>0,即方法参数传入了线别名称。
if (!string.IsNullOrEmpty(lines))
{
sqlCondition = UtilHelper.BuilderWhereConditionString("LINE_NAME", lines.Split(','));
sql += sqlCondition;
}
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetLinesInfo Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 根据线别名称获取线别信息。
/// </summary>
/// <param name="lines">使用“逗号(,)”分开的线别名称字符串。“X01,X02,C01...”</param>
/// <returns>包含线别信息的数据集对象。</returns>
public DataSet GetLinesInfo(string lines)
{
DataSet dsReturn = new DataSet();
string sqlCondition = "";
try
{
string sql = "";
//线别名称长度>0,即方法参数传入了线别名称。
if (lines.Length > 0)
{
sql = @"SELECT PRODUCTION_LINE_KEY,LINE_NAME,LINE_CODE FROM FMM_PRODUCTION_LINE WHERE 1=1";
sqlCondition = UtilHelper.BuilderWhereConditionString("LINE_NAME", lines.Split(','));
sql += sqlCondition;
}
else//如果没有传入线别名称。
{
sql = @"SELECT PRODUCTION_LINE_KEY,LINE_NAME,LINE_CODE FROM FMM_PRODUCTION_LINE WHERE LINE_NAME = ''";
}
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetLines Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 查询在线物料明细信息。
/// </summary>
/// <param name="model">
/// 包含查询条件对象。
/// </param>
/// <param name="storeMaterialKey">
/// 在线物料主键
/// </param>
/// <returns>包含在线物料信息的数据集。</returns>
public DataSet QueryDetail(OnlineMaterialQueryModel model, string storeMaterialKey)
{
DataSet dsReturn = new DataSet();
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.Append(@"SELECT A.STORE_MATERIAL_KEY,
A.MATERIAL_LOT,
A.CURRENT_QTY,
A.MATERIAL_SUPPLIER,
D.UNIT
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.AppendFormat(" AND A.STORE_MATERIAL_KEY = '{0}'", storeMaterialKey.PreventSQLInjection());
dsReturn = this.db.ExecuteDataSet(CommandType.Text, sbSql.ToString());
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotList Error: " + ex.Message);
}
return(dsReturn);
}
//有无组柜信息查询
public DataSet QueryInf(string _arkCode, string _status, string _palletNo)
{
DataSet dsReturn = new DataSet();
string _arkFlag = string.Empty;
StringBuilder sBuilder = new StringBuilder();
try
{
sBuilder.Append(@"SELECT B.CONTAINER_CODE,A.PALLET_NO,A.SAP_NO,A.WORKNUMBER,B.CREATOR,B.CDATE,CASE WHEN A.ARK_FLAG ='1' THEN '已组柜' ELSE '未组柜' END AS ARK_FLAG
from dbo.WIP_CONSIGNMENT A
left join AWMS.dbo.AWMS_CONTAINER_DETAIL B
on A.PALLET_NO = B.PALLET_NO
WHERE A.ISFLAG = 1 AND A.CS_DATA_GROUP = '3'");
if (!string.IsNullOrEmpty(_arkCode))
{
sBuilder.AppendFormat(" AND B.CONTAINER_CODE = '{0}'", _arkCode.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_status))
{
if (_status == "已组柜")
{
_arkFlag = "1";
}
else if (_status == "未组柜")
{
_arkFlag = "0";
}
sBuilder.AppendFormat(" AND A.ARK_FLAG = '{0}'", _arkFlag.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_palletNo))
{
string palletNo = _palletNo;
if (!string.IsNullOrEmpty(palletNo))
{
string palletNos = UtilHelper.BuilderWhereConditionString("A.PALLET_NO", palletNo.Split(new char[] { ',', '\n', '#' }));
sBuilder.AppendFormat(palletNos);
}
}
sBuilder.Append(" ORDER BY A.ARK_FLAG DESC");
dsReturn = db.ExecuteDataSet(CommandType.Text, sBuilder.ToString());
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("QueryInf Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 获取物料耗用信息。
/// </summary>
/// <param name="operations">工序名称,使用逗号分隔:op1,op2...</param>
/// <param name="stores">线上仓名称，使用逗号分隔:store1,store2...</param>
/// <returns>包含物料耗用信息的数据集对象。</returns>
public DataSet GetMaterialUsed(string operations, string stores)
{
DataSet dsReturn = new DataSet();
try
{
string sql = @" SELECT ROW_NUMBER() OVER(ORDER BY A.MATERIAL_LOT) AS ROWNUMBER,
A.MATERIAL_USED_DETAIL_KEY,A.MATERIAL_USED_KEY,A.MATERIAL_LOT,A.OPERATION_KEY,A.ROUTE_OPERATION_NAME,
A.LOCATION_KEY,A.LOCATION_NAME,A.EQUIPMENT_KEY,A.EQUIPMENT_NAME,A.SHIFT_NAME,A.USED_TIME,A.OPERATOR,A.MATNR,A.MATXT,A.LLIEF,
A.USED_QTY,A.ERFME,A.STORE_NAME,A.STIR_TIME,A.PRINT_QTY,A.STATUS
FROM
(SELECT DISTINCT W.MATERIAL_USED_DETAIL_KEY,U.MATERIAL_USED_KEY,D.MATERIAL_LOT,U.OPERATION_KEY,V.ROUTE_OPERATION_NAME,
F.LOCATION_KEY,F.LOCATION_NAME,E.EQUIPMENT_KEY,E.EQUIPMENT_NAME,U.SHIFT_NAME,U.USED_TIME,U.OPERATOR,I.MATNR,
I.MATXT,I.LLIEF,W.USED_QTY,I.ERFME,S.STORE_NAME,W.STIR_TIME,W.PRINT_QTY,W.STATUS
FROM
FMM_LOCATION F,
WST_STORE S,
WST_STORE_MATERIAL M,
WST_STORE_MATERIAL_DETAIL D,
WST_SAP_ISSURE I,
WST_MATERIAL_USED_DETAIL W,
WST_MATERIAL_USED U,
POR_ROUTE_OPERATION_VER V,
EMS_EQUIPMENTS E
WHERE F.LOCATION_KEY = S.LOCATION_KEY
AND S.STORE_KEY = M.STORE_KEY
AND M.STORE_MATERIAL_KEY = D.STORE_MATERIAL_KEY
AND D.STORE_MATERIAL_DETAIL_KEY = I.STORE_MATERIAL_DETAIL_KEY
AND D.STORE_MATERIAL_DETAIL_KEY = W.STORE_MATERIAL_DETAIL_KEY
AND W.MATERIAL_USED_KEY = U.MATERIAL_USED_KEY
AND U.OPERATION_KEY = V.ROUTE_OPERATION_VER_KEY
AND U.EQUIPMENT_KEY = E.EQUIPMENT_KEY AND STATUS!=0 ";
string sqlConditionStore = UtilHelper.BuilderWhereConditionString("S.STORE_NAME", stores.Split(','));
sql += sqlConditionStore;
string sqlConditionStore1 = UtilHelper.BuilderWhereConditionString("V.ROUTE_OPERATION_NAME", operations.Split(','));
sql += sqlConditionStore1;
sql += ") A";
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetMaterialUsed Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 根据工序名（用","分开的工序名字符串），线别名（用","分开的工序名字符串），线边仓类型，班别名称查询等待返工或退库操作的批次信息。
/// </summary>
/// <param name="operations">工序名（用","分开的工序名字符串）</param>
/// <param name="lines">线别名（用","分开的工序名字符串</param>
/// <param name="storeTypes">线别仓类型。</param>
/// <param name="shiftName">班别名称。</param>
/// <returns>
/// 查询得到的包含等待返工或退库操作批次信息的数据集对象。
/// </returns>
public DataSet SearchLotWaitingForTransact(string operations, string lines, string storeTypes, string shiftName)
{
DataSet dsReturn = new DataSet();
try
{
string sql = @"SELECT T.WORKORDER_NUMBER,A.STORE_NAME,T.ITEM_NO,T.ITEM_QTY,B.LINE_NAME,C.ROUTE_STEP_NAME,CSD.SHIFT_VALUE
FROM WST_STORE_MAT T
INNER JOIN WST_STORE A ON T.STORE_KEY = A.STORE_KEY
INNER JOIN FMM_PRODUCTION_LINE B ON T.LINE_KEY=B.PRODUCTION_LINE_KEY
INNER JOIN POR_ROUTE_STEP C ON T.STEP_KEY=C.ROUTE_STEP_KEY
LEFT JOIN CAL_SCHEDULE_DAY CSD ON T.SHIFT_NAME=CSD.DKEY
WHERE A.OBJECT_STATUS = 1
AND A.REQUEST_FLAG=1
AND T.OBJECT_STATUS=0
AND T.DELETED_TERM_FLAG = 0";
if (shiftName.Length > 0)
{
sql += " AND CSD.SHIFT_VALUE = '" + shiftName + "'";
}
if (operations.Length > 0)
{
string operationCondition = UtilHelper.BuilderWhereConditionString("C.ROUTE_STEP_NAME", operations.Split(','));
sql += operationCondition;
}
if (lines.Length > 0)
{
string lineCondition = UtilHelper.BuilderWhereConditionString("B.LINE_NAME", lines.Split(','));
sql += lineCondition;
}
if (storeTypes.Length > 0)
{
string storeCondition = UtilHelper.BuilderWhereConditionString("A.STORE_TYPE", storeTypes.Split(','));
sql += storeCondition;
}
sql += " ORDER BY T.WORKORDER_NUMBER,A.STORE_NAME,T.ITEM_NO";
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("SearchLotWaitingForTransact Error:" + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 获取电池片回收的可选问题工序。
/// </summary>
/// <param name="lotKey">回收电池片的批次主键。。</param>
/// <param name="operations">拥有权限的工序名称，使用逗号(,)分隔。</param>
/// <returns>包含问题工序的数据集对象。</returns>
public DataSet GetRecoveredTroubleStepInfo(string lotKey, string operations)
{
DataSet dsReturn = new DataSet();
try
{
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat(@"SELECT *
FROM V_PROCESS_PLAN
WHERE ENTERPRISE_STATUS=1 AND ROUTE_STATUS=1
AND ROUTE_STEP_KEY IN(
SELECT DISTINCT a.STEP_KEY
FROM WIP_PATCH a
WHERE a.PATCH_LOT_KEY='{0}'
UNION ALL
SELECT DISTINCT a.STEP_KEY
FROM WIP_SCRAP a
LEFT JOIN WIP_TRANSACTION b ON a.TRANSACTION_KEY=b.TRANSACTION_KEY
WHERE b.PIECE_KEY='{0}'
)
AND ROUTE_ENTERPRISE_VER_KEY IN (
SELECT DISTINCT a.ENTERPRISE_KEY
FROM WIP_PATCH a
WHERE a.PATCH_LOT_KEY='{0}'
UNION ALL
SELECT DISTINCT a.ENTERPRISE_KEY
FROM WIP_SCRAP a
LEFT JOIN WIP_TRANSACTION b ON a.TRANSACTION_KEY=b.TRANSACTION_KEY
WHERE b.PIECE_KEY='{0}') ",
lotKey.PreventSQLInjection());
sbSql.Append(UtilHelper.BuilderWhereConditionString("ROUTE_STEP_NAME", operations.PreventSQLInjection().Split(',')));
sbSql.Append(" ORDER BY ((ROUTE_SEQ+1)*10000000)+ROUTE_STEP_SEQ");
//执行查询。
dsReturn = db.ExecuteDataSet(CommandType.Text, sbSql.ToString());
ReturnMessageUtils.AddServerReturnMessage(dsReturn, string.Empty);
}
catch (Exception ex)
{
ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetTroubleStep Error: " + ex.Message);
}
return(dsReturn);
}
/// <summary>
/// 通过工单号和物料料号获取线上仓。
/// </summary>
/// <param name="workOrder">工单号,</param>
/// <param name="materialCode">物料编码,</param>
/// <param name="stores">拥有权限的线上仓名称,使用逗号分隔,store1,store2...。</param>
/// <returns>包含线上仓的数据集对象。</returns>
public DataSet GetStoreByMaterialCode(string workOrder, string materialCode, string stores)
{
const string CONST_GET_SAP_OPERATIONS_SQL = "SELECT DISTINCT WORK_CENTER FROM POR_WORK_ORDER_BOM WHERE ORDER_NUMBER='{0}' AND MATERIAL_CODE='{1}'";
DataSet dsReturn = new DataSet();
try
{
//根据工单号和物料编码获取工作中心（SAP工序）
DataSet dsSAPWorkCenter = db.ExecuteDataSet(CommandType.Text, string.Format(CONST_GET_SAP_OPERATIONS_SQL,
workOrder.PreventSQLInjection(),
materialCode.PreventSQLInjection()));
string strSAPWorkCenter = string.Empty;
foreach (DataRow dr in dsSAPWorkCenter.Tables[0].Rows)
{
strSAPWorkCenter += "'" + dr["WORK_CENTER"].ToString().PreventSQLInjection() + "',";
}
strSAPWorkCenter = strSAPWorkCenter.TrimEnd(',');
//通过WORK_CENTER获取MES工序
DataSet dsSAPMES = CrmAttributeEngine.GetDistinctColumnsData(db, "WORK_CENTER,OPERATION_NAME", "SAP_MES_OPERATIONS");
DataTable dt = dsSAPMES.Tables[0];
DataRow[] drs = dt.Select("WORK_CENTER in (" + strSAPWorkCenter + ")");
string strOpearions = string.Empty;
foreach (DataRow dr in drs)
{
strOpearions += "'" + dr["OPERATION_NAME"].ToString().PreventSQLInjection() + "',";
}
strOpearions = strOpearions.TrimEnd(',');
//通过MES工序和拥有权限的线上仓名称获取线上仓名称。
string sql = string.Format("SELECT STORE_NAME FROM WST_STORE WHERE OPERATION_NAME IN ({0}) AND OBJECT_STATUS=1", strOpearions);
sql += UtilHelper.BuilderWhereConditionString("STORE_NAME", stores.Split(','));
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
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("GetStoreByMaterialCode Error:{0}", ex.Message));
}
return(dsReturn);
}
/// <summary>
/// 根据线上仓名称查询可用线上仓信息。
/// </summary>
/// <param name="storeName">线上仓名称，使用逗号(,)分隔。</param>
/// <returns>包含重工线上仓信息的数据集对象。</returns>
public DataSet GetReworkStore(string storeName)
{
DataSet dsReturn = new DataSet();
string sqlCommand = string.Empty;
try
{
sqlCommand = @"SELECT A.STORE_KEY,A.STORE_NAME,A.STORE_TYPE FROM WST_STORE A WHERE 1=1 AND A.OBJECT_STATUS='1' ";
if (!string.IsNullOrEmpty(storeName))
{
sqlCommand = sqlCommand + UtilHelper.BuilderWhereConditionString("A.STORE_NAME", storeName.Split(','));
}
sqlCommand = sqlCommand + " ORDER BY STORE_NAME";
dsReturn = db.ExecuteDataSet(CommandType.Text, sqlCommand);
dsReturn.Tables[0].TableName = WST_STORE_FIELDS.DATABASE_TABLE_NAME;
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetReworkStroe Error: " + 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="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="_materialLot">物料批号。</param>
/// <param name="_gongXuName">工序名称。</param>
/// <param name="_wuLiaoNumber">物料料号。</param>
/// <param name="_factoryRoomName">车间名称。</param>
/// <param name="_wuLiaoMiaoShu">物料描述。</param>
/// <param name="_equipmentName">设备名称。</param>
/// <param name="_gongYingShang">供应商名称。</param>
/// <param name="_banCi">班次。</param>
/// <param name="_lineCang">线上仓名称。</param>
/// <param name="_jobNumber">工号。</param>
/// <param name="_startTime">耗用时间。</param>
/// <param name="_endTime">耗用时间。</param>
/// <param name="_stores">线上仓名称。使用逗号分隔：store1,store2...</param>
/// <param name="_operations">工序名称。使用逗号分隔：op1,op2...</param>
/// <returns></returns>
public DataSet GetStoreMaterialDetail(string _materialLot, string _gongXuName, string _wuLiaoNumber,
string _factoryRoomName, string _wuLiaoMiaoShu, string _equipmentName, string _gongYingShang,
string _banCi, string _lineCang, string _jobNumber,
DateTime _startTime, DateTime _endTime,
string _stores, string _operations)
{
string sqlCondition = "";
string sqlConditionStore = "";
DataSet dsReturn = new DataSet();
try
{
string sql = @" SELECT ROW_NUMBER() OVER(ORDER BY A.MATERIAL_LOT) AS ROWNUMBER,
A.MATERIAL_LOT,A.MATNR,A.MATXT,A.USED_QTY,A.ERFME,A.LLIEF,A.STORE_NAME,A.ROUTE_OPERATION_NAME,
A.LOCATION_NAME,A.EQUIPMENT_NAME,A.SHIFT_NAME,A.USED_TIME,A.OPERATOR,A.STIR_TIME,A.PRINT_QTY
FROM
(
SELECT DISTINCT D.MATERIAL_LOT,I.MATNR,I.MATXT,W.USED_QTY,I.ERFME,I.LLIEF,S.STORE_NAME,V.ROUTE_OPERATION_NAME,
F.LOCATION_NAME,E.EQUIPMENT_NAME,U.SHIFT_NAME,U.USED_TIME,U.OPERATOR,W.STIR_TIME,W.PRINT_QTY
FROM FMM_LOCATION F,
WST_STORE S,
WST_STORE_MATERIAL M,
WST_STORE_MATERIAL_DETAIL D,
WST_SAP_ISSURE I,
WST_MATERIAL_USED_DETAIL W,
WST_MATERIAL_USED U,
POR_ROUTE_OPERATION_VER V,
EMS_EQUIPMENTS E
WHERE F.LOCATION_KEY = S.LOCATION_KEY
AND S.STORE_KEY = M.STORE_KEY
AND M.STORE_MATERIAL_KEY = D.STORE_MATERIAL_KEY
AND D.STORE_MATERIAL_DETAIL_KEY = I.STORE_MATERIAL_DETAIL_KEY
AND D.STORE_MATERIAL_DETAIL_KEY = W.STORE_MATERIAL_DETAIL_KEY
AND W.MATERIAL_USED_KEY = U.MATERIAL_USED_KEY
AND U.OPERATION_KEY = V.ROUTE_OPERATION_VER_KEY
AND U.EQUIPMENT_KEY = E.EQUIPMENT_KEY ";
if (!string.IsNullOrEmpty(_materialLot))
{
sql += string.Format(" AND D.MATERIAL_LOT LIKE '{0}%'", _materialLot.PreventSQLInjection());
}
if (string.IsNullOrEmpty(_gongXuName))
{
sqlCondition = UtilHelper.BuilderWhereConditionString("ROUTE_OPERATION_NAME", _operations.Split(','));
sql += sqlCondition;
}
else
{
sql += string.Format(" AND V.ROUTE_OPERATION_NAME='{0}'", _gongXuName.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_wuLiaoNumber))
{
sql += string.Format(" AND I.MATNR LIKE '{0}%'", _wuLiaoNumber.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_factoryRoomName))
{
sql += string.Format(" AND F.LOCATION_NAME='{0}'", _factoryRoomName.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_wuLiaoMiaoShu))
{
sql += string.Format(" AND I.MATXT LIKE '{0}%'", _wuLiaoMiaoShu.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_equipmentName))
{
sql += string.Format(" AND E.EQUIPMENT_NAME='{0}'", _equipmentName.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_gongYingShang))
{
sql += string.Format(" AND I.LLIEF LIKE '{0}%'", _gongYingShang.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_banCi))
{
sql += string.Format(" AND U.SHIFT_NAME='{0}'", _banCi.PreventSQLInjection());
}
if (string.IsNullOrEmpty(_lineCang))
{
sqlConditionStore = UtilHelper.BuilderWhereConditionString("STORE_NAME", _stores.Split(','));
sql += sqlConditionStore;
}
else
{
sql += string.Format(" AND S.STORE_NAME='{0}'", _lineCang.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_jobNumber))
{
sql += string.Format(" AND U.OPERATOR LIKE '{0}%'", _jobNumber.PreventSQLInjection());
}
if (!string.IsNullOrEmpty(_startTime.ToString()))
{
sql += string.Format(" AND U.USED_TIME>='{0}'", _startTime);
}
if (!string.IsNullOrEmpty(_endTime.ToString()))
{
sql += string.Format(" AND U.USED_TIME<='{0}'", _endTime);
}
sql += ") A";
dsReturn = db.ExecuteDataSet(CommandType.Text, sql);
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, "");
}
catch (Exception ex)
{
FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dsReturn, ex.Message);
LogService.LogError("GetStoreMaterialDetail Error: " + ex.Message);
}
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);
}