981 lines
41 KiB
C#
981 lines
41 KiB
C#
using Dapper;
|
|
using dccdc.Models;
|
|
using dccdc.Models.DTO;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class InfectionOpenUserInfoDal
|
|
{
|
|
public List<InfectionOpenUserInfoModel> GetAllDataList()
|
|
{
|
|
string sql = "Select *,(Select name From infection_open_crowd Where id = oui.crowd_id) as crowd,year(getdate())-substring(oui.birth,0,5) as age From infection_open_user_info oui Where type = 0 Order By type asc,create_time desc";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListByIdent(string ident)
|
|
{
|
|
string sql = string.Format("Select * From infection_open_user_info Where ident = '{0}'", ident);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
public List<InfectionOpenUserInfoModel> GetDataListByReferee(string referee)
|
|
{
|
|
string sql = string.Format("Select * From infection_open_user_info Where referee = '{0}'", referee);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListNotType()
|
|
{
|
|
string sql = "Select * From infection_open_user_info Where Type = 0 Order By create_time";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public bool SaveData(InfectionOpenUserInfoModel model)
|
|
{
|
|
string selectSQL = "Select * From infection_open_user_info Where ident = @ident";
|
|
bool returnValue = false;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int results = conn.Execute(selectSQL, new { @ident = model.ident});
|
|
if (results > 0)
|
|
returnValue = false;
|
|
else
|
|
returnValue = true;
|
|
}
|
|
catch
|
|
{
|
|
returnValue = false;
|
|
}
|
|
}
|
|
if (returnValue)
|
|
{
|
|
string sql = @"Insert Into infection_open_user_info(user_id,type,name,ident,birth,sex,unit,phone,license,crowd_id,pass_by,pass_time,create_time,referee,recommend)
|
|
Values (@user_id,@type,@name,@ident,@birth,@sex,@unit,@phone,@license,@crowd_id,@pass_by,@pass_time,@create_time,@referee,@recommend)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
returnValue = true;
|
|
else
|
|
returnValue = false;
|
|
}
|
|
catch
|
|
{
|
|
returnValue = false;
|
|
}
|
|
}
|
|
}
|
|
return returnValue;
|
|
}
|
|
|
|
public bool ModifyDataByUserID(InfectionOpenUserInfoModel model)
|
|
{
|
|
string sql = string.Empty;
|
|
if (model.license.Equals("false"))
|
|
{
|
|
sql = "Update infection_open_user_info Set type=0,name=@name,ident=@ident,birth=@birth,sex=@sex,unit=@unit,phone=@phone,crowd_id=@crowd_id,pass_by=@pass_by,pass_time=@pass_time,referee=@referee Where user_id=@user_id";
|
|
}
|
|
else
|
|
{
|
|
sql = "Update infection_open_user_info Set type=0,name=@name,ident=@ident,birth=@birth,sex=@sex,unit=@unit,phone=@phone,license=@license,crowd_id=@crowd_id,pass_by=@pass_by,pass_time=@pass_time,referee=@referee Where user_id=@user_id";
|
|
}
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
return true;
|
|
else
|
|
return false;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string s = ex.Message;
|
|
return false;
|
|
}
|
|
}
|
|
}
|
|
|
|
public string ModifyDataByID(InfectionOpenUserInfoModel model)
|
|
{
|
|
string sql = "Update infection_open_user_info Set type=@type,name=@name,ident=@ident,birth=@birth,sex=@sex,unit=@unit,phone=@phone,license=@license,crowd_id=@crowd_id,pass_by=@pass_by,pass_time=@pass_time Where user_id=@user_id";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
return "True";
|
|
else
|
|
return "False";
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
string s = ex.Message;
|
|
return "False";
|
|
}
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListByNamePhoneIdent(string name, string phone, string ident)
|
|
{
|
|
string sql = string.Format("Select *,(Select name From infection_open_crowd Where id = oui.crowd_id) as crowd From infection_open_user_info oui Where name = '{0}' And phone ='{1}' And ident='{2}' Order By create_time", name, phone, ident);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListByID(string ID)
|
|
{
|
|
string sql = string.Format("Select *,(Select name From infection_open_crowd Where id = oui.crowd_id) as crowd From infection_open_user_info oui Where id = '{0}' Order By create_time", ID);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListByUserID(int user_id)
|
|
{
|
|
string sql = string.Format("Select *,(Select name From infection_open_crowd Where id = oui.crowd_id) as crowd From infection_open_user_info oui Where user_id = {0} Order By create_time", user_id);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetAllHangIntheAirQuestion()
|
|
{
|
|
string sql = "Select oui.*,1 as investigationState From infection_open_user_info oui left join infection_open_user ou on oui.user_id=ou.id where ou.state=2 Order By oui.create_time";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public string AuditInformation(string id, string type, string cancel_reason, string oa_id,string pass_time)
|
|
{
|
|
string returnValue = string.Empty;
|
|
if (type == "1")
|
|
{
|
|
string sql = string.Format("Update infection_open_user_info Set type = {0},cancel_reason = '{1}', pass_by = {2},pass_time = '{3}' Where id = {4}", type, cancel_reason, oa_id, pass_time, id);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql);
|
|
if (result > 0) {
|
|
string sql2 = string.Format("select user_id from infection_open_user_info Where id = {0}", id);
|
|
using (IDbConnection conn2 = CommHelper.GetSqlConnection())
|
|
{
|
|
InfectionOpenUserInfoModel result2 = conn.QueryFirst<InfectionOpenUserInfoModel>(sql2);
|
|
string sql3 = string.Format("Update infection_open_user Set state = 2 Where id = {0}", result2.user_id);
|
|
using (IDbConnection conn3 = CommHelper.GetSqlConnection())
|
|
{
|
|
|
|
int result3 = conn.Execute(sql3);
|
|
if (result3 > 0)
|
|
{
|
|
returnValue = "审核成功！";
|
|
}
|
|
else
|
|
{
|
|
returnValue = "操作失败";
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
returnValue = "操作失败！";
|
|
}
|
|
catch
|
|
{
|
|
returnValue = "操作失败！";
|
|
}
|
|
}
|
|
}
|
|
else if (type == "2")
|
|
{
|
|
string sql = string.Format("Update infection_open_user_info Set type = {0}, cancel_reason = '{1}', pass_by = {2},pass_time = '{3}' Where id = {4}", type, cancel_reason, oa_id,pass_time, id);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql);
|
|
if (result > 0)
|
|
returnValue = "驳回成功！";
|
|
else
|
|
returnValue = "操作失败";
|
|
}
|
|
catch
|
|
{
|
|
returnValue = "操作失败";
|
|
}
|
|
}
|
|
}
|
|
return returnValue;
|
|
}
|
|
|
|
public string JudgementCode(string recommend)
|
|
{
|
|
string sql = string.Format("Select Count(1) From infection_open_user_info Where recommend = '{0}'", recommend);
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.ExecuteScalar<int>(sql);
|
|
if (result > 0)
|
|
return "True";
|
|
else
|
|
return "False";
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
Console.WriteLine(ex);
|
|
return "False";
|
|
}
|
|
}
|
|
}
|
|
|
|
public int getDataCount(string name, string ident)
|
|
{
|
|
string sql = "select count(1) from infection_open_user_info where 1=1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " And name Like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(ident))
|
|
{
|
|
sql += " And ident Like @ident";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { @name = "%" + name + "%", @ident = "%" + ident + "%" });
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> getCheckQuestionnaireList(int page, int pagesize, string name, string ident)
|
|
{
|
|
//string sql = "Select *,row_number() over(order by id) as rownum From infection_open_user_info Where 1=1";
|
|
string sql = "SELECT DISTINCT oui.id,oui.name,oui.ident,oui.unit,erpu.username,MAX(iqa.create_time)as create_time," +
|
|
"row_number() over(order by oui.id) as rownum FROM dbo.infection_open_user_info oui " +
|
|
"LEFT JOIN dbo.infection_question_answer iqa ON oui.id = iqa.info_id " +
|
|
"left join oa.dbo.erpuser erpu on iqa.create_by=erpu.id " +
|
|
"Where 1 = 1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " And name Like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(ident))
|
|
{
|
|
sql += " And ident Like @ident";
|
|
}
|
|
sql = sql + " GROUP BY oui.id,oui.name,oui.ident,oui.unit,erpu.username";
|
|
sql = "Select * From (" + sql + ") t Where t.rownum>(" + page + "-1)*" + pagesize + " And t.rownum<=" + page + "*" + pagesize+ "ORDER BY t.create_time DESC";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.InfectionOpenUserInfoModel>(sql, new { @name = "%" + name + "%", @ident = "%" + ident + "%" }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> getList(int page, int pagesize, string name, string unit, string phone, string pass_by)
|
|
{
|
|
string sql = "select *,row_number() over(order by id) as rownum from infection_open_user_info where 1=1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " and name like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(unit))
|
|
{
|
|
sql += " and unit like @unit";
|
|
}
|
|
if (!string.IsNullOrEmpty(phone))
|
|
{
|
|
sql += " and phone like @phone";
|
|
}
|
|
if (!string.IsNullOrEmpty(pass_by))
|
|
{
|
|
sql += " and pass_by like @pass_by";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.InfectionOpenUserInfoModel>(sql, new { @name = "%" + name + "%", @unit = "%" + unit + "%", @phone = "%" + phone + "%" , @pass_by = "%" + pass_by + "%" }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getCounts(string name, string unit, string phone, string ident)
|
|
{
|
|
string sql = "select count(1) from dbo.infection_open_user_info where 1=1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " and name like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(unit))
|
|
{
|
|
sql += " and unit like @unit";
|
|
}
|
|
if (!string.IsNullOrEmpty(phone))
|
|
{
|
|
sql += " and phone like @phone";
|
|
}
|
|
if (!string.IsNullOrEmpty(ident))
|
|
{
|
|
sql += " and ident like @ident";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { @name = "%" + name + "%", @unit = "%" + unit + "%", @phone = "%" + phone + "%", ident = "%" + ident + "%" });
|
|
}
|
|
}
|
|
|
|
public int getCount(string name, string unit, string phone, string pass_by)
|
|
{
|
|
string sql = "select count(1) from dbo.infection_open_user_info where 1=1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " and name like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(unit))
|
|
{
|
|
sql += " and unit like @unit";
|
|
}
|
|
if (!string.IsNullOrEmpty(phone))
|
|
{
|
|
sql += " and phone like @phone";
|
|
}
|
|
if (!string.IsNullOrEmpty(pass_by))
|
|
{
|
|
sql += " and pass_by like @pass_by";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { @name = "%" + name + "%", @unit = "%" + unit + "%", @phone = "%" + phone + "%", @pass_by = "%" + pass_by + "%" });
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> getAllList(int page, int pagesize, string name, string unit, string phone, string ident)
|
|
{
|
|
try
|
|
{
|
|
string sql = "SELECT info.id AS id,info.name,info.phone,info.ident,info.type," +
|
|
"info.pass_by,info.pass_time,info.unit,info.crowd_id," +
|
|
"crowd.isQuestion,info.user_id,erpu.TrueName as pass_name,info.recommend,info.referee," +
|
|
"isnull(oui.recount,0) as recommendCount, " +
|
|
"(Select Count(1) From [dccdc].[dbo].[infection_question_answer] Where info_id = info.id) as isNotQuestionnaire," +
|
|
"u.nickname as nickname,u.oa_id as oa_id, u.openid as openid, u.state, row_number() over(order by info.id) As rownum " +
|
|
"FROM [dccdc].[dbo].[infection_open_user_info] info " +
|
|
"left join (select referee,count(1) as recount from [dccdc].[dbo].[infection_open_user_info] group by referee) oui on oui.referee = info.recommend " +
|
|
"LEFT JOIN [dccdc].[dbo].[infection_open_user] u ON u.id = info.user_id " +
|
|
"left join [OA].[dbo].[ERPUser] erpu on info.pass_by=erpu.ID " +
|
|
"Left JOIN [dccdc].[dbo].[infection_open_crowd] crowd on info.crowd_id = crowd.id and crowd.isOpen = '1' WHERE 1 = 1 ";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " And info.name Like '%" + name + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(unit))
|
|
{
|
|
sql += " And info.unit Like '%" + unit + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(phone))
|
|
{
|
|
sql += " And info.phone Like '%" + phone + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(ident))
|
|
{
|
|
sql += " And info.ident Like '%" + ident + "%'";
|
|
}
|
|
sql = "Select * From (" + sql + ") t Where t.rownum>(" + page + "-1)*" + pagesize + " And t.rownum<=" + page + "*" + pagesize + " Order By t.type,t.pass_time Desc";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public InfectionOpenUserInfoModel getOne(string id)
|
|
{
|
|
string sql = "SELECT info.id AS id, info.name,info.phone,info.ident,info.type,u.name as pass_name,info.pass_time," +
|
|
" info.unit, info.birth, info.crowd_id, info.license FROM infection_open_user_info info LEFT JOIN infection_open_user_info u ON u.id = info.pass_by WHERE info.id = @id";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql, new { id = id }).First();
|
|
}
|
|
}
|
|
public InfectionOpenUserInfoModel getOne2(string id)
|
|
{
|
|
string sql = "SELECT info.id AS id, info.name,info.phone,info.ident,info.type,info.pass_time," +
|
|
" info.unit, info.birth, info.crowd_id, info.license FROM infection_open_user_info info WHERE info.id = @id";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql, new { id = id }).First();
|
|
}
|
|
}
|
|
|
|
public object save(InfectionOpenUserInfoModel model)
|
|
{
|
|
System.DateTime currentTime = new System.DateTime();
|
|
currentTime = System.DateTime.Now;
|
|
model.create_time = currentTime.ToString();
|
|
string sql = "";
|
|
if (model.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[infection_open_user_info]
|
|
([name]
|
|
,[phone]
|
|
,[ident]
|
|
,[birth]
|
|
,[type]
|
|
,[unit]
|
|
,[license]
|
|
,[crowd_id]
|
|
,[user_id]
|
|
,[create_time]
|
|
)
|
|
VALUES
|
|
(@name
|
|
,@phone
|
|
,@ident
|
|
,@birth
|
|
,0
|
|
,@unit
|
|
,@license
|
|
,@crowd_id
|
|
,@user_id
|
|
,@create_time
|
|
)";
|
|
}
|
|
else
|
|
{
|
|
if (model.investigationState == "1")
|
|
{
|
|
sql = @"UPDATE [dbo].[infection_open_user_info]
|
|
SET [name] = @name
|
|
,[phone] = @phone
|
|
,[ident] = @ident
|
|
,[birth] = @birth
|
|
,[type] = @type
|
|
,[crowd_id] = @crowd_id
|
|
,[unit] = @unit
|
|
WHERE id=@id";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [dbo].[infection_open_user_info]
|
|
SET [name] = @name
|
|
,[phone] = @phone
|
|
,[ident] = @ident
|
|
,[birth] = @birth
|
|
,[type] = @type
|
|
,[license] = @license
|
|
,[crowd_id] = @crowd_id
|
|
,[unit] = @unit
|
|
WHERE id=@id";
|
|
}
|
|
}
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
return new { State = 1, Message = "保存成功！" };
|
|
else
|
|
return new { State = 0, Message = "保存失败！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
//修改审核状态
|
|
public object updateType(string id, string type, string reason, string pass_by, string pass_time)
|
|
{
|
|
string sql = @"UPDATE [dbo].[infection_open_user_info]
|
|
SET [type] = @type,[cancel_reason] = @cancel_reason,[pass_by] = @pass_by,[pass_time] = @pass_time
|
|
WHERE id=@id";
|
|
if(type == "1")
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @type = type, @cancel_reason = reason, @pass_by = pass_by, @pass_time = DateTime.Now.ToString() });
|
|
if (result > 0)
|
|
{
|
|
string sql2 = string.Format("select user_id from infection_open_user_info Where id = {0}", id);
|
|
using (IDbConnection conn2 = CommHelper.GetSqlConnection())
|
|
{
|
|
InfectionOpenUserInfoModel result2 = conn.QueryFirst<InfectionOpenUserInfoModel>(sql2);
|
|
string sql3 = string.Format("Update infection_open_user Set state = 2 Where id = {0}", result2.user_id);
|
|
using (IDbConnection conn3 = CommHelper.GetSqlConnection())
|
|
{
|
|
|
|
int result3 = conn.Execute(sql3);
|
|
if (result3 > 0)
|
|
{
|
|
return new { State = 1, Message = "通过成功" };
|
|
}
|
|
else
|
|
{
|
|
return new { State = 0, Message = "操作失败！" };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
return new { State = 0, Message = "操作失败！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
if (type == "2")
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @type = type, @cancel_reason = reason, @pass_by = pass_by, @pass_time = DateTime.Now.ToString() });
|
|
if (result > 0)
|
|
{
|
|
return new { State = 2, Message = "驳回成功" };
|
|
}
|
|
else
|
|
return new { State = 0, Message = "操作失败！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
return new { State = 0, Message = "操作失败！" };
|
|
}
|
|
|
|
public bool isExist(string num, int jgid)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select count(1) from professionalExam_register where physical_num=@num";
|
|
if (jgid != 49)
|
|
{
|
|
sql += " and jgid=" + jgid;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return (conn.ExecuteScalar<int>(sql, new { num = num }) != 0 ? true : false);
|
|
}
|
|
}
|
|
|
|
//; select SCOPE_IDENTITY()
|
|
public int add(InfectionOpenUserInfoModel model)
|
|
{
|
|
string sql = @"INSERT INTO [dbo].[professionalExam_register]
|
|
([health_certificate_number]
|
|
,[physical_num]
|
|
,[physical_num_old]
|
|
,[person_name]
|
|
,[sex]
|
|
,[person_age]
|
|
,[birth]
|
|
,[card_number]
|
|
,[phone]
|
|
,[native_info_maintain_id]
|
|
,[home_address]
|
|
,[category]
|
|
,[trade_type_maintain_id]
|
|
,[work_category]
|
|
,[person_category]
|
|
,[person_type_maintain_id]
|
|
,[util_name]
|
|
,[enterprise_info_maintain_id]
|
|
,[area]
|
|
,[depart]
|
|
,[physical_category]
|
|
,[medical_scheme_maintain_id]
|
|
,[register_type]
|
|
,[physical_way]
|
|
,[physical_status]
|
|
,[register_date]
|
|
,[phone_path]
|
|
,[status]
|
|
,[prescription]
|
|
,[physial_expenses]
|
|
,[payment]
|
|
,[haemospasia]
|
|
,[stool]
|
|
,[urine]
|
|
,[image]
|
|
,[director_approval_status]
|
|
,[procedure_status]
|
|
,[main_review]
|
|
,[main_result]
|
|
,[result_status]
|
|
,[is_printed]
|
|
,[wait_print]
|
|
,[other]
|
|
,[printtimes]
|
|
,[exam_type_id]
|
|
,[exam_type]
|
|
,[main_user_id]
|
|
,[main_user_name]
|
|
,[main_review_date]
|
|
,[work_category_id]
|
|
,[check_date]
|
|
,[security_card_no]
|
|
,[flag]
|
|
,[physical_type]
|
|
,[special_health_certificate]
|
|
,[charging_time])
|
|
VALUES
|
|
(@health_certificate_number
|
|
,@physical_num
|
|
,@physical_num_old
|
|
,@person_name
|
|
,@sex
|
|
,@person_age
|
|
,@birth
|
|
,@card_number
|
|
,@phone
|
|
,@native_info_maintain_id
|
|
,@home_address
|
|
;select SCOPE_IDENTITY()";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<int>(sql, model).FirstOrDefault();
|
|
//return (conn.Query<int>(sql, model).FirstOrDefault() != 0 ? true : false);
|
|
}
|
|
}
|
|
|
|
public object update2(InfectionOpenUserInfoModel model)
|
|
{
|
|
// model.pass_time = DateTime.Now.ToString("yyyy-MM-dd");
|
|
|
|
string sql = @"UPDATE [dbo].[professionalExam_register]
|
|
SET [person_name] = @person_name
|
|
,[sex] = @sex
|
|
,[nation] =@nation
|
|
,[marry_status]=@marry_status
|
|
,[person_age] = @person_age
|
|
,[birth] = @birth
|
|
,[phone] = @phone
|
|
,[native_info_maintain_id] = @native_info_maintain_id
|
|
,[home_address] = @home_address
|
|
,[work_category] = @work_category
|
|
WHERE id=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
return new { State = 1, Message = "保存成功！" };
|
|
else
|
|
return new { State = 0, Message = "保存失败！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> getAllList(Criteria model)
|
|
{
|
|
string sql = "";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
switch (model.result_status)
|
|
{
|
|
case "1":
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type,hazard_ids,job_status_id
|
|
from professionalExam_register
|
|
where id not in(select distinct person_id from professionalExam_project_result where qualified like '不合格' or deficiency like '缺项')
|
|
and procedure_status like '已录入检查结果' and register_type not like '复检登记'
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|
break;
|
|
case "2":
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|
from professionalExam_register
|
|
where id in(select distinct person_id from professionalExam_project_result where qualified like '不合格')
|
|
and procedure_status like '已录入检查结果' and register_type not like '复检登记'
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|
break;
|
|
case "3":
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|
from professionalExam_register
|
|
where register_type like '复检登记' and procedure_status like '已录入检查结果'
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|
break;
|
|
case "4":
|
|
sql = @"select id,health_certificate_number,enterprise_info_maintain_id,physical_num,person_name,person_age,card_number,phone,category,work_category,util_name,area,register_type ,hazard_ids,job_status_id
|
|
from professionalExam_register
|
|
where procedure_status in('主检医生已审核','已打印健康证')
|
|
and freezing_and_thawing is null and register_date >= @startDate and register_date <= @endDate and physical_type like '%职业%'";
|
|
break;
|
|
}
|
|
|
|
if (model.person_name != "")
|
|
{
|
|
sql += " and person_name like @person_name ";
|
|
}
|
|
|
|
if (model.physical_num != "")
|
|
{
|
|
sql += " and physical_num like @physical_num ";
|
|
}
|
|
if (model.check_type != "")
|
|
{
|
|
sql += " and exam_type = @check_type ";
|
|
}
|
|
|
|
if (model.checkstartDate != null && model.checkstartDate != "")
|
|
{
|
|
sql += "and check_date >= @checkstartDate ";
|
|
}
|
|
if (model.checkendDate != null && model.checkendDate != "")
|
|
{
|
|
sql += "and check_date <= @checkendDate ";
|
|
}
|
|
|
|
sql += " order by register_date";
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql, new { @checkstartDate = model.checkstartDate, @checkendDate = model.checkendDate, @check_type = model.check_type, @person_name = "%" + model.person_name + "%", @physical_num = "%" + model.physical_num + "%", @startDate = model.startDate, @endDate = model.endDate }).ToList();
|
|
}
|
|
}
|
|
|
|
public InfectionOpenUserInfoModel getOneByNum(string physical_num)
|
|
{
|
|
string sql = "select * from professionalExam_register where physical_num=@physical_num";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql, new { physical_num = physical_num }).First();
|
|
}
|
|
}
|
|
|
|
public int Add(InfectionOpenUserInfoModel model)
|
|
{
|
|
string sql = @"INSERT INTO [dbo].[infection_open_user_info]
|
|
([user_id]
|
|
,[name]
|
|
,[sex]
|
|
,[birth]
|
|
,[crowd_id]
|
|
,[unit]
|
|
,[phone]
|
|
,[ident]
|
|
,[type]
|
|
,[license]
|
|
,[create_time])
|
|
VALUES
|
|
(@user_id
|
|
,@name
|
|
,@sex
|
|
,@birth
|
|
,@crowd_id
|
|
,@unit
|
|
,@phone
|
|
,@ident
|
|
,@type
|
|
,@license
|
|
,@create_time)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, model);
|
|
if (result > 0)
|
|
return 1;
|
|
else
|
|
return 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataAllByState()
|
|
{
|
|
string sql = "Select oui.id, oui.name From infection_open_user_info oui left join infection_open_user ou on ou.id = oui.user_id where ou.type = '1'";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserInfoModel> GetDataListNameUnitPhone(string name, string unit, string phone)
|
|
{
|
|
string sql = "Select oui.* From infection_open_user_info oui left join infection_open_user ou on oui.user_id=ou.id Where ou.state=2";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " And name Like '%" + name + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(unit))
|
|
{
|
|
sql += " And unit Like '%" + unit + "%'";
|
|
}
|
|
if (!string.IsNullOrEmpty(phone))
|
|
{
|
|
sql += " And phone Like '%" + phone + "%'";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.InfectionOpenUserInfoModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public InfectionOpenUserInfoModel getInfoByIdent(string ident)
|
|
{
|
|
string sql = "Select * from infection_open_user_info where ident ='" + ident + "'";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList().FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public InfectionOpenUserInfoModel findById(int id)
|
|
{
|
|
string sql = "Select * from infection_open_user_info where id ='" + id + "'";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList().FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public InfectionOpenUserInfoModel findByUserId(int user_id)
|
|
{
|
|
string sql = "Select * from infection_open_user_info where user_id ='" + user_id + "'";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserInfoModel>(sql).ToList().FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public List<InfectionOpenUserModel> getOaIdById(string oa_id)
|
|
{
|
|
string sql = "SELECT oa_id FROM infection_open_user WHERE 1 = 1 and oa_id =" + oa_id;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<InfectionOpenUserModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public int UpdateRecommendStateById(string id)
|
|
{
|
|
string sql = "update infection_open_user_info set recommend_state = '1' where id=" + id;
|
|
try
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Execute(sql);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
}
|
|
|
|
public bool checkRecommend(string recommend)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select count(1) from infection_open_user where recommend=@recommend";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return (conn.ExecuteScalar<int>(sql, new { recommend = recommend }) != 0 ? true : false);
|
|
}
|
|
}
|
|
|
|
public int getMonthPass()
|
|
{
|
|
string sql = "select count(*) from infection_open_user_info where type=1 and DATEDIFF(m,pass_time,GETDATE())=0";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public int getMonthRefuse()
|
|
{
|
|
string sql = "select count(*) from infection_open_user_info where type=2 and DATEDIFF(m,pass_time,GETDATE())=0";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public int getAllPass()
|
|
{
|
|
string sql = "select count(*) from infection_open_user_info where type=1 ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public int getAllRefuse()
|
|
{
|
|
string sql = "select count(*) from infection_open_user_info where type=2 ";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public int getDataCount(int user_id)
|
|
{
|
|
string sql = "select count(1) from infection_open_user_info where 1=1";
|
|
if (user_id!=0)
|
|
{
|
|
sql += " And user_id = @user_id";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { user_id = user_id });
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|