501 lines
20 KiB
C#
501 lines
20 KiB
C#
using Dapper;
|
|
using dccdc.Models;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class sqgwjdDal
|
|
{
|
|
public List<sqgwjdModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
return conn.Query<sqgwjdModel>(sql, new { @id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<sqgwjdModel> GetAllList(string id, string zt)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
if (!string.IsNullOrEmpty(zt))
|
|
{
|
|
sql += " and zt=@zt";
|
|
}
|
|
return conn.Query<sqgwjdModel>(sql, new { @id = id, @zt = zt }).ToList();
|
|
}
|
|
}
|
|
|
|
public object save(sqgwjdModel model)
|
|
{
|
|
string sql = "";
|
|
if (model.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[sqgwjd]
|
|
([sqdh]
|
|
,[sqsj]
|
|
,[sqrid]
|
|
,[sqr]
|
|
,[ksid]
|
|
,[ksmc]
|
|
,[je]
|
|
,[bz]
|
|
,[mx]
|
|
,[fyly]
|
|
,[fydh]
|
|
,[zt]
|
|
,[spr1]
|
|
,[sptime1]
|
|
,[spzt1]
|
|
,[spr2]
|
|
,[sptime2]
|
|
,[spzt2]
|
|
,[spr3]
|
|
,[sptime3]
|
|
,[spzt3]
|
|
,[spr4]
|
|
,[sptime4]
|
|
,[spzt4])
|
|
VALUES
|
|
(@sqdh
|
|
,@sqsj
|
|
,@sqrid
|
|
,@sqr
|
|
,@ksid
|
|
,@ksmc
|
|
,@je
|
|
,@bz
|
|
,@mx
|
|
,@fyly
|
|
,@fydh
|
|
,@zt
|
|
,@spr1
|
|
,@sptime1
|
|
,@spzt1
|
|
,@spr2
|
|
,@sptime2
|
|
,@spzt2
|
|
,@spr3
|
|
,@sptime3
|
|
,@spzt3
|
|
,@spr4
|
|
,@sptime4
|
|
,@spzt4)";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [dbo].[sqgwjd]
|
|
SET [sqdh] = @sqdh
|
|
,[sqsj] = @sqsj
|
|
,[sqrid] = @sqrid
|
|
,[sqr] = @sqr
|
|
,[ksid] = @ksid
|
|
,[ksmc] = @ksmc
|
|
,[je] = @je
|
|
,[bz] = @bz
|
|
,[mx] = @mx
|
|
,[fyly] = @fyly
|
|
,[fydh] = @fydh
|
|
,[zt] = @zt
|
|
,[spr1] = @spr1
|
|
,[sptime1] = @sptime1
|
|
,[spzt1] = @spzt1
|
|
,[spr2] = @spr2
|
|
,[sptime2] = @sptime2
|
|
,[spzt2] = @spzt2
|
|
,[spr3] = @spr3
|
|
,[sptime3] = @sptime3
|
|
,[spzt3] = @spzt3
|
|
,[spr4] = @spr4
|
|
,[sptime4] = @sptime4
|
|
,[spzt4] = @spzt4
|
|
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<sqgwjdModel> getListBydh(string dh)
|
|
{
|
|
string sql = "select * from sqgwjd where sqdh in (@dh)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<sqgwjdModel>(sql, new { dh = dh }).ToList();
|
|
}
|
|
}
|
|
|
|
|
|
public object opSave(string id, int ksid, string ksmc, string items, int sqrid, string sqr, string je, string bz, string jdsj, string ddry, string jdld, string sxrs, string pcrs, string lxdh, string zrs, string ycbz, string yjbz, string qtbz, string gzcbz, string gzcrs, string zsbz, string zsrs, string qt, string jdbg, string jcfj, int zt, string fyly1, string fyly2, string fyly, string fydh, string fydhs, string fydh2)
|
|
{
|
|
string errmsg = "";
|
|
string sqdh = getckkdh(out errmsg);
|
|
if (errmsg != "")
|
|
return new { State = 0, Message = errmsg };
|
|
sqgwjdModel model = new sqgwjdModel();
|
|
model.sqdh = sqdh;
|
|
model.sqrid = sqrid;
|
|
model.sqr = sqr;
|
|
model.sqsj = DateTime.Now;
|
|
model.tjsj = DateTime.Now;
|
|
model.ksid = ksid;
|
|
model.ksmc = ksmc;
|
|
model.je = Convert.ToDecimal(je);
|
|
model.bz = bz;
|
|
model.jdsj = Convert.ToDateTime(jdsj);
|
|
model.ddry = ddry;
|
|
model.jdld = jdld;
|
|
model.sxrs = Convert.ToInt32(sxrs);
|
|
model.pcrs = Convert.ToInt32(pcrs);
|
|
model.lxdh = lxdh;
|
|
model.zrs = Convert.ToInt32(zrs);
|
|
model.ycbz = ycbz;
|
|
model.yjbz = Convert.ToDecimal(yjbz);
|
|
model.qtbz = Convert.ToDecimal(qtbz);
|
|
model.gzcbz = Convert.ToDecimal(gzcbz);
|
|
model.gzcrs = Convert.ToInt32(gzcrs);
|
|
model.zsbz = Convert.ToDecimal(zsbz);
|
|
model.zsrs = Convert.ToInt32(zsrs);
|
|
model.qt = qt;
|
|
model.jdbg = jdbg;
|
|
model.jcfj = jcfj;
|
|
model.fyly1 = Convert.ToBoolean(fyly1);
|
|
model.fyly2 = Convert.ToBoolean(fyly2);
|
|
model.fyly = fyly;
|
|
model.fydh = fydh;
|
|
model.fydhs = fydhs;
|
|
model.fydh2 = fydh2;
|
|
model.zt = zt;
|
|
|
|
string sql = @"INSERT INTO [dbo].[sqgwjd]
|
|
([sqdh],[sqsj],[tjsj],[sqrid],[sqr],[ksid],[ksmc],[je],[bz],
|
|
[jdsj],[ddry],[jdld],[sxrs],[pcrs],[lxdh],[zrs],[ycbz],[yjbz],[qtbz],[gzcbz],[gzcrs],[zsbz],[zsrs],[qt],[jdbg],[jcfj],[fyly1],[fyly2],[fyly],[fydh],[fydhs],[fydh2],[zt])
|
|
VALUES
|
|
(@sqdh,@sqsj,@tjsj,@sqrid,@sqr,@ksid,@ksmc,@je,@bz,
|
|
@jdsj,@ddry,@jdld,@sxrs,@pcrs,@lxdh,@zrs,@ycbz,@yjbz,@qtbz,@gzcbz,@gzcrs,@zsbz,@zsrs,@qt,@jdbg,@jcfj,@fyly1,@fyly2,@fyly,@fydh,@fydhs,@fydh2,@zt)select SCOPE_IDENTITY()";
|
|
|
|
int sqdid = 0;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
sqdid = conn.Query<int>(sql, model).FirstOrDefault();
|
|
|
|
//附件修改
|
|
string sqlfj = @"update sqfile set sqid=" + sqdid + " where type='公务接待' and sqid=0 and sqrid=" + sqrid;
|
|
conn.Execute(sqlfj);
|
|
return new { State = 1, Message = "操作成功", id = sqdid };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public object opSave2(string id, int ksid, string ksmc, string items, int sqrid, string sqr, string je, string bz, string jdsj, string ddry, string jdld, string sxrs, string pcrs, string lxdh, string zrs, string ycbz, string yjbz, string qtbz, string gzcbz, string gzcrs, string zsbz, string zsrs, string qt, string jdbg, string jcfj, int zt, string fyly1, string fyly2, string fyly, string fydh, string fydhs, string fydh2)
|
|
{
|
|
sqgwjdModel model = new sqgwjdModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.je = Convert.ToDecimal(je);
|
|
model.bz = bz;
|
|
model.jdsj = Convert.ToDateTime(jdsj);
|
|
model.ddry = ddry;
|
|
model.jdld = jdld;
|
|
model.sxrs = Convert.ToInt32(sxrs);
|
|
model.pcrs = Convert.ToInt32(pcrs);
|
|
model.lxdh = lxdh;
|
|
model.zrs = Convert.ToInt32(zrs);
|
|
model.ycbz = ycbz;
|
|
model.yjbz = Convert.ToDecimal(yjbz);
|
|
model.qtbz = Convert.ToDecimal(qtbz);
|
|
model.gzcbz = Convert.ToDecimal(gzcbz);
|
|
model.gzcrs = Convert.ToInt32(gzcrs);
|
|
model.zsbz = Convert.ToDecimal(zsbz);
|
|
model.zsrs = Convert.ToInt32(zsrs);
|
|
model.qt = qt;
|
|
model.jdbg = jdbg;
|
|
model.jcfj = jcfj;
|
|
model.fyly1 = Convert.ToBoolean(fyly1);
|
|
model.fyly2 = Convert.ToBoolean(fyly2);
|
|
model.fyly = fyly;
|
|
model.fydh = fydh;
|
|
model.fydhs = fydhs;
|
|
model.fydh2 = fydh2;
|
|
model.zt = zt;
|
|
|
|
string sql = @"UPDATE [dbo].[sqgwjd]
|
|
SET [je] = @je,[bz] = @bz,
|
|
[jdsj]= @jdsj,[ddry]= @ddry,[jdld]= @jdld,[sxrs]= @sxrs,[pcrs]= @pcrs,[lxdh]= @lxdh,[zrs]= @zrs,[ycbz]= @ycbz,[yjbz]= @yjbz,[qtbz]= @qtbz,[gzcbz]= @gzcbz,[gzcrs]= @gzcrs,[zsbz]= @zsbz,[zsrs]= @zsrs,[qt]= @qt,[jdbg]= @jdbg,[jcfj]= @jcfj,[fyly1] = @fyly1,[fyly2] = @fyly2,[fyly] = @fyly,[fydh] = @fydh,[fydhs] = @fydhs,[fydh2] = @fydh2,[zt] = @zt
|
|
WHERE id=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
conn.Execute(sql, model);
|
|
return new { State = 1, Message = "操作成功", id = id };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public object opSp(string id, int sprid, string spr, string yj, bool ty)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqgwjdModel model = conn.Query<sqgwjdModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "";
|
|
int zt = 0;
|
|
switch (model.zt)
|
|
{
|
|
case 1:
|
|
sql = "update sqgwjd set sprid1=@sprid, spr1=@spr,sptime1=@sptime,spnr1=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 2; } else { zt = -1; }
|
|
break;
|
|
case 2:
|
|
sql = "update sqgwjd set sprid2=@sprid, spr2=@spr,sptime2=@sptime,spnr2=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 7; } else { zt = -2; }
|
|
break;
|
|
case 7:
|
|
sql = "update sqgwjd set sprid3=@sprid, spr3=@spr,sptime3=@sptime,spnr3=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 8; } else { zt = -7; }
|
|
break;
|
|
}
|
|
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @sprid = sprid, @spr = spr, @sptime = model.jdsj.AddMinutes(10), @spnr = yj, @zt = zt });
|
|
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 opSpOneself(string id, int sprid, string spr, string yj, bool ty, string zw)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqgwjdModel model = conn.Query<sqgwjdModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "";
|
|
switch (zw)
|
|
{
|
|
case "科室领导":
|
|
sql = "update sqgwjd set sprid1=@sprid, spr1=@spr,sptime1=@sptime,spnr1=@spnr where id=@id";
|
|
break;
|
|
case "分管领导":
|
|
sql = "update sqgwjd set sprid2=@sprid, spr2=@spr,sptime2=@sptime,spnr2=@spnr where id=@id";
|
|
break;
|
|
}
|
|
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @sprid = sprid, @spr = spr, @sptime = model.jdsj.AddMinutes(10), @spnr = yj });
|
|
if (result > 0)
|
|
{
|
|
return new { State = 1, Message = "修改成功" };
|
|
}
|
|
else
|
|
return new { State = 0, Message = "修改失败" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//获取申请单号
|
|
private string getckkdh(out string errmsg)
|
|
{
|
|
errmsg = "";
|
|
string result = "";
|
|
string start = DateTime.Now.ToString("yyyyMM") + "0001";
|
|
string end = DateTime.Now.ToString("yyyyMM") + "9999";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select top 1 sqdh from sqgwjd where sqdh >= '" + start + "' and sqdh<= '" + end + "' order by id desc";
|
|
try
|
|
{
|
|
var result2 = conn.ExecuteScalar(sql);
|
|
if (result2 == null)
|
|
result = start;
|
|
else
|
|
{
|
|
result = (Convert.ToInt32(result2) + 1).ToString();
|
|
if (result == end)
|
|
errmsg = "单号不足！";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
errmsg = ex.Message;
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public object delete(string id)
|
|
{
|
|
string sql = "delete from sqgwjd where id=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { id = id });
|
|
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 bcnr(string id, string bz, string bcnr1, string bcnr2, string bcnr3, string bcje)
|
|
{
|
|
string sql = "update sqgwjd set bcnr1=@bcnr1, bcnr2=@bcnr2,bcnr3=@bcnr3,bcje=@bcje,bcsj=@bcsj where id=@id";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @bcnr1 = bcnr1, @bcnr2 = bcnr2, @bcnr3 = bcnr3, @bcje = bcje, @bcsj = DateTime.Now });
|
|
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 int getCount(string key)
|
|
{
|
|
string sql = "select count(1) from dbo.sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and ksid = @key";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { key = key });
|
|
}
|
|
}
|
|
|
|
public List<sqgwjdModel> getPage(int page, int pagesize, string key)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqgwjd where 1=1";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and ksid = @key";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.sqgwjdModel>(sql, new { key = key }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getCountLevel(string where)
|
|
{
|
|
string sql = "select count(1) from dbo.sqgwjd";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public List<sqgwjdModel> getPageLevel(int page, int pagesize, string where)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqgwjd";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.sqgwjdModel>(sql).ToList();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|