/// <summary>
/// 保养监视数量
/// </summary>
/// <returns></returns>
public static string getBYCount()
{
string result = "0";
string sql = @" select count(1) from MoJu_LY a
join MoJu_BY_APP app on a.ly_no=app.ly_no
join MoJu_Down_Apply ap on a.ly_no=ap.ly_no
left join MoJu_BY on MoJu_BY.by_no=app.by_no
where ([state]='保养完成' and act_date is not null and app.zg_confirm='Y' and app.zg_confirm_date is null)
or state in('待保养','保养中') " ;
Maticsoft.DBUtility.DbHelperSQLP helper = new Maticsoft.DBUtility.DbHelperSQLP(connReport);
result = helper.GetSingle(sql).ToString();
//string appp_ng = re_dt.Rows[0]["app_ng"].ToString();
//string result = "[{\"appp_ng\":\"" + appp_ng + "\"}]";
return(result);
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet result;
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
new SqlDataAdapter
{
SelectCommand = DbHelperSQLP.BuildQueryCommand(connection, storedProcName, parameters)
}.Fill(dataSet, tableName);
connection.Close();
result = dataSet;
}
return(result);
}
/// <summary>
/// 上模监视数量
/// </summary>
/// <returns></returns>
public static string getSMCount()
{
string result = "0";
string sql = @"select sum(cnt) from (
select count(1)cnt from MoJu_ly where state in('待出库','待上模','待下模')
union all
select COUNT(1) from MoJu_LY a join MoJu b on a.moju_id=b.id
join MoJu_Down_apply ap on a.ly_no=ap.ly_no
join MoJu_Down down on a.ly_no=down.ly_no
where ap.mojian_check='末件要检测' and down.mojian_date is null )t " ;
Maticsoft.DBUtility.DbHelperSQLP helper = new Maticsoft.DBUtility.DbHelperSQLP(connReport);
result = helper.GetSingle(sql).ToString();
//string appp_ng = re_dt.Rows[0]["app_ng"].ToString();
//string result = "[{\"appp_ng\":\"" + appp_ng + "\"}]";
return(result);
}
public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int indentity = 0;
foreach (object obj in SQLStringList)
{
DictionaryEntry myDE = (DictionaryEntry)obj;
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
DbHelperSQLP.PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
public SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(this.connectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader result;
try
{
DbHelperSQLP.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
result = myReader;
}
catch (SqlException e)
{
throw e;
}
return(result);
}
public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
int result;
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
DbHelperSQLP.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
result = rows;
}
catch (SqlException e)
{
throw e;
}
}
}
return(result);
}
public int ExecuteSqlTran(List <CommandInfo> cmdList)
{
int result;
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int count = 0;
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
DbHelperSQLP.PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
}
else
{
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
}
trans.Commit();
result = count;
}
catch
{
trans.Rollback();
throw;
}
}
}
return(result);
}
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
int result;
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
DbHelperSQLP.PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
}
object obj = cmd.ExecuteScalar();
if (obj == null && obj == DBNull.Value)
{
}
bool isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
}
}
else
{
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
}
cmd.Parameters.Clear();
}
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
if (!OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList))
{
tx.Rollback();
throw new Exception("Oracle执行失败");
}
tx.Commit();
result = 1;
}
catch (SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e2)
{
tx.Rollback();
throw e2;
}
}
return(result);
}
public TB_RC_CompanyBasic()
{
this.dbhelp = new DbHelperSQLP(PubConstant.TalentConnectionString);
}
public TB_CG_WelfarePolicyType()
{
this.dbhelp = new DbHelperSQLP(PubConstant.CGConnectionString);
}
public TB_RC_UpkeepDate()
{
this.dbhelp = new DbHelperSQLP(PubConstant.TalentConnectionString);
}
public TB_CG_SocialPolicyDetail()
{
this.dbhelp = new DbHelperSQLP(PubConstant.CGConnectionString);
}
public TB_RC_InterViewer()
{
this.dbhelp = new DbHelperSQLP(PubConstant.TalentConnectionString);
}
public TN_SYS_EM_USER()
{
this.dbhelp = new DbHelperSQLP(PubConstant.LoginConnectionString);
}
public TB_CG_CityBase()
{
this.dbhelp = new DbHelperSQLP(PubConstant.CGConnectionString);
}
public static DbHelperSQLP DBSQL()
{
DbHelperSQLP TmpDbHelperSQLP = new DbHelperSQLP(ConfigHelper.GetAppSettings("SqlServer_WMF_DB"));
return TmpDbHelperSQLP;
}
public TB_RC_TalentInfor()
{
this.dbhelp = new DbHelperSQLP(PubConstant.TalentConnectionString);
}
public TB_CG_Permission()
{
this.dbhelp = new DbHelperSQLP(PubConstant.CGConnectionString);
}