/*******************************************************************************
* 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 裸瓷包装撤销（非工序撤销）
///
/// 检验条码pda
///
/// 工序ID
/// 产品条码
/// DataSet
public static ServiceResultEntity CheckLuoci(int procedureID, string barcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
ServiceResultEntity sre = new ServiceResultEntity();
DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable();
DataSet returnDs = new DataSet();
DataRow dr = dtBarCode.NewRow();
string sqlString = "";
#region 查找条码
sqlString = @"SELECT 1 FROM TP_PM_GROUTINGDAILYDETAIL
WHERE BARCODE = :BARCODE
AND VALUEFLAG = '1'";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BARCODE",barcode),
};
DataTable dt = oracleConn.GetSqlResultToDt(sqlString, paras);
if (dt == null || dt.Rows.Count == 0)
{
//无效条码
sre.OtherStatus = -1;
sre.Message = "无效条码";
return sre;
}
#endregion
#region 校验产品是否是成品
sqlString = @"SELECT 1 FROM TP_PM_FINISHEDPRODUCT
WHERE BARCODE = :BARCODE
AND VALUEFLAG = '1'";
dt = oracleConn.GetSqlResultToDt(sqlString, paras);
if (dt == null || dt.Rows.Count == 0)
{
//非成品，不允许此操作
sre.OtherStatus = -1;
sre.Message = "非成品，不允许此操作";
return sre;
}
#endregion
#region 校验是否裸瓷包装产品
sqlString = @"SELECT PROCEDUREID FROM (
SELECT * FROM TP_PM_PRODUCTIONDATA WHERE BARCODE = :BARCODE AND VALUEFLAG ='1' ORDER BY CREATETIME DESC )
WHERE ROWNUM =1";
dt = oracleConn.GetSqlResultToDt(sqlString, paras);
if (dt != null && dt.Rows.Count > 0 && Convert.ToInt32(dt.Rows[0]["PROCEDUREID"]) != 159)
{
//非裸瓷包装产品，不允许此操作
sre.OtherStatus = -1;
sre.Message = "非裸瓷包装产品，不允许此操作";
return sre;
}
#endregion
sqlString = @"SELECT
I.BARCODE,--产品条码
G.GOODSCODE,--产品编码
G.GOODSNAME,--产品名称
L.LOGONAME,--商标名称
I.GROUTINGUSERCODE, --成型工号
NVL(GDD.MATERIALCODE, NVL(G.MATERIALCODE,G.GOODSCODE)) AS MATERIALCODE, --物料编码
'' AS ERRMSG
FROM TP_PM_FINISHEDPRODUCT I
LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = I.GROUTINGDAILYDETAILID
LEFT JOIN TP_MST_LOGO L ON GDD.LOGOID=L.LOGOID
LEFT JOIN TP_MST_GOODS G ON G.GOODSID = I.GOODSID
WHERE I.BARCODE=:BARCODE ";
DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 )
{
sre.Data = ds;
sre.OtherStatus = 1;
}
else
{
sre.OtherStatus = -1;
sre.Message = "未查询到数据";
}
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 撤销裸瓷包装（工序不撤销，仅从成品变为在产）
///
/// 产品条码
///
public static ServiceResultEntity AddChancelLuoci(DataTable detailTable, SUserInfo sUserInfo)
{
ServiceResultEntity entity = new ServiceResultEntity();
int returnRows = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
if (detailTable != null && detailTable.Rows.Count >0)
{
foreach (DataRow row in detailTable.Rows)
{
string barcode = row["barcode"].ToString();
int goodsLevelID;
int goodsLevelTypeID;
// 转换注浆条码
barcode = CommonModuleLogic.CommonModuleLogic.GetBarcode(oracleTrConn, barcode, sUserInfo);
// 1 条码是否在产成品表中
string sql = "select createTime,FHUserCode,GoodsCode,GoodsLevelID,GoodsLevelTypeID from tp_pm_finishedproduct where barcode=:barcode and valueflag=1";
OracleParameter[] Paras = new OracleParameter[] {
new OracleParameter(":barcode",OracleDbType.Varchar2,
barcode,ParameterDirection.Input)
};
DataSet ds = oracleTrConn.GetSqlResultToDs(sql, Paras);
if (ds.Tables[0].Rows.Count == 0)
{
//不存在产成品
returnRows = -1;
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
entity.Result = returnRows;
return entity;
}
else
{
//已经交接不允许撤销
if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0]["FHUserCode"].ToString()))
{
entity.Result = -200; //已交接，不能再次进行交接
entity.Message = "此产品【" + ds.Tables[0].Rows[0]["GoodsCode"].ToString() + "】已交接，不能进行该操作";
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
return entity;
}
else
{
goodsLevelID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsLevelID"]);
goodsLevelTypeID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsLevelTypeID"]);
}
}
sql = "select 1 from tp_pm_productiondatain where barcode=:barcode and valueflag=1";
Paras = new OracleParameter[] {
new OracleParameter(":barcode",OracleDbType.Varchar2,
barcode,ParameterDirection.Input)
};
ds = oracleTrConn.GetSqlResultToDs(sql, Paras);
if (ds.Tables[0].Rows.Count == 0)
{
// 2 生产数据恢复到在产生产数据
sql = @"insert into tp_pm_productiondatain
(
ProductionDataID,
BarCode,
CentralizedBatchNo,
ProductionLineID,
ProductionLineCode,
ProductionLineName,
ProcedureID,
ProcedureCode,
ProcedureName,
ProcedureModel,
ModelType,
PieceType,
IsReworked,
NodeType,
IsPublicBody,
IsReFire,
GoodsLevelID,
GoodsLevelTypeID,
SpecialRepairFlag,
OrganizationID,
GoodsID,
GoodsCode,
GoodsName,
UserID,
UserCode,
UserName,
ClassesSettingID,
KilnID,
KilnCode,
KilnName,
KilnCarID,
KilnCarCode,
KilnCarName,
KilnCarBatchNo,
KilnCarPosition,
ReworkProcedureID,
ReworkProcedureCode,
ReworkProcedureName,
GroutingDailyID,
GroutingDailyDetailID,
GroutingLineID,
GroutingLineCode,
GroutingLineName,
GMouldTypeID,
CanManyTimes,
GroutingLineDetailID,
GroutingDate,
GroutingMouldCode,
MouldCode,
GroutingUserID,
GroutingUserCode,
GroutingNum,
Remarks,
AccountDate,
SettlementFlag,
AccountID,
ValueFlag,
CreateTime,
CreateUserID,
UpdateTime,
UpdateUserID,
OPTimeStamp,
TriggerFlag,
LogoID,
BackOutTime,BackOutUserID,BackOutUserCode,CheckTime
)
select
ProductionDataID,
BarCode,
CentralizedBatchNo,
ProductionLineID,
ProductionLineCode,
ProductionLineName,
ProcedureID,
ProcedureCode,
ProcedureName,
ProcedureModel,
ModelType,
PieceType,
IsReworked,
NodeType,
IsPublicBody,
IsReFire,
GoodsLevelID,
GoodsLevelTypeID,
SpecialRepairFlag,
OrganizationID,
GoodsID,
GoodsCode,
GoodsName,
UserID,
UserCode,
UserName,
ClassesSettingID,
KilnID,
KilnCode,
KilnName,
KilnCarID,
KilnCarCode,
KilnCarName,
KilnCarBatchNo,
KilnCarPosition,
ReworkProcedureID,
ReworkProcedureCode,
ReworkProcedureName,
GroutingDailyID,
GroutingDailyDetailID,
GroutingLineID,
GroutingLineCode,
GroutingLineName,
GMouldTypeID,
CanManyTimes,
GroutingLineDetailID,
GroutingDate,
GroutingMouldCode,
MouldCode,
GroutingUserID,
GroutingUserCode,
GroutingNum,
Remarks,
AccountDate,
SettlementFlag,
AccountID,
ValueFlag,
CreateTime,
CreateUserID,
UpdateTime,
UpdateUserID,
OPTimeStamp,
1,
LogoID,
BackOutTime,BackOutUserID,BackOutUserCode,CheckTime
from TP_PM_ProductionData where valueflag=1 and barcode=:barcode
";
returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
}
// 3 停用在产生产数据包装数据 裸瓷包装不停用最后一条数据
// sql = "update TP_PM_ProductionDataIn set valueflag=0,updateuserID=" + sUserInfo.UserID
// + " where ProductionDataID=(select max(ProductionDataID) from TP_PM_ProductionDataIn where barcode=:barcode and valueflag=1)";
//returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
// 4 插入在产数据
sql = $@"insert into TP_PM_InProduction
(
BarCode,
ProductionLineID,
ProductionLineCode,
ProductionLineName,
FlowProcedureID,
FlowProcedureTime,
ProcedureID,
ProcedureTime,
ProductionDataID,
ProcedureModel,
ModelType,
DefectFlag,
ReworkProcedureID,
IsPublicBody,
IsReFire,
SpecialRepairFlag,
GoodsLevelID,
GoodsLevelTypeID,
GoodsID,
GoodsCode,
GoodsName,
UserID,
GroutingDailyID,
GroutingDailyDetailID,
GroutingDate,
GroutingLineID,
GroutingLineCode,
GroutingLineName,
GMouldTypeID,
CanManyTimes,
GroutingLineDetailID,
GroutingMouldCode,
MouldCode,
GroutingUserID,
GroutingUserCode,
GroutingNum,
Remarks,
KilnID,
KilnCode,
KilnName,
KilnCarID,
KilnCarCode,
KilnCarName,
KilnCarBatchNo,
KilnCarPosition,
AccountID,
ValueFlag,
CreateTime,
CreateUserID,
UpdateTime,
UpdateUserID,
OPTimeStamp,
LogoID
)
select
BarCode,
ProductionLineID,
ProductionLineCode,
ProductionLineName,
ProcedureID,
CreateTime,
ProcedureID,
CreateTime,
ProductionDataID,
ProcedureModel,
ModelType,
0,
ReworkProcedureID,
IsPublicBody,
IsReFire,
SpecialRepairFlag,
{goodsLevelID} AS GoodsLevelID,
{goodsLevelTypeID} AS GoodsLevelTypeID,
GoodsID,
GoodsCode,
GoodsName,
UserID,
GroutingDailyID,
GroutingDailyDetailID,
GroutingDate,
GroutingLineID,
GroutingLineCode,
GroutingLineName,
GMouldTypeID,
CanManyTimes,
GroutingLineDetailID,
GroutingMouldCode,
MouldCode,
GroutingUserID,
GroutingUserCode,
GroutingNum,
Remarks,
KilnID,
KilnCode,
KilnName,
KilnCarID,
KilnCarCode,
KilnCarName,
KilnCarBatchNo,
KilnCarPosition,
AccountID,
ValueFlag,
CreateTime,
CreateUserID,
UpdateTime,
UpdateUserID,
OPTimeStamp,
LogoID
from TP_PM_ProductionData where TP_PM_ProductionData.ProductionDataID=(select max(ProductionDataID) from TP_PM_ProductionData where barcode=:barcode and valueflag=1)";
returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
// 5 删除产成品表
sql = "delete from tp_pm_finishedproduct where barcode=:barcode and valueflag=1";
returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
// 6 更正在产表最新商标 从注浆表取数据 由于变更商标后走了非工序的条码打印，pd表中的最后一条商标不是最新的 ；20240508 by qq
sql = "select LogoID from tp_pm_groutingdailydetail where barcode=:barcode and valueflag=1";
DataSet dset = oracleTrConn.GetSqlResultToDs(sql, Paras);
if (dset != null && dset.Tables.Count > 0 && dset.Tables[0].Rows.Count > 0)
{
sql = "update TP_PM_InProduction set LogoID = " + dset.Tables[0].Rows[0]["LogoID"].ToString() + " where barcode=:barcode and valueflag=1";
returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
}
//7 更新裸瓷撤销标识
sql = "UPDATE TP_PM_GROUTINGDAILYDETAIL SET LUOCIFLAG = 1 WHERE BARCODE=:BARCODE AND VALUEFLAG=1";
returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras);
}
}
if (returnRows <= 0)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
else
{
oracleTrConn.Commit();
oracleTrConn.Disconnect();
}
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == System.Data.ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
throw ex;
}
finally
{
if (oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
entity.Result = returnRows;
return entity;
}
#endregion
#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
S.SENDOUTGOODSLOGID,
S.SENDOUTCODE,
S.USERID,
S.USERCODE,
-- S.WAREHOUSINGID,
S.WAREHOUSINGNAME,
-- S.RECEIVEID ,
S.RECEIVENAME,
-- S.CARPLATEID,
S.CARPLATENAME,
S.ACCOUNTDATE,
S.DELIVERDATE,
S.SYNCSTATUS,
CASE WHEN
S.SYNCSTATUS = 1 THEN '同步成功'
WHEN S.SYNCSTATUS = 2 THEN '同步失败'
ELSE '未同步' END AS SYNCSTATUSNAME,
S.SYNCTIME,
CASE WHEN S.ISREVOKE = 1 THEN '撤销成功'
WHEN S.ISREVOKE = 2 THEN '撤销失败'
ELSE '' END AS ISREVOKE,
S.REVOKETIME,
CASE WHEN S.ISAMENDS = 1 THEN '补推成功'
WHEN S.ISAMENDS = 2 THEN '补推失败'
ELSE '' END AS ISAMENDS,
S.AMENDSTIME,
S.CREATETIME,
U.USERNAME CREATEUSERNAME,
S.UPDATETIME,
U.USERNAME UPDATEUSERNAME,
S.REMARKS
FROM
TP_PM_SENDOUTGOODSLOG S
LEFT JOIN TP_MST_USER U ON U.USERID = S.CREATEUSERID
LEFT JOIN TP_MST_USER U1 ON U1.USERID = S.UPDATEUSERID
WHERE
S.VALUEFLAG = 1 ";
//发货单号
if (cre.Properties["SENDOUTCODE"].ToString() != null && cre.Properties["SENDOUTCODE"].ToString() != "")
{
sqlString += " AND S.SENDOUTCODE LIKE :SENDOUTCODE ";
}
//发出仓库
if (cre.Properties["WAREHOUSINGID"].ToString() != null && cre.Properties["WAREHOUSINGID"].ToString() != "")
{
sqlString += " AND S.WAREHOUSINGID = :WAREHOUSINGID ";
}
//接受仓库
if (cre.Properties["RECEIVEID"].ToString() != null && cre.Properties["RECEIVEID"].ToString() != "")
{
sqlString += " AND S.RECEIVEID = :RECEIVEID ";
}
//车牌号
if (cre.Properties["CARPLATEID"].ToString() != null && cre.Properties["CARPLATEID"].ToString() != "")
{
sqlString += " AND S.CARPLATEID = :CARPLATEID ";
}
//记账日期
if (cre.Properties["ACCOUNTDATE"].ToString() != null && cre.Properties["ACCOUNTDATE"].ToString() != "")
{
sqlString += " AND TRUNC(S.ACCOUNTDATE) =TO_DATE(:ACCOUNTDATE, 'YYYY-MM-DD')";
}
//送货日期
if (cre.Properties["DELIVERDATE"].ToString() != null && cre.Properties["DELIVERDATE"].ToString() != "")
{
sqlString += " AND TRUNC(S.DELIVERDATE) =TO_DATE(:DELIVERDATE, 'YYYY-MM-DD')";
}
//同步状态
if (cre.Properties["SYNCSTATUS"].ToString() != null && cre.Properties["SYNCSTATUS"].ToString() != "")
{
sqlString += " AND S.SYNCSTATUS = '" + cre.Properties["SYNCSTATUS"].ToString() + "'";
}
IDataParameter[] paras = new OracleParameter[]
{
new OracleParameter(":SENDOUTCODE", OracleDbType.NVarchar2, "%"+cre.Properties["SENDOUTCODE"]+"%", ParameterDirection.Input),
new OracleParameter(":WAREHOUSINGID", OracleDbType.Int32, cre.Properties["WAREHOUSINGID"], ParameterDirection.Input),
new OracleParameter(":RECEIVEID", OracleDbType.Int32, cre.Properties["RECEIVEID"], ParameterDirection.Input),
new OracleParameter(":CARPLATEID", OracleDbType.NVarchar2, cre.Properties["CARPLATEID"], ParameterDirection.Input),
new OracleParameter(":ACCOUNTDATE", OracleDbType.NVarchar2,cre.Properties["ACCOUNTDATE"], ParameterDirection.Input),
new OracleParameter(":DELIVERDATE", OracleDbType.NVarchar2,cre.Properties["DELIVERDATE"], 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,
BINDINGCOUNT,
BANMA
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();
}
}
}
///
/// 获取发货单总单+明细（编辑页面用）
///
///
///
///
public static ServiceResultEntity GetSendInfo(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @"SELECT
S.SENDOUTGOODSLOGID,
S.SENDOUTCODE,
S.USERID,
S.USERCODE,
S.WAREHOUSINGID,
S.WAREHOUSINGNAME,
S.RECEIVEID ,
S.RECEIVENAME,
S.CARPLATEID,
S.CARPLATENAME,
S.ACCOUNTDATE,
S.DELIVERDATE,
S.SYNCSTATUS,
S.SYNCTIME,
S.ISREVOKE,
S.REVOKETIME,
S.ISAMENDS ,
S.AMENDSTIME,
S.CREATETIME,
S.UPDATETIME,
S.REMARKS
FROM
TP_PM_SENDOUTGOODSLOG S
WHERE
S.SENDOUTGOODSLOGID = :SENDOUTGOODSLOGID ";
IDataParameter[] paras = new OracleParameter[]
{
new OracleParameter(":SENDOUTGOODSLOGID", OracleDbType.Int32, cre.Properties["SENDOUTGOODSLOGID"], ParameterDirection.Input),
};
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
//明细
sqlString = @"SELECT
--SENDOUTGOODSLOGDETAILID,
--SENDOUTGOODSLOGID,
--FINISHEDLOADBATCHNO,
GOODSCODE,
MATERIALCODE,
MATERIALREMARK,
BINDINGCOUNT
--BANMA
FROM
TP_PM_SENDOUTGOODSLOGDETAIL
WHERE VALUEFLAG = 1
AND SENDOUTGOODSLOGID = :SENDOUTGOODSLOGID ";
DataTable data1 = conn.GetSqlResultToDt(sqlString, paras);
sre.Data.Tables.Add(data1);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 撤销
///
///
///
///
public static ServiceResultEntity KHDRevokeInvoice(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string ementy = cre.Properties["ementy"].ToString();
string userCode = user.UserCode.ToString();
DataTable USERID = conn.GetSqlResultToDt("SELECT PASSWORD FROM TP_MST_USER WHERE USERCODE='" + userCode + "' AND USERID='" + user.UserID + "'");
return PMModuleLogicDAL.RevokeInvoice(ementy, userCode, USERID.Rows[0]["PASSWORD"].ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 删除
///
///
///
///
public static ServiceResultEntity KHDDestroyInvoice(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string ementy = cre.Properties["ementy"].ToString();
string userCode = user.UserCode.ToString();
DataTable USERID = conn.GetSqlResultToDt("SELECT PASSWORD FROM TP_MST_USER WHERE USERCODE='" + userCode + "' AND USERID='" + user.UserID + "'");
return PMModuleLogicDAL.DestroyInvoice(ementy, userCode, USERID.Rows[0]["PASSWORD"].ToString());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 补推
///
///
///
///
public static ServiceResultEntity KHDSupplementaryPush(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string ementy = cre.Properties["ementy"].ToString();
string userCode = user.UserCode.ToString();
DataTable USERID = conn.GetSqlResultToDt("SELECT PASSWORD FROM TP_MST_USER WHERE USERCODE='" + userCode + "' AND USERID='" + user.UserID + "'");
return PMModuleLogicDAL.SupplementaryPush(ementy, userCode);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 产成品交接条码验证
///
///
///
///
public static ServiceResultEntity CHeckFinishOutlabelCode(string barcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
ServiceResultEntity resultEntity = new ServiceResultEntity();
// 如果是板码，不能扫板码？
#region 暂时注释板码
string banMa = string.Empty;
// if (barcode.Length == 8)
// {
// banMa = barcode;
// string sqlBanMa = @"
//SELECT BARCODE,
// FINISHEDLOADBATCHNO
// FROM TP_PM_FINISHEDPRODUCT
// WHERE BANMA = :barcode and FHTIME is not null and SENDOUTFLAG<>1 order by FHTIME desc";
// OracleParameter[] parasBanMa = new OracleParameter[]
// {
// new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input)
// };
// DataTable dtBanMa = oracleConn.GetSqlResultToDt(sqlBanMa, parasBanMa);
// if (dtBanMa.Rows.Count == 0)
// {
// resultEntity.Result = -1;
// resultEntity.Message = "当前载具上无可发货的产品。";
// return resultEntity;
// }
// //DataTable dtFinishedLoadBatchNo = dtBanMa.DefaultView.ToTable(true, new string[] { "FINISHEDLOADBATCHNO" });
// //if (dtFinishedLoadBatchNo.Rows.Count > 1)
// //{
// // resultEntity.Result = -1;
// // resultEntity.Message = "当前载具存在超过两版以上的产品。";
// // return resultEntity;
// //}
// barcode = dtBanMa.Rows[0]["BARCODE"].ToString();
// }
// else//外箱吗
// {
// banMa = barcode;
// string sqlBanMa = @"
// select BANMA,TP_PM_GroutingDailyDetail.GOODSCODE,MATERIALCODE,MATERIALREMARK from TP_PM_FINISHEDPRODUCT left join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.BARCODE=TP_PM_FINISHEDPRODUCT.BARCODE
// WHERE TP_PM_GroutingDailyDetail.outlabelcode = :barcode and FHTIME is not null and SENDOUTFLAG<>1";
// OracleParameter[] parasBanMa = new OracleParameter[]
// {
// new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input)
// };
// DataTable dtBanMa = oracleConn.GetSqlResultToDt(sqlBanMa, parasBanMa);
// if (dtBanMa.Rows.Count == 0)
// {
// resultEntity.Result = -1;
// resultEntity.Message = "当前载具上无可发货的产品。";
// return resultEntity;
// }
// }
#endregion
string FINISHEDLOADBATCHNO = null;
string fhtime = "";
string sql = "";
// 1.判断产品是否在产成品表中
if (barcode.Length == 8)
{
sql = @"select
f.FHUserID,
f.FHUserCode,
f.GoodsID,
f.GoodsCode,
f.GoodsName,
f.BarCode, f.FINISHEDLOADBATCHNO,FHTIME,SENDOUTGOODSLOGID
from TP_PM_FinishedProduct f
where f.BANMA=:BarCode and FHTIME is not null and (SENDOUTGOODSLOGID=0 or SENDOUTGOODSLOGID is null) order by FHTIME desc ";
}
else {
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),
};
DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
barcode = ds.Tables[0].Rows[0][0].ToString();
}
sql = "select f.FHUserCode,f.FINISHEDLOADBATCHNO,FHTIME,SENDOUTGOODSLOGID from TP_PM_FINISHEDPRODUCT f WHERE f.BarCode = :BarCode ";
}
string currentBarcode = string.Empty;
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":BarCode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input),
//new OracleParameter(":Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
};
DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras);
if (dsResult != null && dsResult.Tables[0].Rows.Count > 0)
{
FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString();
fhtime = dsResult.Tables[0].Rows[0]["FHTIME"].ToString();
// 在产成品表中
#region 是否交接过
string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString();
if (dsResult.Tables[0].Rows[0]["SENDOUTGOODSLOGID"] != null && !string.IsNullOrEmpty(dsResult.Tables[0].Rows[0]["SENDOUTGOODSLOGID"].ToString()) && dsResult.Tables[0].Rows[0]["SENDOUTGOODSLOGID"].ToString() != "0")
{
resultEntity.Status = Constant.ServiceResultStatus.ValidationFailed;
resultEntity.Message = "此产品【" + barcode + "】已发货，不能进行该操作";
return resultEntity;
}
if (string.IsNullOrEmpty(fhUserCode))
{
resultEntity.Status = Constant.ServiceResultStatus.DataChanged; //未交接，不能进行
resultEntity.Message = "此产品【" + barcode + "】未交接，不能进行该操作";
return resultEntity;
}
#endregion
}
else
{
#region 条码在废弃产品表中且审批通过，提示：此产品【999999】已被废弃，不能进行该操作。
sql = @"select GoodsCode from tp_pm_inproductiontrash where BarCode=:BarCode";
dsResult = oracleConn.GetSqlResultToDs(sql, paras);
if (dsResult != null && dsResult.Tables[0].Rows.Count > 0)
{
resultEntity.Status = Constant.ServiceResultStatus.NoSearchResults; //产品已经报损
resultEntity.Message = "此产品【" + barcode + "】已被废弃,不能进行该操作";
return resultEntity;
}
#endregion
#region 未交坯
sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode and DeliverTime is null";
dsResult = oracleConn.GetSqlResultToDs(sql, paras);
if (dsResult != null && dsResult.Tables[0].Rows.Count > 0)
{
resultEntity.Status = Constant.ServiceResultStatus.NoModifyData; //产品未交坏
resultEntity.Message = "此产品【" + barcode + "】还未交坯,不能进行该操作";
return resultEntity;
}
else
{
resultEntity.Status = Constant.ServiceResultStatus.SystemError; //无效条码
resultEntity.Message = "此条码【" + barcode + "】为无效条码,不能进行该操作";
return resultEntity;
}
#endregion
}
#region 查询产品相关信息
sql = @" SELECT F.BANMA,G.GOODSMODEL GOODSCODE,GDD.MATERIALCODE,GDD.MATERIALREMARK,F.FINISHEDLOADBATCHNO,COUNT(1) COUNT
FROM TP_PM_FINISHEDPRODUCT F
LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID=F.GROUTINGDAILYDETAILID
LEFT JOIN TP_MST_GOODS G ON G.GOODSID = F.GOODSID WHERE 1=1 ";
if (!string.IsNullOrEmpty(FINISHEDLOADBATCHNO))
{
sql += " AND F.FINISHEDLOADBATCHNO='" + FINISHEDLOADBATCHNO + "'";
}
//if (!string.IsNullOrEmpty(fhtime))
//{
// sql += " and FHTIME=FUN_CMN_GETDATETIME('" + fhtime + "') ";
//}
sql += " GROUP BY F.BANMA,G.GOODSMODEL,GDD.MATERIALCODE,GDD.MATERIALREMARK,F.FINISHEDLOADBATCHNO";
dsResult = oracleConn.GetSqlResultToDs(sql);
if (dsResult != null && dsResult.Tables[0].Rows.Count > 0)
{
resultEntity.Result = dsResult.Tables[0];
resultEntity.Status= Constant.ServiceResultStatus.Success;
}
#endregion
return resultEntity;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
#endregion
#region 首件登记检验
///
/// 查询总单
///
///
///
///
public static ServiceResultEntity GetRegisterData(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @" SELECT F.REGISTERID,F.REGISTERNO
,F.WORKSHOP
,F.GOODSCODE
,L.LOGONAME
,F.MATERIALCODE
,D.DICTIONARYVALUE FIRGOODSTYPE
,F.FIRQUANTITY
,F.BARCODES
,D1.DICTIONARYVALUE FIRBASIS
,to_char( F.ACCOUNTDATE ,'YYYY/MM/DD') ACCOUNTDATE
,F.CREATETIME
,F.UPDATETIME
,U.USERNAME
,U1.USERNAME UPDATEUSER
,F.REMARKS
,CASE WHEN F.ADVICE IS NULL THEN ''
WHEN F.ADVICE = 0 THEN '不合格'
WHEN F.ADVICE = 1 THEN '合格'
ELSE '' END AS ADVICE
,F.ADVICEREMARKS
,CASE WHEN F.STATUS = 0 THEN '未登记'
WHEN F.STATUS = 1 THEN '检测中'
WHEN F.STATUS = 2 THEN '待研发部检验'
WHEN F.STATUS = 3 THEN '待签名'
WHEN F.STATUS = 4 THEN '已完结'
ELSE '' END AS STATUS
,F.STATUS STATUSID
,F.CHECKQUANTITY
FROM TP_PM_FIRSTREGISTER F
LEFT JOIN TP_MST_USER U ON U.USERID = F.CREATEUSERID
LEFT JOIN TP_MST_USER U1 ON U1.USERID = F.UPDATEUSERID
LEFT JOIN TP_MST_DATADICTIONARY D ON D.DICTIONARYID = F.FIRGOODSTYPE
LEFT JOIN TP_MST_DATADICTIONARY D1 ON D1.DICTIONARYID = F.FIRBASIS
LEFT JOIN TP_MST_LOGO L ON L.LOGOID = F.LOGOID
WHERE F.VALUEFLAG = 1
AND F.CREATETIME >= :CREATETIMEBEGIN
AND F.CREATETIME < :CREATETIMEEND";
List parameters = new List();
parameters.Add(new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CREATETIMEBEGIN"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CREATETIMEEND"], ParameterDirection.Input));
// 单号
if (!string.IsNullOrEmpty(cre.Properties["REGISTERNO"].ToString()))
{
sqlString = sqlString + " AND instr(F.REGISTERNO,:REGISTERNO)>0 ";
parameters.Add(new OracleParameter(":REGISTERNO", OracleDbType.NVarchar2, cre.Properties["REGISTERNO"], ParameterDirection.Input));
}
// 车间
if (!string.IsNullOrEmpty(cre.Properties["WORKSHOP"].ToString()))
{
sqlString = sqlString + " AND F.WORKSHOP = :WORKSHOP ";
parameters.Add(new OracleParameter(":WORKSHOP", OracleDbType.Int32, cre.Properties["WORKSHOP"], ParameterDirection.Input));
}
// 产品编码
if (!string.IsNullOrEmpty(cre.Properties["GOODSCODE"].ToString()))
{
sqlString = sqlString + " AND instr(F.GOODSCODE,:GOODSCODE)>0 ";
parameters.Add(new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, cre.Properties["GOODSCODE"], ParameterDirection.Input));
}
// 产品类型
if (!string.IsNullOrEmpty(cre.Properties["FIRGOODSTYPE"].ToString()))
{
sqlString = sqlString + " AND F.FIRGOODSTYPE = :FIRGOODSTYPE ";
parameters.Add(new OracleParameter(":FIRGOODSTYPE", OracleDbType.Int32, cre.Properties["FIRGOODSTYPE"], ParameterDirection.Input));
}
// 检验依据
if (!string.IsNullOrEmpty(cre.Properties["FIRBASIS"].ToString()))
{
sqlString = sqlString + " AND F.FIRBASIS = :FIRBASIS ";
parameters.Add(new OracleParameter(":FIRBASIS", OracleDbType.Int32, cre.Properties["FIRBASIS"], ParameterDirection.Input));
}
//状态
if (!string.IsNullOrEmpty(cre.Properties["STATUS"].ToString()))
{
sqlString = sqlString + " AND F.STATUS = :STATUS ";
parameters.Add(new OracleParameter(":STATUS", OracleDbType.Int32, cre.Properties["STATUS"], ParameterDirection.Input));
}
DataTable dt = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(dt);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 查询条码及签名明细
///
///
///
///
public static ServiceResultEntity GetRegisterDetail(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
//明细
string sqlString = @" SELECT F.BARID,
F.BARCODE
FROM TP_PM_FIRSTREGISTERBAR F
WHERE F.REGISTERID = :REGISTERID ";
//string sqlString = @" SELECT F.REGISTERID,
// F.DETAILID,
// F.ITEMSYTPE,
// F.ITEMSNAME,
// F.ITEMSINFO ,
// F.ITEMSVALUE,
// F.ITEMSREMARKS
// FROM TP_PM_FIRSTREGISTERDETAIL F
// WHERE F.REGISTERID = :REGISTERID
// AND F.VALUEFLAG =1 ";
List parameters = new List();
parameters.Add(new OracleParameter(":REGISTERID", OracleDbType.Int32, cre.Properties["REGISTERID"], ParameterDirection.Input));
DataTable dt = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(dt);
//三个签名
sqlString = @" SELECT F.PGSIGNATURE,
F.CJSIGNATURE,
F.SKSIGNATURE
FROM TP_PM_FIRSTREGISTER F
WHERE F.REGISTERID = :REGISTERID
AND F.VALUEFLAG =1 ";
//sqlString = @" SELECT F.IMAGE PGSIGNATURE
// FROM TP_MST_GOODSFILENAME F ORDER BY F.CREATETIME DESC
//";
DataTable dt1 = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
sre.Data.Tables.Add(dt1);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 查询项目明细
///
///
///
///
public static ServiceResultEntity GetItemDetail(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
//明细
string sqlString = @" SELECT F.REGISTERID,
F.DETAILID,
F.ITEMSYTPE,
F.ITEMSNAME,
F.ITEMSINFO ,
CASE WHEN F.ITEMSVALUE = 0 THEN '不合格'
WHEN F.ITEMSVALUE = 1 THEN '合格'
ELSE '' END AS ITEMSVALUE,
F.ITEMSREMARKS,
U.USERCODE CREATEUSERCODE,
U.USERNAME CREATEUSERNAME,
U1.USERNAME UPDATEUSERNAME
FROM TP_PM_FIRSTREGISTERDETAIL F
LEFT JOIN TP_MST_USER U ON F.CREATEUSERID = U.USERID
LEFT JOIN TP_MST_USER U1 ON F.UPDATEUSERID = U1.USERID
WHERE F.REGISTERID = :REGISTERID
AND F.BARID = :BARID
AND F.VALUEFLAG =1
ORDER BY F.ITEMSID";
List parameters = new List();
parameters.Add(new OracleParameter(":REGISTERID", OracleDbType.Int32, cre.Properties["REGISTERID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":BARID", OracleDbType.Int32, cre.Properties["BARID"], ParameterDirection.Input));
DataTable dt = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(dt);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 查询物料编码
///
///
///
///
public static ServiceResultEntity GetMatnr(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
string sqlString = @" SELECT
MATERIALCODE
FROM
(
SELECT SAP.MATERIALCODE FROM TP_MST_GOODSLOGOSAP SAP
WHERE
SAP.GOODSCODE = :GOODSCODE
AND SAP.LOGOID = :LOGOID
UNION
SELECT G.MATERIALCODE FROM TP_MST_GOODS G
WHERE
G.GOODSCODE = :GOODSCODE
AND G.LOGOID = :LOGOID
)
WHERE
ROWNUM < 2 ";
IDataParameter[] paras = new OracleParameter[]
{
new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, cre.Properties["GOODSCODE"], ParameterDirection.Input),
new OracleParameter(":LOGOID", OracleDbType.Int32, cre.Properties["LOGOID"], ParameterDirection.Input),
};
DataTable data = conn.GetSqlResultToDt(sqlString, paras);
ServiceResultEntity sre = new ServiceResultEntity();
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();
}
}
}
///
/// 编辑查询数据
///
///
///
///
public static ServiceResultEntity GetInfo(SUserInfo user, ClientRequestEntity cre)
{
IDBConnection conn = null;
try
{
conn = ClsDbFactory.CreateDBConnection(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
conn.Open();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = @" SELECT F.REGISTERID,
F.REGISTERNO
,F.WORKSHOP
,F.GOODSID
,F.GOODSCODE
,G.GOODSNAME
,F.LOGOID
,F.MATERIALCODE
,F.FIRGOODSTYPE
,F.FIRQUANTITY
,F.BARCODES
,F.FIRBASIS
,F.ACCOUNTDATE
,F.REMARKS
,F.STATUS
FROM TP_PM_FIRSTREGISTER F
LEFT JOIN TP_MST_GOODS G ON G.GOODSID = F.GOODSID
WHERE F.VALUEFLAG = 1
AND F.REGISTERID = :REGISTERID ";
List parameters = new List();
parameters.Add(new OracleParameter(":REGISTERID", OracleDbType.Int32, cre.Properties["REGISTERID"], ParameterDirection.Input));
DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
if (cre.Properties.ContainsKey("BARID"))
{
//明细
sqlString = @"SELECT F.REGISTERID,
F.DETAILID,
F.ITEMSYTPE,
F.ITEMSNAME,
F.ITEMSINFO ,
F.ITEMSVALUE itemsValueID,
F.ITEMSREMARKS
FROM TP_PM_FIRSTREGISTERDETAIL F
WHERE F.REGISTERID = :REGISTERID
AND F.BARID = :BARID
AND F.VALUEFLAG =1 ";
}
else
{
//明细
sqlString = @"SELECT F.REGISTERID,
F.DETAILID,
F.ITEMSYTPE,
F.ITEMSNAME,
F.ITEMSINFO ,
F.ITEMSVALUE itemsValueID,
F.ITEMSREMARKS
FROM TP_PM_FIRSTREGISTERDETAIL F
WHERE 1=0 ";
}
parameters.Add(new OracleParameter(":BARID", OracleDbType.Int32, cre.Properties["BARID"], ParameterDirection.Input));
DataTable data1 = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
sre.Data.Tables.Add(data1);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Close();
}
}
}
///
/// 保存
///
/// 登录用户信息
/// 查询条件
/// 查询结果
public static ServiceResultEntity SaveFirstRegister(SUserInfo user, ClientRequestEntity cre)
{
IDBTransaction conn = null;
ServiceResultEntity sre = new ServiceResultEntity();
try
{
conn = ClsDbFactory.CreateDBTransaction(Basics.DataAccess.DataBaseType.ORACLE, DataManager.ConnectionString);
int result = 0;
if ( cre.Properties.ContainsKey("REGISTERID") && Convert.ToInt32(cre.Properties["REGISTERID"]) > 0)
{
//编辑
string sqlString = @" SELECT F.STATUS
FROM TP_PM_FIRSTREGISTER F
WHERE F.VALUEFLAG = 1
AND F.REGISTERID = :REGISTERID ";
List parameters = new List();
parameters.Add(new OracleParameter(":REGISTERID", OracleDbType.Int32, cre.Properties["REGISTERID"], ParameterDirection.Input));
DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
if (data != null && data.Rows.Count >0)
{
if (Convert.ToInt32(data.Rows[0]["STATUS"]) == 0)
{
//可编辑总单，没有明细
string updateInsert = @" UPDATE TP_PM_FIRSTREGISTER SET
WORKSHOP = :WORKSHOP,
GOODSID = :GOODSID,
GOODSCODE = :GOODSCODE,
LOGOID = :LOGOID,
MATERIALCODE = :MATERIALCODE,
FIRGOODSTYPE = :FIRGOODSTYPE,
FIRQUANTITY = :FIRQUANTITY,
BARCODES = :BARCODES,
FIRBASIS = :FIRBASIS,
REMARKS = :REMARKS,
ACCOUNTDATE = :ACCOUNTDATE,
UPDATETIME= SYSDATE,
UPDATEUSERID= :UPDATEUSERID
WHERE REGISTERID = :REGISTERID AND VALUEFLAG =1 ";
IDataParameter[] insertParas = new OracleParameter[]
{
new OracleParameter(":REGISTERID", OracleDbType.Int32,cre.Properties["REGISTERID"], ParameterDirection.Input),
new OracleParameter(":WORKSHOP", OracleDbType.Int32,cre.Properties["WORKSHOP"], ParameterDirection.Input),
new OracleParameter(":GOODSID", OracleDbType.Int32,cre.Properties["GOODSID"], ParameterDirection.Input),
new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,cre.Properties["GOODSCODE"], ParameterDirection.Input),
new OracleParameter(":LOGOID", OracleDbType.Int32,cre.Properties["LOGOID"], ParameterDirection.Input),
new OracleParameter(":MATERIALCODE", OracleDbType.NVarchar2,cre.Properties["MATERIALCODE"], ParameterDirection.Input),
new OracleParameter(":FIRGOODSTYPE", OracleDbType.Int32,cre.Properties["FIRGOODSTYPE"], ParameterDirection.Input),
new OracleParameter(":FIRQUANTITY", OracleDbType.Int32,cre.Properties["FIRQUANTITY"], ParameterDirection.Input),
new OracleParameter(":BARCODES", OracleDbType.NVarchar2,cre.Properties["BARCODES"], ParameterDirection.Input),
new OracleParameter(":FIRBASIS", OracleDbType.Int32,cre.Properties["FIRBASIS"], ParameterDirection.Input),
new OracleParameter(":ACCOUNTDATE", OracleDbType.Date,cre.Properties["ACCOUNTDATE"], ParameterDirection.Input),
new OracleParameter(":UPDATEUSERID", OracleDbType.Int32,user.UserID, ParameterDirection.Input),
new OracleParameter(":REMARKS", OracleDbType.NVarchar2,cre.Properties["REMARKS"], ParameterDirection.Input),
};
result += conn.ExecuteNonQuery(updateInsert, insertParas);
}
else
{
//只能编辑明细中的备注及部分值
if (cre.Properties.ContainsKey("BARID") && Convert.ToInt32(cre.Properties["BARID"]) > 0)
{
if (cre.Data.Tables.Count > 0)
{
DataTable detailTable = cre.Data.Tables[0];
for (int i = 0; i < detailTable.Rows.Count; i++)
{
string updatesql = "";
if (detailTable.Rows[i]["ITEMSNAME"].ToString() == "综合判定")
{
updatesql = @" UPDATE TP_PM_FIRSTREGISTERDETAIL SET
ITEMSREMARKS = :ITEMSREMARKS,
UPDATETIME= SYSDATE,
UPDATEUSERID= :UPDATEUSERID
WHERE REGISTERID = :REGISTERID AND DETAILID = :DETAILID AND BARID = :BARID";
IDataParameter[] updateParas = new OracleParameter[]
{
new OracleParameter(":REGISTERID", OracleDbType.Int32,cre.Properties["REGISTERID"], ParameterDirection.Input),
new OracleParameter(":BARID", OracleDbType.Int32,cre.Properties["BARID"], ParameterDirection.Input),
new OracleParameter(":DETAILID", OracleDbType.Int32,Convert.ToInt32(detailTable.Rows[i]["DETAILID"]), ParameterDirection.Input),
new OracleParameter(":ITEMSREMARKS", OracleDbType.NVarchar2,detailTable.Rows[i]["ITEMSREMARKS"].ToString(), ParameterDirection.Input),
new OracleParameter(":UPDATEUSERID", OracleDbType.Int32,user.UserID, ParameterDirection.Input),
};
result += conn.ExecuteNonQuery(updatesql, updateParas);
}
else
{
updatesql = @" UPDATE TP_PM_FIRSTREGISTERDETAIL SET
ITEMSVALUE = :ITEMSVALUE,
ITEMSREMARKS = :ITEMSREMARKS,
UPDATETIME= SYSDATE,
UPDATEUSERID= :UPDATEUSERID
WHERE REGISTERID = :REGISTERID AND DETAILID = :DETAILID AND BARID = :BARID";
IDataParameter[] updateParas = new OracleParameter[]
{
new OracleParameter(":REGISTERID", OracleDbType.Int32,cre.Properties["REGISTERID"], ParameterDirection.Input),
new OracleParameter(":BARID", OracleDbType.Int32,cre.Properties["BARID"], ParameterDirection.Input),
new OracleParameter(":DETAILID", OracleDbType.Int32,Convert.ToInt32(detailTable.Rows[i]["DETAILID"]), ParameterDirection.Input),
new OracleParameter(":ITEMSVALUE", OracleDbType.Int32,Convert.ToInt32(detailTable.Rows[i]["itemsValueID"]), ParameterDirection.Input),
new OracleParameter(":ITEMSREMARKS", OracleDbType.NVarchar2,detailTable.Rows[i]["ITEMSREMARKS"].ToString(), ParameterDirection.Input),
new OracleParameter(":UPDATEUSERID", OracleDbType.Int32,user.UserID, ParameterDirection.Input),
};
result += conn.ExecuteNonQuery(updatesql, updateParas);
}
}
}
}
}
}
}
else
{
//判断系统中是否存在未完结的该产品及商标的登记单，如果有，不能保存；
string sqlString = @" SELECT 1
FROM TP_PM_FIRSTREGISTER F
WHERE F.VALUEFLAG = 1
AND F.STATUS <> 4
AND F.GOODSID = :GOODSID
AND F.LOGOID = :LOGOID ";
List parameters = new List();
parameters.Add(new OracleParameter(":GOODSID", OracleDbType.Int32, cre.Properties["GOODSID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LOGOID", OracleDbType.Int32, cre.Properties["LOGOID"], ParameterDirection.Input));
DataTable data = conn.GetSqlResultToDt(sqlString, parameters.ToArray());
if (data != null && data.Rows.Count > 0)
{
sre.Result = -1;
sre.Message = "保存失败，系统中已存在该产品及商标的未完结登记单！";
return sre;
}
//新建
string sqlInsert = "INSERT INTO TP_PM_FIRSTREGISTER\n" +
" (REGISTERNO\n" +
" ,WORKSHOP\n" +
" ,GOODSID\n" +
" ,GOODSCODE\n" +
" ,LOGOID\n" +
" ,MATERIALCODE\n" +
" ,FIRGOODSTYPE\n" +
" ,FIRQUANTITY\n" +
" ,BARCODES\n" +
" ,FIRBASIS\n" +
" ,ACCOUNTDATE\n" +
" ,ACCOUNTID\n" +
" ,VALUEFLAG\n" +
" ,CREATETIME\n" +
" ,CREATEUSERID\n" +
" ,UPDATETIME\n" +
" ,UPDATEUSERID\n" +
" ,REMARKS)\n" +
"VALUES\n" +
" (:REGISTERNO\n" +
" ,:WORKSHOP\n" +
" ,:GOODSID\n" +
" ,:GOODSCODE\n" +
" ,:LOGOID\n" +
" ,:MATERIALCODE\n" +
" ,:FIRGOODSTYPE\n" +
" ,:FIRQUANTITY\n" +
" ,:BARCODES\n" +
" ,:FIRBASIS\n" +
" ,:ACCOUNTDATE\n" +
" ,:ACCOUNTID\n" +
" ,1\n" +
" ,SYSDATE\n" +
" ,:CREATEUSERID\n" +
" ,SYSDATE\n" +
" ,:CREATEUSERID\n" +
" ,:REMARKS)";
IDataParameter[] insertParas = new OracleParameter[]
{
new OracleParameter(":REGISTERNO", OracleDbType.NVarchar2,cre.Properties["REGISTERNO"], ParameterDirection.Input),
new OracleParameter(":WORKSHOP", OracleDbType.Int32,cre.Properties["WORKSHOP"], ParameterDirection.Input),
new OracleParameter(":GOODSID", OracleDbType.Int32,cre.Properties["GOODSID"], ParameterDirection.Input),
new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2,cre.Properties["GOODSCODE"], ParameterDirection.Input),
new OracleParameter(":LOGOID", OracleDbType.Int32,cre.Properties["LOGOID"], ParameterDirection.Input),
new OracleParameter(":MATERIALCODE", OracleDbType.NVarchar2,cre.Properties["MATERIALCODE"], ParameterDirection.Input),
new OracleParameter(":FIRGOODSTYPE", OracleDbType.Int32,cre.Properties["FIRGOODSTYPE"], ParameterDirection.Input),
new OracleParameter(":FIRQUANTITY", OracleDbType.Int32,cre.Properties["FIRQUANTITY"], ParameterDirection.Input),
new OracleParameter(":BARCODES", OracleDbType.NVarchar2,cre.Properties["BARCODES"], ParameterDirection.Input),
new OracleParameter(":FIRBASIS", OracleDbType.Int32,cre.Properties["FIRBASIS"], ParameterDirection.Input),
new OracleParameter(":ACCOUNTDATE", OracleDbType.Date,cre.Properties["ACCOUNTDATE"], ParameterDirection.Input),
new OracleParameter(":ACCOUNTID", OracleDbType.Int32,user.AccountID, ParameterDirection.Input),
new OracleParameter(":CREATEUSERID", OracleDbType.Int32,user.UserID, ParameterDirection.Input),
new OracleParameter(":REMARKS", OracleDbType.NVarchar2,cre.Properties["REMARKS"], ParameterDirection.Input),
};
result += conn.ExecuteNonQuery(sqlInsert, insertParas);
}
sre.Result = result;
conn.Commit();
return new ServiceResultEntity();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null &&
conn.ConnState == ConnectionState.Open)
{
conn.Disconnect();
}
}
}
#endregion
}
}