615 lines
24 KiB
C#
615 lines
24 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 xmjfsysqDal
|
|
{
|
|
public List<xmjfsysqModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from xmjfsysq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
return conn.Query<xmjfsysqModel>(sql, new { @id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public string GetHaveid()
|
|
{
|
|
string sql = @"select fydhs from sqfyzc where fydhs is not null and fydhs!=''
|
|
union all
|
|
select fydhs from chuchai_sq where fydhs is not null and fydhs!=''
|
|
union all
|
|
select fydhs from sqwzcg where fydhs is not null and fydhs!=''
|
|
union all
|
|
select fydhs from sqwx where fydhs is not null and fydhs!=''
|
|
union all
|
|
select fydhs from sqjbyc where fydhs is not null and fydhs!=''
|
|
union all
|
|
select fydhs from sqgwjd where fydhs is not null and fydhs!=''";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
var list= conn.Query<string>(sql).ToList();
|
|
return string.Join(",", list.ToArray());
|
|
}
|
|
}
|
|
|
|
public List<xmjfsysqModel> GetAllList(string id, string zt)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from xmjfsysq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
if (!string.IsNullOrEmpty(zt))
|
|
{
|
|
sql += " and zt=@zt";
|
|
}
|
|
return conn.Query<xmjfsysqModel>(sql, new { @id = id, @zt = zt }).ToList();
|
|
}
|
|
}
|
|
|
|
public object save(xmjfsysqModel model)
|
|
{
|
|
string sql = "";
|
|
if (model.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[xmjfsysq]
|
|
([sqdh]
|
|
,[sqsj]
|
|
,[sqrid]
|
|
,[sqr]
|
|
,[ksid]
|
|
,[ksmc]
|
|
,[je]
|
|
,[yt]
|
|
,[bz]
|
|
,[zt]
|
|
,[spr1]
|
|
,[sptime1]
|
|
,[spzt1]
|
|
,[spr2]
|
|
,[sptime2]
|
|
,[spzt2]
|
|
,[spr3]
|
|
,[sptime3]
|
|
,[spzt3]
|
|
,[spr4]
|
|
,[sptime4]
|
|
,[spzt4])
|
|
VALUES
|
|
(@sqdh
|
|
,@sqsj
|
|
,@sqrid
|
|
,@sqr
|
|
,@ksid
|
|
,@ksmc
|
|
,@je
|
|
,@yt
|
|
,@bz
|
|
,@zt
|
|
,@spr1
|
|
,@sptime1
|
|
,@spzt1
|
|
,@spr2
|
|
,@sptime2
|
|
,@spzt2
|
|
,@spr3
|
|
,@sptime3
|
|
,@spzt3
|
|
,@spr4
|
|
,@sptime4
|
|
,@spzt4)";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [dbo].[xmjfsysq]
|
|
SET [sqdh] = @sqdh
|
|
,[sqsj] = @sqsj
|
|
,[sqrid] = @sqrid
|
|
,[sqr] = @sqr
|
|
,[ksid] = @ksid
|
|
,[ksmc] = @ksmc
|
|
,[je] = @je
|
|
,[yt] = @yt
|
|
,[bz] = @bz
|
|
,[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<xmjfsysqModel> getXmjfsysqBydh(string dh)
|
|
{
|
|
string sql = "select * from xmjfsysq where sqdh in (@dh)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<xmjfsysqModel>(sql, new { dh = dh }).ToList();
|
|
}
|
|
}
|
|
|
|
public object getMxBydqdid(string sqdid)
|
|
{
|
|
string sql = "select a.*,b.ggxh,b.jldw dw,(a.sl*a.dj) hj from xmjfsysq a join xmjingfei b on a.jfid=b.id where sqdid in (@sqdid)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql, new { sqdid = sqdid }).ToList();
|
|
}
|
|
}
|
|
|
|
public object opSave(string id, int ksid, string ksmc, string items, int sqrid, string sqr, string je, string bz, string bz1, string bz2, string bz3, string bz4, string bz5, string bz6, int zt, string del,string ksids, string ysid)
|
|
{
|
|
string errmsg = "";
|
|
string sqdh = getckkdh(out errmsg);
|
|
if (errmsg != "")
|
|
return new { State = 0, Message = errmsg };
|
|
xmjfsysqModel model = new xmjfsysqModel();
|
|
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.bz1 = bz1;
|
|
model.bz2 = bz2;
|
|
model.bz3 = bz3;
|
|
model.bz4 = bz4;
|
|
model.bz5 = bz5;
|
|
model.bz6 = bz6;
|
|
model.ysid = Convert.ToInt32(ysid);
|
|
model.zt = zt;
|
|
model.del = 0;
|
|
model.ksids = ksids;
|
|
model.sort = "单独添加";
|
|
if (!string.IsNullOrEmpty(del))
|
|
{
|
|
model.del = 1;
|
|
model.sort = "等待确认";
|
|
}
|
|
|
|
string sql = @"INSERT INTO [dbo].[xmjfsysq]
|
|
([sqdh],[sqsj],[tjsj],[sqrid],[sqr],[ksid],[ksmc],[je],[bz],[bz1],[bz2],[bz3],[bz4],[bz5],[bz6],[ysid],[zt],[del],[sort],ksids)
|
|
VALUES
|
|
(@sqdh,@sqsj,@tjsj,@sqrid,@sqr,@ksid,@ksmc,@je,@bz,@bz1,@bz2,@bz3,@bz4,@bz5,@bz6,@ysid,@zt,@del,@sort,@ksids)select SCOPE_IDENTITY()";
|
|
|
|
int sqdid = 0;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
sqdid = conn.Query<int>(sql, model).FirstOrDefault();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
List<xmjfsysqmxModel> models = new List<xmjfsysqmxModel>();
|
|
xmjfsysqmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 3)
|
|
{
|
|
m = new xmjfsysqmxModel();
|
|
m.sqdid = sqdid;
|
|
m.jfid = Convert.ToInt32(itemss[0]);
|
|
m.jfmc = itemss[1];
|
|
m.dj = Convert.ToDecimal(itemss[2]);
|
|
m.sl = 1;
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[xmjfsysqmx]
|
|
([sqdid],[jfid],[jfmc],[sl],[dj],[czsj])
|
|
VALUES
|
|
(@sqdid,@jfid,@jfmc,@sl,@dj,@czsj)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql2, models);
|
|
if (result > 0 || result==0) // result==0 允许明细为空
|
|
{
|
|
return new { State = 1, Message = "操作成功", id = sqdid };
|
|
}
|
|
else
|
|
return new { State = 0, Message = "操作失败" };
|
|
}
|
|
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 bz1, string bz2, string bz3, string bz4, string bz5, string bz6, int zt,string ksids, string ysid)
|
|
{
|
|
xmjfsysqModel model = new xmjfsysqModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.je = Convert.ToDecimal(je);
|
|
model.bz = bz;
|
|
model.bz1 = bz1;
|
|
model.bz2 = bz2;
|
|
model.bz3 = bz3;
|
|
model.bz4 = bz4;
|
|
model.bz5 = bz5;
|
|
model.bz6 = bz6;
|
|
model.ysid = Convert.ToInt32(ysid);
|
|
model.zt = zt;
|
|
model.ksids = ksids;
|
|
|
|
string sql = @"UPDATE [dbo].[xmjfsysq]
|
|
SET [je] = @je,[bz] = @bz,[bz1] = @bz1,[bz2] = @bz2,[bz3] = @bz3,[bz4] = @bz4,[bz5] = @bz5,[bz6] = @bz6,[ysid] = @ysid,[zt] = @zt,ksids=@ksids
|
|
WHERE id=@id";
|
|
|
|
string sql3 = "delete from xmjfsysqmx where sqdid=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
conn.Execute(sql, model);
|
|
conn.Execute(sql3, new { @id = id });
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
List<xmjfsysqmxModel> models = new List<xmjfsysqmxModel>();
|
|
xmjfsysqmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 3)
|
|
{
|
|
m = new xmjfsysqmxModel();
|
|
m.sqdid = Convert.ToInt32(id);
|
|
m.jfid = Convert.ToInt32(itemss[0]);
|
|
m.jfmc = itemss[1];
|
|
m.dj = Convert.ToDecimal(itemss[2]);
|
|
m.sl = 1;
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[xmjfsysqmx]
|
|
([sqdid],[jfid],[jfmc],[sl],[dj],[czsj])
|
|
VALUES
|
|
(@sqdid,@jfid,@jfmc,@sl,@dj,@czsj)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql2, models);
|
|
if (result > 0 || result == 0) // result==0 允许明细为空
|
|
{
|
|
//退货
|
|
//ck(models, -1);
|
|
return new { State = 1, Message = "修改成功", id = id };
|
|
}
|
|
else
|
|
return new { State = 0, Message = "修改失败" };
|
|
}
|
|
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 xmjfsysq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
xmjfsysqModel model = conn.Query<xmjfsysqModel>(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 xmjfsysq 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 xmjfsysq 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 xmjfsysq 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 = DateTime.Now, @spnr = yj, @zt = zt });
|
|
if (result > 0)
|
|
{
|
|
//扣费
|
|
if (zt == 8)
|
|
{
|
|
opKf(id);
|
|
}
|
|
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 xmjfsysq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
xmjfsysqModel model = conn.Query<xmjfsysqModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "";
|
|
switch (zw)
|
|
{
|
|
case "科室领导":
|
|
sql = "update xmjfsysq set sprid1=@sprid, spr1=@spr,sptime1=@sptime,spnr1=@spnr where id=@id";
|
|
break;
|
|
case "分管领导":
|
|
sql = "update xmjfsysq 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 = DateTime.Now, @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 };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public object opQd(string id, string sort, string sortid, int del)
|
|
{
|
|
xmjfsysqModel model = new xmjfsysqModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.sort = sort;
|
|
model.sortid = Convert.ToInt32(sortid);
|
|
model.del = del;
|
|
|
|
string sql = @"UPDATE [dbo].[xmjfsysq]
|
|
SET [sort] = @sort,[sortid] = @sortid,[del] = @del
|
|
WHERE id=@id and del=1";
|
|
|
|
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 };
|
|
}
|
|
}
|
|
}
|
|
|
|
private object opKf(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from xmjfsysqmx where sqdid=@id";
|
|
List<xmjfsysqmxModel> models = conn.Query<xmjfsysqmxModel>(sql2, new { @id = id }).ToList();
|
|
string sql = "update xmjingfei set hfje+= @hfje where id = @id";
|
|
|
|
try
|
|
{
|
|
int result = 0;
|
|
foreach (xmjfsysqmxModel model in models)
|
|
{
|
|
result += conn.Execute(sql, new { @id = model.jfid, @hfje = model.hj });
|
|
}
|
|
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 xmjfsysq 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 xmjfsysq where id=@id";
|
|
string sql2 = "delete from xmjfsysqmx where sqdid=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { id = id });
|
|
//result = conn.Execute(sql2, new { id = id });
|
|
conn.Execute(sql2, 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 int getCount(string key)
|
|
{
|
|
string sql = "select count(1) from dbo.xmjfsysq 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<xmjfsysqModel> getPage(int page, int pagesize, string key)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from xmjfsysq 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.xmjfsysqModel>(sql, new { key = key }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getCountLevel(string where)
|
|
{
|
|
string sql = "select count(1) from dbo.xmjfsysq";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public List<xmjfsysqModel> getPageLevel(int page, int pagesize, string where)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from xmjfsysq";
|
|
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.xmjfsysqModel>(sql).ToList();
|
|
}
|
|
}
|
|
}
|
|
} |