324 lines
11 KiB
C#
324 lines
11 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using dccdc.Models;
|
|
using Dapper;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class YMDJDal
|
|
{
|
|
|
|
public object getymdj()
|
|
{
|
|
string sql = "select vaccine.name as name,sum(YMDJMX.sfje) as num from YMDJ " +
|
|
" left join YMDJMX" +
|
|
" ON YMDJ.ID = YMDJMX.ymdjbid" +
|
|
" left join vaccine" +
|
|
" on YMDJMX.ymid = vaccine.id" +
|
|
" left join charge" +
|
|
" on YMDJ.chargeid = charge.id" +
|
|
" where jfzt = '已缴费' and YMDJMX.sfje <>0" +
|
|
" and charge.moneydate>convert(varchar(10),getdate(),121) " +
|
|
" group by vaccine.name";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql).ToList();
|
|
}
|
|
}
|
|
|
|
public int getDJCount(string zsr, string djr, string djsjb, string djsje)
|
|
{
|
|
string sql = "select count(1) from dbo.YMDJ where 1=1 ";
|
|
if (!string.IsNullOrEmpty(zsr))
|
|
{
|
|
sql += " and name like @zsr";
|
|
}
|
|
if (!string.IsNullOrEmpty(djr))
|
|
{
|
|
sql += " and djrxm like @djr";
|
|
}
|
|
if (!string.IsNullOrEmpty(djsjb))
|
|
{
|
|
sql += " and djsj >= '" + djsjb + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(djsje))
|
|
{
|
|
sql += " and djsj <= '" + djsje + "'";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
//new { zsr = "'%" + zsr + "%'", djr = "'%" + djr + "%'" };
|
|
var result = conn.ExecuteScalar<int>(sql, new { zsr = "%" + zsr + "%", djr = "%" + djr + "%" });
|
|
return result;
|
|
}
|
|
}
|
|
public OperationResult delDj(ERPUser user, string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
YMDJDal dal = new YMDJDal();
|
|
YMDJ ymdj = dal.getOneById(id);
|
|
if(ymdj.djrid != user.ID.ToString())
|
|
{
|
|
return new OperationResult { State = 0, Message = "不能删除其他人的登记！" };
|
|
}else if (ymdj.zs == "1")
|
|
{
|
|
return new OperationResult { State = 0, Message = "该登记已注射完成，不能删除！" };
|
|
}
|
|
else if (ymdj.jfzt == "已缴费" && ymdj.chargeid != "0")
|
|
{
|
|
return new OperationResult { State = 0, Message = "该登记已缴费，不能删除！" };
|
|
}
|
|
YMDJMXDal mxDal = new YMDJMXDal();
|
|
List<YMDJMX> ymdjmxs = mxDal.getList(1, 99999, ymdj.ymdjid);
|
|
bool hasZs = false;
|
|
for (int i = 0; i < ymdjmxs.Count(); i++)
|
|
{
|
|
if(ymdjmxs[i].zssl > 0)
|
|
{
|
|
hasZs = true;
|
|
}
|
|
}
|
|
if (hasZs)
|
|
{
|
|
return new OperationResult { State = 0, Message = "该登记已有疫苗注射成功，不能删除！" };
|
|
}
|
|
string sql = "delete from YMDJMX where ymdjbid = '" + ymdj.id+"'";
|
|
conn.Execute(sql);
|
|
sql = "delete from YMDJ where id = " + id;
|
|
conn.Execute(sql);
|
|
sql = "delete from charge_detail where chargeid = " + ymdj.chargeid;
|
|
conn.Execute(sql);
|
|
sql = "delete from charge where id = " + ymdj.chargeid;
|
|
conn.Execute(sql);
|
|
}
|
|
return new OperationResult { State = 1, Message = "删除成功！" };
|
|
}
|
|
public YMDJ getOneById(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from YMDJ where id="+id;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.YMDJ>(sql).FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public int getCount(string name, string ymdjid)
|
|
{
|
|
string sql = "select count(1) from dbo.YMDJ where 1=1 ";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " and name like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(ymdjid))
|
|
{
|
|
sql += " and ymdjid = @ymdjid";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { name = "'%" + name + "%'", ymdjid = ymdjid });
|
|
}
|
|
}
|
|
|
|
public List<YMDJ> getTFDJ(string xm)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select YMDJ.* from YMDJ left join charge on YMDJ.chargeid = charge.id where jfzt='已缴费' and zs = 0 and charge.money > 0";
|
|
if(!string.IsNullOrEmpty(sql))
|
|
{
|
|
sql += " and YMDJ.name like @xm";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.YMDJ>(sql,new { xm = "%" + xm + "%" }).ToList();
|
|
}
|
|
}
|
|
public List<YMDJ> getJFDJ(string xm)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select *,row_number() over(order by id) as rownum from YMDJ where jfzt='已缴费' and zs = 0";
|
|
if(!string.IsNullOrEmpty(xm))
|
|
{
|
|
sql += " and name like @xm";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.YMDJ>(sql,new { xm = "%"+xm+"%" }).ToList();
|
|
}
|
|
}
|
|
|
|
public YMDJ getDJByChargeID(string chargeid)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from ymdj where chargeid=@chargeid";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<YMDJ>(sql, new { chargeid = chargeid }).FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public List<YMDJ> getList(int page, int pagesize, string name, string ymdjid)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select *,row_number() over(order by id) as rownum from YMDJ where 1=1";
|
|
if (!string.IsNullOrEmpty(name))
|
|
{
|
|
sql += " and name like @name";
|
|
}
|
|
if (!string.IsNullOrEmpty(ymdjid))
|
|
{
|
|
sql += " and ymdjid = @ymdjid";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.YMDJ>(sql, new { name = "%" + name + "%", ymdjid = ymdjid }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<YMDJ> getDLList(int page, int pagesize, string zsr, string djr, string djsjb, string djsje)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = @"select *,row_number() over(order by id) as rownum from YMDJ a left join
|
|
(select ymdjbid, (select name + ' ' from v_ymdjmx where ymdjbid = aa.ymdjbid for xml path('')) as names from v_ymdjmx aa group by ymdjbid)b on a.id = b.ymdjbid where 1 = 1";
|
|
if (!string.IsNullOrEmpty(zsr))
|
|
{
|
|
sql += " and name like @zsr";
|
|
}
|
|
if (!string.IsNullOrEmpty(djr))
|
|
{
|
|
sql += " and djrxm like @djr";
|
|
}
|
|
if (!string.IsNullOrEmpty(djsjb))
|
|
{
|
|
sql += " and djsj >= '" + djsjb + "'";
|
|
}
|
|
if (!string.IsNullOrEmpty(djsje))
|
|
{
|
|
sql += " and djsj <= '" + djsje + "'";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.YMDJ>(sql, new { zsr = "%" + zsr + "%", djr = "%" + djr + "%" }).ToList();
|
|
}
|
|
}
|
|
|
|
public OperationResult updateZS(string id)
|
|
{
|
|
string sql = "update YMDJ set zs = 1 where id = " + id;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int c = conn.Execute(sql);
|
|
if (c > 0)
|
|
{
|
|
return new OperationResult { State = c, Message = "保存成功！" };
|
|
}
|
|
else
|
|
{
|
|
return new OperationResult { State = 0, Message = "操作失败，请联系管理员！" };
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new OperationResult { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public OperationResult save(YMDJ cpm)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "";
|
|
if (cpm.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[YMDJ]
|
|
([ymdjid],
|
|
[ymjzid],
|
|
[name],
|
|
[djrid],
|
|
[djrxm],
|
|
[djsj],
|
|
[sfzje],
|
|
[jfzt],
|
|
[jfsj],
|
|
[zs],
|
|
[chargeid]
|
|
)
|
|
VALUES
|
|
(@ymdjid,
|
|
@ymjzid,
|
|
@name,
|
|
@djrid,
|
|
@djrxm,
|
|
@djsj,
|
|
@sfzje,
|
|
@jfzt,
|
|
@jfsj,
|
|
@zs,
|
|
@chargeid
|
|
)select SCOPE_IDENTITY()
|
|
";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [dbo].[YMDJ]
|
|
SET [ymdjid]=@ymdjid,
|
|
[ymjzid]=@ymjzid,
|
|
[name]=@name,
|
|
[djrid]=@djrid,
|
|
[djrxm]=@djrxm,
|
|
[djsj]=@djsj,
|
|
[sfzje]=@sfzje,
|
|
[jfzt]=@jfzt,
|
|
[jfsj]=@jfsj,
|
|
[zs]=@zs,
|
|
[chargeid]=@chargeid
|
|
WHERE id=@id
|
|
";
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int c = conn.Query<int>(sql, cpm).FirstOrDefault();
|
|
if (c > 0)
|
|
{
|
|
return new OperationResult { State = c, Message = "保存成功！" };
|
|
}
|
|
else
|
|
{
|
|
return new OperationResult { State = 0, Message = "操作失败，请联系管理员！" };
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new OperationResult { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 根据疫苗ID获取收费项目的子项
|
|
/// </summary>
|
|
/// <param name="sfxmid"></param>
|
|
/// <returns></returns>
|
|
public List<ChargeProjectMaintainModel> GetchildsfxmByid(string sfxmid,string jmlx)
|
|
{
|
|
|
|
StringBuilder b = new StringBuilder("select * from charge_project_maintain where id in (select add_ids from charge_project_maintain where id =@sfxmid)");
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<ChargeProjectMaintainModel>(b.ToString(), new { id = sfxmid }).ToList();
|
|
}
|
|
}
|
|
}
|
|
}
|