/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <E_Task> GetList(E_Task model, ref int total)
{
if (model.PageIndex <= 0)
{
model.PageIndex = 1;
}
List <E_Task> list;
StringBuilder strSql = new StringBuilder();
StringBuilder whereSql = new StringBuilder(" where 1=1 ");
strSql.Append(" select ROW_NUMBER() OVER ( ORDER BY id desc) AS RID, task.*,i.PersonnelName as publishname from task left join dbo.tb_InPersonnel i on task.publishid=i.PersonnelID ");
if (!String.IsNullOrEmpty(model.taskname))
{
whereSql.Append(" and taskname like '%'+@taskname +'%'");
}
strSql.Append(whereSql);
string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList";
string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}";
pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString());
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
list = conn.Query <E_Task>(pageSqlStr, model)?.ToList();
total = conn.ExecuteScalar <int>(CountSql, model);
}
return(list);
}
/// <summary>
/// 获取首页任务列表
/// </summary>
public List <E_Task> GetIndexTaskList()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 7 * from task order by publishtime desc");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
return(conn.Query <E_Task>(strSql.ToString())?.ToList());
}
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public E_Task GetInfoById(E_Task model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from task where id=@id");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
model = conn.Query <E_Task>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
public List <tb_Base> GetBaseList()
{
List <tb_Base> list = null;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from tb_Base ");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
list = conn.Query <tb_Base>(strSql.ToString())?.ToList();
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <E_DrugCheck> GetList(E_DrugCheck model, ref int total)
{
if (model.PageIndex <= 0)
{
model.PageIndex = 1;
}
List <E_DrugCheck> list;
StringBuilder strSql = new StringBuilder();
StringBuilder whereSql = new StringBuilder(" where 1=1 ");
strSql.Append(@"select ROW_NUMBER() OVER ( ORDER BY a.id desc) AS RID, c.*,a.putArea,a.validDate yxq,a.amount rukucount,(select SUM(amount) from dbo.tb_DrugOUT WHERE temp2=a.id) chukucount from dbo.tb_DrugIN a
left join dbo.tb_DrugOUT b on b.temp2=a.id
inner join dbo.tb_Drug c on a.drugId=c.id ");
if (!string.IsNullOrEmpty(model.drugName))
{
whereSql.Append(" and drugName like '%'+@drugName+'%'");
}
if (!string.IsNullOrEmpty(model.putArea))
{
whereSql.Append(" and a.putArea like '%'+@putArea+'%'");
}
if (Utils.GetInt(model.riskLevel) > 0)
{
whereSql.Append(" and c.riskLevel=@riskLevel");
}
if (model.drugType > 0)
{
whereSql.Append(" and c.drugType=@drugType");
}
strSql.Append(whereSql);
string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList";
string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}";
pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString());
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
list = conn.Query <E_DrugCheck>(pageSqlStr, model)?.ToList();
total = conn.ExecuteScalar <int>(CountSql, model);
}
return(list);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(E_DrugLock model)
{
string sql = "INSERT INTO tb_DrugLock(temp2,pic,ischem,isdanger,locktypeid,lockName,mark,createUser,createDate,updateUser,updateDate,lockType,temp1) VALUES (@temp2,@pic,@ischem,@isdanger,@locktypeid,@lockName,@mark,@createUser,@createDate,@updateUser,@updateDate,@lockType,@temp1)";
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(E_Task model)
{
string sql = "INSERT INTO task(taskname, director, publishid, remark, status, tasktime, publishtime, finishtime) VALUES (@taskname, @director, @publishid, @remark, @status, @tasktime, @publishtime, @finishtime)";
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool DeleteById(E_Task model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete task where id=@id ");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(E_Task model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update task set taskname=@taskname, director=@director, remark=@remark, status=@status, tasktime=@tasktime, finishtime=@finishtime where id=@id ");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(E_DrugLock model)
{
StringBuilder strSql = new StringBuilder();
// strSql.Append("update dp_food set areaid=@areaid, classinfoid=@classinfoid,foodname=@foodname,pid=@pid,pname=@pname,pic=@pic,updatetime=getdate() where foodid=@foodid ");
strSql.Append("update tb_DrugLock set " + Utils.SetUpdateSql(model, new string[] { "id" }) + " where id=@id ");
using (IDbConnection conn = new SqlConnection(PubConstant.GetConnectionString()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 执行Sql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (SqlConnection conn = new SqlConnection(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;
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(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
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(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("Oracle执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
