/*******************************************************************************
* Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
* 类的信息：
* 1.程序名称：SystemModuleLogic.cs
* 2.功能描述：系统管理数据查询处理
* 编辑履历：
* 作者 日期 版本 修改内容
* 张国印 2014/09/12 1.00 新建
*******************************************************************************/
using System;
using System.Data;
using System.Linq;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
namespace Dongke.IBOSS.PRD.Service.SystemModuleLogic
{
///
/// 系统管理数据查询处理
///
public partial class SystemModuleLogic
{
#region 用户管理
///
/// 用户登录
///
/// 用户请示对象
/// 0：PC端；2：移动端
/// DataSet
public static DataSet DoLoginRefresh(LoginRequestEntity requestEntity, string actionType)
{
LoginResultEntity resultEntity = new LoginResultEntity();
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
// 读取License文件数据
DataSet customer = new DataSet();
//int readLock = LockLicenseHandle.ReadLisenceFile(requestEntity.LisenceFilePath, ref customer);
#region 形成需要执行的SQL语句
requestEntity.SessionKey = Guid.NewGuid().ToString();
string strSql = "PRO_SYS_DoLogin";
OracleParameter[] paras = new OracleParameter[]
{
new OracleParameter("in_AccountCode",requestEntity.AccountCode),
new OracleParameter("in_UserCode",requestEntity.UserCode),
new OracleParameter("in_UserPassword",requestEntity.Password),
new OracleParameter("in_MAC",requestEntity.MACAddress),
new OracleParameter("in_SessionKey",requestEntity.SessionKey),
new OracleParameter("in_ComputerName",requestEntity.ComputerName),
new OracleParameter("in_IP",requestEntity.IPAddress),
new OracleParameter("in_IMEI",requestEntity.IMEI),
new OracleParameter("in_LoginType",actionType),
new OracleParameter("out_LoginStatus",OracleDbType.Int16, ParameterDirection.Output),
new OracleParameter("out_UserOrganization",OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("out_Navigation",OracleDbType.RefCursor, ParameterDirection.Output), //用户导航菜单
new OracleParameter("out_UserRightData",OracleDbType.RefCursor, ParameterDirection.Output), //用户功能权限
new OracleParameter("out_UserRangeRightData",OracleDbType.RefCursor, ParameterDirection.Output), //用户范围权限
new OracleParameter("out_Function",OracleDbType.RefCursor, ParameterDirection.Output) //系统功能菜单
};
#endregion
oracleConn.Open();
//int loginStatus = 0;
DataSet returnDataSet = oracleConn.ExecStoredProcedure(strSql, paras);
oracleConn.Close();
return returnDataSet;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取用户列表
///
/// 用户实体
/// 用户基本信息
/// DataSet数据集
public static DataSet SearchUserData(SUserEntity requestEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",sUserInfo.AccountID),
new OracleParameter("in_userID",sUserInfo.UserID),
new OracleParameter("in_userCode",requestEntity.UserCode),
new OracleParameter("in_userName",requestEntity.UserName),
new OracleParameter("in_organizationID",requestEntity.OrganizationID),
new OracleParameter("in_remarks",requestEntity.Remarks),
new OracleParameter("in_limitMAC",requestEntity.LimitMAC),
new OracleParameter("in_isWorker",requestEntity.IsWorker),
new OracleParameter("rs_result",OracleDbType.RefCursor),
new OracleParameter("in_ValueFlag",requestEntity.IsValueFlag),
new OracleParameter("in_IsLocked",requestEntity.IsLocked),
};
paras[8].Direction = ParameterDirection.Output;
DataSet ds = con.ExecStoredProcedure("PRO_MST_SearchUser", paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 是否存在重复用户编码
///
/// 用户编码
/// 用户基本信息
/// DataSet
public static DataSet IsExistsUserCode(string UserCode, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",sUserInfo.AccountID),
new OracleParameter("in_userID",sUserInfo.UserID),
new OracleParameter("in_userCode",UserCode),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
paras[3].Direction = ParameterDirection.Output;
DataSet ds = con.ExecStoredProcedure("PRO_MST_GetUserCode", paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 是否存在重复生产编码
///
/// 生产编码
/// 用户基本信息
/// DataSet
public static DataSet IsExistWorkCode(string ProductionWorkCode, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",sUserInfo.AccountID),
new OracleParameter("in_userID",sUserInfo.UserID),
new OracleParameter("in_workrCode",ProductionWorkCode),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
paras[3].Direction = ParameterDirection.Output;
DataSet ds = con.ExecStoredProcedure("PRO_MST_GetWorkCode", paras);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取用户信息
///
/// 用户ID
/// DataSet用户信息集合
public static DataSet GetUserRowData(int UserID)
{
using (OracleConnection oracleConnection = new OracleConnection(DataManager.ConnectionString))
{
// 打开数据库连接
oracleConnection.Open();
// 为执行命令对象赋值
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
try
{
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PRO_MST_GetUserRowData";
IDataParameter[] dd = new OracleParameter[]{
new OracleParameter("in_userID",UserID),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
oracleCommand.Parameters.AddRange(dd);
oracleCommand.Parameters[1].Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(oracleCommand);
DataSet ds = new DataSet();
da.Fill(ds);
// 清除sql执行对象的属性值
oracleCommand.Parameters.Clear();
oracleCommand.Dispose();
//获取工号工种关联
OracleCommand oracleCommandJobs = new OracleCommand();
oracleCommandJobs.CommandType = CommandType.Text;
oracleCommandJobs.CommandText = @" Select uj.*,j.JobsName,j.JobsCode,j.Remarks from TP_MST_UserJobs uj
Inner join TP_MST_Jobs j On uj.JobsID = j.JobsID where uj.UserID=" + UserID;
oracleCommandJobs.Connection = oracleConnection;
OracleDataAdapter daJobs = new OracleDataAdapter(oracleCommandJobs);
DataSet dsJobs = new DataSet();
daJobs.Fill(dsJobs);
// 清除sql执行对象的属性值
oracleCommandJobs.Dispose();
if (dsJobs.Tables.Count != 0)
{
DataTable dtJobs = dsJobs.Tables[0].Copy();
dtJobs.TableName = "dtJobs";
ds.Tables.Add(dtJobs);
}
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 释放资源
if (oracleConnection.State != ConnectionState.Closed && oracleConnection != null)
{
oracleConnection.Close();
}
}
}
}
///
/// 根据工号ID获取该工号的工种关联
///
/// 用户ID
/// DataSet
public static DataSet GetJobByUserId(int UserId)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"Select JobsID as UserJobsID,JobsCode as UserJobsCode,JobsName as UserJobsName
from TP_MST_Jobs where JobsID in
(Select JobsID From TP_MST_UserJobs where UserID=:UserID) ");
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":UserID",UserId)
};
DataSet dsStaff = oracleConn.GetSqlResultToDs(sbSql.ToString(), paras);
return dsStaff;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 根据工号ID获取相关的员工及对应工号工种信息
///
/// 用户ID
/// DataSet
public static DataSet GetUserStaffByUserId(int UserId)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.Append( "SELECT USF.USERSTAFFID AS INFOID,\n" +
" USF.USERID,\n" +
" USF.UJOBSID AS USERJOBSID,\n" +
" USF.STAFFID,\n" +
" USF.OPTIMESTAMP,\n" +
" J.JOBSNAME AS USERJOBSNAME,\n" +
" J.JOBSCODE AS USERJOBSCODE,\n" +
" S.STAFFCODE,\n" +
" S.STAFFNAME,\n" +
" SJ.JOBSNAME,\n" +
" SS.STAFFSTATUSNAME\n" +
" FROM TP_MST_USERSTAFF USF\n" +
" INNER JOIN TP_MST_JOBS J\n" +
" ON USF.UJOBSID = J.JOBSID\n" +
" INNER JOIN TP_HR_STAFF S\n" +
" ON USF.STAFFID = S.STAFFID\n" +
" INNER JOIN TP_MST_JOBS SJ\n" +
" ON S.JOBS = SJ.JOBSID\n" +
" INNER JOIN TP_SYS_STAFFSTATUS SS\n" +
" ON S.STAFFSTATUS = SS.STAFFSTATUSID\n" +
" WHERE USERID = :USERID");
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":USERID",UserId)
};
DataSet dsStaff = oracleConn.GetSqlResultToDs(sbSql.ToString(), paras);
return dsStaff;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 根据工号ID获取员工
///
/// 用户ID
/// DataSet
public static DataSet GetStaffByUserId(int UserId)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"Select * from TP_HR_Staff where StaffID in
(Select StaffID From TP_MST_UserStaff where UserID=:UserID)
And ValueFlag = 1 And (StaffStatus = 1 or StaffStatus = 2) ");
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":UserID",UserId)
};
DataSet dsStaff = oracleConn.GetSqlResultToDs(sbSql.ToString(), paras);
return dsStaff;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
finally
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取帐套信息
///
/// 是否停用
/// DataSet帐套信息
public static DataSet GetAccountInfo(bool pValue)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strValueFlag = "1";
if (pValue)
{
strValueFlag = "0";
}
#region 对应要执行的SQL语句
string strSql = "Select * From TP_MST_Account Where valueflag = :valueflag ORDER BY ValueFlag DESC,AccountCode";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras1 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",strValueFlag)
};
#endregion
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(strSql, paras1);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取全部帐套信息
///
/// DataSet帐套信息
public static DataSet GetAllAccountInfo()
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strSql = "Select * From TP_MST_Account ORDER BY ValueFlag DESC,AccountCode";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(strSql, null);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据UserCode判断是否存在生产用户
///
/// 用户实体
/// 用户基本信息
/// DataSet数据集
public static DataSet GetUserIsWorker(SUserEntity requestEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = "select * from tp_mst_user where AccountID=:AccountID and UserCode=:UserCode and IsWorker=1";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":UserCode",requestEntity.UserCode),
};
DataSet dsUserIsWorker = con.GetSqlResultToDs(sqlString, paras);
return dsUserIsWorker;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取用户范围权限
///
/// 权限类别
/// 用户ID
/// DataSet
public static DataSet GetUserPurview(int PurviewType, int UserID)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = "Select PurviewID from TP_MST_UserPurview where PurviewType= :PurviewType and UserID = :UserID";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":PurviewType",PurviewType),
new OracleParameter(":UserID",UserID),
};
DataSet dsUserPurview = con.GetSqlResultToDs(sqlString, paras);
return dsUserPurview;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取工号下的所有工种信息
///
/// 工号ID
/// 用户基本信息
/// DataSet数据集
public static DataSet GetAllJobsByUserID(int UserID, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
string sqlString = @"select
TP_MST_UserJobs.Userid,
TP_MST_Jobs.JobsID as Jobs,
TP_MST_Jobs.JobsName,
TP_MST_Jobs.JobsCode
from TP_MST_UserJobs
left join TP_MST_Jobs
on TP_MST_UserJobs.JobsID=TP_MST_Jobs.JobsID
where TP_MST_UserJobs.Userid=:UserId and TP_MST_Jobs.Accountid=:Accountid
";
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter(":Accountid",sUserInfo.AccountID),
new OracleParameter(":UserId",UserID),
};
DataSet dsUserIsWorker = con.GetSqlResultToDs(sqlString, paras);
return dsUserIsWorker;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
#endregion
#region 获得组织机构记录
///
/// 获得组织机构记录
///
/// 用户基本信息
/// 停用标识
/// 只显示叶子节点标识0：全显示；1：显示叶子节点
/// DataTable
///
/// 陈冰 2014.09.01 新建
///
public static DataTable GetOrganization(SUserInfo sUserInfo, int valueFlag, int leafFlag)
{
using (OracleConnection oracleConnection = new OracleConnection(DataManager.ConnectionString))
{
// 打开数据库连接
oracleConnection.Open();
// 为执行命令对象赋值
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
try
{
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PRO_MST_GetOrganization";
IDataParameter[] dd = new OracleParameter[]{
new OracleParameter("accountID",sUserInfo.AccountID),
new OracleParameter("valueFlag",valueFlag),
new OracleParameter("leafFlag",leafFlag),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
oracleCommand.Parameters.AddRange(dd);
oracleCommand.Parameters[3].Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(oracleCommand);
DataSet ds = new DataSet();
da.Fill(ds);
// 清除sql执行对象的属性值
oracleCommand.Parameters.Clear();
oracleCommand.Dispose();
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 释放资源
if (oracleConnection.State != ConnectionState.Closed && oracleConnection != null)
{
oracleConnection.Close();
}
}
}
}
///
/// 查询组织机构一览
///
/// 组织机构实体类
/// DataSet
public DataSet SearchOrganizationData(OrganizationEntity requestEntity)
{
using (OracleConnection oracleConnection = new OracleConnection(DataManager.ConnectionString))
{
// 打开数据库连接
oracleConnection.Open();
// 为执行命令对象赋值
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
try
{
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PRO_MST_SearchOrganization";
string sqlStringValueFlags = "";
// 正常标识
if (requestEntity.ValueFlags != null
&& requestEntity.ValueFlags.Length == 1)
{
for (int i = 0; i < requestEntity.ValueFlags.Length; i++)
{
if (i == Constant.INT_IS_ZERO)
{
sqlStringValueFlags += requestEntity.ValueFlags[i].ToString();
}
else
{
sqlStringValueFlags += requestEntity.ValueFlags[i].ToString();
}
}
}
IDataParameter[] dd = new OracleParameter[]{
new OracleParameter("in_accountID",requestEntity.in_AccountID),
new OracleParameter("in_userID",requestEntity.in_UserID),
new OracleParameter("in_operateOrganization",requestEntity.OperateOrganization?1:0),
new OracleParameter("in_isLimitAccountOrganization",requestEntity.IsLimitAccountOrganization?1:0),
new OracleParameter("in_isOnlyDisplayEnd",requestEntity.IsOnlyDisplayEnd?1:0),
new OracleParameter("in_organizationID",requestEntity.OrganizationID),
new OracleParameter("in_organizationCode",requestEntity.OrganizationCode),
new OracleParameter("in_organizationName",requestEntity.OrganizationName),
new OracleParameter("in_leader",requestEntity.Leader),
new OracleParameter("in_letterMarket",requestEntity.LetterMarket),
new OracleParameter("in_telephone",requestEntity.Telephone),
new OracleParameter("in_address",requestEntity.Address),
new OracleParameter("in_remarks",requestEntity.Remarks),
new OracleParameter("in_valueFlags",sqlStringValueFlags),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
oracleCommand.Parameters.AddRange(dd);
oracleCommand.Parameters[14].Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(oracleCommand);
DataSet ds = new DataSet();
da.Fill(ds);
// 清除sql执行对象的属性值
oracleCommand.Parameters.Clear();
oracleCommand.Dispose();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 释放资源
if (oracleConnection.State != ConnectionState.Closed && oracleConnection != null)
{
oracleConnection.Close();
}
}
}
}
///
/// 获取组织机构行数据
///
/// 组织机构ID
/// DataSet
public DataSet GetOrganizationRowData(int OrganizationID)
{
using (OracleConnection oracleConnection = new OracleConnection(DataManager.ConnectionString))
{
// 打开数据库连接
oracleConnection.Open();
// 为执行命令对象赋值
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
try
{
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PRO_MST_GetOrganizationRowData";
IDataParameter[] dd = new OracleParameter[]{
new OracleParameter("in_organizationID",OrganizationID),
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
oracleCommand.Parameters.AddRange(dd);
oracleCommand.Parameters[1].Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(oracleCommand);
DataSet ds = new DataSet();
da.Fill(ds);
// 清除sql执行对象的属性值
oracleCommand.Parameters.Clear();
oracleCommand.Dispose();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 释放资源
if (oracleConnection.State != ConnectionState.Closed && oracleConnection != null)
{
oracleConnection.Close();
}
}
}
}
#endregion
#region 获得工序模型类别
///
/// 获得工序模型类别
///
/// DataTable
///
/// 陈冰 2014.09.01 新建
///
public DataTable GetSYSProcessModelType()
{
using (OracleConnection oracleConnection = new OracleConnection(DataManager.ConnectionString))
{
// 打开数据库连接
oracleConnection.Open();
// 为执行命令对象赋值
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
try
{
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PRO_SYS_GetProcessModelType";
IDataParameter[] paras = new OracleParameter[]{
new OracleParameter("rs_result",OracleDbType.RefCursor),
};
oracleCommand.Parameters.AddRange(paras);
oracleCommand.Parameters[0].Direction = System.Data.ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(oracleCommand);
DataSet ds = new DataSet();
da.Fill(ds);
// 清除sql执行对象的属性值
oracleCommand.Parameters.Clear();
oracleCommand.Dispose();
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 释放资源
if (oracleConnection.State != ConnectionState.Closed && oracleConnection != null)
{
oracleConnection.Close();
}
}
}
}
#endregion
#region 数据字典和基础数据相关
#region SearchBox控件数据源
///
/// 获取产品档案的数据
///
///
///
///
public static ServiceResultEntity GetGoods(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string goodsCode = cre.Properties["GoodsCode"] + "";
string goodsName = cre.Properties["GoodsName"] + "";
string sqlString = " SELECT 0 AS SEL, GOODSID, GOODSCODE, GOODSNAME, GOODSSPECIFICATION\n" +
" FROM TP_MST_GOODS\n" +
" WHERE VALUEFLAG = '1'\n" +
" AND CEASEFLAG = '1'\n" +
" AND ACCOUNTID = :accountID\n";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 产品编码
if (!string.IsNullOrEmpty(goodsCode))
{
sqlString += " AND GOODSCODE LIKE :goodsCode\n";
parameters.Add(new OracleParameter(":goodsCode", OracleDbType.Varchar2, "%" + goodsCode + "%", ParameterDirection.Input));
}
// 产品名称
if (!string.IsNullOrEmpty(goodsName))
{
sqlString += " AND GOODSNAME LIKE :goodsName\n";
parameters.Add(new OracleParameter(":goodsName", OracleDbType.Varchar2, "%" + goodsName + "%", ParameterDirection.Input));
}
sqlString += " ORDER BY GOODSCODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取产品类别的数据
///
///
///
///
public static ServiceResultEntity GetGoodsType(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string goodsTypeCode = cre.Properties["GoodsTypeCode"] + "";
string goodsTypeName = cre.Properties["GoodsTypeName"] + "";
string IsLeafNode = cre.Properties["IsLeafNode"] + "";
string sqlString = "SELECT 0 AS SEL,\n" +
" GOODSTYPEID,\n" +
" GOODSTYPECODE,\n" +
" GOODSTYPENAME,\n" +
" GOODSTYPEFULLNAME\n" +
" FROM TP_MST_GOODSTYPE\n" +
" WHERE VALUEFLAG = 1\n" +
" AND ACCOUNTID = :accountID\n";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 是否只查询子节点
if ("1".Equals(IsLeafNode))
{
sqlString += " AND ISLEAFNODE = 1\n";
}
// 产品类别编码
if (!string.IsNullOrEmpty(goodsTypeCode))
{
sqlString += " AND GOODSTYPECODE LIKE :goodsTypeCode\n";
parameters.Add(new OracleParameter(":goodsTypeCode", OracleDbType.Varchar2, "%" + goodsTypeCode + "%", ParameterDirection.Input));
}
// 产品类别名称
if (!string.IsNullOrEmpty(goodsTypeName))
{
sqlString += " AND GOODSTYPENAME LIKE :goodsTypeName\n";
parameters.Add(new OracleParameter(":goodsTypeName", OracleDbType.Varchar2, "%" + goodsTypeName + "%", ParameterDirection.Input));
}
sqlString += " ORDER BY GOODSTYPECODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取组织机构的数据
///
///
///
///
public static ServiceResultEntity GetOrganization(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string organizationCode = cre.Properties["OrganizationCode"] + "";
string organizationName = cre.Properties["OrganizationName"] + "";
string isLeafNode = cre.Properties["IsLeafNode"] + "";
string sqlString = "SELECT 0 AS SEL,\n" +
" ORGANIZATIONID,\n" +
" ORGANIZATIONCODE,\n" +
" ORGANIZATIONNAME,\n" +
" ORGANIZATIONFULLNAME\n" +
" FROM TP_MST_ORGANIZATION\n" +
" WHERE VALUEFLAG = 1\n" +
" AND ACCOUNTID = :accountID\n";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 是否只查询子节点
if ("1".Equals(isLeafNode))
{
sqlString += " AND ISLEAFNODE = 1\n";
}
// 组织机构编码
if (!string.IsNullOrEmpty(organizationCode))
{
sqlString += " AND ORGANIZATIONCODE LIKE :OrganizationCode\n";
parameters.Add(new OracleParameter(":OrganizationCode", OracleDbType.Varchar2, "%" + organizationCode + "%", ParameterDirection.Input));
}
// 组织机构名称
if (!string.IsNullOrEmpty(organizationName))
{
sqlString += " AND ORGANIZATIONNAME LIKE :OrganizationName\n";
parameters.Add(new OracleParameter(":OrganizationName", OracleDbType.Varchar2, "%" + organizationName + "%", ParameterDirection.Input));
}
sqlString += " ORDER BY ORGANIZATIONCODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取工号的数据
///
///
///
///
public static ServiceResultEntity GetUser(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string UserCode = cre.Properties["UserCode"] + "";
string UserName = cre.Properties["UserName"] + "";
string IsWorker = cre.Properties["IsWorker"] + "";
string sqlString = "SELECT 0 AS SEL, U.USERID, U.USERCODE, U.USERNAME, U.USERNAME||'['|| U.USERCODE||']' USERNAMECode, '['|| U.USERCODE||']'||U.USERNAME USERCodeNAME \n" +
" FROM TP_MST_USER U\n" +
" WHERE U.ACCOUNTID = :accountID\n" +
" AND U.VALUEFLAG = 1\n" +
" AND LOWER(U.USERCODE) <> 'system'\n" +
" AND LOWER(U.USERCODE) <> 'admin'";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 是否只查询子节点
if ("1".Equals(IsWorker))
{
sqlString += " AND U.ISWORKER = 1\n";
}
// 组织机构编码
if (!string.IsNullOrEmpty(UserCode))
{
sqlString += " AND U.USERCODE LIKE :UserCode\n";
parameters.Add(new OracleParameter(":UserCode", OracleDbType.Varchar2, "%" + UserCode + "%", ParameterDirection.Input));
}
// 组织机构名称
if (!string.IsNullOrEmpty(UserName))
{
sqlString += " AND U.USERNAME LIKE :UserName\n";
parameters.Add(new OracleParameter(":UserName", OracleDbType.Varchar2, "%" + UserName + "%", ParameterDirection.Input));
}
sqlString += " ORDER BY USERCODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取成型线类型数据
///
///
///
///
public static ServiceResultEntity GetGMouldType(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string gMouldTypeCode = cre.Properties["GMouldTypeCode"] + "";
string gMouldTypeName = cre.Properties["GMouldTypeName"] + "";
string canManyTimes = cre.Properties["CanManyTimes"] + "";
string sqlString = " SELECT 0 AS SEL, GMOULDTYPEID, GMOULDTYPECODE, GMOULDTYPENAME, CANMANYTIMES\n" +
" FROM TP_MST_GMOULDTYPE\n" +
" WHERE VALUEFLAG = 1\n" +
" AND ACCOUNTID = :accountID";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 是否多次注浆
if ("1".Equals(canManyTimes))
{
sqlString += " AND CANMANYTIMES = 1\n";
}
// 成型线类型编码
if (!string.IsNullOrEmpty(gMouldTypeCode))
{
sqlString += " AND GMOULDTYPECODE LIKE :GMouldTypeCode\n";
parameters.Add(new OracleParameter(":GMouldTypeCode", OracleDbType.Varchar2, "%" + gMouldTypeCode + "%", ParameterDirection.Input));
}
// 成型线类型名称
if (!string.IsNullOrEmpty(gMouldTypeName))
{
sqlString += " AND GMOULDTYPENAME LIKE :GMouldTypeName\n";
parameters.Add(new OracleParameter(":GMouldTypeName", OracleDbType.Varchar2, "%" + gMouldTypeName + "%", ParameterDirection.Input));
}
sqlString += " ORDER BY GMOULDTYPECODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取成型线数据
///
///
///
///
public static ServiceResultEntity GetGroutingLine(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string GroutingLineCode = cre.Properties["GroutingLineCode"] + "";
string GroutingLineName = cre.Properties["GroutingLineName"] + "";
string UserCode = cre.Properties["UserCode"] + "";
string sqlString = "SELECT 0 AS SEL,\n" +
" GROUT.GROUTINGLINEID,\n" +
" GROUT.GROUTINGLINECODE,\n" +
" GROUT.GROUTINGLINENAME,\n" +
" GUSER.USERCODE,\n" +
" STATUS.GMOULDSTATUSNAME,\n" +
" GROUT.CREATETIME\n" +
" FROM TP_PC_GROUTINGLINE GROUT\n" +
" LEFT JOIN TP_MST_USER GUSER\n" +
" ON GROUT.USERID = GUSER.USERID\n" +
" LEFT JOIN TP_SYS_GMOULDSTATUS STATUS\n" +
" ON GROUT.GMOULDSTATUS = STATUS.GMOULDSTATUSID\n" +
" WHERE GROUT.VALUEFLAG = 1\n" +
" AND GROUT.ACCOUNTID = :accountID\n";
List parameters = new List();
parameters.Add(new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
// 成型线编码
if (!string.IsNullOrEmpty(GroutingLineCode))
{
sqlString += " AND GROUT.GroutingLineCode LIKE :GroutingLineCode\n";
parameters.Add(new OracleParameter(":GroutingLineCode", OracleDbType.Varchar2, "%" + GroutingLineCode + "%", ParameterDirection.Input));
}
// 成型线名称
if (!string.IsNullOrEmpty(GroutingLineName))
{
sqlString += " AND GROUT.GroutingLineName LIKE :GroutingLineName\n";
parameters.Add(new OracleParameter(":GroutingLineName", OracleDbType.Varchar2, "%" + GroutingLineName + "%", ParameterDirection.Input));
}
// 成型工号
if (!string.IsNullOrEmpty(UserCode))
{
sqlString += " AND GUSER.UserCode LIKE :UserCode\n";
parameters.Add(new OracleParameter(":UserCode", OracleDbType.Varchar2, "%" + UserCode + "%", ParameterDirection.Input));
}
if (!string.IsNullOrEmpty(cre.Properties["GmouldStatus"]+""))
{
sqlString += " AND GROUT.GMOULDSTATUS IN("+ cre.Properties["GmouldStatus"] + ")\n";
}
sqlString += " ORDER BY GROUT.GROUTINGLINECODE";
sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray());
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
#endregion
///
/// 生产线集合
///
/// 生产线实体类
/// 用户基本信息
/// DataSet
///
/// 宋扬 2014.11.19 新建
///
public static DataSet GetProductionLine(SearchProductionLineEntity productionLineEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_productionLineCode",OracleDbType.Varchar2,productionLineEntity.ProductionLineCode,ParameterDirection.Input),
new OracleParameter("in_productionLineName",OracleDbType.Varchar2,productionLineEntity.ProductionLineName,ParameterDirection.Input),
new OracleParameter("in_productionLineIDS",OracleDbType.Varchar2,productionLineEntity.ProcuteLineIDS,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_PC_GetProductionLin", paras);
return dsSearchReport;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获得产品类别
///
/// 用户基本信息
/// 停用标记
/// 是否只查询叶子节点 0：否；1：是
/// DataTable
///
/// 陈冰 2014.09.02 新建
///
public static DataTable GetGoodsType(SUserInfo sUserInfo, int valueFlag, int leafFlag)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_accountID",sUserInfo.AccountID),
new OracleParameter("in_valueFlag",valueFlag),
new OracleParameter("in_leafFlag",leafFlag),
new OracleParameter("out_result",OracleDbType.RefCursor),
};
paras[3].Direction = ParameterDirection.Output;
DataSet ds = con.ExecStoredProcedure("PRO_MST_GetGoodsType", paras);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 获取新增组织机构OrganizationCode
///
/// 上级组织机构编码
/// 用户基本信息
/// string
///
/// 2014.12.04 任海 新建
///
public static string GetOrganizationCode(string v_parentOrganizationCode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString = "select Max(OrganizationCode) from TP_MST_Organization "
+ " where OrganizationCode like '" + v_parentOrganizationCode + "%'"
+ "and length(OrganizationCode) = length('" + v_parentOrganizationCode + "')+3"
+ "and AccountID = " + sUserInfo.AccountID;
DataSet returnDatasetHuoQu2 = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
string v_MaxOrganizationCode = "";
if (returnDatasetHuoQu2 != null && returnDatasetHuoQu2.Tables[0].Rows.Count > 0)
{
v_MaxOrganizationCode = returnDatasetHuoQu2.Tables[0].Rows[0][0].ToString();
}
if (v_MaxOrganizationCode == "")
{
v_MaxOrganizationCode = v_MaxOrganizationCode + v_parentOrganizationCode + "001";
}
else
{
string v_tempCode = "";
v_tempCode = Convert.ToString(Convert.ToInt32(v_MaxOrganizationCode.Substring(v_MaxOrganizationCode.Length - 3)) + 1);
v_MaxOrganizationCode = "000" + v_tempCode;
v_MaxOrganizationCode = v_parentOrganizationCode + v_MaxOrganizationCode.Substring(v_MaxOrganizationCode.Length - 3);
}
return v_MaxOrganizationCode;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取窑炉管理的数据
///
/// 用户基本信息
/// 显示停用标识
/// DataSet
///
/// 2014.09.01 任海 新建
///
public static DataSet GetKilnData(SUserInfo sUserInfo, byte pValue)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
//根据帐套查询数据
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
sqlString = "Select 0 as Sel, KilnID,KilnCode,KilnName,KilnType,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Kiln where AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
//根据帐套，标识查询数据
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
sqlString = "Select KilnID,KilnCode,KilnName,KilnType,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Kiln where valueflag = :valueflag and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取窑炉管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllKilntInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取窑炉管理数据
string sqlString = "Select KilnID,KilnCode,KilnName,KilnType,INOUTMINTIMEIINTERVAL,OUTMINTIMEIINTERVAL,TurnoverInterval," +
"Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp " +
"from TP_MST_Kiln where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取窑车管理的数据
///
/// 用户基本信息
/// 显示停用标识
/// 窑炉ID
/// DataSet
///
/// 2014.09.08 任海 新建
///
public static DataSet GetKilnCarData(SUserInfo sUserInfo, byte pValue, string kilnID)
{
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
//根据帐套查询数据
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
sqlString = "Select KilnCarID,KilnID,KilnCarCode,KilnCarName,MaxGoodsNum,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_KilnCar where KilnID = :KilnID and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":KilnID",kilnID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
//根据帐套，标识查询数据
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
sqlString = "Select KilnCarID,KilnID,KilnCarCode,KilnCarName,MaxGoodsNum,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_KilnCar where valueflag = :valueflag and KilnID = :KilnID and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":KilnID",kilnID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 获取窑车管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllKilnCarInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取窑车管理的数据
string sqlString = "Select KilnCarID,KilnID,KilnCarCode,KilnCarName,MaxGoodsNum,KilnCarNum,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_KilnCar where AccountID = :AccountID "
+ " order by KilnCarNum, KilnCarCode";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取产品分级的数据
///
/// 用户基本信息
/// 显示停用标识
/// DataSet
///
/// 2014.10.22 任海 新建
///
public static DataSet GetGoodsLevelData(SUserInfo sUserInfo, byte pValue)
{
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取产品分级的数据
string sqlString = "select tp_mst_goodslevel.goodslevelid,"
+ "tp_mst_goodslevel.goodslevelname,"
+ "tp_sys_goodsleveltype.goodsleveltypename,"
+ "tp_mst_goodslevel.goodsleveltypeid,"
+ "tp_mst_goodslevel.issemifinishedex,"
+ "tp_mst_goodslevel.sfedisplayno,"
+ "tp_mst_goodslevel.isfinishedex,"
+ "tp_mst_goodslevel.fedisplayno,"
+ "tp_mst_goodslevel.isfinished,"
+ "tp_mst_goodslevel.isscrapped,"
+ "tp_mst_goodslevel.candisable,"
+ "tp_mst_goodslevel.remarks,"
+ "tp_mst_goodslevel.accountid,"
+ "tp_mst_goodslevel.valueflag,"
+ "tp_mst_goodslevel.createtime,"
+ "tp_mst_goodslevel.createuserid,"
+ "tp_mst_goodslevel.updatetime,"
+ "tp_mst_goodslevel.updateuserid,"
+ "tp_mst_goodslevel.optimestamp"
+ " from tp_mst_goodslevel"
+ " left join tp_sys_goodsleveltype"
+ " on tp_mst_goodslevel.goodsleveltypeid ="
+ " tp_sys_goodsleveltype.goodsleveltypeid"
+ " where "
+ " AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 获取产品分级的数据（根据ID）
///
/// 分类类别ID
/// 分类ID
/// 用户基本信息
/// DataSet
///
/// 2014.10.22 庄天威 新建
///
public static DataSet GetGoodsLevelDataById(int? GoodsLevelTypeID, int? GoodsLevelID, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"select GoodsLevelID,GoodsLevelName from TP_MST_GoodsLevel
where ValueFlag=1 and AccountID=" + sUserInfo.AccountID);
if (GoodsLevelTypeID != null)
{
sbSql.Append(" and GoodsLevelTypeID=" + GoodsLevelTypeID);
}
if (GoodsLevelID != null)
{
sbSql.Append(" and GoodsLevelID=" + GoodsLevelID);
}
DataSet results = oracleConn.GetSqlResultToDs(sbSql.ToString());
oracleConn.Close();
return results;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取数据字典管理的数据
///
/// 显示停用标识
/// 字典类别
/// 用户基本信息
/// DataSet
///
/// 2014.09.08 任海 新建
///
public static DataSet GetDictionaryData(byte Pvalue, string dictionaryType, SUserInfo sUserInfo)
{
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = String.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
//根据帐套获得数据字典管理的数据
if (Pvalue.ToString() == Constant.INT_IS_ONE.ToString())
{
sqlString = "Select DictionaryID,DictionaryType,DictionaryValue,DisplayNo,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_DataDictionary where DictionaryType = :DictionaryType and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":DictionaryType",dictionaryType)
};
}
//根据帐套，标识获得数据字典管理的数据
else if (Pvalue.ToString() == Constant.INT_IS_ZERO.ToString())
{
sqlString = "Select DictionaryID,DictionaryType,DictionaryValue,DisplayNo,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_DataDictionary where valueflag = :valueflag and DictionaryType = :DictionaryType and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":DictionaryType",dictionaryType)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 获取数据字典管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllDataDictionaryInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取数据字典管理的全部数据
string sqlString = "Select DictionaryID,DictionaryType,DictionaryValue,DisplayNo,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_DataDictionary where AccountID = :AccountID order by DictionaryType,DisplayNo";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取数据字典左侧树的数据
///
/// DataSet
///
/// 2014.09.15 任海 新建
///
public static DataSet GetDataDictionaryTreeData()
{
DataSet dSetResult = new DataSet();
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取数据字典分类
string sqlString = "Select DataDictionaryClass,DataDictionaryClassName,DisplayNo from TP_SYS_DataDictionaryClass where PROGRAMTYPE = '1' Order By DisplayNo";
oracleConn.Open();
DataTable result = oracleConn.GetSqlResultToDt(sqlString);
result.TableName = "TP_SYS_DataDictionaryClass";
dSetResult.Tables.Add(result);
//获取数据字典类别
string sqlStrings = "select DataDictionaryType,DataDictionaryTypeName,DataDictionaryClass,DisplayNo from TP_SYS_DataDictionaryType Order By DisplayNo";
DataTable results = oracleConn.GetSqlResultToDt(sqlStrings);
results.TableName = "TP_SYS_DataDictionaryType";
dSetResult.Tables.Add(results);
oracleConn.Close();
return dSetResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 获取系统参数管理的数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.09.15 任海 新建
///
public static DataSet GetSystemData(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取系统参数管理的数据
string sqlString = "Select SettingCode,SettingName,CategoryName,SettingValue,SettingDefaultValues,AccountID,Remarks,Tooltip,EditFlag,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_SystemSetting where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 通过SettingCode获取系统参数管理的数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.10.10 王鑫 新建
///
public static DataSet GetSystemSettingDataByCode(string settingcode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取系统参数管理的数据
string sqlString = "Select SettingCode,SettingName,CategoryName,SettingValue,SettingDefaultValues,AccountID,Remarks,Tooltip,EditFlag,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_SystemSetting where AccountID = :AccountID and SettingCode=:SettingCode";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":SettingCode",settingcode)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 由系统参数类型settingType获取系统参数类别和名称
///
/// 系统参数类型
/// 用户基本信息
/// 系统参数类别
/// 系统参数名称
/// DataSet
///
/// 2014.09.18 任海 新建
///
private static void GetParameterBySystemType(string settingType, SUserInfo sUserInfo,
ref string functionName, ref string parameterName)
{
if (string.IsNullOrEmpty(settingType))
{
return;
}
switch (settingType)
{
#region 系统登录
case "S_LGN_0001":
functionName = "系统登录";
parameterName = "错误密码尝试登录次数";
break;
case "S_LGN_0002":
functionName = "系统登录";
parameterName = "用户锁定时长";
break;
case "S_LGN_0003":
functionName = "系统登录";
parameterName = "启用MAC地址认证";
break;
case "S_LGN_0004":
functionName = "系统登录";
parameterName = "启用登录时段限制";
break;
case "SS_LGN_05":
functionName = "系统登录";
parameterName = "启用即时通讯";
break;
#endregion
#region 共通设置
case "SS_CMN_01":
functionName = "共通设置";
parameterName = "金额小数位数";
break;
case "S_CMN_0003":
functionName = "共通设置";
parameterName = "结算账务日期";
break;
case "S_CMN_0001":
functionName = "共通设置";
parameterName = "默认用户密码";
break;
case "SS_CMN_04":
functionName = "共通设置";
parameterName = "重量小数位数";
break;
case "S_CMN_0004":
functionName = "共通设置";
parameterName = "账务时间";
break;
case "S_CMN_0002":
functionName = "共通设置";
parameterName = "年度开始月份";
break;
case "SS_CMN_07":
functionName = "共通设置";
parameterName = "每页显示数量";
break;
#endregion
default:
break;
}
}
///
/// 获取工种管理的数据
///
/// 显示停用标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.04 任海 新建
///
public static DataSet GetJobsData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
// 0 as Sel, 陈冰添加根据帐套查询数据
sqlString = "Select 0 as Sel,JobsID,JobsCode,JobsName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Jobs where AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
// 0 as Sel, 陈冰添加根据账套帐标识符查询数据
sqlString = "Select 0 as Sel,JobsID,JobsCode,JobsName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Jobs where valueflag = :valueflag and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取工种管理的全部数据
///
/// 用户基本信息
///
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllJobsInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select JobsID,JobsCode,JobsName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Jobs where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取职务管理的数据
///
/// 显示停用标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.10 任海 新建
///
public static DataSet GetPostData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
//根据帐套查询数据
sqlString = "Select PostID,PostCode,PostName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Post where AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
//根据帐套，标识符查询数据
sqlString = "Select PostID,PostCode,PostName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Post where valueflag = :valueflag and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取职务管理的全部数据
///
/// 用户信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllPostInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select PostID,PostCode,PostName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ "from TP_MST_Post where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取产品缺陷管理的数据
///
/// 显示停用标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.10 任海 新建
///
public static DataSet GetDefectData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
// 0 as Sel, 陈冰添加根据帐套查询数据
sqlString = "Select 0 as Sel,TP_MST_Defect.DefectID,TP_MST_Defect.DefectCode,TP_MST_Defect.DefectName,TP_MST_Defect.Remarks,TP_MST_Defect.AccountID,TP_MST_Defect.ValueFlag,TP_MST_Defect.CreateTime,TP_MST_Defect.CreateUserID,TP_MST_Defect.UpdateTime,TP_MST_Defect.UpdateUserID,TP_MST_Defect.OPTimeStamp,TP_MST_Defect.DEFECTTYPEID,TP_MST_DEFECTTYPE.DEFECTTYPENAME "
+ "from TP_MST_Defect left join TP_MST_DEFECTTYPE on TP_MST_Defect.DEFECTTYPEID=TP_MST_DEFECTTYPE.DEFECTTYPEID where TP_MST_Defect.AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
//根据帐套，标识符查询数据
sqlString = "Select 0 as Sel,TP_MST_Defect.DefectID,TP_MST_Defect.DefectCode,TP_MST_Defect.DefectName,TP_MST_Defect.Remarks,TP_MST_Defect.AccountID,TP_MST_Defect.ValueFlag,TP_MST_Defect.CreateTime,TP_MST_Defect.CreateUserID,TP_MST_Defect.UpdateTime,TP_MST_Defect.UpdateUserID,TP_MST_Defect.OPTimeStamp,TP_MST_Defect.DEFECTTYPEID,TP_MST_DEFECTTYPE.DEFECTTYPENAME "
+ "from TP_MST_Defect left join TP_MST_DEFECTTYPE on TP_MST_Defect.DEFECTTYPEID=TP_MST_DEFECTTYPE.DEFECTTYPEID where TP_MST_Defect.valueflag = :valueflag and TP_MST_Defect.AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取产品缺陷管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.30 任海 新建
///
public static DataSet GetAllDefectInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectID,DefectCode,DefectName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp,DefectTypeID "
+ " ,displayno from TP_MST_Defect where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取成型线类型管理的数据
///
/// 显示停用标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.11 任海 新建
///
public static DataSet GetGMouldTypeData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
//根据帐套查询数据
sqlString = "Select GMouldTypeID,GMouldTypeCode,GMouldTypeName,CanManyTimes,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_GMouldType where AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
//根据帐套，标识符查询数据
sqlString = "Select GMouldTypeID,GMouldTypeCode,GMouldTypeName,CanManyTimes,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_GMouldType where valueflag = :valueflag and AccountID = :AccountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取成型线类型管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllGMouldTypeInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select GMouldTypeID,GMouldTypeCode,GMouldTypeName,CanManyTimes,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,"
+ "UpdateUserID,OPTimeStamp from TP_MST_GMouldType where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 查询温湿计信息
///
/// 用户基本信息
/// 温湿计信息实体
/// DataSet
///
/// 2014.12.24 任海 新建
///
public static DataSet GetThermometer(ThermometerEntity thermometerEntity, SUserInfo userInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
StringBuilder sbSql = new StringBuilder();
// sbSql.Append(@"Select ThermometerID,BuildingNo,FloorNo,LocationCode,ThermometerCode,ManagerName,Remarks,AccountID,ValueFlag,CreateTime,
// CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_Thermometer
// where (:ThermometerID is null or ThermometerID = :ThermometerID)
// and (:AccountID is null or AccountID = :AccountID)
// and (:ValueFlag is null or ValueFlag = :ValueFlag or 2 = :ValueFlag)
// and (:BuildingNo is null or BuildingNo = :BuildingNo)
// and (:FloorNo is null or FloorNo = :FloorNo)
// and (:LocationCode is null or LocationCode = :LocationCode)
// and (:ThermometerCode is null or ThermometerCode = :ThermometerCode)
// and (:ManagerName is null or ManagerName = :ManagerName)
// and (:Remarks is null or Remarks = :Remarks )
// and (:SelectUserId is null
// or ThermometerID in (Select PurviewId from TP_MST_UserPurview
// where PurviewType=12 and UserId=:SelectUserId))
// Order by ThermometerID desc");
sbSql.Append(@"Select 0 AS Sel, ThermometerID,BuildingNo,FloorNo,LocationCode,ThermometerCode,ManagerName,Remarks,AccountID,ValueFlag,CreateTime,
CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_Thermometer
where (:ThermometerID is null or ThermometerID = :ThermometerID)
and (:AccountID is null or AccountID = :AccountID)
and (:ValueFlag is null or ValueFlag = :ValueFlag or 2 = :ValueFlag)
and (:BuildingNo is null or BuildingNo = :BuildingNo)
and (:FloorNo is null or FloorNo = :FloorNo)
and (:LocationCode is null or LocationCode = :LocationCode)
and (:ThermometerCode is null or ThermometerCode = :ThermometerCode)
and (:ManagerName is null or ManagerName = :ManagerName)
and (:Remarks is null or Remarks = :Remarks )
and (:SelectUserId is null
or exists (Select PurviewId from TP_MST_UserPurview
where PurviewType=12 and (TP_MST_UserPurview.PurviewID = -1 or TP_MST_UserPurview.PurviewID = TP_MST_Thermometer.ThermometerID) and UserId=:SelectUserId))
Order by ThermometerID desc");
OracleParameter[] ThermometerParas = new OracleParameter[]{
new OracleParameter(":ThermometerID",OracleDbType.Int32,
thermometerEntity.ThermometerID,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":ValueFlag",OracleDbType.Int32,
thermometerEntity.ValueFlag,ParameterDirection.Input),
new OracleParameter(":BuildingNo",OracleDbType.NVarchar2,
thermometerEntity.BuildingNo,ParameterDirection.Input),
new OracleParameter(":FloorNo",OracleDbType.NVarchar2,
thermometerEntity.FloorNo,ParameterDirection.Input),
new OracleParameter(":LocationCode",OracleDbType.NVarchar2,
thermometerEntity.LocationCode,ParameterDirection.Input),
new OracleParameter(":ThermometerCode",OracleDbType.NVarchar2,
thermometerEntity.ThermometerCode,ParameterDirection.Input),
new OracleParameter(":ManagerName",OracleDbType.NVarchar2,
thermometerEntity.ManagerName,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
thermometerEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":SelectUserId",OracleDbType.NVarchar2,
thermometerEntity.SelectUserId,ParameterDirection.Input),
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sbSql.ToString(), ThermometerParas);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取配置员工的数据
///
/// 用户ID
/// 用户基本信息
/// DataSet
///
/// 2014.10.13 任海 新建
public static DataSet GetDeploystaffData(int userID, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "select TP_HR_Staff.Staffcode,"
+ "TP_HR_Staff.Staffname,"
+ "TP_HR_Staff.Staffid,"
+ "TP_HR_Staff.Jobs,"
+ "TP_HR_Staff.OrganizationID,"
+ "TP_HR_Staff.StaffStatus,"
+ "TP_MST_Jobs.Jobsname,"
+ "TP_MST_Organization.Organizationname,"
+ "TP_SYS_StaffStatus.Staffstatusname"
+ " from TP_HR_Staff"
+ " left join TP_MST_Jobs"
+ " on TP_MST_Jobs.Jobsid = TP_HR_Staff.Jobs"
+ " left join TP_MST_Organization"
+ " on TP_MST_Organization.Organizationid = TP_HR_Staff.Organizationid"
+ " left join TP_SYS_StaffStatus"
+ " on TP_SYS_StaffStatus.StaffStatusID = TP_HR_Staff.StaffStatus"
+ " WHERE TP_HR_Staff.AccountID = :AccountID"
+ " AND TP_HR_Staff.userID = :userID AND TP_HR_Staff.ValueFlag = 1";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":userID",userID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷位置的数据
///
/// 正常标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.10 冯雪 新建
/// 2014.10.28 任海 修改
///
public static DataSet GetDefectPositionData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
//根据帐套查询数据
sqlString = "Select DefectPositionID,DefectPositionCode,DefectPositionName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,"
+ "UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_DefectPosition where AccountID =:accountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":accountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
//根据帐套，标识符查询数据
sqlString = "Select DefectPositionID,DefectPositionCode,DefectPositionName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,"
+ "UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_DefectPosition where valueflag = :valueflag and AccountID =:accountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new OracleParameter(":accountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷位置管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.31 任海 新建
///
public static DataSet GetAllDefectPositionInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectPositionID,DefectPositionCode,DefectPositionName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,"
+ "UpdateTime,UpdateUserID,OPTimeStamp ,displayno from TP_MST_DefectPosition where AccountID =:accountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取产品类型的数据
///
/// 正常标识
/// 用户基本信息
/// DataSet
///
/// 2014.09.11 冯雪 新建
/// 2014.10.28 任海 修改
///
public static DataSet GetGoodsTypeData(byte pValue, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = string.Empty;
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = null;
if (pValue.ToString() == Constant.INT_IS_ONE.ToString())
{
//根据帐套查询数据
sqlString = "Select GoodsTypeID,GoodsTypeCode,GoodsTypeName,GoodsTypeFullName,IsLeafNode,Remarks,AccountID,ValueFlag,"
+ "CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_GoodsType where AccountID =:accountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":accountID",sUserInfo.AccountID)
};
}
else if (pValue.ToString() == Constant.INT_IS_ZERO.ToString())
{
//根据帐套，标识符查询数据
sqlString = "Select GoodsTypeID,GoodsTypeCode,GoodsTypeName,GoodsTypeFullName,IsLeafNode,Remarks,AccountID,ValueFlag,"
+ "CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_GoodsType where valueflag = :valueflag and AccountID =:accountID";
oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":valueflag",Constant.INT_IS_ONE.ToString()),
new OracleParameter(":accountID",sUserInfo.AccountID)
};
}
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取产品类型的数据
///
/// 用户基本信息
/// DataSet
///
///
public static DataSet GetAllGoodsTypeData(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select GoodsTypeID,GoodsTypeCode,GoodsTypeName,GoodsTypeFullName,IsLeafNode,Remarks,AccountID,ValueFlag,"
+ "CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp from TP_MST_GoodsType where AccountID =:accountID order by goodstypecode";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":accountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 按条件获取产品类型的数据
///
/// 正常标识
/// 产品类型编码
/// 用户基本信息
/// DataSet
///
/// 2014.09.11 冯雪 新建
///
public static DataSet GetGoodsTyperData(byte pValue, string typeCode, SUserInfo sUserInfo)
{
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strValueFlag = "1";
if (pValue.ToString().Equals("1"))
{
strValueFlag = "0";
}
string strSql = " Select * from TP_MST_GoodsType "
+ " where valueflag = :valueflag "
+ " and AccountID =:accountID "
+ " and goodstypecode like :typeCode"
+ " order by goodstypecode ";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras1 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":valueflag",strValueFlag),
new OracleParameter(":typeCode",typeCode+"%"),
new OracleParameter(":accountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(strSql, paras1);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 获取产品档案的ID
///
/// 用户基本信息
/// 产品档案的ID
/// DataSet
///
/// 2014.10.27 任海 新建
///
public static DataSet GetGoodsTypeIDData(SUserInfo sUserInfo, int GoodsTypeID)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strSql = "Select * from TP_MST_Goods where GoodsTypeID = :GoodsTypeID and AccountID =:accountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras1 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new OracleParameter(":accountID",sUserInfo.AccountID),
new OracleParameter(":GoodsTypeID",GoodsTypeID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(strSql, paras1);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取用户操作权限
///
/// 用户ID
/// 用户基本信息
/// DataSet用户操作权限集合
public static DataSet GetUserRightData(int userID, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
DataSet returnUserRight = new DataSet();
//查看部门权限
string strSqlViewOrg = "select a.OrganizationID as PurviewID,a.OrganizationCode,a.OrganizationName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 1 as PurviewType from ("
+ "SELECT OrganizationID, OrganizationCode,OrganizationName, 0 FROM TP_MST_Organization WHERE AccountID = :AccountID ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 1 ) b on a.OrganizationID=b.purviewid";
//操作部门权限
string strSqlOrg = "select a.OrganizationID as PurviewID,a.OrganizationCode,a.OrganizationName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 2 as PurviewType from ("
+ "SELECT OrganizationID, OrganizationCode,OrganizationName, 0 FROM TP_MST_Organization WHERE ValueFlag = 1 and AccountID = :AccountID ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 2) b on a.OrganizationID=b.purviewid ";
//查看工号范围权限
string strSqlViewUser = "select a.UserID as PurviewID,a.UserCode,a.UserName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 3 as PurviewType,a.OrganizationName,a.OrganizationFullName,a.ValueFlag from ("
+ "SELECT TP_MST_User.UserID, TP_MST_User.UserCode,TP_MST_User.UserName,TP_MST_User.ValueFlag,TP_MST_Organization.OrganizationName,TP_MST_Organization.OrganizationFullName FROM TP_MST_User left join TP_MST_Organization on TP_MST_User.ORGANIZATIONID=TP_MST_Organization.ORGANIZATIONID WHERE TP_MST_User.AccountID = :AccountID and TP_MST_USER.UserCode != 'admin' ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 3) b on a.UserID=b.purviewid ";
// + " union all (SELECT -1 as PurviewID,null as UserCode,null as UserName,1 as RightFlag,3 as PurviewType,null as OrganizationName,null as OrganizationFullName,null as ValueFlag FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 3 and purviewid=-1)";
//操作工号范围权限
string strSqlUser = "select a.UserID as PurviewID,a.UserCode,a.UserName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 4 as PurviewType,a.OrganizationName,a.OrganizationFullName,a.ValueFlag from ("
+ "SELECT TP_MST_User.UserID, TP_MST_User.UserCode,TP_MST_User.UserName,TP_MST_User.ValueFlag,TP_MST_Organization.OrganizationName,TP_MST_Organization.OrganizationFullName FROM TP_MST_User left join TP_MST_Organization on TP_MST_User.ORGANIZATIONID=TP_MST_Organization.ORGANIZATIONID WHERE TP_MST_User.ValueFlag=1 and TP_MST_User.AccountID = :AccountID and TP_MST_USER.UserCode != 'admin' ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 4) b on a.UserID=b.purviewid ";
//查看成型线范围权限
string strSqlViewGroutingLine = "select a.Groutinglineid as PurviewID,a.Groutinglinecode,a.GroutinglineName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 5 as PurviewType,a.ValueFlag from ("
+ " select Groutinglineid,Groutinglinecode,GroutinglineName,ValueFlag from tp_pc_groutingline where tp_pc_groutingline.AccountID = :AccountID and ValueFlag in(0,1) Order by Groutinglinecode ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 5) b on a.Groutinglineid=b.purviewid and a.ValueFlag in(0,1)";
//操作成型线范围权限
string strSqlGroutingLine = "select a.Groutinglineid as PurviewID,a.Groutinglinecode,a.GroutinglineName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 6 as PurviewType,a.ValueFlag from ("
+ " select Groutinglineid,Groutinglinecode,GroutinglineName,ValueFlag from tp_pc_groutingline where tp_pc_groutingline.AccountID = :AccountID and ValueFlag in(0,1) Order by Groutinglinecode ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 6) b on a.Groutinglineid=b.purviewid and a.ValueFlag in(0,1)";
//查看生产线范围权限
string strSqlViewProductionLine = "select a.ProductionLineid as PurviewID,a.ProductionLineCode,a.ProductionLinename,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 7 as PurviewType,a.ValueFlag from ("
+ " select ProductionLineid,ProductionLineCode,ProductionLinename,ValueFlag from tp_pc_productionline where tp_pc_productionline.AccountID = :AccountID and tp_pc_productionline.valueflag=1 Order by ProductionLineCode ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 7) b on a.ProductionLineid=b.purviewid ";
//操作生产线范围权限
string strSqlProductionLine = "select a.ProductionLineid as PurviewID,a.ProductionLineCode,a.ProductionLinename,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 8 as PurviewType,a.ValueFlag from ("
+ " select ProductionLineid,ProductionLineCode,ProductionLinename,ValueFlag from tp_pc_productionline where tp_pc_productionline.AccountID = :AccountID and ValueFlag=1 Order by ProductionLineCode ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 8) b on a.ProductionLineid=b.purviewid ";
//查看工序范围权限
string strSqlViewProcedure = "select a.Procedureid as PurviewID,a.ProcedureCode,a.Procedurename,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 9 as PurviewType,a.ValueFlag,a.ProductionLineid from ("
+ " select Procedureid,ProcedureCode,Procedurename,ProductionLineid,valueflag from tp_pc_procedure where tp_pc_procedure.AccountID = :AccountID Order by displayno) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 9) b on a.Procedureid=b.purviewid ";
//操作工序范围权限
//string strSqlProcedure = "select a.Procedureid as PurviewID,a.ProcedureCode,a.Procedurename,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 10 as PurviewType,a.ValueFlag,a.ProductionLineid from ("
// + " select Procedureid,ProcedureCode,Procedurename,ProductionLineid,valueflag from tp_pc_procedure where tp_pc_procedure.AccountID = :AccountID Order by ProcedureCode) a "
// + " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 10) b on a.Procedureid=b.purviewid ";
string strSqlProcedure = @"select a.Procedureid as PurviewID,a.ProcedureCode,a.Procedurename,
a.Productionlinecode,a.Productionlinename,
case (nvl(b.purviewid,0))
when 0 then 0 else 1 end as RightFlag, 10 as PurviewType,a.ValueFlag,a.ProductionLineid from (
select tp_pc_procedure.displayno, tp_pc_procedure.Procedureid, tp_pc_procedure.ProcedureCode, tp_pc_procedure.Procedurename,
tp_pc_procedure.ProductionLineid, tp_pc_procedure.valueflag,TP_PC_ProductionLine.Productionlinecode,TP_PC_ProductionLine.Productionlinename from tp_pc_procedure
left join TP_PC_ProductionLine on tp_pc_procedure.ProductionLineID=TP_PC_ProductionLine.Productionlineid
where tp_pc_procedure.AccountID =:AccountID and tp_pc_procedure.valueflag=1 Order by displayno) a
left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 10) b on a.Procedureid=b.purviewid
order by displayno";//, a.Productionlineid ,ProcedureCode
//查看温湿计范围权限
string strSqlViewThermometer = "select a.ThermometerID as PurviewID,a.ThermometerCode,a.ManagerName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 11 as PurviewType,a.ValueFlag from ("
+ " select ThermometerID,ThermometerCode,ManagerName,ValueFlag from TP_MST_Thermometer where TP_MST_Thermometer.AccountID = :AccountID Order by ThermometerID ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 11) b on a.ThermometerID=b.purviewid ";
//操作温湿计范围权限
string strSqlThermometer = "select a.ThermometerID as PurviewID,a.ThermometerCode,a.ManagerName,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 12 as PurviewType,a.ValueFlag from ("
+ " select ThermometerID,ThermometerCode,ManagerName,ValueFlag from TP_MST_Thermometer where TP_MST_Thermometer.AccountID = :AccountID Order by ThermometerID ) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 12) b on a.ThermometerID=b.purviewid ";
//撤销工序范围权限
string strSqlCancelProcedure = "select a.Procedureid as PurviewID,a.ProcedureCode,a.Procedurename,case (nvl(b.purviewid,0)) when 0 then 0 else 1 end as RightFlag, 13 as PurviewType,a.ValueFlag,a.ProductionLineid from ("
+ " select Procedureid,ProcedureCode,Procedurename,ProductionLineid,valueflag from tp_pc_procedure where tp_pc_procedure.AccountID = :AccountID and UnDoFlag=1 Order by displayno) a "
+ " left join (SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID AND PurviewType = 13) b on a.Procedureid=b.purviewid ";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID),
new Oracle.ManagedDataAccess.Client.OracleParameter(":UserID",userID)
};
string sqlFunctionCode = "SELECT * FROM TP_MST_UserPurview WHERE UserID =:UserID and purviewid=-1";
Oracle.ManagedDataAccess.Client.OracleParameter[] parasFunctionCode = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":UserID",userID)
};
oracleConn.Open();
DataTable dtViewOrg = oracleConn.GetSqlResultToDs(strSqlViewOrg, paras).Tables[0].Copy();
dtViewOrg.TableName = "ViewOrg";
DataTable dtOrg = oracleConn.GetSqlResultToDs(strSqlOrg, paras).Tables[0].Copy();
dtOrg.TableName = "Org";
DataTable dtViewUser = oracleConn.GetSqlResultToDs(strSqlViewUser, paras).Tables[0].Copy();
dtViewUser.TableName = "ViewUser";
DataTable dtUser = oracleConn.GetSqlResultToDs(strSqlUser, paras).Tables[0].Copy();
dtUser.TableName = "User";
DataTable dtViewGroutingLine = oracleConn.GetSqlResultToDs(strSqlViewGroutingLine, paras).Tables[0].Copy();
dtViewGroutingLine.TableName = "ViewGroutingLine";
DataTable dtGroutingLine = oracleConn.GetSqlResultToDs(strSqlGroutingLine, paras).Tables[0].Copy();
dtGroutingLine.TableName = "GroutingLine";
DataTable dtViewProductionLine = oracleConn.GetSqlResultToDs(strSqlViewProductionLine, paras).Tables[0].Copy();
dtViewProductionLine.TableName = "ViewProductionLine";
DataTable dtProductionLine = oracleConn.GetSqlResultToDs(strSqlProductionLine, paras).Tables[0].Copy();
dtProductionLine.TableName = "ProductionLine";
DataTable dtViewProcedure = oracleConn.GetSqlResultToDs(strSqlViewProcedure, paras).Tables[0].Copy();
dtViewProcedure.TableName = "ViewProcedure";
DataTable dtProcedure = oracleConn.GetSqlResultToDs(strSqlProcedure, paras).Tables[0].Copy();
dtProcedure.TableName = "Procedure";
DataTable dtViewThermometer = oracleConn.GetSqlResultToDs(strSqlViewThermometer, paras).Tables[0].Copy();
dtViewThermometer.TableName = "ViewThermometer";
DataTable dtThermometer = oracleConn.GetSqlResultToDs(strSqlThermometer, paras).Tables[0].Copy();
dtThermometer.TableName = "Thermometer";
DataTable dtFunctionCode = oracleConn.GetSqlResultToDs(sqlFunctionCode, parasFunctionCode).Tables[0].Copy();
dtFunctionCode.TableName = "FunctionCode";
DataTable dtCancel = oracleConn.GetSqlResultToDs(strSqlCancelProcedure, paras).Tables[0].Copy();
dtCancel.TableName = "CancelProcedure";
returnUserRight.Tables.Add(dtViewOrg);
returnUserRight.Tables.Add(dtOrg);
returnUserRight.Tables.Add(dtViewUser);
returnUserRight.Tables.Add(dtUser);
returnUserRight.Tables.Add(dtViewGroutingLine);
returnUserRight.Tables.Add(dtGroutingLine);
returnUserRight.Tables.Add(dtViewProductionLine);
returnUserRight.Tables.Add(dtProductionLine);
returnUserRight.Tables.Add(dtViewProcedure);
returnUserRight.Tables.Add(dtProcedure);
returnUserRight.Tables.Add(dtViewThermometer);
returnUserRight.Tables.Add(dtThermometer);
returnUserRight.Tables.Add(dtCancel);
returnUserRight.Tables.Add(dtFunctionCode);
oracleConn.Close();
return returnUserRight;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据用户ID取得用户的功能权限信息
///
/// 用户ID
/// 用户基本信息
/// DataSet
public static DataSet GetUserFunctionRightInfo(int userID, SUserInfo sUserInfo)
{
DataSet dsResult = new DataSet();
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
if (userID < 1)
{
return null;
}
oracleConn.Open();
#region 对应要执行的SQL语句
string strSql1 = "SELECT 0 Choose,FunctionCode, Functionlevel ,FunctionName,FullName,FunctionFlag,FunctionButtonFlag,0 LicensesNumber,0 UseLincenseNumber"
+ " FROM TP_SYS_Function where valueflag='1' and functionprogram='1' and functioncode not like '0101%' ";// and functioncode not like '0102%'";
DataTable dtFunction = oracleConn.GetSqlResultToDt(strSql1);
string strSql2 = "SELECT ur.userid, ur.Functioncode, f.functionlevel\n" +
" FROM TP_MST_UserRight ur\n" +
" INNER JOIN tp_sys_function f\n" +
" ON ur.functioncode = f.functioncode\n" +
" AND f.valueflag = '1'\n" +
" AND f.functionprogram = '1'" +
" WHERE userid = :pUserid";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras2 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":pUserid", OracleDbType.Int32,userID, ParameterDirection.Input)
};
DataTable dtUserRight = oracleConn.GetSqlResultToDt(strSql2, paras2);
//string strSql3 = "SELECT C.FunctionCode, COUNT(C.UserID) AS UseNum FROM TP_MST_UserRight C,TP_MST_User D"
// + " WHERE C.UserID = D.UserID AND D.ValueFlag > 0 AND D.AccountID = :accountID GROUP BY C.FunctionCode";
//Oracle.ManagedDataAccess.Client.OracleParameter[] paras3 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
//{
// new Oracle.ManagedDataAccess.Client.OracleParameter(":accountID", OracleDbType.Int32,sUserInfo.AccountID, ParameterDirection.Input)
//};
//DataTable dtUserFuncNum = oracleConn.GetSqlResultToDt(strSql3, paras3);
#endregion
oracleConn.Close();
foreach (DataRow newFunRow in dtFunction.Rows)
{
//string strFunctionCode = newFunRow["FunctionCode"].ToString();
string strFunctionLevel = newFunRow["FunctionLevel"].ToString();
//DataRow[] userRights = dtUserRight.Select("Functioncode = '" + strFunctionCode + "'");
DataRow[] userRights = dtUserRight.Select("functionlevel like '" + strFunctionLevel + "%'");
if (userRights != null && userRights.Count() > 0)
{
newFunRow["Choose"] = 1;
}
//DataRow[] userRightNums = dtUserFuncNum.Select("Functioncode = '" + strFunctionCode + "'");
//if (userRightNums != null && userRightNums.Count() > Constant.INT_IS_ZERO)
//{
// newFunRow["UseLincenseNumber"] = userRightNums[0]["UseNum"];
//}
}
string sqlExist = "select 1 from TP_MST_UserRight where Functioncode='[ALL]' and UserID=:UserID";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras4 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":UserID", OracleDbType.Int32,userID, ParameterDirection.Input),
};
DataSet dsExist = oracleConn.GetSqlResultToDs(sqlExist, paras4);
if (dsExist != null && dsExist.Tables[0].Rows.Count > 0)
{
DataRow drNew = dtFunction.NewRow();
drNew["Functioncode"] = "[ALL]";
dtFunction.Rows.Add(drNew);
}
dsResult.Tables.Add(dtFunction);
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据用户ID取得用户的功能权限信息
///
/// 用户ID
/// 用户基本信息
/// DataSet
public static DataSet GetUserFunctionRightTwoInfo(int userID, SUserInfo sUserInfo)
{
DataSet dsResult = new DataSet();
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
if (userID < 1)
{
return null;
}
oracleConn.Open();
#region 对应要执行的SQL语句
string strSql1 = "SELECT 0 Choose,FunctionCode, Functionlevel ,FunctionName,FullName,FunctionFlag,FunctionButtonFlag,0 LicensesNumber,0 UseLincenseNumber"
+ " FROM TP_SYS_Function where valueflag='1' ";
DataTable dtFunction = oracleConn.GetSqlResultToDt(strSql1);
//string strSql2 = "SELECT userid,Functioncode FROM TP_MST_UserRight WHERE userid = :pUserid";
string strSql2 = "SELECT ur.userid, ur.Functioncode, f.functionlevel\n" +
" FROM TP_MST_UserRight ur\n" +
" INNER JOIN tp_sys_function f\n" +
" ON ur.functioncode = f.functioncode\n" +
" AND f.valueflag = '1'\n" +
" AND f.functionprogram = '2'" +
" WHERE userid = :pUserid";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras2 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":pUserid", OracleDbType.Int32,userID, ParameterDirection.Input)
};
DataTable dtUserRight = oracleConn.GetSqlResultToDt(strSql2, paras2);
//string strSql3 = "SELECT C.FunctionCode, COUNT(C.UserID) AS UseNum FROM TP_MST_UserRight C,TP_MST_User D"
// + " WHERE C.UserID = D.UserID AND D.ValueFlag > 0 AND D.AccountID = :accountID GROUP BY C.FunctionCode";
//Oracle.ManagedDataAccess.Client.OracleParameter[] paras3 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
//{
// new Oracle.ManagedDataAccess.Client.OracleParameter(":accountID", OracleDbType.Int32,sUserInfo.AccountID, ParameterDirection.Input)
//};
//DataTable dtUserFuncNum = oracleConn.GetSqlResultToDt(strSql3, paras3);
#endregion
oracleConn.Close();
foreach (DataRow newFunRow in dtFunction.Rows)
{
//string strFunctionCode = newFunRow["FunctionCode"].ToString();
string strFunctionLevel = newFunRow["FunctionLevel"].ToString();
//DataRow[] userRights = dtUserRight.Select("Functioncode = '" + strFunctionCode + "'");
DataRow[] userRights = dtUserRight.Select("FunctionLevel like '" + strFunctionLevel + "%'");
if (userRights != null && userRights.Count() > 0)
{
newFunRow["Choose"] = 1;
}
//DataRow[] userRightNums = dtUserFuncNum.Select("Functioncode = '" + strFunctionCode + "'");
//if (userRightNums != null && userRightNums.Count() > Constant.INT_IS_ZERO)
//{
// newFunRow["UseLincenseNumber"] = userRightNums[0]["UseNum"];
//}
}
string sqlExist = "select 1 from TP_MST_UserRight where Functioncode='[ALL2]' and UserID=:UserID";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras4 = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":UserID", OracleDbType.Int32,userID, ParameterDirection.Input),
};
DataSet dsExist = oracleConn.GetSqlResultToDs(sqlExist, paras4);
if (dsExist != null && dsExist.Tables[0].Rows.Count > 0)
{
DataRow drNew = dtFunction.NewRow();
drNew["Functioncode"] = "[ALL2]";
dtFunction.Rows.Add(drNew);
}
dsResult.Tables.Add(dtFunction);
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据功能取得使用该功能的用户列表
///
/// 功能编号
/// 用户基本信息
/// DataSet
public static DataSet GetFunctionUsers(string functionCode, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
#region 对应要执行的SQL语句
string sqlString = "SELECT B.UserCode,B.UserName,C.AccountName,TP_MST_Organization.OrganizationName FROM TP_MST_UserRight A"
+ " INNER JOIN TP_MST_User B ON A.UserID = B.UserID"
+ " INNER JOIN TP_MST_Organization ON B.OrganizationID = TP_MST_Organization.OrganizationID "
+ " INNER JOIN TP_MST_Account C ON B.AccountID = C.AccountID "
+ " AND C.AccountID = " + sUserInfo.AccountID
+ " WHERE B.ValueFlag > 0 AND A.FunctionCode = :pFunctionCode"
+ " ORDER BY C.AccountName,B.UserCode,B.UserName";
Oracle.ManagedDataAccess.Client.OracleParameter[] paras = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":pFunctionCode", OracleDbType.Varchar2, functionCode, ParameterDirection.Input)
};
#endregion
DataSet dsResult = oracleConn.GetSqlResultToDs(sqlString, paras);
oracleConn.Close();
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 检查产品类型到更新
///
/// 产品类型编码
/// int
public static int CheckGoodsTypeToUpdate(string GoodsTypeCode)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string strSql = @"Select Sum(Counts) from
(Select Count(*) as Counts from TP_MST_GOODSTYPE
WHERE GoodsTypeCode like '" + GoodsTypeCode + @"%'
and GoodsTypeCode != '" + GoodsTypeCode + @"'
Union
Select Count(*) as Counts from TP_MST_GOODS
WHERE GOODSTYPEID = '" + GoodsTypeCode + "')";
oracleConn.Open();
int Counts = Convert.ToInt32(oracleConn.GetSqlResultToStr(strSql, null));
oracleConn.Close();
return Counts;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#endregion
#region 报表工序基础信息管理
///
/// 报表工序基础信息
///
/// 报表工序实体
/// 用户基本信息
/// DataSet数据集
public static DataSet GetRptProcedureModule(RptProcedureEntity rptProcedureEntity, SUserInfo sUserInfo)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_rptProcedureId",OracleDbType.Int32,rptProcedureEntity.RptProcedureID,ParameterDirection.Input),
new OracleParameter("in_rptProcedureCode",OracleDbType.Varchar2,rptProcedureEntity.RptProcedureCode,ParameterDirection.Input),
new OracleParameter("in_rptProcedureName",OracleDbType.Varchar2,rptProcedureEntity.RptProcedureName,ParameterDirection.Input),
new OracleParameter("in_rptProcedureType",OracleDbType.Varchar2,rptProcedureEntity.RptProcedureTpye,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_MST_GetRptProcedure", paras);
return dsSearchReport;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
///
/// 根据报表来源工序Id查询先关工序信息
///
/// 报表工序Id
/// DataSet数据集
public static DataSet GetRptProcedureIdByProcedureInfo(int rptProcedureId)
{
IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
con.Open();
OracleParameter[] paras = new OracleParameter[]{
new OracleParameter("in_rptProcedureId",OracleDbType.Int32,rptProcedureId,ParameterDirection.Input),
new OracleParameter("out_rptSResult",OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("out_rptTresult",OracleDbType.RefCursor, ParameterDirection.Output),
};
DataSet dsSearchReport = con.ExecStoredProcedure("PRO_MST_GetRptProcedureBYID", paras);
return dsSearchReport;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.ConnState == ConnectionState.Open)
{
con.Close();
}
}
}
#endregion
#region 系统相关
///
/// 获取服务路径
///
///
public static string GetServerPath()
{
try
{
return System.AppDomain.CurrentDomain.BaseDirectory;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 系统时间
///
///
///
public static DataSet GetSettlementTime(SUserInfo userInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select * from TP_MST_SystemDate Where SystemDateType = 5 and AccountId = " + userInfo.AccountID;
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, null);
oracleConn.Close();
if (result.Tables[0].Rows.Count == 0)
{
return null;
}
else
{
return result;
}
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 取得功能权限信息
///
/// 用户基本信息
/// DataSet
public static DataSet GetFunctionRight(SUserInfo sUserInfo)
{
DataSet dsResult = new DataSet();
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
#region 对应要执行的SQL语句
string strSql1 = "SELECT 0 Choose,FunctionCode, Functionlevel ,FunctionName,FullName,FunctionFlag,FunctionButtonFlag,0 LicensesNumber,0 UseLincenseNumber"
+ " FROM TP_SYS_Function where valueflag='1' and FunctionProgram = '1'";
DataTable dtFunction1 = oracleConn.GetSqlResultToDt(strSql1);
string strSql2 = "SELECT 0 Choose,FunctionCode, Functionlevel ,FunctionName,FullName,FunctionFlag,FunctionButtonFlag,0 LicensesNumber,0 UseLincenseNumber"
+ " FROM TP_SYS_Function where valueflag='1' and FunctionProgram = '2'";
DataTable dtFunction2 = oracleConn.GetSqlResultToDt(strSql2);
#endregion
dsResult.Tables.Add(dtFunction1);
dsResult.Tables.Add(dtFunction2);
oracleConn.Close();
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据功能取得使用该功能的用户列表
///
/// 用户基本信息
/// DataSet
public static DataSet GetFunctionUsersList(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
#region 对应要执行的SQL语句
string sqlString = "SELECT B.UserID,B.UserCode,B.UserName,C.AccountName,TP_MST_Organization.OrganizationName,A.FunctionCode,0 as AddFlag FROM TP_MST_UserRight A"
+ " INNER JOIN TP_MST_User B ON A.UserID = B.UserID"
+ " INNER JOIN TP_MST_Organization ON B.OrganizationID = TP_MST_Organization.OrganizationID "
+ " INNER JOIN TP_MST_Account C ON B.AccountID = C.AccountID "
+ " inner join tp_sys_function on a.functioncode=tp_sys_function.functioncode --and tp_sys_function.functionflag='1' "
+ " and tp_sys_function.functionprogram=1 AND C.AccountID = " + sUserInfo.AccountID
+ " WHERE B.ValueFlag > 0 "
+ " ORDER BY C.AccountName,B.UserCode,B.UserName";
#endregion
DataSet dsResult = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据功能取得使用该功能的用户列表
///
/// 用户基本信息
/// DataSet
public static DataSet GetFunctionUsersTwoList(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
#region 对应要执行的SQL语句
string sqlString = "SELECT B.UserID,B.UserCode,B.UserName,C.AccountName,TP_MST_Organization.OrganizationName,A.FunctionCode,0 as AddFlag FROM TP_MST_UserRight A"
+ " INNER JOIN TP_MST_User B ON A.UserID = B.UserID"
+ " INNER JOIN TP_MST_Organization ON B.OrganizationID = TP_MST_Organization.OrganizationID "
+ " INNER JOIN TP_MST_Account C ON B.AccountID = C.AccountID "
+ " inner join tp_sys_function on a.functioncode=tp_sys_function.functioncode --and tp_sys_function.functionflag='1' "
+ " and tp_sys_function.functionprogram=2 AND C.AccountID = " + sUserInfo.AccountID
+ " WHERE B.ValueFlag > 0 "
+ " ORDER BY C.AccountName,B.UserCode,B.UserName";
#endregion
DataSet dsResult = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 根据范围取得使用该功能的用户列表
///
/// 用户基本信息
/// DataSet
public static DataSet GetFunctionUserPurviewList(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
#region 对应要执行的SQL语句
string sqlString = "SELECT B.UserID,B.UserCode,B.UserName,TP_MST_Organization.OrganizationName,A.Purviewid,A.PURVIEWTYPE,0 as AddFlag FROM TP_MST_UserPurview A"
+ " INNER JOIN TP_MST_User B ON A.UserID = B.UserID"
+ " INNER JOIN TP_MST_Organization ON B.OrganizationID = TP_MST_Organization.OrganizationID "
+ " INNER JOIN TP_MST_Account C ON B.AccountID = C.AccountID "
+ " AND C.AccountID = " + sUserInfo.AccountID
+ " WHERE B.ValueFlag > 0 "
+ " ORDER BY B.UserCode,B.UserName";
#endregion
DataSet dsResult = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return dsResult;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取商标管理的全部数据
///
/// 用户基本信息
///
///
/// 2015.11.12 王鑫 新建
///
public static DataSet GetAllLogoInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select 0 as Sel, LogoID,LogoCode,LogoName,Remarks,ValueFlag,0 as AddFlag,isdefault,concat(LogoName||'[',LogoCode||']') as LogoNameCode,displayno, TagCode "
+ "from tp_mst_logo where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取商标管理的全部数据
///
/// 用户基本信息
///
///
/// 2015.11.12 王鑫 新建
///
public static DataSet GetLogoInfoForChange(SUserInfo sUserInfo, int goodsid)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = null;
if (sUserInfo.AccountCode == "imex")
{
sqlString = "SELECT l.LogoID\n" +
" ,l.LogoCode\n" +
" ,l.LogoName\n" +
" ,l.LogoName || '[' || l.LogoCode || ']' AS LogoNameCode\n" +
" FROM tp_mst_goodslogosap t\n" +
" INNER JOIN tp_mst_logo l\n" +
" ON l.logoid = t.logoid\n" +
" WHERE t.goodsid = :goodsid\n" +
" AND l.valueflag = '1'";
OracleParameter[] pImex = new OracleParameter[]
{
new OracleParameter(":goodsid", goodsid)
};
oracleConn.Open();
DataSet resultImex = oracleConn.GetSqlResultToDs(sqlString, pImex);
if (resultImex != null && resultImex.Tables.Count > 0 && resultImex.Tables[0].Rows.Count > 0)
{
oracleConn.Close();
return resultImex;
}
}
sqlString = "Select LogoID,LogoCode,LogoName,LogoName||'['||LogoCode||']' as LogoNameCode "
+ "from tp_mst_logo where AccountID = :AccountID and valueflag ='1' order by isdefault desc, displayno";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取商标数据源
///
/// 用户基本信息
///
///
/// 2016.05.10 陈晓野 新建
///
public static DataSet GetLogoInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select LogoID,LogoCode,LogoName,isdefault,concat(LogoName||'[',LogoCode||']') as LogoNameCode "
+ "from tp_mst_logo where AccountID = :AccountID and valueflag ='1' order by isdefault desc, displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#endregion
#region 缺陷相关
///
/// 获取缺陷类别管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2014.10.30 任海 新建
///
public static DataSet GetAllDefectTypeInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectTypeID,DefectTypeName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ " ,displayno from TP_MST_DefectType where AccountID = :AccountID";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷扣罚管理的全部数据
///
/// 用户基本信息
///
///
/// 2016.1.5 王鑫 新建
///
public static DataSet GetAllDefectFine(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectFineID,DefectFineCode,Remarks,DisplayNo,ValueFlag,0 as sel "
+ "from TP_MST_DefectFine where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷扣除数管理的全部数据
///
/// 用户基本信息
///
///
/// 2016.1.5 王鑫 新建
///
public static DataSet GetAllDefectDeduction(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select *"
+ "from TP_MST_DefectDeduction where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷扣罚关系管理的全部数据
///
/// 用户基本信息
///
///
/// 2016.1.5 王鑫 新建
///
public static DataSet GetAllDefectFineRelation(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//2020-03-03 xuwei 修改SQL按displayno排序
string sqlString = @"
SELECT *
FROM TP_MST_DEFECTFINERELATION
INNER JOIN TP_MST_DEFECTFINE ON TP_MST_DEFECTFINERELATION.DEFECTFINEID=TP_MST_DEFECTFINE.DEFECTFINEID
WHERE TP_MST_DEFECTFINE.VALUEFLAG='1'
ORDER BY TP_MST_DEFECTFINE.DISPLAYNO
";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取缺陷扣除数关系管理的全部数据
///
/// 用户基本信息
///
///
/// 2016.1.5 王鑫 新建
///
public static DataSet GetAllDefectDeductionRelation(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//2020-03-03 xuwei 增加排序
string sqlString = @"
SELECT *
FROM TP_MST_DEFECTDEDUCTIONRELATION
LEFT JOIN TP_MST_DEFECTDEDUCTION ON TP_MST_DEFECTDEDUCTIONRELATION.DEFECTDEDUCTIONID=TP_MST_DEFECTDEDUCTION.DEFECTDEDUCTIONID
ORDER BY TP_MST_DEFECTDEDUCTION.DISPLAYNO
";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#region 半成品缺陷及位置
///
/// 获取半成品缺陷管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.06.22 王鑫 新建
///
public static DataSet GetAllSemicheckDefect(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectID,DefectCode,DefectName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,"
+ " UpdateTime,UpdateUserID,OPTimeStamp,displayno, concat(tp_mst_semicheckdefect.DefectCode||'->',tp_mst_semicheckdefect.DefectName) as DefectFlagName from tp_mst_semicheckdefect where AccountID =:accountID"
+ " order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取半成品缺陷位置管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.06.22 王鑫 新建
///
public static DataSet GetAllScdefectPosition(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = "Select DefectPositionID,DefectPositionCode,DefectPositionName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,"
+ " UpdateTime,UpdateUserID,OPTimeStamp,displayno, concat(tp_mst_scdefectposition.DefectPositionCode||'->',tp_mst_scdefectposition.DefectPositionName) as DefectPositionFlagName from tp_mst_scdefectposition where AccountID =:accountID"
+ " order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取半检及复检状态数据
///
/// DataSet
///
/// 2016.06.25 王鑫 新建
///
public static DataSet GetSemiCheckType()
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
string sqlString = @"select TP_SYS_SemiCheckType.Semichecktypeid,TP_SYS_SemiCheckType.Semichecktypename,
TP_SYS_SemiCheckType.Displayno,1 as Semichecktype from TP_SYS_SemiCheckType
union
select TP_SYS_ReSemiCheckType.Resemichecktypeid,TP_SYS_ReSemiCheckType.Resemichecktypename,
TP_SYS_ReSemiCheckType.Displayno, 2 as Semichecktype from TP_SYS_ReSemiCheckType";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#endregion
#endregion
#region 工艺管理
///
/// 获取工艺管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.07.19 王鑫 新建
///
public static DataSet GetAllTecDepInfo(SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
//获取窑炉管理数据
string sqlString = "Select ID,Name,TypeFlag,TechnologyFlag,DisplayNo,Remarks,ValueFlag " +
"from TP_MST_TecDep where AccountID = :AccountID order by DisplayNo";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取配置管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.07.19 王鑫 新建
///
public static DataSet GetTransfer(TecDepEntity tecDepEntity, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
List parameters = new List();
parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
//获取管理数据
string sqlString = @"Select T.PTID,
T.Name,
P.ProductionLineName,
T.GroutingDateBegin,
T.DisplayNo,
T.Remarks,
T.ValueFlag
from TP_MST_RPT_Transfer T
left join TP_PC_ProductionLine P
on T.LineID=P.ProductionLineID
where T.AccountID = :AccountID ";
if (!string.IsNullOrEmpty(tecDepEntity.Name))
{
sqlString = sqlString + " AND instr(T.Name,:Name)>0";
parameters.Add(new OracleParameter(":Name", OracleDbType.NVarchar2, tecDepEntity.Name, ParameterDirection.Input));
}
if (!string.IsNullOrEmpty(tecDepEntity.LineIDS))
{
sqlString = sqlString + " AND instr(','||:LineIDS||',',','||P.ProductionLineID||',')>0 ";
parameters.Add(new OracleParameter(":LineIDS", OracleDbType.NVarchar2, tecDepEntity.LineIDS, ParameterDirection.Input));
}
if (!string.IsNullOrEmpty(tecDepEntity.Remarks))
{
sqlString = sqlString + " AND instr(T.Remarks,:Remarks)>0";
parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, tecDepEntity.Remarks, ParameterDirection.Input));
}
if (tecDepEntity.ValueFlag != 2) // 不等于全部
{
sqlString = sqlString + " AND T.ValueFlag=:ValueFlag";
parameters.Add(new OracleParameter(":ValueFlag", OracleDbType.Int32, tecDepEntity.ValueFlag, ParameterDirection.Input));
}
sqlString += " order by DisplayNo desc";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, parameters.ToArray());
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取配置明细管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.07.19 王鑫 新建
///
public static DataSet GetTransferInfo(int PTID, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
List parameters = new List();
parameters.Add(new OracleParameter(":PTID", OracleDbType.Int32, PTID, ParameterDirection.Input));
//获取管理数据
string sqlString = @"Select P1.ProcedureName,
P2.ProcedureName as PerProcedureName,
OutTecDep.Name,
InTecDep.Name as InName,
TS.DisplayNo
from TP_MST_RPT_TransferSetting TS
left join tp_pc_procedure P1
on TS.ProcedureID=P1.ProcedureID
left join tp_pc_procedure P2
on TS.PERPROCEDUREID=P2.ProcedureID
left join TP_MST_TecDep OutTecDep
on TS.OUTTECDEPID=OutTecDep.ID
left join TP_MST_TecDep InTecDep
on TS.INTECDEPID=InTecDep.ID
where TS.PTID = :PTID order by TS.DisplayNo";
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, parameters.ToArray());
oracleConn.Close();
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取配置编辑明细管理的全部数据
///
/// 用户基本信息
/// DataSet
///
/// 2016.07.19 王鑫 新建
///
public static DataSet GetTransferEditInfo(int PTID, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
List parameters = new List();
parameters.Add(new OracleParameter(":PTID", OracleDbType.Int32, PTID, ParameterDirection.Input));
//获取管理数据
string sqlString = @"
Select T.PTID,
T.Name,
T.LineID,
P.ProductionLineName,
T.GroutingDateBegin,
T.DisplayNo,
T.Remarks,
T.ValueFlag
from TP_MST_RPT_Transfer T
left join TP_PC_ProductionLine P
on T.LineID=P.ProductionLineID
where T.PTID = :PTID
";
string sqlString2 = @"Select
TS.PROCEDUREID,
TS.PERPROCEDUREID,
TS.OUTTECDEPID,
TS.INTECDEPID,
P1.ProcedureName,
P2.ProcedureName as PerProcedureName,
OutTecDep.Name as OutName,
InTecDep.Name as InName,
TS.DisplayNo
from TP_MST_RPT_TransferSetting TS
left join tp_pc_procedure P1
on TS.ProcedureID=P1.ProcedureID
left join tp_pc_procedure P2
on TS.PERPROCEDUREID=P2.ProcedureID
left join TP_MST_TecDep OutTecDep
on TS.OUTTECDEPID=OutTecDep.ID
left join TP_MST_TecDep InTecDep
on TS.INTECDEPID=InTecDep.ID
where TS.PTID = :PTID order by TS.DisplayNo";
oracleConn.Open();
DataSet ds = new DataSet();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, parameters.ToArray());
result.Tables[0].TableName = "TP_MST_RPT_Transfer";
ds.Tables.Add(result.Tables[0].Copy());
DataSet result2 = oracleConn.GetSqlResultToDs(sqlString2, parameters.ToArray());
result2.Tables[0].TableName = "TP_MST_RPT_TransferSetting";
ds.Tables.Add(result2.Tables[0].Copy());
oracleConn.Close();
return ds;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#endregion
#region 条码打印
///
/// 查询模板一览
///
///
///
public static ServiceResultEntity GetBarCodePrintLayoutList(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.width\n" +
" ,t.height\n" +
" ,gt.goodstypename\n" +
" ,gt.goodstypecode\n" +
" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logonamecode\n" +
" ,t.remarks\n" +
" ,t.valueflag\n" +
" ,t.PrintType\n" + //xuwei add 2019-11-20
" ,s.PrintTypeName\n" + //xuwei add 2019-11-20
" FROM tp_mst_barcodeprintlayout t\n" +
" INNER JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
" LEFT JOIN tp_mst_Logo logo\n" +
" ON logo.logoid = t.logoid\n" +
" left join TP_SYS_BARCODEPRINTTYPE s on t.printtype = s.printtypeid \n" + //xuwei add 2019-11-20
" WHERE t.accountid = :accountid\n" +
" AND (:layoutname IS NULL OR instr(t.layoutname, :layoutname) > 0)\n" +
" AND (:goodstypecode IS NULL OR\n" +
" instr(gt.goodstypecode, :goodstypecode) = 1)\n" +
" AND (:remarks IS NULL OR instr(t.remarks, :remarks) > 0)\n" +
" AND (:valueflag IS NULL OR t.valueflag = :valueflag)\n" +
" AND (:logos IS NULL OR instr(:logos, ','|| t.logoid ||',') > 0)\n" +
" ORDER BY gt.goodstypecode, decode(logo.logocode, null, ' ',logo.logocode)";
List parameters = new List();
parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutname", OracleDbType.NVarchar2, cre.Properties["layoutname"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":goodstypecode", OracleDbType.NVarchar2, cre.Properties["goodstypecode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":valueflag", OracleDbType.NVarchar2, cre.Properties["valueflag"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":logos", OracleDbType.NVarchar2, cre.Properties["logos"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, 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 (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 验证产品类别是否已存在模板
///
///
///
public static ServiceResultEntity IsExistBarCodePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" FROM tp_mst_barcodeprintlayout t\n" +
" WHERE t.goodstypeid = :goodstypeid\n" +
" AND ((:printtype is null and t.printtype is null) or (t.printtype = :printtype))\n" + //xuwei add 2019-11-20
" AND ((:logoid is null and t.logoid is null) or (t.logoid = :logoid))\n" +
" AND (:layoutid is null or :layoutid <> t.layoutid)";
List parameters = new List();
//parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":printtype", OracleDbType.Int32, cre.Properties["PrintType"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":goodstypeid", OracleDbType.Int32, cre.Properties["GoodsTypeID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["layoutid"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":logoid", OracleDbType.Int32, cre.Properties["LogoID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Result = false;
return sre;
}
sre.Result = true;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取打印项目
///
///
///
public static ServiceResultEntity GetBarCodePrintItem(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT s.itemcode\n" +
" ,s.itemname\n" +
" ,s.itemsample\n" +
" ,s.itemstyle\n" +
" ,s.controlcode\n" +
" ,s.displayno\n" +
" FROM tp_sys_barcodeprintitem s\n" +
" ORDER BY s.displayno";
DataSet data = oracleConn.GetSqlResultToDs(sqlString, null);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = data;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 查询打印模板
///
///
///
public static ServiceResultEntity GetBarCodePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.layoutdata\n" +
" ,t.goodstypeid\n" +
" ,gt.goodstypecode\n" +
" ,gt.goodstypename\n" +
" ,t.logoid\n" +
" ,t.printtype\n" + //xuwei add 2019-11-20
" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logoName\n" +
//" ,logo.logoName || '[' || logo.logocode || ']' logoName\n" +
" ,t.width\n" +
" ,t.height\n" +
" ,t.remarks\n" +
" FROM tp_mst_barcodeprintlayout t\n" +
" INNER JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
" LEFT JOIN tp_mst_logo logo\n" +
" ON logo.logoid = t.logoid\n" +
" WHERE t.layoutid = :layoutid";
List parameters = new List();
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
string sqlString1 =
"SELECT t.layoutid\n" +
" ,t.layoutitemid\n" +
" ,t.itemcode\n" +
" ,s.itemname\n" +
" ,s.itemsample\n" +
" ,s.itemstyle\n" +
" ,s.controlcode\n" +
" FROM tp_mst_barcodeprintitem t\n" +
" INNER JOIN tp_sys_barcodeprintitem s\n" +
" ON s.itemcode = t.itemcode\n" +
" WHERE t.layoutid = :layoutid";
DataTable data1 = oracleConn.GetSqlResultToDt(sqlString1, parameters.ToArray());
sre.Data.Tables.Add(data1);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 新建打印模板
///
///
///
public static ServiceResultEntity SaveAddBarCodePrintLayoutData(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
DataRow layoutdata = cre.Data.Tables[0].Rows[0];
string sqlid = "select SEQ_MST_BARCODEPRINTLAYOUT_ID.Nextval from dual";
int layoutID = Convert.ToInt32(oracleTrConn.GetSqlResultToObj(sqlid));
ServiceResultEntity sre = new ServiceResultEntity();
sre.Result = layoutID;
string sqlString = "INSERT INTO TP_MST_BARCODEPRINTLAYOUT\n" +
" (LAYOUTID\n" +
" ,LAYOUTNAME\n" +
" ,LAYOUTDATA\n" +
" ,GOODSTYPEID\n" +
" ,GOODSTYPECODE\n" +
" ,LOGOID\n" +
" ,PRINTTYPE\n" + //xuwei add 2019-11-20
" ,WIDTH\n" +
" ,HEIGHT\n" +
" ,REMARKS\n" +
" ,ACCOUNTID\n" +
" ,CREATEUSERID\n" +
" ,UPDATEUSERID)\n" +
"VALUES\n" +
" (:LAYOUTID\n" +
" ,:LAYOUTNAME\n" +
" ,:LAYOUTDATA\n" +
" ,:GOODSTYPEID\n" +
" ,:GOODSTYPECODE\n" +
" ,:LOGOID\n" +
" ,:PRINTTYPE\n" + //xuwei add 2019-11-20
" ,:WIDTH\n" +
" ,:HEIGHT\n" +
" ,:REMARKS\n" +
" ,:ACCOUNTID\n" +
" ,:CREATEUSERID\n" +
" ,:CREATEUSERID)";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTNAME", OracleDbType.NVarchar2, layoutdata["LAYOUTNAME"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTDATA", OracleDbType.Blob, layoutdata["LAYOUTDATA"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPEID", OracleDbType.Int32, layoutdata["GOODSTYPEID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPECODE", OracleDbType.NVarchar2, layoutdata["GOODSTYPECODE"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LOGOID", OracleDbType.Int32, layoutdata["LOGOID"], ParameterDirection.Input));
//xuwei add 2019-11-20
parameters.Add(new OracleParameter(":PRINTTYPE", OracleDbType.Int32, layoutdata["PRINTTYPE"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":WIDTH", OracleDbType.Int32, layoutdata["WIDTH"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":HEIGHT", OracleDbType.Int32, layoutdata["HEIGHT"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":REMARKS", OracleDbType.NVarchar2, layoutdata["REMARKS"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
// 插入新的项目明细
foreach (DataRow dataRow in cre.Data.Tables[1].Rows)
{
if (dataRow.RowState == DataRowState.Deleted ||
dataRow.RowState == DataRowState.Detached)
{
continue;
}
sqlString = "INSERT INTO TP_MST_BARCODEPRINTITEM"
+ "(LayoutID"
+ ",LayoutItemID"
+ ",ItemCode"
+ ")"
+ "VALUES"
+ " (:LayoutID"
+ ",:layoutItemID"
+ ",:ItemCode"
+ ")";
parameters.Clear();
parameters.Add(new OracleParameter(":LayoutID", OracleDbType.Int32, layoutID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutItemID", OracleDbType.Int32, dataRow["layoutItemID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ItemCode", OracleDbType.NVarchar2, dataRow["ItemCode"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 编辑打印模板
///
///
///
public static ServiceResultEntity SaveEditBarCodePrintLayoutData(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
DataRow layoutdata = cre.Data.Tables[0].Rows[0];
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "UPDATE TP_MST_BARCODEPRINTLAYOUT t\n" +
" SET t.LAYOUTDATA = :LAYOUTDATA, t.WIDTH = :WIDTH, t.HEIGHT = :HEIGHT, t.UPDATEUSERID=:UPDATEUSERID\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTDATA", OracleDbType.Blob, layoutdata["LAYOUTDATA"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":WIDTH", OracleDbType.Int32, layoutdata["WIDTH"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":HEIGHT", OracleDbType.Int32, layoutdata["HEIGHT"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
sre.Result = result;
sqlString = "DELETE FROM TP_MST_BARCODEPRINTITEM WHERE LAYOUTID = :LAYOUTID";
parameters.Clear();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
// 插入新的项目明细
foreach (DataRow dataRow in cre.Data.Tables[1].Rows)
{
if (dataRow.RowState == DataRowState.Deleted ||
dataRow.RowState == DataRowState.Detached)
{
continue;
}
sqlString = "INSERT INTO TP_MST_BARCODEPRINTITEM"
+ "(LayoutID"
+ ",LayoutItemID"
+ ",ItemCode"
+ ")"
+ "VALUES"
+ " (:LayoutID"
+ ",:layoutItemID"
+ ",:ItemCode"
+ ")";
parameters.Clear();
parameters.Add(new OracleParameter(":LayoutID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutItemID", OracleDbType.Int32, dataRow["layoutItemID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ItemCode", OracleDbType.NVarchar2, dataRow["ItemCode"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 查询打印模板信息
///
///
///
public static ServiceResultEntity GetBarCodePrintLayoutInfo(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.valueflag\n" +
" ,t.goodstypeid\n" +
" ,gt.goodstypecode\n" +
" ,gt.goodstypename\n" +
" ,t.logoid\n" +
" ,t.printtype\n" + //xuwei add 2019-11-20
" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logoNameCode\n" +
//" ,logo.logoName || '[' || logo.logocode || ']' logoNameCode\n" +
" ,t.remarks\n" +
" FROM tp_mst_barcodeprintlayout t\n" +
" INNER JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
" LEFT JOIN tp_mst_Logo logo\n" +
" ON logo.logoid = t.logoid\n" +
" WHERE t.layoutid = :layoutid";
List parameters = new List();
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 编辑打印模板信息
///
///
///
public static ServiceResultEntity SaveEditBarCodePrintLayoutInfo(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
//DataRow layoutdata = cre.Data.Tables[0].Rows[0];
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "UPDATE TP_MST_BARCODEPRINTLAYOUT t\n" +
" SET t.LAYOUTNAME = :LAYOUTNAME, t.GOODSTYPEID = :GOODSTYPEID, t.GOODSTYPECODE = :GOODSTYPECODE\n" +
" ,t.REMARKS = :REMARKS, t.Valueflag = :Valueflag, t.UPDATEUSERID=:UPDATEUSERID\n" +
" ,t.LogoID = :LogoID\n" +
" ,t.PrintType = :PrintType\n" + //xuwei add 2019-11-20
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTNAME", OracleDbType.NVarchar2, cre.Properties["LayoutName"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPEID", OracleDbType.Int32, cre.Properties["GoodsTypeID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPECODE", OracleDbType.NVarchar2, cre.Properties["GoodsTypeCode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":Valueflag", OracleDbType.NVarchar2, cre.Properties["ValueFlag"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":LogoID", OracleDbType.Int32, cre.Properties["LogoID"], ParameterDirection.Input));
//xuwei add 2019-11-20
parameters.Add(new OracleParameter(":PrintType", OracleDbType.Int32, cre.Properties["PrintType"], ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 删除条码打印模板
///
///
///
public static ServiceResultEntity DeleteBarCodePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "DELETE FROM TP_MST_BarCodePrintItem t\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
string sqlString1 = "DELETE FROM TP_MST_BARCODEPRINTLAYOUT t\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
// 删除明细
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
// 删除总单
result = oracleTrConn.ExecuteNonQuery(sqlString1, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 获取条码打印信息（模板，数据）
///
///
///
public static ServiceResultEntity GetBarCodePrintDATA(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string barcode = cre.Properties["Barcode"].ToString();
bool isGBarcode = (bool)cre.Properties["IsGBarcode"];
if (!isGBarcode)
{
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 = oracleConn.GetSqlResultToStr(sqlString, paras1);
}
ServiceResultEntity sre = new ServiceResultEntity();
List parameters = new List();
parameters.Add(new OracleParameter("in_Barcode", OracleDbType.NVarchar2, barcode, ParameterDirection.Input));
parameters.Add(new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter("in_UserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter("out_Status", OracleDbType.Int32, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_Message", OracleDbType.NVarchar2, 2000, null, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_LayoutData", OracleDbType.RefCursor, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_PrintData", OracleDbType.RefCursor, ParameterDirection.Output));
DataSet data = oracleConn.ExecStoredProcedure("PRO_PM_GETBarCodePrintDATA", parameters.ToArray());
int status = Convert.ToInt32(parameters[3].Value.ToString());
if (status < 0)
{
sre.Status = Constant.ServiceResultStatus.Other;
sre.Message = parameters[4].Value.ToString();
}
sre.Data = data;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 记录条码打印日志
///
///
///
public static ServiceResultEntity SetBarCodePrintLog(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "INSERT INTO TP_MST_BarCodePrintLog\n" +
" (BARCODE, LAYOUTID, ACCOUNTID, CREATEUSERID)\n" +
"VALUES\n" +
" (:BARCODE, :LAYOUTID, :ACCOUNTID, :CREATEUSERID)";
List parameters = new List();
parameters.Add(new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["Barcode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 获取打印类型列表 xuwei add 2019-11-20
///
///
///
public static ServiceResultEntity GetPrintType(SUserInfo sUserInfo=null)
{
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Status = Constant.ServiceResultStatus.Success;
IDBTransaction conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
string sqlStr = "";
try
{
conn.Connect();
//获取打印类型
if (sre.Status == Constant.ServiceResultStatus.Success)
{
sqlStr = $@"
SELECT
PRINTTYPEID,
PRINTTYPENAME
FROM
TP_SYS_BARCODEPRINTTYPE
ORDER BY
DISPLAYNO
";
DataTable GroutingLineBatchNo = conn.GetSqlResultToDt(sqlStr);
GroutingLineBatchNo.TableName = "PrintType";
sre.Data.Tables.Add(GroutingLineBatchNo);
if (GroutingLineBatchNo.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
sre.Message = "没有查询结果！";
}
else
{
sre.Status = Constant.ServiceResultStatus.Success;
sre.Message = "操作成功！";
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Disconnect();
}
//返回数据
return sre;
}
#endregion
#region 工号分组（缺陷类型）
///
/// 获取工号分组一览
///
///
///
public static ServiceResultEntity GetWorkerGroupList(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
List parameters = new List();
if (cre.Properties["WorkerGroupDetail"] == null)
{
string sqlString =
"SELECT wg.workergroupid\n" +
" ,wg.workergroupname\n" +
" ,wg.remarks\n" +
" ,wg.displayno\n" +
" ,wg.valueflag\n" +
" ,wg.defecttypeid\n" +
" ,dt.defecttypename\n" +
" FROM TP_MST_WorkerGroup wg\n" +
" LEFT JOIN tp_mst_defecttype dt\n" +
" ON dt.defecttypeid = wg.defecttypeid\n" +
" WHERE wg.accountid = :accountid\n" +
" AND (:workergroupid IS NULL OR wg.workergroupid = :workergroupid)\n" +
" AND (:GroupName IS NULL OR instr(wg.workergroupname, :GroupName) > 0)\n" +
" AND (:DefectTypes IS NULL OR\n" +
" instr(:DefectTypes, ','||dt.defecttypeid||',') > 0)\n" +
" AND (:remarks IS NULL OR instr(wg.remarks, :remarks) > 0)\n" +
" AND (:valueflag IS NULL OR wg.valueflag = :valueflag)\n" +
" ORDER BY dt.displayno, dt.defecttypeid, wg.displayno, wg.workergroupid";
parameters.Add(new OracleParameter(":workergroupid", OracleDbType.Int32, cre.Properties["GroupID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":GroupName", OracleDbType.NVarchar2, cre.Properties["GroupName"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":DefectTypes", OracleDbType.NVarchar2, cre.Properties["DefectTypes"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":valueflag", OracleDbType.NVarchar2, cre.Properties["valueflag"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
// 查询
if (cre.Properties["GroupID"] == null)
{
if (data == null || data.Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoSearchResults;
return sre;
}
cre.Properties["GroupID"] = data.Rows[0]["workergroupid"];
}
sre.Data.Tables.Add(data);
}
string sqlUser =
"SELECT u.userid, u.usercode, u.username\n" +
" FROM TP_MST_WorkerGroupDetail wgd\n" +
" LEFT JOIN tp_mst_user u\n" +
" ON u.userid = wgd.workeruserid\n" +
" WHERE wgd.workergroupid = :workergroupid\n" +
" ORDER BY u.usercode";
parameters.Clear();
parameters.Add(new OracleParameter(":workergroupid", OracleDbType.Int32, cre.Properties["GroupID"], ParameterDirection.Input));
DataTable dataUser = oracleConn.GetSqlResultToDt(sqlUser, parameters.ToArray());
sre.Data.Tables.Add(dataUser);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 停用、启用
///
///
///
public static ServiceResultEntity SetWorkerGroupValueFlag(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "UPDATE TP_MST_WorkerGroup SET ValueFlag = :ValueFlag, UpdateUserID = :UpdateUserID " +
" WHERE workergroupid = :workergroupid";
List parameters = new List();
parameters.Add(new OracleParameter(":ValueFlag", OracleDbType.NVarchar2, cre.Properties["ValueFlag"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":UpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":workergroupid", OracleDbType.Int32, cre.Properties["GroupID"], ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 保存
///
///
///
public static ServiceResultEntity SetWorkerGroup(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
DataRow info = cre.Data.Tables[0].Rows[0];
int groupid = Convert.ToInt32(info["workergroupid"]);
List parameters = new List();
if (groupid > 0)
{
// 编辑
string sqlStringEdit =
"UPDATE TP_MST_WorkerGroup wg\n" +
" SET wg.workergroupname = :workergroupname\n" +
" ,wg.defecttypeid = :defecttypeid\n" +
" ,wg.remarks = :remarks\n" +
" ,wg.displayno = :displayno\n" +
" ,wg.updateuserid = :updateuserid\n" +
" WHERE wg.workergroupid = :workergroupid";
parameters.Add(new OracleParameter(":workergroupname", OracleDbType.NVarchar2, info["workergroupname"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":defecttypeid", OracleDbType.Int32, info["defecttypeid"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":remarks", OracleDbType.NVarchar2, info["remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":displayno", OracleDbType.Int32, info["displayno"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":updateuserid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":workergroupid", OracleDbType.Int32, groupid, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlStringEdit, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
sqlStringEdit = "DELETE FROM TP_MST_WorkerGroupDetail wgd WHERE wgd.workergroupid = :workergroupid";
parameters.Clear();
parameters.Add(new OracleParameter(":workergroupid", OracleDbType.Int32, groupid, ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlStringEdit, parameters.ToArray());
}
else
{
string seqSql = "select SEQ_MST_WORKERGROUP_ID.NEXTVAL from dual";
groupid = Convert.ToInt32(oracleTrConn.GetSqlResultToObj(seqSql));
// 新建
string sqlStringAdd =
"INSERT INTO TP_MST_WORKERGROUP\n" +
" (WORKERGROUPID\n" +
" ,WORKERGROUPNAME\n" +
" ,DEFECTTYPEID\n" +
" ,REMARKS\n" +
" ,DISPLAYNO\n" +
" ,ACCOUNTID\n" +
" ,VALUEFLAG\n" +
" ,CREATEUSERID\n" +
" ,UPDATEUSERID)\n" +
"VALUES\n" +
" (:WORKERGROUPID\n" +
" ,:WORKERGROUPNAME\n" +
" ,:DEFECTTYPEID\n" +
" ,:REMARKS\n" +
" ,:DISPLAYNO\n" +
" ,:ACCOUNTID\n" +
" ,'1'\n" +
" ,:USERID\n" +
" ,:USERID)";
parameters.Add(new OracleParameter(":WORKERGROUPID", OracleDbType.Int32, groupid, ParameterDirection.Input));
parameters.Add(new OracleParameter(":WORKERGROUPNAME", OracleDbType.NVarchar2, info["workergroupname"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":DEFECTTYPEID", OracleDbType.Int32, info["defecttypeid"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":REMARKS", OracleDbType.NVarchar2, info["remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":DISPLAYNO", OracleDbType.Int32, info["displayno"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlStringAdd, parameters.ToArray());
}
string sqlString1 = "INSERT INTO TP_MST_WORKERGROUPDETAIL\n" +
" (WORKERGROUPID, WORKERUSERID, ACCOUNTID, CREATEUSERID, UPDATEUSERID)\n" +
"VALUES\n" +
" (:WORKERGROUPID, :WORKERUSERID, :ACCOUNTID, '1', :USERID)";
foreach (DataRow item in cre.Data.Tables[1].Rows)
{
parameters.Clear();
parameters.Add(new OracleParameter(":WORKERGROUPID", OracleDbType.Int32, groupid, ParameterDirection.Input));
parameters.Add(new OracleParameter(":WORKERUSERID", OracleDbType.NVarchar2, item["USERID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString1, parameters.ToArray());
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
#endregion
#region 条码打印机（PDA用）
///
/// 获取条码打印机（PDA用）
///
///
///
///
public static ServiceResultEntity GetBarcodePrinter(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select PrinterID,PrinterName,Remarks,displayno "
+ " ,PrintType " //xuwei add 2019-11-21
+ " from TP_MST_BarCodePrinter where AccountID = :AccountID and valueflag = '1' order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取条码打印机（PDA用）
///
///
///
///
public static ServiceResultEntity GetAllBarcodePrinter(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select PrinterID,PrinterName,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ " ,PrintType"
+ " ,displayno from TP_MST_BarCodePrinter where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 保存条码打印机（PDA用）
///
/// int
public static ServiceResultEntity SaveBarcodePrinter(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
// 检验参数的有效性
if (cre == null || cre.Data == null || cre.Data.Tables.Count == 0 || cre.Data.Tables[0].Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Connect();
#region 对要保存的数据进行必要的验证
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
// 新建
if (dataRow.RowState == DataRowState.Added)
{
#region 判断是否存在相同的
string sqlString = "SELECT Count(*) FROM TP_MST_BarCodePrinter WHERE AccountID = :AccountID and PrinterName =:PrinterName ";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":PrinterName",dataRow["PrinterName"].ToString())
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlString, oracleParameter);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["PrinterName"].ToString();
return sre;
}
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 判断是否存在相同的
string sqlStrings = "SELECT Count(*) FROM TP_MST_BarCodePrinter WHERE AccountID = :AccountID and PrinterName = :PrinterName and PrinterID <> :PrinterID";
OracleParameter[] oracleParameters = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":PrinterName",dataRow["PrinterName"]),
new OracleParameter(":PrinterID",dataRow["PrinterID"])
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlStrings, oracleParameters);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["PrinterName"].ToString();
return sre;
}
#endregion
}
}
#endregion
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
// 新建
if (dataRow.RowState == DataRowState.Added)
{
#region 新增信息
string sqlInsertString = "INSERT INTO TP_MST_BarCodePrinter"
+ "(PrinterName"
+ ",DisplayNo"
+ ",PrintType" //xuwei add 2019-11-21
+ ",Remarks"
+ ",AccountID"
+ ",ValueFlag"
+ ",UpdateUserID"
+ ",CreateUserID)"
+ " VALUES "
+ "(:PrinterName"
+ ",:DisplayNo"
+ ",:PrintType" //xuwei add 2019-11-21
+ ",:Remarks"
+ ",:AccountID"
+ ",:ValueFlag"
+ ",:UserID"
+ ",:UserID)";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PrinterName",dataRow["PrinterName"].ToString()),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":PrintType",dataRow["PrintType"]),
new OracleParameter(":Remarks",dataRow["Remarks"].ToString()),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"].ToString()),
new OracleParameter(":UserID",sUserInfo.UserID),
};
oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 更新信息
string sqlUpdateString = "UPDATE TP_MST_BarCodePrinter SET "
+ " PrinterName = :PrinterName,"
+ " DisplayNo = :DisplayNo,"
+ " PrintType = :PrintType," //xuwei add 2019-11-21
+ " Remarks = :Remarks,"
+ " AccountID = :AccountID,"
+ " ValueFlag = :ValueFlag,"
+ " UpdateUserID = :UpdateUserID"
+ " WHERE PrinterID = :PrinterID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PrinterName",dataRow["PrinterName"]),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":PrintType",dataRow["PrintType"]), //xuwei add 2019-11-21
new OracleParameter(":Remarks",dataRow["Remarks"]),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"]),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
new OracleParameter(":PrinterID",dataRow["PrinterID"])
};
oracleTrConn.ExecuteNonQuery(sqlUpdateString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Deleted)
{
#region 删除信息
string sqlDeleteString = "DELETE TP_MST_BarCodePrinter WHERE PrinterID = :PrinterID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PrinterID",dataRow["PrinterID",DataRowVersion.Original].ToString())
};
oracleTrConn.ExecuteNonQuery(sqlDeleteString, oracleParameter);
#endregion
}
}
oracleTrConn.Commit();
oracleTrConn.Disconnect();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
throw ex;
}
}
#endregion
#region PLC连接参数
///
/// 获取PLC连接参数
///
///
///
///
public static ServiceResultEntity GetPLC(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select PLCID,PLCName,IP, PORT, PLCDescription, Remarks,displayno "
+ " from TP_MST_PLC where AccountID = :AccountID and valueflag = '1' order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取PLC连接参数
///
///
///
///
public static ServiceResultEntity GetAllPLC(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select PLCID,PLCName,IP, PORT, PLCDescription,Remarks,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ " ,displayno from TP_MST_PLC where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取PLC连接参数
///
/// int
public static ServiceResultEntity SavePLC(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
// 检验参数的有效性
if (cre == null || cre.Data == null || cre.Data.Tables.Count == 0 || cre.Data.Tables[0].Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Connect();
#region 对要保存的数据进行必要的验证
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
if (dataRow.RowState == DataRowState.Added)
{
#region 判断是否存在相同的产品缺陷类别名称
string sqlString = "SELECT Count(*) FROM TP_MST_PLC WHERE AccountID = :AccountID and PLCName =:PLCName ";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":PLCName",dataRow["PLCName"].ToString())
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlString, oracleParameter);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["PLCName"].ToString();
return sre;
}
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 判断是否存在相同的产品缺陷类别名称
string sqlStrings = "SELECT Count(*) FROM TP_MST_PLC WHERE AccountID = :AccountID and PLCName = :PLCName and PLCID <> :PLCID";
OracleParameter[] oracleParameters = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":PLCName",dataRow["PLCName"]),
new OracleParameter(":PLCID",dataRow["PLCID"])
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlStrings, oracleParameters);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["PLCName"].ToString();
return sre;
}
#endregion
}
}
#endregion
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
// 新建
if (dataRow.RowState == DataRowState.Added)
{
#region 新增信息
string sqlInsertString = "INSERT INTO TP_MST_PLC"
+ "(PLCName"
+ ",IP"
+ ",Port"
+ ",PLCDescription"
+ ",DisplayNo"
+ ",Remarks"
+ ",AccountID"
+ ",ValueFlag"
+ ",UpdateUserID"
+ ",CreateUserID)"
+ " VALUES "
+ "(:PLCName"
+ ",:IP"
+ ",:Port"
+ ",:PLCDescription"
+ ",:DisplayNo"
+ ",:Remarks"
+ ",:AccountID"
+ ",:ValueFlag"
+ ",:UserID"
+ ",:UserID)";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PLCName",dataRow["PLCName"].ToString()),
new OracleParameter(":IP",dataRow["IP"]),
new OracleParameter(":Port",dataRow["Port"]),
new OracleParameter(":PLCDescription",dataRow["PLCDescription"]),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":Remarks",dataRow["Remarks"].ToString()),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"].ToString()),
new OracleParameter(":UserID",sUserInfo.UserID),
};
oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 更新信息
string sqlUpdateString = "UPDATE TP_MST_PLC SET "
+ " PLCName = :PLCName,"
+ " IP = :IP,"
+ " Port = :Port,"
+ " PLCDescription = :PLCDescription,"
+ " DisplayNo = :DisplayNo,"
+ " Remarks = :Remarks,"
+ " AccountID = :AccountID,"
+ " ValueFlag = :ValueFlag,"
+ " UpdateUserID = :UpdateUserID"
+ " WHERE PLCID = :PLCID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PLCName",dataRow["PLCName"].ToString()),
new OracleParameter(":IP",dataRow["IP"]),
new OracleParameter(":Port",dataRow["Port"]),
new OracleParameter(":PLCDescription",dataRow["PLCDescription"]),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":Remarks",dataRow["Remarks"]),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"]),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
new OracleParameter(":PLCID",dataRow["PLCID"])
};
oracleTrConn.ExecuteNonQuery(sqlUpdateString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Deleted)
{
#region 删除信息
string sqlDeleteString = "DELETE TP_MST_PLC WHERE PLCID = :PLCID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":PLCID",dataRow["PLCID",DataRowVersion.Original].ToString())
};
oracleTrConn.ExecuteNonQuery(sqlDeleteString, oracleParameter);
#endregion
}
}
oracleTrConn.Commit();
oracleTrConn.Disconnect();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
throw ex;
}
}
#endregion
#region 模具材料供应商
///
/// 获取模具材料供应商
///
///
///
///
public static ServiceResultEntity GetMouldMaterialSuppliers(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select SupplierID,SupplierName,Remarks,displayno "
+ " from TP_MST_MouldMaterialSuppliers where AccountID = :AccountID and valueflag = '1' order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 获取模具材料供应商
///
///
///
///
public static ServiceResultEntity GetAllMouldMaterialSuppliers(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "Select SupplierID,SupplierName,Remarks,displayno,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
+ " from TP_MST_MouldMaterialSuppliers where AccountID = :AccountID order by displayno";
Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[]
{
new Oracle.ManagedDataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
};
oracleConn.Open();
DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
oracleConn.Close();
sre.Data = result;
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 保存模具材料供应商
///
/// int
public static ServiceResultEntity SaveMouldMaterialSuppliers(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
// 检验参数的有效性
if (cre == null || cre.Data == null || cre.Data.Tables.Count == 0 || cre.Data.Tables[0].Rows.Count == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Connect();
#region 对要保存的数据进行必要的验证
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
if (dataRow.RowState == DataRowState.Added)
{
#region 判断是否存在相同的名称
string sqlString = "SELECT Count(*) FROM TP_MST_MouldMaterialSuppliers WHERE AccountID = :AccountID and SupplierName =:SupplierName ";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":SupplierName",dataRow["SupplierName"].ToString())
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlString, oracleParameter);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["SupplierName"].ToString();
return sre;
}
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 判断是否存在相同的产品缺陷类别名称
string sqlStrings = "SELECT Count(*) FROM TP_MST_MouldMaterialSuppliers WHERE AccountID = :AccountID and SupplierName = :SupplierName and SupplierID <> :SupplierID";
OracleParameter[] oracleParameters = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":SupplierName",dataRow["SupplierName"]),
new OracleParameter(":SupplierID",dataRow["SupplierID"])
};
string sqlReturnStr = oracleTrConn.GetSqlResultToStr(sqlStrings, oracleParameters);
if (!Constant.INT_IS_ZERO.ToString().Equals(sqlReturnStr))
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
sre.Status = Constant.ServiceResultStatus.DataDuplicated;
sre.Message = dataRow["SupplierName"].ToString();
return sre;
}
#endregion
}
}
#endregion
foreach (DataRow dataRow in cre.Data.Tables[0].Rows)
{
// 新建
if (dataRow.RowState == DataRowState.Added)
{
#region 新增信息
string sqlInsertString = "INSERT INTO TP_MST_MouldMaterialSuppliers"
+ "(SupplierName"
+ ",DisplayNo"
+ ",Remarks"
+ ",AccountID"
+ ",ValueFlag"
+ ",UpdateUserID"
+ ",CreateUserID)"
+ " VALUES "
+ "(:SupplierName"
+ ",:DisplayNo"
+ ",:Remarks"
+ ",:AccountID"
+ ",:ValueFlag"
+ ",:UserID"
+ ",:UserID)";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":SupplierName",dataRow["SupplierName"].ToString()),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":Remarks",dataRow["Remarks"].ToString()),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"].ToString()),
new OracleParameter(":UserID",sUserInfo.UserID),
};
oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Modified)
{
#region 更新信息
string sqlUpdateString = "UPDATE TP_MST_MouldMaterialSuppliers SET "
+ " SupplierName = :SupplierName,"
+ " DisplayNo = :DisplayNo,"
+ " Remarks = :Remarks,"
+ " AccountID = :AccountID,"
+ " ValueFlag = :ValueFlag,"
+ " UpdateUserID = :UpdateUserID"
+ " WHERE SupplierID = :SupplierID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":SupplierName",dataRow["SupplierName"].ToString()),
new OracleParameter(":DisplayNo",dataRow["DisplayNo"]),
new OracleParameter(":Remarks",dataRow["Remarks"]),
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":ValueFlag",dataRow["ValueFlag"]),
new OracleParameter(":UpdateUserID",sUserInfo.UserID),
new OracleParameter(":SupplierID",dataRow["SupplierID"])
};
oracleTrConn.ExecuteNonQuery(sqlUpdateString, oracleParameter);
#endregion
}
else if (dataRow.RowState == DataRowState.Deleted)
{
#region 删除信息
string sqlDeleteString = "DELETE TP_MST_MouldMaterialSuppliers WHERE SupplierID = :SupplierID";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":SupplierID",dataRow["SupplierID",DataRowVersion.Original].ToString())
};
oracleTrConn.ExecuteNonQuery(sqlDeleteString, oracleParameter);
#endregion
}
}
oracleTrConn.Commit();
oracleTrConn.Disconnect();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
throw ex;
}
}
#endregion
#region 在产品备份设置
///
/// 获取在产品备份设置
///
///
///
///
public static ServiceResultEntity GetMST040101(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "SELECT JOB_NAME, REPEAT_INTERVAL, LAST_START_DATE, NEXT_RUN_DATE, ENABLED\n" +
" FROM USER_SCHEDULER_JOBS\n" +
" WHERE JOB_NAME = 'BAKUP_INPRODUCTION_BYMONTH'";
oracleConn.Open();
DataSet data = oracleConn.GetSqlResultToDs(sqlString);
sre.Data = data;
oracleConn.Close();
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
///
/// 保存在产品备份设置
///
///
///
///
public static ServiceResultEntity SetMST040101(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
// 检验参数的有效性
if (cre == null || cre.Request == null || cre.Request.ToString() == "")
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Connect();
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter("name","BAKUP_INPRODUCTION_BYMONTH"),
new OracleParameter("attribute", "repeat_interval"),
new OracleParameter("value", cre.Request.ToString())
};
oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.set_attribute", oracleParameter);
oracleParameter = new OracleParameter[]
{
new OracleParameter("name","BAKUP_INPRODUCTION_BYMONTH")
};
if (cre.Properties["Enabled"].ToString() == "1")
{
oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.enable", oracleParameter);
}
else
{
oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.disable", oracleParameter);
}
if (cre.Properties["DeleteMonthBackup"].ToString() == "1")
{
string sqlString = "delete from tp_pm_inproduction_bakbymonth inpb where inpb.backupmonth = trunc(sysdate, 'mm')";
oracleTrConn.ExecuteNonQuery(sqlString);
}
if (cre.Properties["Run"].ToString() == "1")
{
// oracleParameter = new OracleParameter[]
// {
// new OracleParameter("job_name","BAKUP_INPRODUCTION_BYMONTH"),
// new OracleParameter("use_current_session", OracleDbType., 0)
// };
//oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.run_job", oracleParameter);
string sqlString = "begin\n" +
"sys.dbms_scheduler.run_job('BAKUP_INPRODUCTION_BYMONTH', false);\n" +
"end;";
oracleTrConn.ExecuteNonQuery(sqlString);
}
oracleTrConn.Commit();
oracleTrConn.Disconnect();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
oracleTrConn.Disconnect();
}
throw ex;
}
}
#endregion
#region 升级履历
///
/// 升级履历
///
///
///
///
public static ServiceResultEntity GetSYS0211(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "SELECT ur.version, ur.releasedate, ur.upgradetime, ud.desno, ud.description\n" +
" FROM t_sys_upgraderecord ur\n" +
" LEFT JOIN t_sys_upgradedescription ud\n" +
" ON ud.releaseid = ur.releaseid\n" +
" WHERE ur.releaseid <> 0\n" +
" ORDER BY ur.releaseid DESC, ud.desno";
oracleConn.Open();
DataSet data = oracleConn.GetSqlResultToDs(sqlString);
sre.Data = data;
oracleConn.Close();
return sre;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
#endregion
#region 模具标签打印
///
/// 查询模板一览
///
///
///
public static ServiceResultEntity GetMouldLablePrintLayoutList(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.width\n" +
" ,t.height\n" +
" ,gt.goodstypename\n" +
" ,gt.goodstypecode\n" +
//" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logonamecode\n" +
" ,t.remarks\n" +
" ,t.valueflag\n" +
" FROM tp_mst_MouldLableprintlayout t\n" +
" LEFT JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
//" LEFT JOIN tp_mst_Logo logo\n" +
//" ON logo.logoid = t.logoid\n" +
" WHERE t.accountid = :accountid\n" +
" AND (:layoutname IS NULL OR instr(t.layoutname, :layoutname) > 0)\n" +
" AND (:goodstypecode IS NULL OR\n" +
" instr(gt.goodstypecode, :goodstypecode) = 1)\n" +
" AND (:remarks IS NULL OR instr(t.remarks, :remarks) > 0)\n" +
" AND (:valueflag IS NULL OR t.valueflag = :valueflag)\n" +
//" AND (:logos IS NULL OR instr(:logos, ','|| t.logoid ||',') > 0)\n" +
//" ORDER BY gt.goodstypecode, decode(logo.logocode, null, ' ',logo.logocode)";
" ORDER BY gt.goodstypecode";
List parameters = new List();
parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutname", OracleDbType.NVarchar2, cre.Properties["layoutname"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":goodstypecode", OracleDbType.NVarchar2, cre.Properties["goodstypecode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":valueflag", OracleDbType.NVarchar2, cre.Properties["valueflag"], ParameterDirection.Input));
//parameters.Add(new OracleParameter(":logos", OracleDbType.NVarchar2, cre.Properties["logos"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, 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 (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 验证产品类别是否已存在模板
///
///
///
public static ServiceResultEntity IsExistMouldLablePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" FROM tp_mst_MouldLableprintlayout t\n" +
//" WHERE t.goodstypeid = :goodstypeid\n" +
" WHERE ((:goodstypeid is null and t.goodstypeid is null) or (t.goodstypeid = :goodstypeid))\n" +
//" AND ((:logoid is null and t.logoid is null) or (t.logoid = :logoid))\n" +
" AND (:layoutid is null or :layoutid <> t.layoutid)";
List parameters = new List();
//parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":goodstypeid", OracleDbType.Int32, cre.Properties["GoodsTypeID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["layoutid"], ParameterDirection.Input));
//parameters.Add(new OracleParameter(":logoid", OracleDbType.Int32, cre.Properties["LogoID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
if (data == null || data.Rows.Count == 0)
{
sre.Result = false;
return sre;
}
sre.Result = true;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 获取打印项目
///
///
///
public static ServiceResultEntity GetMouldLablePrintItem(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT s.itemcode\n" +
" ,s.itemname\n" +
" ,s.itemsample\n" +
" ,s.itemstyle\n" +
" ,s.controlcode\n" +
" ,s.displayno\n" +
" FROM tp_sys_MouldLableprintitem s\n" +
" ORDER BY s.displayno";
DataSet data = oracleConn.GetSqlResultToDs(sqlString, null);
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = data;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 查询打印模板
///
///
///
public static ServiceResultEntity GetMouldLablePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.layoutdata\n" +
" ,t.goodstypeid\n" +
" ,gt.goodstypecode\n" +
" ,gt.goodstypename\n" +
//" ,t.logoid\n" +
//" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logoName\n" +
//" ,logo.logoName || '[' || logo.logocode || ']' logoName\n" +
" ,t.width\n" +
" ,t.height\n" +
" ,t.remarks\n" +
" FROM tp_mst_MouldLableprintlayout t\n" +
" LEFT JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
//" LEFT JOIN tp_mst_logo logo\n" +
//" ON logo.logoid = t.logoid\n" +
" WHERE t.layoutid = :layoutid";
List parameters = new List();
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
string sqlString1 =
"SELECT t.layoutid\n" +
" ,t.layoutitemid\n" +
" ,t.itemcode\n" +
" ,s.itemname\n" +
" ,s.itemsample\n" +
" ,s.itemstyle\n" +
" ,s.controlcode\n" +
" FROM tp_mst_MouldLableprintitem t\n" +
" INNER JOIN tp_sys_MouldLableprintitem s\n" +
" ON s.itemcode = t.itemcode\n" +
" WHERE t.layoutid = :layoutid";
DataTable data1 = oracleConn.GetSqlResultToDt(sqlString1, parameters.ToArray());
sre.Data.Tables.Add(data1);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 新建打印模板
///
///
///
public static ServiceResultEntity SaveAddMouldLablePrintLayoutData(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
DataRow layoutdata = cre.Data.Tables[0].Rows[0];
string sqlid = "select SEQ_MST_MouldLblPRINTLAYOUT_ID.Nextval from dual";
int layoutID = Convert.ToInt32(oracleTrConn.GetSqlResultToObj(sqlid));
ServiceResultEntity sre = new ServiceResultEntity();
sre.Result = layoutID;
string sqlString = "INSERT INTO TP_MST_MouldLablePRINTLAYOUT\n" +
" (LAYOUTID\n" +
" ,LAYOUTNAME\n" +
" ,LAYOUTDATA\n" +
" ,GOODSTYPEID\n" +
" ,GOODSTYPECODE\n" +
//" ,LOGOID\n" +
" ,WIDTH\n" +
" ,HEIGHT\n" +
" ,REMARKS\n" +
" ,ACCOUNTID\n" +
" ,CREATEUSERID\n" +
" ,UPDATEUSERID)\n" +
"VALUES\n" +
" (:LAYOUTID\n" +
" ,:LAYOUTNAME\n" +
" ,:LAYOUTDATA\n" +
" ,:GOODSTYPEID\n" +
" ,:GOODSTYPECODE\n" +
//" ,:LOGOID\n" +
" ,:WIDTH\n" +
" ,:HEIGHT\n" +
" ,:REMARKS\n" +
" ,:ACCOUNTID\n" +
" ,:CREATEUSERID\n" +
" ,:CREATEUSERID)";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTNAME", OracleDbType.NVarchar2, layoutdata["LAYOUTNAME"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTDATA", OracleDbType.Blob, layoutdata["LAYOUTDATA"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPEID", OracleDbType.Int32, layoutdata["GOODSTYPEID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPECODE", OracleDbType.NVarchar2, layoutdata["GOODSTYPECODE"], ParameterDirection.Input));
//parameters.Add(new OracleParameter(":LOGOID", OracleDbType.Int32, layoutdata["LOGOID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":WIDTH", OracleDbType.Int32, layoutdata["WIDTH"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":HEIGHT", OracleDbType.Int32, layoutdata["HEIGHT"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":REMARKS", OracleDbType.NVarchar2, layoutdata["REMARKS"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
// 插入新的项目明细
foreach (DataRow dataRow in cre.Data.Tables[1].Rows)
{
if (dataRow.RowState == DataRowState.Deleted ||
dataRow.RowState == DataRowState.Detached)
{
continue;
}
sqlString = "INSERT INTO TP_MST_MouldLablePRINTITEM"
+ "(LayoutID"
+ ",LayoutItemID"
+ ",ItemCode"
+ ")"
+ "VALUES"
+ " (:LayoutID"
+ ",:layoutItemID"
+ ",:ItemCode"
+ ")";
parameters.Clear();
parameters.Add(new OracleParameter(":LayoutID", OracleDbType.Int32, layoutID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutItemID", OracleDbType.Int32, dataRow["layoutItemID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ItemCode", OracleDbType.NVarchar2, dataRow["ItemCode"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 编辑打印模板
///
///
///
public static ServiceResultEntity SaveEditMouldLablePrintLayoutData(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
DataRow layoutdata = cre.Data.Tables[0].Rows[0];
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "UPDATE TP_MST_MouldLablePRINTLAYOUT t\n" +
" SET t.LAYOUTDATA = :LAYOUTDATA, t.WIDTH = :WIDTH, t.HEIGHT = :HEIGHT, t.UPDATEUSERID=:UPDATEUSERID\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTDATA", OracleDbType.Blob, layoutdata["LAYOUTDATA"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":WIDTH", OracleDbType.Int32, layoutdata["WIDTH"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":HEIGHT", OracleDbType.Int32, layoutdata["HEIGHT"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
sre.Result = result;
sqlString = "DELETE FROM TP_MST_MouldLablePRINTITEM WHERE LAYOUTID = :LAYOUTID";
parameters.Clear();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
// 插入新的项目明细
foreach (DataRow dataRow in cre.Data.Tables[1].Rows)
{
if (dataRow.RowState == DataRowState.Deleted ||
dataRow.RowState == DataRowState.Detached)
{
continue;
}
sqlString = "INSERT INTO TP_MST_MouldLablePRINTITEM"
+ "(LayoutID"
+ ",LayoutItemID"
+ ",ItemCode"
+ ")"
+ "VALUES"
+ " (:LayoutID"
+ ",:layoutItemID"
+ ",:ItemCode"
+ ")";
parameters.Clear();
parameters.Add(new OracleParameter(":LayoutID", OracleDbType.Int32, layoutdata["LAYOUTID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":layoutItemID", OracleDbType.Int32, dataRow["layoutItemID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ItemCode", OracleDbType.NVarchar2, dataRow["ItemCode"], ParameterDirection.Input));
oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 查询打印模板信息
///
///
///
public static ServiceResultEntity GetMouldLablePrintLayoutInfo(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string sqlString =
"SELECT t.layoutid\n" +
" ,t.layoutname\n" +
" ,t.valueflag\n" +
" ,t.goodstypeid\n" +
" ,gt.goodstypecode\n" +
" ,gt.goodstypename\n" +
//" ,t.logoid\n" +
//" ,decode(t.logoid, null, '', logo.logoname || '[' || logo.logocode || ']') logoNameCode\n" +
//" ,logo.logoName || '[' || logo.logocode || ']' logoNameCode\n" +
" ,t.remarks\n" +
" FROM tp_mst_MouldLableprintlayout t\n" +
" LEFT JOIN tp_mst_goodstype gt\n" +
" ON gt.goodstypeid = t.goodstypeid\n" +
//" LEFT JOIN tp_mst_Logo logo\n" +
//" ON logo.logoid = t.logoid\n" +
" WHERE t.layoutid = :layoutid";
List parameters = new List();
parameters.Add(new OracleParameter(":layoutid", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
DataTable data = oracleConn.GetSqlResultToDt(sqlString, parameters.ToArray());
ServiceResultEntity sre = new ServiceResultEntity();
sre.Data = new DataSet();
sre.Data.Tables.Add(data);
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 编辑打印模板信息
///
///
///
public static ServiceResultEntity SaveEditMouldLablePrintLayoutInfo(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
//DataRow layoutdata = cre.Data.Tables[0].Rows[0];
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "UPDATE TP_MST_MouldLablePRINTLAYOUT t\n" +
" SET t.LAYOUTNAME = :LAYOUTNAME, t.GOODSTYPEID = :GOODSTYPEID, t.GOODSTYPECODE = :GOODSTYPECODE\n" +
" ,t.REMARKS = :REMARKS, t.Valueflag = :Valueflag, t.UPDATEUSERID=:UPDATEUSERID\n" +
//" ,t.LogoID = :LogoID\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTNAME", OracleDbType.NVarchar2, cre.Properties["LayoutName"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPEID", OracleDbType.Int32, cre.Properties["GoodsTypeID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":GOODSTYPECODE", OracleDbType.NVarchar2, cre.Properties["GoodsTypeCode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":Valueflag", OracleDbType.NVarchar2, cre.Properties["ValueFlag"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":UPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
//parameters.Add(new OracleParameter(":LogoID", OracleDbType.Int32, cre.Properties["LogoID"], ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 删除条码打印模板
///
///
///
public static ServiceResultEntity DeleteMouldLablePrintLayout(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "DELETE FROM TP_MST_MouldLablePrintItem t\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
string sqlString1 = "DELETE FROM TP_MST_MouldLablePRINTLAYOUT t\n" +
" WHERE t.LAYOUTID = :LAYOUTID";
List parameters = new List();
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
// 删除明细
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
// 删除总单
result = oracleTrConn.ExecuteNonQuery(sqlString1, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
///
/// 获取条码打印信息（模板，数据）
///
///
///
public static ServiceResultEntity GetMouldLablePrintDATA(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
string barcode = cre.Properties["Barcode"].ToString();
//bool isGBarcode = (bool)cre.Properties["IsGBarcode"];
//if (!isGBarcode)
//{
// 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 = oracleConn.GetSqlResultToStr(sqlString, paras1);
//}
ServiceResultEntity sre = new ServiceResultEntity();
List parameters = new List();
parameters.Add(new OracleParameter("in_Barcode", OracleDbType.NVarchar2, barcode, ParameterDirection.Input));
parameters.Add(new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
//parameters.Add(new OracleParameter("in_UserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
parameters.Add(new OracleParameter("out_Status", OracleDbType.Int32, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_Message", OracleDbType.NVarchar2, 2000, null, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_LayoutData", OracleDbType.RefCursor, ParameterDirection.Output));
parameters.Add(new OracleParameter("out_PrintData", OracleDbType.RefCursor, ParameterDirection.Output));
DataSet data = oracleConn.ExecStoredProcedure("PRO_PM_GETMouldLablePrintDATA", parameters.ToArray());
int status = Convert.ToInt32(parameters[2].Value.ToString());
if (status < 0)
{
sre.Status = Constant.ServiceResultStatus.Other;
sre.Message = parameters[3].Value.ToString();
}
sre.Data = data;
return sre;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oracleConn != null &&
oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
}
}
///
/// 记录条码打印日志
///
///
///
public static ServiceResultEntity SetMouldLablePrintLog(ClientRequestEntity cre, SUserInfo sUserInfo)
{
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleTrConn.Connect();
ServiceResultEntity sre = new ServiceResultEntity();
string sqlString = "INSERT INTO TP_MST_MouldLablePrintLog\n" +
" (BARCODE, LAYOUTID, ACCOUNTID, CREATEUSERID)\n" +
"VALUES\n" +
" (:BARCODE, :LAYOUTID, :ACCOUNTID, :CREATEUSERID)";
List parameters = new List();
parameters.Add(new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["Barcode"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":LAYOUTID", OracleDbType.Int32, cre.Properties["LayoutID"], ParameterDirection.Input));
parameters.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
parameters.Add(new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
int result = oracleTrConn.ExecuteNonQuery(sqlString, parameters.ToArray());
if (result == 0)
{
sre.Status = Constant.ServiceResultStatus.NoModifyData;
return sre;
}
oracleTrConn.Commit();
return sre;
}
catch (Exception ex)
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Rollback();
}
throw ex;
}
finally
{
if (oracleTrConn != null &&
oracleTrConn.ConnState == ConnectionState.Open)
{
oracleTrConn.Disconnect();
}
}
}
#endregion
#region 成型破损
///
/// 获取成型破损原因左侧树的数据
///
/// DataSet
///
/// 2018.03.26 周兴 新建
///
public static ServiceResultEntity GetScrapReasonData(ClientRequestEntity cre, SUserInfo sUserInfo)
{
ServiceResultEntity result = new ServiceResultEntity();
DataSet returnData = new DataSet();
try
{
IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
try
{
oracleConn.Open();
DataTable returnTable;
string isALL = cre.Properties["ALL"] + "";
if (isALL == "1")
{
string sqlString = "SELECT ScrapReasonID,ScrapReason,DisplayNo,ScrapType,Remarks,ValueFlag FROM TP_MST_ScrapReason "
+ " WHERE AccountID = :AccountID Order By DisplayNo";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID)
};
returnTable = oracleConn.GetSqlResultToDt(sqlString, oracleParameter);
}
else
{
string sqlString = "SELECT ScrapReasonID,ScrapReason,DisplayNo,ScrapType,Remarks,ValueFlag FROM TP_MST_ScrapReason "
+ " WHERE AccountID = :AccountID and valueflag = :valueflag Order By DisplayNo";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":AccountID",sUserInfo.AccountID),
new OracleParameter(":valueflag","1")
};
returnTable = oracleConn.GetSqlResultToDt(sqlString, oracleParameter);
}
returnTable.TableName = "TP_MST_ScrapReason";
oracleConn.Close();
returnData.Tables.Add(returnTable);
result.Data = returnData;
return result;
}
catch (Exception ex)
{
if (oracleConn.ConnState == ConnectionState.Open)
{
oracleConn.Close();
}
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
}