662 lines
26 KiB
C#
662 lines
26 KiB
C#
using Dapper;
|
|
using dccdc.Models;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Text.RegularExpressions;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class sqccbxDal
|
|
{
|
|
public List<sqccbxModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
return conn.Query<sqccbxModel>(sql, new { @id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public List<sqccbxModel> GetAllList(string id, string zt)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
if (!string.IsNullOrEmpty(zt))
|
|
{
|
|
sql += " and zt=@zt";
|
|
}
|
|
return conn.Query<sqccbxModel>(sql, new { @id = id, @zt = zt }).ToList();
|
|
}
|
|
}
|
|
|
|
public string GetHavedydhs()
|
|
{
|
|
string sql = " select stuff((select ','+dydh from sqccbx where dydh is not null and dydh != '' for xml path ('')),1,1,'') as haveids";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<string>(sql);
|
|
}
|
|
}
|
|
|
|
public object save(sqccbxModel model)
|
|
{
|
|
string sql = "";
|
|
if (model.id == 0)
|
|
{
|
|
sql = @"INSERT INTO [dbo].[sqccbx]
|
|
([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].[sqccbx]
|
|
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<sqccbxModel> getListBydh(string dh)
|
|
{
|
|
string sql = "select * from sqccbx where sqdh in (@dh)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<sqccbxModel>(sql, new { dh = dh }).ToList();
|
|
}
|
|
}
|
|
|
|
|
|
public object opSave(string id, int ksid, string ksmc, string items, int sqrid, string sqr, string je, string fyly, string fydh, string fydhs, string fydh2, string dydh, string bz, int zt, string qrsq)
|
|
{
|
|
string errmsg = "";
|
|
string sqdh = getckkdh(out errmsg);
|
|
if (errmsg != "")
|
|
return new { State = 0, Message = errmsg };
|
|
sqccbxModel model = new sqccbxModel();
|
|
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.fyly = fyly;
|
|
model.fydh = fydh;
|
|
model.fydhs = fydhs;
|
|
model.fydh2 = fydh2;
|
|
model.dydh = dydh;
|
|
model.zt = zt;
|
|
if (zt == 7)
|
|
{
|
|
model.zt = 6; //需要财务审核 状态是7变成6
|
|
}
|
|
if (qrsq == "0") //暂存
|
|
{
|
|
model.zt = 0;
|
|
}
|
|
|
|
string sql = @"INSERT INTO [dbo].[sqccbx]
|
|
([sqdh],[sqsj],[tjsj],[sqrid],[sqr],[ksid],[ksmc],[je],[bz],[fyly],[fydh],[fydhs],[fydh2],[dydh],[zt])
|
|
VALUES
|
|
(@sqdh,@sqsj,@tjsj,@sqrid,@sqr,@ksid,@ksmc,@je,@bz,@fyly,@fydh,@fydhs,@fydh2,@dydh,@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);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
List<sqccbxmxModel> models = new List<sqccbxmxModel>();
|
|
sqccbxmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 19)
|
|
{
|
|
m = new sqccbxmxModel();
|
|
m.sqdid = sqdid;
|
|
m.begint = Convert.ToDateTime(itemss[0]);
|
|
m.endt = Convert.ToDateTime(itemss[1]);
|
|
m.begind = itemss[2];
|
|
m.endd = itemss[3];
|
|
m.havecb = Convert.ToBoolean(itemss[4]);
|
|
m.gongju = itemss[5];
|
|
m.count = Convert.ToDecimal(itemss[6]);
|
|
m.je = Convert.ToDecimal(itemss[7]);
|
|
m.xiangmu = itemss[8];
|
|
m.rs = Convert.ToDecimal(itemss[9]);
|
|
m.ry = itemss[10];
|
|
m.ts = Convert.ToDecimal(itemss[11]);
|
|
m.bz = Convert.ToDecimal(itemss[12]);
|
|
m.bz2 = Convert.ToDecimal(itemss[13]);
|
|
m.hbz = Convert.ToDecimal(itemss[14]);
|
|
m.bhjtbz = Convert.ToInt32(itemss[15]);
|
|
m.je2 = Convert.ToDecimal(itemss[16]);
|
|
m.qt = itemss[17];
|
|
m.je3 = Convert.ToDecimal(itemss[18]);
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[sqccbxmx]
|
|
([sqdid],[begint],[begind],[endt],[endd],[havecb],[gongju],[count],[je],[xiangmu],[rs],[ry],[ts],[bz],[bz2],[hbz],[bhjtbz],[je2],[qt],[je3],[czsj])
|
|
VALUES
|
|
(@sqdid,@begint,@begind,@endt,@endd,@havecb,@gongju,@count,@je,@xiangmu,@rs,@ry,@ts,@bz,@bz2,@hbz,@bhjtbz,@je2,@qt,@je3,@czsj)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql2, models);
|
|
if (result > 0)
|
|
{
|
|
//退货
|
|
//ck(models, -1);
|
|
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 fyly, string fydh, string fydhs, string fydh2, string dydh, string bz, int zt, string qrsq)
|
|
{
|
|
sqccbxModel model = new sqccbxModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.je = Convert.ToDecimal(je);
|
|
model.fyly = fyly;
|
|
model.fydh = fydh;
|
|
model.fydhs = fydhs;
|
|
model.fydh2 = fydh2;
|
|
model.dydh = dydh;
|
|
model.bz = bz;
|
|
model.zt = zt;
|
|
if (zt == 7)
|
|
{
|
|
model.zt = 6; //需要财务审核 状态是7变成6
|
|
}
|
|
if (qrsq == "0") //暂存
|
|
{
|
|
model.zt = 0;
|
|
}
|
|
|
|
string sql = @"UPDATE [dbo].[sqccbx]
|
|
SET [je] = @je,[bz] = @bz,[fyly] = @fyly,[fydh] = @fydh,[fydhs] = @fydhs,[fydh2] = @fydh2,[dydh] = @dydh,[zt] = @zt
|
|
WHERE id=@id";
|
|
|
|
string sql3 = "delete from sqccbxmx 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<sqccbxmxModel> models = new List<sqccbxmxModel>();
|
|
sqccbxmxModel m;
|
|
foreach (string item in items.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
|
|
{
|
|
string[] itemss = item.Split('|');
|
|
if (itemss.Length == 19)
|
|
{
|
|
m = new sqccbxmxModel();
|
|
m.sqdid = Convert.ToInt32(id);
|
|
m.begint = Convert.ToDateTime(itemss[0]);
|
|
m.endt = Convert.ToDateTime(itemss[1]);
|
|
m.begind = itemss[2];
|
|
m.endd = itemss[3];
|
|
m.havecb = Convert.ToBoolean(itemss[4]);
|
|
m.gongju = itemss[5];
|
|
m.count = Convert.ToDecimal(itemss[6]);
|
|
m.je = Convert.ToDecimal(itemss[7]);
|
|
m.xiangmu = itemss[8];
|
|
m.rs = Convert.ToDecimal(itemss[9]);
|
|
m.ry = itemss[10];
|
|
m.ts = Convert.ToDecimal(itemss[11]);
|
|
m.bz = Convert.ToDecimal(itemss[12]);
|
|
m.bz2 = Convert.ToDecimal(itemss[13]);
|
|
m.hbz = Convert.ToDecimal(itemss[14]);
|
|
m.bhjtbz = Convert.ToInt32(itemss[15]);
|
|
m.je2 = Convert.ToDecimal(itemss[16]);
|
|
m.qt = itemss[17];
|
|
m.je3 = Convert.ToDecimal(itemss[18]);
|
|
m.czsj = DateTime.Now;
|
|
models.Add(m);
|
|
}
|
|
}
|
|
|
|
|
|
string sql2 = @"INSERT INTO [dbo].[sqccbxmx]
|
|
([sqdid],[begint],[begind],[endt],[endd],[havecb],[gongju],[count],[je],[xiangmu],[rs],[ry],[ts],[bz],[bz2],[hbz],[bhjtbz],[je2],[qt],[je3],[czsj])
|
|
VALUES
|
|
(@sqdid,@begint,@begind,@endt,@endd,@havecb,@gongju,@count,@je,@xiangmu,@rs,@ry,@ts,@bz,@bz2,@hbz,@bhjtbz,@je2,@qt,@je3,@czsj)";
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql2, models);
|
|
if (result > 0)
|
|
{
|
|
//退货
|
|
//ck(models, -1);
|
|
return new { State = 1, Message = "修改成功" };
|
|
}
|
|
else
|
|
return new { State = 0, Message = "操作失败" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public string GetTime(string timeStamp)
|
|
{
|
|
if (!timeStamp.Contains("Date("))
|
|
return timeStamp;
|
|
//处理字符串,截取括号内的数字
|
|
var strStamp = Regex.Matches(timeStamp, @"(?<=\()((?<gp>\()|(?<-gp>\))|[^()]+)*(?(gp)(?!))").Cast<Match>().Select(t => t.Value).ToArray()[0].ToString();
|
|
//处理字符串获取+号前面的数字
|
|
var str = Convert.ToInt64(strStamp);
|
|
long timeTricks = new DateTime(1970, 1, 1).Ticks + str * 10000 + TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).Hours * 3600 * (long)10000000;
|
|
return new DateTime(timeTricks).ToString("yyyy-MM-dd HH:mm:ss");
|
|
}
|
|
|
|
public object opSp(string id, int sprid, string spr, string yj, bool ty)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqccbxModel model = conn.Query<sqccbxModel>(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 sqccbx 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 sqccbx set sprid2=@sprid, spr2=@spr,sptime2=@sptime,spnr2=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 6; } else { zt = -2; }
|
|
break;
|
|
case 6:
|
|
sql = "update sqccbx set sprid3=@sprid, spr3=@spr,sptime3=@sptime,spnr3=@spnr,zt=@zt where id=@id";
|
|
if (ty) { zt = 7; } else { zt = -6; }
|
|
break;
|
|
case 7:
|
|
sql = "update sqccbx set sprid4=@sprid, spr4=@spr,sptime4=@sptime,spnr4=@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)
|
|
{
|
|
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 sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqccbxModel model = conn.Query<sqccbxModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "";
|
|
switch (zw)
|
|
{
|
|
case "科室领导":
|
|
sql = "update sqccbx set sprid1=@sprid, spr1=@spr,sptime1=@sptime,spnr1=@spnr where id=@id";
|
|
break;
|
|
case "分管领导":
|
|
sql = "update sqccbx 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 opZf(string id, int zfrid, string zfr)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqccbxModel model = conn.Query<sqccbxModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "update sqccbx set zfrid=@zfrid,zfr=@zfr,zftime=@zftime where id=@id";
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @zfrid = zfrid, @zfr = zfr, @zftime = 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 object opBx(string id, int bxrid, string bxr)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from sqccbx where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
sqccbxModel model = conn.Query<sqccbxModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
string sql = "update sqccbx set bxrid=@bxrid, bxr=@bxr,bxtime=@bxtime where id=@id";
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { @id = id, @bxrid = bxrid, @bxr = bxr, @bxtime = 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 };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
//获取申请单号
|
|
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 sqccbx 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 sqccbx where id=@id";
|
|
string sql2 = "delete from sqccbxmx where sqdid=@id";
|
|
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
try
|
|
{
|
|
int result = conn.Execute(sql, new { id = id });
|
|
result = 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.sqccbx 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<sqccbxModel> getPage(int page, int pagesize, string key)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqccbx 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.sqccbxModel>(sql, new { key = key }).ToList();
|
|
}
|
|
}
|
|
|
|
public int getCountLevel(string where)
|
|
{
|
|
string sql = "select count(1) from dbo.sqccbx";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public List<sqccbxModel> getPageLevel(int page, int pagesize, string where)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from sqccbx";
|
|
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.sqccbxModel>(sql).ToList();
|
|
}
|
|
}
|
|
}
|
|
}
|