/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
public ReturnModel Delete(int id)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("DELETE FROM WJ_WJTM WHERE ID=@ID;");
sbSql.Append("DELETE FROM WJ_TMXXBQ WHERE XXID IN (SELECT ID FROM WJ_TMXX WHERE TMID=@ID);");
sbSql.Append("DELETE FROM WJ_TMXX WHERE TMID=@ID;");
conn.Execute(sbSql.ToString(), new { ID = id }, transaction);
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "删除成功"
});
}
catch (Exception ex)
{
transaction.Rollback();
Log.WriteLog(ex.Message);
return(new ReturnModel {
Code = 201, Msg = "删除失败"
});
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
public ReturnModel Delete(int id)
{
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
sbSql.Append($"DELETE FROM WJ_TCBQ WHERE GLID={id}");
conn.Execute(sbSql.ToString(), null, transaction);
sbSql.Clear();
sbSql.Append($"DELETE FROM WJ_TC WHERE ID={id}");
conn.Execute(sbSql.ToString(), null, transaction);
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "删除成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "删除失败"
});
}
}
}
public ReturnModel BatchComplete(string ddbhs, int ddzt)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
List <string> listDdbh = ddbhs.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
var sbSql = new StringBuilder();
sbSql.Append("update dd_jbxx_new set ddzt=@Ddzt where ddbh in @Ddbh;");
if (ddzt.Equals(7))
{
sbSql.Append("update dd_zhxm set sfdj=1 where ddbh in @Ddbh;");
}
conn.Execute(sbSql.ToString(), new { Ddzt = ddzt, Ddbh = listDdbh }, transaction);
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "保存成功"
});
}
catch (Exception ex)
{
transaction.Rollback();
Log.WriteLog("批量完成失败" + ex.Message);
return(new ReturnModel {
Code = 201, Msg = "保存失败"
});
}
}
}
/// <summary>
/// 同步组合项目
/// </summary>
/// <param name="li"></param>
public void InsertOrUpdate(List <XtZhxmbModel> li)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select zhxmbh from xt_zhxmb a where yybh='" + li[0].yybh + "'";
List <XtZhxmbModel> liOld = conn.Query <XtZhxmbModel>(sql, null).ToList();
string sqlcz = "";
IDbTransaction transaction = conn.BeginTransaction();
foreach (XtZhxmbModel model in li)
{
string zhxmms = string.IsNullOrEmpty(model.zhxmms) ? "" : model.zhxmms;
int i = liOld.Where(p => p.zhxmbh == model.zhxmbh.Trim()).Count();
if (i > 0)
{
sqlcz = "update xt_zhxmb set zhxmmc=@zhxmmc,zhxmms=@zhxmms,zhxmjg=@zhxmjg,xb=@xb,sffk=@sffk,sfqy=@sfqy,zhxmksbh=@zhxmksbh,zhxmksmc=@zhxmksmc, sxrs=@sxrs,updatetime=getdate() where yybh=@yybh and zhxmbh=@zhxmbh and zhxmbh=@zhxmbh;";
conn.Execute(sqlcz, model, transaction);
}
else
{
sqlcz = "insert into xt_zhxmb(yybh,zhxmbh,zhxmmc,zhxmms,zhxmjg,xb,sffk,sfqy,zhxmksbh,zhxmksmc,createtime,updatetime,sxrs) values(@yybh, @zhxmbh, @zhxmmc,@zhxmms,@zhxmjg,@xb,@sffk,@sfqy,@zhxmksbh,@zhxmksmc,getdate(),getdate(),@sxrs);";
conn.Execute(sqlcz, model, transaction);
}
}
transaction.Commit();
}
}
public void CreateOrder(List <OrderModel> li)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
foreach (OrderModel model in li)
{
string ddbh = new DdJbxxService().GetDdbh();
int sfjx = 0;
string sql = @"insert into dd_jbxx(ddbh,dsfdd,dsfbzid,ddzt,tcid,tcmc,dwbh,tcjg,
jxbjg,ddze,intime,sfout,outtime,dh,xm,xb,hz,
zjlx,zjhm,yykssj,yyjssj,sfdj,djtime,sfbg,bgtime,
djlsh,sfjx,jxlist,sfjs,csrq,nl,remark,yybh,ddly,dwmc,jsbz,ygzh,trade_no,ysjsj)
values(@ddbh,@dsfdd,@dsfbzid,2,@tcid,@tcmc,@dwbh,@tcjg,
0,@ddze,getdate(),0,'1970-01-01',@dh,@xm,@xb,@hz,
@zjlx,@zjhm,@yysj,@yysj,0,'1970-01-01',0,'1970-01-01',
'',@sfjx,@jxlist,0,@csrq,@nl,'',@yybh,0,@dwmc,0,'',@trade_no,@ysjsj) ";
int id = conn.Execute(sql, new
{
ddbh = ddbh,
dsfdd = model.outOrderCode,
dsfbzid = model.dsfbzId,
tcid = model.appointmentPackageCode,
tcmc = model.appointmentPackageName,
dwbh = model.deptID,
tcjg = model.ddJe,
ddze = model.ddJe,
dh = model.customerMobilePhone,
xm = model.customerName,
xb = model.customerGender,
hz = model.customerMedicalStatus,
zjlx = model.customerIDType,
zjhm = model.customerIDCard,
yysj = model.appointmentDate,
sfjx = sfjx,
jxlist = "",
csrq = model.customerBirthday,
nl = model.customerAge,
yybh = model.yybh,
dwmc = model.deptNm,
trade_no = "",
ysjsj = model.ysjsj
}, transaction);
string sqlDetail = "insert into dd_zhxm(ddbh,zhxmbh,zhxmmc,jg,sfjx,sfdj) select '" + ddbh + "',b.zhxmbh,b.zhxmmc,b.zhxmjg,0,0 from xt_tc_zhxmb a join xt_zhxmb b on a.yybh=b.yybh and a.zhxmbh=b.zhxmbh where a.yybh='" + model.yybh + "' and a.tcbh='" + model.appointmentPackageCode + "' and a.dwbh='" + model.deptID + "'";
conn.Execute(sqlDetail, null, transaction);
}
transaction.Commit();
}
}
/// <summary>
/// 创建中间平台订单
/// </summary>
/// <param name="model"></param>
/// <param name="tcmc"></param>
/// <param name="nl"></param>
/// <returns></returns>
public int CreateOrder(PreOrder model, string tcmc, string ddbh, string dwmc, decimal ysjsj = 0)
{
int sfjx = 0;
string jxlist = "";
string jxlist2 = "";
if (model.items != null && model.items.Count > 0)
{
sfjx = 1;
foreach (var item in model.items)
{
if (item.isAdd == 1)
{
jxlist += item.itemCode + ",";
jxlist2 += "'" + item.itemCode + "',";
}
}
}
jxlist = jxlist.TrimEnd(',');
jxlist2 = jxlist2.TrimEnd(',');
string sql = @"insert into dd_jbxx(ddbh,dsfdd,dsfbzid,ddzt,tcid,tcmc,dwbh,tcjg,jxbjg,ddze,intime,sfout,outtime,dh,xm,xb,hz,zjlx,zjhm,yykssj,yyjssj,sfdj,djtime,sfbg,bgtime,djlsh,sfjx,jxlist,sfjs,csrq,nl,remark,yybh,ddly,dwmc,jsbz,ygzh,trade_no,ysjsj)
values(@ddbh,@dsfdd,@dsfbzid,2,@tcid,@tcmc,@dwbh,@tcjg,
0,@ddze,getdate(),0,'1970-01-01',@dh,@xm,@xb,@hz,
@zjlx,@zjhm,@yysj,@yysj,0,'1970-01-01',0,'1970-01-01',
'',@sfjx,@jxlist,0,@csrq,@nl,'',@yybh,0,@dwmc,0,'',@trade_no,@ysjsj) ";
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
int id = conn.Execute(sql, new
{
ddbh = ddbh, dsfdd = model.outOrderCode, dsfbzid = model.interfaceNum, tcid = model.appointmentPackageCode, tcmc = tcmc, dwbh = model.deptID, tcjg = model.ddJe,
ddze = model.ddJe, dh = model.customerMobilePhone, xm = model.customerName, xb = model.customerGender, hz = model.customerMedicalStatus,
zjlx = model.customerIDType, zjhm = model.customerIDCard, yysj = model.appointmentDate,
sfjx = sfjx, jxlist = jxlist, csrq = model.customerBirthday, nl = model.customerAge, yybh = model.yybh, dwmc = dwmc,
trade_no = string.IsNullOrEmpty(model.tradeNo)?"":model.tradeNo, ysjsj = ysjsj
}, transaction);
string sqlDetail = "insert into dd_zhxm(ddbh,zhxmbh,zhxmmc,jg,sfjx,sfdj) select '" + ddbh + "',b.zhxmbh,b.zhxmmc,b.zhxmjg,0,0 from xt_tc_zhxmb a join xt_zhxmb b on a.yybh=b.yybh and a.zhxmbh=b.zhxmbh where a.yybh='" + model.yybh + "' and a.tcbh='" + model.appointmentPackageCode + "' and a.dwbh='" + model.deptID + "'";
if (jxlist2.Length > 1)
{
string sqlDetailJx = "insert into dd_zhxm(ddbh,zhxmbh,zhxmmc,jg,sfjx,sfdj) select '" + ddbh + "',b.zhxmbh,b.zhxmmc,b.zhxmjg,1,0 from xt_zhxmb b where b.yybh='" + model.yybh + "' and b.zhxmbh in (" + jxlist2 + ")";
conn.Execute(sqlDetailJx, null, transaction);
}
conn.Execute(sqlDetail, null, transaction);
transaction.Commit();
return(id);
}
}
/// <summary>
/// 保存加项包
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int SaveJxb(QyJxbJbxxModel model)
{
int i = 0;
using (IDbConnection conn = new DapperConnection().DbConnection)
{
List <QyJxbTcModel> litc = new List <QyJxbTcModel>();
foreach (string a in model.tcbhs.Split(','))
{
XttcbModel item = new XttcbService().GetTc(model.yybh, a);
litc.Add(new QyJxbTcModel()
{
yybh = model.yybh, tcbh = item.tcbh, tcmc = item.tcmc
});
}
List <QyJxbZhxmModel> lizhxm = new List <QyJxbZhxmModel>();
foreach (string a in model.zhxmbhs.Split(','))
{
XtZhxmbModel item = new XtzhxmService().GetZhxm(model.yybh, a);
lizhxm.Add(new QyJxbZhxmModel()
{
yybh = model.yybh, jg = item.zhxmjg, zhxmbh = item.zhxmbh, zhxmmc = item.zhxmmc
});
}
IDbTransaction transaction = conn.BeginTransaction();
if (model.id == 0)
{
string sql = "insert into qy_jxbjbxx(yybh,qybh,qymc,jxbmc,lrjs,syrq,sfqy,jg,xb,jsj,createtime,updatetime) values(@yybh,@qybh,@qymc,@jxbmc,@lrjs,@syrq,@sfqy,@jg,@xb,@jsj,getdate(),getdate());SELECT SCOPE_IDENTITY()";
string id = conn.ExecuteScalar(sql, model, transaction).ToString();
model.id = int.Parse(id);
}
else
{
string sql = "delete from qy_jxbzhxm where jxbid=@id;delete from qy_jxbtc where jxbid=@id; update qy_jxbjbxx set jxbmc=@jxbmc,lrjs=@lrjs,syrq=@syrq,sfqy=@sfqy,jg=@jg,xb=@xb,jsj=@jsj,updatetime=getdate() where id=@id";
conn.Execute(sql, model, transaction).ToString();
}
string sqlZhxm = "insert into qy_jxbzhxm(jxbid,yybh,zhxmbh,zhxmmc,jg)values(" + model.id + ",@yybh,@zhxmbh,@zhxmmc,@jg)";
conn.Execute(sqlZhxm, lizhxm, transaction);
string sqlTc = "insert into qy_jxbtc(jxbid,yybh,tcbh,tcmc)values(" + model.id + ",@yybh,@tcbh,@tcmc)";
conn.Execute(sqlTc, litc, transaction);
transaction.Commit();
i = 1;
}
return(model.id);
}
public void ConfirmOrder(DdjbxxModel ddModel, List <DdZhxmModel> zhxmList, QyygxxModel ygModel)
{
StringBuilder strSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
if (ddModel.jsbz == 0)//自已才更新
{
strSql.AppendFormat("UPDATE dbo.qy_ygxx SET sfyy=@sfyy WHERE id=@id");
conn.Execute(strSql.ToString(), new { sfyy = ygModel.sfyy, id = ygModel.id }, transaction);
}
strSql.Clear();
if (string.IsNullOrEmpty(ddModel.csrq))
{
ddModel.csrq = DateTime.Now.ToString("yyyy-MM-dd");
}
strSql.Append("insert into dd_jbxx_new(");
strSql.Append("trade_no,zffs,ddbh,dsfdd,dsfbzid,ddzt,tcid,tcmc,dwbh,tcjg,jxbjg,ddze,intime,sfout,outtime,dh,xm,xb,hz,zjlx,zjhm,yykssj,yyjssj,sfdj,djtime,sfbg,bgtime,djlsh,sfjx,jxlist,sfjs,csrq,nl,remark,yybh,ddly,dwmc,jsbz,ygzh)");
strSql.Append(" values (");
strSql.Append("'',@zffs,@ddbh,@dsfdd,@dsfbzid,@ddzt,@tcid,@tcmc,@dwbh,@tcjg,@jxbjg,@ddze,@intime,@sfout,@outtime,@dh,@xm,@xb,@hz,@zjlx,@zjhm,@yykssj,@yyjssj,@sfdj,@djtime,@sfbg,@bgtime,@djlsh,@sfjx,@jxlist,@sfjs,@csrq,@nl,@remark,@yybh,@ddly,@dwmc,@jsbz,@ygzh)");
conn.Execute(strSql.ToString(), ddModel, transaction);
strSql.Clear();
strSql.Append("insert into dd_zhxm(");
strSql.Append("ddbh,zhxmbh,zhxmmc,jg,sfjx,sfdj)");
strSql.Append(" values (");
strSql.Append("@ddbh,@zhxmbh,@zhxmmc,@jg,@sfjx,@sfdj)");
conn.Execute(strSql.ToString(), zhxmList, transaction);
transaction.Commit();
}
catch (Exception ex)
{
LogApiHelper.AddLog(ex.Message);
transaction.Rollback();
throw;
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
public ReturnModel Delete(int id)
{
StringBuilder sbSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
var transaction = conn.BeginTransaction();
try
{
sbSql.Append($"SELECT * FROM WJ_JG WHERE ID={id}");
Wjjg model = conn.QueryFirstOrDefault <Wjjg>(sbSql.ToString(), null, transaction);
if (model == null)
{
return new ReturnModel {
Code = 201, Msg = "医院不存在"
}
}
;
sbSql.Clear();
sbSql.Append($"DELETE FROM WJ_TCBQ WHERE GLID IN (SELECT id FROM WJ_TC WHERE TCID IN (SELECT TCID FROM TC_TJ WHERE YYBH='{model.YYBH}'))");
conn.Execute(sbSql.ToString(), null, transaction);
sbSql.Clear();
sbSql.Append($"DELETE FROM WJ_TC WHERE TCID IN (SELECT tcid FROM TC_TJ WHERE yybh='{model.YYBH}')");
conn.Execute(sbSql.ToString(), null, transaction);
sbSql.Append($"DELETE FROM WJ_JG WHERE ID={id}");
conn.Execute(sbSql.ToString(), null, transaction);
transaction.Commit();
return(new ReturnModel {
Code = 200, Msg = "删除成功"
});
}
catch (Exception e)
{
transaction.Rollback();
Log.WriteLog(e.Message);
return(new ReturnModel {
Code = 201, Msg = "删除失败"
});
}
}
}
public void InsertOrUpdate(List <QyygxxModel> li)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
List <QyygxxModel> liUpdate = new List <QyygxxModel>();
List <QyygxxModel> liInsert = new List <QyygxxModel>();
foreach (var item in li)
{
if (item.sfzh.Length >= 6)
{
item.mm = item.sfzh.Substring(item.sfzh.Length - 6, 6);
}
else
{
item.mm = "000666";
}
string sql = "select yybh,dwbh,ydjh from qy_ygxx a where yybh='" + item.yybh + "' and dwbh='" + item.dwbh + "' and ydjh='" + item.ydjh + "' ";
List <QyygxxModel> liOld = conn.Query <QyygxxModel>(sql, null).ToList();
if (liOld.Count > 0)
{
liUpdate.Add(item);
}
else
{
liInsert.Add(item);
}
}
IDbTransaction transaction = conn.BeginTransaction();
string sqlcz = "";
if (liUpdate.Count > 0)
{
sqlcz = "update qy_ygxx set rybh=@rybh, mm=@mm,tcbh=@tcbh,tcmc=@tcmc,dwbh=@dwbh,dwmc=@dwmc,xb=@xb,hz=@hz,jg=@jg,xm=@xm,dh=@dh,csrq=@csrq,dwgdje=@dwgdje,sfzh=@sfzh,createtime=getdate() where yybh=@yybh and dwbh=@dwbh and ydjh=@ydjh;";
conn.Execute(sqlcz, liUpdate, transaction);
}
if (liInsert.Count > 0)
{
sqlcz = "insert into qy_ygxx(rybh,yybh,dwbh,dwmc,tcbh,tcmc,jg,sfzh,xm,dh,xb,hz,dwgdje,csrq,sfyy,mm,ydjh,createtime) values(@rybh,@yybh,@dwbh,@dwmc,@tcbh,@tcmc,@jg,@sfzh,@xm,@dh,@xb,@hz,@dwgdje,@csrq,0,@mm,@ydjh,getdate());";
conn.Execute(sqlcz, liInsert, transaction);
}
transaction.Commit();
}
}
public void Save(PqTjrqModel model, List <PqTjsjModel> list)
{
StringBuilder strSql = new StringBuilder();
using (IDbConnection conn = new DapperConnection().DbConnection)
{
IDbTransaction transaction = conn.BeginTransaction();
try
{
strSql.AppendLine("IF EXISTS(SELECT 1 FROM dbo.pq_tjrq WHERE qybh=@qybh AND rq=@rq)");
strSql.AppendLine("BEGIN");
strSql.AppendLine("update pq_tjrq SET flag=0,tjrs=@tjrs WHERE qybh=@qybh AND rq=@rq");
strSql.AppendLine("END");
strSql.AppendLine("ELSE ");
strSql.AppendLine("BEGIN");
strSql.AppendLine("insert into pq_tjrq(yybh,qybh,rq,tjrs,flag)values (@yybh,@qybh,@rq,@tjrs,0)");
strSql.AppendLine("END");
conn.Execute(strSql.ToString(), model, transaction);
strSql.Clear();
strSql.Append("DELETE FROM dbo.pq_tjsj WHERE qybh=@qybh AND rq=@rq ");
conn.Execute(strSql.ToString(), new { qybh = model.qybh, rq = model.rq }, transaction);
strSql.Clear();
foreach (var item in list)
{
strSql.Append("insert into pq_tjsj(");
strSql.Append("yybh,qybh,rq,kssj,jssj,tjrs,pqbh)");
strSql.Append(" values (");
strSql.Append("@yybh,@qybh,@rq,@kssj,@jssj,@tjrs,@pqbh)");
conn.Execute(strSql.ToString(), item, transaction);
strSql.Clear();
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
/// <summary>
/// 同步保存
/// </summary>
/// <param name="li"></param>
public void InsertOrUpdate(List <QyJbxxModel> li)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sqlold = "select id,bh,mc from qy_jbxx where yybh='" + li[0].yybh + "'";
List <QyJbxxModel> liOld = conn.Query <QyJbxxModel>(sqlold, null).ToList();
string sqlcz = "";
IDbTransaction transaction = conn.BeginTransaction();
List <QyJbxxModel> liUpdate = new List <QyJbxxModel>();
List <QyJbxxModel> liInsert = new List <QyJbxxModel>();
foreach (QyJbxxModel model in li)
{
int i = liOld.Where(p => p.bh == model.bh).Count();
if (i > 0)
{
model.id = liOld.Where(p => p.bh == model.bh).FirstOrDefault().id;
liUpdate.Add(model);
}
else
{
liInsert.Add(model);
}
}
if (liUpdate.Count > 0)
{
sqlcz = "update qy_jbxx set isdept=@isdept,mc=@mc,lxdh=@lxdh,@lxdz=@lxdz,updatetime=getdate() where id=@id";
conn.Execute(sqlcz, liUpdate, transaction);
}
if (liInsert.Count > 0)
{
sqlcz = "insert into qy_jbxx(sfqd,sfqy,isdept,yybh,bh,mc,dwfzr,lxdh,lxdz,createtime,updatetime) values(0,0,@isdept,@yybh,@bh,@mc,@dwfzr,@lxdh,@lxdz,getdate(),getdate());";
conn.Execute(sqlcz, liInsert, transaction);
}
transaction.Commit();
}
}
/// <summary>
/// 同步企业,渠道，个人套餐表
/// </summary>
/// <param name="li"></param>
public void InsertOrUpdate(List <XttcbModel> li, List <XttczhxmbModel> liZhxm)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select * from xt_tcb where yybh='" + li[0].yybh + "' and dwbh='" + li[0].dwbh + "'";
List <XttcbModel> liOld = conn.Query <XttcbModel>(sql, null).ToList();
string sqlcz = "";
IDbTransaction transaction = conn.BeginTransaction();
sqlcz = "delete from xt_tc_zhxmb where yybh='" + li[0].yybh + "' and dwbh='" + li[0].dwbh + "'; delete from xt_tcb where yybh='" + li[0].yybh + "' and dwbh='" + li[0].dwbh + "'";
conn.Execute(sqlcz, null, transaction);
sqlcz = "insert into xt_tcb(dsfbzid,tclx,tcbh,tcmc,dwbh,dwmc,jg,sfqy,xb,hz,yybh,createtime,updatetime) values(@dsfbzid,@tclx,@tcbh,@tcmc,@dwbh,@dwmc,@jg,@sfqy,@xb,@hz,@yybh,getdate(),getdate());";
conn.Execute(sqlcz, li, transaction);
string sqlinsertZhxm = "insert into xt_tc_zhxmb(zhxmbh,tcbh,yybh,dwbh)values(@zhxmbh,@tcbh,@yybh,@dwbh)";
conn.Execute(sqlinsertZhxm, liZhxm, transaction);
string sqlUpdatejg = "update xt_tcb set jsj=@jsj where yybh=@yybh and dwbh=@dwbh and tcbh=@tcbh";
conn.Execute(sqlUpdatejg, liOld, transaction);
transaction.Commit();
}
}
/// <summary>
/// 同步企业员工
/// </summary>
/// <param name="li"></param>
public void InsertOrUpdate(List <QyygxxModel> li, string dwbh)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select yybh,dwbh,ydjh from qy_ygxx a where yybh='" + li[0].yybh + "' and left(dwbh,4)='" + dwbh + "'";
List <QyygxxModel> liOld = conn.Query <QyygxxModel>(sql, null).ToList();
List <QyygxxModel> liUpdate = new List <QyygxxModel>();
List <QyygxxModel> liInsert = new List <QyygxxModel>();
string sqlcz = "";
IDbTransaction transaction = conn.BeginTransaction();
foreach (QyygxxModel model in li)
{
int i = liOld.Where(p => p.yybh == model.yybh && p.dwbh == model.dwbh && p.ydjh == model.ydjh.Trim()).Count();
if (i > 0)
{
liUpdate.Add(model);
}
else
{
liInsert.Add(model);
}
}
if (liUpdate.Count > 0)
{
sqlcz = "update qy_ygxx set rybh=@rybh, mm=@mm,tcbh=@tcbh,tcmc=@tcmc,dwbh=@dwbh,dwmc=@dwmc,xb=@xb,hz=@hz,jg=@jg,xm=@xm,dh=@dh,csrq=@csrq,dwgdje=@dwgdje,sfzh=@sfzh,createtime=getdate() where yybh=@yybh and dwbh=@dwbh and ydjh=@ydjh;";
conn.Execute(sqlcz, liUpdate, transaction);
}
if (liInsert.Count > 0)
{
sqlcz = "insert into qy_ygxx(rybh,yybh,dwbh,dwmc,tcbh,tcmc,jg,sfzh,xm,dh,xb,hz,dwgdje,csrq,sfyy,mm,ydjh,createtime) values(@rybh,@yybh,@dwbh,@dwmc,@tcbh,@tcmc,@jg,@sfzh,@xm,@dh,@xb,@hz,@dwgdje,@csrq,0,@mm,@ydjh,getdate());";
conn.Execute(sqlcz, liInsert, transaction);
}
transaction.Commit();
}
}
/// <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>
/// <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;
}
}
}
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 = "保存失败"
});
}
}
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>
/// <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>
/// <param name="model"></param>
/// <returns></returns>
public ReturnModel Insert(Wjtc model)
{
try
{
//验证参数
if (string.IsNullOrWhiteSpace(model.TCID))
{
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_TC WHERE TCID=@TCID and SFQY=1");
Wjbq sameName = conn.QueryFirstOrDefault <Wjbq>(sbSql.ToString(), model, transaction);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的套餐"
});
}
sbSql.Clear();
model.SFQY = 1;
sbSql.Append("INSERT INTO WJ_TC(TCID, SYRQ, TCZY, SFQY) VALUES (@TCID, @SYRQ, @TCZY, @SFQY);select @@IDENTITY");
int id = Convert.ToInt32(conn.ExecuteScalar(sbSql.ToString(), model, transaction));
if (!string.IsNullOrWhiteSpace(model.BQIDS))
{
//标签列表
List <string> lstBq = model.BQIDS.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
//深度列表
List <string> lstSd = model.BQSDS.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var item in lstBq)
{
var sd = lstSd[lstBq.IndexOf(item)];
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TCBQ(GLID, BQID, JCSD) VALUES (@GLID, @BQID, @JCSD)");
conn.Execute(sbSql.ToString(), new { GLID = id, BQID = item, JCSD = sd }, 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 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>
/// <returns></returns>
public ReturnModel Update(Wjtc model)
{
try
{
if (string.IsNullOrWhiteSpace(model.TCID))
{
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_TC WHERE Id=@Id AND SFQY=1");
Wjbq existModel = conn.QueryFirstOrDefault <Wjbq>(sbSql.ToString(), new { Id = model.ID }, transaction);
if (existModel == null)
{
return(new ReturnModel {
Code = 201, Msg = "标签不存在"
});
}
//判断重名
sbSql.Clear();
sbSql.Append("SELECT * FROM WJ_TC WHERE ID<>@ID AND TCID=@TCID and SFQY=1");
Wjbq sameName = conn.QueryFirstOrDefault <Wjbq>(sbSql.ToString(), model, transaction);
if (sameName != null)
{
return(new ReturnModel {
Code = 201, Msg = "存在相同名称的套餐"
});
}
//保存更新
sbSql.Clear();
model.SFQY = 1;
sbSql.Append("UPDATE WJ_TC SET TCID = @TCID, SYRQ = @SYRQ, TCZY = @TCZY, SFQY = @SFQY WHERE ID = @ID;");
sbSql.Append("DELETE FROM WJ_TCBQ WHERE GLID = @ID;");
conn.Execute(sbSql.ToString(), model, transaction);
if (!string.IsNullOrWhiteSpace(model.BQIDS))
{
//标签列表
List <string> lstBq = model.BQIDS.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
//深度列表
List <string> lstSd = model.BQSDS.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var item in lstBq)
{
var sd = lstSd[lstBq.IndexOf(item)];
sbSql.Clear();
sbSql.Append("INSERT INTO WJ_TCBQ(GLID, BQID, JCSD) VALUES (@GLID, @BQID, @JCSD)");
conn.Execute(sbSql.ToString(), new { GLID = model.ID, BQID = item, JCSD = sd }, 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 = "保存失败"
});
}
}
