520 lines
15 KiB
C#
520 lines
15 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using dccdc.Models.JianDu;
|
|
using Dapper;
|
|
using System.Data;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class JianDuDal
|
|
{
|
|
public object save_dict(jd_dict dict)
|
|
{
|
|
// throw new NotImplementedException();
|
|
string sql = "";
|
|
if(dict.id==0)
|
|
{
|
|
sql = @"INSERT INTO [jd_dict]
|
|
([lb]
|
|
,[mc]
|
|
,[zt]
|
|
,[px])
|
|
VALUES
|
|
(@lb
|
|
,@mc
|
|
,@zt
|
|
,@px)
|
|
|
|
";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [dccdc].[dbo].[jd_dict]
|
|
SET[lb] = @lb
|
|
,[mc] = @mc
|
|
,[zt] = @zt
|
|
,[px] = @px
|
|
WHERE id=@id
|
|
";
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
conn.Execute(sql, dict);
|
|
return new { State = 1, Message = "操作成功" };
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public int getwsxkcount(string key, string fr, string s_fzrq1, string s_fzrq2, string s_jyfw)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select count(1) from xyl_wsxk where 1=1 and del=0";
|
|
if(!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and dwmc like @key";
|
|
}
|
|
if(!string.IsNullOrEmpty(fr))
|
|
{
|
|
sql += " and fr like @fr";
|
|
}
|
|
DateTime dt1 = DateTime.Now;
|
|
DateTime dt2 = DateTime.Now;
|
|
if (!string.IsNullOrEmpty(s_fzrq1))
|
|
{
|
|
if(DateTime.TryParse(s_fzrq1,out dt1))
|
|
{
|
|
sql += " and fzsj>=@dt1";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(s_fzrq2))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq2, out dt2))
|
|
{
|
|
sql += " and fzsj<=@dt2";
|
|
}
|
|
}
|
|
if(!string.IsNullOrEmpty(s_jyfw))
|
|
{
|
|
sql += " and jyfwid=@s_jyfw";
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new {key="%"+key+"%",fr="%"+fr+"%", dt1,dt2, s_jyfw });
|
|
}
|
|
}
|
|
|
|
public object delwsxk(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "update xyl_wsxk set del=1 where id=@id";
|
|
try
|
|
{
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
int c = conn.Execute(sql, new { id = id });
|
|
if(c>0)
|
|
{
|
|
return new { State = 1, Message = "删除成功！" };
|
|
}
|
|
else
|
|
{
|
|
return new { State = 0, Message = "没有找到要删除的数据，请联系管理员！" };
|
|
}
|
|
}
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
public object delfsxk(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "update xyl_fsxkz set del=1 where id=@id";
|
|
try
|
|
{
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
int c = conn.Execute(sql, new { id = id });
|
|
if (c > 0)
|
|
{
|
|
return new { State = 1, Message = "删除成功！" };
|
|
}
|
|
else
|
|
{
|
|
return new { State = 0, Message = "没有找到要删除的数据，请联系管理员！" };
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
public int getfsxkcount(string key, string fr, string s_fzrq1, string s_fzrq2)
|
|
{
|
|
string sql = "select count(1) from xyl_fsxkz where 1=1 and del=0";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and dwmc like @key";
|
|
}
|
|
if (!string.IsNullOrEmpty(fr))
|
|
{
|
|
sql += " and fr like @fr";
|
|
}
|
|
DateTime dt1 = DateTime.Now;
|
|
DateTime dt2 = DateTime.Now;
|
|
if (!string.IsNullOrEmpty(s_fzrq1))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq1, out dt1))
|
|
{
|
|
sql += " and fzsj>=@dt1";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(s_fzrq2))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq2, out dt2))
|
|
{
|
|
sql += " and fzsj<=@dt2";
|
|
}
|
|
}
|
|
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2 });
|
|
}
|
|
}
|
|
|
|
public object getfsxklistpage(string key, string fr, string s_fzrq1, string s_fzrq2, int page, int pagesize)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from xyl_fsxkz where 1=1 and del=0";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and dwmc like @key";
|
|
}
|
|
if (!string.IsNullOrEmpty(fr))
|
|
{
|
|
sql += " and fr like @fr";
|
|
}
|
|
DateTime dt1 = DateTime.Now;
|
|
DateTime dt2 = DateTime.Now;
|
|
if (!string.IsNullOrEmpty(s_fzrq1))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq1, out dt1))
|
|
{
|
|
sql += " and fzsj>=@dt1";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(s_fzrq2))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq2, out dt2))
|
|
{
|
|
sql += " and fzsj<=@dt2";
|
|
}
|
|
}
|
|
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2 }).ToList();
|
|
}
|
|
}
|
|
|
|
public object savefsxk(fsxkz wsxk)
|
|
{
|
|
string sql = "";
|
|
if (wsxk.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [xyl_fsxkz]
|
|
(
|
|
[dwmc]
|
|
,[dwdz]
|
|
,[lxdh]
|
|
,[xkxm]
|
|
,[fr]
|
|
,[sfzh]
|
|
,[xydm]
|
|
,[sqsj]
|
|
,[fzsj]
|
|
,[bh1]
|
|
,[bh2]
|
|
,[yxq1]
|
|
,[yxq2]
|
|
,[blry]
|
|
,[blry_name]
|
|
,[blsj]
|
|
)
|
|
VALUES
|
|
(
|
|
@dwmc
|
|
,@dwdz
|
|
,@lxdh
|
|
,@xkxm
|
|
,@fr
|
|
,@sfzh
|
|
,@xydm
|
|
,@sqsj
|
|
,@fzsj
|
|
,@bh1
|
|
,@bh2
|
|
,@yxq1
|
|
,@yxq2
|
|
,@blry
|
|
,@blry_name
|
|
,@blsj
|
|
)
|
|
select SCOPE_IDENTITY()
|
|
";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [xyl_fsxkz]
|
|
SET
|
|
[dwmc] = @dwmc
|
|
,[dwdz] = @dwdz
|
|
,[lxdh] = @lxdh
|
|
,[xkxm] = @xkxm
|
|
,[fr] = @fr
|
|
,[sfzh] = @sfzh
|
|
,[xydm] = @xydm
|
|
,[sqsj] = @sqsj
|
|
,[fzsj] = @fzsj
|
|
,[bh2] = @bh2
|
|
,[yxq1] = @yxq1
|
|
,[yxq2] = @yxq2
|
|
,[blry] = @blry
|
|
,[blry_name] = @blry_name
|
|
,[blsj] = @blsj,bh1=@bh1
|
|
WHERE id=@id
|
|
";
|
|
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int id = 0;
|
|
if (wsxk.id == 0)
|
|
{
|
|
id = conn.ExecuteScalar<int>(sql, wsxk);
|
|
wsxk.id = id;
|
|
}
|
|
else
|
|
{
|
|
conn.Execute(sql, wsxk);
|
|
}
|
|
return new { State = 1, Message = "操作成功！", tag = wsxk.id };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public object getfsxkbg(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from xyl_fsxkz where id=@id";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql, new { id }).ToList();
|
|
}
|
|
}
|
|
|
|
public object getwsxkbg(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from xyl_wsxk where id=@id";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql, new { id }).ToList();
|
|
}
|
|
}
|
|
|
|
public object savewsxk(wsxkz wsxkz)
|
|
{
|
|
string sql = "";
|
|
if (wsxkz.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [xyl_wsxk]
|
|
([fgks]
|
|
,[gxqy]
|
|
,[dwmc]
|
|
,[dwdz]
|
|
,[lxdh]
|
|
,[jyxz]
|
|
,[jyfw]
|
|
,[xkxm]
|
|
,[fr]
|
|
,[sfzh]
|
|
,[xydm]
|
|
,[jymj]
|
|
,[sqsj]
|
|
,[fzsj]
|
|
,[bh1]
|
|
,[bh2]
|
|
,[yxq1]
|
|
,[yxq2]
|
|
,[blry]
|
|
,[blry_name]
|
|
,[blsj]
|
|
,[fgksid]
|
|
,[gxqyid]
|
|
,[jyxzid]
|
|
,[jyfwid])
|
|
VALUES
|
|
(@fgks
|
|
,@gxqy
|
|
,@dwmc
|
|
,@dwdz
|
|
,@lxdh
|
|
,@jyxz
|
|
,@jyfw
|
|
,@xkxm
|
|
,@fr
|
|
,@sfzh
|
|
,@xydm
|
|
,@jymj
|
|
,@sqsj
|
|
,@fzsj
|
|
,@bh1
|
|
,@bh2
|
|
,@yxq1
|
|
,@yxq2
|
|
,@blry
|
|
,@blry_name
|
|
,@blsj
|
|
,@fgksid
|
|
,@gxqyid
|
|
,@jyxzid
|
|
,@jyfwid)
|
|
select SCOPE_IDENTITY()
|
|
";
|
|
}
|
|
else
|
|
{
|
|
sql = @"UPDATE [xyl_wsxk]
|
|
SET [fgks] = @fgks
|
|
,[gxqy] = @gxqy
|
|
,[dwmc] = @dwmc
|
|
,[dwdz] = @dwdz
|
|
,[lxdh] = @lxdh
|
|
,[jyxz] = @jyxz
|
|
,[jyfw] = @jyfw
|
|
,[xkxm] = @xkxm
|
|
,[fr] = @fr
|
|
,[sfzh] = @sfzh
|
|
,[xydm] = @xydm
|
|
,[jymj] = @jymj
|
|
,[sqsj] = @sqsj
|
|
,[fzsj] = @fzsj
|
|
,[bh2] = @bh2
|
|
,[yxq1] = @yxq1
|
|
,[yxq2] = @yxq2
|
|
,[blry] = @blry
|
|
,[blry_name] = @blry_name
|
|
,[blsj] = @blsj
|
|
,[fgksid] = @fgksid
|
|
,[gxqyid] = @gxqyid
|
|
,[jyxzid] = @jyxzid
|
|
,[jyfwid] = @jyfwid,bh1=@bh1
|
|
WHERE id=@id
|
|
";
|
|
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int id = 0;
|
|
if (wsxkz.id==0)
|
|
{
|
|
id = conn.ExecuteScalar<int>(sql, wsxkz);
|
|
wsxkz.id = id;
|
|
}
|
|
else {
|
|
conn.Execute(sql, wsxkz); }
|
|
return new { State = 1, Message = "操作成功！",tag=wsxkz.id };
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public object getwsxklistpage(string key, string fr, string s_fzrq1, string s_fzrq2, string s_jyfw, int page, int pagesize)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from xyl_wsxk where 1=1 and del=0";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and dwmc like @key";
|
|
}
|
|
if (!string.IsNullOrEmpty(fr))
|
|
{
|
|
sql += " and fr like @fr";
|
|
}
|
|
DateTime dt1 = DateTime.Now;
|
|
DateTime dt2 = DateTime.Now;
|
|
if (!string.IsNullOrEmpty(s_fzrq1))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq1, out dt1))
|
|
{
|
|
sql += " and fzsj>=@dt1";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(s_fzrq2))
|
|
{
|
|
if (DateTime.TryParse(s_fzrq2, out dt2))
|
|
{
|
|
sql += " and fzsj<=@dt2";
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(s_jyfw))
|
|
{
|
|
sql += " and jyfwid=@s_jyfw";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query(sql, new { key = "%" + key + "%", fr = "%" + fr + "%", dt1, dt2, s_jyfw }).ToList();
|
|
}
|
|
}
|
|
|
|
public object getjddict(string lb)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from jd_dict where zt=1 and lb=@lb order by px";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.JianDu.jd_dict>(sql, new { lb }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<jd_dict> getdictlistpage(string key, string lb, int page, int pagesize)
|
|
{
|
|
string sql = "select *,row_number() over(order by px) as rownum from jd_dict where 1=1 ";
|
|
if (!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and mc like @key";
|
|
}
|
|
if (lb != "0")
|
|
{
|
|
sql += " and lb=@lb";
|
|
}
|
|
sql = "select * from (" + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.JianDu.jd_dict>(sql, new { key = "%" + key + "%", lb }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getdictcount(string key, string lb)
|
|
{
|
|
string sql = "select count(1) from jd_dict where 1=1";
|
|
if(!string.IsNullOrEmpty(key))
|
|
{
|
|
sql += " and mc like @key";
|
|
}
|
|
if(lb!="0")
|
|
{
|
|
sql += " and lb=@lb";
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { key="%"+key+"%", lb });
|
|
}
|
|
}
|
|
}
|
|
}
|