/*******************************************************************************
* Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
* 类的信息：
* 1.程序名称：PMModuleLogic.cs
* 2.功能描述：生产管理
* 编辑履历：
* 作者 日期 版本 修改内容
* 陈冰 2014/09/3 1.00 新建
*******************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Basics.Library;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels.PMModule;
using Oracle.ManagedDataAccess.Client;
namespace Dongke.IBOSS.PRD.Service.PMModuleLogic
{
///
/// 生产管理
///
public partial class PMModuleLogic
{
///
/// 构建 计数/检验时返回的信息
///
///
public static DataTable CreateBarCodeResultTable()
{
// 注意：更新表字段时，一定把后续引用的字段全部更新一遍
DataTable barCodeResultTable = new DataTable("BarCodeTable");
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_errMsg.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsID.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsCode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserID.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingNum.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_mouldCode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_ispublicbody.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingdate.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_isReFire.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_isLengBu.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_missFlag.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoID.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoCode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_MaterialCode.ToString());
//xuwei add 2020-03-04 添加釉料属性
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_glazeName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_deliverLimitCycle.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_barcode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_WaterLabelCode.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_CodeCheckFlag.ToString());
//漏气标识 内漏标识 xuwei add 2020-06-11
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag1.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag2.ToString());
//增加重烧名称等相关列 fubin add 2020-06-30
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag3.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_specialRepairFlagName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_isReFireName.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag1Name.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag2Name.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_LeakFlag3Name.ToString());
barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_lengBuName.ToString());
//增加冷补返工工序
//barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_ReworkProcedureId.ToString());
barCodeResultTable.Columns.Add("out_ReworkProcedureId", typeof(string));
//养水标识 试水标识 qq add 2022-12-28
barCodeResultTable.Columns.Add("out_LeakFlag4", typeof(string));
barCodeResultTable.Columns.Add("out_LeakFlag5", typeof(string));
barCodeResultTable.Columns.Add("out_LeakFlag4Name", typeof(string));
barCodeResultTable.Columns.Add("out_LeakFlag5Name", typeof(string));
// 包装装板用
barCodeResultTable.Columns.Add("GOODSMODELforCheck");
// 最大装板数量
barCodeResultTable.Columns.Add("PlateLimitNum", typeof(int));
barCodeResultTable.Columns.Add("DefectFlagID", typeof(int));
barCodeResultTable.Columns.Add("pdid", typeof(int));
barCodeResultTable.Columns.Add("InspectionLevel", typeof(string));
barCodeResultTable.Columns.Add("PackingDefect", typeof(string));
barCodeResultTable.Columns.Add("InspectionGoodsLevel", typeof(string));
barCodeResultTable.Columns.Add("offlineFlag", typeof(string));
barCodeResultTable.Columns.Add("recyclingFlag", typeof(string));
barCodeResultTable.Columns.Add("waterLabelCode", typeof(string));
//修磨
barCodeResultTable.Columns.Add("out_LeakFlag7", typeof(string));
barCodeResultTable.Columns.Add("out_LeakFlag7Name", typeof(string));
return barCodeResultTable;
}
///
/// 构建 校验窑车号时返回的信息
///
///
public static DataTable CreateKilnCarResultTable()
{
// 注意：更新表字段时，一定把后续引用的字段全部更新一遍
DataTable kilnCarResultTable = new DataTable("KilnCarTable");
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_errMsg.ToString());
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCarID.ToString());
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCarName.ToString());
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnID.ToString());
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCode.ToString());
kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnName.ToString());
return kilnCarResultTable;
}
///
/// 校验窑车是否可用
///
/// 工序ID
/// 窑车号
/// 类别
/// 用户基本信息
/// CheckKilnCarResultEntity实体类
///
/// 陈冰 2014.09.26 新建
///
public static CheckKilnCarResultEntity CheckKilnCar(int pProcedureId, string kilnCarCode, int pModelType, SUserInfo sUserInfo)
{
CheckKilnCarResultEntity kilnCarResultEntity = new CheckKilnCarResultEntity();
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("in_kilnCarCode",OracleDbType.NVarchar2, kilnCarCode,ParameterDirection.Input),
new OracleParameter("in_modelType",OracleDbType.Int32, pModelType,ParameterDirection.Input),
new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("in_procedureid",OracleDbType.Int32, pProcedureId,ParameterDirection.Input),
// 验证能否卸窑
new OracleParameter("in_UserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
DataSet returnDataSet = oracleConn.ExecStoredProcedure("PRO_PM_CheckKilnCar", paras);
kilnCarResultEntity.ErrMsg = paras[3].Value.ToString() == "null" ? string.Empty : paras[3].Value.ToString().Replace("\\n\\r", "\n\r");
if (returnDataSet == null || returnDataSet.Tables.Count <= 0)
{
kilnCarResultEntity.KilnCarInfos = new List();
}
else
{
kilnCarResultEntity.KilnCarInfos = DataConvert.TableConvertToObject(returnDataSet.Tables[0]);
}
return kilnCarResultEntity;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 由工序获取产缺陷列表
///
/// 工序ID
/// DataSet
public static DataSet GetDefectByProcedure(int procedureID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select TP_MST_Defect.DefectID as DefectFlagID,TP_MST_Defect.DefectCode,
TP_MST_Defect.DefectName as ViewDefectFlagName,
concat(TP_MST_Defect.DefectCode||'->',TP_MST_Defect.DefectName) as DefectFlagName
from TP_PC_ProcedureDefect
left join TP_MST_Defect on TP_PC_ProcedureDefect.DefectID=TP_MST_Defect.DefectID
where TP_MST_Defect.valueflag = '1' and TP_PC_ProcedureDefect.procedureID=" + procedureID;
DataSet ds = con.GetSqlResultToDs(sqlString);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 由当前检验工序ID和缺陷Code获取缺陷数据
///
/// 当前检验工序ID
/// 缺陷Code
/// object
///
/// 陈冰 2014.10.04 新建
///
public static object GetDefectByProcedureIDAndDefectCode(int procedureID, string defectCode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select TP_MST_Defect.DefectID,
TP_MST_Defect.DefectName,
TP_PC_ProcedureDefect.procedureID
from TP_MST_Defect
left join TP_PC_ProcedureDefect on TP_MST_Defect.DefectID =
TP_PC_ProcedureDefect.DefectID
and TP_PC_ProcedureDefect.procedureID = :procedureID
where TP_MST_Defect.DefectCode = :defectCode and TP_MST_Defect.Valueflag='1'";
OracleParameter[] paras = new OracleParameter[] {
new OracleParameter(":procedureID",procedureID),
new OracleParameter(":defectCode",defectCode),
};
DataSet resultds = con.GetSqlResultToDs(sqlString, paras);
if (resultds != null && resultds.Tables.Count > Constant.INT_IS_ZERO)
{
string errMsg = string.Empty;
int defectID = 0;
string defectName = string.Empty;
if (resultds.Tables[0].Rows.Count == Constant.INT_IS_ZERO)
{
errMsg = "缺陷在系统中不存在";
}
else
{
if (string.IsNullOrEmpty(resultds.Tables[0].Rows[0]["ProcedureID"].ToString()))
{
errMsg = "当前工序没有配置该缺陷";
}
else
{
defectID = int.Parse(resultds.Tables[0].Rows[0]["DefectID"].ToString());
defectName = resultds.Tables[0].Rows[0]["DefectName"].ToString();
}
}
return new { ErrMsg = errMsg, DefectID = defectID, DefectName = defectName };
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据产品ID查出缺陷位置
///
/// 产品ID
/// DataSet
public static DataSet GetDefectLocaionByGoodsID(int goodsID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select TP_MST_DEFECTPOSITION.DEFECTPOSITIONID,TP_MST_DEFECTPOSITION.DEFECTPOSITIONCODE,TP_MST_DEFECTPOSITION.DEFECTPOSITIONNAME
,concat(TP_MST_DEFECTPOSITION.DEFECTPOSITIONCODE||'->',TP_MST_DEFECTPOSITION.DEFECTPOSITIONNAME) as DEFECTPOSITIONCODEANDNAME from tp_mst_goodsdefectposition left join TP_MST_DEFECTPOSITION
on tp_mst_goodsdefectposition.defectpositionid=TP_MST_DEFECTPOSITION.defectpositionid
where tp_mst_goodsdefectposition.goodsid=" + goodsID;
DataSet ds = con.GetSqlResultToDs(sqlString);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据缺陷位置
///
/// 用户基本信息
/// DataSet
public static DataSet GetDefectLocaion(SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select DEFECTPOSITIONID,
DEFECTPOSITIONCODE,
DEFECTPOSITIONNAME,
concat(DEFECTPOSITIONCODE||'->',DEFECTPOSITIONNAME) as DEFECTPOSITIONCODEANDNAME
from TP_MST_DEFECTPOSITION
where AccountID=:accountID and ValueFlag=1";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":accountID",sUserInfo.AccountID),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据产品ID查出缺陷位置
///
/// 产品ID
/// 位置编码
/// object
public static object GetDefectPositionByGoodsIDAndPositionCode(int goodsID, string positionCode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select tp_mst_defectposition.defectpositionid,
tp_mst_defectposition.defectpositionname,
tp_mst_goodsdefectposition.goodsid
from tp_mst_defectposition
left join tp_mst_goodsdefectposition on tp_mst_goodsdefectposition.defectpositionid =
tp_mst_defectposition.defectpositionid
and tp_mst_goodsdefectposition.goodsid =:goodsid
where tp_mst_defectposition.defectpositioncode = :defectpositioncode and tp_mst_defectposition.Valueflag='1'";
OracleParameter[] paras = new OracleParameter[] {
new OracleParameter(":goodsid",goodsID),
new OracleParameter(":defectpositioncode",positionCode),
};
DataSet resultds = con.GetSqlResultToDs(sqlString, paras);
if (resultds != null && resultds.Tables.Count > Constant.INT_IS_ZERO)
{
string errMsg = string.Empty;
int defectPositionID = 0;
string defectpositionname = string.Empty;
if (resultds.Tables[0].Rows.Count == Constant.INT_IS_ZERO)
{
errMsg = "缺陷位置在系统中不存在";
}
else
{
if (string.IsNullOrEmpty(resultds.Tables[0].Rows[0]["goodsid"].ToString()))
{
errMsg = "当前产品没有配置该缺陷位置";
}
else
{
defectPositionID = int.Parse(resultds.Tables[0].Rows[0]["defectPositionID"].ToString());
defectpositionname = resultds.Tables[0].Rows[0]["defectpositionname"].ToString();
}
}
return new { ErrMsg = errMsg, DefectPositionID = defectPositionID, DefectPositionName = defectpositionname };
}
else
{
return null;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 通过条码查出责任工序
///
/// 产品条码
/// 缺陷ID
/// DataSet
public static DataSet GetDutyProcedureByBarCode(string barcode, int defectid, int accountid, int procedureid = 0)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
DataSet dsReturn = new DataSet();
// string sqlString = @"select TP_PM_ProductionDataIn.ProductionDataID, DutyProcedureID,DutyProcedureCode,DutyProcedureName,NodeType,ModelType from
// (
// select TP_PM_ProductionDataIn.ProductionDataID,TP_PM_ProductionDataIn.ProcedureID as DutyProcedureID,
// TP_PM_ProductionDataIn.ProcedureCode as DutyProcedureCode,
// TP_PM_ProductionDataIn.ProcedureName as DutyProcedureName,
// TP_PM_ProductionDataIn.NodeType,
// TP_PM_ProductionDataIn.ModelType
// from TP_PM_ProductionDataIn
// where valueflag = '1' and TP_PM_ProductionDataIn.Barcode=:Barcode
// order by TP_PM_ProductionDataIn.ProductionDataID
// ) TP_PM_ProductionDataIn
// where exists
// (
// select
// TP_PC_DefectProcedureJobs.DefectID,
// TP_PC_DefectProcedureJobs.ProductionLineID,
// TP_PC_DefectProcedureJobs.ProcedureID,
// TP_PC_DefectProcedureJobs.NodeNo,
// TP_PC_DefectProcedureJobs.JobsID
// from TP_PC_DefectProcedureJobs
// where TP_PC_DefectProcedureJobs.DefectID=:DefectID
// and TP_PM_ProductionDataIn.DutyProcedureID=TP_PC_DefectProcedureJobs.ProcedureID
// )";
//string sqlString =
//"SELECT pdin.ProductionDataID\n" +
//" ,pdin.ProcedureID AS DutyProcedureID\n" +
//" ,pdin.ProcedureCode AS DutyProcedureCode\n" +
//" ,pdin.ProcedureName AS DutyProcedureName\n" +
//" ,pdin.NodeType\n" +
//" ,pdin.ModelType\n" +
//" ,pdin.classessettingid\n" +
//" ,pdin.userid\n" +
//" ,pdin.usercode\n" +
//" ,pdin.username\n" +
//" FROM TP_PM_ProductionDataIn pdin\n" +
//" WHERE pdin.valueflag = '1'\n" +
//" AND pdin.accountid = :accountid\n" +
//" AND pdin.Barcode = :Barcode\n" +
//" AND EXISTS (SELECT 1\n" +
//" FROM TP_PC_DefectProcedureJobs dpJobs\n" +
//" WHERE dpJobs.DefectID = :DefectID\n" +
//" AND pdin.ProcedureID = dpJobs.ProcedureID)\n" +
//" ORDER BY pdin.ProductionDataID";
// 去掉重复工序（取最后一个）
string sqlString = "select pdin.ProductionDataID\n" +
" ,pdin.ProcedureID as DutyProcedureID\n" +
" ,pdin.ProcedureCode as DutyProcedureCode\n" +
" ,pdin.ProcedureName as DutyProcedureName\n" +
" ,pdin.NodeType\n" +
" ,pdin.ModelType\n" +
" ,pdin.classessettingid\n" +
" ,pdin.userid\n" +
" ,pdin.usercode\n" +
" ,pdin.username\n" +
" from TP_PM_ProductionDataIn pdin\n" +
" where pdin.productiondataid in\n" +
" (select max(pdi.productiondataid) pdid\n" +
" from TP_PM_ProductionDataIn pdi\n" +
" where pdi.valueflag = '1'\n" +
" and pdi.accountid = :accountid\n" +
" and pdi.Barcode = :Barcode\n";
// 如果是3#半检一检或者3#半检二检，则只能选择交坯工序
if (procedureid == 89 || procedureid == 95)
{
sqlString += " and pdi.modeltype = 5 ";
}
sqlString +=
" and exists\n" +
" (select 1\n" +
" from TP_PC_DefectProcedureJobs dpJobs\n" +
" where dpJobs.DefectID = :DefectID\n" +
" and pdi.ProcedureID = dpJobs.ProcedureID)\n" +
" group by pdi.ProcedureID)\n" +
" order by pdin.ProductionDataID";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":accountid",OracleDbType.Int32, accountid,ParameterDirection.Input),
new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
new OracleParameter(":DefectID",OracleDbType.Int32, defectid,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
ds.Tables[0].TableName = "TProcedure";
//sqlString = "Select DictionaryID,DictionaryValue"
// + " from TP_MST_DataDictionary where valueflag = 1 and DictionaryType = 'ASE002' and AccountID = :AccountID";
//paras = new Oracle.ManagedDataAccess.Client.OracleParameter[]
// {
// new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",accountid),
// };
//DataSet ds2 = con.GetSqlResultToDs(sqlString, paras);
//ds2.Tables[0].TableName = "TDataDictionary";
string classessettingids = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
classessettingids = classessettingids + ds.Tables[0].Rows[i]["classessettingid"] + ",";
}
if (classessettingids.Length > 0)
{
classessettingids = classessettingids.Substring(0, classessettingids.Length - 1);
sqlString = "select j.procedureid,j.jobsid from TP_PC_DefectProcedureJobs j where j.Defectid=:Defectid " +
" and exists (SELECT * FROM tp_pc_classesdetail c where c.classessettingid IN(" + classessettingids + ") and c.ujobsid = j.jobsid ) ";
//sqlString = "select j.procedureid,j.jobsid from TP_PC_DefectProcedureJobs j where j.Defectid=:Defectid ";
paras = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":Defectid",defectid),
};
DataSet ds3 = con.GetSqlResultToDs(sqlString, paras);
ds3.Tables[0].TableName = "TP_PC_DefectProcedureJobs";
if (!dsReturn.Tables.Contains("TP_PC_DefectProcedureJobs"))
{
dsReturn.Tables.Add(ds3.Tables[0].Copy());
}
}
else
{
sqlString = "select j.procedureid,j.jobsid from TP_PC_DefectProcedureJobs j where j.Defectid=:Defectid ";
paras = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":Defectid",defectid),
};
DataSet ds3 = con.GetSqlResultToDs(sqlString, paras);
ds3.Tables[0].TableName = "TP_PC_DefectProcedureJobs";
if (!dsReturn.Tables.Contains("TP_PC_DefectProcedureJobs"))
{
dsReturn.Tables.Add(ds3.Tables[0].Copy());
}
}
if (!dsReturn.Tables.Contains("TProcedure"))
{
dsReturn.Tables.Add(ds.Tables[0].Copy());
}
//if (!dsReturn.Tables.Contains("TDataDictionary"))
//{
// dsReturn.Tables.Add(ds2.Tables[0].Copy());
//}
return dsReturn;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 通过条码与工序查出责任工号
///
/// 产品条码
/// 责任工序
/// DataSet
public static DataSet GetDutyProcedureCodeByBarCode(string barcode, int dutyProcedureID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select UserID,UserCode,UserName,Remarks,ProductionDataID from TP_PM_ProductionDataIn
where Barcode=:Barcode and ProcedureID=:ProcedureID and valueflag=1";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
new OracleParameter(":ProcedureID",OracleDbType.Int32, dutyProcedureID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 责任工序查出工号根据生产数据ID
///
///
///
public static DataSet GetDutyProcedureCodeByProductionDataID(int ProductionDataID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select UserID,UserCode,UserName,Remarks,ProductionDataID from TP_PM_ProductionDataIn
where ProductionDataID=:ProductionDataID";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":ProductionDataID",OracleDbType.Int32, ProductionDataID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选工序默认查出工种信息
///
/// 工号ID
/// 生产数据ID
/// DataSet
public static DataSet GetDutyJobsCodeByUser(int userID, int productionDataID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
// string sqlString = @"select distinct
// TP_PM_Producer.UserID,TP_MST_Jobs.JobsID,TP_MST_Jobs.JobsName,TP_MST_Jobs.JobsCode
// from TP_PM_Producer
// left join TP_MST_Jobs
// on TP_PM_Producer.UJobsID=TP_MST_Jobs.JobsID
// where TP_PM_Producer.UserID=:UserID
// and TP_PM_Producer.UJobsID in
// (
// select
// JobsID
// from TP_PC_DefectProcedureJobs where ProcedureID=
// (
// Select ProcedureID from TP_PM_ProductionDataIn where ProductionDataID=:ProductionDataID
// )
// )";
string sqlString = @"select distinct tp_pc_classesdetail.UserID,
TP_MST_Jobs.JobsID,
TP_MST_Jobs.JobsName,
TP_MST_Jobs.JobsCode
from tp_pc_classesdetail
left join TP_MST_Jobs on tp_pc_classesdetail.UJobsID = TP_MST_Jobs.JobsID
where tp_pc_classesdetail.UserID = :UserID
and tp_pc_classesdetail.UJobsID in
(select JobsID
from TP_PC_DefectProcedureJobs
where ProcedureID =
(Select ProcedureID
from TP_PM_ProductionDataIn
where ProductionDataID = :ProductionDataID)
)";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":UserID",OracleDbType.Int32, userID,ParameterDirection.Input),
new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选生产数据ID，用户ID及工种选出责任员工
///
/// 生产数据ID
/// 用户ID
/// 工种ID
/// DataSet
public static DataSet GetDutyStaffByUserIDAndJobs(int productionDataID, int userID, int jobs)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
// string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_PM_Producer.SJobsID
// from TP_PM_Producer
// left join TP_HR_Staff on TP_PM_Producer.StaffID=TP_HR_Staff.StaffID
// where TP_PM_Producer.ProductionDataID=:ProductionDataID
// and TP_PM_Producer.UserID=:UserID and TP_PM_Producer.UJobsID=:JobsID
// ";
string sqlString = @"select TP_HR_Staff.StaffID,
TP_HR_Staff.StaffCode,
TP_HR_Staff.StaffName,
TP_HR_Staff.StaffStatus,
tp_pc_classesdetail.SJobsID
from tp_pc_classesdetail
left join TP_HR_Staff on tp_pc_classesdetail.StaffID =
TP_HR_Staff.StaffID
where tp_pc_classesdetail.ClassesSettingID=(select ClassesSettingID from tp_pm_productiondatain where productiondataid=:ProductionDataID)
and tp_pc_classesdetail.UserID = :UserID
and tp_pc_classesdetail.UJobsID = :JobsID
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input),
new OracleParameter(":UserID",OracleDbType.Int32, userID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32, jobs,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据传入的实体获取进度考核奖惩信息
///
/// 查询实体
/// 用户基本信息
/// DataSet进度考核奖惩信息表
public static DataSet SearcStaffAdminRAPInfo(SearchProgressRAPEntity searchProgressRAPEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strIdList = string.Empty;
if (searchProgressRAPEntity.IDList != null && searchProgressRAPEntity.IDList.Length > 0)
{
strIdList = DataConvert.ConvertListToSqlInWhere(searchProgressRAPEntity.IDList);
}
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("in_staffID",OracleDbType.Int32,searchProgressRAPEntity.StaffID,ParameterDirection.Input),
new OracleParameter("in_rapType",OracleDbType.Double,searchProgressRAPEntity.RAPType,ParameterDirection.Input),
new OracleParameter("in_startRAPDate",OracleDbType.Date,searchProgressRAPEntity.StartRAPDate,ParameterDirection.Input),
new OracleParameter("in_endRAPDate",OracleDbType.Date,searchProgressRAPEntity.EndRAPDate,ParameterDirection.Input),
new OracleParameter("in_startRAPAmount",OracleDbType.Double,searchProgressRAPEntity.StartRAPAmount,ParameterDirection.Input),
new OracleParameter("in_endRAPAmount",OracleDbType.Double,searchProgressRAPEntity.EndRAPAmount,ParameterDirection.Input),
new OracleParameter("in_auditStatus",OracleDbType.Int32,searchProgressRAPEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter("in_settlementFlag",OracleDbType.Char,searchProgressRAPEntity.SettlementFlag,ParameterDirection.Input),
new OracleParameter("in_valueFlag",OracleDbType.Char,searchProgressRAPEntity.ValueFlag,ParameterDirection.Input),
new OracleParameter("in_sIDList",OracleDbType.Varchar2,strIdList,ParameterDirection.Input),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
};
return con.ExecStoredProcedure("PRO_PM_StaffProgressRAP", paras);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据条码获取窑炉窑车信息
///
/// 产品条码
/// DataSet
public static DataSet GetKilnCarByBarCode(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
// string sqlString = @"
// select
// TP_MST_KilnCar.KilnCarCode,
// TP_MST_Kiln.KilnCode,
// TP_MST_DataDictionary.Dictionaryvalue
// from TP_PM_KilnCarGoods
// left join TP_MST_KilnCar
// on TP_PM_KilnCarGoods.KilnCarID=TP_MST_KilnCar.Kilncarid
// left join TP_MST_Kiln
// on TP_MST_KilnCar.Kilnid=TP_MST_Kiln.Kilnid
// left join TP_MST_DataDictionary
// on TP_PM_KilnCarGoods.KilnCarPosition=TP_MST_DataDictionary.DictionaryID
// where TP_PM_KilnCarGoods.Barcode=:Barcode
// ";
string sqlString = @"
select
TP_PM_InProduction.KilnCarCode,
TP_PM_InProduction.KilnCode,
TP_MST_DataDictionary.Dictionaryvalue
from TP_PM_InProduction
inner join TP_MST_DataDictionary
on TP_PM_InProduction.KilnCarPosition=TP_MST_DataDictionary.DictionaryID
where TP_PM_InProduction.Barcode=:Barcode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
#region 生产数据
///
/// 查询在产产品数据
///
/// 在产产品数据实体
/// 用户基本信息
/// DataSet在产产品数据信息表
public static DataSet GetInProductionData(SearchInProductionEntity searchInProductionEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_barCode",OracleDbType.Varchar2,searchInProductionEntity.BarCode,ParameterDirection.Input),
new OracleParameter("in_productionLineId",OracleDbType.Varchar2,searchInProductionEntity.ProductionLineIDS,ParameterDirection.Input),
new OracleParameter("in_completeProcedureId",OracleDbType.Varchar2,searchInProductionEntity.ProcedureIDS,ParameterDirection.Input),
new OracleParameter("in_goodsId",OracleDbType.Varchar2,searchInProductionEntity.GoodsIDS,ParameterDirection.Input),
new OracleParameter("in_userId",OracleDbType.Varchar2,searchInProductionEntity.UserIDS,ParameterDirection.Input),
new OracleParameter("in_groutingLineId",OracleDbType.Varchar2,searchInProductionEntity.GroutingLineIDS,ParameterDirection.Input),
new OracleParameter("in_gMouldTypeId",OracleDbType.Varchar2,searchInProductionEntity.GMouldTypeIDS,ParameterDirection.Input),
new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("in_createTimeStart",OracleDbType.Varchar2,searchInProductionEntity.CreateTimeStart,ParameterDirection.Input),
new OracleParameter("in_createTimeEnd",OracleDbType.Varchar2,searchInProductionEntity.CreateTimeEnd,ParameterDirection.Input),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
};
DataSet dsInproduction = con.ExecStoredProcedure("PRO_PM_GetInProductionData", paras);
return dsInproduction;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 查询成品数据
///
/// 产成品数据实体
/// 用户基本信息
/// DataSet产成品数据信息表
public static DataSet GetFinishedProductionData(SearchFinishedProductEntity searchFinishedProductionEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_barCode",OracleDbType.Varchar2,searchFinishedProductionEntity.BarCode,ParameterDirection.Input),
new OracleParameter("in_productionLineId",OracleDbType.Varchar2,searchFinishedProductionEntity.ProductionLineIDS,ParameterDirection.Input),
new OracleParameter("in_goodsId",OracleDbType.Varchar2,searchFinishedProductionEntity.GoodsIDS,ParameterDirection.Input),
new OracleParameter("in_groutingLineId",OracleDbType.Varchar2,searchFinishedProductionEntity.GroutingLineIDS,ParameterDirection.Input),
new OracleParameter("in_gMouldTypeId",OracleDbType.Varchar2,searchFinishedProductionEntity.GMouldTypeIDS,ParameterDirection.Input),
new OracleParameter("in_accountDateStart",OracleDbType.Varchar2,searchFinishedProductionEntity.AccountDateStart,ParameterDirection.Input),
new OracleParameter("in_accountDateEnd",OracleDbType.Varchar2,searchFinishedProductionEntity.AccountDateEnd,ParameterDirection.Input),
new OracleParameter("in_createTimeStart",OracleDbType.Varchar2,searchFinishedProductionEntity.CreateTimeStart,ParameterDirection.Input),
new OracleParameter("in_createTimeEnd",OracleDbType.Varchar2,searchFinishedProductionEntity.CreateTimeEnd,ParameterDirection.Input),
new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
};
DataSet dsInproduction = con.ExecStoredProcedure("PRO_PM_GetFinishdProductData", paras);
return dsInproduction;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 查询半检数据一览
///
/// 查询半检数据明细实体类
/// 用户基本信息
/// DataSet
public static DataSet GetSearchSemiTestListModule(SemiTestDetailEntity semiTestDetailEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_goodsId",OracleDbType.Varchar2,semiTestDetailEntity.GoodsIDS,ParameterDirection.Input),
new OracleParameter("in_testUserID",OracleDbType.Varchar2,semiTestDetailEntity.TestUserIDS,ParameterDirection.Input),
new OracleParameter("in_groutingUserID",OracleDbType.Varchar2,semiTestDetailEntity.GroutingUserIDS,ParameterDirection.Input),
new OracleParameter("in_semitesttype",OracleDbType.Int32,semiTestDetailEntity.SemiTestType,ParameterDirection.Input),
new OracleParameter("in_remarks",OracleDbType.Varchar2,semiTestDetailEntity.Remarks,ParameterDirection.Input),
new OracleParameter("in_userPurviews",OracleDbType.Varchar2,semiTestDetailEntity.UserPurviews,ParameterDirection.Input),
new OracleParameter("in_semiTestDateStart",OracleDbType.Varchar2,semiTestDetailEntity.SemiTestDateStart,ParameterDirection.Input),
new OracleParameter("in_semiTestDateEnd",OracleDbType.Varchar2,semiTestDetailEntity.SemiTestDateEnd,ParameterDirection.Input),
new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
};
DataSet dsSearchReport = con.ExecStoredProcedure("PRO_PM_GetSemiTestList", paras);
return dsSearchReport;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
#endregion
#region 校验条码是否可以下车
///
/// 校验条码是否可以下车
///
/// 当前工序
/// 产品条码
/// 用户基本信息
/// CheckCancelLoadCar
public static CheckCancelLoadCar CheckCancelLoadCar(int procedureID, string barcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input),
new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input),
new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("out_errMsg",OracleDbType.NVarchar2,200,null,ParameterDirection.Output),
new OracleParameter("out_goodscode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output),
new OracleParameter("out_goodsname",OracleDbType.NVarchar2,50,null,ParameterDirection.Output),
new OracleParameter("out_kilncarcode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output),
};
oracleConn.ExecStoredProcedure("PRO_PM_CheckCancelLoadCar", paras);
CheckCancelLoadCar checkCancelLoadCar = new CheckCancelLoadCar();
checkCancelLoadCar.ErrMsg = paras[3].Value.ToString() == "null" ? "" : paras[3].Value.ToString().Replace("\\n\\r", "\n\r");
checkCancelLoadCar.GoodsCode = paras[4].Value.ToString() == "null" ? "" : paras[4].Value.ToString();
checkCancelLoadCar.GoodsName = paras[5].Value.ToString() == "null" ? "" : paras[5].Value.ToString();
checkCancelLoadCar.KilnCarCode = paras[6].Value.ToString() == "null" ? "" : paras[6].Value.ToString();
return checkCancelLoadCar;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
#endregion
///
/// 根据所选生产数据ID,显示成检数据信息
///
/// 生产数据ID
/// DataSet
public static DataSet GetProductionDataByID(int productionDataID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
DataSet dsReturn = new DataSet();
string sqlString = @"select
TP_PM_ProductionData.Barcode as BarCode,
TP_PM_ProductionData.Goodsid as GoodsID,
TP_PM_ProductionData.Goodscode as GoodsCode,
TP_PM_ProductionData.Goodsname as GoodsName,
TP_PM_ProductionData.GoodsLevelID as DefectFlagID,
TP_PM_ProductionData.Reworkprocedureid as ReworkProcedureID,
TP_PM_ProductionData.Remarks as Remarks,
TP_PM_ProductionData.Userid as UserID,
TP_PM_ProductionData.UserCode as UserCode,
TP_PM_ProductionData.UserName as UserName,
TP_PM_ProductionData.Goodsleveltypeid as GoodsLevelTypeID,
TP_PM_ProductionData.SpecialRepairflag,
TP_PM_ProductionData.UserCode,
TP_PM_ProductionData.KilnCode,
TP_PM_ProductionData.KilnCarCode,
TP_PM_ProductionData.GroutingUserCode,
TP_PM_ProductionData.GroutingMouldCode as MouldCode,
TP_PM_ProductionData.GroutingNum,
TP_PM_ProductionData.GroutingDate,
TP_MST_DataDictionary.Dictionaryvalue,
TP_PM_ProductionData.IsPublicBody,
TP_MST_Logo.logoid,
TP_MST_Logo.logocode,
TP_MST_Logo.logoname,
TP_PM_ProductionData.CreateTime,
TP_PM_ProductionData.CheckTime
from TP_PM_ProductionData
left join TP_MST_DataDictionary
on TP_PM_ProductionData.KilnCarPosition=TP_MST_DataDictionary.DictionaryID
left join TP_MST_Logo
on TP_PM_ProductionData.logoid=TP_MST_Logo.logoid
where
TP_PM_ProductionData.Productiondataid=:ProductionDataID
";
string sqlString2 = @"
select
TP_PM_Defect.ProductionDefectID as ProductionDefectID,
TP_PM_Defect.Barcode as BarCode,
TP_PM_Defect.DefectID as DefectID,
TP_PM_Defect.Defectname as DefectName,
TP_PM_Defect.Defectcode as DefectCode,
TP_PM_Defect.Defectpositionid as DefectPositionID,
TP_PM_Defect.Defectpositionname as DefectPositionName,
TP_PM_Defect.Defectpositioncode as DefectPositionCode,
TP_PM_Defect.Defectprocedureid as DefectProcedureID,
TP_PM_Defect.Defectprocedurename as DefectProcedureName,
TP_PM_Defect.Defectprocedurecode as DefectProcedureCode,
TP_PM_Defect.Defectuserid as DefectUserID,
TP_PM_Defect.Defectusername as DefectUserName,
TP_PM_Defect.Defectusercode as DefectUserCode,
TP_PM_Defect.DefectJobs as Jobs,
nvl(TP_PM_Defect.MissedUserID,-1) as MissedUserID,
TP_PM_Defect.MissedUserCode,
TP_PM_Defect.MissedUserName,
TP_MST_Jobs.Jobsname as JobsText,
TP_PM_Defect.Remarks as DefectRemarks,
TP_PM_Defect.Productiondataid as ProductionDataID,
nvl(TP_PM_Defect.DefectProductionDataID,0) as DefectProductionDataID,
TP_PM_Defect.Defectfine as DefectFineID,
TP_MST_DefectFine.DefectFineCode as DefectFineValue,
TP_PM_Defect.SpecialDefect,
TP_PM_Defect.DefectDeductionNum,
TP_PM_Defect.CheckTime
from TP_PM_Defect
left join TP_MST_Jobs
on TP_PM_Defect.Defectjobs=TP_MST_Jobs.JobsID
left join TP_MST_DefectFine
on TP_PM_Defect.Defectfine= TP_MST_DefectFine.DefectFineid
where TP_PM_Defect.Productiondataid =:ProductionDataID";
string sqlString3 = @"select
TP_PM_DefectResponsible.ProductionDefectID as ProductionDefectID,
TP_PM_DefectResponsible.Staffid as StaffID,
TP_HR_Staff.Staffcode as StaffCode,
TP_HR_Staff.Staffname as StaffName,
TP_PM_DefectResponsible.Staffstatus as StaffStatus
from TP_PM_DefectResponsible
left join TP_HR_Staff
on TP_PM_DefectResponsible.StaffID=TP_HR_Staff.Staffid
where TP_PM_DefectResponsible.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
string sqlString4 = @" select
TP_PM_DefectImage.ProductionDefectID,
TP_PM_DefectImage.Thumbnailpath,
TP_PM_DefectImage.Imagepath
from TP_PM_DefectImage
where TP_PM_DefectImage.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
string sqlString5 = @"select
TP_PM_DefectMissedResponsible.ProductionDefectID as ProductionDefectID,
TP_PM_DefectMissedResponsible.Staffid as StaffID,
TP_HR_Staff.Staffcode as StaffCode,
TP_HR_Staff.Staffname as StaffName,
TP_PM_DefectMissedResponsible.Staffstatus as StaffStatus,
TP_PM_DefectMissedResponsible.UJobsID,
TP_PM_DefectMissedResponsible.SJobsID
from TP_PM_DefectMissedResponsible
left join TP_HR_Staff
on TP_PM_DefectMissedResponsible.StaffID=TP_HR_Staff.Staffid
where TP_PM_DefectMissedResponsible.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
ds.Tables[0].TableName = "TP_PM_ProductionData";
DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras);
ds2.Tables[0].TableName = "TP_PM_Defect";
DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras);
ds3.Tables[0].TableName = "TP_PM_DefectResponsible";
DataSet ds4 = con.GetSqlResultToDs(sqlString4, paras);
ds4.Tables[0].TableName = "TP_PM_DefectImage";
DataSet ds5 = con.GetSqlResultToDs(sqlString5, paras);
ds5.Tables[0].TableName = "TP_PM_DefectMissedResponsible";
dsReturn.Tables.Add(ds.Tables[0].Copy());
dsReturn.Tables.Add(ds2.Tables[0].Copy());
dsReturn.Tables.Add(ds3.Tables[0].Copy());
dsReturn.Tables.Add(ds4.Tables[0].Copy());
dsReturn.Tables.Add(ds5.Tables[0].Copy());
dsReturn.Tables["TP_PM_DefectImage"].Columns.Add("SourcePathByte", typeof(byte[]));
for (int i = 0; i < dsReturn.Tables["TP_PM_DefectImage"].Rows.Count; i++)
{
string saveAllFilePath = AppDomain.CurrentDomain.BaseDirectory + dsReturn.Tables["TP_PM_DefectImage"].Rows[i]["ImagePath"].ToString();
if (File.Exists(saveAllFilePath))
{
FileInfo file = new FileInfo(saveAllFilePath);
Image PicImage = Image.FromStream(file.OpenRead());
byte[] smallbuffer = null;
using (MemoryStream ms = new MemoryStream())
{
PicImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
ms.Position = 0;
smallbuffer = new byte[ms.Length];
ms.Read(smallbuffer, 0, Convert.ToInt32(ms.Length));
ms.Flush();
}
dsReturn.Tables["TP_PM_DefectImage"].Rows[i]["SourcePathByte"] = smallbuffer;
}
}
return dsReturn;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取产品完成工序的ID
///
/// 产品条码
/// int
public static int GetCompleteProcedureID(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
FlowProcedureID as
CompleteProcedureID,
ProductionDataID,
ProcedureID
from TP_PM_InProduction
where
TP_PM_InProduction.BarCode=:BarCode
union
select
FlowProcedureID as
CompleteProcedureID,
ProductionDataID,
ProcedureID
from Tp_Pm_Inproductiontrash
where
Tp_Pm_Inproductiontrash.BarCode=:BarCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["CompleteProcedureID"]);
}
else
{
sqlString = @"select
BarCode
from TP_PM_FinishedProduct
where
TP_PM_FinishedProduct.BarCode=:BarCode
";
paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds2 = con.GetSqlResultToDs(sqlString, paras);
if (ds2 != null && ds2.Tables[0].Rows.Count > 0)
{
return -2;
}
else
{
return -1;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 编辑后删除生产数据
///
/// 生产数据ID
/// int
public static int DeleteProductionDataByID(int productionDataID)
{
int deleteRow = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
string sqlString5 = @"update TP_PM_DefectMissedResponsible set ValueFlag=0
where TP_PM_DefectMissedResponsible.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
string sqlString4 = @"update TP_PM_ProductionData set ValueFlag=0
where
TP_PM_ProductionData.Productiondataid=:ProductionDataID
";
string sqlString3 = @"update TP_PM_Defect set ValueFlag=0
where TP_PM_Defect.Productiondataid =:ProductionDataID";
string sqlString2 = @"update TP_PM_DefectResponsible set ValueFlag=0
where TP_PM_DefectResponsible.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
string sqlString = @"update TP_PM_DefectImage set ValueFlag=0
where TP_PM_DefectImage.Productiondefectid in
(
select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID
)";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input),
};
deleteRow += oracleTrConn.ExecuteNonQuery(sqlString, paras);
deleteRow += oracleTrConn.ExecuteNonQuery(sqlString2, paras);
deleteRow += oracleTrConn.ExecuteNonQuery(sqlString3, paras);
deleteRow += oracleTrConn.ExecuteNonQuery(sqlString4, paras);
deleteRow += oracleTrConn.ExecuteNonQuery(sqlString5, paras);
// 没有错误 提交事务
if (deleteRow > 0)
{
oracleTrConn.Commit();
}
else
{
oracleTrConn.Rollback();
}
}
catch (Exception ex)
{
oracleTrConn.Rollback();
throw ex;
}
finally
{
// 释放资源
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
return deleteRow;
}
///
/// 由产品条码获取注浆信息
///
/// 产品条码
/// DataSet
public static DataSet GetGroutingProducttByBarCode(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode,
TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum,
TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode,
TP_MST_Logo.logoid,
TP_MST_Logo.logocode,
TP_MST_Logo.logoname,
(select tp_pm_inproduction.ispublicbody from
tp_pm_inproduction where tp_pm_inproduction.BarCode=:barcode) as ispublicbody,
(select tp_pm_inproductiontrash.ispublicbody from
tp_pm_inproductiontrash where tp_pm_inproductiontrash.BarCode=:barcode) as ispublicbodyTrach,
TP_PM_GroutingDailyDetail.Groutingdate
from TP_PM_GroutingDailyDetail
left join TP_MST_Logo
on TP_PM_GroutingDailyDetail.logoid=TP_MST_Logo.logoid
where TP_PM_GroutingDailyDetail.BarCode=:barcode";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",barcode),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选工号对应的工种，查出缺陷责任员工
///
/// 工种ID
/// 用户基本信息
/// DataSet
public static DataSet GetDutyStaffByUserID(int jobs, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID
from TP_MST_UserStaff
left join TP_HR_Staff
on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid
where TP_MST_UserStaff.Ujobsid=:jobs and TP_HR_Staff.StaffStatus in (1,2)
order by TP_HR_Staff.StaffCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选工号，查出漏检责任员工
///
/// 工号
/// 用户基本信息
/// DataSet
public static DataSet GetMissedStaffByUserID(int userid, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as UJobsID,TP_HR_Staff.Jobs as SJobsID
from TP_MST_UserStaff
left join TP_HR_Staff
on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid
where TP_MST_UserStaff.Userid=:userid
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选工号对应的工种，查出缺陷责任员工
///
/// 工种ID
/// 用户ID
/// 用户基本信息
/// DataSet
public static DataSet GetDutyStaffByUserJobsID(int jobs, SUserInfo sUserInfo, int userid)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID
from TP_MST_UserStaff
left join TP_HR_Staff
on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid
where TP_MST_UserStaff.Ujobsid=:jobs and TP_MST_UserStaff.Userid=:userid
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input),
new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取干补标识
///
/// 产品条码ram>
/// int
public static int GetSpecialRepairflagByBarcode(string barcode, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
SpecialRepairflag
from tp_pm_inproduction where barcode=:barcode
union
select
SpecialRepairflag from
tp_pm_inproductiontrash where barcode=:barcode
union
select
SpecialRepairflag from
tp_pm_finishedproduct where barcode=:barcode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["SpecialRepairflag"]);
}
return 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取窑车对应产品列表
/// 窑车ID
///
/// DataSet
public static DataSet GetKilnCarGoodsByKilnCarID(int KilnCarID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select distinct
TP_PM_KilnCarGoods.Barcode,
TP_MST_Goods.Goodsid,
TP_MST_Goods.Goodscode,
TP_MST_Goods.Goodsname,
TP_PM_KilnCarGoods.Userid,
TP_PM_KilnCarGoods.Usercode,
TP_PM_KilnCarGoods.Username,
TP_MST_KilnCar.Kilncarname,
TP_MST_KilnCar.Kilncarcode,
TP_MST_Kiln.KilnName,
TP_MST_Kiln.KilnCode,
TP_MST_KilnCar.Kilnid,
TP_MST_KilnCar.KilnCarid,
TP_PM_KilnCarGoods.KilnCarPosition,
TP_MST_DataDictionary.Dictionaryvalue,
TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode,
TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum,
TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode,
(select max(tp_pm_inproduction.ispublicbody) from
tp_pm_inproduction where tp_pm_inproduction.BarCode=TP_PM_KilnCarGoods.Barcode) as ispublicbody,
TP_PM_GroutingDailyDetail.Groutingdate,
tp_mst_logo.logoid,
tp_mst_logo.logocode,
tp_mst_logo.logoname
from
TP_PM_KilnCarGoods
left join TP_MST_Goods
on TP_PM_KilnCarGoods.Goodsid=TP_MST_Goods.Goodsid
left join TP_MST_KilnCar
on TP_PM_KilnCarGoods.Kilncarid=TP_MST_KilnCar.Kilncarid
left join TP_MST_Kiln
on TP_MST_KilnCar.Kilnid=TP_MST_Kiln.Kilnid
left join TP_MST_DataDictionary
on TP_PM_KilnCarGoods.KilnCarPosition=TP_MST_DataDictionary.Dictionaryid
left join TP_PM_GroutingDailyDetail
on TP_PM_KilnCarGoods.Barcode=TP_PM_GroutingDailyDetail.BarCode
left join tp_mst_logo
on TP_PM_GroutingDailyDetail.logoid=tp_mst_logo.logoid
where TP_PM_KilnCarGoods.KilnCarID=:KilnCarID
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":KilnCarID",OracleDbType.Int32, KilnCarID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取未在生产线上报损的注浆信息
/// 产品条码
///
/// DataSet
public static DataSet GetBarCodeInGroutingDailyDetail(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
// 首先查看条码是否有效
string sqlString = @"select TP_PM_GroutingDailyDetail.GoodsID,TP_PM_GroutingDailyDetail.GoodsCode,TP_PM_GroutingDailyDetail.GoodsName
,TP_PM_GroutingDailyDetail.GroutingDailyID,TP_PM_GroutingDailyDetail.GroutingDailyDetailID
,TP_PM_GroutingDailyDetail.GroutingDate,TP_PM_GroutingDailyDetail.GroutingLineID,TP_PM_GroutingDailyDetail.GroutingLineCode
,TP_PM_GroutingDailyDetail.GroutingLineName
,TP_PM_GroutingDailyDetail.GroutingLineDetailID,TP_PM_GroutingDailyDetail.GroutingMouldCode,TP_PM_GroutingDailyDetail.MouldCode
,TP_PM_GroutingDailyDetail.UserID as GroutingUserID
,TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode
,TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum
,0 as IsPublicBody
,0 as IsReFire
,TP_PC_GroutingLine.GMouldTypeID
,TP_PM_GroutingDailyDetail.SpecialRepairFlag
from TP_PM_GroutingDailyDetail
left join TP_PC_GroutingLine
on TP_PM_GroutingDailyDetail.GroutingLineID=TP_PC_GroutingLine.GroutingLineID
where TP_PM_GroutingDailyDetail.barcode=:barcode";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取编辑的生产数据ID与列表所选生产数据ID做比较
///
/// 产品条码
/// int
public static int GetCompleteProductionDataID(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
ProductionDataID
from TP_PM_InProduction
where
TP_PM_InProduction.BarCode=:BarCode
union
select
ProductionDataID
from Tp_Pm_Inproductiontrash
where
Tp_Pm_Inproductiontrash.BarCode=:BarCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["ProductionDataID"]);
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取在产产品的信息标识列表
///
/// 产品条码
/// int
public static DataSet GetInProductionDataList(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
BarCode,
FlowProcedureID,
FlowProcedureTime,
ProcedureID,
ProcedureTime,
ProductionDataID,
IsReFire,
IsLengBu,
SpecialRepairFlag
from TP_PM_InProduction
where
TP_PM_InProduction.BarCode=:BarCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 成检时获取此条码是否报损
/// 产品条码
///
/// int
public static int CheckScrapProduct(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select auditstatus from tp_pm_scrapproduct where auditstatus in(0,1) and valueflag=1 and goodsleveltypeid=8 and barcode=:barcode and recyclingflag=0";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["auditstatus"]);
}
return -100;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取产品完成工序的ID(PDA)
///
/// 产品条码
/// int
public static DataSet GetCompleteProcedureIDPDA(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
FlowProcedureID as
CompleteProcedureID,
ProductionDataID,
ProcedureID,
OPTimeStamp,
to_char(OPTimeStamp,'DD-MM-YYHH12.MI.SS.FFAM') as ConvertOPTimeStamp
from TP_PM_InProduction
where
TP_PM_InProduction.BarCode=:BarCode
union
select
FlowProcedureID as
CompleteProcedureID,
ProductionDataID,
ProcedureID,
OPTimeStamp,
to_char(OPTimeStamp,'DD-MM-YYHH12.MI.SS.FFAM') as ConvertOPTimeStamp
from Tp_Pm_Inproductiontrash
where
Tp_Pm_Inproductiontrash.BarCode=:BarCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 成检时获取条码的产品信息（成检(正品)--报损-->干补-->成检）
///
/// 产品条码
/// int
public static DataSet GetGoodsInfoBybarcode(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
GoodsID,GoodsCode,GoodsName,
(
select
TP_PM_InProduction.SpecialRepairFlag
from TP_PM_InProduction
where TP_PM_InProduction.BarCode=:BarCode
) SpecialRepairFlag,
(
select
TP_PM_InProduction.IsReFire
from TP_PM_InProduction
where TP_PM_InProduction.BarCode=:BarCode
) IsReFire,
tp_mst_logo.logoid,
tp_mst_logo.logocode,
tp_mst_logo.logoname,
TP_PM_GroutingDailyDetail.UserCode,
TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode,
TP_PM_GroutingDailyDetail.GroutingCount,
TP_PM_GroutingDailyDetail.GroutingDate
from TP_PM_GroutingDailyDetail
left join tp_mst_logo
on TP_PM_GroutingDailyDetail.logoid=tp_mst_logo.logoid
where
TP_PM_GroutingDailyDetail.BarCode=:BarCode
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 生成盘点明细
///
/// 工序ID集
/// 用户基本信息
///
public static DataSet GetInCheckedDetail(string ProcedureIDS, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_PM_InProduction.BarCode,
TP_PM_InProduction.ProductionLineID,
TP_PM_InProduction.ProductionLineCode,
TP_PM_InProduction.ProductionLineName,
TP_PM_InProduction.FlowProcedureID,
TP_PM_InProduction.ProcedureID,
TP_PM_InProduction.ProcedureModel,
TP_PM_InProduction.ModelType,
TP_PM_InProduction.GoodsID,
TP_PM_InProduction.GoodsCode,
TP_PM_InProduction.GoodsName,
TP_PM_InProduction.GroutingDate,
TP_PM_InProduction.GroutingLineCode,
TP_PM_InProduction.GroutingLineName,
TP_PM_InProduction.GroutingMouldCode,
TP_PM_InProduction.GroutingUserCode,
TP_PM_InProduction.GroutingNum,
TP_PM_InProduction.KilnCode,
TP_PM_InProduction.KilnName,
TP_PM_InProduction.KilnCarCode,
TP_PM_InProduction.KilnCarName,
TP_PM_InProduction.IsPublicBody,
TP_PM_InProduction.IsReFire,
TP_PM_InProduction.SpecialRepairFlag,
TP_PC_Procedure.ProcedureName
from TP_PM_InProduction
left join TP_PC_Procedure
on TP_PM_InProduction.FlowProcedureID=TP_PC_Procedure.ProcedureID where TP_PM_InProduction.accountid=:accountid
";
List parameters = new List();
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 工序IDS
if (!string.IsNullOrEmpty(ProcedureIDS))
{
sqlString += "AND instr(','||:ProcedureIDS||',',','||TP_PM_InProduction.FlowProcedureID||',')>0 ";
parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, ProcedureIDS, ParameterDirection.Input));
}
DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取盘点单列表
///
///
///
public static DataSet GetInCheckedList(SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select InCheckedID,InCheckedNo,AccountDate from TP_PM_InChecked where accountid=:accountid and ValueFlag=1";
List parameters = new List();
parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取盘点单工号列表
///
///
///
public static DataSet GetInCheckedUserList(int InCheckedID, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select tp_pm_incheckeduser.InCheckedID,tp_pm_incheckeduser.UserID,tp_pm_incheckeduser.UserCode,
tp_mst_user.UserName from tp_pm_incheckeduser
left join tp_mst_user on tp_pm_incheckeduser.userid=tp_mst_user.userid
where tp_pm_incheckeduser.InCheckedID=:InCheckedID";
List parameters = new List();
parameters.Add(new OracleParameter(":InCheckedID", OracleDbType.Int32, InCheckedID, ParameterDirection.Input));
DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取主表盘点信息
///
/// 盘点类
/// 用户基本信息
///
public static DataSet GetAllInChecked(InCheckedEntity entity, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_inCheckedNo",OracleDbType.NVarchar2, entity.InCheckedNo,ParameterDirection.Input),
new OracleParameter("in_inCheckedName",OracleDbType.NVarchar2, entity.InCheckedName,ParameterDirection.Input),
new OracleParameter("in_remarks",OracleDbType.NVarchar2, entity.Remarks,ParameterDirection.Input),
new OracleParameter("in_begindate",OracleDbType.Date, entity.BeginDate,ParameterDirection.Input),
new OracleParameter("in_enddate",OracleDbType.Date,entity.EndDate,ParameterDirection.Input),
new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("in_accountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter("in_userID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
DataSet returnDataSet = oracleConn.ExecStoredProcedure("PRO_PM_GetInChecked", paras);
return returnDataSet;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取盘点单名细信息
///
/// 盘点单号
///
public static DataSet GetAllInCheckedDetail(int inCheckedID)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sql = @"select
TP_PM_InCheckedDetail.InCheckedID,
0 as Sel,
TP_PM_InCheckedDetail.BarCode,
TP_PM_InCheckedDetail.InCheckedNo,
TP_PM_InCheckedDetail.ProductionLineCode,
TP_PM_InCheckedDetail.ProductionLineName,
TP_PC_Procedure.ProcedureName,
decode(TP_PM_InCheckedDetail.ProcedureModel, '1', '计件模型' , '检验模型') as ProcedureModel,
decode(TP_PM_InCheckedDetail.DefectFlag, '1', '无缺陷' , '有缺陷') as DefectFlag,
TP_PM_InCheckedDetail.IsPublicBody,
TP_PM_InCheckedDetail.IsReFire,
TP_PM_InCheckedDetail.SpecialRepairFlag,
TP_PM_InCheckedDetail.GoodsCode,
TP_PM_InCheckedDetail.GoodsName,
TP_PM_InCheckedDetail.GroutingDate,
TP_PM_InCheckedDetail.GroutingLineCode,
TP_PM_InCheckedDetail.GroutingLineName,
TP_PM_InCheckedDetail.GroutingMouldCode,
TP_PM_InCheckedDetail.GroutingUserCode,
TP_PM_InCheckedDetail.GroutingNum,
TP_PM_InCheckedDetail.KilnCode,
TP_PM_InCheckedDetail.KilnName,
TP_PM_InCheckedDetail.KilnCarCode,
TP_PM_InCheckedDetail.KilnCarName,
decode(TP_PM_InCheckedDetail.InCheckedFlag, '0', '未盘点' , '1','已盘点','盘盈') as InCheckedFlagName,
TP_PM_InCheckedDetail.InCheckedFlag,
TP_PM_InCheckedDetail.CheckedDate,
Tp_mst_user.usercode CheckedUserCode,
u.usercode,
inpu.usercode cusercode,
TP_PM_InCheckedDetail.ProcedureTime,
TP_PM_InCheckedDetail.IsReworkFlag,
TP_PM_InCheckedDetail.InScrapFlag,
--xuwei add 2021-01-31
CASE TP_PM_InCheckedDetail.GOODSLEVELTYPEID
when 1 then '无缺陷'
when 2 then '有缺陷'
when 4 then '正品'
when 5 then '副品'
when 6 then '重烧'
when 9 then '干补'
else ''
END AS GOODSLEVELTYPE,
decode(sp.SCRAPPRODUCTID, null, 0, 1) SCRAPPRODUCTID,
su.usercode susercode,
spin.CREATETIME sdatetime,
decode(spin.AUDITSTATUS,1, au.usercode , null) ausercode,
spin.AUDITDATE adatetime,gt.goodstypename
--,inpp.procedurename currentprocedurename
--,inp.proceduretime currentproceduretime" + "\n" +
" ,case when inp.barcode is not null then to_char(inpp.procedurename) " +
" when sp.barcode is not null then to_char(glt.goodsleveltypename) " +
" when fp.barcode is not null then '生产完成' else '' end currentprocedurename -- 当前工序\n" +
" ,case when inp.barcode is not null then inp.PROCEDURETIME " +
" when sp.barcode is not null then sp.auditdate " +
" when fp.barcode is not null then fp.createtime else null end currentproceduretime -- 当前工序时间\n" +
//2021年12月21日13:18:31 by fy modify 次品、不合格、不合格（返）、损坯 增加一列【报废工序】
//begin
" ,case when sp.barcode is not null and sp.goodsleveltypeid in (7,8,13,14) then sp.procedurename else null end scrapprocedurename\n" +
//end
@"from TP_PM_InCheckedDetail
left join tp_mst_goods g on TP_PM_InCheckedDetail.goodsid = g.goodsid
left join tp_mst_goodstype gt on gt.goodstypeid = g.goodstypeid
left join TP_PC_Procedure
on TP_PM_InCheckedDetail.ProcedureID=TP_PC_Procedure.ProcedureID
left join Tp_mst_user
on TP_PM_InCheckedDetail.CheckedUserID=Tp_mst_user.userid
left join Tp_mst_user u
on TP_PM_InCheckedDetail.UserID=u.userid
left join TP_PM_SCRAPPRODUCT spin
-- on spin.barcode = TP_PM_InCheckedDetail.BarCode
on spin.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid
and spin.GOODSLEVELTYPEID = 8 and spin.AUDITSTATUS in (0,1)
and spin.valueflag = '1' and spin.RECYCLINGFLAG = '0'
left join Tp_mst_user su on su.userid = spin.CREATEUSERID
left join Tp_mst_user au on au.userid = spin.AUDITOR
LEFT JOIN Tp_Pm_Inproduction inp
--ON inp.barcode = TP_PM_InCheckedDetail.Barcode
ON inp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid" +
// 完成 groutingdailydetailid
" LEFT JOIN tp_pm_finishedproduct fp \n" +
//" ON inp.barcode is null and fp.barcode = TP_PM_InCheckedDetail.barcode\n" +
" ON inp.barcode is null and fp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid\n" +
// 损坯
" LEFT JOIN tp_pm_scrapproduct sp \n" +
//" ON inp.barcode is null and fp.barcode is null and sp.barcode = TP_PM_InCheckedDetail.barcode\n" +
" ON inp.barcode is null and fp.barcode is null and sp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid\n" +
" AND sp.valueflag = '1'\n" +
" AND sp.auditstatus = 1\n" +
" AND sp.goodsleveltypeid <> 9\n" +
" AND sp.RECYCLINGFLAG = '0'\n" +
" LEFT JOIN tp_sys_goodsleveltype glt \n" +
" ON glt.goodsleveltypeid = sp.goodsleveltypeid\n" +
@"LEFT JOIN tp_pc_procedure inpp
ON inpp.procedureid = inp.procedureid
left join Tp_mst_user inpu
on inp.UserID=inpu.userid
where InCheckedID=:InCheckedID and TP_PM_InCheckedDetail.valueflag=1 and TP_PM_InCheckedDetail.InCheckedFlag = :InCheckedFlag
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":InCheckedFlag",OracleDbType.Int32,(int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input),
new OracleParameter(":InCheckedID",OracleDbType.Int32,inCheckedID,ParameterDirection.Input),
};
DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras);
paras[0].Value = (int)Constant.InCheckedFlag.InCheckeded;
returnDataSet.Tables.Add(oracleConn.GetSqlResultToDt(sql, paras));
paras[0].Value = (int)Constant.InCheckedFlag.InCheckedWin;
returnDataSet.Tables.Add(oracleConn.GetSqlResultToDt(sql, paras));
sql = @"select tp_pm_incheckeduser.InCheckedID,tp_pm_incheckeduser.UserID,tp_pm_incheckeduser.UserCode,
tp_mst_user.UserName from tp_pm_incheckeduser
left join tp_mst_user on tp_pm_incheckeduser.userid=tp_mst_user.userid
where tp_pm_incheckeduser.InCheckedID=:InCheckedID";
paras = new OracleParameter[]{
new OracleParameter(":InCheckedID",OracleDbType.Int32,inCheckedID,ParameterDirection.Input),
};
DataTable userTable = oracleConn.GetSqlResultToDt(sql, paras);
userTable.TableName = "UserTable";
returnDataSet.Tables.Add(userTable);
return returnDataSet;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取盘点单名细信息
///
/// 盘点单号
///
public static DataSet GetGroutingInfoBybarcode(string barcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
// Flowprocedureid 换成 procedureid,干补特殊，会不一样，别的都一样
string sql = @"select
gdd.barcode,
gdd.GoodsCode,
gdd.UserCode,
gdd.GroutingDate,
gdd.GroutingMouldCode,
logo.logoname -- 商标
--,g.MaterialCode || logo.TagCode || gdd.OnlyCode OutOnlyCode -- 外包装唯一码
,nvl(gdd.outlabelcode, g.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = gdd.accountid) || logo.TagCode || gdd.OnlyCode) OutOnlyCode
,tp_pc_procedure.procedurename
from TP_PM_GroutingDailyDetail gdd
INNER JOIN tp_mst_goods g
ON g.goodsid = gdd.goodsid
LEFT JOIN tp_mst_logo logo
ON logo.logoid = gdd.logoid
left join TP_PM_InProduction
on gdd.barcode=TP_PM_InProduction.barcode
left join tp_pc_procedure
on TP_PM_InProduction.procedureid=tp_pc_procedure.procedureid
where gdd.barcode=FUN_CMN_GetBarCode(:barcode,null,:accountid) and gdd.valueflag=1 and gdd.scrapflag=0
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input),
new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
};
DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras);
return returnDataSet;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取盘点单名细信息
///
/// 盘点单号
///
public static DataSet GetFinishedProductGroutingInfoBybarcode(string barcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sql = @"select
gdd.barcode,
gdd.GoodsCode,
gdd.UserCode,
gdd.GroutingDate,
gdd.GroutingMouldCode,
logo.logoname -- 商标
--,g.MaterialCode || logo.TagCode || gdd.OnlyCode OutOnlyCode -- 外包装唯一码
,nvl(gdd.outlabelcode, g.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = gdd.accountid) || logo.TagCode || gdd.OnlyCode) OutOnlyCode
,tp_pc_procedure.procedurename
from TP_PM_GroutingDailyDetail gdd
INNER JOIN tp_mst_goods g
ON g.goodsid = gdd.goodsid
LEFT JOIN tp_mst_logo logo
ON logo.logoid = gdd.logoid
left join TP_PM_InProduction
on gdd.barcode=TP_PM_InProduction.barcode
left join tp_pc_procedure
on TP_PM_InProduction.Flowprocedureid=tp_pc_procedure.procedureid
where gdd.barcode=FUN_CMN_GetBarCode(:barcode,null,:accountid) and gdd.valueflag=1 and gdd.scrapflag=0
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input),
new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
};
DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras);
return returnDataSet;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
#region 清除在产残留数据
///
/// 取得PM2108画面（在产品明细表）的查询数据
///
/// 登录用户信息
/// 查询条件
/// 查询结果
public static ServiceResultEntity GetPM2108Data(SUserInfo user, RPT010401_SE se)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
List parameters = new List();
StringBuilder sql = new StringBuilder(PMModuleLogic.GetRPT010401SSQL());
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
sql.Append(" and TP_PM_InProduction.modeltype not in (1,2,3) ");
if (se != null)
{
//生产线IDS
if (!string.IsNullOrEmpty(se.ProductionLineIDS))
{
sql.Append(" AND instr(','||:ProductionLineIDS||',',','||TP_PM_InProduction.ProductionLineID||',')>0 ");
parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input));
}
//完成工序IDS
if (!string.IsNullOrEmpty(se.ProcedureIDS))
{
sql.Append(" AND instr(','||:ProcedureIDS||',',','||TP_PM_InProduction.ProcedureID||',')>0 ");
parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input));
}
// 生产时间--开始
if (se.UpdateTimeStart.HasValue)
{
sql.Append(" AND TP_PM_InProduction.Proceduretime >= :UpdateTimeStart ");
parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input));
}
// 生产时间--结束
if (se.UpdateTimeEnd.HasValue)
{
sql.Append(" AND TP_PM_InProduction.Proceduretime <= :UpdateTimeEnd ");
parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input));
}
//产品条码
if (!string.IsNullOrEmpty(se.Barcode))
{
sql.Append(" AND instr(TP_PM_InProduction.barcode,:barcode)>0 ");
parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input));
}
sql.Append(" AND TP_PM_InProduction.inscrapflag=0 ");
}
sql.Append(" order by TP_PM_InProduction.UpdateTime desc");
DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 获取RPT010401画面（在产品明细表）的查询sql
///
/// sql
private static string GetRPT010401SSQL()
{
string selSql =
"SELECT 0 as Sel," +
" TP_PM_InProduction.BarCode, " +
" TP_PM_InProduction.GoodsID,TP_PM_InProduction.GoodsCode, " +
" TP_PM_InProduction.GoodsName, " +
" TP_PM_InProduction.Userid,A.USERNAME as UserName,A.USERCode as UserCode, " +
" TP_PM_InProduction.ProductionLineID, " +
" TP_PM_InProduction.ProductionLineCode, " +
" TP_PM_InProduction.ProductionLineName, " +
" TP_PM_InProduction.ModelType, " +
" decode(TP_PM_InProduction.inscrapflag , '1', '待审核的报废品' , '正常') as inscrapflag, " +
//" decode(TP_PM_InProduction.IsPublicBody, '1', '是' , '否') as IsPublicBody, " +
//" decode(TP_PM_InProduction.IsReFire, '1', '是' , '否') as IsReFire, " +
" TP_PM_InProduction.IsPublicBody, " +
" TP_PM_InProduction.IsReFire, " +
" decode(TP_PM_InProduction.CanManyTimes, '1', '能' , '不能') as CanManyTimes, " +
" decode(TP_PM_InProduction.ProcedureModel, '1', '计件模型' , '检验模型') as ProcedureModel, " +
" TP_PM_InProduction.GroutingDailyID, " +
" TP_PM_InProduction.GroutingDailyDetailID, " +
" TP_PM_InProduction.GroutingDate, " +
" TP_PM_InProduction.GroutingLineID, " +
" TP_PM_InProduction.GroutingLineCode, " +
" TP_PM_InProduction.GroutingLineName, " +
" TP_PM_InProduction.ProcedureID CompleteProcedureID, " +
" D.ProcedureName as CompleteProcedureName, " +
" TP_PM_InProduction.GMouldTypeID, " +
" TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName, " +
" TP_PM_InProduction.GroutingLineDetailID, " +
" TP_PM_InProduction.GroutingMouldCode, " +
" TP_PM_InProduction.SPECIALREPAIRFLAG, " +
" TP_PM_InProduction.GROUTINGUSERCODE, " +
" TP_PM_GroutingDailyDetail.GROUTINGCOUNT, " +
" TP_PM_InProduction.Remarks, " +
" TP_PM_InProduction.GoodsLevelID,TP_MST_GoodsLevel.GOODSLEVELNAME, " +
" TP_PM_InProduction.GoodsLevelTypeID,TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME, " +
" TP_PM_InProduction.AccountID,TP_PM_InProduction.ValueFlag, " +
" TP_PM_InProduction.CreateTime,TP_PM_InProduction.CreateUserID,B.USERNAME as CreateUserName, " +
" TP_PM_InProduction.PROCEDURETIME AS UpdateTime,TP_PM_InProduction.UpdateUserID,C.USERNAME as UpdateUserName, " +
" TP_PM_InProduction.IsReworkFlag " +
"FROM TP_PM_InProduction " +
" inner join TP_MST_User A on A.UserID=TP_PM_InProduction.Userid " +
" inner join TP_MST_User B on B.UserID=TP_PM_InProduction.CreateUserID " +
" inner join TP_MST_User C on C.UserID=TP_PM_InProduction.UpdateUserID " +
" inner join TP_PC_Procedure D on D.PROCEDUREID=TP_PM_InProduction.ProcedureID " +
" inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID=TP_PM_InProduction.GMouldTypeID " +
" inner join TP_SYS_ProcedureModelType on TP_SYS_ProcedureModelType.ProcedureModelTypeID=TP_PM_InProduction.ModelType " +
" inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID=TP_PM_InProduction.GroutingDailyID " +
" inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid=TP_PM_InProduction.GroutingDailyDetailID " +
" inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid=TP_PM_InProduction.GroutingLineDetailID " +
" inner join TP_MST_Account on TP_MST_Account.Accountid=TP_PM_InProduction.Accountid " +
" left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID=TP_PM_InProduction.GoodsLevelID " +
" left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID=TP_PM_InProduction.GoodsLevelTypeID " +
" Where 1=1 and TP_PM_InProduction.ValueFlag = 1 " +
" and TP_PM_InProduction.AccountID=:AccountID ";
return selSql;
}
#endregion
#region 清除在产回收站数据
public static ServiceResultEntity GetPM2110Data(SUserInfo user, FPM2110_SE se)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
List parameters = new List();
StringBuilder sql = new StringBuilder(PMModuleLogic.GetPM2110SQL());
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
if (se != null)
{
//生产线IDS
if (!string.IsNullOrEmpty(se.ProductionLineIDS))
{
sql.Append(" AND instr(','||:ProductionLineIDS||',',','||Tp_Pm_Inproductiontrash.ProductionLineID||',')>0 ");
parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input));
}
//完成工序IDS
if (!string.IsNullOrEmpty(se.ProcedureIDS))
{
sql.Append(" AND instr(','||:ProcedureIDS||',',','||Tp_Pm_Inproductiontrash.ProcedureID||',')>0 ");
parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input));
}
// 生产时间--开始
if (se.UpdateTimeStart.HasValue)
{
sql.Append(" AND Tp_Pm_Inproductiontrash.Proceduretime >= :UpdateTimeStart ");
parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input));
}
// 生产时间--结束
if (se.UpdateTimeEnd.HasValue)
{
sql.Append(" AND Tp_Pm_Inproductiontrash.Proceduretime <= :UpdateTimeEnd ");
parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input));
}
//报废日期开始
if (se.ScrapDataStart.HasValue)
{
sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE >= trunc(:ScrapDateStart) ");
parameters.Add(new OracleParameter(":ScrapDateStart", OracleDbType.Date, se.ScrapDataStart.Value, ParameterDirection.Input));
}
//报废日期结束
if (se.ScrapDataEnd.HasValue)
{
sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE <= trunc(:ScrapDateEnd)");
parameters.Add(new OracleParameter(":ScrapDateEnd", OracleDbType.Date, se.ScrapDataEnd.Value, ParameterDirection.Input));
}
//产品条码
if (!string.IsNullOrEmpty(se.Barcode))
{
sql.Append(" AND instr(Tp_Pm_Inproductiontrash.barcode,:barcode)>0 ");
parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input));
}
// 产品分级
sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID = :GooddLevelTypeID ");
parameters.Add(new OracleParameter(":GooddLevelTypeID", OracleDbType.Int32, se.GooddLevelTypeID, ParameterDirection.Input));
}
sql.Append(" order by Tp_Pm_Inproductiontrash.UpdateTime desc");
DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 获取RPT010401画面（在产品明细表）的查询sql
///
/// sql
private static string GetPM2110SQL()
{
string selSql =
@"SELECT 0 as Sel,
Tp_Pm_Inproductiontrash.BarCode,
Tp_Pm_Inproductiontrash.GoodsID,
Tp_Pm_Inproductiontrash.GoodsCode,
Tp_Pm_Inproductiontrash.GoodsName,
Tp_Pm_Inproductiontrash.Userid,
A.USERNAME as UserName,
A.USERCode as UserCode,
Tp_Pm_Inproductiontrash.ProductionLineID,
Tp_Pm_Inproductiontrash.ProductionLineCode,
Tp_Pm_Inproductiontrash.ProductionLineName,
Tp_Pm_Inproductiontrash.ModelType,
--decode(Tp_Pm_Inproductiontrash.IsPublicBody, '1', '是', '否') as IsPublicBody,
--decode(Tp_Pm_Inproductiontrash.IsReFire, '1', '是', '否') as IsReFire,
Tp_Pm_Inproductiontrash.IsPublicBody,
Tp_Pm_Inproductiontrash.IsReFire,
decode(Tp_Pm_Inproductiontrash.CanManyTimes, '1', '能', '不能') as CanManyTimes,
decode(Tp_Pm_Inproductiontrash.ProcedureModel,
'1',
'计件模型',
'检验模型') as ProcedureModel,
Tp_Pm_Inproductiontrash.GroutingDailyID,
Tp_Pm_Inproductiontrash.GroutingDailyDetailID,
Tp_Pm_Inproductiontrash.GroutingDate,
Tp_Pm_Inproductiontrash.GroutingLineID,
Tp_Pm_Inproductiontrash.GroutingLineCode,
Tp_Pm_Inproductiontrash.GroutingLineName,
Tp_Pm_Inproductiontrash.ProcedureID CompleteProcedureID,
D.ProcedureName as CompleteProcedureName,
Tp_Pm_Inproductiontrash.GMouldTypeID,
TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName,
Tp_Pm_Inproductiontrash.GroutingLineDetailID,
Tp_Pm_Inproductiontrash.GroutingMouldCode,
Tp_Pm_Inproductiontrash.SPECIALREPAIRFLAG,
Tp_Pm_Inproductiontrash.GROUTINGUSERCODE,
TP_PM_GroutingDailyDetail.GROUTINGCOUNT,
Tp_Pm_Inproductiontrash.Remarks,
Tp_Pm_Inproductiontrash.GoodsLevelID,
TP_MST_GoodsLevel.GOODSLEVELNAME,
Tp_Pm_Inproductiontrash.GoodsLevelTypeID,
TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME,
Tp_Pm_Inproductiontrash.AccountID,
Tp_Pm_Inproductiontrash.ValueFlag,
Tp_Pm_Inproductiontrash.CreateTime,
Tp_Pm_Inproductiontrash.CreateUserID,
B.USERNAME as CreateUserName,
Tp_Pm_Inproductiontrash.PROCEDURETIME AS UpdateTime,
Tp_Pm_Inproductiontrash.UpdateUserID,
C.USERNAME as UpdateUserName
FROM Tp_Pm_Inproductiontrash
inner join TP_MST_User A on A.UserID = Tp_Pm_Inproductiontrash.Userid
inner join TP_MST_User B on B.UserID = Tp_Pm_Inproductiontrash.CreateUserID
inner join TP_MST_User C on C.UserID = Tp_Pm_Inproductiontrash.UpdateUserID
inner join TP_PC_Procedure D on D.PROCEDUREID =
Tp_Pm_Inproductiontrash.ProcedureID
inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID =
Tp_Pm_Inproductiontrash.GMouldTypeID
inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID =
Tp_Pm_Inproductiontrash.GroutingDailyID
inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid =
Tp_Pm_Inproductiontrash.GroutingDailyDetailID
inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid =
Tp_Pm_Inproductiontrash.GroutingLineDetailID
inner join TP_MST_Account on TP_MST_Account.Accountid =
Tp_Pm_Inproductiontrash.Accountid
left join TP_PM_SCRAPPRODUCT on TP_PM_SCRAPPRODUCT.barcode=Tp_Pm_Inproductiontrash.barcode
left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID =
TP_PM_SCRAPPRODUCT.GoodsLevelID
left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID =
TP_PM_SCRAPPRODUCT.GoodsLevelTypeID
Where 1 = 1
and Tp_Pm_Inproductiontrash.ValueFlag = 1
and Tp_Pm_Inproductiontrash.AccountID = :AccountID";
return selSql;
}
#endregion
#region 清除在产临时表数据
public static ServiceResultEntity GetPM2112Data(SUserInfo user, FPM2112_SE se)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
List parameters = new List();
StringBuilder sql = new StringBuilder(PMModuleLogic.GetPM2112SQL());
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input));
if (se != null)
{
//生产线IDS
if (!string.IsNullOrEmpty(se.ProductionLineIDS))
{
sql.Append(" AND instr(','||:ProductionLineIDS||',',','||Tp_Pm_Inproduction_Tmp.ProductionLineID||',')>0 ");
parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input));
}
//完成工序IDS
if (!string.IsNullOrEmpty(se.ProcedureIDS))
{
sql.Append(" AND instr(','||:ProcedureIDS||',',','||Tp_Pm_Inproduction_Tmp.ProcedureID||',')>0 ");
parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input));
}
// 生产时间--开始
if (se.UpdateTimeStart.HasValue)
{
sql.Append(" AND Tp_Pm_Inproduction_Tmp.Proceduretime >= :UpdateTimeStart ");
parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input));
}
// 生产时间--结束
if (se.UpdateTimeEnd.HasValue)
{
sql.Append(" AND Tp_Pm_Inproduction_Tmp.Proceduretime <= :UpdateTimeEnd ");
parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input));
}
//产品条码
if (!string.IsNullOrEmpty(se.Barcode))
{
sql.Append(" AND instr(Tp_Pm_Inproduction_Tmp.barcode,:barcode)>0 ");
parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input));
}
if (se.TrashFlag == 1)
{
//报废日期开始
if (se.ScrapDataStart.HasValue)
{
sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE >= trunc(:ScrapDateStart) ");
parameters.Add(new OracleParameter(":ScrapDateStart", OracleDbType.Date, se.ScrapDataStart.Value, ParameterDirection.Input));
}
//报废日期结束
if (se.ScrapDataEnd.HasValue)
{
sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE <= trunc(:ScrapDateEnd)");
parameters.Add(new OracleParameter(":ScrapDateEnd", OracleDbType.Date, se.ScrapDataEnd.Value, ParameterDirection.Input));
}
}
// 清除时间
if (se.DeletedTimeStart.HasValue)
{
sql.Append(" AND Tp_Pm_Inproduction_Tmp.deletedtime >= :deletedtime ");
parameters.Add(new OracleParameter(":deletedtime", OracleDbType.Date, se.DeletedTimeStart.Value, ParameterDirection.Input));
}
// 清除时间--结束
if (se.DeletedTimeEnd.HasValue)
{
sql.Append(" AND Tp_Pm_Inproduction_Tmp.deletedtime <= :deletedtimeEnd ");
parameters.Add(new OracleParameter(":deletedtimeEnd", OracleDbType.Date, se.DeletedTimeEnd.Value, ParameterDirection.Input));
}
if (se.GooddLevelTypeID != -100)
{
// 产品分级
sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID = :GooddLevelTypeID ");
parameters.Add(new OracleParameter(":GooddLevelTypeID", OracleDbType.Int32, se.GooddLevelTypeID, ParameterDirection.Input));
}
else
{
// 产品分级
//// sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID is null ");
}
//数据来源
sql.Append(" AND Tp_Pm_Inproduction_Tmp.TrashFlag = :TrashFlag ");
parameters.Add(new OracleParameter(":TrashFlag", OracleDbType.Int32, se.TrashFlag, ParameterDirection.Input));
}
sql.Append(" order by Tp_Pm_Inproduction_Tmp.UpdateTime desc");
DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 获取RPT010401画面（在产品明细表）的查询sql
///
/// sql
private static string GetPM2112SQL()
{
string selSql =
@"SELECT 0 as Sel,
TP_PM_SCRAPPRODUCT.SCRAPDATE,
Tp_Pm_Inproduction_Tmp.BarCode,
Tp_Pm_Inproduction_Tmp.GoodsID,
Tp_Pm_Inproduction_Tmp.GoodsCode,
Tp_Pm_Inproduction_Tmp.GoodsName,
Tp_Pm_Inproduction_Tmp.Userid,
A.USERNAME as UserName,
A.USERCode as UserCode,
Tp_Pm_Inproduction_Tmp.ProductionLineID,
Tp_Pm_Inproduction_Tmp.ProductionLineCode,
Tp_Pm_Inproduction_Tmp.ProductionLineName,
Tp_Pm_Inproduction_Tmp.ModelType,
--decode(Tp_Pm_Inproduction_Tmp.IsPublicBody, '1', '是', '否') as IsPublicBody,
--decode(Tp_Pm_Inproduction_Tmp.IsReFire, '1', '是', '否') as IsReFire,
Tp_Pm_Inproduction_Tmp.IsPublicBody,
Tp_Pm_Inproduction_Tmp.IsReFire,
decode(Tp_Pm_Inproduction_Tmp.CanManyTimes, '1', '能', '不能') as CanManyTimes,
decode(Tp_Pm_Inproduction_Tmp.ProcedureModel,
'1',
'计件模型',
'检验模型') as ProcedureModel,
Tp_Pm_Inproduction_Tmp.GroutingDailyID,
Tp_Pm_Inproduction_Tmp.GroutingDailyDetailID,
Tp_Pm_Inproduction_Tmp.GroutingDate,
Tp_Pm_Inproduction_Tmp.GroutingLineID,
Tp_Pm_Inproduction_Tmp.GroutingLineCode,
Tp_Pm_Inproduction_Tmp.GroutingLineName,
Tp_Pm_Inproduction_Tmp.ProcedureID CompleteProcedureID,
D.ProcedureName as CompleteProcedureName,
Tp_Pm_Inproduction_Tmp.GMouldTypeID,
TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName,
Tp_Pm_Inproduction_Tmp.GroutingLineDetailID,
Tp_Pm_Inproduction_Tmp.GroutingMouldCode,
Tp_Pm_Inproduction_Tmp.SPECIALREPAIRFLAG,
Tp_Pm_Inproduction_Tmp.GROUTINGUSERCODE,
TP_PM_GroutingDailyDetail.GROUTINGCOUNT,
Tp_Pm_Inproduction_Tmp.Remarks,
Tp_Pm_Inproduction_Tmp.GoodsLevelID,
TP_MST_GoodsLevel.GOODSLEVELNAME,
Tp_Pm_Inproduction_Tmp.GoodsLevelTypeID,
TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME,
Tp_Pm_Inproduction_Tmp.AccountID,
Tp_Pm_Inproduction_Tmp.ValueFlag,
Tp_Pm_Inproduction_Tmp.CreateTime,
Tp_Pm_Inproduction_Tmp.CreateUserID,
B.USERNAME as CreateUserName,
Tp_Pm_Inproduction_Tmp.PROCEDURETIME AS UpdateTime,
Tp_Pm_Inproduction_Tmp.UpdateUserID,
C.USERNAME as UpdateUserName
FROM Tp_Pm_Inproduction_Tmp
inner join TP_MST_User A on A.UserID = Tp_Pm_Inproduction_Tmp.Userid
inner join TP_MST_User B on B.UserID = Tp_Pm_Inproduction_Tmp.CreateUserID
inner join TP_MST_User C on C.UserID = Tp_Pm_Inproduction_Tmp.UpdateUserID
inner join TP_PC_Procedure D on D.PROCEDUREID =
Tp_Pm_Inproduction_Tmp.ProcedureID
inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID =
Tp_Pm_Inproduction_Tmp.GMouldTypeID
inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID =
Tp_Pm_Inproduction_Tmp.GroutingDailyID
inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid =
Tp_Pm_Inproduction_Tmp.GroutingDailyDetailID
inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid =
Tp_Pm_Inproduction_Tmp.GroutingLineDetailID
inner join TP_MST_Account on TP_MST_Account.Accountid =
Tp_Pm_Inproduction_Tmp.Accountid
left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID =
Tp_Pm_Inproduction_Tmp.GoodsLevelID
left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID =
Tp_Pm_Inproduction_Tmp.GoodsLevelTypeID
left join TP_PM_SCRAPPRODUCT on TP_PM_SCRAPPRODUCT.barcode=Tp_Pm_Inproduction_Tmp.barcode and TP_PM_SCRAPPRODUCT.AUDITSTATUS=1 and TP_PM_SCRAPPRODUCT.RECYCLINGFLAG='0' and TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID=8
Where Tp_Pm_Inproduction_Tmp.AccountID = :AccountID";
return selSql; //and Tp_Pm_Inproduction_Tmp.ValueFlag = 1
}
#endregion
///
/// 工序是否有商标ID
/// 工序ID
///
/// int
public static int GetLogoID(int ProcedureID, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select LogoID from tp_pc_procedure where accountid=:accountid and ProcedureID=:ProcedureID";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":ProcedureID",OracleDbType.Int32, ProcedureID,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["LogoID"] == DBNull.Value)
{
return 0;
}
else
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["LogoID"]);
}
}
return 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取条码商标
/// 产品条码
///
/// int
public static DataSet GetBarCodeLogoID(string barcode, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
//// 转换条码
//string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL";
//OracleParameter[] paras1 = new OracleParameter[]{
// new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
// new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
// };
//barcode = con.GetSqlResultToStr(sqlString, paras1);
//sqlString = @"select g.LogoID,l.logocode,l.logoname, g.glazetypeid, d.DICTIONARYVALUE glazetypename, g.goodsid
// from tp_pm_groutingdailydetail g
// left join tp_mst_logo l
// on g.logoid=l.logoid
// left join TP_MST_DataDictionary d on d.DICTIONARYID = g.glazetypeid
// where g.barcode=:barcode";
//OracleParameter[] paras = new OracleParameter[]{
// new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
//};
//DataSet ds = con.GetSqlResultToDs(sqlString, paras);
//if (ds != null && ds.Tables[0].Rows.Count > 0)
//{
// return ds;
//}
//return null;
// 转换条码
string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL";
OracleParameter[] paras1 = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
};
barcode = con.GetSqlResultToStr(sqlString, paras1);
sqlString = @"select
-- 以主键提升容错，便于以后扩展
g.GROUTINGDAILYDETAILID,
-- end
g.LogoID,l.logocode,l.logoname, g.glazetypeid, d.DICTIONARYVALUE glazetypename, g.goodsid, f.fhuserid, '' err_msg
from tp_pm_groutingdailydetail g
left join tp_mst_logo l on g.logoid=l.logoid
left join TP_MST_DataDictionary d on d.DICTIONARYID = g.glazetypeid
left join tp_pm_finishedproduct f on g.barcode = f.barcode
where g.barcode=:barcode";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
object fhuserid = ds.Tables[0].Rows[0]["fhuserid"];
if (fhuserid != null && fhuserid != DBNull.Value)
{
ds.Tables[0].Rows[0]["err_msg"] = "已交接的产品不能变更商标";
}
return ds;
}
else
{
ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("err_msg");
DataRow row = dt.NewRow();
row["err_msg"] = "条码不存在";
dt.Rows.Add(row);
ds.Tables.Add(dt);
return ds;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 半检时,入窑前检验获取此条码是否报损为废品
/// 产品条码
///
/// int
public static int CheckWasteScrapProduct(string barcode)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select auditstatus from tp_pm_scrapproduct where auditstatus in(0,1) and valueflag=1 and goodsleveltypeid=3 and barcode=:barcode";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["auditstatus"]);
}
return -100;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取盘点单明细
///
/// 工序ID
/// DataSet
public static DataSet GetUpdateInCheckedInfo(string incheckedno, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select tp_pm_inchecked.incheckedid from tp_pm_inchecked where tp_pm_inchecked.incheckedno=:incheckedno";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":incheckedno",OracleDbType.Varchar2, incheckedno,ParameterDirection.Input),
};
DataSet dsReturn = null;
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
int incheckedid = Convert.ToInt32(ds.Tables[0].Rows[0]["incheckedid"]);
sqlString = @"SELECT to_char(checked.UserCheckedCount) || '/' ||
to_char(checked.CheckedCount) CheckedCount
,to_char(checked.UserOverageCount) || '/' ||
to_char(checked.OverageCount) OverageCount
,to_char(checked.UserCheckedCount + checked.UserOverageCount) || '/' ||
to_char(checked.CheckedCount + checked.OverageCount) || '/' ||
to_char(TCount) TCount
FROM (SELECT icd.incheckedid
, SUM(CASE
WHEN icd.checkeduserid = :userid AND icd.InCheckedFlag = '1' THEN
1
ELSE
0
END) UserCheckedCount
, SUM(CASE
WHEN icd.checkeduserid = :userid AND icd.InCheckedFlag = '2' THEN
1
ELSE
0
END) UserOverageCount
, SUM(CASE
WHEN icd.InCheckedFlag = '1' THEN
1
ELSE
0
END) CheckedCount
, SUM(CASE
WHEN icd.InCheckedFlag = '2' THEN
1
ELSE
0
END) OverageCount
,COUNT(icd.incheckedid) TCount
FROM tp_pm_incheckeddetail icd
WHERE icd.incheckedid = :incheckedid
AND icd.valueflag = '1' GROUP BY icd.incheckedid) checked";
paras = new OracleParameter[]{
new OracleParameter(":incheckedid",OracleDbType.Int32, incheckedid,ParameterDirection.Input),
new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
ds = con.GetSqlResultToDs(sqlString, paras);
ds.Tables[0].TableName = "Table1";
dsReturn = new DataSet();
dsReturn.Tables.Add(ds.Tables[0].Copy());
sqlString = @"
SELECT icd.goodscode, COUNT(icd.goodscode) goodscount
FROM tp_pm_incheckeddetail icd
WHERE icd.incheckedid = :incheckedid
AND icd.checkeduserid = :userid
AND icd.InCheckedFlag = '1'
AND icd.valueflag = '1'
GROUP BY icd.goodscode";
paras = new OracleParameter[]{
new OracleParameter(":incheckedid",OracleDbType.Int32, incheckedid,ParameterDirection.Input),
new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
ds = con.GetSqlResultToDs(sqlString, paras);
ds.Tables[0].TableName = "Table2";
dsReturn.Tables.Add(ds.Tables[0].Copy());
}
return dsReturn;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取半成品检验列表
/// 实体类
///
/// DataSet
public static DataSet GetSemiCheck(SemiCheckEntity entity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
List parameters = new List();
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
string sqlString = @"select
SemiCheck.SemiCheckID,
CProcedure.ProcedureName as CProcedureName,
SemiCheck.ProcedureTime,
SemiCheck.UserCode,
GoodsType2.GoodsTypeName as LevelGoodsTypeName,
GoodsType.GoodsTypeName,
Goods.GoodsCode,
SemiCheck.BarCode,
-- 复检后不显示半检状态
SemiCheckType.SemiCheckTypeName,
--decode(SemiCheck.ReSemiCheckType, 0, SemiCheckType.SemiCheckTypeName, '已复检') SemiCheckTypeName
SemiCheck.SemiCheckUserCode,
SemiCheck.SemiCheckTime,
ReSemiCheckType.ReSemiCheckTypeName,
SemiCheck.ReSemiCheckUserCode,
SemiCheck.ReSemiCheckTime,
decode(SemiCheck.BackOutFlag,'0','正常','撤销') as BackOutFlag,
SemiCheck.BackOutUserCode,
SemiCheck.BackOutTime,
SemiCheck.GroutingUserCode,
SemiCheck.GroutingDate,
GroutingDailyDetail.DeliverTime,
logo.logoName,
RProcedure.ProcedureName as RProcedureName,
SemiCheck.ReworkUserCode
from TP_PM_SemiCheck SemiCheck
left join TP_PC_Procedure CProcedure
on SemiCheck.ProcedureID=CProcedure.ProcedureID
left join TP_MST_Goods Goods
on SemiCheck.GoodsID=Goods.GoodsID
left join TP_MST_GoodsType GoodsType
on Goods.GoodsTypeID=GoodsType.GoodsTypeID
left join TP_MST_GoodsType GoodsType2
on GoodsType2.accountid=GoodsType.accountid
and GoodsType2.GoodsTypecode=substr(GoodsType.GoodsTypecode,1,6)
left join TP_SYS_SemiCheckType SemiCheckType
on SemiCheck.SemiCheckType=SemiCheckType.SemiCheckTypeID
left join TP_SYS_ReSemiCheckType ReSemiCheckType
on SemiCheck.ReSemiCheckType=ReSemiCheckType.ReSemiCheckTypeID
left join TP_PM_GroutingDailyDetail GroutingDailyDetail
on GroutingDailyDetail.GroutingDailyDetailID=SemiCheck.GroutingDailyDetailID
left join TP_MST_Logo logo
on GroutingDailyDetail.LogoID=logo.LogoID
left join TP_PC_Procedure RProcedure
on SemiCheck.ReworkProcedureID=RProcedure.ProcedureID
where SemiCheck.AccountID=:AccountID
";
// 完成工序
if (!string.IsNullOrEmpty(entity.CProcedureIDS))
{
sqlString = sqlString + " AND instr(','||:CProcedureIDS||',',','||SemiCheck.ProcedureID||',')>0 ";
parameters.Add(new OracleParameter(":CProcedureIDS", OracleDbType.NVarchar2, entity.CProcedureIDS, ParameterDirection.Input));
}
// 完成时间起始
if (entity.CDateTimeStart.HasValue)
{
sqlString = sqlString + " AND SemiCheck.ProcedureTime >= :CDateTimeStart ";
parameters.Add(new OracleParameter(":CDateTimeStart", OracleDbType.Date, entity.CDateTimeStart.Value, ParameterDirection.Input));
}
// 完成时间结束
if (entity.CDateTimeEnd.HasValue)
{
sqlString = sqlString + " AND SemiCheck.ProcedureTime <= :CDateTimeEnd ";
parameters.Add(new OracleParameter(":CDateTimeEnd", OracleDbType.Date, entity.CDateTimeStart.Value, ParameterDirection.Input));
}
// 完成工号
if (!string.IsNullOrEmpty(entity.CUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.UserCode,:UserCode)>0";
parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, entity.CUserCode, ParameterDirection.Input));
}
// 产品类别
if (!string.IsNullOrEmpty(entity.GoodsTypeCode))
{
sqlString = sqlString + " AND instr(GoodsType.GoodsTypeCode,:GoodsTypeCode)=1 ";
parameters.Add(new OracleParameter(":GoodsTypeCode", OracleDbType.NVarchar2, entity.GoodsTypeCode, ParameterDirection.Input));
}
// 产品编码
if (!string.IsNullOrEmpty(entity.GoodsCode))
{
sqlString = sqlString + " AND instr(Goods.GoodsCode,:GoodsCode)>0";
parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, entity.GoodsCode, ParameterDirection.Input));
}
// 成型工号
if (!string.IsNullOrEmpty(entity.GroutingUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.GroutingUserCode,:GroutingUserCode)>0";
parameters.Add(new OracleParameter(":GroutingUserCode", OracleDbType.NVarchar2, entity.GroutingUserCode, ParameterDirection.Input));
}
// 注浆时间起始
if (entity.GroutingDateTimeStart.HasValue)
{
sqlString = sqlString + " AND SemiCheck.GroutingDate >= :GroutingDateTimeStart ";
parameters.Add(new OracleParameter(":GroutingDateTimeStart", OracleDbType.Date, entity.GroutingDateTimeStart.Value, ParameterDirection.Input));
}
// 注浆时间结束
if (entity.GroutingDateTimeEnd.HasValue)
{
sqlString = sqlString + " AND SemiCheck.GroutingDate <= :GroutingDateTimeEnd ";
parameters.Add(new OracleParameter(":GroutingDateTimeEnd", OracleDbType.Date, entity.GroutingDateTimeEnd.Value, ParameterDirection.Input));
}
// 交坯时间起始
if (entity.DeliveryDateTimeStart.HasValue)
{
sqlString = sqlString + " AND GroutingDailyDetail.DeliverTime >= :DeliveryDateTimeStart ";
parameters.Add(new OracleParameter(":DeliveryDateTimeStart", OracleDbType.Date, entity.DeliveryDateTimeStart.Value, ParameterDirection.Input));
}
// 交坯时间结束
if (entity.DeliveryDateTimeEnd.HasValue)
{
sqlString = sqlString + " AND GroutingDailyDetail.DeliverTime <= :DeliveryDateTimeEnd ";
parameters.Add(new OracleParameter(":DeliveryDateTimeEnd", OracleDbType.Date, entity.DeliveryDateTimeEnd.Value, ParameterDirection.Input));
}
// 半检状态
if (!string.IsNullOrEmpty(entity.SemiCheckType))
{
sqlString = sqlString + " AND instr(','||:SemiCheckType||',',','||SemiCheck.SemiCheckType||',')>0 ";
parameters.Add(new OracleParameter(":SemiCheckType", OracleDbType.NVarchar2, entity.SemiCheckType, ParameterDirection.Input));
}
// 半检时间起始
if (entity.SemiCheckDateTimeStart.HasValue)
{
sqlString = sqlString + " AND SemiCheck.SemiCheckTime >= :SemiCheckDateTimeStart ";
parameters.Add(new OracleParameter(":SemiCheckDateTimeStart", OracleDbType.Date, entity.SemiCheckDateTimeStart.Value, ParameterDirection.Input));
}
// 半检时间结束
if (entity.SemiCheckDateTimeEnd.HasValue)
{
sqlString = sqlString + " AND SemiCheck.SemiCheckTime <= :SemiCheckDateTimeEnd ";
parameters.Add(new OracleParameter(":SemiCheckDateTimeEnd", OracleDbType.Date, entity.SemiCheckDateTimeEnd.Value, ParameterDirection.Input));
}
// 半检工号
if (!string.IsNullOrEmpty(entity.SemiCheckUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.SemiCheckUserCode,:SemiCheckUserCode)>0";
parameters.Add(new OracleParameter(":SemiCheckUserCode", OracleDbType.NVarchar2, entity.SemiCheckUserCode, ParameterDirection.Input));
}
// 返工工序
if (!string.IsNullOrEmpty(entity.RProcedureIDS))
{
sqlString = sqlString + " AND instr(','||:RProcedureIDS||',',','||SemiCheck.ReworkProcedureID||',')>0 ";
parameters.Add(new OracleParameter(":RProcedureIDS", OracleDbType.NVarchar2, entity.RProcedureIDS, ParameterDirection.Input));
}
// 返工工号
if (!string.IsNullOrEmpty(entity.ReworkUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.ReworkUserCode,:ReworkUserCode)>0";
parameters.Add(new OracleParameter(":ReworkUserCode", OracleDbType.NVarchar2, entity.ReworkUserCode, ParameterDirection.Input));
}
// 复检状态
if (!string.IsNullOrEmpty(entity.ReSemiCheckType))
{
sqlString = sqlString + " AND instr(','||:ReSemiCheckType||',',','||SemiCheck.ReSemiCheckType||',')>0 ";
parameters.Add(new OracleParameter(":ReSemiCheckType", OracleDbType.NVarchar2, entity.ReSemiCheckType, ParameterDirection.Input));
}
// 复检时间起始
if (entity.ReSemiCheckDateTimeStart.HasValue)
{
sqlString = sqlString + " AND SemiCheck.ReSemiCheckTime >= :ReSemiCheckDateTimeStart ";
parameters.Add(new OracleParameter(":ReSemiCheckDateTimeStart", OracleDbType.Date, entity.ReSemiCheckDateTimeStart.Value, ParameterDirection.Input));
}
// 复检时间结束
if (entity.ReSemiCheckDateTimeEnd.HasValue)
{
sqlString = sqlString + " AND SemiCheck.ReSemiCheckTime <= :ReSemiCheckDateTimeEnd ";
parameters.Add(new OracleParameter(":ReSemiCheckDateTimeEnd", OracleDbType.Date, entity.ReSemiCheckDateTimeEnd.Value, ParameterDirection.Input));
}
// 复检工号
if (!string.IsNullOrEmpty(entity.ReSemiCheckUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.ReSemiCheckUserCode,:ReSemiCheckUserCode)>0";
parameters.Add(new OracleParameter(":ReSemiCheckUserCode", OracleDbType.NVarchar2, entity.ReSemiCheckUserCode, ParameterDirection.Input));
}
// 撤销标识
if (!string.IsNullOrEmpty(entity.BackOutFlag))
{
sqlString = sqlString + " AND instr(','||:BackOutFlag||',',','||SemiCheck.BackOutFlag||',')>0 ";
parameters.Add(new OracleParameter(":BackOutFlag", OracleDbType.NVarchar2, entity.BackOutFlag, ParameterDirection.Input));
}
// 撤销时间起始
if (entity.BackOutTimeStart.HasValue)
{
sqlString = sqlString + " AND SemiCheck.BackOutTime >= :BackOutTimeStart ";
parameters.Add(new OracleParameter(":BackOutTimeStart", OracleDbType.Date, entity.BackOutTimeStart.Value, ParameterDirection.Input));
}
// 撤销时间结束
if (entity.BackOutTimeEnd.HasValue)
{
sqlString = sqlString + " AND SemiCheck.BackOutTime <= :BackOutTimeEnd ";
parameters.Add(new OracleParameter(":BackOutTimeEnd", OracleDbType.Date, entity.BackOutTimeEnd.Value, ParameterDirection.Input));
}
// 撤销工号
if (!string.IsNullOrEmpty(entity.BackOutUserCode))
{
sqlString = sqlString + " AND instr(SemiCheck.BackOutUserCode,:BackOutUserCode)>0";
parameters.Add(new OracleParameter(":BackOutUserCode", OracleDbType.NVarchar2, entity.BackOutUserCode, ParameterDirection.Input));
}
// 产品条码
if (!string.IsNullOrEmpty(entity.BarCode))
{
sqlString = sqlString + " AND instr(SemiCheck.BarCode,:BarCode)>0";
parameters.Add(new OracleParameter(":BarCode", OracleDbType.NVarchar2, entity.BarCode, ParameterDirection.Input));
}
DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return ds;
}
return null;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取半成品检验缺陷列表
/// 半成品检验ID
///
/// DataSet
public static DataSet GetSemiCheckDefect(int semiCheckID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
List parameters = new List();
parameters.Add(new OracleParameter(":semiCheckID", OracleDbType.Int32, semiCheckID, ParameterDirection.Input));
string sqlString = @"select
TP_PM_SemiCheckDefect.DefectCode,
TP_PM_SemiCheckDefect.DefectName,
TP_PM_SemiCheckDefect.DefectPositionCode,
TP_PM_SemiCheckDefect.DefectPositionName,
TP_PC_Procedure.ProcedureName,
TP_PM_SemiCheckDefect.DefectUserCode
from TP_PM_SemiCheckDefect
left join TP_PC_Procedure
on TP_PM_SemiCheckDefect.DefectProcedureID=TP_PC_Procedure.ProcedureID
where TP_PM_SemiCheckDefect.semiCheckID=:semiCheckID
";
DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return ds;
}
return null;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据所选工号，查出缺陷责任员工
///
/// 用户ID
/// 用户基本信息
/// DataSet
public static DataSet GetSemiCheckDefectStaffByUserID(SUserInfo sUserInfo, int userid)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,
TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID,
TP_MST_UserStaff.Ujobsid
from TP_MST_UserStaff
left join TP_HR_Staff
on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid
where TP_MST_UserStaff.Userid=:userid
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取标识
///
/// 产品条码ram>
/// int
public static int GetRecyclingflagByBarcode(string barcode, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = "select recyclingflag from TP_PM_ScrapProduct "
+ " where BarCode=:BarCode and CreateTime=(select max(CreateTime) "
+ " from TP_PM_ScrapProduct "
+ " where BarCode=:BarCode and ValueFlag=1)"
+ " and ValueFlag=1";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input),
};
DataSet ds = con.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0]["recyclingflag"]);
}
return 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 查询产品组件报损履历 add xiacm 2022-11-17
///
///
///
///
public static DataSet GetIdnrkOnlyCode(string IdnrkOnlyCode, DateTime createTime, string confirmFlag, SUserInfo sUserInfo, out string message)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
message = "";
DataSet ds = new DataSet();
if (!string.IsNullOrEmpty(IdnrkOnlyCode))
{
if ("1".Equals(confirmFlag))
{
string sqlString = @"
SELECT BIS.SCRAPID,
BIS.MATNR,
BIS.IDNRK,
BIS.MEINS,
TO_CHAR(BIS.MENGE, 'FM9999999999999999.000') MENGESUM,
BIS.IDNRKNAME,
BIS.IDNRKONLYCODE,
BIS.BARCODE,
BIS.CHECKFLAG
FROM TP_PM_BARCODEIDNRKSCRAP BIS
WHERE BIS.VALUEFLAG = '1'
AND BIS.SCRAPTYPE = '1'
AND ACCOUNTID = :ACCOUNTID
AND IDNRKONLYCODE = :IDNRKONLYCODE ";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":IDNRKONLYCODE",OracleDbType.NVarchar2, IdnrkOnlyCode,ParameterDirection.Input),
};
ds = con.GetSqlResultToDs(sqlString, paras);
if (ds.Tables[0].Rows.Count == 0)
{
message = "当前条码不存在。";
}
}
else
{
string sqlString = @"
SELECT BIS.SCRAPID,
BIS.MATNR,
BIS.IDNRK,
BIS.MEINS,
TO_CHAR(BIS.MENGE, 'FM9999999999999999.000') MENGESUM,
BIS.IDNRKNAME,
BIS.IDNRKONLYCODE,
BIS.BARCODE
FROM TP_PM_BARCODEIDNRKSCRAP BIS
WHERE BIS.VALUEFLAG = '1'
AND BIS.SCRAPTYPE = '1'
AND ACCOUNTID = :ACCOUNTID
AND IDNRKONLYCODE = :IDNRKONLYCODE ";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":IDNRKONLYCODE",OracleDbType.NVarchar2, IdnrkOnlyCode,ParameterDirection.Input),
};
ds = con.GetSqlResultToDs(sqlString, paras);
if (ds.Tables[0].Rows.Count == 0)
{
message = "当前条码不存在。";
}
}
}
else
{
string sqlString = @"
SELECT T.SCRAPID,
T.IDNRK,
T.MEINS,
T.IDNRKNAME,
TO_CHAR(T.MENGESUM, 'FM9999999999999999.000') AS MENGESUM,
TO_CHAR(DECODE(T.CONFIRMSUM, 0, T.MENGESUM, T.CONFIRMSUM), 'FM9999999999999999.000') AS CONFIRMSUM,
DECODE(T.CONFIRMSUM, 0, 0, 1) AS ISCONFIRM
FROM (SELECT 0 SCRAPID,
SUM(BIS.MENGE) MENGESUM,
SUM(DECODE(BIS.CHECKFLAG, '0', 0, BIS.MENGE)) CONFIRMSUM,
BIS.IDNRK,
BIS.MEINS,
BIS.IDNRKNAME
FROM TP_PM_BARCODEIDNRKSCRAP BIS
WHERE (BIS.IDNRKONLYCODE = '' OR BIS.IDNRKONLYCODE = ' ' OR BIS.IDNRKONLYCODE IS NULL)
AND BIS.VALUEFLAG = '1'
AND BIS.SCRAPTYPE = '1'
AND BIS.SYNCFLAG = '0'
AND BIS.ACCOUNTID = :ACCOUNTID
AND BIS.CREATETIME >= :CREATETIMEFROM
AND BIS.CREATETIME < :CREATETIMETO
GROUP BY BIS.IDNRK,
BIS.MEINS,
BIS.IDNRKNAME) T ";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
new OracleParameter(":CREATETIMEFROM", OracleDbType.Date, createTime, ParameterDirection.Input),
new OracleParameter(":CREATETIMETO", OracleDbType.Date, createTime.AddDays(1), ParameterDirection.Input),
};
ds = con.GetSqlResultToDs(sqlString, paras);
}
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 确认产品组件报损履历 add xiacm 2022-11-17
///
///
///
///
public static ServiceResultEntity ConfirmBarcodeIdnrkScrap(ClientRequestEntity cre, SUserInfo sUserInfo)
{
ServiceResultEntity result = new ServiceResultEntity();
result.Status = Constant.ServiceResultStatus.Success;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
string sqlString = string.Empty;
int returnRow = Constant.INT_IS_ZERO;
try
{
oracleTrConn.Connect();
OracleParameter[] paras = null;
DataTable IdnrkScrapDt = cre.Data.Tables[0];
DateTime createTime = Convert.ToDateTime(cre.Properties["CreateTime"]);
decimal mengesum = 0;
foreach (DataRow item in IdnrkScrapDt.Rows)
{
int scrapid = Convert.ToInt32(item["SCRAPID"]);//报损id
string idnrk = item["IDNRK"].ToString();//组件编码
decimal.TryParse(item["mengesum"] + "", out mengesum);
if (string.IsNullOrEmpty(idnrk))
{
result.Status = Constant.ServiceResultStatus.SystemError;
return result;
}
//无组件唯一编码
if (scrapid == 0)
{
sqlString = @"
SELECT BIS.SCRAPID,
BIS.MATNR,
BIS.IDNRK,
BIS.MEINS,
BIS.MENGE
FROM TP_PM_BARCODEIDNRKSCRAP BIS
WHERE (BIS.IDNRKONLYCODE = '' OR BIS.IDNRKONLYCODE = ' ' OR BIS.IDNRKONLYCODE IS NULL)
AND BIS.VALUEFLAG = '1'
AND BIS.SCRAPTYPE = '1'
AND BIS.SYNCFLAG = '0'
AND BIS.ACCOUNTID = :ACCOUNTID
AND BIS.CREATETIME >= :CREATETIMEFROM
AND BIS.CREATETIME < :CREATETIMETO
AND BIS.IDNRK = :IDNRK
ORDER BY BIS.MENGE DESC ";
paras = new OracleParameter[]
{
new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":CREATETIMEFROM", OracleDbType.Date, createTime, ParameterDirection.Input),
new OracleParameter(":CREATETIMETO", OracleDbType.Date, createTime.AddDays(1), ParameterDirection.Input),
new OracleParameter(":IDNRK",OracleDbType.NVarchar2, idnrk, ParameterDirection.Input),
// new OracleParameter(":MATNR",OracleDbType.Int32,MATNR,ParameterDirection.Input),
// new OracleParameter(":MEINS",OracleDbType.Int32,MEINS ,ParameterDirection.Input),
};
DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt.Rows.Count == 0)
{
result.Status = Constant.ServiceResultStatus.SystemError;
result.Message = "报损已确认或报损数据已改变";
return result;
}
decimal menge = 0;
foreach (DataRow row in dt.Rows)
{
decimal.TryParse(row["MENGE"] + "", out menge);
if (mengesum >= menge)
{
//报损确认
sqlString = @"
UPDATE TP_PM_BARCODEIDNRKSCRAP
SET CHECKFLAG = '1',
CHECKTIME = SYSDATE,
UPDATETIME = SYSDATE,
UPDATEUSERID = :UPDATEUSERID
WHERE SCRAPID = :SCRAPID";
paras = new OracleParameter[]
{
new OracleParameter(":SCRAPID", OracleDbType.Int32, row["SCRAPID"], ParameterDirection.Input),
new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, paras);
mengesum -= menge;
}
else
{
//取消确认
sqlString = @"
UPDATE TP_PM_BARCODEIDNRKSCRAP
SET CHECKFLAG = '0',
CHECKTIME = SYSDATE,
UPDATETIME = SYSDATE,
UPDATEUSERID = :UPDATEUSERID
WHERE SCRAPID = :SCRAPID";
paras = new OracleParameter[]
{
new OracleParameter(":SCRAPID", OracleDbType.Int32, row["SCRAPID"], ParameterDirection.Input),
new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, paras);
}
}
if (mengesum > 0)
{
result.Status = Constant.ServiceResultStatus.SystemError;
result.Message = "可确认数量不足";
return result;
}
}
else
{
////验证履历状态
//sqlString = @"select 1 from TP_PM_BARCODEIDNRKSCRAP bis
// where bis.ValueFlag ='1'
// and bis.CheckFlag = '0'
// and bis.AccountID =:accountid
// and bis.SCRAPTYPE = '1'
// and bis.SCRAPID = :SCRAPID";
//OracleParameter[] paras = new OracleParameter[]
//{
// new OracleParameter(":SCRAPID",OracleDbType.Int32, scrapid, ParameterDirection.Input),
// new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
//};
//DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
//if (dt.Rows.Count == 0)
//{
// result.Status = Constant.ServiceResultStatus.SystemError;
// result.Message = "报损已确认或报损数据已改变";
// return result;
//}
if ("1".Equals(cre.Properties["ConfirmFlag"]+""))
{
//报损确认
sqlString = @"UPDATE tp_pm_barcodeidnrkscrap
SET checkflag = '1'
,checktime = SYSDATE
,updatetime = SYSDATE
,updateuserid = :updateuserid
where SCRAPID = :SCRAPID";
paras = new OracleParameter[]
{
new OracleParameter(":SCRAPID",OracleDbType.Int32, scrapid, ParameterDirection.Input),
new OracleParameter(":updateuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, paras);
}
else
{
//报损撤销
sqlString = @"UPDATE tp_pm_barcodeidnrkscrap
SET checkflag = '0'
,checktime = SYSDATE
,updatetime = SYSDATE
,updateuserid = :updateuserid
where SCRAPID = :SCRAPID";
paras = new OracleParameter[]
{
new OracleParameter(":SCRAPID",OracleDbType.Int32, scrapid, ParameterDirection.Input),
new OracleParameter(":updateuserid",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, paras);
}
}
}
oracleTrConn.Commit();
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
result.Status = Constant.ServiceResultStatus.SystemError;
result.Message = "确认产品组件报损失败";
throw ex;
}
finally
{
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
result.Result = returnRow;
return result;
}
///
/// 次品扫描功能 add qq 20230406
///
///
///
///
public static ServiceResultEntity RecyclingAddBarcode(string barcode,string type, SUserInfo sUserInfo) {
ServiceResultEntity result = new ServiceResultEntity();
result.Status = Constant.ServiceResultStatus.Success;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
string sqlString = string.Empty;
int returnRow = Constant.INT_IS_ZERO;
try
{
oracleTrConn.Connect();
//1：次品回收扫描 2：次品补釉 3：次品装窑 4：次品卸窑
if (type == "1")
{
//是次品
sqlString = @"select 1 from tp_pm_inproduction where goodslevelid = 7 and valueflag = '1' and barcode = :barcode";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0 )
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 1";
paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
//提示已存在该数据
returnRow = -1;
}
else
{
sqlString = @"INSERT INTO TP_PM_Recycling
( ScanType,
BarCode,
Remarks,
AccountID,
ValueFlag,
CreateUserID,
UpdateUserID
) values
(
1,
:BarCode,
:Remarks,
:AccountID,
1,
:CreateUserID,
:UpdateUserID
)
";
OracleParameter[] parmeters1 = new OracleParameter[]
{
new OracleParameter(":BarCode",barcode),
new OracleParameter(":Remarks",""),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":CreateUserID",sUserInfo.UserID),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, parmeters1);
}
}
else
{
//提示不是次品，无法扫描
returnRow = -2;
}
}
else if (type == "2")
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 1";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 2";
paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
//提示已做过补釉
returnRow = -3;
}
else
{
sqlString = @"INSERT INTO TP_PM_Recycling
( ScanType,
BarCode,
Remarks,
AccountID,
ValueFlag,
CreateUserID,
UpdateUserID
) values
(
2,
:BarCode,
:Remarks,
:AccountID,
1,
:CreateUserID,
:UpdateUserID
)
";
OracleParameter[] parmeters1 = new OracleParameter[]
{
new OracleParameter(":BarCode",barcode),
new OracleParameter(":Remarks",""),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":CreateUserID",sUserInfo.UserID),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, parmeters1);
}
}
else
{
//提示未做过次品扫描
returnRow = -4;
}
}
else if (type == "3")
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 1";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 3";
paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
//提示已做过登窑
returnRow = -5;
}
else
{
sqlString = @"INSERT INTO TP_PM_Recycling
( ScanType,
BarCode,
Remarks,
AccountID,
ValueFlag,
CreateUserID,
UpdateUserID
) values
(
3,
:BarCode,
:Remarks,
:AccountID,
1,
:CreateUserID,
:UpdateUserID
)
";
OracleParameter[] parmeters1 = new OracleParameter[]
{
new OracleParameter(":BarCode",barcode),
new OracleParameter(":Remarks",""),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":CreateUserID",sUserInfo.UserID),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, parmeters1);
}
}
else
{
//提示未做过次品扫描
returnRow = -4;
}
}
else if (type == "4")
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 3";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
sqlString = @" SELECT * FROM TP_PM_Recycling where barcode = :barcode and scantype = 4";
paras = new OracleParameter[]
{
new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
};
dt = oracleTrConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0)
{
//提示已做过卸窑
returnRow = -6;
}
else
{
sqlString = @"INSERT INTO TP_PM_Recycling
( ScanType,
BarCode,
Remarks,
AccountID,
ValueFlag,
CreateUserID,
UpdateUserID
) values
(
4,
:BarCode,
:Remarks,
:AccountID,
1,
:CreateUserID,
:UpdateUserID
)
";
OracleParameter[] parmeters1 = new OracleParameter[]
{
new OracleParameter(":BarCode",barcode),
new OracleParameter(":Remarks",""),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":CreateUserID",sUserInfo.UserID),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
};
returnRow += oracleTrConn.ExecuteNonQuery(sqlString, parmeters1);
}
}
else
{
//提示未做过登窑
returnRow = -7;
}
}
oracleTrConn.Commit();
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
result.Status = Constant.ServiceResultStatus.SystemError;
result.Message = "保存失败";
throw ex;
}
finally
{
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
result.Result = returnRow;
return result;
}
#region 发货单
///
/// 获取仓库代码
///
///
///
///
public static ServiceResultEntity GetSendWarehouse(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @"SELECT WAREHOUSECODE,WAREHOUSENAME,WAREHOUSETYPE FROM TP_PC_SENDWAREHOUSE WHERE VALUEFLAG = 1";
IDataParameter[] paras = new OracleParameter[] { };
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 获取车牌号
///
///
///
///
public static ServiceResultEntity GetNumberPlate(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @"SELECT NUMBERPLATECODE FROM TP_PC_NUMBERPLATE WHERE VALUEFLAG = 1";
IDataParameter[] paras = new OracleParameter[] { };
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 获取发货单日志表
///
///
///
///
public static ServiceResultEntity GetSendOutGoodsLog(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @"SELECT
SENDOUTGOODSLOGID,
SENDOUTCODE,
USERID,
USERCODE,
USERNAME,
WAREHOUSINGCODE,
WAREHOUSINGNAME,
RECEIVECODE,
RECEIVENAME,
CARPLATE,
ACCOUNTDATE,
DELIVERDATE,
SYNCSTATUS,
ISREVOKE,
ISAMENDS
FROM
TP_PM_SENDOUTGOODSLOG
WHERE
VALUEFLAG = 1 ";
//发货单号
if (cre.Properties["SENDOUTCODE"].ToString() != null && cre.Properties["SENDOUTCODE"].ToString() != "")
{
sqlString += " AND SENDOUTCODE LIKE :SENDOUTCODE ";
}
if (cre.Properties["WAREHOUSINGCODE"].ToString() != null && cre.Properties["WAREHOUSINGCODE"].ToString() != "")
{
sqlString += " AND WAREHOUSINGCODE = :WAREHOUSINGCODE ";
}
if (cre.Properties["RECEIVECODE"].ToString() != null && cre.Properties["RECEIVECODE"].ToString() != "")
{
sqlString += " AND RECEIVECODE = :RECEIVECODE ";
}
if (cre.Properties["NUMBERPLATE"].ToString() != null && cre.Properties["NUMBERPLATE"].ToString() != "")
{
sqlString += " AND CARPLATE = :NUMBERPLATE ";
}
if (cre.Properties["ACCOUNTDATE"].ToString() != null && cre.Properties["ACCOUNTDATE"].ToString() != "")
{
sqlString += " AND ACCOUNTDATE = :ACCOUNTDATE ";
}
if (cre.Properties["DELIVERDATE"].ToString() != null && cre.Properties["DELIVERDATE"].ToString() != "")
{
}
if (cre.Properties["SYNCSTATUS"].ToString() != null && cre.Properties["SYNCSTATUS"].ToString() != "")
{
sqlString += " AND SYNCSTATUS = :SYNCSTATUS ";
}
IDataParameter[] paras = new OracleParameter[]
{
new OracleParameter(":SENDOUTCODE", OracleDbType.NVarchar2, "%"+cre.Properties["SENDOUTCODE"]+"%", ParameterDirection.Input),
new OracleParameter(":WAREHOUSINGCODE", OracleDbType.NVarchar2, cre.Properties["WAREHOUSINGCODE"], ParameterDirection.Input),
new OracleParameter(":RECEIVECODE", OracleDbType.NVarchar2, cre.Properties["RECEIVECODE"], ParameterDirection.Input),
new OracleParameter(":NUMBERPLATE", OracleDbType.NVarchar2, cre.Properties["NUMBERPLATE"], ParameterDirection.Input),
new OracleParameter(":ACCOUNTDATE", OracleDbType.NVarchar2, cre.Properties["ACCOUNTDATE"], ParameterDirection.Input),
new OracleParameter(":DELIVERDATE", OracleDbType.NVarchar2, cre.Properties["DELIVERDATE"], ParameterDirection.Input),
new OracleParameter(":SYNCSTATUS", OracleDbType.NVarchar2, cre.Properties["SYNCSTATUS"], ParameterDirection.Input)
};
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 发货单日志明细表查询
///
///
///
///
public static ServiceResultEntity GetSendOutGoodsLogDetail(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @"SELECT
SENDOUTGOODSLOGDETAILID,
SENDOUTGOODSLOGID,
OUTLABELCODE,
GOODSCODE,
MATERIALCODE,
MATERIALREMARK,
BINDINGCODE
FROM
TP_PM_SENDOUTGOODSLOGDETAIL
WHERE
VALUEFLAG = 1
AND SENDOUTGOODSLOGID = :SENDOUTGOODSLOGID ";
IDataParameter[] paras = new OracleParameter[]
{
new OracleParameter(":SENDOUTGOODSLOGID", OracleDbType.NVarchar2, cre.Properties["SENDOUTGOODSLOGID"], ParameterDirection.Input),
};
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
#endregion
}
}