304 lines
13 KiB
C#
304 lines
13 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 chuchai_sqDal
|
|
{
|
|
public int getCountLevel(string where)
|
|
{
|
|
string sql = "select count(1) from dbo.chuchai_sq";
|
|
if (!string.IsNullOrEmpty(where))
|
|
{
|
|
sql += " where " + where;
|
|
}
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql);
|
|
}
|
|
}
|
|
|
|
public List<chuchai_sqModel> getPageLevel(int page, int pagesize, string where)
|
|
{
|
|
string sql = "select *,row_number() over(order by id desc) as rownum from chuchai_sq";
|
|
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.chuchai_sqModel>(sql).ToList();
|
|
}
|
|
}
|
|
|
|
private string getchuchaisqdh(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 chuchai_sq 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 List<chuchai_sqModel> GetAllList(string id)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql = "select * from chuchai_sq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql += " and id=@id";
|
|
}
|
|
return conn.Query<chuchai_sqModel>(sql, new { @id = id }).ToList();
|
|
}
|
|
}
|
|
|
|
public object chuchai_sqSave(string id, int ksid, string ksmc, int sqrid, string sqr, string je, string cclx, string ccrs, string ccry, string ccryids, string filepath, string sy, string ccsj, string ccts, string ccdd, string cclb, string areaid1, string areaid2, string areaid3, string area, string fyly1, string fyly2, string fyly, string fydh, string fydhs, string fydh2, int zt)
|
|
{
|
|
string errmsg = "";
|
|
string sqdh = getchuchaisqdh(out errmsg);
|
|
if (errmsg != "")
|
|
return new { State = 0, Message = "false" };
|
|
chuchai_sqModel model = new chuchai_sqModel();
|
|
model.sqdh = sqdh;
|
|
model.sqrid = sqrid;
|
|
model.sqr = sqr;
|
|
model.sqsj = DateTime.Now;
|
|
model.ksid = ksid;
|
|
model.ksmc = ksmc;
|
|
model.je = Convert.ToDecimal(je);
|
|
model.cclx = cclx;
|
|
model.ccrs = Convert.ToInt32(ccrs);
|
|
model.ccry = ccry;
|
|
model.ccryids = ccryids;
|
|
model.filepath = filepath;
|
|
model.sy = sy;
|
|
model.ccsj = Convert.ToDateTime(ccsj);
|
|
model.ccts = Convert.ToInt32(ccts);
|
|
model.ccdd = ccdd;
|
|
model.cclb = cclb;
|
|
model.areaid1 = Convert.ToInt32(areaid1);
|
|
model.areaid2 = Convert.ToInt32(areaid2);
|
|
model.areaid3 = Convert.ToInt32(areaid3);
|
|
model.area = area;
|
|
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].[chuchai_sq]
|
|
([sqdh],[sqsj],[sqrid],[sqr],[ksid],[ksmc],[je],[cclx],[ccrs],[ccry],[ccryids],[filepath],[sy],[ccsj],[ccts],[ccdd],[cclb],[areaid1],[areaid2],[areaid3],[area],[fyly1],[fyly2],[fyly],[fydh],[fydhs],[fydh2],[zt])
|
|
VALUES
|
|
(@sqdh,@sqsj,@sqrid,@sqr,@ksid,@ksmc,@je,@cclx,@ccrs,@ccry,@ccryids,@filepath,@sy,@ccsj,@ccts,@ccdd,@cclb,@areaid1,@areaid2,@areaid3,@area,@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();
|
|
return new { State = 1, Message = "操作成功！", id = sqdid };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
public object CcSave2(string id, int ksid, string ksmc, int sqrid, string sqr, string je, string cclx, string ccrs, string ccry, string ccryids, string filepath, string sy, string ccsj, string ccts, string ccdd, string cclb, string areaid1, string areaid2, string areaid3, string area, string fyly1, string fyly2, string fyly, string fydh, string fydhs, string fydh2, int zt)
|
|
{
|
|
chuchai_sqModel model = new chuchai_sqModel();
|
|
model.id = Convert.ToInt32(id);
|
|
model.je = Convert.ToDecimal(je);
|
|
model.ccrs = Convert.ToInt32(ccrs);
|
|
model.sy = sy;
|
|
model.ccsj = Convert.ToDateTime(ccsj);
|
|
model.cclx = cclx;
|
|
model.ccts = Convert.ToInt32(ccts);
|
|
model.ccry = ccry;
|
|
model.ccryids = ccryids;
|
|
model.filepath = filepath;
|
|
model.ccdd = ccdd;
|
|
model.cclb = cclb;
|
|
model.areaid1 = Convert.ToInt32(areaid1);
|
|
model.areaid2 = Convert.ToInt32(areaid2);
|
|
model.areaid3 = Convert.ToInt32(areaid3);
|
|
model.area = area;
|
|
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].[chuchai_sq]
|
|
SET [je] = @je,[cclx] = @cclx,[ccrs] = @ccrs,[ccry] = @ccry,[ccryids] = @ccryids,[filepath] = @filepath,[sy] = @sy,[ccsj] = @ccsj,[ccts] = @ccts,[ccdd] = @ccdd,[cclb] = @cclb,[areaid1] = @areaid1,[areaid2] = @areaid2,[areaid3] = @areaid3,[area] = @area,[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 = "操作成功！" };
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
}
|
|
|
|
public object delete(string id)
|
|
{
|
|
string sql = "delete from chuchai_sq 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 opCcSp(string id, int sprid, string spr, string yj, bool ty)
|
|
{
|
|
using (IDbConnection conn = CommHelper.GetSqlConnection())
|
|
{
|
|
string sql2 = "select * from chuchai_sq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
chuchai_sqModel model = conn.Query<chuchai_sqModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
bool isSzyd = string.IsNullOrEmpty(model.fydh2) ? false : true;
|
|
string sql = "";
|
|
int zt = 0;
|
|
switch (model.zt)
|
|
{
|
|
case 1:
|
|
sql = "update chuchai_sq 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 chuchai_sq 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 chuchai_sq 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)
|
|
{
|
|
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 chuchai_sq where 1=1";
|
|
if (!string.IsNullOrEmpty(id))
|
|
{
|
|
sql2 += " and id=@id";
|
|
}
|
|
chuchai_sqModel model = conn.Query<chuchai_sqModel>(sql2, new { @id = id }).FirstOrDefault();
|
|
if (model == null)
|
|
return new { State = 0, Message = "不存在记录" };
|
|
else
|
|
{
|
|
bool isSzyd = string.IsNullOrEmpty(model.fydh2) ? false : true;
|
|
string sql = "";
|
|
switch (zw)
|
|
{
|
|
case "科室领导":
|
|
sql = "update chuchai_sq set sprid1=@sprid, spr1=@spr,sptime1=@sptime,spnr1=@spnr where id=@id";
|
|
break;
|
|
case "分管领导":
|
|
sql = "update chuchai_sq 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 };
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|