/// <summary>
/// 医院端企业订单
/// </summary>
/// <param name="ht"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <DdjbxxModel> OrderYyQyList(Hashtable ht, int pageIndex, int pageSize, ref int count)
{
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddly"].ToString()))
{
sd.Append(" and a.ddly = @ddly");
paramList.Add("ddly", ht["ddly"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddstart"].ToString()))
{
sd.Append(" and a.intime>= @ddstart ");
paramList.Add("ddstart", ht["ddstart"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddend"].ToString()))
{
sd.Append(" and a.intime<= @ddend ");
paramList.Add("ddend", ht["ddend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["yystart"].ToString()))
{
sd.Append(" and a.yykssj>= @yystart ");
paramList.Add("yystart", ht["yystart"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyend"].ToString()))
{
sd.Append(" and a.yykssj<= @yyend ");
paramList.Add("yyend", ht["yyend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh = @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm = @xm");
paramList.Add("xm", ht["xm"].ToString());
}
if (!string.IsNullOrEmpty(ht["tczt"].ToString()))
{
sd.Append(" and a.ddzt=@tczt ");
paramList.Add("tczt", ht["tczt"].ToString());
}
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select a.* from dd_jbxx_new a where 1=1";
sql += sd.ToString();
string sqlNew = "";
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
sqlNew = "Select * From (Select ROW_NUMBER() Over (Order By ddbh desc) As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "";
List <DdjbxxModel> li = conn.Query <DdjbxxModel>(sqlNew, paramList).ToList();
count = conn.Query <int>("Select Count(1) From (" + sql + ") As t ", paramList).FirstOrDefault();
return(li);
}
}
public ReturnModel Add(XtZhbModel model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.zh))
{
return new ReturnModel {
Code = 201, Msg = "账号不能为空"
}
}
;
if (string.IsNullOrWhiteSpace(model.mm))
{
return new ReturnModel {
Code = 201, Msg = "医院编号不能为空"
}
}
;
if (string.IsNullOrWhiteSpace(model.dh))
{
return new ReturnModel {
Code = 201, Msg = "手机号不能为空"
}
}
;
if (model.jsid.Equals(0))
{
return new ReturnModel {
Code = 201, Msg = "请选择角色"
}
}
;
if (string.IsNullOrWhiteSpace(model.lxr))
{
return new ReturnModel {
Code = 201, Msg = "姓名不能为空"
}
}
;
StringBuilder sbSql = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
paramList.Add("Zh", model.zh);
paramList.Add("Mm", model.mm);
paramList.Add("CreateTime", model.createtime);
paramList.Add("Dh", model.dh);
paramList.Add("Lxr", model.lxr);
paramList.Add("Ys", model.ys);
paramList.Add("Yybh", model.yybh);
paramList.Add("Jsid", model.jsid);
paramList.Add("Zt", model.zt);
paramList.Add("Bz", model.bz);
paramList.Add("QdId", model.QdId);
paramList.Add("Lx", model.Lx);
paramList.Add("Txlj", model.txlj);
using (IDbConnection conn = new DapperConnection().DbConnection)
{
//判断重名
sbSql.Clear();
sbSql.Append("SELECT * FROM xt_zhb WHERE zh=@Zh");
JsModel sameName = conn.QueryFirstOrDefault <JsModel>(sbSql.ToString(), paramList);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同的账号名"
});
}
sbSql.Clear();
sbSql.Append("INSERT INTO xt_zhb (zh, mm, createtime, dh, lxr, ys, yybh, jsid, zt, bz,Lx,QdId,txlj) VALUES (@Zh, @Mm, @CreateTime, @Dh, @Lxr, @Ys, @Yybh, @Jsid, @Zt, @Bz,@Lx,@QdId,@Txlj)");
int result = conn.Execute(sbSql.ToString(), paramList);
if (result.Equals(1))
{
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 医院端企业订单导出
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public DataTable ExportOrderYyQyList(Hashtable ht)
{
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddly"].ToString()))
{
sd.Append(" and a.ddly = @ddly");
paramList.Add("ddly", ht["ddly"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddstart"].ToString()))
{
sd.Append(" and a.intime>= @ddstart ");
paramList.Add("ddstart", ht["ddstart"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddend"].ToString()))
{
sd.Append(" and a.intime<= @ddend ");
paramList.Add("ddend", ht["ddend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["yystart"].ToString()))
{
sd.Append(" and a.yykssj>= @yystart ");
paramList.Add("yystart", ht["yystart"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyend"].ToString()))
{
sd.Append(" and a.yykssj<= @yyend ");
paramList.Add("yyend", ht["yyend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh = @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm = @xm");
paramList.Add("xm", ht["xm"].ToString());
}
if (!string.IsNullOrEmpty(ht["tczt"].ToString()))
{
sd.Append(" and a.ddzt=@tczt ");
paramList.Add("tczt", ht["tczt"].ToString());
}
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = @"select a.djlsh as '医院登记编号',a.dwmc as '企业名称',a.dwbh as '企业编号', a.tcjg as '套餐价格', a.xm as '姓名', case when a.xb=1 then '男' else '女' end as '性别'
, a.dh as '电话', case when a.ddzt=2 then '预约中' when a.ddzt=3 then '预约成功' when a.ddzt=4 then '退款中' when a.ddzt=5 then '退款完成'
when a.ddzt=6 then '已到检' when a.ddzt=7 then '已完成' when a.ddzt=8 then '已取消' else '已出报告' end as '订单状态', CONVERT(varchar(10),a.yykssj,120) as '预约时间'
from dd_jbxx_new a where 1=1";
sql += sd.ToString();
sql += sd.ToString();
var reader = conn.ExecuteReader(sql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 渠道端获取结算订单列表
/// </summary>
/// <returns></returns>
public List <DdjbxxModel> GetListQd(int qdid, string yymc, string fromdate, string todate, int sfjs, string xm, string dh, int sftk, int pageIndex, int pageSize, ref int count, ref decimal tkze, ref decimal ysjsze)
{
StringBuilder sbSql = new StringBuilder();
if (!string.IsNullOrEmpty(yymc))
{
sbSql.Append(" and c.jgmc LIKE @Yymc");
}
if (!string.IsNullOrEmpty(fromdate))
{
sbSql.Append(" and convert(varchar(10), djtime,120)>=@Fromdate");
}
if (!string.IsNullOrEmpty(todate))
{
sbSql.Append(" and convert(varchar(10), djtime,120)<=@Todate");
}
if (sfjs > -1)
{
sbSql.Append(" and a.sfjs=@Jszt");
}
if (!string.IsNullOrEmpty(xm))
{
sbSql.Append(" and a.xm = @Xm");
}
if (!string.IsNullOrEmpty(dh))
{
sbSql.Append(" and a.dh = @Dh");
}
if (sftk.Equals(0))
{
sbSql.Append(" and ISNULL(a.tkze,0) = 0");
}
else if (sftk.Equals(1))
{
sbSql.Append(" and a.tkze > 0");
}
string strWhere = sbSql.ToString();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
DynamicParameters paramList = new DynamicParameters();
sbSql.Clear();
sbSql.Append("SELECT ddbh, c.jgmc, dsfdd, tcmc, xm, dh, ysjsj, tkze, sfjs FROM dd_jbxx a JOIN xt_dsfbz b ON a.dsfbzid=b.id LEFT JOIN xt_jgb c ON a.yybh=c.yybh WHERE a.ddly=0 and a.ddzt in(2,3,6,7,9) and a.dsfbzid=@Qdid");
sbSql.Append(strWhere);
string sqlPage = "";
int fromRow = (pageIndex - 1) * pageSize;
int toRow = (pageIndex) * pageSize;
paramList.Add("Yymc", "%" + yymc + "%");
paramList.Add("Fromdate", fromdate);
paramList.Add("Todate", todate);
paramList.Add("Jszt", sfjs);
paramList.Add("Xm", xm);
paramList.Add("Dh", dh);
paramList.Add("FromRow", fromRow);
paramList.Add("ToRow", toRow);
paramList.Add("Qdid", qdid);
sqlPage = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ddbh DESC) AS rowNum, * FROM (" + sbSql + ") AS T ) AS N WHERE rowNum >@FromRow AND rowNum <=@ToRow ";
List <DdjbxxModel> li = conn.Query <DdjbxxModel>(sqlPage, paramList).ToList();
//count = conn.Query<int>("SELECT COUNT(1) FROM (" + sbSql + ") AS t ", paramList).FirstOrDefault();
sbSql.Clear();
sbSql.Append($"SELECT isnull(sum(ysjsj),0) as ysjsze, isnull(sum(tkze),0) as tkze, count(*) as total FROM dd_jbxx a JOIN xt_dsfbz b ON a.dsfbzid=b.id LEFT JOIN xt_jgb c ON a.yybh=c.yybh WHERE a.ddly=0 and a.ddzt in(2,3,6,7,9) and a.dsfbzid={qdid}");
sbSql.Append(strWhere);
var reader = conn.ExecuteReader(sbSql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
if (dt.Rows.Count > 0)
{
ysjsze = Convert.ToDecimal(dt.Rows[0]["ysjsze"]);
tkze = Convert.ToDecimal(dt.Rows[0]["tkze"]);
count = Convert.ToInt32(dt.Rows[0]["total"]);
}
dt.Load(reader);
return(li);
}
}
/// <summary>
/// 修改账号
/// </summary>
/// <returns></returns>
public ReturnModel Update(XtZhbModel model)
{
try
{
if (string.IsNullOrWhiteSpace(model.zh))
{
return new ReturnModel {
Code = 201, Msg = "账号不能为空"
}
}
;
if (string.IsNullOrWhiteSpace(model.mm))
{
return new ReturnModel {
Code = 201, Msg = "医院编号不能为空"
}
}
;
if (model.jsid.Equals(0))
{
return new ReturnModel {
Code = 201, Msg = "请选择角色"
}
}
;
if (string.IsNullOrWhiteSpace(model.lxr))
{
return new ReturnModel {
Code = 201, Msg = "姓名不能为空"
}
}
;
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
DynamicParameters paramList = new DynamicParameters();
paramList.Add("Id", model.id);
paramList.Add("Zh", model.zh);
paramList.Add("Mm", model.mm);
paramList.Add("Dh", model.dh);
paramList.Add("Lxr", model.lxr);
paramList.Add("Ys", model.ys);
paramList.Add("Yybh", model.yybh);
paramList.Add("Jsid", model.jsid);
paramList.Add("Zt", model.zt);
paramList.Add("Bz", model.bz);
//判断账号存在
sbSql.Append("SELECT * FROM xt_zhb WHERE Id=@Id");
XtZhbModel existModel = conn.QueryFirstOrDefault <XtZhbModel>(sbSql.ToString(), paramList);
if (existModel == null)
{
return(new ReturnModel {
Code = 201, Msg = "账号不存在"
});
}
//判断重名
sbSql.Clear();
sbSql.Append("SELECT * FROM xt_zhb WHERE id<>@Id AND zh=@Zh");
JsModel sameName = conn.QueryFirstOrDefault <JsModel>(sbSql.ToString(), paramList);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同的账号名"
});
}
//保存更新
sbSql.Clear();
sbSql.Append("UPDATE xt_zhb SET zh=@Zh, mm=@Mm, dh=@Dh, lxr=@Lxr, ys=@Ys, jsid=@Jsid, zt=@Zt, bz=@Bz WHERE Id=@Id");
int result = conn.Execute(sbSql.ToString(), paramList);
if (result.Equals(1))
{
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 修改角色
/// </summary>
/// <returns></returns>
public ReturnModel UpdateJs(JsModel model)
{
try
{
if (string.IsNullOrWhiteSpace(model.Jsmc))
{
return(new ReturnModel {
Code = 201, Msg = "角色名称不能为空"
});
}
if (model.Lx == 1 || model.Lx == 3)
{
if (string.IsNullOrWhiteSpace(model.Yybh))
{
return(new ReturnModel {
Code = 201, Msg = "医院编号不能为空"
});
}
}
else
{
if (string.IsNullOrWhiteSpace(model.QdId.ToString()))
{
return(new ReturnModel {
Code = 201, Msg = "渠道不能为空"
});
}
}
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
//判断角色存在
sbSql.Append("SELECT * FROM xt_jsglb WHERE Id=@Id");
JsModel existModel = conn.QueryFirstOrDefault <JsModel>(sbSql.ToString(), new { Id = model.Id });
if (existModel == null)
{
return(new ReturnModel {
Code = 201, Msg = "角色不存在"
});
}
//判断重名
sbSql.Clear();
sbSql.Append("SELECT * FROM xt_jsglb WHERE Id<>@Id AND Jsmc=@Jsmc and lx=" + model.Lx.ToString());
JsModel sameName = conn.QueryFirstOrDefault <JsModel>(sbSql.ToString(), model);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的角色"
});
}
//保存更新
sbSql.Clear();
sbSql.Append("UPDATE xt_jsglb SET Jsmc=@Jsmc, Bz=@Bz, Yybh=@Yybh, Gxrq=@Gxrq, Gxr=@Gxr WHERE Id=@Id");
int result = conn.Execute(sbSql.ToString(), model);
if (result.Equals(1))
{
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 企业员工，预约详情,导出
/// </summary>
/// <param name="ht"></param>
/// <returns></returns>
public DataTable GetQyygYyxqListExcel(Hashtable ht)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select a.ydjh as 医院登记号,a.xm as 性名,a.dwmc as 部门,a.tcmc as 套餐名称,case a.xb when 1 then '男' else '女' end as 性别,a.sfzh as 身份证号, a.dh as 联系电话,case a.sfyy when 1 then '已预约' else '未预约' end as 预约状态, b.yykssj as 预约日期 from qy_ygxx a left join dd_jbxx_new b on a.yybh=b.yybh and a.ydjh=b.dsfdd where 1=1";
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfyy"].ToString()))
{
sd.Append(" and a.sfyy=@sfyy");
paramList.Add("sfyy", ht["sfyy"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dept"].ToString()))
{
sd.Append(" and a.dwbh=@dept");
paramList.Add("dept", ht["dept"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm like @xm");
paramList.Add("xm", "%" + ht["xm"].ToString() + "%");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh= @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfzh"].ToString()))
{
sd.Append(" and a.sfzh=@sfzh");
paramList.Add("sfzh", ht["sfzh"].ToString());
}
if (!string.IsNullOrEmpty(ht["yykssj"].ToString()))
{
sd.Append(" and b.yykssj>= @yykssj");
paramList.Add("yykssj", ht["yykssj"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyjssj"].ToString()))
{
sd.Append(" and b.yykssj<= @yyjssj");
paramList.Add("yyjssj", ht["yyjssj"].ToString());
}
sql += sd.ToString();
DataTable dt = new DataTable();
var reader = conn.ExecuteReader(sql, paramList);
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 修改
/// </summary>
/// <returns></returns>
public ReturnModel Update(Wjgz model)
{
try
{
if (string.IsNullOrWhiteSpace(model.GZMC))
{
return(new ReturnModel {
Code = 201, Msg = "规则名称不能为空"
});
}
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
//判断标签存在
sbSql.Append("SELECT * FROM WJ_GZ WHERE ID=@ID AND SFQY=1");
Wjgz existModel = conn.QueryFirstOrDefault <Wjgz>(sbSql.ToString(), new { ID = model.ID }, transaction);
if (existModel == null)
{
return(new ReturnModel {
Code = 201, Msg = "规则不存在"
});
}
//判断重名
sbSql.Clear();
sbSql.Append("SELECT * FROM WJ_GZ WHERE ID<>@ID AND GZMC=@GZMC AND SFQY=1");
Wjgz sameName = conn.QueryFirstOrDefault <Wjgz>(sbSql.ToString(), model, transaction);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的规则"
});
}
//保存更新
sbSql.Clear();
sbSql.Append("UPDATE WJ_GZ SET GZMC = @GZMC, ZXNL = @ZXNL, ZDNL = @ZDNL, XB=@XB WHERE ID = @ID;");
sbSql.Append("DELETE FROM WJ_GZMX WHERE GZID = @ID;");
conn.Execute(sbSql.ToString(), model, transaction);
if (!string.IsNullOrWhiteSpace(model.XZBQ))
{
List <string> lstBq = model.XZBQ.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var item in lstBq)
{
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_GZMX(GZID, BQID) VALUES(@GZID, @BQID)");
conn.Execute(sbSql.ToString(), new { GZID = model.ID, BQID = item }, transaction);
}
}
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ReturnModel Insert(Wjtm model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.TMNR))
{
return(new ReturnModel {
Code = 201, Msg = "题目内容不能为空"
});
}
if (model.ListTMXX == null || model.ListTMXX.Count < 2)
{
return(new ReturnModel {
Code = 201, Msg = "题目选项少于2个"
});
}
//保存
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
sbSql.Append("SELECT MAX(PX) FROM WJ_WJTM WHERE WJID=@WJID");
int maxPx = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), model, transaction));
maxPx += 1;
sbSql.Clear();
model.PX = maxPx;
sbSql.Append("INSERT INTO WJ_WJTM(WJID, TMNR, PX, XGR, XGSJ, TMLX) VALUES (@WJID, @TMNR, @PX, @XGR, @XGSJ, @TMLX);select @@IDENTITY");
int tmid = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), model, transaction));
foreach (var xx in model.ListTMXX)
{
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TMXX(TMID, XXNR) VALUES(@TMID, @XXNR);select @@IDENTITY");
int xxid = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), new { TMID = tmid, XXNR = xx.XXNR }, transaction));
if (xx.ListTMXXBQ == null || xx.ListTMXXBQ.Count <= 0)
{
continue;
}
foreach (var bq in xx.ListTMXXBQ)
{
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TMXXBQ(XXID, BQID) VALUES(@XXID, @BQID)");
conn.Execute(sbSql.ToString(), new { XXID = xxid, BQID = bq.BQID }, transaction);
}
}
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
public void Save(PqQytDto model)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
StringBuilder strSql = new StringBuilder();
if (!string.IsNullOrEmpty(model.pqbh))
{
strSql.Append("DELETE FROM dbo.pq_qy_rq WHERE pqbh=@pqbh;");
strSql.Append("DELETE FROM dbo.pq_qy_pjtc WHERE pqbh=@pqbh;");
strSql.Append("UPDATE dbo.pq_qy SET ksrq=@ksrq,jsrq=@jsrq,jzsj=@jzsj,xxr=@xxr,tqts=@tqts,tjrs=@tjrs WHERE pqbh=@pqbh");
}
else
{
model.pqbh = GetPqbh();
strSql.Append("insert into pq_qy(pqbh,yybh,qdid,ksrq,jsrq,cjsj,jzsj,xxr,tqts,tjrs) values (@pqbh,@yybh,@qdid,@ksrq,@jsrq,@cjsj,@jzsj,@xxr,@tqts,@tjrs);");
}
conn.Execute(strSql.ToString(), model, transaction);
strSql.Clear();
foreach (var item in model.pjtcList)
{
strSql.Append("insert into pq_qy_pjtc(");
strSql.Append("pqbh,tcbh,tjrs)");
strSql.Append(" values (");
strSql.Append("@pqbh,@tcbh,@tjrs)");
item.pqbh = model.pqbh;
conn.Execute(strSql.ToString(), item, transaction);
strSql.Clear();
}
for (int i = 0; i < model.jsrq.Subtract(model.ksrq).TotalDays + 1; i++)
{
DateTime rq = model.ksrq.AddDays(i);
PqQyRqModel rqModel = new PqQyRqModel();
rqModel.pqbh = model.pqbh;
rqModel.qdid = model.qdid;
rqModel.rq = model.ksrq.AddDays(i);
rqModel.tjrs = model.tjrs;
rqModel.yybh = model.yybh;
rqModel.flag = GetFlag(model.ksrq.AddDays(i), model.xxr);
strSql.Append("insert into pq_qy_rq(");
strSql.Append("pqbh,yybh,qdid,rq,tjrs,flag)");
strSql.Append(" values (");
strSql.Append("@pqbh,@yybh,@qdid,@rq,@tjrs,@flag)");
conn.Execute(strSql.ToString(), rqModel, transaction);
strSql.Clear();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
/// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ReturnModel Insert(Wjgz model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.GZMC))
{
return(new ReturnModel {
Code = 201, Msg = "规则名称不能为空"
});
}
//保存
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
//判断重名
sbSql.Append("SELECT * FROM WJ_GZ WHERE GZMC=@GZMC and SFQY=1");
Wjgz sameName = conn.QueryFirstOrDefault <Wjgz>(sbSql.ToString(), model, transaction);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的规则"
});
}
sbSql.Clear();
model.SFQY = 1;
sbSql.Append("INSERT INTO WJ_GZ(GZMC, ZXNL, ZDNL, SFQY, XB) VALUES (@GZMC, @ZXNL, @ZDNL, @SFQY, @XB);select @@IDENTITY");
int gzid = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), model, transaction));
if (!string.IsNullOrWhiteSpace(model.XZBQ))
{
List <string> lstBq = model.XZBQ.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var item in lstBq)
{
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_GZMX(GZID, BQID) VALUES(@GZID, @BQID)");
conn.Execute(sbSql.ToString(), new { GZID = gzid, BQID = item }, transaction);
}
}
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 追加保存
/// </summary>
/// <param name="model"></param>
public void ZjSave(PqQyszModel model)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("UPDATE dbo.pq_qysz SET jsrq=@jsrq WHERE pqbh=@pqbh");
conn.Execute(strSql.ToString(), model, transaction);
strSql.Clear();
model.SjdList = JsonConvert.DeserializeObject <List <Sjd> >(model.sjd);
for (int i = 0; i < model.jsrq.Subtract(model.ksrq).TotalDays + 1; i++)
{
DateTime rq = model.ksrq.AddDays(i);
PqTjrqModel rqModel = new PqTjrqModel();
rqModel.pqbh = model.pqbh;
rqModel.qybh = model.qybh;
rqModel.rq = model.ksrq.AddDays(i);
rqModel.tjrs = model.SjdList.Sum(x => x.tjrs);
rqModel.yybh = model.yybh;
rqModel.flag = GetFlag(model.ksrq.AddDays(i), model.xxr);
strSql.Append("insert into pq_tjrq(");
strSql.Append("pqbh,yybh,qybh,rq,tjrs,flag)");
strSql.Append(" values (");
strSql.Append("@pqbh,@yybh,@qybh,@rq,@tjrs,@flag)");
conn.Execute(strSql.ToString(), rqModel, transaction);
strSql.Clear();
foreach (var item in model.SjdList)
{
PqTjsjModel sjModel = new PqTjsjModel();
sjModel.yybh = model.yybh;
sjModel.pqbh = model.pqbh;
sjModel.qybh = model.qybh;
sjModel.rq = rqModel.rq;
sjModel.tjrs = item.tjrs;
sjModel.kssj = Convert.ToDateTime(rqModel.rq.ToString("yyyy-MM-dd") + " " + item.kssj);
sjModel.jssj = Convert.ToDateTime(rqModel.rq.ToString("yyyy-MM-dd") + " " + item.jssj);
strSql.Append("insert into pq_tjsj(");
strSql.Append("pqbh,yybh,qybh,rq,kssj,jssj,tjrs)");
strSql.Append(" values (");
strSql.Append("@pqbh,@yybh,@qybh,@rq,@kssj,@jssj,@tjrs)");
conn.Execute(strSql.ToString(), sjModel, transaction);
strSql.Clear();
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
/// <summary>
/// 保存
/// </summary>
/// <param name="model"></param>
/// <param name="pjtc"></param>
/// <param name="pjtcrs"></param>
public void Save(PqQyszModel model, string[] pjtc, string[] pjtcrs, string[] pjtcXxr, string[] pjtcSfxz)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
StringBuilder strSql = new StringBuilder();
if (!string.IsNullOrEmpty(model.pqbh))
{
strSql.Append("DELETE FROM dbo.pq_tjrq WHERE pqbh=@pqbh;");
strSql.Append("DELETE FROM dbo.pq_tjsj WHERE pqbh=@pqbh;");
strSql.Append("DELETE FROM dbo.pq_pjtc WHERE pqbh=@pqbh;");
strSql.Append("DELETE FROM dbo.pq_pjtcgz WHERE pqbh=@pqbh;");
strSql.Append("UPDATE dbo.pq_qysz SET ksrq=@ksrq,jsrq=@jsrq,jzsj=@jzsj,xxr=@xxr,tqts=@tqts,sjd=@sjd,tjrs=@tjrs,tjrs_dy=@tjrs_dy WHERE pqbh=@pqbh");
}
else
{
model.pqbh = GetPqbh();
strSql.Append("insert into pq_qysz(pqbh,yybh,qybh,ksrq,jsrq,cjsj,jzsj,xxr,tqts,sjd,tjrs,tjrs_dy) values (@pqbh,@yybh,@qybh,@ksrq,@jsrq,@cjsj,@jzsj,@xxr,@tqts,@sjd,@tjrs,@tjrs_dy);");
}
conn.Execute(strSql.ToString(), model, transaction);
strSql.Clear();
//foreach (var item in pjtcList)
//{
// strSql.Append("insert into pq_pjtc(");
// strSql.Append("pqbh,tcbh,tjrs)");
// strSql.Append(" values (");
// strSql.Append("@pqbh,@tcbh,@tjrs)");
// item.pqbh = model.pqbh;
// conn.Execute(strSql.ToString(), item, transaction);
// strSql.Clear();
//}
if (pjtc != null && pjtcrs != null)
{
for (int i = 0; i < pjtc.Length; i++)
{
var fzbh = GetFzbh();
var tjrs = Convert.ToInt32(pjtcrs[i]);
var xxr = pjtcXxr[i];
var sfxz = Convert.ToInt32(pjtcSfxz[i]);
strSql.Append("insert into pq_pjtcgz(");
strSql.Append("pqbh,fzbh,tjrs,xxr,sfxz)");
strSql.Append(" values (");
strSql.Append("@pqbh,@fzbh,@tjrs,@xxr,@sfxz)");
conn.Execute(strSql.ToString(), new { pqbh = model.pqbh, fzbh = fzbh, tjrs = tjrs, xxr = xxr, sfxz = sfxz }, transaction);
strSql.Clear();
foreach (var item in pjtc[i].Split(','))
{
strSql.Append("insert into pq_pjtc(");
strSql.Append("pqbh,tcbh,fzbh)");
strSql.Append(" values (");
strSql.Append("@pqbh,@tcbh,@fzbh)");
conn.Execute(strSql.ToString(), new { pqbh = model.pqbh, fzbh = fzbh, tcbh = item }, transaction);
strSql.Clear();
}
}
}
for (int i = 0; i < model.jsrq.Subtract(model.ksrq).TotalDays + 1; i++)
{
DateTime rq = model.ksrq.AddDays(i);
PqTjrqModel rqModel = new PqTjrqModel();
rqModel.pqbh = model.pqbh;
rqModel.qybh = model.qybh;
rqModel.rq = model.ksrq.AddDays(i);
rqModel.tjrs = model.SjdList.Sum(x => x.tjrs);
rqModel.yybh = model.yybh;
rqModel.flag = GetFlag(model.ksrq.AddDays(i), model.xxr);
strSql.Append("insert into pq_tjrq(");
strSql.Append("pqbh,yybh,qybh,rq,tjrs,flag)");
strSql.Append(" values (");
strSql.Append("@pqbh,@yybh,@qybh,@rq,@tjrs,@flag)");
conn.Execute(strSql.ToString(), rqModel, transaction);
strSql.Clear();
foreach (var item in model.SjdList)
{
PqTjsjModel sjModel = new PqTjsjModel();
sjModel.yybh = model.yybh;
sjModel.pqbh = model.pqbh;
sjModel.qybh = model.qybh;
sjModel.rq = rqModel.rq;
sjModel.tjrs = item.tjrs;
sjModel.kssj = Convert.ToDateTime(rqModel.rq.ToString("yyyy-MM-dd") + " " + item.kssj);
sjModel.jssj = Convert.ToDateTime(rqModel.rq.ToString("yyyy-MM-dd") + " " + item.jssj);
strSql.Append("insert into pq_tjsj(");
strSql.Append("pqbh,yybh,qybh,rq,kssj,jssj,tjrs)");
strSql.Append(" values (");
strSql.Append("@pqbh,@yybh,@qybh,@rq,@kssj,@jssj,@tjrs)");
conn.Execute(strSql.ToString(), sjModel, transaction);
strSql.Clear();
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
/// <summary>
/// 保存权限
/// </summary>
/// <returns></returns>
public ReturnModel Save(int jsId, string yybh, List <string> lstMenuId)
{
try
{
StringBuilder sbSql = new StringBuilder();
DynamicParameters paramList;
List <KeyValuePair <string, object> > lstKeyValuePair = new List <KeyValuePair <string, object> >();
List <JsqxModel> lstModels = new List <JsqxModel>();
int result = 0;
//判断角色存在
JsModel jsModel = new JsglbService().GetJs(jsId);
if (jsModel == null)
{
return new ReturnModel {
Code = 201, Msg = "角色不存在"
}
}
;
//遍历权限列表
foreach (string menu in lstMenuId)
{
lstModels.Add(new JsqxModel {
JsId = jsId, MenuId = Convert.ToInt32(menu), Yybh = yybh
});
}
//删除原有权限
sbSql.Append("DELETE FROM xt_jsqxb WHERE jsid=@JsId AND yybh=@Yybh");
paramList = new DynamicParameters();
paramList.Add("JsId", jsId);
paramList.Add("Yybh", yybh);
lstKeyValuePair.Add(new KeyValuePair <string, object>(sbSql.ToString(), paramList));
//重新插入权限列表
foreach (var model in lstModels)
{
sbSql.Clear();
paramList = new DynamicParameters();
paramList.Add("JsId", model.JsId);
paramList.Add("MenuId", model.MenuId);
paramList.Add("Yybh", model.Yybh);
sbSql.Append("INSERT INTO xt_jsqxb (menuid, jsid, yybh) VALUES (@MenuId, @JsId, @Yybh)");
lstKeyValuePair.Add(new KeyValuePair <string, object>(sbSql.ToString(), paramList));
}
result = DapperConnection.ExecuteTransaction(lstKeyValuePair);
if (result < 0)
{
return new ReturnModel {
Code = 201, Msg = "保存失败"
}
}
;
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 导出医院端渠道订单
/// </summary>
/// <param name="ht"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataTable ExportOrderYyQdList(Hashtable ht)
{
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dsfbz"].ToString()))
{
sd.Append(" and a.dsfbzid = @dsfbz");
paramList.Add("dsfbz", ht["dsfbz"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddly"].ToString()))
{
sd.Append(" and a.ddly = @ddly");
paramList.Add("ddly", ht["ddly"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddstart"].ToString()))
{
sd.Append(" and a.intime>= @ddstart ");
paramList.Add("ddstart", ht["ddstart"].ToString());
}
if (!string.IsNullOrEmpty(ht["ddend"].ToString()))
{
sd.Append(" and a.intime<= @ddend ");
paramList.Add("ddend", ht["ddend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["yystart"].ToString()))
{
sd.Append(" and a.yykssj>= @yystart ");
paramList.Add("yystart", ht["yystart"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyend"].ToString()))
{
sd.Append(" and a.yykssj<= @yyend ");
paramList.Add("yyend", ht["yyend"].ToString() + " 23:59:59");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh = @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm = @xm");
paramList.Add("xm", ht["xm"].ToString());
}
if (!string.IsNullOrEmpty(ht["tczt"].ToString()))
{
sd.Append(" and a.ddzt=@tczt ");
paramList.Add("tczt", ht["tczt"].ToString());
}
//sd.Append(" and a.ddbh in (" + ddbhs + ")");
using (IDbConnection conn = new DapperConnection().DbConnection)
{
//string sql = @"select a.dsfdd as '订单编号', b.dsfbz as '渠道名称', a.tcmc as '套餐名称', a.tcjg as '套餐价格', a.xm as '姓名', case when a.xb=1 then '男' else '女' end as '性别'
// , a.dh as '电话', case when a.ddzt=2 then '预约中' when a.ddzt=3 then '预约成功' when a.ddzt=4 then '退款中' when a.ddzt=5 then '退款完成'
// when a.ddzt=6 then '已到检' when a.ddzt=7 then '已完成' when a.ddzt=8 then '已取消' else '已出报告' end as '订单状态', CONVERT(varchar(10),a.yykssj,120) as '预约时间'
// from dd_jbxx a join xt_dsfbz b on a.dsfbzid=b.id where 1=1";
//sql += sd.ToString();
string sql = @"select '' as 工卡号,a.dwbh as 单位,a.xm as 姓名,a.csrq as 出生日期,a.tcmc as 分组名称,
a.dh as 联系电话,case a.hz when 1 then '已婚' else '未婚' end as 婚姻, '' as 标签个数, a.zjhm as 身份证号,'' as 职称,'' as 职务,'' as 人员类别,'' as 体检类别,a.dh as 手机号码,'' as 电子邮件,0 as 是否VIP ,a.yykssj as 预定体检日期
from dd_jbxx_new a where 1 = 1";
sql += sd.ToString();
var reader = conn.ExecuteReader(sql.ToString(), paramList);
DataTable dt = new DataTable();
dt.Load(reader);
return(dt);
}
}
/// <summary>
/// 修改
/// </summary>
/// <returns></returns>
public ReturnModel Update(Wjtm model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.TMNR))
{
return(new ReturnModel {
Code = 201, Msg = "题目内容不能为空"
});
}
if (model.ListTMXX == null || model.ListTMXX.Count < 2)
{
return(new ReturnModel {
Code = 201, Msg = "题目选项少于2个"
});
}
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
//判断题目存在
sbSql.Append("SELECT * FROM WJ_WJTM WHERE Id=@Id");
Wjtm existModel = conn.QueryFirstOrDefault <Wjtm>(sbSql.ToString(), new { Id = model.ID }, transaction);
if (existModel == null)
{
return(new ReturnModel {
Code = 201, Msg = "题目不存在"
});
}
//保存题目
sbSql.Clear();
sbSql.Append("UPDATE WJ_WJTM SET TMNR = @TMNR, XGR = @XGR, XGSJ = @XGSJ, TMLX = @TMLX WHERE ID = @ID");
conn.Execute(sbSql.ToString(), model, transaction);
//保存选项
foreach (var xx in model.ListTMXX)
{
//删除选项标签
sbSql.Clear();
sbSql.Append("DELETE FROM WJ_TMXXBQ WHERE XXID IN (SELECT ID FROM WJ_TMXX WHERE TMID=@TMID);");
conn.Execute(sbSql.ToString(), new { TMID = xx.ID }, transaction);
//删除选项
sbSql.Clear();
sbSql.Append("DELETE FROM WJ_TMXX WHERE TMID=@TMID;");
conn.Execute(sbSql.ToString(), new { TMID = model.ID }, transaction);
//重新插入选项
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TMXX(TMID, XXNR) VALUES(@TMID, @XXNR);select @@IDENTITY");
int xxid = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), new { TMID = model.ID, XXNR = xx.XXNR }, transaction));
//重新插入标签
foreach (var bq in xx.ListTMXXBQ)
{
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TMXXBQ(XXID, BQID) VALUES(@XXID, @BQID)");
conn.Execute(sbSql.ToString(), new { XXID = xxid, BQID = bq.BQID }, transaction);
}
}
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
/// <summary>
/// 新增角色
/// </summary>
/// <returns></returns>
public ReturnModel AddJs(JsModel model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.Jsmc))
{
return(new ReturnModel {
Code = 201, Msg = "角色名称不能为空"
});
}
if (model.Lx == 1 || model.Lx == 3)
{
if (string.IsNullOrWhiteSpace(model.Yybh))
{
return(new ReturnModel {
Code = 201, Msg = "医院编号不能为空"
});
}
}
else
{
if (string.IsNullOrWhiteSpace(model.QdId.ToString()))
{
return(new ReturnModel {
Code = 201, Msg = "渠道不能为空"
});
}
}
//保存
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
//判断重名
sbSql.Append("SELECT * FROM xt_jsglb WHERE Jsmc=@Jsmc and Lx=" + model.Lx.ToString());
JsModel sameName = conn.QueryFirstOrDefault <JsModel>(sbSql.ToString(), model);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的角色"
});
}
sbSql.Clear();
sbSql.Append("INSERT INTO xt_jsglb (Jsmc, Bz, Yybh, Cjrq, Cjr, Gxrq, Gxr,Lx,QdId) VALUES (@Jsmc, @Bz, @Yybh, @Cjrq, @Cjr, @Gxrq, @Gxr,@Lx,@QdId)");
int result = conn.Execute(sbSql.ToString(), model);
if (result.Equals(1))
{
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
catch (Exception e)
{
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
public ReturnModel PostToCalculate(List <string> listDdbh, string yybh, int qdid, int tjr)
{
try
{
//验证参数
if (listDdbh.Count.Equals(0))
{
return(new ReturnModel {
Code = 201, Msg = "请选择订单"
});
}
StringBuilder sbSql = new StringBuilder();
//生成流水号
string jsbh = new XT_ZZB().GetJsLsh(qdid);
//保存
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
//获取订单基本信息
sbSql.Append("SELECT * FROM dd_jbxx WHERE ddbh IN @ids");
var listDd = conn.Query <DdjbxxModel>(sbSql.ToString(), new { ids = listDdbh }, transaction).ToList();
int result = 0;
decimal ysjsze = listDd.Sum(x => x.ysjsj);
decimal tkze = listDd.Sum(x => x.tkze);
//插入结算基本信息表获取自增Id
sbSql.Clear();
sbSql.Append("INSERT INTO qd_jsjbxx (jsbh, yybh, qdid, zt, tjsj, tjr, ysjsze, sjjsze, tkze) VALUES (@Jsbh, @Yybh, @Qdid, @Zt, @Tjsj, @Tjr, @Ysjsze, @Sjjsze, @Tkze);select @@IDENTITY");
int jsjbxxid = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), new { Jsbh = jsbh, Yybh = yybh, Qdid = qdid, Zt = 1, Tjsj = DateTime.Now, Tjr = tjr, Ysjsze = ysjsze, Sjjsze = ysjsze - tkze, Tkze = tkze }, transaction));
foreach (var model in listDd)
{
//插入结算明细
sbSql.Clear();
sbSql.Append("INSERT INTO qd_jsjbxxmx (jbxxid, ddbh, ysjsj, tkze, jstz, sjjsj) VALUES (@Jbxxid, @Ddbh, @Ysjsj, @Tkze, @Jstz, @Sjjsj)");
conn.Execute(sbSql.ToString(), new { Jbxxid = jsjbxxid, Ddbh = model.ddbh, Ysjsj = model.ysjsj, Tkze = model.tkze, Jstz = 0, Sjjsj = model.ysjsj - model.tkze }, transaction);
}
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "提交成功"
});
}
catch (Exception ex)
{
transaction.Rollback();
Log.WriteLog(ex.Message);
return(new ReturnModel {
Code = 201, Msg = "提交失败"
});
}
}
}
catch (Exception ex)
{
Log.WriteLog(ex.Message);
return(new ReturnModel {
Code = 201, Msg = "提交失败"
});
}
}
/// <summary>
/// 企业预约管理列表
/// </summary>
/// <param name="dwbh"></param>
/// <param name="pqstart"></param>
/// <param name="paend"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <QyyyModel> GetQyygjbxxList(string yybh, string dwbh, string pqstart, string pqend, int pageIndex, int pageSize, ref int count)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = @"select a.id, a.yybh, b.bh as dwbh ,b.mc as dwmc,a.ksrq,a.jsrq,c.tjrs,d.ylme,isnull(e.yyrs,0) as yyrs,isnull(h.djrs,0) as djrs from pq_qysz a join qy_jbxx b on a.yybh=b.yybh and a.qybh=b.bh
join(select count(1) as tjrs, left(dwbh, 4) as dwbh, yybh from qy_ygxx group by yybh, left(dwbh, 4)) c on a.yybh = c.yybh and a.qybh = c.dwbh--体检人数
join(select sum(tjrs) as ylme, pqbh from pq_tjrq group by pqbh) d on a.pqbh = d.pqbh--预留名额
left join(select count(1) as yyrs, left(dwbh, 4) as dwbh, yybh from qy_ygxx where sfyy = 1 group by yybh, left(dwbh, 4)) e on a.yybh = e.yybh and a.qybh = e.dwbh--预约人数
left join(select count(1) as djrs, left(dwbh, 4) as dwbh, yybh from dd_jbxx_new where ddly = 1 and ddzt in (6, 7, 9) group by yybh,left(dwbh, 4)) h on a.yybh = h.yybh and a.qybh = h.dwbh --到检人数
where 1=1";
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(yybh))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", yybh);
}
if (!string.IsNullOrEmpty(dwbh))
{
sd.Append(" and a.qybh=@dwbh");
paramList.Add("dwbh", dwbh);
}
if (!string.IsNullOrEmpty(pqstart))
{
sd.Append(" and a.jsrq>=@ksrq");
paramList.Add("ksrq", pqstart);
}
if (!string.IsNullOrEmpty(pqend))
{
sd.Append(" and a.jsrq<=@jsrq");
paramList.Add("jsrq", pqend);
}
sql += sd.ToString();
string sqlNew = "";
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
sqlNew = "Select * From (Select ROW_NUMBER() Over (Order By id desc) As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "";
List <QyyyModel> li = conn.Query <QyyyModel>(sqlNew, paramList).ToList();
foreach (QyyyModel model in li)
{
model.yyl = (model.yyrs * 1.0 / model.tjrs * 100).ToString("0.##") + "%";
if (model.yyrs == 0)
{
model.djjd = "0.00%";
}
else
{
model.djjd = (model.djrs * 1.0 / model.tjrs * 100).ToString("0.##") + "%";
}
if (DateTime.Now < model.ksrq)
{
model.tjzt = "未开始";
}
else if (DateTime.Now > model.jsrq)
{
model.tjzt = "已完成";
}
else
{
model.tjzt = "进行中";
}
}
count = conn.Query <int>("Select Count(1) From (" + sql + ") As t ", paramList).FirstOrDefault();
return(li);
}
}
/// <summary>
/// 企业员工，预约详情
/// </summary>
/// <param name="ht"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <QyygxxModel> GetQyygYyxqList(Hashtable ht, int pageIndex, int pageSize, ref int count)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select a.*,b.yykssj,b.yyjssj from qy_ygxx a left join dd_jbxx_new b on a.yybh=b.yybh and a.ydjh=b.dsfdd where 1=1";
StringBuilder sd = new StringBuilder();
DynamicParameters paramList = new DynamicParameters();
if (!string.IsNullOrEmpty(ht["yybh"].ToString()))
{
sd.Append(" and a.yybh = @yybh");
paramList.Add("yybh", ht["yybh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfyy"].ToString()))
{
sd.Append(" and a.sfyy=@sfyy");
paramList.Add("sfyy", ht["sfyy"].ToString());
}
if (!string.IsNullOrEmpty(ht["dwbh"].ToString()))
{
sd.Append(" and left(a.dwbh,4)=@dwbh");
paramList.Add("dwbh", ht["dwbh"].ToString());
}
if (!string.IsNullOrEmpty(ht["dept"].ToString()))
{
sd.Append(" and a.dwbh=@dept");
paramList.Add("dept", ht["dept"].ToString());
}
if (!string.IsNullOrEmpty(ht["xm"].ToString()))
{
sd.Append(" and a.xm like @xm");
paramList.Add("xm", "%" + ht["xm"].ToString() + "%");
}
if (!string.IsNullOrEmpty(ht["dh"].ToString()))
{
sd.Append(" and a.dh= @dh");
paramList.Add("dh", ht["dh"].ToString());
}
if (!string.IsNullOrEmpty(ht["sfzh"].ToString()))
{
sd.Append(" and a.sfzh=@sfzh");
paramList.Add("sfzh", ht["sfzh"].ToString());
}
if (!string.IsNullOrEmpty(ht["yykssj"].ToString()))
{
sd.Append(" and b.yykssj>= @yykssj");
paramList.Add("yykssj", ht["yykssj"].ToString());
}
if (!string.IsNullOrEmpty(ht["yyjssj"].ToString()))
{
sd.Append(" and b.yykssj<= @yyjssj");
paramList.Add("yyjssj", ht["yyjssj"].ToString());
}
sql += sd.ToString();
string sqlNew = "";
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
sqlNew = "Select * From (Select ROW_NUMBER() Over (Order By id desc) As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "";
List <QyygxxModel> li = conn.Query <QyygxxModel>(sqlNew, paramList).ToList();
count = conn.Query <int>("Select Count(1) From (" + sql + ") As t ", paramList).FirstOrDefault();
return(li);
}
}